[development] Database / SQL future thoughts

[Jeff Eaton]

On May 5, 2009, at 4:49 PM, Bertrand Mansion wrote:

> On Tue, May 5, 2009 at 10:55 PM, Earl Miles <merlin at logrus.com> wrote:
>> Bertrand Mansion wrote:
>>>
>>> Actually, "views" are inside the database:
>>> http://wiki.apache.org/couchdb/Introduction_to_CouchDB_views
>>> For example, the Views module would create views in the database
>>> directly, instead of storing SQL queries in a table and executing  
>>> them
>>> later. That's more logical.
>>
>> That's only more logical if you don't actually know what Views  
>> does. Views
>> SQL is not generated ahead of time and then just run on demand, it is
>> created as needed based upon the data available. Quite an amount of  
>> that
>> data can change at runtime.
>
> Yes, it is more logical to have views inside the database, because it
> is a database feature, than to store chunks of SQL and serialized PHP
> in a database table... I guess you haven't thought about what you
> posted before you posted it.


I'm not sure you understood what Earl was saying. Views does not store  
serialized PHP, nor does it store serialized SQL.

Views stores descriptive metadata based on high-level Drupal entities  
(nodes, comments, users, etc), and at runtime builds an appropriate  
query. Oftentimes they are constructed based on contextual information  
available only at runtime (the current date, the ID of the currently  
logged in user, the page that the user is currently on). A stored view  
in a database is useful, but not at all the same thing. Some plugins  
for Views take advantage of the ability to modify the query at runtime  
based on those conditions, so even stored procedures would be an  
uncomfortable fit. Perhaps Views could *build* stored procs and save  
them to the database?

This is what I meant about understanding how Drupal -- and some of its  
most-used subsystems -- work. I mean no offense, and it's not about  
dismissing your suggestions about more efficient storage mechanisms.  
But details matter in these areas. Understanding the current schemas,  
and understanding how they are used by existing tools like Views,  
matters.

Let's have these discussions! But also let's make sure that we know  
what we're talking about, and we're accurate.

--Jeff