KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > raptus > owxv3 > SQLDataManager


1 /*
2  * eAdmin/OWX
3  * Copyright (C) 1996-2003 OWX-Project Team <owx-team@gmx.net>
4  */

5
6 package com.raptus.owxv3;
7
8 import java.util.*;
9 import java.sql.*;
10 import javax.sql.*;
11
12 /**
13  * A datamanager that can create DB (prefixed) tables, put localized
14  * default data into it
15  *
16  * <hr>
17  * <table width="100%" border="0">
18  * <tr>
19  * <td width="24%"><b>Filename</b></td><td width="76%">SQLDataManager.java</td>
20  * </tr>
21  * <tr>
22  * <td width="24%"><b>Author</b></td><td width="76%">Guy Z�rcher (gzuercher@raptus.com)</td>
23  * </tr>
24  * <tr>
25  * <td width="24%"><b>Date</b></td><td width="76%">16th of April 2001</td>
26  * </tr>
27  * </table>
28  * <hr>
29  * <table width="100%" border="0">
30  * <tr>
31  * <td width="24%"><b>Date / Author</b></td><td width="76%"><b>Changes</b></td>
32  * </tr>
33  * </table>
34  * <hr>
35  */

36 public class SQLDataManager extends PropertiesDataManager
37 {
38     protected static final String JavaDoc PROPERTY_DATATYPE = "data.type";
39     protected static final String JavaDoc PROPERTY_TYPE_NAMESPACED = "namespaced";
40     protected static final String JavaDoc PROPERTY_TYPE_DIRECT = "direct";
41     protected static final String JavaDoc PROPERTY_SQLSETTINGS = "data.sql.settings";
42     protected static final String JavaDoc PROPERTY_SQLTABLES = "data.sql.tables";
43     protected static final String JavaDoc PROPERTY_SQLTABLE = "data.sql.table.";
44     protected static final String JavaDoc PROPERTY_SQLTABLE_CREATE = ".createstmt";
45     protected static final String JavaDoc PROPERTY_SQLTABLE_STATEMENT = ".statement";
46
47     /**
48      *
49      */

50     protected Hashtable sqlTables = new Hashtable();
51     protected String JavaDoc settings = null;
52
53
54     /**
55      *Added by REEA.<br>
56      *A Hashtable that holds the mapping between tables and vmodules.<br>
57      *Contains entries like ("nwslistnews","news").<br>
58      *This allows that a table to be only in one vmodule.<br>
59      *(this is what we want because if a table belongs to more than one vmodule we can't <br>
60      *find out based only on a tablename to wich vmodule belongs to)<br>
61      *
62      */

63    protected Hashtable vmoduleTables= new Hashtable();
64
65     /**
66      *
67      */

68     protected boolean namespaced = false;
69
70     /**
71      *
72      */

73     protected javax.sql.DataSource JavaDoc ds = null;
74
75     /**
76      *
77      */

78     public boolean initialize(String JavaDoc prefix, DataSource ds)
79     {
80         LoggingManager.log("SQLDataManager initialise!, prefix="+prefix, this);
81 // if(!super.initialize(file, prefix))
82
// return false;
83

84         this.ds = ds;
85
86         process();
87         return true;
88     }
89
90     /**
91      *
92      */

93     public boolean initialize(VModule m)
94     {
95         LoggingManager.log("SQLDataManager.initialise, vm="+m.getIdentification(), this);
96         if(!super.initialize(m))
97         {
98             LoggingManager.log("super.initialise failed!", this);
99             //return false;
100
}
101
102         ds = vmodule.getDatasource();
103
104         process(m);
105         return true;
106     }
107
108
109
110     /**
111      *
112      */

113     public void process()
114     {
115         process(null);
116
117     }
118
119     /**
120      *
121      */

122     public void process(VModule vm)
123     {
124         LoggingManager.log("SQLDataManager.process(),vm="+vm, this);
125         XMLConfigManager xmlc = XMLConfigManager.getInstance();
126         Vector tableStatements = new Vector();
127         
128         int tableCount = 0;
129         int stmtCount = 0;
130         
131         if(vm == null)
132         {
133             LoggingManager.log("Processing in SQLDataManager() with vm=null", this);
134             String JavaDoc tablesprop = xmlc.getPropertyByTree("virtualhost/global_resource/property?name=tables", "value");
135
136             LoggingManager.log("Tables are :"+tablesprop, this);
137             
138             if(tablesprop != null && tablesprop.length() > 0)
139             {
140                 StringTokenizer st = new StringTokenizer(tablesprop, ",");
141                 while(st.hasMoreTokens())
142                 {
143                     String JavaDoc tableName = st.nextToken();
144                     String JavaDoc type = xmlc.getPropertyByTree("virtualhost/global_resource/table?name="+tableName, "type");
145                     if(type != null && type.compareToIgnoreCase(PROPERTY_TYPE_NAMESPACED) == 0)
146                         namespaced = true;
147                     LoggingManager.log("Table is namespaced", this);
148                     String JavaDoc statements = xmlc.getPropertyByTree("virtualhost/global_resource/table?name="+tableName, "statements");
149                     LoggingManager.log("statements="+statements, this);
150                     // read all sql statements for this table, beginning with create!
151
int i = 1;
152                     StringTokenizer stt = new StringTokenizer(statements, ",");
153                     while(stt.hasMoreTokens())
154                     {
155                         String JavaDoc createStmt = xmlc.getPropertyByTree("virtualhost/global_resource/table?name="+tableName+"/statement?name="+stt.nextToken(), "");
156                         LoggingManager.log("Statement="+createStmt, this);
157                         if(createStmt != null && createStmt.length() > 0)
158                         {
159                             if(namespaced)
160                                 createStmt = parseAndPrefixTablestatement(createStmt, tableName);
161                             tableStatements.add(createStmt);
162                         }
163
164                         stmtCount ++;
165                     }
166 //
167
sqlTables.put(tableName, tableStatements);
168                     tableCount ++;
169 // /**
170
// *added by REEA
171
// */
172
// if(vm!=null){
173
//
174
// //putting table name with prefix
175
// vmoduleTables.put(tableName+vm.getIdentification(),vm.getIdentification());
176
// //LoggingManager.log("Putting "+tableName+vm.getIdentification()+" for vmodule "+ vm.getIdentification());
177
// }
178
// /**
179
// *end added by REEA
180
// */
181
}
182
183                 LoggingManager.log("Found " + tableCount + " tables and " +
184                                    stmtCount + " statements", this);
185             }
186             else
187                 LoggingManager.log("Failed to find property " + PROPERTY_SQLTABLES, this);
188 // if(dtype != null && dtype.compareToIgnoreCase(PROPERTY_TYPE_NAMESPACED) == 0)
189
// namespaced = true;
190

191         }
192         else
193         {
194             // we have a vmodule
195
LoggingManager.log("Processing in SQLDataManager() with vm="+vm, this);
196             String JavaDoc tablesprop = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getIdentification()+"/properties/property?name=tables", "value");
197             LoggingManager.log("Tables are :"+tablesprop, this);
198             if(tablesprop == null && vm.isVirtual())
199             {
200                 tablesprop = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getBaseModule()+"/properties/property?name=tables", "value");
201                 LoggingManager.log("Tables are (from basemodule):"+tablesprop, this);
202             }
203             
204             if( tablesprop== null)
205             {
206                 tablesprop="";
207             }
208             
209                 StringTokenizer st = new StringTokenizer(tablesprop, ",");
210                 while(st.hasMoreTokens())
211                 {
212
213                     String JavaDoc tableName = st.nextToken();
214                     String JavaDoc type = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getIdentification()+"/table?name="+tableName, "type");
215                     if(type == null&&vm.isVirtual())
216                     {
217                         // try base module
218
type = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getBaseModule()+"/table?name="+tableName, "type");
219                         LoggingManager.log("Got table type from base module "+type, this);
220                     }
221                     if(type != null && type.compareToIgnoreCase(PROPERTY_TYPE_NAMESPACED) == 0)
222                         namespaced = true;
223                     LoggingManager.log("Table is namespaced", this);
224                     
225                     String JavaDoc statements = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getIdentification()+"/table?name="+tableName, "statements");
226                     if(statements == null && vm.isVirtual())
227                     {
228                         statements = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getBaseModule()+"/table?name="+tableName, "statements");
229                         LoggingManager.log("Got statements from base module!", this);
230                     }
231                     LoggingManager.log("statements="+statements, this);
232                     
233                     // read all sql statements for this table, beginning with create!
234
int i = 1;
235                     StringTokenizer stt = new StringTokenizer(statements, ",");
236                     while(stt.hasMoreTokens())
237                     {
238                         String JavaDoc stok = stt.nextToken();
239                         String JavaDoc createStmt = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getIdentification()+"/table?name="+tableName+"/statement?name="+stok, "");
240                         if(createStmt == null && vm.isVirtual())
241                         {
242                             createStmt = xmlc.getPropertyByTree("virtualhost/vmodules/vmodule?name="+vm.getBaseModule()+"/table?name="+tableName+"/statement?name="+stok, "");
243                             LoggingManager.log("Got create statement from base module!", this);
244                         }
245                         LoggingManager.log("Statement="+createStmt, this);
246                         if(createStmt != null && createStmt.length() > 0)
247                         {
248                             if(namespaced)
249                                 createStmt = parseAndPrefixTablestatement(createStmt, tableName);
250                             tableStatements.add(createStmt);
251                         }
252
253                         stmtCount ++;
254                     }
255 //
256
sqlTables.put(tableName, tableStatements);
257                     tableCount ++;
258 // /**
259
// *added by REEA
260
// */
261
if(vm!=null){
262
263                         //putting table name with prefix
264
vmoduleTables.put(tableName+vm.getIdentification(),vm.getIdentification());
265                         LoggingManager.log("Putting "+tableName+vm.getIdentification()+" for vmodule "+ vm.getIdentification(), this);
266                     }
267 // /**
268
// *end added by REEA
269
// */
270
}
271
272                 LoggingManager.log("Found " + tableCount + " tables and " +
273                                    stmtCount + " statements", this);
274 //-------------------------------------------
275

