Greetings, colleagues,
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?
Regards,