Repository: weecology/retriever

Issue:
Removing tests for python 2
Officially removing tests for Python2 and this is because of the fact that the underlying tools are breaking more often.

**`On Travis:`**
We are moving to dockers specifically for Geo spatial testing
Most Geo-spatial tools will not work with Python 2  straight from the box.
**`On Appveyor:`**
There must have been some updates or changes in the environment and this is causing encoding problems. 
When using Python2 tools/environment, some Unicode character encoding in a PR will result into a diff due to failed proper encoding/decoding of the bits for those chars.
 

Discussion:
henrykironde: Looks like we need a better way to catch errors.
Instead of `docker-compose  run --service-ports python_retriever py.test -v --cov=retriever 2>error.log && if [[ -s "error.log" ]]; then exit 1; else echo 0; fi`
ethanwhite: Nice work @henrykironde!

Patch:
diff --git a/.travis.yml b/.travis.yml
index d0e8f53..3e9bff5 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -1,38 +1,16 @@
-sudo: false
-dist: trusty
-language: python
-python:
-  - "2.7"
-  - "3.5"
-addons:
-  postgresql: "9.4"
-  apt:
-    sources:
-      - travis-ci/sqlite3
-    packages:
-      - sqlite3
-
+cache: packages
+sudo: required
+warnings_are_errors: false
 services:
-  - postgresql
-install:
-  - pip install -r requirements.txt
-  - pip install -e .
-  - pip install codecov -U
-  - pip install pytest-cov -U
-  - pip install pytest-xdist -U
-  # Use pytest 3.9.3 version since 3.10.0 has a bug
-  - pip install pytest==3.9.3 -U
+  - docker
 
+# database setup
+before_install:
+  - docker-compose ps
 
 # command to run tests using py.test
-
 script:
-  - py.test -v --cov=retriever
-
-# database setup
-before_script:
-  - sqlite3 --version
-  - psql -c 'create database testdb_retriever;' -U postgres
+  - docker-compose  run --service-ports python_retriever py.test -v --cov=retriever
 
 after_success:
   - codecov
diff --git a/Dockerfile b/Dockerfile
new file mode 100644
index 0000000..07fef78
--- /dev/null
+++ b/Dockerfile
@@ -0,0 +1,51 @@
+# Download base image ubuntu 16.04
+FROM ubuntu:16.04
+
+MAINTAINER Weecology "https://github.com/weecology/retriever"
+
+RUN apt-get update && apt-get install -y --no-install-recommends apt-utils
+RUN apt-get install -y --force-yes build-essential wget git locales locales-all > /dev/null
+RUN apt-get install -y --force-yes postgresql-client mysql-client > /dev/null
+
+# Set encoding
+ENV LC_ALL en_US.UTF-8
+ENV LANG en_US.UTF-8
+ENV LANGUAGE en_US.UTF-8
+
+# Remove python2 and install python3
+RUN apt-get remove -y python && apt-get install -y python3  python3-pip curl
+RUN rm -f /usr/bin/python && ln -s /usr/bin/python3 /usr/bin/python
+RUN rm -f /usr/bin/pip && ln -s /usr/bin/pip3 /usr/bin/pip
+
+RUN echo "export PATH="/usr/bin/python:$PATH"" >> ~/.profile
+RUN echo "export PYTHONPATH="/usr/bin/python:$PYTHONPATH"" >> ~/.profile
+RUN echo "export PGPASSFILE="~/.pgpass"" >> ~/.profile
+
+# Add permissions to config files
+RUN chmod 0644 ~/.profile
+
+RUN pip install psycopg2 pymysql > /dev/null
+RUN pip install codecov -U
+RUN pip install pytest-cov -U
+RUN pip install pytest-xdist -U
+# Use pytest 3.9.3 version since 3.10.0 has a bug
+RUN pip install pytest==3.9.3 -U
+
+# Install Postgis after Python is setup
+RUN apt-get install -y --force-yes postgis
+
+COPY . ./retriever
+RUN chmod 0755 /retriever/cli_tools/entrypoint.sh
+ENTRYPOINT ["/retriever/cli_tools/entrypoint.sh"]
+
+
+WORKDIR ./retriever
+
+RUN pip install -e .
+# Add permissions to config files
+# Do not run these cmds before Entrypoint.
+RUN export PGPASSFILE="~/.pgpass"
+RUN chmod 600 cli_tools/.pgpass
+RUN chmod 600 cli_tools/.my.cnf
+
+CMD ["bash", "-c", "Python --version"]
diff --git a/appveyor.yml b/appveyor.yml
index feed39b..e1f9993 100644
--- a/appveyor.yml
+++ b/appveyor.yml
@@ -2,10 +2,6 @@ environment:
 
   matrix:
 
