Database Configuration

MySQL Configuration

Create database statement:

CREATE DATABASE `archelon` /*!40100 DEFAULT CHARACTER SET utf8 */;

Create user table statement:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `state` int(2) NOT NULL,
  `name` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `description` text,
  `birthdate` date DEFAULT NULL,
  `location` varchar(64) DEFAULT NULL,
  `affiliate` varchar(64) DEFAULT NULL,
  `url` varchar(64) DEFAULT NULL,
  `twitter_id` varchar(20) DEFAULT NULL,
  `twitter_token` varchar(255) DEFAULT NULL,
  `twitter_secret` varchar(255) DEFAULT NULL,
  `facebook_id` varchar(64) DEFAULT NULL,
  `facebook_token` varchar(255) DEFAULT NULL,
  `facebook_secret` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

Create main-category table statement:

CREATE TABLE `category_main` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Create sub-category table statement:

CREATE TABLE `category_sub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `main_id` (`main_id`),
  CONSTRAINT `category_sub_ibfk_1` FOREIGN KEY (`main_id`) REFERENCES `category_main` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

Create discussion table statement:

CREATE TABLE `discussion` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `author_id` bigint(20) NOT NULL,
  `category_id` int(11) NOT NULL,
  `state` int(2) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `subject` varchar(64) NOT NULL,
  `participants` int(11) NOT NULL DEFAULT '1',
  `posts` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `discussion_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `user` (`id`),
  CONSTRAINT `discussion_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category_sub` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Create activity table statement:

CREATE TABLE `activity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `activity_type` int(3) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `target_discussion_id` bigint(20),
  `target_user_id` bigint(20),
  `target_discussion_content_id` varchar(24),
  `target_post` int(11),
  PRIMARY KEY (`id`),
  CONSTRAINT `activity_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `activity_ibfk_2` FOREIGN KEY (`target_discussion_id`) REFERENCES `discussion` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Archelon configuration

Archelon requires information of your database environment. The configuration format is Java Properties "key=value".

The keys and values of database.properties are:

db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://<MYSQL_HOST>:<MYSQL_PORT>/archelon?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true
db.username=<USERNAME>
db.password=<PASSWORD>
mongo.host=<MONGODB_HOST>
mongo.port=<MONGODB_PORT>

NOTE: The MySQL default port number is 3306.

Create your database.properties and place to:

/WEB-INF/classes/

Restart context of archelon in your tomcat.