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)); }