SQLite semi Embedded database functionality in AutoIt

[ptrex]

@Azu

Thanks for your vote.

Glad to hear you are in favour !!

See you around !!

ptrex

[Autox]

How about from the perspective of a non-professional programmer? We have needs but not the resources to get help to implement them so I cobble together what I can. Currently for an application we are using I have to gather all of my information and then run an external application to report the data to the database but the app is very limited and we have issues frequently with it.

To be able to write to, or pull from database would be awesome. I don't know VB, C, C+, C++, J or any other language. I have used AutoIT for years because it is quick and easy to learn. I have tried a number of other external tools but most require a certain version of .net or have some other limitation. I have reviewed a lot of the message strings on using DLL's but I need something that can run across multiple windows based platforms without requiring special extras installed. I know it can be done since the external app I am using works on everything from NT - 2003. So if there was an option of an include that would give the ability to interact with a centralized database that would be incredible.

Just my humble, non-programmer opinion.

[ptrex]

@Autox

Thanks for your interest in SQLite.

To be able to write to, or pull from database would be awesome

This can be done allready as you can see in the first post.

So if there was an option of an include that would give the ability to interact with a centralized database that would be incredible.

This is already there as well.

If you have problems finding the correct scripts to get started let us know.

regards,

ptrex

[webmedic]

I enjoy this much also. I have used it for things now and it is working quite nicely.

Thank you.

Now if sqlite had a version that allowed the db to be compressed. (that was stull public domain)

Thanks.

Edited September 12, 2006 by webmedic

[ptrex]

@webmedic

Now if sqlite had a version that allowed the db to be compressed

The compression is done with help outside the SQLite functionality.

In the article it is described how to build your own compression/decrompression with PHP

SQLite data compression

They use a UDF in PHP to handle this :

The sqliteCreateFunction() method contains all the magic to attach a PHP function to SQLite. Let's create a compression function to dynamically compress a large data field, like possibly an article from that new blog system you're working on.

function compress_data($data_string) { return gzcompress($data_string, 6); } 
$dbh->sqliteCreateFunction('compress', 'compress_data', 1);

We just registered our PHP function compress_data as a new SQLite function compress. We can now use compress in any query we'd like:

$stmt = $dbh->prepare("INSERT INTO articles (auth_id, date, article) VALUES (:auth_id, :date, compress(:article))");

If PHP can deal with it by means of a UDF than AutoIT can do the same.

Look for a compression UDF in the Scripts and Scraps, I know there are around somewhere.

Look how the PHP was build and give it a try.

If I would have the time I would test it myself. But I am fully booked till the end of this year at least.

Hope this helps you going.

Regards

ptrex

[webmedic]

Well it does. I have done extensive programming in php and understand perfectly what they are doing. I used to do the same with the obgzhandler to gzip a page before sending it.

However Having said that it would also mean that I can no longer use like statements and % % when doing a search through the database. It means I would have to grab each table uncompress it and then use string searches from autoit to do a search through the text. This would slow searches down tremendously.

There is one other option though and that would be to break down and finally build the full text indexing engine I have been thinking about doing anyway. Then I could search through the indexes and leave the main text compressed. this would reduce the size of the db but it would also make it larger due to the indexes. It would also give me faster search results I suppose but I would also have to completely change my search logic and I'm not a search engine writting guru.

If you happen ot know of an indexing engine that could be easily ported to autoit let me know. I have already looked at everything usable but there are no windows binaries for the ones that look the most usable.

This includes:

lucene/pylucene/ferret

hyperetraier

xapian

ft3

xapian and possibly ft3 look the most usable but no windows ports are there and I'm not about to start trying to compile them under windows. I do it all the time under linux but I dont want to even deal with getting a ming environment going to try and compile this thing.

I was actually able to get hyperestraier running properly but the indexes are huge and the data it outputs whould be a pain to parse to get the relevant data out of it after the search is finished. I probably looked at a few others also but those listed where the most usable.

[ptrex]

@webmedic,

I can follow your issue here.

Well there is a 2000$ solution for it SQLite Support that takes care of your problem.

Or there is a cheaper one 60$ listed here that deal with compression. Encrypt Compress

I have an other solution as well for you

It also supports native NTFS compression, which helps keep the db size down !!

You can pay me between 60$ and 2000$, up to you to choose

regards

ptrex

Edited September 13, 2006 by ptrex

[webmedic]

does this native compression also work from a cd?

[ptrex]

@webmedic

Who wants to run a compressed db from a cd ?

Compression is only usefull if you have capacity problems.

On a CD there is + 700 Mb available, and if not enough use a DVD 4 Gb (dual layer 8 Gb)

What are you trying to say ?

[webmedic]

um well I'm talking about haveing a db that almost fills a cd and may go over a litle. dvd is not an option since it should be usable by others without a dvd on thier system. Almost everybody can read a cd.

[ptrex]

@webmedic

This is a question that runs out of the scope of AutoIT.

I am willing to investigate it, depending on what your are having to offer.

Let me know.

regards

ptrex

[wannabeeprogrammer]

Dear Ptrex,

