package daruma.storage_manager;

import java.io.ByteArrayOutputStream;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.transform.TransformerException;

import org.w3c.dom.Element;

import daruma.storage_manager.StorageException;
import daruma.storage_manager.schema.XMLSchemaParseAndRegister;
import daruma.storage_manager.schema.XMLSchemaParserException;
import daruma.storage_manager.schema.XMLSchemaSet;
import daruma.storage_manager.type_definition.ElementName;
import daruma.storage_manager.type_definition.TypeName;
import daruma.storage_manager.type_definition.TypeDefinition;
import daruma.storage_manager.type_definition.TypedInstance;
import daruma.storage_manager.type_definition.TypedInstanceSet;
import daruma.storage_manager.type_definition.TypeException;
import daruma.storage_manager.type_definition.ColumnNameFactory;
import daruma.storage_manager.type_definition.types.GeometryPropertyTypeDefinition;

import daruma.wfs.filter.PredicateDescription;

import daruma.geometry.CoordinateSystem;
import daruma.geometry.CoordinateSystemTransformation;
import daruma.geometry.AffineCoordinateSystemTransformation;
import daruma.geometry.CoordinateSystemTransformationDictionary;
import daruma.geometry.TransformationContext;

import daruma.geometry.DrmEnvelope;

import daruma.xml.UniversalName;
import daruma.xml.SimpleXPath;
import daruma.xml.util.PrefixMap;
import daruma.xml.util.XMLFormatConverter;

import daruma.sql.DatabaseConnection;
import daruma.sql.DatabaseConnectionException;
import daruma.sql.SQLDataType;
import daruma.sql.SQLDataTypeConstant;
import daruma.sql.TableColumnDefinition;
import daruma.sql.TableDefinition;
import daruma.sql.TableColumn;
import daruma.sql.ElementInserter;

import daruma.util.Pair;
import daruma.util.StringSplitter;
import daruma.util.TextUtil;
import daruma.util.ParseException;
import daruma.util.LogWriter;
import daruma.util.PropertyReader;
import daruma.util.Itk;


public class DBMSStorageManager extends StorageManager
{
    /* <<< [06/08/16 19:12 I.Noda] <<< */
    /**
     * SCHEMA_TABLE の各カラムの名称定義
     */
    public static final String	SCHEMA_TABLE_NAME  = "__schema__";
    public static final String	COORD_TRANS_TABLE_NAME  = "__coord_trans__";
    public static final String ColName_SchemaTabSchemaID = "schema_id" ;
    public static final String ColName_SchemaTabInternalSchemaID =
	"internal_schema_id" ;
    public static final String ColName_SchemaTabTargetNamespace = 
	"target_namespace" ;

    public static final String ColName_SchemaTabSchemaDef = "schema_definition" ;
    public static final String ColName_SchemaTabTimeStamp = "timestamp" ;

    /**
     * ELEMENT_TABLE の各カラムの名称定義
     */
    public static final String	ELEMENT_TABLE_NAME = "__element__";
    public static final String ColName_ElementTabElementNS
						= "element_namespace";
    public static final String ColName_ElementTabElementName = "element_name";
    public static final String ColName_ElementTabElementInternalSchemaID
					    = "element_internal_schema_id";
    public static final String ColName_ElementTabTypeNS = "type_namespace";
    public static final String ColName_ElementTabTypeName = "type_name";
    public static final String ColName_ElementTabTypeInternalSchemaID
						= "type_internal_schema_id";
    public static final String ColName_ElementTabDeleted = "deleted" ;
    public static final String ColName_ElementTabTimeStamp = "timestamp" ;

    /**
     * TYPE_TABLE の各カラムの名称定義
     */
    public static final String	TYPE_TABLE_NAME    = "__type__";
    public static final String ColName_TypeTabTypeNS = "type_namespace" ;
    public static final String ColName_TypeTabTypeName = "type_name" ;
    public static final String ColName_TypeTabSchemaURI = "schema_uri" ;
    public static final String ColName_TypeTabInternalSchemaID
						    = "internal_schema_id" ;
    public static final String ColName_TypeTabTimeStamp = "timestamp" ;
    public static final String ColName_TypeTabDeleted = "deleted" ;

    /**
     * UNIVERSAL_NAME_ALIAS_TABLE の各カラムの名称定義
     */
    public static final String	UNIVERSAL_NAME_ALIAS_TABLE_NAME =
						  "__universal_name_alias__";
    public static final String ColName_UNameTabNameSpace = "namespace" ;
    public static final String ColName_UNameTabLocalName = "local_name" ;
    public static final String ColName_UNameTabSerialNum = "id_number" ;
    public static final String ColName_UNameTabId = "id" ;
    public static final String ColName_UNameTabAlias = "alias" ;

    /* !!! [06/08/17 02:12 I.Noda] !!! */
    /**
     * UNIVERSAL_NAME_ALIAS_TABLE の各カラムの名称定義
     */
    public static final String TRANSACTION_LOG_TABLE_NAME = 
	"__transaction_log__" ;
    public static final String ColName_TransTabSerialNum = "id" ;
    public static final String ColName_TransTabURI = "uri" ;
    public static final String ColName_TransTabTimeStamp = "timestamp" ;
    public static final String ColName_TransTabFrom = "request_from" ;
    public static final String ColName_TransTabType = "type" ;
    public static final String ColName_TransTabInfo = "info" ;

    /**
     * COORD_TRANS_TABLE の各コラムの名称定義
     */
    public static final String ColName_CoordTransID = "id";
    public static final String ColName_CoordTransSource = "source_coord";
    public static final String ColName_CoordTransTarget = "target_coord";
    public static final String ColName_CoordTransSourceDim = "source_dimension";
    public static final String ColName_CoordTransTargetDim = "target_dimension";
    public static final String ColName_CoordTransTargetMethod = "method";
    public static final String ColName_CoordTransValue = "value";
    public static final String ColName_CoordTransDeleted = "deleted";

    /**
     * 各 feature のテーブルの、管理下のカラムの名称定義
     */
    public static final String ColName_SystemID = "_id_" ;
    public static final String ColName_SystemParentID = "_parent_id_" ;
    public static final String ColName_SystemTransactionID = 
	"_transaction_id_" ;
    public static final String ColName_SystemCreateTime = "_create_time_" ;
    public static final String ColName_SystemUpdateTime = "_update_time_" ;
    public static final String ColName_SystemDeleted = "_deleted_" ;

    static final String UNameIdPrefix = "id" ;


	private	DatabaseConnection	db;

	private	static
		TypeDictionary	typeDictionaryCache = new TypeDictionary();

	private	Map<ElementName,TypeName>	elementDictionaryCache;
	private	Map<UniversalName, String>	universalNameDictionaryCache;


	private static final String MYSQL_ENGINE = "daruma.mysql.engine";
	private String get_engine_string()
	{
		String engine = PropertyReader.getProperty(MYSQL_ENGINE, "");
		if(engine.length() == 0) {
			return null;
		}
		return "ENGINE=" + engine;
	}

	private static final String MYSQL_ENCODING = "daruma.mysql.default_encoding";
	private String get_encoding_string()
	{
		String encoding = PropertyReader.getProperty(MYSQL_ENCODING, "");
		if(encoding.length() == 0) {
			return null;
		}
		return "DEFAULT CHARSET=" + encoding;
	}

	public	DBMSStorageManager( DatabaseConnection  db )
	{
		assert db != null;

		this.db = db;

		/*
		this.typeDictionaryCache = new TypeDictionary();
		*/
		this.elementDictionaryCache
				    = new HashMap<ElementName, TypeName>();

		this.universalNameDictionaryCache
				    = new HashMap<UniversalName, String>();
	}

	private void initializeStorage() throws DatabaseConnectionException
	{
		try
		{
			// XXX: should collect all universal name
			// for ( TypeDefinition t : Set of Types )
			// {
			//     this.registerUniversalNameIfNotRegistrated
			//	( each universal name of t );
			// }

			this.registerUniversalNameIfNotRegistrated
				( new UniversalName( null , "srsName" ) );

			this.updateCoordTransDictionary();
		}
		catch( StorageException e )
		{
			throw new DatabaseConnectionException( e );
		}
	}

	@Override
	public void setReadOnly( boolean readOnly ) throws StorageException
	{
	    try
	    {
		this.db.setReadOnly( readOnly );
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new StorageException( e.getMessage(), e );
	    }
	}


	@Override
	public void startTransaction() throws StorageException
	{
	    try
	    {
		this.db.startTransaction();
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new StorageException( e.getMessage(), e );
	    }
	}

	@Override
	public void commit() throws StorageException
	{
	    try
	    {
		this.db.commit();
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new StorageException( e.getMessage(), e );
	    }
	}

	@Override
	public void rollback() throws StorageException
	{
	    try
	    {
		this.db.rollback();
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new StorageException( e.getMessage(), e );
	    }
	}



	//
	// DBMSStorageManager extentions
	//
	public	void	connect( String  databaseName ,
				 String  user ,  String  passwd )
					  throws DatabaseConnectionException
	{
		this.db.connect( databaseName , user , passwd );

		this.initializeStorage();
	}

	public	void	close() throws DatabaseConnectionException
	{
		this.db.close();
	}

	@Override
	public	void	reset() throws StorageException
	{
	    try
	    {
		this.db.reset();
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new StorageException( e.getMessage() , e );
	    }
	}


	@Override
	public void	registerSchema( Element  schema ,
					PrefixMap  prefixMap )
						throws StorageException
	{
		LogWriter.qwrite("DEBUG",  this.getClass().getName(), ": ",
				 "registerSchema" );

		// XXX: this.db.startTransaction()

		long	internalSchemaID;

		try
		{
			internalSchemaID
				= this.db.getMaxLongValueFromTable
				  ( this.SCHEMA_TABLE_NAME ,
				    this.ColName_SchemaTabInternalSchemaID );

			internalSchemaID ++;

			LogWriter.qwrite( "DEBUG" ,
					  "internalSchemaID = "
					  + internalSchemaID );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't update schema definition: "
				    + e.getMessage() , e );
		}


		XMLSchemaParseAndRegister
			parser = new XMLSchemaParseAndRegister();
		try
		{
			parser.parseXMLSchemaDOMElement
					( schema , internalSchemaID ,
					  prefixMap , this , false );
		}
		catch( XMLSchemaParserException  e )
		{
			throw new StorageException( e.getMessage() );
		}



		//
		// convert schema to string
		//
		ByteArrayOutputStream	buf = new ByteArrayOutputStream();

		try
		{
			XMLFormatConverter.print( schema , buf );
		}
		catch( TransformerException  e )
		{
			throw new StorageException
				  ( "Can't create schema string" , e );
		}

