|
1 package org.jahia.services.pages;
2
3 import java.sql.Connection  ;
4 import java.sql.PreparedStatement ;
5 import java.sql.ResultSet ;
6 import java.sql.SQLException ;
7 import java.sql.Statement ;
8 import java.util.ArrayList ;
9 import java.util.HashMap ;
10 import java.util.Iterator ;
11 import java.util.Map ;
12 import java.util.Vector ;
13
14 import org.apache.log4j.Logger;
15 import org.jahia.data.JahiaDBDOMObject;
16 import org.jahia.data.JahiaDOMObject;
17 import org.jahia.exceptions.JahiaException;
18 import org.jahia.exceptions.JahiaInitializationException;
19 import org.jahia.services.cache.Cache;
20 import org.jahia.services.cache.CacheFactory;
21 import org.jahia.services.database.ConnectionDispenser;
22 import org.jahia.utils.JahiaTools;
23
24
33
34 public class PagePropertyDB {
35
36 private static Logger logger = Logger.getLogger (PagePropertyDB.class);
37
38 private static PagePropertyDB singletonInstance = null;
39
40 public static final String PAGE_PROPERTY_CACHE_BYPAGEID = "PagePropertiesByPageIDCache";
42 public static final String PAGE_PROPERTY_CACHE_BYVALUE = "PagePropertiesByValueCache";
44 public static final String PAGE_ID_FOR_PAGE_KEY_CACHE = "PageIDForPageKeyCache";
45 private static Cache propertiesCacheByPageID;
46 private static Cache propertiesCacheByValue;
47 private static Cache pageIDForPageKeyCache;
49 private static final String GET_PROPVALUE_BYPAGEIDANDPROPNAME = "SELECT prop_value FROM jahia_pages_prop WHERE page_id=? AND prop_name=?";
50 private static final String GET_PROPS_BYPAGEID = "SELECT * FROM jahia_pages_prop WHERE page_id=? ORDER BY prop_name";
51 private static final String DELETE_PROP_BYPAGEIDANDPROPNAME = "DELETE FROM jahia_pages_prop WHERE page_id=? AND prop_name=?";
52 private static final String INSERT_PROP = "INSERT INTO jahia_pages_prop(page_id, prop_name, prop_value, language_code) VALUES (?, ?, ?, ?)";
53 private static final String UPDATE_PROPVALUE_BYPAGEIDANDPROPNAME = "UPDATE jahia_pages_prop SET prop_value=? WHERE page_id=? AND prop_name=? AND language_code=?";
54
55 private PagePropertyDB () {
56 try {
57 propertiesCacheByPageID = CacheFactory.createCache(
58 PAGE_PROPERTY_CACHE_BYPAGEID);
59 propertiesCacheByValue = CacheFactory.createCache(
60 PAGE_PROPERTY_CACHE_BYVALUE);
61 pageIDForPageKeyCache = CacheFactory.createCache(
62 PAGE_ID_FOR_PAGE_KEY_CACHE );
63
64 } catch (JahiaInitializationException jie) {
65 logger.error("Error while trying to initialize cache for page properties", jie);
66 }
67 }
68
69 public static PagePropertyDB getInstance () {
70 if (singletonInstance == null) {
71 singletonInstance = new PagePropertyDB ();
72 }
73 return singletonInstance;
74 }
75
76 public void setPageProperty(int pageID, String name, String value)
77 throws JahiaException {
78 setPageProperty(pageID, name, PageProperty.SHARED_LANGUAGE_MARKER, value);
79 }
80
81 public synchronized void setPageProperty(int pageID, String name, String languageCode, String value)
82 throws JahiaException {
83 Connection dbConn = null;
84 PreparedStatement stmt = null;
85
86 try {
87 propertiesCacheByValue.remove(value);
88
89 dbConn = ConnectionDispenser.getConnection();
90 stmt = dbConn.prepareStatement(UPDATE_PROPVALUE_BYPAGEIDANDPROPNAME);
91 stmt.setString(1, value);
92 stmt.setInt(2, pageID);
93 stmt.setString(3, name);
94 stmt.setString(4, languageCode);
95
96 int rowCount = stmt.executeUpdate();
97 if (rowCount == 0) {
98 closeStatement(stmt);
99 stmt = dbConn.prepareStatement(INSERT_PROP);
100
101 stmt.setInt(1, pageID);
102 stmt.setString(2, name);
103 stmt.setString(3, value);
104 stmt.setString(4, languageCode);
105 stmt.executeUpdate();
106 }
107
108 PageProperty newProperty = new PageProperty(pageID, name);
109
110 Map pageProperties = (Map ) propertiesCacheByPageID.get(new Integer (pageID));
111 if (pageProperties != null) {
112 PageProperty oldProperty = (PageProperty) pageProperties.get(name);
113 if (oldProperty != null) {
114 for (Iterator it = oldProperty.getLanguageCodes(); it.hasNext();) {
115 String lang = (String ) it.next();
116 newProperty.setValue(oldProperty.getValue(lang), lang);
117 }
118 }
119 }
120 newProperty.setValue(value, languageCode);
121 pageProperties.put(name, newProperty);
122 propertiesCacheByPageID.put(new Integer (pageID), pageProperties);
124
125 } catch (SQLException se) {
126 String errorMsg = "Error in PagePropertyDB.setPageProperty : " + se.getMessage();
127 logger.error(errorMsg + " -> BAILING OUT");
128
129 throw new JahiaException("Cannot save page property into the database", errorMsg,
130 JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY, se);
131 } finally {
132 closeStatement(stmt);
133 }
134 }
135
136 public void setPageProperty (PageProperty pageProp)
137 throws JahiaException {
138 Connection dbConn = null;
139 PreparedStatement stmt = null;
140
141 synchronized (PagePropertyDB.class) {
142 try {
143 dbConn = ConnectionDispenser.getConnection ();
144 stmt = dbConn.prepareStatement(GET_PROPVALUE_BYPAGEIDANDPROPNAME);
145 stmt.setInt(1, pageProp.getPageID());
146 stmt.setString(2, pageProp.getName());
147 ResultSet rs = stmt.executeQuery();
148 while (rs.next()) {
149 propertiesCacheByValue.remove(rs.getString(1));
150 }
151 closeStatement(stmt);
152
153 stmt = dbConn.prepareStatement(DELETE_PROP_BYPAGEIDANDPROPNAME);
154 stmt.setInt(1, pageProp.getPageID());
155 stmt.setString(2, pageProp.getName());
156 stmt.executeUpdate();
157 closeStatement(stmt);
158
159 stmt = dbConn.prepareStatement(INSERT_PROP);
160 Iterator propertyLanguagesIter = pageProp.getLanguageCodes ();
161 while (propertyLanguagesIter.hasNext ()) {
162 String curLanguageCode = (String ) propertyLanguagesIter.next ();
163 stmt.setInt(1, pageProp.getPageID());
164 stmt.setString(2, pageProp.getName());
165 stmt.setString(3, pageProp.getValue(curLanguageCode));
166 stmt.setString(4, curLanguageCode);
167 stmt.executeUpdate ();
168 propertiesCacheByValue.remove(pageProp.getValue(curLanguageCode));
169 }
170
171 Map pageProperties = (Map ) propertiesCacheByPageID.get(new Integer (pageProp.getPageID()));
172 pageProperties.put(pageProp.getName(), pageProp);
173 propertiesCacheByPageID.put(new Integer (pageProp.getPageID()), pageProperties);
175
176 } catch (SQLException se) {
177 String errorMsg = "Error in PagePropertyDB.setPageProperty : " + se.getMessage ();
178 logger.error (errorMsg + " -> BAILING OUT");
179 throw new JahiaException ("Cannot save page property into the database",
180 errorMsg,
181 JahiaException.DATABASE_ERROR,
182 JahiaException.CRITICAL_SEVERITY,
183 se);
184 } finally {
185 closeStatement(stmt);
186 }
187 }
188 }
189
190 public PageProperty getPageProperty (int pageID, String propertyName)
191 throws JahiaException {
192 Connection dbConn = null;
193 Statement stmt = null;
194 ResultSet rs = null;
195 PageProperty result = null;
196
197 try {
198 StringBuffer sqlQuery = new StringBuffer ();
199 sqlQuery.append ("SELECT * FROM jahia_pages_prop ");
200 sqlQuery.append ("WHERE page_id=");
201 sqlQuery.append (pageID);
202 sqlQuery.append (" AND prop_name='");
203 sqlQuery.append (JahiaTools.quote (propertyName));
204 sqlQuery.append ("'");
205
206 dbConn = ConnectionDispenser.getConnection ();
207 stmt = dbConn.createStatement ();
208 rs = stmt.executeQuery (sqlQuery.toString ());
209
210 int propPageID = 0;
211 String propName = "";
212 Vector values = new Vector ();
213 Vector languages = new Vector ();
214
215 while (rs.next ()) {
216 propPageID = rs.getInt ("page_id");
217 propName = rs.getString ("prop_name");
218 String curValue = rs.getString ("prop_value");
219 values.add (curValue);
220 String curLang = rs.getString ("language_code");
221 languages.add (curLang);
222 }
223
224 result = new PageProperty (propPageID, propName);
225 for (int i = 0; i < languages.size (); i++) {
226 String curLanguage = (String ) languages.elementAt (i);
227 String curValue = (String ) values.elementAt (i);
228 result.setValue (curValue, curLanguage);
229 }
230
231
232 } catch (SQLException se) {
233 String errorMsg = "Error in PagePropertyDB.getPageProperty : " + se.getMessage ();
234 logger.error (errorMsg + " -> BAILING OUT", se);
235 throw new JahiaException ("Cannot load page property from the database",
236 errorMsg,
237 JahiaException.DATABASE_ERROR,
238 JahiaException.CRITICAL_SEVERITY,
239 se);
240 } finally {
241 try {
242
243 if (stmt != null) stmt.close ();
244 } catch (SQLException ex) {
245 logger.error ("Cannot free resources", ex);
246 }
247 }
248 return result;
249 }
250
251 public void removePageProperty (PageProperty pageProp)
252 throws JahiaException {
253 Connection dbConn = null;
254 PreparedStatement stmt = null;
255
256 synchronized (PagePropertyDB.class) {
257 try {
258 dbConn = ConnectionDispenser.getConnection ();
259
260 stmt = dbConn.prepareStatement(GET_PROPVALUE_BYPAGEIDANDPROPNAME);
261 stmt.setInt(1, pageProp.getPageID());
262 stmt.setString(2, pageProp.getName());
263 ResultSet rs = stmt.executeQuery();
264 while (rs.next()) {
265 propertiesCacheByValue.remove(rs.getString(1));
266 }
267 closeStatement(stmt);
268
269 stmt = dbConn.prepareStatement(DELETE_PROP_BYPAGEIDANDPROPNAME);
270 stmt.setInt(1, pageProp.getPageID());
271 stmt.setString(2, pageProp.getName());
272 stmt.executeUpdate();
273
274 Map pageProperties = (Map ) propertiesCacheByPageID.get(new Integer (pageProp.getPageID()));
275 pageProperties.remove(pageProp.getName());
276
277 } catch (SQLException se) {
278 String errorMsg = "Error in PagePropertyDB.setPageProperty : " + se.getMessage ();
279 logger.error (errorMsg + " -> BAILING OUT");
280 throw new JahiaException ("Cannot save page property into the database",
281 errorMsg,
282 JahiaException.DATABASE_ERROR,
283 JahiaException.CRITICAL_SEVERITY,
284 se);
285 } finally {
286 closeStatement(stmt);
287 }
288 }
289 }
290
291
351
352
358 public int getPageIDFromPageKeyAndSiteID( String pageKey, int siteID )throws JahiaException{
359 Connection dbConn = null;
360 PreparedStatement stmt = null;
361 ResultSet rs = null;
362 int pageID = 0;
363 StringBuffer cacheEntryKey = new StringBuffer ( pageKey );
364 cacheEntryKey.append( "_" ).append( siteID );
365
366 if (pageIDForPageKeyCache.containsKey( cacheEntryKey.toString() )) {
367 return ((Integer )pageIDForPageKeyCache.get( cacheEntryKey.toString() )).intValue();
368 }
369
370 try {
371 dbConn = ConnectionDispenser.getConnection ();
372
373 StringBuffer query = new StringBuffer ( "SELECT page_id " );
374 query.append( " FROM jahia_pages_prop jpageprop" );
375 query.append( " INNER JOIN jahia_pages_data ON( jpageprop.page_id = jahia_pages_data.id_jahia_pages_data) " );
376 query.append( " WHERE prop_name = '" ).append( PageProperty.PAGE_URL_KEY_PROPNAME ).append( "' " );
377 query.append( " AND prop_value=?" );
378 query.append( " AND jahia_pages_data.jahiaid_jahia_pages_data =?" );
379
380 stmt = dbConn.prepareStatement( query.toString() );
381 stmt.setString(1, pageKey );
382 stmt.setInt(2, siteID );
383 rs = stmt.executeQuery ();
384
385 if(rs.next ()) {
386 pageID = rs.getInt( "page_id" );
387 }
388
389 pageIDForPageKeyCache.put( cacheEntryKey.toString(), new Integer ( pageID ) );
390 } catch (SQLException se) {
391 String errorMsg = "Error in PagePropertyDB.getPageIDFromPageKeyAndSiteID : " + se.getMessage ();
392 logger.error (errorMsg + " -> BAILING OUT", se);
393 throw new JahiaException ("Cannot load page id from the database",
394 errorMsg,
395 JahiaException.DATABASE_ERROR,
396 JahiaException.CRITICAL_SEVERITY,
397 se);
398 } finally {
399 closeStatement(stmt);
400 }
401
402 return pageID;
403 }
404
405
406 public ArrayList getPagePropertiesByValue (String propertyValue)
407 throws JahiaException {
408 Connection dbConn = null;
409 PreparedStatement stmt = null;
410 ResultSet rs = null;
411 ArrayList result = new ArrayList ();
412 PageProperty curProperty = null;
413
414 if (propertiesCacheByValue.containsKey(propertyValue)) {
415 return (ArrayList ) propertiesCacheByValue.get(propertyValue);
416 }
417
418 try {
419 dbConn = ConnectionDispenser.getConnection ();
420
421 stmt = dbConn.prepareStatement("SELECT * FROM jahia_pages_prop WHERE prop_value=? ORDER BY page_id, prop_name");
422 stmt.setString(1, propertyValue);
423
424 rs = stmt.executeQuery ();
425
426 int propPageID = 0;
427 String propName = "";
428 Vector values = new Vector ();
429 Vector languages = new Vector ();
430
431 while (rs.next ()) {
432 propPageID = rs.getInt ("page_id");
433 propName = rs.getString ("prop_name");
434 String curValue = rs.getString ("prop_value");
435 values.add (curValue);
436 String curLang = rs.getString ("language_code");
437 languages.add (curLang);
438 }
439
440 curProperty = new PageProperty (propPageID, propName);
441 for (int i = 0; i < languages.size (); i++) {
442 String curLanguage = (String ) languages.elementAt (i);
443 String curValue = (String ) values.elementAt (i);
444 curProperty.setValue (curValue, curLanguage);
445 }
446
447 result.add(curProperty);
448
449 propertiesCacheByValue.put(propertyValue, result);
450
451 } catch (SQLException se) {
452 String errorMsg = "Error in PagePropertyDB.getPageProperty : " + se.getMessage ();
453 logger.error (errorMsg + " -> BAILING OUT", se);
454 throw new JahiaException ("Cannot load page property from the database",
455 errorMsg,
456 JahiaException.DATABASE_ERROR,
457 JahiaException.CRITICAL_SEVERITY,
458 se);
459 } finally {
460 closeStatement(stmt);
461 }
462 return result;
463 }
464
465 public Map getPageProperties (int pageID) throws JahiaException {
466 Connection dbConn = null;
467 PreparedStatement stmt = null;
468 ResultSet rs = null;
469 Map properties = new HashMap ();
470
471 if (propertiesCacheByPageID.containsKey(new Integer (pageID))) {
472 return (Map ) propertiesCacheByPageID.get(new Integer (pageID));
473 }
474
475 try {
476
477 dbConn = ConnectionDispenser.getConnection ();
478 stmt = dbConn.prepareStatement(GET_PROPS_BYPAGEID);
479 stmt.setInt(1, pageID);
480 rs = stmt.executeQuery ();
481
482 PageProperty curProperty = null;
483 while (rs.next ()) {
484 int newPageID = rs.getInt ("page_id");
485 String newName = rs.getString ("prop_name");
486 String newValue = rs.getString ("prop_value");
487 String newLang = rs.getString ("language_code");
488 if (curProperty == null) {
489 curProperty = new PageProperty (newPageID, newName);
490 properties.put (curProperty.getName (), curProperty);
491 }
492 if (curProperty.getName ().equals (newName)) {
493 curProperty.setValue (newValue, newLang);
494 } else {
495 properties.put (curProperty.getName (), curProperty);
496 curProperty = new PageProperty (newPageID, newName);
497 curProperty.setValue (newValue, newLang);
498 }
499 }
500 if (curProperty != null) {
501 properties.put(curProperty.getName(), curProperty);
502 }
503 if (!propertiesCacheByPageID.containsKey(new Integer (pageID))) {
504 propertiesCacheByPageID.put(new Integer (pageID), properties);
505 }
506
507 } catch (SQLException se) {
508 String errorMsg = "Error in PagePropertyDB.getPageProperties : " + se.getMessage ();
509 logger.error (errorMsg + " -> BAILING OUT", se);
510 throw new JahiaException ("Cannot load page properties from the database",
511 errorMsg,
512 JahiaException.DATABASE_ERROR,
513 JahiaException.CRITICAL_SEVERITY,
514 se);
515 } finally {
516 closeStatement(stmt);
517 }
518 return properties;
519 }
520
521 public JahiaDOMObject getPagePropertiesAsDOM (int pageID)
522 throws JahiaException {
523
524 Connection dbConn = null;
525 Statement statement = null;
526 JahiaDBDOMObject dom = null;
527
528 try {
529 String sqlQuery = "SELECT * FROM jahia_pages_prop where page_id=" + pageID;
530
531 dbConn = ConnectionDispenser.getConnection ();
532 statement = dbConn.createStatement ();
533 if (statement != null) {
534 ResultSet rs = statement.executeQuery (sqlQuery);
535 if (rs != null) {
536 dom = new JahiaDBDOMObject ();
537 dom.addTable ("jahia_pages_prop", rs);
538 return dom;
539 }
540 }
541 } catch (SQLException se) {
542 String errorMsg = "Error in getPagePropertiesAsDOM(int pageID) : " + se.getMessage ();
543 logger.error (errorMsg + " -> BAILING OUT", se);
544 throw new JahiaException ("Cannot load page properties from the database",
545 errorMsg,
546 JahiaException.DATABASE_ERROR,
547 JahiaException.CRITICAL_SEVERITY,
548 se);
549 } finally {
550 closeStatement (statement);
551 }
552
553 return dom;
554 }
555
556 public void invalidateCacheForPage(int pageID) {
557 Map pageProperties = (Map ) propertiesCacheByPageID.get(new Integer (pageID));
558 if (pageProperties == null) {
559 return;
560 }
561 Iterator propertyIter = pageProperties.values().iterator();
562 while (propertyIter.hasNext()) {
563 PageProperty curProperty = (PageProperty) propertyIter.next();
564 propertiesCacheByValue.remove(curProperty.getValue());
565 }
566 propertiesCacheByPageID.remove(new Integer (pageID));
567 }
568
569 public void preloadCacheByPageID(ArrayList pageIDs) {
570 Connection dbConn = null;
571 PreparedStatement stmt = null;
572 ResultSet rs = null;
573 Map properties = new HashMap ();
574
575 int highestPageID = -1;
579 Iterator pageIDIter = pageIDs.iterator();
580 while (pageIDIter.hasNext()) {
581 Integer curPageID = (Integer ) pageIDIter.next();
582 if (curPageID.intValue() > highestPageID) {
583 highestPageID = curPageID.intValue();
584 }
585 if (!propertiesCacheByPageID.containsKey(curPageID)) {
586 propertiesCacheByPageID.put(curPageID, new HashMap ());
587 }
588 }
589
590 try {
591
592 dbConn = ConnectionDispenser.getConnection ();
593 stmt = dbConn.prepareStatement("SELECT * FROM jahia_pages_prop ORDER BY page_id, prop_name");
594 rs = stmt.executeQuery ();
595
596 PageProperty curProperty = null;
597 int lastPageID = -1;
598 int pageCounter = 0;
599 while ((rs.next ()) && (pageCounter < pageIDs.size())) {
600 int newPageID = rs.getInt ("page_id");
601 String newName = rs.getString ("prop_name");
602 String newValue = rs.getString ("prop_value");
603 String newLang = rs.getString ("language_code");
604 if ((newPageID != lastPageID) && (lastPageID != -1)) {
605 pageCounter++;
606 propertiesCacheByPageID.put(new Integer (lastPageID), properties);
607 properties = new HashMap ();
608 curProperty = null;
609 }
610 if (curProperty == null) {
611 curProperty = new PageProperty (newPageID, newName);
612 properties.put (curProperty.getName (), curProperty);
613 }
614 if (curProperty.getName ().equals (newName)) {
615 curProperty.setValue (newValue, newLang);
616 } else {
617 curProperty = new PageProperty (newPageID, newName);
618 curProperty.setValue (newValue, newLang);
619 properties.put (curProperty.getName (), curProperty);
620 }
621 lastPageID = newPageID;
622 }
623 propertiesCacheByPageID.put(new Integer (lastPageID), properties);
624 logger.debug("Page properties preloaded for " + pageCounter + " pages");
625
626 } catch (SQLException se) {
627 String errorMsg = "Error in PagePropertyDB.getPageProperties : " + se.getMessage ();
628 logger.error (errorMsg + " -> BAILING OUT", se);
629 } finally {
630 closeStatement(stmt);
631 }
632 }
633
634 private void closeStatement (Statement statement) {
635 try {
637 if (statement != null) {
638 statement.close ();
639 }
640 } catch (SQLException sqlEx) {
641 logger.warn ("Cannot close a statement", sqlEx);
642 }
643 }
644
645
653 public boolean isKeyAlreadyUsedInSiteWithID( String key, int siteID, int currentPageID )throws JahiaException{
654 boolean used = false;
655 Connection dbConn = null;
656 ResultSet rs = null;
657 Statement stmt = null;
658
659 try {
660 dbConn = ConnectionDispenser.getConnection ();
661 stmt = dbConn.createStatement();
662 StringBuffer query = new StringBuffer ( "SELECT count(jpageprop.page_id) as usageCount" );
663 query.append( " FROM jahia_pages_prop jpageprop INNER JOIN jahia_pages_data ON jpageprop.page_id = jahia_pages_data.id_jahia_pages_data" );
664 query.append( " WHERE prop_name = '" ).append( PageProperty.PAGE_URL_KEY_PROPNAME ).append( "'" );
665 query.append( " AND prop_value = '" ).append( JahiaTools.quote( key ) ).append( "'" );
666 query.append( " AND page_id != " ).append( currentPageID );
667 query.append( " AND jahia_pages_data.jahiaid_jahia_pages_data =" ).append( siteID );
668
669 rs = stmt.executeQuery( query.toString() );
670
671 if(rs.next ()) {
672 used = rs.getInt( "usageCount" ) != 0;
673 }
674 } catch (SQLException se) {
675 String errorMsg = "Error in PagePropertyDB.isKeyAlreadyUsedInSiteWithID : " + se.getMessage ();
676 logger.error (errorMsg + " -> BAILING OUT", se);
677 throw new JahiaException ("Cannot check for url key uniqueness from the database",
678 errorMsg,
679 JahiaException.DATABASE_ERROR,
680 JahiaException.CRITICAL_SEVERITY,
681 se);
682 } finally {
683 closeStatement(stmt);
684 }
685 return used;
686 }
687 }
688 |
Popular Tags |
Java API By Example, From Geeks To Geeks.