Create sample data

This document will summarize the steps of creating a sample data file.

First, create your new version of the data either by modifying an existing file or by adding to joomla.sql.

Next you need to prepare the data for distribution.

Some important steps for core distributions:

UPDATE `#__content`
 SET `created`='2011-01-01 00:00:01',
 `created_by`=42,
 `created_by_alias`='Joomla',
 `modified`=null,
 `modified_by`=null,
 `checked_out`=null,
 `checked_out_time`=null,
 `version`=1,
 `hits`=0;
UPDATE `#__contact_details`
 SET `created`='2011-01-01 00:00:01',
 `created_by`=42,
 `created_by_alias`='Joomla',
 `modified`=null,
 `modified_by`=null,
 `checked_out`=null,
 `checked_out_time`=null,
 `version`=1,
 `hits`=0;
UPDATE `#__weblinks`
 SET `created`='2011-01-01 00:00:01',
 `created_by`=42,
 `created_by_alias`='Joomla',
 `modified`=null,
 `modified_by`=null,
 `checked_out`=null,
 `checked_out_time`=null,
 `version`=1,
 `hits`=0;
UPDATE `#__newsfeeds`
 SET `created`='2011-01-01 00:00:01',
 `created_by`=42,
 `created_by_alias`='Joomla',
 `modified`=null,
 `modified_by`=null,
 `checked_out`=null,
 `checked_out_time`=null,
 `version`=1,
 `hits`=0;
UPDATE `#__banners`
 SET `created`='2011-01-01 00:00:01',
 `created_by`=42,
 `created_by_alias`='Joomla',
 `modified`=null,
 `modified_by`=null,
 `checked_out`=null,
 `checked_out_time`=null,
 `version`=1,
 `impmade`=0,
 `clicks`=0;
UPDATE `#__categories`
 SET `created_time`='2011-01-01 00:00:01',
 `created_user_id`=42,
 `modified_time`=null,
 `modified_user_id`=null,
 `checked_out`=null,
 `checked_out_time`=null,
 `version`=1,
 `hits`=0;

Then drop any tables that are not changed by sample data. Here is a typical example, but you must adjust based on your circumstances.

DROP TABLE `#__associations`, `#__banner_tracks`, `#__core_log_searches`, `#__extensions`,
 `#__finder_filters`, `#__finder_links`, `#__finder_links_terms0`, `#__finder_links_terms1`,
 `#__finder_links_terms2`, `#__finder_links_terms3`, `#__finder_links_terms4`,
 `#__finder_links_terms5`, `#__finder_links_terms6`, `#__finder_links_terms7`,
 `#__finder_links_terms8`, `#__finder_links_terms9`, `#__finder_links_termsa`,
 `#__finder_links_termsb`, `#__finder_links_termsc`, `#__finder_links_termsd`,
 `#__finder_links_termse`, `#__finder_links_termsf`, `#__finder_taxonomy`,
 `#__finder_taxonomy_map`, `#__finder_terms`, `#__finder_terms_common`, `#__finder_tokens`,
 `#__finder_tokens_aggregate`, `#__finder_types`, `#__languages`, `#__messages`,
 `#__messages_cfg`, `#__overrider`, `#__redirect_links`, `#__schemas`, `#__session`,
 `#__updates`, `#__update_categories`, `#__update_sites`, `#__update_sites_extensions`,
 `#__users`, `#__user_notes`, `#__user_profiles`, `#__user_usergroup_map`;

At this point you should dump out your file. It's good to do this with

• No comments
• Foreign keys disabled
• INSERT IGNORE
• CREATE TABLE IF NOT EXISTS
• Back ticks around names

At the top of your file you will want to add truncation for the tables you are modifying. It is safer to replace the whole table than to try to do it piecemeal.

TRUNCATE `#__assets`;
TRUNCATE `#__categories`;
TRUNCATE `#__menu`;
TRUNCATE `#__menu_types`;
TRUNCATE `#__modules`;
TRUNCATE `#__modules_menu`;
TRUNCATE `#__usergroups`;

At this point ... you will want to change your table prefixes from "jos_" to "#__" (substituting your prefix).

Testing

Before testing with the Joomla installation, simply try importing the joomla.sql file and then your file into a blank data base. This will quickly highlight any obvious problems in table structure and similar items.

If you call your file sample_something.sql and put it in the appropriate folder it will automatically appear in the list of installable files when the Joomla installer runs. Test you file there.

Warnings

In Joomla 2.5 and later the data structures are complex and interdependent. Make sure in particular that you include necessary changes in the assets table and the categories table if you create content items.

Make sure that you use a UTF-8 safe editor if you edit your sample data file directly.

Make sure not to include your session data, redirect data (unless it is deliberate), user data and similar items.

Most likely you do not want to include indexed data from Smart Search.