1. Audrius Kažukauskas
  2. worldip

Commits

Audrius Kažukauskas  committed cffcba6

Add shell script to create PostgreSQL database.

  • Participants
  • Parent commits 2335412
  • Branches default

Comments (0)

Files changed (7)

File README.rst

View file
 1. Download ``worldip.sql.zip`` (SQL-format DB) from
    http://www.wipmania.com/en/base/
 2. Run ``./wip2sqlite.sh``.  This will create ``worldip.db`` SQLite DB file.
+   Alternatively run ``./wip2pgsql.sh`` to create tables in PostgreSQL database
+   named ``worldip`` (the database must already exist before running this
+   script).
 3. Set up ``worldip.py`` to be served by mod_wsgi_ or uWSGI_ (or whatever you
    use for WSGI apps).  You may need to install the dependencies (listed in
-   ``worldip-requirements.txt`` pip requirements file) in virtualenv or
-   somewhere in ``PYTHONPATH``.
+   ``requirements.txt`` pip requirements file) in virtualenv or somewhere in
+   ``PYTHONPATH``.  By default it looks for SQLite DB, if you intend to use
+   PostgreSQL, change DB URI at the end of the file.
 
 .. _mod_wsgi: http://code.google.com/p/modwsgi/
 .. _uWSGI: http://projects.unbit.it/uwsgi/

File create_tables.sql

View file
 );
 
 CREATE TABLE IF NOT EXISTS worldip (
-    start_ip UNSIGNED INTEGER NOT NULL,
-    end_ip UNSIGNED INTEGER NOT NULL,
+    start_ip BIGINT NOT NULL,
+    end_ip BIGINT NOT NULL,
     code VARCHAR(2) NOT NULL,
     PRIMARY KEY (start_ip, end_ip),
     FOREIGN KEY (code) REFERENCES worldip_land (code)

File requirements.txt

View file
+SQLAlchemy
+IPy
+WebOb
+PyYAML

File wip2pgsql.sh

View file
+#!/bin/bash
+
+DB_NAME="worldip"
+IP_TABLE="worldip.sql"
+LAND_TABLE="worldip.lands.en.sql"
+
+set -eu
+
+# Extract only files that we need.
+unzip -o worldip.sql.zip $IP_TABLE $LAND_TABLE
+
+# Massage IP data for psql \copy command.
+sed -i \
+  -e '1,10d' \
+  -e '$d' \
+  -e "s/'//g" \
+  -e 's/^(//' \
+  -e 's/).$//' \
+  -e 's/, /,/g' \
+  $IP_TABLE
+
+# Massage country code data for psql \copy command.
+sed -i \
+  -e '1,9d' \
+  -e '$d' \
+  -e "s/'//g" \
+  -e 's/^(//' \
+  -e 's/).$//' \
+  -e 's/, /,/' \
+  $LAND_TABLE
+
+# Create tables.
+psql $DB_NAME < create_tables.sql
+
+# Populate tables with data.
+psql -c "\\copy worldip_land from $LAND_TABLE csv" $DB_NAME
+psql -c "\\copy worldip from $IP_TABLE csv" $DB_NAME
+
+# Cleanup.
+rm -f $IP_TABLE $LAND_TABLE

File wip2sqlite.sh

View file
-#!/bin/sh
-
-set -eu
+#!/bin/bash
 
 DB_FILE="worldip.db"
 IP_TABLE="worldip.sql"
 LAND_TABLE="worldip.lands.en.sql"
 
+set -eu
+
 # Extract only files that we need.
 unzip -o worldip.sql.zip $IP_TABLE $LAND_TABLE
 
 # Massage IP data for sqlite3 `.import' command.
-sed -i -e '1,10d' -e '$d' -e "s/'//g" -e 's/^(//' -e 's/).$//' $IP_TABLE
+sed -i \
+  -e '1,10d' \
+  -e '$d' \
+  -e "s/'//g" \
+  -e 's/^(//' \
+  -e 's/).$//' \
+  $IP_TABLE
 
 # Massage country code data for sqlite3 `.import' command.
-sed -i -e '1,9d' -e '$d' -e "s/'//g" -e 's/^(//' -e 's/).$//' $LAND_TABLE
+sed -i \
+  -e '1,9d' \
+  -e '$d' \
+  -e "s/'//g" \
+  -e 's/^(//' \
+  -e 's/).$//' \
+  $LAND_TABLE
 
 # Create tables.
 sqlite3 $DB_FILE < create_tables.sql

File worldip-requirements.txt

-SQLAlchemy
-IPy
-WebOb
-PyYAML

File worldip.py

View file
 # -*- coding: utf-8 -*-
 
 from sqlalchemy import create_engine
-from sqlalchemy import Table, Column, Integer, Unicode
+from sqlalchemy import Table, Column, BigInteger, Unicode
 from sqlalchemy import MetaData, ForeignKey
 from sqlalchemy.sql import select, and_
 
 metadata = MetaData()
 
 ip_ranges = Table('worldip', metadata,
-    Column('start_ip', Integer, primary_key=True),
-    Column('end_ip', Integer, primary_key=True),
+    Column('start_ip', BigInteger, primary_key=True),
+    Column('end_ip', BigInteger, primary_key=True),
     Column('code', Unicode(2), ForeignKey('worldip_land.code'))
 )