From 6c1fce61473b8d3faf9ee4602b9d0a392f148b7c Mon Sep 17 00:00:00 2001
From: Sean Dague
Date: Wed, 11 Jun 2008 21:01:33 +0000
Subject: 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.
---
OpenSim/Data/Migration.cs | 8 +-
OpenSim/Data/SQLite/SQLiteAssetData.cs | 24 ++-
OpenSim/Data/SQLite/SQLiteInventoryStore.cs | 120 ++++++------
OpenSim/Data/SQLite/SQLiteRegionData.cs | 286 +++++++++++++++-------------
OpenSim/Data/SQLite/SQLiteUserData.cs | 52 +++--
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;
}
}
}
--
cgit v1.1