Symphony.

First of all: god, the search function of this forum is unusable. I am 100% sure there are zillions of other threads about this, but I can not find them.

On to the real deal: database abstraction. There have been many attempts at this, with extensions like SymQL and ASDC, and I even remember an IRC discussion about this a few years ago.

Yet, it seems there is not much progress on this end. Because of the very normalised nature of Symphony tables, I can not write a single query from the top of my head. I need to have Sequel Pro, or PHPMyAdmin running on the side to see which table I have to query, and where I have to join, etc.

From what I remember, the main reason not to have something like this in the core is because it is very hard to get right. There are many different situations, and you can never think of them all when you design the abstraction layer, making seemingly simple things very hard.

Now, a few days ago I found idiorm, which is a very lightweight, yet quite powerful abstraction layer. What I like most about it is that it's only 1100 lines long, including comments(!)

I think this could be a very good starting point for a layer in Symphony: add a bit of Symphony sugar (standard relations, where clauses on dates, those kind of things) on top of this lightweight and robust layer, which is also written on top of PDO (double win!)

Just wanted to 'throw the bat in the coop', to see how we all feel about this:)

@huib: does Idiorm come with SQL dialects?

@creativedutchmen: Have you noticed that the Idiorm project is looking for a new maintainer?

If you're interested in taking over maintenance of Idiorm and Paris, please get in touch