While you are all having fruitfull discussions about whether or not to embed SQLite, can you please tell me whether your solution makes it possible to create a multi-user database application with record locking?

I need to build a simple database app where 5 people can add, edit and remove client records simultaneously.

Can you please help me out with this? Autoit is the only language that i can build something with. All my Autoit programs work, but probably excel in their level of design stupidity. So please keep it simple for me...

E.g. i read one of your listings and i really don't understand what's happening there.

Please tell me what files i need, in what directory i should put them, what i should do to create a table with fields and how i should update them in a Windows network environment.

File locking is no option, since i have simultaneous users. It must be record locking.

Can you help me?

Best regards,

Johan

[webmedic]

do you know sql?

from the sqlite faq. http://www.sqlite.org/faq.html

(7) Can multiple applications or multiple instances of the same application access a single database file at the same time?

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

We are aware of no other embedded SQL database engine that supports as much concurrancy as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

so the answer to your queastion is that it should work but may not.

Do simple search for these kinds of answers.

Now for my first question. If you don't know sql then this will be much harder for you than you think as you will need to spend allot of time searching to find your answers.

[ptrex]

@wannabeeprogrammer

The record locking is not something that is handled by AutoIT but by SQLite itself.

See for more information.

SQLite Locking principles

Your other questions are hard to answer if you have little or no knowledge of DB' s or DB management.

Writing an application that uses a DB is hard to do even for more experienced users.

You need to have different skills :

- AutoIT to write the code to write the application.

- SQL knowledge to interact with the DB.

So it is not just know what files to use and know where to put them.

To start with it is download the LiteX Automation DLL where you can find the link in post 2.

Than put it in the Windows directory and register it like described in the 2 nd post.

Copy the code in the 2 nd post and run it. If it works you have a sample code that tells you how to get started.

Second alternative is use the DLLCall example from post 1. But is was never finished.

Which runs on the native SQLite DLL which you can download from the SQLite.Org site

Nevertheless it will also do what you are trying to accomplish.

I hope this get's you started.

Regards

ptrex

[gcriaco]

Second alternative is use the DLLCall example from post 1. But is was never finished.

Personally I prefer this method to access SQlite db from AutoIt (no ocx to register, and so a less invasive way to deploy applications, less size,...).

Any plan to finish it?

IMHO, the most important feature to add is the Begin/End Transation method.

However, I agree with you: it could be a perfect marriage between AutoIt and SQLite: both are light, free, fast,...

Many thanks for your effort in the matter

Peppe

[ptrex]

@gcriaco

I brought up the COM DLL to get SQLite into AutoIT.

Picasso brought up the DLL CALL version.

So maybe you can ask / PM him, about what his plans are on finalizing the pending project.

I also prefer this one, but as it is not finished I will not use it so far.

Regards

ptrex

[webmedic]

how is it not finished? I am using it for a very large project and don't have any issues with what is there.

[JSThePatriot]

how is it not finished? I am using it for a very large project and don't have any issues with what is there.

I have also been using that method for some time, and have had no issues as of yet. I would hope that it will be finished at some point soon, and nothing be broken.

Just out of curiosity, who would be interested in a SQLite Plugin for AutoIt? That would make things a bit simpler I believe. If there is enough interest then I will look into doing this for the AutoIt/SQLite community.

IMHO,

JS

[webmedic]

I would.

Just curious this would take the udf and turn it into a dll more or less. You would still have to have the sqlite dll besides correct?

At any rate I'm sure it could not hurt performance as a native compiled dll should be faster than the udf.

I will help to test if you do decide to work on it. Now all we need is somebody to compile clucene for windows and make a plugin for that. Then we would have a full blown search engine as well as a sql engine.

clucene can already be compiled on windows as it is included as a dll with the flock browser. I just don't know anything about making plugins or using dll struct so it is of little use for me at this time.

that is unless somebody would like to take a stab at it.

Thanks.

Oh and I already use your md5hash plugin. Works great.

Edited September 18, 2006 by webmedic

[JSThePatriot]

I would.

Just curious this would take the udf and turn it into a dll more or less. You would still have to have the sqlite dll besides correct?

At any rate I'm sure it could not hurt performance as a native compiled dll should be faster than the udf.

I will help to test if you do decide to work on it. Now all we need is somebody to compile clucene for windows and make a plugin for that. Then we would have a full blown search engine as well as a sql engine.

clucene can already be compiled on windows as it is included as a dll with the flock browser. I just don't know anything about making plugins or using dll struct so it is of little use for me at this time.

that is unless somebody would like to take a stab at it.

Thanks.

Oh and I already use your md5hash plugin. Works great.

Ah I am glad to know you have gotten use out of my plugin. To answer your questions...

The AutoIt Plugin DLL would be the only extra file needed. You would no longer need the SQLite3.dll. It wouldnt have to be a registered DLL either, as it would work just like my MD5 and SHA-1 functions.

I hope that helps explain it a bit further.

I have downloaded the source code to SQLite, and have alerted the author of my endeavors. I hope he will respond with some more resources for me. He has a library of functions, and I need to make the AutoIt Plugin from there, working on it, but progress will be slow as I have many other projects on my plate at the moment.

I do appreciate your interest in the project.

JS