eZ Community » Blogs » Harry Oosterveen » Make life easier with SQL Views

By

Make life easier with SQL Views

Thursday 15 November 2012 12:39:05 am

  • Currently 5 out of 5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Every now and then, I find myself exploring the database behind eZ Publish, trying to do some obscure queries, or just because it is quicker to do the queries in a database tool than to write a script to do it and use the regular fetch functions.

There are more than 100 tables, and you often have to combine a few of them. You may have to write complex and long queries. Good SQL tools have the option to store your favourite queries, it saves you the typing, but the queries are still long and complex.

Using SQL views, you can use the result set of such a query and  create a virtual table with always up-to-date data. Now you can treat a complex query as if it were a simple table. 

Let's start with a simple example; a query I often use is to get the class attributes of a certain class.. Rather than typing the full query each time, I create a view:

CREATE VIEW ezclass AS
SELECT ezcontentclass.id AS class_id
 , ezcontentclass.identifier AS class_identifier
 , ezcontentclass_attribute.id AS attr_id
 , ezcontentclass_attribute.identifier AS attr_identifier
 , ezcontentclass_attribute.data_type_string AS data_type_string
FROM ezcontentclass
LEFT JOIN ezcontentclass_attribute
 ON ezcontentclass_attribute.contentclass_id = ezcontentclass.id
WHERE ezcontentclass.version = 0
 AND ezcontentclass_attribute.version = 0
ORDER BY ezcontentclass.id
 , ezcontentclass_attribute.placement;

Now every time I need to list the attributes of, say, a folder, I can simply use the query:

SELECT * 
FROM ezclass
WHERE class_identifier = 'folder'

Note that this is a virtual table--no duplicate data is created, and hardly any additional storage space is needed. Each time you use the short query on the view table, the full query is executed on the original tables.

This is different from an additional table. You could get the same result by creating an additional table, e.g. 

CREATE TABLE ezclass2
SELECT ezcontentclass.id AS class_id
 , etc.  
SELECT * 
FROM ezclass2
WHERE class_identifier = 'folder'

The result will be the same, but there are two differences:

  1. ezclass2 stores the data in a table, increasing the storage space
  2. ezclass2 is not updated; if data in the original tables is changed, you need to manually update the additional table.

A more complex example is the following, where I want to list the nodes where a certain URL is linked to in the content. The query combines five tables:

CREATE VIEW ezlink AS
SELECT ezcontentobject_tree.node_id AS node_id
 , ezcontentobject_tree.parent_node_id
 , ezcontentobject_tree.contentobject_id
 , ezcontentobject_tree.contentobject_version
 , ezcontentobject_tree.contentobject_is_published
 , ezcontentobject_tree.depth
 , ezcontentobject_tree.path_string
 , ezcontentobject_tree.sort_field
 , ezcontentobject_tree.sort_order
 , ezcontentobject_tree.priority
 , ezcontentobject_tree.path_identification_string
 , ezcontentobject_tree.main_node_id
 , ezcontentobject_tree.modified_subnode
 , ezcontentobject_tree.remote_id
 , ezcontentobject_tree.is_hidden
 , ezcontentobject_tree.is_invisible
 , ezcontentobject.owner_id
 , ezcontentobject.section_id
 , ezcontentobject.contentclass_id
 , ezcontentobject.`name`
 , ezcontentobject.current_version
 , ezcontentobject.published
 , ezcontentobject.modified
 , ezcontentobject.`status`
 , ezcontentobject.language_mask
 , ezcontentobject.initial_language_id
 , ezcontentobject_attribute.data_type_string
 , ezcontentobject_attribute.language_code
 , ezcontentobject_attribute.contentclassattribute_id
 , ezcontentobject_attribute.data_text
 , ezcontentobject_attribute.data_int
 , ezcontentobject_attribute.language_id
 , ezurl.url
FROM ezurl
 , ezurl_object_link
 , ezcontentobject_attribute
 , ezcontentobject
 , ezcontentobject_tree
WHERE ezurl.id = ezurl_object_link.url_id
 AND ezcontentobject_attribute.id = ezurl_object_link.contentobject_attribute_id
 AND ezcontentobject_attribute.version = ezurl_object_link.contentobject_attribute_version
 AND ezcontentobject_attribute.contentobject_id = ezcontentobject.id
 AND ezcontentobject_attribute.version = ezcontentobject.current_version 
 AND ezcontentobject_tree.contentobject_id = ezcontentobject.id

Once the view is created, I can find the objects where a link  to this site (http://share.ez.no/) is used:

SELECT *
FROM ezlink
WHERE url LIKE 'http://share.ez.no%'

After having defined a few handy views, life has become so much easier!

But thinking about it, makes me wonder, why are views not used in the eZ Publish kernel?

I can think of a few possible reasons (compatibility between different databases, views were not available in earlier versions of databases, performance issues) , but don't know the real reason. Anyone who can shed some light on this?

And the more relevant question: does it make sense to start using views in the kernel?

Proudly Developed with from