Example #1
0
        void store_CreateSchema(object sender, SQLiteConnection cnn, int actualVersion)
        {
            // cnn connection running in transaction

            SQLiteStore store = (SQLiteStore)sender;

            if (actualVersion <= 2)
            {
                // expand new schema

                if (actualVersion == 2)
                {
                    store.DropTable(cnn, "Attributes");
                    store.DropTable(cnn, "Files");
                }

                TableDef tableDef = new TableDef
                {
                    name = "Attributes",
                    displayName = "File Attributes",
                    columns = new ColumnDef[] {
                        new ColumnDef {name = "Name", displayName = "Name", type = "NVARCHAR"},
                        new ColumnDef {name = "Value", displayName = "Value", type = "NVARCHAR"},
                        new ColumnDef {name = "FileId", displayName = "FileId", type = "TABLE", parentTable = "Files"}
                    }
                };

                store.AddTable(cnn, tableDef);

                tableDef = new TableDef
                {
                    name = "Files",
                    displayName = "My Files",
                    columns = new ColumnDef[] {
                        new ColumnDef {name = "FileName", displayName = "File name", type = "NVARCHAR"},
                        new ColumnDef {name = "FilePath", displayName = "Path", type = "NVARCHAR"},
                        new ColumnDef {name = "FileSize", displayName = "Size", type = "INT"}
                    }
                };

                store.AddTable(cnn, tableDef);
            }
            else if (actualVersion == 3)
            {
                ColumnDef[] newCols = new ColumnDef[] {
                    new ColumnDef{ name = "state", displayName = "State", type = "TEXT", defValue = "'n/a'"},
                    new ColumnDef{ name = "image_size", displayName = "Image Size", type = "TEXT", defValue = "''"},
                    new ColumnDef{ name = "pixel_format", displayName = "Pixel Format", type = "TEXT", defValue = "''"}
                };

                store.AddColumns(cnn, "Files", newCols);
            }
        }
Example #2
0
        private void LoadTableDefs(SQLiteConnection cnn)
        {
            if (_tableDefs == null)
            {
                _tableDefs = new Dictionary <string, TableDef>(StringComparer.CurrentCultureIgnoreCase);
            }
            else
            {
                _tableDefs.Clear();
            }

            // read table defs

            SQLiteCommand    cmd    = GetCommand(cnn, "SELECT Id, Name, DisplayName FROM TableDefs");
            SQLiteDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                TableDef tableDef = new TableDef
                {
                    id          = reader.GetInt32(0),
                    name        = reader.GetString(1),
                    displayName = reader.GetString(2)
                };

                _tableDefs.Add(tableDef.name, tableDef);
            }
            reader.Close();

            // read column defs

            cmd.CommandText = "SELECT Id, Name, DisplayName, Type, ParentTable FROM ColumnDefs WHERE TableId = ?";
            SQLiteParameter idParam = new SQLiteParameter();

            cmd.Parameters.Add(idParam);
            cmd.Prepare();

            foreach (TableDef tableDef in _tableDefs.Values)
            {
                List <ColumnDef> colDefs = new List <ColumnDef>();

                idParam.Value = tableDef.id;
                reader        = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ColumnDef colDef = new ColumnDef()
                    {
                        id          = reader.GetInt32(0),
                        name        = reader.GetString(1),
                        displayName = reader.GetString(2),
                        type        = reader.GetString(3),
                        parentTable = reader.GetString(4),
                        tableName   = tableDef.name
                    };

                    colDefs.Add(colDef);
                }

                reader.Close();

                tableDef.columns = colDefs.ToArray();
            }
        }
Example #3
0
        private void ConfigureAdapter(TableDef tableDef)
        {
            SQLiteDataAdapter adapter;

            if (!_adapters.TryGetValue(tableDef.name, out adapter))
            {
                SQLiteCommandBuilder builder = new SQLiteCommandBuilder();

                // save default builder to quote identifiers
                if (_builder == null)
                    _builder = builder;

                adapter = new SQLiteDataAdapter(string.Format("SELECT * FROM {0}", QuoteIdentifier(tableDef.name)), _connString);
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                builder.DataAdapter = adapter;

                // to update primary key
                //adapter.InsertCommand = builder.GetInsertCommand();
                //adapter.InsertCommand.CommandText = adapter.InsertCommand.CommandText + "; SELECT last_insert_rowid() AS Id";
                //adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

                _adapters.Add(tableDef.name, adapter);
            }

            adapter.Fill(_dataSet, tableDef.name);
        }
