en Documentación

Dolphin para MySQL

A continuación un acercamiento breve a lo que ofrece la clase Dolphin de Daniel García Gil, para la gestión de bases de datos MySQL.

Mayor información y descargas en https://bitbucket.org/danielgarciagil/tdolphin/downloads

CLASS TDolphinSrv

   CLASSDATA nQueryId
   CLASSDATA nServerId  INIT 1

   DATA bDecrypt       /*codeblock to evaluate in connect process (C Level), ;
                         to decrpty Host, User, Password and Database*/
   DATA bOnError       /*Custom manager error message
                         ( Self, nError, lInternal ) */
   DATA bOnBackUp      /*codeblock to evaluate in backup process*/

   DATA bOnRestore     /*codeblock to evaluate in restore process*/
   DATA bOnMultiQry    /*codeblock to evaluate for each Query in METHOD MultiQuery*/
   DATA bOnAfterQuery   /*codeblock to evaluate after execute a MySql Statement*/
#ifdef DEBUG
   DATA bDebug         /*codeblock to evaluate for each Query, ;
                         Arg cQuery, ProcName( 1 ), ProcLine( 1 )*/
#endif

   DATA cDBName        /*Data base selected*/
   DATA cPassword      /*Data contains the password for user*/
   DATA cHost          /*Host name, may be either a host name or an IP address */
   DATA cUser          /*DAta contains the user's MySQL login ID*/
   DATA cNameHost

   DATA cBuild     INIT "25-Jan-12 6:41:25 AM"

   DATA hMysql         /*MySQL connection handle*/
                       
   DATA lReConnect     
   
   DATA Cargo          /*For programmer use*/
                       
   DATA lError         /*Error detection switch*/
                  
   DATA nFlags         /*Client flags*/
   DATA nInternalError /*error manager, no come from MySQL*/
   DATA nPort          /*value is used as the port number for the TCP/IP connection*/
   
   DATA aQueries       /*Array queries actives*/
   
   METHOD New( cHost, cUser, cPassword, nPort, nFlags, bOnError, cDBName )
   METHOD ssl( cHost, cUser, cPassword, nPort, nFlags, bOnError, cDBName, cSslKeyFile, ;
                                      cSslCertFile, cSslCaFile, cCertsPath, cSslCipher )
   
   METHOD AddUser( cHost, cUser, cPassword, cDb, lCreateDB, acPrivilegs, cWithOption )
                              /*The AddUser() enables system administrators to grant privileges ;
                                to MySQL user accounts. 
                                AddUser also serves to specify other account characteristics such ;
                                as use of secure connections and limits on access to server resources. 
                                To use AddUser(), you must have the GRANT OPTION privilege, 
                                and you must have the privileges that you are granting.*/
                                
   METHOD AddQuery( oQuery )          INLINE AAdd( ::aQueries, oQuery )
                              /*used internally*/

   METHOD Backup( aTables, cFile, lDrop, lOver, nStep, cHeader, cFooter, lCancel )

   METHOD BeginTransaction()          INLINE ::SqlQuery( "BEGIN" )    
   
   METHOD Call( /*...*/ )  /*run a function/procedure with n parameters, 
                             1st parameter must be a function/procedure name,
                             this method does not return any result in query */

   METHOD Debug( cText )     INLINE  If(  ::bDebug != NIL, Eval( ::bDebug, cText, ;
                                                  ProcName( 1 ), ProcLine( 1 ) ), )
   METHOD ReturnCall( /*...*/ ) /*same METHOD Call but this return a result set*/
   
   METHOD ChangeEngine( cTable, cType )  INLINE ::SqlQuery( "ALTER TABLE " + D_LowerCase( cTable ) + ;
                                                             " ENGINE = " + D_LowerCase( cType ) )

   METHOD ChangeEngineAll( cType )  
   
   METHOD CheckError( nError )

   METHOD CloseQuery( nId )
   
   METHOD CloseAllQuery()             

   METHOD Compact( cTable )
 
   METHOD Connect( cHost, cUser, cPassword, nPort, nFlags, cDBName )    
                     /*to establish a connection to a MySQL database engine running on server*/

   METHOD SSLConnect( cHost, cUser, cPassword, nPort, nFlags, cDBName, cSslCaFile, ;
                                             cSslCertFile, cSslKeyFile, cSslCipher )


   METHOD CommitTransaction()       INLINE ::Debug( "COMMITED" ),  MySqlCommit( ::hMySql ) == 0 
                              /*Commits the current transaction.*/

   METHOD CreateForeign( cName, cTabParent, aIndColName, cTabChild, aIndColRef, ;
                         lOnDelete, nActionDelete, lOnUpdate, nActionUpdate ) 
                              /* Create Foreign Key cName Symbol Name */
   
   METHOD CreateIndex( cName, cTable, aFNames, nCons, nType )                              
   
   METHOD CreateInfo( cTable )
                              
   METHOD CreateTable( cTable, aStruct, cPrimaryKey, cUniqueKey, cAuto, cExtra )
                              /*creates a table with the cTable name*/

   METHOD DBCreate( cName, lIfNotExist, cCharSet, cCollate )
                              /* Create Database in current active connection*/

                                
   METHOD DBExist( cDB )  INLINE If( ! Empty( cDB ), Len( ::ListDBs( D_LowerCase( cDB ) ) ) > 0, .F. )
                              /* verify is Data Base exist, return logical value*/

   METHOD DeleteDB( cDB, lExists )
                              /*Delete Tables*/
                              
   METHOD DeleteForeign( cName, cTable ) 
                              /*Delete Foreign*/

   METHOD DeleteIndex( cName, cTable )         
                              /*Delete Index*/                     
   
   METHOD DeleteTables( acTable, lExists )
                              /*Delete Tables*/
                              
   METHOD DropUser( cUser )             INLINE ::SqlQuery( "DROP USER " + cUser )
                              /*Drop User*/
   
   METHOD Embedded( cDataBase, aOptions, aGroups )
   
   METHOD End()
   
   METHOD ErrorTxt()          INLINE  If( ::hMysql != NIL, MySqlError( ::hMysql ), "" )
                              /* Returns a string containing the error message for 
                                 the most recently invoked API function that failed.*/
   
   METHOD ErrorNo()           INLINE ::lError := .F., MySqlGetErrNo( ::hMysql )
                              /* Returns the error code for the most recently invoked 
                                API function that can succeed or fail. 
                                A return value of zero means that no error occurred.*/
                                
   METHOD Execute( cQuery, uParams )   INLINE ::SqlQuery( cQuery, uParams )
   
   METHOD ExecuteScript( cFile ) 

   METHOD GetAutoIncrement( cTable )
                                /*Retrieve next Auto increment value in specified table;
                                 in current database selected*/   

   METHOD GetEngine( cTable, cSchema )
      
   METHOD GetServerInfo()       INLINE If( ::hMysql != NIL, MyServerInfo( ::hMysql ), "" ) 
                                /*Returns a string that represents the server version number.*/

   
   METHOD GetClientInfo()       INLINE If( ::hMysql != NIL, MyClientInfo(), "" ) 
                           /*Return a string that represents the MySQL client library version.*/

   
   METHOD GetPrivileges()
   
   METHOD GetQueryId()   
   

   METHOD GetRowsFromTable( cTable )
                               /*Retrieve total row avalaible in  specified table;
                                in current database selected*/    

   METHOD GetRowsFromQry( oQuery )
                               /*Retrieve total row avalaible in  specified query;
                                in current database selected*/    
   
   METHOD hInsert( ctable, hValues, cDuplicateKey )
   
   METHOD Insert( cTable, aColumns, aValues, cDuplicateKey )
                              /*inserts new rows into an existing table.*/
                              
   METHOD InsertFromDbf( cTable, cAlias, nLimit, aStruct, bOnInsert, cDuplicateKey ) 
                              /*insert new rows into an existing table from DBF file,
                                the table should be contain same fieldname that DBF */

   METHOD IsAutoIncrement( cField, cTable )
                              /* Verify is a field is Auto Increment*/
   
   METHOD LastDownData( cTable, cCol, uDef )
   
   METHOD LastInsertID()      /*Returns the first automatically generated value that was 
                                set for an AUTO_INCREMENT column by the most recently 
                                executed INSERT statement to affect such a column.*/
   
   METHOD ListDBs( cWild )    /* Returns a array set consisting of database names on the server 
                                 that match the simple regular expression specified by the wild  
                                 parameter.wild may contain the wildcard characters n++%n++ or 
                                 n++_n++, or may be a "" to match all databases.*/
   
   METHOD ListTables( cWild ) /* Returns a array set consisting of tables names in current satabase 
                                 that match the simple regular expression specified by the wild 
                                 parameter. wild may contain the wildcard characters n++%n++ 
                                 or n++_n++, or may be a "" to match all tables.*/
                                 
   METHOD MultiQuery( aQuery, lTransaction )
   
   METHOD NextResult() INLINE mysql_next_result( ::hMysql )
                               /* Use only for MULTIPLE STATEMENT or stored PROCEDURE/FUNCTION */   
   
   METHOD Ping()       INLINE If( MySqlPing( ::hMysql ) > 0, ( ::CheckError(), .F.), .T. )
                   /* Checks whether the connection to the server is working. 
                      If the connection has gone down and auto-reconnect is enabled an attempt 
                      to reconnect is made. If the connection is down and auto-reconnect is disabled,
                      ::ping() returns an error.*/

   METHOD Query( cQuery )   

   METHOD ReConnect()

   METHOD RenameUser( cFromUser, cServer, cRename )
                              /*Rename User*/
                              
   METHOD Restore( cFile, lCancel )                              

   METHOD RevokePrivileges( cHost, cUser, cDB, acPrivilegs )
                              /*The RevokePrivileges() enables system administrators 
                                              to revoke privileges from MySQL accounts.*/
   

   METHOD RollBack()        INLINE ::Debug( "ROLLBACK" ), MySqlRollBack( ::hMysql )
                              /* Rolls back the current transaction.*/
                             
   METHOD SelectDB( cDBName ) 
                              /*Select data base in current active connection*/
   

   METHOD SelectTable( aColumns, aTables, cWhere, cGroup, cHaving, cOrder, cLimit, lWithRoll )
   
   METHOD SetNameServer( cName )
   
   METHOD SetMultiStatement( lOnOf ) INLINE SetMultiStatement( ::hMysql, lOnOf )
   
   METHOD SqlQuery( cQuery, uParams )  /*Executes the SQL statement pointed to by cQuery, 
                              Normally, the string must consist of a single SQL statement 
                              and you should not add a terminating semicolon (n++;n++) 
                              or \g to the statement. If multiple-statement execution has 
                              been enabled, the string can contain several statements 
                              separated by semicolons.*/
 
   METHOD TableExist( cTable )  INLINE If( ! Empty( cTable ), ;
                                       Len( ::ListTables( D_LowerCase( cTable ) ) ) > 0, .F. )
                              /* verify is table exist, return logical value*/ 

   METHOD TableInitValues( cTable )
   
   METHOD TableStructure( cTable )  
   
   METHOD hUpdate( cTable, hValues, cWhere ) 
                             /*update specific rows into an existing table from a hash, ;
                               the index of hash shold be field name.*/
                             
   METHOD Update( cTable, aColumns, aValues, cWhere )
                             /*update specific row into an existing table.*/
                                 
