Java > Open Source Codes > org > jahia > services > fields > JahiaFieldsDB


1 //
2 // ____.
3 // __/\ ______| |__/\. _______
4 // __ .____| | \ | +----+ \
5 // _______| /--| | | - \ _ | : - \_________
6 // \\______: :---| : : | : | \________>
7 // |__\---\_____________:______: :____|____:_____\
8 // /_____|
9 //
10 // . . . i n j a h i a w e t r u s t . . .
11 //
12
13 //
14 // JahiaFieldsDB
15 // EV 05.01.2001
16 // DJ 18.04.2002 - content staging + versioning
17 //
18
19
20 package org.jahia.services.fields;
21
22 import org.jahia.bin.Jahia;
23 import org.jahia.data.JahiaDBDOMObject;
24 import org.jahia.data.JahiaDOMObject;
25 import org.jahia.data.fields.JahiaField;
26 import org.jahia.exceptions.JahiaException;
27 import org.jahia.exceptions.database.JahiaDatabaseException;
28 import org.jahia.registries.ServicesRegistry;
29 import org.jahia.services.version.EntryLoadRequest;
30 import org.jahia.services.version.JahiaSaveVersion;
31 import org.jahia.settings.SettingsBean;
32 import org.jahia.utils.DBRowDataFilter;
33 import org.jahia.utils.JahiaTools;
34
35 import java.nio.ByteBuffer ;
36 import java.nio.charset.Charset ;
37 import java.sql.Connection ;
38 import java.sql.ResultSet ;
39 import java.sql.SQLException ;
40 import java.sql.Statement ;
41 import java.util.Hashtable ;
42 import java.util.Iterator ;
43 import java.util.Locale ;
44 import java.util.Vector ;
45 import java.io.UnsupportedEncodingException ;
46
47
48 public class JahiaFieldsDB {
49
50     private static org.apache.log4j.Logger logger =
51             org.apache.log4j.Logger.getLogger (JahiaFieldsDB.class);
52
53     private SettingsBean jSettings;
54     private JahiaFieldPropertiesDB props;
55
56     /**
57      * constructor
58      */
59     public JahiaFieldsDB (SettingsBean jSettings) {
60         this.jSettings = jSettings;
61         props = new JahiaFieldPropertiesDB ();
62     }
63
64
65     /**
66      * loads all page fields from the database
67      *
68      * @param pageID thefield ID
69      * @param loadVersion if <code>null</code>, load All
70      *
71      * @return a Vector of JahiaField object
72      *
73      * @throws JahiaException a critical JahiaException if SQL error
74      * @throws JahiaException a warning JahiaException if cannot free resources
75      */
76     public Vector db_load_all_fields_info_from_page (int pageID, EntryLoadRequest loadVersion)
77             throws JahiaException {
78         Vector result = new Vector ();
79         Connection dbConn = null;
80         Statement stmt = null;
81         ResultSet rs = null;
82         try {
83             String sqlQuery = "SELECT * FROM jahia_fields_data WHERE pageid_jahia_fields_data=" + pageID;
84
85             if (loadVersion != null) {
86                 sqlQuery += " AND workflow_state=" + loadVersion.getWorkflowState ();
87                 if (!loadVersion.isCurrent ()) {
88                     sqlQuery += " AND version_id=" + loadVersion.getVersionID ();
89                 }
90                 Iterator iterator = loadVersion.getLocales ().iterator ();
91                 Locale locale = null;
92                 if (iterator.hasNext ()) {
93                     sqlQuery += " AND ( language_code='";
94                     while (iterator.hasNext ()) {
95                         locale = (Locale ) iterator.next ();
96                         sqlQuery += JahiaTools.quote (locale.toString ());
97                         if (iterator.hasNext ()) {
98                             sqlQuery += "' OR language_code='";
99                         } else {
100                             sqlQuery += "')";
101                         }
102                     }
103                 }
104             }
105             logger.debug (sqlQuery.toString ());
106
107             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
108             stmt = dbConn.createStatement ();
109             rs = stmt.executeQuery (sqlQuery);
110
111             if (rs != null) {
112                 while (rs.next ()) {
113                     int jahiaID = rs.getInt ("jahiaid_jahia_fields_data");
114                     int fieldID = rs.getInt ("id_jahia_fields_data");
115                     int ctnid = rs.getInt ("ctnid_jahia_fields_data");
116                     int fieldDefID = rs.getInt ("fielddefid_jahia_fields_data");
117                     int typeField = rs.getInt ("type_jahia_fields_data");
118                     int connectType = rs.getInt ("connecttype_jahia_fields_data");
119                     String fieldValue = rs.getString ("value_jahia_fields_data");
120                     int rank = rs.getInt ("rank_jahia_fields_data");
121                     int rights = rs.getInt ("rights_jahia_fields_data");
122                     int versionID = rs.getInt ("version_id");
123                     int workflowState = rs.getInt ("workflow_state");
124                     String languageCode = rs.getString ("language_code");
125
126                     if (fieldValue == null) {
127                         // this ugly hack is necessary because Oracle is incapable of
128 // distinguishing between NULL and empty string values.
129 fieldValue =
130                                 org.jahia.data.constants.JahiaConstants.NULL_STRING_MARKER;
131                     }
132                     /**
133                      * @todo FIXME potential problem here with Oracle script
134                      * that returns a null for the field value in cases that
135                      * were not fully known yet.
136                      */
137                     if (fieldValue.equals ("<empty>")) {
138                         fieldValue = "";
139                     }
140
141                     JahiaField theField = ServicesRegistry.getInstance ().
142                             getJahiaFieldService ().createJahiaField (
143                                     fieldID, jahiaID, pageID, ctnid, fieldDefID,
144                                     typeField, connectType, fieldValue, rank,
145                                     rights, versionID, workflowState, languageCode);
146                     props.db_load_field_properties (theField);
147
148                     // load specific field value
149 // theField.load(LoadFlags.BIGTEXT,null);
150
151                     result.add (theField);
152                 }
153             }
154
155         } catch (SQLException se) {
156             String errorMsg = "Error in db_load_all_fields_info_from_page : " + se.getMessage ();
157             logger.error (errorMsg + " -> BAILING OUT");
158             logger.error ("Cannot load page's fields from the database", se);
159         } finally {
160             closeStatement (stmt);
161         }
162
163         return result;
164     }
165
166
167     /**
168      * loads a field from its id
169      *
170      * @param fieldID thefield ID
171      *
172      * @return a JahiaField object, or null if the object could not
173      * be found.
174      *
175      * @throws JahiaException a critical JahiaException if SQL error
176      * @throws JahiaException a warning JahiaException if cannot free resources
177      * @see org.jahia.data.fields.LoadFlags
178      */
179     public JahiaField db_load_field (int fieldID, EntryLoadRequest loadVersion)
180             throws JahiaException {
181 // String tmpValue = "";
182 Connection dbConn = null;
183         Statement stmt = null;
184         ResultSet rs = null;
185
186         JahiaField theField = null;
187
188         try {
189             // default sql query, if we're querying current version
190 String sqlQuery = "SELECT * FROM jahia_fields_data " +
191                     "WHERE (id_jahia_fields_data=" + fieldID + " AND workflow_state=1)";
192             // if we want to load the staged version
193 if (loadVersion.isStaging ()) {
194                 sqlQuery = new String ("SELECT * FROM jahia_fields_data " +
195                         "WHERE (id_jahia_fields_data=" + fieldID + " AND workflow_state>0) " +
196                         "ORDER BY workflow_state DESC");
197                 logger.debug ("Loading staged entry of field " + fieldID);
198             }
199             // if we want to load the version ID
200 if (loadVersion.isVersioned ()) {
201                 sqlQuery = new String ("SELECT * FROM jahia_fields_data " +
202                         "WHERE (id_jahia_fields_data=" + fieldID + " AND workflow_state<2 " +
203                         "AND version_id<=" + loadVersion.getVersionID () + ") " +
204                         "ORDER BY version_id DESC");
205                 logger.debug ("Loading an old version of field " + fieldID);
206             }
207
208             logger.debug ("version =" + loadVersion + " fieldID = " + fieldID);
209
210             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
211             stmt = dbConn.createStatement ();
212             rs = stmt.executeQuery (sqlQuery);
213
214             if (rs != null) {
215                 if (rs.next ()) {
216                     int jahiaID = rs.getInt ("jahiaid_jahia_fields_data");
217                     int pageID = rs.getInt ("pageid_jahia_fields_data");
218                     int ctnid = rs.getInt ("ctnid_jahia_fields_data");
219                     int fieldDefID = rs.getInt ("fielddefid_jahia_fields_data");
220                     int typeField = rs.getInt ("type_jahia_fields_data");
221                     int connectType = rs.getInt ("connecttype_jahia_fields_data");
222                     String fieldValue = rs.getString ("value_jahia_fields_data");
223                     int rank = rs.getInt ("rank_jahia_fields_data");
224                     int rights = rs.getInt ("rights_jahia_fields_data");
225                     int versionID = rs.getInt ("version_id");
226                     int workflowState = rs.getInt ("workflow_state");
227                     String languageCode = rs.getString ("language_code");
228
229                     if (fieldValue == null) {
230                         // this ugly hack is necessary because Oracle is incapable of
231 // distinguishing between NULL and empty string values.
232 fieldValue =
233                                 org.jahia.data.constants.JahiaConstants.NULL_STRING_MARKER;
234                     }
235                     /**
236                      * @todo FIXME potential problem here with Oracle script
237                      * that returns a null for the field value in cases that
238                      * were not fully known yet.
239                      */
240                     if (fieldValue.equals ("<empty>")) {
241                         fieldValue = "";
242                     }
243
244                     //theField = new JahiaField( fieldID, jahiaID, pageID, ctnid, fieldDefID, typeField,
245 // connectType, fieldValue, rank, rights );
246 theField = ServicesRegistry.getInstance ().
247                             getJahiaFieldService ().createJahiaField (
248                                     fieldID, jahiaID, pageID, ctnid, fieldDefID,
249                                     typeField, connectType, fieldValue, rank,
250                                     rights, versionID, workflowState, languageCode);
251
252 // if (!tmpValue.equals("")) {
253 // theField.setObject( tmpValue );
254 // }
255
256                     props.db_load_field_properties (theField);
257                 }
258             }
259
260         } catch (SQLException se) {
261             String errorMsg = "Error in db_load_field : " + se.getMessage ();
262             logger.error (errorMsg + " -> BAILING OUT");
263             logger.warn("Cannot load fields from the database", se);
264         } finally {
265             closeStatement(stmt);
266         }
267
268         return theField;
269     }
270
271
272     /**
273      * creates a field entry in the database
274      * do not call updatefield anymore, should do that in the fieldservice!
275      *
276      * @param theField theJahiaField object
277      * @param saveVersion the version to save
278      *
279      * @throws JahiaException a critical JahiaException if SQL error
280      * @throws JahiaException a warning JahiaException if cannot free resources
281      */
282     public void db_create_field (JahiaField theField, JahiaSaveVersion saveVersion)
283             throws JahiaException {
284         Connection dbConn = null;
285         Statement stmt = null;
286         try {
287             // opens connection
288 dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
289             stmt = dbConn.createStatement ();
290
291             String theValue = theField.getValue ();
292             if (theField.getValue ().equals ("")) {
293                 theValue = "<empty>";
294             }
295
296             int length = theValue.length();
297             if (Jahia.getSettings().isUtf8Encoding()) {
298                 try {
299                     length = theValue.getBytes("UTF-8").length;
300                 } catch (UnsupportedEncodingException e) {
301                 }
302             }
303             if (length > 250) {
304                 logger.debug (
305                         "Field value is more thant 250 char and would be replaced with empty space");
306                 theValue = "<empty>";
307             }
308             // creates empty line
309 StringBuffer buffer = new StringBuffer ("INSERT INTO jahia_fields_data (id_jahia_fields_data,jahiaid_jahia_fields_data,pageid_jahia_fields_data,ctnid_jahia_fields_data,fielddefid_jahia_fields_data,type_jahia_fields_data,connecttype_jahia_fields_data,value_jahia_fields_data,rank_jahia_fields_data,rights_jahia_fields_data,version_id,workflow_state, language_code) VALUES (");
310             buffer.append(theField.getID ());
311             buffer.append(",");
312             buffer.append(theField.getJahiaID ());
313             buffer.append(",");
314             buffer.append(theField.getPageID ());
315             buffer.append(",");
316             buffer.append(theField.getctnid ());
317             buffer.append(",");
318             buffer.append(theField.getFieldDefID ());
319             buffer.append(",");
320             buffer.append(theField.getType ());
321             buffer.append(",");
322             buffer.append(theField.getConnectType ());
323             buffer.append(",'");
324             buffer.append(JahiaTools.quote (theValue));
325             buffer.append("',");
326             buffer.append(theField.getRank ());
327             buffer.append(",");
328             buffer.append(theField.getAclID ());
329             buffer.append(",");
330             buffer.append(((saveVersion.getWorkflowState () > 1) ? 0 : saveVersion.getVersionID ()));
331             buffer.append(",");
332             buffer.append(saveVersion.getWorkflowState ());
333             buffer.append(",'");
334             buffer.append(JahiaTools.quote (theField.getLanguageCode ()));
335             buffer.append("')");
336
337             // executes the query, then closes the connection
338 stmt.execute (buffer.toString());
339
340             props.db_save_field_properties (theField);
341
342             try {
343
344                 if (stmt != null) stmt.close ();
345             } catch (SQLException ex) {
346                 logger.error ("Cannot free resources", ex);
347             }
348         }
349                 // catches error if cannot execute insert query
350 catch (SQLException se) {
351             String errorMsg = "Error in create_jahia_field : " + se.getMessage ();
352             logger.error (errorMsg + " -> BAILING OUT");
353             throw new JahiaException ("Cannot insert new fields in the database",
354                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
355         }
356     }
357
358
359
360     /***
361      * creates a field entry in the database
362      * do not call updatefield anymore, should do that in the fieldservice!
363      *
364      * @param theField theJahiaField object
365      *
366      * @exception throws a critical JahiaException if SQL error
367      * @exception throws a warning JahiaException if cannot free resources
368      *
369      */
370 /* public void db_create_field( JahiaField theField )
371     throws JahiaException
372     {
373         Connection dbConn = null;
374         Statement stmt = null;
375         try {
376             // opens connection
377             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection();
378             stmt = dbConn.createStatement();
379
380             // creates empty line
381             String sqlQuery = "INSERT INTO jahia_fields_data(id_jahia_fields_data) VALUES(" + theField.getID() + ")";
382
383             // executes the query, then closes the connection
384             stmt.execute( sqlQuery );
385
386              try {
387
388                 if ( stmt != null ) stmt.close();
389              } catch ( SQLException ex ) {
390                  JahiaException je = new JahiaException( "Cannot free resources",
391                                         "db_create_field : cannot free resources",
392                                         JahiaException.DATABASE_ERROR, JahiaException.WARNING );
393              }
394         }
395         // catches error if cannot execute insert query
396         catch (SQLException se)
397         {
398             String errorMsg = "Error in create_jahia_field : " + se.getMessage();
399             logger.error(errorMsg + " -> BAILING OUT", se );
400             throw new JahiaException( "Cannot insert new fields in the database",
401                                         errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
402         }
403     } // end db_create_field*/
404
405
406
407     /**
408      * updates a field entry in the database
409      *
410      * @param theField theJahiaField object
411      * @param saveVersion the version to save
412      *
413      * @throws JahiaException a critical JahiaException if SQL error
414      * @throws JahiaException a warning JahiaException if cannot free resources
415      */
416     public void db_update_field (JahiaField theField, JahiaSaveVersion saveVersion)
417             throws JahiaException {
418 // logger.debug("Updating field version="+saveVersion);
419 Connection dbConn = null;
420         Statement stmt = null;
421         try {
422             logger.debug (
423                     "db_update_field called!!!! isVersionID=" + saveVersion.isVersioned () + " - isStaging=" + saveVersion.isStaging ());
424             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
425             stmt = dbConn.createStatement ();
426
427             String theValue = theField.getValue ();
428             if (theField.getValue ().equals ("")) {
429                 theValue = "<empty>";
430             }
431
432             int fieldID = theField.getID ();
433             // update staged version
434 if (saveVersion.isStaging ()) {
435                 String sqlQuery = "UPDATE jahia_fields_data SET "
436                         + "jahiaid_jahia_fields_data = " + theField.getJahiaID () + ","
437                         + "pageid_jahia_fields_data = " + theField.getPageID () + ","
438                         + "ctnid_jahia_fields_data = " + theField.getctnid () + ","
439                         + "fielddefid_jahia_fields_data = " + theField.getFieldDefID () + ","
440                         + "type_jahia_fields_data = " + theField.getType () + ","
441                         + "connecttype_jahia_fields_data = " + theField.getConnectType () + ","
442                         + "value_jahia_fields_data = '" + JahiaTools.quote (theValue) + "',"
443                         + "rank_jahia_fields_data = " + theField.getRank () + ","
444                         + "rights_jahia_fields_data = " + theField.getAclID () + ","
445                         + "version_id = 0 " // no version id in staging mode
446 + "WHERE id_jahia_fields_data = " + theField.getID () + " "
447                         + "AND workflow_state>1";
448                 // executes the query
449 int rows = stmt.executeUpdate (sqlQuery);
450                 logger.debug ("ROWS=" + rows);
451                 // the staged version of the field doesn't already exist
452 if (rows == 0) {
453                     logger.debug ("Creating the staged field");
454                     createStagedField (stmt, fieldID, saveVersion);
455                     stmt.executeUpdate (sqlQuery);
456                 }
457             } else
458             // update active version
459 {
460                 if (saveVersion.isVersioned ()) {
461                     backupFieldVersion (stmt, fieldID);
462                 }
463                 // composes the query
464 String sqlQuery = "UPDATE jahia_fields_data SET "
465                         + "jahiaid_jahia_fields_data = " + theField.getJahiaID () + ","
466                         + "pageid_jahia_fields_data = " + theField.getPageID () + ","
467                         + "ctnid_jahia_fields_data = " + theField.getctnid () + ","
468                         + "fielddefid_jahia_fields_data = " + theField.getFieldDefID () + ","
469                         + "type_jahia_fields_data = " + theField.getType () + ","
470                         + "connecttype_jahia_fields_data = " + theField.getConnectType () + ","
471                         + "value_jahia_fields_data = '" + JahiaTools.quote (theValue) + "',"
472                         + "rank_jahia_fields_data = " + theField.getRank () + ","
473                         + "rights_jahia_fields_data = " + theField.getAclID () + ","
474                         + "version_id = " + saveVersion.getVersionID () + " "
475                         + "WHERE id_jahia_fields_data = " + theField.getID () + " "
476                         + "AND workflow_state=1";
477                 // executes the query
478 stmt.executeUpdate (sqlQuery);
479
480                 // updates field properties
481 props.db_save_field_properties (theField);
482             }
483         }
484                 // catches error if cannot execute update query
485 catch (SQLException se) {
486             String errorMsg = "Error in db_update_field : " + se.getMessage ();
487             logger.error (errorMsg + " -> BAILING OUT");
488             throw new JahiaException ("Cannot update fields in the database",
489                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
490         } finally {
491             closeStatement(stmt);
492         }
493
494     }
495
496     /**
497      * To be called when the site is in versioning mode, to backup the
498      * old field value before changing it.
499      */
500     private void backupFieldVersion (Statement stmt, int fieldID)
501             throws SQLException {
502         String sqlQuery = "SELECT * FROM jahia_fields_data WHERE id_jahia_fields_data = " +
503                 fieldID + " AND workflow_state=1";
504         ResultSet rs = stmt.executeQuery (sqlQuery);
505         if (rs != null)
506             if (rs.next ()) {
507                 sqlQuery = "INSERT INTO jahia_fields_data (id_jahia_fields_data,jahiaid_jahia_fields_data,pageid_jahia_fields_data,ctnid_jahia_fields_data,fielddefid_jahia_fields_data,type_jahia_fields_data,connecttype_jahia_fields_data,value_jahia_fields_data,rank_jahia_fields_data,rights_jahia_fields_data,version_id,workflow_state,language_code) VALUES (" +
508                         fieldID + "," +
509                         rs.getInt ("jahiaid_jahia_fields_data") + "," +
510                         rs.getInt ("pageid_jahia_fields_data") + "," +
511                         rs.getInt ("ctnid_jahia_fields_data") + "," +
512                         rs.getInt ("fielddefid_jahia_fields_data") + "," +
513                         rs.getInt ("type_jahia_fields_data") + "," +
514                         rs.getInt ("connecttype_jahia_fields_data") + "," +
515                         "'" + JahiaTools.quote (rs.getString ("value_jahia_fields_data")) + "'," +
516                         rs.getInt ("rank_jahia_fields_data") + "," +
517                         rs.getInt ("rights_jahia_fields_data") + "," +
518                         rs.getInt ("version_id") + "," +
519                         "0, '" +
520                         rs.getString ("language_code") + "')";
521                 stmt.execute (sqlQuery);
522             }
523     }
524
525
526     /**
527      * creates a staged version of a field, to be called when the site is in
528      * staging mode, and that a field only exist in Active mode and we want
529      * to change it...
530      */
531     private void createStagedField (Statement stmt, int fieldID, JahiaSaveVersion saveVersion)
532             throws SQLException {
533         String sqlQuery = "SELECT * FROM jahia_fields_data WHERE id_jahia_fields_data = " +
534                 fieldID + " AND workflow_state=1";
535         ResultSet rs = stmt.executeQuery (sqlQuery);
536         if (rs != null)
537             if (rs.next ()) {
538                 sqlQuery = "INSERT INTO jahia_fields_data (id_jahia_fields_data,jahiaid_jahia_fields_data,pageid_jahia_fields_data,ctnid_jahia_fields_data,fielddefid_jahia_fields_data,type_jahia_fields_data,connecttype_jahia_fields_data,value_jahia_fields_data,rank_jahia_fields_data,rights_jahia_fields_data,version_id,workflow_state, language_code) VALUES (" +
539                         fieldID + "," +
540                         rs.getInt ("jahiaid_jahia_fields_data") + "," +
541                         rs.getInt ("pageid_jahia_fields_data") + "," +
542                         rs.getInt ("ctnid_jahia_fields_data") + "," +
543                         rs.getInt ("fielddefid_jahia_fields_data") + "," +
544                         rs.getInt ("type_jahia_fields_data") + "," +
545                         rs.getInt ("connecttype_jahia_fields_data") + "," +
546                         "'" + JahiaTools.quote (rs.getString ("value_jahia_fields_data")) + "'," +
547                         rs.getInt ("rank_jahia_fields_data") + "," +
548                         rs.getInt ("rights_jahia_fields_data") + "," +
549                         saveVersion.getVersionID () + "," +
550                         saveVersion.getWorkflowState () + "," +
551                         "'" + rs.getString ("language_code") + "'" +
552                         ")";
553                 stmt.execute (sqlQuery);
554             }
555     }
556
557
558     /**
559      * deletes a field entry in the database
560      *
561      * @param fieldID the field id
562      * @param saveVersion the version to save
563      *
564      * @throws JahiaException a critical JahiaException if SQL error
565      * @throws JahiaException a warning JahiaException if cannot free resources
566      */
567
568     public void db_delete_field (int fieldID, JahiaSaveVersion saveVersion)
569             throws JahiaException {
570         Connection dbConn = null;
571         Statement stmt = null;
572         try {
573             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
574             stmt = dbConn.createStatement ();
575             // the site is in staging mode, we update the staging version
576 if (saveVersion.isStaging ()) {
577                 // composes the query
578 String sqlQuery = "UPDATE jahia_fields_data SET version_id = -1 WHERE id_jahia_fields_data = " +
579                         fieldID + " AND workflow_state>1";
580
581                 int rows = stmt.executeUpdate (sqlQuery);
582                 // the staged version of the field doesn't already exist
583 if (rows == 0) {
584                     createStagedField (stmt, fieldID, saveVersion);
585                     stmt.executeUpdate (sqlQuery);
586                 }
587                 // deletes field properties
588 // props.db_delete_field_properties( fieldID );
589 } else
590             // Versioning mode ?
591 if (saveVersion.isVersioned ()) {
592                     backupFieldVersion (stmt, fieldID);
593                     String sqlQuery = "UPDATE jahia_fields_data SET version_id = " +
594                             saveVersion.getVersionID () +
595                             ", workflow_state = -1 WHERE id_jahia_fields_data = " + fieldID +
596                             " AND workflow_state=1";
597                     stmt.executeUpdate (sqlQuery);
598                 } else
599                 // Normal mode (no staging, no versioning)
600 {
601                     String sqlQuery = "DELETE FROM jahia_fields_data WHERE id_jahia_fields_data=" + fieldID +
602                             " AND workflow_state=1";
603                     stmt.executeUpdate (sqlQuery);
604                     // deletes field properties
605 props.db_delete_field_properties (fieldID);
606                 }
607         } catch (SQLException se) {
608             String errorMsg = "Error in db_delete_field : " + se.getMessage ();
609             logger.error (errorMsg + " -> BAILING OUT");
610             logger.warn ("Cannot delete fields in the database", se);
611
612         } finally {
613             closeStatement (stmt);
614         }
615     }
616
617
618     /**
619      * Validates the staged field that way:
620      * if (versioning):backup field,
621      * update active version with staged version,
622      * delete staged version
623      *
624      * @param saveVersion with the right versionID, and right isStaging() and isVersionID()
625      */
626     public void db_validate_staged_field (int fieldID, JahiaSaveVersion saveVersion)
627             throws JahiaException {
628         Connection dbConn = null;
629         Statement stmt = null;
630         try {
631             dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
632             stmt = dbConn.createStatement ();
633             String sqlQuery = "SELECT * FROM jahia_fields_data WHERE " +
634                     "id_jahia_fields_data = " + fieldID +
635                     " AND workflow_state>1";
636             ResultSet rs = stmt.executeQuery (sqlQuery);
637             if (rs != null)
638                 if (rs.next ()) {
639                     int aclID = rs.getInt ("rights_jahia_fields_data");
640                     int field_jahiaID = rs.getInt ("jahiaid_jahia_fields_data");
641                     int field_pageID = rs.getInt ("pageid_jahia_fields_data");
642                     int field_ctnID = rs.getInt ("ctnid_jahia_fields_data");
643                     int field_defID = rs.getInt ("fielddefid_jahia_fields_data");
644                     int field_type = rs.getInt ("type_jahia_fields_data");
645                     int field_connecttype = rs.getInt ("connecttype_jahia_fields_data");
646                     String field_value = rs.getString ("value_jahia_fields_data");
647                     int field_rank = rs.getInt ("rank_jahia_fields_data");
648                     String languageCode = rs.getString ("language_code");
649                     int versionID = rs.getInt ("version_id");
650
651                     // the site is in versioning, backup version
652 if (saveVersion.isVersioned ())
653                         backupFieldVersion (stmt, fieldID);
654
655                     // we must delete this field ?
656 if (versionID == -1) {
657                         if (saveVersion.isVersioned ()) {
658                             // update staged version into inactive version
659 sqlQuery = "UPDATE jahia_fields_data SET "
660                                     + "version_id = " + saveVersion.getVersionID () + ","
661                                     + "workflow_state = -1 "
662                                     + "WHERE id_jahia_fields_data = " + fieldID + " "
663                                     + "AND workflow_state>1";
664                             stmt.executeUpdate (sqlQuery);
665                         }
666                         // delete active & staged version
667 sqlQuery = "DELETE FROM jahia_fields_data WHERE id_jahia_fields_data=" + fieldID +
668                                 " AND workflow_state>=1";
669                         stmt.executeUpdate (sqlQuery);
670                     } else
671                     // no, we must validate it
672 {
673                         // update active version
674 sqlQuery = "UPDATE jahia_fields_data SET "
675                                 + "jahiaid_jahia_fields_data = " + field_jahiaID + ","
676                                 + "pageid_jahia_fields_data = " + field_pageID + ","
677                                 + "ctnid_jahia_fields_data = " + field_ctnID + ","
678                                 + "fielddefid_jahia_fields_data = " + field_defID + ","
679                                 + "type_jahia_fields_data = " + field_type + ","
680                                 + "connecttype_jahia_fields_data = " + field_connecttype + ","
681                                 + "value_jahia_fields_data = '" + JahiaTools.quote (
682                                         field_value) + "',"
683                                 + "rank_jahia_fields_data = " + field_rank + ","
684                                 + "rights_jahia_fields_data = " + aclID + ","
685                                 + "version_id = " + saveVersion.getVersionID () + " "
686                                 + "WHERE id_jahia_fields_data = " + fieldID + " "
687                                 + "AND workflow_state=1";
688                         int rows = stmt.executeUpdate (sqlQuery);
689                         // this field didn't already exist
690 // --> we must create a new one
691 if (rows == 0) {
692                             sqlQuery = "INSERT INTO jahia_fields_data (id_jahia_fields_data,jahiaid_jahia_fields_data,pageid_jahia_fields_data,ctnid_jahia_fields_data,fielddefid_jahia_fields_data,type_jahia_fields_data,connecttype_jahia_fields_data,value_jahia_fields_data,rank_jahia_fields_data,rights_jahia_fields_data,version_id,workflow_state, language_code) VALUES (" +
693                                     fieldID + "," +
694                                     field_jahiaID + "," +
695                                     field_pageID + "," +
696                                     field_ctnID + "," +
697                                     field_defID + "," +
698                                     field_type + "," +
699                                     field_connecttype + "," +
700                                     "'" + JahiaTools.quote (field_value) + "'," +
701                                     field_rank + "," +
702                                     aclID + "," +
703                                     saveVersion.getVersionID () + "," +
704                                     "1, '" +
705                                     languageCode + "')";
706                             stmt.execute (sqlQuery);
707                         }
708
709                         // delete staged version
710 sqlQuery = "DELETE FROM jahia_fields_data WHERE id_jahia_fields_data=" + fieldID +
711                                 " AND workflow_state>1";
712                         stmt.executeUpdate (sqlQuery);
713                     }
714                 }
715         } catch (SQLException se) {
716             String errorMsg = "Error in db_validate_staged_field : " + se.getMessage ();
717             logger.error (errorMsg + " -> BAILING OUT");
718             throw new JahiaException ("Cannot update fields in the database",
719                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY,
720                     se);
721         } finally {
722             closeStatement (stmt);
723         }
724     }
725
726
727
728     /**
729      * return a DOM document of all fields of a site
730      *
731      * @param siteID the site id
732      *
733      * @return JahiaDOMObject a DOM representation of this object
734      */
735     public JahiaDOMObject getFieldsAsDOM (int siteID)
736             throws JahiaException {
737
738
739
740         JahiaDBDOMObject dom = new JahiaDBDOMObject ();
741
742         Connection dbConn = null;
743         Statement statement = null;
744         ResultSet rs = null;
745
746         StringBuffer buff = new StringBuffer ("SELECT DISTINCT id_jahia_fields_data,");
747         buff.append ("jahiaid_jahia_fields_data,");
748         buff.append ("pageid_jahia_fields_data,");
749         buff.append ("ctnid_jahia_fields_data,");
750         buff.append ("fielddefid_jahia_fields_data,");
751         buff.append ("type_jahia_fields_data,");
752         buff.append ("connecttype_jahia_fields_data,");
753         buff.append ("value_jahia_fields_data,");
754         buff.append ("rank_jahia_fields_data,");
755         buff.append (
756                 "rights_jahia_fields_data, version_id, workflow_state, language_code FROM jahia_fields_data WHERE id_jahia_fields_data");
757
758         int fieldIDMax = getBiggestFieldID ();
759         if (fieldIDMax == -1) {
760             dom = new JahiaDBDOMObject ();
761             try {
762                 dom.addTable ("jahia_fields_data", null);
763             } catch (Throwable t) {
764                 String errorMsg = "Error in getFieldsAsDOM(int siteID) : " + t.getMessage ();
765                 logger.error (errorMsg);
766                 throw new JahiaException ("Cannot load field from the database",
767                         errorMsg, JahiaException.DATABASE_ERROR,
768                         JahiaException.CRITICAL_SEVERITY);
769             }
770             return dom;
771         }
772
773         FieldFilter filter = new FieldFilter (siteID);
774
775         dom = new JahiaDBDOMObject ();
776         int low = 0;
777         int high = 0;
778         while (high <= fieldIDMax) {
779             low = high;
780             high += 5000;
781
782             StringBuffer query = new StringBuffer (buff.toString ());
783             query.append (" > ");
784             query.append (low);
785             query.append (" AND id_jahia_fields_data <= ");
786             query.append (high);
787
788             try {
789                 dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
790                 statement = dbConn.createStatement ();
791                 if (statement != null) {
792                     rs = statement.executeQuery (query.toString ());
793                     if (rs != null) {
794                         dom.addTable ("jahia_fields_data", rs, filter);
795                     }
796                 }
797             } catch (SQLException se) {
798                 se.printStackTrace ();
799                 String errorMsg = "Error in getFieldsAsDOM(int siteID) : " + se.getMessage ();
800                 logger.error (errorMsg);
801                 throw new JahiaException ("Cannot load field from the database",
802                         errorMsg, JahiaException.DATABASE_ERROR,
803                         JahiaException.CRITICAL_SEVERITY);
804             } catch (JahiaDatabaseException jdbe) {
805                 jdbe.printStackTrace ();
806                 String errorMsg = "Error in getFieldsAsDOM(int siteID) : " + jdbe.getMessage ();
807                 logger.error (errorMsg);
808                 throw new JahiaException ("Cannot load field from the database",
809                         errorMsg, JahiaException.DATABASE_ERROR,
810                         JahiaException.CRITICAL_SEVERITY);
811
812             } finally {
813
814                 closeStatement (statement);
815             }
816         }
817
818         return dom;
819
820     }
821
822     /**
823      * Return the biggest field id number.
824      *
825      * @return int , the biggest field id number or -1 on error
826      *
827      * @throws JahiaDatabaseException Thows this exception on any database failure.
828      */
829     public synchronized int getBiggestFieldID ()
830             throws JahiaDatabaseException {
831         final String MAX_ID = "MaxID";
832         int val = -1;
833
834         Connection connection = org.jahia.services.database.ConnectionDispenser.getConnection ();
835         if (connection != null) {
836             Statement statement = null;
837             StringBuffer query = new StringBuffer ();
838
839             try {
840                 query.append ("SELECT MAX(id_jahia_fields_data) as ");
841                 query.append (MAX_ID);
842                 query.append (" FROM jahia_fields_data");
843
844                 statement = connection.createStatement ();
845                 if (statement != null) {
846                     ResultSet rs = statement.executeQuery (query.toString ());
847                     if (rs != null) {
848                         if (rs.next ()) {
849                             // get the highest used acl ID
850 val = rs.getInt (MAX_ID);
851                         }
852                     }
853                 }
854             } catch (SQLException ex) {
855                 throw new JahiaDatabaseException (
856                         "Could not get the max field ID",
857                         query.toString (), ex, JahiaDatabaseException.ERROR_SEVERITY);
858             } finally {
859
860                 closeStatement (statement);
861             }
862         } else {
863             throw new JahiaDatabaseException (
864                     "Could not get a database connection while getting the max field ID",
865                     JahiaDatabaseException.CRITICAL_SEVERITY);
866         }
867
868         return val;
869     }
870
871
872     private void closeStatement (Statement statement) {
873         // Close the opened statement
874 try {
875             if (statement != null) {
876                 statement.close ();
877             }
878         } catch (SQLException sqlEx) {
879             logger.warn ("Cannot close a statement", sqlEx);
880         }
881     }
882
883
884     class FieldFilter implements DBRowDataFilter {
885
886         private int siteID;
887
888         public FieldFilter (int siteID) {
889             this.siteID = siteID;
890         }
891
892         public boolean inValue (Hashtable vals) {
893             if (vals == null) {
894                 return false;
895             }
896             String val = null;
897             try {
898                 val = (String ) vals.get ("jahiaid_jahia_fields_data");
899                 int jahiaID = Integer.parseInt (val);
900                 return (this.siteID == jahiaID);
901             } catch (Throwable t) {
902                 logger.error ("Error parsing " + val);
903
904             }
905             return false;
906         }
907     }
908
909 }
910
911

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags