NexJ-SQLite: User Stories 1, 3

This page may be obsolete.
It contains historical information.

Collapsed revision
* User story 1: end of day commit
* Fixes #1 Database configuration files: new directory setup
* ref #4 User story 3: create test initialization data.
create sqlite_drop.sql
* Fixes #2 User story 3: Implement appendColumnType and appendColumnSuffix
* Fixes #5 User Story 3: Indexes, Fixes #6 User Story 3: Indexes
* Fixes #7 User Story 3: SQLite truncate table not supported
* ref #4: User story 3: create test initialization data
upconverted sqlite_insert.sql and sqlite_drop.sql to sqlite standards. they should be ready for use.
* Moved SQLiteDataSourceWrapper to nexj.core.persistence.sql from meta.persistence.sql
* Ref #4: User story 3: create test initialization data.
Create sqlite setup script.
* Fixed driver name for sqlite database
* Fixes #10 User Story 3: Unit test - Primary Key autoincrement
* Code style fix
* Refs #9 User Story 3: Add full-text search
* Fixes #4: User story 3: create test initialization data
upconverted sqlite_setup.sql sqlite_upd_addr.sql sqlite_upd_const.sql.
covers nexj.core.persistence.sql.script
* Fixes #9 User Story 3: Add full-text search
* Fixes #20 User Story 3: drop virtual tables
* Refs #21 User Story 3: implemenation support references
* Refs #15 SQLiteSQLSchemaManager:Date manipulation, Direct copy of PostgreSQL
* Fixes #15 SQLiteSQLSchemaManager:Date manipulation
* Fixes #15 SQLiteSQLSchemaManager:Date manipulation, Microsecond support and SQL concat operator
* Modified comments.
Code style changes.
* Fixes #22 appendColumnType style fix
* Refs #23 User story 3: fix create view, An inner class is definitely the correct approach to overriding existing functionality. Cleanly intercepting the create view call is the hard part.
* Updated default to branch and rebased sqlite branch.
Implemented SQLite datasource using PseudoXADataSourceWrapper.
* Fixes #23: User story 3: fix create view.

Who
    Grace Batumbya
When
    25 October 2011


File     Lines added     Lines removed
src/nexj/core/admin/etl/DataLoader.java     1     1
src/nexj/core/meta/persistence/sql/RelationalDatabase.java     11     5
src/nexj/core/meta/persistence/sql/RelationalDatabaseFragment.java     17     –
src/nexj/core/meta/persistence/sql/SQLiteDataSourceWrapper.java     176     –
src/nexj/core/meta/persistence/sql/XMLRelationalMetadataLoader.java     12     12
src/nexj/core/meta/sys/system.dstypes     1     –
src/nexj/core/persistence/sql/SQLRAConnectionFactory.java     15     –
src/nexj/core/persistence/sql/SQLSchemaManager.java     120     88
src/nexj/core/persistence/sql/SQLSchemaManagerFactory.java     4     –
src/nexj/core/persistence/sql/SQLiteAdapter.java     365     –
src/nexj/core/persistence/sql/SQLiteDataSourceWrapper.java     200     –
src/nexj/core/persistence/sql/SQLiteDataSourceWrapper1.java     180     –
src/nexj/core/persistence/sql/SQLiteSQLSchemaManager.java     622     –
src/nexj/core/persistence/sql/pseudoxa/PseudoXADataSourceWrapper.java     5     2
src/nexj/core/util/JDBCInfo.java     32     1
test/nexj/core/persistence/sql/AllTests.java     1     1
test/nexj/core/persistence/sql/SQLiteAdapterTest.java     49     –
test/nexj/core/persistence/sql/SQLiteSchemaManagerTest.java     40     –
test/nexj/core/persistence/sql/script/sqlite_drop.sql     8     –                  ###
test/nexj/core/persistence/sql/script/sqlite_insert.sql     364     –          ###
test/nexj/core/persistence/sql/script/sqlite_setup.sql     13     –                  ###
test/nexj/core/persistence/sql/script/sqlite_upd_addr.sql     2     –              ###
test/nexj/core/persistence/sql/script/sqlite_upd_cont.sql     1     –              ###
test/nexj/default.config     7     6                                              ###
test/nexj/filestorage.connections     2     2                                      ###
test/nexj/filestoragefragment.connections     2     2                              ###
test/nexj/sqlite.connections                                                   ###


src/nexj/core/admin/etl/DataLoader.java »
        @@ -476,7 +476,7 @@
