Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Searching Multiple Sqlite Databases vs One Big One
#1
Hi,

[Sorry below is a cross-post. Should have put in Programming not Resources to begin with) :oops:

I have successfully implemented a dialog which fills and displays a QM grid (also part of the dialog) by writing to and from a SQLite database, as posted in http://www.quickmacros.com/forum/showthr...1#pid13261. I have implemented the search function, as in the previous post.

My question is about extending the search query and display to multiple different user's database. Currently each user has their own database to which they write to and display from, depending on their logon user id.

For searching across ALL user databases, I see a few options:

One is to serially search through each individual user's .db3 file and fill the Quick Macros Grid sequentially with those items that are marked Sharable and, of course, meet the search criteria.

The other option is to make a single mega-database that all users write-to (instead of each accessing their own individual one on the network). Thus at time of a user writing-to or calling up their own database, they will really just be seeing a filtered version of the mega-database.

A third option would be a hybrid. Keep the individuals ones but on a continual basis dump to the mega-database which would be just for mega-searching.

Obviously, the tradeoffs would be speed but I can't predict the speed hits of searches. I foresee users having in excess of 2000-3000+ entires (rows with ~15 columns of very short text entries) the total number of users might be over a 100 eventually.

I just don't have a sense yet of numbers for database dynamics.

Also not sure what the "server" side impact of multiple users writing and reading from the same network file.

Any thoughts would be great. Once this is robust, I foresee the option of adding an image column to the SQLite db3.

Apparently, this can be done using this inexpensive software: Image2db

http://www.vive.net/products/image2db.htm

Quote:Image2db is a utility for uploading large binary and text data to the database. It allows you to insert any number of files into the database. Image2db works with BLOB columns. A SQL BLOB is a built-in type that stores a Binary Large Object as a column value in a row of a database table. You can use them to store binary files such as images, pdf documents, Microsoft Word documents, AutoCAD files or, also, plain text data.

Today database driven websites became a standard and more and more webmasters are using databases to store images and other binary data. It is easy, convenient and secure way for everybody who wants to manage large amount of images and/or documents on the website.

Image2db supports Oracle, PostgreSQL, Microsoft SQL Server, Microsoft Access, MySql and Sqlite databases.

If I do this with images (expect 1-5 jpg images of ~ < 200 kb) and maybe a few journal articles pdfs (1-2 Mb average). Probably users would add images in only a small fraction of the cases (at most a 1/3), the db file could get quite large. Does this suggest that I should keep individual users db's?

Thanks for anybody's thoughts on this who has experiences with databases.
Much appreciated !!!

Stuart
#2
I have little experience with databases. I would use single database and maybe store files separately.
#3
I just read that one of the advantages of SQLite is that everything in the database is essentially all in one file and that file can be up to around a Terabyte.
This would be probably be sufficient but could imagine in the scenario where I do store lots of images, that the users could exceed that. So I think I will figure out a way to do it as you suggested with separate media files location but the "index" and regular text informaiton in the sqlite database. Just ordered online some books about sqlite and databases in general so hopefully can make some progress here.

Thanks for your suggestion!
Stuart

ps. Thanks again for QM - the successes I have had in programming with it is are one of the most enjoyable and gratifying things in my life (I don't know what that says about my life!!!! Big Grin , but true nonetheless!!


Forum Jump:


Users browsing this thread: 1 Guest(s)