SQLite彩民之家论坛9066777

2019-09-18 21:58 来源:未知

2.3、迁移文件的命名

通过.sql 的方式来命名。描述文字是可选的的。

SQLite 基础知识

SQLite

rails gmodel person name:string age:int

2.4、生成sql文件

可通过下面的命令生成

touch "`ruby -e "puts Time.now.strftime('%Y%m%d%H%M%S%3N').to_i"`"_CreateMyAwesomeTable.sql

SQLite 表复制

  • 复制整张表,包括表里面的数据。
CREATE TABLE affix_backup AS SELECT * FROM affix;
  • 只复制表结构,不复制内容。
CREATE TABLE affix_backup AS SELECT * FROM affix where 1=0;
  • 内容复制(SQLite不支持第一种)。
--把数据插入一张新建立的表中,这张表是执行sql的时候建立的。
SELECT ... INTO new_tablename
FROM ...
WHERE ...
--或者
--把查询出来的数据插入已经建立好的表中
INSERT INTO tmp_tablename
SELECT ...
FROM ...
WHERE ...

这将在db/migrate目录下创建002_add_degree_to_people.rb文件,对它进行编辑:

2.2、版本号的命名

推荐使用时间戳的方式来命名,可使用下面的语法来生成。

ruby -e "puts Time.now.strftime('%Y%m%d%H%M%S%3N').to_i"

列操作

sqlite中不支持删除列的操作,也不能够修改主键。

彩民之家论坛9066777 1

ALTER TABLE操作

从上图可见,SQLite官方并不支持delete或者drop列。

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table.

那么在SQLite中,如果需要删除某一列或者更改表的主码,需要使用创建临时表的方式进行。创建一张表结构正确的临时表,并将原表中的数据转移过去,再删除原表,重命名临时表为正确表名。下面是一个Java的例子:

List<String> sqls = new ArrayList<>();
if (isTableExist(dbManager, "XX")) {
  sqls.add("CREATE TABLE "XX_tmp" ("arg0" TEXT ,"arg1" TEXT ,"arg2" TEXT ,"arg3" TEXT ,"arg4" TEXT PRIMARY KEY ,"attflag" TEXT ,"attmsg" TEXT );"); 
  sqls.add("INSERT INTO XX_tmp SELECT * FROM XX;");
  sqls.add("DROP TABLE XX;");
  sqls.add("ALTER TABLE XX_tmp RENAME TO XX;");
  dbManager.getDatabase().beginTransaction();
  for (String sql : sqls)
    dbManager.execNonQuery(sql);
  dbManager.getDatabase().endTransaction();
  U.L.d(TAG, "用户数据库第一次升级完成:修改XX表的主键!");
}

另外,除了db:migrate,还有两个比较有用的操作数据库的rake任务:

随着应用的不断升级,原有的数据库结构不再适应新的功能,这时候,就需要对SQLite数据库的结构进行迁移升级。推荐使用 FMDBMigrationManager

FMDBMigrationManager对数据的操作是基于FMDB的,所以必须在项目中引入FMDB

PRAGMA Statements

SQLite 的 PRAGMA 命令是一个特殊的命令,可以用在 SQLite 环境内控制各种环境变量和状态标志。

如果我们连people表也不需要了,那就执行:

3.1、通过.sql文件进行迁移升级

  • 创建第一个.sql 文件
$ touch "`ruby -e "puts Time.now.strftime('%Y%m%d%H%M%S%3N').to_i"`"_CreateMyUserTable.sql
  • 编辑.sql文件 输入下面的命令
// 创建User表CREATE TABLE User(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT);
  • 创建第二个.sql 文件
$touch "`ruby -e "puts Time.now.strftime('%Y%m%d%H%M%S%3N').to_i"`"_CreateMyGroupTable.sql
  • 编辑.sql文件 输入下面的命令
// 创建Grouping表,并修改User表添加一个TEXT类型的email字段CREATE TABLE Grouping(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT);ALTER TABLE User ADD email TEXT;
  • 创建第三个.sql 文件
$ touch "`ruby -e "puts Time.now.strftime('%Y%m%d%H%M%S%3N').to_i"`"_AlterMyGroupTable.sql
  • 编辑.sql文件 输入下面的命令
// 修改Grouping表添加一个TEXT类型的remark字段ALTER TABLE Grouping ADD remark TEXT;
  • 具体用法代码