ENDCLASS



CLASS TDolphinQry

   DATA aColumns,;    // query active columns (select)
        aTables,;     // query active tables
        aRow,;        // info currect record selected
        aStructure    // type of each field, a copy is here a copy inside each row
   DATA aOldRow       // Value copy
   DATA aRowOriginal  // Original data values (without changes, like return from mysql)

   DATA bBof,;        //codeblock to evaluate if the value is the first row
        bEof,;        //codeblock to evaluate if the value is the last row
        bOnFillArray,;//codeblock to evaluate while is filling array
        bOnChangePage,; //codeblock to evaluate when paginmation is activated and change page
        bOnLoadQuery,;  //codeblock to evaluate before load new Query
        bOnNewFilter   //codeblock to evaluate before set new query, 
                       //should return .t./.f. to call BuildQuery

   DATA cQuery,;        // copy of query that generated this object
        cWhere,;        // copy of WHERE command
        cGroup,;        // copy of GROUP BY command
        cHaving,;       // copy of HAVING command
        cOrder,;        // copy of OREDER BY command
        cLimit          // copy of LIMIT command

   DATA Cargo           // For programmer use
   
   DATA hOldRow                // Hash Last row selected
   DATA hResult                 
   DATA hRow                   // Hash current row selected

   DATA lBof                   // Begin of query, compatibility with dbf*/
   DATA lEof                   // End of Query, compatibility with dbf*/
   DATA lAppend
   DATA lPagination
   DATA lInverted              // Seek in inverted order

   DATA nFCount                // number of fields in the query
   DATA nRecCount              // number of rows in the current query
   DATA nRecNo                 // Current query row position
   DATA nQryId
   
   
   //Paginations datas
   DATA nCurrentPage           // Current page
   DATA nTotalRows             // Total row without limits
   DATA nPageStep              // total rows for page
   DATA nMaxPages              // Max pages avalaible in query
   DATA nCurrentLimit          // Current limit value
   
   DATA oServer
   DATA oRow


   METHOD New( cQuery, oServer )
   METHOD End()        INLINE ::oServer:CloseQuery( ::nQryId ), ::oRow := NIL

   METHOD Bof()        INLINE ::lBof  
   
   METHOD BuildDatas( cQuery ) 
   METHOD BuildDataWhere()     /* build a where with oldrow values */
   
   METHOD BuildQuery( aColumns, aTables, cWhere, cGroup, cHaving, ;
                      cOrder, cLimit, lWithRoll )   
                      
   METHOD CheckError( nError, cExtra )  INLINE ::oServer:CheckError( nError, cExtra )
                               /*Compatibility with CheckError from TDolphinSrv*/

   METHOD Delete( lAll )       /*Delete current record active*/