-    - PYTHON: "C:\\Python27-x64"
-      PYTHON_VERSION: "2.7.x"
-      PYTHON_ARCH: "64"
-
     - PYTHON: "C:\\Python35-x64"
       PYTHON_VERSION: "3.5.x"
       PYTHON_ARCH: "64"
@@ -66,4 +62,4 @@ build_script:
   - cd C:\projects\retriever
 
 test_script:
-  - py.test -v -k "not test_download_archive"
+  - py.test -v -k "not test_download_archive and not test_postgres_spatial"
diff --git a/cli_tools/.my.cnf b/cli_tools/.my.cnf
new file mode 100644
index 0000000..b0edd24
--- /dev/null
+++ b/cli_tools/.my.cnf
@@ -0,0 +1,5 @@
+[client]
+user="travis"
+password="Password12!"
+host="mysqldb_retriever"
+port="3306"
diff --git a/cli_tools/.pgpass b/cli_tools/.pgpass
new file mode 100644
index 0000000..a4dd6fe
--- /dev/null
+++ b/cli_tools/.pgpass
@@ -0,0 +1,1 @@
+python_retriever:*:testdb_retriever:postgres:Password12!
\ No newline at end of file
diff --git a/cli_tools/entrypoint.sh b/cli_tools/entrypoint.sh
new file mode 100644
index 0000000..d877cbc
--- /dev/null
+++ b/cli_tools/entrypoint.sh
@@ -0,0 +1,8 @@
+#!/bin/sh
+set -e
+
+# Copy config files to $HOME
+cp -r /retriever/cli_tools/.pgpass  ~/
+cp -r /retriever/cli_tools/.my.cnf  ~/
+
+exec "$@"
diff --git a/docker-compose.yml b/docker-compose.yml
new file mode 100644
index 0000000..98f171a
--- /dev/null
+++ b/docker-compose.yml
@@ -0,0 +1,46 @@
+version: '3'
+
+services:
+  mysqldb_retriever:
+    image: mysql:5.7
+    container_name: mysqldb_ret_container
+    environment:
+      MYSQL_USER: travis
+      MYSQL_PASSWORD: Password12!
+      MYSQL_ROOT_PASSWORD: Password12!
+      MYSQL_DATABASE: testdb_retriever
+
+  pgdb_retriever:
+    image: kartoza/postgis:11.0-2.5
+    volumes:
+      - ./cli_tools/postgis-data:/var/lib/postgresql
+      - ./cli_tools/:/backups
+    container_name: pgdb_ret_container
+    environment:
+      # To create multiple database you can add coma separated databases eg gis,data
+      - POSTGRES_DB=testdb_retriever
+      - POSTGRES_USER=postgres
+      - POSTGRES_PASS=Password12!
+      - ALLOW_IP_RANGE=0.0.0.0/0
+    restart: on-failure
+
+  python_retriever:
+    container_name: ret_container
+    image: ret_image
+    command: bash -c "Python --version"
+    environment:
+      # If IN_DOCKER is set, use service names as hosts(travis)
+      # Otherwise use localhost (local tests)
+      "IN_DOCKER" : "true"
+      "PGUSER" : "postgres"
+      "PGPASSWORD" : "Password12!"
+      "PGDATABASE" : "testdb_retriever"
+      "PGPORT" : "5432"
+      "PGHOST" : "pgdb_retriever"
+    restart: on-failure
+    build: .
+    depends_on:
+      - mysqldb_retriever
+      - pgdb_retriever
+    ports:
+      - 5001:80
diff --git a/retriever/engines/csvengine.py b/retriever/engines/csvengine.py
index 03566cd..26dbe20 100644
--- a/retriever/engines/csvengine.py
+++ b/retriever/engines/csvengine.py
@@ -49,10 +49,7 @@ class engine(Engine):
 
         # Register all tables created to enable
         # testing python files having custom download function
-        if self.script.name not in self.script_table_registry:
-            self.script_table_registry[self.script.name] = []
-        self.script_table_registry[self.script.name].append(
-            (self.table_name(), self.table))
+        Engine.register_tables(self)
 
     def disconnect(self):
         """Close the last file in the dataset"""
