Need an Advice about Memo fields usage in MariaDB.

Need an Advice about Memo fields usage in MariaDB.

Postby Horizon » Sun Apr 12, 2020 9:18 pm

Hi,

I am try to convert my application Dbf to MariaDB. I was using external MEMO.dbf because of corruption of memo field in dbf.

My customers enters huge of data in my app. There is about 6 memo fields in my record. When I converting MariaDB, I need an advice.

Should I transfer my data again memo fields in my record or should I change DBFMEMO.prg to use MariaDB?

Thanks in advance.

DBFMEMO.prg
Code: Select all  Expand view

#include "FiveWin.ch"

*+--------------------------------------------------------------------
*+
*+    Source Module => DBFMEMO.PRG
*+
*+    Functions: Function TESTMEMO()
*+               Function B_DBFMEMO()
*+               Static Function B_ADDREC()
*+               Static Function B_DELREC()
*+               Static Function B_DEL_REC()
*+
*+       Tables: use memo alias memo shared
*+
*+      Indexes: index on TYPE + CODE + SEQUENCE to memo
*+
*+    Reformatted by Click! 1.08 on Jun-18-1997 at 11:59 pm
*+
*+--------------------------------------------------------------------

/***
    DBFMEMO.PRG - allows the storage of "memo" fields in standard DBF files.
    You can use a SINGLE memo file to service multiple DBF files.

    * Store memotext in only one memofile (+1 indexfile).
    * Every database uses the same memofile (less file handles).
    * No memofields needed in DBF file.
    * Automatic recycling of unused space => no memofile bloat!
    * Every database record can have several memofields.
    * Even memotext not related to a database, can also stored.
    * 100% clipper code.

  Uploaded by: cautere@innet.be (Jos Cautereels)

  Comments:

    Requires a DBF file with the following structure:
      NAME        TYPE LEN DEC
      ----------- ---- --- ---
      TYPE        C      2
      CODE        C     10
      SEQUENCE    C      1
      TEXTLINE    C     64
    ...and an index on TYPE+CODE+SEQUENCE

    The files have to be open for the function to work (duh!)

       This function currently supports memos up to 256 64-byte lines long
       (16 kb).  This limitation can be overcome by simply increasing the size
       of SEQUENCE to 2 bytes and modifying the corresponding code to use
       I2BIN() and BIN2I() to store the line counter.
       Another way to increase the maximum memo size would be to increase
       TEXTLINE.  You can optimize the memos any way you want like this.

       The cType parameter to B_DBFMEMO() allows you to segregate memos from
       different files, fields, etc..  This way you can use a SINGLE memo file
       to service multiple DBF files.  For example suppose file A contains 3
       memo fields and file B contains 2, you could Type them like this:
          "A1", "A2", "A3", "B1", "B2"

          To store a memo in file A for memo field 2 for record ID "000123":
             lSuccess:=B_DBFMEMO( MEMOSAVE, "A2", "000123", "THIS IS A MEMO" )

          To retrieve the same memo:
             cMemo:=B_DBFMEMO( MEMOGRAB, "A2", "000123" )

          To delete the same memo:
             lSuccess:=B_DBFMEMO( MEMODELETE, "A2", "000123" )

       If you have a file that does NOT contain a unique key field, you will
       probably have to use some sort of memo-id scheme.  Go to bottom of memo
       file, increment last memo->code used or maintain a last_memo field in a
       configuration file and increment it whenever a memo is added.  Either
       way you will probably want to write a function for adding memos that
       does this work for you.

       Please note, if your application allows the user to change the key field,
       the corresponding memo->code field will also have to be changed for all
       memo records associated with that key field.
*/


#include "common.ch"
#translate B_RLOCK()  => rlock()  // use your own record-lock function
                                  //  with error message.

// DBF-MEMO DEFINES - Place these in an application-wide header file
#define MEMOSAVE    1
#define MEMODELETE  2
#define MEMOGRAB    3
#define MEMOSIL   4
#define MEMOALIAS   memo

#ifdef TEST

*+--------------------------------------------------------------------
*+
*+    Function TESTMEMO()
*+
*+--------------------------------------------------------------------
*+
function TESTMEMO

