public static void UpdateVersionNumber(SQLiteDatabase db)
{
string commandString = "update " + MetadataTable.TableName + " set " + MetadataTable.Version.Name + " = '" + Properties.Settings.Default.Version + "' ";
db.NonQueryCommand(commandString);
}
static private void migrateFromTempTables(Dictionary <string, string> tableMap, SQLiteDatabase db)
{
string commandString;
foreach (KeyValuePair <string, string> tablePair in tableMap)
{
string tableName = tablePair.Key;
string tempName = tablePair.Value;
commandString = "insert into '" + tableName + "' select * from '" + tempName + "'";
db.NonQueryCommand(commandString);
commandString = "drop table '" + tempName + "'";
db.NonQueryCommand(commandString);
}
}
private static bool delete(UpdateItem item, SQLiteDatabase db)
{
return(db.Delete(item.Table, item.FieldData));
}
private static void cleanMigratedDatabase(SQLiteDatabase db, int version)
{
if (version < 10)
{
string commandString = "";
#region Point Types
List <String> deprecatedTypes = new List <string>()
{
"BACnetMSTP",
"BACnetIP",
"LonWorks",
"ModbusTCP",
"ModbusRTU"
};
foreach (var item in deprecatedTypes)
{
commandString = String.Format("update {0} set {1} = '{2}' where {3} = '{4}'",
PointTable.TableName, PointTable.Type.Name, "AI", PointTable.Type.Name, item);
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}' where {3} = '{4}'",
IOTable.TableName, IOTable.IOType.Name, "AI", IOTable.IOType.Name, item);
db.NonQueryCommand(commandString);
}
#endregion
#region HardwiredConnections
string deviceJoinTable = String.Format("select {0}, {1} as ConDevID from {2} inner join {3} where {2}.{4} = {3}.{5}", HardwiredConnectionChildrenTable.ConnectionID.Name, SubScopeDeviceTable.DeviceID.Name, HardwiredConnectionChildrenTable.TableName,
SubScopeDeviceTable.TableName, HardwiredConnectionChildrenTable.ChildID.Name, SubScopeDeviceTable.SubScopeID.Name);
string connectionConnectionTypeTableJoin = String.Format("select {0}, {1}, {2} from {3} inner join ({4}) where {5} = ConDevID", HardwiredConnectionChildrenTable.ConnectionID.Name,
DeviceConnectionTypeTable.TypeID.Name, DeviceConnectionTypeTable.Quantity.Name, DeviceConnectionTypeTable.TableName,
deviceJoinTable, DeviceConnectionTypeTable.DeviceID.Name);
commandString = String.Format("insert into {0} ({1}, {2}, {3}) {4}", HardwiredConnectionConnectionTypeTable.TableName, HardwiredConnectionConnectionTypeTable.ConnectionID.Name,
HardwiredConnectionConnectionTypeTable.TypeID.Name, HardwiredConnectionConnectionTypeTable.Quantity.Name, connectionConnectionTypeTableJoin);
db.NonQueryCommand(commandString);
#endregion
#region Network Connections
commandString = String.Format("insert into {0} select {1}, {1} from {2}", NetworkConnectionProtocolTable.TableName, NetworkConnectionTable.ID.Name, NetworkConnectionTable.TableName);
db.NonQueryCommand(commandString);
//Considered to account for the lack of protocol-compliant devices
//commandString = String.Format("delete from {0}", NetworkConnectionChildrenTable.TableName);
//db.NonQueryCommand(commandString);
#endregion
#region Bid Controllers and panels
if (dbType == DBType.Bid)
{
var controllerQuery = String.Format("select {0} as ControllerID from {1} where {0} not in (select {2} from {3})", ProvidedControllerTable.ID.Name, ProvidedControllerTable.TableName, SystemControllerTable.ControllerID.Name, SystemControllerTable.TableName);
var bidControllerJoin = String.Format("select {1} as BidID, ControllerID from {0} join ({2}) ", BidInfoTable.TableName, BidInfoTable.ID.Name, controllerQuery);
commandString = String.Format("insert into {0} ({1}, {2}) {3}", BidControllerTable.TableName, BidControllerTable.BidID.Name, BidControllerTable.ControllerID.Name, bidControllerJoin);
db.NonQueryCommand(commandString);
var panelQuery = String.Format("select {0} as PanelID from {1} where {0} not in (select {2} from {3})", PanelTable.ID.Name, PanelTable.TableName, SystemPanelTable.PanelID.Name, SystemPanelTable.TableName);
var bidPanelJoin = String.Format("select {1} as BidID, PanelID from {0} join ({2}) ", BidInfoTable.TableName, BidInfoTable.ID.Name, panelQuery);
commandString = String.Format("insert into {0} ({1}, {2}) {3}", BidPanelTable.TableName, BidPanelTable.BidID.Name, BidPanelTable.PanelID.Name, bidPanelJoin);
db.NonQueryCommand(commandString);
}
#endregion
#region Manager Templates
Guid templatesGuid = Guid.NewGuid();
var idField = dbType == DBType.Bid ? BidInfoTable.ID.Name : TemplatesInfoTable.ID.Name;
var managerTable = dbType == DBType.Bid ? BidInfoTable.TableName : TemplatesInfoTable.TableName;
var managerID = String.Format("select {0} from {1} limit 1", idField, managerTable);
commandString = String.Format("insert into {0} ({1}, {2}) values (({3}), '{4}')",
ManagerTemplatesTable.TableName, ManagerTemplatesTable.ManagerID.Name, ManagerTemplatesTable.TemplatesID.Name, managerID, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesControllerTable.TableName, TemplatesControllerTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesEquipmentTable.TableName, TemplatesEquipmentTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesSystemTable.TableName, TemplatesSystemTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesSubScopeTable.TableName, TemplatesSubScopeTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesMiscCostTable.TableName, TemplatesMiscCostTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesPanelTable.TableName, TemplatesPanelTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("insert into {0} ({1}) select {2} from {3}", TemplatesParametersTable.TableName, TemplatesParametersTable.ParametersID.Name, ParametersTable.ID.Name, ParametersTable.TableName);
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesParametersTable.TableName, TemplatesParametersTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("insert into {0} values ('{1}')", ScopeTemplatesTable.TableName, templatesGuid.ToString());
db.NonQueryCommand(commandString);
#endregion
}
}
private static bool add(UpdateItem item, SQLiteDatabase db)
{
return(db.Replace(item.Table, item.FieldData));
}