check in working migration code fore SQLite. This

is now using migrations instead of the old model to
create tables.  Tested for existing old tables, 
and for creating new ones.

5 files changed, 276 insertions, 214 deletions

diff --git a/OpenSim/Data/Migration.cs b/OpenSim/Data/Migration.cs
index 83f4c3d..54d4970 100644
--- a/OpenSim/Data/Migration.cs
+++ b/OpenSim/Data/Migration.cs
@@ -159,7 +159,13 @@ namespace OpenSim.Data
         public int Version 
         {
             get { return FindVersion(_type); }
-            set { UpdateVersion(_type, value); }
+            set { 
+                if (Version < 1) {
+                    InsertVersion(_type, value);
+                } else {
+                    UpdateVersion(_type, value);
+                } 
+            }
         }
 
         private int FindVersion(string type) 
diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs
index 8d4fb99..4370cf7 100644
--- a/OpenSim/Data/SQLite/SQLiteAssetData.cs
+++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs
@@ -64,7 +64,19 @@ namespace OpenSim.Data.SQLite
             }
             m_conn = new SqliteConnection(dbconnect);
             m_conn.Open();
-            TestTables(m_conn);
+
+
+
+
+            Assembly assem = GetType().Assembly;
+            Migration m = new Migration(m_conn, assem, "AssetStore");
+            // TODO: remove this next line after changeset 6000,
+            // people should have all gotten into the migration swing
+            // again.
+            TestTables(m_conn, m);
+
+            m.Update();
+
             return;
         }
 
@@ -258,7 +270,7 @@ namespace OpenSim.Data.SQLite
             pcmd.ExecuteNonQuery();
         }
 
-        private static bool TestTables(SqliteConnection conn)
+        private static bool TestTables(SqliteConnection conn, Migration m)
         {
             SqliteCommand cmd = new SqliteCommand(assetSelect, conn);
             SqliteDataAdapter pDa = new SqliteDataAdapter(cmd);
@@ -270,8 +282,14 @@ namespace OpenSim.Data.SQLite
             catch (SqliteSyntaxException)
             {
                 m_log.Info("[ASSET DB]: SQLite Database doesn't exist... creating");
-                InitDB(conn);
+                return false;
             }
+            
+            // if the tables are here, and we don't have a migration,
+            // set it to 1, as we're migrating off of legacy bits
+            if (m.Version == 0) 
+                m.Version = 1;
+
             return true;
         }
 
diff --git a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
index 5acc5f2..5b77f82 100644
--- a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
+++ b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
@@ -61,7 +61,12 @@ namespace OpenSim.Data.SQLite
 
             conn.Open();
 
-            TestTables(conn);
+            Assembly assem = GetType().Assembly;
+            Migration m = new Migration(conn, assem, "InventoryStore");
+            // TODO: remove this line after changeset 6000
+            TestTables(conn, m);
+
+            m.Update();
 
             SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
             invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
@@ -672,7 +677,7 @@ namespace OpenSim.Data.SQLite
             scmd.ExecuteNonQuery();
         }
 
