IP Nation Example

Tables:

• L_IPNation - Lookup table for IP Address.
• L_Nation - Nation (ISO 3166, 2 Character Country Code).
• L_NationCity - List of Cities that is link to the Nation Table.
• L_Region - Group Nation by Internet Regions.

Database Schema:

• CREATE TABLE L_IpNation (IPFm LONG NOT NULL,IPTo LONG NOT NULL, NationCode TEXT (2) NOT NULL, IPUid INTEGER PRIMARY KEY);
• CREATE INDEX Index_FmTo ON  L_IpNation (IPFm ,IPTo,NationCode);
• CREATE TABLE IpNation_Seq (id INTEGER UNSIGNED PRIMARY KEY);
• CREATE TRIGGER IpNation_Seq_cleanup AFTER INSERT ON IpNation_Seq BEGIN DELETE FROM IpNation_seq WHERE id[_lessthan_]LAST_INSERT_ROWID(); END;
• CREATE TABLE L_Nation (NationCode VARCHAR (2) PRIMARY KEY, NationName TEXT (100) NOT NULL, RegionCode TEXT (10) NOT NULL);
• CREATE INDEX Index_NationName ON  L_Nation (NationName);
• CREATE TABLE L_NationCity ( NationCode TEXT (2) NOT NULL, CityName TEXT (50)  NOT NULL, CityGmt INTEGER, CityLatitude INTEGER, Citylongitude INTEGER, CityIsCaptial INTEGER NOT NULL, PRIMARY KEY (NationCode,CityName));
• CREATE TABLE L_Region (RegionCode TEXT (10) PRIMARY KEY, RegionName TEXT (100) NOT NULL, RegionUrl TEXT (255) NOT NULL);

Copy & Paste Tips:

Step 1:  Highlight the code and then right click and select Copy.

Step 2:  Start the SQLITE program.  Paste the above code into directly into  sqlite> prompt.  By click the left top icon of the command prompt and select Edit menu and choose Paste.