-
Notifications
You must be signed in to change notification settings - Fork 1
Database
haides99 edited this page Apr 27, 2013
·
5 revisions
# Copied from discourse.org and converted to mysql statement.
# Quote all table names with '{' and '}', and prefix all system tables with 'core.'
# DROP TABLE IF EXISTS `users`;
CREATE TABLE `{users}` (
`id` INT(10) unsigned NOT NULL auto_increment,
`username` VARCHAR(20) NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP,
`name` VARCHAR(255),
`bio_raw` TEXT,
`seen_notification_id` INT(10) unsigned NOT NULL DEFAULT 0,
`last_posted_at` DATETIME,
`email` VARCHAR(255) NOT NULL,
`password_hash` VARCHAR(64),
`salt` VARCHAR(32),
`active` boolean,
# `username_lower` VARCHAR(20) NOT NULL,
`auth_token` VARCHAR(32),
`last_seen_at` DATETIME,
`website` VARCHAR(255),
`admin` boolean NOT NULL DEFAULT false,
`moderator` boolean NOT NULL DEFAULT false ,
`last_emailed_at` DATETIME,
`email_digests` boolean NOT NULL DEFAULT true ,
`trust_level_id` INT(10) unsigned NOT NULL DEFAULT 1 ,
`bio_cooked` TEXT,
`email_private_messages` boolean DEFAULT true,
`email_direct` boolean NOT NULL DEFAULT true,
`approved` boolean NOT NULL DEFAULT false,
`approved_by_id` INT(10) unsigned,
`approved_at` DATETIME,
`topics_entered` INT(10) unsigned NOT NULL DEFAULT 0,
`posts_read_count` INT(10) unsigned NOT NULL DEFAULT 0,
`digest_after_days` INT(10) unsigned NOT NULL DEFAULT 0,
`previous_visit_at` DATETIME,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
# DROP TABLE IF EXISTS `categories`;
CREATE TABLE `{categories}` (
`id` INT(10) unsigned NOT NULL auto_increment,
`name` VARCHAR(50) NOT NULL,
`color` char(6) NOT NULL DEFAULT 'AB9364',
# `topic_id` INT(10) unsigned DEFAULT NULL,
`top1_topic_id` INT(10) unsigned DEFAULT NULL,
`top2_topic_id` INT(10) unsigned DEFAULT NULL,
`top1_user_id` INT(10) unsigned DEFAULT NULL,
`top2_user_id` INT(10) unsigned DEFAULT NULL,
`topic_count` INT(10) unsigned NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP,
# `user_id` INT(10) unsigned DEFAULT NULL,
`topics_year` INT(10) unsigned NOT NULL DEFAULT 0,
`topics_month` INT(10) unsigned NOT NULL DEFAULT 0,
`topics_week` INT(10) unsigned NOT NULL DEFAULT 0,
`slug` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
# DROP TABLE IF EXISTS `topics`;
CREATE TABLE `{topics}` (
`id` INT(10) unsigned NOT NULL auto_increment,
`title` VARCHAR(255) NOT NULL,
`last_posted_at` DATETIME NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP,
`views` INT(10) unsigned NOT NULL DEFAULT 0,
`posts_count` INT(10) unsigned NOT NULL DEFAULT 0,
`user_id` INT(10) unsigned NOT NULL,
`last_post_user_id` INT(10) unsigned NOT NULL DEFAULT 0,
`reply_count` INT(10) unsigned NOT NULL DEFAULT 0,
`featured_user1_id` INT(10) unsigned DEFAULT NULL,
`featured_user2_id` INT(10) unsigned DEFAULT NULL,
`featured_user3_id` INT(10) unsigned DEFAULT NULL,
`featured_user4_id` INT(10) unsigned DEFAULT NULL,
`avg_time` INT(10) unsigned DEFAULT NULL,
`deleted_at` DATETIME NOT NULL DEFAULT 0,
`highest_post_number` INT(10) unsigned NOT NULL DEFAULT 0,
# `image_url` VARCHAR(255) NOT NULL ,
# `off_topic_count` INT(10) unsigned NOT NULL DEFAULT 0,
`offensive_count` INT(10) unsigned NOT NULL DEFAULT 0,
`like_count` BOOLEAN NOT NULL DEFAULT false,
`incoming_link_count` INT(10) unsigned NOT NULL DEFAULT 0,
`bookmark_count` INT(10) unsigned NOT NULL DEFAULT 0,
`star_count` INT(10) unsigned NOT NULL DEFAULT 0,
`category_id` INT(10) unsigned NOT NULL,
`visible` BOOLEAN NOT NULL DEFAULT true,
`moderator_posts_count` INT(10) unsigned NOT NULL DEFAULT 0,
`closed` BOOLEAN NOT NULL DEFAULT false,
`pinned` BOOLEAN NOT NULL DEFAULT false,
# `archived` BOOLEAN NOT NULL DEFAULT false,
`bumped_at` DATETIME NOT NULL DEFAULT 0,
`sub_tag` VARCHAR(255),
# `has_best_of` BOOLEAN NOT NULL DEFAULT false,
`meta_data` VARCHAR(255),
`vote_count` BOOLEAN NOT NULL DEFAULT false,
# `archetype` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
# DROP TABLE IF EXISTS `posts`;
CREATE TABLE `{posts}` (
`id` INT(10) unsigned NOT NULL,
`user_id` INT(10) unsigned NOT NULL,
`topic_id` INT(10) unsigned NOT NULL,
`post_number` INT(10) unsigned,
`raw` TEXT NOT NULL,
`cooked` TEXT,
`created_at` DATETIME NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP,
`reply_to_post_number` INT(10) unsigned DEFAULT NULL,
`cached_version` INT(10) unsigned NOT NULL DEFAULT 1,
`reply_count` INT(10) unsigned NOT NULL DEFAULT 0,
`quote_count` INT(10) unsigned NOT NULL DEFAULT 0,
`reply_below_post_number` INT(10) unsigned NOT NULL DEFAULT 0,
`deleted_at` DATETIME NOT NULL DEFAULT 0,
`off_topic_count` INT(10) unsigned NOT NULL DEFAULT 0,
`offensive_count` INT(10) unsigned NOT NULL DEFAULT 0,
`like_count` INT(10) unsigned NOT NULL DEFAULT 0,
`incoming_link_count` INT(10) unsigned NOT NULL DEFAULT 0,
`bookmark_count` INT(10) unsigned NOT NULL DEFAULT 0,
`avg_time` INT(10) unsigned,
`score` DOUBLE PRECISION,
`reads` INT(10) unsigned NOT NULL DEFAULT 0,
`post_type` INT(10) unsigned NOT NULL DEFAULT 1,
`vote_count` INT(10) unsigned NOT NULL DEFAULT 0,
`sort_order` INT(10),
`last_editor_id` INT(10),
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
# original discourse tables:
# $ grep 'CREATE TABLE' ~/discourse/db/structure.sql
CREATE TABLE categories (
CREATE TABLE category_featured_topics (
CREATE TABLE category_featured_users (
CREATE TABLE draft_sequences (
CREATE TABLE drafts (
CREATE TABLE email_logs (
CREATE TABLE email_tokens (
CREATE TABLE facebook_user_infos (
CREATE TABLE incoming_links (
CREATE TABLE invites (
CREATE TABLE notifications (
CREATE TABLE onebox_renders (
CREATE TABLE post_action_types (
CREATE TABLE post_actions (
CREATE TABLE post_onebox_renders (
CREATE TABLE post_replies (
CREATE TABLE post_timings (
CREATE TABLE posts (
CREATE TABLE site_customizations (
CREATE TABLE site_settings (
CREATE TABLE topic_allowed_users (
CREATE TABLE topic_invites (
CREATE TABLE topic_link_clicks (
CREATE TABLE topic_links (
CREATE TABLE topic_users (
CREATE TABLE topics (
CREATE TABLE trust_levels (
CREATE TABLE twitter_user_infos (
CREATE TABLE uploads (
CREATE TABLE user_actions (
CREATE TABLE user_open_ids (
CREATE TABLE user_visits (
CREATE TABLE users (
CREATE TABLE versions (
CREATE TABLE views (
CREATE TABLE categories (
CREATE TABLE categories_search (
CREATE TABLE category_featured_topics (
CREATE TABLE category_featured_users (
CREATE TABLE draft_sequences (
CREATE TABLE drafts (
CREATE TABLE email_logs (
CREATE TABLE email_tokens (
CREATE TABLE facebook_user_infos (
CREATE TABLE github_user_infos (
CREATE TABLE incoming_links (
CREATE TABLE invites (
CREATE TABLE message_bus (
CREATE TABLE notifications (
CREATE TABLE post_action_types (
CREATE TABLE post_actions (
CREATE TABLE post_replies (
CREATE TABLE post_timings (
CREATE TABLE posts (
CREATE TABLE posts_search (
CREATE TABLE schema_migrations (
CREATE TABLE site_customizations (
CREATE TABLE site_settings (
CREATE TABLE topic_allowed_users (
CREATE TABLE topic_invites (
CREATE TABLE topic_link_clicks (
CREATE TABLE topic_links (
CREATE TABLE topic_users (
CREATE TABLE topics (
CREATE TABLE twitter_user_infos (
CREATE TABLE uploads (
CREATE TABLE user_actions (
CREATE TABLE user_open_ids (
CREATE TABLE user_visits (
CREATE TABLE users (
CREATE TABLE users_search (
CREATE TABLE versions (
CREATE TABLE views (
$ psql
vagrant=# -c discourse_development
discourse_development=# select bio_raw, bio_cooked from users;
bio_raw | bio_cooked
----------------------------+-------------------------------------------------------------
|
|
hello world +| <p>hello world <br><strong>aaaa</strong> +
**aaaa** +| <em>cccc</em> +
*cccc* +| <a href="http://www.baidu.com" rel="nofollow">baidu</a></p>
[baidu][1] +|
+|
+|
[1]: http://www.baidu.com |
(3 rows)