@@ -105,7 +102,7 @@ class engine(Engine):
         table_name = os.path.join(tabledir, tablename)
         return os.path.exists(table_name)
 
-    def to_csv(self, sort=True):
+    def to_csv(self, sort=True, path=None, select_columns=None):
         """Export sorted version of CSV file"""
         for table_item in self.script_table_registry[self.script.name]:
             sort_csv(table_item[0])
diff --git a/retriever/engines/jsonengine.py b/retriever/engines/jsonengine.py
index 0a37fe4..cf1f692 100644
--- a/retriever/engines/jsonengine.py
+++ b/retriever/engines/jsonengine.py
@@ -127,7 +127,7 @@ class engine(Engine):
         table_name = os.path.join(tabledir, tablename)
         return os.path.exists(table_name)
 
-    def to_csv(self, sort=True, path=None):
+    def to_csv(self, sort=True, path=None, select_columns=None):
         """Export table from json engine to CSV file"""
         for table_item in self.script_table_registry[self.script.name]:
             header = table_item[1].get_insert_columns(join=False, create=True)
diff --git a/retriever/engines/postgres.py b/retriever/engines/postgres.py
index 49b23e1..c26d9a2 100644
--- a/retriever/engines/postgres.py
+++ b/retriever/engines/postgres.py
@@ -165,16 +165,19 @@ CSV HEADER;"""
 
     def insert_raster(self, path=None, srid=4326):
         """Import Raster into Postgis Table
-        Uses raster2pgsql -I -C -s <SRID> <PATH> <SCHEMA>.<DBTABLE>
+        Uses raster2pgsql -Y -M -d -I -s <SRID> <PATH> <SCHEMA>.<DBTABLE>
         | psql -d <DATABASE>
         The sql processed by raster2pgsql is run
         as psql -U postgres -d <gisdb> -f <elev>.sql
+        -Y uses COPY to insert data,
+        -M VACUUM table,
+        -d  Drops the table, recreates insert raster data
         """
 
         if not path:
             path = Engine.format_data_dir(self)
 
-        raster_sql = "raster2pgsql -M -d -I -s {SRID} \"{path}\" -F -t 100x100 {SCHEMA_DBTABLE}".format(
+        raster_sql = "raster2pgsql -Y -M -d -I -s {SRID} \"{path}\" -F -t 100x100 {SCHEMA_DBTABLE}".format(
             SRID=srid,
             path=os.path.normpath(path),
             SCHEMA_DBTABLE=self.table_name())
@@ -189,6 +192,7 @@ CSV HEADER;"""
         cmd_stmt = raster_sql + cmd_string
         if self.debug:
             print(cmd_stmt)
+        Engine.register_tables(self)
         subprocess.call(cmd_stmt, shell=True, stdout=subprocess.PIPE)
 
     def insert_vector(self, path=None, srid=4326):
@@ -210,10 +214,12 @@ CSV HEADER;"""
 
         The sql processed by shp2pgsql is run
         as  psql -U postgres -d <DBNAME>>
+        shp2pgsql -c -D -s 4269 -i -I
          """
         if not path:
             path = Engine.format_data_dir(self)
