Converting from a Progress table to MySQL table
So you have a Progress app, but it is showing it's age version-wise and it doesn't seem like Progress is going to follow up with Webspeed as it is. Upper management starts talking "lets gid rid of Progress" and developers now have to see about running stuff on PHP/MySQL. (It's been around for fifteen years that I know of, it's fast enough.)
So first off, we want to move Progress tables over to MySQL - but Progress doesn't have that! (Of course, I mean they are in it to make some cash!) So, lets say have a bunch of tables that need to be redone over in MySQL - here is an answer to that.
We have a catalogitem table defined in Progress - and we want this to be returned from an app for the SQL in MySQL:
create table catalogitem (
check_subs boolean,
clearance boolean,
compressed_part_num text,
compressed_std_part_num text,
cust_buys integer,
date_entered date,
Description text,
family_code text,
franchised boolean,
hashed_part_num text,
in_stock boolean,
line text,
List_Type text,
Mfg_Name text,
obsolete boolean,
parent_mfg_code text,
Part_Num text,
Price decimal(32,8),
ps_description text,
ps_parms text,
ps_stale boolean,
Qty_Avail decimal(32,8),
rating decimal(32,8),
sales_cat text,
sales_subcat text,
search_desc text,
series text,
special_beg date,
special_code text,
special_end date,
std_part_num text,
subfamily_code text,
total_buys integer,
UOM text
);
As many of you know, I have been a MySQL programmer for 15 or so years, and looking over the open source code that Mark Newnham and I wrote - it was time for some upgrades.
I wanted a simple thing that would take the table as seen in Progress and return the table as seen in MySQL (or some other SQL engine) and can understand the hundrends of tables that are needed. I needed something easy to use like...
Recommended by LinkedIn
define variable A as TableDefinition.cls no-undo.
A = new TableDefinition("catalogitem", "c:\temp\catalogitem.sql").
Very easy to expand upon to get a schema for a lotta tables.
What follows is a convertor in Progress 4GL that will create SQL statements for creating the table:
/******************************************************************************/
/* Progress to MySQL (Version 3) */
/* Scott Auge */
/* */
/* Copyright 2021 Scott Auge */
/* */
/* Permission is hereby granted, free of charge, to any person obtaining a */
/* copy of this software and associated documentation files (the "Software"), */
/* to deal in the Software without restriction, including without limitation */
/* the rights to use, copy, modify, merge, publish, distribute, sublicense, */
/* and/or sell copies of the Software, and to permit persons to whom the */
/* Software is furnished to do so, subject to the following conditions: */
/* */
/* The above copyright notice and this permission notice shall be included in */
/* all copies or substantial portions of the Software. */
/* */
/* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR */
/* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, */
/* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE*/
/* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER */
/* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING */
/* FROM OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER */
/* DEALINGS IN THE SOFTWARE. */
/* */
/******************************************************************************/
class TableDefinition:
// What file the results can be found in
define public variable cFileName as character no-undo.
define public variable cTableName as character no-undo.
define stream src.
// -------------------------------------------------------------------------
// Define the output file to flow results into
// -------------------------------------------------------------------------
method public void DefineOutputFile ():
output stream src to value(cFileName).
end. // DefineOutputFile()
// -------------------------------------------------------------------------
// Various Clean Ups
// -------------------------------------------------------------------------
method public void CleanUp ():
output stream src close.
end. // CleanUp()
// -------------------------------------------------------------------------
// Our constructore
// -------------------------------------------------------------------------
constructor TableDefinition (input ipcTableName as character, input ipcFileName as character):
assign
cFileName = ipcFileName
cTableName = ipcTableName
.
DefineOutputFile().
StartFile().
FillFields().
EndFile().
CleanUp().
end.
// -------------------------------------------------------------------------
// Start and End of the table
// -------------------------------------------------------------------------
method public void StartFile():
put stream src unformatted ("create table " + cTableName + " (") skip.
end.
method public void EndFile():
put stream src unformatted skip ");" skip.
end.
method public void FillFields():
define variable FirstOf as logical no-undo.
find _file
where _file._file-name = cTableName
no-lock no-error.
FirstOf = true.
for each _field of _file no-lock:
// If we are on another field, then it aint the last field!
// If it is the first of, then dont put , on its lonesome
if not FirstOf then
put stream src unformatted "," skip.
FirstOf = false.
case _field._data-type:
when "integer" then put stream src unformatted _field._field-name " " _field._data-type.
when "character" then put stream src unformatted _field._field-name " text".
when "longchar" then put stream src unformatted _field._field-name " longtext".
when "decimal" then put stream src unformatted _field._field-name " decimal(32,8)".
when "logical" then put stream src unformatted _field._field-name " boolean".
when "date" then put stream src unformatted _field._field-name " date".
end. // case
end. //for each
end. // FillFields
end. // TableDefinition
/* -------------------------------------------------------------------------
define variable A as TableDefinition.cls no-undo.
A = new TableDefinition("catalogitem", "c:\temp\catalogitem.sql").
---------------------------------------------------------------------- */
One will find a need to translate between a Progress value and the MySQL value (after MySQL is what this was checked on.) One can expand or contract the values in FillFields() by changing the type.
I have Progress Developer Studio so I am not so worried about making something that runs on not the Studio. I simply run it and then cut and paste the SQL code into the Web that runs MySQL.
There are dangers as the SQL code uses different formats, but I think I have chosen the usual size for the size of values that can be used. Since you have the code now - you can set the types needed for your use of the conversion code. The big one is date and the various time values, but we'll get to those in the data moving from Progress to an SQL database in the next article. For example, date uses mm-yy-yyyy or mm/dd/yyyy while in SQL it is yyyy-mm-dd, a simple change once one writes a function to ShowDate() in PHP.
This should also work for PostgreSQL with some value type changes - happy coding!
Yes, I just now realized I forgot indices... for the future - for the future...