<commit_before>import os
import shutil
import sqlite3

import tempfile
import urlparse
import urllib2
import subprocess

import logging

import argparse

import csv
import sys

this_dir = os.path.realpath(os.path.dirname(__file__))
sys.path.append(os.path.realpath(os.path.join(os.pardir, os.pardir)))

from geodata.encoding import safe_decode
from geodata.geonames.paths import *

from geodata.file_utils import *
from geodata.log import *

from itertools import islice, chain

log_to_file(sys.stderr)
logger = logging.getLogger('geonames.sqlite')

BASE_URL = 'http://download.geonames.org/export/'

DUMP_URL = urlparse.urljoin(BASE_URL, 'dump/')
ALL_COUNTRIES_ZIP_FILE = 'allCountries.zip'
HIERARCHY_ZIP_FILE = 'hierarchy.zip'
ALTERNATE_NAMES_ZIP_FILE = 'alternateNames.zip'

ZIP_URL = urlparse.urljoin(BASE_URL, 'zip/')

GEONAMES_DUMP_FILES = (ALL_COUNTRIES_ZIP_FILE,
                       HIERARCHY_ZIP_FILE,
                       ALTERNATE_NAMES_ZIP_FILE)

# base_url, local_dir, is_gzipped, local_filename


GEONAMES_FILES = [(DUMP_URL, '', True, ALL_COUNTRIES_ZIP_FILE),
                  (DUMP_URL, '', True, HIERARCHY_ZIP_FILE),
                  (DUMP_URL, '', True, ALTERNATE_NAMES_ZIP_FILE),
                  (ZIP_URL, 'zip', True, ALL_COUNTRIES_ZIP_FILE),
                  ]


def download_file(url, dest):
    logger.info('Downloading file from {}'.format(url))
    subprocess.check_call(['wget', url, '-O', dest])


def admin_ddl(admin_level):
    columns = ['country_code TEXT'] + \
              ['admin{}_code TEXT'.format(i)
               for i in xrange(1, admin_level)]

    create = '''
    CREATE TABLE admin{level}_codes (
    geonames_id INT,
    code TEXT,
    name TEXT,
    {fields}
    )'''.format(level=admin_level,
                fields=''',
    '''.join(columns))

    indices = (
        '''CREATE INDEX admin{}_code_index ON
        admin{}_codes (code)'''.format(admin_level, admin_level),
        '''CREATE INDEX admin{}_gn_id_index ON
        admin{}_codes (geonames_id)'''.format(admin_level, admin_level),
    )

    return (create, ) + indices

geonames_ddl = {
    'geonames': (
        '''CREATE TABLE geonames (
        geonames_id INT PRIMARY KEY,
        name TEXT,
        ascii_name TEXT,
        alternate_names TEXT,
        latitude DOUBLE,
        longitude DOUBLE,
        feature_class TEXT,
        feature_code TEXT,
        country_code TEXT,
        cc2 TEXT,
        admin1_code TEXT,
        admin2_code TEXT,
        admin3_code TEXT,
        admin4_code TEXT,
        population LONG DEFAULT 0,
        elevation INT,
        dem INT,
        timezone TEXT,
        modification_date TEXT)''',
        '''CREATE INDEX feature_code ON
        geonames (feature_code)''',
        '''CREATE INDEX country_code ON
        geonames (country_code)''',
        '''CREATE INDEX admin1_code ON
        geonames (admin1_code)''',
        '''CREATE INDEX admin2_code ON
        geonames (admin2_code)''',
        '''CREATE INDEX admin3_code ON
        geonames (admin3_code)''',
        '''CREATE INDEX admin4_code ON
        geonames (admin4_code)''',
    ),

    'alternate_names': (
        '''CREATE TABLE alternate_names (
        alternate_name_id INT PRIMARY KEY,
        geonames_id INT,
        iso_language TEXT,
        alternate_name TEXT,
        is_preferred_name BOOLEAN DEFAULT 0,
        is_short_name BOOLEAN DEFAULT 0,
        is_colloquial BOOLEAN DEFAULT 0,
        is_historic BOOLEAN DEFAULT 0)''',
        '''CREATE INDEX geonames_id_index ON
        alternate_names (geonames_id)''',
        '''CREATE INDEX geonames_id_alt_name_index ON
        alternate_names(geonames_id, alternate_name)''',
    ),

    'hierarchy': (
        '''CREATE TABLE hierarchy (
        parent_id INT,
        child_id INT,
        type TEXT
        );''',
        '''CREATE INDEX parent_child_index ON
        hierarchy (parent_id, child_id)''',
        '''CREATE INDEX child_parent_index ON
        hierarchy (child_id, parent_id)''',
    ),

    'postal_codes': (
        '''CREATE TABLE postal_codes (
        country_code TEXT,
        postal_code TEXT,
        place_name TEXT,
        admin1 TEXT,
        admin1_code TEXT,
        admin2 TEXT,
        admin2_code TEXT,
        admin3 TEXT,
        admin3_code TEXT,
        latitude DOUBLE,
        longitude DOUBLE,
        accuracy INT
        )''',
        '''CREATE INDEX post_code_index ON
        postal_codes (country_code, postal_code)''',
    ),
    'admin1_codes': admin_ddl(1),
    'admin2_codes': admin_ddl(2),
    'admin3_codes': admin_ddl(3),
    'admin4_codes': admin_ddl(4),

}

