MS Access to MySQL conversion of ICIS databases
From ICISWiki
Contents
|
Install MySQL and ODBC Driver
- MySQL version: MySQL 5.1.22-rc-community
- MySQL ODBC Driver version: 3.51.x
Data transfer (recommended process)
1. Use SQLYog as your MySQL admin tool
(SQLyog MySQL GUI - Community Edition (Stable) at http://code.google.com/p/sqlyog/downloads/list)
2. Create an empty destination MySQL database first.
E.g. database named "iris"
Recommended character set: utf8 (unicode)
Recommended collation: utf8_general_ci
CREATE DATABASE iris CHARACTER SET utf8 COLLATE utf8_general_ci;
3. Run the .SQL file on the MySQL database to create tables
(Right-click on the database icon, then select "Restore from SQL dump" on the pop-up menu. Select the .SQLs containing ICIS schema definitions)
The standard Data Definition Language (DDL) files for ICIS web central (Rice) : Download here.
- Genealogy Management System (GMS) schema: GMS_TABLES.sql
- Data Management System (DMS) schema: DMS_TABLES.sql
- Gene Management System (GEMS): GEMS_TABLES.sql
Important: Make sure you have the most up-to-date copies of these DDL files. These .SQL files have:
- all table names defined in lowercase letters
- all tables defined with ENGINE = MyISAM
- all tables having utf8 as the default character set
- all integer data types defined as Long Integer (in MS Access) (equivalent to INT data type of Mysql).
- all data types previously defined as TEXT changed to type VARCHAR(255)
This is required for databases that will be used by the Zeus application (ICIS web).
Define the database structure first, and then run the Bullzip tool just to transfer data.
4. Use Bullzip.com's Access-to-MySQL conversion tool to transfer data from MS Access.
<<Download Access-to-MySQL tool (it's freeware)>>
(Leave other fields blank. Click "Next").
Select the MySQL database you want to use. Specify Mysql username and password. Select option "direct transfer".
Select all tables.
Select "Transfer records", "Include column list in INSERT statements", and "Unicode (utf-8)" (to support multi-lingual NVALs).
Click "Run Now" to start data migration.
Example of germplasm with multi-lingual NVALs:
???? Is the database for local ICIS standalone or Zeus (web)?????
- If for ZEUS:
- Repeat the procedure above for DMS and GEMS -- making sure that the name of the destination database remains the same (e.g. "iris").
- See Zeus database installation procedure... for further instructions.
- If for use with ICIS standalone applications, proceed with adding of table indices..keep reading below...
Define indices on ICIS Core tables
Central database
- Index definition files for:
- Genealogy Management System (GMS) tables: GMS_INDICES.sql
- Data Management System (DMS) tables: DMS_INDICES.sql
- Gene Management System (GEMS) tables: GEMS_INDICES.sql
Download here.
- UNIQUE Index definition files for:
- Genealogy Management System (GMS) tables: GMS_UNIQUEKEYS_CENTRAL.sql
- Data Management System (DMS) tables: DMS_UNIQUEKEYS.sql
- Gene Management System (GEMS) tables: GEMS_UNIQUEKEYS.sql
Download here.
Right-click on the database icon, then select "Restore from SQL dump" on the pop-up menu. Select the .SQLs containing CREATE INDEX commands.
Local database
- Index definition files for:
- Genealogy Management System (GMS) tables: GMS_INDICES.sql
- Data Management System (DMS) tables: DMS_INDICES.sql
- Inventory Management System (IMS) tables: IMS_INDICES.sql
Download here.
- UNIQUE Index definition files for:
- Genealogy Management System (GMS) tables: GMS_UNIQUEKEYS_LOCAL.sql
- Data Management System (DMS) tables: DMS_UNIQUEKEYS.sql
Download here.
GRANT database privileges
Set privileges for the ICIS user UID/PWD e.g.
if (in ICIS.ini file):
[Local GMS] DSN=LOCAL-TRAINING-MYSQL UID=RBREED PWD=RBREED
then...
mysql> GRANT ALL PRIVILEGES ON iris_local.* to 'RBREED'@'%' IDENTIFIED BY 'RBREED'; mysql> FLUSH PRIVILEGES;
