pro2my

    Progress to MySQL conversion routines www.newnhams.com Pro My  Version 2.0 15 th  Nov 2004  Introduction Chapter 1 Product Overview Pro2My is a simple group of PROGRESS source code routines that can be run against any PROGRESS database greater than version 6 to produce a series of files containing MySQL format SQL statements. These files can be used to produce a MySQL database that is a mirror of the PROGRESS database. The routines provide a method of: ã Extracting the data schema from the PROGRESS database ã Checking and converting PROGRESS data to prevent MySQL database load errors. ã Extracting the data from the PROGRESS database. ã Extracting the Security information from the database schema. ã Importing the schema, data and security into MySQL using the mysql   command. System Requirements PROGRESS The Pro2My conversion routines dynamically create and run PROGRESS programs. They also include routines that update data in the database. To run these routines, a PROGRESS development license version 6 or higher must be installed on the machine from which the routines are run. MySQL The Pro2My programs are qualified against MySQL version 3. releases 23 and higher. Versions of MySQL that do not support the FULLTEXT key option are not supported. There are no licensing requirements for MySQL Licensing The free version of Pro2My is issued under the terms of the GPL license. This has important ramifications for you as a PROGRESS developer. Broadly speaking it means that: ã YOU CAN  use the code for any personal or business related purpose, adding and changing as required, and even redistributing to other people your changes, along as the srcinal copyright is retained in each routine. Any routine you distribute is also covered under the GPL, and you cannot  prevent others changing and distributing your work. ã YOU CANNOT  incorporate this work inside a commercial product, if that work is not: 1. Available free of charge 2. Available in human readable (not encrypted) source code to the customer. If you want to incorporate this work into software which does not meet the above criteria, you must  purchase a Commercial License, which is more restrictive, but allows you to use the product in commercial applications . For more information, email info@newnhams.com The full text of the GPL license is included at the end of this manual. PROGRESS is a registered trademark of PROGRESS Software MySQL is a registered trademark of MySQL AB  - 1 -    Transferring the Schema Chapter 2  The schema dump routines are designed to create an SQL file that can be used to create a MySQL database of the same name with the same tables. Limitations of the schema dump There are certain limitations of the schema dump, mostly related to limitations of the MySQL database. 1. MySQL does not currently support triggers, so no trigger information is dumped. 2. MySQL does not support sequences, although a limited replication can be achieved using auto-increment table fields. 3. MySQL requires a NON-NULL, UNIQUE, primary index. In order to ensure this, the Pro2My routines create a new primary index based on the PROGRESS recid. This ensures that the data is loaded cleanly into MySQL, but requires specific routines to manage after the data is converted. An alternative approach would be to create an auto-increment variable for the primary index. 4. MySQL does not support validation routines inside the database, so any PROGRESS data validation stored in the database is ignored. 5. MySQL does not currently support Views, so any view information is ignored. 6. The dump routines do not distinguish between 4GL and SQL tables. 7. Extended information in PROGRESS version 9 (SYS tables) is ignored. 8. Tables with a large number of fields may cause the PROGRESS client to fail. There is no current resolution for this problem. Data Type Conversion  Note that integer and decimal conversions are determined based on the display format in the dictionary, no just on the data type. If the database contains values in an integer or decimal field that is greater than the display format, the data may fail to load into the MySQL database because the field type will have been defined too small. Progress data type MySQL data type Notes Character   with a field length less than or equal to 255 Varchar   of equivalent size Character   with a field length greater than 255 characters. Text See also MySQL  BLOB  format  Date Date MySQL dates are stored in ISO format. A PROGRESS  Date field could easily be migrated to a MySQL  Datetime  field for additional functionality.  Logical Tinyint False = 0 True = 1.  Decimal Decimal  Integer   less than 3 digits Tinyint  Integer   between 3 and 4 digits  Smallint  Integer   between 4 and 6 digits  MediumInt  Integer   over 6 digits  Integer Mysql  Integer   and  Bigint   are equivalent  Recid (Version 9) Bigint  Raw Blob The data in raw fields is currently not converted. - 2 -  Index conversion. There are 2 specific index conversion items 1. The creation of a new non-null primary index (see above). 2. Word indexes are converted to the MySQL FULLTEXT format. If you are using Word Indexes with progress and wish to convert them, you need to modify ptmsch.p. See the note at line 387 of  ptmsch.p All other indices are converted as-is. Other schema items Mandatory fields are written into MySQL as NOT NULL Default values are written directly into the MySQL database with the equivalent value, except for the PROGRESS unknown value, which is written as blank for character and date fields, and zero for numbers. Arrays The Pro2My routines expand array variables into individual fields. Each field is named as follows: Array[1], array[2],array[3],……..array[n] Becomes Array__1,array__2,array__3,……..array__n It should however be noted that in most languages that are used to develop programs against mySQL databases such as PERL or PHP, arrays and loops normally start at value 0. To achieve this, modify the code, where marked, in  ptmsch.p   Field and File Name conversion The Pro2My routines make various changes to the field and file names to ensure they are compliant with MySQL naming conventions. These changes are made in the program d2us.p . The following rules are applied: 1. All field/file/database names are converted to upper case. Depending on your requirements, change the code in d2us.p  to convert them to lower case, but in any case, be consistent. 2. All occurrences of the - (minus) character are converted to an _ (underscore). 3. The resulting variable name is compared against a list of MySQL reserved words. If the variable is a reserved word, it is prefixed with X_ , which will at least allow the schema to load into MySQL. The MySQL reserved words are held in the include file mysqlr.i   which can be modified as necessary. To see all these features in action, run the  ptmsch.p  program against the SPORTS database, and view the output.  Note that the Order table has been renamed X_ORDER. TIP In case of problems, is far easier to change the PROGRESS database using the dictionary tools before dumping, than to dump the database and try to manipulate the schema using the MySQL tools. - 3 -