Using SQLite vs. MySQL for this project

[kittrellbj]

I am working on a new PHP application (well, a huge revision of an old application), and I have a decision to make. I'd like to get a few thoughts on this decision from more experienced coders before beginning since, if I start one way and finish, it would be troublesome to change methodology after that point.

 

Basically, I have clients that will set up an account on the server and be allowed access to an area where they can upload files, format data, and the software will compile all this together into a package for them based on a cron job's packaging instructions. The cron is more or less a queue controller to keep the system from getting bogged down since part of the service is free to use. Oh, I'm getting off on a tangent...

 

Here's the crossroads that I'm at now:

 

- The plan is to create a database of some kind for each user to keep their information separate from others. The materials being uploaded/packaged are copyrighted by the account holder, so the need for isolation is paramount (someone can't break into one database and get everyone's copyrighted works, for instance).

- The separate databases issue keeps the system from becoming corrupted for everyone in case something goes wrong. I'd much rather deal with one client's corrupted database than having the whole service fall apart because something went wrong for one person.

 

I know that SQLite seems to fit the bill for the above two primary requirements, and it also sounds good for other reasons:

- transaction locking for a reader/writer: only one person per database, so they will either be reading or writing at any particular time, and no standing in line waiting for others to get done. I know that it's only milliseconds to perform transactions, but data safety is of the highest importance.

- lightweight and can be configured easily. (Well, it's technically already configured with PHP 5 anyway.)

- can easily create databases on the fly based on user sessions. (i.e. I can name a database md5($_SESSION[$id]) or something similar to make each page modular very quickly. I don't believe MySQL allows this (easily).

- each package can easily be created in its own table. Of course, MySQL is easily capable of this.

- the data for each package could go as high as 2 MB into the table if I put all the data in there (but I'm thinking of doing flat files that are mapped from the database instead to keep the database more compact.)

 

The stats for this service will probably be less than 100 users active at any given time, and that is a high-end estimate if it becomes ultimately popular all over the place. Such is the nature of the niche market for this tool and the small community that exists in need of such tools. Any thoughts?

 

Note: I can answer any further questions as they come along, but I can't add any more in this first post. For some reason, this textarea hops back and forth to the top each time I type a letter.

[kittrellbj]

Ugh, this should be in Application Design. Sorry about that, it's been a little while since I've been around.

[gizmola]

- The plan is to create a database of some kind for each user to keep their information separate from others. The materials being uploaded/packaged are copyrighted by the account holder, so the need for isolation is paramount (someone can't break into one database and get everyone's copyrighted works, for instance).

- The separate databases issue keeps the system from becoming corrupted for everyone in case something goes wrong. I'd much rather deal with one client's corrupted database than having the whole service fall apart because something went wrong for one person.

 

I know that SQLite seems to fit the bill for the above two primary requirements, and it also sounds good for other reasons:

- transaction locking for a reader/writer: only one person per database, so they will either be reading or writing at any particular time, and no standing in line waiting for others to get done. I know that it's only milliseconds to perform transactions, but data safety is of the highest importance.

- lightweight and can be configured easily. (Well, it's technically already configured with PHP 5 anyway.)

- can easily create databases on the fly based on user sessions. (i.e. I can name a database md5($_SESSION[$id]) or something similar to make each page modular very quickly. I don't believe MySQL allows this (easily).

- each package can easily be created in its own table. Of course, MySQL is easily capable of this.

- the data for each package could go as high as 2 MB into the table if I put all the data in there (but I'm thinking of doing flat files that are mapped from the database instead to keep the database more compact.)

 

The stats for this service will probably be less than 100 users active at any given time, and that is a high-end estimate if it becomes ultimately popular all over the place. Such is the nature of the niche market for this tool and the small community that exists in need of such tools. Any thoughts?

 

 

I don't find any of your arguments to be good ones.  SQLLite is a throw back to the days of PC databases like Paradox, Access and Dbase.  They are just profoundly different technology.  SQLLite has no server, so that means that anytime you want to do anything with it, the entire SQLLite code base, as well as all the data and indexes needs to be loaded into memory.  This means that for every script, and every user, you will be loading in all that code and data.  My opinion is that SQLLite is great for quick prototypes, really small quick and dirty apps, or an app that is going to be deployed on clients, and requires a small local database. 

 

-You make a table with myisam and you get set of files for that table.  How is that worse than sqllite having a seperate database for each user?

 

-If your server gets compromised it is not going to matter that you have data in seperate files/tables/databases whatever.

 

-People waiting for resources -- just isn't going to be an issue with 100 users, and more than likely any contention issues will be caused by overall load on the system, and is probably just as likely to appear in SQLLite as it would be in mysql.  If you're highly concerned with contention, then you should use the innodb engine.  You can configure mysql to make tables per file, rather than using the default innodb tablespace, and innodb gives you transactions as well as row level locking.

 

-naming a database after a session id is a horrible idea.  Session id's can and should change frequently. 

 

I would not put blobs in a database, so I agree with your plan to keep them on the file system.  Of course this design also undercuts the importance of concerns about security, since the material you're concerned about will be in directories on the server filesystem anyways.

 

In summary it seems to me that you've assigned advantages to what is in essence a barebones technology, that it does not really have. 

[kittrellbj]

Thank you for your post on this, that is some very good information and brings in some points that I had not thought about before - one being that I assumed PHP already loaded in everything it needed for SQLite functionality anyway. If it loads it only when called, and if the application uses it in each instance for each user, yes, that could cause a problem.

 

For clarity, I wrote the $_SESSION[$id] part without thinking it would be misconstrued. This would be some constant value unique to each user, such as his username, his ID in the system, or a combination of such values. Of course the session id itself couldn't be used; it's hardly reliable.

 

So, perhaps an architecture in MySQL with file mapping will be best, and all users are contained in a single table, i.e. Maps, which contains the information for each of their projects and maps files to the appropriate directory. I have been thinking that I could keep the users' files outside the public_html directory (or above it, I should say) so that they cannot be accessed through a web browser itself. The only way they could get to the files in that case is if someone was able to get an administrator's password who had root access.

[gizmola]

I think you have a lot of good ideas there.  One other thing you can do is -- if this is a software as a service business, you can create a new database and user combination whenever you provision a newcustomer.  This allows you to generate a seperate user/pw for each customer so you get separation there, and you can write that out to an individual customer config file.  The other thing that is nice about that idea is that you can de-provision an entire customer dataset without causing any issues by mysqldumping/bzipping the database and dropping it, and you still have a way to bring it back to life if the customer wants to come back at some later data.

 

For security of assets, yes all the php file oriented routines work filesystem permissions and don't really know about the webroot unless you're ever employing url wrappers.  So this allows you to employ whatever walls you want, although you do of course take the performance hit. 

 

For asset storage you probably should take a serious look at a nosql db like membase.  Zynga is one example of a company using membase to store assets.  http://www.membase.org/

[448191]

If you need to authorize downloads, the alternative to storing assets in a database, no-sql or traditional, is using a "sendfile" variant. Apache has the x-sendfile extension, and Lighttpd and Nginx offer similar functionality.

 

Generally I store Document objects with a reference to files outside of the doc root, then allow normal routing (eg. /dowload/:docid). This action will fetch the file spec from the db and add the headers to let x-sendfile feed the file if allowed.

 

x-sendfile: https://tn123.org/mod_xsendfile/