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

Last change on this file since 9874 was 9874, checked in by kjdon, 19 years ago

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