source: trunk/gsdl3/src/java/org/greenstone/gsdl3/gs3build/database/GS3SQLConnection.java@ 8773

Last change on this file since 8773 was 8773, checked in by schweer, 19 years ago

bugfix
  • Property svn:keywords set to Author Date Id Revision
File size: 9.9 KB
Line 
1package org.greenstone.gsdl3.gs3build.database;
2
3import java.util.List;
4import java.util.ArrayList;
5
6import java.sql.Connection;
7import java.sql.DriverManager;
8import java.sql.SQLException;
9import java.sql.Statement;
10import java.sql.ResultSet;
11
12
13public class GS3SQLConnection extends SQLConnection
14{
15
16 public GS3SQLConnection(java.sql.Connection connection)
17 {
18 super(connection);
19 }
20
21
22 public void clearCollection(String collectionName)
23 {
24 dropDatabase(collectionName);
25 }
26
27
28 /**
29 * Initialise a collection for use.
30 *
31 * @return <code>boolean</code> <code>true</code> if the collection successfully initialised.
32 */
33 public boolean initCollection(String collectionName)
34 {
35 /*
36 // Check if the collection already exists
37 // if so, abort
38
39 // do a sample query to check if the collection exists
40 GS3SQLSelect select = new GS3SQLSelect("document");
41 select.addField("*");
42 if (this.execute(select.toString())) {
43 return true;
44 }
45
46 // if there is an error, assume that the database doesn't exist...
47 // and reconnect to a "test" database for now...
48 this.connection = GS3SQLConnectionFactory.getConnection("test");
49 */
50
51 // create the new db
52 if (!createDatabase(collectionName)) {
53 System.err.println("Couldn't create database " + collectionName);
54 return false;
55 }
56 // now connect to it
57 if (!connectToDatabase(collectionName)) {
58 System.err.println("couldn't connect to the database " + collectionName);
59 return false;
60 }
61
62
63 try {
64 statement = this.connection.createStatement();
65
66 // create build history table
67 GS3SQLCreateTable buildTable = new GS3SQLCreateTable("build");
68 buildTable.addAutoPrimaryKey("buildRef");
69 buildTable.addProperty("buildKey", 64);
70 buildTable.addProperty("buildValue");
71 statement.execute(buildTable.toString());
72
73 // create document table
74 GS3SQLCreateTable docTable = new GS3SQLCreateTable("document");
75 docTable.addProperty("DocID", 64); // The document identifer, unique in collection
76 docTable.setPrimaryKey("DocID"); // ...which is also the primary key
77 docTable.addProperty("DocType", 64); // The document type - used to regenerate the
78 // correct Document object type.
79 docTable.addProperty("AccessionDate", GS3SQLField.DATETIME_TYPE);
80 docTable.addProperty("IndexedDate", GS3SQLField.DATETIME_TYPE);
81 docTable.addProperty("ModifiedDate", GS3SQLField.DATETIME_TYPE);
82 statement.execute(docTable.toString());
83
84 // create structure table
85 GS3SQLCreateTable structureMap = new GS3SQLCreateTable("structure");
86 structureMap.addAutoPrimaryKey("StructureRef");
87 structureMap.addProperty("DocID", 64); // a 'foreign key' in effect - but not treated
88 // as such for efficiency reasons
89 structureMap.addProperty("StructureID", 64); // this identifier
90 structureMap.addProperty("StructureType", 64); // the type of the structure
91 structureMap.addProperty("Label", 128); // the label of the structure
92 statement.execute(structureMap.toString());
93
94 // create section table
95 GS3SQLCreateTable sectionMap = new GS3SQLCreateTable("divisions");
96 sectionMap.addAutoPrimaryKey("DivisionRef");
97 sectionMap.addProperty("StructureRef", GS3SQLField.INTEGER_TYPE);
98 sectionMap.addProperty("DocID", 64); // a 'foreign key' in effect - but not treated
99 // as such for efficiency reasons
100 sectionMap.addProperty("ParentType", 64); // the type of the parent - document or section
101 sectionMap.addProperty("ParentRef", 64); // the parent sql reference identifier
102 sectionMap.addProperty("SectionID", 64); // this identifier
103 sectionMap.addProperty("DivisionType", 64); // the type of the section
104 sectionMap.addProperty("LabelOrder", 64); // the order of the section
105 sectionMap.addProperty("ShortLabel", 128); // the short label of the section
106 sectionMap.addProperty("UserLabel", 255); // the long label of the section
107 statement.execute(sectionMap.toString());
108
109 // a division->metadata reference mapping
110 GS3SQLCreateTable metaRefs = new GS3SQLCreateTable("divisionmetarefs");
111 metaRefs.addProperty("DocID", 64);
112 metaRefs.addProperty("DivisionRef", GS3SQLField.INTEGER_TYPE);
113 metaRefs.addProperty("DivisionType", 16); // whether a namespace or meta ref
114 metaRefs.addProperty("MetaID", 64); // the metadata reference itself, as a string
115 statement.execute(metaRefs.toString());
116
117 // a division->file reference mapping
118 GS3SQLCreateTable fileRefs = new GS3SQLCreateTable("divisionfilerefs");
119 fileRefs.addProperty("DocID", 64);
120 fileRefs.addProperty("DivisionRef", GS3SQLField.INTEGER_TYPE);
121 fileRefs.addProperty("DivisionType", 16); // whether section, group or file
122 fileRefs.addProperty("FileID", 32);
123 statement.execute(fileRefs.toString());
124
125 // create metadata table section table
126 GS3SQLCreateTable metadataSection = new GS3SQLCreateTable("metadata");
127 metadataSection.addAutoPrimaryKey("MetadataRef");
128 metadataSection.addProperty("DocID", 64); // this table isn't actually used for much
129 metadataSection.addProperty("MetaID", 64); // in and of itself...
130 metadataSection.addProperty("GroupID", 64); // the 'name' property
131 statement.execute(metadataSection.toString());
132
133 // create namespace table
134 GS3SQLCreateTable namespaces = new GS3SQLCreateTable("namespaces");
135 namespaces.addAutoPrimaryKey("NamespaceRef");
136 namespaces.addProperty("MetadataRef", GS3SQLField.INTEGER_TYPE);
137 namespaces.addProperty("DocID", 64); // these three fields are the primary key
138 namespaces.addProperty("MetaID", 64);
139 namespaces.addProperty("NamespaceID", 64);
140 namespaces.addProperty("NamespaceType", 64);
141 namespaces.addProperty("FileType", 64);
142 namespaces.addProperty("FileLoc");
143 namespaces.addProperty("Creator", 128);
144 statement.execute(namespaces.toString());
145
146 // create metadata grouping table
147 GS3SQLCreateTable metagroups = new GS3SQLCreateTable("mdgroups");
148 metagroups.addProperty("NamespaceRef", GS3SQLField.INTEGER_TYPE);
149 metagroups.addAutoPrimaryKey("MdGroupRef");
150 metagroups.addProperty("MdGroupID", 32);
151 statement.execute(metagroups.toString());
152
153 // create metadata qualifiers table
154 GS3SQLCreateTable metaqualifiers = new GS3SQLCreateTable("mdqualifiers");
155 metaqualifiers.addProperty("MdGroupRef", GS3SQLField.INTEGER_TYPE);
156 metaqualifiers.addProperty("QualifierName", 64);
157 metaqualifiers.addProperty("QualifierValue", 64);
158 statement.execute(metaqualifiers.toString());
159
160 // create metadata values table
161 GS3SQLCreateTable metadata = new GS3SQLCreateTable("mdvalues");
162 metadata.addProperty("NamespaceRef", GS3SQLField.INTEGER_TYPE); // the parent namespace {one candidate primary key}
163 metadata.addProperty("GroupRef", GS3SQLField.INTEGER_TYPE); // the parent group {a sub-key or independent primary key}
164 metadata.addProperty("Label", 256); // the key of the metadata item
165 metadata.addProperty("Value"); // the value of the metadata item
166 statement.execute(metadata.toString());
167
168 // create file section table
169 GS3SQLCreateTable filesec = new GS3SQLCreateTable("filesection");
170 filesec.addAutoPrimaryKey("FileSectionRef");
171 filesec.addProperty("DocID", 64);
172 filesec.addProperty("FileSecID", 64);
173 statement.execute(filesec.toString());
174
175 // create file groups table
176 GS3SQLCreateTable filegroups = new GS3SQLCreateTable("filegroups");
177 // TODO: some unique identifier
178 filegroups.addAutoPrimaryKey("FileGroupRef");
179 filegroups.addProperty("DocID", 64);
180 filegroups.addProperty("FileGroupID", 64);
181 filegroups.addProperty("ParentRef", GS3SQLField.INTEGER_TYPE);
182 filegroups.addProperty("ParentType", 16);
183 statement.execute(filegroups.toString());
184
185 // create file table
186 GS3SQLCreateTable files = new GS3SQLCreateTable("files");
187 // TODO: the unique identifier from file groups table
188 files.addProperty("FileGroupRef", GS3SQLField.INTEGER_TYPE);
189 files.addProperty("FileLocType", 64);
190 files.addProperty("FileLocation");
191 files.addProperty("MIMEType", 64);
192 files.addProperty("FileID", 32);
193 statement.execute(files.toString());
194
195 // create admin table...etc.
196
197 //
198 // END OF METS TABLES
199 //
200
201 //
202 // BEGINNING OF GSDL TABLES
203 //
204 GS3SQLCreateTable classifiers = new GS3SQLCreateTable("classifiers");
205 classifiers.addAutoPrimaryKey("ClassifyRef");
206 classifiers.addProperty("ClassifyID");
207 classifiers.addProperty("ParentID");
208 classifiers.addProperty("ClassifyOrder");
209 classifiers.addProperty("Name");
210 classifiers.addProperty("Description");
211 classifiers.addProperty("NumLeafDocs", GS3SQLField.INTEGER_TYPE);
212 statement.execute(classifiers.toString());
213
214 GS3SQLCreateTable classDocs = new GS3SQLCreateTable("classdocuments");
215 classDocs.addProperty("ClassifyRef", GS3SQLField.INTEGER_TYPE);
216 classDocs.addProperty("DocID");
217 classDocs.addProperty("DocOrder", GS3SQLField.INTEGER_TYPE);
218 statement.execute(classDocs.toString());
219
220 GS3SQLCreateTable classData = new GS3SQLCreateTable("classdata");
221 classData.addProperty("ClassifyRef", GS3SQLField.INTEGER_TYPE);
222 classData.addProperty("Label");
223 classData.addProperty("Value");
224 statement.execute(classData.toString());
225
226 //
227 // END OF GSDL TABLES
228 //
229
230 }
231 catch (SQLException ex)
232 {
233 System.out.println(ex.toString());
234 return false;
235 }
236 return true;
237 }
238
239}
240
241
Note: See TracBrowser for help on using the repository browser.