geonames_file_table_map = {
    ('', ALL_COUNTRIES_ZIP_FILE): 'geonames',
    ('', ALTERNATE_NAMES_ZIP_FILE): 'alternate_names',
    ('', HIERARCHY_ZIP_FILE): 'hierarchy',
    ('zip', ALL_COUNTRIES_ZIP_FILE): 'postal_codes',
}


country_codes_create_table = (
    'drop table if exists country_codes',
    '''
    create table country_codes as<commit_msg>[geonames] admin codes on geonames/postal_codes tables<commit_after>import os
import shutil
import sqlite3

import tempfile
import urlparse
import urllib2
import subprocess

import logging

import argparse

import csv
import sys

this_dir = os.path.realpath(os.path.dirname(__file__))
sys.path.append(os.path.realpath(os.path.join(os.pardir, os.pardir)))

from geodata.encoding import safe_decode
from geodata.geonames.paths import *

from geodata.file_utils import *
from geodata.log import *

from itertools import islice, chain

log_to_file(sys.stderr)
logger = logging.getLogger('geonames.sqlite')

BASE_URL = 'http://download.geonames.org/export/'

DUMP_URL = urlparse.urljoin(BASE_URL, 'dump/')
ALL_COUNTRIES_ZIP_FILE = 'allCountries.zip'
HIERARCHY_ZIP_FILE = 'hierarchy.zip'
ALTERNATE_NAMES_ZIP_FILE = 'alternateNames.zip'

ZIP_URL = urlparse.urljoin(BASE_URL, 'zip/')

GEONAMES_DUMP_FILES = (ALL_COUNTRIES_ZIP_FILE,
                       HIERARCHY_ZIP_FILE,
                       ALTERNATE_NAMES_ZIP_FILE)

# base_url, local_dir, is_gzipped, local_filename


GEONAMES_FILES = [(DUMP_URL, '', True, ALL_COUNTRIES_ZIP_FILE),
                  (DUMP_URL, '', True, HIERARCHY_ZIP_FILE),
                  (DUMP_URL, '', True, ALTERNATE_NAMES_ZIP_FILE),
                  (ZIP_URL, 'zip', True, ALL_COUNTRIES_ZIP_FILE),
                  ]


def download_file(url, dest):
    logger.info('Downloading file from {}'.format(url))
    subprocess.check_call(['wget', url, '-O', dest])


def admin_ddl(admin_level):
    columns = ['country_code TEXT'] + \
              ['admin{}_code TEXT'.format(i)
               for i in xrange(1, admin_level)]

    create = '''
    CREATE TABLE admin{level}_codes (
    geonames_id INT,
    code TEXT,
    name TEXT,
    {fields}
    )'''.format(level=admin_level,
                fields=''',
    '''.join(columns))

    indices = (
        '''CREATE INDEX admin{}_code_index ON
        admin{}_codes (code)'''.format(admin_level, admin_level),
        '''CREATE INDEX admin{}_gn_id_index ON
        admin{}_codes (geonames_id)'''.format(admin_level, admin_level),
    )

    return (create, ) + indices

geonames_ddl = {
    'geonames': (
        '''CREATE TABLE geonames (
        geonames_id INT PRIMARY KEY,
        name TEXT,
        ascii_name TEXT,
        alternate_names TEXT,
        latitude DOUBLE,
        longitude DOUBLE,
        feature_class TEXT,
        feature_code TEXT,
        country_code TEXT,
        cc2 TEXT,
        admin1_code TEXT,
        admin2_code TEXT,
        admin3_code TEXT,
        admin4_code TEXT,
        population LONG DEFAULT 0,
        elevation INT,
        dem INT,
        timezone TEXT,
        modification_date TEXT)''',
        '''CREATE INDEX feature_code ON
        geonames (feature_code)''',
        '''CREATE INDEX country_code ON
        geonames (country_code)''',
        '''CREATE INDEX admin_codes ON
        geonames (country_code, admin1_code, admin2_code, admin3_code, admin4_code)''',
    ),

    'alternate_names': (
        '''CREATE TABLE alternate_names (
        alternate_name_id INT PRIMARY KEY,
        geonames_id INT,
        iso_language TEXT,
        alternate_name TEXT,
        is_preferred_name BOOLEAN DEFAULT 0,
        is_short_name BOOLEAN DEFAULT 0,
        is_colloquial BOOLEAN DEFAULT 0,
        is_historic BOOLEAN DEFAULT 0)''',
        '''CREATE INDEX geonames_id_index ON
        alternate_names (geonames_id)''',
        '''CREATE INDEX geonames_id_alt_name_index ON
        alternate_names(geonames_id, alternate_name)''',
    ),

    'hierarchy': (
        '''CREATE TABLE hierarchy (
        parent_id INT,
        child_id INT,
        type TEXT
        );''',
        '''CREATE INDEX parent_child_index ON
        hierarchy (parent_id, child_id)''',
        '''CREATE INDEX child_parent_index ON
        hierarchy (child_id, parent_id)''',
    ),

    'postal_codes': (
        '''CREATE TABLE postal_codes (
        country_code TEXT,
        postal_code TEXT,
        place_name TEXT,
        admin1 TEXT,
        admin1_code TEXT,
        admin2 TEXT,
        admin2_code TEXT,
        admin3 TEXT,
        admin3_code TEXT,
        latitude DOUBLE,
        longitude DOUBLE,
        accuracy INT
        )''',
        '''CREATE INDEX post_code_index ON
        postal_codes (country_code, postal_code)''',
        '''CREATE INDEX postal_code_admins ON
        postal_codes (country_code, admin1_code, admin2_code, admin3_code)''',
    ),
    'admin1_codes': admin_ddl(1),
    'admin2_codes': admin_ddl(2),
    'admin3_codes': admin_ddl(3),
    'admin4_codes': admin_ddl(4),

}

geonames_file_table_map = {
    ('', ALL_COUNTRIES_ZIP_FILE): 'geonames',
    ('', ALTERNATE_NAMES_ZIP_FILE): 'alternate_names',
    ('', HIERARCHY_ZIP_FILE): 'hierarchy',
    ('zip', ALL_COUNTRIES_ZIP_FILE): 'postal_codes',
}


country_codes_create_table = (
    'drop table if exists country_codes',
    '''
    create table country_codes as
{'fraction_non_alphanumeric': 0.1050830397584298, 'fraction_numerical': 0.006945143432310015, 'mean_word_length': 2.68, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4881844', 'n_tokens_mistral': 3353, 'n_tokens_neox': 3270, 'n_words': 699}