		try
		{
			PreparedStatement  deleteStatement =
			    this.db.prepareStatement
			    ( "UPDATE " + this.SCHEMA_TABLE_NAME
			      + " SET deleted=TRUE"
			      + " WHERE "
			      + this.ColName_SchemaTabSchemaID + "=?" );

			PreparedStatement  insertStatement =
			    this.db.prepareStatement
			    ( "INSERT INTO " + this.SCHEMA_TABLE_NAME
			      + " ("
			      + this.ColName_SchemaTabTargetNamespace + ","
			      + this.ColName_SchemaTabSchemaID + ","
			      + this.ColName_SchemaTabInternalSchemaID + ","
			      + this.ColName_SchemaTabSchemaDef
			      + ")"
			      + " VALUES (?, ?, " + internalSchemaID
			      + ", ?)" );

			try
			{
				deleteStatement.setString
				   ( 1 , parser.getSchemaID() );

				this.db.executeUpdate( deleteStatement );


				insertStatement.setString
				   ( 1 , parser.getTargetNamespace() );

				insertStatement.setString
				   ( 2 , parser.getSchemaID() );

				insertStatement.setString
				   ( 3 , buf.toString() );

				this.db.executeUpdate( insertStatement );
			}
			catch( SQLException  e )
			{
				throw new StorageException
				  ( "Can't update schema definition: "
				    + e.getMessage() , e );
			}
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't update schema definition: "
				    + e.getMessage() , e );
		}
	}

    /* !!! [06/09/04 15:20 I.Noda] !!! 
     * エレメントによるスキーマ取得と、ネームスペースによる取得を
     * 区別する。エレメントによる取得はそのままネームスペースによる取得
     * を呼び出す。
     */
    //------------------------------------------------------------
    /**
     * 指定されたネームスペースを定義するスキーマをすべて取得する。
     */
    /* <<< [06/09/07 16:53 I.Noda] <<< */
    //public List<String> getSchemasByNS(String namespaceURI,
    //				       XMLSchemaSet schemaSet)
    //	throws StorageException
    /* === [06/09/07 16:53 I.Noda] === */
    @Override
    public XMLSchemaSet getSchemasByNS(String namespaceURI,
    				       XMLSchemaSet schemaSet)
    	throws StorageException
    /* >>> [06/09/07 16:53 I.Noda] >>> */
    {
	/* !!! [06/09/07 01:57 I.Noda] !!! */
	if(schemaSet == null) {
	    schemaSet = new XMLSchemaSet() ;
	}
	
	String	query =
	    "SELECT " 
	    + "`" + this.ColName_SchemaTabSchemaDef + "`"
	    + " FROM `" + this.SCHEMA_TABLE_NAME + "`"
	    + " WHERE "
	    + "`" + this.ColName_SchemaTabTargetNamespace + "`"
	    + "=\"" + namespaceURI + "\""
	    + " AND deleted=FALSE" ;

	LogWriter.qwrite("DEBUG",  "#" + query );

	/* !!! [06/09/07 16:53 I.Noda] !!! */
	//List<String>	ret = new ArrayList<String>();
	
	try {
	    DatabaseConnection.QueryResult r =
		this.db.executeQuery( query );
	    while( r.next() ) {
		String schemaStr = r.getString( /*column*/ 1 ) ;

		/* !!! [06/09/07 16:53 I.Noda] !!! */
		//ret.add( schemaStr ) ;

		/* !!! [06/09/07 01:57 I.Noda] !!! */
		XMLSchemaSet.Entry entry = schemaSet.add(schemaStr) ;
		if(entry != null) {
		    for(String importNsURI : entry.getImportList()) {
			getSchemasByNS(importNsURI, schemaSet) ;
		    }
		}
	    }
	    r.close();
	}
	catch( DatabaseConnectionException e ) {
	    throw new StorageException( e );
	}

	/* !!! [06/09/07 16:53 I.Noda] !!! */
	//return( ret );
	return( schemaSet ) ;
    }


    /* <<< [06/09/07 16:53 I.Noda] <<< */
    //public List<String> getSchemasByNS(String namespaceURI)
    //	throws StorageException
    /* === [06/09/07 16:53 I.Noda] === */
    @Override
    public XMLSchemaSet getSchemasByNS(String namespaceURI)
	throws StorageException
    /* >>> [06/09/07 16:53 I.Noda] >>> */
    {
	return getSchemasByNS(namespaceURI, null) ;
    }

    //------------------------------------------------------------
    /**
     * 指定された要素の定義を含むネームスペースのスキーマをすべて取得する。
     */
    /* <<< [06/09/07 16:53 I.Noda] <<< */
    //public List<String> getSchemasOfElement(ElementName elementName)
    //	throws StorageException
    /* === [06/09/07 16:53 I.Noda] === */
    @Override
    public XMLSchemaSet getSchemasOfElement(ElementName elementName)
	throws StorageException
    /* >>> [06/09/07 16:53 I.Noda] >>> */
    {
	return getSchemasByNS(elementName.getNamespace()) ;
    }

    //------------------------------------------------------------
    /**
     * 指定された要素の定義を含むネームスペースのスキーマをすべて取得する。
     */
    @Override
    public XMLSchemaSet getSchemasOfElement(ElementName elementName,
					    XMLSchemaSet schemaSet)
	throws StorageException
    /* >>> [06/09/07 16:53 I.Noda] >>> */
    {
	return getSchemasByNS(elementName.getNamespace(), schemaSet) ;
    }

    //------------------------------------------------------------
	protected String getSchema( String  schemaID ) throws StorageException
	{
		String	query = "SELECT `" + ColName_SchemaTabSchemaDef + "`"
				+ " FROM `" + SCHEMA_TABLE_NAME + "`"
				+ " WHERE `"
				+ ColName_SchemaTabSchemaID + "`=\"" + schemaID
				+ "\" AND deleted=FALSE";

		LogWriter.qwrite("DEBUG",  "#", query );


		String	ret = null;

		try
		{
			DatabaseConnection.QueryResult
				r = this.db.executeQuery( query );

			while( r.next() )
			{
				if ( ret != null )
				{
					throw new StorageException
					  ( "schema bounded to schema id ["
					    + schemaID
					    + "] multiply defined" );
				}

				ret = r.getString( /*column*/ 1 );
			}

			r.close();
		}
		catch( DatabaseConnectionException e )
		{
			throw new StorageException( e );
		}

		if ( ret == null )
		{
			throw new StorageException
			  ( "schema bounded to schema id ["
			    + schemaID + "] not found" );
		}

		return( ret );
	}


	@Override
	synchronized /* XXX: synchronized for typeDictionaryCache */
	public	void	registerTypeDefinition( TypeName  typeName ,
						TypeDefinition  definition ,
						String  schemaID ,
						long  internalSchemaID ,
						boolean  cacheUpdateOnly )
					      throws StorageException
	{
		assert typeName != null;
		assert definition != null;
		assert schemaID != null;

		LogWriter.qwrite("DEBUG",  this.getClass().getName(), ": ",
				 "registerTypeDefinition: ",
				 "[", typeName.getNamespace(), " ",
				 typeName.getLocalName(), "]" );

		this.registerUniversalNameIfNotRegistrated( typeName );

		this.typeDictionaryCache.registerType( typeName , definition );

		SQLDataType	singleSQLDataType
					= definition.getSingleSQLDataType();

		if ( singleSQLDataType != null )
		{
			// composite type
			LogWriter.qwrite("DEBUG", "single sql type = [",
					 singleSQLDataType.getSQLBaseDataTypeString(),
					 "]" );
		}
		else
		{
			LogWriter.qwrite("DEBUG",  "single sql type = null" );

			List<TableColumnDefinition>	columns;

			try
			{
				columns = definition.getCompositeSQLDataType
						  ( this , (SimpleXPath)null );
			}
			catch( TypeException  e )
			{
				throw new StorageException( e.getMessage() ,
							    e );
			}

			if ( columns == null )
			{
				// internal error
				throw new StorageException
					( "Can't determine type definition" );
			}
		}


		if ( cacheUpdateOnly )
		{
		    return;
		}

		try
		{
			PreparedStatement
			  updateStatement
				= this.db.prepareStatement
				  ( "UPDATE " + this.TYPE_TABLE_NAME
				    + " SET deleted=TRUE"
				    + " WHERE "
				    + this.ColName_TypeTabTypeNS + "=?"
				    + " AND "
				    + this.ColName_TypeTabTypeName + "=?" );

			PreparedStatement
			  insertStatement
				= this.db.prepareStatement
				( "INSERT INTO " + this.TYPE_TABLE_NAME
				  + " ("
				  + this.ColName_TypeTabTypeNS + ","
				  + this.ColName_TypeTabTypeName + ","
				  + this.ColName_TypeTabSchemaURI + ","
				  + this.ColName_TypeTabInternalSchemaID
				  + ")"
				  + " VALUES (?,?,?,"
				  + internalSchemaID + ")" );

			try
			{
				updateStatement.setString
				   ( 1 , typeName.getNamespace() );
				updateStatement.setString
				   ( 2 , typeName.getLocalName() );

				insertStatement.setString
				   ( 1 , typeName.getNamespace() );
				insertStatement.setString
				   ( 2 , typeName.getLocalName() );
				insertStatement.setString
				   ( 3 , schemaID );

				this.db.executeUpdate( updateStatement );
				this.db.executeUpdate( insertStatement );
			}
			catch( SQLException  e )
			{
				throw new StorageException
				  ( "Can't update schema definition: "
				    + e.getMessage() , e );
			}
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't update type definition: "
				    + e.getMessage() , e );
		}
	}


	@Override
	synchronized /* XXX: for typeDictionaryCache */
	public TypeDefinition	getTypeDefinition( TypeName  typeName )
						throws StorageException
	{
		TypeDefinition
			t = this.typeDictionaryCache.getType( typeName );

		if ( t != null )
		{
			return( t );
		}

		final String	storageExceptionMessage
				= "Can't get type definition of "
					+ typeName.toString();


		//
		// fetch element type from DB
		//
		DatabaseConnection.QueryResult	r = null;

		try
		{
			PreparedStatement
			  queryStatement
				= this.db.prepareStatement
				( "SELECT"
				  + " schema_uri"
				  + " FROM "
				  + this.TYPE_TABLE_NAME
				  + " WHERE " + ColName_TypeTabTypeNS + "=?"
				  + " AND " + ColName_TypeTabTypeName + "=?"
				  + " AND " + ColName_TypeTabDeleted
				  + "=FALSE" );

			try
			{
				queryStatement.setString
				   ( 1 , typeName.getNamespace() );

				queryStatement.setString
				   ( 2 , typeName.getLocalName() );

				r = new DatabaseConnection.QueryResult
					( queryStatement.executeQuery() ,
					  queryStatement );
			}
			catch( SQLException  e )
			{
				throw new StorageException
				  ( "Can't get schema uri: "
				    + e.getMessage() , e );
			}


			int	count = 0;
			String	schemaID = null;

			while( r.next() )
			{
				count ++;

				schemaID = r.getString( 1 );
			}

			if ( count == 0
			  || schemaID == null )
			{
				return( null );
			}
			else if ( count >= 2 )
			{
				throw new StorageException
					( storageExceptionMessage + ": "
					  + "element type definition"
					  + " multiply defined" );
			}

			String	schemaString = this.getSchema( schemaID );

			// registerSchema( cacheUpdateOnly=true )
			XMLSchemaParseAndRegister
				parser = new XMLSchemaParseAndRegister();
			try
			{
				parser.parseXMLSchemaString
					( schemaString , -1 , this , true );
			}
			catch( XMLSchemaParserException  e )
			{
				throw new StorageException( e.getMessage() );
			}

			return( this.typeDictionaryCache.getType( typeName ) );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				( storageExceptionMessage + ": "
				  + e.getMessage() );
		}
		finally
		{
			try
			{
				if ( r != null )
				{
					r.close();
				}
			}
			catch( DatabaseConnectionException  e )
			{
				throw new StorageException
					( storageExceptionMessage + ": "
					  + e.getMessage() );
			}
		}
	}


	private long	getInternalSchemaIDFromExistType( TypeName  typeName )
						throws StorageException
	{
		DatabaseConnection.QueryResult	r = null;

		try
		{
			PreparedStatement
			  queryStatement
			      = this.db.prepareStatement
				( "SELECT "
				  + this.ColName_TypeTabInternalSchemaID
				  + " FROM "
				  + this.TYPE_TABLE_NAME
				  + " WHERE " + ColName_TypeTabTypeNS + "=?"
				  + " AND " + ColName_TypeTabTypeName + "=?"
				  + " AND " + ColName_TypeTabDeleted
				  + "=FALSE" );

			queryStatement.setString
			   ( 1 , typeName.getNamespace() );

			queryStatement.setString
			   ( 2 , typeName.getLocalName() );

			return( this.db.getSingleLongValueFromPreparedStatement
				( queryStatement , 1 ) );
		}
		catch( SQLException  e )
		{
			// builtin type
			return( 0L );
		}
		catch( DatabaseConnectionException  e )
		{
			// builtin type
			return( 0L );
		}
	}

	@Override
	public	void	registerElementDefinition
				( ElementName  elementName ,
				  TypeName  typeName ,
				  long  elementInternalSchemaID ,
				  boolean  cacheUpdateOnly )
						throws StorageException
	{
		LogWriter.qwrite("DEBUG",  "registerElementDefinition: ",
				 "[", elementName.getNamespace(), " - ",
				 elementName.getLocalName(), "]: ",
				 "[", typeName.getNamespace(), " - ",
				 typeName.getLocalName(), "]" );

		this.registerUniversalNameIfNotRegistrated( typeName );


		String	elementID = this.getUniversalNameID( elementName );

		if ( elementID == null )
		{
			this.registerUniversalNameIfNotRegistrated
							( elementName );
			elementID = this.getUniversalNameID( elementName );
		}

		LogWriter.qwrite("DEBUG", "elementID = [", elementID, "]" );

		TypeDefinition	type = this.getTypeDefinition( typeName );

		if ( type == null )
		{
			throw new StorageException
				  ( "unknown type " + typeName );
		}

		if ( cacheUpdateOnly )
		{
			this.elementDictionaryCache.put( elementName ,
							 typeName );
			return;
		}


		try
		{
			// XXX: transaction

			long	typeInternalSchemaID
				= this.getInternalSchemaIDFromExistType
								( typeName );


			//
			// update element table
			//
			PreparedStatement	updateStatement;

			updateStatement =
			    this.db.prepareStatement
			    ( "UPDATE "  + this.ELEMENT_TABLE_NAME
			      + " SET deleted=TRUE"
			      + " WHERE "
			      + this.ColName_ElementTabElementNS + "=?"
			      + " AND "
			      + this.ColName_ElementTabElementName + "=?" );

			PreparedStatement	insertStatement;

			insertStatement =
			    this.db.prepareStatement
			    ( "INSERT INTO " + this.ELEMENT_TABLE_NAME
			      + " ("
			      + this.ColName_ElementTabElementNS + ","
			      + this.ColName_ElementTabElementName + ","
			      + this.ColName_ElementTabElementInternalSchemaID
			      + ","
			      + this.ColName_ElementTabTypeNS + ","
			      + this.ColName_ElementTabTypeName + ","
			      + this.ColName_ElementTabTypeInternalSchemaID
			      + ")"
			      + " VALUES (?,?,"
			      + elementInternalSchemaID + ",?,?,"
			      + typeInternalSchemaID + ")" );

			try
			{
				updateStatement.setString
					( 1 , elementName.getNamespace() );

				updateStatement.setString
					( 2 , elementName.getLocalName() );


				insertStatement.setString
					( 1 , elementName.getNamespace() );
				insertStatement.setString
					( 2 , elementName.getLocalName() );

				insertStatement.setString
					( 3 , typeName.getNamespace() );
				insertStatement.setString
					( 4 , typeName.getLocalName() );

				this.db.executeUpdate( updateStatement );
				this.db.executeUpdate( insertStatement );
			}
			catch( SQLException  e )
			{
				throw new StorageException
				  ( "Can't update element definition: "
				    + e.getMessage() , e );
			}



			String	tableName = elementID;

			String	tableColumnsString;

			SQLDataType  t = type.getSingleSQLDataType();

			TableDefinition	tableDefinition = null;

			if ( t != null )
			{
				tableColumnsString =
				    ColumnNameFactory
				    .getTopLevelElementColumnName()
				    + " " + t.getSQLBaseDataTypeString();
			}
			else
			{
				try
				{
					tableDefinition
					 = new TableDefinition
					   ( type
					     .getCompositeSQLDataType
					      (this ,
					       (SimpleXPath)null) );

					tableColumnsString
						= tableDefinition
						  .getTableDefinitionString();
				}
				catch( TypeException  e )
				{
					throw new StorageException
					 ( "Can't update"
					   + " element definition: "
					   + e.getMessage() , e );
				}
			}


			String	managementColumnsString =
			    "`" + this.ColName_SystemID + "`"
			    + " integer not null auto_increment,"
			    + "index(`" + this.ColName_SystemID + "`),"
			    + this.ColName_SystemTransactionID 
			    + " integer not null,"
			    + "index(`" + this.ColName_SystemTransactionID 
			    + "`),"
			    + this.ColName_SystemCreateTime
			    + " timestamp,"
			    + this.ColName_SystemUpdateTime
			    + " timestamp,"
			    + this.ColName_SystemDeleted
			    + " boolean not null default FALSE,"
			    + "index(`" + this.ColName_SystemDeleted
			    + "`),";

			String	sqlInstruction
				= "CREATE TABLE "
				  + "`" + tableName + "`"
				  + " (" + managementColumnsString
					 + tableColumnsString + ")";

			String engine = get_engine_string();
			String encoding = get_encoding_string();

			if(engine != null) {
				sqlInstruction += (" " + engine);
			}
			if(encoding != null) {
				sqlInstruction += (" " + encoding);
			}

			LogWriter.qwrite("DEBUG",  "#", sqlInstruction );


			this.db.dropTableIfExists( tableName );
			this.db.executeUpdate( sqlInstruction );


			if ( tableDefinition != null )
			{
			    for ( String  subTableName
				      : tableDefinition.getSubTables
				      ( tableName ) ) {
				LogWriter.qwrite("DEBUG",  "sub table = ",
						 subTableName );

				this.db.dropTableIfExists( subTableName );

				String subTableSqlInstruction =
				    "CREATE TABLE "
				    + "`" + subTableName + "`"
				    + " ("
				    + "`" + this.ColName_SystemID + "`"
				    + " integer not null,"
				    + "index(`" + this.ColName_SystemID + "`),"
				    + "`" + this.ColName_SystemParentID + "`"
				    + " integer not null,"
				    + "index(`" + this.ColName_SystemParentID
				    + "`),"
				    + (ColumnNameFactory
				       .getTopLevelElementColumnName())
				    + " " + SQLDataTypeConstant.BLOB
					    .getSQLBaseDataTypeString() + ")";

				if(engine != null) {
					subTableSqlInstruction += (" " + engine);
				}
				if(encoding != null) {
					subTableSqlInstruction += (" " + encoding);
				}

				LogWriter.qwrite
					("DEBUG", "#", subTableSqlInstruction );

				this.db.executeUpdate
					( subTableSqlInstruction );
			    }

			}

			this.elementDictionaryCache.put( elementName ,
							 typeName );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't update element definition: "
				    + e.getMessage() , e );
		}
	}


	/**
	 * returns type of element.
	 *
	 * If type has not found, returns null
	 */
	@Override
	public	TypeName	getElementTypeName( ElementName  elementName )
							throws StorageException
	{
		TypeName  ret = this.elementDictionaryCache.get( elementName );

		if ( ret != null )
		{
			return( ret );
		}


		final String	storageExceptionMessage
				= "Can't get type definition of element "
					+ elementName.toString();


		//
		// fetch element type from DB
		//
		PreparedStatement		queryStatement = null;
		DatabaseConnection.QueryResult	r = null;

		try
		{
			queryStatement = this.db.prepareStatement
					 ( "SELECT "
					  + this.ColName_ElementTabTypeNS + ","
					  + this.ColName_ElementTabTypeName
					  + " FROM "
					  + this.ELEMENT_TABLE_NAME
					  + " WHERE "
					  + this.ColName_ElementTabElementNS
					  + "=?"
					  + " AND "
					  + this.ColName_ElementTabElementName
					  + "=?"
					  + " AND deleted=FALSE" );

			try
			{
				queryStatement.setString
				   ( 1 , elementName.getNamespace() );

				queryStatement.setString
				   ( 2 , elementName.getLocalName() );

				r = new DatabaseConnection.QueryResult
					( queryStatement.executeQuery() ,
					  queryStatement );
			}
			catch( SQLException  e )
			{
				throw new StorageException
				  ( "Can't get element type name: "
				    + e.getMessage() , e );


			}


			int	count = 0;

			String	typeNamespace = null;
			String	typeLocalName = null;

			while( r.next() )
			{
				count ++;

				typeNamespace = r.getString( /*column*/ 1 );
				typeLocalName = r.getString( /*column*/ 2 );
			}

			if ( count == 0
			  || typeNamespace == null
			  || typeLocalName == null )
			{
				/*
				throw new StorageException
					( storageExceptionMessage + ": "
					  + "element type definition"
					  + " not found in DB" );
				*/
				return( null );
			}
			else if ( count >= 2 )
			{
				throw new StorageException
					( storageExceptionMessage + ": "
					  + "element type definition"
					  + " multiply defined" );
			}

			TypeName  typeName;

			typeName = new TypeName( typeNamespace ,
						 typeLocalName );

			this.elementDictionaryCache
				.put( elementName , typeName );

			return( typeName );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				( storageExceptionMessage + ": "
				  + e.getMessage() );
		}
		finally
		{
			try
			{
				if ( r != null )
				{
					r.close();
				}
			}
			catch( DatabaseConnectionException  e )
			{
				throw new StorageException
					( storageExceptionMessage + ": "
					  + e.getMessage() );
			}
			/*
			finally
			{

				try
				{
					queryStatement.close();
				}
				catch( SQLException  e )
				{
					throw new StorageException
					      ( storageExceptionMessage + ": "
						+ e.getMessage() );
				}
			}
			*/
		}
	}


	@Override
	public void bulkInsertElement( BulkInsertBuffer buf,
				       long transactionSN )
							throws StorageException
	{
	    for ( Map.Entry< ElementName, List<TypedInstance> > p
		      : buf.getBuffer().entrySet() )
	    {
		ElementName elementName = p.getKey();
		List<TypedInstance> objs = p.getValue();

		assert ! objs.isEmpty();

		String tableName = this.getUniversalNameID( elementName );

		try
		{
		    ElementInserter.executeBulkInsert( db,
						       tableName,
						       objs,
						       transactionSN );
		}
		catch( DatabaseConnectionException e )
		{
		    throw new StorageException( e.getMessage(), e );
		}
	    }

	    buf.clear();
	}

	@Override
	public	int	insertElement( ElementName  elementName ,
				       TypedInstance  obj,
				       long transactionSN )
							throws StorageException
	{
		String	tableName;

		try
		{
			tableName = this.getUniversalNameID( elementName );
		}
		catch( StorageException e )
		{
			throw e;
		}


		List<TableColumn>	columns = obj.getColumns();

		try
		{
			ElementInserter.executeInsert( this.db ,
						       tableName, 
						       columns,
						       transactionSN );
			return 1;
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException( "data insertion failed: "
						   + e.getMessage() ,
						   e );
		}
	}

	@Override
	public	int	deleteElement( ElementName  elementName ,
				       PredicateDescription  predicate ,
				       long transactionSN,
				       boolean  virtualDelete )
							throws StorageException
	{
		String	id = this.getUniversalNameID( elementName );

		if ( id == null )
		{
			throw new StorageException( elementName
						   + " not found" );
		}

		String	sqlString;

		if ( virtualDelete )
		{
			sqlString = "UPDATE `" + id + "`"
			    + " SET `"
			    + this.ColName_SystemDeleted + "`=TRUE";
		}
		else
		{
			sqlString = "DELETE FROM `" + id + "`";
		}


		if ( predicate != null )
		{
			sqlString += " WHERE " + predicate.getSQLExpression();
		}

		LogWriter.qwrite("DEBUG",  "#", sqlString );

		try
		{
			return( this.db.executeUpdate( sqlString ) );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't delete element "
				    + elementName.toString()
				    + ": " + e.getMessage() , e );
		}
	}

	@Override
	public	int	restoreElement( ElementName  elementName ,
					PredicateDescription  predicate,
					long transactionSN)
							throws StorageException
	{
		String	id = this.getUniversalNameID( elementName );

		if ( id == null )
		{
			throw new StorageException( elementName
						   + " not found" );
		}

		String	sqlString;

		sqlString = "UPDATE `" + id + "` SET `"
		    + this.ColName_SystemDeleted + "`=FALSE";

		if ( predicate != null )
		{
			sqlString += " WHERE " + predicate.getSQLExpression();
		}

		LogWriter.qwrite("DEBUG",  "#", sqlString );

		try
		{
			return( this.db.executeUpdate( sqlString ) );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't restore element "
				    + elementName.toString()
				    + ": " + e.getMessage() , e );
		}
	}

	@Override
	public	int	updateElement
			    ( ElementName  elementName ,
			      List< Pair<SimpleXPath,String> >  updateValues ,
			      PredicateDescription  predicate ,
			      long transactionSN,
			      boolean  virtualUpdate ) throws StorageException
	{
		String	id = this.getUniversalNameID( elementName );

		if ( id == null )
		{
			throw new StorageException
				( elementName + " not found" );
		}

		if ( updateValues.isEmpty() )
		{
			return 0;
		}

		StringBuilder	sqlString = new StringBuilder();
		sqlString.append( "UPDATE `" + id + "` SET " );

		boolean	firstColumn = true ;
		for ( Pair<SimpleXPath, String>  p  :  updateValues )
		{
			String	columnName;

			try
			{
				columnName = super.getShortXPathStringForDB
							( p.getFirst() );
			}
			catch( StorageException  e )
			{
				throw new StorageException
				    ( "invalid property: " + p.getFirst() ,
				      e );
			}

			if ( ! firstColumn )
			{
				sqlString.append( "," );
			}
			firstColumn = false;

			sqlString.append( "`" );
			sqlString.append( columnName );
			sqlString.append( "`='" );
			sqlString.append( p.getSecond() );
			sqlString.append( "'" );
		}

		if ( predicate != null )
		{
			sqlString.append
				  ( " WHERE " + predicate.getSQLExpression() );
		}

		LogWriter.qwrite("DEBUG",  "#", sqlString );


		try
		{
			return( this.db.executeUpdate( sqlString.toString() ) );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "Can't update element "
				    + elementName.toString()
				    + ": " + e.getMessage() , e );
		}
	}

	@Override
	public	TypedInstanceSet	getTypedInstanceSet
					  ( ElementName  elementName ,
					    String sqlWhereBlock,
					    TransformationContext trans,
					    long offsetCount,
					    long limitCount )
							throws StorageException
	{
		TypeDefinition	type;

		try
		{
			type = super.getElementTypeDefinition( elementName );
		}
		catch( StorageException  e )
		{
			throw e;
		}

		if ( type == null )
		{
			throw new StorageException( "type of element "
						   + elementName
						   + " not found" );
		}


		String	tableName;

		try
		{
			tableName = this.getUniversalNameID( elementName );
		}
		catch( StorageException e )
		{
			throw e;
		}


		try
		{
			String	retrieveColumnString;

			SQLDataType  t = type.getSingleSQLDataType();

			if ( t != null )
			{
				String	tableColumnsString =
				    ColumnNameFactory
				    .getTopLevelElementColumnName();

				retrieveColumnString
				  = t.getSQLRetrieveColumnString
					  ( tableColumnsString );
			}
			else
			{
				retrieveColumnString
				= new TableDefinition
					( type.getCompositeSQLDataType
					  (this ,
					   (SimpleXPath)null) )
				  .getCommaSeparatedColumnNamesForRawSQLString();
			}


			String	sqlString = 
			    "SELECT "+ "_id_," + retrieveColumnString
			    + " FROM " + "`" + tableName + "`"
			    + genWhereAndLimitBlocks(sqlWhereBlock, 
						     offsetCount,
						     limitCount) ;

			LogWriter.qwrite("DEBUG",  "#", sqlString );

			return( new TypedInstanceSet
				( type ,
				  this.db.executeQuery( sqlString ) ,
				  this ) );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException
				  ( "data retrieve failed: " + e.getMessage() ,
				    e );
		}
		catch( TypeException  e )
		{
			throw new StorageException
				  ( "data retrieve failed: " + e.getMessage() ,
				    e );
		}
	}

    /* !!! [06/08/22 18:56 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * count mode query
     */
    protected String genWhereAndLimitBlocks( String whereBlock,
					     long offsetCount,
					     long limitCount )
    {
	String sqlString = null ;

	if ( whereBlock != null ) {
	    sqlString = 
		" WHERE `" + ColName_SystemDeleted + "`=FALSE"
		+ " AND (" + whereBlock + ")";
	} else {
	    sqlString = " WHERE `" + ColName_SystemDeleted + "`=FALSE";
	}

	if ( limitCount >= 0 || offsetCount > 0 ) {
	    sqlString += " LIMIT ";
	    if ( limitCount < 0 )
	    {
		sqlString += "0";
	    }
	    else
	    {
		sqlString += Long.toString(limitCount);
	    }

	    if ( offsetCount > 0 ) {
		sqlString += " OFFSET " 
		    + Long.toString(offsetCount) ;
	    }
	}

	return sqlString ;
    }


    /* !!! [06/08/22 18:56 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * count mode query
     */
    @Override
    public long getCountOfTypedInstanceSet(ElementName elementName,
					   String sqlWhereBlock,
					   long limitCount)
	throws StorageException 
    {
	String tableName = null ;
	try {
	    tableName =
		this.getUniversalNameID( elementName ) ;

	    String sqlString = 
		("SELECT count(*)"
		 + " FROM `" + tableName + "`"
		 + genWhereAndLimitBlocks(sqlWhereBlock, 0, limitCount)) ;
		
	    DatabaseConnection.QueryResult result =
		this.db.executeQuery(sqlString) ;

	    long count = 0 ;

	    if(result.next()) {
		count = result.getLong(1) ;
	    } else {
		throw 
		    new StorageException("something wrong for query:"
					+ sqlString) ;
	    }
	    
	    return count ;

	} catch(DatabaseConnectionException ex) {
	    throw new StorageException
		(("Can't access table for " + tableName 
		  + ":" + ex.getMessage()), 
		 ex );
	}

    }

    /* !!! [06/08/22 18:56 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * count mode query
     */
    @Override
    public DrmEnvelope getEnvelopeOfTypedInstanceSet(ElementName elementName,
						     String propertyColName,
						     String sqlWhereBlock,
						     long limitCount)
	throws StorageException 
    {
	String tableName = null ;
	try {
	    tableName =
		this.getUniversalNameID( elementName ) ;

	    /* @geo 変数の初期化 */
	    this.db.executeUpdate("SET @geo = null") ;

	    String realColName = 
		GeometryPropertyTypeDefinition
		.convertShortXPathToColumnName(propertyColName) ;

	    /* ELEMENT_TABLE を使うのは、feature を検索する際には、
	     * かならず 1 つは要素があるため。
	     */
	    String sqlString = 
		("SELECT AsText(@geo) FROM " + ELEMENT_TABLE_NAME
		 + " WHERE "
		 + "(SELECT count("
		 + "@geo := "
		 + "if(ifnull(@geo,1)=1,"
		 + realColName + ","
		 + "Envelope(GeomFromText(concat('GeometryCollection(',"
		 + "AsText(" + realColName + "),"
		 + "',',AsText(@geo),')')))))"
		 + " FROM " + tableName
		 + genWhereAndLimitBlocks(sqlWhereBlock, 0, limitCount)
		 + ")"
		 + " LIMIT 1") ;

	    DatabaseConnection.QueryResult
		    result = this.db.executeQuery(sqlString) ;


	    if(result.next()) {
		String bbox = 
		    result.getString(1) ;

		try {
		    DrmEnvelope env = DrmEnvelope.scanPolygonWKT(bbox) ;

		    return env ;
		} catch (Exception ex) {
		    throw new StorageException(ex) ;
		}
	    } else {
		/* もし答えがなければ null を返す。*/
		return null ;
	    }

	} catch(DatabaseConnectionException ex) {
	    throw new StorageException
		(("Can't access table for " + tableName 
		  + ":" + ex.getMessage()), 
		 ex );
	}
    }

    //------------------------------------------------------------
	@Override
	public List<String>	getSubTableValues( String  tableName ,
						   long  parentID )
							throws StorageException
	{
	    String	query =
		"SELECT `"
		+ (ColumnNameFactory
		   .getTopLevelElementColumnName())
		+ "` FROM `" + tableName + "`"
		+ " WHERE `_parent_id_`=" + parentID;

		LogWriter.qwrite("DEBUG", "#", query );

		List<String>	ret = new ArrayList<String>();

		try
		{
			DatabaseConnection.QueryResult
				r = this.db.executeQuery( query );

			while( r.next() )
			{
				String	rawBytes = r.getString( 1 );

				if ( rawBytes == null )
				{
					continue;
				}

				ret.add( r.getString( 1 ) );
			}

			r.close();
		}
		catch( DatabaseConnectionException e )
		{
			throw new StorageException( e );
		}

		return( ret );
	}

	@Override
	public	List<ElementName>  getElementList() throws StorageException
	{
		DatabaseConnection.QueryResult	queryResult;

		try
		{
			queryResult
			      = this.db.executeQuery
				( "SELECT "
				  + this.ColName_ElementTabElementNS + ","
				  + this.ColName_ElementTabElementName
				  + " FROM " + this.ELEMENT_TABLE_NAME
				  + " WHERE deleted=FALSE" );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException( e );
		}


		List<ElementName>	featureTypeList
					    = new ArrayList<ElementName>();

		try
		{
			DatabaseConnection.QueryResult	r = queryResult;

			while( r.next() )
			{
				featureTypeList
				    .add( new ElementName
					  ( r.getString( /*column*/ 1 ) ,
					    r.getString( /*column*/ 2 ) ) );
			}

			r.close();
		}
		catch( DatabaseConnectionException e )
		{
			throw new StorageException( e );
		}

		return( featureTypeList );
	}


	//
	// universal name alias
	//
	@Override
	public void	registerUniversalNameIfNotRegistrated
					( UniversalName  universalName )
							throws StorageException
	{
		LogWriter.qwrite("DEBUG",  this.getClass().getName(), ": ",
				 "registerUniversalNameIfNotRegistrated: ",
				 universalName.getNamespace(), " - ",
				 universalName.getLocalName(), "]" );

		if ( this.getUniversalNameID( universalName ) != null )
		{
			return;
		}


		try
		{
			this.db.startTransaction();

			/* <<< [06/08/16 19:12 I.Noda] <<<
			 * use constants
			 */
			//long	idNumber
			//	= this.db.getMaxLongValueFromTable
			//	       ( this.UNIVERSAL_NAME_ALIAS_TABLE_NAME ,
			//		 "id_number" ,
			//		 0 );
			/* === [06/08/16 19:12 I.Noda] === */
			long	idNumber =
			    this.db.getMaxLongValueFromTable
			    ( this.UNIVERSAL_NAME_ALIAS_TABLE_NAME ,
			      this.ColName_UNameTabSerialNum );
			/* >>> [06/08/16 19:12 I.Noda] >>> */

			idNumber ++;

			/* !!! [06/08/16 19:12 I.Noda] !!!
			 * id prefix の定数化
			 */
			//String idString = "id" + Long.toString( idNumber );
			String idString =
			    genUniversalIdString(universalName, idNumber) ;

			String	alias = idString;
			if ( universalName.getNamespace() != null )
			{
				alias += "-"
					 + StringSplitter
					    .getLastNonEmptyElement
					     ( universalName.getNamespace() ,
					       "/" );
			}
			alias += "-" + universalName.getLocalName();


			//
			// when id column type equals integer auto_increment
			//
			PreparedStatement	s;
			/* <<< [06/08/16 19:12 I.Noda] <<<
			 * use constants
			 */
			//s = this.db.prepareStatement
			//    ( "INSERT INTO "
			//      + this.UNIVERSAL_NAME_ALIAS_TABLE_NAME
			//      + " (namespace,local_name,"
			//      + "id_number,id,alias)"
			//      + " VALUES (?,?,?,?,?)" );
			/* === [06/08/16 19:12 I.Noda] === */
			s = this.db.prepareStatement
			    ( "INSERT INTO "
			      + this.UNIVERSAL_NAME_ALIAS_TABLE_NAME
			      + " ("
			      + this.ColName_UNameTabNameSpace + ","
			      + this.ColName_UNameTabLocalName + ","
			      + this.ColName_UNameTabSerialNum + ","
			      + this.ColName_UNameTabId + ","
			      + this.ColName_UNameTabAlias
			      + ")"
			      + " VALUES (?,?,?,?,?)" );
			/* >>> [06/08/16 19:12 I.Noda] >>> */

			try
			{
				s.setString( 1 ,
					     universalName
					       .getNamespaceNonNull() );
				s.setString( 2 ,
					     universalName.getLocalName() );
				s.setLong  ( 3 , idNumber );
				s.setString( 4 , idString );
				s.setString( 5 , alias );

				this.db.executeUpdate( s );
			}
			catch( SQLException e )
			{
				throw new StorageException
				  ( "Can't update universal name id: "
				    + e.getMessage() , e );
			}

			// calls getUniversalNameID() only for cache updating
			this.getUniversalNameID( universalName );

			this.db.commit();
		}
		catch( DatabaseConnectionException  e )
		{
			try
			{
			    this.db.rollback();
			}
			catch( DatabaseConnectionException  de )
			{
				throw new StorageException
				  ( "Can't close database connection" , de );
			}

			throw new StorageException
				  ( "Can't update universal name id: "
				    + e.getMessage() , e );
		}
	}


	/**
	 * returns UniversalNameID which is a short alias name of universalName
	 *
	 * when UniversalNameID has not found, returns null
	 */
	@Override
	public String	getUniversalNameID( UniversalName  universalName )
							throws StorageException
	{
		String	id = this.universalNameDictionaryCache
						    .get( universalName );

		if ( id != null )
		{
			return( id );
		}


		DatabaseConnection.QueryResult	queryResult = null;

		try
		{
		    /* <<< [06/08/16 19:12 I.Noda] <<<
		     * use constants
		     */
		    //queryResult = this.db.executeQuery
		    //			   ( "SELECT id FROM "
		    //			     + this
		    //			       .UNIVERSAL_NAME_ALIAS_TABLE_NAME
		    //			     + " WHERE NOT deleted"
		    //			     + " AND namespace='"
		    //			     + universalName.getNamespace()
		    //			     + "'"
		    //			     + " AND local_name='"
		    //			     + universalName.getLocalName()
		    //			     + "'" );
		    /* === [06/08/16 19:12 I.Noda] === */
		    queryResult = this.db.executeQuery
			( "SELECT id FROM "
			  + this.UNIVERSAL_NAME_ALIAS_TABLE_NAME
			  + " WHERE NOT deleted"
			  + " AND " + this.ColName_UNameTabNameSpace + "="
			  + "'" + universalName.getNamespaceNonNull() + "'"
			  + " AND " + this.ColName_UNameTabLocalName + "="
			  + "'" + universalName.getLocalName() + "'") ;
		    /* >>> [06/08/16 19:12 I.Noda] >>> */

			DatabaseConnection.QueryResult	r = queryResult;

			long	count = 0;
			while( r.next() )
			{
				id = r.getString( 1 );
				count ++;
			}


			if ( count == 0 )
			{
				return null;
			}
			else if ( count >= 2 )
			{
				throw new StorageException
					  ( "Database consistency error:"
					    + " id of ["
					    + universalName.getNamespace()
					    + " - "
					    + universalName.getLocalName()
					    + "] multiply defined" );
			}


			//
			// add to local cache
			//
			this.universalNameDictionaryCache
				.put( universalName , id );
		}
		catch( DatabaseConnectionException  e )
		{
			throw new StorageException( e );
		}
		finally
		{
			if ( queryResult != null )
			{
				try
				{
					queryResult.close();
				}
				catch( DatabaseConnectionException  e )
				{
					throw new StorageException
					  ( "Can't close database connection" ,
					    e );
				}
			}
		}

		return( id );
	}



	@Override
	public	Date	getCurrentTime() throws StorageException
	{
		try
		{
			return this.db.getCurrentTime();
		}
		catch( DatabaseConnectionException e )
		{
			throw new StorageException( e );
		}
	}


    /* !!! [06/08/16 19:12 I.Noda] !!! */
    //------------------------------------------------------------
    /** 
     * universal name と serial number から universal id を生成
     */
    protected String genUniversalIdString(UniversalName uname,
					  Long serialNum)
    {
	String idString ;

	/* デバッグ時には、カラム名はある程度予想がつくものにする。 */
	//if(false) {
	if(true) {
	    idString = this.UNameIdPrefix + Long.toString(serialNum) ;
	} else {
	    idString = uname.getLocalName() + Long.toString(serialNum) ;
	}

	return idString ;
    }

    /* !!! [06/08/17 17:28 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * Transaction Log 管理。新しいエントリを生成。
     * 得られた transaction number （シリアルナンバー）を返す。
     */
    @Override
    public long registerTransactionURI(String uri)
	throws StorageException
    {
	LogWriter.qwrite("DEBUG",
			 this.getClass().getName(),
			 "::registerTransactionURI(): ",
			 "uri=", uri) ;

	long serialNumber = 0 ;

	try {
	    PreparedStatement statement =
		this.db.prepareStatement
		( "INSERT INTO " + this.TRANSACTION_LOG_TABLE_NAME
		  + " ("
		  + this.ColName_TransTabURI 
		  + ")"
		  + " VALUES (?)" ) ;

	    try {
		this.db.startTransaction() ;

		statement.setString(1,uri) ;
		
		this.db.executeUpdate(statement);

		DatabaseConnection.QueryResult result =
		    this.db.executeQuery("SELECT LAST_INSERT_ID()");
		if(result.next()) {
		    serialNumber = result.getLong(1) ;
		} else {
		    throw new StorageException
			("can not get LAST_INSERT_ID() from database.") ;
		}

		this.db.commit() ;

	    } catch( SQLException ex) {
		this.db.rollback();
		throw new StorageException
		    ("Can't insert transaction log:" + ex.getMessage(), ex );
	    } catch( DatabaseConnectionException ex) {
		this.db.rollback();
		throw new StorageException
		    ("Can't insert transaction log:" + ex.getMessage(), ex );
	    }		
	} catch( DatabaseConnectionException  ex ) {
	    throw new StorageException
		( "Can't update transaction log:" + ex.getMessage() , ex );
	}
	return serialNumber ;
    }

    /* !!! [06/08/17 17:28 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * Transaction Log 管理。
     * URI から transaction number （シリアルナンバー）を返す。
     * もし未知の URI なら 0 を返す。
     */
    @Override
    public long getTransactionSerialNumber(String uri)
	throws StorageException
    {
	LogWriter.qwrite("DEBUG",
			 this.getClass().getName(),
			 "::getTransactionSerialNumber(): ",
			 "uri=", uri) ;
	
	long serialNumber = 0 ;
	
	try {
	    DatabaseConnection.QueryResult result =
		this.db.executeQuery
		    ("SELECT " + ColName_TransTabSerialNum 
		     + " from `" + TRANSACTION_LOG_TABLE_NAME + "`"
		     + " where "
		     + ColName_TransTabURI + "="
		     + "'" + uri + "'") ;

	    if(result.next()) {
		serialNumber = result.getLong(1) ;
	    } else {//知らないURIの場合。
		serialNumber = 0 ;
	    }
	    /* 2つめがあったらおかしい */
	    if(result.next()) {
		throw new StorageException
		    ("multiple serial number for Transaction URI:" + uri) ;
	    }
	} catch(DatabaseConnectionException ex) {
	    throw new StorageException
		("Can't access transaction log:" + ex.getMessage(), ex );
	}
	
	return serialNumber ;
    }
	
    /* !!! [06/08/17 17:28 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * Transaction Log 管理。
     * 最新の transaction URIを返す。
     * もし未知の URI なら 0 を返す。
     */
    @Override
    public String getMostRecentTransactionURI()
	throws StorageException
    {
	LogWriter.qwrite("DEBUG",
			 this.getClass().getName(),
			 "::getMostRecentTransactionURI()") ;
	
	String uri = null ;
	
	try {
	    DatabaseConnection.QueryResult result =
		this.db.executeQuery
		    ("SELECT " + ColName_TransTabURI
		     + " from `" + TRANSACTION_LOG_TABLE_NAME + "`"
		     + " order by "
		     + ColName_TransTabSerialNum + " desc"
		     + " limit 1") ;

	    if(result.next()) {
		uri = result.getString(1) ;
	    } else {//まだエントリがない場合
		uri = null ;
	    }
	    /* 2つめがあったらおかしい */
	    if(result.next()) {
		throw new StorageException
		    ("multiple uri for most recent transaction") ;
	    }
	} catch(DatabaseConnectionException ex) {
	    throw new StorageException
		("Can't access transaction log:" + ex.getMessage(), ex );
	}
	
	return uri ;
    }

    /* !!! [06/08/22 02:35 I.Noda] !!! */
    //------------------------------------------------------------
    /**
     * Transaction Log 管理。
     * 特定の feature に対する最新の transaction URIを返す。
     * もし未知の URI なら 0 を返す。
     */
    @Override
    public String getMostRecentTransactionURIFor(String nsURI,
						 String localname)
	throws StorageException
    {
	return getMostRecentTransactionURIFor(new UniversalName(nsURI,
								localname)) ; 
								
    }

    @Override
    public String getMostRecentTransactionURIFor(UniversalName uname)
	throws StorageException
    {
	String featureTableName = getUniversalNameID(uname) ;
	String uri = null ;
	try {
	    DatabaseConnection.QueryResult result =
		this.db.executeQuery
		    ("SELECT " + ColName_TransTabURI
		     + " from `" + TRANSACTION_LOG_TABLE_NAME + "`"
		     + " where "
		     + "(" + "select "
		     + "max(" + ColName_SystemTransactionID + ")"
		     + " from `" + featureTableName + "`"
		     /* 削除対象も更新の１つなので、deleted も対象 */
		     // + " where not " + ColName_SystemDeleted
		     + ")"
		     + " limit 1") ;

	    if(result.next()) {
		uri = result.getString(1) ;
	    } else {//まだエントリがない場合
		uri = null ;
	    }
	    /* 2つめがあったらおかしい */
	    if(result.next()) {
		throw new StorageException
		    ("multiple uri for most recent transaction") ;
	    }
	} catch(DatabaseConnectionException ex) {
	    throw new StorageException
		("Can't access transaction log:" + ex.getMessage(), ex );
	}
	
	return uri ;
    }



	//
	// Coordinate System
	//
	private static CoordinateSystemTransformationDictionary
			    coordTransDictionary = null;

	synchronized
	private void updateCoordTransDictionary()
	    throws DatabaseConnectionException
	{
	    this.coordTransDictionary
		= new CoordinateSystemTransformationDictionary();

	    PreparedStatement
		get = this.db.prepareStatement
		      ( "SELECT "
			+ this.ColName_CoordTransID + ","
			+ this.ColName_CoordTransSource + ","
			+ this.ColName_CoordTransTarget + ","
			+ this.ColName_CoordTransSourceDim + ","
			+ this.ColName_CoordTransTargetDim + ","
			+ this.ColName_CoordTransTargetMethod + ","
			+ this.ColName_CoordTransValue
			+ " FROM " + this.COORD_TRANS_TABLE_NAME
			+ " WHERE " + this.ColName_CoordTransDeleted
			+ "=FALSE" );

	    try
	    {
		DatabaseConnection.QueryResult
			r = this.db.executeQuery( get );

		while( r.next() )
		{
		    String id      = r.getString( /*column*/ 1 );
		    String source  = r.getString( /*column*/ 2 );
		    String target  = r.getString( /*column*/ 3 );
		    long sourceDim = r.getLong  ( /*column*/ 4 );
		    long targetDim = r.getLong  ( /*column*/ 5 );
		    String method  = r.getString( /*column*/ 6 );
		    String value   = r.getString( /*column*/ 7 );

		    // XXX
		    assert method.equals( "affine" );

		    double[][] matrix;

		    try
		    {
			matrix = TextUtil.parseMatrixValue
			    ( value , sourceDim , targetDim + 1 );
		    }
		    catch( ParseException e )
		    {
			throw new DatabaseConnectionException
			    ( "matrix parsing failed: " + e.getMessage() ,
			      e );
		    }

		    this.coordTransDictionary.put
			( new AffineCoordinateSystemTransformation
			  ( id ,
			    new CoordinateSystem( source ) ,
			    new CoordinateSystem( target ) ,
			    matrix ) );
		}

		r.close();
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new DatabaseConnectionException
		    ( "failed to create"
		      + " coordinate system transformation dictionary", e );
	    }
	}

	@Override
	synchronized
	public void registerCoordinateSystemTransformation
			( CoordinateSystemTransformation trans )
	    throws StorageException
	{
	    try
	    {
		PreparedStatement
		    del = this.db.prepareStatement
			  ( "UPDATE " + this.COORD_TRANS_TABLE_NAME
			    + " SET " + this.ColName_CoordTransDeleted
			    + "=TRUE"
			    + " WHERE "
			    + this.ColName_CoordTransID + "=?" );

		del.setString( 1 , trans.getIdentifier() );
		this.db.executeUpdate( del );

		PreparedStatement
		    ins = this.db.prepareStatement
			  ( "INSERT INTO " + this.COORD_TRANS_TABLE_NAME
			    + " ("
			    + this.ColName_CoordTransID + ","
			    + this.ColName_CoordTransSource + ","
			    + this.ColName_CoordTransTarget + ","
			    + this.ColName_CoordTransSourceDim + ","
			    + this.ColName_CoordTransTargetDim + ","
			    + this.ColName_CoordTransTargetMethod + ","
			    + this.ColName_CoordTransValue + ","
			    + this.ColName_CoordTransDeleted
			    + ")"
			    + " VALUES (?, ?, ?, ?, ?, ?, ?, FALSE)" );

		ins.setString( 1 , trans.getIdentifier() );
		ins.setString( 2 , trans.getSourceCS().getSrsName() );
		ins.setString( 3 , trans.getTargetCS().getSrsName() );
		ins.setLong  ( 4 , trans.getSourceCS().getDimension() );
		ins.setLong  ( 5 , trans.getTargetCS().getDimension() );
		ins.setString( 6 , trans.getTransformName() );
		ins.setString( 7 , trans.encode() );

		this.db.executeUpdate( ins );
	    }
	    catch( SQLException e )
	    {
		throw new StorageException
		    ( "Can't update coordinate system transformation: "
		      + e.getMessage() , e );
	    }
	    catch( DatabaseConnectionException e )
	    {
		throw new StorageException
		    ( "Can't update coordinate system transformation: "
					    + e.getMessage(), e );
	    }

	    this.coordTransDictionary.put( trans );
	}

	@Override
	synchronized
	public CoordinateSystemTransformationDictionary
		getCoordTransDictionary() throws StorageException
	{
	    return this.coordTransDictionary;
	}
}
