Clean up your clean installation

When you install ExpressionEngine, it automatically creates some default templates and categories for you, among other things. But since I like my clean installations really spotless, I created some sql queries to clean up your clean install.

What does it do?

The queries will truncate some tables and insert some defaults that I work with regularly:

  • Delete all template groups and insert a new one: home
  • Delete all templates and insert a new one in home: index
  • Delete all html-buttons and insert buttons for <h2>, <h3>, <strong> and <em>; usually all the formatting an editor would need
  • Delete all categories and category groups
  • Delete all weblog entries
  • Delete all but one weblog fields

That’s it. You could probably do more, but this gives me a great start for new projects.

The SQL

# get rid of template groups
TRUNCATE TABLE exp_template_groups;
INSERT INTO exp_template_groups
	(group_name, group_order, is_site_default)
VALUES
	('home', '1', 'y');

# get rid of templates
TRUNCATE TABLE exp_templates;
INSERT INTO exp_templates
	(group_id, template_name)
VALUES
	('1', 'index');

# better html buttons
TRUNCATE TABLE exp_html_buttons;
INSERT INTO exp_html_buttons
	(tag_name, tag_open, tag_close, accesskey, tag_order)
VALUES
	('h2', '<h2>', '</h2>', '2', 1),
	('h3', '<h3>', '</h3>', '3', 2),
	('em', '<em>', '</em>', 'i', 3),
	('strong', '<strong>', '</strong>', 'b', 4);

# no categories please
TRUNCATE TABLE exp_category_posts;
TRUNCATE TABLE exp_categories;
TRUNCATE TABLE exp_category_groups;
TRUNCATE TABLE exp_category_field_data;

# no weblog entries please
TRUNCATE TABLE exp_weblog_titles;
TRUNCATE TABLE exp_weblog_data;
TRUNCATE TABLE exp_weblog_fields;

# just one weblog field
ALTER TABLE exp_weblog_data DROP field_id_2, DROP field_ft_2, DROP field_id_3, DROP field_ft_3;
INSERT INTO exp_weblog_fields
	(group_id, field_name, field_label, field_type, field_ta_rows, field_search, field_order)
VALUES
	(1, 'body', 'Body', 'textarea', 8, 'y', 1);

December 2nd 2007 | ExpressionEngine, SQL | 14 comments

Comments

  1. 1 Steven Hambleton December 2nd 2007, 19:27

    This is great Low!

    Is it possible to change other settings like changing to Cookies instead of Sessions and Cookies?

    I don’t know a lot about SQL or databases so a cheat sheet would be great (if it existed).

  2. 2 Low December 2nd 2007, 19:45

    @Steven: I’m afraid not. Those settings are saved as serialized arrays, so you’d need some php to decode and encode them. Not something you could do properly with mysql only.

  3. 3 Steven Hambleton December 2nd 2007, 19:53

    Well what you have done here is highly useful! Especially setting the HTML buttons. I use Markdown for text formatting so this will save a lot of time!

    And also it will help with setting up template groups for includes etc.

  4. 4 Deron Sizemore December 7th 2007, 21:17

    I’m a novice when it comes to MySQL queries stuff…so, with that said, how do I use these queries to clean up my install?

    Just go to phpMyAdmin and copy/paste this code above somewhere?

    Thanks

  5. 5 Brice Irvine December 8th 2007, 18:33

    Ditto. How does one go about making this MySQL query to the EE database?

    Thanks

  6. 6 Low December 8th 2007, 19:04

    You can either copy and paste the code into phpMyAdmin, or save the code as a file (plain text) and run it from de mysql command line. If you’re a developer who installs EE on a regular basis, you’ll probably know what to do.

  7. 7 Ryan Blaind December 13th 2007, 20:54

    I ran this query on a fresh install of 1.6.1 and when I went to the templates tab after that it says “No templates available” .. just an FYI that something may be off with your query. The only thing I changed was the name of the new default template group changed from “home” to “global” .. ran it in phpmyadmin right after installing without even logging into the cp..

  8. 8 Low December 13th 2007, 21:49

    @Ryan: I just did the exact same thing, but everything went fine. Can you verify that the template group and template were created in phpMyAdmin?

  9. 9 Philip December 19th 2007, 20:34

    Thanks for the SQL! I do pretty much the same clean up you do after every EE install, I use to do it by importing templates, but this does the same thing, with less pain! Thanks again!

  10. 10 Ty (tzmedia) December 24th 2007, 21:26

    Awesome stuff Low… especially the SQL script, there’s no use messing around, when you can just lay the smack-down on it!

  11. 11 bjorn/ee December 26th 2007, 16:50

    so simple, yet so effective. Love it, thanks :-)

  12. 12 BridgingUnit May 22nd 2008, 11:30

    Just a thought, but couldn’t you achieve the same thing by customising the SQL that’s used in install.php to perform the initial set up in the first place. Then I think you would be able to change defaults like sessions and cookies etc too and perhaps default weblogs and so on too if you chose.

  13. 13 Low May 23rd 2008, 13:47

    @BridgingUnit: yes, you could do that. However, I prefer not to meddle with EE’s out-of-the-box scripts.

  14. 14 Dan Lee October 19th 2008, 20:14

    Good idea, good script. I stumbled upon this while troubleshooting a pesky install that wasn’t doing just what I wanted. Pretty impressive. I think not changing the install.php that EE provides is noble.

Leave a comment



Some html (a, em, strong, etc) allowed.
Email won’t be displayed and takes care of your Gravatar.