Example #4
0
        private void LoadTableDefs(SQLiteConnection cnn)
        {
            if (_tableDefs == null)
                _tableDefs = new Dictionary<string, TableDef>(StringComparer.CurrentCultureIgnoreCase);
            else
                _tableDefs.Clear();

            // read table defs

            SQLiteCommand cmd = GetCommand(cnn, "SELECT Id, Name, DisplayName FROM TableDefs");
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                TableDef tableDef = new TableDef
                {
                    id = reader.GetInt32(0),
                    name = reader.GetString(1),
                    displayName = reader.GetString(2)
                };

                _tableDefs.Add(tableDef.name, tableDef);
            }
            reader.Close();

            // read column defs

            cmd.CommandText = "SELECT Id, Name, DisplayName, Type, ParentTable FROM ColumnDefs WHERE TableId = ?";
            SQLiteParameter idParam = new SQLiteParameter();
            cmd.Parameters.Add(idParam);
            cmd.Prepare();

            foreach (TableDef tableDef in _tableDefs.Values)
            {
                List<ColumnDef> colDefs = new List<ColumnDef>();

                idParam.Value = tableDef.id;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ColumnDef colDef = new ColumnDef()
                    {
                        id = reader.GetInt32(0),
                        name = reader.GetString(1),
                        displayName = reader.GetString(2),
                        type = reader.GetString(3),
                        parentTable = reader.GetString(4),
                        tableName = tableDef.name
                    };

                    colDefs.Add(colDef);
                }

                reader.Close();

                tableDef.columns = colDefs.ToArray();
            }
        }
Example #5
0
        // Add new table
        // cnn specified by CheckSchema create/update schema while Store object creating
        // specify newSchemaVersion after Store object created
        public void AddTable(SQLiteConnection cnn, TableDef tableDef, int newSchemaVersion = 0)
        {
            if (newSchemaVersion != 0 && _schemaVersion >= newSchemaVersion)
                throw new ArgumentException("New schema version should be greater then current");

            string quotedTableName = QuoteIdentifier(tableDef.name);

            string sql = string.Format("INSERT INTO TableDefs (Name, DisplayName) VALUES ('{0}', '{1}');\n", tableDef.name, tableDef.displayName);
            string colList = "Id INTEGER PRIMARY KEY AUTOINCREMENT, ";
            string valList = string.Format("('Id','INTEGER','Id','',(SELECT Id FROM TableDefs WHERE Name = '{0}')), ", tableDef.name);

            foreach (ColumnDef column in tableDef.columns)
            {
                if (column.type.StartsWith("TABLE")) // TABLE parentTable
                    colList += string.Format("{0} INTEGER {1}, FOREIGN KEY({0}) REFERENCES {2}(Id), ",
                        QuoteIdentifier(column.name),
                        (column.allowDBNull) ? string.Empty : "NOT NULL",
                        QuoteIdentifier(column.parentTable)
                    );
                else
                    colList += string.Format("{0} {1} {2} {3}, ",
                        QuoteIdentifier(column.name),
                        column.type,
                        (column.allowDBNull) ? string.Empty : "NOT NULL",
                        (column.defValue != null) ? "DEFAULT " + column.defValue : string.Empty
                    );

                valList += string.Format("('{0}','{1}','{2}','{3}',(SELECT Id FROM TableDefs WHERE Name = '{4}')), ",
                    column.name, column.type, column.displayName, column.parentTable, tableDef.name);
            }

            sql += string.Format("INSERT INTO ColumnDefs (Name,Type,DisplayName,ParentTable,TableId) VALUES {0};\n", valList.Remove(valList.Length - 2));
            sql += string.Format("CREATE TABLE {0} ({1});\n", quotedTableName, colList.Remove(colList.Length - 2));

            if (newSchemaVersion != 0 && _schemaVersion != newSchemaVersion)
                sql += string.Format("UPDATE Store SET Version = '{0}';", newSchemaVersion);

            ExecuteNonQuery(cnn, sql);

            // update _tableDefs if schema loaded
            if (_schemaVersion > 0)
            {
                _tableDefs.Add(tableDef.name, tableDef);
                ConfigureAdapter(tableDef);
            }

            if (newSchemaVersion != 0)
                _schemaVersion = newSchemaVersion;
        }