Introduction
In Karttur’s GeoImagine Framework both the processes and the data are stored in a postgres database. The database is built up with schemas associated with typical processes and data sources. This post explains how to setup the complete database by using the special package (setup_db) and prepared xml files defining all the schemas and tables.
Prerequisites
You must have the complete Spatial Data Integrated Development Environment (SPIDE) installed as described in the blog Install and setup spatial data IDE. You must have setup Karttur’s GeoImagine Framework, either by importing or by copying (drag and drop). You must also have prepared a solution for how to link the Framework processes and the postgres database.
If you get stuck, please have a look at the pages on describing the Framework key concept and running processes.
Setup database
The blogpost Connect Python and PostgreSQL using psycopg2 contains a python script for creating databases in postgres using Python. And the blogpost Postgres setup with Python & xml describes how to combine xml and python to create tables.
The framework package setup_db contains the same python modules, plus all the xml and txt documents (files) needed for setting up the complete database structure of Karttur’s GeoImagine Framework. Installing and running the setup_db package will setup the complete structure of schemas and tables used by karttur’s GeoImagine Framework.
db_setup python package
The db_setup package contains four .py files, the standard modules __init__.py and version.py, plus one main module and one class module:
- setup_db_main.py
- setup_db_class.py
The package also contains a subfolder called doc that contains a number of text (.txt) files and a subfolder called xmlsql. Inside the latter are all the xml files that define all the schemas and tables for the Framework.
Package processes
The database setup is accomplished through a set of special processes:
- createschema
- createtable
- tableinsert
- tableupdate
- grant
These processes are not defined in the database itself and can only be run from the package setup_db.
setup_db_main.py
In setup_db_main.py there are predefined commands and links in the main section that are used to setup the entire database. You do not need to edit any of these files unless you want to change the default roles (users) as explained in the preparation post on Database connections.
Create main production db
If you did not create a production db as described in the post Postgres setup with Python & xml you have to create it now. The default name of the database is postgres, but it can be set to any other name.
To create the production database uncomment the first lines under the “__main__” section, and then run the module from the Eclipse menu .
if __name__ == "__main__":
'''
This module only needs to be run at the very startup of building the Karttur Geo Imagine framework.
To run, remove the comment "#prodDB" and set the name of your production DB ("YourProdDB")
'''
verbose = 1
#prodDB = 'YourProdDB' #'postgres
# SetUpProdDb creates an empty Postgres db cluster
# Change 'YourProdDB' to the name of your db
SetUpProdDb(prodDB,verbose)
You should now have a production database. If you want to check the database or change/add superuser(s) please refer to the post on Install PostgreSQL and postGIS.
Setup schemas and tables
The next lines of the “__main__” section link to the files in doc subfolder and loops through them to setup all the schemas and tables. All you need to do is to uncomment the lines as shown below.
# Create db schemas and tables from xml files
projFPN = 'doc/db_karttur_setup_20181116.txt'
SetupSchemasTables(projFPN,prodDB,verbose)
The file db_karttur_setup_YYYYMMDD.txt only contains links to the xml file to loop over.
Run the module, and all the schemas and tables defined in the above xml files will be created.
Add default users
The next lines under the __main__” section link to the text file db_karttur_dbusers_YYYYMMDD.txt.
# Create and grant db users
projFPN = 'doc/db_karttur_dbusers_20181116.txt'
SetupSchemasTables(projFPN,prodDB,verbose)
This links to a single xml file general_grant_v80_sql.xml that contains all the database roles and rights, including passwords. You need to edit the passwords to match the .netrc file that is used for accessing the database at runtime.
Superusers and process for setting up all other processes
The package db_setup also installs the system superuser and two processes: addrootproc and addsubproc. Having access to the superuser and the two processes, all other processes can be installed using the package setup_processes.