You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by js...@apache.org on 2002/09/09 20:15:04 UTC
cvs commit: jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model Table.java Column.java
jstrachan 2002/09/09 11:15:04
Modified: sql/src/java/org/apache/commons/sql/model Table.java
Column.java
Added: sql/src/test/org/apache/commons/sql/builder TestBuilder.java
sql/src/java/org/apache/commons/sql/builder
MySqlBuilder.java SqlBuilder.java
OracleBuilder.java MSSqlBuilder.java
SybaseBuilder.java
Log:
Added initial cut of a little builder library that uses regular Java code to generate the DDL to create/drop/alter tables against various physical databases.
This was mostly done as an experiment to see how it compares to the Velocity version, just for the DDL generation as I need to programatically, at runtime, create tables.
Also added a couple of helper methods to the model beans.
Revision Changes Path
1.1 jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/builder/TestBuilder.java
Index: TestBuilder.java
===================================================================
package org.apache.commons.sql.builder;
/*
* Copyright (C) The Apache Software Foundation. All rights reserved.
*
* This software is published under the terms of the Apache Software License
* version 1.1, a copy of which has been included with this distribution in
* the LICENSE file.
*
* $Id: TestProjectRoundTrip.java,v 1.3 2002/03/10 20:16:03 jvanzyl Exp $
*/
import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.PrintWriter;
import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
import junit.textui.TestRunner;
import org.apache.commons.betwixt.XMLIntrospector;
import org.apache.commons.betwixt.io.BeanReader;
import org.apache.commons.betwixt.io.BeanWriter;
import org.apache.commons.betwixt.strategy.DecapitalizeNameMapper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.impl.SimpleLog;
import org.apache.commons.sql.builder.*;
import org.apache.commons.sql.model.*;
/**
* Test harness for the SqlBuilder for various databases.
*
* @version $Revision: 1.3 $
*/
public class TestBuilder extends TestCase
{
private Database database;
private String baseDir;
/**
* A unit test suite for JUnit
*/
public static Test suite()
{
return new TestSuite(TestBuilder.class);
}
/**
* Constructor for the TestBuilder object
*
* @param testName
*/
public TestBuilder(String testName)
{
super(testName);
}
/**
* The JUnit setup method
*/
protected void setUp() throws Exception
{
super.setUp();
baseDir = System.getProperty("basedir", ".");
String uri = baseDir + "/src/test-input/datamodel.xml";
BeanReader reader = new BeanReader();
reader.setXMLIntrospector(createXMLIntrospector());
reader.registerBeanClass(Database.class);
database = (Database) reader.parse(new FileInputStream(uri));
assertTrue("Loaded a valid database", database != null);
}
/**
* A unit test for JUnit
*/
public void testBuilders()
throws Exception
{
testBuilder( new SybaseBuilder(), "sybase.sql" );
testBuilder( new OracleBuilder(), "oracle.sql" );
testBuilder( new MySqlBuilder(), "mysql.sql" );
testBuilder( new MSSqlBuilder(), "mssql.sql" );
}
protected void testBuilder(SqlBuilder builder, String fileName) throws Exception
{
String name = baseDir + "/target/" + fileName;
PrintWriter writer = new PrintWriter( new FileWriter( name ) );
builder.setWriter( writer );
builder.createDatabase( database );
writer.close();
}
/**
* ### it would be really nice to move this somewhere shareable across Maven
* / Turbine projects. Maybe a static helper method - question is what to
* call it???
*/
protected XMLIntrospector createXMLIntrospector()
{
XMLIntrospector introspector = new XMLIntrospector();
// set elements for attributes to true
introspector.setAttributesForPrimitives(false);
// turn bean elements into lower case
introspector.setElementNameMapper(new DecapitalizeNameMapper());
return introspector;
}
}
1.1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/MySqlBuilder.java
Index: MySqlBuilder.java
===================================================================
package org.apache.commons.sql.builder;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.sql.model.Column;
import org.apache.commons.sql.model.Database;
import org.apache.commons.sql.model.Table;
/**
* An SQL Builder for MySQL
*
* @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
* @version $Revision: 1.14 $
*/
public class MySqlBuilder extends SqlBuilder {
public MySqlBuilder() {
}
public void dropTable(Table table) throws IOException {
writer.write( "drop table if exists " );
writer.write( table.getName() );
writeEndOfStatement();
}
protected void writeAutoIncrementColumn() throws IOException {
writer.write( "AUTO_INCREMENT " );
}
}
1.1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/SqlBuilder.java
Index: SqlBuilder.java
===================================================================
package org.apache.commons.sql.builder;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.sql.model.Column;
import org.apache.commons.sql.model.Database;
import org.apache.commons.sql.model.ForeignKey;
import org.apache.commons.sql.model.Reference;
import org.apache.commons.sql.model.Table;
/**
* This class is a collection of Strategy methods for creating the DDL required to create and drop
* databases and tables.
*
* It is hoped that just a single implementation of this class, for each database should make creating DDL
* for each physical database fairly straightforward.
*
* An implementation of this class can always delegate down to some templating technology such as Velocity if
* it requires. Though often that can be quite complex when attempting to reuse code across many databases.
* Hopefully only a small amount code needs to be changed on a per database basis.
*
* @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
* @version $Revision: 1.14 $
*/
public class SqlBuilder {
protected PrintWriter writer;
/** used for generating sequential constraints */
protected int counter;
public SqlBuilder() {
}
/**
* Outputs the DDL required to drop and recreate the database
*/
public void createDatabase(Database database) throws IOException {
for ( Iterator iter = database.getTables().iterator(); iter.hasNext(); ) {
Table table = (Table) iter.next();
tableComment(table);
dropTable(table);
createTable(table);
}
}
/**
* Outputs the DDL required to drop the database
*/
public void dropDatabase(Database database) throws IOException {
for ( Iterator iter = database.getTables().iterator(); iter.hasNext(); ) {
Table table = (Table) iter.next();
tableComment(table);
dropTable(table);
}
}
/**
* Outputs a comment for the table
*/
public void tableComment(Table table) throws IOException {
writeComment( "-----------------------------------------------------------------------" );
writeComment( table.getName() );
writeComment( "-----------------------------------------------------------------------" );
writer.println();
}
/**
* Outputs the DDL to drop the table
*/
public void dropTable(Table table) throws IOException {
writer.write( "drop table " );
writer.write( table.getName() );
writeEndOfStatement();
}
/**
* Outputs the DDL to create the table along with any constraints
*/
public void createTable(Table table) throws IOException {
writer.write( "create table " );
writer.write( table.getName() );
writer.println( " (" );
writeColumnTypes(table);
if (isPrimaryKeyEmbedded()) {
writePrimaryKeys(table);
}
if (isForeignKeysEmbedded()) {
writeForeignKeys(table);
}
writer.println();
writer.write( ")" );
writeEndOfStatement();
if (! isPrimaryKeyEmbedded()) {
writePrimaryKeysAlterTable(table);
}
if (! isForeignKeysEmbedded()) {
writeForeignKeysAlterTable(table);
}
}
/**
* Outputs the DDL to add a column to a table.
*/
public void createColumn(Column column) throws IOException {
writer.write( column.getName() );
writer.write( " " );
writer.write( getSqlType( column ) );
writer.write( column.getType() );
writer.write( " " );
if ( column.isAutoIncrement() ) {
writeAutoIncrementColumn();
}
if ( column.isRequired() ) {
writer.write( "NOT NULL" );
}
else {
writer.write( "NULL" );
}
}
// Properties
//-------------------------------------------------------------------------
/**
* Returns the writer.
* @return PrintWriter
*/
public PrintWriter getWriter() {
return writer;
}
/**
* Sets the writer.
* @param writer The writer to set
*/
public void setWriter(PrintWriter writer) {
this.writer = writer;
}
// Implementation methods
//-------------------------------------------------------------------------
/**
* @return the full SQL type string including the size
*/
protected String getSqlType(Column column) {
return column.getTypeString();
}
/**
* Writes the column types for a table
*/
protected void writeColumnTypes(Table table) throws IOException {
boolean first = true;
for (Iterator iter = table.getColumns().iterator(); iter.hasNext(); ) {
Column column = (Column) iter.next();
if (first) {
first = false;
}
else {
writer.println(",");
}
writer.write( " " );
createColumn(column);
}
}
/**
* Writes the primary key constraints inside a create table () clause.
*/
protected void writePrimaryKeys(Table table) throws IOException {
List primaryKeyColumns = table.getPrimaryKeyColumns();
if ( primaryKeyColumns.size() > 0 ) {
writer.println( "," );
writePrimaryKeyStatement(primaryKeyColumns);
}
}
/**
* Writes the primary key constraints as an AlterTable clause.
*/
protected void writePrimaryKeysAlterTable(Table table) throws IOException {
List primaryKeyColumns = table.getPrimaryKeyColumns();
if ( primaryKeyColumns.size() > 0 ) {
writer.write( "ALTER TABLE " );
writer.println( table.getName() );
writer.write( " ADD CONSTRAINT " );
writer.write( table.getName() );
writer.println( "_PK" );
writePrimaryKeyStatement(primaryKeyColumns);
writeEndOfStatement();
writer.println();
}
}
/**
* Writes the 'PRIMARY KEY(A,B,...,N)' statement
*/
protected void writePrimaryKeyStatement(List primaryKeyColumns) throws IOException {
writer.write( "PRIMARY KEY (" );
boolean first = true;
for (Iterator iter = primaryKeyColumns.iterator(); iter.hasNext(); ) {
Column column = (Column) iter.next();
if (first) {
first = false;
}
else {
writer.write(", " );
}
writer.write(column.getName());
}
writer.write( ")" );
}
/**
* Writes the foreign key constraints inside a create table () clause.
*/
protected void writeForeignKeys(Table table) throws IOException {
for (Iterator keyIter = table.getForeignKeys().iterator(); keyIter.hasNext(); ) {
ForeignKey key = (ForeignKey) keyIter.next();
if (key.getForeignTable() == null) {
System.err.println( "WARN: foreign key table is null for key: " + key );
}
else {
writer.println( "," );
writer.write( " CONSTRAINT " );
writer.write( table.getName() );
writer.write( "_FK_" );
writer.write( Integer.toString(++counter) );
writer.write( " FOREIGN KEY (" );
writeLocalReferences(key);
writer.write( key.getForeignTable() );
writer.println( ")" );
writer.write( " REFERENCES " );
writer.write( key.getForeignTable() );
writer.write( " (" );
writeForeignReferences(key);
writer.println( ")" );
}
}
}
/**
* Writes the foreign key constraints as an AlterTable clause.
*/
protected void writeForeignKeysAlterTable(Table table) throws IOException {
counter = 0;
for (Iterator keyIter = table.getForeignKeys().iterator(); keyIter.hasNext(); ) {
ForeignKey key = (ForeignKey) keyIter.next();
if (key.getForeignTable() == null) {
System.err.println( "WARN: foreign key table is null for key: " + key );
}
else {
writer.write( "ALTER TABLE " );
writer.println( table.getName() );
writer.write( " ADD CONSTRAINT " );
writer.write( table.getName() );
writer.write( "_FK_" );
writer.write( Integer.toString(++counter) );
writer.write( " FOREIGN KEY (" );
writeLocalReferences(key);
writer.println( ")" );
writer.write( " REFERENCES " );
writer.write( key.getForeignTable() );
writer.write( " (" );
writeForeignReferences(key);
writer.println( ")" );
writeEndOfStatement();
}
}
}
/**
* Writes a list of local references for the givek key
*/
protected void writeLocalReferences(ForeignKey key) throws IOException {
boolean first = true;
for (Iterator iter = key.getReferences().iterator(); iter.hasNext(); ) {
Reference reference = (Reference) iter.next();
if (first) {
first = false;
}
else {
writer.write( ", " );
}
writer.write( reference.getLocal() );
}
}
/**
* Writes a list of foreign references for the given key
*/
protected void writeForeignReferences(ForeignKey key) throws IOException {
boolean first = true;
for (Iterator iter = key.getReferences().iterator(); iter.hasNext(); ) {
Reference reference = (Reference) iter.next();
if (first) {
first = false;
}
else {
writer.write( ", " );
}
writer.write( reference.getForeign() );
}
}
/**
* Writes the end of statement text, which is typically a semi colon followed by
* a carriage return
*/
protected void writeEndOfStatement() throws IOException {
writer.println( ";" );
writer.println();
}
/**
* Write a comment to the DDL stream
*/
protected void writeComment(String text) throws IOException {
writer.write( "# " );
writer.println( text );
}
/**
* Outputs that the current column is an auto increment
*/
protected void writeAutoIncrementColumn() throws IOException {
}
/**
* Should the primary key constraints be embedded inside the create table statement
*/
protected boolean isPrimaryKeyEmbedded() {
return false;
}
/**
* Should the foreign key constraints be embedded inside the create table statement
*/
protected boolean isForeignKeysEmbedded() {
return false;
}
}
1.1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/OracleBuilder.java
Index: OracleBuilder.java
===================================================================
package org.apache.commons.sql.builder;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.sql.model.Column;
import org.apache.commons.sql.model.Database;
import org.apache.commons.sql.model.Table;
/**
* An SQL Builder for Oracle
*
* @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
* @version $Revision: 1.14 $
*/
public class OracleBuilder extends SqlBuilder {
public OracleBuilder() {
}
public void dropTable(Table table) throws IOException {
writer.write( "drop table " );
writer.write( table.getName() );
writer.write( " CASCADE CONSTRAINTS" );
writeEndOfStatement();
}
protected boolean isPrimaryKeyEmbedded() {
return true;
}
protected boolean isForeignKeysEmbedded() {
return true;
}
}
1.1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/MSSqlBuilder.java
Index: MSSqlBuilder.java
===================================================================
package org.apache.commons.sql.builder;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.sql.model.Column;
import org.apache.commons.sql.model.Database;
import org.apache.commons.sql.model.Table;
/**
* An SQL Builder for MS SQL
*
* @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
* @version $Revision: 1.14 $
*/
public class MSSqlBuilder extends SqlBuilder {
public MSSqlBuilder() {
}
}
1.1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/SybaseBuilder.java
Index: SybaseBuilder.java
===================================================================
package org.apache.commons.sql.builder;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.sql.model.Column;
import org.apache.commons.sql.model.Database;
import org.apache.commons.sql.model.Table;
/**
* An SQL Builder for Sybase
*
* @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
* @version $Revision: 1.14 $
*/
public class SybaseBuilder extends SqlBuilder {
public SybaseBuilder() {
}
protected void writeComment(String text) throws IOException {
writer.write( "/* " );
writer.write( text );
writer.println( " */" );
}
}
1.2 +20 -0 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.java
Index: Table.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- Table.java 9 Sep 2002 13:47:56 -0000 1.1
+++ Table.java 9 Sep 2002 18:15:04 -0000 1.2
@@ -58,6 +58,7 @@
// Helper methods
+ //-------------------------------------------------------------------------
/**
* @return true if there is at least one primary key column
@@ -75,4 +76,23 @@
}
return false;
}
+
+ /**
+ * @return a List of primary key columns or an empty list if there are no
+ * primary key columns for this Table
+ */
+ public List getPrimaryKeyColumns()
+ {
+ List answer = new ArrayList();
+ for (Iterator iter = getColumns().iterator(); iter.hasNext(); )
+ {
+ Column column = (Column) iter.next();
+ if ( column.isPrimaryKey() )
+ {
+ answer.add(column);
+ }
+ }
+ return answer;
+ }
+
}
1.2 +26 -0 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Column.java
Index: Column.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Column.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- Column.java 9 Sep 2002 13:47:56 -0000 1.1
+++ Column.java 9 Sep 2002 18:15:04 -0000 1.2
@@ -9,6 +9,7 @@
private String name;
private boolean primaryKey = false;
private boolean required = false;
+ private boolean autoIncrement = false;
private String type;
private int size = 0;
@@ -44,6 +45,16 @@
this.required = required;
}
+ public boolean isAutoIncrement()
+ {
+ return autoIncrement;
+ }
+
+ public void setAutoIncrement(boolean autoIncrement)
+ {
+ this.autoIncrement = autoIncrement;
+ }
+
public String getType()
{
return type;
@@ -63,4 +74,19 @@
{
this.size=size;
}
+
+
+ // Helper methods
+ //-------------------------------------------------------------------------
+
+ /**
+ * @return the full SQL type string including the size, such as "VARCHAR (2000)"
+ */
+ public String getTypeString() {
+ if ( getSize() > 0 ) {
+ return getType() + " (" + getSize() + ")";
+ }
+ return getType();
+ }
+
}
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>