Virtual RDBMS over SQLite
(1.1) By imaginetables on 2021-07-29 08:39:41 edited from 1.0 [link] [source]
I'm working on a pet project that I call 'virtual RDBMS'.
It's DBMS because it operates user data using a very special command language (I'm experimenting with some no-SQL ideas).
It's Relational because user data are organized in tables with columns of predefined types (not exactly as in SQL, but compatible ones, my focus is on being user-friendly, so the types are: number, datetime, short text, long text and so on, so they could be easily mapped to SQLite types). And, of course, any table can refer any other table.
It's 'virtual' because I don't want to implement data storing myself. What I really want is to focus on my command language design, so I'd prefer to use SQLite under the hood.
The question is what's the best way to map 'virtual DBs' (created by my users) to a real database(s?) in SQLite?
Just for information, how it is implemented in WSS (Windows SharePoint Services) 2007, which can be called 'a virtual RDBMS over MS SQL Server'. One can create/edit/delete data using GUI or CAML language, but in fact the data are stored in the 'WSS_something' MS SQL database. They have a huge table 'AllData' with columns 'TableId', 'Int1', 'Int2' .. 'Int64', 'String1', 'String2' .. 'String64', 'Bool1', 'Bool2' .. 'Bool64'. (Real names can differ, but you get the idea). Also, they have a table named 'Tables' with columns 'Column1Name', 'Column1Type' .. 'Column64Name', 'Column64Type', describing user tables. When the user creates a new table ('List' as they called it), a new record is added to the 'Tables'. When user adds a new record ('List element') to an existing table ('List'), a new record is added to the 'AllData'. Is that the way to go? Obviously, the user cannot create tables with more than 64 columns (actually, to be accurate, for some types more than 16 columns). I wouldn't like to limit my users neither with column count nor record count.
Maybe, there are better ways exclusively in SQLite?
Is SQLite a good choice for the task at all?
The only advantage of the design you describe is that the schema is fixed,
you never have to do any DDLs, only DMLs. But that seems like a pretty terrible schema IMHO.
Is DDL not transactional in SQL-Server like in Oracle, but unlike SQLite and PostgreSQL?
That could be a reason for such a design maybe.
It's up to you whether to use a single table, many tables, or even several different DB files too.
Unless you have thousands of tables, SQLite will be fine. SQLite is optimized for many-rows, not many-tables,
still it will do great with a few hundreds to thousands of tables, just don't go into the dozens of thousand tables (or shard them on several DBs).
Have fun! --DD
You didn't say what you were developing it in/for.
Handles all the native types date, number, string, blob (typedArray), etc... has a small create table parser which can take a create table statement, create it if it doesn't exist, and update/add columns/indexes that are new...
basically 1 open command, and 1 do SQL command command.
(4.1) By J.M. Aranda (JMAranda) on 2021-08-09 06:28:18 edited from 4.0 in reply to 1.1 [link] [source]
A few weeks ago I had a similar idea. I searched the net and discovered that a single table database was "very popular"(1). My idea was something like dbm, string/string. I downloaded gdbm, ndbm, cdbm, kissdb, and even found the original dbm. But a hash table is unpredictable. A BTree is more serious. An old dilemma. "Gentlemen prefer bonds" (JP Morgan) Anita Loos: "Gentlemen prefer blondes". (1) - Correction: "used sometimes". Sorry. :)
I searched the net and discovered that a database with only 1 table was very popular
It's not very popular at all, and you certainly did not find that on this forum.
I mean I'm sure you can find examples of people doing it, but that's like saying the act of strapping a Deer to the hood of your car on the drive home after the hunting trip is very popular because you can find many pictures of idiots who did that on the internet. While it is true that you can find many examples/pics of said idiots, it must never be mistaken for popularity with people in general.
I can't speak for all DBs, but for the ones I do use (SQLite, MySQL/MariaDB, Postgres, MSSQL) I can promise you there is no advantage to any DB having one table per schema precisely, as opposed to two or more. In fact, I can point out many disadvantages.
I feel less strongly about the other possible meaning you could have intended, namely that using a set of single-table files is one way of solving a multi-format virtual/object model. Perhaps "also-used-sometimes" is better phrasing than "popular" - plus I still fail to see a significant advantage. [PS: If there is a tangible advantage that can be shown, I'm very willing to be corrected and very interested to hear about it.]