Hi
I am assuming that if one wants to change the database schema on an application that has already deployed, one must first
I assume most people write a stand-alone application to do that rather than build it in to the deployed app.
Am I on the right track?
Phil
I use a combination of external app & built in. I typically keep the last few schema changes in the app, but offload the older upgrades to an upgrade app for slow adopters.
I also ALWAYS have 2 copies of the database, the one I’m developing with and a test deployment version.
I have a CheckDBVersion method that runs every time my app starts. I store a DBVersion setting in the database. When my app starts it checks the DBVersion against the version the app expects. If the DBVerion is newer than expected a warning is displayed and the app closes. If the DB is older then schema changes are executed to update the database.
Here is my entire method. (PostgreSQL)
[code]Sub CheckMainDBVersion()
Dim ProgramDBVersion as Integer = 7
Dim DBVersion as Integer = SettingGlobal(“DBVersion”).IntegerValue
if ProgramDBVersion < DBVersion Then
MsgBoxT “This version of QBMS is not compatible with this version of the database.”
quit
end if
if DBVersion < 1 Then
MainDB.SQLExecute(“ALTER TABLE QBItems ADD COLUMN SubItem TEXT”)
end if
if DBVersion < 2 Then
'create trigger to notify server about orders to Import
MainDB.SQLExecute(" " _
+ "CREATE OR REPLACE FUNCTION notify_orderchanged() " _
+ "RETURNS trigger " _
+ "LANGUAGE plpgsql " _
+ "AS $function$ " _
+ "DECLARE " _
+ "BEGIN " _
+ "PERFORM pg_notify(CAST(‘server’ as text), ‘OrderChanges,’ || NEW.id::text || ‘,’ || NEW.savestatus::text ) " _
+ "WHERE NEW.savestatus = ‘1’ or NEW.savestatus = ‘2’ or NEW.savestatus = ‘3’ or NEW.savestatus = ‘9’; " _
+ "RETURN NEW; " _
+ “END;” _
+ "$function$; " _
+ “” _
+ "DROP TRIGGER IF EXISTS notify_orderchanged on orders ; " _
+ “CREATE TRIGGER notify_orderchanged AFTER INSERT OR UPDATE ON Orders FOR EACH ROW EXECUTE PROCEDURE notify_orderchanged();”)
MainDB.SQLExecute(“ALTER TABLE Panels ADD COLUMN ManualComplete BOOLEAN”)
MainDB.SQLExecute(“ALTER TABLE QBInventory ADD COLUMN AvgCost NUMERIC”)
end if
if DBVersion < 3 Then
MainDB.SQLExecute(“ALTER TABLE Customers ADD COLUMN Phone TEXT”)
MainDB.SQLExecute(“ALTER TABLE Orders ADD COLUMN Phone TEXT”)
MainDB.SQLExecute(“ALTER TABLE OrderLines ALTER COLUMN Quantity TYPE NUMERIC”)
end if
if DBVersion < 4 Then
MainDB.SQLExecute(“ALTER TABLE Orders ADD COLUMN ShipIdx INTEGER DEFAULT 0”)
end if
if DBVersion < 5 Then
'create table to store inventory assembly builds. Also create trigger to notify server about new build to import to QB.
MainDB.SQLExecute("" _
+ " CREATE TABLE inventorybuilds(ID SERIAL, PID INTEGER,ITEM TEXT, QTY NUMERIC, status TEXT DEFAUlT ‘new’, TS Timestamp default now()); " _
+ " CREATE OR REPLACE FUNCTION notify_newinventorybuild() " _
+ " RETURNS trigger " _
+ " LANGUAGE plpgsql AS $function$ " _
+ " DECLARE BEGIN PERFORM pg_notify(CAST(‘server’ as text), ‘NewBuild,’ || NEW.id::text) WHERE NEW.status = ‘new’ ; " _
+ " RETURN NEW; END;$function$; " _
+ " CREATE TRIGGER notify_newinventorybuild AFTER INSERT OR UPDATE ON inventorybuilds FOR EACH ROW EXECUTE PROCEDURE notify_newinventorybuild();")
end if
if DBVersion < 6 Then
MainDB.SQLExecute(“ALTER TABLE ProductionLog ALTER COLUMN TimeStamp TYPE TIMESTAMP;” _
+ “ALTER TABLE inventorybuilds ALTER COLUMN TS SET DEFAULT now();” _
+ “ALTER TABLE Orders ADD COLUMN BundleInfo TEXT;”)
end if
If DBVersion < 7 Then
'create trigger to notify server about new messages
MainDB.SQLExecute(" " _
+ "DROP TABLE IF EXISTS messages; "_
+ "CREATE TABLE messages(id SERIAL, user1 Text, user2 Text, status1 Integer, status2 Integer, msg Text, ts Timestamp Without Time Zone DEFAULT now(),PRIMARY KEY (id)); " _
+ "CREATE OR REPLACE FUNCTION notify_newmsg() " _
+ "RETURNS trigger " _
+ "LANGUAGE plpgsql AS $function$ " _
+ "DECLARE BEGIN PERFORM pg_notify(CAST(‘server’ as text), ‘NewMessage,’ || NEW.id::text) WHERE NEW.status1 = 3 OR NEW.status2 = 3; " _
+ "RETURN NEW; END;$function$; " _
+ “CREATE TRIGGER notify_newmsg AFTER INSERT OR UPDATE ON messages FOR EACH ROW EXECUTE PROCEDURE notify_newmsg();” _
+ “ALTER TABLE Log ALTER COLUMN Time TYPE TIMESTAMP;”)
End If
'update the version of the MainDB
SettingGlobal(“DBVersion”) = ProgramDBVersion
End Sub
[/code]
Thank you for sharing that. I have been considering something similar, but this code will make my though processes much clearer.
I was thinking of writing a stand alone app for the purpose, but I can see the benefit of an automatic update where data schema has been altered. I am still learning SQLite, but hope to move across to PostgreSQL once I am more comfortable with SQL in general.
Thanks again
At development time, I start by creating two methods:
a. Import data from TEXT file
b. Export data to TEXT file.
And if I make an error (Ive done plenty in the past week),
I can export the data (old schema),
delete the .sqlite file,
made changes to the schema (import / export)
recreate one with the correct schema and import from the TEXT file.
But even that have to be considered rude, boring, uneasy (even if on the paper it looks easy).
Thanks
Yes, I have a routine to export data from the .sqlite database built into the application. Using Dave S’s Tadpole application makes importing and exporting very simple. I am looking for ways to automate the process so that I can update remote stand-alone databases, and I think a stand-alone application with a menu item that runs it from the original application may be the way to go. Still working on it.
I have two ways:
In one project, I have a set of classes that can parse the (rather simple) SQL schema and creates objects describing tables and rows of an existing DB. Then I run code that defines which tables and rows I currently need, and then the code determines fairly automatically which changes have to be made to the DB. That code is based on something written years ago by Koingo Software.
Most times, though, I simple maintain a short text file (or an internal constant) into which I write commands that would change the schema as needed, such as
ALTER TABLE Files ADD COLUMN flags INTEGER DEFAULT 0;
ALTER TABLE Reports ADD COLUMN finishDate DATETIME DEFAULT NULL;
Then I simply execute each line whenever I open the DB, and ignore any errors.
This procedure doesn’t make it easy to update data in the tables, though. If I’d need that, I’d use a special table which contains flags or version numbers, and which I’d use to test in my SQL cmds to decide whether to update some rows.
Oh, and here’s a neat, free and simple schema designer tool, BTW: http://ondras.zarovi.cz/sql/demo/
To try it, click “Save / Load”, then click “Load” in the Server panel on the right, and enter “wenda” or any other name that you get listed when clicking “List items”.