Best way to ALTER COLUMN ADD multiple tables if the columns don't already exist

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Best way to ALTER COLUMN ADD multiple tables if the columns don't already exist

Tommy Lane
Hi all,
        I'm still working on this journal app and ran across a need to
update our table schema, I want my entries to be _essentially_
immutable. My solution to this problem is a linked list type dependency
where each entry has a parent and a child, which corresponds to an
entry's past and future modifications.
.
        I do not want to have to replace all the journal files (sqlite
databases) with a completely new set of tables, I would like to update
them. I know there should be a way to progoramattically update the
table columns with SQL, but I'm not sure the most effective way to go
about doing it.

        Current Schema creation command:
        String createTableSQL = "CREATE TABLE IF NOT EXISTS entries(\n" +
                "id INTEGER PRIMARY KEY, entry_creation_date TEXT NOT NULL, \n" +
                "entry_last_update_date TEXT NOT NULL, entry_title TEXT, \n" +
                "entry_content TEXT, entry_data BLOB);";

        How  would I go about updating an existing table to also have a
parent integer and child integer column. How could I follow this
convention of creation/update verification for column modifications
on existing databases.

Thanks for any help!

-Tommy
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to ALTER COLUMN ADD multiple tables if the columns don't already exist

Jens Alfke-2


> On Apr 22, 2019, at 6:37 PM, Tommy Lane <[hidden email]> wrote:
>
> How  would I go about updating an existing table to also have a
> parent integer and child integer column.

Use `pragma user_version` to get/set a schema version number in your database. It defaults to zero.
Associate each schema change with a user_version value. So call this one `1`.

When you open a db, check its user_version against the version# of each supported schema, in order. If it’s one less, apply that schema change, increment the user_version, and check the next one. That way all applicable schema updates will be applied in order.

In the case of this specific change, you’d use an ALTER TABLE ADD COLUMN statement to add the `parent` and `child` columns. All rows will now have the default values of those columns; if that’s not appropriate, you’ll need to run some UPDATE statements to initialize them to the proper values.

(Nitpick: You don’t need both `parent` and `child`; they’re redundant. In a properly normalized schema, you’d only have one; you can go the other direction by querying. My quick 2¢ opinion is to keep `child`. That way you can find current versions of entries with `child is null`.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users