source: trunk/gsdl3/src/java/org/greenstone/gsdl3/gs3build/util/GS3SQLConnection.java@ 6700

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 <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.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("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)
    {
    }
  }
}