Regenerate view or table documentation

Hi! MySQL before version 5.0 didn't support the CREATE VIEW statement, so if you're running MySQL 4.1 (which a lot of people are), you need to use a table. If you're running MySQL 5.0+, you should use a view.

The difference between them is what happens when a user is added, or a user changes his information.

A database view is basically a SELECT query that the database stores and treats as (well, mostly as) a table. The database can optimize and cache the results, but whenever anything changes that would affect the data in the view, it gets automatically updated (well, kinda-sorta; it's a bunch more complicated, but this is the effect of using a view). So when a new user is added, or a user changes his profile information, the site user list will still be correct and up-to-date.

However, if a table is being used instead, that table reflects a snapshot of the data at the moment the table was created. So if a user is added, or a user changes her profile information, the table doesn't change, and the site user list is now out-of-date. To capture the new information, you would need to re-run the CREATE TABLE AS SELECT statement used to get the information in the first place. In this case, it's highly suggested that you "use cron" to rebuild the table, because this way your data will be at least no older than the last cron run.

On the other hand, if your users aren't changing data, and no new users are being added, the table could be a lot faster to get data from...

The 4.7.x version of this module isn't really smart about the database — it doesn't know about database versions and will present you with option to use a table or a view regardless whether your database supports the view.

I should note, however, that this problem only affects MySQL. The version of PostgreSQL required by Drupal should work just fine with a view.

Does this make things clearer?

Ricky

Hi! The 4.7.x version of this module is my original idea of how this would work. I designed it very closely for the needs of my site. When I released it to Drupal, I didn't realize that MySQL < 5.0 didn't support views, so I needed to fix things very quickly to make them work. It was relatively easy to add in the ability to work with a table, as the SQL is very similar (CREATE TABLE instead of CREATE OR REPLACE VIEW, and DROP TABLE instead of DROP VIEW are the only syntax differences). I wasn't sure if MySQL < 5.0 supported sub-selects in the FROM clause, because the last time I had worked with MySQL before having 5.0 it didn't support sub-selects. So I put in the table support and left it at that (oddly enough, cron was already supported, for other reasons).

I recently rewrote the 5.x version, basically from scratch, and this time I knew more about what I wanted to make it capable of. I also did some research and found that MySQL 4.1 is where they introduced the ability to do sub-select queries. So the new 5.x version is smart enough to prefer queries where it can, and only drop back to tables for MySQL <= 4.0.

Unfortunately, I'm no longer running 4.7.x, so I can't really justify to the people at work backporting the changes from 5.x to 4.7.x (there's no real reason it couldn't be done though). If someone had patches, I would be more than willing to commit them, but until then, the 4.7.x version is basically dead-ended (unless some very obvious low-hanging fruits show up...).

Sorry,
Ricky