Symphony.

Hi!

Is Symphony 3 going to use the mysql VIEWS that Mysql 5.0 offers?

The number of queries to the database would certainly be reduced a lot, resulting in more performance…

I would see it like this:

1. Everything works te same as it does now.
2. Added are section VIEWS (which are result tables of them queries to build an entry).
3. Datasources use the VIEWS to get the data
4. Select box link fields would be stored as a list of id’s in the VIEW so only those kind of fields would result in more than one query….

or are there any drawbacks i fail to see?

This assumes that all fields have single values. Only the most basic Symphony entry can be represented as a single database row (one or two columns per field), but the reality is that many fields are complex and are represented un an un-normalised form spread over multiple rows. Anything that can accept more than one value, such as a select box; Select Box Link; tag list; and more complex fields such as Subsection Manager and Meta Keys… these are not represented by a single column.

I know little of views, so would they be applicable in these situations?

Would it be acceptable to drop MySQL 4 support?

Many of Symphony 2’s queries are caused by having to resolve the structure of the database before content can be queried. This is eliminated in S3 because the structure is defined in XML, so these queries can be disregarded straight away. I believe Brendan has done some work on query refinement so that the data for each field (across multiple entries) is only one query. So if you have 3 entries comprising 10 fields, to get the data you’d need 10 queries.

I’ve tried to implement Views into Symphony before. It’s tricky because:

• As Nick has mentioned, fields have multiple columns, all named the same in most situations (ie. value, handle), so when joining the tables to complete the view, everything has to be custom aliased.
• A VIEW is technically a complicated join, in some instances this will run slower than simple queries. This is something Symphony has in it’s favour, although there are a number of queries, almost all are simple SELECT which and fast and can be easily optimised. VIEW’s don’t utilise Indexes, which again can affect performance.
• It won’t be possible to INSERT into a VIEW (multiple tables)

I can’t see a benefit in using VIEW’s for datasources, but I’d love to hear one, my experiments were spurred by just wanting to play with VIEW’s.

With all of that said, I believe VIEW’s could actually play a part in User Access Control, for instance, a AuthorSections, DeveloperSections VIEW, which would limit the access a Symphony backend user would have to certain sections.

In regards to MySQL 4 support, I definitely think it could/should be dropped. Foreign Key support is something that I think will come up again with S3 as the Section Associations stuff is relooked at. I believe Transaction support (ACID) will definitely play a part in Symphony being more usable in enterprise applications.

Triggers again could be useful for the elusive Symphony shopping cart.

Hm, i was thinking about VIEWS as some sort of cached result-set containing the unfiltered datasource-data as it appears in the XML output. Still leaving the issue of some field-types.

My (limited) understanding of VIEWS was that they only had to be updated when any of it’s “source tables” got an INDERT, DELETE or ALTER… so it’s basically a physical table and those complex joins and stuff are ran only once after a change has happened…

And yes, MySQL5 support should be in S3, it would add a level of reliability to symphony.