[yunqa.de] Re: DISQLITE3: Sqlite file opens with pro version, but not personal version

  • From: "Michael Hooker" <michael.hooker@xxxxxxxxxx>
  • To: <yunqa@xxxxxxxxxxxxx>
  • Date: Sat, 26 Sep 2015 00:33:17 +0100

Thank you Ralf.

I shall have to set up a separate test system to see if a modified .sqb file
works with the commercial software which created it and feeds it.

I can see the purpose of the triggers and am sure they are not needed in my
case. Without going into unnecessary detail, I only delete Sessions from
the sessions table after the Flights table has been purged of relevant
entries anyway, so there is no need to automatically delete them. The
constraints are obviously there for a reason but I think they can go without
any problem in my case too, The system records the location of the user
when data is received, but my system is not mobile and hasn't moved from my
desk since 2006, so there is only one item of location data to pick from.

I shall simply create a new database on a different computer, set up the
tables and indices using the sql I quoted earlier modified to omit the
trigger and constraints, and copy all the data from the old database to the
new using sql. Then both can be run for a while in parallel to see if there
is any problem. The system runs 24/7 collecting data, so downtime means
missing data and has to be kept as short as possible. Then if all is well
after testing, I rename the old database and put the new one in its place.

Thank you again.

Michael Hooker


-----Original Message-----
From: yunqa-bounce@xxxxxxxxxxxxx [mailto:yunqa-bounce@xxxxxxxxxxxxx] On
Behalf Of Delphi Inspiration
Sent: 25 September 2015 06:58
To: yunqa@xxxxxxxxxxxxx
Subject: [yunqa.de] Re: DISQLITE3: Sqlite file opens with pro version, but
not personal version

On 25.09.2015 00:06, Michael Hooker wrote:

Could it be that Disqlite3 personal is limited to sqlite3 files with
certain
extensions ?

No, database file name extensions do not matter to DISQLite3, neither
professional nor personal.

CREATE TABLE Sessions(SessionID integer primary key,LocationID integer not
null,StartTime datetime not null,EndTime datetime,CONSTRAINT LocationIDfk
FOREIGN KEY (LocationID) REFERENCES Locations);

CREATE TRIGGER SessionIDdeltrig BEFORE DELETE ON Sessions FOR EACH ROW
BEGIN
DELETE FROM Flights WHERE SessionID = OLD.SessionID;END;

The table above uses features unsupported by DISQLite3 Personal, in
particular table constraints and triggers. DISQLite3 supports those
features, look at the feature chart here:

http://www.yunqa.de/delphi/doku.php/products/sqlite3/feature_chart

Before you can open the database with DISQLite3 Personal, you must
remove constraints. SQLite does not have a dedicated command for this,
but you can create a new table without constraints, transfer the data,
delete the old table, and rename the new one. Here is an simple script
for the table above that you can run in SQLiteSpy:

CREATE TABLE Temp AS SELECT * FROM Sessions;
DROP TABLE Sessions;
ALTER TABLE Temp RENAME TO Sessions;

Notice that this will drop indexes, so queries might run slower unless
you recreate the indexes. Triggers are also dropped, but they do not
effect reading the database.

Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa



_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa



Other related posts:

  1. Home
  2. yunqa
  3. Archive
  4. 09-2015