com.draagon.meta.manager.db.driver.MSSQLDriver Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of metaobjects-omdb Show documentation
Relational Database ObjectManager libraries for MetaData-driven development.
The newest version!
/*
 * Copyright 2003 Draagon Software LLC. All Rights Reserved.
 *
 * This software is the proprietary information of Draagon Software LLC.
 * Use is subject to license terms.
 */

package com.draagon.meta.manager.db.driver;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Types;

import com.draagon.meta.MetaClass;
import com.draagon.meta.MetaException;
import com.draagon.meta.MetaField;
import com.draagon.meta.manager.db.defs.ColumnDef;
import com.draagon.meta.manager.db.defs.TableDef;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * The Object Manager Base is able to add, update, delete,
 * and retrieve objects of those types from a datastore.
 */
public class MSSQLDriver extends GenericSQLDriver
{
	private static Log log = LogFactory.getLog(MSSQLDriver.class);

	public MSSQLDriver() {
	}

	/**
	 * Creates a table in the database
	 */
	@Override
	public void createTable( Connection c, TableDef table ) throws SQLException
	{
		String query = "CREATE TABLE [" + table + "] (\n";

		boolean multi = ( table.getPrimaryKeys().size() > 1 );

		boolean hasIdentity = false;

		// Create the individual table fields
		int found = 0;
		for ( ColumnDef col : table.getColumns() )
		{
			String name = col.getName();
			if ( name == null || name.length() == 0 ) {
				throw new IllegalArgumentException( "No name defined for column [" + col + "]" );
			}

			if (found > 0) query += ",\n";
			found++;

			String flags = "";
			if ( col.isPrimaryKey() && !multi)
				flags = "PRIMARY KEY ";
			else if ( col.isUnique())
				flags = "UNIQUE ";
			//else if (getManager().isIndex(mf)) flags = "NONCLUSTERED ";

			switch ( col.getSQLType() ) {
			case Types.BOOLEAN:
			case Types.BIT:
				query += "[" + name + "] [bit] " + flags;
				break;
			case Types.TINYINT:
				query += "[" + name + "] [tinyint] " + flags;
				break;
			case Types.SMALLINT:
				query += "[" + name + "] [smallint] " + flags;
				break;
			case Types.INTEGER:
				query += "[" + name + "] [int] " + flags;
				break;
			case Types.BIGINT:
				query += "[" + name + "] [bigint] " + flags;
				break;
			case Types.FLOAT:
				query += "[" + name + "] [float] " + flags;
				break;
			case Types.DOUBLE:
				query += "[" + name + "] [decimal](19,4) " + flags;
				break;
			case Types.TIMESTAMP:
				query += "[" + name + "] [datetime] " + flags;
				break;
			case Types.VARCHAR:
				query += "[" + name + "] [varchar](" + col.getLength() + ") " + flags;
				break;

			default:
				throw new IllegalArgumentException( "Table [" + table + "] with Column [" + col + "] is of SQL type (" + col.getSQLType() + ") which is not support by this database" );
			}

			// Create the identity columns
			if ( col.isAutoIncrementor() )
			{
				if ( hasIdentity )
					throw new MetaException( "Table [" + table + "] cannot have multiple identity (auto id) columns!" );

				query += "NOT NULL IDENTITY( " + col.getSequence().getStart() + ", " + col.getSequence().getIncrement() + " ) ";

				hasIdentity = true;
			}
		}

		query += "\n)";

		// This means there were no columns defined for the table
		if (found == 0) return;

		if ( log.isDebugEnabled() ) {
			log.debug( "Creating table [" + table + "]: " + query);
		}
		//ystem.out.println( ">>>> Creating table [" + table + "]: " + query);

		Statement s = c.createStatement();
		try {
			s.execute(query);
		} finally {
			s.close();
		}
	}

	/*@Override
		public void createSequence(...) { 

			// CREATE SEQUENCES
			for (Iterator i = fields.iterator(); i.hasNext();)
            {
                MetaField mf = (MetaField) i.next();

                String seq = getManager().getSequenceName(mf);
                if (seq != null)
                {
                  String type = "INT";

                  // If the MetaField type is long, then use a BIGINT
                  if ( mf.getType() == MetaField.LONG )
                    type = "BIGINT";

                    query = "CREATE TABLE " + seq + " (id " + type + " NOT NULL IDENTITY( " + ( getManager().getSequenceStart(mf) - 1 ) + ", 1 ), value INT )";
                    s = c.createStatement();
                    try {
                        s.execute(query);
                    } finally {
                        s.close();
                    }

                    if (log.isDebugEnabled())
                        log.debug("Creating sequence [" + seq + "] on field [" + mf.getName() + "]: " + query);

                    //query = "INSERT INTO " + seq + " VALUES(" + ( getManager().getSequenceStart(mf) - 1 ) + ")";
                    //s = c.createStatement();
                    //try {
                    //    s.execute(query);
                    //} finally {
                    //    s.close();
                    //}

                    //if (log.isDebugEnabled())
                    //    log.debug("Initializing sequence [" + seq + "] on field [" + mf.getName() + "]: " + query);
                }
            }
		}*/

	// Create indexes
	/* public void createIndex( ... ) {
	for ( MetaField mf : getManager().getWriteableFields( mc ))
	{
		String name = getManager().getColumnName(mf);
		if (name == null) continue;

		if (!getManager().isPrimaryKey(mf)
				&& !getManager().isIndex(mf))
			continue;

		// Create the sequence
		String query2 = "CREATE INDEX " + table + "_" + name + "_index ON " + table + "(" + name + ")";

		log.debug("(createIndex) Creating index for [" + mc.getName() + "] on field [" + mf.getName() + "]: " + query2);

		s = c.createStatement();
		try {
			s.execute(query2);
		} finally {
			s.close();
		}

	// WARNING:  Reimplement this to work with single auto keys

	// query = "ALTER TABLE [" + table + "] WITH NOCHECK ADD\n" +
    //                "CONSTRAINT [PK_" + table +"] PRIMARY KEY NONCLUSTERED ( [id] ) ON [PRIMARY]";

    //        s = c.createStatement();
    //        s.execute( query );
    //        s.close(); 
	}*/


	/**
	 * Creates a view in the database
	 */
	/* @Override
	public void createView(Connection c, MetaClass mc) throws MetaException
	{
		String view = null;

		view = (String) mc.getAttribute( ObjectManagerDB.VIEW_REF );

		String sql = (String) mc.getAttribute( ObjectManagerDB.VIEW_SQL_REF );

		String query = "CREATE VIEW [" + view + "] AS " + sql;

		log.debug( "Creating view: " + query);
		//ystem.out.println( ">>>> Creating View: " + query);

		try
		{
			Statement s = c.createStatement();
			try {
				s.execute(query);
			} finally {
				s.close();
			}
		}
		catch (Exception e) {
			throw new MetaException( "Creation of view [" + view + "] failed [" + query + ": " + e.getMessage(), e );
		}
	}*/

	/**
	 * Creates the foreign keys for the table in the database
	 */
	/*@Override
	public void createForeignKeys( Connection c, MetaClass mc ) throws MetaException
	{
		for( ForeignKeyDef fk : getManager().getForeignKeys( mc ))
		{
			String table = getManager().getTableName( mc );
			String col = getManager().getColumnName( fk.getField() );

			String foreignTable = getManager().getTableName( fk.getForeignClass() );
			String foreignCol = getManager().getColumnName( fk.getForeignField() );

			String fkstr = "fk_" + table + "_" + col;

			String query = "ALTER TABLE [" + table + "]"
			+ " ADD CONSTRAINT [" + fkstr + "]"
			+ " FOREIGN KEY (" + col + ")"
			+ " REFERENCES [" + foreignTable + "] (" + foreignCol + ")";

			log.debug( "Creating foreign key: " + query);
			//ystem.out.println( ">>>> Creating foreign key: " + query);

			try
			{
				Statement s = c.createStatement();
				try {
					s.execute(query);
				} finally {
					s.close();
				}
			}
			catch (Exception e) {
				throw new MetaException( "Creation of foreign key [" + fk + "] failed [" + query + "]: " + e.getMessage(), e );
			}
		}
	}*/

	/**
	 * Returns whether the auto id is retrieved prior to creation, and MSSQL is not
	 */
	//@Override
	//public int getAutoType() {
	//	return AUTO_DURING;
	//}

	/**
	 * Gets the string to append to the INSERT call to request the generated ids
	 */
	private String getInsertAppendString( MetaClass mc ) throws MetaException
	{
		//if ( autoIdField( mc ) == null ) return "";
		//else 
			return ";SELECT @@IDENTITY"; // Identity_Scope()";
	}

	protected MetaField autoIdField( ColumnDef col ) throws MetaException
	{
		/*Object val = mc.getCacheValue( "MSSQL_IDENTITY" );
		if ( val != null )
		{
			if ( val instanceof MetaField ) {
				return (MetaField) val;
			}
		}
		else
		{
			for( MetaField mf : mc.getMetaFields() ) {
				if ( mf.hasAttribute( ObjectManager.AUTO )) {
					if ( mf.getAttribute( ObjectManager.AUTO ).toString().equals( ObjectManagerDB.AUTO_ID ))
					{
						mc.setCacheValue( "MSSQL_IDENTITY", mf );
						return mf;
					}
				}
			}

			mc.setCacheValue( "MSSQL_IDENTITY", new Object() );
		}*/

		return null;
	}

	/**
	 * Used if AUTO_DURING to retrieve the ids for the specified class
	 */
	//@Override
	private void getAutoIdentifiers( PreparedStatement s, ColumnDef col ) throws MetaException
	{
		/*MetaField mf = autoIdField( col );
		if ( mf == null ) return;

		try
		{
			ResultSet rs = s.getResultSet();

			if ( rs == null && s.getMoreResults() )
				rs = s.getResultSet();

			if ( rs == null )
				throw new MetaException( "No identifiers were returned in the result set" );

			try
			{
				if ( rs.next() )
				{
					switch( mf.getType() )
					{
					case MetaFieldTypes.INT:
						mf.setInt( o, rs.getInt( 1 ));
						break;
					case MetaFieldTypes.LONG:
						mf.setLong( o, rs.getLong( 1 ));
						break;
					case MetaFieldTypes.SHORT:
						mf.setShort( o, rs.getShort( 1 ));
						break;
					default:
						mf.setString( o, rs.getString( 1 ));
					}
				}
			}
			finally {
				rs.close();
			}
		}
		catch( SQLException e ) {
			log.error( "Unable to get identity for MetaClass[" + mc + "]: " + e.getMessage());
			throw new MetaException("Unable to get identity for MetaClass[" + mc + "]: " + e.getMessage(), e);
		}*/
	}

	/**
	 * Gets the last auto id for the given MetaClass
	 */
	/*	@Override
	public String getLastAutoId( Connection conn, MetaClass mc, MetaField mf ) throws MetaException
    {
        try
        {
            String identity = null;

            // Get the last identity value inserted
            Statement s = conn.createStatement();
            try {

            	// This only works on SQL 2000
                String query = "SELECT SCOPE_IDENTITY()";

                // This works on older versions of SQL but can cause problems
                //String query = "SELECT @@IDENTITY";

                ResultSet rs = s.executeQuery(query);

                if (!rs.next())
                    throw new MetaException( "Unable to get last id for MetaField[" + mf + "], no result in result set" );

                try {
                	identity = rs.getString( 1 );
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }

            return identity;
        }
        catch ( SQLException e ) {
            log.error( "Unable to get last id for MetaField[" + mf + "]: " + e.getMessage());
            throw new MetaException("Unable to get last id for MetaField[" + mf + "]: " + e.getMessage(), e);
        }
     }*/

	@Override
	public String getDateFormat() {
		return "MM/dd/yyyy hh:mm:ss:SSS aaa";
	}

	///////////////////////////////////////////////////////
	//	TO STRING METHOD
	public String toString() {
		return "MSSQL Database Driver";
	}
}