Merge lp:~gnuoy/ubuntu-geonames/add-support-for-remote-db into lp:ubuntu-geonames

[Liam Young]

These changes are to enable deploying geonames via juju. The charm is a WIP at the moment but needs the sphinx template so the credentials of the remote db can be substituted in. Since the db is on another server I've also added options to import-geonames.sh to specify host, port etc of the remote db

[Dimitri John Ledkov (xnox)]

looks good.

review: Approve

=== modified file 'import-geonames.sh'
--- import-geonames.sh	2012-08-23 04:23:45 +0000
+++ import-geonames.sh	2013-05-28 13:39:30 +0000
@@ -1,11 +1,25 @@
 #!/bin/bash
- 
+# If DB is local pass no arguments else
+# ./import-geonames.sh -u user -h host -p port -d dbname
+while getopts u:h:p:d: flag; do
+    case $flag in
+        u) PGUSER=$OPTARG;
+           PGUSER_PARAM="--username $OPTARG";;
+        h) PGHOST="--host $OPTARG";;
+        p) PGPORT="--port $OPTARG";;
+        d) PGDBNAME="--dbname $OPTARG";;
+    esac
+done
+PGDBNAME=${PGDBNAME:="geonames"}
+PGUSER=${PGUSER:="geouser"}
+
+PSQL_CMD="psql $PGUSER_PARAM $PGPASS $PGHOST $PGPORT $PGDBNAME"
 WORKPATH="$(mktemp -d)"
  
 chmod 755 $WORKPATH
 cd $WORKPATH
 trap "rm -rf $WORKPATH" EXIT HUP INT QUIT TERM
- 
+pwd 
 # allCountries.zip contains allCountries.txt
 # alternateNames.zip contains iso-languagecodes.txt alternateNames.txt
 ZIPFILES="allCountries.zip alternateNames.zip"
@@ -24,14 +38,14 @@
 grep -v '^#' countryInfo.txt > countryInfo.txt.tmp
 tail -n +2 timeZones.txt > timeZones.txt.tmp
  
-psql geonames <<EOT
+$PSQL_CMD <<EOT
 BEGIN;
 DROP TABLE IF EXISTS geoname;
 CREATE TABLE geoname (
 	geonameid int,
 	name varchar(200),
 	asciiname varchar(200),
-	alternatenames varchar(10000),
+	alternatenames varchar(12000),
 	latitude float,
 	longitude float,
 	fclass char(1),
@@ -48,20 +62,20 @@
 	timezone varchar(40),
 	moddate date
 );
-copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from '$WORKPATH/allCountries.txt' null as '';
+\copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from $WORKPATH/allCountries.txt null as ''
 
 DROP TABLE IF EXISTS alternatename;
 CREATE TABLE alternatename (
 	alternatenameId int,
 	geonameid int,
 	isoLanguage varchar(7),
-	alternateName varchar(200),
+	alternateName varchar(400),
 	isPreferredName boolean,
 	isShortName boolean,
 	isColloquial boolean,
 	isHistoric boolean
 );
-copy alternatename  (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from '$WORKPATH/alternateNames.txt' null as '';
+\copy alternatename  (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from $WORKPATH/alternateNames.txt null as '';
 
 DROP TABLE IF EXISTS countryinfo;
 CREATE TABLE countryinfo (
@@ -85,7 +99,7 @@
 	neighbours char(50), 
 	equivalentFipsCode char(10)
 );
-copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currency,currencyName,Phone,postalCodeFormat,postalCodeRegex,languages,geonameId,neighbours,equivalentFipsCode) from '$WORKPATH/countryInfo.txt.tmp' null as '';
+\copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currency,currencyName,Phone,postalCodeFormat,postalCodeRegex,languages,geonameId,neighbours,equivalentFipsCode) from $WORKPATH/countryInfo.txt.tmp null as ''
 
 DROP TABLE IF EXISTS iso_languagecodes;
 CREATE TABLE iso_languagecodes(
@@ -94,7 +108,7 @@
 	iso_639_1 VARCHAR(50),
 	language_name VARCHAR(200)
 );
-copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from '$WORKPATH/iso-languagecodes.txt.tmp' null as '';
+\copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from $WORKPATH/iso-languagecodes.txt.tmp null as ''
 
 DROP TABLE IF EXISTS admin1codes;
 CREATE TABLE admin1codes (
@@ -103,7 +117,7 @@
 	nameAscii TEXT,
 	geonameid int
 );
-copy admin1codes (code,name,nameAscii,geonameid) from '$WORKPATH/admin1CodesASCII.txt' null as '';
+\copy admin1codes (code,name,nameAscii,geonameid) from $WORKPATH/admin1CodesASCII.txt null as ''
 
 DROP TABLE IF EXISTS timeZones;
 CREATE TABLE timeZones (
@@ -113,7 +127,7 @@
 	DST_offset numeric(3,1),
 	RAW_offset numeric(3,1)
 );
-copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from '$WORKPATH/timeZones.txt.tmp' null as '';
+\copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from $WORKPATH/timeZones.txt.tmp null as ''
 
 DROP TABLE IF EXISTS continentCodes;
 CREATE TABLE continentCodes (
@@ -133,6 +147,7 @@
 CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);
 CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);
 CREATE INDEX geoname_alternatename_idx ON alternatename(alternatenameId);
-GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO geouser;
+GRANT ALL PRIVILEGES ON geoname, admin1codes, countryInfo, alternatename TO $PGUSER;
+GRANT SELECT ON geoname, admin1codes, countryInfo, alternatename TO public;
 COMMIT;
 EOT

=== added file 'sphinx.conf.tmpl'
--- sphinx.conf.tmpl	1970-01-01 00:00:00 +0000
+++ sphinx.conf.tmpl	2013-05-28 13:39:30 +0000
@@ -0,0 +1,49 @@
+source geonamessrc
+{{
+type = pgsql
+sql_host = {host}
+sql_user = {user}
+sql_pass = {password}
+sql_db = {database}
+sql_query = SELECT geoname.geonameid*10+1 AS id, geoname.name AS name, geoname.population AS population, geoname.geonameid AS geonameid FROM geoname WHERE geoname.fclass='P'
+sql_attr_uint = population
+}}
+
+source altnamessrc
+{{
+type = pgsql
+sql_host = {host}
+sql_user = {user}
+sql_pass = {password}
+sql_db = {database}
+sql_query = SELECT alternatename.alternatenameId*10+2 AS id, alternatename.alternateName AS name, geoname.population AS population, geoname.geonameid AS geonameid FROM alternatename JOIN geoname on (geoname.geonameid = alternatename.geonameid AND geoname.name != alternatename.alternateName) WHERE (geoname.fclass='P' AND (LENGTH(alternatename.isoLanguage) < 4 OR alternatename.isoLanguage is null))
+sql_attr_uint = population
+}}
+
+index geonames
+{{
+	source			= geonamessrc
+	source			= altnamessrc
+	path			= /var/lib/sphinxsearch/data/geonames
+	docinfo			= extern
+	morphology			= none
+	stopwords			=
+	min_word_len		= 2
+	min_prefix_len		= 0
+	min_infix_len		= 0
+        charset_type = utf-8
+	charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101, U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109, U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F, U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117, U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D, U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135, U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C, U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144, U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B, U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153, U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159, U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161, U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167, U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F, U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175, U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C, U+017C, U+017D->U+017E, U+017E, U+4E00..U+9FFF, U+3000..U+30FF
+}}
+searchd
+{{
+port = 3312
+log					= /var/log/sphinxsearch/searchd.log
+query_log			= /var/log/sphinxsearch/query.log
+read_timeout		= 5
+max_children		= 30
+pid_file			= {sphinx_pid_file_location}
+max_matches			= 1000
+seamless_rotate		= 1
+preopen_indexes		= 0
+unlink_old			= 1
+}}