(https://github.com/j4mie/words/blob/master/2012-07-15-idiorm-and-paris.md)

@michael, yes I have. Which is one of the reasons I am bringing this up right now.

@remie: it does not come with dialects per se. It supports every DB that speaks SQL by default, because it doesn't do anything fancy, where differences in dialects become apparent. It does have different quote styles depending on the engine you use, though.

In fact, this simplicity is the main thing that has drawn my attention to it. It does not make assumptions out of the box, and it provides very simple methods you can build on, nothing more. This made me think it was a perfect match for Symphony, where all the building blocks are in place, and nothing more.

People also mentioned Doctrine DBAL as another possible choice to achieve a higher level of database abstraction. See last few posts on the related issue on GitHub.

I know, but doctrine is huge, especially compared with a thousand lines which include comments. Building from such a huge project is pretty much always a pain in the ass. I'd rather start with something simple, and expand only where we really need to (by adding queries we often use, maybe even in extensions) compared to relying on a project with a very big codebase, lots of features and bloat we will never use.

I agree. What do you think on PDO implementation in general? Should we do it step by step, implementing PDO first and worry about higher abstraction later? Or do it all at once?

Seems Doctrine DBAL have moved from LGPL to MIT...

Edit: ...and so have Doctrine Common, and ORM too... Edit: ...too fast, because the change is not yet finished... Found this site: http://dlm.beberlei.de/licenses/projects

@jens: From what I've understood, the community wants the query building process to be easier, and safer. PDO can really help with the latter, as you pointed out already, so I definitely think PDO is a big step forward for Symphony.

Now, if we are already rewriting the database layer in Symphony; why not go a tad further, and abstract that, too? For that matter, I do not really care if we build our own or incorporate an existing framework, as long as it makes the overall code cleaner, and query-building easier. By looking at the queries that happen often, and creating standard methods for those I think we can save ourselves a lot of time and frustration in the long run.

So, my vote would be: if we are rewriting stuff, rewrite it properly. We can't expect people to rewrite their code to use the new PDO, then change it again to use more abstract methods.

While we are on this subject; how awesome would it be if the core bundled a few "simple" ways of querying the DB, and extensions/fields could add their own query methods (for the SBL/relations field, for example, this would include multiple JOIN's, for the date field this would be filtering based on date, to name a few). This way your custom datasources can actually benefit from the code already written in the fields (the queries are there, but they can only be used within their context), and make complex queries easier to understand and maintain. Much like we did with the Email Gateways.

This is actually slightly underway with the SymQL extension. I heard rumor from a very reliable source close to the core codebase that SymQL is being beefed up and added to the core at some point soon to make it mush easier for extensions and custom components to use an active record like approach to the database.

The next step logically is to have more abstraction to allow other RDBMSs to be used in place of MySQL.

As it's something I've been on about sine I originally started with Symphony a while back, I nearly weed myself when I heard this.

I say if we continue discussing this here and on the github issue, we can get the right approach.

Thanks John, I'd love to hear more about this. I've never really liked SymQL because I thought it wasn't powerful enough, so a 'beefed up' version sounds really, really good to me!

I love SymQL.

It might be even better, but it is already so much better than "plain nothing".

but it is already so much better than "plain nothing".

Sure, it is better in a lot of ways. It is also worse, if you think about the discussion on a relations field we had earlier. As long as there is something, people will use it and there is no pressure to really fix it, and come up with the 'perfect solution'.

But, maybe you are right: maybe we have to start with a simple step forward, and see where the big gains are, and make our move based on that, rather than trying to get it "right" the first try.

Just a note on the hugeness of the Doctrine DBAL, from what I see the latest from github it is around 950KB. For less than 1MB you get cross platform database abstraction, schema management and tested code for free. Not something you can label as "sucks". This endeavour is not easy, see any other PHP DBAL and see if it is less big with similar features.

@petsagouris, while I understand what you mean, let me put those numbers into perspective.

The lib directory of Symphony (where all the real code is; not the images, not the styles, just the PHP) weighs about 920KB. Adding Doctrine would mean we'd double the amount of code just to speak to the database.

This also means developers have to learn a new API, which most likely is very different from the rest of Symphony; nobody is going to rewrite a project the size of Symphony to Symphony's coding style.

Sure, it might include features, but are they worth it?

Let me give another example of why I think including doctrine is a bad idea; email. A while ago Symphony only supported emails through the email function, or you'd have to bundle your own lib (like the ETF did). When Michael and I started to change this, we could've just included Zend, PHPMailer or any of the libs already out there. Of course we didn't, and we choose to create only the features Symphony needs, which has lead to a very small, very manageable codebase that plugs right into the core. Sure, there are features the current gateways do not support out of the box (retry-on-error, queuing, backgrounded sending to name a few) but if you really, really need those you can always write a new Gateway (or an extension) to do so.

Database abstraction is something we will do ourselves I reckon. It's a great idea to look at all these abstraction layers to get best practices, but as @creativedutchmen says, we will only need to do the simple things we already do.

Lets see what happens with @rowan-lewis' work...

Just to set the record straight, adding a new ORM still won't let you write a single SQL query to do custom things. An ORM is there to do just that, map objects onto a database, so that you work it's the objects only. If you think about it, Symphony is already its own ORM. We have Section, Field and Entry objects which know how to persist themselves into a normalised database.

I don't want to get into the discussions of whether Symphony should use MySQL databases or not, or whether we should use a more advanced database connector such as PDO (for prepared statements, transactions etc). As creativedutchmen hints, what's lacking in Symphony's existing ORM is a powerful API. Originally none of the API was for public consumption, but since we're rooting around in the core more often, we need a better API. This would solve the problems of writing custom logic.

My SymQL (and indeed REST API) extensions so very little beyond repackaging Symphony's own data sources (read queries) and events (write queries). These things themselves wrap up a lot of Symphony logic that is otherwise quite messy. In my opinion all we need to do is beef up the EntryManager class, which presently requires manual building of join SQL statements (which SymQL is the abstraction for) and get this neat API in the core. Rowan took my SymQL idea one step further and added full write support (named SymQuery), so this stuff is possible. It's not only possible, by it's also relatively trivial.

https://github.com/symphonycms/symphony-2/issues/1157

So in my opinion, I'd like to kill SymQL and for Symphony's own objects (the managers) to provide the DSL that SymQL started. Once this is in place it an be expanded upon in the future for things like relationships. It will force people away from using raw SQL (but of course the API might have a "dump" option to export the SQL queries its running, so the developer can run them manually if they wish), which opens doors further in the future for replacing the database entirely, since everyone will be using the API and not interfacing with the database itself.

We can solve this by keeping the same database structure and by improving the API.

We could, of course, rewrite Symphony entirely to use something like the ActiveRecord pattern which is widely supported and implemented (there must be a PHP implementation). But that's a mahoosive task and involves pretty much rewriting the thing from scratch.

I'm in favour of beefing up the existing API to solve the tasks we currently have to form SQL for ourselves (or use SymQL for).

Thinking some more: presently to get entries Symphony first creates a SectionDatasource object which itself builds the correct joins (fields) and where statements (filters), passes this to EntryManager which then runs the queries and returns Entry objects. The logic inside the DS class needs to be replicated into things like SymQL. Meh. In reality Symphony's Entry or EntryManager should handle all of this. A SectionDatasource file would therefore be really basic, it wouldn't contain any logic for building SQL, it'd just insatiate a query object.

Huib: you, myself and Rowan discussed this at length when thinking about a Symphony 3 API remember? Maybe we can find that transcript and see if the ideas still make sense and are relevant here to migrate into S2. I'm pretty sure it can be done in a fully backward compatible manner (eg bring in new classes such as EntryQuery or SectionQuery and deprecate methods in Entry/EntryManager).

Nice :)

I don't want to get into the discussions of whether Symphony should use MySQL databases or not, or whether we should use a more advanced database connector such as PDO (for prepared statements, transactions etc).

I do think this should be on the radar though, it is an important feature.

In my opinion all we need to do is beef up the EntryManager class, which presently requires manual building of join SQL statements (which SymQL is the abstraction for) and get this neat API in the core

The thing is, an ORM architecture is more flexibel and allows extensions developers that use custom tables for their own data storage to use the same interface for connecting to the database. That part is currently missing from this discussion. What you want is to get rid of custom SQL entirely, not only for the Symphony core.