SQLite on mapped Lan drive

[bearcav]

I was wondering what the performance issue would be with running a AutoIT - SQLite App on a Novell mapped drive. My project is tracking the PM'ing (Vacuuming, checking settings ++) of computers and gathering missing inventory information while we are at it. Occasionally the app may be run by multiple users at the same time. I was wanting to keep this simple and not use the server and mysql db.

I was thinking it might be possible to hold down file locking problems by using read only mode most of the time and opening the db for quick writes. I am really a noob especially, with AutoIT.

Edited January 6, 2007 by bearcav

[bearcav]

Just to be sure you understand, I am not looking for code per say, more theory and general experience. I have already created a nice GUI in Koda and have a much more basic gui and code that writes data to the registry and appends a csv file on the network. I am wanting to delve into SQLite and distributed db's one step at a time. I have created fairly complex access db's but am not a fan of m<es>s in general.

[Zedna]

Look here for more details about SQLite.

Maybe you will find there answers on some of your questions...

Edited January 6, 2007 by Zedna

[bearcav]

Look here for more details about SQLite.

Maybe you will find there answers on some of your questions...

Actually I started there, I had been setting up the csv append and thought it would be much nicer to use a db. I had SQLite in mind when I did my search on the forum. Looking at it again:

Faster than popular client/server database engines for most common operations.

I guess this answers my main question, so I will head in that direction. Any pitfalls I should lookout for? So I wanted to get a clear picture of problems to look for and things to do to improve the odds of my success and reduce the amount of frustration.

[bearcav]

The editing of posts is a bit choppy, I decided I should elaborate:

I am planning ahead by allowing for a user adjusted schema and possibly making it commercial. Since I'm living in one of the poorest parts of NC I have to look to do something like that! This would become the PM client and will allow for templates on the logon "Legal Text" screen (working) someday to be managed centrally. It needs to verify computer inventory though I am allowing to broaden from the local computer to other computers and items. The scope for this portion is for the technician (or possible less qualified individuals) to post their work on that piece of equipment, not as an administrative interface. I have 12 generic fields and several other fields that will be configurable. I will hide all but what is needed according to the configuration. I've overlaid text boxes with the combo boxes hidden at the start on all twelve generic fields (they use the same labels) as well as a couple of others. They are to be swapped out as needed when the data is read and before the form is shown. Eventually (when I add more than just the local computer to "PM") I plan on a combo box switching out the fields as needed ("Computer Model" become "Printer Model" and the fields change accordingly). I've done some testing (running the script and then changing variable values by hand and running again) and know it will work.

Koda is a trip, I really like it and have finally gotten good enough with it that I think I won't have to manually edit the results except for adding 12 of the overlaid cbo boxes, you just need to be very thorough. I'll never make a VB app again if I can help it!! AutoIT Rocks!!

Any pitfalls I should lookout for? I wanted to get a clear picture of problems to look for and things to do to improve the odds of my success and reduce the amount of frustration. I am speaking about AutoIT's SQLite funtionality, and Yes Please Make It Native!. Do you know how often organizations need a quick and dirty db app?? I know AutoIT's SQLite functionality is green but it seems a good gamble for me count on its progress and its a lot of fun. This code will always be a fairly dumb client pushing a simple submission set. I may never need to build a "server" App, though I hope to use a server based DB eventually.

Thanks!

[ptrex]

@bearcav

Maybe this can help you get started optimizing SQLite

Enjoy !!

regards

pterex

Edited January 7, 2007 by ptrex

[bearcav]

...

Maybe this can help you get started optimizing SQLite

Yes, I've reading up. The following is basically addressing my main concern:

Client/Server Applications

If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

We have file locking problems on our Novell servers. I'm not a Novell expert by any means. Really no one in our camp is, though the ones that set up the server know more than me. In this case Novell errs on the safe side, the problem is that the file cannot be written to because it is locked by another user. It does not get freed until that user is logged out or kicked out manually. I feel like it is somewhat of a configuration error on our part because though it seems to happen somewhat infrequently it does happen sometimes on profilefiles that are in fact "read only" for those users groups.

So I expect some trouble in that regard and am considering what to do to make the App robust. Since SQLite dbs are small, as is their overhead, I am considering separating sections into separate files and/or possibly still using append to csv for "change submissions". In this case I would then need a single user "administrative cleanup app" and, if used, a regular read on the csv file for data changes in progress. A lot more coding for sure, but it could be done in a way to make the transition to a server db smooth and enterprise ready. I am trying to avoid a lot of upfront code cost and yet keep myself away from an eventual complete rewrite.

Hence the more I think about it the more I think the problem of lockout should be considered in my code with the idea of modifying the code "if it gets to be a problem". Still someone else's real world experience in the matter would be greatly appreciated!

Thanks for the tip!,

Barry