field TYPE
field CODE
field SEQUENCE
field TEXTLINE
local cMemo
local lSuccess

altd()
use memo alias memo shared
index on TYPE + CODE + SEQUENCE to memo

//  To store a memo in file A for memo field 2 for record ID "000123":
lSuccess := B_DBFMEMO( MEMOSAVE, "A2", "000123", replic( "THIS IS A MEMO. ", 50 ) )
? lSuccess

//  To retrieve the same memo:
cMemo := B_DBFMEMO( MEMOGRAB, "A2", "000123" )
? cMemo

//  To delete the same memo:
lSuccess := B_DBFMEMO( MEMODELETE, "A2", "000123" )

// Test if slot is empty
cMemo := "xxx"
cMemo := B_DBFMEMO( MEMOGRAB, "A2", "000123" )
? empty( cMemo )    // .T.

// Empty space reused
lSuccess := B_DBFMEMO( MEMOSAVE, "A5", "000123", replic( "Recycling !!! ", 25 ) )

return nil
#endif

*+--------------------------------------------------------------------
*+
*+    Function B_DBFMEMO()
*+
*+    Called from ( dbfmemo.prg  )   5 - function testmemo()
*+
*+--------------------------------------------------------------------
*+
function B_DBFMEMO( nMode, cType, cCode, cText, lYENI )

/* B_DBFMEMO(nMode,cType,cCode,[cText]) --> ?
   nMode is MEMOSAVE, MEMODELETE, MEMOGRAB
   cType is a 2 char attribute that allows you to seperate types of memos
   cCode is a 10 char attribute that points to the individual memo

   Returns: if MEMOSAVE or MEMODELETE lSuccess
            if MEMOGRAB cMemoText
*/

local xReturn
local lOk       := .T.
local nSequence := 1
local nDelRecNo
local nNextRec

IF VALTYPE(lYENI) = "U"
    lYENI := .F.
ENDIF

// ? cCode, lYENI, MEMOSAVE, cText

    //? LYENI
    //WAIT "İÇERDE"

cType := padr( cType, 2 )
cCode := padr( cCode, 10 )
//? cType, cCode, nMode
do case
case nMode == MEMOSAVE
   if ( cText <> NIL )
      // Find first record of existing memo and replace as long as
      // slots exist for this type and code
//      ? cText, cCode, cType + cCode + Mydectohex( 1, 3), "YAZMAYA GELDİM"
            IF lYENI
          MEMOALIAS->( dbseek( cType + cCode + Mydectohex( 1, 3) ) )
            ELSE
          MEMOALIAS->( dbseek( cType + cCode + chr( 1 ) ) )
        ENDIF
      while MEMOALIAS->type == cType .and. ;
            MEMOALIAS->code == cCode .and. ;
            MEMOALIAS->( !eof() ) .and. ;
            !empty( cText ) .and. lOk

         if ( lOk := MEMOALIAS->( B_RLOCK() ) )
                        IF lYENI
                     MEMOALIAS->sequence := Mydectohex( nSequence, 3)
                        ELSE
                   MEMOALIAS->sequence := chr( nSequence )
                        ENDIF
                MEMOALIAS->textline := substr( cText, 1, 64 )
                        IF lYENI
                    lOk                 := ( ++ nSequence <= 4095 )
                        ELSE
                    lOk                 := ( ++ nSequence <= 254 )
                        ENDIF
              cText                 := substr( cText, 65 )
              MEMOALIAS->( dbunlock() )
                        MEMOALIAS->( dbskip() )
         end
      enddo


      // We are either going to add new slots or delete existing
      // unused slots.
      IF !empty( cText ) .and. lOk
         // Add new slots for this type and code
         while !empty( cText ) .and. lOk
            if ( lOk := MEMOALIAS->( B_ADDREC() ) )
              MEMOALIAS->type     := cType
                MEMOALIAS->code     := cCode
                            IF lYENI
                MEMOALIAS->sequence := Mydectohex( nSequence, 3 )
                            ELSE
                MEMOALIAS->sequence := chr( nSequence )
                            ENDIF
                  MEMOALIAS->textline := substr( cText, 1, 64 )
                            IF lYENI
                lOk                 := ( ++ nSequence <= 4095 )
                            ELSE
                    lOk                 := ( ++ nSequence <= 254 )
                            ENDIF
              cText               := substr( cText, 65 )
                MEMOALIAS->( dbunlock() )
            end
         enddo
      else
         // Delete old unused slots for this type and code
         do while MEMOALIAS->type + MEMOALIAS->code == cType + cCode .and. ;
                 MEMOALIAS->( !eof() )
            nDelRecNo := MEMOALIAS->( recno() )
            MEMOALIAS->( dbskip() )
            nNextRec := MEMOALIAS->( recno() )
            MEMOALIAS->( dbgoto( nDelRecNo ) )
            MEMOALIAS->( B_DEL_REC() )
            MEMOALIAS->( dbgoto( nNextRec ) )
         enddo
         lOk := .t.
      end

   else
      lOk := .F.
   endif
   xReturn := lOk

