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.

Updated On: 15.02.13