Installation and Configuration

PostreSQL

Meta-Data Batch Import

Make sure, all editors used in the process are set to UTF-8 encoding.

1. Mark the actual table Data on the Google drive spreadsheet colums B - AF, Rows 8 ... n (Without column description and other stuff outside of the table)
2. Copy it into an text document (i.E. a .txt)
3. Now you have the table data as tabulator separated values
4. In Postgres, create a table containing the same columns as the Excel sheet ("import_create_importTable.sql")
5. If using PGAdmin Select to import on this table and using following configurations:

• File Options:
  • Filename: the in task 2 created file.
  • Format: .csv
  • encoding: (if left blank. UTF-8 will be used, or choose UTF-8)
  • Columns: all active
• Misc. Options
  • OID: []
  • Header: []
  • Seperator: [tab] - Choosable in the dropdown menu
  • Quote Options: Nothing
  • NULL options: Nothing

6. Import into the cids structure with the import_importtable_to_cids.sql script.

Database Export / Import

For export and import from and to databases, one can use the postgresql command pg_dump and pg_restore. Note that, depending on installation and/or entries in $PATH, locations could be different. The same is true for hostnames and ports (especially at SSH Tunnel)

To export data out of a database, use: pg_dump --host <database-ip/address> --port <databasePort> --username "<dataBaseUsername>" --format custom --blobs --verbose --file "<Path-to-dumpFile>" "<databaseName>" As example, here is the command used to export from the Switchon-VM: pg_dump --host switchon-virtualbox --port 5432 --username "postgres" --format custom --blobs --verbose --file "C:\SWITCH-ON-dump" "switchon"

To import data into a database, use: pg_restore --host <database-ip/address> --username "<dataBaseUsername>" --dbname "<databaseName>" --verbose "<Path-to-dumpFile>" As example, here is the command used to import to the Deltares PG9.3 Server when a SSH tunnel is accessible: pg_restore --host 127.0.0.1 --port 55433 --username "switchon" --dbname "switchon" --verbose "C:\SWITCH-ON-dump"

If you choose to execute export and import via PGAdmin: For export you can use the default configuration if you accessed the dialog by right clock onto the database itself and choose backup... in the context menu. Just choose a filename and location, and click on Backup. Same goes for import. If you accessed the import dialog via right click onto the database itself and choosing restore you can use the default configuration, just fill in the location of the backupfile and click on restore.

If you want to import and override an existing database use the following command: pg_restore --host <database-ip/address> --username "<dataBaseUsername>" --dbname "<databaseName>" --clean --verbose "<Path-to-dumpFile>" In PGAdmin, you can achive this behavior by activating Clean before restore at the Restore Options #2.

If chosen to use PGAdmin, it can get an error with authentification, if it happens, use the commandline instead.

All Views, Functions and Trigger are also transferred to the new database by using this commands.

pyCSW

pyCSW Modification

In default, the pyCSW is unable to work with a view, the reason is, that it needs a primarykey column for the sqlAlchemy components to work. A view has no primarykey column. To get the pyCSW working with a view, it is necessairy to set a primarykey column directly in the code. To achieve this, two lines in the code of repository.py has to be changed.

Change value of the line from

sqlalchemy import create_engine, asc, desc, func, __version__, select,

to

from sqlalchemy import create_engine, asc, desc, func, __version__, select, PrimaryKeyConstraint

and Line

self.dataset = type('dataset', (base,), dict(__tablename__=table,__table_args__={'autoload':True, 'schema': schema}))

to

self.dataset = type('dataset', (base,), dict(__tablename__=table,__table_args__=(PrimaryKeyConstraint('identifier'), {'autoload': True, 'schema': schema})))

to make the pycsw compatible with a view.

TODO: (Fabian)

• Workarounds? Pull request?
• Refer to this issue: https://github.com/switchonproject/cids-custom-switchon-server/issues/8

pyCSW Configuration

To configuration the pycsw, there is file named "default.cfg" in the main directory of the CSW. /home/hewerf/pycswNstuff/pycsw on Deltares /home/switchon/pycsw/pycsw on the VM In this file one can change the configurations for the pyCSW. To change the database and table, which the pyCSW is using, one can find the "database" and the "table" property in this file, which has to be changed to reflect the now to use database and table. Changes to the configfile can be made while the pyCSW is still running, and one doesn't need to restart the pyCSW for it to load the new configurations.

pyCSW runs on port 8000. For starting pyCSW, please refer to pyCSW Demo System: (https://github.com/switchonproject/cids-custom-switchon/wiki/SIP-Workshop-Demo-System#pycsw-demo-system)