Suggestion for DefineSchema()

dietbrisk

This is a serious consideration. Each time you call DefineSchema, it taxes the database on each pageview for every model being used. In heavy production sites, the temp data available in MySQL is really time, and a simple query as "show columns from GDN_TableName" (specifically, User) throws me:

mysql> show columns from GDN_User;
ERROR 1030 (HY000): Got error 28 from storage engine

This is personally out of my control, but it's crashing the forums whenever a handshake occurs for a logged in user, and has an ugly error message on the index page for guests. (On a sidenote, turning debug mode off does nothing. The error messages still show)

To me, this is unacceptable. Instead of querying and taxing the database for a schema that rarely ever changes, the schemas should be stored on flat-files in a directory; this surely leads to less connections, less intra-server traffic, less senseless queries, and faster loading time. The schema doesn't change, to my knowledge. And if it does, due to any plugin, then the flatfile should be changed. Please take this into account.

I'm going to be spending the next few weeks figuring out how to make the SQL models read a flatfile of schemas instead of taxing my database, and this is setting me back a ridiculous amount. I need to start working on other projects.

Mark

That's a good idea, and we considered doing it a while back, but eventually decided not to because we found in our initial tests that it rarely had to go to the schema (only when validating values for saving, not when selecting). We already have the caching mechanisms in place, so taking this the extra step shouldn't be difficult.

dietbrisk

When I put the forums into production, it kept trying to read the schema of the the Users table, so guests saw an ugly error, and logged in users saw nothing but an error (I was using a modified handshaking method - directly checked against our product's users API instead of fsock'ing a file for improved performance). The MySQL table has no extra space, so it failed when it tried to do "show columns from GDN_User"; this turned out to be a fault of the ITs, not letting us know the server the database went to wasn't so good; it hasn't had many problems since.

However, I'm still worried about the fact that it's doing a taxing query on every pageview for the User table. I've worked with out MVCs - i.e., Symfony - and they store table schemas as YAML on flatfile, which it actually uses to build the tables (with the help of certain commands), as well as to build the base classes (to avoid magic method calling, I assume), and also serves for the MVC to know the structure of the tables.

Just my two cents.

dietbrisk

Thanks for your response, by the way

Mark

We also build our tables from what we call "structure files". Every application has a /settings/structure.php file that defines it's tables (or alters other application tables). I don't know if it would be the best place for us to go check on the schema, though - as it's really not built for that. @Todd handles all of our database libraries. I'm gonna see what he says about all this.

Todd

I thinking caching the schemas in files is a good idea if there is a speed issue with some storage engines. I didn't know that it caused problems in some environments.

In the meantime I can look at the handshake and see if the db calls can't just go right to the database without a schema check.

dietbrisk

Well, from what I've seen, the structure files store the SQL to create the table. For example, Symfony PHP stores schemas in YAML, so it can be easily read by both PHP and converted to SQL. Their schemas typically look like this:

propel:
  blog_article:
    _attributes: { phpName: Article }
    id:       { type: integer, required: true, primaryKey: true, autoIncrement: true }
    name:     { type: varchar(50), default: foobar, index: true }
    group_id: { type: integer, foreignTable: db_group, foreignReference: id, onDelete: cascade }
    content:     longvarchar
    created_at:
  blog_comment:
    _attributes: { phpName: Comment }
    id:
    article_id:
    author:      varchar(255)
    content:     longvarchar
    created_at: