SQLite Table name quiery

[Gerry]

Hi All

I am trying to write a database program using SQLite and may I add the this is the first time I'm trying SQLite.

I have managed to create a database file with 2 tables. Each table has a few columns to it but no data lines yet. I am able to see the tables when viewing it with SQLite Expert, so the tables do exist.

Them I'm trying to see count and names of the tables I created. Searching for methods to quiery the database to extract the said info, resulted in me using this line:

$aClientIndex = _SQLite_Exec(-1,"SELECT * FROM sqlite_master WHERE type='table'","_cb")

I then displayed the $aClientIndex in a messagebox but it shows up as Zero

MsgBox(0, "SQL Tables : " , $aClientIndex)

Can anyone please point me on how I can get the amount of tables and their names?

Any help greatly appreciated

Gerry

[jchd]

I am able to see the tables when viewing it with SQLite Expert, so the tables do exist.

Excellent program, you've made THE right choice.

$aClientIndex = _SQLite_Exec(-1,"SELECT * FROM sqlite_master WHERE type='table'","_cb")

_SQLite_Exec is made precisely for ... Executing a resultless query.

To see what you want, you need to use either _SQLite_GetTable2d or _SQLite_GetTable.

With $hDB being the handle returned by _SQLite_Open (or -1 if you're lazy and on't play wih several bases at the same time), try this:

Local $rows, $nbrows, $nbcols, $ret
    $ret = _SQLite_GetTable2d($hDB, "select * from sqlite_master;", $rows, $nbrows, $nbcols)
    If @error Then
    ConsoleWrite(@error & ' ' & @extended & ' ' & $ret & @LF)
    Else
    _ArrayDisplay($rows, "2D display of sqlite_master")
    Endif
    $ret = _SQLite_GetTable($hDB, "select * from sqlite_master;", $rows, $nbrows, $nbcols)
    If @error Then
    ConsoleWrite(@error & ' ' & @extended & ' ' & $ret & @LF)
    Else
    _ArrayDisplay($rows, "Less convenient 1D display of sqlite_master")
    Endif

More to read in the help file.

[Gerry]

Thanks, I realy appreciate your assistance.

I have been to quite a few sites and looked at number of AutoIt example

scripts. I would seem most of the examples provided assume a proficiency

with both SQLite or SQL and AutoIt.

I found most of the AutoIt scripts don't work anymore on the latest release.

One can modify a lot of these settings and then hope to perhaps eventually

get the scripts going again but it feels like you are re-inventing the wheel

again as there is very little comments to understand why certain procedures

were taken.

Please I'm not knocking or intending this to offend anyone, scripts comments

realy help wannabe programmers like me and I am apprciative for any examples

provided.

I now have something to work with, I was just about to abandon

my idea of using SQLite.

Wonderful what happens when one suddenly see the light, isn't it?

Thanks again for your assistance

Regards

Gerry

[jchd]

It's possible that some examples in the help are broken. I'll look into that someday (not tomorrow).

I promise to make a decent AutoIt + SQLite presentation with working examples aimed at starters and experienced guys as well, but it will take even more time.

I also would like to perform some other work to the UDF itself.

But I can only push you to familiarize yourself with SQLite and start using it. There is really an enormous potential for use in _so_ many different contexts, always for making applications more robust, more flexible, easier to maintain and simpler. The use of SQLite extensions makes the potential even broader, to an extent that only few people are exploiting fully. You get all this for free with unprecedented support, so isn't life wonderful?

Please feel free to ask questions if anything isn't clear/working. I'll be trying to answer the forum regularly but you may also PM anyway.

For those seriously looking using SQLite (in any environment) consider buying/borrowing/stealing this book:

The SQL Guide to SQLite

Rick van der Lans

Lulu

ISBN 978-0-557-07676-5

The easiest way to buy it is the Lulu site. I couldn't manage to get a link from an english page, so I hope this will make sense to you.

[Melba23]

jchd,

I promise to make a decent AutoIt + SQLite presentation with working examples aimed at starters and experienced guys as well, but it will take even more time

Could I please encourage you in this endeavour. I have never used databases of any kind before and am finding the learning curve pretty steep. Still it gives me something else to do when the weather is too wet for golf.

Mais cela n'arrive pas trop chez vous, n'est-ce pas?

M23

[jchd]

Could I please encourage you in this endeavour. I have never used databases of any kind before and am finding the learning curve pretty steep. Still it gives me something else to do when the weather is too wet for golf.

Not having ever used databases is no excuse for trading learning new stuff against golf

More seriously, you're right, I know there is ample motivation for bringing close-to-their-needs, easy-to-follow examples to many different kind of users. Just as an easy example, SQLite can be used to implement associative arrays. As a free bonus, it offer 500% more flexibility and potential than simple dictionaries and I wonder why people don't use it more. The reason _must_ be that they simply don't have the first steps shown to them. Once you get into implementing SQLite in your own simple or complex applications and experience its power, I bet noone is going to stop using it. Of course it doesn't mean that such a simple database is going to fit every need and can be substituted blindly to any other ways of storing/retrieving data, but honestly I've been surprised myself how easily it can do things you didn't even dream of beforehand.

Mais cela n'arrive pas trop chez vous, n'est-ce pas?

We do sometimes have not only cats and dogs, but also elephants falling down, in terms of rain. OTOH I confess we also enjoy extended rain-free periods during the year.

[Gerry]

Thanks for all the reponses

I will looking out for your updates!

Next question, class is in session . . . I was wondering the memory footprint of Indexing some columns of my tables.

Indexing enlarges the database, is this index then kept in memory to speed up transactions? Sorry to ask it here, I should probably ask this in a SQLite forum.

Gerry

[jchd]

Indexing enlarges the database, is this index then kept in memory to speed up transactions? Sorry to ask it here, I should probably ask this in a SQLite forum.

This is still reasonable to answer here since it can be of value to other users of SQLite and AutoIt.

First and by all means, read the SQLite documentation. You'll learn a lot.

SQLite allocates a page cache of its own (I mean above the filesystem cache provided or not by the OS/driver/hardware). There are pragmas to control this cache as well as the page size (but page size can only be set at database creation). Beyond that, you don't have much control over SQLite' use of this cache. Cached pages can be any mix of index, data and other housekeeping information.