276 // String dtype = getProperty(PROPERTY_DATATYPE);
277

278 // if(dtype != null && dtype.compareToIgnoreCase(PROPERTY_TYPE_NAMESPACED) == 0)
279
// namespaced = true;
280

281             //LoggingManager.log( ((namespaced) ? "Enabled" : "Disabled") + " namespacing for " + propPrefix, this);
282

283 // LoggingManager.log( ((namespaced) ? "Enabled" : "Disabled") + " namespacing for " + propPrefix);
284
// // 20020128/gz: load settings statements
285
// Vector tableStatements = new Vector();
286
// settings = getProperty(PROPERTY_SQLSETTINGS);
287

288             // read all statements
289
// String tablesprop = getProperty(PROPERTY_SQLTABLES);
290
// if(tablesprop != null && tablesprop.length() > 0)
291
// {
292
// int tableCount = 0;
293
// int stmtCount = 0;
294
// StringTokenizer st = new StringTokenizer(tablesprop, ",");
295
// while(st.hasMoreTokens())
296
// {
297
// String tableName = st.nextToken();
298
//
299
// // read all sql statements for this table, beginning with create!
300
// int i = 1;
301
// String createStmt = getProperty(PROPERTY_SQLTABLE + tableName +
302
// PROPERTY_SQLTABLE_CREATE);
303
// if(createStmt != null && createStmt.length() > 0)
304
// {
305
// if(namespaced)
306
// createStmt = parseAndPrefixTablestatement(createStmt, tableName);
307
// tableStatements.add(createStmt);
308
// }
309
// else
310
// LoggingManager.log("Missing CREATE statement for table " + tableName);
311
//
312
// String proppfx = PROPERTY_SQLTABLE + tableName + PROPERTY_SQLTABLE_STATEMENT;
313
// String prop = proppfx + i;
314
// String stmt = null;
315
// while( (stmt = getProperty(prop)) != null )
316
// {
317
// if(stmt.length() > 0)
318
// {
319
// if(namespaced)
320
// stmt = parseAndPrefixTablestatement(stmt, tableName);
321
// tableStatements.add(stmt);
322
// }
323
// else
324
// LoggingManager.log("Ignoring SQL statement #" + i);
325
//
326
// i ++;
327
// prop = proppfx + i;
328
// stmtCount ++;
329
// }
330
//
331
// sqlTables.put(tableName, tableStatements);
332
// tableCount ++;
333
// /**
334
// *added by REEA
335
// */
336
// if(vm!=null){
337
//
338
// //putting table name with prefix
339
// vmoduleTables.put(tableName+vm.getIdentification(),vm.getIdentification());
340
// //LoggingManager.log("Putting "+tableName+vm.getIdentification()+" for vmodule "+ vm.getIdentification());
341
// }
342
// /**
343
// *end added by REEA
344
// */
345
// }
346
//
347
// LoggingManager.log("Found " + tableCount + " tables and " +
348
// stmtCount + " statements");
349
// }
350
// else
351
// LoggingManager.log("Failed to find property " + PROPERTY_SQLTABLES);
352
}
353     }
354
355     /**
356      * This method handles the automatic prefixing of database table names
357      * during their creation phase. In fact this is more a suffix �-)
358      * The prefix/suffix is the virtual module identification. So caution
359      * when specifying too lenghty table name constructs.
360      *
361      *
362      * The following statement becomes
363      * SELECT * FROM nwslist; SELECT * FROM nwslistnews;
364      * INSERT INTO nwslist VALUES (.. INSERT INTO nwslistnews VALUES (..
365      * 0123456789 123456789 123456789
366      *
367      *
368      */