case nMode == MEMODELETE
   xReturn := B_DELREC( cType + cCode )
   dbunlock()

case nMode == MEMOSIL
   xReturn := B_DELSREC( cType + cCode )
   dbunlock()

case nMode == MEMOGRAB
//? "BURDAYIM", cType, cCode
   xReturn := ""
   MEMOALIAS->( dbseek( cType + cCode ) )
//? FOUND()
   while MEMOALIAS->type == cType .and. ;
         MEMOALIAS->code == cCode .and. ;
         !MEMOALIAS->( eof() )
//? "GİRDİM"
      xReturn += MEMOALIAS->textline
      MEMOALIAS->( dbskip() )
   enddo
    IF LEN(xReturn) > 32000
        xReturn := LEFT(xReturn, 32000)
    ENDIF

//? "BİTTİ",xReturn
endcase

return xReturn

*+--------------------------------------------------------------------
*+
*+    Static Function B_ADDREC()
*+
*+    Called from ( dbfmemo.prg  )   1 - function b_dbfmemo()
*+
*+--------------------------------------------------------------------
*+
static function B_ADDREC()              // search empty record or make a new

if !dbseek( space( len( MEMOALIAS->type + MEMOALIAS->Code ) ), .f. )
   dbappend()
endif

return B_RLOCK()

*+--------------------------------------------------------------------
*+
*+    Static Function B_DELREC()
*+
*+    Called from ( dbfmemo.prg  )   1 - function b_dbfmemo()
*+
*+--------------------------------------------------------------------
*+
static function B_DELREC( cDelete )     // delete whole memo

do while dbseek( cDelete, .f. )
   B_DEL_REC()
enddo

return .t.

static function B_DELSREC( cDelete )     // delete whole memo

do while MEMOALIAS->(dbseek( cDelete, .f. ))
    if MEMOALIAS->(RLOCK())
    MEMOALIAS->(DBDELETE())
    endif
enddo

return .t.


*+--------------------------------------------------------------------
*+
*+    Static Function B_DEL_REC()
*+
*+    Called from ( dbfmemo.prg  )   1 - function b_dbfmemo()
*+                                   1 - static function b_delrec()
*+
*+--------------------------------------------------------------------
*+
static function B_DEL_REC()             // delete (=make empty) single record

if B_RLOCK()
   MEMOALIAS->Type     := ""
   MEMOALIAS->Code     := ""
   MEMOALIAS->sequence := ""
   MEMOALIAS->textline := ""
endif

return nil
/*
function nHex( cHex )

   local n, nChar, nResult := 0
   local nLen := Len( cHex )

   for n = 1 to nLen
      nChar = Asc( Upper( SubStr( cHex, n, 1 ) ) )
      nResult += ( ( nChar - If( nChar <= 57, 48, 55 ) ) * ( 16 ^ ( nLen - n ) ) )
   next

return nResult
*/

//----------------------------------------------------------------------------//

function MyDecToHex( nValue, nLen )

   local nMod, cDec := ""

   while nValue > 15
      nMod   = nValue % 16
      nValue = Int( nValue / 16)
      cDec   = HexVal( nMod ) + cDec
   end

   cDec = HexVal( nValue ) + cDec

    IF LEN(cDec) < nLen
        cDec := STRZERO(0,nLen-LEN(cDec),0)+cDec
    ENDIF