Your question is turning the issue backward. You mainly create indexes to avoid full table scan in repetitive queries. In exchange of a database size increase you get better speed on average typical usage. The decision to create a specific index must be based on both deceptive unindexed response time and query plan analysis (if the indexing schema isn't obvious). That you trade cached data against cached index pages is a bit irrelevant as you don't control what happens at this level (I would even add: "hopefully").

It's very easy to create an index, either using pedestrian SQL or a decent SQLite manager. Run explain query plan to check that you index will be used in the queries you want to speed up and benchmark the result for average use. If you feel you don't gain much doing so, just drop the index. It's that simple, at least in theory!

From time to time some weird optimization question is discussed on the SQLite forum where DB and SQLite gurus give expert advices 24/7. Reading this kind of questions and answers is enlightning: SQLite is almost always doing a pretty good job by itself. This is a bit amazing, especially when you realize that SQLite is being used on cellphones as well as in datacenters with hundreds or thousands of simultaneous users. You shouldn't worry that much in most cases, unless you do anything critical (in AutoIt?).

The type of interface that AutoIt can use with SQLite doesn't allow to reach the maximum speed that SQLite is able to sustain. Multi-threading is also impossible. If you really need the fastest speed, you need to switch to some other language.

Now, if you definitely can't get it to work at the speed your application requires, you still have another option: backup the disk database to memory at startup and dump it back to disk before exit. Memory databases are magnitudes faster than disk-based, but can't be shared and of course don't use cache.

What are your batabase size, complexity, speed, memory requirements?

[Gerry]

Wow, that was a mouthful! It settles it, I don't need to use Indexing then.

I am trying to write program for a very small call centre, maybe 20 users.

All fields will be less than 20 charackets but for one where notes are taken about the call.

I'm deviding the database file into sections with a table for each client all his debtors details in that table about 30 columns. I don't think any of these entries will ever have 10 000 lines in them. Tipcally I don't think any one Client Table would be ever reach 1 000 entries.

I weighting up between adding another column to containing scanned account attachments and I then havimg a separate folder for attachments. Would having the attachments in the Database slow down performance significantly? I know the database file will be larger but the attachments will be on the drive anyhow, it is just more convenient. I think I ahve answered my own question, attachemts included in the database impact the speed badly for networked users, you don't need to download one large file, you could just download the database and what attachments you need, when and if.

Thanks for your responses so far.

Gerry

[jchd]

It settles it, I don't need to use Indexing then.

You don't know for sure before trying.

I am trying to write program for a very small call centre, maybe 20 users.

Then be aware that SQLite is an _embedded_ database manager. That means in practice that it isn't designed to work over the network, at least it's very risky to use it for networked applications as it comes. SQLite is mainly designed to be embedded into or along application(s) for local base usage.

It's still possible to wrap a server layer around it, but it doesn't come in the package and none is available from the SQLite dev team. Again read SQLite documentation on the SQLite site to learn why and how.

I have the very same problem but only with 4-5 users. For now, the application runs on a single PC. My application doesn't imply heavy load, so I'll be devising some form of server layer for SQLite for my own needs. There are several in source form floating around that I may use as a base of design decision.

I don't want to say that SQLite can't do the job, simply that you need to take care of some aspects that you seem to be unaware of currently.

Selecting the right product is important and you should also look at other RDBMS like MySQL or PostGres. Of course, those require _much_ more work for setup and administration, but they offer proven client/server behavior right out of the box.

I'm deviding the database file into sections with a table for each client all his debtors details in that table about 30 columns. I don't think any of these entries will ever have 10 000 lines in them. Tipcally I don't think any one Client Table would be ever reach 1 000 entries.

I can tell you at once this is bad design. Databases and the underlying SQL forces you to see things in a different manner (derived from set theory). Forget about your sections and building a separate table for every client. Make that a Client table, each row holding information pertinent for each client. Then make a Debtor table with a foreign key = client ID where you store attachment _links_ (not big things like images, scans, videos !). Then built a Call table, with foreign key = client ID for storing date, time, references, notes about a given call. I don't know the specifics about your application, but that's the general idea: group together only homogenous information, avoid "holes", don't duplicate information. Lookup database normalisation.

BTW, 10K clients, 30 debtors, 1000 ?, 10 chars on average field = roughly 3Gb, not including attachments. With 20 users that doesn't make for a "very small" thing.

I weighting up between adding another column to containing scanned account attachments and I then havimg a separate folder for attachments.

Do that.

Would having the attachments in the Database slow down performance significantly?

Don't put attachments inside! This will cut down performance dramatically.

The reason is that SQL looks and manipulates rows while SQLite manipulates pages. If a row contains only reasonably short columns (remember that SQLite always uses the minimum amount of space to store any data: it doesn't have fixed-length fields, even for integers) then there is a fairly good chance that the next page in sequence (on disk or on cache) will hold the next row, or part of, or several. This is the reason that you can fine-tune the pagesize before putting the base in production. But if you store blobs as part of your rows, you're guaranteed that the next row will require page index lookup and/or another or several other disk access. So even if SQLite gives you the facility to store blobs (even very large ones) as part of rows, that's a bad idea in general. Exceptions are for instance in cellphones or other limited hardware, where filesystem access is much worse than on a PC.

[Gerry]

Hi

Thanks for your response, it was realy informative and I foolwed your advice to read more on the subject

I think I will stay with SqLite, I will create a file for each client and since only 4 consultants would work on any one clients file at atime, this should reduce the file sizes and possible simultanous access there of.

>> It's still possible to wrap a server layer around it, but it doesn't come in the package

I saw a few shareware / freeware server wrappers and also a commercial solution to network SqLite, I will probably use an implementation with access via sockets. If the worst case, I will have to put my hand deep in to my pocket for the comercial version . . .

I don't know how viable it is to open a database, read / change it and immeadiately close it again, this may stop simultanous access but slow the process down. Sorry I'm just trying to sound clever.

Regards

Gerry

[jchd]

I think I will stay with SqLite, I will create a file for each client and since only 4 consultants would work on any one clients file at atime, this should reduce the file sizes and possible simultanous access there of.

You're in the best place to determne what's the best solution given the constraints you have.

I don't know how viable it is to open a database, read / change it and immeadiately close it again, this may stop simultanous access but slow the process down.

It all depends on your application expected behavior. If you can afford to exclusive lock the database (even a central one shared by all your users) and keep all your transactions _short_ then you can get along without anything else. If there's no chance two consultants will have to call the same client at the same time, then you're fairly safe. OTOH if there is also the possibility that some other users asynchronously update the base at any time, then you need to protect against various "dirty" or "phantom" data situations.

Consultant A grabs data for client X without locking then start calling the client.

Sales person K updates client X data.

Consultant A updates his local copy of the data then rewrites it to the database.

Updates made by sales person K are silently lost.

There are many ways this kind of issue can happen. It is very important to keep data consistant.

Note that even for read-only transactions, SQLite (like any DBMS) needs to lock at times. A SELECT implies looking up at least internal storage structures (Btrees), indexes and data pages. If another process causes a change in the same pages in the middle of your reads, then you end up with garbage. Unfortunately, network locking mecanisms are known to be defective on almost every OS around. I don't want to scare you: this is a fact that is quite difficult to ignore when talking about a database, where only one disk cluster read with bad data due to buggy locking can compromise the entire database integrity. It's preferable to be aware of possible issues beforehand and take steps to avoid problems, than to start using an hastily-chosen architecture that will break anytime and wait for disaster.