PC Toledo

Código: Selecionar todos

/*
   FREEWARE
   Written for Clipper 5.2e by Janusz Piwowarski.
   Home page: http://www.geocities.com/SiliconValley/Heights/9174
   Email: januszp@geocities.com
   
   *----------*
   
   Extract a component from a full file name.

   Syntax:

       FILEDRIVE(<cFullFileName>) --> cDrive
       FILEPATH(<cFullFileName>) --> cPath
       FILENAME(<cFullFileName>) --> cName
       FILEEXT(<cFullFileName>) --> cExtension

   Arguments:

       <cFullFileName> is a full file name to split.

   Returns:

       FILE*() returns one of four <cFullFileName> components.

   Example:

       c:\database>nfmysql database table table.sql
       c:\mysql\bin>mysql -h127.0.0.1 -uroot p""
       mysql> \. c:/database/table.sql
 */

#include "sixcdx.ch"
#include "inkey.ch"
#include "Directry.ch"

#define   CR      chr(13)+chr(10)

REQUEST HARDCR
REQUEST MEMOTRAN
REQUEST STRTRAN

function main(pDbf,pTbl,pSql)
    
    // structure info
    public cName     := ""
    public cType     := ""
    public nLen      := 0
    public nDec      := 0
    
    // splitpath file name
    public cFname    := ""
    
    // # of CDX TAGS
    public nCdx      := 0
    public nTagCount := 0 
    public aTagInfo  := {}      
    public aTags     := {}      
    
    // array for dbf structure
    public aStru     := {}
    
    // output file handle
    public nHandle
    
    // temp var for fwrite
    public cTmp      := ""
    
    // input parameters database & test file
    public cDbf      := ""
    public cTbl      := ""
    public cSql      := ""
    
    REQUEST DBFCDX
    RDDSetDefault("DBFCDX")
        DBSetDriver("DBFCDX")
    cls
    
    // make sure correct amount of parameters entered
    if( pcount() !=3 )
        qout()
        qout("Syntax <database> <tabela> <mySqlMakeFile>")    
        qout()
        qout("Exemplo:")    
        qout()
        qout("DBF2SQL MYSQL CLIENTES CLIENTES.SQL")    
        qout()
        quit
    endif    
    
    // put parameter values into public variables
    cDbf   := pDbf
    cTbl   := pTbl
    cSql   := pSql
    //cFname := b_spltpath(cTbl,3)
    cFname := FILENAME(cTbl)
    
    // see if dbf exists
    if( !file(cTbl+".DBF") )
        qout()
        qout(cDbf+" not found!")
        qout()
        quit
    endif
    
    // open dbf
    use &cTbl shared via "SIXCDX" alias dFile
    //    use &cTbl shared alias dFile
    if file(cTbl+".cdx")
       set index to &cTbl
    endif
    
    // check for open error    
    if( neterr() )
        qout()
        qout("Sharing violation opening "+cTbl)
        qout()
        quit
    endif    
    
    // six driver functions
    nCdx      := sx_IndexCount()
    nTagCount := Sx_TagCount() 
    aTagInfo  := Sx_TagInfo()      
    aTags     := Sx_Tags()      
    
    // put dbf structure into array
    aStru := dFile->(dbstruct())
    
    // create output file
    if( (nHandle := fcreate(cSql,0)) == -1 )
        qout()
        qout("Unable to create "+cSql)
        qout()
        quit
    endif
    
    // log (error) file
    cTmp := "# hbADOx Dump 1.1"+CR+;
            "#"+CR+;
            "# Host: 127.0.0.1 Database: "+lower(cDbf)+CR+;
            "#--------------------------------------------------------"+CR+;
            "# Server version 4.1.15"+CR+CR
    fwrite(nHandle,cTmp,len(cTmp))
    
    // select database
    cTmp := "USE "+lower(cDbf)+";"+CR+CR
    fwrite(nHandle,cTmp,len(cTmp))
    
    cTmp := "#"+CR+;
            "# Estrutura da Tabela '"+lower(cTbl)+"'"+CR+;
            "#"+CR+CR
    fwrite(nHandle,cTmp,len(cTmp))

    // erase table if it already exists
    cTmp := "DROP TABLE IF EXISTS "+lower(cFname)+";"+CR
    fwrite(nHandle,cTmp,len(cTmp))
    
    // mySql command
    cTmp := "CREATE TABLE IF NOT EXISTS "+lower(cFname)+"("+CR
    fwrite(nHandle,cTmp,len(cTmp))
    
    // record numbers auto increment & primary key
    cTmp := "RECNO INT NOT NULL AUTO_INCREMENT,"+CR    
    fwrite(nHandle,cTmp,len(cTmp))
    
    // figure out what we have here
    for i = 1 to len(aStru)
        cName := lower(aStru[i][1])
        cType := aStru[i][2]
        nLen  := aStru[i][3]
        nDec  := aStru[i][4]
        do case
            case cType == "C"
                 cTmp := " "+cName+" CHAR("+alltrim(str(nLen)) +")"            
                 fwrite(nHandle,cTmp,len(cTmp))
            case cType == "M"
                 cTmp := " "+cName+" TEXT"            
                 fwrite(nHandle,cTmp,len(cTmp))
            case cType == "L"
                 cTmp := " "+cName+" TINYINT(1)"            
                 fwrite(nHandle,cTmp,len(cTmp))
            case cType == "D"
                 cTmp := " "+cName+" DATE"            
                 fwrite(nHandle,cTmp,len(cTmp))
            case cType == "N"
                 if( nDec == 0 )
                     cTmp := " "+cName+" INT("+alltrim(str(nLen))+")"
                     fwrite(nHandle,cTmp,len(cTmp))
                 else
                     cTmp := " "+cName+" DOUBLE("+alltrim(str(nLen))+","+alltrim(str(nDec))+")"            
                     fwrite(nHandle,cTmp,len(cTmp))
                 endif
        endcase        
        // see if this is an index key
        cTmp  := isKeyFld(cName,i)+","+CR
        fwrite(nHandle,cTmp,len(cTmp))
    next        
    
    // create indexes
    if( nCdx > 0 )
      for i := 1 to nTagCount    
          // replace + with ,
          cExp := strtran(aTagInfo[i,2],chr(43),chr(44))
          cTmp := "INDEX "+aTagInfo[i,1]+"( "+alltrim(cExp)+" ),"+CR
          fwrite(nHandle,cTmp,len(cTmp))
      next    
    endif  
    
    // make record number the primary key
    cTmp := "PRIMARY KEY(RECNO) );"+CR+CR
    fwrite(nHandle,cTmp,len(cTmp))
    
    cTmp := "#"+CR+;
            "# Inserindo Dados na Tabela '"+lower(cTbl)+"'"+CR+;
            "#"+CR+CR
    fwrite(nHandle,cTmp,len(cTmp))
    // lets write some data
    dFile->(dbgotop())
    nFields := fcount()
    
    do while( !dFile->(eof()) )
        // write to screen
        @0,0 say "Processing Record Number: "
        @0,26 say alltrim(tran(recno(),"999,999,999,999"))
        cMem := ""
        cTmp := ""
        cTmp := "INSERT INTO "+lower(cFname)+" VALUES("+alltrim(str(recno()))+","
        
        for i := 1 to nFields
            
            fVal  := fieldget(i)
            cType := type("FVAL")
            
            // check for float float
            if( cType == "N" )
                cLen = alltrim(str(fVal))
                if( rat(".",str(i)) > 0 )
                    nDec := len(substr(str(i), rat(".",str(i)) + 1))
                else
                    nDec := 0    
                endif
            endif    
            
            do case
                case  cType == "C"
                    cTmp += "'"+escape(fVal)+"'"
                case  cType == "M"
                    cMem := escape(fVal)
                    cTmp += "'"+cMem+"'"
                case  cType == "D"
                    cTmp += "'"+subs(dtos(fVal),1,4)
                    cTmp += subs(dtos(fVal),5,2)
                    cTmp += subs(dtos(fVal),7,2)+"'"
                case  cType == "L"
                    cTmp += iif(fVal,"1","0")
                case cType == "N"
                    if( nDec == 0 )
                        cTmp += cLen            
                    else
                        cTmp += cLen+"."+alltrim(str(nDec))
                    endi
            endcase
            
            //comma seperate fields
            if( i != nFields )
              cTmp += ','
            endif
        
        next
        
        // end of mySql create table statement
        cTmp += ");"+CR
        fwrite(nHandle,cTmp,len(cTmp))
        dFile->(dbskip())
    
    enddo    
    
    // quit mySql
    cTmp := ""
    fwrite(nHandle,cTmp,len(cTmp))
    
    fclose(nHandle)
    dbcloseall()
    qout()
    qout("Finished!!!")
    qout()
    quit

return(NIL)

// see if field is part of index key
function IsKeyFld(cName,nCt)
  local cRv := ""
  local j   := 0
  
  for j := 1 to nTagCount    
    if( cName $ aTagInfo[j,2] ) 
      cRv := " NOT NULL"
      exit
    endif
  next
  return(cRv)

// clean up memo fields
function escape(cText)
    local cStr := trim(cText)
    //  )
    cStr := strtran(cStr,chr(41),chr(32))
    //  (
    cStr := strtran(cStr,chr(40),chr(32))
    //  '
    cStr := strtran(cStr,chr(39),chr(32))
    //  "
    cStr := strtran(cStr,chr(34),chr(32))
    //  .
    cStr := strtran(cStr,chr(46),chr(32))
    //  \
    cStr := strtran(cStr,chr(92),chr(32))
    //