Help to rewrite the whole SQL Database?

It's great to see some new discussion on this front!

IMHO one thing that needs to be done, is to make the db schema less video-centric.
Specifically the content table has fields (plot,tagline,season,episode,etc) that do not apply to non-video content. Also it is not very clear what is defined as content record. Are the movie actor or music artist data stored there as well? I guess not, but then an artist table is missing.
Perhaps the first step would be to define in detail, all the entities (movie,tvepisode,music video clip,song,picture,artist,file,etc) and their collections (tvshow,season,album,disc,photo album) that need to get stored in the db, together with their attributes. After that the assignments to tables and columns can be done with precision.

Also, is this db going to replace all the currently separate dbs? It'd be great to have only one db. It also helps to see the big picture. Currently I have: commoncache.db, Addons15.db, Epg7.db, MyMusic18.db, MyPictures.db, MyPrograms6.db, MyVideos61.db, Textures6.db, TV20.db, ViewModes4.db

(2012-04-10 08:23)jmarshall Wrote:  Another idea is just dropping SQL and using a document store (eg Kyoto Cabinet with a JSON document for value). Though we'd lose the experience of those that deal with SQL, we might gain with a c++ style interface (map:reduce implementation, cursors etc.)

i'd probably suggest that this suggestion would be a bad thing in terms of running a single central database feeding multiple client machines. also, it's trivial to put a json interface in front of the database itself for retrieving the content thereby allowing you to entirely abstract away the database itself from the code - all communications would be through the json layer, which means that you could switch multiple database back-end code in and out based on usage scenarios or user preference, and in theory enable further support of more back-end data stores as users decide to code them up.

that, however, would require a database api to be created, which i'd suggest might be a better place to start - the api would likely inform the database structure to a large extent. abstracting out the database api would also have the added benefit of allowing other mechanisms (e.g. a server-side database updater, a library editor etc) to be built independently of xmbc itself. you could expose the database access layer via an api or a library and have it running as a back-end service independently of the rest of the xmbc code. i'd also argue that would have the added benefit of compartmentalisation, too, in that developers could work on that without impacting the rest of the system, which isn't really possible at the moment.

as to getting involved with this, i'd be happy to join in - but i'll have to spend a bit of time to familiarize myself with the work that's already gone on here

Why not consider using a NoSQL database like CouchDB?

• Everything is stored as an JSON document, no need to maintain a strict schema
• This allows new add-ons to leverage the database without making structural changes
• is fully accessible with web-service calls
• is built to be used in offline mode
• is multi-master with replication so every end-point can have a copy of the database eliminating single points of failure
  

-

Because it's not embeddable.

(2012-05-31 06:23)jmarshall Wrote:  Because it's not embeddable.

Forgive the ignorance - but what do you mean by 'embeddable'?

Able to be used from within an executable, without a separate server installation. Like sqlite3 is an embeddable SQL database. Similarly, the closest embeddable thing to a nosql database is something like Kyoto Cabinet (it's an embeddable key/value store)

CouchDB for example relies on erlang being installed, which is not a workable solution. Most other NoSQL databases are similar.

IMO if we go that route, we it might be best to just use an SQL table as the document store (eg store JSON or similar) with index tables for the relationships. This would require quite a bit of thought to get completely right, however.

Cheers,
Jonathan

There are solutions out there for accessing SQL databases as file systems (ie, they are exposed as File Like Objects with open(), read(), and the whole 9). They're all uselessly slow, but it's been done =)

MongoDB claims to fit the bill, but I've never used it myself =(

id suggest to use Neo4j which can be embedden in python. Built in java thou but its really fast and extremeley flexible.

-rog

How about just (documented) interface? And by default use embedded sqlite "interface".
Then it should not be problem to build non-sql/sql or even ldap interface to interact with data.
Though interface need to be much higher level stuff, so is this already something which might be more similar with http RPC API or even extend RPC API for this?