Database Structure

This page covers the Chameleon Database Structure, and how to set up your database.

Creating the database

First, you need to set up a database, in which all data will be stored. In order to set up the database you need to consider what to call your database. To create a database called 'chameleon', use the following command:

CREATE DATABASE IF NOT EXISTS chameleon;

If you then want to start working with the database (to for example run the SQL below to create the database tables), run the following command (assuming the database you just created is called 'chameleon'):

USE chameleon;

Creating the tables in an (already existing) database

To set up the tables Chameleon will use and populate them, you can run the SQL below. This is a copy of the database schema used by Chameleon. Please note however that it is recommended you run the command "rake db:schema:load" in the command line interface (or CLI, the console or terminal window) to set up your database, as it made to work on all systems and in all environments.

This code has been tested in MySQL.

DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `author` varchar(50) default NULL,
  `email` varchar(50) default NULL,
  `website` varchar(50) default NULL,
  `ip` varchar(15) default NULL,
  `user_agent` varchar(255) default NULL,
  `referer` varchar(255) default NULL,
  `text` text,
  `comment_type` varchar(1) NOT NULL default 'C',
  `date` datetime default NULL,
  `approved` tinyint(1) NOT NULL default '1',
  `entry_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `entry_id` (`entry_id`),
  KEY `type` (`comment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `entries`;
CREATE TABLE `entries` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `slug` varchar(255) NOT NULL default '',
  `description` text,
  `contents` blob,
  `state` varchar(1) default NULL,
  `date_published` datetime default NULL,
  `user_id` int(11) default NULL,
  `type_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `entries_tags`;
CREATE TABLE `entries_tags` (
  `entry_id` int(11) NOT NULL default '0',
  `tag_id` int(11) NOT NULL default '0',
  KEY `entry_id` (`entry_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `metas`;
CREATE TABLE `metas` (
  `id` int(11) NOT NULL auto_increment,
  `key` varchar(255) NOT NULL default '',
  `value` text NOT NULL,
  `entry_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `entry_id` (`entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `options`;
CREATE TABLE `options` (
  `id` int(11) NOT NULL auto_increment,
  `section` varchar(255) NOT NULL default '',
  `key` varchar(255) NOT NULL default '',
  `value` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `key` (`section`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `schema_info`;
CREATE TABLE `schema_info` (
  `version` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `schema_info` (`version`) VALUES 
 (7);

DROP TABLE IF EXISTS `tags`;
CREATE TABLE `tags` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `types`;
CREATE TABLE `types` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(255) NOT NULL default '',
  `password` varchar(255) NOT NULL default '',
  `nickname` varchar(255) NOT NULL default '',
  `email` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`,`nickname`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Adding some data to get started

To get started with Chameleon, you should also run the "rake db:fixtures:load" command in your command line interface (CLI, console or terminal window). It will create a first post, set some setting, make the database ready to be used by Chameleon, and - most importantly - create a default user with username "admin" and password "admin".