// 创建.sql文件的方式进行迁移, 自动扫描包内以.sql结尾的文件 func migrate1() { //db路径 let path = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask) let filePath = path.first?.appendingPathComponent("User.db").path // 迁移管理器 let manager = FMDBMigrationManager(databaseAtPath: filePath, migrationsBundle: Bundle.main)! // 判断是否有迁移表,如果没有 则创建 schema_migrations表 里面记录着创建的版本信息 if !manager.hasMigrationsTable { do { try manager.createMigrationsTable() }catch{ print("createMigrationsTable- error:  } } // 迁移数据库 do { try manager.migrateDatabase(toVersion: UINT64_MAX, progress: nil) }catch{ print("migrateDatabase - error:  } // 迁移表 存储版本号信息 print("Has `schema_migrations` table?: ", manager.hasMigrationsTable ? "true" : "false") // 原始版本 print("Origin Version: ", manager.originVersion) // 当前版本 print("Current version: ", manager.currentVersion) // 所有的版本 print("All migrations: ", manager.migrations) // 这次更新应用的版本 print("Applied versions: ", manager.appliedVersions) // 要更新的版本 print("Pending versions: ", manager.pendingVersions) /** Has `schema_migrations` table?: true Origin Version: 0 Current version: 20170315173002899 All migrations: [<Alert___Custom.FSMigration: 0x600000671e80>, <FMDBFileMigration: 0x60800002f300>, <FMDBFileMigration: 0x60800002f3c0>, <FMDBFileMigration: 0x60800002f480>] Applied versions: [0, 20170315171810827, 20170315172759084, 20170315173002899] Pending versions: [] **/ }

sqlite_master

SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表, 它定义数据库的模式。

type值为table表示对应的是表记录,type值为index表示对应的是索引的记录。
最后一栏列名为“sql”,存储的是创建表或者索引时的原始sql命令。如果index是自动生成的,那么sql为空。
sqlite_master表是只读表,不能手工进行内容修改。

彩民之家论坛9066777 2

sqlite_master.png

查询数据库中是否存在某张表,或者查询数据库中所有的表可用如下方式:

--查询是否存在table_name这张表,实际上是查询有几个table_name表名存在,返回0则不存在,1则存在。
SELECT count(*) AS c
FROM sqlite_master
WHERE TYPE ='table'
  AND name ='table_name';

--查询数据库中所有的表
SELECT name AS TABLE_NAME
FROM sqlite_master
WHERE TYPE='table';

上面用到的isTableExist(DbManager dbManager, String tableName)方法如下:

//判断表是否存在
protected final boolean isTableExist(DbManager dbManager, String tableName) {
    boolean isExist = false;
    String sql = "SELECT COUNT(*) AS c FROM sqlite_master WHERE TYPE ='table' AND NAME ='"   tableName   "';";
    try {
        Cursor mCursor = dbManager.execQuery(sql);
        if (mCursor.moveToNext()) {
            int count = mCursor.getInt(0);
            if (count > 0) {
                isExist = true;
            }
        }
    } catch (DbException e) {
        e.printStackTrace();
    }
    return isExist;
}

临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER 的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多, 但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表, 不管是永久的还是临时的,可以使用类似下面的命令:

SELECT name
FROM
 (SELECT *
  FROM sqlite_master
  UNION ALL SELECT *
  FROM sqlite_temp_master)
WHERE TYPE=’TABLE’

现在,你会发现你的数据库中已经多了一个people表,它包含3个字段:id,name,age,如果你不需要那个自动创建的id字段,那么你可以修改001_create_people.rb,在create_table :people后面加上:id => false即可。

use_frameworks!platform :ios, '8.0'target ‘AppName’ do# SQLite 本地数据库pod 'FMDB'# SQLite 本地数据库版本升级管理pod 'FMDBMigrationManager'

$ rake db:migrate version=1

2.1、实现细节

FMDBMigrationManager对版本的管理通过在数据库中引入一个schema_migrations表。表的每一个行记录着一个应用的版本号。

CREATE TABLE schema_migrations( version INTEGER UNIQUE NOT NULL);

$rails gmigration add_degree_to_people
== AddDegreeToPeople: migrating ============
-- add_column(:people, :degree, :string)
-> 0.3400s
== AddDegreeToPeople: migrated ===

Podfile 文件:

如果随后你发现你需要再增加一个学位字段,也很简单:

3.2、通过自定义对象进行迁移升级

定义对象FSMigration 遵循FMDBMigrating协议

