Thread

It’s amazing how quick you can be with a cut-paste followed by a couple of search-replaces to fix up formats and then auto-inserting a contents section.

It’s the summary on page two that counts as that’s what goes toward creating the schema diagrams/s.

By the way control-clicking on the contents takes you to the details for each respective table.

It’s a LOT of work to document the whole database schema of EE… it’s 91 tables, and some of the tables change, eg.  exp_weblog_data.  And it’s not like it’s OBVIOUS what’s going on if you wade through the SQL tables… it’s like performing an alien biopsy… I’m wondering what the heck is going on.

Can anyone tell me:
1.  Where are the site settings stored?  Things like the root folder of the site (e.g. /home/myuser/thisproject/htdocs/)?  There is some of this info in exp_sites, but it’s stored all weird (cached?).  The problem is that we want to run a dev environment of an EE site, so when we copy over the production version of the database, we need to update certain settings.  Preferably, we’d like to do this via an automated script, but after hours of looking, I still haven’t found where those settings are stored.  Anybody know the secret?
2.  What tables are updated when you do a new version of a template if versioning tracking is in place?  Are rows deleted?  The idea here is if we develop a new template version in dev, we want to roll that out on the production database as a new version.  This has to be done via a script… manual copy and pasting is prone to unacceptable error.

Thanks for your help!

As a note, this was done - for 1.2.1.  It’s in the wiki.  Obviously it’s far too out of date to be useful as a current view, but it may provide a helpful starting point/cross-reference.

Would be good if EE had an ORM for it, like PHP Doctrine 😊

1.  Where are the site settings stored?  Things like the root folder of the site (e.g. /home/myuser/thisproject/htdocs/)?  There is some of this info in exp_sites, but it’s stored all weird (cached?).  The problem is that we want to run a dev environment of an EE site, so when we copy over the production version of the database, we need to update certain settings.  Preferably, we’d like to do this via an automated script, but after hours of looking, I still haven’t found where those settings are stored.  Anybody know the secret?

They ARE stored in exp_sites. It’s not weird, it’s a serialized array and that’s where and how the settings are stored.

Seriously?  Well, maybe it’s not weird, but trying to get a dev site up and running and knowing where to store environment settings is turning out to be a holy pain.  Other tables in EE grow as needed… there aren’t THAT many site variables in there… why serialize that particular field and all of its goodies?  I guess I can write a PHP script to unserialize it and replace values with environment variables set on each developer’s machine.

Serialization is a standard transport mechanism of PHP variables while maintaining their type and structure; there’s not really any faster reliable methods of doing this with PHP, and we certainly didn’t want to add a column to the exp_sites table for every single preference.  Makes it that much harder to add, remove, and modify preferences.

In 2.0, we’re taking an extra step of base64 encoding that field, to help prevent developers from accidentally changing (and breaking) the serialized array with global find/replace queries executed on the database manually.

Oiy… so where are the people using EE in a dev environment?  It seems to be made in a way that seriously hinders the traditional dev->qa->prod release model.  Granted this is a problem with any CMS, but wow… I’m getting an aneurysm trying to come up with a way to do this that will pass our system’s criteria.  How have people handled this issue before?

Oh, my apologies for not touching on that in my post above.  Every site pref can be overridden in the config.php file.  So one way would be to maintain config.php files for the local/dev versions to override the necessary settings.

Whew… that’s a huge relief.  Thanks for that post.  Is the config file in version v1.6.x going to have the same hash keys as the config file for v1.5.1 (referenced here: http://expressionengine.com/wiki/Config.php_for_v1.5.1/)?

Now I just need to figure out which tables should be pushed from dev->qa->prod, and which tables to leave alone.  It’s easy enough to push the entire table from dev->qa->prod, but I’d rather just take the most recent iteration of a template (for example), and push that one row out, so that the production database has a versioning history of only the nicely tested and functional version of each template.  (Does that make sense?  In dev, I may make 20 edits to a template before I get it working the way I want, but on production I only want to show the history of the working templates).

If you dump $PREFS->core_ini, you’ll see all of the available variable keys.  And yes, makes sense, your revision tables would be fine to not be moved to the live server.

Ah, thanks.  That’s a handy command right there.  I appreciate all the help!

Glad to help, everettg_99, and welcome to the forums btw. 😊

I always find it easier to just open install.php and look at the table create queries to refresh my memory if need be, personally.

Wait… I spoke too soon… how should the $PREFS->core_ini be used?  You can’t just put it into a template and turn on PHP processing…
And I think you meant “var_dump($PREFS->core_ini);” right?  I’m just wanting to see all the available hash-keys that I can override in my config.php file. 

Thanks!