001: //=============================================================================
002: //=== Copyright (C) 2001-2007 Food and Agriculture Organization of the
003: //=== United Nations (FAO-UN), United Nations World Food Programme (WFP)
004: //=== and United Nations Environment Programme (UNEP)
005: //===
006: //=== This program is free software; you can redistribute it and/or modify
007: //=== it under the terms of the GNU General Public License as published by
008: //=== the Free Software Foundation; either version 2 of the License, or (at
009: //=== your option) any later version.
010: //===
011: //=== This program is distributed in the hope that it will be useful, but
012: //=== WITHOUT ANY WARRANTY; without even the implied warranty of
013: //=== MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: //=== General Public License for more details.
015: //===
016: //=== You should have received a copy of the GNU General Public License
017: //=== along with this program; if not, write to the Free Software
018: //=== Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
019: //===
020: //=== Contact: Jeroen Ticheler - FAO - Viale delle Terme di Caracalla 2,
021: //=== Rome - Italy. email: geonetwork@osgeo.org
022: //==============================================================================
023:
024: package org.fao.gast.lib;
025:
026: import java.io.File;
027: import java.io.FileInputStream;
028: import java.io.FileNotFoundException;
029: import java.io.FileOutputStream;
030: import java.io.IOException;
031: import java.sql.SQLException;
032: import java.util.ArrayList;
033: import java.util.HashMap;
034: import java.util.Iterator;
035: import java.util.List;
036: import java.util.Map;
037: import java.util.Set;
038: import java.util.StringTokenizer;
039: import java.util.UUID;
040: import jeeves.resources.dbms.Dbms;
041: import jeeves.utils.BinaryFile;
042: import org.fao.gast.lib.druid.Import;
043: import org.fao.geonet.kernel.AccessManager;
044: import org.fao.geonet.kernel.DataManager;
045: import org.fao.geonet.kernel.XmlSerializer;
046: import org.fao.geonet.util.ISODate;
047: import org.jdom.Document;
048: import org.jdom.Element;
049:
050: //=============================================================================
051:
052: public class DatabaseLib {
053: //---------------------------------------------------------------------------
054: //---
055: //--- Interfaces
056: //---
057: //---------------------------------------------------------------------------
058:
059: public static interface CallBack {
060: public void schemaObjects(int count);
061:
062: public void removed(String object, String type);
063:
064: public void cyclicRefs(List<String> objects);
065:
066: public void creating(String object, String type);
067:
068: public void skipping(String table);
069:
070: public void filling(String table, String file);
071: }
072:
073: //---------------------------------------------------------------------------
074:
075: public static interface Mapper {
076: public Object map(String field, Object value);
077: }
078:
079: //---------------------------------------------------------------------------
080: //---
081: //--- Constructor
082: //---
083: //---------------------------------------------------------------------------
084:
085: public DatabaseLib(String appPath) {
086: this .appPath = appPath;
087: }
088:
089: //---------------------------------------------------------------------------
090: //---
091: //--- API methods
092: //---
093: //---------------------------------------------------------------------------
094:
095: /** Transactional */
096:
097: public void setup(Resource resource, CallBack cb) throws Exception {
098: Dbms dbms = (Dbms) resource.open();
099:
100: try {
101: removeObjects(dbms, cb);
102: createSchema(dbms, cb);
103:
104: //--- needed for PostgreSQL
105: dbms.commit();
106:
107: fillTables(dbms, cb);
108: setupSiteId(dbms);
109: setupVersion(dbms);
110:
111: //--- the commit is needed by subsequent addTemplates method
112: dbms.commit();
113:
114: addTemplates(dbms);
115: Lib.metadata.clearIndexes();
116:
117: resource.close();
118: } catch (Exception e) {
119: resource.abort();
120: throw e;
121: }
122: }
123:
124: //---------------------------------------------------------------------------
125: /** NOT Transactional */
126:
127: public String getSetting(Dbms dbms, String path)
128: throws SQLException {
129: String query = "SELECT id, value FROM Settings WHERE parentId=? AND name=?";
130:
131: StringTokenizer st = new StringTokenizer(path, "/");
132:
133: int parent = 0;
134: String value = null;
135:
136: while (st.hasMoreTokens()) {
137: String name = st.nextToken();
138: List list = dbms.select(query, parent, name).getChildren();
139:
140: if (list.size() == 0)
141: return null;
142:
143: Element sett = (Element) list.get(0);
144:
145: parent = Integer.parseInt(sett.getChildText("id"));
146: value = sett.getChildText("value");
147: }
148:
149: return value;
150: }
151:
152: //---------------------------------------------------------------------------
153: /** NOT Transactional */
154:
155: public void insert(Dbms dbms, String table, List records,
156: String fields[], Mapper mapper) throws SQLException {
157: for (Object rec : records)
158: insert(dbms, table, (Element) rec, fields, mapper);
159: }
160:
161: //---------------------------------------------------------------------------
162: /** NOT Transactional */
163:
164: public void insert(Dbms dbms, String table, Element rec,
165: Mapper mapper) throws SQLException {
166: Map<String, Object> fields = new HashMap<String, Object>();
167:
168: for (Object e : rec.getChildren()) {
169: Element elem = (Element) e;
170: String name = elem.getName();
171: String value = elem.getText();
172:
173: fields.put(name, value);
174: }
175:
176: insert(dbms, table, fields, mapper);
177: }
178:
179: //---------------------------------------------------------------------------
180: /** NOT Transactional */
181:
182: public void insert(Dbms dbms, String table, Element rec,
183: String fields[], Mapper mapper) throws SQLException {
184: Map<String, Object> map = new HashMap<String, Object>();
185:
186: for (String field : fields)
187: map.put(field, rec.getChildText(field.toLowerCase()));
188:
189: insert(dbms, table, map, mapper);
190: }
191:
192: //---------------------------------------------------------------------------
193: /** NOT Transactional */
194:
195: public void insert(Dbms dbms, String table,
196: Map<String, Object> fields, Mapper mapper)
197: throws SQLException {
198: StringBuffer names = new StringBuffer();
199: StringBuffer marks = new StringBuffer();
200:
201: ArrayList<Object> values = new ArrayList<Object>();
202:
203: for (Iterator<String> i = fields.keySet().iterator(); i
204: .hasNext();) {
205: String name = i.next();
206: Object value = fields.get(name);
207:
208: names.append(name);
209: marks.append("?");
210:
211: if (mapper != null)
212: value = mapper.map(name, value);
213:
214: values.add(value);
215:
216: if (i.hasNext()) {
217: names.append(", ");
218: marks.append(", ");
219: }
220: }
221:
222: String query = "INSERT INTO " + table + "(" + names
223: + ") VALUES (" + marks + ")";
224:
225: dbms.execute(query, values.toArray());
226: }
227:
228: //---------------------------------------------------------------------------
229:
230: public void insert(Dbms dbms, String table, Set<String> langs,
231: String id, String label) throws SQLException {
232: Map<String, Object> hm = new HashMap<String, Object>();
233:
234: hm.put("idDes", new Integer(id));
235: hm.put("label", label);
236:
237: for (String lang : langs) {
238: hm.put("langId", lang);
239:
240: Lib.database.insert(dbms, table, hm, null);
241: }
242: }
243:
244: //---------------------------------------------------------------------------
245: /** NOT Transactional */
246:
247: public int getNextSerial(Dbms dbms, String table)
248: throws SQLException {
249: List list = dbms
250: .select("SELECT max(id) as maxid FROM " + table)
251: .getChildren();
252:
253: if (list.size() == 0)
254: return 1;
255:
256: Element rec = (Element) list.get(0);
257:
258: return Integer.parseInt(rec.getChildText("maxid")) + 1;
259: }
260:
261: //---------------------------------------------------------------------------
262: //---
263: //--- Private setup methods
264: //---
265: //---------------------------------------------------------------------------
266:
267: /** Transactional */
268:
269: private void removeObjects(Dbms dbms, CallBack cb)
270: throws FileNotFoundException, IOException {
271: Lib.log.info("Removing database objects");
272:
273: List<String> schema = loadSchemaFile(dbms.getURL());
274:
275: //--- step 1 : collect objects to remove
276:
277: ArrayList<ObjectInfo> objects = new ArrayList<ObjectInfo>();
278:
279: for (String row : schema)
280: if (row.toUpperCase().startsWith("CREATE ")) {
281: ObjectInfo oi = new ObjectInfo();
282: oi.name = getObjectName(row);
283: oi.type = getObjectType(row);
284:
285: if (!oi.type.toLowerCase().equals("index"))
286: objects.add(oi);
287: }
288:
289: //--- step 2 : remove objects
290:
291: if (cb != null)
292: cb.schemaObjects(objects.size());
293:
294: while (true) {
295: boolean removed = false;
296:
297: for (Iterator<ObjectInfo> i = objects.iterator(); i
298: .hasNext();) {
299: ObjectInfo oi = i.next();
300: String query = "DROP " + oi.type + " " + oi.name;
301:
302: if (safeExecute(dbms, query)) {
303: removed = true;
304: i.remove();
305:
306: if (cb != null)
307: cb.removed(oi.name, oi.type);
308: }
309: }
310:
311: if (objects.size() == 0)
312: return;
313:
314: //--- if no object was removed then we have a cyclic loop
315:
316: if (!removed) {
317: if (cb != null) {
318: ArrayList<String> al = new ArrayList<String>();
319:
320: for (ObjectInfo oi : objects)
321: al.add(oi.name);
322:
323: cb.cyclicRefs(al);
324: }
325:
326: return;
327: }
328: }
329: }
330:
331: //---------------------------------------------------------------------------
332:
333: private boolean safeExecute(Dbms dbms, String query) {
334: try {
335: dbms.execute(query);
336:
337: //--- as far as I remember, PostgreSQL needs a commit even for DDL
338: dbms.commit();
339:
340: return true;
341: } catch (SQLException e) {
342: dbms.abort();
343:
344: return false;
345: }
346: }
347:
348: //---------------------------------------------------------------------------
349:
350: public void createSchema(Dbms dbms, CallBack cb)
351: throws FileNotFoundException, IOException, SQLException {
352: Lib.log.info("Creating database schema");
353:
354: List<String> schema = loadSchemaFile(dbms.getURL());
355:
356: StringBuffer sb = new StringBuffer();
357:
358: for (String row : schema)
359: if (!row.toUpperCase().startsWith("REM")
360: && !row.startsWith("--") && !row.trim().equals("")) {
361: sb.append(" ");
362: sb.append(row);
363:
364: if (row.endsWith(";")) {
365: String sql = sb.toString();
366:
367: sql = sql.substring(0, sql.length() - 1);
368:
369: if (cb != null)
370: cb.creating(getObjectName(sql),
371: getObjectType(sql));
372:
373: dbms.execute(sql);
374: sb = new StringBuffer();
375: }
376: }
377: }
378:
379: //---------------------------------------------------------------------------
380: /** Transaction must be aborted on error */
381:
382: public void fillTables(Dbms dbms, CallBack cb) throws Exception {
383: Lib.log.info("Filling database tables");
384:
385: List<String> schema = loadSchemaFile(dbms.getURL());
386:
387: for (String row : schema)
388: if (row.toUpperCase().startsWith("CREATE TABLE ")) {
389: String table = getObjectName(row);
390: String file = appPath + SETUP_DIR + "/db/" + table
391: + ".ddf";
392:
393: if (!new File(file).exists()) {
394: if (cb != null)
395: cb.skipping(table);
396: } else {
397: if (cb != null)
398: cb.filling(table, file);
399:
400: Lib.log.debug(" - Filling table : " + table);
401:
402: Import.load(dbms.getConnection(), table, file);
403: dbms.commit();
404: }
405: }
406: }
407:
408: //---------------------------------------------------------------------------
409: //---
410: //--- Private methods
411: //---
412: //---------------------------------------------------------------------------
413:
414: private List<String> loadSchemaFile(String url)
415: throws FileNotFoundException, IOException {
416: //--- find out which dbms schema to load
417:
418: String file = "create-db-mckoi.sql";
419:
420: if (url.indexOf("oracle") != -1)
421: file = "create-db-oracle.sql";
422:
423: else if (url.indexOf("mysql") != -1)
424: file = "create-db-mysql.sql";
425:
426: else if (url.indexOf("postgresql") != -1)
427: file = "create-db-postgres.sql";
428:
429: //--- load the dbms schema
430:
431: return Lib.text.load(appPath + SETUP_DIR + "/sql/" + file);
432: }
433:
434: //---------------------------------------------------------------------------
435:
436: private String getObjectName(String createStatem) {
437: StringTokenizer st = new StringTokenizer(createStatem, " ");
438: st.nextToken();
439: st.nextToken();
440:
441: return st.nextToken();
442: }
443:
444: //---------------------------------------------------------------------------
445:
446: private String getObjectType(String createStatem) {
447: StringTokenizer st = new StringTokenizer(createStatem, " ");
448: st.nextToken();
449:
450: return st.nextToken();
451: }
452:
453: //---------------------------------------------------------------------------
454:
455: private void addTemplates(Dbms dbms) throws Exception {
456: String siteId = getSiteId(dbms);
457: String siteURL = Lib.site.getSiteURL(dbms);
458: String date = new ISODate().toString();
459:
460: int serial = 1;
461:
462: File schemaDir = new File(appPath, "gast/setup/templates");
463:
464: for (File schema : Lib.io.scanDir(schemaDir))
465: //--- skip '.svn' folders and other hidden files
466: if (!schema.getName().startsWith("."))
467: for (File temp : Lib.io.scanDir(schema, "xml")) {
468: Lib.log.debug(" - Adding template file : "
469: + temp.getName());
470:
471: Document doc = Lib.xml.load(temp);
472: String uuid = UUID.randomUUID().toString();
473: Element xml = doc.getRootElement();
474:
475: String file = temp.getName();
476: String templ = "y";
477: String title = null;
478:
479: if (file.startsWith("sub-")) {
480: templ = "s";
481: title = file.substring(4, file.length() - 4);
482: }
483:
484: //--- templates are by default assigned to administrator/intranet group
485:
486: Lib.metadata.insertMetadata(dbms, schema.getName(),
487: xml, serial, siteId, date, date, uuid, 1,
488: null, templ, title);
489:
490: setupTemplatePriv(dbms, serial);
491: dbms.commit();
492: serial++;
493: }
494: }
495:
496: //---------------------------------------------------------------------------
497: /** NOT Transactional */
498: /** This method should be called only during setup, when the database is empty
499: * and there is only 1 siteId string into settings */
500:
501: private String getSiteId(Dbms dbms) throws SQLException {
502: String query = "SELECT value FROM Settings WHERE name='siteId'";
503: List list = dbms.select(query).getChildren();
504: Element rec = (Element) list.get(0);
505:
506: return rec.getChildText("value");
507: }
508:
509: //---------------------------------------------------------------------------
510:
511: private void setupTemplatePriv(Dbms dbms, int id)
512: throws SQLException {
513: String query = "INSERT INTO OperationAllowed(groupId, metadataId, operationId) "
514: + "VALUES(?, ?, ?)";
515:
516: dbms
517: .execute(query, 1, id, new Integer(
518: AccessManager.OPER_VIEW));
519: }
520:
521: //---------------------------------------------------------------------------
522:
523: private void setupSiteId(Dbms dbms) throws SQLException,
524: IOException {
525: String uuid = UUID.randomUUID().toString();
526:
527: //--- duplicate dummy logo to reflect the uuid
528:
529: FileInputStream is = new FileInputStream(appPath
530: + "/gast/images/dummy.gif");
531: FileOutputStream os = new FileOutputStream(appPath
532: + "/web/geonetwork/images/logos/" + uuid + ".gif");
533: BinaryFile.copy(is, os, true, true);
534:
535: dbms.execute("UPDATE Settings SET value=? WHERE name='siteId'",
536: uuid);
537: }
538:
539: //---------------------------------------------------------------------------
540:
541: private void setupVersion(Dbms dbms) throws SQLException {
542: String version = Lib.server.getVersion();
543: String subVersion = Lib.server.getSubVersion();
544:
545: dbms.execute(
546: "UPDATE Settings SET value=? WHERE name='version'",
547: version);
548: dbms.execute(
549: "UPDATE Settings SET value=? WHERE name='subVersion'",
550: subVersion);
551: }
552:
553: //---------------------------------------------------------------------------
554: //---
555: //--- Variables
556: //---
557: //---------------------------------------------------------------------------
558:
559: private String appPath;
560:
561: //---------------------------------------------------------------------------
562:
563: private static final String SETUP_DIR = "/gast/setup";
564: }
565:
566: //=============================================================================
567:
568: class ObjectInfo {
569: public String name;
570: public String type;
571: }
572:
573: //=============================================================================
|