Hi Ralf
A long time ago, when I was a member of this list under a different email
address, I asked why a sqlite database I used could not be opened and worked
on with the personal version of Disqlite3 in Delphi 7 personal when a friend
could open the same file with the Pro version. SqliteSpy and every other
utility I have tried will open this file. I have even written simple
programs in Basic for Android and Basic4PPC which work with it quite
happily. My friend had had the same problem with the personal version of
Disqlite3, but he was a lot richer than me and could afford to go Pro :)
You asked me to send you the database, but at 90 MB or so that wasn't very
practical, and in the end my friend offered to compile my code on his PC.
Now I need to rewrite some Delphi programs, but my friend has passed on, so
I am returning to my question.
Could it be that Disqlite3 personal is limited to sqlite3 files with certain
extensions ? This one is a ".sqb". I can't just change the extension
because then it would not work with the commercial software and hardware it
gets its data from. I need to be able to interrogate it while it is active,
I can't just work on a renamed version, and then restore the original name.
Likewise I cannot change the schema if the changed version will not work
with the other software. My needs are entirely personal, my programs are
for my own use and not distributed in any way.
Now I know a bit more about sqlite, I have finally managed to produce a
cut-down version of the file which I can send if you wish, but for the time
being, the sql which creates it is below, as conveniently provided by
SqliteExpert.
I hope there is an easy fix to this problem, many thanks in advance if you
will be so kind as to look at it.
Michael Hooker
CREATE TABLE Aircraft(AircraftID integer primary key,FirstCreated datetime
not null,LastModified datetime not null,ModeS varchar(6) not null
unique,ModeSCountry varchar(24),Country varchar(24),Registration
varchar(20),CurrentRegDate varchar(10),PreviousID varchar(10),FirstRegDate
varchar(10),Status varchar(10),DeRegDate varchar(10),Manufacturer
varchar(60),ICAOTypeCode varchar(10),Type varchar(40),SerialNo
varchar(30),PopularName varchar(20),GenericName varchar(20),AircraftClass
varchar(20),Engines varchar(40),OwnershipStatus varchar(10),RegisteredOwners
varchar(100),MTOW varchar(10),TotalHours varchar(20),YearBuilt
varchar(4),CofACategory varchar(30),CofAExpiry varchar(10),UserNotes
varchar(300),Interested boolean not null default 0,UserTag
varchar(5),InfoURL varchar(150),PictureURL1 varchar(150),PictureURL2
varchar(150),PictureURL3 varchar(150),UserBool1 boolean not null default
0,UserBool2 boolean not null default 0,UserBool3 boolean not null default
0,UserBool4 boolean not null default 0,UserBool5 boolean not null default
0,UserString1 varchar(20),UserString2 varchar(20),UserString3
varchar(20),UserString4 varchar(20),UserString5 varchar(20),UserInt1 integer
default 0,UserInt2 integer default 0,UserInt3 integer default 0,UserInt4
integer default 0,UserInt5 integer default 0,OperatorFlagCode varchar(20));
CREATE INDEX AircraftAircraftClass ON Aircraft(AircraftClass);
CREATE INDEX AircraftCountry ON Aircraft(Country);
CREATE INDEX AircraftGenericName ON Aircraft(GenericName);
CREATE INDEX AircraftICAOTypeCode ON Aircraft(ICAOTypeCode);
CREATE INDEX AircraftInterested ON Aircraft(Interested);
CREATE INDEX AircraftManufacturer ON Aircraft(Manufacturer);
CREATE INDEX AircraftModeS ON Aircraft(ModeS);
CREATE INDEX AircraftModeSCountry ON Aircraft(ModeSCountry);
CREATE INDEX AircraftPopularName ON Aircraft(PopularName);
CREATE INDEX AircraftRegisteredOwners ON Aircraft(RegisteredOwners);
CREATE INDEX AircraftRegistration ON Aircraft(Registration);
CREATE INDEX AircraftSerialNo ON Aircraft(SerialNo);
CREATE INDEX AircraftType ON Aircraft(Type);
CREATE INDEX AircraftUserTag ON Aircraft(UserTag);
CREATE INDEX AircraftYearBuilt ON Aircraft(YearBuilt);
CREATE TRIGGER AircraftIDdeltrig BEFORE DELETE ON Aircraft FOR EACH ROW
BEGIN DELETE FROM Flights WHERE AircraftID = OLD.AircraftID;END;
CREATE TABLE DBHistory(DBHistoryID integer primary key,TimeStamp datetime
not null,Description varchar(100) not null);
CREATE TABLE DBInfo(OriginalVersion smallint not null,CurrentVersion
smallint not null);
CREATE TABLE Locations(LocationID integer primary key,LocationName
varchar(20) not null,Latitude real not null,Longitude real not null,Altitude
real not null);
CREATE INDEX LocationsLocationName ON Locations(LocationName);
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 INDEX SessionsEndTime ON Sessions(EndTime);
CREATE INDEX SessionsLocationID ON Sessions(LocationID);
CREATE INDEX SessionsStartTime ON Sessions(StartTime);
CREATE TRIGGER SessionIDdeltrig BEFORE DELETE ON Sessions FOR EACH ROW BEGIN
DELETE FROM Flights WHERE SessionID = OLD.SessionID;END;
CREATE TABLE Flights(FlightID integer primary key,SessionID integer not
null,AircraftID integer not null,StartTime datetime not null,EndTime
datetime,Callsign varchar(20),NumPosMsgRec integer,NumADSBMsgRec
integer,NumModeSMsgRec integer,NumIDMsgRec integer,NumSurPosMsgRec
integer,NumAirPosMsgRec integer,NumAirVelMsgRec integer,NumSurAltMsgRec
integer,NumSurIDMsgRec integer,NumAirToAirMsgRec integer,NumAirCallRepMsgRec
integer,FirstIsOnGround boolean not null default 0,LastIsOnGround boolean
not null default 0,FirstLat real,LastLat real,FirstLon real,LastLon
real,FirstGroundSpeed real,LastGroundSpeed real,FirstAltitude
integer,LastAltitude integer,FirstVerticalRate integer,LastVerticalRate
integer,FirstTrack real,LastTrack real,FirstSquawk integer,LastSquawk
integer,HadAlert boolean not null default 0,HadEmergency boolean not null
default 0,HadSPI boolean not null default 0,UserNotes
varchar(300),CONSTRAINT SessionIDfk FOREIGN KEY (SessionID) REFERENCES
Sessions,CONSTRAINT AircraftIDfk FOREIGN KEY (AircraftID) REFERENCES
Aircraft);
CREATE INDEX FlightsAircraftID ON Flights(AircraftID);
CREATE INDEX FlightsCallsign ON Flights(Callsign);
CREATE INDEX FlightsEndTime ON Flights(EndTime);
CREATE INDEX FlightsSessionID ON Flights(SessionID);
CREATE INDEX FlightsStartTime ON Flights(StartTime);
CREATE TABLE SystemEvents(SystemEventsID integer primary key,TimeStamp
datetime not null,App varchar(15) not null,Msg varchar(100) not null);
CREATE INDEX SystemEventsApp ON SystemEvents(App);
CREATE INDEX SystemEventsTimeStamp ON SystemEvents(TimeStamp);
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