-        vector_sql = "shp2pgsql -d -I -s {SRID} \"{path}\" {SCHEMA_DBTABLE}".format(
+        vector_sql = "shp2pgsql -d -I -W {encd} -s {SRID} \"{path}\" {SCHEMA_DBTABLE}".format(
+            encd=ENCODING,
             SRID=srid,
             path=os.path.normpath(path),
             SCHEMA_DBTABLE=self.table_name())
@@ -227,6 +233,7 @@ CSV HEADER;"""
         cmd_stmt = vector_sql + cmd_string
         if self.debug:
             print(cmd_stmt)
+        Engine.register_tables(self)
         subprocess.call(cmd_stmt, shell=True, stdout=subprocess.PIPE)
 
     def format_insert_value(self, value, datatype):
diff --git a/retriever/engines/xmlengine.py b/retriever/engines/xmlengine.py
index b801cfa..3a6c9ac 100644
--- a/retriever/engines/xmlengine.py
+++ b/retriever/engines/xmlengine.py
@@ -122,7 +122,7 @@ class engine(Engine):
                       for key, value in zip(keys, line_data)]
         return ''.join(row_values)
 
-    def to_csv(self, sort=True, path=None):
+    def to_csv(self, sort=True, path=None, select_columns=None):
         """Export table from xml engine to CSV file."""
         for table_item in self.script_table_registry[self.script.name]:
             header = table_item[1].get_insert_columns(join=False, create=True)
diff --git a/retriever/lib/engine.py b/retriever/lib/engine.py
index e477ae3..1a83c40 100644
--- a/retriever/lib/engine.py
+++ b/retriever/lib/engine.py
@@ -403,10 +403,7 @@ class Engine(object):
             print(create_stmt)
         try:
             self.execute(create_stmt)
-            if self.script.name not in self.script_table_registry:
-                self.script_table_registry[self.script.name] = []
-            self.script_table_registry[self.script.name].append(
-                (self.table_name(), self.table))
+            self.register_tables()
 
             if self.table.name not in self.script.tables:
                 self.script.tables[self.table.name] = self.table
@@ -418,6 +415,13 @@ class Engine(object):
             print(e)
             print("Replacing existing table")
 
+    def register_tables(self):
+        if self.script.name not in self.script_table_registry:
+            self.script_table_registry[self.script.name] = []
+        self.script_table_registry[self.script.name].append(
+            (self.table_name(), self.table)
+        )
+
     def create_table_statement(self):
         """Return SQL statement to create a table."""
         create_stmt = "CREATE TABLE " + self.table_name() + " ("
@@ -828,7 +832,14 @@ class Engine(object):
                 dbname = ''
         return self.opts["table_name"].format(db=dbname, table=name)
 
-    def to_csv(self, sort=True, path=None):
+    def to_csv(self, sort=True, path=None, select_columns=None):
+        """Create a CSV file from the a data store.
+
+        sort flag to create a sorted file,
+        path to write the flag else write to the PWD,
+        select_columns flag is used by large files to select
+        columns data and has SELECT LIMIT 3.
+        """
         # Due to Cyclic imports we can not move this import to the top
         from retriever.lib.engine_tools import sort_csv
 
@@ -840,7 +851,13 @@ class Engine(object):
             csv_writer = open_csvw(csv_file)
             self.get_cursor()
             self.set_engine_encoding()
-            self.cursor.execute("SELECT * FROM  {};".format(table_name[0]))
+            limit = ""
+            cols = "*"
+            if select_columns:
+                limit = "LIMIT 3"
+                cols = ",".join(select_columns)
+            sql_query = "SELECT {cols} FROM  {tab} {limit};"
+            self.cursor.execute(sql_query.format(cols=cols, tab=table_name[0], limit=limit))
             row = self.cursor.fetchone()
             column_names = [u'{}'.format(tuple_i[0])
                             for tuple_i in self.cursor.description]


Test patch:
diff --git a/test/raw_data_gis/scripts/test_eco_level_four.json b/test/raw_data_gis/scripts/test_eco_level_four.json
index 5eca586..4d459c5 100644
--- a/test/raw_data_gis/scripts/test_eco_level_four.json
+++ b/test/raw_data_gis/scripts/test_eco_level_four.json
@@ -20,7 +20,7 @@
     {
       "name": "eco_level_four",
       "path": "ecoregion_level_four.shp",
-      "url": "https://github.com/weecology/retriever/blob/master/test/raw_data_gis/test_vector_ecoregion_level_four.zip",
+      "url": "https://github.com/weecology/retriever/raw/master/test/raw_data_gis/test_eco_level_four.zip",
       "geom_type": "Polygon",
       "extent": {
         "xMax": -1249755.9583,
diff --git a/test/raw_data_gis/scripts/test_raster_bio1.json b/test/raw_data_gis/scripts/test_raster_bio1.json
index 17cb8e0..86dce8f 100644
--- a/test/raw_data_gis/scripts/test_raster_bio1.json
+++ b/test/raw_data_gis/scripts/test_raster_bio1.json
@@ -6,7 +6,7 @@
   "keywords": [
     "tests"
   ],
-  "url": "https://github.com/weecology/retriever/blob/master/test/raw_data_gis/test_raster_bio1.zip",
+  "url": "https://github.com/weecology/retriever/raw/master/test/raw_data_gis/test_raster_bio1.zip",
   "ref": "N/A",
   "version": "1.0.0",
   "retriever_minimum_version": "2.1.dev",
diff --git a/test/raw_data_gis/scripts/test_raster_bio2.json b/test/raw_data_gis/scripts/test_raster_bio2.json
index 40e1c60..02dabf4 100644
--- a/test/raw_data_gis/scripts/test_raster_bio2.json
+++ b/test/raw_data_gis/scripts/test_raster_bio2.json
@@ -6,7 +6,7 @@
   "keywords": [
     "test"
   ],
-  "url": "https://github.com/weecology/retriever/blob/master/test/raw_data_gis/test_raster_bio2.zip",
+  "url": "https://github.com/weecology/retriever/raw/master/test/raw_data_gis/test_raster_bio2.zip",
   "ref": "N/A",
   "version": "1.0.0",
   "retriever_minimum_version": "2.1.dev",
diff --git a/test/raw_data_gis/scripts/test_us_eco.json b/test/raw_data_gis/scripts/test_us_eco.json
index a19f6da..0f4f1ab 100644
--- a/test/raw_data_gis/scripts/test_us_eco.json
+++ b/test/raw_data_gis/scripts/test_us_eco.json
@@ -21,7 +21,7 @@
     {
       "name": "us_eco",
       "path": "us_eco.shp",
-      "url": "https://github.com/weecology/retriever/blob/master/test/raw_data_gis/test_vector_us_eco.zip",
+      "url": "https://github.com/weecology/retriever/raw/master/test/raw_data_gis/test_us_eco.zip",
       "geom_type": "Polygon",
       "extent": {
         "xMax": -1249755.9583,
diff --git a/test/test_integration.py b/test/test_integration.py
index d2f6ed2..68560e9 100644
--- a/test/test_integration.py
+++ b/test/test_integration.py
@@ -26,10 +26,20 @@ from retriever.lib.engine_tools import create_file
 
 # Set postgres password, Appveyor service needs the password given
 # The Travis service obtains the password from the config file.
+os_password = ""
+pgdb_host = "localhost"
+mysqldb_host = "localhost"
+testdb_retriever = "testdb_retriever"
+testschema = "testschema_retriever"
+
 if os.name == "nt":
     os_password = "Password12!"
-else:
-    os_password = ""
+
+docker_or_travis = os.environ.get("IN_DOCKER")
+if docker_or_travis == "true":
+    os_password = 'Password12!'
+    pgdb_host = "pgdb_retriever"
+    mysqldb_host = "mysqldb_retriever"
 
 mysql_engine, postgres_engine, sqlite_engine, msaccess_engine, \
 csv_engine, download_engine, json_engine, xml_engine = engine_list
@@ -507,13 +517,12 @@ def test_jsonengine_integration(dataset, expected, tmpdir):
 @pytest.mark.parametrize("dataset, expected", test_parameters)
 def test_postgres_integration(dataset, expected, tmpdir):
     """Check for postgres regression."""
-    cmd = 'psql -U postgres -d testdb_retriever -h localhost -c ' \
-          '"DROP SCHEMA IF EXISTS testschema CASCADE"'
+    cmd = 'psql -U postgres -d ' + testdb_retriever +' -h ' + pgdb_host + ' -w -c \"DROP SCHEMA IF EXISTS ' + testschema + ' CASCADE\"'
     subprocess.call(shlex.split(cmd))
     postgres_engine.opts = {'engine': 'postgres', 'user': 'postgres',
-                            'password': os_password, 'host': 'localhost',
-                            'port': 5432, 'database': 'testdb_retriever',
-                            'database_name': 'testschema',
+                            'password': os_password, 'host': pgdb_host,
+                            'port': 5432, 'database': testdb_retriever,
+                            'database_name': testschema,
                             'table_name': '{db}.{table}'}
     assert get_output_as_csv(dataset, postgres_engine, tmpdir,
                              db=postgres_engine.opts['database_name']) == expected
@@ -522,14 +531,14 @@ def test_postgres_integration(dataset, expected, tmpdir):
 @pytest.mark.parametrize("dataset, expected", test_parameters)
 def test_mysql_integration(dataset, expected, tmpdir):
     """Check for mysql regression."""
-    cmd = 'mysql -u travis -Bse "DROP DATABASE IF EXISTS testdb_retriever"'
+    cmd = 'mysql -u travis -Bse "DROP DATABASE IF EXISTS {testdb_retriever}"'.format(testdb_retriever=testdb_retriever)
     subprocess.call(shlex.split(cmd))
     mysql_engine.opts = {
         'engine': 'mysql',
         'user': 'travis',
         'password': '',
-        'host': 'localhost',
+        'host': mysqldb_host,
         'port': 3306,
-        'database_name': 'testdb_retriever',
+        'database_name': testdb_retriever,
         'table_name': '{db}.{table}'}
     assert get_output_as_csv(dataset, mysql_engine, tmpdir, db=mysql_engine.opts['database_name']) == expected
diff --git a/test/test_regression.py b/test/test_regression.py
index d861df7..ac464f2 100644
--- a/test/test_regression.py
+++ b/test/test_regression.py
@@ -7,19 +7,15 @@ import shlex
 import shutil
 import subprocess
 import sys
+from distutils.dir_util import copy_tree
+import time
+
 from imp import reload
 
-from retriever import download
-from retriever import fetch
-from retriever import install_csv
-from retriever import install_json
-from retriever import install_mysql
-from retriever import install_postgres
-from retriever import install_sqlite
-from retriever import install_xml
+import retriever as rt
 from retriever.lib.defaults import ENCODING, DATA_DIR
 from retriever.lib.load_json import read_json
-
+from retriever.lib.defaults import HOME_DIR
 encoding = ENCODING.lower()
 
 reload(sys)
@@ -31,10 +27,20 @@ from retriever.engines import engine_list
 
 # Set postgres password, Appveyor service needs the password given
 # The Travis service obtains the password from the config file.
-if os.name == 'nt':
+os_password = ""
+pgdb_host = "localhost"
+mysqldb_host = "localhost"
+testdb_retriever = "testdb_retriever"
+testschema = "testschema_retriever"
+
+if os.name == "nt":
+    os_password = "Password12!"
+
+docker_or_travis = os.environ.get("IN_DOCKER")
+if docker_or_travis == "true":
     os_password = 'Password12!'
-else:
-    os_password = ''
+    pgdb_host = "pgdb_retriever"
+    mysqldb_host = "mysqldb_retriever"
 
 mysql_engine, postgres_engine, sqlite_engine, msaccess_engine, \
 csv_engine, download_engine, json_engine, xml_engine = engine_list
@@ -42,7 +48,8 @@ file_location = os.path.dirname(os.path.realpath(__file__))
 retriever_root_dir = os.path.abspath(os.path.join(file_location, os.pardir))
 working_script_dir = os.path.abspath(os.path.join(retriever_root_dir, "scripts"))
 HOMEDIR = os.path.expanduser('~')
-script_home = '{}/.retriever/scripts'.format(HOMEDIR)
+script_home = '{}/.retriever/scripts/'.format(HOMEDIR)
+script_home = os.path.normpath(script_home)
 
 download_md5 = [
     ('mt-st-helens-veg', 'd5782e07241cb3fe9f5b2e1bb804a794'),
@@ -56,6 +63,14 @@ db_md5 = [
     ('mammal_masses', '6fec0fc63007a4040d9bbc5cfcd9953e')
 ]
 
+spatial_db_md5 = [
+    ("test-eco-level-four", ["gid", "us_l3code", "na_l3code","na_l2code"], 'd1c01d8046143e9700f5cf92cbd6be3d'),
+    ("test-raster-bio1", ["rid", "filename"], '27e0472ddc2da9fe807bfb48b786a251'),
+    ("test-raster-bio2", ["rid", "filename"], '2983a9f7e099355db2ce2fa312a94cc6'),
+    ("test-us-eco", ["gid", "us_l3code", "na_l3code", "na_l2code"], 'eaab9fa30c745557ff6ba7c116910b45')
+]
+
+
 # Tuple of (dataset_name, list of dict values corresponding to a table)
 fetch_tests = [
     ('iris',
@@ -110,6 +125,16 @@ def setup_module():
     os.chdir(retriever_root_dir)
     subprocess.call(['cp', '-r', 'test/raw_data', retriever_root_dir])
 
+    src = os.path.join(retriever_root_dir, 'scripts')
+    copy_tree(src, script_home)
+
+    # Add spatail scripts
+    spatial_src = os.path.join(retriever_root_dir, 'test/raw_data_gis/scripts/')
+    copy_tree(spatial_src, script_home)
+
+    # Use reload to include the new test scripts
+    rt.reload_scripts()
+
 
 def teardown_module():
     """Cleanup temporary output files and return to root directory."""
@@ -127,18 +152,14 @@ def get_script_module(script_name):
     return read_json(os.path.join(retriever_root_dir, 'scripts', script_name))
 
 
-def get_csv_md5(dataset, engine, tmpdir, install_function, config):
+def get_csv_md5(dataset, engine, tmpdir, install_function, config, cols=None):
     workdir = tmpdir.mkdtemp()
-    src = os.path.join(retriever_root_dir, 'scripts')
-    dest = os.path.join(str(workdir), 'scripts')
-    subprocess.call(['cp', '-r', src, dest])
     workdir.chdir()
     final_direct = os.getcwd()
     engine.script_table_registry = {}
     engine_obj = install_function(dataset.replace('_', '-'), **config)
-    engine_obj.to_csv()
-    # need to remove scripts before checking md5 on dir
-    subprocess.call(['rm', '-r', 'scripts'])
+    time.sleep(5)
+    engine_obj.to_csv(select_columns=cols)
     current_md5 = getmd5(data=final_direct, data_type='dir')
     os.chdir(retriever_root_dir)
     return current_md5
@@ -159,47 +180,50 @@ def test_sqlite_regression(dataset, expected, tmpdir):
         'table_name': '{db}_{table}',
         'data_dir': DATA_DIR}
     interface_opts = {'file': dbfile, 'data_dir': retriever_root_dir}
-    assert get_csv_md5(dataset, sqlite_engine, tmpdir, install_sqlite, interface_opts) == expected
+    assert get_csv_md5(dataset, sqlite_engine, tmpdir, rt.install_sqlite, interface_opts) == expected
 
 
 @pytest.mark.parametrize("dataset, expected", db_md5)
 def test_postgres_regression(dataset, expected, tmpdir):
     """Check for postgres regression."""
-    cmd = 'psql -U postgres -d testdb_retriever -h localhost -c ' \
-          '"DROP SCHEMA IF EXISTS testschema CASCADE"'
+    cmd = 'psql -U postgres -d ' + testdb_retriever + ' -h ' + pgdb_host + ' -w -c \"DROP SCHEMA IF EXISTS ' + testschema + ' CASCADE\"'
     subprocess.call(shlex.split(cmd))
     postgres_engine.opts = {'engine': 'postgres',
                             'user': 'postgres',
                             'password': os_password,
-                            'host': 'localhost',
+                            'host': pgdb_host,
                             'port': 5432,
-                            'database': 'testdb_retriever',
-                            'database_name': 'testschema',
+                            'database': testdb_retriever,
+                            'database_name': testschema,
                             'table_name': '{db}.{table}'}
     interface_opts = {"user": 'postgres',
                       "password": postgres_engine.opts['password'],
+                      'host': postgres_engine.opts['host'],
+                      'port': postgres_engine.opts['port'],
                       "database": postgres_engine.opts['database'],
                       "database_name": postgres_engine.opts['database_name'],
                       "table_name": postgres_engine.opts['table_name']}
-    assert get_csv_md5(dataset, postgres_engine, tmpdir, install_postgres, interface_opts) == expected
+    assert get_csv_md5(dataset, postgres_engine, tmpdir, rt.install_postgres, interface_opts) == expected
 
 
 @pytest.mark.parametrize("dataset, expected", db_md5)
 def test_mysql_regression(dataset, expected, tmpdir):
-    """Check for mysql regression."""
-    cmd = 'mysql -u travis -Bse "DROP DATABASE IF EXISTS testdb_retriever"'
+    cmd = 'mysql -u travis -Bse "DROP DATABASE IF EXISTS {testdb_retriever}"'.format(testdb_retriever=testdb_retriever)
     subprocess.call(shlex.split(cmd))
     mysql_engine.opts = {'engine': 'mysql',
                          'user': 'travis',
                          'password': '',
-                         'host': 'localhost',
+                         'host': mysqldb_host,
                          'port': 3306,
-                         'database_name': 'testdb_retriever',
+                         'database_name': testdb_retriever,
                          'table_name': '{db}.{table}'}
     interface_opts = {"user": mysql_engine.opts['user'],
+                      # 'password': mysql_engine.opts['password'],
+                      'host': mysql_engine.opts['host'],
+                      'port': mysql_engine.opts['port'],
                       "database_name": mysql_engine.opts['database_name'],
                       "table_name": mysql_engine.opts['table_name']}
-    assert get_csv_md5(dataset, mysql_engine, tmpdir, install_mysql, interface_opts) == expected
+    assert get_csv_md5(dataset, mysql_engine, tmpdir, rt.install_mysql, interface_opts) == expected
 
 
 @pytest.mark.parametrize("dataset, expected", db_md5)
@@ -210,7 +234,7 @@ def test_xmlengine_regression(dataset, expected, tmpdir):
         'table_name': '{db}_output_{table}.xml',
         'data_dir': DATA_DIR}
     interface_opts = {'table_name': '{db}_output_{table}.xml'}
-    assert get_csv_md5(dataset, xml_engine, tmpdir, install_xml, interface_opts) == expected
+    assert get_csv_md5(dataset, xml_engine, tmpdir, rt.install_xml, interface_opts) == expected
 
 
 @pytest.mark.parametrize("dataset, expected", db_md5)
@@ -221,7 +245,7 @@ def test_jsonengine_regression(dataset, expected, tmpdir):
         'table_name': '{db}_output_{table}.json',
         'data_dir': DATA_DIR}
     interface_opts = {'table_name': '{db}_output_{table}.json'}
-    assert get_csv_md5(dataset, json_engine, tmpdir, install_json, interface_opts) == expected
+    assert get_csv_md5(dataset, json_engine, tmpdir, rt.install_json, interface_opts) == expected
 
 
 @pytest.mark.parametrize("dataset, expected", db_md5)
@@ -232,14 +256,14 @@ def test_csv_regression(dataset, expected, tmpdir):
         'table_name': '{db}_output_{table}.csv',
         'data_dir': DATA_DIR}
     interface_opts = {'table_name': '{db}_output_{table}.csv'}
-    assert get_csv_md5(dataset, csv_engine, tmpdir, install_csv, interface_opts) == expected
+    assert get_csv_md5(dataset, csv_engine, tmpdir, rt.install_csv, interface_opts) == expected
 
 
 @pytest.mark.parametrize("dataset, expected", download_md5)
 def test_download_regression(dataset, expected):
     """Test download regression."""
     os.chdir(retriever_root_dir)
-    download(dataset, "raw_data/{0}".format(dataset))
+    rt.download(dataset, "raw_data/{0}".format(dataset))
     current_md5 = getmd5(data="raw_data/{0}".format(dataset), data_type='dir')
     assert current_md5 == expected
 
@@ -248,7 +272,7 @@ def test_download_regression(dataset, expected):
 def test_fetch():
     """Test fetch interface"""
     for dataset, expected in fetch_tests:
-        data_frame = fetch(dataset)
+        data_frame = rt.fetch(dataset)
         for itm in expected:
             for table_i in itm:
                 expected_data = itm[table_i][0]
@@ -263,13 +287,36 @@ def test_interface_table_registry():
     # Test if script_table_registry keeps only the latest
     # table names of the installed data packages in
     # script_table_registry
-    install_csv("iris")
-    wine_data = fetch("wine-composition")
+    rt.install_csv("iris")
+    wine_data = rt.fetch("wine-composition")
     assert "iris" not in wine_data.keys()
 
 
 @pytest.mark.parametrize("dataset, expected", fetch_order_tests)
 def test_fetch_order(dataset, expected):
     """Test fetch dataframe order"""
-    data_frame_dict = fetch(dataset)
+    data_frame_dict = rt.fetch(dataset)
     assert list(data_frame_dict.keys()) == expected
+
+
+@pytest.mark.parametrize("dataset, cols, expected", spatial_db_md5)
+def test_postgres_spatial(dataset, cols, expected, tmpdir):
+    """Check for postgres regression."""
+    cmd = 'psql -U postgres -d ' + testdb_retriever + ' -h ' + pgdb_host + ' -w -c \"DROP SCHEMA IF EXISTS ' + testschema + ' CASCADE\"'
+    subprocess.call(shlex.split(cmd))
+    postgres_engine.opts = {'engine': 'postgres',
+                            'user': 'postgres',
+                            'password': os_password,
+                            'host': pgdb_host,
+                            'port': 5432,
+                            'database': testdb_retriever,
+                            'database_name': testschema,
+                            'table_name': '{db}.{table}'}
+    interface_opts = {"user": 'postgres',
+                      "password": postgres_engine.opts['password'],
+                      'host': postgres_engine.opts['host'],
+                      'port': postgres_engine.opts['port'],
+                      "database": postgres_engine.opts['database'],
+                      "database_name": postgres_engine.opts['database_name'],
+                      "table_name": postgres_engine.opts['table_name']}
+    assert get_csv_md5(dataset, postgres_engine, tmpdir, rt.install_postgres, interface_opts, cols) == expected