public void SetupVersioning()
{
// check to see this is not a special db
string curDB = db.GetCatalogName().ToUpper();
if (curDB == "MASTER" || curDB == "MSDB" || curDB == "TEMPDB" || curDB == "MODEL")
{
UI.Feedback("WARNING", "Cannot mark a system DB for versioning (currently [" + curDB + "])", mac.GetTF("$VERBOSE$"));
}
else
{
// needed vars
string sql;
bool needVersion = true;
bool needChanges = true;
// get the list of expected tables (dbo.DBBVersion & dbo.DBBChanges)
sql = "SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND (TABLE_SCHEMA + '.' + TABLE_NAME = '" + mac.Get("$DBBVERSION$") + "' OR TABLE_SCHEMA + '.' + TABLE_NAME = '" + mac.Get("$DBBCHANGES$") + "')";
DataSet ds = db.GetDataSet(sql);
// check to see if this has dbbversion/dbbchanges
if (ds.Tables.Count == 1)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
// check if we need the DBBVersion
if (row["TName"].ToString().ToUpper() == mac.Get("$DBBVERSION$").ToUpper())
{
needVersion = false;
}
// check if we need the DBBChanges
if (row["TName"].ToString().ToUpper() == mac.Get("$DBBCHANGES$").ToUpper())
{
needChanges = false;
}
}
}
// if not there, create DBBVersion
if (needVersion)
{
// create table
sql = "CREATE TABLE " + mac.Get("$DBBVERSION$") + " (InstalledOn datetime NOT NULL CONSTRAINT PK_" + mac.Get("$DBBVERSION$").Replace('.', '_') + " PRIMARY KEY, LastUpdateOn datetime NOT NULL, CurrentState nvarchar(30) NOT NULL)";
db.ExecuteSQL(sql);
// set initial version info
sql = "INSERT INTO " + mac.Get("$DBBVERSION$") + " (InstalledOn, LastUpdateOn, CurrentState) VALUES (GetUTCDate(), GetUTCDate(), 'INITIALIZING')";
db.ExecuteSQL(sql);
}
// if not there, create DBBChanges
if (needChanges)
{
// create table
sql = "CREATE TABLE " + mac.Get("$DBBCHANGES$") + " (Script nvarchar(450) NOT NULL CONSTRAINT PK_" + mac.Get("$DBBCHANGES$").Replace('.', '_') + " PRIMARY KEY, ChangeState nvarchar(30) NOT NULL, StartedOn datetime NOT NULL, CompletedOn datetime NULL)";
db.ExecuteSQL(sql);
}
}
}
