Database
The database the development is mostly focusing on is MySQL and PostgreSQL. In addition, there are fully java based alternatives, such as HSQLDB
for use on stand alone clients where it can run embedded in the client.
However, we need PostgreSQL for PostGIS, which is essential for the WebGIS.
Index:
Backups and moving data
Naturally, sql and csv files must be zipped before uploaded. WinXP has built in zip compression, 7-zip is an excellent open source tool for both Windows and Linux. There are of course many other programs that will compress.
Moving MySQL databases
To dump the create statements for a MySQL database, but remove comments and the like:
mysqldump -d --compact -u dhis -p dhis > create.sql
To dump just the data from the table datavalue:
mysqldump -t --compact -u dhis -p dhis datavalue > datavalue.sql
It may be of interest to see if LOAD DATA will work even faster for the datavalue table:
SELECT * INTO OUTFILE 'datavalue.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM datavalue;
To load the data back in:
LOAD DATA INFILE 'datavalue.txt' INTO TABLE datavalue FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
More info:
Following mysqldump import example for InnoDB tables:
mysqldump --opt --user=username --password database > dumbfile.sql (--opt is on by default in Mysql5)
Edit the dump file and put these lines at the beginning:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
Put these lines at the end:
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
Then load the file:
mysql --user=username --password database < dumpfile.sql
Postgresql
TODO: Explore the use of the -Fc option with pg_restore
Only schema
pg_dump -Fp -s -c -U <username> -f <file> -d <databasename>
Only data
pg_dump -Fp -a -c -U <username> -f <file> -d <databasename>
Dump a PostGIS database with functions and all>
pg_dump -Fp -c -U <username> -f <file> -d <databasename>
Import
psql -U <username> -d <databasename> -f dumpfile
References
Convert from Access
Convert from MySQL
One way of converting could be to use DHIS 2 running on MySQL to create a full export file, and then import this file into DHIS 2 running on PostgreSQL (just changing the hibernate properties). Still, we would like to have a more direct method.
The database schema (only the CREATE TABLE statements)
Make sure you have Perl in your path. On Linux, that will usually be the case. On Windows, you will usually have to install Perl. Download the Windows MSI package from here: http://activestate.com/Products/Download/Download.plex?id=ActivePerl
Then download the Perl script mysql2pgsql_HISP.pl which is attached to this page, and is a modification of the mysql2psql script (The changes are indicated by #CHANGED).
Now you can convert using first mysqldump with proper options, then mysql2pgsql_HISP.pl, and finally import using psql:
mysqldump --compatible=postgresql --no-data --skip-add-drop-table -u MY_USERNAME -p MY_DATABASENAME > dhis.sql
perl mysql2pgsql_HISP.pl dhis.sql dhis_pg.sql
psql -U PG_USERNAME -d PG_DATABASENAME -f dhis_pg.sql
Only the data
mysqldump -t --quote-names=false --add-locks=false --extended-insert=false -u USERNAME -p MY_DATABASENAME > dhis.sql
psql -U USERNAME -d PG_DATABASENAME -f dhis_pg.sql
Tolerant search (soundex etc.)
http://www.informit.com/articles/article.asp?p=24691&seqNum=3
References
