How can I get some more RDBMS functionality but still use SQLite?
I decided to use SQLite for my personal development project because it just popped up from my computer, apparently of its own accord, and I wanted to know more about it. The problem is that I'm starting to miss a lot of the features I'm used to, super heavy RDBMSs: stored programs, constraints, DRIs, complex data types like DateTime, etc.
I really like the concept of "database as a file", but I find myself implementing a lot of integrity features from scratch as I go.
So, as the name says, I'm curious: is there more features in the product that I just don't know about? Are there tools or libraries (preferably for .Net) that are added to functions as needed? Is there another version that is slightly more reliable?
I am using Mono 2.4 and Mono.Data.SQLite.
source to share
There are at least a few ways to enforce foreign key constraints . This page explains how to do this with triggers, and links to at least two sites that will convert your normal foreign key constraint to a trigger, programmatically for you.
Since SQLite uses a character set, you can store any type in any column. You don't need "complex data types like DateTime" because you can store blobs (serialized data) anywhere you want, or you can store whatever you want as a string. Note that there are a number of datetime functions in the core of SQLite - they just work with numbers or strings (or both due to auto-casting), depending on the function. There is no real need for a date type when there is enough string here. This is one of the ways that SQLite remains so small and portable.
source to share
I think the key is in the name, SQLite!
It's designed to have enough functionality for built-in use, but a little more. But it's fine to add extra functionality if your application needs it.
There is a database alternative you could embed though.
source to share
SQLite, unfortunately, just doesn't support some of the nice features you're used to. You just need to get used to working without them if you want to use it.
However, there are other single-file DBs that work in this way. For example VistaDB supports Mono (it's 100% managed) and provides stored procedures, constraints, etc. It shares much of the MS SQL feature set, including having a very similar interface (although there are subtle differences).
source to share
Some of the features you mentioned are mainly due to the usual client / server separation. with an embedded DB library like SQLite, it is no longer client / server, it is a layered architecture.
For example stored procedures. What difference does it make if SQLite manages them or if your program does it? they are both in the same process and in the same memory space! it's just a layer on top of the storage. Better yet, you can use your own language instead of some "procedural SQL". Likewise for complex types and constraints.
just a few caveats:
- You need to write the missing layers, SQLite does not provide them. (OTOH, they will suit your tastes and needs).
- To truly handle constraints in your own layer, you need to make sure you are making all calls through your layer.
source to share