View Javadoc

1   package org.paneris.jal.model;
2   
3   import java.sql.Connection;
4   import java.sql.PreparedStatement;
5   import java.sql.ResultSet;
6   import java.sql.Statement;
7   import java.util.Enumeration;
8   import java.util.Vector;
9   
10  import org.paneris.util.ExtendedHash;
11  import org.paneris.util.JoinVector;
12  import org.paneris.util.ValidationProblem;
13  import org.webmacro.servlet.HandlerException;
14  import org.webmacro.servlet.WebContext;
15  
16  /**
17   * <p> A DDRecord holds data and metadata for a single record from the database
18   * it is passed into the webcontext and introspected by the template.</p>
19   * Note that a DDRecord represents one record in a particular table.
20   * It will always hold the metadata (as defined in the
21   * datadictionary tables in the DB).
22   *<p> It may also contain values for each field
23   * in the record. 
24   * We say that it is "empty" if it does not contain such values.
25   *
26   * @author  Paneris
27   * @version 0.9, 11/6/1999
28   **/
29  public class DDRecord extends ExtendedHash {
30  
31    private static final long serialVersionUID = 1L;
32      public TableMetaData metaData;
33      protected static final boolean debug = false;
34      public DataCache dataCache = DataCache.getInstance();
35      protected DBConnectionManager connMgr = DBConnectionManager.getInstance();
36      protected String database;
37  
38  
39      /**
40       * Constructor to build an empty DDRecord given a String describing 
41       * the table name.
42       *
43       * @param db        a String indicating which database to use. This
44       *                  is used to look up the full (JDBC) database details
45       * @param tn        the name of the table that this DDRecord is from
46       * @exception       can throw exceptions if we can't connect to the db
47       */
48      public DDRecord(String db, String tn) throws Exception {
49          metaData = dataCache.getTableMetaData(db, tn);
50          database = db;
51          init(null);
52      }
53  
54  
55      /**
56       * Constructor to build an empty DDRecord given an Integer denoting 
57       * the table number in the DataDictionary.
58       *
59       * @param db        a String indicating which database to use. This
60       *                  is used to look up the full (JDBC) database details
61       * @param tn        the number of the table that this DDRecord is from
62       *                  as defined by the DataDictionaryTables table
63       * @exception       can throw exceptions if we can't connect to the db
64       */
65      public DDRecord(String db, Integer tn) throws Exception {
66          metaData = dataCache.getTableMetaData(db, tn);
67          database = db;
68          init(null);
69      }
70  
71  
72      /**
73       * Constructor to build a DDRecord given a string describing the table and
74       * a java.sql.resultSet to find the values from
75       *
76       * @param db        a String indicating which database to use. This
77       *                  is used to look up the full (JDBC) database details
78       * @param tn        the name of the table that this DDRecord is from
79       * @param rs        a record used to fill in the values of this DDRecord
80       * @exception       can throw exceptions if we can't connect to the db
81       */
82      public DDRecord(String db, String tn, ResultSet rs) throws Exception {
83          metaData = dataCache.getTableMetaData(db, tn);
84          database = db;
85          init(rs);
86      }
87  
88  
89      /**
90       * Constructor to build a DDRecord given an integer denoting the table and
91       * a java.sql.resultSet to find the values from.
92       *
93       * @param db        a String indicating which database to use. This
94       *                  is used to look up the full (JDBC) database details
95       * @param tn        the number of the table that this DDRecord is from
96       *                  as defined by the DataDictionaryTables table
97       * @param rs        a record used to fill in the values of this DDRecord
98       * @exception       can throw exceptions if we can't connect to the db
99       */
100     public DDRecord(String db, Integer tn, ResultSet rs) throws Exception {
101         metaData = dataCache.getTableMetaData(db, tn);
102         database = db;
103         init(rs);
104     }
105 
106 
107     /**
108      * Constructor to build a DDRecord given an integer denoting the table and
109      * an Integer indicating the record to read into this DDRecord
110      *
111      * @param db        a String indicating which database to use. This
112      *                  is used to look up the full (JDBC) database details
113      * @param tn        the name of the table that this DDRecord is from
114      * @param record    the id of a record used to fill in the values of 
115      *                  this DDRecord
116      * @exception       can throw exceptions if we can't connect to the db
117      */
118     public DDRecord(String db, String tn, Integer record) throws Exception {
119         metaData = dataCache.getTableMetaData(db, tn);
120         database = db;
121         if (record.intValue() == 0) {
122             init(null);
123         } else {
124             Connection conn = connMgr.getConnection("DDRecord",db);
125             Statement s = conn.createStatement();
126             init(getResultSet(record,s));
127             s.close();
128             connMgr.freeConnection(db, conn);
129         }
130     }
131 
132     /**
133      * Constructor to build a DDRecord given an integer denoting the table and
134      * an Integer indicating the record to read into this DDRecord
135      *
136      * @param db        a String indicating which database to use. This
137      *                  is used to look up the full (JDBC) database details
138      * @param tn        the number of the table that this DDRecord is from
139      *                  as defined by the DataDictionaryTables table
140      * @param record    the id of a record used to fill in the values of 
141      *                  this DDRecord
142      * @exception       Exception if we can't connect to the db
143      */
144     public DDRecord(String db, Integer tn, Integer record) throws Exception {
145         metaData = dataCache.getTableMetaData(db, tn);
146         database = db;
147         if (record.intValue() == 0) {
148             init(null);
149         } else {
150             Connection conn = connMgr.getConnection("DDRecord",db);
151             Statement s = conn.createStatement();
152             init(getResultSet(record,s));
153             s.close();
154             connMgr.freeConnection(db, conn);
155         }
156     }
157 
158     /**
159      * Sets the values of this DDRecord from a ResultsSet (a record in the DB)
160      *
161      * @param rs    the ResultSet to use in setting the values of the DDRecord
162      * @exception Exception if cannot connect to the DB or column not found
163      */
164     protected void init(ResultSet rs) throws Exception {
165       for (Enumeration en = metaData.getFields().elements(); 
166            en.hasMoreElements() ;) {
167         FieldMetaData fieldMetaData = (FieldMetaData)en.nextElement();
168         DDField field = new DDField(database, fieldMetaData.getFieldNumber());
169         //assert (fieldMetaData == field.getMetaData()) : "We could use fieldMetaData instead of field.getMetaData() below";
170         String fieldName = field.getMetaData().getFieldName();
171         String fieldType = field.getMetaData().getType();
172         // if we don't have a resultset 
173         // then build a DDRecord with all the data,
174         // if we do have one, then only add the columns that are relevant
175         if (rs == null) {
176           // add the field into this DDRecord
177           put(fieldName, field);
178         } else {
179           try {
180             // postgres large objects
181             if (connMgr.getDatabaseEngineType(database) == DBConnectionManager.POSTGRES &&
182                 field.getMetaData().getSQLMetaData().ColumnTypeName.equals("oid")) {
183                String value = new String(rs.getBytes(fieldName));
184                field.setValue(value);
185             } else {
186               if (fieldType.equals("boolean")) {
187                 field.setValue(new Boolean(rs.getBoolean(fieldName)));
188               } else {
189                 if (fieldType.equals("timestamp")) {
190                   field.setValue(rs.getTimestamp(fieldName));
191                 } else {
192                    field.setValue(rs.getObject(fieldName));
193                 }
194               }
195             }
196             put(fieldName, field);
197           } catch (Exception e) {
198           // there appears to be no other way to trap column not found errors
199             throw (Exception)new Exception("Column: " + fieldName + " - " + e.toString()).initCause(e);
200           }
201         }
202       }
203     }
204 
205     /**
206      * Gets a ResultSet representing the record indicated by record
207      *
208      * @param record    the id of a record
209      * @param s         a Statement used to execute a query
210      * @return          a ResultSet representing the record
211      * @exception       can throw exceptions if we can't connect to the db
212      */
213     protected ResultSet getResultSet(Integer record, Statement s) 
214         throws Exception {
215       if (record == null)
216         return null;
217       else {
218         ResultSet rs;
219         String sqlString = new String("SELECT * FROM " + metaData.getTableName() + " WHERE id = " + record);
220         try {
221           rs = s.executeQuery(sqlString);
222           if (!rs.next()) {
223             throw new RuntimeException("Record Number is invalid: " + record + " sqlString: " + sqlString);
224           }
225         } catch (Exception e) {
226           throw new HandlerException(e.toString() + " sqlString: " + sqlString);
227         }
228         return rs;
229       }
230     }
231 
232 
233     /**
234      * Get the metadata for the table that this DDRecord is in
235      *
236      * @return      A TableMetaData object representing the metadata
237      */
238     public TableMetaData getMetaData() {
239       return metaData;
240     }
241 
242 
243     /**
244      * Get the value of the given field as a String
245      *
246      * @param field     the name of the field
247      * @return          the value of the field as a String
248      */
249     public String getFieldValue(String field) {
250       DDField f = (DDField)get(field);
251       return (f != null)? f.getStringValue():"";
252     }
253 
254 
255     /**
256      * Set the value of the given field from a String
257      *
258      * @param field     the name of the field
259      * @param value     the value we wish to set the field to
260      */
261     public void setFieldValue(String field, String value) {
262       DDField f = (DDField) get(field);
263       f.setValue(value);
264     }
265 
266  /**
267   * append a value to the value of this field
268   **/
269   public void appendFieldValue(String field, String v) {
270     if (v != null) setFieldValue(field, getFieldValue(field) + v);
271   }
272 
273 
274   /**
275    * Returns the DDField for a named field
276    *
277    * @param field     the name of the field
278    * @return          the named DDField
279    */
280   public DDField getField(String field) {
281     DDField f = (DDField) get(field);
282     return f;
283   }
284 
285     /**
286      * Set the value of the given field from a Object
287      *
288      * @param field     the name of the field
289      * @param value     the value we wish to set the field to
290      */
291     public void setFieldValue(String field, Object value) {
292         DDField f = (DDField) get(field);
293         f.setValue(value);
294     }
295 
296     /**
297      * Get any validation problems with this record
298      *
299      * @return      a vector of any ValidationProblems found
300      * @exception   Database problems only?
301      */
302     public Vector getProblems() throws Exception {
303         Vector problems = new Vector();
304         ValidationProblem prob;
305         String value;
306         // need to validate that the record exists
307         Connection conn = connMgr.getConnection("DDRecord",database);
308         Statement s = conn.createStatement();
309         for (Enumeration en = elements(); en.hasMoreElements();) {
310             DDField field = (DDField) en.nextElement();
311             prob  = field.getProblem();
312             value = (field.getValue() != null) ? field.getValue().toString() : "";
313 
314             // We have checked for any formatting problems when we set the value
315             if (prob != null) {
316                 problems.addElement(prob);
317             } else if (field.getMetaData().getEnforceUnique() && isDuplicated(field, value)) {
318                 problems.addElement(
319                     new ValidationProblem(ValidationProblem.DUPLICATED_UNIQUE_FIELD, field.getMetaData().getDisplayName()));
320             } else if (field.getMetaData().getType().equals("lookupnodrop")) {
321                 String sqlString = "SELECT id FROM " + field.getMetaData().getRelationshipTable() + " WHERE id = " + value;
322                 ResultSet rs = s.executeQuery(sqlString);
323                 if (!rs.next()) {
324                     problems.addElement(new ValidationProblem(ValidationProblem.INCORRECT_LOOKUP_STRING, field.getMetaData().getDisplayName(),value));
325                 }
326             }
327         }
328         connMgr.freeConnection(database, conn);
329         return problems;
330     }
331 
332 
333     /**
334      * fill in the values for this DDRecord by reading them in from
335      * a CGI Form - The idea is that this DDRecord will not have any
336      * values set, just the metaData and DDField structure.
337      * <p>
338      * It may, however, have the id set. If an id is read in from
339      * the form then this will override the current value. If there
340      * is no id in the form then the current value is left.
341      * <P>
342      * If postfix is set, then it is appended to the name of the
343      * to read the value from the form. For example, normally the
344      * field "myfield" will be set to the value of the form field
345      * "myfield". If postfix is set to "_default" then the field
346      * will be set from the form field "myfield_default".
347      *
348      * @param context   a WebContext which contains the Form
349      *                  object from which we read the values
350      * @param postfix   A string to append to the fieldnames before
351      *                  getting the values from the form
352      * @param force     Set fields to null if input is null
353      *                  useful to set this to false when not all fields are on the form
354      *                  watch out for checkboxes which return null when unchecked
355      * @exception       Database problems only?
356      */
357     public void setFromForm(WebContext context) throws Exception {
358         setFromForm(context, "");
359     }
360     public void setFromForm(WebContext context, String postfix) throws Exception {
361         setFromForm(context, postfix, true);
362     }
363     public void setFromForm(WebContext context, String postfix, boolean force) throws Exception {
364         DDField field;
365         String value;
366         for (Enumeration en = elements() ; en.hasMoreElements() ;) {
367             field = (DDField) en.nextElement();
368             if (postfix == null) postfix="";
369             String formfield = field.getMetaData().getFieldName();
370 
371             if (field.getMetaData().getType().equals("datedrop")) {
372                 value = (String) context.getForm(formfield+postfix+"_date") + "/";
373                 value += (String) context.getForm(formfield+postfix+"_month") + "/";
374                 value += (String) context.getForm(formfield+postfix+"_year");
375             }
376             else if (field.getMetaData().getType().equals("datetimedrop")) {
377                 value = (String) context.getForm(formfield+postfix+"_date") + "/";
378                 value += (String) context.getForm(formfield+postfix+"_month") + "/";
379                 value += (String) context.getForm(formfield+postfix+"_year") + " ";
380                 value += (String) context.getForm(formfield+postfix+"_hour") + ":";
381                 value += (String) context.getForm(formfield+postfix+"_minute");
382             }
383             else {
384                 value = (String) context.getForm(formfield+postfix);
385             }
386 
387             if (value == null) {
388               if (force) {
389                 if (!formfield.equals("id"))
390                     if (field.getMetaData().getType().equals("boolean"))
391                         field.setValue("false");
392                     else
393                         field.setValue(field.getMetaData().getDefaultValue());
394               }
395             }
396             else {   // got a value.
397               if (field.getMetaData().getType().equals("lookup") || field.getMetaData().getType().equals("lookupnodrop") || metaData.getType().equals("lookupwindow")) {
398                 try {
399                     // Is it an integer or is a lookup needed?
400                     Integer.parseInt(value);
401                     field.setValue(value);
402                 } catch (NumberFormatException e) {
403                     field.setValueFromLookup(value);
404                 }
405               } else {  // not a lookup
406                 field.setValue(value);
407               }
408             }
409         }
410     }
411 
412 
413 
414 
415     /**
416      * fill in the values for this DDRecord From the defaults
417      * set in the DataDictionary
418      *
419      * @exception       Database problems only?
420      */
421     public void setFromDefaults() throws Exception {
422         DDField field;
423         for (Enumeration en = elements() ; en.hasMoreElements() ;) {
424             field = (DDField) en.nextElement();
425             if (!field.getMetaData().getFieldName().equals("id")) {
426             // only default already blank values
427                 if (field.getStringValue().equals("")) {
428                     field.setValue(field.getMetaData().getDefaultValue());
429                 }
430             }
431         }
432     }
433 
434 
435     /**
436      * <p>Write this record to the database.</p>
437      * <p>If this record has a autorandom field, and we are 'inserting', the autorandom
438      * field will be set to (surprise) a random number.  This is then used by the
439      * reread() method to reload the record from the database.  This means the the id
440      * field will be set, and can be used in your program.</p>
441      *
442      * @exception       Database problems only?
443      */
444     public void write() throws Exception {
445         write(false);
446     }
447 
448     /**
449      * <p>Write this record to the database.</p>
450      * <p>The id of the record should be 0 (or forceid should be true) if you
451      * wish to create a new record. If this is not the case then we update the
452      * record that already exists (and we'll get an error if it doesn't!)</p>
453      * <p>If this record has a autorandom field, and we are 'inserting', the autorandom
454      * field will be set to (surprise) a random number.  This is then used by the
455      * reread() method to reload the record from the database.  This means the the id
456      * field will be set, and can be used in your program.</p>
457      *
458      * @param forceid   Write a new record into the database, even if one
459      *                  already exists (i.e. don't just update the existing record)
460      * @exception       Database problems only?
461      */
462     public void write(boolean forceid) throws Exception {
463 
464         Connection conn = connMgr.getConnection("DDRecord",database);
465         JoinVector v = new JoinVector();
466         JoinVector w = new JoinVector();
467         //boolean reread = false;
468         String sqlString = "";
469         DDField idField = (DDField) get("id");
470         Object idValue = idField.getValue();
471         int id;
472         if (idValue == null) {
473             id = 0;
474         } else {
475             id = ((Integer)idValue).intValue();
476         }
477         if ((id == 0) || forceid) {
478             sqlString = new String("INSERT INTO ");
479             sqlString += metaData.getTableName() + " (";
480         } else {
481             sqlString = new String("UPDATE ");
482             sqlString += metaData.getTableName() + " SET ";
483         }
484         for (Enumeration en = elements(); en.hasMoreElements();) {
485             DDField field = (DDField) en.nextElement();
486             if (!(field.getMetaData().getType().equals("id")) || forceid) {
487                 if (field.getMetaData().getType().equals("timestamp")) {
488                     java.util.Date today = new java.util.Date();
489                     java.sql.Timestamp sqltoday = new java.sql.Timestamp(today.getTime());
490                     field.setValue(sqltoday);
491                 }
492                 if ((id == 0) || forceid) {
493                     if (field.getMetaData().getType().equals("autorandom")) {
494                         field.setValue((new Double(java.lang.Math.random())).toString() );
495                         //reread = true;
496                     }
497                     v.addElement(field.getMetaData().getFieldName());
498                     w.addElement("?");
499                 } else {
500                     if (!(field.getMetaData().getType().equals("autorandom"))) {
501                         v.addElement(new String(field.getMetaData().getFieldName() + " = ? "));
502                     }
503                 }
504             }
505         }
506         if ((id == 0) || forceid) sqlString = sqlString.concat(v.join(",") + ") VALUES (" + w.join(",") + ")");
507         else sqlString = sqlString.concat(v.join(",") + " WHERE id = " + id);
508         if (debug) System.err.println("sqlString:" + sqlString);
509         PreparedStatement sqlStatement = conn.prepareStatement(sqlString);
510         int i = 0;
511         for (Enumeration en = elements() ; en.hasMoreElements() ;) {
512             DDField field = (DDField) en.nextElement();
513             if (!(field.getMetaData().getType().equals("id")) || forceid) {
514                 if (!(field.getMetaData().getType().equals("autorandom")) || (id == 0)) {
515                     i++;
516                     Object o = field.getValue();
517                     if (o == null) {
518                         int colType = field.getMetaData().getSQLMetaData().ColumnType;
519 // special hack to get round access bug
520                         if ( (field.getMetaData().getType().equals("date") || field.getMetaData().getType().equals("datetime") || field.getMetaData().getType().equals("timestamp"))
521                               && (connMgr.getDatabaseEngineType(database) == DBConnectionManager.ACCESS)
522                            )
523                             colType = java.sql.Types.VARCHAR;
524                         if (field.getMetaData().getType().equals("boolean"))
525                             sqlStatement.setBoolean(i, false);
526                         else
527                             sqlStatement.setNull(i,colType);
528 
529         if (debug) System.err.println(field.getMetaData().getFieldName() + " : " + field.getMetaData().getSQLMetaData().ColumnType);
530 
531                     } else {
532         if (debug) System.err.println(field.getMetaData().getFieldName() + " : " + o.toString() + " : " + field.getMetaData().getSQLMetaData().ColumnTypeName);
533                     // postgres large objects
534                         if (connMgr.getDatabaseEngineType(database) == DBConnectionManager.POSTGRES
535                 && field.getMetaData().getSQLMetaData().ColumnTypeName.equals("oid")) {
536                             String os = o.toString();
537                             sqlStatement.setBytes(i, os.getBytes());
538 /*                        an attempt (aborted) to get fields of >256 chars to work with Access
539                         } else if (connMgr.getDatabaseEngineType(database) == DBConnectionManager.ACCESS && 
540                     field.getMetaData().getSQLMetaData().ColumnTypeName.equals("LONGTEXT")) {   
541                             String os = o.toString();
542                             ByteArrayInputStream bais = new ByteArrayInputStream(os.getBytes());
543                             sqlStatement.setBinaryStream(i, bais, os.length());
544 */
545                         } else {
546                             sqlStatement.setObject(i, o);
547                         }
548                     }
549                 }
550             }
551         }
552         sqlStatement.executeUpdate();
553         sqlStatement.close();
554 
555         // sort out the cache - no need if we are inserting this record
556         if (metaData.getTableName().equals("datadictionary")) {
557             if (id != 0) {
558                 dataCache.removeFieldMetaData(database, new Integer(id));
559             }
560             DDField table = (DDField) get("tablename");
561             Integer tableNumber = (Integer) table.getValue();
562             dataCache.removeTableMetaData(database, tableNumber);
563         }
564         if (id != 0) {
565             if (metaData.getTableName().equals("datadictionarytables")) {
566                 dataCache.removeTableMetaData(database, new Integer(id));
567             }
568             if (metaData.getCached()) {
569                 dataCache.removeDDRecords(database, metaData.getTableName(), new Integer(id));
570             }
571         }
572         // Remember the value of the last used id (of the record we have just written)
573         // but only if we are using Postgres (and using the 'serial' type for
574         // ids) or Access (and so SELECT max(id) works).
575         else {
576             Statement s = conn.createStatement();
577             DDField idf = (DDField) get("id");
578 
579             if (connMgr.getDatabaseEngineType(database) == DBConnectionManager.POSTGRES) {
580                 sqlString = "SELECT CURRVAL ('"+metaData.getTableName()+"_id_seq')";
581                 ResultSet rs = s.executeQuery(sqlString);
582                 if (rs.next())
583                     idf.setValue(new Integer(rs.getInt(1)));
584             }
585             else {
586 //if (connMgr.getDatabaseEngineType() == DBConnectionManager("ACCESS")) {
587                 sqlString = "SELECT max(id) FROM " + metaData.getTableName();
588                 ResultSet rs = s.executeQuery(sqlString);
589                 if (rs.next())
590                     idf.setValue(new Integer(rs.getInt(1)));
591             }
592             s.close();
593         }
594 
595         connMgr.freeConnection(database, conn);
596 /*
597         // reread the record if we need to
598         if (reread) {
599             reread();
600         }
601 */
602     }
603 
604     /**
605      * Deletes this record from the database
606      *
607      * @exception       Database problems only?
608      */
609     public void delete() throws Exception {
610         Connection conn = connMgr.getConnection("DDRecord",database);
611         DDField idField = (DDField) get("id");
612         String id = ((Integer)idField.getValue()).toString();
613         String sqlString = new String("DELETE FROM " + metaData.getTableName() + " WHERE id = " + id);
614         conn.createStatement().executeUpdate(sqlString);
615         // sort out the cache
616         if (metaData.getTableName().equals("datadictionary")) {
617             dataCache.removeFieldMetaData(database, new Integer(id));
618             DDField table = (DDField) get("tablename");
619             Integer tableNumber = (Integer) table.getValue();
620             dataCache.removeTableMetaData(database, tableNumber);
621         }
622         if (metaData.getTableName().equals("datadictionarytables")) {
623             dataCache.removeTableMetaData(database, new Integer(id));
624         }
625         if (metaData.getCached()) {
626             dataCache.removeDDRecords(database, metaData.getTableName(), new Integer(id));
627         }
628         connMgr.freeConnection(database, conn);
629     }
630 
631     /**
632      * Reread this record, primarily to get the id for a newly inserted record.
633      * <p>This function requires that one of the fields is "autorandom".
634      *
635      * @exception       Database problems only?
636      */
637 /*
638     public void reread() throws Exception {
639         Connection conn = connMgr.getConnection("DDRecord",database);
640        // find "autorandom" field
641         DDField identifier = null;
642         boolean found = false;
643         for (Enumeration enum = elements() ; enum.hasMoreElements() ;) {
644             DDField field = (DDField) enum.nextElement();
645             if (field.getMetaData().getType().equals("autorandom")) {
646                 found = true;
647                 identifier = field;
648             }
649         }
650         if (!found) {
651             throw new HandlerException("can't find autorandom field for reread");
652         } else {
653             if (identifier.getValue() == null) {
654                throw new HandlerException("identifier is null for reread");
655             } else {
656                 if (connMgr.getDatabaseEngineType().equals("Access")) {
657                     String sqlString = "SELECT max(id) FROM " + metaData.getTableName();
658                     Statement s = conn.createStatement();
659                     ResultSet rs = s.executeQuery(sqlString);
660                     DDField idf = (DDField) get("id");
661                     if (rs.next()) idf.setValue(new Integer(rs.getInt(1)));
662                     s.close();
663                 } else {
664                     String sqlString = "SELECT * FROM " + metaData.getTableName() + " WHERE " + identifier.getMetaData().getFieldName() + " =";
665                     sqlString += "?";
666                     PreparedStatement sqlStatement = conn.prepareStatement(sqlString);
667                     sqlStatement.setObject(1, identifier.getValue());
668                     ResultSet rs = sqlStatement.executeQuery();
669                     if (rs.next()) {
670                         try {
671                             for (Enumeration enum = elements() ; enum.hasMoreElements() ;) {
672                                 DDField field = (DDField) enum.nextElement();
673                                 if (field.getMetaData().getType().equals("boolean"))
674                                     field.setValue(new Boolean(rs.getBoolean(field.getMetaData().getFieldName())));
675                                 else
676                                     field.setValue(rs.getObject(field.getMetaData().getFieldName()));
677                             }
678                         } catch (Exception e) {
679                     // there appears to be no other way to trap column not found errors
680                     // if (debug) System.err.println(field.getMetaData().getFieldName() + " - " + e.toString());
681                         }
682                     }
683                     sqlStatement.close();
684                 }
685             }
686         }
687         connMgr.freeConnection(database, conn);
688     }
689 */
690 
691     /**
692      * Find out if any other record in the database has the relevant field set
693      * to this value.
694      * <p>It is assumed that the id of this DDField is already
695      * set to a value which we are going to use to insert this record into the
696      * DB under (which could be 0)
697      *
698      * @param field     the field we want to be unique
699      * @param value     the value we don't want to already exist for this field
700      * @return          'true' if the value already exists for this field,
701      *                  'false' otherwise
702      * @exception       Database problems only?
703      */
704     public boolean isDuplicated(DDField field, String value) throws Exception {
705 
706         Connection conn = null;
707         boolean foundDuplicate = false;
708         try {
709             Object idObj = get("id");
710             Object idVal = (idObj != null) ? ((DDField)idObj).getValue():null;
711             String myId  = (idVal != null) ? idVal.toString():"0";
712 
713             conn = connMgr.getConnection("DDRecord",database);
714             String sqlString = ("SELECT id FROM " + metaData.getTableName() +
715                                 " WHERE " + field.getMetaData().getFieldName() + " = ?");
716             PreparedStatement sqlStatement = conn.prepareStatement(sqlString);
717             sqlStatement.setObject(1, value);
718             ResultSet rs = sqlStatement.executeQuery();
719             while (rs.next()) {
720                 String idFound = new String(rs.getString(1).trim());
721                 if (!idFound.equals(myId)) // if we've found a different record with same
722                                            // unique field's value
723                     foundDuplicate = true;
724             }
725             sqlStatement.close();
726         } catch (Exception e) {
727             throw e;
728         } finally {
729             connMgr.freeConnection(database, conn);
730         }
731         return foundDuplicate;
732     }
733 
734 
735     /**
736      * Get a list of records which link to this record.
737      * Note that if the id is not set,
738      *
739      * @param full      if true then the return Vector will have one entry
740      *                  for each record linking to this one. Otherwise, the
741      *                  vector will only contain one (representative, i.e. only
742      *                  containing metadata) DDRecord for each table/field
743      *                  combination which links to this recrod
744      * @param force     if true, then the eturn Vector will have an entry even if
745      *                  no data in the child table links to this record
746      * @return          A vector of records linking to this record
747      * @exception       If the id field is not set, we cannot find out
748      *                  if any records link to us, so we get an exception
749      *
750      */
751     public Vector getLinksToThis(boolean full) throws Exception {
752         return getLinksToThis(full, false);
753     }
754     public Vector getLinksToThis(boolean full, boolean force) throws Exception {
755 
756         String id = getFieldValue("id");
757         if (id.equals("")) throw new Exception("DDRecord does not have an id set");
758 
759         Vector linksToUs = new Vector();
760         if (id.equals("0")) return linksToUs;
761 
762         Connection conn = null;
763 
764 
765         // Find all tables/fields which link to this table
766         String sqlString = "SELECT datadictionary.tablename, datadictionary.fieldname "+
767                            "FROM datadictionary, datadictionarytables "+
768                            "WHERE relationshiptable = datadictionarytables.id "+
769                            "AND datadictionarytables.tablename = "+
770                            "'" + getMetaData().getTableName() + "'";
771 
772         try {
773 
774             conn = connMgr.getConnection("DDRecord",database);
775             ResultSet rs = conn.createStatement().executeQuery(sqlString);
776 
777             while (rs.next()) {
778                 Integer tableID = Integer.valueOf(rs.getString(1).trim());
779                 String  fieldName = rs.getString(2).trim();
780                 sqlString = "SELECT tablename FROM datadictionarytables WHERE id =" +tableID;
781                 Statement s2 = conn.createStatement();
782                 ResultSet rs2 = s2.executeQuery(sqlString);
783                 if (rs2.next()) {
784                     String tableName = new String(rs2.getString(1).trim());
785                     sqlString = "SELECT id FROM " + tableName + " WHERE " + fieldName + " = " + id;
786 // speed it up a bit                    
787                     if (!full && connMgr.getDatabaseEngineType(database) == DBConnectionManager.POSTGRES && !connMgr.getDatabaseProductVersion(database).equals("6.4")) {
788                         sqlString += " LIMIT 1";
789                     }
790                     Statement s3 = conn.createStatement();
791                     ResultSet rs3 = s3.executeQuery(sqlString);
792 // Put one or all on
793                     if (full) {
794                         while (rs3.next()) {
795                             ExtendedHash item = new ExtendedHash();
796                             item.put("table", new DDRecord(database, tableID, Integer.valueOf(rs3.getString(1).trim())));
797                             item.put("fieldname", fieldName);
798                             linksToUs.addElement(item);
799                         }
800                     } else  {
801                         if (rs3.next() || force) {
802                             ExtendedHash item = new ExtendedHash();
803                             item.put("table", new DDRecord(database, tableID));
804                             item.put("fieldname", fieldName);
805                             linksToUs.addElement(item);
806                         }
807                     }
808                     s3.close();
809                 }
810                 s2.close();
811             }
812         } catch (Exception e) {
813             throw e;
814         } finally {
815             connMgr.freeConnection(database, conn);
816         }
817         return linksToUs;
818     }
819 
820 
821 /*
822 gets a record set of child data for this record for a given table
823 */
824     public RecordSet getChildren(String table, String field) throws Exception {
825         String sqlString = "SELECT id FROM " + table + " WHERE " + field + " = " + getFieldValue("id");
826         return new RecordSet(database, table, sqlString, new Integer(0));
827     }
828 
829 /**
830  *gets an ordered record set of child data for this record for a given table
831  */
832     public RecordSet getOrderedChildren(String table, String field, 
833                                         String orderField) 
834       throws Exception {
835         String sqlString = "SELECT id FROM " + table + " WHERE " + field + " = " + getFieldValue("id") + "ORDER BY " + orderField;
836         if (debug) System.err.println("sqlString: " + sqlString);
837         return new RecordSet(database, table, sqlString, new Integer(0));
838     }
839 
840   /**
841    * Returns the value to which the specified key is mapped 
842    * in this ExtendedHash.
843    *
844    * @param   key   a key in the DDRecord.
845    * @return  the value to which the key is mapped in this DDRecord;
846    * @see     NoSuchFieldException
847    *          if the key is not mapped to any value in
848    *          this DDRecord, as that means the field name is wrong.
849    */
850    public synchronized Object get(Object key) {
851      if (containsKey(key)) {
852        return super.get(key);
853      } else {
854        throw new NoSuchFieldException("Field ("+key.toString()+") not found.");
855      }
856    }
857 
858 }
859