SQLite ListView and BLOB demo [Closed]

[GreenCan]

Topic Closed, please go here SQLite demonstration of native recognition of BLOB object in Listview

Following an idea that was raised in a discussion with water and jchd, I have been working during the past week on a ListView SQLite demo that integrates BLOB (Binary large Object) fields.

Download link at the bottom of this post

This is what I have until now, version sero sero sero … one – to use the words of a well know Spanish biker . It is a fully workable example and I would be happy if you could test it and give me your comments.
Pre-requisites:
1. You have to download the SQLite Database here:
http://users.telenet.be/GreenCan/AutoIt/GreenCan_SQLite_Database_demo.zip

2. You have to download trancexx’ great GIFAnimation udf here:
GIFAnimation or http://code.google.com/p/gif-animation/downloads/list
For your comfort, I included the latest version in the above zip file.
I decided to use GIFAnimation.au3 for the Image display because it works with several kinds of graphic types (except multipage tiff) including animated GIFs J . GIFAnimation? What’s in a name…

3. SQLite3.dll (also included in the zip package)

In this first version, you will see two different icons in the Listview (as opposed to the screenshot where you see all images as icons)
1. Eye Icon: this is an image object, clicking once will open a preview box
2. Database with lightning arrow: any kind of other (non-image) object
A BLOB field doesn’t need to be an image, it can store literally everything you can imagine, the database contains several objects that you can try-out. I didn’t include an exe file, but why not? It is possible. Up to you to find out if it is useful.
These non-graph objects are not preview-able and will be executed.

By the way, all these BLOB examples are inoffensive, for example the autoit code just displays an ‘Hello World’ MsgBox. If you don’t trust it, just comment out the ShellExecute in line 219 and verify the content in the Temp folder… or pass your favorite virus scanner over these files
You will notice that the first execute take a bit more time to start, afterwards, everything is blinking fast. Not sure why this slow behavior, maybe it’s only on my PC.

Although the example looks good, I am not completely satisfied with the result. The BLOB object is not natively recognized and I think it should, but I don’t know how to do this. Any suggestion is welcome here…
I can use a isBinary() to identify the field as a BLOB, but I still don’t know how to recognize the BLOB as an image, or an Excel sheet, or anything else. So this is the reason why I have the TypeOfObject column, that links the object to the physical file. Again, any suggestion is welcome here…