#ifdef __WIN__
   METHOD Export( nType, cFieldName, aColumns, aPictures )   ;
                INLINE TDolphinExport():New( nType, Self, cFieldName, aColumns, aPictures )
#endif __WIN__
   METHOD Eof()        INLINE ::lEof  
                      
   METHOD FCount()     INLINE    ::nFCount
                        /*returns the number of fields in the query, compatibility with dbf*/

   METHOD FieldLen( cnField )
   METHOD FieldDec( cnField )

   METHOD FieldName( nNum )     /*returns the name of the specified field as a character string.*/
   METHOD FieldPos( cFieldName )
                                /*returns the position of the specified field*/
   METHOD FieldGet( cnField )   /*returns the value of the specified field*/
   METHOD FieldType( cnField )  /*returns the field type of the specified field*/
   METHOD FieldMySqlType( cnField ) 
                                /*returns the MySql field type of the specified field*/
                                
   METHOD FieldPut( cnField, uValue )
                         /*Set the value of a field variable using the ordinal position of the field.
                                 returns the value assigned to the designated field.*/  
                                  
   METHOD FieldToNum( cnField ) HIDDEN                                                            

   METHOD FillArray( bOnFillArray, aColumns ) /*Fill and return a array with all query information*/
   METHOD FillHRow( lEmpty )          /*Fill (or not) and return a Hash with current record selected*/

   METHOD FirstPage()   INLINE ::PrevPage( ::nCurrentPage - 1 )
                                /*Go to first page in pagination*/
   METHOD Find( aValues, aFields, nStart, nEnd, lRefresh )                                
   
   METHOD GetBlankRow()                       
   METHOD GetRow( nRow )        /*Fill aRow and Hash with current data row selected*/
   METHOD GoTo( nRow ) INLINE   ::GetRow( nRow )
                                /*Goto specific Row (RecNo) and fill aRow/Hash*/
                                
   METHOD GetFieldsModified()   /*Return a Array with fields modified*/                             

   METHOD GetRowObj( nRow )     /*Return TDolphinRow Object*/
                                
   METHOD GoBottom()   INLINE   ::GetRow( ::nRecCount  ) 
                                /*Goto BOTTOM of Query and fill aRow/Hash*/
                                
   METHOD GoTop()      INLINE   ::GetRow( 1 )
                                /*Goto TOP of Query and fill aRow/Hash*/
                                
   METHOD IsEqual( nIdx )                                

   METHOD IsSingleTable() INLINE Len( ::aTables ) == 1  
   METHOD IsCommand()     INLINE ( ::IsSingleTable() .AND. Len( ::aColumns ) == 0 ) .OR. ;
                                 ( Len( ::aTables ) < 1 .AND. Len( ::aColumns ) >= 1 )


   METHOD LastPage() INLINE ::NextPage( ::nMaxPages - ::nCurrentPage )
                               /*Go to Last page in pagination*/

                                  
   METHOD LastRec()    INLINE     ::nRecCount
                   /*returns the number of rows in the current query, compatibility with dbf*/

   METHOD LoadQuery()          /*Load and fill current query*/
   METHOD LoadNextQuery( lBuildData ) 
                               /*Load next result with multiple statement*/
   
   METHOD Locate( aValues, aFields, nStart, nEnd, lSoft, lRefresh )

   METHOD MakePrimaryKeyWhere() 
                               /*Build Make Primary key if exist*/
   
   METHOD GoToPage( nPage ) INLINE If( nPage > ::nCurrentPage, ;
                            ::NextPage( nPage - ::nCurrentPage ), ;
                            If( nPage < ::nCurrentPage, ;
                            ::PrevPage( ::nCurrentPage - nPage ), ) )
       
   METHOD NextPage( nSkip )    /* Go to next page avalaible with pagination active */
   
   METHOD PrevPage( nSkip )    /* Go to previous page avalaible with pagination active */

   METHOD RecNo()      INLINE    ::nRecNo
                               /*returns the identity found at the position of the row pointer.*/
                               
   METHOD RecCount()   INLINE ::LastRec()
                               /*Compatibility with TMysql*/
                               
   METHOD Refresh( lBuildData )    
   
   METHOD Save()               /*Save current data*/
                 
   METHOD Seek( cSeek, cnField, nStart, nEnd, lSoft ) 
                               /*Move to the record having the specified 
                                 cSeek value, in selected field
                                 from nStart to nEnd with SoftSeek*/
                 
   METHOD SetData( nNum, uValue ) HIDDEN
                               /*set value into array or hash*/
   
   METHOD SetNewFilter( nType, cFilter, lRefresh )
   METHOD SetWhere( cWhere, lRefresh )   INLINE ::SetNewFilter( SET_WHERE, cWhere, lRefresh )
   METHOD SetGroup( cGroup, lRefresh )   INLINE ::SetNewFilter( SET_GROUP, cGroup, lRefresh )
   METHOD SetHaving( cHaving, lRefresh ) INLINE ::SetNewFilter( SET_HAVING, cHaving, lRefresh )
   METHOD SetOrder( cOrder, lRefresh )   INLINE ::SetNewFilter( SET_ORDER, cOrder, lRefresh )
   METHOD SetLimit( cLimit, lRefresh )   INLINE ::SetNewFilter( SET_LIMIT, cLimit, lRefresh )
   
   METHOD SetPages( nLimit )   /*Activate pagination and Set total rows by page*/
  
   METHOD Skip( nRecords )
   
   METHOD VerifyValue( nIdx, cField ) //HIDDEN
   
   METHOD Undo( cnField )

   METHOD Zap() INLINE ::Delete( .T. )
                               /*Delete all record in table*/
   
   ERROR HANDLER ONERROR()   

ENDCLASS


//Main class to manager exports
CLASS TDolphinExport

    DATA oQuery        /* Query object */
    DATA aColumns      /* Columns seelct to fill file */
    DATA aPictures     /* Columns's Picture */

    DATA cFileName     /* File name */
    DATA hFile
    DATA lAddHeader

    DATA nType         /* Export type */
    DATA bOnRow        /* codeblock to evaluate row by row */
    DATA bOnStart      /* codeblock to evaluate at the begin process */
    DATA bOnEnd        /* codeblock to evaluate at the end process */
    DATA oExport       /* Export Objet, is direftent by type */
    DATA oMain         /* Self */


    METHOD New( nType, oQuery, aColumns, aPictures  )

    METHOD Start() INLINE ::oExport:Export()
    METHOD Write( cLine, lNoCRLF )
    METHOD Close()  INLINE FClose( ::hFile )

    ERROR HANDLER ONERROR()


ENDCLASS