[RFC] Database Abstraction Proposal - Request for Comments (Devs and DB Admins only!)

dbrobins Wrote:Unless there's a good reason why this is infeasible

It's definitely feasible.

dbrobins Wrote:Things are already being done quietly by firnsy or others

Indeed things are happening in the background, though these are focussed towards a completely revised schema. You'll be happy to know that FOREIGN keys are leveraged heavily in it. Currently I'm benchmarking an EAV/CR schema that will provide foundation for all content (eg. audio, video, pictures, etc).

dbrobins Wrote:I will attempt to add foreign keys in my database (using SQLite), and, all going well, make a patch to create them in the table creation/update code and add it to a Trac ticket. (And perhaps implement some of the other ideas above too.)

CC me on the trac patch and I'll review it for inclusion.

firnsy Wrote:Indeed things are happening in the background, though these are focussed towards a completely revised schema. You'll be happy to know that FOREIGN keys are leveraged heavily in it. Currently I'm benchmarking an EAV/CR schema that will provide foundation for all content (eg. audio, video, pictures, etc).

CC me on the trac patch and I'll review it for inclusion.

If you're redesigning the entire schema, is it worthwhile for me to make a patch that adds foreign keys that are only going to be entirely replaced? If you have a repo where you're working on this, I'd like to help, or at least see how things are going.

I also discovered that SQLite spells "alter table X add constraint" as "rename table X to oldX; create table X with constraint; copy rows from oldX to X; drop table oldX", which makes it worse. Not impossible, as that could be done as part of the upgrade process.

Having appropriate foreign keys with cascading deletes will also mean that a lot of the cleanup code will go away - no need to explicitly delete from the link tables - although it will still be necessary to clean up referenced tables like actors.

dbrobins Wrote:If you're redesigning the entire schema, is it worthwhile for me to make a patch that adds foreign keys that are only going to be entirely replaced?

Of course its worthwhile, the redesign won't happen overnight. It would be nice to have it completed in time for Eden, but there is a lot of work to do in the meantime.

dbrobins Wrote:If you have a repo where you're working on this, I'd like to help, or at least see how things are going.

No repo, just yet. It's all in one complex python script. I'll be updating the wiki page with the latest schema revision in the next few days.

dbrobins Wrote:I also discovered that SQLite spells "alter table X add constraint" as "rename table X to oldX; create table X with constraint; copy rows from oldX to X; drop table oldX", which makes it worse. Not impossible, as that could be done as part of the upgrade process.

Indeed, it's very eek!

dbrobins Wrote:Having appropriate foreign keys with cascading deletes will also mean that a lot of the cleanup code will go away - no need to explicitly delete from the link tables - although it will still be necessary to clean up referenced tables like actors.

You know, I only just learnt this the other week. That and indexing your foreign keys. I love learning new things.

One thing that I hope the new schema can handle is allowing for some sort of proper ordering of things like actor links (eg, listing the most important ones first).

In SQLite you can "ORDER BY rowid" to get things back ordered by in the same way they were inserted and this makes more sense to me than ordering by the id (eg idActor). Unfortunately mysql doesn't seem to have something similar, so a column of row numbers may be necessary. In fact I think this was the way it used to be before it got changed to ensure mysql compatibility.

firnsy Wrote:Of course its worthwhile, the redesign won't happen overnight. It would be nice to have it completed in time for Eden, but there is a lot of work to do in the meantime.

OK, I'll put it on the list and see what I can do. Thanks for offering to review it for inclusion.

Quote:No repo, just yet. It's all in one complex python script. I'll be updating the wiki page with the latest schema revision in the next few days.

If you're interested, I've been using SQLAlchemy (Python db toolkit) to model the XBMC video database for my own work. SA can build DDL from the model. Since you're using Python too, you may be able to make use of parts of my model. SA also offers cascade functionality even if the db isn't set up that way; right now if I delete a row from files it will also queue the relevant settings, streamdetails, movie and *linkmovie rows for deletion (it can also be told that the database will handle it, when foreign keys are in use, and not try to do the deletes itself).

marirs Wrote:One thing that I hope the new schema can handle is allowing for some sort of proper ordering of things like actor links (eg, listing the most important ones first).

Sequences are handled.

marirs Wrote:In fact I think this was the way it used to be before it got changed to ensure mysql compatibility.

In fact, I don't think it was. Ordering was/is mapped by the way it was inserted.

dbrobins Wrote:If you're interested, I've been using SQLAlchemy (Python db toolkit) to model the XBMC video database for my own work. SA can build DDL from the model. Since you're using Python too, you may be able to make use of parts of my model. SA also offers cascade functionality even if the db isn't set up that way; right now if I delete a row from files it will also queue the relevant settings, streamdetails, movie and *linkmovie rows for deletion (it can also be told that the database will handle it, when foreign keys are in use, and not try to do the deletes itself).

I have looked at the SQLAlchemy code. Python has been used to identify the methods needed to be implemented in C++ once the schema is ready. The new DB will come with an internal query generator to dynamically shape the view(s) as content objects are manipulated.

In addition, content objects and relationships will become more manageable and loosely follow an OO methodology.

firnsy Wrote:I have looked at the SQLAlchemy code. Python has been used to identify the methods needed to be implemented in C++ once the schema is ready. The new DB will come with an internal query generator to dynamically shape the view(s) as content objects are manipulated.

In addition, content objects and relationships will become more manageable and loosely follow an OO methodology.

What do you mean about methods that need to be implemented in C++? I would encourage you to openly discuss details of the redesign - maybe in a new thread - rather than working in silence for a long time then coming out with a fait accompli. You can of course do what you want, but designs benefit from early discussion. I'm sure there are plenty of other database architects and developers here that would be interested. It wouldn't need to become a "too many cooks" problem, either - you could reject any suggestions or ideas you didn't like. Including this one.

dbrobins Wrote:What do you mean about methods that need to be implemented in C++?

XBMC is written in C++.

dbrobins Wrote:I would encourage you to openly discuss details of the redesign - maybe in a new thread - rather than working in silence for a long time then coming out with a fait accompli.

Short of providing you a remote desktop connection to my PC I'm not sure how much more open I could be [TIC]

http://forum.xbmc.org/showthread.php?tid...tabase+4.0

firnsy Wrote:XBMC is written in C++.

Ya, I know that , I was thrown off by the phrasing "methods that need to be implemented in C++" rather than "code" or "classes", say. I understand now that you just meant updates to the general C++-database interface.