SqlLite.Net: Tables creation and schema changes strategies

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

SqlLite.Net: Tables creation and schema changes strategies

Andy KU7T
Hello,

I'm looking for best practices on when and where to create/initialize your tables when using SqlLite.Net as well as how to properly handle schema changes from one version to another.

It seems I could roll my own using large SQL files or use some library that can do the updates for me…
I have found some code on the internet but I do not seem to have the right references:


Core.Instance.dbConn = new SQLiteConnection (Core.Instance.DatabaseName, true);

Core.Instance.dbConn.CreateTable<tblSettings> ();

Where can I find the CreateTable, and is it re-entrant, and so it can run on every launch of my program?  Any help is appreciated.

Thanks
Andy

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

_______________________________________________
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: SqlLite.Net: Tables creation and schema changes strategies

Simon Slavin-3
On 8 Feb 2020, at 3:10pm, Andy KU7T <[hidden email]> wrote:

> I'm looking for best practices on when and where to create/initialize your tables when using SqlLite.Net as well as how to properly handle schema changes from one version to another.

I urge you not to use CreateTable, but instead to use SQL commands to make and convert tables.  That way anyone who knows SQL will understand what you're doing, and you can more easily transfer your code to something other than .net.

In terms of version control, SQLite has space to store a version number which is ideal for that:

<https://sqlite.org/pragma.html#pragma_user_version>

You can store whatever integer you like in there and recall it whenever you want.  Put a 1 in there for the first version of your program.  If you ever need to make schema changes, start using it to keep track of your schema versions.

If you would prefer not to use a SQLite-only facility, you might create a configuration table, with key and value rows, and have a row of that table indicate your schema version.
_______________________________________________
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: SqlLite.Net: Tables creation and schema changes strategies

Andy KU7T
Ok, your reasoning sounds good.

I am struggling to come up with a SQL statement that runs a few expressions conditionally by a book expression. Can you help me get going?

Something like:

IF isnull(version) THEN
-- create table, indexes etc.

ELSE
-- make incremental changes

END IF


I'm getting a syntax error 'near IF'

Thanks
Andy
Sent from my T-Mobile 4G LTE Device
Get Outlook for Android<https://aka.ms/ghei36>
________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Saturday, February 8, 2020 9:42:25 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

On 8 Feb 2020, at 3:10pm, Andy KU7T <[hidden email]> wrote:

> I'm looking for best practices on when and where to create/initialize your tables when using SqlLite.Net as well as how to properly handle schema changes from one version to another.

I urge you not to use CreateTable, but instead to use SQL commands to make and convert tables.  That way anyone who knows SQL will understand what you're doing, and you can more easily transfer your code to something other than .net.

In terms of version control, SQLite has space to store a version number which is ideal for that:

<https://sqlite.org/pragma.html#pragma_user_version>

You can store whatever integer you like in there and recall it whenever you want.  Put a 1 in there for the first version of your program.  If you ever need to make schema changes, start using it to keep track of your schema versions.

If you would prefer not to use a SQLite-only facility, you might create a configuration table, with key and value rows, and have a row of that table indicate your schema version.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: SqlLite.Net: Tables creation and schema changes strategies

Simon Slavin-3
On 8 Feb 2020, at 6:25pm, Andy KU7T <[hidden email]> wrote:

> IF isnull(version) THEN
> -- create table, indexes etc.
>
> ELSE
> -- make incremental changes
>
> END IF

You're going to have to give up the idea that you can do all this in one complicated SQL command.  You're going to end up doing a lot of

1) use your software to execute SQL command
2) use your software to check the result and decide what to do

For instance you can't do the enclosing if checking the version number.  You have to do that in your software.

There is a form of of CREATE TABLE which goes

CREATE TABLE IF NOT EXISTS …

which might help.  Also SQL does have an 'IF' construction, but can't be used to make different changes, it is used to return different values.  Look for "The CASE expression" on this page:

<https://sqlite.org/lang_expr.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users