/*
ZE_SQLBACKUP - Backup de MySQL
José Quintas
*/

#include "josequintas.ch"
#include "inkey.ch"
#define MYSQL_MAX_RECBACKUP 25000

FUNCTION SQLBackup()

   LOCAL oTableList, cTable, oTableCreate, nHandle, nQtd, nLimitStart, nCont, nQtdFields, nQtdRec, cTxt
   LOCAL cFixSql, nKey := 0, nMySqlLimitRecBackup, nAtual
   LOCAL cnGERAL := ADOClass():New( AppConexao() )

   IF "josequintas" $ cnGeral:cn:ConnectionString()
      Errorsys_WriteErrorLog( "servidor errado" )
      RETURN NIL
   ENDIF
   Mensagem( "Fazendo backup da base MySql" )
   nHandle      := fCreate( "backup.sql" )
   cnGERAL:cSql := "SHOW TABLES;"
   oTableList   := cnGERAL:ExecuteCmd( cnGERAL:cSql )
   fWrite( nHandle, "SET autocommit=0;" + hb_eol() )
   fWrite( nHandle, "SET unique_checks=0;" + hb_eol() )
   fWrite( nHandle, "SET foreign_key_checks=0;" + hb_eol() )
   DO WHILE nKey != K_ESC .AND. ! oTableList:Eof()
      cTable       := oTableList:Fields( "tables_in_" + Lower( AppEmpresaApelido() ) ):Value
      IF "VIEW" $ Upper( cTable )
         //fWrite( "DROP VIEW IF EXISTS " + cTable ) + hb_Eol()
         //fWrite( nHandle, StrTran( oTableCreate:Fields( "Create View" ):Value, Chr(10), hb_Eol() ) + ";" )
         oTableList:MoveNext()
         LOOP
      ENDIF
      oTableCreate := cnGERAL:ExecuteCmd( "SHOW CREATE TABLE " + cTable )
      fWrite( nHandle, "DROP TABLE IF EXISTS " + cTable + ";" + hb_eol() )
      fWrite( nHandle, StrTran( oTableCreate:Fields( "Create Table" ):Value, Chr(10), hb_eol() ) + ";" )
      fWrite( nHandle, hb_eol() + hb_eol() )
      oTableCreate:Close()
      nQtd := ADORecCount( cTable )
      SayScroll( "Fazendo backup do MySql " + cTable + " (" + Ltrim( Transform( nQtd, PicVal(9,0) ) ) + ")" )
      fWrite( "LOCK TABLES `" + cTable + "` WRITE;" + hb_eol() )
      fWrite( nHandle, "ALTER TABLE `" + cTable + "` DISABLE KEYS;" + hb_eol() )
      // ATENCAO! Primeira coluna do MySQL é ZERO
      nLimitStart := 0
      nMySqlLimitRecBackup := iif( "XML" $ Upper( cTable ), Int( MYSQL_MAX_RECBACKUP / 5 ), MYSQL_MAX_RECBACKUP )
      GrafTempo( "Backup " + cTable )
      nAtual := 0
      DO WHILE nKey != K_ESC .AND. nLimitStart <= nQtd
         cnGERAL:cSql := "SELECT SQL_NO_CACHE * FROM `" + cTable + "` LIMIT " + ;
            iif( nLimitStart == 0, "", Ltrim( Str( nLimitStart ) ) + ", " ) + ;
            Ltrim( Str( nMySqlLimitRecBackup ) )
         cnGERAL:Execute()
         nQtdFields := cnGERAL:Rs:Fields:Count() - 1
         nQtdRec    := cnGERAL:Rs:RecordCount()
         cFixSql := "INSERT INTO `" + cTable + "` "
         IF .F. // com nomes de campos
            cFixSql += "( "
            FOR nCont = 0 TO nQtdFields
               cFixSql += "`" + cnGERAL:Rs:Fields( nCont ):Name + "`"
               IF nCont != nQtdFields
                  cFixSql += ", "
               ENDIF
            NEXT
            cFixSql += ") "
         ENDIF
         cFixSql += "VALUES "
         cFixSql += hb_eol()
         cTxt := ""
         DO WHILE nKey != K_ESC .AND. ! cnGERAL:Rs:Eof()
            Inkey()
            GrafTempo( nAtual++, nQtd )
            IF Len( cTxt ) == 0
               cTxt += cFixSql
            ENDIF
            cTxt += "( "
            FOR nCont = 0 TO nQtdFields
               cTxt += ValueSql( cnGERAL:Rs:Fields( nCont ):Value )
               IF nCont != nQtdFields
                  cTxt += ", "
               ENDIF
            NEXT
            cTxt += " )"
            nQtdRec -= 1
            IF Len( cTxt ) > MYSQL_MAX_CMDINSERT
               fWrite( nHandle, cTxt + " ;" + hb_eol() )
               cTxt := ""
            ENDIF
            IF Len( cTxt ) != 0 .AND. nQtdRec != 0
               cTxt += ", " + hb_eol()
            ENDIF
            cnGERAL:MoveNext()
         ENDDO
         cnGERAL:CloseRecordset()
         IF Len( cTxt ) != 0
            IF Right( cTxt, 4 ) == ", " + hb_eol()
               cTxt := Substr( cTxt, 1, Len( cTxt ) - 4 )
            ENDIF
            fWrite( nHandle, cTxt + ";" + hb_eol())
         ENDIF
         nLimitStart += nMySqlLimitRecBackup
      ENDDO
      fWrite( nHandle, "ALTER TABLE `" + cTable + "` ENABLE KEYS;" + hb_eol() )
      fWrite( nHandle, "UNLOCK TABLES;" + hb_eol() )
      oTableList:MoveNext()
   ENDDO
   fWrite( nHandle, "SET autocommit=1;" + hb_eol() )
   fWrite( nHandle, "SET unique_checks=1;" + hb_eol() )
   fWrite( nHandle, "SET foreign_key_checks=1;" + hb_eol() )
   fWrite( nHandle, "COMMIT" + hb_eol() )
   oTableList:Close()
   fClose( nHandle )

   RETURN NIL