return cDec

//----------------------------------------------------------------------------//

static function HexVal( nValue )

   LOCAL cValue

   if nValue < 10
      cValue := Str( nValue, 1 )
   else
      cValue := Chr( 55 + nValue )
   endif

return cValue

//----------------------------------


*+ EOF: DBFMEMO.PRG
 
Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.04
Horizon
 
Posts: 1322
Joined: Fri May 23, 2008 1:33 pm

Re: Need an Advice about Memo fields usage in MariaDB.

Postby Adolfo » Wed Apr 15, 2020 3:46 pm

Hi
I don´t quite understand your problem, but if your migrating from DBF to MARIADB I support you 100%
I had 2 DBF's with memo fields, create them as TEXT fields in MARIADB, never ever had a problem, even though as you said, clients enter huge amount of data in them.

Some of my clients db's are more than 25 GB, including memo fields and blob fields with images inside.

Use Mariadb.

Greetings, from Chile
Adolfo
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
User avatar
Adolfo
 
Posts: 860
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile

Re: Need an Advice about Memo fields usage in MariaDB.

Postby Horizon » Wed Apr 15, 2020 5:21 pm

Adolfo wrote:Hi
I don´t quite understand your problem, but if your migrating from DBF to MARIADB I support you 100%
I had 2 DBF's with memo fields, create them as TEXT fields in MARIADB, never ever had a problem, even though as you said, clients enter huge amount of data in them.

Some of my clients db's are more than 25 GB, including memo fields and blob fields with images inside.

Use Mariadb.

Greetings, from Chile
Adolfo


Thank you Adolfo. I got the answer to the question I asked.
Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.04
Horizon
 
Posts: 1322
Joined: Fri May 23, 2008 1:33 pm

Re: Need an Advice about Memo fields usage in MariaDB.

Postby Antonio Mart. » Wed Apr 15, 2020 9:30 pm

Adolfo wrote:Hi
I don´t quite understand your problem, but if your migrating from DBF to MARIADB I support you 100%
I had 2 DBF's with memo fields, create them as TEXT fields in MARIADB, never ever had a problem, even though as you said, clients enter huge amount of data in them.

Some of my clients db's are more than 25 GB, including memo fields and blob fields with images inside.

Use Mariadb.

Greetings, from Chile
Adolfo


IMHO, images records on maria db slowing db access. Best option is record images on external files.
Antonio Mart.
 
Posts: 174
Joined: Sat Feb 23, 2013 10:04 am

Re: Need an Advice about Memo fields usage in MariaDB.

Postby nageswaragunupudi » Thu Apr 16, 2020 12:41 am

You can safely store large memos also in LONGTEXT fields. In case of binary data you can store in LONGBLOB fields.

Regarding the speed of retrieval, it is a good practice to exclude these fields in the SQL reading the data and access these fields only when needed.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Need an Advice about Memo fields usage in MariaDB.

Postby Adolfo » Thu Apr 16, 2020 4:44 pm

Antonio Mart. wrote:
Adolfo wrote:Hi
I don´t quite understand your problem, but if your migrating from DBF to MARIADB I support you 100%
I had 2 DBF's with memo fields, create them as TEXT fields in MARIADB, never ever had a problem, even though as you said, clients enter huge amount of data in them.

Some of my clients db's are more than 25 GB, including memo fields and blob fields with images inside.

Use Mariadb.

Greetings, from Chile
Adolfo


IMHO, images records on maria db slowing db access. Best option is record images on external files.


Antonio

I never put an image field in any query, If I need it I load it, so I do no have slow access to any of the queries I do.
In mi db design, I have some tables with 2 fields, CODE,IMAGE, so is very efficient and fast to find and load the image if I need it.
On the other hand, I have remote access to my DB, from different cities, there's no way I can have images in external files on a file server, I tried in public servers, SAMBA servers and the retrieval i slower than loading them from the MariaDb table

Just to clear it up.

Greetings, From Chile
Adolfo
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
User avatar
Adolfo
 
Posts: 860
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 38 guests

cron