X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=93e444b6b80254053be81b07479dc17d130e7052;hb=3ad7c395fb9a33319531e04673563e7c9983d8f0;hp=5ebba33393a0374b237b78a654ffc9f464e6343e;hpb=c10e7723185c608217d0944fe62a36995e552322;p=friendica.git diff --git a/database.sql b/database.sql index 5ebba33393..93e444b6b8 100644 --- a/database.sql +++ b/database.sql @@ -11,6 +11,8 @@ SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; +-- +-- -- -------------------------------------------------------- @@ -23,11 +25,24 @@ CREATE TABLE IF NOT EXISTS `challenge` ( `challenge` char(255) NOT NULL, `dfrn-id` char(255) NOT NULL, `expire` int(11) NOT NULL, + `type` char(255) NOT NULL, + `last_update` char(255) NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- +CREATE TABLE IF NOT EXISTS `config` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `cat` char(255) NOT NULL, + `k` char(255) NOT NULL, + `v` text NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=MYISAM DEFAULT CHARSET=utf8; + + + + -- -- Table structure for table `contact` -- @@ -36,15 +51,20 @@ CREATE TABLE IF NOT EXISTS `contact` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL COMMENT 'owner uid', `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `self` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'boolean 1 == info for local UID, primarily name and photo to use in item displays.', + `self` tinyint(1) NOT NULL DEFAULT '0', + `rel` tinyint(1) NOT NULL DEFAULT '0', + `duplex` tinyint(1) NOT NULL DEFAULT '0', + `network` char(255) NOT NULL, `name` char(255) NOT NULL, - `photo` text NOT NULL COMMENT 'remote photo URL initially until approved', + `nick` char(255) NOT NULL, + `photo` text NOT NULL, `thumb` text NOT NULL, + `micro` text NOT NULL, `site-pubkey` text NOT NULL, `issued-id` char(255) NOT NULL, `dfrn-id` char(255) NOT NULL, `url` char(255) NOT NULL, - `issued-pubkey` char(255) NOT NULL, + `alias` char(255) NOT NULL, `pubkey` text NOT NULL, `prvkey` text NOT NULL, `request` text NOT NULL, @@ -53,15 +73,32 @@ CREATE TABLE IF NOT EXISTS `contact` ( `confirm` text NOT NULL, `aes_allow` tinyint(1) NOT NULL DEFAULT '0', `ret-aes` tinyint(1) NOT NULL DEFAULT '0', - `ret-id` char(255) NOT NULL, - `ret-pubkey` text NOT NULL, + `usehub` tinyint(1) NOT NULL DEFAULT '0', + `subhub` tinyint(1) NOT NULL DEFAULT '0', + `hub-verify` char(255) NOT NULL, + `last-update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `success_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `name-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `uri-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `avatar-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `term-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `priority` tinyint(3) NOT NULL, `blocked` tinyint(1) NOT NULL DEFAULT '1', - `rating` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0-5 reputation, 0 unknown, 1 call police, 5 inscrutable', - `reason` text NOT NULL COMMENT 'why a rating was given - will help friends decide to make friends or not', - `profile-id` int(11) NOT NULL DEFAULT '0' COMMENT 'which profile to display - 0 is public default', - PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=109 ; + `readonly` tinyint(1) NOT NULL DEFAULT '0', + `pending` tinyint(1) NOT NULL DEFAULT '1', + `rating` tinyint(1) NOT NULL DEFAULT '0', + `reason` text NOT NULL, + `info` mediumtext NOT NULL, + `profile-id` int(11) NOT NULL DEFAULT '0', + `bdyear` CHAR( 4 ) NOT NULL COMMENT 'birthday notify flag', + PRIMARY KEY (`id`), + KEY `uid` (`uid`), + KEY `self` (`self`), + KEY `issued-id` (`issued-id`), + KEY `dfrn-id` (`dfrn-id`), + KEY `blocked` (`blocked`), + KEY `readonly` (`readonly`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -72,9 +109,10 @@ CREATE TABLE IF NOT EXISTS `contact` ( CREATE TABLE IF NOT EXISTS `group` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL, + `deleted` tinyint(1) NOT NULL DEFAULT '0', `name` char(255) NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -88,7 +126,7 @@ CREATE TABLE IF NOT EXISTS `group_member` ( `gid` int(10) unsigned NOT NULL, `contact-id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -101,13 +139,14 @@ CREATE TABLE IF NOT EXISTS `intro` ( `uid` int(10) unsigned NOT NULL, `contact-id` int(11) NOT NULL, `knowyou` tinyint(1) NOT NULL, + `duplex` tinyint(1) NOT NULL DEFAULT '0', `note` text NOT NULL, `hash` char(255) NOT NULL, `datetime` datetime NOT NULL, `blocked` tinyint(1) NOT NULL DEFAULT '1', `ignore` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=64 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -117,30 +156,90 @@ CREATE TABLE IF NOT EXISTS `intro` ( CREATE TABLE IF NOT EXISTS `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `uri` char(255) NOT NULL, `uid` int(10) unsigned NOT NULL DEFAULT '0', - `type` char(255) NOT NULL, - `resource-id` char(255) NOT NULL, `contact-id` int(10) unsigned NOT NULL DEFAULT '0', + `type` char(255) NOT NULL, + `wall` tinyint(1) NOT NULL DEFAULT '0', + `gravity` tinyint(1) NOT NULL DEFAULT '0', + `parent` int(10) unsigned NOT NULL DEFAULT '0', + `parent-uri` char(255) NOT NULL, + `thr-parent` char(255) NOT NULL, `created` datetime NOT NULL, `edited` datetime NOT NULL, - `commented` datetime NOT NULL, - `hash` char(255) NOT NULL, - `parent` int(10) unsigned NOT NULL DEFAULT '0', + `changed` datetime NOT NULL, + `owner-name` char(255) NOT NULL, + `owner-link` char(255) NOT NULL, + `owner-avatar` char(255) NOT NULL, + `author-name` char(255) NOT NULL, + `author-link` char(255) NOT NULL, + `author-avatar` char(255) NOT NULL, `title` char(255) NOT NULL, - `body` text NOT NULL, - `visible` tinyint(1) NOT NULL DEFAULT '0', - `allow_uid` mediumtext NOT NULL, + `body` mediumtext NOT NULL, + `verb` char(255) NOT NULL, + `object-type` char(255) NOT NULL, + `object` text NOT NULL, + `target-type` char(255) NOT NULL, + `target` text NOT NULL, + `plink` char(255) NOT NULL, + `resource-id` char(255) NOT NULL, + `tag` mediumtext NOT NULL, + `inform` mediumtext NOT NULL, + `location` char(255) NOT NULL, + `coord` char(255) NOT NULL, + `allow_cid` mediumtext NOT NULL, `allow_gid` mediumtext NOT NULL, - `deny_uid` mediumtext NOT NULL, + `deny_cid` mediumtext NOT NULL, `deny_gid` mediumtext NOT NULL, + `private` tinyint(1) NOT NULL DEFAULT '0', + `visible` tinyint(1) NOT NULL DEFAULT '0', + `unseen` tinyint(1) NOT NULL DEFAULT '1', + `deleted` tinyint(1) NOT NULL DEFAULT '0', + `last-child` tinyint(1) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`id`), - KEY `created` (`created`), - KEY `guid` (`hash`), + KEY `uri` (`uri`), + KEY `uid` (`uid`), + KEY `contact-id` (`contact-id`), KEY `type` (`type`), - KEY `commented` (`commented`), + KEY `wall` (`wall`), + KEY `parent` (`parent`), + KEY `parent-uri` (`parent-uri`), + KEY `created` (`created`), + KEY `edited` (`edited`), + KEY `visible` (`visible`), + KEY `deleted` (`deleted`), + KEY `last-child` (`last-child`), + KEY `unseen` (`unseen`), + FULLTEXT KEY `title` (`title`), FULLTEXT KEY `body` (`body`), - FULLTEXT KEY `title` (`title`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ; + FULLTEXT KEY `allow_cid` (`allow_cid`), + FULLTEXT KEY `allow_gid` (`allow_gid`), + FULLTEXT KEY `deny_cid` (`deny_cid`), + FULLTEXT KEY `deny_gid` (`deny_gid`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `mail` +-- + +CREATE TABLE IF NOT EXISTS `mail` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `uid` int(10) unsigned NOT NULL, + `from-name` char(255) NOT NULL, + `from-photo` char(255) NOT NULL, + `from-url` char(255) NOT NULL, + `contact-id` char(255) NOT NULL, + `title` char(255) NOT NULL, + `body` text NOT NULL, + `seen` tinyint(1) NOT NULL, + `replied` tinyint(1) NOT NULL, + `uri` char(255) NOT NULL, + `parent-uri` char(255) NOT NULL, + `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -151,22 +250,25 @@ CREATE TABLE IF NOT EXISTS `item` ( CREATE TABLE IF NOT EXISTS `photo` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL, + `contact-id` int(10) unsigned NOT NULL, `resource-id` char(255) NOT NULL, `created` datetime NOT NULL, `edited` datetime NOT NULL, `title` char(255) NOT NULL, `desc` text NOT NULL, + `album` char(255) NOT NULL, `filename` char(255) NOT NULL, `height` smallint(6) NOT NULL, `width` smallint(6) NOT NULL, `data` mediumblob NOT NULL, `scale` tinyint(3) NOT NULL, - `allow_uid` mediumtext NOT NULL, + `profile` tinyint(1) NOT NULL DEFAULT '0', + `allow_cid` mediumtext NOT NULL, `allow_gid` mediumtext NOT NULL, - `deny_uid` mediumtext NOT NULL, + `deny_cid` mediumtext NOT NULL, `deny_gid` mediumtext NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=95 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -179,23 +281,41 @@ CREATE TABLE IF NOT EXISTS `profile` ( `uid` int(11) NOT NULL, `profile-name` char(255) NOT NULL, `is-default` tinyint(1) NOT NULL DEFAULT '0', + `hide-friends` tinyint(1) NOT NULL DEFAULT '0', `name` char(255) NOT NULL, - `dob` char(32) NOT NULL, + `pdesc` char(255) NOT NULL, + `dob` char(32) NOT NULL DEFAULT '0000-00-00', `address` char(255) NOT NULL, `locality` char(255) NOT NULL, `region` char(255) NOT NULL, `postal-code` char(32) NOT NULL, `country-name` char(255) NOT NULL, - `age` tinyint(3) NOT NULL, - `gender` char(8) NOT NULL, + `gender` char(32) NOT NULL, `marital` char(255) NOT NULL, + `showwith` tinyint(1) NOT NULL DEFAULT '0', + `with` text NOT NULL, + `sexual` char(255) NOT NULL, + `politic` char(255) NOT NULL, + `religion` char(255) NOT NULL, + `keywords` text NOT NULL, `about` text NOT NULL, + `summary` char(255) NOT NULL, + `music` text NOT NULL, + `book` text NOT NULL, + `tv` text NOT NULL, + `film` text NOT NULL, + `interest` text NOT NULL, + `romance` text NOT NULL, + `work` text NOT NULL, + `education` text NOT NULL, + `contact` text NOT NULL, `homepage` char(255) NOT NULL, `photo` char(255) NOT NULL, `thumb` char(255) NOT NULL, `publish` tinyint(1) NOT NULL DEFAULT '0', + `net-publish` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -206,10 +326,12 @@ CREATE TABLE IF NOT EXISTS `profile` ( CREATE TABLE IF NOT EXISTS `profile_check` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL, + `cid` int(10) unsigned NOT NULL, `dfrn_id` char(255) NOT NULL, + `sec` char(255) NOT NULL, `expire` int(11) NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -225,7 +347,7 @@ CREATE TABLE IF NOT EXISTS `session` ( PRIMARY KEY (`id`), KEY `sid` (`sid`), KEY `expire` (`expire`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- @@ -239,10 +361,120 @@ CREATE TABLE IF NOT EXISTS `user` ( `password` char(255) NOT NULL, `nickname` char(255) NOT NULL, `email` char(255) NOT NULL, + `openid` char(255) NOT NULL, `timezone` char(128) NOT NULL, + `register_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `login_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `default-location` char(255) NOT NULL, + `allow_location` tinyint(1) NOT NULL DEFAULT '0', + `theme` char(255) NOT NULL, `pubkey` text NOT NULL, `prvkey` text NOT NULL, - `verified` tinyint(1) unsigned NOT NULL DEFAULT '0', - `blocked` tinyint(1) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`uid`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ; + `spubkey` text NOT NULL, + `sprvkey` text NOT NULL, + `verified` tinyint(1) unsigned NOT NULL DEFAULT '0', + `blocked` tinyint(1) unsigned NOT NULL DEFAULT '0', + `notify-flags` int(11) unsigned NOT NULL DEFAULT '65535', + `page-flags` int(11) unsigned NOT NULL DEFAULT '0', + `pwdreset` char(255) NOT NULL, + `maxreq` int(11) NOT NULL DEFAULT '10', + `allow_cid` mediumtext NOT NULL, + `allow_gid` mediumtext NOT NULL, + `deny_cid` mediumtext NOT NULL, + `deny_gid` mediumtext NOT NULL, + `openidserver` text NOT NULL, + PRIMARY KEY (`uid`), + KEY `nickname` (`nickname`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + + +CREATE TABLE IF NOT EXISTS `register` ( + `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, + `hash` CHAR( 255 ) NOT NULL , + `created` DATETIME NOT NULL , + `uid` INT(11) UNSIGNED NOT NULL, + `password` CHAR(255) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + + +CREATE TABLE IF NOT EXISTS `clients` ( +`client_id` VARCHAR( 20 ) NOT NULL , +`pw` VARCHAR( 20 ) NOT NULL , +`redirect_uri` VARCHAR( 200 ) NOT NULL , +PRIMARY KEY ( `client_id` ) +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `tokens` ( +`id` VARCHAR( 40 ) NOT NULL , +`client_id` VARCHAR( 20 ) NOT NULL , +`expires` INT NOT NULL , +`scope` VARCHAR( 200 ) NOT NULL , +PRIMARY KEY ( `id` ) +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `auth_codes` ( +`id` VARCHAR( 40 ) NOT NULL , +`client_id` VARCHAR( 20 ) NOT NULL , +`redirect_uri` VARCHAR( 200 ) NOT NULL , +`expires` INT NOT NULL , +`scope` VARCHAR( 250 ) NOT NULL , +PRIMARY KEY ( `id` ) +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `queue` ( +`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , +`cid` INT NOT NULL , +`created` DATETIME NOT NULL , +`last` DATETIME NOT NULL , +`content` MEDIUMTEXT NOT NULL +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `pconfig` ( +`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , +`uid` INT NOT NULL DEFAULT '0', +`cat` CHAR( 255 ) NOT NULL , +`k` CHAR( 255 ) NOT NULL , +`v` MEDIUMTEXT NOT NULL +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + + +CREATE TABLE IF NOT EXISTS `hook` ( +`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , +`hook` CHAR( 255 ) NOT NULL , +`file` CHAR( 255 ) NOT NULL , +`function` CHAR( 255 ) NOT NULL +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + + +CREATE TABLE IF NOT EXISTS `addon` ( +`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , +`name` CHAR( 255 ) NOT NULL , +`version` CHAR( 255 ) NOT NULL , +`installed` TINYINT( 1 ) NOT NULL DEFAULT '0' +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + + +CREATE TABLE IF NOT EXISTS `event` ( +`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , +`uid` INT NOT NULL , +`cid` INT NOT NULL , +`created` DATETIME NOT NULL , +`edited` DATETIME NOT NULL , +`start` DATETIME NOT NULL , +`finish` DATETIME NOT NULL , +`desc` TEXT NOT NULL , +`location` TEXT NOT NULL , +`type` CHAR( 255 ) NOT NULL , +`adjust` TINYINT( 1 ) NOT NULL DEFAULT '1', +`allow_cid` MEDIUMTEXT NOT NULL , +`allow_gid` MEDIUMTEXT NOT NULL , +`deny_cid` MEDIUMTEXT NOT NULL , +`deny_gid` MEDIUMTEXT NOT NULL +) ENGINE = MYISAM DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `cache` ( + `k` CHAR( 255 ) NOT NULL PRIMARY KEY , + `v` TEXT NOT NULL, + `updated` DATETIME NOT NULL +) ENGINE = MYISAM DEFAULT CHARSET=utf8;