476    476    */
477    477    public List exportData(Iterator specItr)
478    478    {
    479    Lookup identityMap = new IdentityHashTab(128);
479        Lookup identityMap = new IdentityHashTab(128); ** (BLUE-ADDED)
480    480   
481    481    while (specItr.hasNext())
482    482    {


src/nexj/core/meta/persistence/sql/RelationalDatabase.java »
        @@ -250,26 +250,32 @@
250    250   
251    251    if (m_sDriver == null && m_adapter != null)
252    252    {
    253    if (m_adapter.getName().equals("MSSQL"))
253        String sName = m_adapter.getName();  **
254       
255        if (sName.equals("MSSQL"))
256    254    {
257    255    m_sDriver = "net.sourceforge.jtds.jdbcx.JtdsDataSource";
258    256    }
    257    else if (m_adapter.getName().startsWith("Oracle"))
259        else if (sName.startsWith("Oracle"))
260    258    {
261    259    m_sDriver = "oracle.jdbc.xa.client.OracleXADataSource";
262    260    }
    261    else if (m_adapter.getName().equals("Sybase"))
263        else if (sName.equals("Sybase")) **
264    262    {
265    263    m_sDriver = "com.sybase.jdbc3.jdbc.SybXADataSource";
266    264    }
    265    else if (m_adapter.getName().equals("MySQL"))
267        else if (sName.equals("MySQL")) **
268    266    {
269    267    m_sDriver = "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource";
270    268    }
    269    else if (m_adapter.getName().startsWith("DB2"))
271        else if (sName.startsWith("DB2"))  **
272    270    {
273    271    m_sDriver = "com.ibm.db2.jcc.DB2XADataSource";
274    272    }
275        else if (sName.equals("SQLite")) **
276        {
277        m_sDriver = "nexj.core.persistence.sql.SQLiteDataSourceWrapper";
278        }
279    273    else if (m_adapter.getName().equals("Teradata"))
280    274    {
281    275    m_sDriver = "nexj.core.persistence.sql.TeradataDataSourceWrapper";


src/nexj/core/meta/persistence/sql/RelationalDatabaseFragment.java »
        @@ -622,6 +622,23 @@
622    622    m_props.addDefaultProperty("databaseName", m_sDatabase);
623    623    }
624    624    }
625        else if (sDriver.equals("nexj.core.persistence.sql.SQLiteDataSourceWrapper"))
626        {
627        Properties props = new Properties();
628       
629        if (m_sHost != null)
630        {
631        props.setProperty("serverName", m_sHost);
632        }
633       
634        if (m_sDatabase != null)
635        {
636        props.setProperty("databaseName", m_sDatabase);
637        }
638       
639        m_props.addDefaultProperty("DataSource", "org.sqlite.SQLiteDataSource");
640        m_props.addDefaultProperty("Properties", PropertyUtil.toString(props));
641        }
642    625    }
643    626    }
644    627   

src/nexj/core/meta/persistence/sql/SQLiteDataSourceWrapper.java »

src/nexj/core/persistence/sql/SQLSchemaManager.java »

