RFC: Provide mechanism to fully separate data in database per model

[michalkleiner]

It might be a long shot, but...

It is currently possible to define a table name for a data model via private static $table_name. The ORM and the db layer use that and/or derives the table name from the model class for db related operations. It then decides which column from which model in the inheritance chain goes into which database table, and joins the tables by class and ID (generally speaking) when needed.

Would it be possible to allow (opt-in perhaps?) mechanism to store a single data model data in a separate table, holding all required columns?

The goal, perhaps should have been mentioned as the first thing, is to separate data, provide a set of known tables per model (e.g. its base table and tables for M:N relations).

Under the suggested behaviour, a NewsPage type (inheriting from Page, inheriting from SiteTree) would have all data stored in NewsPage db table, no partial data in Page table and no partial data in SiteTree table.

[dhensby]

So to move from Multi Table Inheritance (MTI) to Single Table (STI)?

While there might be uses for this, I'm not sure there's much of a case to actually support this explicitly in the framework. We shouldn't get in the way of the schema builder being able to return 1 table for the entire hierarchy, but I don't think we should be taking on the maintenance burden of it.

Really this touches on another aspect, though, which I would like to see and that's a greater abstraction between the model and their schemas and the underlying storage schema. This touches on my attempts to replace our DB layer with doctorine/dbal, but it really is a huge undertaking because our (Data)Model layer is actually very tightly coupled to not only our DB layer but SQL in general.

I also feel that MTI is one of SS's unique features that separates it from other frameworks.

[robbieaverill]

Cool idea @michalkleiner!

I see your goal statement, do you have an example of why you'd need to do this?

If the goal is to have data in flat tables perhaps you could do that with some kind of indexing service rather than changing the DB schema for Silverstripe.

My initial thoughts on this are (a) why, and (b) this sounds like it would make Silverstripe's job of constructing joins etc quite difficult.

[dnsl48]

Yes, while some other frameworks definitely support that, I agree with @dhensby that it may not be worthwhile without introducing a proper dbal first. Meanwhile, perhaps some benefits of that might be achieved by leveraging php traits instead of the usual model inheritance?

[michalkleiner]

Thanks for the reaction, @robbieaverill and others.

The main starter of the idea was a client considering the possibility of cohabitation of multiple related sites in a single codebase. They know about the subsites/multi-sites approach and didn't want to use it, though they were still interested in seeing if there were other ways possibilities.

Whether it was somehow achievable through specifying the custom table names for custom data models (since that already exists, but might be slightly misleading to folks not familiar with how Silverstripe works behind the scenes), making it easier to separate different content domains from a single codebase/database into several instances, e.g. for testing, anonymisation, selective backups etc.

An auxiliary solution, for example, an indexing service, won't deliver in this scenario, it would need to be within the realms of how Silverstripe works with its database.

Similarly, I don't think PHP traits would help here, @dnsl48, but maybe I can't foresee all the possibilities of that.

I also feel that MTI is one of SS's unique features that separates it from other frameworks.

Exactly, @dhensby, it makes it unique, but it also complicates things (from certain perspectives). Every non-standard solution has its pros and cons.

Looking into a dbal sounds like a good long term plan, though I'm not experienced enough in terms of core dev to be able to confidently grasp it, 'own it' and/or contribute to any ongoing exploration work of dhensby's.

[dhensby]

I'm not sure I'm fully grasping the multiple sites in one repo concept, do you mean many sites that run off one codebase, or many sites that run off one DB, or literally a repo with lots of separate code bases in?

I do have a project where we have one repo that powers many different sites, the sites all share some common models and even common content (there's a "master" site that is used to edit shared content and all the others then pull that content in). This system uses database views for any of the shared tables.

I'm also not sure I understand how MTI is a problem in any of the scenarios you're talking about.

If your want a complete abstraction from sites and their database then you'd probably want to look at implementing a content API, but SS's lack of abstraction from the database layer can make this tricky if you want this content to be a first class citizen (ie: editable / queryable like a DataObject)

[michalkleiner]

Yes, a single code base off one version of Silverstripe in a single instance with a single database.

It sounds very similar to the project you mentioned, with some content shared across the sites and some content unique. (Side note — is there a way to define database views through Silverstripe, or they need to be done separately through a database management tool?)

With MTI, you can't simply lift and shift custom page types and a number of tables associated with them and expect the content in these tables to be just and only the content for these.

Basically it's not that much about the content abstraction as it is about how Silverstripe works with the database and how tied SS is with the db.

[dhensby]

(Side note — is there a way to define database views through Silverstripe, or they need to be done separately through a database management tool?)

Not in a way that is abstracted, no. You can just run the raw SQL queries. The project I work on has a deployment tools that sets up the views before running a dev/build.

With MTI, you can't simply lift and shift custom page types and a number of tables associated with them and expect the content in these tables to be just and only the content for these.

Yes, I suppose that's true when you have sub-classes of SiteTree and you only want to pull one page type out. For that, you need a tool to extract the data or, again, create a view that you could run an SQL dump against.

[michalkleiner]

Closing this for now, not really a priority.