I am adding versioning to my database a bit later than I should, and as such I have some tables with inconsistent states. I have a table that a column was added to in Java, but not all tables are guaranteed to have that column at this point.
我将版本控制添加到我的数据库中比我应该晚一点,因此我有一些状态不一致的表。我有一个表在Java中添加了一个列,但并不是所有表都保证在此时具有该列。
What I had been doing is on the first run of the program, checking if the column existed, and adding it if it did not exist.
我一直在做的是在程序的第一次运行,检查列是否存在,如果它不存在则添加它。
The library (flyway.org) I am using to deal with versioning takes in a bunch of .sql files in order to set up the database. For many tables, this is simple, I just have an sql file that has "CREATE TABLE IF NOT EXISTS XXX," which means it is easily handled, those can still be run.
我用来处理版本控制的库(flyway.org)需要一堆.sql文件才能设置数据库。对于许多表,这很简单,我只有一个sql文件,其中包含“CREATE TABLE IF NOT EXISTS XXX”,这意味着它很容易处理,仍然可以运行。
I am wondering if there is some way to handle these alter tables without SQLite generating an error that I haven't thought of, or if I haven't found out how to do it.
我想知道是否有一些方法来处理这些alter table而没有SQLite生成我没有想到的错误,或者如果我还没有找到如何做到这一点。
I've tried looking to see if there is a command to add a column if it doesn't exist, but there doesn't seem to be one. I've tried to find a way to handle errors in sqlite, for example running the alter table anyways, and just ignoring the error, but there doesn't seem to be a way of doing that (as far as I can tell). Does anyone have any suggestions? I want a solution 100% in a .sql script if possible.
我试过看是否有一个命令添加一个列,如果它不存在,但似乎没有一个。我试图找到一种方法来处理sqlite中的错误,例如无论如何运行alter table,只是忽略错误,但似乎没有办法做到这一点(据我所知)。有没有人有什么建议?如果可能的话,我想在.sql脚本中100%获得解决方案。
1 个解决方案
#1
2
There is no "IF NOT EXIST" clause for Alter Tables in SQLite, it doesn't exist.
SQLite中的Alter Tables没有“IF NOT EXIST”子句,它不存在。
There is a way to interrogate the database on what columns a table contains with PRAGMA table_info(table_name);. But there is no 100% SQL way to take that information and apply it to an Alter Table statement.
有一种方法可以使用PRAGMA table_info(table_name);询问数据库中表包含哪些列。但是没有100%的SQL方法来获取该信息并将其应用于Alter Table语句。
Maybe one day, but not today.
也许有一天,但不是今天。
#1
2
There is no "IF NOT EXIST" clause for Alter Tables in SQLite, it doesn't exist.
SQLite中的Alter Tables没有“IF NOT EXIST”子句,它不存在。
There is a way to interrogate the database on what columns a table contains with PRAGMA table_info(table_name);. But there is no 100% SQL way to take that information and apply it to an Alter Table statement.
有一种方法可以使用PRAGMA table_info(table_name);询问数据库中表包含哪些列。但是没有100%的SQL方法来获取该信息并将其应用于Alter Table语句。
Maybe one day, but not today.
也许有一天,但不是今天。