0

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.

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.

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.

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Jdban101
  • 367
  • 1
  • 3
  • 21
  • You would have to use dynamic sql for this level of flexibility. The ALTER TABLE statement would be inside the dynamic sql. – Sean Lange Jul 12 '16 at 19:35
  • Have you seen [ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite](http://stackoverflow.com/questions/3604310/alter-table-add-column-if-not-exists-in-sqlite)? – Feneric Jul 12 '16 at 19:36
  • @Feneric, I saw that one, it doesn't offer a solution with just sql statements though. They're recommending versioning (which is what I'm trying to do) and saying that you can do it in python/etc. – Jdban101 Jul 12 '16 at 20:01
  • @SeanLange I'm googling dynamic sql sqlite right now, but not really seeing anything. Any more tips/a nudge in the right direction? – Jdban101 Jul 12 '16 at 20:02
  • This was originally tagged with sql server and that is what my comment was referencing. I have no clue if you can or need to do that with sqlite. – Sean Lange Jul 12 '16 at 20:08
  • @SeanLange Whoops, my bad – Jdban101 Jul 12 '16 at 20:11
  • Or mine for not reading the title. :) – Sean Lange Jul 12 '16 at 20:12
  • @Jdban101, it's not efficient, but to do it purely in SQL one could rename the table to something temporary, create the new table with all the desired columns, copy the contents from the old to the new, and drop the old. It'll be slow for lots of data, but it will be pure SQL and should always work regardless of whether or not the column exists (although note that it'll do lots of extra work if the column already exists). – Feneric Jul 13 '16 at 00:53
  • @Feneric - I think that still won't work because I would end up having to select the column from a table where I'm not sure it exists, which will error if the column doesn't exist. (Also I should point out, these tables are going to have at most around 15 rows, so time isn't a big deal). – Jdban101 Jul 13 '16 at 13:36
  • @Jdban101 I think I maybe didn't state it well (or maybe I'm missing something). There shouldn't be anything referencing that column except the new creation of the table. The operations wouldn't be conditional, the SELECT would operate on *. Again, it won't be efficient. – Feneric Jul 13 '16 at 13:44
  • @Feneric Ah, ok, i get what you mean. I tried it out to see if it would work, and it does not. "INSERT INTO tablename SELECT * FROM tablename" complains about the mismatch of one column being selected to be inserted into two columns. – Jdban101 Jul 13 '16 at 17:13
  • @Jdban101 Bummer; I was playing around with it myself and see that it only works when one specifies the columns in the INSERT. This means that it'll lose the new column values in tables that already have them, so it's not quite there. – Feneric Jul 13 '16 at 19:45

1 Answers1

2

There is no "IF NOT EXIST" clause for Alter Tables in SQLite, it doesn't 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.

Maybe one day, but not today.

MPelletier
  • 16,256
  • 15
  • 86
  • 137