4404        return getSQL(new StringWriter(sSQL.length()), getSQLSubstReader(new StringReader(sSQL), schema, table));
4405        }
4406       
4407        /**
4408        * Returns the expanded and formated SQL for creating a view from a specific SQLScript.
4409        * @param script The script to get the SQL template from.
4410        * @param schema The relational schema that the script is destined for.
4411        * @param table The default table used during script template expansion. Can be null.
4412        * @return The expanded and formated SQL for the specified SQLScript.
4413        */
4414        protected String getViewSQL(SQLScript script, RelationalSchema schema, Table table)
4415        {
4416        return getExecSQL(script, schema, table);
4417        }
4418       
4419        /**
4420        * Returns the expanded and formated SQL for a specific SQLScript.
4421        * @param script The script to get the SQL template from.
4422        * @param schema The relational schema that the script is destined for.
4423        * @param table The default table used during script template expansion. Can be null.
4424        * @return The expanded and formated SQL for the specified SQLScript.
4425        */
4426        protected String getSQL(Writer writer, Reader reader)
4427        {
4428    4405    try
4429    4406    {
    4407    IOUtil.copy(writer, getSQLSubstReader(new StringReader(sSQL), schema, table));
4430        IOUtil.copy(writer, reader);
4431    4408    }

/**
5768        * Reader expanding SQL macro variables.
5769        */
5770        protected class SchemaManagerSQLSubstReader extends SQLSubstReader
5771        {

src/nexj/core/persistence/sql/SQLSchemaManagerFactory.java »
        @@ -38,6 +38,10 @@
38    38    {
39    39    return new PostgreSQLAdapter();
40    40    }
41        else if (sDatabase.equals("SQLite"))
42        {
43        return new SQLiteAdapter();
44        }
45    41    else
46    42    {
47    43    throw new PersistenceException("Unknown database product \"{0}\"",

src/nexj/core/persistence/sql/SQLiteDataSourceWrapper.java »
        @@ -0,0 +1,200 @@
1        package nexj.core.persistence.sql;
2       
3        import java.io.PrintWriter;
4        import java.lang.reflect.Method;
5        import java.sql.Connection;
6        import java.sql.SQLException;
7        import java.sql.Statement;
8       
9        import javax.sql.DataSource;
10       
11        import nexj.core.persistence.sql.pseudoxa.PseudoXADataSourceWrapper;
12       
13        public class SQLiteDataSourceWrapper extends PseudoXADataSourceWrapper
14        {

src/nexj/core/persistence/sql/SQLiteSQLSchemaManager.java »
        @@ -0,0 +1,622 @@
1        package nexj.core.persistence.sql;
2       
3        import java.io.Reader;
4       
5        import java.io.StringReader;
6        import java.io.StringWriter;
7       
8        import nexj.core.meta.Primitive;
9        import nexj.core.meta.persistence.sql.Column;
10        import nexj.core.meta.persistence.sql.Index;
11        import nexj.core.meta.persistence.sql.RelationalSchema;
12        import nexj.core.meta.persistence.sql.SQLScript;
13        import nexj.core.meta.persistence.sql.SQLSubstReader;
14        import nexj.core.meta.persistence.sql.Table;
15        import nexj.core.persistence.SchemaVersion;
16        import nexj.core.util.StringUtil;
17       
18        /**
19        * The SQLite schema manager.
20        */
21        public class SQLiteSQLSchemaManager extends SQLSchemaManager

##################### junit tests #########################
test/nexj/core/persistence/sql/AllTests.java »
        @@ -2,7 +2,6 @@
2    2    package nexj.core.persistence.sql;
3    3   
4    4    import junit.framework.Test;
    5   
5    6    import nexj.test.junit.SQLTestSuite;
6    7   
7    8    public class AllTests
        @@ -20,6 +19,7 @@
19    20    suite.addSQLTests("nexj.core.persistence.sql.Oracle9iAdapterTest", "nexj.core.persistence.sql.Oracle9iSchemaManagerTest");
20    21    suite.addSQLTests("nexj.core.persistence.sql.Oracle10gAdapterTest", "nexj.core.persistence.sql.Oracle10gSchemaManagerTest");
21    22    suite.addSQLTests("nexj.core.persistence.sql.Oracle11gAdapterTest", "nexj.core.persistence.sql.Oracle11gSchemaManagerTest");
22        suite.addSQLTests("nexj.core.persistence.sql.SQLiteAdapterTest", "nexj.core.persistence.sql.SQLiteSchemaManagerTest");  **
23    23    suite.addSQLTests("nexj.core.persistence.sql.SybaseAdapterTest", "nexj.core.persistence.sql.SybaseSchemaManagerTest");
24    24    suite.addSQLTests("nexj.core.persistence.sql.DB2v8AdapterTest", "nexj.core.persistence.sql.DB2v8SchemaManagerTest");
25    25    suite.addSQLTests("nexj.core.persistence.sql.DB2AdapterTest", "nexj.core.persistence.sql.DB2SchemaManagerTest");
files

test/nexj/core/persistence/sql/SQLiteAdapterTest.java »
        @@ -0,0 +1,49 @@
1        package nexj.core.persistence.sql;
2       
3        import nexj.core.meta.Metadata;
4        import nexj.core.util.Logger;
5       
6        /**
7        *
8        */
9        public class SQLiteAdapterTest extends SQLAdapterTest

test/nexj/core/persistence/sql/SQLiteSchemaManagerTest.java »
        @@ -0,0 +1,40 @@
1        package nexj.core.persistence.sql;
2       
3        /**
4        *
5        */
6        public class SQLiteSchemaManagerTest extends SQLSchemaManagerTest
7        {

test/nexj/core/persistence/sql/script/sqlite_drop.sql »
test/nexj/core/persistence/sql/script/sqlite_drop.sql »
test/nexj/core/persistence/sql/script/sqlite_insert.sql »
test/nexj/core/persistence/sql/script/sqlite_setup.sql »
test/nexj/core/persistence/sql/script/sqlite_upd_addr.sql »
test/nexj/core/persistence/sql/script/sqlite_upd_cont.sql »

test/nexj/default.config »
        @@ -11,20 +11,21 @@
11    11    test="true"
12    12    secureTransport="false"
13    13    app.user="jtest"
    14    defaultrelationaldatabase.mysql.enabled="true"
    15    defaultrelationaldatabase.postgresql.enabled="false">
14        defaultrelationaldatabase.mysql.enabled="false"
15        defaultrelationaldatabase.postgresql.enabled="false"
16        defaultrelationaldatabase.sqlite.enabled="true">
17    16    <DataSourceConnections>
18    17    <RelationalDatabaseConnection dataSource="DefaultRelationalDatabase"
    18    adapter="MySQL" unicode="true"
19        adapter="SQLite" unicode="true"
20    19    database="test"
    20    host="tor-sv-mysql" port="3306"
21        host="D:/NexjExpress/databases"
22    21    password="test" user="test">
23    22    <SQLHook activation="context" description="Hook to count the number of reads" type="nexj.core.persistence.sql.ReadCountHook"/>
24    23    </RelationalDatabaseConnection>
25    24    <RelationalDatabaseConnection dataSource="ExternalRelationalDatabase"
    25    adapter="MySQL" unicode="true"
26        adapter="SQLite" unicode="true"
27    26    database="test"
    27    host="tor-sv-mysql" port="3306"
28        host="D:/NexjExpress/databases"
29    28    password="test" user="test">
30    29    <SQLHook activation="context" description="Hook to count the number of reads" type="nexj.core.persistence.sql.ReadCountHook"/>
31    30    </RelationalDatabaseConnection>

test/nexj/filestorage.connections »
        @@ -1,7 +1,7 @@
1    1    <Connections description="MS SQL Server JUnit test environment">
2    2    <DataSourceConnections>
    3    <RelationalDatabaseConnection adapter="MySQL" dataSource="DefaultRelationalDatabase" database="test" host="tor-sv-mysql" password="test" port="3306" user="test"/>
    4    <RelationalDatabaseConnection adapter="MySQL" dataSource="ExternalRelationalDatabase" database="test" host="tor-sv-mysql" password="test" port="3306" user="test"/>
3        <RelationalDatabaseConnection adapter="SQLite" dataSource="DefaultRelationalDatabase" database="test" host="D:/NexjExpress/databases" password="test" user="test"/>
4        <RelationalDatabaseConnection adapter="SQLite" dataSource="ExternalRelationalDatabase" database="test" host="D:/NexjExpress/databases" password="test" user="test"/>
5    5    <!--Parameters here are placeholders, to be modified by the unit test code-->
6    6    <FileStorageConnection adapter="Generic" dataSource="TestFilePersistenceDataSource"
7    7    description="File persistence adapter testing"


test/nexj/filestoragefragment.connections »
        @@ -1,7 +1,7 @@
1    1    <Connections description="MS SQL Server JUnit test environment">
2    2    <DataSourceConnections>
    3    <RelationalDatabaseConnection adapter="MySQL" dataSource="DefaultRelationalDatabase" database="test" host="tor-sv-mysql" password="test" port="3306" user="test"/>
    4    <RelationalDatabaseConnection adapter="MySQL" dataSource="ExternalRelationalDatabase" database="test" host="tor-sv-mysql" password="test" port="3306" user="test"/>
3        <RelationalDatabaseConnection adapter="SQLite" dataSource="DefaultRelationalDatabase" database="test" host="D:/NexjExpress/databases" password="test" user="test"/>
4        <RelationalDatabaseConnection adapter="SQLite" dataSource="ExternalRelationalDatabase" database="test" host="D:/NexjExpress/databases" password="test" user="test"/>
5    5    <!--Parameters here are placeholders, to be modified by the unit test code-->
6    6    <FileStorageConnection adapter="Generic" dataSource="TestFilePersistenceDataSource"
7    7    description="File persistence adapter testing"

test/nexj/sqlite.connections »
        @@ -0,0 +1,14 @@
1        <Connections description="SQLite Server JUnit test environment">
2        <DataSourceConnections>
3        <RelationalDatabaseConnection adapter="SQLite" dataSource="DefaultRelationalDatabase" database="test" host="D:/NexjExpress/databases" password="test" user="test"/>
4        <RelationalDatabaseConnection adapter="SQLite" dataSource="ExternalRelationalDatabase" database="test" host="D:/NexjExpress/databases" password="test" user="test"/>
5        </DataSourceConnections>
6        <ChannelConnections>
7        <MessageQueueConnection channel="ErrorQueue" password="mqpass" user="mq"/>
8        <MessageQueueConnection channel="InputQueue" password="mqpass" user="mq"/>
9        <MessageQueueConnection channel="OutputQueue" password="mqpass" user="mq"/>
10        <MessageQueueConnection channel="SystemQueue" password="mqpass" user="mq"/>
11        <MessageQueueConnection channel="SystemTopic" password="mqpass" user="mq"/>
12        <MailConnection channel="Mail" from="mailtest@nexjsystems.com" inHost="192.168.25.1" inProtocol="pop3" inEncryption="TLS" outHost="192.168.25.1" outProtocol="smtp" password="mail123pass" user="mailtest"/>
13        </ChannelConnections>
14        </Connections>
15        \ No newline at end of file