0

I'm looking for an alternative to the IF-ELSE-Statement as known in MS-SQL for SQLite.

Try searching on stackoverflow first!

I did. I found something like that:

The problem in that cases is that they perform SELECT/INSERT/UPDATE/DELETE.

I want to alter the schema of an existing database.

Creating a new table is trivial because there is CREATE TABLE IF NOT EXISTS "foo" (....);.

But how about adding columns to an existing table?

I want to write a script like:

IF NOT EXISTS (SELECT * FROM pragma_table_info("<table_name>") WHERE name == "<column_name>")
BEGIN
    ALTER TABLE "<table_name>" ADD "<column_name>" TEXT;
END

This sounds like ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite. But this post is out of the year 2010 and I would expect that something has changed in the last 9 years.

I need to do some statements like that using arbitrary queries and statements.

Is there any way to do that in pure SQL or do I have to handle that in application code?

Sebastian Schumann
  • 3,204
  • 19
  • 37
  • You can version a database with the `user_version` [pragma](https://www.sqlite.org/pragma.html) and use that in a script to figure out what needs to be added to bring your schema up to the latest version. – Shawn Sep 18 '19 at 17:31
  • @Shawn Yeah I know about that solution. The problem is that we've plugins that have diffent needs to alter the schema. There is no way to determine whether a plugin should alter a table or not by a single integer called `user_version`. Currently I implemented a table that contains the schema version per plugin. The script has been split into multiple files that handle a single schema change. This seams to work. – Sebastian Schumann Sep 18 '19 at 17:43
  • The answer is "yes" to overall question "Is there any way...", but this is too broad and covers too many subtopics. There is a way to query the schema from the sqlite schema table. Adding columns is actually easy, but removing columns and changing column details requires a particular, complicated process. There are not stored procedures or scripts in sqlite, primarily because as an embedded (i.e. serverless) RDBMS, it is intended that you use the host language for flow control, branching, etc. Any one of those topics is quite involved and can be discovered online or in Stack Overflow. – C Perkins Sep 19 '19 at 01:49
  • @CPerkins Thx for this explanation. It makes sense that SQLite doesn't support things like stored procedures or scripts. If I think about it that way, I've to admit that the question has been disappeared. – Sebastian Schumann Sep 19 '19 at 04:37

0 Answers0