X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=010c63bb24b331e3efce90d7c457ffbba662720e;hb=269f21ddc13c2817b5ba9d8c69814a8f46c1dd6c;hp=5b7c870df9cb6d3da112cd8e3395ad694a73993f;hpb=a76a497d924a16b8b07b126408db21655aac3bd6;p=friendica.git

diff --git a/database.sql b/database.sql
old mode 100755
new mode 100644
index 5b7c870df9..010c63bb24
--- a/database.sql
+++ b/database.sql
@@ -34,10 +34,11 @@ CREATE TABLE IF NOT EXISTS `challenge` (
 
 CREATE TABLE IF NOT EXISTS `config` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `cat` char(255) NOT NULL,
-  `k` char(255) NOT NULL,
+  `cat` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
+  `k` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
   `v` text NOT NULL,
-  PRIMARY KEY (`id`)
+  PRIMARY KEY (`id`), 
+  UNIQUE KEY `access`(`cat`,`k`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
@@ -92,10 +93,12 @@ CREATE TABLE IF NOT EXISTS `contact` (
   `blocked` tinyint(1) NOT NULL DEFAULT '1',
   `readonly` tinyint(1) NOT NULL DEFAULT '0',
   `writable` tinyint(1) NOT NULL DEFAULT '0',
+  `forum` tinyint(1) NOT NULL DEFAULT '0',
   `hidden` tinyint(1) NOT NULL DEFAULT '0',
   `pending` tinyint(1) NOT NULL DEFAULT '1',
   `rating` tinyint(1) NOT NULL DEFAULT '0',
   `reason` text NOT NULL,
+  `closeness` tinyint(2) NOT NULL DEFAULT '99',
   `info` mediumtext NOT NULL,
   `profile-id` int(11) NOT NULL DEFAULT '0',
   `bdyear` CHAR( 4 ) NOT NULL COMMENT 'birthday notify flag',
@@ -115,8 +118,10 @@ CREATE TABLE IF NOT EXISTS `contact` (
   KEY `dfrn-id` (`dfrn-id`),
   KEY `blocked` (`blocked`),
   KEY `readonly` (`readonly`),
+  KEY `forum` (`forum`),
   KEY `hidden` (`hidden`),
-  KEY `pending` (`pending`)  
+  KEY `pending` (`pending`),
+  KEY `closeness` (`closeness`)  
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
 -- --------------------------------------------------------
@@ -214,6 +219,7 @@ CREATE TABLE IF NOT EXISTS `item` (
   `tag` mediumtext NOT NULL,
   `attach` mediumtext NOT NULL,
   `inform` mediumtext NOT NULL,
+  `file` mediumtext NOT NULL,
   `location` char(255) NOT NULL,
   `coord` char(255) NOT NULL,
   `allow_cid` mediumtext NOT NULL,
@@ -224,6 +230,7 @@ CREATE TABLE IF NOT EXISTS `item` (
   `pubmail` tinyint(1) NOT NULL DEFAULT '0',
   `moderated` tinyint(1) NOT NULL DEFAULT '0',
   `visible` tinyint(1) NOT NULL DEFAULT '0',
+  `spam` tinyint(1) NOT NULL DEFAULT '0',
   `starred` tinyint(1) NOT NULL DEFAULT '0',
   `bookmark` tinyint(1) NOT NULL DEFAULT '0',
   `unseen` tinyint(1) NOT NULL DEFAULT '1',
@@ -245,6 +252,7 @@ CREATE TABLE IF NOT EXISTS `item` (
   KEY `received` (`received`),
   KEY `moderated` (`moderated`),
   KEY `visible` (`visible`),
+  KEY `spam` (`spam`),
   KEY `starred` (`starred`),
   KEY `bookmark` (`bookmark`),
   KEY `deleted` (`deleted`),
@@ -253,10 +261,12 @@ CREATE TABLE IF NOT EXISTS `item` (
   KEY `last-child` (`last-child`),
   KEY `unseen` (`unseen`),
   KEY `wall` (`wall`),
+  KEY `author-name` (`author-name`),
   KEY `author-link` (`author-link`),
   FULLTEXT KEY `title` (`title`),
   FULLTEXT KEY `body` (`body`),
   FULLTEXT KEY `tag` (`tag`),
+  FULLTEXT KEY `file` (`file`),
   FULLTEXT KEY `allow_cid` (`allow_cid`),
   FULLTEXT KEY `allow_gid` (`allow_gid`),
   FULLTEXT KEY `deny_cid` (`deny_cid`),
@@ -280,9 +290,10 @@ CREATE TABLE IF NOT EXISTS `mail` (
   `convid` int(10) unsigned NOT NULL,
   `title` char(255) NOT NULL,
   `body` mediumtext NOT NULL,
-  `seen` tinyint(1) NOT NULL,
+  `seen` tinyint(1) NOT NULL DEFAULT '0',
   `reply` tinyint(1) NOT NULL DEFAULT '0',
-  `replied` tinyint(1) NOT NULL,
+  `replied` tinyint(1) NOT NULL DEFAULT '0',
+  `unknown` tinyint(1) NOT NULL DEFAULT '0',
   `uri` char(255) NOT NULL,
   `parent-uri` char(255) NOT NULL,
   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
@@ -291,6 +302,7 @@ CREATE TABLE IF NOT EXISTS `mail` (
   KEY `guid` (`guid`),
   KEY `convid` (`convid`),
   KEY `reply` (`reply`),
+  KEY `unknown` (`unknown`),
   KEY `uri` (`uri`),
   KEY `parent-uri` (`parent-uri`),
   KEY `created` (`created`)
@@ -444,6 +456,8 @@ CREATE TABLE IF NOT EXISTS `user` (
   `blockwall` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `hidewall` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `blocktags` tinyint(1) unsigned NOT NULL DEFAULT '0',
+  `unkmail` tinyint(1) unsigned NOT NULL DEFAULT '0',
+  `cntunkmail` int(11) unsigned NOT NULL DEFAULT '10',
   `notify-flags` int(11) unsigned NOT NULL DEFAULT '65535', 
   `page-flags` int(11) unsigned NOT NULL DEFAULT '0',
   `prvnets` tinyint(1) NOT NULL DEFAULT '0',
@@ -463,6 +477,8 @@ CREATE TABLE IF NOT EXISTS `user` (
   KEY `account_expired` (`account_expired`),
   KEY `hidewall` (`hidewall`),
   KEY `blockwall` (`blockwall`),
+  KEY `unkmail` (`unkmail`),
+  KEY `cntunkmail` (`cntunkmail`),
   KEY `blocked` (`blocked`),
   KEY `verified` (`verified`),
   KEY `login_date` (`login_date`)
@@ -527,9 +543,10 @@ INDEX ( `batch` )
 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
+`cat` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL ,
+`k` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL ,
+`v` MEDIUMTEXT NOT NULL, 
+UNIQUE KEY `access`(`cat`, `k`)
 ) ENGINE = MyISAM DEFAULT CHARSET=utf8;
 
 
@@ -630,6 +647,8 @@ CREATE TABLE IF NOT EXISTS `mailacct` (
 `user` CHAR( 255 ) NOT NULL ,
 `pass` TEXT NOT NULL ,
 `reply_to` CHAR( 255 ) NOT NULL ,
+`action` INT NOT NULL ,
+`movetofolder` CHAR(255) NOT NULL ,
 `pubmail` TINYINT(1) NOT NULL DEFAULT '0',
 `last_check` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
 ) ENGINE = MyISAM DEFAULT CHARSET=utf8;
@@ -737,6 +756,7 @@ CREATE TABLE IF NOT EXISTS `conv` (
 
 CREATE TABLE IF NOT EXISTS `notify` (
 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`hash` CHAR( 64 ) NOT NULL,
 `type` INT( 11 ) NOT NULL ,
 `name` CHAR( 255 ) NOT NULL ,
 `url` CHAR( 255 ) NOT NULL ,
@@ -745,13 +765,18 @@ CREATE TABLE IF NOT EXISTS `notify` (
 `msg` MEDIUMTEXT NOT NULL ,
 `uid` INT NOT NULL ,
 `link` CHAR( 255 ) NOT NULL ,
+`parent` INT( 11 ) NOT NULL,
 `seen` TINYINT( 1 ) NOT NULL DEFAULT '0',
 `verb` CHAR( 255 ) NOT NULL,
 `otype` CHAR( 16 ) NOT NULL,
+INDEX ( `hash` ),
 INDEX ( `type` ),
 INDEX ( `uid` ),
+INDEX ( `link` ),
+INDEX ( `parent` ),
 INDEX ( `seen` ),
-INDEX ( `date` )
+INDEX ( `date` ),
+INDEX ( `otype` )
 ) ENGINE = MyISAM DEFAULT CHARSET=utf8;
 
 CREATE TABLE IF NOT EXISTS `item_id` (
@@ -769,4 +794,84 @@ INDEX ( `twit` ),
 INDEX ( `stat` )
 ) ENGINE = MyISAM DEFAULT CHARSET=utf8;
 
+CREATE TABLE IF NOT EXISTS `manage` (
+`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`uid` INT NOT NULL ,
+`mid` INT NOT NULL,
+INDEX ( `uid` ),
+INDEX ( `mid` )
+) ENGINE = MyISAM DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS `poll_result` (
+`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`poll_id` INT NOT NULL ,
+`choice` INT NOT NULL ,
+INDEX ( `poll_id` ),
+INDEX ( `choice` )
+) ENGINE = MyISAM DEFAULT CHARSET=utf8;
+
+
+CREATE TABLE IF NOT EXISTS `poll` (
+`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`uid` INT NOT NULL ,
+`q0` MEDIUMTEXT NOT NULL ,
+`q1` MEDIUMTEXT NOT NULL ,
+`q2` MEDIUMTEXT NOT NULL ,
+`q3` MEDIUMTEXT NOT NULL ,
+`q4` MEDIUMTEXT NOT NULL ,
+`q5` MEDIUMTEXT NOT NULL ,
+`q6` MEDIUMTEXT NOT NULL ,
+`q7` MEDIUMTEXT NOT NULL ,
+`q8` MEDIUMTEXT NOT NULL ,
+`q9` MEDIUMTEXT NOT NULL ,
+INDEX ( `uid` )
+) ENGINE = MyISAM DEFAULT CHARSET=utf8;
+
+
+--
+-- Table structure for table `notify-threads`
+--
+-- notify-id:          notify.id of the first notification of this thread
+-- master-parent-item: item.id of the parent item
+-- parent-item:        item.id of the imediate parent (only for multi-thread)
+--                     not used yet.
+-- receiver-uid: user.uid of the receiver of this notification.
+--
+-- If we query for a master-parent-item and receiver-uid...
+--   * Returns 1 item: this is not the parent notification, 
+--     so just "follow" the thread (references to this notification)
+--   * Returns no item: this is the first notification related to
+--     this parent item. So, create the record and use the message-id 
+--     header.
+
+
+CREATE TABLE IF NOT EXISTS `notify-threads` (
+`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`notify-id` INT NOT NULL,
+`master-parent-item` INT( 10 ) unsigned NOT NULL DEFAULT '0',
+`parent-item` INT( 10 ) unsigned NOT NULL DEFAULT '0',
+`receiver-uid` INT NOT NULL,
+INDEX ( `master-parent-item` ),
+INDEX ( `receiver-uid` )
+) ENGINE = MyISAM DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS `spam` (
+`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`uid` INT NOT NULL,
+`spam` INT NOT NULL DEFAULT '0',
+`ham` INT NOT NULL DEFAULT '0',
+`term` CHAR(255) NOT NULL,
+`date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+INDEX ( `uid` ),
+INDEX ( `spam` ),
+INDEX ( `ham` ),
+INDEX ( `term` )
+) ENGINE = MyISAM DEFAULT CHARSET=utf8;
+
+
+CREATE TABLE IF NOT EXISTS `userd` (
+`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+`username` CHAR( 255 ) NOT NULL,
+INDEX ( `username` )
+) ENGINE = MyISAM DEFAULT CHARSET=utf8;