1 | package org.greenstone.gsdl3.gs3build.util;
|
---|
2 |
|
---|
3 | import java.util.List;
|
---|
4 | import java.util.ArrayList;
|
---|
5 |
|
---|
6 | import java.sql.Connection;
|
---|
7 | import java.sql.DriverManager;
|
---|
8 | import java.sql.SQLException;
|
---|
9 | import java.sql.Statement;
|
---|
10 | import java.sql.ResultSet;
|
---|
11 |
|
---|
12 |
|
---|
13 | import org.greenstone.gsdl3.gs3build.database.*;
|
---|
14 |
|
---|
15 | public class GS3SQLConnection
|
---|
16 | {
|
---|
17 | private Connection connection;
|
---|
18 | private Statement statement;
|
---|
19 |
|
---|
20 | class GS3SQLCreateTable
|
---|
21 | { String tableName;
|
---|
22 | List properties;
|
---|
23 | List primaryKey;
|
---|
24 | List indexed;
|
---|
25 |
|
---|
26 | public GS3SQLCreateTable(String tableName)
|
---|
27 | { this.tableName = tableName;
|
---|
28 | this.properties = new ArrayList();
|
---|
29 | this.primaryKey = new ArrayList();
|
---|
30 | this.indexed = new ArrayList();
|
---|
31 | }
|
---|
32 |
|
---|
33 | public void addAutoPrimaryKey(String fieldName)
|
---|
34 | {
|
---|
35 | GS3SQLField field = new GS3SQLField(fieldName, GS3SQLField.AUTOINTEGER_TYPE);
|
---|
36 | this.properties.add(field);
|
---|
37 | this.setPrimaryKey(fieldName);
|
---|
38 | }
|
---|
39 |
|
---|
40 | public void addProperty(String fieldname)
|
---|
41 | { GS3SQLField field = new GS3SQLField(fieldname);
|
---|
42 | this.properties.add(field);
|
---|
43 | }
|
---|
44 |
|
---|
45 | public void addProperty(String fieldname, String type)
|
---|
46 | { GS3SQLField field = new GS3SQLField(fieldname, type);
|
---|
47 | this.properties.add(field);
|
---|
48 | }
|
---|
49 |
|
---|
50 | public void addProperty(String fieldname, int length)
|
---|
51 | { GS3SQLField field = new GS3SQLField(fieldname, length);
|
---|
52 | this.properties.add(field);
|
---|
53 | }
|
---|
54 |
|
---|
55 | public void setPrimaryKey(String fieldname)
|
---|
56 | { this.primaryKey.clear();
|
---|
57 | this.primaryKey.add(fieldname);
|
---|
58 | }
|
---|
59 |
|
---|
60 | public void extendPrimaryKey(String fieldname)
|
---|
61 | { this.primaryKey.add(fieldname);
|
---|
62 | }
|
---|
63 |
|
---|
64 | public void addIndex(String fieldname)
|
---|
65 | { this.indexed.add(fieldname);
|
---|
66 | }
|
---|
67 |
|
---|
68 | public String toString()
|
---|
69 | {
|
---|
70 | StringBuffer reply = new StringBuffer();
|
---|
71 |
|
---|
72 | reply.append("CREATE TABLE ");
|
---|
73 | reply.append(this.tableName);
|
---|
74 | reply.append(" (");
|
---|
75 | for (int f = 0; f < this.properties.size(); f ++)
|
---|
76 | { GS3SQLField field = (GS3SQLField) this.properties.get(f);
|
---|
77 | if (f != 0)
|
---|
78 | { reply.append(",");
|
---|
79 | }
|
---|
80 | reply.append(field.toString());
|
---|
81 | }
|
---|
82 |
|
---|
83 | for (int p = 0; p < this.primaryKey.size(); p ++)
|
---|
84 | { if (p == 0)
|
---|
85 | { reply.append(", PRIMARY KEY(");
|
---|
86 | }
|
---|
87 | else
|
---|
88 | { reply.append(",");
|
---|
89 | }
|
---|
90 | reply.append(this.primaryKey.get(p).toString());
|
---|
91 |
|
---|
92 | if (p == this.primaryKey.size() - 1)
|
---|
93 | { reply.append(")");
|
---|
94 | }
|
---|
95 | }
|
---|
96 | // TODO: add primary key, index etc. notations
|
---|
97 | reply.append(");");
|
---|
98 |
|
---|
99 | return reply.toString();
|
---|
100 | }
|
---|
101 | }
|
---|
102 |
|
---|
103 | public GS3SQLConnection(java.sql.Connection connection)
|
---|
104 | { this.connection = connection;
|
---|
105 | }
|
---|
106 |
|
---|
107 | public boolean execute(String sql)
|
---|
108 | {
|
---|
109 | try {
|
---|
110 | this.statement = this.connection.createStatement();
|
---|
111 | this.statement.execute(sql);
|
---|
112 | }
|
---|
113 | catch (SQLException ex) {
|
---|
114 | System.out.println(ex);
|
---|
115 | return false;
|
---|
116 | }
|
---|
117 | return true;
|
---|
118 | }
|
---|
119 |
|
---|
120 | public Statement createStatement()
|
---|
121 | { try {
|
---|
122 | return this.connection.createStatement();
|
---|
123 | }
|
---|
124 | catch (SQLException ex)
|
---|
125 | {
|
---|
126 | return null;
|
---|
127 | }
|
---|
128 | }
|
---|
129 |
|
---|
130 | public Statement getStatement()
|
---|
131 | { return this.statement;
|
---|
132 | }
|
---|
133 |
|
---|
134 | public ResultSet getResultSet()
|
---|
135 | { try {
|
---|
136 | return this.statement.getResultSet();
|
---|
137 | }
|
---|
138 | catch (SQLException ex)
|
---|
139 | {
|
---|
140 | return null;
|
---|
141 | }
|
---|
142 | }
|
---|
143 |
|
---|
144 | public void clearCollection(String collectionName)
|
---|
145 | {
|
---|
146 | // strip the old database
|
---|
147 | String killCommand = "DROP DATABASE " + collectionName;
|
---|
148 |
|
---|
149 | try {
|
---|
150 | if (/*makeClean*/true) {
|
---|
151 | this.statement = this.connection.createStatement();
|
---|
152 | this.statement.execute(killCommand);
|
---|
153 | System.out.println("attempting: " + killCommand);
|
---|
154 | }
|
---|
155 | }
|
---|
156 | catch (SQLException sqlEx) {
|
---|
157 | System.out.println(sqlEx);
|
---|
158 | }
|
---|
159 | }
|
---|
160 |
|
---|
161 |
|
---|
162 | /**
|
---|
163 | * Initialise a collection for use.
|
---|
164 | *
|
---|
165 | * @return <code>boolean</code> <code>true</code> if the collection successfully initialised.
|
---|
166 | */
|
---|
167 | public boolean initCollection(String collectionName)
|
---|
168 | {
|
---|
169 | /*
|
---|
170 | // Check if the collection already exists
|
---|
171 | // if so, abort
|
---|
172 |
|
---|
173 | // do a sample query to check if the collection exists
|
---|
174 | GS3SQLSelect select = new GS3SQLSelect("document");
|
---|
175 | select.addField("*");
|
---|
176 | if (this.execute(select.toString())) {
|
---|
177 | return true;
|
---|
178 | }
|
---|
179 |
|
---|
180 | // if there is an error, assume that the database doesn't exist...
|
---|
181 | // and reconnect to a "test" database for now...
|
---|
182 | this.connection = GS3SQLConnectionFactory.reconnect("test");
|
---|
183 | */
|
---|
184 |
|
---|
185 | // create database
|
---|
186 | String command = "CREATE DATABASE " + collectionName;
|
---|
187 |
|
---|
188 | try {
|
---|
189 | this.statement = this.connection.createStatement();
|
---|
190 | this.statement.execute(command);
|
---|
191 |
|
---|
192 | // reconnect with the new database
|
---|
193 | this.connection = GS3SQLConnectionFactory.reconnect(collectionName);
|
---|
194 |
|
---|
195 | // create document table
|
---|
196 | GS3SQLCreateTable docTable = new GS3SQLCreateTable("document");
|
---|
197 | docTable.addProperty("DocID", 64); // The document identifer, unique in collection
|
---|
198 | docTable.setPrimaryKey("DocID"); // ...which is also the primary key
|
---|
199 | docTable.addProperty("DocType", 64); // The document type - used to regenerate the
|
---|
200 | // correct Document object type.
|
---|
201 | docTable.addProperty("AccessionDate", GS3SQLField.DATETIME_TYPE);
|
---|
202 | docTable.addProperty("IndexedDate", GS3SQLField.DATETIME_TYPE);
|
---|
203 | statement = this.connection.createStatement();
|
---|
204 | statement.execute(docTable.toString());
|
---|
205 |
|
---|
206 | // create structure table
|
---|
207 | GS3SQLCreateTable structureMap = new GS3SQLCreateTable("structure");
|
---|
208 | structureMap.addAutoPrimaryKey("StructureRef");
|
---|
209 | structureMap.addProperty("DocID", 64); // a 'foreign key' in effect - but not treated
|
---|
210 | // as such for efficiency reasons
|
---|
211 | structureMap.addProperty("StructureID", 64); // this identifier
|
---|
212 | structureMap.addProperty("StructureType", 64); // the type of the structure
|
---|
213 | structureMap.addProperty("Label", 128); // the label of the structure
|
---|
214 | statement.execute(structureMap.toString());
|
---|
215 |
|
---|
216 | // create section table
|
---|
217 | GS3SQLCreateTable sectionMap = new GS3SQLCreateTable("divisions");
|
---|
218 | sectionMap.addAutoPrimaryKey("DivisionRef");
|
---|
219 | sectionMap.addProperty("StructureRef", GS3SQLField.INTEGER_TYPE);
|
---|
220 | sectionMap.addProperty("DocID", 64); // a 'foreign key' in effect - but not treated
|
---|
221 | // as such for efficiency reasons
|
---|
222 | sectionMap.addProperty("ParentType", 64); // the type of the parent - document or section
|
---|
223 | sectionMap.addProperty("ParentRef", 64); // the parent sql reference identifier
|
---|
224 | sectionMap.addProperty("SectionID", 64); // this identifier
|
---|
225 | sectionMap.addProperty("DivisionType", 64); // the type of the section
|
---|
226 | sectionMap.addProperty("LabelOrder", 64); // the order of the section
|
---|
227 | sectionMap.addProperty("ShortLabel", 128); // the short label of the section
|
---|
228 | sectionMap.addProperty("UserLabel", 255); // the long label of the section
|
---|
229 | statement.execute(sectionMap.toString());
|
---|
230 |
|
---|
231 | // a division->metadata reference mapping
|
---|
232 | GS3SQLCreateTable metaRefs = new GS3SQLCreateTable("divisionmetarefs");
|
---|
233 | metaRefs.addProperty("DocID", 64);
|
---|
234 | metaRefs.addProperty("DivisionRef", GS3SQLField.INTEGER_TYPE);
|
---|
235 | metaRefs.addProperty("DivisionType", 16); // whether a namespace or meta ref
|
---|
236 | metaRefs.addProperty("MetaID", 64); // the metadata reference itself, as a string
|
---|
237 | statement.execute(metaRefs.toString());
|
---|
238 |
|
---|
239 | // a division->file reference mapping
|
---|
240 | GS3SQLCreateTable fileRefs = new GS3SQLCreateTable("divisionfilerefs");
|
---|
241 | fileRefs.addProperty("DocID", 64);
|
---|
242 | fileRefs.addProperty("DivisionRef", GS3SQLField.INTEGER_TYPE);
|
---|
243 | fileRefs.addProperty("DivisionType", 16); // whether section, group or file
|
---|
244 | fileRefs.addProperty("FileID", 32);
|
---|
245 | statement.execute(fileRefs.toString());
|
---|
246 |
|
---|
247 | // create metadata table section table
|
---|
248 | GS3SQLCreateTable metadataSection = new GS3SQLCreateTable("metadata");
|
---|
249 | metadataSection.addAutoPrimaryKey("MetadataRef");
|
---|
250 | metadataSection.addProperty("DocID", 64); // this table isn't actually used for much
|
---|
251 | metadataSection.addProperty("MetaID", 64); // in and of itself...
|
---|
252 | metadataSection.addProperty("GroupID", 64); // the 'name' property
|
---|
253 | statement.execute(metadataSection.toString());
|
---|
254 |
|
---|
255 | // create namespace table
|
---|
256 | GS3SQLCreateTable namespaces = new GS3SQLCreateTable("namespaces");
|
---|
257 | namespaces.addAutoPrimaryKey("NamespaceRef");
|
---|
258 | namespaces.addProperty("MetadataRef", GS3SQLField.INTEGER_TYPE);
|
---|
259 | namespaces.addProperty("DocID", 64); // these three fields are the primary key
|
---|
260 | namespaces.addProperty("MetaID", 64);
|
---|
261 | namespaces.addProperty("NamespaceID", 64);
|
---|
262 | namespaces.addProperty("NamespaceType", 64);
|
---|
263 | namespaces.addProperty("fileType", 64);
|
---|
264 | namespaces.addProperty("fileLoc");
|
---|
265 | namespaces.addProperty("creator", 128);
|
---|
266 | statement.execute(namespaces.toString());
|
---|
267 |
|
---|
268 | // create metadata values table
|
---|
269 | GS3SQLCreateTable metadata = new GS3SQLCreateTable("mdvalues");
|
---|
270 | // todo: some unique id for the namespaces item
|
---|
271 | metadata.addProperty("NamespaceRef", GS3SQLField.INTEGER_TYPE);
|
---|
272 | metadata.addProperty("label", 256);
|
---|
273 | metadata.addProperty("value");
|
---|
274 | statement.execute(metadata.toString());
|
---|
275 |
|
---|
276 | // create file section table
|
---|
277 | GS3SQLCreateTable filesec = new GS3SQLCreateTable("filesection");
|
---|
278 | filesec.addAutoPrimaryKey("FileSectionRef");
|
---|
279 | filesec.addProperty("DocID", 64);
|
---|
280 | filesec.addProperty("FileSecID", 64);
|
---|
281 | statement.execute(filesec.toString());
|
---|
282 |
|
---|
283 | // create file groups table
|
---|
284 | GS3SQLCreateTable filegroups = new GS3SQLCreateTable("filegroups");
|
---|
285 | // TODO: some unique identifier
|
---|
286 | filegroups.addAutoPrimaryKey("FileGroupRef");
|
---|
287 | filegroups.addProperty("DocID", 64);
|
---|
288 | filegroups.addProperty("FileGroupID", 64);
|
---|
289 | filegroups.addProperty("ParentRef", GS3SQLField.INTEGER_TYPE);
|
---|
290 | filegroups.addProperty("ParentType", 16);
|
---|
291 | statement.execute(filegroups.toString());
|
---|
292 |
|
---|
293 | // create file table
|
---|
294 | GS3SQLCreateTable files = new GS3SQLCreateTable("files");
|
---|
295 | // TODO: the unique identifier from file groups table
|
---|
296 | files.addProperty("FileGroupRef", GS3SQLField.INTEGER_TYPE);
|
---|
297 | files.addProperty("FileLocType", 64);
|
---|
298 | files.addProperty("FileLocation");
|
---|
299 | files.addProperty("MIMEType", 64);
|
---|
300 | files.addProperty("FileID", 32);
|
---|
301 | statement.execute(files.toString());
|
---|
302 |
|
---|
303 | // create admin table...etc.
|
---|
304 |
|
---|
305 | //
|
---|
306 | // END OF METS TABLES
|
---|
307 | //
|
---|
308 |
|
---|
309 | //
|
---|
310 | // BEGINNING OF GSDL TABLES
|
---|
311 | //
|
---|
312 | GS3SQLCreateTable classifiers = new GS3SQLCreateTable("classifiers");
|
---|
313 | classifiers.addAutoPrimaryKey("ClassifyRef");
|
---|
314 | classifiers.addProperty("ClassifyID");
|
---|
315 | classifiers.addProperty("ParentID");
|
---|
316 | classifiers.addProperty("ClassifyOrder");
|
---|
317 | classifiers.addProperty("Name");
|
---|
318 | classifiers.addProperty("Description");
|
---|
319 | classifiers.addProperty("NumLeafDocs", GS3SQLField.INTEGER_TYPE);
|
---|
320 | statement.execute(classifiers.toString());
|
---|
321 |
|
---|
322 | GS3SQLCreateTable classDocs = new GS3SQLCreateTable("classdocuments");
|
---|
323 | classDocs.addProperty("ClassifyRef", GS3SQLField.INTEGER_TYPE);
|
---|
324 | classDocs.addProperty("DocID");
|
---|
325 | classDocs.addProperty("DocOrder", GS3SQLField.INTEGER_TYPE);
|
---|
326 | statement.execute(classDocs.toString());
|
---|
327 |
|
---|
328 | GS3SQLCreateTable classData = new GS3SQLCreateTable("classdata");
|
---|
329 | classData.addProperty("ClassifyRef", GS3SQLField.INTEGER_TYPE);
|
---|
330 | classData.addProperty("Label");
|
---|
331 | classData.addProperty("Value");
|
---|
332 | statement.execute(classData.toString());
|
---|
333 |
|
---|
334 | //
|
---|
335 | // END OF GSDL TABLES
|
---|
336 | //
|
---|
337 |
|
---|
338 | }
|
---|
339 | catch (SQLException ex)
|
---|
340 | {
|
---|
341 | System.out.println(ex.toString());
|
---|
342 | return false;
|
---|
343 | }
|
---|
344 | return true;
|
---|
345 | }
|
---|
346 |
|
---|
347 | public void deleteCollection(String collection)
|
---|
348 | {
|
---|
349 | try {
|
---|
350 | statement = this.connection.createStatement();
|
---|
351 | statement.execute("DROP DATABASE "+collection+";");
|
---|
352 | }
|
---|
353 | catch (SQLException ex)
|
---|
354 | {
|
---|
355 | }
|
---|
356 | }
|
---|
357 | }
|
---|
358 |
|
---|
359 |
|
---|