-        private static bool TestTables(SqliteConnection conn)
+        private static bool TestTables(SqliteConnection conn, Migration m)
         {
             SqliteCommand invItemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
             SqliteDataAdapter pDa = new SqliteDataAdapter(invItemsSelectCmd);
@@ -688,63 +693,68 @@ namespace OpenSim.Data.SQLite
             catch (SqliteSyntaxException)
             {
                 m_log.Info("[INVENTORY DB]: SQLite Database doesn't exist... creating");
-                InitDB(conn);
+                return false;
             }
 
-            pDa.Fill(tmpDS, "inventoryitems");
-            sDa.Fill(tmpDS, "inventoryfolders");
-
-            // Very clumsy way of checking whether we need to upgrade the database table version and then updating.  Only
-            // putting up with this because this code should be blown away soon by nhibernate...
-            conn.Open();
-
-            SqliteCommand cmd;
-            try
-            {
-                cmd = new SqliteCommand("select salePrice from inventoryitems limit 1;", conn);
-                cmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                m_log.Info("[INVENTORY DB]: Upgrading sqlite inventory database to version 2");
-
-                cmd = new SqliteCommand("alter table inventoryitems add column salePrice integer default 99;", conn);
-                cmd.ExecuteNonQuery();
-                cmd = new SqliteCommand("alter table inventoryitems add column saleType integer default 0;", conn);
-                cmd.ExecuteNonQuery();
-                cmd = new SqliteCommand("alter table inventoryitems add column creationDate integer default 2000;", conn);
-                cmd.ExecuteNonQuery();
-                cmd = new SqliteCommand("alter table inventoryitems add column groupID varchar(255) default '00000000-0000-0000-0000-000000000000';", conn);
-                cmd.ExecuteNonQuery();
-                cmd = new SqliteCommand("alter table inventoryitems add column groupOwned integer default 0;", conn);
-                cmd.ExecuteNonQuery();
-                cmd = new SqliteCommand("alter table inventoryitems add column flags integer default 0;", conn);
-                cmd.ExecuteNonQuery();
+            if (m.Version == 0)
+                m.Version = 1;
 
-                pDa.Fill(tmpDS, "inventoryitems");
-            }
-            finally
-            {
-                conn.Close();
-            }
-
-            foreach (DataColumn col in createInventoryItemsTable().Columns)
-            {
-                if (! tmpDS.Tables["inventoryitems"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[INVENTORY DB]: Missing required column:" + col.ColumnName);
-                    return false;
-                }
-            }
-            foreach (DataColumn col in createInventoryFoldersTable().Columns)
-            {
-                if (! tmpDS.Tables["inventoryfolders"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[INVENTORY DB]: Missing required column:" + col.ColumnName);
-                    return false;
-                }
-            }
             return true;
+
+            // pDa.Fill(tmpDS, "inventoryitems");
+            // sDa.Fill(tmpDS, "inventoryfolders");
+
+            // // Very clumsy way of checking whether we need to upgrade the database table version and then updating.  Only
+            // // putting up with this because this code should be blown away soon by nhibernate...
+            // conn.Open();
+
+            // SqliteCommand cmd;
+            // try
+            // {
+            //     cmd = new SqliteCommand("select salePrice from inventoryitems limit 1;", conn);
+            //     cmd.ExecuteNonQuery();
+            // }
+            // catch (SqliteSyntaxException)
+            // {
+            //     m_log.Info("[INVENTORY DB]: Upgrading sqlite inventory database to version 2");
+
+            //     cmd = new SqliteCommand("alter table inventoryitems add column salePrice integer default 99;", conn);
+            //     cmd.ExecuteNonQuery();
+            //     cmd = new SqliteCommand("alter table inventoryitems add column saleType integer default 0;", conn);
+            //     cmd.ExecuteNonQuery();
+            //     cmd = new SqliteCommand("alter table inventoryitems add column creationDate integer default 2000;", conn);
+            //     cmd.ExecuteNonQuery();
+            //     cmd = new SqliteCommand("alter table inventoryitems add column groupID varchar(255) default '00000000-0000-0000-0000-000000000000';", conn);
+            //     cmd.ExecuteNonQuery();
+            //     cmd = new SqliteCommand("alter table inventoryitems add column groupOwned integer default 0;", conn);
+            //     cmd.ExecuteNonQuery();
+            //     cmd = new SqliteCommand("alter table inventoryitems add column flags integer default 0;", conn);
+            //     cmd.ExecuteNonQuery();
+
+            //     pDa.Fill(tmpDS, "inventoryitems");
+            // }
+            // finally
+            // {
+            //     conn.Close();
+            // }
+
+            // foreach (DataColumn col in createInventoryItemsTable().Columns)
+            // {
+            //     if (! tmpDS.Tables["inventoryitems"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[INVENTORY DB]: Missing required column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
+            // foreach (DataColumn col in createInventoryFoldersTable().Columns)
+            // {
+            //     if (! tmpDS.Tables["inventoryfolders"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[INVENTORY DB]: Missing required column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
+            // return true;
         }
     }
 }
diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs
index 6a16a88..42299a0 100644
--- a/OpenSim/Data/SQLite/SQLiteRegionData.cs
+++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs
@@ -84,6 +84,8 @@ namespace OpenSim.Data.SQLite
             m_conn = new SqliteConnection(m_connectionString);
             m_conn.Open();
 
+
+
             SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
             primDa = new SqliteDataAdapter(primSelectCmd);
             //            SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
@@ -104,10 +106,15 @@ namespace OpenSim.Data.SQLite
             SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
             landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
 
-            // We fill the data set, now we've got copies in memory for the information
-            // TODO: see if the linkage actually holds.
-            // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema");
-            TestTables(m_conn);
+            // This actually does the roll forward assembly stuff
+            Assembly assem = GetType().Assembly;
+            Migration m = new Migration(m_conn, assem, "RegionStore");
+
+            // TODO: After rev 6000, remove this.  People should have
+            // been rolled onto the new migration code by then.
+            TestTables(m_conn, m);
+
+            m.Update();
 
             lock (ds)
             {
@@ -1520,81 +1527,81 @@ namespace OpenSim.Data.SQLite
         /// Create the necessary database tables.
         /// </summary>
         /// <param name="conn"></param>
-        private void InitDB(SqliteConnection conn)
-        {
-            string createPrims = defineTable(createPrimTable());
-            string createShapes = defineTable(createShapeTable());
-            string createItems = defineTable(createItemsTable());
-            string createTerrain = defineTable(createTerrainTable());
-            string createLand = defineTable(createLandTable());
-            string createLandAccessList = defineTable(createLandAccessListTable());
-
-            SqliteCommand pcmd = new SqliteCommand(createPrims, conn);
-            SqliteCommand scmd = new SqliteCommand(createShapes, conn);
-            SqliteCommand icmd = new SqliteCommand(createItems, conn);
-            SqliteCommand tcmd = new SqliteCommand(createTerrain, conn);
-            SqliteCommand lcmd = new SqliteCommand(createLand, conn);
-            SqliteCommand lalcmd = new SqliteCommand(createLandAccessList, conn);
-
-            try
-            {
-                pcmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                m_log.Warn("[REGION DB]: Primitives Table Already Exists");
-            }
-
-            try
-            {
-                scmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                m_log.Warn("[REGION DB]: Shapes Table Already Exists");
-            }
-
-            if (persistPrimInventories)
-            {
-                try
-                {
-                    icmd.ExecuteNonQuery();
-                }
-                catch (SqliteSyntaxException)
-                {
-                    m_log.Warn("[REGION DB]: Primitives Inventory Table Already Exists");
-                }
-            }
-
-            try
-            {
-                tcmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                m_log.Warn("[REGION DB]: Terrain Table Already Exists");
-            }
-
-            try
-            {
-                lcmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                m_log.Warn("[REGION DB]: Land Table Already Exists");
-            }
-
-            try
-            {
-                lalcmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                m_log.Warn("[SQLITE]: LandAccessList Table Already Exists");
-            }
-        }
-
-        private bool TestTables(SqliteConnection conn)
+        // private void InitDB(SqliteConnection conn)
+        // {
+        //     string createPrims = defineTable(createPrimTable());
+        //     string createShapes = defineTable(createShapeTable());
+        //     string createItems = defineTable(createItemsTable());
+        //     string createTerrain = defineTable(createTerrainTable());
+        //     string createLand = defineTable(createLandTable());
+        //     string createLandAccessList = defineTable(createLandAccessListTable());
+
+        //     SqliteCommand pcmd = new SqliteCommand(createPrims, conn);
+        //     SqliteCommand scmd = new SqliteCommand(createShapes, conn);
+        //     SqliteCommand icmd = new SqliteCommand(createItems, conn);
+        //     SqliteCommand tcmd = new SqliteCommand(createTerrain, conn);
+        //     SqliteCommand lcmd = new SqliteCommand(createLand, conn);
+        //     SqliteCommand lalcmd = new SqliteCommand(createLandAccessList, conn);
+
+        //     try
+        //     {
+        //         pcmd.ExecuteNonQuery();
+        //     }
+        //     catch (SqliteSyntaxException)
+        //     {
+        //         m_log.Warn("[REGION DB]: Primitives Table Already Exists");
+        //     }
+
+        //     try
+        //     {
+        //         scmd.ExecuteNonQuery();
+        //     }
+        //     catch (SqliteSyntaxException)
+        //     {
+        //         m_log.Warn("[REGION DB]: Shapes Table Already Exists");
+        //     }
+
+        //     if (persistPrimInventories)
+        //     {
+        //         try
+        //         {
+        //             icmd.ExecuteNonQuery();
+        //         }
+        //         catch (SqliteSyntaxException)
+        //         {
+        //             m_log.Warn("[REGION DB]: Primitives Inventory Table Already Exists");
+        //         }
+        //     }
+
+        //     try
+        //     {
+        //         tcmd.ExecuteNonQuery();
+        //     }
+        //     catch (SqliteSyntaxException)
+        //     {
+        //         m_log.Warn("[REGION DB]: Terrain Table Already Exists");
+        //     }
+
+        //     try
+        //     {
+        //         lcmd.ExecuteNonQuery();
+        //     }
+        //     catch (SqliteSyntaxException)
+        //     {
+        //         m_log.Warn("[REGION DB]: Land Table Already Exists");
+        //     }
+
+        //     try
+        //     {
+        //         lalcmd.ExecuteNonQuery();
+        //     }
+        //     catch (SqliteSyntaxException)
+        //     {
+        //         m_log.Warn("[SQLITE]: LandAccessList Table Already Exists");
+        //     }
+        // }
+
+        private bool TestTables(SqliteConnection conn, Migration m)
         {
             SqliteCommand primSelectCmd = new SqliteCommand(primSelect, conn);
             SqliteDataAdapter pDa = new SqliteDataAdapter(primSelectCmd);
@@ -1630,65 +1637,72 @@ namespace OpenSim.Data.SQLite
             catch (SqliteSyntaxException)
             {
                 m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
-                InitDB(conn);
-            }
-
-            pDa.Fill(tmpDS, "prims");
-            sDa.Fill(tmpDS, "primshapes");
-
-            if (persistPrimInventories)
-                iDa.Fill(tmpDS, "primitems");
-
-            tDa.Fill(tmpDS, "terrain");
-            lDa.Fill(tmpDS, "land");
-            lalDa.Fill(tmpDS, "landaccesslist");
-
-            foreach (DataColumn col in createPrimTable().Columns)
-            {
-                if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[REGION DB]: Missing required column:" + col.ColumnName);
-                    return false;
-                }
+                return false;
             }
 
-            foreach (DataColumn col in createShapeTable().Columns)
-            {
-                if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[REGION DB]: Missing required column:" + col.ColumnName);
-                    return false;
-                }
-            }
-
-            // XXX primitems should probably go here eventually
-
-            foreach (DataColumn col in createTerrainTable().Columns)
-            {
-                if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[REGION DB]: Missing require column:" + col.ColumnName);
-                    return false;
-                }
-            }
-
-            foreach (DataColumn col in createLandTable().Columns)
-            {
-                if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[REGION DB]: Missing require column:" + col.ColumnName);
-                    return false;
-                }
-            }
-
-            foreach (DataColumn col in createLandAccessListTable().Columns)
-            {
-                if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName))
-                {
-                    m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
-                    return false;
-                }
-            }
+            // if we've gotten this far, and our version is still 0,
+            // it's because the migration was never done, so
+            // initialize to 1 just to sync up to where we should be.
+            
+            if (m.Version == 0) 
+                m.Version = 1;
+
+            // pDa.Fill(tmpDS, "prims");
+            // sDa.Fill(tmpDS, "primshapes");
+
+            // if (persistPrimInventories)
+            //     iDa.Fill(tmpDS, "primitems");
+
+            // tDa.Fill(tmpDS, "terrain");
+            // lDa.Fill(tmpDS, "land");
+            // lalDa.Fill(tmpDS, "landaccesslist");
+
+            // foreach (DataColumn col in createPrimTable().Columns)
+            // {
+            //     if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[REGION DB]: Missing required column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
+
+            // foreach (DataColumn col in createShapeTable().Columns)
+            // {
+            //     if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[REGION DB]: Missing required column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
+
+            // // XXX primitems should probably go here eventually
+
+            // foreach (DataColumn col in createTerrainTable().Columns)
+            // {
+            //     if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[REGION DB]: Missing require column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
+
+            // foreach (DataColumn col in createLandTable().Columns)
+            // {
+            //     if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[REGION DB]: Missing require column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
+
+            // foreach (DataColumn col in createLandAccessListTable().Columns)
+            // {
+            //     if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName))
+            //     {
+            //         m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
+            //         return false;
+            //     }
+            // }
 
             return true;
         }
diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs
index c9ef3c2..36ec9ea 100644
--- a/OpenSim/Data/SQLite/SQLiteUserData.cs
+++ b/OpenSim/Data/SQLite/SQLiteUserData.cs
@@ -72,12 +72,20 @@ namespace OpenSim.Data.SQLite
                 connect = "URI=file:userprofiles.db,version=3";
 
             SqliteConnection conn = new SqliteConnection(connect);
-            TestTables(conn);
 
             // This sucks, but It doesn't seem to work with the dataset Syncing :P
             g_conn = conn;
             g_conn.Open();
 
+            Assembly assem = GetType().Assembly;
+            Migration m = new Migration(g_conn, assem, "UserStore");
+            
+            // TODO: remove this after rev 6000
+            TestTables(conn, m);
+
+            m.Update();
+
+
             ds = new DataSet();
             da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
             daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
@@ -824,7 +832,7 @@ namespace OpenSim.Data.SQLite
             conn.Close();
         }
 
-        private static bool TestTables(SqliteConnection conn)
+        private static bool TestTables(SqliteConnection conn, Migration m)
         {
             SqliteCommand cmd = new SqliteCommand(userSelect, conn);
             SqliteCommand fcmd = new SqliteCommand(userFriendsSelect, conn);
@@ -842,26 +850,32 @@ namespace OpenSim.Data.SQLite
             catch (SqliteSyntaxException)
             {
                 m_log.Info("[USER DB]: SQLite Database doesn't exist... creating");
-                InitDB(conn);
-            }
-            conn.Open();
-            try
-            {
-                cmd = new SqliteCommand("select webLoginKey from users limit 1;", conn);
-                cmd.ExecuteNonQuery();
-            }
-            catch (SqliteSyntaxException)
-            {
-                cmd = new SqliteCommand("alter table users add column webLoginKey text default '00000000-0000-0000-0000-000000000000';", conn);
-                cmd.ExecuteNonQuery();
-                pDa.Fill(tmpDS, "users");
-            }
-            finally
-            {
-                conn.Close();
+                return false;
             }
 
+            if (m.Version == 0) 
+                m.Version = 1;
+
             return true;
+
+            // conn.Open();
+            // try
+            // {
+            //     cmd = new SqliteCommand("select webLoginKey from users limit 1;", conn);
+            //     cmd.ExecuteNonQuery();
+            // }
+            // catch (SqliteSyntaxException)
+            // {
+            //     cmd = new SqliteCommand("alter table users add column webLoginKey text default '00000000-0000-0000-0000-000000000000';", conn);
+            //     cmd.ExecuteNonQuery();
+            //     pDa.Fill(tmpDS, "users");
+            // }
+            // finally
+            // {
+            //     conn.Close();
+            // }
+
+            // return true;
         }
     }
 }