如何管理桌面文件数据库版本? [英] How to manage desktop file database versions?
问题描述
在升级桌面应用程序时如何管理数据库更改?
How to manage database changes while upgrading desktop applications?
我们有一个用于桌面应用程序的SQLite数据库.卸载程序将保留该数据库供下次安装使用.但是，如果下一次安装具有更新版本，该怎么办?如何保留数据但升级表?
We have a SQLite database for one of our desktop apps. The uninstaller keeps the database to be used by the next install. But what if the next install has an updated version? How to keep the data but upgrade the tables?
我们使用.NET和Linq2sql
We use .NET and Linq2sql
推荐答案
这是我的操作方式:
在我的代码中，我定义了数据库的版本
In my code I define the version of the database
#define DB_VERSION 2
每次更改破坏"数据库的代码(对数据库内容的模式或语义进行不兼容的更改)时，此版本号都会递增.
This version number is incremented every time I make a change to the code that 'breaks' the database ( makes an incompatible change to the schema or the semantics of the db contents )
当代码创建新数据库时，它将执行此SQL命令
When the code creates a new database, it executes this SQL command
QueryFormat(L"PRAGMA SCHEMA_VERSION = %d;",DB_VERSION);
请注意，必须在执行完所有CREATE TABLE命令之后，否则，sqlite会递增SCHEMA_VERSION.
Note that this must be AFTER all CREATE TABLE commands have been executed, otherwise sqlite increments SCHEMA_VERSION.
当代码打开现有数据库时，它要做的第一件事是
When the code opens an existing database, the first thing it does is
Query(L"PRAGMA schema_version;")
将从此查询返回的SCHEMA_VERSION与DB_VERSION进行比较.如果它们不匹配，则说明数据库是由其他软件版本创建的.接下来会发生什么取决于您所需的详细信息.通常:
The SCHEMA_VERSION that is returned from this query is compared with the DB_VERSION. If they do not match, then the database was created by a different software version. What happens next depends on the details of what you need. Typically:
-
数据库是由较新的软件创建的:通知用户并退出
database was created by a more recent software: inform user and exit
数据库由具有升级功能的较旧软件创建:提供用户选项以运行升级代码或重新初始化数据库
database was created by older software for which there is an upgrade: offer user option to run upgrade code, or re-initialize database
数据库是由较旧的软件创建的，无需升级:提供用户选项以重新初始化数据库或退出.
database was created by older software with no upgrade: offer user option to re-initialize database or exit.
有关升级代码如何工作的详细信息在很大程度上取决于您的需求.通常，打开旧数据库并打开一个新的空数据库.读取旧表，根据需要转换数据，然后写入新数据库.关闭数据库.删除旧的数据库.将新数据库重命名为标准数据库名称.打开新的数据库.
The details of how the upgrade code works depends very much on what you need. In general open the old database AND open a new empty database. Read the old tables, convert the data as required, and write to the new database. Close the dbs. Delete the old db. Rename the new db to the standard db name. Open the new db.
这篇关于如何管理桌面文件数据库版本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
