Convert ICIS 5.5 Schema to ICIS 6.0 Schema

From ICISWiki

Jump to: navigation, search

Contents

Introduction

 MySQL script for converting ICIS 5.5 schema to ICIS 6.0 schema can be downloaded at cropforge.  Stored procedures used are under the /stored_procedures folder.  Create table queries in its respective database folder.  That is, to create DMS tables, the script is under the DMS folder while to create a GMS table look in the GMS folder (same with GEMS table creation).

1. Create a new database for the new schema. 

create database <icis6>;

2. Create ICIS 6.0 tables by executing the Create ICIS tables scripts in the newly created database

  • To create DMS tables, execute the CreateGMSTable 
mysql -u <username> -p<password> icis6 <GMSCreateTables.sql

or from wiithin mysql,

source GMSCreateTables.sql


  • To create DMS tables, execute the CreateDMSTable 
 mysql -u <username> -p<password> icis6 <DMSCreateTables.sql

or from wiithin mysql,

source DMSCreateTables.sql


  • To create DMS tables, execute the CreateGEMSTable 
 mysql -u <username> -p<password> icis6 <GEMSCreateTables.sql


or from wiithin mysql,

source GEMSCreateTables.sql

3. Check if tables levels, obsunit, represtn and steffects exists in the older version of ICIS.

Create previously mentioned tables if not existing.  To do this, execute the create_new_5_6_uniqe_tables script.

call create_new_5_6_uniqe_tables('<database name>');

4. In the ICIS 5.5 database, execute the create tables query.

This will create the missing (if any) ICIS tables in the ICIS 5.5 database.

5. Execute query for inserting missing/new column.

The script compares the two schemas (ICIS 5.5 and ICIS 6.0) adds all that are in  ICIS 6.0 but is not in the older schema. 

call insert_missing_columns('<old_schema_database>', '<new_schema_database>');


Note:

Steps 1 and 2 is optional if ICIS 6.0 is already existing. The purpose of creating an ICIS 6.0 database is only for comparison. The insert_missing_columns procedure compares ICIS 6.0 and the older ICIS schema. If an ICIS 6.0 schema is already existing in the host, migration of an older database will only need the following steps:
 

mysql_ shell> use <older_version_icis_database>;
mysql_shell> CALL create_new_5_6_unique_tables('<older_version_icis_database>');
mysql_shell> source GMSCreateTable.sql;
mysql_shell> source DMSCreateTable.sql;
mysql_shell> source GEMSCreateTable.sql;
mysql_shell> CALL insert_missing_columns('<older_version_icis_database','icis_6.0_schema');>

Script Description

  • Adds new field into the table if it does not exist
  • Adds primary keys
  • Checks redundant indices
  • Deletes redundant indices



Retrieved from "http://www.cropinfo.org/icis/index.php/Convert_ICIS_5.5_Schema_to_ICIS_6.0_Schema"
Personal tools