369     protected String JavaDoc parseAndPrefixTablestatement(String JavaDoc stmt, String JavaDoc tableName)
370     {
371         String JavaDoc vmid = vmodule.getIdentification();
372         LoggingManager.log("parseAndPrefixTablestatement,vmid=" +
373                                    vmid, this);
374         int count = 0;
375         int spos = 0;
376         while( (spos = stmt.indexOf(tableName, spos)) != -1 )
377         {
378             int epos = spos + tableName.length();
379             String JavaDoc tmp = stmt.substring(0, epos) + vmid;
380             stmt = tmp + stmt.substring(epos);
381             spos = epos;
382             count++;
383         }
384
385         return stmt;
386     }
387
388     /**
389      *
390      */

391     public boolean prepareData()
392     {
393         LoggingManager.log("in SQLDataManager.prepareData()", this);
394         boolean retval = super.prepareData();
395
396         // get database connection
397
Connection con = null;
398         DatabaseMetaData dbmd = null;
399         try
400         {
401             con = ds.getConnection();
402             dbmd = con.getMetaData();
403
404             if(settings != null && settings.length() > 0)
405             {
406                 Statement stmt = con.createStatement();
407                 int retcount = stmt.executeUpdate(settings);
408                 stmt.close();
409
410                 LoggingManager.log("ExecuteUpdated SQL settings. Return value: " + retcount, this);
411             }
412         }
413         catch(SQLException e) {
414             LoggingManager.log("Cannot get connection to database or its metadata. " +
415                                e.getMessage(), this);
416             return false;
417         }
418
419         // loop through all tables for this virtual module
420
Enumeration tablenames = sqlTables.keys();
421         while(tablenames.hasMoreElements())
422         {
423             String JavaDoc table = (String JavaDoc) tablenames.nextElement();
424             Vector tableStmts = (Vector) sqlTables.get(table);
425
426             // everytime: check if table exists (default db, default schema, all types)
427
boolean exist = true;
428             String JavaDoc tblId = table + ((namespaced) ? propPrefix : "");
429             LoggingManager.log("checking if table " + tblId + " exists.", this);
430             try
431             {
432                 //ResultSet rs = dbmd.getTables(null, null, tblId, null);
433
ResultSet rs = dbmd.getTables(null, null, tblId, null);
434                 //exist = rs.next();
435

436                 exist = false;
437                 while(rs.next())
438                 {
439                     if(tblId.equals(rs.getString("TABLE_NAME")))
440                     {
441                         exist = true;
442                     }
443                 }
444             }
445             catch(SQLException e) {
446                 LoggingManager.log("SQLException while checking if table " + tblId + " exists.", this);
447             }
448
449             if(!exist)
450             {
451                 // once: execute all statements to database for this table
452
int count = 0;
453                 Iterator stmts = tableStmts.iterator();
454                 while(stmts.hasNext())
455                 {
456                     count++;
457
458                     // create the table
459
String JavaDoc strstmt = (String JavaDoc) stmts.next();
460                     try
461                     {
462                         if(strstmt != null && strstmt.length() > 0)
463                         {
464                             Statement stmt = con.createStatement();
465                             int retcount = stmt.executeUpdate(strstmt);
466                             stmt.close();
467                             LoggingManager.log("ExecuteUpdated SQL statement #" + count +
468                                                ". Return value: " + retcount, this);
469                         }
470                         else
471                             LoggingManager.log("Missing SQL statement #" + count, this);
472                     }
473                     catch(SQLException e) {
474                         LoggingManager.log("SQLException while preparing table " + table + " " +
475                                            e.getMessage() + "(Stmt: " + strstmt + ")", this);
476                     }
477                 }
478             }
479             else
480                 LoggingManager.log("Table " + tblId + " exists. Doing nothing.", this);
481         }
482
483         // just close...
484
try
485         {
486             con.commit();
487             con.close();
488         }
489         catch(SQLException e) {
490             LoggingManager.log("SQLException while closing connection to database. " +
491                                e.getMessage(), this);
492         }
493
494         return retval;
495     }
496
497 // public String getProperty(String property) { return super.getProperty(property); }
498
public void setProperty(String JavaDoc property,
499                             String JavaDoc value) { super.setProperty(property, value); }
500
501     public void saveProperties() { super.saveProperties(); }
502
503
504     /**
505      *Added by REEA
506      *Method for getting a the table mapping to vmodules Hashtable
507      */

508
509     public Hashtable getTableNames(){
510         return vmoduleTables;
511     }
512 }
513
514 /* end class DataManager */
515
Popular Tags