自分メモ:BaseStation.sqb関連

sudo apt install sqlite

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  libsqlite0
Suggested packages:
  sqlite-doc
The following NEW packages will be installed:
  libsqlite0 sqlite
0 upgraded, 2 newly installed, 0 to remove and 46 not upgraded.
Need to get 155 kB of archives.
After this operation, 398 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://ftp.tsukuba.wide.ad.jp/Linux/raspbian/raspbian buster/main armhf libsqlite0 armhf 2.8.17-15 [135 kB]
Get:2 http://ftp.tsukuba.wide.ad.jp/Linux/raspbian/raspbian buster/main armhf sqlite armhf 2.8.17-15 [19.9 kB]
Fetched 155 kB in 3s (48.2 kB/s)
Selecting previously unselected package libsqlite0.
(Reading database ... 120152 files and directories currently installed.)
Preparing to unpack .../libsqlite0_2.8.17-15_armhf.deb ...
Unpacking libsqlite0 (2.8.17-15) ...
Selecting previously unselected package sqlite.
Preparing to unpack .../sqlite_2.8.17-15_armhf.deb ...
Unpacking sqlite (2.8.17-15) ...
Setting up libsqlite0 (2.8.17-15) ...
Setting up sqlite (2.8.17-15) ...
Processing triggers for libc-bin (2.28-10+rpi1) ...
Processing triggers for man-db (2.8.5-2) ...

sqlite3 BaseStation.sqb
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
Aircraft      DBInfo        Locations     SystemEvents
DBHistory     Flights       Sessions

sqlite> .schema Aircraft
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 AircraftModeS ON Aircraft(ModeS);
CREATE INDEX AircraftModeSCountry ON Aircraft(ModeSCountry);
CREATE INDEX AircraftCountry ON Aircraft(Country);
CREATE INDEX AircraftRegistration ON Aircraft(Registration);
CREATE INDEX AircraftManufacturer ON Aircraft(Manufacturer);
CREATE INDEX AircraftICAOTypeCode ON Aircraft(ICAOTypeCode);
CREATE INDEX AircraftType ON Aircraft(Type);
CREATE INDEX AircraftSerialNo ON Aircraft(SerialNo);
CREATE INDEX AircraftPopularName ON Aircraft(PopularName);
CREATE INDEX AircraftGenericName ON Aircraft(GenericName);
CREATE INDEX AircraftAircraftClass ON Aircraft(AircraftClass);
CREATE INDEX AircraftRegisteredOwners ON Aircraft(RegisteredOwners);
CREATE INDEX AircraftYearBuilt ON Aircraft(YearBuilt);
CREATE INDEX AircraftInterested ON Aircraft(Interested);
CREATE INDEX AircraftUserTag ON Aircraft(UserTag);
CREATE TRIGGER [AircraftIDdeltrig] BEFORE DELETE ON [Aircraft] FOR EACH ROW BEGIN DELETE FROM Flights WHERE AircraftID = OLD.AircraftID;END;

sqlite> .quit

sqlite> select * from Aircraft where modeS='8477C8';
117756|2021-02-09 02:02:51.15|2021-02-09 02:02:51.15|8477C8|Japan|JA|JA18AN||||A||BOEING|B737|737-700/737-700 BBJ/C-40/C-40 Clipper/Clipper/BBJ (737-700)|||||||All Nippon Airways|||||||0||||||0|0|0|0|0||||||0|0|0|0|0|B737
sqlite>

sqlite3 BaseStation.sqb < SQL.file