class FSMigration: NSObject, FMDBMigrating { // 只读 计算属性 var name: String! { return myName } var version: UInt64 { return myVersion } private var updateArray = [String]() // 更新数组 private var myName:String! // 名字 private var myVersion: UInt64 = 0 // 版本 override init() { super.init() } convenience init(name:String, version: UInt64, updateArray:[String]) { self.init() myName = name myVersion = version self.updateArray = updateArray print("self.updateArray: (self.updateArray)") } func migrateDatabase(_ database: FMDatabase!) throws { print("updateArray: (self.updateArray)") for updateStr in self.updateArray { do { try database.executeUpdate(updateStr, values: nil) }catch { throw error } } } }
  • 具体使用代码
// 数据库表迁移 func migrate() { //db路径 let path = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask) let filePath = path.first?.appendingPathComponent("person.db").path // 迁移管理器 let manager = FMDBMigrationManager(databaseAtPath: filePath, migrationsBundle: Bundle.main)! // 版本 1 let migration1 = FSMigration(name: "新增User表", version: 1, updateArray: ["create table User(id integer primary key autoincrement, name text, age integer)"]) // 版本 2 let migration2 = FSMigration(name: "USer表新增字段email", version: 2, updateArray: ["alter table User add email text"]) // 报错 不能修改 let migration3 = FSMigration(name: "修改email类型为integer", version: 3, updateArray: ["alter table User alter column email integer"]) // 报错 不能删除 let migration4 = FSMigration(name: "删除email列", version: 4, updateArray: ["ALTER TABLE User DROP COLUMN email"]) // 添加版本到 manager manager.addMigration(migration1) manager.addMigration(migration2) manager.addMigration(migration3) manager.addMigration(migration4) // 判断是否有迁移表,如果没有 则创建 schema_migrations表 里面记录着创建的版本信息 if !manager.hasMigrationsTable { do { try manager.createMigrationsTable() }catch{ print("createMigrationsTable- error:  } } // 迁移数据库 do { try manager.migrateDatabase(toVersion: UINT64_MAX, progress: nil) }catch{ print("migrateDatabase - error:  } print("Has `schema_migrations` table?: ", manager.hasMigrationsTable ? "true" : "false") print("Origin Version: ", manager.originVersion) print("Current version: ", manager.currentVersion) print("All migrations: ", manager.migrations) print("Applied versions: ", manager.appliedVersions) print("Pending versions: ", manager.pendingVersions) }

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

SQLite 对 ALERT TABLE 命令受限制,SQLite 中的 ALERT TABLE 命令只能允许用户重命名表或者添加新列,不能重命名列或者删除列或者删除约束。

  • FMDBMigrationManager
  • iOS SQLite 数据库迁移

比如,假设我们现在想从people中去除degree字段,那我们只需要执行下面这条语句就可以了:

2.4、 计算未应用的迁移

使用下面的算法来指定给数据的迁移

  1. 计算database的origin version
  2. 对于给定bundle的的migrations创建一个包含version的数组
  3. 创建一个包含所以应用到database的迁移版本(SELECT version FROM schema_migrations)
  4. 从列表中移除所以比origin version小的migration
  5. 比较迁移数组的差异。剩余的集合是(pending migrations)待迁移的集合。
  6. 对pending migrations进行升序排列,并对其从旧到新的应用进行数据库的迁移。

首先,确保你已经配置好了database.yml,假设我们要创建一个名为person的model,现在我们可以通过db:migrate任务来避免直接使用SQL来操作数据库:

rake db:schema:dump,这将在db目录下创建一个schema文件,你可以通过复制它的内容来为数据库中现有的表创建migration文件(如果你的应用不是一开始就使用db:migrate的话)
rake db:structure:dump,这将在db目录下创建一个structure文件,它包含创建当前数据库结构所需的SQL语句。

$ rake db:migrate version=0

create_table(name, options),创建表
drop_table,删除表
rename_table(old_name, new_name),重命名表
add_column(table_name, column_name, type, options),添加列
rename_column(table_name, column_name, new_column_name),重命名列
change_column(table_name, column_name, type, options),改变列属性
remove_column(table_SQLite彩民之家论坛9066777。name, column_name),删除列
add_index(table_name, column_name, index_type),创建索引
remove_index(table_name, column_name),删除索引
唯一需要注意的就是:必须在down方法中撤销up方法对数据库所作的一切更改,以保证我们所有的迁移都是可逆的。

class AddDegreeToPeople < ActiveRecord::Migration
def self.up
add_column :people, :degree, :string
end
def self.down
remove_column :people, :degree
end
end

事实上除了add_column,我们还可以对数据库进行更多的操作:

这条命令将在你的db/migrate目录下创建001_create_people.rb文件,我们可以通过它来创建people(people是person的复数)表:

$ rake db:migrate
== CreatePeople: migrating ===================
-- create_table
-> 0.1900s
== CreatePeople: migrated ==========

然后再次执行rake db:migrate,现在你会发现people表已经多了一个degree字段。

版权声明:本文由彩民之家高手论坛发布于编程技术,转载请注明出处:SQLite彩民之家论坛9066777