github.com/jancarloviray/community@v0.41.1-0.20170124221257-33a66c87cf2f/core/database/scripts/autobuild/db_00000.sql (about) 1 -- SQL to set up the Documize database 2 3 DROP TABLE IF EXISTS `user`; 4 5 CREATE TABLE IF NOT EXISTS `user` ( 6 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 7 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 8 `firstname` NVARCHAR(500) NOT NULL, 9 `lastname` NVARCHAR(500) NOT NULL, 10 `email` NVARCHAR(250) NOT NULL UNIQUE, 11 `initials` NVARCHAR(10) NOT NULL DEFAULT "", 12 `password` NVARCHAR(500) NOT NULL DEFAULT "", 13 `salt` NVARCHAR(100) NOT NULL DEFAULT "", 14 `reset` NVARCHAR(100) NOT NULL DEFAULT "", 15 `active` BOOL NOT NULL DEFAULT 1, 16 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 17 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 18 CONSTRAINT pk_refid PRIMARY KEY (refid), 19 UNIQUE INDEX `idx_user_id` (`id` ASC)) 20 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 21 ENGINE = InnoDB; 22 23 DROP TABLE IF EXISTS `audit`; 24 25 CREATE TABLE IF NOT EXISTS `audit` ( 26 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 27 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 28 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 29 `documentid` CHAR(16) NOT NULL DEFAULT "" COLLATE utf8_bin, 30 `pageid` CHAR(16) NOT NULL DEFAULT "" COLLATE utf8_bin, 31 `action` NVARCHAR(200) NOT NULL DEFAULT "", 32 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 33 UNIQUE INDEX `idx_audit_id` (`id` ASC), 34 INDEX `idx_orgid_url` (`orgid`)) 35 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 36 ENGINE = InnoDB; 37 38 DROP TABLE IF EXISTS `organization`; 39 40 CREATE TABLE IF NOT EXISTS `organization` ( 41 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 42 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 43 `company` NVARCHAR(500) NOT NULL, 44 `title` NVARCHAR(500) NOT NULL, 45 `message` NVARCHAR(500) NOT NULL, 46 `url` NVARCHAR(200) NOT NULL DEFAULT "", 47 `domain` NVARCHAR(200) NOT NULL DEFAULT "", 48 `email` NVARCHAR(500) NOT NULL DEFAULT "", 49 `allowanonymousaccess` BOOL NOT NULL DEFAULT 0, 50 `verified` BOOL NOT NULL DEFAULT 0, 51 `serial` NVARCHAR(50) NOT NULL DEFAULT "", 52 `active` BOOL NOT NULL DEFAULT 1, 53 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 54 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 55 CONSTRAINT pk_refid PRIMARY KEY (refid), 56 UNIQUE INDEX `idx_organization_id` (`id` ASC), 57 INDEX `idx_organization_url` (`url`), 58 INDEX `idx_organization_domain` (`domain`)) 59 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 60 ENGINE = InnoDB; 61 62 DROP TABLE IF EXISTS `account`; 63 64 CREATE TABLE IF NOT EXISTS `account` ( 65 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 66 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 67 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 68 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 69 `editor` BOOL NOT NULL DEFAULT 0, 70 `admin` BOOL NOT NULL DEFAULT 0, 71 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 72 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 73 CONSTRAINT pk_refid PRIMARY KEY (refid), 74 UNIQUE INDEX `idx_account_id` (`id` ASC), 75 INDEX `idx_account_userid` (`userid` ASC), 76 INDEX `idx_account_orgid` (`orgid` ASC)) 77 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 78 ENGINE = InnoDB; 79 80 DROP TABLE IF EXISTS `label`; 81 82 CREATE TABLE IF NOT EXISTS `label` ( 83 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 84 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 85 `label` NVARCHAR(255) NOT NULL, 86 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 87 `userid` CHAR(16) NOT NULL DEFAULT "" COLLATE utf8_bin, 88 `type` INT NOT NULL DEFAULT 1, 89 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 90 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 91 CONSTRAINT pk_refid PRIMARY KEY (refid), 92 UNIQUE INDEX `idx_label_id` (`id` ASC), 93 INDEX `idx_label_userid` (`userid` ASC), 94 INDEX `idx_label_orgid` (`orgid` ASC)) 95 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 96 ENGINE = InnoDB; 97 98 DROP TABLE IF EXISTS `labelrole`; 99 100 CREATE TABLE IF NOT EXISTS `labelrole` ( 101 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 102 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 103 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 104 `labelid` CHAR(16) NOT NULL COLLATE utf8_bin, 105 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 106 `canview` BOOL NOT NULL DEFAULT 0, 107 `canedit` BOOL NOT NULL DEFAULT 0, 108 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 109 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 110 CONSTRAINT pk_refid PRIMARY KEY (refid), 111 UNIQUE INDEX `idx_labelrole_id` (`id` ASC), 112 INDEX `idx_labelrole_userid` (`userid` ASC), 113 INDEX `idx_labelrole_labelid` (`labelid` ASC), 114 INDEX `idx_labelrole_orgid` (`orgid` ASC)) 115 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 116 ENGINE = InnoDB; 117 118 DROP TABLE IF EXISTS `document`; 119 120 CREATE TABLE IF NOT EXISTS `document` ( 121 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 122 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 123 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 124 `labelid` CHAR(16) NOT NULL COLLATE utf8_bin, 125 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 126 `job` CHAR(36) NOT NULL, 127 `location` NVARCHAR(2000) NOT NULL, 128 `title` NVARCHAR(2000) NOT NULL, 129 `excerpt` NVARCHAR(2000) NOT NULL, 130 `slug` NVARCHAR(2000) NOT NULL, 131 `tags` NVARCHAR(1000) NOT NULL DEFAULT '', 132 `template` BOOL NOT NULL DEFAULT 0, 133 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 134 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 135 CONSTRAINT pk_refid PRIMARY KEY (refid), 136 UNIQUE INDEX `idx_document_id` (`id` ASC), 137 INDEX `idx_document_orgid` (`orgid` ASC), 138 INDEX `idx_document_labelid` (`labelid` ASC)) 139 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 140 ENGINE = InnoDB; 141 142 DROP TABLE IF EXISTS `page`; 143 144 CREATE TABLE IF NOT EXISTS `page` ( 145 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 146 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 147 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 148 `documentid` CHAR(16) NOT NULL COLLATE utf8_bin, 149 `userid` CHAR(16) DEFAULT '' COLLATE utf8_bin, 150 `contenttype` CHAR(20) NOT NULL DEFAULT 'wysiwyg', 151 `level` INT UNSIGNED NOT NULL, 152 `sequence` DOUBLE NOT NULL, 153 `title` NVARCHAR(2000) NOT NULL, 154 `body` LONGTEXT, 155 `revisions` INT UNSIGNED NOT NULL, 156 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 157 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 158 CONSTRAINT pk_refid PRIMARY KEY (refid), 159 UNIQUE INDEX `idx_page_id` (`id` ASC), 160 INDEX `idx_page_orgid` (`orgid` ASC), 161 INDEX `idx_page_documentid` (`documentid` ASC)) 162 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 163 ENGINE = InnoDB; 164 165 DROP TABLE IF EXISTS `pagemeta`; 166 167 CREATE TABLE IF NOT EXISTS `pagemeta` ( 168 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 169 `pageid` CHAR(16) NOT NULL COLLATE utf8_bin, 170 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 171 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 172 `documentid` CHAR(16) NOT NULL COLLATE utf8_bin, 173 `rawbody` LONGBLOB, 174 `config` JSON, 175 `externalsource` BOOL DEFAULT 0, 176 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 177 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 178 CONSTRAINT pk_pageid PRIMARY KEY (pageid), 179 UNIQUE INDEX `idx_pagemeta_id` (`id` ASC), 180 INDEX `idx_pagemeta_pageid` (`pageid` ASC), 181 INDEX `idx_pagemeta_orgid` (`orgid` ASC), 182 INDEX `idx_pagemeta_documentid` (`documentid` ASC)) 183 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 184 ENGINE = InnoDB; 185 186 DROP TABLE IF EXISTS `attachment`; 187 188 CREATE TABLE IF NOT EXISTS `attachment` ( 189 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 190 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 191 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 192 `documentid` CHAR(16) NOT NULL COLLATE utf8_bin, 193 `job` CHAR(36) NOT NULL, 194 `fileid` CHAR(10) NOT NULL, 195 `filename` NVARCHAR(255) NOT NULL, 196 `data` LONGBLOB, 197 `extension` CHAR(6) NOT NULL, 198 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 199 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 200 CONSTRAINT pk_refid PRIMARY KEY (refid), 201 UNIQUE INDEX `idx_attachment_id` (`id` ASC), 202 INDEX `idx_attachment_orgid` (`orgid` ASC), 203 INDEX `idx_attachment_documentid` (`documentid` ASC), 204 INDEX `idx_attachment_job_and_fileid` (`job`,`fileid` ASC)) 205 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 206 ENGINE = InnoDB; 207 208 DROP TABLE IF EXISTS `search`; 209 210 CREATE TABLE IF NOT EXISTS `search` ( 211 `id` CHAR(16) NOT NULL COLLATE utf8_bin, 212 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 213 `documentid` CHAR(16) NOT NULL COLLATE utf8_bin, 214 `level` INT UNSIGNED NOT NULL, 215 `sequence` DOUBLE NOT NULL, 216 `documenttitle` NVARCHAR(2000) NOT NULL, 217 `pagetitle` NVARCHAR(2000) NOT NULL, 218 `slug` NVARCHAR(2000) NOT NULL, 219 `body` LONGTEXT, 220 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 221 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 222 UNIQUE INDEX `idx_search_id` (`id` ASC), 223 INDEX `idx_search_orgid` (`orgid` ASC), 224 INDEX `idx_search_documentid` (`documentid` ASC), 225 INDEX `idx_search_sequence` (`sequence` ASC), 226 FULLTEXT(`pagetitle`,`body`)) 227 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 228 ENGINE = MyISAM; 229 230 DROP TABLE IF EXISTS `revision`; 231 232 CREATE TABLE IF NOT EXISTS `revision` ( 233 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 234 `refid` CHAR(16) NOT NULL COLLATE utf8_bin, 235 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 236 `documentid` CHAR(16) NOT NULL COLLATE utf8_bin, 237 `ownerid` CHAR(16) DEFAULT '' COLLATE utf8_bin, 238 `pageid` CHAR(16) NOT NULL COLLATE utf8_bin, 239 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 240 `contenttype` CHAR(20) NOT NULL DEFAULT 'wysiwyg', 241 `title` NVARCHAR(2000) NOT NULL, 242 `body` LONGTEXT, 243 `rawbody` LONGBLOB, 244 `config` JSON, 245 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 246 `revised` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 247 CONSTRAINT pk_refid PRIMARY KEY (refid), 248 UNIQUE INDEX `idx_revision_id` (`id` ASC), 249 INDEX `idx_revision_orgid` (`orgid` ASC), 250 INDEX `idx_revision_documentid` (`documentid` ASC), 251 INDEX `idx_revision_pageid` (`pageid` ASC)) 252 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 253 ENGINE = InnoDB; 254 255 DROP TABLE IF EXISTS `config`; 256 257 CREATE TABLE IF NOT EXISTS `config` ( 258 `key` CHAR(255) NOT NULL, 259 `config` JSON, 260 UNIQUE INDEX `idx_config_area` (`key` ASC)) 261 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 262 263 INSERT INTO `config` VALUES ('SMTP','{\"userid\": \"\",\"password\": \"\",\"host\": \"\",\"port\": \"\",\"sender\": \"\"}'); 264 INSERT INTO `config` VALUES ('FILEPLUGINS', 265 '[{\"Comment\": \"Disable (or not) built-in html import (NOTE: no Plugin name)\",\"Disabled\": false,\"API\": \"Convert\",\"Actions\": [\"htm\",\"html\"]},{\"Comment\": \"Disable (or not) built-in Documize API import used from SDK (NOTE: no Plugin name)\",\"Disabled\": false,\"API\": \"Convert\",\"Actions\": [\"documizeapi\"]}]'); 266 INSERT INTO `config` VALUES ('LICENSE','{\"token\": \"\",\"endpoint\": \"https://api.documize.com\"}'); 267 INSERT INTO `config` VALUES ('META','{\"database\": \"db_00000.sql\"}'); 268 INSERT INTO `config` VALUES ('SECTION-GITHUB', '{\"clientID\": \"\", \"clientSecret\": \"\", \"authorizationCallbackURL\": \"https://localhost:5001/api/public/validate?section=github\"}'); 269 INSERT INTO `config` VALUES ('SECTION-TRELLO','{\"appKey\": \"\"}'); 270 271 DROP TABLE IF EXISTS `userconfig`; 272 273 CREATE TABLE IF NOT EXISTS `userconfig` ( 274 `orgid` CHAR(16) NOT NULL COLLATE utf8_bin, 275 `userid` CHAR(16) NOT NULL COLLATE utf8_bin, 276 `key` CHAR(255) NOT NULL, 277 `config` JSON, 278 UNIQUE INDEX `idx_userconfig_orguserkey` (`orgid`, `userid`, `key` ASC)) 279 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 280 ENGINE = InnoDB;