For subsequent launches, the migration code searches for any scripts that have a higher version, and executes the SQL statements in those scripts. The schema version of the database shall be set to the latest version specified among the scripts. If the database for the application does not exist (as would be the case for the first launch), the migration code shall run the up scripts to create it. The up/down suffix determines whether the script will be used to upgrade or downgrade the database.This is ignored in the migration process. The description denotes the changes made by the script.The version is the numerical value used to determine the schema version of the database.This conveys the order in which these must be executed. The naming of these files is done in a specific format: _.sql. We refer to these files as migration scripts. This can be done by embedding a set of scripts containing SQL commands to do the same. Migration Scripts And VersioningĪ good way of shipping out a client side database application is to equip it with the mechanism to create the DB itself, when launched for the first time. Note: If you are aware of how to create and setup migration for databases using Go, please skip the Let Us GO! section below. This is based on one of our applications, where we encountered this problem of schema migration. This post covers the basics of SQLite DB creation and migration using Go, and provides guidelines on how to update the DB schema without loss or corruption of data. The Go programming language has libraries for creation and migration of SQLite databases. This is where SQLite has a slight disadvantage, discussed in detail later in this post. Such a modification must not, in any manner, tamper with or corrupt any data that already exists on the database. For instance, one of the tables might need a new column. However, occasionally, there may arise the need to modify the database schema. It is serverless, works with easily accessible cross-platform database files, and does not need any installation. SELECT t.name AS tbl_name, c.name, c.type, c.notnull, c.dflt_value, c.SQLite is a nifty choice for client side database applications. Instead, you can query directly for schema information using the sqlite_master table and PRAGMA statements like table_info and foreign_key_list.įor example, this query will retrieve metadata about all the columns in the database. doesn't implement the GetSchema method on DbConnection. Post.id AS post_id INTEGER PRIMARY KEY AUTOINCREMENT If (column as bool? = true)įor example, this query would produce the following debug string: SELECT id AS post_id, Var schemaTable = reader.GetSchemaTable() įoreach (DataRow column in schemaTable.Rows) The following example shows how to use GetSchemaTable to create a debug string that shows metadata about a result: var builder = new StringBuilder() True if the origin column is part of the PRIMARY KEY.Īlways NULL. True if the column originates from an expression in the query. True if the origin column was created with the AUTOINCREMENT keyword. True if the column name is aliased in the result set. The ordinal of the column in the result set.Īlways -1. The name or alias of the column in the result set. The path to the database file specified in the connection string. The name of the origin column's database. The returned DataTable contains the following columns: Column You can retrieve metadata about the results of a query using the GetSchemaTable method on SqliteDataReader. The other retrieves metadata about the database schema. One retrieves metadata about query results. There are two APIs for retrieving metadata in ADO.NET.
0 Comments
Leave a Reply. |