package org.greenstone.gsdl3.gs3build.util;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import org.greenstone.gsdl3.gs3build.database.*;
public class GS3SQLConnection
{
private Connection connection;
private Statement statement;
class GS3SQLCreateTable
{ String tableName;
List properties;
List primaryKey;
List indexed;
public GS3SQLCreateTable(String tableName)
{ this.tableName = tableName;
this.properties = new ArrayList();
this.primaryKey = new ArrayList();
this.indexed = new ArrayList();
}
public void addAutoPrimaryKey(String fieldName)
{
GS3SQLField field = new GS3SQLField(fieldName, GS3SQLField.AUTOINTEGER_TYPE);
this.properties.add(field);
this.setPrimaryKey(fieldName);
}
public void addProperty(String fieldname)
{ GS3SQLField field = new GS3SQLField(fieldname);
this.properties.add(field);
}
public void addProperty(String fieldname, String type)
{ GS3SQLField field = new GS3SQLField(fieldname, type);
this.properties.add(field);
}
public void addProperty(String fieldname, int length)
{ GS3SQLField field = new GS3SQLField(fieldname, length);
this.properties.add(field);
}
public void setPrimaryKey(String fieldname)
{ this.primaryKey.clear();
this.primaryKey.add(fieldname);
}
public void extendPrimaryKey(String fieldname)
{ this.primaryKey.add(fieldname);
}
public void addIndex(String fieldname)
{ this.indexed.add(fieldname);
}
public String toString()
{
StringBuffer reply = new StringBuffer();
reply.append("CREATE TABLE ");
reply.append(this.tableName);
reply.append(" (");
for (int f = 0; f < this.properties.size(); f ++)
{ GS3SQLField field = (GS3SQLField) this.properties.get(f);
if (f != 0)
{ reply.append(",");
}
reply.append(field.toString());
}
for (int p = 0; p < this.primaryKey.size(); p ++)
{ if (p == 0)
{ reply.append(", PRIMARY KEY(");
}
else
{ reply.append(",");
}
reply.append(this.primaryKey.get(p).toString());
if (p == this.primaryKey.size() - 1)
{ reply.append(")");
}
}
// TODO: add primary key, index etc. notations
reply.append(");");
return reply.toString();
}
}
public GS3SQLConnection(java.sql.Connection connection)
{ this.connection = connection;
}
public boolean execute(String sql)
{
try {
this.statement = this.connection.createStatement();
this.statement.execute(sql);
}
catch (SQLException ex) {
System.out.println(ex);
return false;
}
return true;
}
public Statement createStatement()
{ try {
return this.connection.createStatement();
}
catch (SQLException ex)
{
return null;
}
}
public Statement getStatement()
{ return this.statement;
}
public ResultSet getResultSet()
{ try {
return this.statement.getResultSet();
}
catch (SQLException ex)
{
return null;
}
}
public void clearCollection(String collectionName)
{
// strip the old database
String killCommand = "DROP DATABASE " + collectionName;
try {
if (/*makeClean*/true) {
this.statement = this.connection.createStatement();
this.statement.execute(killCommand);
System.out.println("attempting: " + killCommand);
}
}
catch (SQLException sqlEx) {
System.out.println(sqlEx);
}
}
/**
* Initialise a collection for use.
*
* @return boolean
true
if the collection successfully initialised.
*/
public boolean initCollection(String collectionName)
{
/*
// Check if the collection already exists
// if so, abort
// do a sample query to check if the collection exists
GS3SQLSelect select = new GS3SQLSelect("document");
select.addField("*");
if (this.execute(select.toString())) {
return true;
}
// if there is an error, assume that the database doesn't exist...
// and reconnect to a "test" database for now...
this.connection = GS3SQLConnectionFactory.reconnect("test");
*/
// create database
String command = "CREATE DATABASE " + collectionName;
try {
this.statement = this.connection.createStatement();
this.statement.execute(command);
// reconnect with the new database
this.connection = GS3SQLConnectionFactory.reconnect(collectionName);
// create document table
GS3SQLCreateTable docTable = new GS3SQLCreateTable("document");
docTable.addProperty("DocID", 64); // The document identifer, unique in collection
docTable.setPrimaryKey("DocID"); // ...which is also the primary key
docTable.addProperty("DocType", 64); // The document type - used to regenerate the
// correct Document object type.
docTable.addProperty("AccessionDate", GS3SQLField.DATETIME_TYPE);
docTable.addProperty("IndexedDate", GS3SQLField.DATETIME_TYPE);
statement = this.connection.createStatement();
statement.execute(docTable.toString());
// create structure table
GS3SQLCreateTable structureMap = new GS3SQLCreateTable("structure");
structureMap.addAutoPrimaryKey("StructureRef");
structureMap.addProperty("DocID", 64); // a 'foreign key' in effect - but not treated
// as such for efficiency reasons
structureMap.addProperty("StructureID", 64); // this identifier
structureMap.addProperty("StructureType", 64); // the type of the structure
structureMap.addProperty("Label", 128); // the label of the structure
statement.execute(structureMap.toString());
// create section table
GS3SQLCreateTable sectionMap = new GS3SQLCreateTable("divisions");
sectionMap.addAutoPrimaryKey("DivisionRef");
sectionMap.addProperty("StructureRef", GS3SQLField.INTEGER_TYPE);
sectionMap.addProperty("DocID", 64); // a 'foreign key' in effect - but not treated
// as such for efficiency reasons
sectionMap.addProperty("ParentType", 64); // the type of the parent - document or section
sectionMap.addProperty("ParentRef", 64); // the parent sql reference identifier
sectionMap.addProperty("SectionID", 64); // this identifier
sectionMap.addProperty("DivisionType", 64); // the type of the section
sectionMap.addProperty("LabelOrder", 64); // the order of the section
sectionMap.addProperty("ShortLabel", 128); // the short label of the section
sectionMap.addProperty("UserLabel", 255); // the long label of the section
statement.execute(sectionMap.toString());
// a division->metadata reference mapping
GS3SQLCreateTable metaRefs = new GS3SQLCreateTable("divisionmetarefs");
metaRefs.addProperty("DocID", 64);
metaRefs.addProperty("DivisionRef", GS3SQLField.INTEGER_TYPE);
metaRefs.addProperty("DivisionType", 16); // whether a namespace or meta ref
metaRefs.addProperty("MetaID", 64); // the metadata reference itself, as a string
statement.execute(metaRefs.toString());
// a division->file reference mapping
GS3SQLCreateTable fileRefs = new GS3SQLCreateTable("divisionfilerefs");
fileRefs.addProperty("DocID", 64);
fileRefs.addProperty("DivisionRef", GS3SQLField.INTEGER_TYPE);
fileRefs.addProperty("DivisionType", 16); // whether section, group or file
fileRefs.addProperty("FileID", 32);
statement.execute(fileRefs.toString());
// create metadata table section table
GS3SQLCreateTable metadataSection = new GS3SQLCreateTable("metadata");
metadataSection.addAutoPrimaryKey("MetadataRef");
metadataSection.addProperty("DocID", 64); // this table isn't actually used for much
metadataSection.addProperty("MetaID", 64); // in and of itself...
metadataSection.addProperty("GroupID", 64); // the 'name' property
statement.execute(metadataSection.toString());
// create namespace table
GS3SQLCreateTable namespaces = new GS3SQLCreateTable("namespaces");
namespaces.addAutoPrimaryKey("NamespaceRef");
namespaces.addProperty("MetadataRef", GS3SQLField.INTEGER_TYPE);
namespaces.addProperty("DocID", 64); // these three fields are the primary key
namespaces.addProperty("MetaID", 64);
namespaces.addProperty("NamespaceID", 64);
namespaces.addProperty("NamespaceType", 64);
namespaces.addProperty("FileType", 64);
namespaces.addProperty("FileLoc");
namespaces.addProperty("Creator", 128);
statement.execute(namespaces.toString());
// create metadata values table
GS3SQLCreateTable metadata = new GS3SQLCreateTable("mdvalues");
// todo: some unique id for the namespaces item
metadata.addProperty("NamespaceRef", GS3SQLField.INTEGER_TYPE);
metadata.addProperty("Label", 256);
metadata.addProperty("Value");
statement.execute(metadata.toString());
// create file section table
GS3SQLCreateTable filesec = new GS3SQLCreateTable("filesection");
filesec.addAutoPrimaryKey("FileSectionRef");
filesec.addProperty("DocID", 64);
filesec.addProperty("FileSecID", 64);
statement.execute(filesec.toString());
// create file groups table
GS3SQLCreateTable filegroups = new GS3SQLCreateTable("filegroups");
// TODO: some unique identifier
filegroups.addAutoPrimaryKey("FileGroupRef");
filegroups.addProperty("DocID", 64);
filegroups.addProperty("FileGroupID", 64);
filegroups.addProperty("ParentRef", GS3SQLField.INTEGER_TYPE);
filegroups.addProperty("ParentType", 16);
statement.execute(filegroups.toString());
// create file table
GS3SQLCreateTable files = new GS3SQLCreateTable("files");
// TODO: the unique identifier from file groups table
files.addProperty("FileGroupRef", GS3SQLField.INTEGER_TYPE);
files.addProperty("FileLocType", 64);
files.addProperty("FileLocation");
files.addProperty("MIMEType", 64);
files.addProperty("FileID", 32);
statement.execute(files.toString());
// create admin table...etc.
//
// END OF METS TABLES
//
//
// BEGINNING OF GSDL TABLES
//
GS3SQLCreateTable classifiers = new GS3SQLCreateTable("classifiers");
classifiers.addAutoPrimaryKey("ClassifyRef");
classifiers.addProperty("ClassifyID");
classifiers.addProperty("ParentID");
classifiers.addProperty("ClassifyOrder");
classifiers.addProperty("Name");
classifiers.addProperty("Description");
classifiers.addProperty("NumLeafDocs", GS3SQLField.INTEGER_TYPE);
statement.execute(classifiers.toString());
GS3SQLCreateTable classDocs = new GS3SQLCreateTable("classdocuments");
classDocs.addProperty("ClassifyRef", GS3SQLField.INTEGER_TYPE);
classDocs.addProperty("DocID");
classDocs.addProperty("DocOrder", GS3SQLField.INTEGER_TYPE);
statement.execute(classDocs.toString());
GS3SQLCreateTable classData = new GS3SQLCreateTable("classdata");
classData.addProperty("ClassifyRef", GS3SQLField.INTEGER_TYPE);
classData.addProperty("Label");
classData.addProperty("Value");
statement.execute(classData.toString());
//
// END OF GSDL TABLES
//
}
catch (SQLException ex)
{
System.out.println(ex.toString());
return false;
}
return true;
}
public void deleteCollection(String collection)
{
try {
statement = this.connection.createStatement();
statement.execute("DROP DATABASE "+collection+";");
}
catch (SQLException ex)
{
}
}
}