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