Tables
No. | Schema Table Name | Primary Key | Columns | Keys | Indices | Triggers | RowID |
---|---|---|---|---|---|---|---|
1 | cars | ID | 3 | Yes | |||
2 | Numbers | ID | 5 | 1 | 1 | Yes | |
3 | countrylist | SortOrder | 14 | 2 | Yes | ||
4 | cities | City_Key | 3 | 1 | 1 | Yes |
No. | Schema Table Name | Primary Key | Columns | Keys | Indices | Triggers | RowID |
---|---|---|---|---|---|---|---|
1 | cars | ID | 3 | Yes | |||
2 | Numbers | ID | 5 | 1 | 1 | Yes | |
3 | countrylist | SortOrder | 14 | 2 | Yes | ||
4 | cities | City_Key | 3 | 1 | 1 | Yes |
No. | Schema View Name | Kind | Columns | References | Comment |
---|---|---|---|---|---|
1 | Cities_of_the_World | Persist | 2 | 1 | World Capital Cities |
2 | Independent_Countries | Persist | 7 | 1 | Lists only the Independent countries as of Jan-2015. |
Property Name | Property Value | |
---|---|---|
Application ID | : | 00000000 |
Incremental-Vacuum | : | - |
Page Size | : | 1 024 |
Pages in DB | : | 81 |
File Size | : | 82 944 |
Default Page Cache | : | 0 |
SQLite Version | : | 3 008 008 |
User version | : | 0 |
Schema Format | : | 4 |
Database Encoding | : | UTF-8 |
Database Pragma | Pragma Value | |
---|---|---|
application_id | : | 0 |
auto_vacuum | : | 0 |
automatic_index | : | 1 |
compile_options | : | ENABLE_COLUMN_METADATA,ENABLE_FTS3,ENABLE_RTREE,SYSTEM_MALLOC,THREADSAFE=1 |
collation_list | : | 0,1,2 |
defer_foreign_keys | : | 0 |
encoding | : | UTF-8 |
foreign_keys | : | 1 |
freelist_count | : | 9 |
journal_mode | : | delete |
journal_size_limit | : | -1 |
max_page_count | : | 1073741823 |
page_count | : | 81 |
page_size | : | 1024 |
read_uncommitted | : | 0 |
recursive_triggers | : | 0 |
schema_version | : | 112 |
secure_delete | : | 0 |
user_version | : | 0 |
Primary Key | Columns | Keys | Indices | Triggers | Row_ID |
---|---|---|---|---|---|
ID | 3 | Yes |
No. | Column/Field Name | Type | Collation | Column Constraint | Default | Object References | Comment |
---|---|---|---|---|---|---|---|
1 | ID | INTEGER | Primary Key | Manufacturer DB ID | |||
2 | Make | VARCHAR(32) | NOCASE | Unique Not NULL | Manufacturer | ||
3 | URL | VARCHAR(255) | http://www. | Company web-url |
CREATE TABLE "cars" ( "ID" INTEGER PRIMARY KEY /* Manufacturer DB ID */, "Make" VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE /* Manufacturer */, "URL" VARCHAR(255) DEFAULT 'http://www.' /* Company web-url */ ); |
Primary Key | Columns | Keys | Indices | Triggers | Row_ID |
---|---|---|---|---|---|
ID | 5 | 1 | 1 | Yes |
No. | Column/Field Name | Type | Collation | Column Constraint | Default | Object References | Comment |
---|---|---|---|---|---|---|---|
1 | ID | INTEGER | Primary Key | Used in Trigger - Trig_Numbers_Int1 | Number | ||
2 | Str1 | VARCHAR(32) | NOCASE | Unique Not NULL | Used in Index - Idx_Numbers_Str1 | Text Representation | |
3 | Int1 | TINYINT | BINARY | 0 | Used in Trigger - Trig_Numbers_Int1 | Value | |
4 | Sqr1 | INTEGER | BINARY | 0 | Used in Trigger - Trig_Numbers_Int1 | Value Squared | |
5 | Comm | TEXT | NULL | Odd/Even |
No. | Type | Name | Columns | Comment |
---|---|---|---|---|
1 | Index | Idx_Numbers_Str1 | Str1 | Number Name Idx |
No. | Name | Event Type | Trigger Program | Comment |
---|---|---|---|---|
1 | Trig_Numbers_Int1 | After Update | UPDATE Numbers SET `Sqr1` = (`Int1`*`Int1`) WHERE ID = NEW.ID; | Auto-Set Values |
CREATE TABLE "Numbers" ( "ID" INTEGER PRIMARY KEY /* Number */, "Str1" VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE /* Text Representation */, "Int1" TINYINT DEFAULT 0 COLLATE BINARY /* Value */, "Sqr1" INTEGER DEFAULT 0 COLLATE BINARY /* Value Squared */, "Comm" TEXT DEFAULT NULL /* Odd/Even */ ); DROP INDEX IF EXISTS Idx_Numbers_Str1; CREATE INDEX Idx_Numbers_Str1 /* Number Name Idx */ ON Numbers (Str1 COLLATE NOCASE); DROP TRIGGER IF EXISTS Trig_Numbers_Int1; CREATE TRIGGER IF NOT EXISTS Trig_Numbers_Int1 AFTER UPDATE OF Int1 /* Auto-Set Values */ ON Numbers FOR EACH ROW BEGIN UPDATE Numbers SET `Sqr1` = (`Int1`*`Int1`) WHERE ID = NEW.ID; END; |
Primary Key | Columns | Keys | Indices | Triggers | Row_ID |
---|---|---|---|---|---|
SortOrder | 14 | 2 | Yes |
No. | Column/Field Name | Type | Collation | Column Constraint | Default | Object References | Comment |
---|---|---|---|---|---|---|---|
1 | SortOrder | INTEGER | Primary Key | ID and Order | |||
2 | CommonName | VARCHAR(128) | NOCASE | Not NULL | Used in Unique Index - Idx_countrylist_CommonName | Name known by | |
3 | FormalName | VARCHAR(128) | Proper / Legal | ||||
4 | Type | VARCHAR(64) | NOCASE | Governing Structure | |||
5 | SubType | VARCHAR(64) | NOCASE | ||||
6 | Sovereignty | VARCHAR(64) | NOCASE | Attached to | |||
7 | Capital | VARCHAR(128) | NOCASE | City | |||
8 | Currency_Code | VARCHAR(3) | NOCASE | ISO4217CurrencyCode | |||
9 | Currency | VARCHAR(32) | NOCASE | ISO4217CurrencyName | |||
10 | Dial_Code | VARCHAR(8) | ITUTTelephoneCode | ||||
11 | ISO_2Char_Code | VARCHAR(2) | NOCASE | ISO316612LetterCode | |||
12 | ISO_3Char_Code | VARCHAR(3) | NOCASE | Referenced in Index - Idx_countrylist_ISO_Code | ISO316613LetterCode | ||
13 | Country_Number | INTEGER | ISO31661Number | ||||
14 | IANA_Code | VARCHAR(4) | NOCASE | IANACountryCodeTLD |
No. | Type | Name | Columns | Comment |
---|---|---|---|---|
1 | Index | Idx_countrylist_ISO_Code | ISO_3Char_Code | Resolving Code-base Searches - Unique when available |
2 | Unique Index | Idx_countrylist_CommonName | CommonName | Resolving Name-base Searches |
CREATE TABLE "countrylist" ( "SortOrder" INTEGER PRIMARY KEY /* ID and Order */, "CommonName" VARCHAR(128) NOT NULL COLLATE NOCASE /* Name known by */, "FormalName" VARCHAR(128) /* Proper / Legal */, "Type" VARCHAR(64) COLLATE NOCASE /* Governing Structure */, "SubType" VARCHAR(64) COLLATE NOCASE, "Sovereignty" VARCHAR(64) COLLATE NOCASE /* Attached to */, "Capital" VARCHAR(128) COLLATE NOCASE /* City */, "Currency_Code" VARCHAR(3) COLLATE NOCASE /* ISO4217CurrencyCode */, "Currency" VARCHAR(32) COLLATE NOCASE /* ISO4217CurrencyName */, "Dial_Code" VARCHAR(8) /* ITUTTelephoneCode */, "ISO_2Char_Code" VARCHAR(2) COLLATE NOCASE /* ISO316612LetterCode */, "ISO_3Char_Code" VARCHAR(3) COLLATE NOCASE /* ISO316613LetterCode */, "Country_Number" INTEGER /* ISO31661Number */, "IANA_Code" VARCHAR(4) COLLATE NOCASE /* IANACountryCodeTLD */ ); DROP INDEX IF EXISTS Idx_countrylist_ISO_Code; CREATE INDEX Idx_countrylist_ISO_Code /* Resolving Code-base Searches - Unique when available */ ON countrylist (ISO_3Char_Code COLLATE NOCASE) WHERE ISO_3Char_Code<>''; DROP INDEX IF EXISTS Idx_countrylist_CommonName; CREATE UNIQUE INDEX Idx_countrylist_CommonName /* Resolving Name-base Searches */ ON countrylist (CommonName COLLATE NOCASE); |
Primary Key | Columns | Keys | Indices | Triggers | Row_ID |
---|---|---|---|---|---|
City_Key | 3 | 1 | 1 | Yes |
No. | Column/Field Name | Type | Collation | Column Constraint | Default | Object References | Comment |
---|---|---|---|---|---|---|---|
1 | City_Key | INTEGER | Primary Key | ||||
2 | City | TEXT | Used in Index - Idx_cities_City | Official City-name | |||
3 | CountryName | TEXT | NOCASE | Not NULL | Used in Foreign Key constraint | Country CommonName |
No. | Name | Description | Comment |
---|---|---|---|
1 | Foreign Key on (CountryName) referencing table "countrylist" | Country-Name link |
No. | Type | Name | Columns | Comment |
---|---|---|---|---|
1 | Index | Idx_cities_City | City | Use for City-name searches |
CREATE TABLE "cities" ( "City_Key" INTEGER PRIMARY KEY, "City" TEXT /* Official City-name */, "CountryName" TEXT NOT NULL COLLATE NOCASE /* Country CommonName */, FOREIGN KEY (CountryName) REFERENCES "countrylist"(CommonName) ON DELETE SET NULL ON UPDATE CASCADE /* Country-Name link */ ); DROP INDEX IF EXISTS Idx_cities_City; CREATE INDEX Idx_cities_City /* Use for City-name searches */ ON cities (City COLLATE NOCASE); |
Kind | Columns | References | Comment |
---|---|---|---|
Persist | 2 | countrylist | World Capital Cities |
No. | Column/Field Name/Definition | Inferred Type |
---|---|---|
1 | City | TEXT |
2 | Capital of | TEXT |
CREATE VIEW Cities_of_the_World /* World Capital Cities */ AS SELECT Capital AS 'City', CommonName AS 'Capital of' FROM countrylist WHERE City<>'' ORDER BY Capital |
Kind | Columns | References | Comment |
---|---|---|---|
Persist | 7 | countrylist | Lists only the Independent countries as of Jan-2015. |
No. | Column/Field Name/Definition | Inferred Type |
---|---|---|
1 | SortOrder | INTEGER |
2 | Code | TEXT |
3 | Name | TEXT |
4 | FormalName | TEXT |
5 | Capital | TEXT |
6 | Currency | TEXT |
7 | Dial_Code | TEXT |
CREATE VIEW Independent_Countries /* Lists only the Independent countries as of Jan-2015. */ AS SELECT SortOrder, ISO_3Char_Code AS 'Code', CommonName AS 'Name', FormalName, Capital, Currency, Dial_Code FROM countrylist WHERE Type LIKE 'Independent%' |