X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=source%2Fmir%2Fstorage%2FDatabase.java;h=572f674c5b35c833d89456288e2c8ef9901fb9ef;hb=bcd0c64b09ce0e8f3e6260f76f3a253863c74e60;hp=4996931994fee08ce496fe5ccc28646f0b475d19;hpb=5bf2a1fa11c1529a731ab6b4521b8254c1fa100f;p=mir.git diff --git a/source/mir/storage/Database.java b/source/mir/storage/Database.java index 49969319..572f674c 100755 --- a/source/mir/storage/Database.java +++ b/source/mir/storage/Database.java @@ -1,15 +1,48 @@ /* - * put your module comment here + * Copyright (C) 2001, 2002 The Mir-coders group + * + * This file is part of Mir. + * + * Mir 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. + * + * Mir 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 Mir; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + * + * In addition, as a special exception, The Mir-coders gives permission to link + * the code of this program with the com.oreilly.servlet library, any library + * licensed under the Apache Software License, The Sun (tm) Java Advanced + * Imaging library (JAI), The Sun JIMI library (or with modified versions of + * the above that use the same license as the above), and distribute linked + * combinations including the two. You must obey the GNU General Public + * License in all respects for all of the code used other than the above + * mentioned libraries. If you modify this file, you may extend this exception + * to your version of the file, but you are not obligated to do so. If you do + * not wish to do so, delete this exception statement from your version. */ + package mir.storage; import java.sql.*; import java.lang.*; import java.io.*; import java.util.*; +import java.text.SimpleDateFormat; +import java.text.ParseException; import freemarker.template.*; -import com.javaexchange.dbConnectionBroker.*; +import com.codestudio.sql.*; +import com.codestudio.util.*; + import mir.storage.StorageObject; +import mir.storage.store.*; import mir.entity.*; import mir.misc.*; @@ -22,32 +55,72 @@ import mir.misc.*; * Treiber, Host, User und Passwort, ueber den der Zugriff auf die * Datenbank erfolgt. * - * @author RK - * @version 16.7.1999 + * @version $Revision: 1.21.2.1 $ $Date: 2002/09/01 21:31:41 $ + * @author $Author: mh $ + * + * $Log: Database.java,v $ + * Revision 1.21.2.1 2002/09/01 21:31:41 mh + * Mir goes GPL + * + * Revision 1.21 2002/08/04 23:38:22 mh + * fix up the webdb_create update stuff + * + * Revision 1.20 2002/07/21 22:32:25 mh + * on insert, the "webdb_lastchange" field should get a value + * + * Revision 1.19 2002/06/29 15:44:46 mh + * make the webdb_create update be called webdb_create_update. it breaks things otherwise. a fixme case I know.. + * + * Revision 1.18 2002/06/28 20:42:13 mh + * added necessary bits in templates and Database.java to make webdb_create modifiable. make the conversion from sql/Timestamp to String more robust + * + * */ public class Database implements StorageObject { - protected DbConnectionBroker myBroker; protected String theTable; protected String theCoreTable=null; protected String thePKeyName="id"; - protected int thePKeyType; + protected int thePKeyType, thePKeyIndex; protected boolean evaluatedMetaData=false; - protected ArrayList metadataFields,metadataLabels,metadataNotNullFields; + protected ArrayList metadataFields,metadataLabels, + metadataNotNullFields; protected int[] metadataTypes; protected Class theEntityClass; protected StorageObject myselfDatabase; - protected HashMap cache; protected SimpleList popupCache=null; - protected boolean hasPopupCache = false; + protected boolean hasPopupCache = false; protected SimpleHash hashCache=null; protected boolean hasTimestamp=true; - private String database_driver; - private String database_url; + private String database_driver, database_url; private int defaultLimit; - private DatabaseAdaptor theAdaptor; + protected DatabaseAdaptor theAdaptor; protected Logfile theLog; - protected Connection con; + private static Class GENERIC_ENTITY_CLASS=null, + STORABLE_OBJECT_ENTITY_CLASS=null; + private static SimpleHash POPUP_EMTYLINE=new SimpleHash(); + protected static final ObjectStore o_store=ObjectStore.getInstance(); + private SimpleDateFormat _dateFormatterOut = + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + private SimpleDateFormat _dateFormatterIn = + new SimpleDateFormat("yyyy-MM-dd HH:mm"); + private Calendar _cal = new GregorianCalendar(); + + private static final int _millisPerHour = 60 * 60 * 1000; + private static final int _millisPerMinute = 60 * 1000; + + static { + // always same object saves a little space + POPUP_EMTYLINE.put("key", ""); POPUP_EMTYLINE.put("value", "--"); + try { + GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity"); + STORABLE_OBJECT_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity"); + } + catch (Exception e) { + System.err.println("FATAL: Database.java could not initialize" + e.toString()); + } + } + /** * Kontruktor bekommt den Filenamen des Konfigurationsfiles übergeben. @@ -59,25 +132,32 @@ public class Database implements StorageObject { * * @param String confFilename Dateiname der Konfigurationsdatei */ - public Database() { - theLog = Logfile.getInstance(Configuration.getProperty("Home") + Configuration.getProperty("Database.Logfile")); - String database_username=Configuration.getProperty("Database.Username"); - String database_password=Configuration.getProperty("Database.Password"); - String database_host=Configuration.getProperty("Database.Host"); - String theAdaptorName=Configuration.getProperty("Database.Adaptor"); + public Database() throws StorageObjectException { + theLog = Logfile.getInstance(MirConfig.getProp("Home")+ + MirConfig.getProp("Database.Logfile")); + String theAdaptorName=MirConfig.getProp("Database.Adaptor"); + defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit")); try { - theEntityClass = Class.forName("mir.entity.GenericEntity"); + theEntityClass = GENERIC_ENTITY_CLASS; theAdaptor = (DatabaseAdaptor)Class.forName(theAdaptorName).newInstance(); - defaultLimit = Integer.parseInt(Configuration.getProperty("Database.Limit")); - database_driver=theAdaptor.getDriver(); - database_url=theAdaptor.getURL(database_username,database_password,database_host); - theLog.printDebugInfo("adding Broker with: " +database_driver+":"+database_url ); - Configuration.addBroker(database_driver,database_url); - myBroker=Configuration.getBroker(); - } - catch (Exception e){ - theLog.printError("Bei Konstruktion von Database() with " + theAdaptorName + " -- " +e.toString()); + } catch (Exception e){ + theLog.printError("Error in Database() constructor with "+ + theAdaptorName + " -- " +e.toString()); + throw new StorageObjectException("Error in Database() constructor with " + +e.toString()); } + /*String database_username=MirConfig.getProp("Database.Username"); + String database_password=MirConfig.getProp("Database.Password"); + String database_host=MirConfig.getProp("Database.Host"); + try { + database_driver=theAdaptor.getDriver(); + database_url=theAdaptor.getURL(database_username,database_password, + database_host); + theLog.printDebugInfo("adding Broker with: " +database_driver+":"+ + database_url ); + MirConfig.addBroker(database_driver,database_url); + //myBroker=MirConfig.getBroker(); + }*/ } /** @@ -121,7 +201,8 @@ public class Database implements StorageObject { /* * Dient dazu vererbte Tabellen bei objectrelationalen DBMS - * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet wird. + * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet + * wird. * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst * the Table */ @@ -166,7 +247,7 @@ public class Database implements StorageObject { /* - * Uebersetzt die Datenbankwerte in einen String. + * Gets value out of ResultSet according to type and converts to String * @param inValue Wert aus ResultSet. * @param aType Datenbanktyp. * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich @@ -186,6 +267,8 @@ public class Database implements StorageObject { outValue = new Integer(out).toString(); break; case java.sql.Types.NUMERIC: + /** @todo Numeric can be float or double depending upon + * metadata.getScale() / especially with oracle */ long outl = rs.getLong(valueIndex); if (!rs.wasNull()) outValue = new Long(outl).toString(); @@ -216,19 +299,30 @@ public class Database implements StorageObject { break; case java.sql.Types.CHAR:case java.sql.Types.VARCHAR:case java.sql.Types.LONGVARCHAR: outValue = rs.getString(valueIndex); - if (outValue != null) - outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); + //if (outValue != null) + //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); break; case java.sql.Types.LONGVARBINARY: outValue = rs.getString(valueIndex); - if (outValue != null) - outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); + //if (outValue != null) + //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); break; case java.sql.Types.TIMESTAMP: - Timestamp timestamp = (rs.getTimestamp(valueIndex)); - if (!rs.wasNull()) { - outValue = timestamp.toString(); - } + // it's important to use Timestamp here as getting it + // as a string is undefined and is only there for debugging + // according to the API. we can make it a string through formatting. + // -mh + Timestamp timestamp = (rs.getTimestamp(valueIndex)); + if(!rs.wasNull()) { + java.util.Date date = new java.util.Date(timestamp.getTime()); + outValue = _dateFormatterOut.format(date); + _cal.setTime(date); + int offset = _cal.get(Calendar.ZONE_OFFSET)+ + _cal.get(Calendar.DST_OFFSET); + String tzOffset = StringUtil.zeroPaddingNumber( + offset/_millisPerHour,2,2); + outValue = outValue+"+"+tzOffset; + } break; default: outValue = ""; @@ -248,18 +342,27 @@ public class Database implements StorageObject { * @param id Primaerschluessel des Datensatzes. * @return liefert EntityObject des gefundenen Datensatzes oder null. */ - public Entity selectById(String id) - throws StorageObjectException { - + public Entity selectById(String id) throws StorageObjectException + { if (id==null||id.equals("")) - throw new StorageObjectException("id war null"); - if (cache != null && cache.containsKey(id)) - return (Entity)cache.get(id); // wenn cache gesetzt, evtl. kein roundtrip zur Datenbank + throw new StorageObjectException("id war null"); + + // ask object store for object + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + String uniqueId = id; + if ( theEntityClass.equals(StorableObjectEntity.class) ) + uniqueId+="@"+theTable; + StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId); + theLog.printDebugInfo("CACHE: (dbg) looking for sid " + search_sid.toString()); + Entity hit = (Entity)o_store.use(search_sid); + if ( hit!=null ) return hit; + } Statement stmt=null;Connection con=getPooledCon(); Entity returnEntity=null; try { ResultSet rs; + /** @todo better prepared statement */ String selectSql = "select * from " + theTable + " where " + thePKeyName + "=" + id; stmt = con.createStatement(); rs = executeSql(stmt, selectSql); @@ -269,30 +372,33 @@ public class Database implements StorageObject { returnEntity = makeEntityFromResultSet(rs); else theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" + theTable); rs.close(); - } else { - theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable); - } - } catch (SQLException sqe){ - throwSQLException(sqe,"selectById"); return null; - } catch (NumberFormatException e) { - theLog.printError("ID ist keine Zahl: " + id); - } finally { - freeConnection(con,stmt); - } + } + else { + theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable); + } + } + catch (SQLException sqe){ + throwSQLException(sqe,"selectById"); return null; + } + catch (NumberFormatException e) { + theLog.printError("ID ist keine Zahl: " + id); + } + finally { freeConnection(con,stmt); } + /** @todo OS: Entity should be saved in ostore */ return returnEntity; } + /** * select-Operator um Datensaetze zu bekommen, die key = value erfuellen. * @param key Datenbankfeld der Bedingung. * @param value Wert die der key anehmen muss. * @return EntityList mit den gematchten Entities */ - public EntityList selectByFieldValue(String aField, String aValue) - throws StorageObjectException { - + throws StorageObjectException + { return selectByFieldValue(aField, aValue, 0); } @@ -303,10 +409,9 @@ public class Database implements StorageObject { * @param offset Gibt an ab welchem Datensatz angezeigt werden soll. * @return EntityList mit den gematchten Entities */ - public EntityList selectByFieldValue(String aField, String aValue, int offset) - throws StorageObjectException { - + throws StorageObjectException + { return selectByWhereClause(aField + "=" + aValue, offset); } @@ -320,8 +425,8 @@ public class Database implements StorageObject { * @exception StorageObjectException */ public EntityList selectByWhereClause(String where) - throws StorageObjectException { - + throws StorageObjectException + { return selectByWhereClause(where, 0); } @@ -336,11 +441,12 @@ public class Database implements StorageObject { * @exception StorageObjectException */ public EntityList selectByWhereClause(String whereClause, int offset) - throws StorageObjectException { - + throws StorageObjectException + { return selectByWhereClause(whereClause, null, offset); } + /** * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück. * Also offset wird der erste Datensatz genommen. @@ -353,10 +459,11 @@ public class Database implements StorageObject { */ public EntityList selectByWhereClause(String where, String order) - throws StorageObjectException { - + throws StorageObjectException { return selectByWhereClause(where, order, 0); } + + /** * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück. * Als maximale Anzahl wird das Limit auf der Konfiguration genommen. @@ -369,8 +476,7 @@ public class Database implements StorageObject { */ public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) - throws StorageObjectException { - + throws StorageObjectException { return selectByWhereClause(whereClause, orderBy, offset, defaultLimit); } @@ -386,18 +492,33 @@ public class Database implements StorageObject { */ public EntityList selectByWhereClause(String wc, String ob, int offset, int limit) - throws StorageObjectException { + throws StorageObjectException + { + + // check o_store for entitylist + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreIdentifier search_sid = + new StoreIdentifier( theEntityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST, + StoreUtil.getEntityListUniqueIdentifierFor(theTable,wc,ob,offset,limit) ); + EntityList hit = (EntityList)o_store.use(search_sid); + if ( hit!=null ) { + theLog.printDebugInfo("CACHE (hit): " + search_sid.toString()); + return hit; + } + } // local EntityList theReturnList=null; - Connection con=null; - Statement stmt=null; + Connection con=null; Statement stmt=null; ResultSet rs; - int offsetCount = 0; - int count=0; - + int offsetCount = 0, count=0; // build sql-statement + + /** @todo count sql string should only be assembled if we really count + * see below at the end of method //rk */ + if (wc != null && wc.length() == 0) { wc = null; } @@ -422,28 +543,17 @@ public class Database implements StorageObject { } } - // execute sql + // execute sql try { con = getPooledCon(); stmt = con.createStatement(); - // counting rows - if (theAdaptor.hasLimit()) { - rs = executeSql(stmt, countSql.toString()); - if (rs != null) { - if (rs.next()) - count = rs.getInt(1); - rs.close(); - } - else - theLog.printError("Mh. Konnte nicht zaehlen: " + countSql); - } - // hier select + + // selecting... rs = executeSql(stmt, selectSql.toString()); if (rs != null) { + if (!evaluatedMetaData) evalMetaData(rs.getMetaData()); + theReturnList = new EntityList(); - if (evaluatedMetaData == false) { - evalMetaData(rs.getMetaData()); - } Entity theResultEntity; while (rs.next()) { theResultEntity = makeEntityFromResultSet(rs); @@ -452,37 +562,59 @@ public class Database implements StorageObject { } rs.close(); } - // making entitylist - if (!(theAdaptor.hasLimit())) - count = offsetCount; + + // making entitylist infos + if (!(theAdaptor.hasLimit())) count = offsetCount; + if (theReturnList != null) { + // now we decide if we have to know an overall count... + count=offsetCount; + if (limit > -1 && offset > -1) { + if (offsetCount==limit) { + /** @todo counting should be deffered to entitylist + * getSize() should be used */ + rs = executeSql(stmt, countSql.toString()); + if (rs != null) { + if ( rs.next() ) count = rs.getInt(1); + rs.close(); + } + else theLog.printError("Could not count: " + countSql); + } + } theReturnList.setCount(count); theReturnList.setOffset(offset); theReturnList.setWhere(wc); theReturnList.setOrder(ob); - if (offset >= limit) { + theReturnList.setStorage(this); + theReturnList.setLimit(limit); + if ( offset >= limit ) theReturnList.setPrevBatch(offset - limit); - } - if (offset + offsetCount < count) { + if ( offset+offsetCount < count ) theReturnList.setNextBatch(offset + limit); - } + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreIdentifier sid=theReturnList.getStoreIdentifier(); + theLog.printDebugInfo("CACHE (add): " + sid.toString()); + o_store.add(sid); + } } - } catch (SQLException sqe) { - throwSQLException(sqe, "selectByWhereClause"); - } finally { - freeConnection(con, stmt); } + catch (SQLException sqe) { throwSQLException(sqe, "selectByWhereClause"); } + finally { freeConnection(con, stmt); } + return theReturnList; } + /** * Bastelt aus einer Zeile der Datenbank ein EntityObjekt. * * @param rs Das ResultSetObjekt. * @return Entity Die Entity. */ - - public Entity makeEntityFromResultSet (ResultSet rs) throws StorageObjectException { + private Entity makeEntityFromResultSet (ResultSet rs) + throws StorageObjectException + { + /** @todo OS: get Pkey from ResultSet and consult ObjectStore */ HashMap theResultHash = new HashMap(); String theResult = null; int theType; @@ -493,9 +625,8 @@ public class Database implements StorageObject { // alle durchlaufen bis nix mehr da theType = metadataTypes[i]; if (theType == java.sql.Types.LONGVARBINARY) { - InputStream us = rs.getAsciiStream(i + 1); - if (us != null) { - InputStreamReader is = new InputStreamReader(us); + InputStreamReader is = (InputStreamReader)rs.getCharacterStream(i + 1); + if (is != null) { char[] data = new char[32768]; StringBuffer theResultString = new StringBuffer(); int len; @@ -516,26 +647,18 @@ public class Database implements StorageObject { theResultHash.put(metadataFields.get(i), theResult); } } - if (cache != null && theResultHash.containsKey(thePKeyName) && cache.containsKey((String)theResultHash.get(thePKeyName))) { - //theLog.printDebugInfo("CACHE: (out) "+ theResultHash.get(thePKeyName)+ " :"+theTable); - returnEntity = (Entity)cache.get((String)theResultHash.get(thePKeyName)); - } - else { - if (theEntityClass != null) { - returnEntity = (Entity)theEntityClass.newInstance(); - returnEntity.setValues(theResultHash); - returnEntity.setStorage(myselfDatabase); - if (cache != null) { - //theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable); - cache.put(returnEntity.getId(), returnEntity); - } - } - else { - throwStorageObjectException("Interner Fehler theEntityClass nicht gesetzt!"); - } - } - } // try - catch (IllegalAccessException e) { + if (theEntityClass != null) { + returnEntity = (Entity)theEntityClass.newInstance(); + returnEntity.setValues(theResultHash); + returnEntity.setStorage(myselfDatabase); + if ( returnEntity instanceof StorableObject ) { + theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable); + o_store.add(((StorableObject)returnEntity).getStoreIdentifier()); + } + } else { + throwStorageObjectException("Internal Error: theEntityClass not set!"); + } + } catch (IllegalAccessException e) { throwStorageObjectException("Kein Zugriff! -- " + e.toString()); } catch (IOException e) { throwStorageObjectException("IOException! -- " + e.toString()); @@ -556,13 +679,21 @@ public class Database implements StorageObject { * @return der Wert des Primary-keys der eingefügten Entity */ public String insert (Entity theEntity) throws StorageObjectException { - String returnId = "0"; - Connection con = null; - PreparedStatement pstmt = null; //cache invalidatePopupCache(); - try { - HashMap theEntityValues = theEntity.getValues(); + + // invalidating all EntityLists corresponding with theEntityClass + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreContainerType stoc_type = + StoreContainerType.valueOf( theEntityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST); + o_store.invalidate(stoc_type); + } + + String returnId = null; + Connection con = null; PreparedStatement pstmt = null; + + try { ArrayList streamedInput = theEntity.streamedInput(); StringBuffer f = new StringBuffer(); StringBuffer v = new StringBuffer(); @@ -574,7 +705,8 @@ public class Database implements StorageObject { if (!aField.equals(thePKeyName)) { aValue = null; // sonderfaelle - if (aField.equals("webdb_create")) { + if (aField.equals("webdb_create") || + aField.equals("webdb_lastchange")) { aValue = "NOW()"; } else { @@ -582,8 +714,8 @@ public class Database implements StorageObject { aValue = "?"; } else { - if (theEntityValues.containsKey(aField)) { - aValue = "'" + StringUtil.quote((String)theEntityValues.get(aField)) + if (theEntity.hasValueForField(aField)) { + aValue = "'" + StringUtil.quote((String)theEntity.getValue(aField)) + "'"; } } @@ -611,11 +743,15 @@ public class Database implements StorageObject { pstmt = con.prepareStatement(sql); if (streamedInput != null) { for (int i = 0; i < streamedInput.size(); i++) { - String inputString = (String)theEntityValues.get(streamedInput.get(i)); + String inputString = (String)theEntity.getValue((String)streamedInput.get(i)); pstmt.setBytes(i + 1, inputString.getBytes()); } } - pstmt.execute(); + int ret = pstmt.executeUpdate(); + if(ret == 0){ + //insert failed + return null; + } pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL((Database)myselfDatabase)); ResultSet rs = pstmt.executeQuery(); rs.next(); @@ -631,6 +767,7 @@ public class Database implements StorageObject { } freeConnection(con, pstmt); } + /** @todo store entity in o_store */ return returnId; } @@ -640,11 +777,28 @@ public class Database implements StorageObject { * * @param theEntity */ - public void update (Entity theEntity) throws StorageObjectException { - Connection con = null; - PreparedStatement pstmt = null; + public void update (Entity theEntity) throws StorageObjectException + { + Connection con = null; PreparedStatement pstmt = null; + /** @todo this is stupid: why do we prepare statement, when we + * throw it away afterwards. should be regular statement + * update/insert could better be one routine called save() + * that chooses to either insert or update depending if we + * have a primary key in the entity. i don't know if we + * still need the streamed input fields. // rk */ + + /** @todo extension: check if Entity did change, otherwise we don't need + * the roundtrip to the database */ + + /** invalidating corresponding entitylists in o_store*/ + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreContainerType stoc_type = + StoreContainerType.valueOf( theEntityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST); + o_store.invalidate(stoc_type); + } + ArrayList streamedInput = theEntity.streamedInput(); - HashMap theEntityValues = theEntity.getValues(); String id = theEntity.getId(); String aField; StringBuffer fv = new StringBuffer(); @@ -657,14 +811,14 @@ public class Database implements StorageObject { // only normal cases if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") || aField.equals("webdb_lastchange") || (streamedInput != null && streamedInput.contains(aField)))) { - if (theEntityValues.containsKey(aField)) { + if (theEntity.hasValueForField(aField)) { if (firstField == false) { fv.append(", "); } else { firstField = false; } - fv.append(aField).append("='").append(StringUtil.quote((String)theEntityValues.get(aField))).append("'"); + fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'"); } } } @@ -673,6 +827,27 @@ public class Database implements StorageObject { if (metadataFields.contains("webdb_lastchange")) { sql.append(",webdb_lastchange=NOW()"); } + // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm + // format so anything extra will be ignored. -mh + if (metadataFields.contains("webdb_create") && + theEntity.hasValueForField("webdb_create")) { + // minimum of 10 (yyyy-mm-dd)... + if (theEntity.getValue("webdb_create").length() >= 10) { + String dateString = theEntity.getValue("webdb_create"); + // if only 10, then add 00:00 so it doesn't throw a ParseException + if (dateString.length() == 10) + dateString=dateString+" 00:00"; + + // TimeStamp stuff + try { + java.util.Date d = _dateFormatterIn.parse(dateString); + Timestamp tStamp = new Timestamp(d.getTime()); + sql.append(",webdb_create='"+tStamp.toString()+"'"); + } catch (ParseException e) { + throw new StorageObjectException(e.toString()); + } + } + } if (streamedInput != null) { for (int i = 0; i < streamedInput.size(); i++) { sql.append(",").append(streamedInput.get(i)).append("=?"); @@ -687,7 +862,7 @@ public class Database implements StorageObject { pstmt = con.prepareStatement(sql.toString()); if (streamedInput != null) { for (int i = 0; i < streamedInput.size(); i++) { - String inputString = (String)theEntityValues.get(streamedInput.get(i)); + String inputString = theEntity.getValue((String)streamedInput.get(i)); pstmt.setBytes(i + 1, inputString.getBytes()); } } @@ -710,28 +885,31 @@ public class Database implements StorageObject { * @return boolean liefert true zurueck, wenn loeschen erfolgreich war. */ public boolean delete (String id) throws StorageObjectException { - Statement stmt = null; - Connection con = null; - String sql; - int res = 0; - // loeschen des caches + invalidatePopupCache(); - sql = "delete from " + theTable + " where " + thePKeyName + "='" + id + - "'"; + // ostore send notification + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + String uniqueId = id; + if ( theEntityClass.equals(StorableObjectEntity.class) ) + uniqueId+="@"+theTable; + theLog.printInfo("CACHE: (del) " + id); + StoreIdentifier search_sid = + new StoreIdentifier(theEntityClass, StoreContainerType.STOC_TYPE_ENTITY, uniqueId); + o_store.invalidate(search_sid); + } + + /** @todo could be prepared Statement */ + Statement stmt = null; Connection con = null; + int res = 0; + String sql="delete from "+theTable+" where "+thePKeyName+"='"+id+"'"; theLog.printInfo("DELETE " + sql); try { - con = getPooledCon(); - stmt = con.createStatement(); + con = getPooledCon(); stmt = con.createStatement(); res = stmt.executeUpdate(sql); - } catch (SQLException sqe) { - throwSQLException(sqe, "delete"); - } finally { - freeConnection(con, stmt); - } - if (cache != null) { - theLog.printInfo("CACHE: deleted " + id); - cache.remove(id); } + catch (SQLException sqe) { throwSQLException(sqe, "delete"); } + finally { freeConnection(con, stmt); } + return (res > 0) ? true : false; } @@ -748,7 +926,7 @@ public class Database implements StorageObject { * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll. * @return null */ - public SimpleList getPopupData () { + public SimpleList getPopupData () throws StorageObjectException { return null; } @@ -758,7 +936,8 @@ public class Database implements StorageObject { * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt. * @return SimpleList Gibt freemarker.template.SimpleList zurueck. */ - public SimpleList getPopupData (String name, boolean hasNullValue) { + public SimpleList getPopupData (String name, boolean hasNullValue) + throws StorageObjectException { return getPopupData(name, hasNullValue, null); } @@ -769,8 +948,8 @@ public class Database implements StorageObject { * @param where Schraenkt die Selektion der Datensaetze ein. * @return SimpleList Gibt freemarker.template.SimpleList zurueck. */ - public SimpleList getPopupData (String name, boolean hasNullValue, String where) { - return getPopupData(name, hasNullValue, where, null); + public SimpleList getPopupData (String name, boolean hasNullValue, String where) throws StorageObjectException { + return getPopupData(name, hasNullValue, where, null); } /** @@ -781,8 +960,7 @@ public class Database implements StorageObject { * @param order Gibt ein Feld als Sortierkriterium an. * @return SimpleList Gibt freemarker.template.SimpleList zurueck. */ - public SimpleList getPopupData (String name, boolean hasNullValue, String where, - String order) { + public SimpleList getPopupData (String name, boolean hasNullValue, String where, String order) throws StorageObjectException { // caching if (hasPopupCache && popupCache != null) return popupCache; @@ -790,7 +968,9 @@ public class Database implements StorageObject { Connection con = null; Statement stmt = null; // build sql - StringBuffer sql = new StringBuffer("select ").append(thePKeyName).append(",").append(name).append(" from ").append(theTable); + StringBuffer sql = new StringBuffer("select ").append(thePKeyName) + .append(",").append(name).append(" from ") + .append(theTable); if (where != null && !(where.length() == 0)) sql.append(" where ").append(where); sql.append(" order by "); @@ -801,19 +981,20 @@ public class Database implements StorageObject { // execute sql try { con = getPooledCon(); + } catch (Exception e) { + throw new StorageObjectException(e.toString()); + } + try { stmt = con.createStatement(); ResultSet rs = executeSql(stmt, sql.toString()); + if (rs != null) { - if (evaluatedMetaData == false) - get_meta_data(); + if (!evaluatedMetaData) get_meta_data(); simpleList = new SimpleList(); + // if popup has null-selector + if (hasNullValue) simpleList.add(POPUP_EMTYLINE); + SimpleHash popupDict; - if (hasNullValue) { - popupDict = new SimpleHash(); - popupDict.put("key", ""); - popupDict.put("value", "--"); - simpleList.add(popupDict); - } while (rs.next()) { popupDict = new SimpleHash(); popupDict.put("key", getValueAsString(rs, 1, thePKeyType)); @@ -823,13 +1004,13 @@ public class Database implements StorageObject { rs.close(); } } catch (Exception e) { - theLog.printDebugInfo(e.toString()); + theLog.printError("getPopupData: "+e.toString()); + throw new StorageObjectException(e.toString()); } finally { freeConnection(con, stmt); } - if (hasPopupCache) { - popupCache = simpleList; - } + + if (hasPopupCache) popupCache = simpleList; return simpleList; } @@ -840,6 +1021,8 @@ public class Database implements StorageObject { * @return SimpleHash mit den Tabellezeilen. */ public SimpleHash getHashData () { + /** @todo dangerous! this should have a flag to be enabled, otherwise + * very big Hashes could be returned */ if (hashCache == null) { try { hashCache = HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("", @@ -853,8 +1036,7 @@ public class Database implements StorageObject { /* invalidates the popupCache */ - private void invalidatePopupCache () { - + protected void invalidatePopupCache () { /** @todo invalidates toooo much */ popupCache = null; hashCache = null; @@ -865,14 +1047,25 @@ public class Database implements StorageObject { * @param stmt Statemnt * @param sql Sql-String * @return ResultSet - * @exception StorageObjectException, SQLException + * @exception StorageObjectException */ - public ResultSet executeSql (Statement stmt, String sql) throws StorageObjectException, - SQLException { - long startTime = (new java.util.Date()).getTime(); - ResultSet rs = stmt.executeQuery(sql); - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + public ResultSet executeSql (Statement stmt, String sql) + throws StorageObjectException, SQLException + { + long startTime = System.currentTimeMillis(); + ResultSet rs; + try { + rs = stmt.executeQuery(sql); + theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: " + sql); + } + catch (SQLException e) + { + theLog.printDebugInfo("Failed: " + (System.currentTimeMillis() + - startTime) + "ms. for: "+ sql); + throw e; + } + return rs; } @@ -883,23 +1076,23 @@ public class Database implements StorageObject { * @return Liefert ResultSet des Statements zurueck. * @exception StorageObjectException, SQLException */ - public ResultSet executeSql (PreparedStatement stmt) throws StorageObjectException, - SQLException { + public ResultSet executeSql (PreparedStatement stmt) + throws StorageObjectException, SQLException { + long startTime = (new java.util.Date()).getTime(); ResultSet rs = stmt.executeQuery(); theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms."); return rs; } - /** + /** * returns the number of rows in the table */ public int getSize(String where) throws SQLException,StorageObjectException { - long startTime = (new java.util.Date()).getTime(); + long startTime = System.currentTimeMillis(); String sql = "SELECT count(*) FROM "+ theTable + " where " + where; - //theLog.printDebugInfo("trying: "+ sql); Connection con = null; Statement stmt = null; int result = 0; @@ -916,18 +1109,29 @@ public class Database implements StorageObject { } finally { freeConnection(con,stmt); } - theLog.printInfo(theTable + " has "+ result +" rows where " + where); - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql); + //theLog.printInfo(theTable + " has "+ result +" rows where " + where); + theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: " + + sql); return result; } public int executeUpdate(Statement stmt, String sql) throws StorageObjectException, SQLException { + int rs; long startTime = (new java.util.Date()).getTime(); - //theLog.printDebugInfo("trying: "+ sql); - int rs = stmt.executeUpdate(sql); - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql); + try + { + rs = stmt.executeUpdate(sql); + theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + + sql); + } + catch (SQLException e) + { + theLog.printDebugInfo("Failed: " + (new java.util.Date().getTime() + - startTime) + "ms. for: "+ sql); + throw e; + } return rs; } @@ -942,9 +1146,13 @@ public class Database implements StorageObject { pstmt = con.prepareStatement(sql); result = pstmt.executeUpdate(); } - catch (Exception e) {theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString());} - finally { freeConnection(con,pstmt); } - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql); + catch (Exception e) { + theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString()); + throw new StorageObjectException("executeUpdate failed: "+e.toString()); + } + finally { freeConnection(con,pstmt); } + theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + + sql); return result; } @@ -953,7 +1161,9 @@ public class Database implements StorageObject { * @param md ResultSetMetaData * @exception StorageObjectException */ - private void evalMetaData (ResultSetMetaData md) throws StorageObjectException { + private void evalMetaData (ResultSetMetaData md) + throws StorageObjectException { + this.evaluatedMetaData = true; this.metadataFields = new ArrayList(); this.metadataLabels = new ArrayList(); @@ -970,7 +1180,7 @@ public class Database implements StorageObject { aType = md.getColumnType(i); metadataTypes[i - 1] = aType; if (aField.equals(thePKeyName)) { - thePKeyType = aType; + thePKeyType = aType; thePKeyIndex = i; } if (md.isNullable(i) == md.columnNullable) { metadataNotNullFields.add(aField); @@ -1003,47 +1213,31 @@ public class Database implements StorageObject { } } - /** - * Datenbankverbindung wird geschlossen - */ - public void disconnectPool () { - try { - myBroker.destroy(100); - } catch (SQLException sqe) { - ; - } - } - /** - * Liefert ein Connection-Objekt aus dem ConnectionPool. - * @return Connection Objekt. - */ - public Connection getPooledCon () throws StorageObjectException { - if (myBroker != null) { - Connection con = myBroker.getConnection(); - if (con != null) - return con; + public Connection getPooledCon() throws StorageObjectException { + /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh + try{ + Class.forName("com.codestudio.sql.PoolMan").newInstance(); + } catch (Exception e){ + throw new StorageObjectException("Could not find the PoolMan Driver" + +e.toString()); + }*/ + Connection con = null; + try{ + con = SQLManager.getInstance().requestConnection(); + } catch(SQLException e){ + theLog.printError("could not connect to the database "+e.toString()); + System.err.println("could not connect to the database "+e.toString()); + throw new StorageObjectException("Could not connect to the database"+ + e.toString()); } - throw new StorageObjectException("KEINE VERBINDUNG ZUR DATENBANK"); + return con; } - /** - * Connection und StatementObjekt werden geschlossen und an den Connectionpool - * zurückgeben - * @param con Connection zur Datenbank - * @param stmt Statement-Objekt - */ - public void freeConnection (Connection con, Statement stmt) { - try { - if (stmt != null) - stmt.close(); - } catch (SQLException e1) { - theLog.printDebugInfo(e1.toString()); - } - if (con != null) - myBroker.freeConnection(con); - else - theLog.printDebugInfo("Con was null!"); + public void freeConnection (Connection con, Statement stmt) + throws StorageObjectException { + SQLManager.getInstance().closeStatement(stmt); + SQLManager.getInstance().returnConnection(con); } /** @@ -1052,7 +1246,8 @@ public class Database implements StorageObject { * @param wo Funktonsname, in der die SQLException geworfen wurde * @exception StorageObjectException */ - void throwSQLException (SQLException sqe, String wo) throws StorageObjectException { + protected void throwSQLException (SQLException sqe, String wo) + throws StorageObjectException { String state = ""; String message = ""; int vendor = 0; @@ -1067,15 +1262,29 @@ public class Database implements StorageObject { sqe.toString()); } + protected void _throwStorageObjectException (Exception e, String wo) + throws StorageObjectException { + if (e != null) { + theLog.printError(e.toString()+ wo); + throw new StorageObjectException(wo + e.toString()); + } else { + theLog.printError(wo); + throw new StorageObjectException(wo); + } + + } + /** - * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach eine StorageObjectException + * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach + * eine StorageObjectException * @param message Nachricht mit dem Fehler * @exception StorageObjectException */ - void throwStorageObjectException (String message) throws StorageObjectException { - theLog.printError(message); - throw new StorageObjectException(message); + void throwStorageObjectException (String message) + throws StorageObjectException { + _throwStorageObjectException(null, message); } + }