SQLite blob, another alternative

[harkon]

Previously, someone posted (I'm sorry but I don't remember who) a way to save binary data to SQLite (using the PB interface) by Base64 encoding the data into a plain string. The problem, of course, is that the result is at least 33% larger than the original.

Looking around I found some VB code for yEnc. The encoded string is marginally larger than the original. I adapted the code for PB as follows using the PB4.3 installer file as a test. The file is 14MB and reads encodes then decodes and writes in about 10 seconds on my machine. The size penalty is +1.55%. You can save the result as TEXT in the database then. This should work with any database that can save text. I would recommend though, that for larger files, things get done in blocks to keep from running out of memory. As well this should not be comiled to use Unicode as the data just gets conberted to 8 bit data.

Code: Select all

; yEncode / yDecode for use in encoding binaries for file storage
; for PB4.30
; Written by Harold Koenig
; Adapted from some VB6 code I found that was posted at the below URL;
;   http://www.vbforums.com/showthread.php?t=379143
; This does not adhere to the yEnc spec, as it does not accomodate line length'
;  or anything else. All this does is make sure there are no special characters
;  in the output string. This was adapted to store binary data in databases using
;  the native PB database interface.
;
; For Large files it makes sense to read and write in blocks as memory
;  use becomes problematical

EnableExplicit

Procedure.s yEncode(*encMemoryID.l)
  Define *OutBuffer.l
  Define InputSize.l
  Define Char.l
  Define Test.c
  Define StringPos.l
  Define OutString.s
  
  InputSize=MemorySize(*encMemoryID)
  ;allowing for a string twice as long as the input.
  ; this should allow for worst case where everything
  ; is a special character
  *OutBuffer=AllocateMemory(InputSize*2)
  
  For Char=0 To InputSize
    Test=PeekC(*encMemoryID+Char)
    Test=(Test+42)%256
    Select Test
      Case 0,13,10,61
        ;input was a special charcter
        PokeC(*OutBuffer+StringPos,61)
        StringPos=StringPos+1
        PokeC(*OutBuffer+StringPos,(Test+64)%256)
        StringPos=StringPos+1
      Default
        PokeC(*OutBuffer+StringPos,Test)
        Stringpos=StringPos+1
    EndSelect
  Next Char
  OutString=PeekS(*OutBuffer,StringPos-1)
  FreeMemory(*OutBuffer)
  ProcedureReturn OutString
EndProcedure

Procedure.l yDecode(InputString.s)
  Define *decMemoryID.l
  Define Char.l
  Define InputSize.l
  Define Test.c
  Define MemPos.l
  
  InputSize=Len(InputString)
  ;allocate at least as much memory as the input string
  ; this will shrink later
  *decMemoryID=AllocateMemory(InputSize)
  
  For Char=0 To InputSize
    Test=PeekC(@InputString+Char)
    If Test <> 61
      Test=((Test+256)-42)%256
      Else
      Char=Char+1
      Test=PeekC(@InputString+Char)
      Test=((Test+256)-64)%256
      Test=((Test+256)-42)%256
    EndIf
    PokeC(*decMemoryID+MemPos,Test)
    MemPos=MemPos+1
  Next Char 
  ;shrink memory to appropriate size
  *decMemoryID=ReAllocateMemory(*decMemoryID,MemPos-1)

  ProcedureReturn *decMemoryID
EndProcedure

; -----------------------------------------------------
;  this section is just to test the above procedures
; -----------------------------------------------------
;PB 4.3 install file was 14,987,720 bytes
#SourceDataFile="C:\Program Files\PureBasic\Projects\yEnc\PureBasic_4_30_x86_(tnpzex).exe"
#DestTextFile="C:\Program Files\PureBasic\Projects\yEnc\yEnc.txt"
#DestDataFile="C:\Program Files\PureBasic\Projects\yEnc\yDec.bin"

Define FileLen.l
Define *FileBuffer.l
Define yEncString.s
Define StartTime.l
Define StringLen.l

StartTime=ElapsedMilliseconds()

ReadFile(0,#SourceDataFile)
  FileLen=Lof(0)
  Debug "Input file is " + Str(Filelen) + " bytes long"
  *FileBuffer=AllocateMemory(FileLen)
  ReadData(0,*FileBuffer,FileLen)
CloseFile(0)

yEncString=yEncode(*FileBuffer)
StringLen=Len(yEncString)
Debug "Encoded string is " + Str(StringLen) + " bytes long"
Debug " - An increase of " + StrF(((StringLen-FileLen)/FileLen)*100,2) + "%"
Debug ""
FreeMemory(*FileBuffer)

*FileBuffer=yDecode(yEncString)
yEncString=""

CreateFile(0,#DestDataFile)
  WriteData(0,*FileBuffer,MemorySize(*FileBuffer))
CloseFile(0)

StartTime=(ElapsedMilliseconds()-StartTime)
Debug StrF(StartTime/1000,3) + " seconds to encode and decode file"
End

I hope someone will find this useful

[Kiffi]

@harkon: Great! Thanks!

Greetings ... Kiffi

[Trond]

You can also use PB's packer routines on the data before base64 encoding it.

[srod]

Code: Select all

For Char=0 To InputSize 

Should that not be

Code: Select all

For Char=0 To InputSize - 1

?

I apologise if not.

Thanks for sharing.

[DarkLord]

to be honest, saving a file as a blob isn't really worth the effort, you may aswell save the file in a folder with a reference to the file saved into a table, saves on database load and works alot quicker.

[rsts]

beg to differ, but having everything in one place is a definate positive. no worries about losing something among other advantages.

cheers

[bobobo]

yep ..

and think about sql which CAN handle with blobs.

[harkon]

Code: Select all

For Char=0 To InputSize 

Should that not be

Code: Select all

For Char=0 To InputSize - 1

?

I apologise if not.

Thanks for sharing.

Sorry, playing with nxreport and haven't had much time to cruise forums.

I had the code that way, but always ended up 2 bytes short. Not sure why either

[harkon]

I discovered you can shrink the file overhead even a bit more if in the procedure yEncode() you take out the 10 and 13 as special characters. SQLite doesn't really care if there's a carriage return or a line feed in the string so you only really need to treat a chr(0) and chr(61) as special.

Of course packing first and then converting will result in far more compact strings.