Database schema update code?

Hello all.

I’ve been trying to write good database schema update code - but not go! I am using both SQLite and PostgreSQL (in different apps). Can anyone direct me to some good code to copy/sample to solve this problem?

I’d really appreciate any help with this!
Tim

Your question is way to generic. What is “no go” for you?

I have a couple of variations of this code:

'and now remove checksum field NotificationManager.Post "NextMajor", MakeDictionary("StatusText":kRemoveDuplicates, "Type": "Other") if ValentinaDB.SchemaVersion = 14 then RemoveCheckSum ValentinaDB.SchemaVersion = 15 end if

and so on for the other updates. Not really good OO but as I touch this only every couple of months at best it’s not worth refactoring.

Hi Beatrix.

Yes, I am sorry it is way generic, because I think my whole way of looking at this is wrong. For example, should the SchemaVersion, as you refer to it, be for every change (global scale) or micro scale for an adder to a particular table? The table(s) are where I have a problem.

What is best way to update a particular table? How best to test if the table has changed (programatically) then effect the change necessary?

To show how bad mine is, I actually update manually - individually change the table(s) for each installed user - which of course is a horrible way to go and not scalable by any stretch of the imagination!

So maybe I am looking for best practices; sample code to do all of that; and how best to integrate to Kem’s Kaju updater.

Basically a newbie in this regard. Embarrassed and sorry to say…
Tim

@Tim Seyfarth this is not a simple answer. different databases have different abilities to modify the schema of a table. there is not “one set of rules”.

what I have done in the past is when I need to change the schema on the fly, I would create a new temp table with the new schema, copy all the data from the old table to new table. then delete the old table. create the new table with the old table name, copy the data back over to it. it is a Pain In The *** but for some database engines that is the best way to go. other engines I could possibly do an alter table to change the specific columns that dont match up.

good luck!
–sb

We all have been starting at some time. I think for starting out my approach should be fine. Valentina has a global property called schemaversion. I have a corresponding global property in my application.

Then I have a method that does a comparison of both properties. The updates are done one by one so that users can skip versions:

[code] if ValentinaDB.SchemaVersion = 10 then
ChangeIndexes
ValentinaDB.SchemaVersion = 11
end if

if ValentinaDB.SchemaVersion = 11 then
ConvertToVarchar
ValentinaDB.SchemaVersion = 12
end if

if ValentinaDB.SchemaVersion = 12 then
CorrectVarChar
ValentinaDB.SchemaVersion = 13
end if[/code]

up to the current schemaversion.

in all databases you have the ability to add a table and add a field
a “simple” way is to never delete a table or a field, only add new one.
then just compare a schema when you open the database to the current one
and add the tables and fields that are not in the customer database.
update the schemanumber when there is a big change, or a need to run a dedicated update method.

@Jean-Yves Pochez adding is easy. how about changes to a column name or removing a column? or something even more complicated as changing a field type? i.e. changing from int32 to int64.

generally I save a “version” in the db itself
and then iterate over a list of sql DDL to get to the current version
this can then include create table alter tabe and any other sql required to move from one revision to the next

while someone could subvert this process by altering the value in the table I’ve never run into that

leave the original bad column in place, make a new field mycolumn_64 and use this one in the app update.
copy the datas from the old column to the new one.
easy, not applicable to any situation …
in general big databases are network based, so accessible from the database architect.
for sqlite customer databases, generally this “always add column” is enough for most cases.
and easy to implement.

[quote=432179:@Beatrix Willius]We all have been starting at some time. I think for starting out my approach should be fine. Valentina has a global property called schemaversion. I have a corresponding global property in my application.

Then I have a method that does a comparison of both properties. The updates are done one by one so that users can skip versions:

[code] if ValentinaDB.SchemaVersion = 10 then
ChangeIndexes
ValentinaDB.SchemaVersion = 11
end if

if ValentinaDB.SchemaVersion = 11 then
ConvertToVarchar
ValentinaDB.SchemaVersion = 12
end if

if ValentinaDB.SchemaVersion = 12 then
CorrectVarChar
ValentinaDB.SchemaVersion = 13
end if[/code]

up to the current schemaversion.[/quote]

This is the best option. In SQLite you can use the PRAGMA user_version

Thanks everyone.
So it seems like I have to keep track of every change, check the versioning, then write updates depending on how many versions have been skipped to get to the current one.

Thats what I have been doing, but it has not worked out well. Guess I need to examine where I am falling apart!
Tim

I use a loop something like

Do Select Case Schemaversion ' however you get it Case 0 // Some DDL/SQL execute statements Case 1 // Some more DDL/SQL Case 2 // Etc Case 3 Exit Do Case Else // Raise an exception due to the schema being newer than this application End Select // Update the schemaversion (if necessary) Loop

If I need to update the schema again I add the statements before Exit Do and add new case statement to exit the loop in the new version.

I also prefer to add a table to the db to hold my schema version which makes the code engine agnostic.

Thanks Wayne.
Tim

also with sqlite, it’s quite delicate to change or remove a column, or change it’s type.
there are no built-in commands for that.
also there are a very few column types in sqlite, int32 or int64 doesnt exist, only integer.
this conforts me in only adding tables or columns in sqlite at least.

For the record… SQLite technically does not have ANY datatyypes, but it has what are known as “affinitys”…
You can infact define a SQLite filed as Integer, and yet store a String … It is only Xojo the will coerce the field contents into a specific datatype.

You could look at something like flyway to handle this kind of thing.

[quote=432313:@Jean-Yves Pochez]also with sqlite, it’s quite delicate to change or remove a column or change it’s type.[/quote] To remove or edit colums, yes, yoou have to create another table and transfer the data.

[quote=432313:@Jean-Yves Pochez]
also there are a very few column types in sqlite, int32 or int64 doesnt exist, only integer.[/quote]

SQLite HAS something like data types and can store int32 and int64, even more eficient than other DB

Yes, the definition of the column is an “affinity” that tells the engine what data type to use (as a sugestion).

That is not the case, if you define the afinity as integer and store something that can be parsed as integer, it will be stored as an integer. It will only be converted to Text if is biger than a 8 bytes int.

https://www.sqlite.org/datatype3.html