source: branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/database/GS3SQLConnection.java@ 9858

package org.greenstone.gsdl3.gs3build.database;

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;


public class GS3SQLConnection extends SQLConnection
{

    public GS3SQLConnection(java.sql.Connection connection, String database)
    { 
    super(connection, database);
    }
    

  public void clearCollection(String collectionName)
  {
      dropDatabase(collectionName);
  }
    
    public GS3SQLConnection cloneConnection() {
    GS3SQLConnection conn = GS3SQLConnectionFactory.getGS3SQLConnection(this.database);
    return conn;
    }
  /**
   *  Initialise a collection for use.
   *
   *  @return <code>boolean</code> <code>true</code> 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.getConnection("test");
    */

      // create the new db
      if (!createDatabase(collectionName)) {
          System.err.println("Couldn't create database " + collectionName);
      return false;
      }
      // now connect to it
      if (!connectToDatabase(collectionName)) {
          System.err.println("couldn't connect to the database " + collectionName);
      return false;
      }
    
      
    try {
      Statement statement = this.connection.createStatement();

      // create build history table
      GS3SQLCreateTable buildTable = new GS3SQLCreateTable("build");
      buildTable.addAutoPrimaryKey("buildRef");
      buildTable.addProperty("buildKey", 64);
      buildTable.addProperty("buildValue");
      statement.execute(buildTable.toString());

      // 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);
      docTable.addProperty("ModifiedDate", GS3SQLField.DATETIME_TYPE);
      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 grouping table
      GS3SQLCreateTable metagroups = new GS3SQLCreateTable("mdgroups");
      metagroups.addProperty("NamespaceRef", GS3SQLField.INTEGER_TYPE);
      metagroups.addAutoPrimaryKey("MdGroupRef");
      metagroups.addProperty("MdGroupID", 32);
      statement.execute(metagroups.toString());

      // create metadata qualifiers table
      GS3SQLCreateTable metaqualifiers = new GS3SQLCreateTable("mdqualifiers");
      metaqualifiers.addProperty("MdGroupRef", GS3SQLField.INTEGER_TYPE);
      metaqualifiers.addProperty("QualifierName", 64);
      metaqualifiers.addProperty("QualifierValue", 64);
      statement.execute(metaqualifiers.toString());
      
      // create metadata values table
      GS3SQLCreateTable metadata = new GS3SQLCreateTable("mdvalues");
      metadata.addProperty("NamespaceRef", GS3SQLField.INTEGER_TYPE); // the parent namespace {one candidate primary key}
      metadata.addProperty("GroupRef", GS3SQLField.INTEGER_TYPE);     // the parent group     {a sub-key or independent primary key}
      metadata.addProperty("Label", 256);                             // the key of the metadata item
      metadata.addProperty("Value");                                  // the value of the metadata item
      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());

      statement.close();
      //
      // END OF GSDL TABLES
      //

    }
    catch (SQLException ex)
    {
      System.out.println(ex.toString());
      return false;
    }
    return true;
  }

}