Gerry Posted January 19, 2010 Share Posted January 19, 2010 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 Link to comment Share on other sites More sharing options...
jchd Posted January 19, 2010 Share Posted January 19, 2010 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Gerry Posted January 20, 2010 Author Share Posted January 20, 2010 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 Link to comment Share on other sites More sharing options...
jchd Posted January 20, 2010 Share Posted January 20, 2010 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 SQLiteRick van der LansLuluISBN 978-0-557-07676-5The 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted January 20, 2010 Moderators Share Posted January 20, 2010 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 timeCould 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  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
jchd Posted January 20, 2010 Share Posted January 20, 2010 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Gerry Posted January 21, 2010 Author Share Posted January 21, 2010 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 Link to comment Share on other sites More sharing options...
jchd Posted January 21, 2010 Share Posted January 21, 2010 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? This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Gerry Posted January 21, 2010 Author Share Posted January 21, 2010 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 Link to comment Share on other sites More sharing options...
jchd Posted January 21, 2010 Share Posted January 21, 2010 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Gerry Posted January 27, 2010 Author Share Posted January 27, 2010 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 Link to comment Share on other sites More sharing options...
jchd Posted January 27, 2010 Share Posted January 27, 2010 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now