Another issue is that I have to export the file to a physical file to be able to display (or use) it. I tried UEZ’s _GDIPlus_BMPFromMemory() function for the bitmaps but it is too slow (not even sure it can handle anything else than BMP. And slowness will increase with very large binary objects. I am wondering what the performance would be with a database containing several hundreds of objects to export.

I will come with version 0.0.0.2 within a few days. I just let you test the current version first. Version 2 has, as you can see in the screenshot above, a visible icon representation of the image object. This is how it should be but there are significant disadvantages in the way that I programmed it. But I will explain that when I publish the new example script.

SQLite GreenCan_demo BLOB in Listview 0.0.0.1.au3 (Please jump immediately to version 0.0.0.2 if you do not intend to test both examples)

Edit: 06 May 2013 New version!
Version 0.0.0.2
This enhanced version displays a visible thumbnail of each Database image object in the ListView. The example works with the database file that you have to download or already downloaded with the first version (see Prerequisite 1 higher in this post)
SQLite GreenCan_demo BLOB in Listview 0.0.0.2.au3

Edit: 10 May 2013

With this script, you can import your own objects into the GreenCan_demo.db Database (you have to download the database again from the link in Pre-requisite 1, because I modified the structure, the key column is now autoincremental)

The script will work for both demo's

Import Object in SQLite Database.zip

Anyhow, enjoy and please give me feedback.
GreenCan

Edited May 14, 2013 by GreenCan

[reybol7]

Helo sir, i got an error in my Win7 it says Query Failure, error 1 in a message box.

maybe the DB = GreenCan_demo.db is not yet created. just guessing.

Edit: ops

i forgot to down load the number 1 prerequisite .

now it work fine thanks for sharing .

Edited May 5, 2013 by reybol7

[GreenCan]

30 Downloads for version 0.0.0.1 and I got only 1 feedback? OK

Let’s hope that version 0.0.0.2 catches more attention…

==> for download see post 1

This enhanced version displays a visible thumbnail of each Database image object in the ListView (screenshot in post 1). It works for all graphic types (that I tested) except for Tiff formats. 2 issues:

• Converting .tiff to .bmp fails, Tiff will therefore show a generic eye thumbnail
• Multipage Tiff does not display in trancexx' udf but the ‘normal’ tiff will in the image viewer. I still use .tif for single page and .tiff for multipage format.

A cosmetic issue is that in Column 0, although there is no thumbnail, the left most part seems to be reserved for an image that does not exist. Does anyone know if I can get rid of this, and how? Help appreciated.

As I wrote in my previous post, there are some drawbacks to the technique used. How do I get the thumbnails in the listview?

• First SQL fired
• Extract BLOB to a file
• Check if .gif;.png;.jpg;.bmp;.jpeg;.ico
• Convert the image file into a resized .bmp format (only format usable in the listview, together with the awful .ico that I don’t want to use)
• Delete the initial extracted image file
• Get the small bmp image it into the _GUIImageList
• Delete the small .bmp file
• SQL fired Second time (re-query)
• Create the listview and add the bitmaps to it
• Done

So, you see that this is a lot of overhead to display an thumbnail of the image BLOB. Is it worth the effort? I guess it is for a small number of rows. The effect is certainly neater than just showing generic thumbnails.

I have to fire the SQL twice, if I used _SQLite_GetTable2d() I would not have to fire the same SQL twice, but the benefit would be small I guess. And I prefer not to have to load large BLOBs in one 2d Array.

I am now working on step 3 (version 0.0.0.3) which will be close to a ListView with natively recognized BLOB objects.

I hope to have feedback this time.

GreenCan

Edited May 6, 2013 by GreenCan

[jchd]

Hi GreenCan,

Nice job!

I have little time to look close into this so excuse me for pointing out sideband stuff!

It's better than what SQLite Expert shows, at least for most image types. Yeah, I love Expert!

Besides, here's your schema:

CREATE TABLE [image_store] (

[Key] INTEGER NOT NULL ON CONFLICT ROLLBACK,

[Filename] TEXT NOT NULL,

[TypeOfObject] TEXT(4) NOT NULL,

[Title] TEXT NOT NULL,

[Object] BLOB NOT NULL ON CONFLICT FAIL);

CREATE INDEX [Key] ON [image_store] ([Key]);

You might change it to:

CREATE TABLE "Image_store" (

"Key" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

"Filename" TEXT NOT NULL,

"TypeOfObject" TEXT(4) NOT NULL,

"Title" TEXT NOT NULL,

"Object" BLOB NOT NULL);

There is no possiility of conflict on a blob column. Also autoincrement on [key] is probably all what is needed (thus sparing an extra index duplicating rowid).

Very good job anyway.

[Myicq]

I will look forward to viewing this example in detail when I have a bit more time. Looks real promising. I can see other good uses for it, such as a simple way to include necessary files in an installation, or an advanced way of storing ini file settings.

[jchd]

SQLite has been precisely designed as a versatile embedded application data container, independant of the hardware/software platfrom. In practice it has proven over time that it is also suitable for successful use in other contexts where it was unexpected.

Hence it is no wonder that you see fit for advanced ini storage.

[GreenCan]

You might change it to:

CREATE TABLE "Image_store" (

"Key" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

"Filename" TEXT NOT NULL,

"TypeOfObject" TEXT(4) NOT NULL,

"Title" TEXT NOT NULL,

"Object" BLOB NOT NULL);

Thanks jchd

I changed the schema as you suggested.

In the next version (already ready but I am waiting to publish it) I won't need Key, Filename or TypeOfObject anymore but I will use RowID to get the BLOB when clicking on the ListView Thumbnail. So no links with other columns anymore.

I have made the BLOB object natively recognizable, but I had to make a concession. The compromise is that the BLOB object starts with a header containing the filename.

I could have identified the object by reading the header of the object, so that I can identify all images (this is probably how SQLite expert is doing it, but I don't want to limit the BLOB to images, and how do I recognize header-less objects like an AutoIt script, text files, csv? No way to do this.

To be continued...

GreenCan

[GreenCan]

I will look forward to viewing this example in detail when I have a bit more time. Looks real promising. I can see other good uses for it, such as a simple way to include necessary files in an installation, or an advanced way of storing ini file settings.

Thank you

You are absolutely correct, the idea of embedding resources, ini file or (why not) functions that are rarely used in an application can be explored.

[jchd]

When you declare a column as integer primary key [autoincrement] it becomes an alias for rowid, so you only have one column and one index. The added bonus is that aliased rowid never change under a vacuum contrary to implicit rowid not aliased.

You can simply store metadata in another column. This will save you the burden of reading the whole blob to identify file type and other information. You just don't display that internal-use column.

Also, storing a thumbnail while inserting new rows would save the work needed for re-computing it every time the list is displayed.

Finally remember --should the feature prove to be of any use in your case-- that you can reserve a fixed size for a given blob and use incremental I/O on large blobs. Don't think I'm patronizing you by saying that. If you ever need wrappers to binding functions, just ask: I've experimented with parameter binding but it is slower in most use cases than using Exec due to the overhead of multiple DllCalls. I don't post the functions to keep freshmen away from being tempted to use them: they are more sensitive to errors and bring only complexity most of the time.

Have fun!

[GreenCan]

When you declare a column as integer primary key [autoincrement] it becomes an alias for rowid, so you only have one column and one index. The added bonus is that aliased rowid never change under a vacuum contrary to implicit rowid not aliased.

Thanks

Yes, I know that using rowid is a risk because it is not guaranteed that the row will keep it’s initial identity.

See next

You can simply store metadata in another column. This will save you the burden of reading the whole blob to identify file type and other information. You just don't display that internal-use column.

But I hate using other fields that contain metadata or a record key and which I have to link all together to retrieve the identity of the stored object, that is not what I call native object recognition. Additional reason is that it would not be possible to integrate the logic in a generic reporting tool like my SQLite Report Generator. It would just be impossible to build the logic for this. Anyhow, I don’t think there is an ideal solution for what I want to do. Even a nice tool like SQLite expert can not recognize a BLOB being an Excel sheet...

Also, storing a thumbnail while inserting new rows would save the work needed for re-computing it every time the list is displayed.

Yes, that sounds like a good idea, completely different approach and saving SQLs. I will think about this for version 4

Finally remember --should the feature prove to be of any use in your case-- that you can reserve a fixed size for a given blob and use incremental I/O on large blobs. Don't think I'm patronizing you by saying that. If you ever need wrappers to binding functions, just ask: I've experimented with parameter binding but it is slower in most use cases than using Exec due to the overhead of multiple DllCalls. I don't post the functions to keep freshmen away from being tempted to use them: they are more sensitive to errors and bring only complexity most of the time.

Are you talking about the library of ‘functions that assist access to an SQLite database with parameter bindings’, that you published a while ago (2011) ? It is still Chinese to me… I have to dig into the matter.

[Andreik]

That's a pretty nice work GreenCan. Thanks for share!

[GreenCan]

That's a pretty nice work GreenCan. Thanks for share!

Thank you Andreik

[GreenCan]

On request of reybol7, I have developped a small script that allows you to import your own BLOB objects in the demo database GreenCan_demo.db.  Both Database and script can be found in Post 1.

I used the SQLiteBind udf written by jchd. to update the BLOB. 

See SQLiteBind library for more details

Thanks to him for sharing this fundamental SQLite UDF

My script is pretty simple but does what it has to do.  You can import any kind of objects, images or whatever you like.

Enjoy,

GreenCan

[GreenCan]

It looks like I went too fast yesterday,  I forgot that I changed the structure of the database, the key column is now autoincrement

You have to download the new database from the link in the first post to make the BLOB import script work

http://users.telenet.be/GreenCan/AutoIt/GreenCan_SQLite_Database_demo.zip

GreenCan

[jchd]

Geez, I didn't remember clearly having posted that: I thought I sent it thru PM. Re-reading the thread reminded me with my own truth, "memory leak: age"

Please chime in if you find a bug or anything bizarre with these functions.

Should someone _really_ need wrappers to the blob I/O routines, just ask and I'll add them to the binding mini-UDF.

Again all of this stuff is not for fresh[wo]men and you should have strong and uncommon reasons to even look at it. Thinking that use of these functions would make your code look alike any C example of using SQLite is no excuse for using it in AutoIt: higher-level functions in the standard SQLite.au3 UDF are safer, easier to use, more concise and more efficient.

[GreenCan]

Again all of this stuff is not for fresh[wo]men and you should have strong and uncommon reasons to even look at it. Thinking that use of these functions would make your code look alike any C example of using SQLite is no excuse for using it in AutoIt: higher-level functions in the standard SQLite.au3 UDF are safer, easier to use, more concise and more efficient.

jchd,

I admit that I could have used the more familiar standard SQLite functions, your udf works fine however..

Without SQLiteBind the import script looks like this

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=SQLite2.ico
#AutoIt3Wrapper_Res_Description=Import Objcet in SQLite Database
#AutoIt3Wrapper_Res_Fileversion=0.0.0.1
#AutoIt3Wrapper_Res_LegalCopyright=GreenCan
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.8.1
    Author:         GreenCan

    Credits to:
    jchd: for disapproving the usage of SQLiteBind.au3 :)

    Script Function:
    Import images and other objects to SQLite GreenCan_demo.db
    To be used in conjunction with SQLite database with BLOB example

    Known problems:
    None

#ce ----------------------------------------------------------------------------
#include <Array.au3>
#include <sqlite.au3>
#include <SQLiteBind.au3>

Global $sFile = FileOpenDialog("Choose EmbedFile",@ScriptDir,"All Files (*.*)")
If @error Then Exit

Global $sFileName = '_' & StringTrimLeft($sFile,StringInStr($sFile,"\",1,-1))
Global $sTitle = InputBox($sFileName, "Description for " & $sFileName, $sFileName)
If @error Then Exit

Global $sDatabase = @ScriptDir & "\GreenCan_demo.db"
_SQLite_Startup()
If Not @Compiled Then ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

_SQLite_Open($sDatabase)
Global $sTypeOfObject = StringRight($sFileName,3) ; moet beter
Global $Object =  Create_BinaryFromFile($sFile)
If @error Then Exit MsgBox(48,"Error", "Cannot convert " & $sFileName & " to Binary format")

Global $sSQL
$sSQL = "INSERT INTO Image_store (Filename, TypeOfObject, Title , Object) VALUES ('" & $sFilename & "','" & $sTypeOfObject & "','" & $sTitle & "'," & $Object & ")"
$sReturn = _SQLite_Exec(-1, $sSQL)
If @error Then Exit MsgBox(48,"Error", $sSQL & " failed" )
_SQLite_Close()
_SQLite_Shutdown()

Func Create_BinaryFromFile($sFile)
    ; this function will convert the selected file into a Binary string
    Local $fHandle = FileOpen($sFile,16)
    If $fHandle = -1 Then Return SetError(1, 0, 0)
    Local $sBinaryString = FileRead($fHandle)
    If @error Then Return SetError(2, 0, 0)
    FileClose($fHandle)
    ; not sure if this is the best method but it works
    $sBinaryString = _SQLite_FastEncode(Binary(BinaryToString($sBinaryString)) )
    Return $sBinaryString
EndFunc ;==>Create_BinaryFromFile

[jchd]

Hey GreenCan,

My warning wasn't aimed at you since it's quite clear that you know what you're doing and why.

Also SQLite users may have consider other points about large strings and BLOBs:

• the saddle point where internal vs external storage efficiency meet (see http://www.sqlite.org/intern-v-extern-blob.html for a read-only test but depending on OS and hardware YMWV)
• the absolute maximum size of a string or BLOB under SQLite (1Gb)
• the maximum size of a string or BLOB defined at sqlite3.dll compile-time (default 1 MiB)
• the current maximum size of a string or BLOB for the current connection, which can be changed thru a call to sqlite3_limit()
• the absolute, compile-time and current maximum size of an SQL statement (default 1MiB) [note that host parameters (bindings) are not subjects to this limit]
• AutoIt strings are UCS-2 encoded (but most databases are using the default UTF-8 encoding). If your use involves significant ratio of non werstern ANSI characters, the maximum usable string size is under 1Mi chars
• BLOBs inlined (e.g. in Exec INSERT calls) use ~2 bytes per byte (hex) hence the max usable size if essentially halved.

For a complete description of SQLite limits see http://www.sqlite.org/limits.html

[ptrex]

This is the refurbished version for the latest Version.

Enjoy !!

 

 

 

BLOB Demo.zip

Edited May 22, 2020 by ptrex

[argumentum]

@ptrex, could you ZIP it with _inputmask.au3 and GIFAnimation.au3 in it.

[ptrex]

OK done...