package com.fuse.storage.sql.data_objects; /* FUSE Light Server - multiuser server application Copyright (C) 2000 Aapo Kyrola / Sulake Oy Helsinki, Finland This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. */ import java.util.*; import java.io.*; import java.text.*; import java.sql.*; import com.fuse.Log; import com.fuse.storage.*; import com.fuse.storage.data_objects.*; import com.fuse.storage.sql.*; /** * FUSEUser represents a registered user of a FUSE-service. * Name implementation classes FUSEUser, like SQLFUSEUser * @author Aapo Kyrola */ public class SQLFUSEUser extends FUSEUser implements SQLDataObject { public static final SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); /* User data is read into a properties - object */ private Properties properties = null; // Keys of Properties that maps directly into fuse_user table. private static Vector fuseUserKeys = null; /* Constructor */ public SQLFUSEUser() { properties = new Properties(); fuseUserKeys = new Vector(); fuseUserKeys.addElement("id"); fuseUserKeys.addElement("name"); fuseUserKeys.addElement("password"); fuseUserKeys.addElement("figure"); fuseUserKeys.addElement("sex"); fuseUserKeys.addElement("realname.first"); fuseUserKeys.addElement("realname.last"); fuseUserKeys.addElement("phonenumber"); fuseUserKeys.addElement("phoneNumber"); // This was also used somewhere fuseUserKeys.addElement("email"); fuseUserKeys.addElement("address"); fuseUserKeys.addElement("post_location"); fuseUserKeys.addElement("zipcode"); fuseUserKeys.addElement("age"); fuseUserKeys.addElement("custom"); properties.put("id", "" + System.currentTimeMillis()); // risk - may not be unique.. } /* getMyId() returns the id of a user. If it doesn't exist, -1 is returned Could return just boolean or wrap all stuff in Hashtable. PRIMARY KEY might speed following statements. */ private long getMyId(Connection con) throws DatabaseException{ Statement stmt = null; String cmd = null; ResultSet rs = null; long retval = -1; try { stmt = con.createStatement(); cmd = new String("SELECT id FROM fuse_user WHERE loginname='"+this.getName()+"'"); rs = stmt.executeQuery(cmd); if (rs.next() != false) { retval = rs.getLong("id"); } stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in getMyId(): selecting user"); } finally { try { stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in getMyId(): finally"); } } return retval; } // Jotta ei ajonaikaisia castaus-sxceptioneita (String)... private String getPropertyString(String key) { if (properties.get(key) instanceof NullObject) { return null; } return (String) properties.get(key); } /* PreparedStatement is used in multiple places. Setting values here, for fewer lines of code. */ private void setPreparedValues(PreparedStatement pstmt) throws SQLException { pstmt.setString(1, getPropertyString("name")); pstmt.setString(2, getPropertyString("password")); pstmt.setString(3, getPropertyString("figure")); pstmt.setString(4, getPropertyString("sex")); pstmt.setString(5, getPropertyString("realname.first")); pstmt.setString(6, getPropertyString("realname.last")); pstmt.setString(7, getPropertyString("phonenumber")); pstmt.setString(8, getPropertyString("email")); pstmt.setString(9, getPropertyString("address")); pstmt.setString(10, getPropertyString("post_location")); pstmt.setString(11, getPropertyString("zipcode")); // pstmt.setString(12, getPropertyString("age")); String ageStr = getPropertyString("age"); int ageInt = 0; if (ageStr != null) { try { ageInt = Integer.parseInt(ageStr); } catch (Exception e) { Log.error("SQLFUSEUser:Couldn't parseInt(age). age="+ageStr); ageInt = 0; } } pstmt.setInt(12, ageInt); pstmt.setString(13, getPropertyString("custom")); } // Loops thru user's user_params and puts them into properties. private void setUserParams(Properties properties, ResultSet rs) throws DatabaseException{ String tmp = null; try { while (rs.next() != false) { // Some of the values can be null. put() doesn't allow nulls. properties.put(""+rs.getString("name"), ""+rs.getString("value")); } } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error"); } } // There should be only one row. rs.next() isn't called yet. private boolean setUserProperties(Properties properties, ResultSet rs) throws DatabaseException{ try { if (rs.next() == false) { return false; // throw new DatabaseException("User doesn't exist."); } // Some of the values can be null. put() doesn't allow nulls. String tmp = null; int tmpInt = 0; properties.put("id", ""+rs.getInt("id")); properties.put("name", rs.getString("loginname")); properties.put("password", rs.getString("password")); properties.put("figure", rs.getString("figure")); properties.put("sex", ((tmp = rs.getString("sex")) != null)? (Object) tmp:(new NullObject())); properties.put("realname.first", ((tmp = rs.getString("firstname")) != null)? (Object) tmp:new NullObject()); properties.put("realname.last", ((tmp = rs.getString("lastname")) != null)? (Object) tmp:new NullObject()); properties.put("phonenumber", ((tmp = rs.getString("phone")) != null)? (Object) tmp:new NullObject()); properties.put("email", ((tmp = rs.getString("email")) != null)? (Object) tmp:new NullObject()); properties.put("address", ((tmp = rs.getString("address")) != null)? (Object) tmp:new NullObject()); properties.put("post_location", ((tmp = rs.getString("city")) != null)? (Object) tmp:new NullObject()); properties.put("zipcode", ((tmp = rs.getString("zipcode")) != null)? (Object) tmp:new NullObject()); // properties.put("age", ((tmp = rs.getString("age")) != null)? (Object) tmp:new NullObject()); properties.put("age", ((rs.getObject("age")) != null)? (Object) (""+rs.getInt("age")):new NullObject()); System.out.println("SQLFUSEUser: age from database="+properties.get("age")); properties.put("custom", ((tmp = rs.getString("extra")) != null)? (Object) tmp:new NullObject()); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error"); } return true; } // Inserts user_params into DB. // Using MySQL's REPLACE syntax. private void insertUserParams(Connection con, String userId) throws DatabaseException { Statement stmt = null; Enumeration tmpKeys = properties.keys(); StringBuffer tmpStr = new StringBuffer(); String paramKey = null; String paramValue = null; String cmd = null; try { for (; tmpKeys.hasMoreElements() ;) { if (!fuseUserKeys.contains(paramKey = (String) tmpKeys.nextElement())) { // This key is part of user_params. paramValue = getPropertyString(paramKey); cmd = "REPLACE INTO user_params (user_id, value, par_type_id) SELECT "; cmd += userId+", '"+paramValue+"', id FROM par_type WHERE name = '" +paramKey+"'"; // This syntax should be ok. // REPLACE INTO user_params (user_id, value, par_type_id) SELECT 1, 'true', id FROM par_type WHERE name = 'access_count'; // Insert one row into user_params stmt = con.createStatement(); stmt.executeUpdate(cmd); stmt.close(); } } } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in insertUserParams()"); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in insert(): finally"); } } } public void insert(Connection con) throws DatabaseException { PreparedStatement pstmt = null; Statement stmt = null; String cmd = null; ResultSet rs = null; if (this.getName() == null) { throw new DatabaseException("User does not have a name!"); } try { stmt = con.createStatement(); cmd = new String("SELECT * FROM fuse_user WHERE loginname='"+this.getName()+"'"); rs = stmt.executeQuery(cmd); if (rs.next() != false) { stmt.close(); throw new UserExistsException(); } stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in insert(): select"); } try { // id is AUTO_INCREMENT so NULL creates a new row cmd = new String("INSERT INTO fuse_user (id, loginname, password, figure, sex, firstname, lastname, phone, email, address, city, zipcode, age, extra, created, updated) VALUES (NULL, ?,?,?,?,?,?,?,?,?,?,?,?,?, NOW(), NOW())"); pstmt = con.prepareStatement(cmd); // properties <-> fuse_user has few different names (custom <-> extra etc.) // Should we change the DB? Maybe we could loop the properties if names match. setPreparedValues(pstmt); pstmt.executeUpdate(); pstmt.close(); // Because id should be correct, let's select it. Done only when new // user registers, so overhead isn't too bad. properties.put("id", ""+getMyId(con)); // Have to insert values into user_params table. insertUserParams(con, (String) properties.get("id")); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in insert(): insert"); } finally { try { if (pstmt != null) pstmt.close(); if (stmt != null) stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in insert(): finally"); } } } /* Can we trust that user exists already? Should we create new if it doesn't? */ public void update(Connection con) throws DatabaseException { PreparedStatement pstmt = null; String cmd = null; long myId = getMyId(con); if (myId == -1) { // Used doesn't exist. What should we do? throw new DatabaseException("User doesn't exist"); } try { // TIMESTAMP kantaan: properties.put("updated", sdf.format(new Date())); // Some DBMSs don't allow (?,?,?...) syntax cmd = new String("UPDATE fuse_user SET loginname=?, password=?, figure=?, sex=?, firstname=?, lastname=?, phone=?, email=?, address=?, city=?, zipcode=?, age=?, extra=? WHERE id="+myId); pstmt = con.prepareStatement(cmd); setPreparedValues(pstmt); pstmt.executeUpdate(); pstmt.close(); // Have to insert values into user_params table. (also in update) insertUserParams(con, ""+myId); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in update()"); } finally { try { if (pstmt != null) pstmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in update(): finally"); } } } public void delete(Connection con) throws DatabaseException { Statement stmt = null; String cmd = null; try { cmd = new String("DELETE FROM fuse_user WHERE loginname='"+this.getName()+"'"); stmt.executeUpdate(cmd); stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in delete()"); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in delete(): finally"); } } } /* Called by SQLFindUserOfName - querybean */ public boolean loadById(Connection con, long searchId) throws DatabaseException { Statement stmt = null; ResultSet rs = null; String cmd = null; try { properties = new Properties(); stmt = con.createStatement(); cmd = new String("SELECT * FROM fuse_user WHERE id='"+searchId+"'"); rs = stmt.executeQuery(cmd); if (!setUserProperties(properties, rs)) { // User doesn't exist. return false; } stmt.close(); // Read also user_params. stmt = con.createStatement(); cmd = new String("SELECT * FROM user_params, par_type WHERE user_id="+properties.get("id")+" AND user_params.par_type_id = par_type.id"); rs = stmt.executeQuery(cmd); setUserParams(properties, rs); stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in readFromDB()"); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in readFromDB(): finally"); } } return true; } /* Called by SQLFindUserOfName - querybean */ boolean readFromDB(Connection con, String name) throws DatabaseException { Statement stmt = null; ResultSet rs = null; String cmd = null; try { properties = new Properties(); stmt = con.createStatement(); cmd = new String("SELECT * FROM fuse_user WHERE loginname='"+name+"'"); rs = stmt.executeQuery(cmd); if (!setUserProperties(properties, rs)) { // User doesn't exist. return false; } stmt.close(); // Read also user_params. stmt = con.createStatement(); cmd = new String("SELECT * FROM user_params, par_type WHERE user_id="+properties.get("id")+" AND user_params.par_type_id = par_type.id"); rs = stmt.executeQuery(cmd); setUserParams(properties, rs); stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in readFromDB()"); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in readFromDB(): finally"); } } return true; } boolean readFromDB(Connection con, long searchId) throws DatabaseException { Statement stmt = null; ResultSet rs = null; String cmd = null; try { properties = new Properties(); stmt = con.createStatement(); cmd = new String("SELECT * FROM fuse_user WHERE id='"+searchId+"'"); rs = stmt.executeQuery(cmd); if (!setUserProperties(properties, rs)) { // User doesn't exist. return false; } stmt.close(); // Read also user_params. stmt = con.createStatement(); cmd = new String("SELECT * FROM user_params, par_type WHERE user_id="+properties.get("id")+" AND user_params.par_type_id = par_type.id"); rs = stmt.executeQuery(cmd); setUserParams(properties, rs); stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in readFromDB()"); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException e) { Log.error(e); throw new DatabaseException("SQL Error in readFromDB(): finally"); } } return true; } /* Returns an unique id for the user*/ public long getId() { return Long.parseLong((String) properties.get("id")); } /* User name - is unique */ public void setName(String name) { properties.put("name", name); } public String getName() { // return (String) properties.get("name"); return getPropertyString("name"); } /* Password */ public void setPassword(String s) { properties.put("password", s); } public String getPassword() { // return (String) properties.get("password"); return getPropertyString("password"); } /* Figure: format is "bottomPart,middlePart,topPart" */ public void setFigure(String s) { properties.put("figure", s); } public String getFigure() { // return (String) properties.get("figure"); return getPropertyString("figure"); } /* Sex: "Male" or "Female" */ public String getSex() { // return (String) properties.get("sex"); return getPropertyString("sex"); } /* If true, then set the user to be male */ public void setSex(boolean male) { String s = male ? "Male" : "Female"; properties.put("sex", s); } public boolean isMale() { return getSex().equals("Male"); } public boolean isFemale() { return getSex().equals("Female"); } /* Real name */ public void setRealName(String firstName, String lastName) { setRealFirstName(firstName); setRealLastName(lastName); } public void setRealFirstName(String s) { properties.put("realname.first", s); } public void setRealLastName(String s) { properties.put("realname.last", s); } public String getRealLastName() { // return (String) properties.get("realname.last"); return getPropertyString("realname.last"); } public String getRealFirstName() { // return (String) properties.get("realname.first"); return getPropertyString("realname.first"); } /* Phone number */ public void setPhoneNumber(String s) { properties.put("phonenumber", s); } public String getPhoneNumber() { // return (String) properties.get("phonenumber"); return getPropertyString("phonenumber"); } /* Address */ public void setAddress(String s) { properties.put("address",s); } public String getAddress() { // return (String) properties.get("address"); return getPropertyString("address"); } public void setZipCode(String s) { properties.put("zipcode", s); } public String getZipCode() { // return (String) properties.get("zipcode"); return getPropertyString("zipcode"); } /* Toimipaikka.. */ public void setPostLocation(String s) { properties.put("post_location", s); } public String getPostLocation() { // return (String) properties.get("post_location"); return getPropertyString("post_location"); } /* E-mail */ public void setEmail(String s) { properties.put("email",s); } public String getEmail() { // return (String) properties.get("email"); return getPropertyString("email"); } /* Age */ public void setAge(int age) { properties.put("age", "" + age); } public int getAge() { // return Integer.parseInt((String) properties.get("age")); int tmpInt = 0; String tmp = getPropertyString("age"); if (tmp != null) { // System.out.println("SQLFUSEUser::getAge():tmp="+tmp); try { tmpInt = Integer.parseInt(tmp); } catch (Exception e) { tmpInt = 0; } return tmpInt; } return 0; } /* Custom text (motto) */ public void setCustomData(String s) { properties.put("custom", s); } public String getCustomData() { // return (String) properties.get("custom"); return getPropertyString("custom"); } /* Free properties / attributes (like "allow_direct_advertisement" or "last_access_time")*/ public void setProperty(String type, String value) { properties.put(type, value); } public String getProperty(String type) { // return (String) properties.get(type); return getPropertyString(type); } public void removeProperty(String type) { properties.remove(type); } public String toFusePString() { StringBuffer sb = new StringBuffer(1024); sb.append(super.toFusePString()); Enumeration tmpKeys = properties.keys(); String paramKey = null; String paramValue = null; for (; tmpKeys.hasMoreElements() ;) { if (!fuseUserKeys.contains(paramKey = (String) tmpKeys.nextElement())) { // This key is part of user_params. paramValue = getPropertyString(paramKey); sb.append("\r" + paramKey + "=" + paramValue); } } return sb.toString(); } /* Utility / query methods */ public FUSEUser getUserWithName(String name) throws DatabaseException { try { SQLFindUserOfName bean = new SQLFindUserOfName(name); Vector v = com.fuse.FUSEEnvironment.getDatabaseProxy().getDatabase().query(bean); if (v.size() == 1) return (FUSEUser) v.elementAt(0); else return null; } catch (java.rmi.RemoteException re) { Log.error(re); return null; } } public FUSEUser getUserById(long searchId) throws DatabaseException { try { SQLFindUserOfId bean = new SQLFindUserOfId(searchId); Vector v = com.fuse.FUSEEnvironment.getDatabaseProxy().getDatabase().query(bean); if (v.size() == 1) return (FUSEUser) v.elementAt(0); else return null; } catch (java.rmi.RemoteException re) { Log.error(re); return null; } } } /** * SQLFindUserOfName is an ObjectQueryBean which * finds an user of given name. */ class SQLFindUserOfId implements SQLObjectQueryBean { private long searchId; SQLFindUserOfId(long searchId) { this.searchId = searchId; } /* Interface: SQLObjectQueryBean */ public Vector execute(Connection con) throws DatabaseException { Vector results = new Vector(); SQLFUSEUser user = new SQLFUSEUser(); if (!user.readFromDB(con, searchId)) { return new Vector(0); } results.addElement(user); return results; } public String toString() { return "sql impl. of FindUserOfId; id=" + searchId; } }