SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `status111`
--

-- --------------------------------------------------------

--
-- Table structure for table `avatar`
--

CREATE TABLE `avatar` (
  `profile_id` int(11) NOT NULL COMMENT 'foreign key to profile table',
  `original` tinyint(4) default '0' COMMENT 'uploaded by user or generated?',
  `width` int(11) NOT NULL COMMENT 'image width',
  `height` int(11) NOT NULL COMMENT 'image height',
  `mediatype` varchar(32) collate utf8_bin NOT NULL COMMENT 'file type',
  `filename` varchar(255) collate utf8_bin default NULL COMMENT 'local filename, if local',
  `url` varchar(255) collate utf8_bin default NULL COMMENT 'avatar location',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`profile_id`,`width`,`height`),
  UNIQUE KEY `avatar_url_key` (`url`),
  KEY `avatar_profile_id_idx` (`profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `bookmark`
--

CREATE TABLE `bookmark` (
  `id` char(36) collate utf8_bin NOT NULL,
  `profile_id` int(11) NOT NULL,
  `url` varchar(255) collate utf8_bin NOT NULL,
  `title` varchar(255) collate utf8_bin default NULL,
  `description` text collate utf8_bin,
  `uri` varchar(255) collate utf8_bin NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `bookmark_uri_idx` (`uri`),
  KEY `bookmark_profile_id_idx` (`profile_id`),
  KEY `bookmark_url_idx` (`url`),
  KEY `bookmark_created_idx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `config`
--

CREATE TABLE `config` (
  `section` varchar(32) collate utf8_bin NOT NULL default '' COMMENT 'configuration section',
  `setting` varchar(32) collate utf8_bin NOT NULL default '' COMMENT 'configuration setting',
  `value` varchar(255) collate utf8_bin default NULL COMMENT 'configuration value',
  PRIMARY KEY  (`section`,`setting`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `confirm_address`
--

CREATE TABLE `confirm_address` (
  `code` varchar(32) collate utf8_bin NOT NULL COMMENT 'good random code',
  `user_id` int(11) NOT NULL COMMENT 'user who requested confirmation',
  `address` varchar(255) collate utf8_bin NOT NULL COMMENT 'address (email, xmpp, SMS, etc.)',
  `address_extra` varchar(255) collate utf8_bin NOT NULL COMMENT 'carrier ID, for SMS',
  `address_type` varchar(8) collate utf8_bin NOT NULL COMMENT 'address type ("email", "xmpp", "sms")',
  `claimed` datetime default NULL COMMENT 'date this was claimed for queueing',
  `sent` datetime default NULL COMMENT 'date this was sent for queueing',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `confirm_address`
--

INSERT INTO `confirm_address` VALUES
('ZZS75N6RKZ5M2A36JPA8GT3W6M', 1, '[[admin_email]]', '', 'email', NULL, NULL, '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `consumer`
--

CREATE TABLE `consumer` (
  `consumer_key` varchar(255) collate utf8_bin NOT NULL COMMENT 'unique identifier, root URL',
  `consumer_secret` varchar(255) collate utf8_bin NOT NULL COMMENT 'secret value',
  `seed` char(32) collate utf8_bin NOT NULL COMMENT 'seed for new tokens by this consumer',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`consumer_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `conversation`
--

CREATE TABLE `conversation` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `uri` varchar(225) collate utf8_bin default NULL COMMENT 'URI of the conversation',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `conversation_uri_key` (`uri`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `deleted_notice`
--

CREATE TABLE `deleted_notice` (
  `id` int(11) NOT NULL COMMENT 'identity of notice',
  `profile_id` int(11) NOT NULL COMMENT 'author of the notice',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier, usually a tag URI',
  `created` datetime NOT NULL COMMENT 'date the notice record was created',
  `deleted` datetime NOT NULL COMMENT 'date the notice record was created',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `deleted_notice_uri_key` (`uri`),
  KEY `deleted_notice_profile_id_idx` (`profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `fave`
--

CREATE TABLE `fave` (
  `notice_id` int(11) NOT NULL COMMENT 'notice that is the favorite',
  `user_id` int(11) NOT NULL COMMENT 'user who likes this notice',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier, usually a tag URI',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`notice_id`,`user_id`),
  UNIQUE KEY `fave_uri_key` (`uri`),
  KEY `fave_notice_id_idx` (`notice_id`),
  KEY `fave_user_id_idx` (`user_id`,`modified`),
  KEY `fave_modified_idx` (`modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `file`
--

CREATE TABLE `file` (
  `id` int(11) NOT NULL auto_increment,
  `url` varchar(255) collate utf8_bin default NULL COMMENT 'destination URL after following redirections',
  `mimetype` varchar(50) collate utf8_bin default NULL COMMENT 'mime type of resource',
  `size` int(11) default NULL COMMENT 'size of resource when available',
  `title` varchar(255) collate utf8_bin default NULL COMMENT 'title of resource when available',
  `date` int(11) default NULL COMMENT 'date of resource according to http query',
  `protected` int(11) default NULL COMMENT 'true when URL is private (needs login)',
  `filename` varchar(255) collate utf8_bin default NULL COMMENT 'if a local file, name of the file',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `file_url_key` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `file_oembed`
--

CREATE TABLE `file_oembed` (
  `file_id` int(11) NOT NULL COMMENT 'oEmbed for that URL/file',
  `version` varchar(20) collate utf8_bin default NULL COMMENT 'oEmbed spec. version',
  `type` varchar(20) collate utf8_bin default NULL COMMENT 'oEmbed type: photo, video, link, rich',
  `mimetype` varchar(50) collate utf8_bin default NULL COMMENT 'mime type of resource',
  `provider` varchar(50) collate utf8_bin default NULL COMMENT 'name of this oEmbed provider',
  `provider_url` varchar(255) collate utf8_bin default NULL COMMENT 'URL of this oEmbed provider',
  `width` int(11) default NULL COMMENT 'width of oEmbed resource when available',
  `height` int(11) default NULL COMMENT 'height of oEmbed resource when available',
  `html` text collate utf8_bin COMMENT 'html representation of this oEmbed resource when applicable',
  `title` varchar(255) collate utf8_bin default NULL COMMENT 'title of oEmbed resource when available',
  `author_name` varchar(50) collate utf8_bin default NULL COMMENT 'author name for this oEmbed resource',
  `author_url` varchar(255) collate utf8_bin default NULL COMMENT 'author URL for this oEmbed resource',
  `url` varchar(255) collate utf8_bin default NULL COMMENT 'URL for this oEmbed resource when applicable (photo, link)',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `file_redirection`
--

CREATE TABLE `file_redirection` (
  `url` varchar(255) collate utf8_bin NOT NULL COMMENT 'short URL (or any other kind of redirect) for file (id)',
  `file_id` int(11) default NULL COMMENT 'short URL for what URL/file',
  `redirections` int(11) default NULL COMMENT 'redirect count',
  `httpcode` int(11) default NULL COMMENT 'HTTP status code (20x, 30x, etc.)',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `file_thumbnail`
--

CREATE TABLE `file_thumbnail` (
  `file_id` int(11) NOT NULL COMMENT 'thumbnail for what URL/file',
  `url` varchar(255) collate utf8_bin default NULL COMMENT 'URL of thumbnail',
  `width` int(11) default NULL COMMENT 'width of thumbnail',
  `height` int(11) default NULL COMMENT 'height of thumbnail',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `file_to_post`
--

CREATE TABLE `file_to_post` (
  `file_id` int(11) NOT NULL COMMENT 'id of URL/file',
  `post_id` int(11) NOT NULL COMMENT 'id of the notice it belongs to',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`file_id`,`post_id`),
  KEY `post_id_idx` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `foreign_link`
--

CREATE TABLE `foreign_link` (
  `user_id` int(11) NOT NULL COMMENT 'link to user on this system, if exists',
  `foreign_id` bigint(20) NOT NULL COMMENT 'link to user on foreign service, if exists',
  `service` int(11) NOT NULL COMMENT 'foreign key to service',
  `credentials` varchar(255) collate utf8_bin default NULL COMMENT 'authc credentials, typically a password',
  `noticesync` tinyint(4) default '1' COMMENT 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies',
  `friendsync` tinyint(4) default '2' COMMENT 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
  `profilesync` tinyint(4) default '1' COMMENT 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
  `last_noticesync` datetime default NULL COMMENT 'last time notices were imported',
  `last_friendsync` datetime default NULL COMMENT 'last time friends were imported',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`user_id`,`foreign_id`,`service`),
  KEY `foreign_user_user_id_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `foreign_service`
--

CREATE TABLE `foreign_service` (
  `id` int(11) NOT NULL COMMENT 'numeric key for service',
  `name` varchar(32) collate utf8_bin NOT NULL COMMENT 'name of the service',
  `description` varchar(255) collate utf8_bin default NULL COMMENT 'description',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `foreign_service_name_key` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `foreign_service`
--

INSERT INTO `foreign_service` VALUES
(1, 'Twitter', 'Twitter Micro-blogging service', '[[regtime]]', '[[regtime]]'),
(2, 'Facebook', 'Facebook', '[[regtime]]', '[[regtime]]'),
(3, 'FacebookConnect', 'Facebook Connect', '[[regtime]]', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `foreign_subscription`
--

CREATE TABLE `foreign_subscription` (
  `service` int(11) NOT NULL COMMENT 'service where relationship happens',
  `subscriber` bigint(20) NOT NULL COMMENT 'subscriber on foreign service',
  `subscribed` bigint(20) NOT NULL COMMENT 'subscribed user',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`service`,`subscriber`,`subscribed`),
  KEY `foreign_subscription_subscriber_idx` (`service`,`subscriber`),
  KEY `foreign_subscription_subscribed_idx` (`service`,`subscribed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `foreign_user`
--

CREATE TABLE `foreign_user` (
  `id` bigint(20) NOT NULL COMMENT 'unique numeric key on foreign service',
  `service` int(11) NOT NULL COMMENT 'foreign key to service',
  `uri` varchar(255) collate utf8_bin NOT NULL COMMENT 'identifying URI',
  `nickname` varchar(255) collate utf8_bin default NULL COMMENT 'nickname on foreign service',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`,`service`),
  UNIQUE KEY `foreign_user_uri_key` (`uri`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `group_alias`
--

CREATE TABLE `group_alias` (
  `alias` varchar(64) collate utf8_bin NOT NULL COMMENT 'additional nickname for the group',
  `group_id` int(11) NOT NULL COMMENT 'group profile is blocked from',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date alias was created',
  PRIMARY KEY  (`alias`),
  KEY `group_alias_group_id_idx` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `group_block`
--

CREATE TABLE `group_block` (
  `group_id` int(11) NOT NULL COMMENT 'group profile is blocked from',
  `blocked` int(11) NOT NULL COMMENT 'profile that is blocked',
  `blocker` int(11) NOT NULL COMMENT 'user making the block',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date of blocking',
  PRIMARY KEY  (`group_id`,`blocked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `group_inbox`
--

CREATE TABLE `group_inbox` (
  `group_id` int(11) NOT NULL COMMENT 'group receiving the message',
  `notice_id` int(11) NOT NULL COMMENT 'notice received',
  `created` datetime NOT NULL COMMENT 'date the notice was created',
  PRIMARY KEY  (`group_id`,`notice_id`),
  KEY `group_inbox_created_idx` (`created`),
  KEY `group_inbox_notice_id_idx` (`notice_id`),
  KEY `group_inbox_group_id_created_notice_id_idx` (`group_id`,`created`,`notice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `group_join_queue`
--

CREATE TABLE `group_join_queue` (
  `profile_id` int(11) NOT NULL COMMENT 'remote or local profile making the request',
  `group_id` int(11) NOT NULL default '0' COMMENT 'remote or local group to join, if any',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`profile_id`,`group_id`),
  KEY `group_join_queue_profile_id_created_idx` (`profile_id`,`created`),
  KEY `group_join_queue_group_id_created_idx` (`group_id`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `group_member`
--

CREATE TABLE `group_member` (
  `group_id` int(11) NOT NULL COMMENT 'foreign key to user_group',
  `profile_id` int(11) NOT NULL COMMENT 'foreign key to profile table',
  `is_admin` tinyint(4) default '0' COMMENT 'is this user an admin?',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universal identifier',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`group_id`,`profile_id`),
  UNIQUE KEY `group_member_uri_key` (`uri`),
  KEY `group_member_profile_id_idx` (`profile_id`),
  KEY `group_member_created_idx` (`created`),
  KEY `group_member_profile_id_created_idx` (`profile_id`,`created`),
  KEY `group_member_group_id_created_idx` (`group_id`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `happening`
--

CREATE TABLE `happening` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID',
  `uri` varchar(255) collate utf8_bin NOT NULL,
  `profile_id` int(11) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `title` varchar(255) collate utf8_bin NOT NULL,
  `location` varchar(255) collate utf8_bin default NULL,
  `url` varchar(255) collate utf8_bin default NULL,
  `description` text collate utf8_bin,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `happening_uri_key` (`uri`),
  KEY `happening_created_idx` (`created`),
  KEY `happening_start_end_idx` (`start_time`,`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `inbox`
--

CREATE TABLE `inbox` (
  `user_id` int(11) NOT NULL COMMENT 'user receiving the notice',
  `notice_ids` blob COMMENT 'packed list of notice ids',
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `inbox`
--

INSERT INTO `inbox` VALUES
(1, '');

-- --------------------------------------------------------

--
-- Table structure for table `invitation`
--

CREATE TABLE `invitation` (
  `code` varchar(32) collate utf8_bin NOT NULL COMMENT 'random code for an invitation',
  `user_id` int(11) NOT NULL COMMENT 'who sent the invitation',
  `address` varchar(255) collate utf8_bin NOT NULL COMMENT 'invitation sent to',
  `address_type` varchar(8) collate utf8_bin NOT NULL COMMENT 'address type ("email", "xmpp", "sms")',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `registered_user_id` int(11) default NULL COMMENT 'if the invitation is converted, who the new user is',
  PRIMARY KEY  (`code`),
  KEY `invitation_address_idx` (`address`,`address_type`),
  KEY `invitation_user_id_idx` (`user_id`),
  KEY `invitation_registered_user_id_idx` (`registered_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `local_group`
--

CREATE TABLE `local_group` (
  `group_id` int(11) NOT NULL COMMENT 'group represented',
  `nickname` varchar(64) collate utf8_bin default NULL COMMENT 'group represented',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`group_id`),
  UNIQUE KEY `local_group_nickname_key` (`nickname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `location_namespace`
--

CREATE TABLE `location_namespace` (
  `id` int(11) NOT NULL COMMENT 'identity for this namespace',
  `description` varchar(255) collate utf8_bin default NULL COMMENT 'description of the namespace',
  `created` datetime NOT NULL COMMENT 'date the record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `login_token`
--

CREATE TABLE `login_token` (
  `user_id` int(11) NOT NULL COMMENT 'user owning this token',
  `token` char(32) collate utf8_bin NOT NULL COMMENT 'token useable for logging in',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `message`
--

CREATE TABLE `message` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier',
  `from_profile` int(11) NOT NULL COMMENT 'who the message is from',
  `to_profile` int(11) NOT NULL COMMENT 'who the message is to',
  `content` text collate utf8_bin COMMENT 'message content',
  `rendered` text collate utf8_bin COMMENT 'HTML version of the content',
  `url` varchar(255) collate utf8_bin default NULL COMMENT 'URL of any attachment (image, video, bookmark, whatever)',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  `source` varchar(32) collate utf8_bin default NULL COMMENT 'source of comment, like "web", "im", or "clientname"',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `message_uri_key` (`uri`),
  KEY `message_from_idx` (`from_profile`),
  KEY `message_to_idx` (`to_profile`),
  KEY `message_created_idx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `nonce`
--

CREATE TABLE `nonce` (
  `consumer_key` varchar(255) collate utf8_bin NOT NULL COMMENT 'unique identifier, root URL',
  `tok` char(32) collate utf8_bin default NULL COMMENT 'buggy old value, ignored',
  `nonce` char(32) collate utf8_bin NOT NULL COMMENT 'nonce',
  `ts` datetime NOT NULL COMMENT 'timestamp sent',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`consumer_key`,`ts`,`nonce`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `notice`
--

CREATE TABLE `notice` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `profile_id` int(11) NOT NULL COMMENT 'who made the update',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier, usually a tag URI',
  `content` text character set utf8 COMMENT 'update content',
  `rendered` text collate utf8_bin COMMENT 'HTML version of the content',
  `url` varchar(255) collate utf8_bin default NULL COMMENT 'URL of any attachment (image, video, bookmark, whatever)',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  `reply_to` int(11) default NULL COMMENT 'notice replied to (usually a guess)',
  `is_local` tinyint(4) default '0' COMMENT 'notice was generated by a user',
  `source` varchar(32) collate utf8_bin default NULL COMMENT 'source of comment, like "web", "im", or "clientname"',
  `conversation` int(11) default NULL COMMENT 'id of root notice in this conversation',
  `lat` decimal(10,7) default NULL COMMENT 'latitude',
  `lon` decimal(10,7) default NULL COMMENT 'longitude',
  `location_id` int(11) default NULL COMMENT 'location id if possible',
  `location_ns` int(11) default NULL COMMENT 'namespace for location',
  `repeat_of` int(11) default NULL COMMENT 'notice this is a repeat of',
  `object_type` varchar(255) collate utf8_bin default 'http://activitystrea.ms/schema/1.0/note' COMMENT 'URI representing activity streams object type',
  `verb` varchar(255) collate utf8_bin default 'http://activitystrea.ms/schema/1.0/post' COMMENT 'URI representing activity streams verb',
  `scope` int(11) default NULL COMMENT 'bit map for distribution scope; 0 = everywhere; 1 = this server only; 2 = addressees; 4 = followers; null = default',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `notice_uri_key` (`uri`),
  KEY `notice_created_id_is_local_idx` (`created`,`id`,`is_local`),
  KEY `notice_profile_id_idx` (`profile_id`,`created`,`id`),
  KEY `notice_repeat_of_created_id_idx` (`repeat_of`,`created`,`id`),
  KEY `notice_conversation_created_id_idx` (`conversation`,`created`,`id`),
  KEY `notice_replyto_idx` (`reply_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `notice_source`
--

CREATE TABLE `notice_source` (
  `code` varchar(32) collate utf8_bin NOT NULL COMMENT 'source code',
  `name` varchar(255) collate utf8_bin NOT NULL COMMENT 'name of the source',
  `url` varchar(255) collate utf8_bin NOT NULL COMMENT 'url to link to',
  `notice_id` int(11) NOT NULL COMMENT 'date this record was created',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `notice_source`
--

INSERT INTO `notice_source` VALUES
('Afficheur', 'Afficheur', 'http://afficheur.sourceforge.jp/', 0, '[[regtime]]', '[[regtime]]'),
('AgentSolo.com', 'AgentSolo.com', 'http://www.agentsolo.com/', 0, '[[regtime]]', '[[regtime]]'),
('DarterosStatus', 'Darteros Status', 'http://www.darteros.com/doc/Darteros_Status', 0, '[[regtime]]', '[[regtime]]'),
('Do', 'Gnome Do', 'http://do.davebsd.com/wiki/index.php?title=Microblog_Plugin', 0, '[[regtime]]', '[[regtime]]'),
('Facebook', 'Facebook', 'http://apps.facebook.com/identica/', 0, '[[regtime]]', '[[regtime]]'),
('Gwibber', 'Gwibber', 'http://launchpad.net/gwibber', 0, '[[regtime]]', '[[regtime]]'),
('HelloTxt', 'HelloTxt', 'http://hellotxt.com/', 0, '[[regtime]]', '[[regtime]]'),
('IdentiCurse', 'IdentiCurse', 'http://identicurse.net/', 0, '[[regtime]]', '[[regtime]]'),
('IdentiFox', 'IdentiFox', 'http://www.bitbucket.org/uncryptic/identifox/', 0, '[[regtime]]', '[[regtime]]'),
('Jiminy', 'Jiminy', 'http://code.google.com/p/jiminy/', 0, '[[regtime]]', '[[regtime]]'),
('LaTwit', 'LaTwit', 'http://latwit.mac65.com/', 0, '[[regtime]]', '[[regtime]]'),
('LiveTweeter', 'LiveTweeter', 'http://addons.songbirdnest.com/addon/1204', 0, '[[regtime]]', '[[regtime]]'),
('Mobidentica', 'Mobidentica', 'http://www.substanceofcode.com/software/mobidentica/', 0, '[[regtime]]', '[[regtime]]'),
('Pikchur', 'Pikchur', 'http://www.pikchur.com/', 0, '[[regtime]]', '[[regtime]]'),
('Ping.fm', 'Ping.fm', 'http://ping.fm/', 0, '[[regtime]]', '[[regtime]]'),
('StatusNet Android', 'Android', 'http://status.net/android', 0, '[[regtime]]', '[[regtime]]'),
('StatusNet Blackberry', 'Blackberry', 'http://status.net/blackberry', 0, '[[regtime]]', '[[regtime]]'),
('StatusNet Desktop', 'StatusNet Desktop', 'http://status.net/desktop', 0, '[[regtime]]', '[[regtime]]'),
('StatusNet Mobile', 'StatusNet Mobile', 'http://status.net/mobile', 0, '[[regtime]]', '[[regtime]]'),
('StatusNet iPhone', 'iPhone', 'http://status.net/iphone', 0, '[[regtime]]', '[[regtime]]'),
('TweetDeck', 'TweetDeck', 'http://www.tweetdeck.com/', 0, '[[regtime]]', '[[regtime]]'),
('Twidge', 'Twidge', 'http://software.complete.org/twidge', 0, '[[regtime]]', '[[regtime]]'),
('Updating.Me', 'Updating.Me', 'http://updating.me/', 0, '[[regtime]]', '[[regtime]]'),
('adium', 'Adium', 'http://www.adiumx.com/', 0, '[[regtime]]', '[[regtime]]'),
('anyio', 'Any.IO', 'http://any.io/', 0, '[[regtime]]', '[[regtime]]'),
('betwittered', 'BeTwittered', 'http://www.32hours.com/betwitteredinfo/', 0, '[[regtime]]', '[[regtime]]'),
('bti', 'bti', 'http://gregkh.github.com/bti/', 0, '[[regtime]]', '[[regtime]]'),
('choqok', 'Choqok', 'http://choqok.gnufolks.org/', 0, '[[regtime]]', '[[regtime]]'),
('cliqset', 'Cliqset', 'http://www.cliqset.com/', 0, '[[regtime]]', '[[regtime]]'),
('deskbar', 'Deskbar-Applet', 'http://www.gnome.org/projects/deskbar-applet/', 0, '[[regtime]]', '[[regtime]]'),
('drupal', 'Drupal', 'http://drupal.org/', 0, '[[regtime]]', '[[regtime]]'),
('eventbox', 'EventBox', 'http://thecosmicmachine.com/eventbox/', 0, '[[regtime]]', '[[regtime]]'),
('feed2omb', 'feed2omb', 'http://projects.ciarang.com/p/feed2omb/', 0, '[[regtime]]', '[[regtime]]'),
('gNewBook', 'gNewBook', 'http://www.gnewbook.org/', 0, '[[regtime]]', '[[regtime]]'),
('get2gnow', 'get2gnow', 'http://uberchicgeekchick.com/?projects=get2gnow', 0, '[[regtime]]', '[[regtime]]'),
('gravity', 'Gravity', 'http://mobileways.de/gravity', 0, '[[regtime]]', '[[regtime]]'),
('identica-mode', 'Emacs Identica-mode', 'http://nongnu.org/identica-mode/', 0, '[[regtime]]', '[[regtime]]'),
('identicatools', 'Laconica Tools', 'http://bitbucketlabs.net/laconica-tools/', 0, '[[regtime]]', '[[regtime]]'),
('identichat', 'identichat', 'http://identichat.prosody.im/', 0, '[[regtime]]', '[[regtime]]'),
('identitwitch', 'IdentiTwitch', 'http://richfish.org/identitwitch/', 0, '[[regtime]]', '[[regtime]]'),
('livetweeter', 'livetweeter', 'http://addons.songbirdnest.com/addon/1204', 0, '[[regtime]]', '[[regtime]]'),
('maisha', 'Maisha', 'http://maisha.grango.org/', 0, '[[regtime]]', '[[regtime]]'),
('mbpidgin', 'mbpidgin', 'http://code.google.com/p/microblog-purple/', 0, '[[regtime]]', '[[regtime]]'),
('moconica', 'Moconica', 'http://moconica.com/', 0, '[[regtime]]', '[[regtime]]'),
('mustard', 'mustard', 'http://mustard.macno.org', 0, '[[regtime]]', '[[regtime]]'),
('nambu', 'Nambu', 'http://www.nambu.com/', 0, '[[regtime]]', '[[regtime]]'),
('peoplebrowsr', 'PeopleBrowsr', 'http://www.peoplebrowsr.com/', 0, '[[regtime]]', '[[regtime]]'),
('pingvine', 'PingVine', 'http://pingvine.com/', 0, '[[regtime]]', '[[regtime]]'),
('pocketwit', 'PockeTwit', 'http://code.google.com/p/pocketwit/', 0, '[[regtime]]', '[[regtime]]'),
('posty', 'Posty', 'http://spreadingfunkyness.com/posty/', 0, '[[regtime]]', '[[regtime]]'),
('qtwitter', 'qTwitter', 'http://qtwitter.ayoy.net/', 0, '[[regtime]]', '[[regtime]]'),
('qwit', 'Qwit', 'http://code.google.com/p/qwit/', 0, '[[regtime]]', '[[regtime]]'),
('royalewithcheese', 'Royale With Cheese', 'http://p.hellyeah.org/', 0, '[[regtime]]', '[[regtime]]'),
('rss.me', 'rss.me', 'http://rss.me/', 0, '[[regtime]]', '[[regtime]]'),
('rssdent', 'rssdent', 'http://github.com/zcopley/rssdent/tree/master', 0, '[[regtime]]', '[[regtime]]'),
('rygh.no', 'rygh.no', 'http://rygh.no/', 0, '[[regtime]]', '[[regtime]]'),
('ryghsms', 'ryghsms', 'http://sms.rygh.no/', 0, '[[regtime]]', '[[regtime]]'),
('smob', 'SMOB', 'http://smob.sioc-project.org/', 0, '[[regtime]]', '[[regtime]]'),
('socialoomphBfD4pMqz31', 'SocialOomph', 'http://www.socialoomph.com/', 0, '[[regtime]]', '[[regtime]]'),
('spaz', 'Spaz', 'http://funkatron.com/spaz', 0, '[[regtime]]', '[[regtime]]'),
('tarpipe', 'tarpipe', 'http://tarpipe.com/', 0, '[[regtime]]', '[[regtime]]'),
('tjunar', 'Tjunar', 'http://nederflash.nl/boek/titels/tjunar-air', 0, '[[regtime]]', '[[regtime]]'),
('tr.im', 'tr.im', 'http://tr.im/', 0, '[[regtime]]', '[[regtime]]'),
('triklepost', 'Tricklepost', 'http://github.com/zcopley/tricklepost/tree/master', 0, '[[regtime]]', '[[regtime]]'),
('tweenky', 'Tweenky', 'http://beta.tweenky.com/', 0, '[[regtime]]', '[[regtime]]'),
('twhirl', 'Twhirl', 'http://www.twhirl.org/', 0, '[[regtime]]', '[[regtime]]'),
('twibble', 'twibble', 'http://www.twibble.de/', 0, '[[regtime]]', '[[regtime]]'),
('twidge', 'Twidge', 'http://software.complete.org/twidge', 0, '[[regtime]]', '[[regtime]]'),
('twidroid', 'twidroid', 'http://www.twidroid.com/', 0, '[[regtime]]', '[[regtime]]'),
('twittelator', 'Twittelator', 'http://www.stone.com/iPhone/Twittelator/', 0, '[[regtime]]', '[[regtime]]'),
('twitter', 'Twitter', 'http://twitter.com/', 0, '[[regtime]]', '[[regtime]]'),
('twitterfeed', 'twitterfeed', 'http://twitterfeed.com/', 0, '[[regtime]]', '[[regtime]]'),
('twitterphoto', 'TwitterPhoto', 'http://richfish.org/twitterphoto/', 0, '[[regtime]]', '[[regtime]]'),
('twitterpm', 'Net::Twitter', 'http://search.cpan.org/dist/Net-Twitter/', 0, '[[regtime]]', '[[regtime]]'),
('twittertools', 'Twitter Tools', 'http://wordpress.org/extend/plugins/twitter-tools/', 0, '[[regtime]]', '[[regtime]]'),
('twitux', 'Twitux', 'http://live.gnome.org/DanielMorales/Twitux', 0, '[[regtime]]', '[[regtime]]'),
('twitvim', 'TwitVim', 'http://vim.sourceforge.net/scripts/script.php?script_id=2204', 0, '[[regtime]]', '[[regtime]]'),
('urfastr', 'urfastr', 'http://urfastr.net/', 0, '[[regtime]]', '[[regtime]]'),
('yatca', 'Yatca', 'http://www.yatca.com/', 0, '[[regtime]]', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `notice_tag`
--

CREATE TABLE `notice_tag` (
  `tag` varchar(64) collate utf8_bin NOT NULL COMMENT 'hash tag associated with this notice',
  `notice_id` int(11) NOT NULL COMMENT 'notice tagged',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`tag`,`notice_id`),
  KEY `notice_tag_created_idx` (`created`),
  KEY `notice_tag_notice_id_idx` (`notice_id`),
  KEY `notice_tag_tag_created_notice_id_idx` (`tag`,`created`,`notice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `oauth_application`
--

CREATE TABLE `oauth_application` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `owner` int(11) NOT NULL COMMENT 'owner of the application',
  `consumer_key` varchar(255) collate utf8_bin NOT NULL COMMENT 'application consumer key',
  `name` varchar(255) collate utf8_bin NOT NULL COMMENT 'name of the application',
  `description` varchar(255) collate utf8_bin default NULL COMMENT 'description of the application',
  `icon` varchar(255) collate utf8_bin NOT NULL COMMENT 'application icon',
  `source_url` varchar(255) collate utf8_bin default NULL COMMENT 'application homepage - used for source link',
  `organization` varchar(255) collate utf8_bin default NULL COMMENT 'name of the organization running the application',
  `homepage` varchar(255) collate utf8_bin default NULL COMMENT 'homepage for the organization',
  `callback_url` varchar(255) collate utf8_bin default NULL COMMENT 'url to redirect to after authentication',
  `type` tinyint(4) default '0' COMMENT 'type of app, 1 = browser, 2 = desktop',
  `access_type` tinyint(4) default '0' COMMENT 'default access type, bit 1 = read, bit 2 = write',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `oauth_application_name_key` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `oauth_application_user`
--

CREATE TABLE `oauth_application_user` (
  `profile_id` int(11) NOT NULL COMMENT 'user of the application',
  `application_id` int(11) NOT NULL COMMENT 'id of the application',
  `access_type` tinyint(4) default '0' COMMENT 'access type, bit 1 = read, bit 2 = write',
  `token` varchar(255) collate utf8_bin default NULL COMMENT 'request or access token',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`profile_id`,`application_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `oauth_token_association`
--

CREATE TABLE `oauth_token_association` (
  `profile_id` int(11) NOT NULL COMMENT 'associated user',
  `application_id` int(11) NOT NULL COMMENT 'the application',
  `token` varchar(255) collate utf8_bin NOT NULL COMMENT 'token used for this association',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`profile_id`,`application_id`,`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `oid_associations`
--

CREATE TABLE `oid_associations` (
  `server_url` blob NOT NULL,
  `handle` varchar(255) collate utf8_bin NOT NULL default '',
  `secret` blob,
  `issued` int(11) default NULL,
  `lifetime` int(11) default NULL,
  `assoc_type` varchar(64) collate utf8_bin default NULL,
  PRIMARY KEY  (`server_url`(255),`handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `oid_nonces`
--

CREATE TABLE `oid_nonces` (
  `server_url` varchar(2047) collate utf8_bin default NULL,
  `timestamp` int(11) default NULL,
  `salt` char(40) collate utf8_bin default NULL,
  UNIQUE KEY `oid_nonces_server_url_timestamp_salt_key` (`server_url`(255),`timestamp`,`salt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `old_school_prefs`
--

CREATE TABLE `old_school_prefs` (
  `user_id` int(11) NOT NULL COMMENT 'user who has the preference',
  `stream_mode_only` tinyint(4) default '1' COMMENT 'No conversation streams',
  `conversation_tree` tinyint(4) default '1' COMMENT 'Hierarchical tree view for conversations',
  `stream_nicknames` tinyint(4) default '1' COMMENT 'Show nicknames for authors and addressees in streams',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `poll`
--

CREATE TABLE `poll` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID',
  `uri` varchar(255) collate utf8_bin NOT NULL,
  `profile_id` int(11) default NULL,
  `question` text collate utf8_bin,
  `options` text collate utf8_bin,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `poll_uri_key` (`uri`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `poll_response`
--

CREATE TABLE `poll_response` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of the response',
  `uri` varchar(255) collate utf8_bin NOT NULL COMMENT 'UUID to the response notice',
  `poll_id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of poll being responded to',
  `profile_id` int(11) default NULL,
  `selection` int(11) default NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `poll_uri_key` (`uri`),
  UNIQUE KEY `poll_response_poll_id_profile_id_key` (`poll_id`,`profile_id`),
  KEY `poll_response_profile_id_poll_id_index` (`profile_id`,`poll_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `profile`
--

CREATE TABLE `profile` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `nickname` varchar(64) character set utf8 NOT NULL COMMENT 'nickname or username',
  `fullname` varchar(255) character set utf8 default NULL COMMENT 'display name',
  `profileurl` varchar(255) collate utf8_bin default NULL COMMENT 'URL, cached so we dont regenerate',
  `homepage` varchar(255) character set utf8 default NULL COMMENT 'identifying URL',
  `bio` text character set utf8 COMMENT 'descriptive biography',
  `location` varchar(255) character set utf8 default NULL COMMENT 'physical location',
  `lat` decimal(10,7) default NULL COMMENT 'latitude',
  `lon` decimal(10,7) default NULL COMMENT 'longitude',
  `location_id` int(11) default NULL COMMENT 'location id if possible',
  `location_ns` int(11) default NULL COMMENT 'namespace for location',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  KEY `profile_nickname_idx` (`nickname`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;

--
-- Dumping data for table `profile`
--

INSERT INTO `profile` VALUES
(1, '[[admin_username]]', '[[admin_username]]', '[[softurl]]/index.php/[[admin_username]]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '[[regtime]]', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `profile_block`
--

CREATE TABLE `profile_block` (
  `blocker` int(11) NOT NULL COMMENT 'user making the block',
  `blocked` int(11) NOT NULL COMMENT 'profile that is blocked',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date of blocking',
  PRIMARY KEY  (`blocker`,`blocked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `profile_detail`
--

CREATE TABLE `profile_detail` (
  `id` int(11) NOT NULL auto_increment,
  `profile_id` int(11) NOT NULL,
  `field_name` varchar(16) collate utf8_bin NOT NULL,
  `value_index` int(11) default NULL,
  `field_value` text collate utf8_bin,
  `date` datetime default NULL,
  `rel` varchar(16) collate utf8_bin default NULL,
  `rel_profile` int(11) default NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `profile_detail_profile_id_field_name_value_index` (`profile_id`,`field_name`,`value_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `profile_list`
--

CREATE TABLE `profile_list` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `tagger` int(11) NOT NULL COMMENT 'user making the tag',
  `tag` varchar(64) collate utf8_bin NOT NULL COMMENT 'people tag',
  `description` text collate utf8_bin COMMENT 'description of the people tag',
  `private` tinyint(4) default '0' COMMENT 'is this tag private',
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date the tag was added',
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'date the tag was modified',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universal identifier',
  `mainpage` varchar(255) collate utf8_bin default NULL COMMENT 'page to link to',
  `tagged_count` int(11) default '0' COMMENT 'number of people tagged with this tag by this user',
  `subscriber_count` int(11) default '0' COMMENT 'number of subscribers to this tag',
  PRIMARY KEY  (`tagger`,`tag`),
  UNIQUE KEY `profile_list_id_key` (`id`),
  KEY `profile_list_modified_idx` (`modified`),
  KEY `profile_list_tag_idx` (`tag`),
  KEY `profile_list_tagger_tag_idx` (`tagger`,`tag`),
  KEY `profile_list_tagged_count_idx` (`tagged_count`),
  KEY `profile_list_subscriber_count_idx` (`subscriber_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `profile_role`
--

CREATE TABLE `profile_role` (
  `profile_id` int(11) NOT NULL COMMENT 'account having the role',
  `role` varchar(32) collate utf8_bin NOT NULL COMMENT 'string representing the role',
  `created` datetime NOT NULL COMMENT 'date the role was granted',
  PRIMARY KEY  (`profile_id`,`role`),
  KEY `profile_role_role_created_profile_id_idx` (`role`,`created`,`profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `profile_role`
--

INSERT INTO `profile_role` VALUES
(1, 'administrator', '[[regtime]]'),
(1, 'moderator', '[[regtime]]'),
(1, 'owner', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `profile_tag`
--

CREATE TABLE `profile_tag` (
  `tagger` int(11) NOT NULL COMMENT 'user making the tag',
  `tagged` int(11) NOT NULL COMMENT 'profile tagged',
  `tag` varchar(64) collate utf8_bin NOT NULL COMMENT 'hash tag associated with this notice',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date the tag was added',
  PRIMARY KEY  (`tagger`,`tagged`,`tag`),
  KEY `profile_tag_modified_idx` (`modified`),
  KEY `profile_tag_tagger_tag_idx` (`tagger`,`tag`),
  KEY `profile_tag_tagged_idx` (`tagged`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `profile_tag_subscription`
--

CREATE TABLE `profile_tag_subscription` (
  `profile_tag_id` int(11) NOT NULL COMMENT 'foreign key to profile_tag',
  `profile_id` int(11) NOT NULL COMMENT 'foreign key to profile table',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`profile_tag_id`,`profile_id`),
  KEY `profile_tag_subscription_profile_id_idx` (`profile_id`),
  KEY `profile_tag_subscription_created_idx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `qna_answer`
--

CREATE TABLE `qna_answer` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of the response',
  `uri` varchar(255) collate utf8_bin NOT NULL COMMENT 'UUID to the answer notice',
  `question_id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of question being responded to',
  `content` text collate utf8_bin,
  `best` tinyint(4) default NULL,
  `revisions` int(11) default NULL,
  `profile_id` int(11) default NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `question_uri_key` (`uri`),
  UNIQUE KEY `question_id_profile_id_key` (`question_id`,`profile_id`),
  KEY `profile_id_question_id_index` (`profile_id`,`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `qna_question`
--

CREATE TABLE `qna_question` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID',
  `uri` varchar(255) collate utf8_bin NOT NULL,
  `profile_id` int(11) default NULL,
  `title` text collate utf8_bin,
  `closed` tinyint(4) default NULL,
  `description` text collate utf8_bin,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `question_uri_key` (`uri`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `qna_vote`
--

CREATE TABLE `qna_vote` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of the vote',
  `question_id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of question being voted on',
  `answer_id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID of answer being voted on',
  `vote` tinyint(4) default NULL,
  `profile_id` int(11) default NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `profile_id_question_Id_index` (`profile_id`,`answer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `queue_item`
--

CREATE TABLE `queue_item` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `frame` blob NOT NULL COMMENT 'data: object reference or opaque string',
  `transport` varchar(8) collate utf8_bin NOT NULL COMMENT 'queue for what? "email", "xmpp", "sms", "irc", ...',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `claimed` datetime default NULL COMMENT 'date this item was claimed',
  PRIMARY KEY  (`id`),
  KEY `queue_item_created_idx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `related_group`
--

CREATE TABLE `related_group` (
  `group_id` int(11) NOT NULL COMMENT 'foreign key to user_group',
  `related_group_id` int(11) NOT NULL COMMENT 'foreign key to user_group',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`group_id`,`related_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `remember_me`
--

CREATE TABLE `remember_me` (
  `code` varchar(32) collate utf8_bin NOT NULL COMMENT 'good random code',
  `user_id` int(11) NOT NULL COMMENT 'user who is logged in',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `remote_profile`
--

CREATE TABLE `remote_profile` (
  `id` int(11) NOT NULL COMMENT 'foreign key to profile table',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier, usually a tag URI',
  `postnoticeurl` varchar(255) collate utf8_bin default NULL COMMENT 'URL we use for posting notices',
  `updateprofileurl` varchar(255) collate utf8_bin default NULL COMMENT 'URL we use for updates to this profile',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `remote_profile_uri_key` (`uri`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `reply`
--

CREATE TABLE `reply` (
  `notice_id` int(11) NOT NULL COMMENT 'notice that is the reply',
  `profile_id` int(11) NOT NULL COMMENT 'profile replied to',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  `replied_id` int(11) default NULL COMMENT 'notice replied to (not used, see notice.reply_to)',
  PRIMARY KEY  (`notice_id`,`profile_id`),
  KEY `reply_notice_id_idx` (`notice_id`),
  KEY `reply_profile_id_idx` (`profile_id`),
  KEY `reply_replied_id_idx` (`replied_id`),
  KEY `reply_profile_id_modified_notice_id_idx` (`profile_id`,`modified`,`notice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `rsvp`
--

CREATE TABLE `rsvp` (
  `id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID',
  `uri` varchar(255) collate utf8_bin NOT NULL,
  `profile_id` int(11) default NULL,
  `event_id` char(36) collate utf8_bin NOT NULL COMMENT 'UUID',
  `response` char(1) collate utf8_bin default NULL COMMENT 'Y, N, or ? for three-state yes, no, maybe',
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `rsvp_uri_key` (`uri`),
  UNIQUE KEY `rsvp_profile_event_key` (`profile_id`,`event_id`),
  KEY `rsvp_created_idx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `schema_version`
--

CREATE TABLE `schema_version` (
  `table_name` varchar(64) collate utf8_bin NOT NULL COMMENT 'Table name',
  `checksum` varchar(64) collate utf8_bin NOT NULL COMMENT 'Checksum of schema array; a mismatch indicates we should check the table more thoroughly.',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `searchsub`
--

CREATE TABLE `searchsub` (
  `search` varchar(64) collate utf8_bin NOT NULL COMMENT 'hash search associated with this subscription',
  `profile_id` int(11) NOT NULL COMMENT 'profile ID of subscribing user',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`search`,`profile_id`),
  KEY `searchsub_created_idx` (`created`),
  KEY `searchsub_profile_id_tag_idx` (`profile_id`,`search`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `session`
--

CREATE TABLE `session` (
  `id` varchar(32) collate utf8_bin NOT NULL COMMENT 'session ID',
  `session_data` text collate utf8_bin COMMENT 'session data',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  KEY `session_modified_idx` (`modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `sms_carrier`
--

CREATE TABLE `sms_carrier` (
  `id` int(11) NOT NULL COMMENT 'primary key for SMS carrier',
  `name` varchar(64) collate utf8_bin default NULL COMMENT 'name of the carrier',
  `email_pattern` varchar(255) collate utf8_bin NOT NULL COMMENT 'sprintf pattern for making an email address from a phone number',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `sms_carrier_name_key` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `sms_carrier`
--

INSERT INTO `sms_carrier` VALUES
(100056, '3 River Wireless', '%s@sms.3rivers.net', '[[regtime]]', '[[regtime]]'),
(100057, '7-11 Speakout', '%s@cingularme.com', '[[regtime]]', '[[regtime]]'),
(100058, 'Airtel (Karnataka, India)', '%s@airtelkk.com', '[[regtime]]', '[[regtime]]'),
(100059, 'Alaska Communications Systems', '%s@msg.acsalaska.com', '[[regtime]]', '[[regtime]]'),
(100060, 'Alltel Wireless', '%s@message.alltel.com', '[[regtime]]', '[[regtime]]'),
(100061, 'AT&T Wireless', '%s@txt.att.net', '[[regtime]]', '[[regtime]]'),
(100062, 'Bell Mobility (Canada)', '%s@txt.bell.ca', '[[regtime]]', '[[regtime]]'),
(100063, 'Boost Mobile', '%s@myboostmobile.com', '[[regtime]]', '[[regtime]]'),
(100064, 'Cellular One (Dobson)', '%s@mobile.celloneusa.com', '[[regtime]]', '[[regtime]]'),
(100065, 'Cingular (Postpaid)', '%s@cingularme.com', '[[regtime]]', '[[regtime]]'),
(100066, 'Centennial Wireless', '%s@cwemail.com', '[[regtime]]', '[[regtime]]'),
(100067, 'Cingular (GoPhone prepaid)', '%s@cingularme.com', '[[regtime]]', '[[regtime]]'),
(100068, 'Claro (Nicaragua)', '%s@ideasclaro-ca.com', '[[regtime]]', '[[regtime]]'),
(100069, 'Comcel', '%s@comcel.com.co', '[[regtime]]', '[[regtime]]'),
(100070, 'Cricket', '%s@sms.mycricket.com', '[[regtime]]', '[[regtime]]'),
(100071, 'CTI', '%s@sms.ctimovil.com.ar', '[[regtime]]', '[[regtime]]'),
(100072, 'Emtel (Mauritius)', '%s@emtelworld.net', '[[regtime]]', '[[regtime]]'),
(100073, 'Fido (Canada)', '%s@fido.ca', '[[regtime]]', '[[regtime]]'),
(100074, 'General Communications Inc.', '%s@msg.gci.net', '[[regtime]]', '[[regtime]]'),
(100075, 'Globalstar', '%s@msg.globalstarusa.com', '[[regtime]]', '[[regtime]]'),
(100076, 'Helio', '%s@myhelio.com', '[[regtime]]', '[[regtime]]'),
(100077, 'Illinois Valley Cellular', '%s@ivctext.com', '[[regtime]]', '[[regtime]]'),
(100078, 'i wireless', '%s.iws@iwspcs.net', '[[regtime]]', '[[regtime]]'),
(100079, 'Meteor (Ireland)', '%s@sms.mymeteor.ie', '[[regtime]]', '[[regtime]]'),
(100080, 'Mero Mobile (Nepal)', '%s@sms.spicenepal.com', '[[regtime]]', '[[regtime]]'),
(100081, 'MetroPCS', '%s@mymetropcs.com', '[[regtime]]', '[[regtime]]'),
(100082, 'Movicom', '%s@movimensaje.com.ar', '[[regtime]]', '[[regtime]]'),
(100083, 'Mobitel (Sri Lanka)', '%s@sms.mobitel.lk', '[[regtime]]', '[[regtime]]'),
(100084, 'Movistar (Colombia)', '%s@movistar.com.co', '[[regtime]]', '[[regtime]]'),
(100085, 'MTN (South Africa)', '%s@sms.co.za', '[[regtime]]', '[[regtime]]'),
(100086, 'MTS (Canada)', '%s@text.mtsmobility.com', '[[regtime]]', '[[regtime]]'),
(100087, 'Nextel (Argentina)', '%s@nextel.net.ar', '[[regtime]]', '[[regtime]]'),
(100088, 'Orange (Poland)', '%s@orange.pl', '[[regtime]]', '[[regtime]]'),
(100089, 'Personal (Argentina)', '%s@personal-net.com.ar', '[[regtime]]', '[[regtime]]'),
(100090, 'Plus GSM (Poland)', '%s@text.plusgsm.pl', '[[regtime]]', '[[regtime]]'),
(100091, 'President''s Choice (Canada)', '%s@txt.bell.ca', '[[regtime]]', '[[regtime]]'),
(100092, 'Qwest', '%s@qwestmp.com', '[[regtime]]', '[[regtime]]'),
(100093, 'Rogers (Canada)', '%s@pcs.rogers.com', '[[regtime]]', '[[regtime]]'),
(100094, 'Sasktel (Canada)', '%s@sms.sasktel.com', '[[regtime]]', '[[regtime]]'),
(100095, 'Setar Mobile email (Aruba)', '%s@mas.aw', '[[regtime]]', '[[regtime]]'),
(100096, 'Solo Mobile', '%s@txt.bell.ca', '[[regtime]]', '[[regtime]]'),
(100097, 'Sprint (PCS)', '%s@messaging.sprintpcs.com', '[[regtime]]', '[[regtime]]'),
(100098, 'Sprint (Nextel)', '%s@page.nextel.com', '[[regtime]]', '[[regtime]]'),
(100099, 'Suncom', '%s@tms.suncom.com', '[[regtime]]', '[[regtime]]'),
(100100, 'T-Mobile', '%s@tmomail.net', '[[regtime]]', '[[regtime]]'),
(100101, 'T-Mobile (Austria)', '%s@sms.t-mobile.at', '[[regtime]]', '[[regtime]]'),
(100102, 'Telus Mobility (Canada)', '%s@msg.telus.com', '[[regtime]]', '[[regtime]]'),
(100103, 'Thumb Cellular', '%s@sms.thumbcellular.com', '[[regtime]]', '[[regtime]]'),
(100104, 'Tigo (Formerly Ola)', '%s@sms.tigo.com.co', '[[regtime]]', '[[regtime]]'),
(100105, 'Unicel', '%s@utext.com', '[[regtime]]', '[[regtime]]'),
(100106, 'US Cellular', '%s@email.uscc.net', '[[regtime]]', '[[regtime]]'),
(100107, 'Verizon', '%s@vtext.com', '[[regtime]]', '[[regtime]]'),
(100108, 'Virgin Mobile (Canada)', '%s@vmobile.ca', '[[regtime]]', '[[regtime]]'),
(100109, 'Virgin Mobile (USA)', '%s@vmobl.com', '[[regtime]]', '[[regtime]]'),
(100110, 'YCC', '%s@sms.ycc.ru', '[[regtime]]', '[[regtime]]'),
(100111, 'Orange (UK)', '%s@orange.net', '[[regtime]]', '[[regtime]]'),
(100112, 'Cincinnati Bell Wireless', '%s@gocbw.com', '[[regtime]]', '[[regtime]]'),
(100113, 'T-Mobile Germany', '%s@t-mobile-sms.de', '[[regtime]]', '[[regtime]]'),
(100114, 'Vodafone Germany', '%s@vodafone-sms.de', '[[regtime]]', '[[regtime]]'),
(100115, 'E-Plus', '%s@smsmail.eplus.de', '[[regtime]]', '[[regtime]]'),
(100116, 'Cellular South', '%s@csouth1.com', '[[regtime]]', '[[regtime]]'),
(100117, 'ChinaMobile (139)', '%s@139.com', '[[regtime]]', '[[regtime]]'),
(100118, 'Dialog Axiata', '%s@dialog.lk', '[[regtime]]', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `subscription`
--

CREATE TABLE `subscription` (
  `subscriber` int(11) NOT NULL COMMENT 'profile listening',
  `subscribed` int(11) NOT NULL COMMENT 'profile being listened to',
  `jabber` tinyint(4) default '1' COMMENT 'deliver jabber messages',
  `sms` tinyint(4) default '1' COMMENT 'deliver sms messages',
  `token` varchar(255) collate utf8_bin default NULL COMMENT 'authorization token',
  `secret` varchar(255) collate utf8_bin default NULL COMMENT 'token secret',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`subscriber`,`subscribed`),
  UNIQUE KEY `subscription_uri_key` (`uri`),
  KEY `subscription_subscriber_idx` (`subscriber`,`created`),
  KEY `subscription_subscribed_idx` (`subscribed`,`created`),
  KEY `subscription_token_idx` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `subscription`
--

INSERT INTO `subscription` VALUES
(1, 1, 1, 1, NULL, NULL, NULL, '[[regtime]]', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `subscription_queue`
--

CREATE TABLE `subscription_queue` (
  `subscriber` int(11) NOT NULL COMMENT 'remote or local profile making the request',
  `subscribed` int(11) NOT NULL COMMENT 'remote or local profile being subscribed to',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`subscriber`,`subscribed`),
  KEY `subscription_queue_subscriber_created_idx` (`subscriber`,`created`),
  KEY `subscription_queue_subscribed_created_idx` (`subscribed`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `tagsub`
--

CREATE TABLE `tagsub` (
  `tag` varchar(64) collate utf8_bin NOT NULL COMMENT 'hash tag associated with this subscription',
  `profile_id` int(11) NOT NULL COMMENT 'profile ID of subscribing user',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  PRIMARY KEY  (`tag`,`profile_id`),
  KEY `tagsub_created_idx` (`created`),
  KEY `tagsub_profile_id_tag_idx` (`profile_id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `token`
--

CREATE TABLE `token` (
  `consumer_key` varchar(255) collate utf8_bin NOT NULL COMMENT 'unique identifier, root URL',
  `tok` char(32) collate utf8_bin NOT NULL COMMENT 'identifying value',
  `secret` char(32) collate utf8_bin NOT NULL COMMENT 'secret value',
  `type` tinyint(4) default '0' COMMENT 'request or access',
  `state` tinyint(4) default '0' COMMENT 'for requests, 0 = initial, 1 = authorized, 2 = used',
  `verifier` varchar(255) collate utf8_bin default NULL COMMENT 'verifier string for OAuth 1.0a',
  `verified_callback` varchar(255) collate utf8_bin default NULL COMMENT 'verified callback URL for OAuth 1.0a',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`consumer_key`,`tok`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int(11) NOT NULL COMMENT 'foreign key to profile table',
  `nickname` varchar(64) collate utf8_bin default NULL COMMENT 'nickname or username, duped in profile',
  `password` varchar(255) collate utf8_bin default NULL COMMENT 'salted password, can be null for OpenID users',
  `email` varchar(255) collate utf8_bin default NULL COMMENT 'email address for password recovery etc.',
  `incomingemail` varchar(255) collate utf8_bin default NULL COMMENT 'email address for post-by-email',
  `emailnotifysub` tinyint(4) default '1' COMMENT 'Notify by email of subscriptions',
  `emailnotifyfav` tinyint(4) default '1' COMMENT 'Notify by email of favorites',
  `emailnotifynudge` tinyint(4) default '1' COMMENT 'Notify by email of nudges',
  `emailnotifymsg` tinyint(4) default '1' COMMENT 'Notify by email of direct messages',
  `emailnotifyattn` tinyint(4) default '1' COMMENT 'Notify by email of @-replies',
  `emailmicroid` tinyint(4) default '1' COMMENT 'whether to publish email microid',
  `language` varchar(50) collate utf8_bin default NULL COMMENT 'preferred language',
  `timezone` varchar(50) collate utf8_bin default NULL COMMENT 'timezone',
  `emailpost` tinyint(4) default '1' COMMENT 'Post by email',
  `sms` varchar(64) collate utf8_bin default NULL COMMENT 'sms phone number',
  `carrier` int(11) default NULL COMMENT 'foreign key to sms_carrier',
  `smsnotify` tinyint(4) default '0' COMMENT 'whether to send notices to SMS',
  `smsreplies` tinyint(4) default '0' COMMENT 'whether to send notices to SMS on replies',
  `smsemail` varchar(255) collate utf8_bin default NULL COMMENT 'built from sms and carrier',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universally unique identifier, usually a tag URI',
  `autosubscribe` tinyint(4) default '0' COMMENT 'automatically subscribe to users who subscribe to us',
  `subscribe_policy` tinyint(4) default '0' COMMENT '0 = anybody can subscribe; 1 = require approval',
  `urlshorteningservice` varchar(50) collate utf8_bin default 'internal' COMMENT 'service to use for auto-shortening URLs',
  `inboxed` tinyint(4) default '0' COMMENT 'has an inbox been created for this user?',
  `private_stream` tinyint(4) default '0' COMMENT 'whether to limit all notices to followers only',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_nickname_key` (`nickname`),
  UNIQUE KEY `user_email_key` (`email`),
  UNIQUE KEY `user_incomingemail_key` (`incomingemail`),
  UNIQUE KEY `user_sms_key` (`sms`),
  UNIQUE KEY `user_uri_key` (`uri`),
  KEY `user_smsemail_idx` (`smsemail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `user`
--

INSERT INTO `user` VALUES
(1, '[[admin_username]]', '[[admin_pass]]', NULL, NULL, 1, 1, 1, 1, 1, 1, NULL, NULL, 1, NULL, NULL, 0, 0, NULL, '[[softurl]]/index.php/user/1', 0, 0, 'internal', 1, 0, '[[regtime]]', '[[regtime]]');

-- --------------------------------------------------------

--
-- Table structure for table `user_group`
--

CREATE TABLE `user_group` (
  `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
  `nickname` varchar(64) collate utf8_bin default NULL COMMENT 'nickname for addressing',
  `fullname` varchar(255) collate utf8_bin default NULL COMMENT 'display name',
  `homepage` varchar(255) collate utf8_bin default NULL COMMENT 'URL, cached so we dont regenerate',
  `description` text collate utf8_bin COMMENT 'group description',
  `location` varchar(255) collate utf8_bin default NULL COMMENT 'related physical location, if any',
  `original_logo` varchar(255) collate utf8_bin default NULL COMMENT 'original size logo',
  `homepage_logo` varchar(255) collate utf8_bin default NULL COMMENT 'homepage (profile) size logo',
  `stream_logo` varchar(255) collate utf8_bin default NULL COMMENT 'stream-sized logo',
  `mini_logo` varchar(255) collate utf8_bin default NULL COMMENT 'mini logo',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  `uri` varchar(255) collate utf8_bin default NULL COMMENT 'universal identifier',
  `mainpage` varchar(255) collate utf8_bin default NULL COMMENT 'page for group info to link to',
  `join_policy` tinyint(4) default NULL COMMENT '0=open; 1=requires admin approval',
  `force_scope` tinyint(4) default NULL COMMENT '0=never,1=sometimes,-1=always',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_group_uri_key` (`uri`),
  KEY `user_group_nickname_idx` (`nickname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_im_prefs`
--

CREATE TABLE `user_im_prefs` (
  `user_id` int(11) NOT NULL COMMENT 'user',
  `screenname` varchar(255) collate utf8_bin NOT NULL COMMENT 'screenname on this service',
  `transport` varchar(255) collate utf8_bin NOT NULL COMMENT 'transport (ex xmpp, aim)',
  `notify` tinyint(4) default '0' COMMENT 'Notify when a new notice is sent',
  `replies` tinyint(4) default '0' COMMENT 'Send replies  from people not subscribed to',
  `microid` tinyint(4) default '1' COMMENT 'Publish a MicroID',
  `updatefrompresence` tinyint(4) default '0' COMMENT 'Send replies from people not subscribed to.',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`user_id`,`transport`),
  UNIQUE KEY `transport_screenname_key` (`transport`,`screenname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `user_location_prefs`
--

CREATE TABLE `user_location_prefs` (
  `user_id` int(11) NOT NULL COMMENT 'user who has the preference',
  `share_location` tinyint(4) default '1' COMMENT 'Whether to share location data',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `user_openid`
--

CREATE TABLE `user_openid` (
  `canonical` varchar(255) collate utf8_bin NOT NULL,
  `display` varchar(255) collate utf8_bin NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`canonical`),
  UNIQUE KEY `user_openid_display_idx` (`display`),
  KEY `user_openid_user_id_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `user_openid_trustroot`
--

CREATE TABLE `user_openid_trustroot` (
  `trustroot` varchar(255) collate utf8_bin NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`trustroot`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `user_urlshortener_prefs`
--

CREATE TABLE `user_urlshortener_prefs` (
  `user_id` int(11) NOT NULL COMMENT 'user',
  `urlshorteningservice` varchar(50) collate utf8_bin default 'internal' COMMENT 'service to use for auto-shortening URLs',
  `maxurllength` int(11) NOT NULL COMMENT 'urls greater than this length will be shortened, 0 = always, null = never',
  `maxnoticelength` int(11) NOT NULL COMMENT 'notices with content greater than this value will have all urls shortened, 0 = always, null = never',
  `created` datetime NOT NULL COMMENT 'date this record was created',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;