DatabaseStorageManager


1   /*
2   
3   This software is OSI Certified Open Source Software.
4   OSI Certified is a certification mark of the Open Source Initiative.
5   
6   The license (Mozilla version 1.0) can be read at the MMBase site.
7   See http://www.MMBase.org/license
8   
9   */
10  package org.mmbase.storage.implementation.database;
11  
12  import java.io.*;
13  import java.sql.*;
14  import java.util.*;
15  
16  import org.mmbase.bridge.Field;
17  import org.mmbase.bridge.NodeManager;
18  import org.mmbase.cache.Cache;
19  import org.mmbase.core.CoreField;
20  import org.mmbase.core.util.Fields;
21  import org.mmbase.module.core.*;
22  import org.mmbase.storage.*;
23  import org.mmbase.storage.util.*;
24  import org.mmbase.util.Casting;
25  import org.mmbase.util.logging.Logger;
26  import org.mmbase.util.logging.Logging;
27  import org.mmbase.util.transformers.CharTransformer;
28  
29  /**
30   * A JDBC implementation of an object related storage manager.
31   * @javadoc
32   *
33   * @author Pierre van Rooden
34   * @since MMBase-1.7
35   * @version $Id: DatabaseStorageManager.java,v 1.169.2.1 2006/09/26 13:01:55 michiel Exp $
36   */
37  public class DatabaseStorageManager implements StorageManager {
38  
39      /** Max size of the object type cache */
40      public static final int OBJ2TYPE_MAX_SIZE = 20000;
41  
42      // contains a list of buffered keys
43      protected static final List sequenceKeys = new LinkedList();
44  
45      private static final Logger log = Logging.getLoggerInstance(DatabaseStorageManager.class);
46  
47      private static final Blob BLOB_SHORTED = new InputStreamBlob(null, -1);
48  
49      private static final CharTransformer UNICODE_ESCAPER = new org.mmbase.util.transformers.UnicodeEscaper();
50  
51      // maximum size of the key buffer
52      protected static Integer   bufferSize = null;
53  
54      /**
55       * This sets contains all existing tables which could by associated with MMBase builders. This
56       * is because they are queried all at once, but requested for existance only one at a time.
57       * @since MMBase-1.7.4
58       */
59      protected static Set tableNameCache = null;
60  
61      /**
62       * This sets contains all verified tables.
63       * @since MMBase-1.8.1
64       */
65      protected static Set verifiedTablesCache = new HashSet();
66  
67      /**
68       * Whether the warning about blob on legacy location was given.
69       */
70      private static boolean legacyWarned = false;
71  
72      /**
73       * The cache that contains the last X types of all requested objects
74       * @since 1.7
75       */
76      private static Cache typeCache;
77  
78      static {
79          typeCache = new Cache(OBJ2TYPE_MAX_SIZE) {
80              public String   getName()        { return "TypeCache"; }
81              public String   getDescription() { return "Cache for node types";}
82          };
83          typeCache.putCache();
84      }
85  
86      /**
87       * The factory that created this manager
88       */
89      protected DatabaseStorageManagerFactory factory;
90  
91      /**
92       * The currently active Connection.
93       * This member is set by {!link #getActiveConnection()} and unset by {@link #releaseActiveConnection()}
94       */
95      protected Connection activeConnection;
96  
97      /**
98       * <code>true</code> if a transaction has been started.
99       * This member is for state maitenance and may be true even if the storage does not support transactions
100      */
101     protected boolean inTransaction = false;
102 
103     /**
104      * The transaction issolation level to use when starting a transaction.
105      * This value is retrieved from the factory's {@link Attributes#TRANSACTION_ISOLATION_LEVEL} attribute, which is commonly set
106      * to the highest (most secure) transaction isolation level available.
107      */
108     protected int transactionIsolation = Connection.TRANSACTION_NONE;
109 
110     /**
111      * Pool of changed nodes in a transaction
112      */
113     protected Map changes;
114 
115     /**
116      * Constructor
117      */
118     public DatabaseStorageManager() {}
119 
120     protected long getLogStartTime() {
121         return System.currentTimeMillis();
122     }
123 
124     // for debug purposes
125     protected final void logQuery(String   query, long startTime) {
126         if (log.isDebugEnabled()) {
127             long now = System.currentTimeMillis();
128             log.debug("Time:" + (now - startTime) + " Query :" + query);
129             if (log.isTraceEnabled()) {
130                 log.trace(Logging.stackTrace());
131             }
132         }
133     }
134 
135     // javadoc is inherited
136     public double getVersion() {
137         return 1.0;
138     }
139 
140     // javadoc is inherited
141     public void init(StorageManagerFactory factory) throws StorageException {
142         this.factory = (DatabaseStorageManagerFactory)factory;
143         if (factory.supportsTransactions()) {
144             transactionIsolation = ((Integer  )factory.getAttribute(Attributes.TRANSACTION_ISOLATION_LEVEL)).intValue();
145         }
146         // determine generated key buffer size
147         if (bufferSize == null) {
148             bufferSize = new Integer  (1);
149             Object   bufferSizeAttribute = factory.getAttribute(Attributes.SEQUENCE_BUFFER_SIZE);
150             if (bufferSizeAttribute != null) {
151                 try {
152                     bufferSize = Integer.valueOf(bufferSizeAttribute.toString());
153                 } catch (NumberFormatException   nfe) {
154                     // remove the SEQUENCE_BUFFER_SIZE attribute (invalid value)
155                     factory.setAttribute(Attributes.SEQUENCE_BUFFER_SIZE, null);
156                     log.error("The attribute 'SEQUENCE_BUFFER_SIZE' has an invalid value(" +
157                         bufferSizeAttribute + "), will be ignored.");
158                 }
159             }
160         }
161 
162     }
163 
164     /**
165      * Obtains an active connection, opening a new one if needed.
166      * This method sets and then returns the {@link #activeConnection} member.
167      * If an active connection was allready open, and the manager is in a database transaction, that connection is returned instead.
168      * Otherwise, the connection is closed before a new one is opened.
169      * @throws SQLException if opening the connection failed
170      */
171     protected Connection getActiveConnection() throws SQLException {
172         if (activeConnection != null) {
173             if (factory.supportsTransactions() && inTransaction) {
174                 return activeConnection;
175             } else {
176                 releaseActiveConnection();
177             }
178         }
179         activeConnection = factory.getDataSource().getConnection();
180         // set autocommit to true
181         if (activeConnection != null) {
182             activeConnection.setAutoCommit(true);
183         }
184         return activeConnection;
185     }
186 
187     /**
188      * Safely closes the active connection.
189      * If a transaction has been started, the connection is not closed.
190      */
191     protected void releaseActiveConnection() {
192         if (!(inTransaction && factory.supportsTransactions()) && activeConnection != null) {
193             try {
194                 // ensure that future attempts to obtain a connection (i.e.e if it came from a pool)
195                 // start with autocommit set to true
196                 // needed because Query interface does not use storage layer to obtain transactions
197                 activeConnection.setAutoCommit(true);
198                 activeConnection.close();
199             } catch (SQLException se) {
200                 // if something went wrong, log, but do not throw exceptions
201                 log.error("Failure when closing connection: " + se.getMessage());
202             }
203             activeConnection = null;
204         }
205     }
206 
207     // javadoc is inherited
208     public void beginTransaction() throws StorageException {
209         if (inTransaction) {
210             throw new StorageException("Cannot start Transaction when one is already active.");
211         } else {
212             if (factory.supportsTransactions()) {
213                 try {
214                     getActiveConnection();
215                     if (activeConnection == null) return;
216                     activeConnection.setTransactionIsolation(transactionIsolation);
217                     activeConnection.setAutoCommit(false);
218                 } catch (SQLException se) {
219                     releaseActiveConnection();
220                     inTransaction = false;
221                     throw new StorageException(se);
222                 }
223             }
224             inTransaction = true;
225             changes = new HashMap();
226         }
227 
228     }
229 
230     // javadoc is inherited
231     public void commit() throws StorageException {
232         if (!inTransaction) {
233             throw new StorageException("No transaction started.");
234         } else {
235             inTransaction = false;
236             if (factory.supportsTransactions()) {
237                 if (activeConnection == null) {
238                     throw new StorageException("No active connection");
239                 }
240 
241                 try {
242                     activeConnection.commit();
243                 } catch (SQLException se) {
244                     throw new StorageException(se);
245                 } finally {
246                     releaseActiveConnection();
247                     factory.getChangeManager().commit(changes);
248                 }
249             }
250         }
251     }
252 
253     // javadoc is inherited
254     public boolean rollback() throws StorageException {
255         if (!inTransaction) {
256             throw new StorageException("No transaction started.");
257         } else {
258             inTransaction = false;
259             if (factory.supportsTransactions()) {
260                 try {
261                     activeConnection.rollback();
262                 } catch (SQLException se) {
263                     throw new StorageException(se);
264                 } finally {
265                     releaseActiveConnection();
266                     changes.clear();
267                 }
268             }
269             return factory.supportsTransactions();
270         }
271     }
272 
273     /**
274      * Commits the change to a node.
275      * If the manager is in a transaction (and supports it), the change is stored in a
276      * {@link #changes} object (to be committed after the transaction ends).
277      * Otherwise it directly commits and broadcasts the changes
278      * @param node the node to register
279      * @param change the type of change: "n": new, "c": commit, "d": delete, "r" : relation changed
280      */
281     protected void commitChange(MMObjectNode node, String   change) {
282         if (inTransaction && factory.supportsTransactions()) {
283             changes.put(node, change);
284         } else {
285             factory.getChangeManager().commit(node, change);
286             log.debug("Commited node");
287         }
288     }
289 
290     public int createKey() throws StorageException {
291         log.debug("Creating key");
292         synchronized (sequenceKeys) {
293             log.debug("Acquired lock");
294             // if sequenceKeys conatins (buffered) keys, return this
295             if (sequenceKeys.size() > 0) {
296                 return ((Integer  )sequenceKeys.remove(0)).intValue();
297             } else {
298                 String   query = "";
299                 try {
300                     getActiveConnection();
301                     Statement s;
302                     Scheme scheme = factory.getScheme(Schemes.UPDATE_SEQUENCE, Schemes.UPDATE_SEQUENCE_DEFAULT);
303                     if (scheme != null) {
304                         query = scheme.format(new Object  [] { this, factory.getStorageIdentifier("number"), bufferSize });
305                         long startTime = getLogStartTime();
306                         s = activeConnection.createStatement();
307                         s.executeUpdate(query);
308                         s.close();
309                     logQuery(query, startTime);
310                     }
311                     scheme = factory.getScheme(Schemes.READ_SEQUENCE, Schemes.READ_SEQUENCE_DEFAULT);
312                     query = scheme.format(new Object  [] { this, factory.getStorageIdentifier("number"), bufferSize });
313                     s = activeConnection.createStatement();
314                     try {
315                         long startTime = getLogStartTime();
316                         ResultSet result = s.executeQuery(query);
317                         logQuery(query, startTime);
318                         try {
319                             if (result.next()) {
320                                 int keynr = result.getInt(1);
321                                 // add remaining keys to sequenceKeys
322                                 for (int i = 1; i < bufferSize.intValue(); i++) {
323                                     sequenceKeys.add(new Integer  (keynr+i));
324                                 }
325                                 return keynr;
326                             } else {
327                                 throw new StorageException("The sequence table is empty.");
328                             }
329                         } finally {
330                             result.close();
331                         }
332                     } finally {
333                         s.close();
334                     }
335                 } catch (SQLException se) {
336                     log.error("" + query + " " + se.getMessage(), se);
337                     // wait 2 seconds, so any locks that were claimed are released.
338                     try {
339                         Thread.sleep(2000);
340                     } catch (InterruptedException   re) {}
341                     throw new StorageException(se);
342                 } finally {
343                     releaseActiveConnection();
344                 }
345             }
346         }
347     }
348 
349     // javadoc is inherited
350     public String   getStringValue(MMObjectNode node, CoreField field) throws StorageException {
351         try {
352             MMObjectBuilder builder = node.getBuilder();
353             Scheme scheme = factory.getScheme(Schemes.GET_TEXT_DATA, Schemes.GET_TEXT_DATA_DEFAULT);
354             String   query = scheme.format(new Object  [] { this, builder, field, builder.getField("number"), node });
355             getActiveConnection();
356             Statement s = activeConnection.createStatement();
357             ResultSet result = s.executeQuery(query);
358             try {
359                 if ((result != null) && result.next()) {
360                     String   rvalue = (String  ) getStringValue(result, 1, field, false);
361                     result.close();
362                     s.close();
363                     return rvalue;
364                 } else {
365                     if (result != null) result.close();
366                     s.close();
367                     throw new StorageException("Node with number " + node.getNumber() + " not found.");
368                 }
369             } finally {
370                 result.close();
371             }
372         } catch (SQLException se) {
373             throw new StorageException(se);
374         } finally {
375             releaseActiveConnection();
376         }
377     }
378 
379     /**
380      * Retrieve a text for a specified object field.
381      * The default method uses {@link ResultSet#getString(int)} to obtain text.
382      * Override this method if you want to optimize retrieving large texts,
383      * i.e by using clobs or streams.
384      * @param result the resultset to retrieve the text from
385      * @param index the index of the text in the resultset
386      * @param field the (MMBase) fieldtype. This value can be null
387      * @return the retrieved text, <code>null</code> if no text was stored
388      * @throws SQLException when a database error occurs
389      * @throws StorageException when data is incompatible or the function is not supported
390      */
391     protected Object   getStringValue(ResultSet result, int index, CoreField field, boolean mayShorten) throws StorageException, SQLException {
392         String   untrimmedResult = null;
393         if (field != null && (field.getStorageType() == Types.CLOB || field.getStorageType() == Types.BLOB || factory.hasOption(Attributes.FORCE_ENCODE_TEXT))) {
394             InputStream inStream = result.getBinaryStream(index);
395             if (result.wasNull()) {
396                 return null;
397             }
398             if (mayShorten && shorten(field)) {
399                 return MMObjectNode.VALUE_SHORTED;
400             }
401             try {
402                 ByteArrayOutputStream bytes = new ByteArrayOutputStream();
403                 int c = inStream.read();
404                 while (c != -1) {
405                     bytes.write(c);
406                     c = inStream.read();
407                 }
408                 inStream.close();
409                 String   encoding = factory.getMMBase().getEncoding();
410                 if (encoding.equalsIgnoreCase("ISO-8859-1")) {
411                     // CP 1252 only fills in the 'blanks' of ISO-8859-1,
412                     // so it is save to upgrade the encoding, in case accidentily those bytes occur
413                     encoding = "CP1252";
414                 }
415                 untrimmedResult = new String  (bytes.toByteArray(), encoding);
416             } catch (IOException ie) {
417                 throw new StorageException(ie);
418             }
419         } else {
420             untrimmedResult = result.getString(index);
421             if (factory.hasOption(Attributes.LIE_CP1252) && untrimmedResult != null) {
422                 try {
423                     String   encoding = factory.getMMBase().getEncoding();
424                     if (encoding.equalsIgnoreCase("ISO-8859-1")) {
425                         untrimmedResult = new String  (untrimmedResult.getBytes("ISO-8859-1"), "CP1252");
426                     }
427                  } catch(java.io.UnsupportedEncodingException   uee) {
428                      // cannot happen
429                  }
430             }
431         }
432 
433 
434         if(untrimmedResult != null) {
435             if (factory.hasOption(Attributes.TRIM_STRINGS)) {
436                 untrimmedResult = untrimmedResult.trim();
437             }
438             if (factory.getGetSurrogator() != null) {
439                 untrimmedResult = factory.getGetSurrogator().transform(untrimmedResult);
440             }
441         }
442 
443         return untrimmedResult;
444     }
445 
446     /**
447      * Retrieve the XML (as a string) for a specified object field.
448      * The default method uses {@link ResultSet#getString(int)} to obtain text.
449      * Unlike
450      * Override this method if you want to optimize retrieving large texts,
451      * i.e by using clobs or streams.
452      * @param result the resultset to retrieve the xml from
453      * @param index the index of the xml in the resultset
454      * @param field the (MMBase) fieldtype. This value can be null
455      * @return the retrieved xml as text, <code>null</code> if nothing was stored
456      * @throws SQLException when a database error occurs
457      * @throws StorageException when data is incompatible or the function is not supported
458      */
459      protected Object   getXMLValue(ResultSet result, int index, CoreField field, boolean mayShorten) throws StorageException, SQLException {
460          return getStringValue(result, index, field, mayShorten);
461      }
462 
463 
464     /**
465      * Retrieve a date for a specified object field.
466      * The default method uses {@link ResultSet#getTimestamp(int)} to obtain the date.
467      * @param result the resultset to retrieve the value from
468      * @param index the index of the value in the resultset
469      * @param field the (MMBase) fieldtype. This value can be null
470      * @return the retrieved java.util.Date value, <code>null</code> if no text was stored
471      * @throws SQLException when a database error occurs
472      * @throws StorageException when data is incompatible or the function is not supported
473      * @since MMBase-1.8
474      */
475     protected java.util.Date   getDateTimeValue(ResultSet result, int index, CoreField field) throws StorageException, SQLException {
476         Timestamp ts = null;
477         try {
478             ts = result.getTimestamp(index);
479         }
480         catch (SQLException sqle) {
481             // deal with all-zero datetimes when reading them
482             if ("S1009".equals(sqle.getSQLState())) {
483                 return null;
484             }
485             else {
486                 throw sqle;
487             }
488         }
489         if (ts == null) {
490             return null;
491         } else {
492             long time = ts.getTime();
493             java.util.Date   d = new java.util.Date  (time + factory.getTimeZoneOffset(time));
494             return d;
495         }
496     }
497 
498     /**
499      * Retrieve a boolean value for a specified object field.
500      * The default method uses {@link ResultSet#getBoolean(int)} to obtain the date.
501      * @param result the resultset to retrieve the value from
502      * @param index the index of the value in the resultset
503      * @param field the (MMBase) fieldtype. This value can be null
504      * @return the retrieved Boolean value, <code>null</code> if no text was stored
505      * @throws SQLException when a database error occurs
506      * @throws StorageException when data is incompatible or the function is not supported
507      * @since MMBase-1.8
508      */
509     protected Boolean   getBooleanValue(ResultSet result, int index, CoreField field) throws StorageException, SQLException {
510         boolean value = result.getBoolean(index);
511         if (result.wasNull()) {
512             return null;
513         } else {
514             return Boolean.valueOf(value);
515         }
516     }
517 
518     /**
519      * Determine whether a field (such as a large text or a blob) should be shortened or not.
520      * A 'shortened' field contains a placeholder text ('$SHORTED') to indicate that the field is expected to be of large size
521      * and should be retrieved by an explicit call to {@link #getStringValue(MMObjectNode, CoreField)} or.
522      * {@link #getBinaryValue(MMObjectNode, CoreField)}.
523      * The default implementation returns <code>true</code> for binaries, and <code>false</code> for other
524      * types.
525      * Override this method if you want to be able to change the placeholder strategy.
526      * @param field the (MMBase) fieldtype
527      * @return <code>true</code> if the field should be shortened
528      * @throws SQLException when a database error occurs
529      * @throws StorageException when data is incompatible or the function is not supported
530      */
531     protected boolean shorten(CoreField field) {
532         return field.getType() == Field.TYPE_BINARY;
533     }
534 
535     /**
536      * Read a binary (blob) from a field in the database
537      * @param node the node the binary data belongs to
538      * @param field the binary field
539      * @return An InputStream representing the binary data, <code>null</code> if no binary data was stored, or VALUE_SHORTED, if mayShorten
540      */
541     protected Blob getBlobFromDatabase(MMObjectNode node, CoreField field, boolean mayShorten) {
542         try {
543             MMObjectBuilder builder = node.getBuilder();
544             Scheme scheme = factory.getScheme(Schemes.GET_BINARY_DATA, Schemes.GET_BINARY_DATA_DEFAULT);
545             String   query = scheme.format(new Object  [] { this, builder, field, builder.getField("number"), node });
546             getActiveConnection();
547             Statement s = activeConnection.createStatement();
548             ResultSet result = s.executeQuery(query);
549             try {
550                 if ((result != null) && result.next()) {
551                     Blob blob = getBlobValue(result, 1, field, mayShorten);
552                     if (blob != null) {
553                         node.setSize(field.getName(), blob.length());
554                     }
555                     return blob;
556                 } else {
557                     if (result != null) result.close();
558                     s.close();
559                     throw new StorageException("Node with number " + node.getNumber() + " of type " + builder + " not found.");
560                 }
561             } finally {
562                 result.close();
563             }
564         } catch (SQLException se) {
565             throw new StorageException(se);
566         } finally {
567             releaseActiveConnection();
568         }
569     }
570 
571     // javadoc is inherited
572     public byte[] getBinaryValue(MMObjectNode node, CoreField field) throws StorageException {
573         try {
574             Blob b = getBlobValue(node, field);
575             if (b == null) {
576                 return null;
577             } else {
578                 return b.getBytes(1, (int) b.length());
579             }
580         } catch (SQLException sqe) {
581             throw new StorageException(sqe);
582         }
583     }
584     // javadoc is inherited
585     public InputStream getInputStreamValue(MMObjectNode node, CoreField field) throws StorageException {
586         try {
587             return getBlobValue(node, field).getBinaryStream();
588         } catch (SQLException sqe) {
589             throw new StorageException(sqe);
590         }
591     }
592 
593     public Blob getBlobValue(MMObjectNode node, CoreField field) throws StorageException {
594         return getBlobValue(node, field, false);
595     }
596 
597     public Blob getBlobValue(MMObjectNode node, CoreField field, boolean mayShorten) throws StorageException {
598         if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE)) {
599             return getBlobFromFile(node, field, mayShorten);
600         } else {
601             return getBlobFromDatabase(node, field, mayShorten);
602         }
603     }
604 
605 
606     /**
607      * Retrieve a large binary object (byte array) for a specified object field.
608      * The default method uses {@link ResultSet#getBytes(int)} to obtain text.
609      * Override this method if you want to optimize retrieving large objects,
610      * i.e by using clobs or streams.
611      * @param result the resultset to retrieve the text from
612      * @param index the index of the text in the resultset, or -1 to retireiv from file (blobs).
613      * @param field the (MMBase) fieldtype. This value can be null
614      * @return the retrieved data, <code>null</code> if no binary data was stored
615      * @throws SQLException when a database error occurs
616      * @throws StorageException when data is incompatible or the function is not supported
617      */
618     protected Blob getBlobValue(ResultSet result, int index, CoreField field, boolean mayShorten) throws StorageException, SQLException {
619         if (factory.hasOption(Attributes.SUPPORTS_BLOB)) {
620             Blob blob = result.getBlob(index);
621             if (result.wasNull()) {
622                 return null;
623             }
624             if (mayShorten && shorten(field)) {
625                 return BLOB_SHORTED;
626             }
627 
628             return blob;
629         } else {
630             try {
631                 InputStream inStream = result.getBinaryStream(index);
632                 if (result.wasNull()) {
633                     if (inStream != null) {
634                         try {
635                             inStream.close();
636                         }
637                         catch (RuntimeException   e) {
638                             log.debug("" + e.getMessage(), e);
639                         }
640                     }
641                     return null;
642                 }
643                 if (mayShorten && shorten(field)) {
644                     if (inStream != null) {
645                         try {
646                             inStream.close();
647                         }
648                         catch (RuntimeException   e) {
649                             log.debug("" + e.getMessage(), e);
650                         }
651                     }
652                     return BLOB_SHORTED;
653                 }
654                 return new InputStreamBlob(inStream);
655             } catch (IOException ie) {
656                 throw new StorageException(ie);
657             }
658         }
659     }
660 
661     /**
662      * Defines how binary (blob) data files must look like.
663      * @param node the node the binary data belongs to
664      * @param fieldName the name of the binary field
665      * @return The File where to store or read the binary data
666      */
667     protected File getBinaryFile(MMObjectNode node, String   fieldName) {
668         String   basePath = factory.getBinaryFileBasePath();
669         StringBuffer   pathBuffer = new StringBuffer  ();
670         int number = node.getNumber() / 1000;
671         while (number > 0) {
672             int num = number % 100;
673             pathBuffer.insert(0, num);
674             if (num < 10) {
675                 pathBuffer.insert(0, 0);
676             }
677             pathBuffer.insert(0, File.separator);
678             number /= 100;
679         }
680 
681         /*
682          * This method is sometimes called with a node which has a supertype builder
683          * attached instead of the real subtype builder. A read from the file system will fail,
684          * because binaries are stored based on the subtype.
685          */
686         String   builderName = null;
687         int builderType = node.getBuilder().getObjectType();
688         int realOtypeValue = node.getOType();
689         if (builderType != realOtypeValue) {
690             MMBase mmb = factory.getMMBase();
691             builderName = mmb.getTypeDef().getValue(realOtypeValue);
692             builderName = mmb.getBuilder(builderName).getFullTableName();
693         }
694         else {
695             builderName = node.getBuilder().getFullTableName();
696         }
697 
698         pathBuffer.insert(0, basePath + factory.getDatabaseName() + File.separator + builderName);
699         return new File(pathBuffer.toString(), "" + node.getNumber() + '.' + fieldName);
700     }
701 
702     /**
703      * Tries legacy paths
704      * @returns such a File if found and readable, 'null' otherwise.
705      */
706     private File getLegacyBinaryFile(MMObjectNode node, String   fieldName) {
707         // the same basePath, so you so need to set that up right.
708         String   basePath = factory.getBinaryFileBasePath();
709 
710         File f = new File(basePath, node.getBuilder().getTableName() + File.separator + node.getNumber() + '.' + fieldName);
711         if (f.exists()) { // 1.6 storage or 'support' blobdatadir
712             if (!f.canRead()) {
713                 log.warn("Found '" + f + "' but it cannot be read");
714             } else {
715                 return f;
716             }
717         }
718 
719         f = new File(basePath + File.separator + factory.getCatalog() + File.separator + node.getBuilder().getFullTableName() + File.separator + node.getNumber() + '.' + fieldName);
720         if (f.exists()) { // 1.7.0.rc1 blob data dir
721             if (!f.canRead()) {
722                 log.warn("Found '" + f + "' but it cannot be read");
723             } else {
724                 return f;
725             }
726         }
727 
728         // don't know..
729         return null;
730 
731     }
732 
733     /**
734      * Store a binary (blob) data file
735      * @todo how to do this in a transaction???
736      * @param node the node the binary data belongs to
737      * @param field the binary field
738      */
739     protected void storeBinaryAsFile(MMObjectNode node, CoreField field) throws StorageException {
740         try {
741             String   fieldName = field.getName();
742             File binaryFile = getBinaryFile(node, fieldName);
743             binaryFile.getParentFile().mkdirs(); // make sure all directory exist.
744             if (node.isNull(fieldName)) {
745                 if (field.isNotNull()) {
746                     node.storeValue(field.getName(), new ByteArrayInputStream(new byte[0]));
747                 } else {
748                     if (binaryFile.exists()) {
749                         binaryFile.delete();
750                     }
751                     return;
752                 }
753             }
754             //log.warn("Storing " + field + " for " + node.getNumber());
755             InputStream in = node.getInputStreamValue(fieldName);
756             OutputStream out = new BufferedOutputStream(new FileOutputStream(binaryFile));
757             long size = 0;
758             int c = in.read();
759             while (c > -1) {
760                 out.write(c);
761                 c = in.read();
762                 size ++;
763             }
764             out.close();
765             in.close();
766             // unload the input-stream, it is of no use any more.
767             node.setSize(fieldName, size);
768             node.storeValue(fieldName, MMObjectNode.VALUE_SHORTED);
769         } catch (IOException ie) {
770             throw new StorageException(ie);
771         }
772     }
773 
774     /**
775      * Checks whether file is readable and existing. Warns if not.
776      * If non-existing it checks older locations.
777      * @return the file to be used, or <code>null</code> if no existing readable file could be found, also no 'legacy' one.
778      */
779 
780     protected File checkFile(File binaryFile, MMObjectNode node, CoreField field) {
781         String   fieldName = field.getName();
782         if (!binaryFile.canRead()) {
783             String   desc = "while it should contain the byte array data for node '" + node.getNumber() + "' field '" + fieldName + "'. Returning null.";
784             if (!binaryFile.exists()) {
785                 // try legacy
786                 File legacy = getLegacyBinaryFile(node, fieldName);
787                 if (legacy == null) {
788                     if (!binaryFile.getParentFile().exists()) {
789                         log.warn("The file '" + binaryFile + "' does not exist, " + desc, new Exception  ());
790                         log.info("If you upgraded from older MMBase version, it might be that the blobs were stored on a different location. Make sure your blobs are in '"
791                                  + factory.getBinaryFileBasePath()
792                                  + "' (perhaps use symlinks?). If you changed configuration to 'blobs-on-disk' while it was blobs-in-database. Go to admin-pages.");
793 
794                     } else if (log.isDebugEnabled()) {
795                         log.debug("The file '" + binaryFile + "' does not exist. Probably the blob field is simply 'null'");
796                     }
797                 } else {
798                     if (!legacyWarned) {
799                         log.warn("Using the legacy location '" + legacy + "' rather then '" + binaryFile + "'. You might want to convert this dir.");
800                         legacyWarned = true;
801                     }
802                     return legacy;
803                 }
804             } else {
805                 log.error("The file '" + binaryFile + "' can not be read, " + desc);
806             }
807             return null;
808         } else {
809             return binaryFile;
810         }
811     }
812 
813     /**
814      * Read a binary (blob) data file
815      * @todo how to do this in a transaction???
816      * @param node the node the binary data belongs to
817      * @param field the binary field
818      * @return the byte array containing the binary data, <code>null</code> if no binary data was stored
819      */
820     protected Blob getBlobFromFile(MMObjectNode node, CoreField field, boolean mayShorten) throws StorageException {
821         String   fieldName = field.getName();
822         File binaryFile = checkFile(getBinaryFile(node, fieldName), node, field);
823         if (binaryFile == null) {
824             return null;
825         }
826         try {
827             node.setSize(field.getName(), binaryFile.length());
828             if (mayShorten && shorten(field)) {
829                 return BLOB_SHORTED;
830             }
831             return new InputStreamBlob(new FileInputStream(binaryFile), binaryFile.length());
832         } catch (FileNotFoundException fnfe) {
833             throw new StorageException(fnfe);
834         }
835     }
836 
837     // javadoc is inherited
838     public int create(MMObjectNode node) throws StorageException {
839         // assign a new number if the node has not yet been assigned one
840         int nodeNumber = node.getNumber();
841         if (nodeNumber == -1) {
842             nodeNumber = createKey();
843             node.setValue(MMObjectBuilder.FIELD_NUMBER, nodeNumber);
844         }
845         MMObjectBuilder builder = node.getBuilder();
846         // precommit call, needed to convert or add things before a save
847         // Should be done in MMObjectBuilder
848         builder.preCommit(node);
849         create(node, builder);
850         commitChange(node, "n");
851         unloadShortedFields(node, builder);
852         //refresh(node);
853         return nodeNumber;
854     }
855 
856     /**
857      * This method inserts a new object in a specific builder, and registers the change.
858      * This method makes it easier to implement relational databases, where you may need to update the node
859      * in more than one builder.
860      * Call this method for all involved builders if you use a relational database.
861      * @param node The node to insert. The node already needs to have a (new) number assigned
862      * @param builder the builder to store the node
863      * @throws StorageException if an error occurred during creation
864      */
865     protected void create(MMObjectNode node, MMObjectBuilder builder) throws StorageException {
866         // get a builders fields
867         List createFields = new ArrayList();
868         List builderFields = builder.getFields(NodeManager.ORDER_CREATE);
869         for (Iterator f = builderFields.iterator(); f.hasNext();) {
870             CoreField field = (CoreField)f.next();
871             if (field.inStorage()) {
872                 createFields.add(field);
873             }
874         }
875         String   tablename = (String  ) factory.getStorageIdentifier(builder);
876         create(node, createFields, tablename);
877     }
878 
879     protected void create(MMObjectNode node, List createFields, String   tablename) {
880         // Create a String that represents the fields and values to be used in the insert.
881         StringBuffer   fieldNames = null;
882         StringBuffer   fieldValues = null;
883 
884         List fields = new ArrayList();
885         for (Iterator f = createFields.iterator(); f.hasNext();) {
886             CoreField field = (CoreField)f.next();
887             // skip bytevalues that are written to file
888             if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE) && (field.getType() == Field.TYPE_BINARY)) {
889                 storeBinaryAsFile(node, field);
890                 // do not handle this field further
891             } else {
892                 // store the fieldname and the value parameter
893                 fields.add(field);
894                 String   fieldName = (String  )factory.getStorageIdentifier(field);
895                 if (fieldNames == null) {
896                     fieldNames = new StringBuffer  (fieldName);
897                     fieldValues = new StringBuffer  ("?");
898                 } else {
899                     fieldNames.append(',').append(fieldName);
900                     fieldValues.append(",?");
901                 }
902             }
903         }
904         if (log.isDebugEnabled()) {
905             log.debug("insert field values " + fieldNames + " " + fieldValues);
906         }
907         if (fields.size() > 0) {
908             Scheme scheme = factory.getScheme(Schemes.INSERT_NODE, Schemes.INSERT_NODE_DEFAULT);
909             try {
910                 String   query = scheme.format(new Object  [] { this, tablename, fieldNames.toString(), fieldValues.toString()});
911                 getActiveConnection();
912                 executeUpdateCheckConnection(query, node, fields);
913             } catch (SQLException se) {
914                 throw new StorageException(se.getMessage() + " during creation of " + UNICODE_ESCAPER.transform(node.toString()), se);
915             } finally {
916                 releaseActiveConnection();
917             }
918         }
919     }
920 
921     protected void unloadShortedFields(MMObjectNode node, MMObjectBuilder builder) {
922         for (Iterator f = builder.getFields().iterator(); f.hasNext();) {
923             CoreField field = (CoreField)f.next();
924             if (field.inStorage() && shorten(field)) {
925                 String   fieldName = field.getName();
926                 if (! node.isNull(fieldName)) {
927                     node.storeValue(fieldName, MMObjectNode.VALUE_SHORTED);
928                 }
929             }
930         }
931     }
932 
933     /**
934      * Executes an update query for given node and fields. It will close the connections which are no
935      * good, which it determines by trying "SELECT 1 FROM <OBJECT TABLE>" after failure. If that happens, the connection
936      * is explicitely closed (in case the driver has not done that), which will render is unusable
937      * and at least GenericDataSource will automaticly try to get new ones.
938      *
939      * @throws SQLException If something wrong with the query, or the database is down or could not be contacted.
940      * @since MMBase-1.7.1
941      */
942     protected void executeUpdateCheckConnection(String   query, MMObjectNode node,  List fields) throws SQLException {
943         try {
944             executeUpdate(query, node, fields);
945         } catch (SQLException sqe) {
946             while (true) {
947                 Statement s = null;
948                 ResultSet rs = null;
949                 try {
950                     s = activeConnection.createStatement();
951                     rs = s.executeQuery("SELECT 1 FROM " + factory.getMMBase().getBuilder("object").getFullTableName() + " WHERE 1 = 0"); // if this goes wrong too it can't be the query
952                 } catch (SQLException isqe) {
953                     // so, connection must be broken.
954                     log.service("Found broken connection, closing it");
955                     if (activeConnection instanceof org.mmbase.module.database.MultiConnection) {
956                         ((org.mmbase.module.database.MultiConnection) activeConnection).realclose();
957                     } else {
958                         activeConnection.close();
959                     }
960                     getActiveConnection();
961                     if (activeConnection.isClosed()) {
962                         // don't know if that can happen, but if it happens, this would perhaps avoid an infinite loop (and exception will get thrown in stead)
963                         break;
964                     }
965                     continue;
966                  } finally {
967                      if (s != null) s.close();
968                      if (rs != null) rs.close();
969                  }
970                 break;
971             }
972             executeUpdate(query, node, fields);
973         }
974     }
975 
976     /**
977      * Executes an update query for given node and fields.  This is wrapped in a function because it
978      * is repeatedly called in {@link #executeUpdateCheckConnection} which in turn is called from
979      * several spots in this class.
980      *
981      * @since MMBase-1.7.1
982      */
983     protected void executeUpdate(String   query, MMObjectNode node, List fields) throws SQLException {
984         PreparedStatement ps = activeConnection.prepareStatement(query);
985         for (int fieldNumber = 0; fieldNumber < fields.size(); fieldNumber++) {
986             CoreField field = (CoreField) fields.get(fieldNumber);
987             try {
988                 setValue(ps, fieldNumber + 1, node, field);
989             } catch (Exception   e) {
990                 SQLException sqle = new SQLException(node.toString() + "/" + field + " " + e.getMessage());
991                 sqle.initCause(e);
992                 throw sqle;
993             }
994         }
995         long startTime = getLogStartTime();
996         ps.executeUpdate();
997         ps.close();
998         logQuery(query, startTime);
999 
1000    }
1001
1002    // javadoc is inherited
1003    public void change(MMObjectNode node) throws StorageException {
1004        // resolve aliases, if any.
1005        MMObjectBuilder builder = node.getBuilder();
1006        for (Iterator i = builder.getFields().iterator(); i.hasNext(); ) {
1007            CoreField field = (CoreField) i.next();
1008            if (field.getName().equals(builder.FIELD_NUMBER))      continue;
1009            if (field.getName().equals(builder.FIELD_OBJECT_TYPE)) continue;
1010            if (field.getType() == Field.TYPE_NODE) {
1011                Object   value = node.getValue(field.getName());
1012                if (value instanceof String  ) {
1013                    node.setValue(field.getName(), builder.getNode((String  )value));
1014                }
1015            }
1016        }
1017        // precommit call, needed to convert or add things before a save
1018        // Should be done in MMObjectBuilder
1019        builder.preCommit(node);
1020        change(node, builder);
1021        commitChange(node, "c");
1022        unloadShortedFields(node, builder);
1023        // the node instance can be wrapped by other objects (org.mmbase.bridge.implementation.BasicNode) or otherwise still in use.
1024        // this make sure that the values are realistic reflections of the database:
1025        // This can change after a commit e.g. if the database enforces a maximum length for certain fields.
1026        refresh(node);
1027    }
1028
1029    /**
1030     * Change this node in the specified builder.
1031     * This method makes it easier to implement relational databses, where you may need to update the node
1032     * in more than one builder.
1033     * Call this method for all involved builders if you use a relational database.
1034     * @param node The node to change
1035     * @param builder the builder to store the node
1036     * @throws StorageException if an error occurred during change
1037     */
1038    protected void change(MMObjectNode node, MMObjectBuilder builder) throws StorageException {
1039        List changeFields = new ArrayList();
1040        // obtain the node's changed fields
1041        Collection fieldNames = node.getChanged();
1042        synchronized(fieldNames) { // make sure the set is not changed during this loop
1043            for (Iterator f = fieldNames.iterator(); f.hasNext();) {
1044                String   key = (String  )f.next();
1045                CoreField field = builder.getField(key);
1046                if ((field != null) && field.inStorage()) {
1047                    changeFields.add(field);
1048                }
1049            }
1050        }
1051        String   tablename = (String  ) factory.getStorageIdentifier(builder);
1052        change(node, builder, tablename, changeFields);
1053    }
1054
1055    protected void change(MMObjectNode node, MMObjectBuilder builder, String   tableName, Collection changeFields) {
1056        // Create a String that represents the fields to be used in the commit
1057        StringBuffer   setFields = null;
1058        List fields = new ArrayList();
1059        for (Iterator iter = changeFields.iterator(); iter.hasNext();) {
1060            CoreField field = (CoreField) iter.next();
1061            // changing number is not allowed
1062            if ("number".equals(field.getName()) || "otype".equals(field.getName())) {
1063                throw new StorageException("trying to change the '" + field.getName() + "' field of " + node + ". Changed fields " + node.getChanged());
1064            }
1065            // skip bytevalues that are written to file
1066            if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE) && (field.getType() == Field.TYPE_BINARY)) {
1067                storeBinaryAsFile(node, field);
1068            } else {
1069                // handle this field - store it in fields
1070                fields.add(field);
1071                // store the fieldname and the value parameter
1072                String   fieldName = (String  )factory.getStorageIdentifier(field);
1073                if (setFields == null) {
1074                    setFields = new StringBuffer  (fieldName + "=?");
1075                } else {
1076                    setFields.append(',').append(fieldName).append("=?");
1077                }
1078            }
1079        }
1080        if (log.isDebugEnabled()) {
1081            log.debug("change field values " + node);
1082        }
1083        if (fields.size() > 0) {
1084            Scheme scheme = factory.getScheme(Schemes.UPDATE_NODE, Schemes.UPDATE_NODE_DEFAULT);
1085            try {
1086                String   query = scheme.format(new Object  [] { this, tableName , setFields.toString(), builder.getField("number"), node });
1087                getActiveConnection();
1088                executeUpdateCheckConnection(query, node, fields);
1089            } catch (SQLException se) {
1090                throw new StorageException(se.getMessage() + " for node " + node, se);
1091            } finally {
1092                releaseActiveConnection();
1093            }
1094        }
1095    }
1096
1097
1098    /**
1099     * Store the value of a field in a prepared statement
1100     * @todo Note that this code contains some code that should really be implemented in CoreField.
1101     * In particular, casting should be done in CoreField, IMO.
1102     * @param statement the prepared statement
1103     * @param index the index of the field in the prepared statement
1104     * @param node the node from which to retrieve the value
1105     * @param field the MMBase field, containing meta-information
1106     * @throws StorageException if the fieldtype is invalid, or data is invalid or missing
1107     * @throws SQLException if an error occurred while filling in the fields
1108     */
1109    protected void setValue(PreparedStatement statement, int index, MMObjectNode node, CoreField field) throws StorageException, SQLException {
1110        String   fieldName = field.getName();
1111        Object   value = node.getValue(fieldName);
1112        switch (field.getType()) {
1113            // Store numeric values
1114        case Field.TYPE_INTEGER :
1115        case Field.TYPE_FLOAT :
1116        case Field.TYPE_DOUBLE :
1117        case Field.TYPE_LONG :
1118            setNumericValue(statement, index, value, field, node);
1119            break;
1120        case Field.TYPE_BOOLEAN :
1121            setBooleanValue(statement, index, value, field, node);
1122            break;
1123        case Field.TYPE_DATETIME :
1124            setDateTimeValue(statement, index, value, field, node);
1125            break;
1126            // Store nodes
1127        case Field.TYPE_NODE :
1128            // cannot do getNodeValue here because that might cause a new connection to be needed -> deadlocks
1129            setNodeValue(statement, index, value, field, node);
1130            break;
1131            // Store strings
1132        case Field.TYPE_XML :
1133            setXMLValue(statement, index, value, field, node);
1134            break;
1135        case Field.TYPE_STRING :
1136            // note: do not use getStringValue, as this may attempt to
1137            // retrieve a (old, or nonexistent) value from the storage
1138            node.storeValue(fieldName, setStringValue(statement, index, value, field, node));
1139            break;
1140            // Store binary data
1141        case Field.TYPE_BINARY : {
1142            // note: do not use getByteValue, as this may attempt to
1143            // retrieve a (old, or nonexistent) value from the storage
1144            setBinaryValue(statement, index, value, field, node);
1145            break;
1146        }
1147        case Field.TYPE_LIST : {
1148            setListValue(statement, index, value, field, node);
1149            break;
1150        }
1151        default :    // unknown field type - error
1152            throw new StorageException("unknown fieldtype");
1153        }
1154    }
1155
1156
1157    /**
1158     * Stores the 'null' value in the statement if appopriate (the value is null or unset, and the
1159     * value may indeed be NULL, according to the configuration). If the value is null or unset,
1160     * but the value may not be NULL, then -1 is stored.
1161     * @param statement the prepared statement
1162     * @param index the index of the field in the prepared statement
1163     * @param value the numeric value to store, which will be checked for null.
1164     * @param field the MMBase field, containing meta-information
1165     * @throws StorageException if the data is invalid or missing
1166     * @throws SQLException if an error occurred while filling in the fields
1167     * @return true if a null value was set, false otherwise
1168     * @since MMBase-1.7.1
1169     */
1170    protected boolean setNullValue(PreparedStatement statement, int index, Object   value, CoreField field, int type) throws StorageException, SQLException {
1171        boolean mayBeNull = ! field.isNotNull();
1172        if (value == null) { // value unset
1173            if (mayBeNull) {
1174                statement.setNull(index, type);
1175                return true;
1176            }
1177            /*
1178        } else if (value == MMObjectNode.VALUE_NULL) { // value explicitely set to 'null'
1179            if (mayBeNull) {
1180                statement.setNull(index, type);
1181                return true;
1182            } else {
1183                log.debug("Tried to set 'null' in field '" + field.getName() + "' but the field is 'NOT NULL', it will be cast.");
1184            }
1185            */
1186        }
1187
1188        return false;
1189    }
1190
1191    /**
1192     * Store a numeric value of a field in a prepared statement
1193     * The method uses the Casting class to convert to the appropriate value.
1194     * Null values are stored as NULL if possible, otherwise they are stored as -1.
1195     * Override this method if you want to override this behavior.
1196     * @param statement the prepared statement
1197     * @param index the index of the field in the prepared statement
1198     * @param value the numeric value to store. This may be a String, MMObjectNode, Numeric, or other value - the
1199     *        method will convert it to the appropriate value.
1200     * @param field the MMBase field, containing meta-information
1201     * @param node the node that contains the data. Used to update this node if the database layer makes changes
1202     *             to the data (i.e. creating a default value for a non-null field that had a null value)
1203     * @throws StorageException if the data is invalid or missing
1204     * @throws SQLException if an error occurred while filling in the fields
1205     */
1206    protected void setNumericValue(PreparedStatement statement, int index, Object   value, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1207        // Store integers, floats, doubles and longs
1208        if (!setNullValue(statement, index, value, field, field.getType())) {
1209            switch (field.getType()) { // it does this switch part twice now?
1210            case Field.TYPE_INTEGER : {
1211                int storeValue = Casting.toInt(value);
1212                statement.setInt(index, storeValue);
1213                node.storeValue(field.getName(), new Integer  (storeValue));
1214                break;
1215            }
1216            case Field.TYPE_FLOAT : {
1217                float storeValue = Casting.toFloat(value);
1218                statement.setFloat(index, storeValue);
1219                node.storeValue(field.getName(), new Float  (storeValue));
1220                break;
1221            }
1222            case Field.TYPE_DOUBLE : {
1223                double storeValue = Casting.toDouble(value);
1224                statement.setDouble(index, storeValue);
1225                node.storeValue(field.getName(), new Double  (storeValue));
1226                break;
1227            }
1228            case Field.TYPE_LONG : {
1229                long storeValue = Casting.toLong(value);
1230                statement.setLong(index, storeValue);
1231                node.storeValue(field.getName(), new Long  (storeValue));
1232                break;
1233            }
1234            default:
1235                break;
1236            }
1237        }
1238    }
1239
1240
1241    /**
1242     * Store a node value of a field in a prepared statement
1243     * Nodes are stored in the database as numeric values.
1244     * Since a node value can be a (referential) key (depending on implementation),
1245     * Null values should be stored as NULL, not -1. If a field cannot be null when a
1246     * value is not given, an exception is thrown.
1247     * Override this method if you want to override this behavior.
1248     * @param statement the prepared statement
1249     * @param index the index of the field in the prepared statement
1250     * @param nodeValue the node to store
1251     * @param field the MMBase field, containing meta-information
1252     * @param node the node that contains the data.
1253     * @throws StorageException if the data is invalid or missing
1254     * @throws SQLException if an error occurred while filling in the fields
1255     */
1256    protected void setNodeValue(PreparedStatement statement, int index, Object   nodeValue, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1257        if (!setNullValue(statement, index, nodeValue, field, java.sql.Types.INTEGER)) {
1258            if (nodeValue == null && field.isNotNull()) {
1259                throw new StorageException("The NODE field with name " + field.getClass() + " " + field.getName() + " of type " + field.getParent().getTableName() + " can not be NULL.");
1260            }
1261            int nodeNumber;
1262            if (nodeValue instanceof MMObjectNode) {
1263                nodeNumber = ((MMObjectNode) nodeValue).getNumber();
1264            } else {
1265                nodeNumber = Casting.toInt(nodeValue);
1266            }
1267            if (nodeNumber < 0) {
1268                throw new StorageException("Node number " + nodeNumber + "(from " + nodeValue.getClass() + " " + nodeValue + ") is not valid for field '" + field.getName() + "' of node " + node.getNumber());
1269            }
1270            // retrieve node as a numeric value
1271            statement.setInt(index, nodeNumber);
1272        }
1273    }
1274
1275    /**
1276     * Store a boolean value of a field in a prepared statement.
1277     * The method uses the Casting class to convert to the appropriate value.
1278     * Null values are stored as NULL if possible, otherwise they are stored as <code>false</code>
1279     * Override this method if you use another way to store booleans
1280     * @param statement the prepared statement
1281     * @param index the index of the field in the prepared statement
1282     * @param value the data (boolean) to store
1283     * @param field the MMBase field, containing meta-information
1284     * @param node the node that contains the data. Used to update this node if the database layer makes changes
1285     *             to the data (i.e. creating a default value for a non-null field that had a null value)
1286     * @throws StorageException if the data is invalid or missing
1287     * @throws SQLException if an error occurred while filling in the fields
1288     * @since MMBase-1.8
1289     */
1290    protected void setBooleanValue(PreparedStatement statement, int index, Object   value, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1291        if (!setNullValue(statement, index, value, field, java.sql.Types.BOOLEAN)) {
1292            boolean bool = Casting.toBoolean(value);
1293            statement.setBoolean(index, bool);
1294            node.storeValue(field.getName(),Boolean.valueOf(bool));
1295        }
1296    }
1297
1298    /**
1299     * Store a Date value of a field in a prepared statement.
1300     * The method uses the Casting class to convert to the appropriate value.
1301     * Null values are stored as NULL if possible, otherwise they are stored as the date 31/12/1969 23:59:59 GMT (-1)
1302     * TODO: I think that is -1000, not -1.
1303     *
1304     * Override this method if you use another way to store dates
1305     * @param statement the prepared statement
1306     * @param index the index of the field in the prepared statement
1307     * @param value the data (date) to store
1308     * @param field the MMBase field, containing meta-information
1309     * @param node the node that contains the data. Used to update this node if the database layer makes changes
1310     *             to the data (i.e. creating a default value for a non-null field that had a null value)
1311     * @throws StorageException if the data is invalid or missing
1312     * @throws SQLException if an error occurred while filling in the fields
1313     * @since MMBase-1.8
1314     */
1315    protected void setDateTimeValue(PreparedStatement statement, int index, Object   value, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1316        if (!setNullValue(statement, index, value, field, java.sql.Types.TIMESTAMP)) {
1317            java.util.Date   date = Casting.toDate(value);
1318            long time = date.getTime();
1319            // The driver will interpret the date object and convert it to the default timezone when storing.
1320
1321            // undo that..
1322            if (log.isDebugEnabled()) {
1323                log.debug("Setting time " + date);
1324                log.debug("Converting with defaultTime Zone  " + new java.util.Date  (time - factory.getTimeZoneOffset(time)));
1325                log.debug("Offset with MMBase setting " + factory.getMMBase().getTimeZone().getOffset(time));
1326            }
1327            statement.setTimestamp(index, new Timestamp(time - factory.getTimeZoneOffset(time)));
1328            node.storeValue(field.getName(), date);
1329        }
1330    }
1331
1332    /**
1333     * Store a List value of a field in a prepared statement.
1334     * The method uses the Casting class to convert to the appropriate value.
1335     * Null values are stored as NULL if possible, otherwise they are stored as an empty list.
1336     * Override this method if you use another way to store lists
1337     * @param statement the prepared statement
1338     * @param index the index of the field in the prepared statement
1339     * @param value the data (List) to store
1340     * @param field the MMBase field, containing meta-information. This value can be null
1341     * @param node the node that contains the data. Used to update this node if the database layer makes changes
1342     *             to the data (i.e. creating a default value for a non-null field that had a null value)
1343     * @throws StorageException if the data is invalid or missing
1344     * @throws SQLException if an error occurred while filling in the fields
1345     * @since MMBase-1.8
1346     */
1347    protected void setListValue(PreparedStatement statement, int index, Object   value, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1348        if (!setNullValue(statement, index, value, field, java.sql.Types.ARRAY)) {
1349            List list = Casting.toList(value);
1350            statement.setObject(index, list);
1351            node.storeValue(field.getName(), list);
1352        }
1353    }
1354
1355    /**
1356     * Store binary data of a field in a prepared statement.
1357     * This basic implementation uses a binary stream to set the data.
1358     * Null values are stored as NULL if possible, otherwise they are stored as an empty byte-array.
1359     * Override this method if you use another way to store binaries (i.e. Blobs).
1360     * @param statement the prepared statement
1361     * @param index the index of the field in the prepared statement
1362     * @param objectValue the data (byte array) to store
1363     * @param field the MMBase field, containing meta-information
1364     * @param node the node that contains the data. Used to update this node if the database layer makes changes
1365     *             to the data (i.e. creating a default value for a non-null field that had a null value)
1366     * @throws StorageException if the data is invalid or missing
1367     * @throws SQLException if an error occurred while filling in the fields
1368     */
1369    protected void setBinaryValue(PreparedStatement statement, int index, Object   objectValue, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1370        if (log.isDebugEnabled()) {
1371            log.debug("Setting inputstream bytes into field " + field);
1372        }
1373        if (!setNullValue(statement, index, objectValue, field, java.sql.Types.VARBINARY)) {
1374            log.debug("Didn't set null");
1375            InputStream stream = Casting.toInputStream(objectValue);
1376            long size = -1;
1377            if (objectValue instanceof byte[]) {
1378                size = ((byte[])objectValue).length;
1379            } else {
1380                size = node.getSize(field.getName());
1381            }
1382            log.debug("Setting " + size + " bytes for inputstream");
1383            try {
1384                statement.setBinaryStream(index, stream, (int) size);
1385                stream.close();
1386            } catch (IOException ie) {
1387                throw new StorageException(ie);
1388            }
1389        }
1390    }
1391
1392    /**
1393     * Store the text value of a field in a prepared statement.
1394     * Null values are stored as NULL if possible, otherwise they are stored as an empty string.
1395     * If the FORCE_ENCODE_TEXT option is set, text is encoded (using the MMBase encoding) to a byte array
1396     * and stored as a binary stream.
1397     * Otherwise it uses {@link PreparedStatement#setString(int, String)} to set the data.
1398     * Override this method if you use another way to store large texts (i.e. Clobs).
1399     * @param statement the prepared statement
1400     * @param index the index of the field in the prepared statement
1401     * @param objectValue the text to store
1402     * @param field the MMBase field, containing meta-information
1403     * @param node the node that contains the data.
1404     * @throws StorageException if the data is invalid or missing
1405     * @throws SQLException if an error occurred while filling in the fields
1406     */
1407    protected Object   setStringValue(PreparedStatement statement, int index, Object   objectValue, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1408
1409        if (setNullValue(statement, index, objectValue, field, java.sql.Types.VARCHAR)) return objectValue;
1410        String   value = Casting.toString(objectValue);
1411        if (factory.getSetSurrogator() != null) {
1412            value = factory.getSetSurrogator().transform(value);
1413        }
1414        String   encoding = factory.getMMBase().getEncoding();
1415        // Store data as a binary stream when the code is a clob or blob, or
1416        // when database-force-encode-text is true.
1417        if (field.getStorageType() == Types.CLOB || field.getStorageType() == Types.BLOB || factory.hasOption(Attributes.FORCE_ENCODE_TEXT)) {
1418            byte[] rawchars = null;
1419            try {
1420                if (encoding.equalsIgnoreCase("ISO-8859-1") && factory.hasOption(Attributes.LIE_CP1252)) {
1421                    encoding = "CP1252";
1422                } else {
1423                }
1424                rawchars = value.getBytes(encoding);
1425                ByteArrayInputStream stream = new ByteArrayInputStream(rawchars);
1426                statement.setBinaryStream(index, stream, rawchars.length);
1427                stream.close();
1428            } catch (IOException ie) {
1429                throw new StorageException(ie);
1430            }
1431        } else {
1432            String   setValue = value;
1433            if (factory.hasOption(Attributes.LIE_CP1252)) {
1434                try {
1435                    if (encoding.equalsIgnoreCase("ISO-8859-1")) {
1436                        log.info("Lying CP-1252");
1437                        encoding = "CP1252";
1438                        setValue = new String  (value.getBytes("CP1252"), "ISO-8859-1");
1439                    } else {
1440                    }
1441                } catch(java.io.UnsupportedEncodingException   uee) {
1442                    // cannot happen
1443                }
1444            } else {
1445            }
1446            statement.setString(index, setValue);
1447
1448        }
1449        if (value != null) {
1450            if (! encoding.equalsIgnoreCase("UTF-8")) {
1451                try {
1452                    value = new String  (value.getBytes(encoding), encoding);
1453                } catch(java.io.UnsupportedEncodingException   uee) {
1454                    log.error(uee);
1455                    // cannot happen
1456                }
1457            }
1458
1459            // execute also getSurrogator, to make sure that it does not confuse, and the node contains what it would contain if fetched from database.
1460            if (factory.getGetSurrogator() != null) {
1461                value = factory.getGetSurrogator().transform(value);
1462            }
1463            if (factory.hasOption(Attributes.TRIM_STRINGS)) {
1464                value = value.trim();
1465            }
1466        }
1467
1468
1469        if (objectValue == null) node.storeValue(field.getName(), value);
1470
1471        return value;
1472    }
1473
1474    /**
1475     * This default implementation calls {@link #setStringValue}.
1476     * Override this method if you want to override this behavior.
1477     * @since MMBase-1.7.1
1478     */
1479    protected void setXMLValue(PreparedStatement statement, int index, Object   objectValue, CoreField field, MMObjectNode node) throws StorageException, SQLException {
1480        if (objectValue == null) {
1481            if(field.isNotNull()) {
1482                objectValue = "<p/>";
1483            }
1484        }
1485        objectValue = Casting.toXML(objectValue);
1486        if (objectValue != null) {
1487            objectValue = org.mmbase.util.xml.XMLWriter.write((org.w3c.dom.Document  ) objectValue, false, true);
1488        }
1489        node.storeValue(field.getName(), objectValue);
1490        setStringValue(statement, index, objectValue, field, node);
1491    }
1492
1493    // javadoc is inherited
1494    public void delete(MMObjectNode node) throws StorageException {
1495        // determine parent
1496        if (node.hasRelations()) {
1497            throw new StorageException("cannot delete node " + node.getNumber() + ", it still has relations");
1498        }
1499        delete(node, node.getBuilder());
1500        commitChange(node, "d");
1501    }
1502
1503    /**
1504     * Delete a node from a specific builder
1505     * This method makes it easier to implement relational databses, where you may need to remove the node
1506     * in more than one builder.
1507     * Call this method for all involved builders if you use a relational database.
1508     * @param node The node to delete
1509     * @throws StorageException if an error occurred during delete
1510     */
1511    protected void delete(MMObjectNode node, MMObjectBuilder builder) throws StorageException {
1512        List blobFileField = new ArrayList();
1513        List builderFields = builder.getFields(NodeManager.ORDER_CREATE);
1514        for (Iterator f = builderFields.iterator(); f.hasNext();) {
1515            CoreField field = (CoreField)f.next();
1516            if (field.inStorage()) {
1517                if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE) && (field.getType() == Field.TYPE_BINARY)) {
1518                    blobFileField.add(field);
1519                }
1520            }
1521        }
1522        String   tablename = (String  ) factory.getStorageIdentifier(builder);
1523        delete(node, builder, blobFileField, tablename);
1524    }
1525
1526    protected void delete(MMObjectNode node, MMObjectBuilder builder, List blobFileField, String   tablename) {
1527        try {
1528            Scheme scheme = factory.getScheme(Schemes.DELETE_NODE, Schemes.DELETE_NODE_DEFAULT);
1529            String   query = scheme.format(new Object  [] { this, tablename, builder.getField("number"), node });
1530            getActiveConnection();
1531            Statement s = activeConnection.createStatement();
1532            long startTime = getLogStartTime();
1533            s.executeUpdate(query);
1534            s.close();
1535            logQuery(query, startTime);
1536            // delete blob files too
1537            for (Iterator f = blobFileField.iterator(); f.hasNext();) {
1538                CoreField field = (CoreField)f.next();
1539                String   fieldName = field.getName();
1540                File binaryFile = getBinaryFile(node, fieldName);
1541                File checkedFile = checkFile(binaryFile, node, field);
1542                if (checkedFile == null) {
1543                    if (field.isNotNull()) {
1544                        log.warn("Could not find blob for field to delete '" + fieldName + "' of node " + node.getNumber() + ": " + binaryFile);
1545                    } else {
1546                        // ok, value was probably simply 'null'.
1547                    }
1548                } else if (! checkedFile.delete()) {
1549                    log.warn("Could not delete '" + checkedFile + "'");
1550                } else {
1551                    log.debug("Deleted '" + checkedFile + "'");
1552                }
1553            }
1554        } catch (SQLException se) {
1555            throw new StorageException(se);
1556        } finally {
1557            releaseActiveConnection();
1558        }
1559    }
1560
1561    // javadoc is inherited
1562    public MMObjectNode getNode(final MMObjectBuilder builder, final int number) throws StorageException {
1563        if (builder == null) throw new IllegalArgumentException  ("Builder cannot be null when requesting node " + number);
1564        Scheme scheme = factory.getScheme(Schemes.SELECT_NODE, Schemes.SELECT_NODE_DEFAULT);
1565        try {
1566            // create a new node (must be done before acquiring the connection, because this code might need a connection)
1567            MMObjectNode node = builder.getEmptyNode("system");
1568
1569            getActiveConnection();
1570            // get a builders fields
1571            List builderFields = builder.getFields(NodeManager.ORDER_CREATE);
1572            StringBuffer   fieldNames = null;
1573            for (Iterator f = builderFields.iterator(); f.hasNext();) {
1574                CoreField field = (CoreField)f.next();
1575                if (field.inStorage()) {
1576                    if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE) && (field.getType() == Field.TYPE_BINARY)) {
1577                        continue;
1578                    }
1579                    if (field.getType() == Field.TYPE_BINARY) {
1580                        continue;
1581                    }
1582                    // store the fieldname and the value parameter
1583                    String   fieldName = (String  )factory.getStorageIdentifier(field);
1584                    if (fieldNames == null) {
1585                        fieldNames = new StringBuffer  (fieldName);
1586                    } else {
1587                        fieldNames.append(',').append(fieldName);
1588                    }
1589                }
1590            }
1591            String   query = scheme.format(new Object  [] { this, builder, fieldNames.toString(), builder.getField("number"), new Integer  (number)});
1592            Statement s = activeConnection.createStatement();
1593            ResultSet result = null;
1594            try {
1595                result = s.executeQuery(query);
1596                fillNode(node, result, builder);
1597            } finally {
1598                if (result != null) result.close();
1599                s.close();
1600            }
1601            return node;
1602        } catch (SQLException se) {
1603            throw new StorageException(se);
1604        } finally {
1605            releaseActiveConnection();
1606        }
1607    }
1608
1609
1610    /**
1611     * Reloads the data from a node from the database.
1612     * Use this after a create or change action, so the data in memory is consistent with
1613     * any data stored in the database.
1614     * @param node the node to refresh
1615     */
1616    protected void refresh(MMObjectNode node) throws StorageException {
1617        Scheme scheme = factory.getScheme(Schemes.SELECT_NODE, Schemes.SELECT_NODE_DEFAULT);
1618        try {
1619            getActiveConnection();
1620            MMObjectBuilder builder = node.getBuilder();
1621            // get a builders fields
1622            List builderFields = builder.getFields(NodeManager.ORDER_CREATE);
1623            StringBuffer   fieldNames = null;
1624            for (Iterator f = builderFields.iterator(); f.hasNext();) {
1625                CoreField field = (CoreField)f.next();
1626                if (field.inStorage()) {
1627                    if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE) && (field.getType() == Field.TYPE_BINARY)) {
1628                        continue;
1629                    }
1630                    // store the fieldname and the value parameter
1631                    String   fieldName = (String  )factory.getStorageIdentifier(field);
1632                    if (fieldNames == null) {
1633                        fieldNames = new StringBuffer  (fieldName);
1634                    } else {
1635                        fieldNames.append(',').append(fieldName);
1636                    }
1637                }
1638            }
1639            String   query = scheme.format(new Object  [] { this, builder, fieldNames.toString(), builder.getField("number"), new Integer  (node.getNumber())});
1640            Statement s = activeConnection.createStatement();
1641            ResultSet result = null;
1642            try {
1643                result = s.executeQuery(query);
1644                fillNode(node, result, builder);
1645            } finally {
1646                if (result != null) result.close();
1647                s.close();
1648            }
1649        } catch (SQLException se) {
1650            throw new StorageException(se);
1651        } finally {
1652            releaseActiveConnection();
1653        }
1654    }
1655
1656    /**
1657     * Fills a single Node from the resultset of a query.
1658     * You can use this method to iterate through a query, creating multiple nodes, provided the resultset still contains
1659     * members (that is, <code>result.isAfterLast</code> returns <code>false</code>)
1660     * @param node The MMObjectNode to be filled
1661     * @param result the resultset
1662     * @param builder the builder to use for creating the node
1663     * @throws StorageException if the resultset is exhausted or a database error occurred
1664     */
1665    protected void fillNode(MMObjectNode node, ResultSet result, MMObjectBuilder builder) throws StorageException {
1666        try {
1667            if ((result != null) && result.next()) {
1668
1669                // iterate through all a builder's fields, and retrieve the value for that field
1670                // Note that if we would do it the other way around (iterate through the recordset's fields)
1671                // we might get inconsistencies if we 'remap' fieldnames that need not be mapped.
1672                // this also guarantees the number field is set first, which we  may need when retrieving blobs
1673                // from disk
1674                for (Iterator i = builder.getFields(NodeManager.ORDER_CREATE).iterator(); i.hasNext();) {
1675                    CoreField field = (CoreField)i.next();
1676                    if (field.inStorage()) {
1677                        Object   value;
1678                        if (field.getType() == Field.TYPE_BINARY && factory.hasOption(Attributes.STORES_BINARY_AS_FILE)) {
1679                            value =  getBlobFromFile(node, field, true);
1680                            if (value == BLOB_SHORTED) value = MMObjectNode.VALUE_SHORTED;
1681                        } else if (field.getType() == Field.TYPE_BINARY) {
1682                            // it is never in the resultset that came from the database
1683                            value = MMObjectNode.VALUE_SHORTED;
1684                        } else {
1685                            String   id = (String  )factory.getStorageIdentifier(field);
1686                            value = getValue(result, result.findColumn(id), field, true);
1687                        }
1688                        if (value == null) {
1689                            node.storeValue(field.getName(), null);
1690                        } else {
1691                            node.storeValue(field.getName(), value);
1692                        }
1693                    }
1694                }
1695                // clear the changed signal on the node
1696                node.clearChanged();
1697                return;
1698            } else {
1699                throw new StorageNotFoundException("Statement " + result.getStatement() + " (to fetch a Node) did not result anything");
1700            }
1701        } catch (SQLException se) {
1702            throw new StorageException(se);
1703        }
1704    }
1705
1706    /**
1707     * Attempts to return a single field value from the resultset of a query.
1708     * @todo This method is called from the search query code and therefor needs to be public.
1709     *       Perhaps code from searchquery should be moved to storage.
1710     * @param result the resultset
1711     * @param index the index of the field in the resultset
1712     * @param field the expected MMBase field type. This can be null
1713     * @param mayShorten Whether it would suffice to return only a 'shorted' version of the value.
1714     * @return the value
1715     * @throws StorageException if the value cannot be retrieved from the resultset
1716     */
1717
1718    public Object   getValue(ResultSet result, int index, CoreField field, boolean mayShorten) throws StorageException {
1719        try {
1720            int dbtype = Field.TYPE_UNKNOWN;
1721            if (field != null) {
1722                dbtype = field.getType();
1723            } else { // use database type.as
1724                dbtype = getJDBCtoField(result.getMetaData().getColumnType(index), dbtype);
1725            }
1726
1727            switch (dbtype) {
1728                // string-type fields
1729            case Field.TYPE_XML :
1730                return getXMLValue(result, index, field, mayShorten);
1731            case Field.TYPE_STRING :
1732                return getStringValue(result, index, field, mayShorten);
1733            case Field.TYPE_BINARY :
1734                Blob b =  getBlobValue(result, index, field, mayShorten);
1735                if (b == BLOB_SHORTED) return MMObjectNode.VALUE_SHORTED;
1736                if (b == null) return null;
1737                return b.getBytes(1L, (int) b.length());
1738            case Field.TYPE_DATETIME :
1739                return getDateTimeValue(result, index, field);
1740            case Field.TYPE_BOOLEAN :
1741                return getBooleanValue(result, index, field);
1742            case Field.TYPE_INTEGER :
1743            case Field.TYPE_NODE :
1744                Object   o = result.getObject(index);
1745                if (o instanceof Integer  ) {
1746                    return o;
1747                } else if (o instanceof Number  ) {
1748                    return new Integer  (((Number  )o).intValue());
1749                } else {
1750                    return o;
1751                }
1752            default :
1753                return result.getObject(index);
1754            }
1755        } catch (SQLException se) {
1756            throw new StorageException(se);
1757        }
1758    }
1759
1760    // javadoc is inherited
1761    public int getNodeType(int number) throws StorageException {
1762        Integer   numberValue = new Integer  (number);
1763        Integer   otypeValue = (Integer  )typeCache.get(numberValue);
1764        if (otypeValue != null) {
1765            return otypeValue.intValue();
1766        } else {
1767            Scheme scheme = factory.getScheme(Schemes.SELECT_NODE_TYPE, Schemes.SELECT_NODE_TYPE_DEFAULT);
1768            try {
1769                getActiveConnection();
1770                MMBase mmbase = factory.getMMBase();
1771                String   query = scheme.format(new Object  [] { this, mmbase, mmbase.getTypeDef().getField("number"), numberValue });
1772                Statement s = activeConnection.createStatement();
1773                long startTime = System.currentTimeMillis();
1774                try {
1775                    ResultSet result = s.executeQuery(query);
1776                    if (result != null) {
1777                        try {
1778                            if (result.next()) {
1779                                int retval = result.getInt(1);
1780                                typeCache.put(numberValue, new Integer  (retval));
1781                                return retval;
1782                            } else {
1783                                return -1;
1784                            }
1785                        } finally {
1786                            result.close();
1787                        }
1788                    } else {
1789                        return -1;
1790                    }
1791                } finally {
1792                    logQuery(query, startTime);
1793                    s.close();
1794                }
1795            } catch (SQLException se) {
1796                throw new StorageException(se);
1797            } finally {
1798                releaseActiveConnection();
1799            }
1800        }
1801    }
1802
1803    /**
1804     * Returns whether tables inherit fields form parent tables.
1805     * this determines whether fields that are inherited in mmbase builders
1806     * are redefined in the database tables.
1807     */
1808    protected boolean tablesInheritFields() {
1809        return true;
1810    }
1811
1812    /**
1813     * Determines whether the storage should make a field definition in a builder table for a
1814     * specified field.
1815     */
1816    protected boolean isPartOfBuilderDefinition(CoreField field) {
1817        // persistent field?
1818        // skip binary fields when values are written to file
1819        boolean isPart = field.inStorage() && (field.getType() != Field.TYPE_BINARY || !factory.hasOption(Attributes.STORES_BINARY_AS_FILE));
1820        // also, if the database is OO, and the builder has a parent,
1821        // skip fields that are in the parent builder
1822        MMObjectBuilder parentBuilder = field.getParent().getParentBuilder();
1823        if (isPart && parentBuilder != null) {
1824            isPart = !tablesInheritFields() || parentBuilder.getField(field.getName()) == null;
1825        }
1826        return isPart;
1827    }
1828
1829    // javadoc is inherited
1830    public void create(MMObjectBuilder builder) throws StorageException {
1831        log.debug("Creating a table for " + builder);
1832        // use the builder to get the fields and create a
1833        // valid create SQL string
1834        // for backward compatibility, fields are to be created in the order defined
1835        List fields = builder.getFields(NodeManager.ORDER_CREATE);
1836        log.debug("found fields " + fields);
1837
1838        List tableFields = new ArrayList();
1839        for (Iterator f = fields.iterator(); f.hasNext();) {
1840            CoreField field = (CoreField)f.next();
1841            if (isPartOfBuilderDefinition(field)) {
1842                tableFields.add(field);
1843            }
1844        }
1845        String   tableName = (String  ) factory.getStorageIdentifier(builder);
1846        createTable(builder, tableFields, tableName);
1847        if (!isVerified(builder)) {
1848            verify(builder);
1849        }
1850    }
1851
1852    protected void createTable(MMObjectBuilder builder, List tableFields, String   tableName) {
1853        StringBuffer   createFields = new StringBuffer  ();
1854        StringBuffer   createIndices = new StringBuffer  ();
1855        StringBuffer   createFieldsAndIndices = new StringBuffer  ();
1856        StringBuffer   createConstraints = new StringBuffer  ();
1857        // obtain the parentBuilder
1858        MMObjectBuilder parentBuilder = builder.getParentBuilder();
1859        Scheme rowtypeScheme;
1860        Scheme tableScheme;
1861        // if the builder has no parent, it is an object table,
1862        // so use CREATE_OBJECT_ROW_TYPE and CREATE_OBJECT_TABLE schemes.
1863        // Otherwise use CREATE_ROW_TYPE and CREATE_TABLE schemes.
1864        //
1865        if (parentBuilder == null) {
1866            rowtypeScheme = factory.getScheme(Schemes.CREATE_OBJECT_ROW_TYPE);
1867            tableScheme = factory.getScheme(Schemes.CREATE_OBJECT_TABLE, Schemes.CREATE_OBJECT_TABLE_DEFAULT);
1868        } else {
1869            rowtypeScheme = factory.getScheme(Schemes.CREATE_ROW_TYPE);
1870            tableScheme = factory.getScheme(Schemes.CREATE_TABLE, Schemes.CREATE_TABLE_DEFAULT);
1871        }
1872
1873        for (Iterator f = tableFields.iterator(); f.hasNext();) {
1874            try {
1875                CoreField field = (CoreField)f.next();
1876                // convert a fielddef to a field SQL createdefinition
1877                String   fieldDef = getFieldDefinition(field);
1878                if (createFields.length() > 0) {
1879                    createFields.append(", ");
1880                }
1881                createFields.append(fieldDef);
1882                // test on other indices
1883                String   constraintDef = getConstraintDefinition(field);
1884                if (constraintDef != null) {
1885                    // note: the indices are prefixed with a comma, as they generally follow the fieldlist.
1886                    // if the database uses rowtypes, however, fields are not included in the CREATE TABLE statement,
1887                    // and the comma should not be prefixed.
1888                    if (rowtypeScheme == null || createIndices.length() > 0) {
1889                        createIndices.append(", ");
1890                    }
1891
1892                    createIndices.append(constraintDef);
1893                    if (createFieldsAndIndices.length() > 0) {
1894                        createFieldsAndIndices.append(", ");
1895                    }
1896                    createFieldsAndIndices.append(fieldDef + ", " + constraintDef);
1897                } else {
1898                    if (createFieldsAndIndices.length() > 0) {
1899                        createFieldsAndIndices.append(", ");
1900                    }
1901                    createFieldsAndIndices.append(fieldDef);
1902                }
1903            } catch (StorageException se) {
1904                // if something wrong with one field, don't fail the complete table.
1905                log.error("" + se.getMessage(), se);
1906            }
1907        }
1908        String   query = "";
1909        try {
1910            getActiveConnection();
1911            // create a rowtype, if a scheme has been given
1912            // Note that creating a rowtype is optional
1913            if (rowtypeScheme != null) {
1914                query = rowtypeScheme.format(new Object  [] { this, tableName, createFields.toString(), parentBuilder });
1915                // remove parenthesis with empty field definitions -
1916                // unfortunately Schems don't take this into account
1917                if (factory.hasOption(Attributes.REMOVE_EMPTY_DEFINITIONS)) {
1918                    query = query.replaceAll("\\(\\s*\\)", "");
1919                }
1920                Statement s = activeConnection.createStatement();
1921                long startTime = getLogStartTime();
1922                s.executeUpdate(query);
1923                s.close();
1924                logQuery(query, startTime);
1925            }
1926            // create the table
1927            query = tableScheme.format(new Object  [] { this, tableName, createFields.toString(), createIndices.toString(), createFieldsAndIndices.toString(), createConstraints.toString(), parentBuilder, factory.getDatabaseName() });
1928            // remove parenthesis with empty field definitions -
1929            // unfortunately Schemes don't take this into account
1930            if (factory.hasOption(Attributes.REMOVE_EMPTY_DEFINITIONS)) {
1931                query = query.replaceAll("\\(\\s*\\)", "");
1932            }
1933
1934            Statement s = activeConnection.createStatement();
1935            long startTime = getLogStartTime();
1936            s.executeUpdate(query);
1937            s.close();
1938            logQuery(query, startTime);
1939
1940            tableNameCache.add(tableName.toUpperCase());
1941
1942            // create indices and unique constraints
1943            for (Iterator i = builder.getStorageConnector().getIndices().values().iterator(); i.hasNext();) {
1944                Index index = (Index)i.next();
1945                create(index);
1946            }
1947
1948        } catch (SQLException se) {
1949            throw new StorageException(se.getMessage() + " in query:" + query, se);
1950        } finally {
1951            releaseActiveConnection();
1952        }
1953    }
1954
1955    /**
1956     * Creates a field type definition, of the format '[fieldtype] NULL' or
1957     * '[fieldtype] NOT NULL' (depending on whether the field is nullable).
1958     * The fieldtype is taken from the type mapping in the factory.
1959     * @since MMBase-1.8
1960     * @param field the field
1961     * @return the typedefiniton as a String
1962     * @throws StorageException if the field type cannot be mapped
1963     */
1964    protected String   getFieldTypeDefinition(CoreField field) throws StorageException {
1965        // create the type mapping to search for
1966        String   typeName = Fields.getTypeDescription(field.getType());
1967        int size = field.getMaxLength();
1968        TypeMapping mapping = new TypeMapping();
1969        mapping.name = typeName;
1970        mapping.setFixedSize(size);
1971        // search type mapping
1972        List typeMappings = factory.getTypeMappings();
1973        int found = typeMappings.indexOf(mapping);
1974        if (found > -1) {
1975            String   fieldDef = ((TypeMapping)typeMappings.get(found)).getType(size);
1976            if (field.isNotNull()) {
1977                fieldDef += " NOT NULL";
1978            }
1979            return fieldDef;
1980        } else {
1981            throw new StorageException("Type for field " + field.getName() + ": " + typeName + " (" + size + ") undefined.");
1982        }
1983    }
1984
1985    /**
1986     * Creates a fielddefinition, of the format '[fieldname] [fieldtype] NULL' or
1987     * '[fieldname] [fieldtype] NOT NULL' (depending on whether the field is nullable).
1988     * The fieldtype is taken from the type mapping in the factory.
1989     * @param field the field
1990     * @return the typedefiniton as a String
1991     * @throws StorageException if the field type cannot be mapped
1992     */
1993    protected String   getFieldDefinition(CoreField field) throws StorageException {
1994        return factory.getStorageIdentifier(field) + " " + getFieldTypeDefinition(field);
1995    }
1996
1997    /**
1998     * Creates an index definition string for a field to be passed when creating a table.
1999     * @param field the field for which to make the index definition
2000     * @return the index definition as a String, or <code>null</code> if no definition is available
2001     */
2002    protected String   getConstraintDefinition(CoreField field) throws StorageException {
2003        String   definitions = null;
2004        Scheme scheme = null;
2005        if (field.getName().equals("number")) {
2006            scheme = factory.getScheme(Schemes.CREATE_PRIMARY_KEY, Schemes.CREATE_PRIMARY_KEY_DEFAULT);
2007            if (scheme != null) {
2008                definitions = scheme.format(new Object  [] { this, field.getParent(), field, factory.getMMBase() });
2009            }
2010        } else {
2011            // the field is unique: create a unique key for it
2012            if (field.isUnique()) {
2013                scheme = factory.getScheme(Schemes.CREATE_UNIQUE_KEY, Schemes.CREATE_UNIQUE_KEY_DEFAULT);
2014                if (scheme != null) {
2015                    definitions = scheme.format(new Object  [] { this, field.getParent(), field, field });
2016                }
2017            }
2018            if (field.getType() == Field.TYPE_NODE) {
2019                scheme = factory.getScheme(Schemes.CREATE_FOREIGN_KEY, Schemes.CREATE_FOREIGN_KEY_DEFAULT);
2020                if (scheme != null) {
2021                    Object   keyname = factory.getStorageIdentifier("" + field.getParent().getTableName() + "_" + field.getName() + "_FOREIGN");
2022                    String   definition = scheme.format(new Object  [] { this, field.getParent(), field, factory.getMMBase(), factory.getStorageIdentifier("number"), keyname});
2023                    if (definitions != null) {
2024                        definitions += ", " + definition;
2025                    } else {
2026                        definitions = definition;
2027                    }
2028                }
2029            }
2030        }
2031        return definitions;
2032    }
2033
2034    // javadoc is inherited
2035    public void change(MMObjectBuilder builder) throws StorageException {
2036        // test if you can make changes
2037        // iterate through the fields,
2038        // use metadata.getColumns(...)  to select fields
2039        //      (incl. name, datatype, size, null)
2040        // use metadata.getImportedKeys(...) to get foreign keys
2041        // use metadata.getIndexInfo(...) to get composite and other indices
2042        // determine changes and run them
2043        throw new StorageException("Operation not supported");
2044    }
2045
2046    // javadoc is inherited
2047    public synchronized void delete(MMObjectBuilder builder) throws StorageException {
2048        int size = size(builder);
2049        if (size != 0) {
2050            throw new StorageException("Can not drop builder, it still contains " + size + " node(s)");
2051        }
2052        try {
2053            getActiveConnection();
2054            Scheme scheme = factory.getScheme(Schemes.DROP_TABLE, Schemes.DROP_TABLE_DEFAULT);
2055            String   query = scheme.format(new Object  [] { this, builder });
2056            Statement s = activeConnection.createStatement();
2057            long startTime = getLogStartTime();
2058            s.executeUpdate(query);
2059            s.close();
2060            logQuery(query, startTime);
2061            scheme = factory.getScheme(Schemes.DROP_ROW_TYPE);
2062            if (scheme != null) {
2063                query = scheme.format(new Object  [] { this, builder });
2064                s = activeConnection.createStatement();
2065                long startTime2 = getLogStartTime();
2066                s.executeUpdate(query);
2067                s.close();
2068                logQuery(query, startTime2);
2069
2070                String   tableName = factory.getStorageIdentifier(builder).toString().toUpperCase();
2071                if(tableNameCache.contains(tableName)) {
2072                    tableNameCache.remove(tableName);
2073                }
2074            }
2075        } catch (Exception   e) {
2076            throw new StorageException(e.getMessage());
2077        } finally {
2078            releaseActiveConnection();
2079        }
2080    }
2081
2082    // javadoc is inherited
2083    public void create() throws StorageException {
2084        create(factory.getMMBase().getRootBuilder());
2085        createSequence();
2086    }
2087
2088    /**
2089     * Creates a means for the database to pre-create keys with increasing numbers.
2090     * A sequence can be a database routine, a number table, or anything else that can be used to create unique numbers.
2091     * Keys can be obtained from the sequence by calling {@link #createKey()}.
2092     * @throws StorageException when the sequence can not be created
2093     */
2094    protected void createSequence() throws StorageException {
2095        synchronized (sequenceKeys) {
2096            try {
2097                getActiveConnection();
2098                // create the type mapping to search for
2099                String   typeName = Fields.getTypeDescription(Field.TYPE_INTEGER);
2100                TypeMapping mapping = new TypeMapping();
2101                mapping.name = typeName;
2102                // search type mapping
2103                List typeMappings = factory.getTypeMappings();
2104                int found = typeMappings.indexOf(mapping);
2105                if (found == -1) {
2106                    throw new StorageException("Type " + typeName + " undefined.");
2107                }
2108                String   fieldName = (String  )factory.getStorageIdentifier("number");
2109                String   fieldDef = fieldName + " " + ((TypeMapping)typeMappings.get(found)).type + " NOT NULL, PRIMARY KEY(" + fieldName + ")";
2110                String   query;
2111                Statement s;
2112                Scheme scheme = factory.getScheme(Schemes.CREATE_SEQUENCE, Schemes.CREATE_SEQUENCE_DEFAULT);
2113                if (scheme != null) {
2114                    query = scheme.format(new Object  [] { this, fieldDef, factory.getDatabaseName()});
2115                    long startTime = getLogStartTime();
2116                    s = activeConnection.createStatement();
2117                    s.executeUpdate(query);
2118                    s.close();
2119                    logQuery(query, startTime);
2120                }
2121                scheme = factory.getScheme(Schemes.INIT_SEQUENCE, Schemes.INIT_SEQUENCE_DEFAULT);
2122                if (scheme != null) {
2123                    query = scheme.format(new Object  [] { this, factory.getStorageIdentifier("number"), new Integer  (1), bufferSize });
2124                    long startTime = getLogStartTime();
2125                    s = activeConnection.createStatement();
2126                    s.executeUpdate(query);
2127                    s.close();
2128                    logQuery(query, startTime);
2129                }
2130            } catch (SQLException se) {
2131                throw new StorageException(se);
2132            } finally {
2133                releaseActiveConnection();
2134            }
2135        }
2136    }
2137
2138    // javadoc is inherited
2139    public boolean exists(MMObjectBuilder builder) throws StorageException {
2140        boolean result = exists((String  )factory.getStorageIdentifier(builder));
2141        if (result) {
2142            if (!isVerified(builder)) {
2143                verify(builder);
2144            }
2145        }
2146        return result;
2147    }
2148
2149    /**
2150     * Queries the database metadata to test whether a given table exists.
2151     *
2152     * @param tableName name of the table to look for
2153     * @throws StorageException when the metadata could not be retrieved
2154     * @return <code>true</code> if the table exists
2155     */
2156    protected synchronized boolean exists(String   tableName) throws StorageException {
2157        if(tableNameCache == null) {
2158            try {
2159                tableNameCache = new HashSet();
2160                getActiveConnection();
2161                DatabaseMetaData metaData = activeConnection.getMetaData();
2162                String   prefixTablename = factory.getMMBase().getBaseName();
2163                if (metaData.storesLowerCaseIdentifiers()) {
2164                    prefixTablename = prefixTablename.toLowerCase();
2165                }
2166                if (metaData.storesUpperCaseIdentifiers()) {
2167                    prefixTablename = prefixTablename.toUpperCase();
2168                }
2169                ResultSet res = metaData.getTables(factory.getCatalog(), null, prefixTablename+"_%", new String  [] { "TABLE", "VIEW", "SEQUENCE" });
2170                try {
2171                    while(res.next()) {
2172                        if(! tableNameCache.add(res.getString(3).toUpperCase())) {
2173                            log.warn("builder already in cache(" + res.getString(3) + ")!");
2174                        }
2175                    }
2176                } finally {
2177                    res.close();
2178                }
2179
2180            } catch(Exception   e) {
2181                throw new StorageException(e.getMessage());
2182            } finally {
2183                releaseActiveConnection();
2184             }
2185        }
2186
2187        return tableNameCache.contains(tableName.toUpperCase());
2188    }
2189
2190    // javadoc is inherited
2191    public boolean exists() throws StorageException {
2192        return exists(factory.getMMBase().getRootBuilder());
2193    }
2194
2195    // javadoc is inherited
2196    public int size(MMObjectBuilder builder) throws StorageException {
2197        try {
2198            getActiveConnection();
2199            Scheme scheme = factory.getScheme(Schemes.GET_TABLE_SIZE, Schemes.GET_TABLE_SIZE_DEFAULT);
2200            String   query = scheme.format(new Object  [] { this, builder });
2201            Statement s = activeConnection.createStatement();
2202            ResultSet res = s.executeQuery(query);
2203            int retval;
2204            try {
2205                res.next();
2206                retval = res.getInt(1);
2207            } finally {
2208                res.close();
2209            }
2210            s.close();
2211            return retval;
2212        } catch (Exception   e) {
2213            throw new StorageException(e);
2214        } finally {
2215            releaseActiveConnection();
2216        }
2217    }
2218
2219    // javadoc is inherited
2220    public int size() throws StorageException {
2221        return size(factory.getMMBase().getRootBuilder());
2222    }
2223
2224    /**
2225     * Guess the (mmbase) type in storage using the JDBC type.
2226     * Because a JDBC type can represent more than one mmbase Type,
2227     * the current type is also passed - if the current type matches, that type
2228     * is returned, otherwise the method returns the closest matching MMBase type.
2229     */
2230    protected int getJDBCtoField(int jdbcType, int mmbaseType) {
2231        switch (jdbcType) {
2232        case Types.INTEGER :
2233        case Types.SMALLINT :
2234        case Types.TINYINT :
2235            if (mmbaseType == Field.TYPE_INTEGER || mmbaseType == Field.TYPE_NODE) {
2236                return mmbaseType;
2237            } else {
2238                return Field.TYPE_INTEGER;
2239            }
2240        case Types.BIGINT :
2241            if (mmbaseType == Field.TYPE_INTEGER || mmbaseType == Field.TYPE_LONG || mmbaseType == Field.TYPE_NODE) {
2242                return mmbaseType;
2243            } else {
2244                return Field.TYPE_LONG;
2245            }
2246        case Types.FLOAT :
2247        case Types.REAL :
2248            return Field.TYPE_FLOAT;
2249        case Types.DOUBLE :
2250        case Types.NUMERIC :
2251        case Types.DECIMAL :
2252            if (mmbaseType == Field.TYPE_FLOAT || mmbaseType == Field.TYPE_DOUBLE) {
2253                return mmbaseType;
2254            } else {
2255                return Field.TYPE_DOUBLE;
2256            }
2257        case Types.BINARY :
2258        case Types.LONGVARBINARY :
2259        case Types.VARBINARY :
2260        case Types.BLOB :
2261            if (mmbaseType == Field.TYPE_BINARY || mmbaseType == Field.TYPE_STRING || mmbaseType == Field.TYPE_XML) {
2262                return mmbaseType;
2263            } else {
2264                return Field.TYPE_BINARY;
2265            }
2266        case Types.CHAR :
2267        case Types.CLOB :
2268        case Types.LONGVARCHAR :
2269        case Types.VARCHAR :
2270            if (mmbaseType == Field.TYPE_STRING || mmbaseType == Field.TYPE_XML) {
2271                return mmbaseType;
2272            } else {
2273                return Field.TYPE_STRING;
2274            }
2275        case Types.BIT :
2276        case Types.BOOLEAN :
2277            return Field.TYPE_BOOLEAN;
2278        case Types.DATE :
2279        case Types.TIME :
2280        case Types.TIMESTAMP :
2281            return Field.TYPE_DATETIME;
2282        case Types.ARRAY :
2283            return Field.TYPE_LIST;
2284        case Types.JAVA_OBJECT :
2285        case Types.OTHER :
2286            if (mmbaseType == Field.TYPE_LIST) {
2287                return mmbaseType;
2288            }  else {
2289                return Field.TYPE_UNKNOWN;
2290            }
2291        default :
2292            return Field.TYPE_UNKNOWN;
2293        }
2294    }
2295
2296    /**
2297     * Check if builders are already verified with the database.
2298     * @param builder Builder which might be verified
2299     * @return <code>true</code> when already verified
2300     */
2301    public boolean isVerified(MMObjectBuilder builder) {
2302        return verifiedTablesCache.contains(builder.getTableName().toUpperCase());
2303    }
2304
2305    /**
2306     * Tests whether a builder and the table present in the database match.
2307     */
2308    public void verify(MMObjectBuilder builder) throws StorageException {
2309        try {
2310            getActiveConnection();
2311            String   tableName = (String  )factory.getStorageIdentifier(builder);
2312            DatabaseMetaData metaData = activeConnection.getMetaData();
2313            if (metaData.storesUpperCaseIdentifiers()) {
2314                tableName = tableName.toUpperCase();
2315            }
2316            // skip if does not support inheritance, or if this is the object table
2317            if (tablesInheritFields()) {
2318                MMObjectBuilder parent = builder.getParentBuilder();
2319                try {
2320                    ResultSet superTablesSet = metaData.getSuperTables(null, null, tableName);
2321                    try {
2322                        if (superTablesSet.next()) {
2323                            String   parentName = superTablesSet.getString("SUPERTABLE_NAME");
2324                            if (parent == null || !parentName.equalsIgnoreCase((String  )factory.getStorageIdentifier(parent))) {
2325                                log.error("VERIFY: parent builder in storage for builder " + builder.getTableName() + " should be " + parent.getTableName() + " but defined as " + parentName);
2326                            } else {
2327                                log.debug("VERIFY: parent builder in storage for builder " + builder.getTableName() + " defined as " + parentName);
2328                            }
2329                        } else if (parent != null) {
2330                            log.error("VERIFY: no parent builder defined in storage for builder " + builder.getTableName());
2331                        }
2332                    } finally {
2333                        superTablesSet.close();
2334                    }
2335                } catch (AbstractMethodError   ae) {
2336                    // ignore: the method is not implemented by the JDBC Driver
2337                    log.debug("VERIFY: Driver does not fully implement the JDBC 3.0 API, skipping inheritance consistency tests for " + tableName);
2338                } catch (UnsupportedOperationException   uoe) {
2339                    // ignore: the operation is not supported by the JDBC Driver
2340                    log.debug("VERIFY: Driver does not support all JDBC 3.0 methods, skipping inheritance consistency tests for " + tableName);
2341                } catch (SQLException se) {
2342                    // ignore: the method is likely not implemented by the JDBC Driver
2343                    // (should be one of the above errors, but postgresql returns this as an SQLException. Tsk.)
2344                    log.debug("VERIFY: determining super tables failed, skipping inheritance consistency tests for " + tableName);
2345                }
2346            }
2347            Map columns = new HashMap();
2348            ResultSet columnsSet = metaData.getColumns(null, null, tableName, null);
2349            try {
2350                // get column information
2351                while (columnsSet.next()) {
2352                    Map colInfo = new HashMap();
2353                    colInfo.put("DATA_TYPE", new Integer  (columnsSet.getInt("DATA_TYPE")));
2354                    colInfo.put("TYPE_NAME", columnsSet.getString("TYPE_NAME"));
2355                    colInfo.put("COLUMN_SIZE", new Integer  (columnsSet.getInt("COLUMN_SIZE")));
2356                    colInfo.put("NULLABLE", Boolean.valueOf(columnsSet.getInt("NULLABLE") != DatabaseMetaData.columnNoNulls));
2357                    columns.put(columnsSet.getString("COLUMN_NAME"), colInfo);
2358                }
2359            } finally {
2360                columnsSet.close();
2361            }
2362            // iterate through fields and check all fields present
2363            int pos = 0;
2364            List builderFields = builder.getFields(NodeManager.ORDER_CREATE);
2365            for (Iterator i = builderFields.iterator(); i.hasNext();) {
2366                CoreField field = (CoreField)i.next();
2367                if (field.inStorage() && (field.getType() != Field.TYPE_BINARY || !factory.hasOption(Attributes.STORES_BINARY_AS_FILE))) {
2368                    field.rewrite();
2369                    pos++;
2370                    Object   id = field.getStorageIdentifier();
2371                    Map colInfo = (Map)columns.get(id);
2372                    if ((colInfo == null)) {
2373
2374                        log.error("VERIFY: Field '" + field.getName() + "' " +
2375                                  (id.equals(field.getName()) ? "" : "(mapped to field '" + id + "') ") +
2376                                   "of builder '" + builder.getTableName() + "' does NOT exist in storage! Field will be considered virtual");
2377
2378                        // set field to virtual so it will not be stored -
2379                        // prevents future queries or statements from failing
2380                        field.setState(Field.STATE_VIRTUAL);
2381                    } else {
2382                        // compare type
2383                        int curtype = field.getType();
2384                        int storageType = ((Integer  )colInfo.get("DATA_TYPE")).intValue();
2385                        field.setStorageType(storageType);
2386                        int type = getJDBCtoField(storageType, curtype);
2387                        if (type != curtype) {
2388                            log.warn("VERIFY: Field '" + field.getName() + "' of builder '"
2389                                      + builder.getTableName() + "' mismatch : type defined as "
2390                                      + Fields.getTypeDescription(curtype)
2391                                      + ", but in storage " + Fields.getTypeDescription(type)
2392                                      + " (" + colInfo.get("TYPE_NAME") + "). Storage type will be used.");
2393                            // set the new type (keep the old datatype)
2394                            if (type == Field.TYPE_UNKNOWN) {
2395                                log.warn("Storage type = 'UNKNOWN', wil not fall back to _that_");
2396                            } else {
2397                                field.setType(type);
2398                            }
2399                        }
2400                        boolean nullable = ((Boolean  )colInfo.get("NULLABLE")).booleanValue();
2401                        if (nullable == field.isNotNull()) {
2402                            // only correct if storage is more restrictive
2403                            if (! nullable) {
2404                                field.setNotNull(!nullable);
2405                                log.warn("VERIFY: Field '" + field.getName() + "' of builder '" + builder.getTableName() + "' mismatch : notnull in storage is " + !nullable + " (value corrected for this session)");
2406                            } else {
2407                                log.debug("VERIFY: Field '" + field.getName() + "' of builder '" + builder.getTableName() + "' mismatch : notnull in storage is " + !nullable);
2408                            }
2409                        }
2410                        // compare size
2411                        int size = ((Integer  )colInfo.get("COLUMN_SIZE")).intValue();
2412                        int cursize = field.getMaxLength();
2413                        // ignore the size difference for large fields (generally blobs or memo texts)
2414                        // since most databases do not return accurate sizes for these fields
2415                        if (cursize != -1 && size > 0 && size != cursize && cursize <= 255) {
2416                            if (size < cursize) {
2417                                // only correct if storage is more restrictive
2418                                field.setMaxLength(size);
2419                                log.warn("VERIFY: Field '" + field.getName() + "' of builder '" + builder.getTableName() + "' mismatch : size defined as " + cursize + ", but in storage " + size + " (value corrected for this session)");
2420                            } else {
2421                                log.debug("VERIFY: Field '" + field.getName() + "' of builder '" + builder.getTableName() + "' mismatch : size defined as " + cursize + ", but in storage " + size);
2422                            }
2423                        }
2424                        columns.remove(id);
2425                    }
2426                    // lock the field now that it has been checked
2427                    // this prevents any accidental changes to the field.
2428                    field.finish();
2429                }
2430            }
2431            // if any are left, these fields were removed!
2432            for (Iterator i = columns.keySet().iterator(); i.hasNext();) {
2433                String   column = (String  )i.next();
2434                log.warn("VERIFY: Column '" + column + "' for builder '" + builder.getTableName() + "' in Storage but not defined!");
2435            }
2436        } catch (Exception   e) {
2437            log.error("Error during check of table (Assume table is correct.):" + e.getMessage());
2438            log.error(Logging.stackTrace(e));
2439        } finally {
2440            releaseActiveConnection();
2441        }
2442        verifiedTablesCache.add(builder.getTableName().toUpperCase());
2443    }
2444
2445    /**
2446     * Determines if an index exists.
2447     * You should have an active connection before calling this method.
2448     * @param index the index to test
2449     * @param tablename the tablename to test the index against
2450     * @throws StorageException when a database error occurs
2451     */
2452    protected boolean exists(Index index, String   tablename) {
2453        boolean result = false;
2454        try {
2455            DatabaseMetaData metaData = activeConnection.getMetaData();
2456            ResultSet indexSet = metaData.getIndexInfo(null, null, tablename, index.isUnique(), false);
2457            try {
2458                String   indexName = (String  )factory.getStorageIdentifier(index);
2459                while (!result && indexSet.next()) {
2460                    int indexType = indexSet.getInt("TYPE");
2461                    if (indexType != DatabaseMetaData.tableIndexStatistic) {
2462                        result = indexName.equalsIgnoreCase(indexSet.getString("INDEX_NAME"));
2463                    }
2464                }
2465            } finally {
2466                indexSet.close();
2467            }
2468        } catch (SQLException se) {
2469            throw new StorageException(se);
2470        }
2471        return result;
2472    }
2473
2474    /**
2475     * Determines if an index exists.
2476     * You should have an active connection before calling this method.
2477     * @param index the index to test
2478     * @throws StorageException when a database error occurs
2479     */
2480    protected boolean exists(Index index) throws StorageException {
2481        return exists(index, index.getParent().getTableName());
2482    }
2483
2484
2485    /**
2486     * Drop all constraints and indices that contain a specific field.
2487     * You should have an active connection before calling this method.
2488     * @param field the field for which to drop indices
2489     * @throws StorageException when a database error occurs
2490     */
2491    protected void deleteIndices(CoreField field) throws StorageException {
2492        for (Iterator i = field.getParent().getStorageConnector().getIndices().values().iterator(); i.hasNext();) {
2493            Index index = (Index)i.next();
2494            if (index.contains(field)) {
2495                delete(index);
2496            }
2497        }
2498    }
2499
2500    /**
2501     * Drop a constraint or index.
2502     * You should have an active connection before calling this method.
2503     * @param index the index to drop
2504     * @throws StorageException when a database error occurs
2505     */
2506    protected void delete(Index index) throws StorageException {
2507        Scheme deleteIndexScheme;
2508        if (index.isUnique()) {
2509            //  Scheme: DELETE_CONSTRAINT
2510            deleteIndexScheme = factory.getScheme(Schemes.DELETE_UNIQUE_INDEX, Schemes.DELETE_UNIQUE_INDEX_DEFAULT);
2511        } else {
2512            //  Scheme: DELETE_INDEX
2513            deleteIndexScheme = factory.getScheme(Schemes.DELETE_INDEX, Schemes.DELETE_INDEX_DEFAULT);
2514        }
2515        if (deleteIndexScheme != null && exists(index)) {
2516            // remove index
2517            String   query = null;
2518            try {
2519                Statement s = activeConnection.createStatement();
2520                query = deleteIndexScheme.format(new Object  [] { this, index.getParent(), index });
2521                long startTime = getLogStartTime();
2522                try {
2523                    s.executeUpdate(query);
2524                } finally {
2525                    s.close();
2526                }
2527                logQuery(query, startTime);
2528            } catch (SQLException se) {
2529                throw new StorageException(se.getMessage() + " in query:" + query, se);
2530            }
2531        }
2532    }
2533
2534    /**
2535     * Returns a comma seperated list of fieldnames for an index.
2536     * @param index the index to create it for
2537     * @return the field list definition as a String, or <code>null</code> if the index was empty, or
2538     *         if it consists of a composite index and composite indices are not supported.
2539     */
2540    protected String   getFieldList(Index index) {
2541        String   result = null;
2542        if (index.size() == 1 || factory.hasOption(Attributes.SUPPORTS_COMPOSITE_INDEX)) {
2543            StringBuffer   indexFields = new StringBuffer  ();
2544            for (Iterator f = index.iterator(); f.hasNext();) {
2545                CoreField field = (CoreField)f.next();
2546                if (indexFields.length() > 0) {
2547                    indexFields.append(", ");
2548                }
2549                indexFields.append(factory.getStorageIdentifier(field));
2550            }
2551            if (indexFields.length() > 0) {
2552                result = indexFields.toString();
2553            }
2554        }
2555        return result;
2556    }
2557
2558    /**
2559     * (Re)create all constraints and indices that contain a specific field.
2560     * You should have an active connection before calling this method.
2561     * @param field the field for which to create indices
2562     * @throws StorageException when a database error occurs
2563     */
2564    protected void createIndices(CoreField field) throws StorageException {
2565        for (Iterator i = field.getParent().getStorageConnector().getIndices().values().iterator(); i.hasNext();) {
2566            Index index = (Index)i.next();
2567            if (index.contains(field)) {
2568                create(index);
2569            }
2570        }
2571    }
2572
2573    /**
2574     * Create an index or a unique constraint.
2575     * @param index the index to create
2576     */
2577    protected void create(Index index) throws StorageException {
2578        String   tablename = (String  ) factory.getStorageIdentifier(index.getParent());
2579        createIndex(index, tablename);
2580    }
2581
2582    /**
2583     * Create an index or a unique constraint.
2584     * @param index the index to create
2585     * @param tablename name of the table
2586     */
2587    protected void createIndex(Index index, String   tablename) {
2588        Scheme createIndexScheme;
2589        if (index.isUnique()) {
2590            //  Scheme: CREATE_UNIQUE_INDEX
2591            createIndexScheme = factory.getScheme(Schemes.CREATE_UNIQUE_INDEX, Schemes.CREATE_UNIQUE_INDEX_DEFAULT);
2592        } else {
2593            //  Scheme: CREATE_INDEX
2594            createIndexScheme = factory.getScheme(Schemes.CREATE_INDEX, Schemes.CREATE_INDEX_DEFAULT);
2595        }
2596        // note: do not attempt to create an index if it already exists.
2597        if (createIndexScheme != null && !exists(index, tablename)) {
2598            String   fieldlist = getFieldList(index);
2599            if (fieldlist != null) {
2600                String   query = null;
2601                try {
2602                    Statement s = activeConnection.createStatement();
2603                    query = createIndexScheme.format(new Object  [] { this, tablename, fieldlist, index });
2604                    long startTime = getLogStartTime();
2605                    try {
2606                        s.executeUpdate(query);
2607                    } finally {
2608                        s.close();
2609                    }
2610                    logQuery(query, startTime);
2611                } catch (SQLException se) {
2612                    throw new StorageException(se.getMessage() + " in query:" + query, se);
2613                }
2614            }
2615        }
2616    }
2617
2618    // javadoc is inherited
2619    public void create(CoreField field) throws StorageException {
2620        if (field == null) throw new IllegalArgumentException  ("No field given");
2621        if (!factory.hasOption(Attributes.SUPPORTS_DATA_DEFINITION)) {
2622            throw new StorageException("Data definiton statements (create new field) are not supported.");
2623        }
2624        if (factory.getScheme(Schemes.CREATE_OBJECT_ROW_TYPE) != null) {
2625            throw new StorageException("Can not use data definiton statements (create new field) on row types.");
2626        }
2627        log.debug("Creating new field " + field);
2628        if (field.inStorage() && (field.getType() != Field.TYPE_BINARY || !factory.hasOption(Attributes.STORES_BINARY_AS_FILE))) {
2629            Scheme scheme = factory.getScheme(Schemes.CREATE_FIELD, Schemes.CREATE_FIELD_DEFAULT);
2630            if (scheme == null) {
2631                throw new StorageException("Storage layer does not support the dynamic creation of fields");
2632            } else {
2633                try {
2634                    getActiveConnection();
2635                    // add field
2636                    String   fieldTypeDef = getFieldTypeDefinition(field);
2637                    String   query = scheme.format(new Object  [] { this, field.getParent(), field, fieldTypeDef });
2638                    Statement s = activeConnection.createStatement();
2639                    long startTime = getLogStartTime();
2640                    s.executeUpdate(query);
2641                    s.close();
2642                    logQuery(query, startTime);
2643                    // add constraints
2644                    String   constraintDef = getConstraintDefinition(field);
2645                    if (constraintDef != null) {
2646                        scheme = factory.getScheme(Schemes.CREATE_CONSTRAINT, Schemes.CREATE_CONSTRAINT_DEFAULT);
2647                        if (scheme != null) {
2648                            query = scheme.format(new Object  [] { this, field.getParent(), constraintDef });
2649                            s = activeConnection.createStatement();
2650                            s.executeUpdate(query);
2651                            s.close();
2652                            logQuery(query, startTime);
2653                        }
2654                    }
2655                    deleteIndices(field);
2656                    createIndices(field);
2657                } catch (SQLException se) {
2658                    throw new StorageException(se);
2659                } finally {
2660                    releaseActiveConnection();
2661                }
2662            }
2663        }
2664    }
2665
2666    // javadoc is inherited
2667    public void change(CoreField field) throws StorageException {
2668        if (!factory.hasOption(Attributes.SUPPORTS_DATA_DEFINITION)) {
2669            throw new StorageException("Data definiton statements (change field) are not supported.");
2670        }
2671        if (factory.getScheme(Schemes.CREATE_OBJECT_ROW_TYPE) != null) {
2672            throw new StorageException("Can not use data definiton statements (change field) on row types.");
2673        }
2674        if (field.inStorage() && (field.getType() != Field.TYPE_BINARY || !factory.hasOption(Attributes.STORES_BINARY_AS_FILE))) {
2675            Scheme scheme = factory.getScheme(Schemes.CHANGE_FIELD, Schemes.CHANGE_FIELD_DEFAULT);
2676            if (scheme == null) {
2677                throw new StorageException("Storage layer does not support the dynamic changing of fields");
2678            } else {
2679                try {
2680                    getActiveConnection();
2681                    deleteIndices(field);
2682                    String   fieldTypeDef = getFieldTypeDefinition(field);
2683                    String   query = scheme.format(new Object  [] { this, field.getParent(), field, fieldTypeDef });
2684                    Statement s = activeConnection.createStatement();
2685                    long startTime = getLogStartTime();
2686                    s.executeUpdate(query);
2687                    s.close();
2688                    logQuery(query, startTime);
2689                    // add constraints
2690                    String   constraintDef = getConstraintDefinition(field);
2691                    if (constraintDef != null) {
2692                        scheme = factory.getScheme(Schemes.CREATE_CONSTRAINT, Schemes.CREATE_CONSTRAINT_DEFAULT);
2693                        if (scheme != null) {
2694                            query = scheme.format(new Object  [] { this, field.getParent(), constraintDef });
2695                            s = activeConnection.createStatement();
2696                            long startTime2 = getLogStartTime();
2697                            s.executeUpdate(query);
2698                            s.close();
2699                            logQuery(query, startTime2);
2700                        }
2701                    }
2702                    createIndices(field);
2703                } catch (SQLException se) {
2704                    throw new StorageException(se);
2705                } finally {
2706                    releaseActiveConnection();
2707                }
2708            }
2709        }
2710    }
2711
2712    // javadoc is inherited
2713    public void delete(CoreField field) throws StorageException {
2714        if (!factory.hasOption(Attributes.SUPPORTS_DATA_DEFINITION)) {
2715            throw new StorageException("Data definiton statements (delete field) are not supported.");
2716        }
2717        if (factory.getScheme(Schemes.CREATE_OBJECT_ROW_TYPE) != null) {
2718            throw new StorageException("Can not use data definiton statements (delete field) on row types.");
2719        }
2720        if (field.inStorage() && (field.getType() != Field.TYPE_BINARY || !factory.hasOption(Attributes.STORES_BINARY_AS_FILE))) {
2721            Scheme scheme = factory.getScheme(Schemes.DELETE_FIELD, Schemes.DELETE_FIELD_DEFAULT);
2722            if (scheme == null) {
2723                throw new StorageException("Storage layer does not support the dynamic deleting of fields");
2724            } else {
2725                try {
2726                    getActiveConnection();
2727                    deleteIndices(field);
2728                    String   query = scheme.format(new Object  [] { this, field.getParent(), field });
2729                    Statement s = activeConnection.createStatement();
2730                    long startTime = getLogStartTime();
2731                    s.executeUpdate(query);
2732                    s.close();
2733                    logQuery(query, startTime);
2734                    createIndices(field);
2735                } catch (SQLException se) {
2736                    throw new StorageException(se);
2737                } finally {
2738                    releaseActiveConnection();
2739                }
2740            }
2741        }
2742    }
2743
2744    /**
2745     * Convert legacy file
2746     * @return Number of converted fields. Or -1 if not storing binaries as files
2747     */
2748    public int convertLegacyBinaryFiles() throws org.mmbase.storage.search.SearchQueryException, SQLException {
2749        if (factory.hasOption(Attributes.STORES_BINARY_AS_FILE)) {
2750            synchronized(factory) { // there is only on factory. This makes sure that there is only one conversion running
2751                int result = 0;
2752                int fromDatabase = 0;
2753                Iterator builders = factory.getMMBase().getBuilders().iterator();
2754                while (builders.hasNext()) {
2755                    MMObjectBuilder builder = (MMObjectBuilder)builders.next();
2756                    // remove clusternodes from the convert
2757                    if (!builder.getSingularName().equals("clusternodes")) {
2758                        Iterator fields = builder.getFields().iterator();
2759                        while (fields.hasNext()) {
2760                            CoreField field = (CoreField)fields.next();
2761                            String   fieldName = field.getName();
2762                            if (field.getType() == Field.TYPE_BINARY) { // check all binaries
2763                                // check whether it might be in a column
2764                                boolean foundColumn = false;
2765                                try {
2766                                    getActiveConnection();
2767                                    String   tableName = (String  )factory.getStorageIdentifier(builder);
2768                                    DatabaseMetaData metaData = activeConnection.getMetaData();
2769                                    ResultSet columnsSet = metaData.getColumns(null, null, tableName, null);
2770                                    try {
2771                                        while (columnsSet.next()) {
2772                                            if (columnsSet.getString("COLUMN_NAME").equals(fieldName)) {
2773                                                foundColumn = true;
2774                                                break;
2775                                            }
2776                                        }
2777                                    } finally {
2778                                        columnsSet.close();
2779                                    }
2780                                } catch (java.sql.SQLException   sqe) {
2781                                    log.error(sqe.getMessage());
2782                                } finally {
2783                                    releaseActiveConnection();
2784                                }
2785                                List nodes = builder.getNodes(new org.mmbase.storage.search.implementation.NodeSearchQuery(builder));
2786                                log.service("Checking all " + nodes.size() + " nodes of '" + builder.getTableName() + "'");
2787                                Iterator i = nodes.iterator();
2788                                while (i.hasNext()) {
2789                                    MMObjectNode node = (MMObjectNode)i.next();
2790                                    File storeFile = getBinaryFile(node, fieldName);
2791                                    if (!storeFile.exists()) { // not found!
2792                                        File legacyFile = getLegacyBinaryFile(node, fieldName);
2793                                        if (legacyFile != null) {
2794                                            storeFile.getParentFile().mkdirs();
2795                                            if (legacyFile.renameTo(storeFile)) {
2796                                                log.service("Renamed " + legacyFile + " to " + storeFile);
2797                                                result++;
2798                                            } else {
2799                                                log.warn("Could not rename " + legacyFile + " to " + storeFile);
2800                                            }
2801                                        } else {
2802                                            if (foundColumn) {
2803
2804                                                Blob b = getBlobFromDatabase(node, field, false);
2805                                                byte[] bytes = b.getBytes(0L, (int) b.length());
2806                                                node.setValue(fieldName, bytes);
2807                                                storeBinaryAsFile(node, field);
2808
2809                                                node.storeValue(fieldName, MMObjectNode.VALUE_SHORTED); // remove to avoid filling node-cache with lots of handles and cause out-of-memory
2810                                                // node.commit(); no need, because we only changed blob (so no database updates are done)
2811                                                result++;
2812                                                fromDatabase++;
2813                                                log.service("( " + result + ") Found bytes in database while configured to be on disk. Stored to " + storeFile);
2814                                            }
2815                                        }
2816                                    }
2817                                } // nodes
2818                            } // if type = byte
2819                        } // fields
2820                    }
2821                } // builders
2822                if (result > 0) {
2823                    log.info("Converted " + result + " fields " + ((fromDatabase > 0 && fromDatabase < result) ? " of wich  " + fromDatabase + " from database" : ""));
2824                    if (fromDatabase > 0) {
2825                        log.info("You may drop byte array columns from the database now. See the the VERIFY warning during initialisation.");
2826                    }
2827                } else {
2828                    log.service("Converted no fields");
2829                }
2830                return result;
2831            } // synchronized
2832        } else {
2833            // not configured to store blobs as file
2834            return -1;
2835        }
2836    }
2837
2838    protected static class InputStreamBlob implements Blob {
2839        private InputStream inputStream;
2840        private byte[] bytes = null;
2841        private long size;
2842
2843        public InputStreamBlob(InputStream is, long s) {
2844            inputStream = is;
2845            size = s;
2846        }
2847        public InputStreamBlob(InputStream is) {
2848            inputStream = is;
2849            size = -1;
2850        }
2851
2852        public InputStream getBinaryStream() {
2853            if (bytes != null) {
2854                return new ByteArrayInputStream(bytes);
2855            } else {
2856                return inputStream;
2857            }
2858        }
2859
2860        public byte[] getBytes(long pos, int length) {
2861            if (pos == 1 && size == length && bytes != null) return bytes;
2862
2863            ByteArrayOutputStream b = new ByteArrayOutputStream();
2864            long p = 1;
2865            int c;
2866            InputStream stream = getBinaryStream();
2867            try {
2868                while((c = stream.read()) > -1) {
2869                    if (p >= pos) {
2870                        b.write(c);
2871                    }
2872                    p++;
2873                    if (p > pos + length) break;
2874                }
2875            } catch (IOException ioe) {
2876                log.error(ioe);
2877            }
2878            return b.toByteArray();
2879        }
2880
2881        protected void getBytes() {
2882            ByteArrayOutputStream b = new ByteArrayOutputStream();
2883            int c;
2884            byte[] buf = new byte[1024];
2885            try {
2886                while((c = inputStream.read(buf)) > -1) {
2887                    b.write(buf, 0, c);
2888                }
2889            } catch (IOException ioe) {
2890                log.error(ioe);
2891            }
2892            bytes = b.toByteArray();
2893            size = bytes.length;
2894        }
2895
2896        public long length() {
2897            if (size < 0 && inputStream != null) {
2898                getBytes();
2899            }
2900            return size;
2901        }
2902
2903        public long position(Blob pattern, long start) {
2904            throw new UnsupportedOperationException  ("");
2905        }
2906
2907        public long  position(byte[] pattern, long start) {
2908            throw new UnsupportedOperationException  ("");
2909        }
2910
2911        public OutputStream setBinaryStream(long pos) {
2912            throw new UnsupportedOperationException  ("");
2913        }
2914
2915        public int setBytes(long pos, byte[] bytes) {
2916            throw new UnsupportedOperationException  ("");
2917        }
2918
2919        public int setBytes(long pos, byte[] bytes, int offset, int len) {
2920            throw new UnsupportedOperationException  ("");
2921        }
2922
2923        public void truncate(long len) {
2924            throw new UnsupportedOperationException  ("");
2925        }
2926    }
2927}
2928
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags