/* createdb.p -- convert a progress database to mysql */
/* by:  Matthew Lang    matt@moredirect.com           */


define variable sql       as character no-undo.
define variable last-char as character no-undo.
define variable i         as integer   no-undo.
define variable j         as integer   no-undo.

{/u1/source/traxx/mysql/mysql-functions.i}

define variable mysql     as memptr no-undo.
define variable mysql_res as memptr no-undo.

db_connect(mysql).  /* connect to mysql */

db_query(mysql,"create database sports").  /* create new database */
db_query(mysql, "use sports").

/* add schema */

for each _file where _file-name < "_" and _owner <> "sysprogress" no-lock,
    each _field of _file no-lock break by _file-name:

    if first-of(_file-name) then
        sql = 'create table `' + replace(_file-name,"-","_") + "` ( ".
        
    if _extent = 0 then
        run make-columns(replace(_field-name,"-","_")) .
    else do i = 1 to _extent:
        run make-columns(replace(_field-name,"-","_") + "__" + string(i)).
        if i < _extent then
            sql = sql + ", ".
    end.
 
    if not last-of (_file-name) then
            sql = sql +  ", ".
    else do :
        sql = sql +  ")" .
        if db_query(mysql,sql) <> 0 then do:     /* execute our query */
            message db_last_error(mysql) view-as alert-box error.
            message sql view-as alert-box.       
        end.
    end.
end.


/* now add our indices */

for each _index where _index-name < "_":

    if _index-name = 'default' then next.
    
    find _file where recid(_file) = _index._file-recid.
    
    sql = "alter table `" + replace(_file-name,"-","_") + "` add ".
    if _file._prime-index = recid(_index) and _index._unique then
        sql = sql +  " primary key ".
        
    else if _index._unique then
        sql = sql +  " unique ".
    else
        sql = sql + " index ".
                        
    if _index-name = "order" then
        sql = sql +  "key_".
                                    
    sql = sql + replace(replace(_index-name,"-","_"),"&","")  + " (".

    last-char = "".
    for each _index-field of _index,
        each _field where _index-recid = recid(_index) and
             _field-recid = recid(_field):

        sql = sql + last-char.
        
        sql = sql + replace(_field-name,"-","_").
        last-char = ",".                               
        if _width > 100 then
            sql = sql + "(100)".
     end.
                                 
     sql = sql + ")". 
    
     if db_query(mysql,sql) <> 0 then do:     /* execute our query */
        message db_last_error(mysql) view-as alert-box error.
        message sql view-as alert-box.       
     end.

end.


/* now add all of the data */

define variable qry-handle   as handle no-undo.
define variable buff-handle  as handle    no-undo.
define variable field-handle as handle    no-undo.
define variable sql-fields   as character no-undo.
define variable sql-values   as character no-undo.
define variable tmp-string as character no-undo.

for each _file where _file-name < "_" and _owner <> "sysprogress" no-lock:
    
    /* just in case we had an error in an earlier run of this , */
    /* let's clear the table and start over                     */
    /* we're going to recreate the table instead of truncating */
    /* it, because it's much faster if you use InnoDB table types */
    
    sql = "create temporary table mytmp like " + replace(_file-name,'-','_').
    db_query(mysql,sql).
    
    sql = "drop table " + replace(_file-name,"-","_").
    db_query(mysql,sql).
    
    sql = "create table " + replace(_file-name,"-","_") + " like mytmp".
    db_query(mysql,sql).

    sql = "drop table mytmp".
    db_query(mysql,sql).
    
    create buffer buff-handle for table _file-name.
    create query qry-handle.
    
    qry-handle:set-buffers(buff-handle).
    qry-handle:query-prepare("for each " + _file-name + " no-lock").
    qry-handle:query-open().
    qry-handle:get-next().
    sql-fields = "insert into `" + replace(_file-name,"-","_") + "` (".

    do i = 1 to buff-handle:num-fields:
        field-handle = buff-handle:buffer-field(i).
        
        if field-handle:extent = 0 then 
            sql-fields = sql-fields + replace(field-handle:name,"-","_")  .

        else do j = 1 to field-handle:extent:
            sql-fields = sql-fields + replace(field-handle:name,"-","_")
                        + "__" + string(j).
            if j < field-handle:extent then
                sql-fields = sql-fields + ",".
        end.
        if i < buff-handle:num-fields then
            sql-fields = sql-fields + ",".
    end.
    
    sql-fields  =  replace(sql-fields,"#","") + ")".
    
    qry-handle:query-close().
    qry-handle:query-open().
    
    repeat:
        qry-handle:get-next().
        if qry-handle:query-off-end then leave.
            
        assign sql-values = " values("
               sql = "".

        do i = 1 to buff-handle:num-fields:
            field-handle = buff-handle:buffer-field(i).
            if field-handle:extent = 0 then do:
                tmp-string = field-handle:buffer-value.
                if tmp-string = ? then tmp-string = "".
                case field-handle:data-type:
                    when "character" then
                        sql-values = sql-values + "'" +
                             replace(replace(tmp-string,"\\",""),"'","\\'")
                                +  "'".
                    when "date" then do:
                        if tmp-string = "" then
                            sql-values = sql-values + "'0000-00-00'".
                        else
                            sql-values = sql-values + "'" +
                                       string(year(date(tmp-string)))   + "-" +
                                         entry(1,tmp-string,"/") + "-" +
                                         entry(2,tmp-string,"/") + "'".
                    end.
            
                    when "logical" then
                        sql-values = sql-values +
                                     string(logical(tmp-string),"1/0").
                            
                    when "integer" then
                        sql-values = sql-values + string(integer(tmp-string)).
            
                    when "decimal" then do:
                        if tmp-string = "" then
                            sql-values = sql-values + "0".
                        else
                            sql-values = sql-values +
                                      trim(string(decimal(tmp-string))).
                    end.
                    otherwise
                        sql-values = sql-values + tmp-string.
                end.  
            end.

            else  do j = 1 to field-handle:extent:
                tmp-string = trim(field-handle:buffer-value[j]).
                if tmp-string = ? then tmp-string = "".
                if field-handle:data-type = "character" then
                    sql-values = sql-values + "'" +
                         replace(replace(tmp-string,"\\",""),"'","\\'") + "'".
                else if field-handle:data-type = "decimal" then do:
                    if tmp-string = "" then
                        sql-values = sql-values + "0".
                    else
                        sql-values = sql-values +
                                  trim(string(decimal(tmp-string))).
                end.
                else
                    sql-values = sql-values + tmp-string.
                if j < field-handle:extent then
                    assign sql-values = sql-values + ",". 
             end.
         
             if i < buff-handle:num-fields then
                assign sql-values = sql-values + ",".
           
        end.  

        assign sql-values = sql-values + ")"
               sql        = sql-fields + sql-values.
        if db_query(mysql,sql) <> 0 then do:
            message db_last_error(mysql) view-as alert-box error.
            message sql view-as alert-box.
        end.
    end.
end.
db_close(mysql).




procedure make-columns:

    define input parameter column-name as character no-undo.
    
    sql = sql + column-name.

     
    case _field._data-type:           /* handle column data types */
        when "character" then
            sql = sql +  " varchar(" + string(_width) + ")  ".
        when "decimal" then
            sql = sql +  " decimal(" + string(length(_format)) + ","  +
                         if index(_format,".") > 0 then
   string(length(substring(_format,r-index(_format,".")))) + ")"    else "2)".
        when "integer" then
            sql = sql +  " integer(" + string(length(_format)) + ")".
        when "logical" then
            sql = sql +  " tinyint(1) ".
        when "date" then
            sql = sql +  " date ".
    end case.

    if _mandatory then   /* required field? */
        sql = sql +  " NOT ".
    sql = sql +  " NULL ".
    
    /* handle initial values */
    if _initial > "" and _initial <> "?" and _data-type <> "date" then do:
        sql = sql +  " default ".
        case _data-type:
            when "character" then
                sql = sql +  '"' + _initial + '"'.
            when "decimal" or when "integer" then
                sql = sql +  _initial.
            when "logical" then do:
                if caps(_initial) begins "N" then
                    sql = sql +  "0".
                else if caps(_initial) begins "Y" then
                    sql = sql +  "1".
                else sql = sql +  "0".
            end.
        end case.
    end.
end.