Пример #1
0
        public static void UpdateVersionNumber(SQLiteDatabase db)
        {
            string commandString = "update " + MetadataTable.TableName + " set " + MetadataTable.Version.Name + " = '" + Properties.Settings.Default.Version + "' ";

            db.NonQueryCommand(commandString);
        }
Пример #2
0
        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);
            }
        }
Пример #3
0
 private static bool delete(UpdateItem item, SQLiteDatabase db)
 {
     return(db.Delete(item.Table, item.FieldData));
 }
Пример #4
0
        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
            }
        }
Пример #5
0
 private static bool add(UpdateItem item, SQLiteDatabase db)
 {
     return(db.Replace(item.Table, item.FieldData));
 }