[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [stellation-res] Yet another DB abstraction idea...
|
On Wed, 2002-08-14 at 11:53, Mark C. Chu-Carroll wrote:
I also think we might be spending more time than this is really worth. It
might be time to try some coding. I'm going to implement the template
approach. If you have time, try putting together at least a sketched
implementation of the factory approach with a Firebird implementation.
Then I'll write the intrinsic tables in both approaches, and we can
really gauge what seems better in the code.
Hi Mark,
Table.java implements the creation of a table with Firebird as database backend.
TestTable.java shows of how to use Table class to create the table you used as example.
This example lacks a lot of features, like check constraints, default values, support for arrays and others. However, Table.java can be extended to support other databases without effort (IMO).
Comments are scarce, but the code should be easy to follow.
Try it and tell me what you think.
I'm going to sleep now.
Rodolfo
--
MAXPROGRAMS
IBM Business Partner
Microsoft MSDN Business Connection Partner
rmraya@xxxxxxxxxxxxxxx
http://www.maxprograms.com
|
package com.maxprograms.scripts;
import java.util.Vector;
/**
* @author Rodolfo M. Raya
*
*/
public class TestTable {
public static void main(String[] args) {
Table table = new Table("Properties");
// use the types as specified in java.sql.Types only
table.addField("aid", "INTEGER");
table.addField("vid", "INTEGER");
table.addField("inheritable", "INTEGER");
table.addField("name", "VARCHAR", "200");
table.addField("value", "VARCHAR", "1600");
Vector primary = new Vector();
primary.add("aid");
primary.add("vid");
primary.add("name");
table.addPrimaryKey(primary);
Vector fields = new Vector();
fields.add("aid");
fields.add("vid");
Vector referenced = new Vector();
referenced.add("aid");
referenced.add("vid");
table.addForeignKey("fk3", "Versions", fields, referenced);
// fields and referenced are the same vectors in this case,
// but they may be different sometimes
System.out.println(table.generateSQL());
}
}
package com.maxprograms.scripts;
import java.util.Collection;
import java.util.Vector;
/**
* @author Rodolfo M. Raya
*
*/
public class Table {
private String name = null;
private Vector fields = null;
private Vector types = null;
private Vector subtypes = null;
private Vector primary = null;
private Vector foreign = null;
String foreignKeys = null;
int fieldcount;
public Table(String tableName) {
name = new String(tableName);
fields = new Vector(10, 10);
types = new Vector(10, 10);
subtypes = new Vector(10, 10);
fieldcount = 0;
}
public void addField(String fieldName, String fieldType, String subType) {
fieldcount++;
fields.add(fieldName);
types.add(fieldType);
subtypes.add(subType);
}
public void addPrimaryKey(Vector fieldList) {
primary = new Vector((Collection) fieldList);
}
/**
* Method addForeignKey.
* @param name name of the foreign key or constraint
* @param table referenced table
* @param fieldList list of fields to check
* @param referenced corresponding fields in referenced table
*
* In Firebird the name of the constraint is optional, I put it here
* because it was used in Mark's example
*
*/
public void addForeignKey( String name, String table, Vector fieldList, Vector referenced ) {
if ( foreignKeys == null ) {
foreignKeys = "";
}
foreignKeys += ",\nCONSTRAINT " + name +" FOREIGN KEY"+ "( " ;
for (int i=0 ; i<fieldList.size() - 1 ; i++ ) {
foreignKeys += fieldList.get(i) + ", ";
}
foreignKeys += fieldList.lastElement() + " ) REFERENCES " + table + "( ";
for (int i=0 ; i<referenced.size() - 1 ; i++ ) {
foreignKeys += referenced.get(i) + ", ";
}
foreignKeys += referenced.lastElement() + " ) ";
}
public void addField(String fieldName, String fieldType) {
fieldcount++;
fields.add(fieldName);
types.add(fieldType);
subtypes.add(null);
}
public String generateSQL() {
String statement;
statement = "CREATE TABLE " + name + " ( \n";
for (int i = 0; i < fieldcount; i++) {
statement += " " + fields.get(i) + " " + checkType(i);
if (primary != null && primary.contains(fields.get(i))) {
statement += "NOT NULL";
}
if (i != fieldcount - 1 || primary != null ) {
statement += ", \n";
} else {
statement += " \n";
}
}
if ( primary != null ) {
int count = primary.size() - 1;
statement += "PRIMARY KEY ( ";
for ( int i = 0 ; i < count ; i++ ) {
statement += primary.get( i ) + ", ";
}
statement += primary.lastElement() + " ) ";
}
if ( foreignKeys != null ) {
statement += foreignKeys;
}
statement += ") ";
return statement;
}
private String checkType(int i) throws IllegalArgumentException {
// check all types that java.sql.Types support.
if (types.get(i).equals("ARRAY")) {
throw (
new IllegalArgumentException("ARRAY can only be implemented using API."));
}
if (types.get(i).equals("BIGINT")) {
return "INTEGER ";
}
if (types.get(i).equals("BINARY")) {
return "BLOB SUBTYPE " + subtypes.get(i) + " ";
}
if (types.get(i).equals("BIT")) {
throw (new IllegalArgumentException("BIT type is not supported."));
}
if (types.get(i).equals("BLOB")) {
return "BLOB SUBTYPE " + subtypes.get(i) + " ";
}
if (types.get(i).equals("CHAR")) {
return types.get(i) + "(" + subtypes.get(i) + ") ";
}
if (types.get(i).equals("CLOB")) {
return "BLOB SUBTYPE TEXT ";
}
if (types.get(i).equals("DATE")) { // only valid in Dialect 3 databases
return "DATE ";
}
if (types.get(i).equals("DECIMAL")) {
return "DECIMAL(" + subtypes.get(i) + ") ";
// put "precision[,scale]" as subtype
}
if (types.get(i).equals("DOUBLE")) {
return "DOUBLE PRECISION ";
}
if (types.get(i).equals("FLOAT")) {
return "FLOAT ";
}
if (types.get(i).equals("INTEGER")) {
return "INTEGER ";
}
if (types.get(i).equals("LONGVARBINARY")) {
return "BLOB ";
}
if (types.get(i).equals("LONGVARCHAR")) {
return "BLOB SUBTYPE TEXT ";
}
if (types.get(i).equals("NUMERIC")) {
return "NUMERIC(" + subtypes.get(i) + ") ";
// put "precision[,scale]" as subtype
}
if (types.get(i).equals("REAL")) {
throw (new IllegalArgumentException("REAL is not supported."));
}
if (types.get(i).equals("SMALLINT")) {
return "SMALLINT ";
}
if (types.get(i).equals("TIME")) { // only valid in Dialect 3 databases
return "TIME ";
}
if (types.get(i).equals("TIMESTAMP")) {
// only valid in Dialect 1 databases
return "TIMESTAMP ";
}
if (types.get(i).equals("TINYINT")) {
return "SMALLINT ";
}
if (types.get(i).equals("VARBINARY")) {
return "BLOB ";
}
if (types.get(i).equals("VARCHAR")) {
return types.get(i) + "(" + subtypes.get(i) + ") ";
}
throw (
new IllegalArgumentException(
" Unknown or unsupported datatype:" + types.get(i) ) );
}
}
