Ubuntu Geonames

Merge lp:~gnuoy/ubuntu-geonames/swap-tables-in into lp:ubuntu-geonames

  1. swap-tables-in
  2. Merge into trunk
Proposed by Liam Young
Status: Merged
Merged at revision: 27
Proposed branch: lp:~gnuoy/ubuntu-geonames/swap-tables-in
Merge into: lp:ubuntu-geonames
Diff against target: 160 lines (+72/-29)
1 file modified
import-geonames.sh (+72/-29)
To merge this branch: bzr merge lp:~gnuoy/ubuntu-geonames/swap-tables-in
Reviewer Review Type Date Requested Status
Ubuntu Geonames Hackers Pending
Review via email: mp+185459@code.launchpad.net

Description of the change

Allow new data to be loaded alongside old data and then swapped in.

To post a comment you must log in.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'import-geonames.sh'
--- import-geonames.sh 2013-05-28 10:19:18 +0000
+++ import-geonames.sh 2013-09-13 10:35:56 +0000
@@ -37,11 +37,13 @@
37tail -n +2 iso-languagecodes.txt > iso-languagecodes.txt.tmp37tail -n +2 iso-languagecodes.txt > iso-languagecodes.txt.tmp
38grep -v '^#' countryInfo.txt > countryInfo.txt.tmp38grep -v '^#' countryInfo.txt > countryInfo.txt.tmp
39tail -n +2 timeZones.txt > timeZones.txt.tmp39tail -n +2 timeZones.txt > timeZones.txt.tmp
40 40
41LOAD_POSTFIX="_load"
42BACKUP_POSTFIX="_bkup"
41$PSQL_CMD <<EOT43$PSQL_CMD <<EOT
42BEGIN;44BEGIN;
43DROP TABLE IF EXISTS geoname;45DROP TABLE IF EXISTS geoname${LOAD_POSTFIX};
44CREATE TABLE geoname (46CREATE TABLE geoname${LOAD_POSTFIX} (
45 geonameid int,47 geonameid int,
46 name varchar(200),48 name varchar(200),
47 asciiname varchar(200),49 asciiname varchar(200),
@@ -62,10 +64,10 @@
62 timezone varchar(40),64 timezone varchar(40),
63 moddate date65 moddate date
64);66);
65\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 ''67\copy geoname${LOAD_POSTFIX} (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 ''
6668
67DROP TABLE IF EXISTS alternatename;69DROP TABLE IF EXISTS alternatename${LOAD_POSTFIX};
68CREATE TABLE alternatename (70CREATE TABLE alternatename${LOAD_POSTFIX} (
69 alternatenameId int,71 alternatenameId int,
70 geonameid int,72 geonameid int,
71 isoLanguage varchar(7),73 isoLanguage varchar(7),
@@ -75,10 +77,10 @@
75 isColloquial boolean,77 isColloquial boolean,
76 isHistoric boolean78 isHistoric boolean
77);79);
78\copy alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from $WORKPATH/alternateNames.txt null as '';80\copy alternatename${LOAD_POSTFIX} (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from $WORKPATH/alternateNames.txt null as '';
7981
80DROP TABLE IF EXISTS countryinfo;82DROP TABLE IF EXISTS countryinfo${LOAD_POSTFIX};
81CREATE TABLE countryinfo (83CREATE TABLE countryinfo${LOAD_POSTFIX} (
82 iso_alpha2 char(2),84 iso_alpha2 char(2),
83 iso_alpha3 char(3),85 iso_alpha3 char(3),
84 iso_numeric integer,86 iso_numeric integer,
@@ -99,50 +101,91 @@
99 neighbours char(50), 101 neighbours char(50),
100 equivalentFipsCode char(10)102 equivalentFipsCode char(10)
101);103);
102\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 ''104\copy countryInfo${LOAD_POSTFIX} (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 ''
103105
104DROP TABLE IF EXISTS iso_languagecodes;106DROP TABLE IF EXISTS iso_languagecodes${LOAD_POSTFIX};
105CREATE TABLE iso_languagecodes(107CREATE TABLE iso_languagecodes${LOAD_POSTFIX}(
106 iso_639_3 CHAR(4),108 iso_639_3 CHAR(4),
107 iso_639_2 VARCHAR(50),109 iso_639_2 VARCHAR(50),
108 iso_639_1 VARCHAR(50),110 iso_639_1 VARCHAR(50),
109 language_name VARCHAR(200)111 language_name VARCHAR(200)
110);112);
111\copy iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name) from $WORKPATH/iso-languagecodes.txt.tmp null as ''113\copy iso_languagecodes${LOAD_POSTFIX} (iso_639_3, iso_639_2, iso_639_1, language_name) from $WORKPATH/iso-languagecodes.txt.tmp null as ''
112114
113DROP TABLE IF EXISTS admin1codes;115DROP TABLE IF EXISTS admin1codes${LOAD_POSTFIX};
114CREATE TABLE admin1codes (116CREATE TABLE admin1codes${LOAD_POSTFIX} (
115 code varchar(10),117 code varchar(10),
116 name TEXT,118 name TEXT,
117 nameAscii TEXT,119 nameAscii TEXT,
118 geonameid int120 geonameid int
119);121);
120\copy admin1codes (code,name,nameAscii,geonameid) from $WORKPATH/admin1CodesASCII.txt null as ''122\copy admin1codes${LOAD_POSTFIX} (code,name,nameAscii,geonameid) from $WORKPATH/admin1CodesASCII.txt null as ''
121123
122DROP TABLE IF EXISTS timeZones;124DROP TABLE IF EXISTS timeZones${LOAD_POSTFIX};
123CREATE TABLE timeZones (125CREATE TABLE timeZones${LOAD_POSTFIX} (
124 code CHAR(2),126 code CHAR(2),
125 timeZoneId VARCHAR(200),127 timeZoneId VARCHAR(200),
126 GMT_offset numeric(3,1),128 GMT_offset numeric(3,1),
127 DST_offset numeric(3,1),129 DST_offset numeric(3,1),
128 RAW_offset numeric(3,1)130 RAW_offset numeric(3,1)
129);131);
130\copy timeZones (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from $WORKPATH/timeZones.txt.tmp null as ''132\copy timeZones${LOAD_POSTFIX} (code,timeZoneId,GMT_offset,DST_offset,RAW_offset) from $WORKPATH/timeZones.txt.tmp null as ''
131133
132DROP TABLE IF EXISTS continentCodes;134DROP TABLE IF EXISTS continentCodes${LOAD_POSTFIX};
133CREATE TABLE continentCodes (135CREATE TABLE continentCodes${LOAD_POSTFIX} (
134 code CHAR(2),136 code CHAR(2),
135 name VARCHAR(20),137 name VARCHAR(20),
136 geonameid INT138 geonameid INT
137);139);
138INSERT INTO continentCodes VALUES ('AF', 'Africa', 6255146);140INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('AF', 'Africa', 6255146);
139INSERT INTO continentCodes VALUES ('AS', 'Asia', 6255147);141INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('AS', 'Asia', 6255147);
140INSERT INTO continentCodes VALUES ('EU', 'Europe', 6255148);142INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('EU', 'Europe', 6255148);
141INSERT INTO continentCodes VALUES ('NA', 'North America', 6255149);143INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('NA', 'North America', 6255149);
142INSERT INTO continentCodes VALUES ('OC', 'Oceania', 6255150);144INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('OC', 'Oceania', 6255150);
143INSERT INTO continentCodes VALUES ('SA', 'South America', 6255151);145INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('SA', 'South America', 6255151);
144INSERT INTO continentCodes VALUES ('AN', 'Antarctica', 6255152);146INSERT INTO continentCodes${LOAD_POSTFIX} VALUES ('AN', 'Antarctica', 6255152);
145147COMMIT;
148EOT
149
150# If the live tables exist, back them up and drop the indexes
151#TABLES_PRESENT=$(psql $PGUSER_PARAM $PGPASS $PGHOST $PGPORT $PGDBNAME -A -q -t -c "select count(*) from pg_tables where tablename='geoname'")
152TABLES_PRESENT=$($PSQL_CMD -A -q -t -c "select count(*) from pg_tables where tablename='geoname'")
153if [[ $TABLES_PRESENT == 1 ]]; then
154 echo "Backing up current tables"
155 $PSQL_CMD <<EOT
156BEGIN;
157DROP TABLE IF EXISTS geoname${BACKUP_POSTFIX};
158DROP TABLE IF EXISTS alternatename${BACKUP_POSTFIX};
159DROP TABLE IF EXISTS countryinfo${BACKUP_POSTFIX};
160DROP TABLE IF EXISTS iso_languagecodes${BACKUP_POSTFIX};
161DROP TABLE IF EXISTS admin1codes${BACKUP_POSTFIX};
162DROP TABLE IF EXISTS timeZones${BACKUP_POSTFIX};
163DROP TABLE IF EXISTS continentCodes${BACKUP_POSTFIX};
164DROP INDEX IF EXISTS geoname_id_idx;
165DROP INDEX IF EXISTS geoname_admin1codes_code_idx;
166DROP INDEX IF EXISTS geoname_countryinfo_isoalpha2_idx;
167DROP INDEX IF EXISTS geoname_alternatename_idx;
168ALTER TABLE geoname RENAME TO geoname${BACKUP_POSTFIX};
169ALTER TABLE alternatename RENAME TO alternatename${BACKUP_POSTFIX};
170ALTER TABLE countryinfo RENAME TO countryinfo${BACKUP_POSTFIX};
171ALTER TABLE iso_languagecodes RENAME TO iso_languagecodes${BACKUP_POSTFIX};
172ALTER TABLE admin1codes RENAME TO admin1codes${BACKUP_POSTFIX};
173ALTER TABLE timeZones RENAME TO timeZones${BACKUP_POSTFIX};
174ALTER TABLE continentCodes RENAME TO continentCodes${BACKUP_POSTFIX};
175COMMIT;
176EOT
177fi
178
179# Put the load tables live and rebuild the indexes
180$PSQL_CMD <<EOT
181BEGIN;
182ALTER TABLE geoname${LOAD_POSTFIX} RENAME TO geoname;
183ALTER TABLE alternatename${LOAD_POSTFIX} RENAME TO alternatename;
184ALTER TABLE countryinfo${LOAD_POSTFIX} RENAME TO countryinfo;
185ALTER TABLE iso_languagecodes${LOAD_POSTFIX} RENAME TO iso_languagecodes;
186ALTER TABLE admin1codes${LOAD_POSTFIX} RENAME TO admin1codes;
187ALTER TABLE timeZones${LOAD_POSTFIX} RENAME TO timeZones;
188ALTER TABLE continentCodes${LOAD_POSTFIX} RENAME TO continentCodes;
146CREATE INDEX geoname_id_idx ON geoname(geonameid);189CREATE INDEX geoname_id_idx ON geoname(geonameid);
147CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);190CREATE INDEX geoname_admin1codes_code_idx ON admin1codes(code);
148CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);191CREATE INDEX geoname_countryinfo_isoalpha2_idx ON countryinfo(iso_alpha2);

Subscribers

People subscribed via source and target branches

to all changes: