BioTorrents.de’s version of Gazelle
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

gazelle.sql 64KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793
  1. -- Please see the limits for kinds of fields below.
  2. -- The commented dates above each table refer to when I last manually reconciled the schema.
  3. --
  4. -- * Integers, ID numbers, etc.
  5. -- * Warning: #1681 Integer display width is deprecated and will be removed in a future release
  6. --
  7. -- * Accession numbers: varchar(50)
  8. -- * Gazelle titles: varchar(50)
  9. -- * Torrent titles: varchar(255)
  10. -- * Ocelot tokens: char(32)
  11. -- * Tag lists: varchar(500)
  12. -- Development or production?
  13. SET FOREIGN_KEY_CHECKS = 0;
  14. CREATE DATABASE gazelle_development CHARACTER SET utf8mb4;
  15. USE gazelle_development;
  16. -- 2020-10-11
  17. CREATE TABLE `api_user_tokens`(
  18. `ID` INT NOT NULL AUTO_INCREMENT,
  19. `UserID` INT NOT NULL,
  20. `AppID` INT DEFAULT NULL,
  21. `Name` VARCHAR(50) NOT NULL,
  22. `Token` CHAR(255) NOT NULL,
  23. `Scope` TEXT,
  24. `Created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  25. `Revoked` ENUM('0', '1', '2') NOT NULL DEFAULT '0',
  26. PRIMARY KEY(`ID`, `Token`),
  27. UNIQUE KEY `Name`(`Name`),
  28. KEY `UserID`(`UserID`)
  29. ) ENGINE = InnoDB CHARSET = utf8mb4;
  30. -- 2020-10-11
  31. CREATE TABLE `api_applications`(
  32. `ID` INT unsigned NOT NULL AUTO_INCREMENT,
  33. `UserID` INT unsigned NOT NULL,
  34. `Name` VARCHAR(50) NOT NULL,
  35. `Token` CHAR(255) NOT NULL,
  36. `Description` TEXT,
  37. `CategoryID` INT unsigned NOT NULL DEFAULT '0',
  38. `TagList` VARCHAR(500) NOT NULL DEFAULT '',
  39. `Created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  40. PRIMARY KEY(`ID`),
  41. UNIQUE KEY `Name`(`Name`),
  42. KEY `UserID`(`UserID`),
  43. KEY `CategoryID`(`CategoryID`)
  44. ) ENGINE = InnoDB CHARSET = utf8mb4;
  45. -- https://github.com/OPSnet/Gazelle/blob/master/db/data/gazelle.sql
  46. -- 2020-12-12
  47. CREATE TABLE `login_attempts`(
  48. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  49. `UserID` int unsigned NOT NULL DEFAULT 0,
  50. `IP` varchar(15) NOT NULL,
  51. `LastAttempt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  52. `Attempts` int unsigned NOT NULL DEFAULT 1,
  53. `BannedUntil` datetime DEFAULT NULL,
  54. `Bans` int unsigned NOT NULL,
  55. `Capture` varchar(20) DEFAULT NULL,
  56. PRIMARY KEY (`ID`),
  57. KEY `IP` (`IP`),
  58. KEY `attempts_idx` (`Attempts`)
  59. ) ENGINE = InnoDB CHARSET = utf8mb4;
  60. -- https://github.com/OPSnet/Gazelle/blob/master/db/data/gazelle.sql
  61. -- 2020-12-12
  62. CREATE TABLE `ip_bans` (
  63. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  64. `FromIP` int unsigned NOT NULL,
  65. `ToIP` int unsigned NOT NULL,
  66. `Reason` varchar(255) DEFAULT NULL,
  67. `UserID` int unsigned NOT NULL DEFAULT 0,
  68. `Created` datetime NOT NULL DEFAULT current_timestamp,
  69. PRIMARY KEY (`ID`),
  70. UNIQUE KEY `FromIP_2` (`FromIP`,`ToIP`)
  71. ) ENGINE=InnoDB CHARSET=utf8mb4;
  72. -- 2020-03-09
  73. CREATE TABLE `artists_alias` (
  74. `AliasID` int NOT NULL AUTO_INCREMENT,
  75. `ArtistID` int NOT NULL,
  76. `Name` varchar(200) DEFAULT NULL, -- todo: 200 vs. 255?
  77. `Redirect` int NOT NULL DEFAULT '0',
  78. `UserID` int unsigned NOT NULL DEFAULT '0',
  79. PRIMARY KEY (`AliasID`),
  80. KEY `ArtistID` (`ArtistID`),
  81. KEY `Name` (`Name`)
  82. ) ENGINE=InnoDB CHARSET=utf8mb4;
  83. -- 2020-03-09
  84. CREATE TABLE `artists_aliases` (
  85. `ID` int NOT NULL AUTO_INCREMENT,
  86. `ArtistID` int NOT NULL,
  87. `Name` varchar(255) NOT NULL DEFAULT '',
  88. `Primary` enum('0','1') DEFAULT '0',
  89. PRIMARY KEY (`ArtistID`,`Name`),
  90. UNIQUE KEY `ArtistID` (`ArtistID`,`Primary`),
  91. KEY `ID` (`ID`)
  92. ) ENGINE=InnoDB CHARSET=utf8mb4;
  93. -- 2020-03-09
  94. CREATE TABLE `artists_group` (
  95. `ArtistID` int NOT NULL AUTO_INCREMENT,
  96. `Name` varchar(255) NOT NULL DEFAULT '',
  97. `ORCiD` varchar(20) NOT NULL DEFAULT '', -- todo
  98. `RevisionID` int DEFAULT NULL,
  99. `LastCommentID` int NOT NULL DEFAULT '0',
  100. PRIMARY KEY (`ArtistID`,`Name`),
  101. KEY `RevisionID` (`RevisionID`)
  102. ) ENGINE=InnoDB CHARSET=utf8mb4;
  103. -- 2020-03-09
  104. CREATE TABLE `artists_tags` (
  105. `TagID` int NOT NULL DEFAULT '0',
  106. `ArtistID` int NOT NULL DEFAULT '0',
  107. `PositiveVotes` int NOT NULL DEFAULT '1',
  108. `NegativeVotes` int NOT NULL DEFAULT '1',
  109. `UserID` int DEFAULT NULL,
  110. PRIMARY KEY (`TagID`,`ArtistID`),
  111. KEY `TagID` (`TagID`),
  112. KEY `ArtistID` (`ArtistID`),
  113. KEY `PositiveVotes` (`PositiveVotes`),
  114. KEY `NegativeVotes` (`NegativeVotes`),
  115. KEY `UserID` (`UserID`)
  116. ) ENGINE=InnoDB CHARSET=utf8mb4;
  117. -- 2020-03-09
  118. CREATE TABLE `badges` (
  119. `ID` int NOT NULL AUTO_INCREMENT,
  120. `Icon` varchar(255) NOT NULL,
  121. `Name` varchar(255) DEFAULT NULL,
  122. `Description` varchar(255) DEFAULT NULL,
  123. PRIMARY KEY (`ID`)
  124. ) ENGINE=InnoDB CHARSET=utf8mb4;
  125. -- 2020-03-09
  126. CREATE TABLE `blog` (
  127. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  128. `UserID` int unsigned NOT NULL,
  129. `Title` varchar(255) NOT NULL,
  130. `Body` text,
  131. `Time` datetime,
  132. `ThreadID` int unsigned DEFAULT NULL,
  133. `Important` tinyint NOT NULL DEFAULT '0',
  134. PRIMARY KEY (`ID`),
  135. KEY `UserID` (`UserID`),
  136. KEY `Time` (`Time`)
  137. ) ENGINE=InnoDB CHARSET=utf8mb4;
  138. -- 2020-03-09
  139. CREATE TABLE `bookmarks_artists` (
  140. `UserID` int NOT NULL,
  141. `ArtistID` int NOT NULL,
  142. `Time` datetime,
  143. KEY `UserID` (`UserID`),
  144. KEY `ArtistID` (`ArtistID`)
  145. ) ENGINE=InnoDB CHARSET=utf8mb4;
  146. -- 2020-03-09
  147. CREATE TABLE `bookmarks_collages` (
  148. `UserID` int NOT NULL,
  149. `CollageID` int NOT NULL,
  150. `Time` datetime,
  151. KEY `UserID` (`UserID`),
  152. KEY `CollageID` (`CollageID`)
  153. ) ENGINE=InnoDB CHARSET=utf8mb4;
  154. -- 2020-03-09
  155. CREATE TABLE `bookmarks_requests` (
  156. `UserID` int NOT NULL,
  157. `RequestID` int NOT NULL,
  158. `Time` datetime,
  159. KEY `UserID` (`UserID`),
  160. KEY `RequestID` (`RequestID`)
  161. ) ENGINE=InnoDB CHARSET=utf8mb4;
  162. -- 2020-03-09
  163. CREATE TABLE `bookmarks_torrents` (
  164. `UserID` int NOT NULL,
  165. `GroupID` int NOT NULL,
  166. `Time` datetime,
  167. `Sort` int NOT NULL DEFAULT '0',
  168. UNIQUE KEY `groups_users` (`GroupID`,`UserID`),
  169. KEY `UserID` (`UserID`),
  170. KEY `GroupID` (`GroupID`)
  171. ) ENGINE=InnoDB CHARSET=utf8mb4;
  172. -- 2020-03-09
  173. CREATE TABLE `collages` (
  174. `ID` int NOT NULL AUTO_INCREMENT,
  175. `Name` varchar(100) NOT NULL DEFAULT '', -- todo: 100 vs. 255?
  176. `Description` text,
  177. `UserID` int NOT NULL DEFAULT '0',
  178. `NumTorrents` int NOT NULL DEFAULT '0',
  179. `Deleted` enum('0','1') DEFAULT '0',
  180. `Locked` enum('0','1') NOT NULL DEFAULT '0',
  181. `CategoryID` int NOT NULL DEFAULT '1',
  182. `TagList` varchar(500) NOT NULL DEFAULT '',
  183. `MaxGroups` int NOT NULL DEFAULT '0',
  184. `MaxGroupsPerUser` int NOT NULL DEFAULT '0',
  185. `Featured` tinyint NOT NULL DEFAULT '0',
  186. `Subscribers` int DEFAULT '0',
  187. `updated` datetime,
  188. PRIMARY KEY (`ID`),
  189. UNIQUE KEY `Name` (`Name`),
  190. KEY `UserID` (`UserID`),
  191. KEY `CategoryID` (`CategoryID`)
  192. ) ENGINE=InnoDB CHARSET=utf8mb4;
  193. -- 2020-03-09
  194. CREATE TABLE `collages_artists` (
  195. `CollageID` int NOT NULL,
  196. `ArtistID` int NOT NULL,
  197. `UserID` int NOT NULL,
  198. `Sort` int NOT NULL DEFAULT '0',
  199. `AddedOn` datetime,
  200. PRIMARY KEY (`CollageID`,`ArtistID`),
  201. KEY `UserID` (`UserID`),
  202. KEY `Sort` (`Sort`)
  203. ) ENGINE=InnoDB CHARSET=utf8mb4;
  204. -- 2020-03-09
  205. CREATE TABLE `collages_torrents` (
  206. `CollageID` int NOT NULL,
  207. `GroupID` int NOT NULL,
  208. `UserID` int NOT NULL,
  209. `Sort` int NOT NULL DEFAULT '0',
  210. `AddedOn` datetime,
  211. PRIMARY KEY (`CollageID`,`GroupID`),
  212. KEY `UserID` (`UserID`),
  213. KEY `Sort` (`Sort`)
  214. ) ENGINE=InnoDB CHARSET=utf8mb4;
  215. -- 2020-03-09
  216. CREATE TABLE `comments` (
  217. `ID` int NOT NULL AUTO_INCREMENT,
  218. `Page` enum('artist','collages','requests','torrents') NOT NULL,
  219. `PageID` int NOT NULL,
  220. `AuthorID` int NOT NULL,
  221. `AddedTime` datetime,
  222. `Body` mediumtext,
  223. `EditedUserID` int DEFAULT NULL,
  224. `EditedTime` datetime DEFAULT NULL,
  225. PRIMARY KEY (`ID`),
  226. KEY `Page` (`Page`,`PageID`),
  227. KEY `AuthorID` (`AuthorID`)
  228. ) ENGINE=InnoDB CHARSET=utf8mb4;
  229. -- 2020-03-09
  230. CREATE TABLE `comments_edits` (
  231. `Page` enum('forums','artist','collages','requests','torrents') DEFAULT NULL,
  232. `PostID` int DEFAULT NULL,
  233. `EditUser` int DEFAULT NULL,
  234. `EditTime` datetime DEFAULT NULL,
  235. `Body` mediumtext,
  236. KEY `EditUser` (`EditUser`),
  237. KEY `PostHistory` (`Page`,`PostID`,`EditTime`)
  238. ) ENGINE=InnoDB CHARSET=utf8mb4;
  239. -- 2020-03-09
  240. CREATE TABLE `cover_art` (
  241. `ID` int NOT NULL AUTO_INCREMENT,
  242. `GroupID` int NOT NULL,
  243. `Image` varchar(255) NOT NULL DEFAULT '',
  244. `Summary` varchar(100) DEFAULT NULL, -- todo: 100 vs. 255?
  245. `UserID` int NOT NULL DEFAULT '0',
  246. `Time` datetime DEFAULT NULL,
  247. PRIMARY KEY (`ID`),
  248. UNIQUE KEY `GroupID` (`GroupID`,`Image`)
  249. ) ENGINE=InnoDB CHARSET=utf8mb4;
  250. -- 2020-03-09
  251. CREATE TABLE `deletion_requests` (
  252. `UserID` int unsigned NOT NULL,
  253. `Value` varchar(255) NOT NULL,
  254. `Type` varchar(255) NOT NULL,
  255. `Reason` text,
  256. `Time` datetime,
  257. PRIMARY KEY (`UserID`,`Value`)
  258. ) ENGINE=InnoDB CHARSET=utf8mb4;
  259. -- 2020-03-09
  260. CREATE TABLE `donations` (
  261. `UserID` int NOT NULL,
  262. `Amount` decimal(6,2) NOT NULL,
  263. `Email` varchar(255) NOT NULL,
  264. `Time` datetime,
  265. `Currency` varchar(5) NOT NULL DEFAULT 'USD',
  266. `Source` varchar(30) NOT NULL DEFAULT '', -- todo: 30 vs. 25?
  267. `Reason` mediumtext,
  268. `Rank` int DEFAULT '0',
  269. `AddedBy` int DEFAULT '0',
  270. `TotalRank` int DEFAULT '0',
  271. KEY `UserID` (`UserID`),
  272. KEY `Time` (`Time`),
  273. KEY `Amount` (`Amount`)
  274. ) ENGINE=InnoDB CHARSET=utf8mb4;
  275. -- 2020-03-09
  276. CREATE TABLE `donor_forum_usernames` (
  277. `UserID` int NOT NULL DEFAULT '0',
  278. `Prefix` varchar(30) NOT NULL DEFAULT '',
  279. `Suffix` varchar(30) NOT NULL DEFAULT '',
  280. `UseComma` tinyint DEFAULT '1',
  281. PRIMARY KEY (`UserID`)
  282. ) ENGINE=InnoDB CHARSET=utf8mb4;
  283. -- 2020-03-09
  284. CREATE TABLE `donor_rewards` (
  285. `UserID` int NOT NULL DEFAULT '0',
  286. `IconMouseOverText` varchar(200) NOT NULL DEFAULT '', -- todo: 200 vs. 255?
  287. `AvatarMouseOverText` varchar(200) NOT NULL DEFAULT '', -- todo
  288. `CustomIcon` varchar(200) NOT NULL DEFAULT '', -- todo
  289. `SecondAvatar` varchar(200) NOT NULL DEFAULT '', -- todo
  290. `CustomIconLink` varchar(200) NOT NULL DEFAULT '', -- todo
  291. `ProfileInfo1` text,
  292. `ProfileInfo2` text,
  293. `ProfileInfo3` text,
  294. `ProfileInfo4` text,
  295. `ProfileInfoTitle1` varchar(255) NOT NULL,
  296. `ProfileInfoTitle2` varchar(255) NOT NULL,
  297. `ProfileInfoTitle3` varchar(255) NOT NULL,
  298. `ProfileInfoTitle4` varchar(255) NOT NULL,
  299. PRIMARY KEY (`UserID`)
  300. ) ENGINE=InnoDB CHARSET=utf8mb4;
  301. -- 2020-03-09
  302. CREATE TABLE `dupe_groups` (
  303. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  304. `Comments` text,
  305. PRIMARY KEY (`ID`)
  306. ) ENGINE=InnoDB CHARSET=utf8mb4;
  307. -- 2020-03-09
  308. CREATE TABLE `email_blacklist` (
  309. `ID` int NOT NULL AUTO_INCREMENT,
  310. `UserID` int NOT NULL,
  311. `Email` varchar(255) NOT NULL,
  312. `Time` datetime,
  313. `Comment` text,
  314. PRIMARY KEY (`ID`)
  315. ) ENGINE=InnoDB CHARSET=utf8mb4;
  316. -- 2020-03-09
  317. CREATE TABLE `featured_albums` (
  318. `GroupID` int NOT NULL DEFAULT '0',
  319. `ThreadID` int NOT NULL DEFAULT '0',
  320. `Title` varchar(35) NOT NULL DEFAULT '', -- todo: 35 vs. 50 vs. 255?
  321. `Started` datetime,
  322. `Ended` datetime
  323. ) ENGINE=InnoDB CHARSET=utf8mb4;
  324. -- 2020-03-09
  325. CREATE TABLE `featured_merch` (
  326. `ProductID` int NOT NULL DEFAULT '0',
  327. `Title` varchar(35) NOT NULL DEFAULT '', -- todo: 35 vs. 50 vs. 255?
  328. `Image` varchar(255) NOT NULL DEFAULT '',
  329. `Started` datetime,
  330. `Ended` datetime,
  331. `ArtistID` int unsigned DEFAULT '0'
  332. ) ENGINE=InnoDB CHARSET=utf8mb4;
  333. -- 2020-03-09
  334. CREATE TABLE `forums` (
  335. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  336. `CategoryID` tinyint NOT NULL DEFAULT '0',
  337. `Sort` int unsigned NOT NULL,
  338. `Name` varchar(40) NOT NULL DEFAULT '', -- todo: 40 vs. 50 vs. 255?
  339. `Description` varchar(255) DEFAULT '',
  340. `MinClassRead` int NOT NULL DEFAULT '0',
  341. `MinClassWrite` int NOT NULL DEFAULT '0',
  342. `MinClassCreate` int NOT NULL DEFAULT '0',
  343. `NumTopics` int NOT NULL DEFAULT '0',
  344. `NumPosts` int NOT NULL DEFAULT '0',
  345. `LastPostID` int NOT NULL DEFAULT '0',
  346. `LastPostAuthorID` int NOT NULL DEFAULT '0',
  347. `LastPostTopicID` int NOT NULL DEFAULT '0',
  348. `LastPostTime` datetime,
  349. PRIMARY KEY (`ID`),
  350. KEY `Sort` (`Sort`),
  351. KEY `MinClassRead` (`MinClassRead`)
  352. ) ENGINE=InnoDB CHARSET=utf8mb4;
  353. -- 2020-03-09
  354. CREATE TABLE `forums_categories` (
  355. `ID` tinyint NOT NULL AUTO_INCREMENT,
  356. `Name` varchar(40) NOT NULL DEFAULT '', -- todo: 40 vs. 50 vs. 255?
  357. `Sort` int unsigned NOT NULL DEFAULT '0',
  358. PRIMARY KEY (`ID`),
  359. KEY `Sort` (`Sort`)
  360. ) ENGINE=InnoDB CHARSET=utf8mb4;
  361. -- 2020-03-09
  362. CREATE TABLE `forums_last_read_topics` (
  363. `UserID` int NOT NULL,
  364. `TopicID` int NOT NULL,
  365. `PostID` int NOT NULL,
  366. PRIMARY KEY (`UserID`,`TopicID`),
  367. KEY `TopicID` (`TopicID`)
  368. ) ENGINE=InnoDB CHARSET=utf8mb4;
  369. -- 2020-03-09
  370. CREATE TABLE `forums_polls` (
  371. `TopicID` int unsigned NOT NULL,
  372. `Question` varchar(255) NOT NULL,
  373. `Answers` text,
  374. `Featured` datetime,
  375. `Closed` enum('0','1') NOT NULL DEFAULT '0',
  376. PRIMARY KEY (`TopicID`)
  377. ) ENGINE=InnoDB CHARSET=utf8mb4;
  378. -- 2020-03-09
  379. CREATE TABLE `forums_polls_votes` (
  380. `TopicID` int unsigned NOT NULL,
  381. `UserID` int unsigned NOT NULL,
  382. `Vote` tinyint unsigned NOT NULL,
  383. PRIMARY KEY (`TopicID`,`UserID`)
  384. ) ENGINE=InnoDB CHARSET=utf8mb4;
  385. -- 2020-03-09
  386. CREATE TABLE `forums_posts` (
  387. `ID` int NOT NULL AUTO_INCREMENT,
  388. `TopicID` int NOT NULL,
  389. `AuthorID` int NOT NULL,
  390. `AddedTime` datetime,
  391. `Body` mediumtext,
  392. `EditedUserID` int DEFAULT NULL,
  393. `EditedTime` datetime DEFAULT NULL,
  394. PRIMARY KEY (`ID`),
  395. KEY `TopicID` (`TopicID`),
  396. KEY `AuthorID` (`AuthorID`)
  397. ) ENGINE=InnoDB CHARSET=utf8mb4;
  398. -- 2020-03-09
  399. CREATE TABLE `forums_specific_rules` (
  400. `ForumID` int unsigned DEFAULT NULL,
  401. `ThreadID` int DEFAULT NULL
  402. ) ENGINE=InnoDB CHARSET=utf8mb4;
  403. -- 2020-03-09
  404. CREATE TABLE `forums_topics` (
  405. `ID` int NOT NULL AUTO_INCREMENT,
  406. `Title` varchar(150) NOT NULL, -- todo: 150 vs. 255?
  407. `AuthorID` int NOT NULL,
  408. `IsLocked` enum('0','1') NOT NULL DEFAULT '0',
  409. `IsSticky` enum('0','1') NOT NULL DEFAULT '0',
  410. `ForumID` int NOT NULL,
  411. `NumPosts` int NOT NULL DEFAULT '0',
  412. `LastPostID` int NOT NULL DEFAULT '0',
  413. `LastPostTime` datetime,
  414. `LastPostAuthorID` int NOT NULL,
  415. `StickyPostID` int NOT NULL DEFAULT '0',
  416. `Ranking` tinyint DEFAULT '0',
  417. `CreatedTime` datetime,
  418. PRIMARY KEY (`ID`),
  419. KEY `AuthorID` (`AuthorID`),
  420. KEY `ForumID` (`ForumID`),
  421. KEY `IsSticky` (`IsSticky`),
  422. KEY `LastPostID` (`LastPostID`),
  423. KEY `Title` (`Title`),
  424. KEY `CreatedTime` (`CreatedTime`)
  425. ) ENGINE=InnoDB CHARSET=utf8mb4;
  426. -- 2020-03-09
  427. CREATE TABLE `forums_topic_notes` (
  428. `ID` int NOT NULL AUTO_INCREMENT,
  429. `TopicID` int NOT NULL,
  430. `AuthorID` int NOT NULL,
  431. `AddedTime` datetime,
  432. `Body` mediumtext,
  433. PRIMARY KEY (`ID`),
  434. KEY `TopicID` (`TopicID`),
  435. KEY `AuthorID` (`AuthorID`)
  436. ) ENGINE=InnoDB CHARSET=utf8mb4;
  437. -- 2020-03-09
  438. CREATE TABLE `friends` (
  439. `UserID` int unsigned NOT NULL,
  440. `FriendID` int unsigned NOT NULL,
  441. `Comment` text,
  442. PRIMARY KEY (`UserID`,`FriendID`),
  443. KEY `UserID` (`UserID`),
  444. KEY `FriendID` (`FriendID`)
  445. ) ENGINE=InnoDB CHARSET=utf8mb4;
  446. -- 2020-03-09
  447. CREATE TABLE `group_log` (
  448. `ID` int NOT NULL AUTO_INCREMENT,
  449. `GroupID` int NOT NULL,
  450. `TorrentID` int NOT NULL,
  451. `UserID` int NOT NULL DEFAULT '0',
  452. `Info` mediumtext,
  453. `Time` datetime,
  454. `Hidden` tinyint NOT NULL DEFAULT '0',
  455. PRIMARY KEY (`ID`),
  456. KEY `GroupID` (`GroupID`),
  457. KEY `TorrentID` (`TorrentID`),
  458. KEY `UserID` (`UserID`)
  459. ) ENGINE=InnoDB CHARSET=utf8mb4;
  460. -- 2020-03-09
  461. CREATE TABLE `invites` (
  462. `InviterID` int NOT NULL DEFAULT '0',
  463. `InviteKey` char(32) NOT NULL,
  464. `Email` varchar(255) NOT NULL,
  465. `Expires` datetime,
  466. `Reason` varchar(255) NOT NULL DEFAULT '',
  467. PRIMARY KEY (`InviteKey`),
  468. KEY `Expires` (`Expires`),
  469. KEY `InviterID` (`InviterID`)
  470. ) ENGINE=InnoDB CHARSET=utf8mb4;
  471. -- 2020-03-09
  472. CREATE TABLE `invite_tree` (
  473. `UserID` int NOT NULL DEFAULT '0',
  474. `InviterID` int NOT NULL DEFAULT '0',
  475. `TreePosition` int NOT NULL DEFAULT '1',
  476. `TreeID` int NOT NULL DEFAULT '1',
  477. `TreeLevel` int NOT NULL DEFAULT '0',
  478. PRIMARY KEY (`UserID`),
  479. KEY `InviterID` (`InviterID`),
  480. KEY `TreePosition` (`TreePosition`),
  481. KEY `TreeID` (`TreeID`),
  482. KEY `TreeLevel` (`TreeLevel`)
  483. ) ENGINE=InnoDB CHARSET=utf8mb4;
  484. -- 2020-03-09
  485. CREATE TABLE `label_aliases` (
  486. `ID` int NOT NULL AUTO_INCREMENT,
  487. `BadLabel` varchar(100) NOT NULL, -- todo: 100 vs. 255?
  488. `AliasLabel` varchar(100) NOT NULL, -- todo
  489. PRIMARY KEY (`ID`),
  490. KEY `BadLabel` (`BadLabel`),
  491. KEY `AliasLabel` (`AliasLabel`)
  492. ) ENGINE=InnoDB CHARSET=utf8mb4;
  493. -- 2020-03-09
  494. CREATE TABLE `last_sent_email` (
  495. `UserID` int NOT NULL,
  496. PRIMARY KEY (`UserID`)
  497. ) ENGINE=InnoDB CHARSET=utf8mb4;
  498. -- 2020-03-09
  499. CREATE TABLE `library_contest` (
  500. `UserID` int NOT NULL,
  501. `TorrentID` int NOT NULL,
  502. `Points` int NOT NULL DEFAULT '0',
  503. PRIMARY KEY (`UserID`,`TorrentID`)
  504. ) ENGINE=InnoDB CHARSET=utf8mb4;
  505. -- 2020-03-09
  506. CREATE TABLE `locked_accounts` (
  507. `UserID` int unsigned NOT NULL,
  508. `Type` tinyint NOT NULL,
  509. PRIMARY KEY (`UserID`),
  510. CONSTRAINT `fk_user_id` FOREIGN KEY (`UserID`) REFERENCES `users_main` (`ID`) ON DELETE CASCADE
  511. ) ENGINE=InnoDB CHARSET=utf8mb4;
  512. -- 2020-03-09
  513. CREATE TABLE `log` (
  514. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  515. `Message` varchar(400) NOT NULL, -- todo: 400 vs. 500?
  516. `Time` datetime,
  517. PRIMARY KEY (`ID`),
  518. KEY `Time` (`Time`)
  519. ) ENGINE=InnoDB CHARSET=utf8mb4;
  520. -- 2020-03-09
  521. CREATE TABLE `misc` (
  522. `ID` int NOT NULL AUTO_INCREMENT,
  523. `Name` varchar(64) NOT NULL,
  524. `First` text,
  525. `Second` text,
  526. PRIMARY KEY (`ID`),
  527. UNIQUE KEY `Name` (`Name`),
  528. KEY `name_index` (`Name`)
  529. ) ENGINE=InnoDB CHARSET=utf8mb4;
  530. -- 2020-03-09
  531. CREATE TABLE `news` (
  532. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  533. `UserID` int unsigned NOT NULL,
  534. `Title` varchar(255) NOT NULL,
  535. `Body` text,
  536. `Time` datetime,
  537. PRIMARY KEY (`ID`),
  538. KEY `UserID` (`UserID`),
  539. KEY `Time` (`Time`)
  540. ) ENGINE=InnoDB CHARSET=utf8mb4;
  541. -- 2020-03-09
  542. CREATE TABLE `new_info_hashes` (
  543. `TorrentID` int NOT NULL,
  544. `InfoHash` binary(20) DEFAULT NULL,
  545. PRIMARY KEY (`TorrentID`),
  546. KEY `InfoHash` (`InfoHash`)
  547. ) ENGINE=InnoDB CHARSET=utf8mb4;
  548. -- 2020-03-09
  549. CREATE TABLE `ocelot_query_times` (
  550. `buffer` enum('users','torrents','snatches','peers') NOT NULL,
  551. `starttime` datetime,
  552. `ocelotinstance` datetime,
  553. `querylength` int NOT NULL,
  554. `timespent` int NOT NULL,
  555. UNIQUE KEY `starttime` (`starttime`)
  556. ) ENGINE=InnoDB CHARSET=utf8mb4;
  557. -- 2020-03-09
  558. CREATE TABLE `permissions` (
  559. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  560. `Level` int unsigned NOT NULL,
  561. `Name` varchar(25) NOT NULL,
  562. `Values` text,
  563. `DisplayStaff` enum('0','1') NOT NULL DEFAULT '0',
  564. `PermittedForums` varchar(150) NOT NULL DEFAULT '', -- todo: 150 vs. 255?
  565. `Secondary` tinyint NOT NULL DEFAULT '0',
  566. `Abbreviation` varchar(5) NOT NULL DEFAULT '',
  567. PRIMARY KEY (`ID`),
  568. UNIQUE KEY `Level` (`Level`),
  569. KEY `DisplayStaff` (`DisplayStaff`)
  570. ) ENGINE=InnoDB CHARSET=utf8mb4;
  571. -- 2020-03-09
  572. CREATE TABLE `pm_conversations` (
  573. `ID` int NOT NULL AUTO_INCREMENT,
  574. `Subject` varchar(255) DEFAULT NULL,
  575. PRIMARY KEY (`ID`)
  576. ) ENGINE=InnoDB CHARSET=utf8mb4;
  577. -- 2020-03-09
  578. CREATE TABLE `pm_conversations_users` (
  579. `UserID` int NOT NULL DEFAULT '0',
  580. `ConvID` int NOT NULL DEFAULT '0',
  581. `InInbox` enum('1','0') NOT NULL,
  582. `InSentbox` enum('1','0') NOT NULL,
  583. `SentDate` datetime,
  584. `ReceivedDate` datetime,
  585. `UnRead` enum('1','0') NOT NULL DEFAULT '1',
  586. `Sticky` enum('1','0') NOT NULL DEFAULT '0',
  587. `ForwardedTo` int NOT NULL DEFAULT '0',
  588. PRIMARY KEY (`UserID`,`ConvID`),
  589. KEY `InInbox` (`InInbox`),
  590. KEY `InSentbox` (`InSentbox`),
  591. KEY `ConvID` (`ConvID`),
  592. KEY `UserID` (`UserID`),
  593. KEY `SentDate` (`SentDate`),
  594. KEY `ReceivedDate` (`ReceivedDate`),
  595. KEY `Sticky` (`Sticky`),
  596. KEY `ForwardedTo` (`ForwardedTo`)
  597. ) ENGINE=InnoDB CHARSET=utf8mb4;
  598. -- 2020-03-09
  599. CREATE TABLE `pm_messages` (
  600. `ID` int NOT NULL AUTO_INCREMENT,
  601. `ConvID` int NOT NULL DEFAULT '0',
  602. `SentDate` datetime,
  603. `SenderID` int NOT NULL DEFAULT '0',
  604. `Body` text,
  605. PRIMARY KEY (`ID`),
  606. KEY `ConvID` (`ConvID`)
  607. ) ENGINE=InnoDB CHARSET=utf8mb4;
  608. -- Added back 2020-12-05
  609. CREATE TABLE `reports` (
  610. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  611. `UserID` int unsigned NOT NULL DEFAULT '0',
  612. `ThingID` int unsigned NOT NULL DEFAULT '0',
  613. `Type` varchar(30) DEFAULT NULL,
  614. `Comment` text,
  615. `ResolverID` int unsigned NOT NULL DEFAULT '0',
  616. `Status` enum('New','InProgress','Resolved') DEFAULT 'New',
  617. `ResolvedTime` datetime,
  618. `ReportedTime` datetime,
  619. `Reason` text,
  620. `ClaimerID` int unsigned NOT NULL DEFAULT '0',
  621. `Notes` text,
  622. PRIMARY KEY (`ID`),
  623. KEY `Status` (`Status`),
  624. KEY `Type` (`Type`),
  625. KEY `ResolvedTime` (`ResolvedTime`),
  626. KEY `ResolverID` (`ResolverID`)
  627. ) ENGINE=InnoDB CHARSET=utf8mb4;
  628. -- 2020-03-09
  629. CREATE TABLE `reportsv2` (
  630. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  631. `ReporterID` int unsigned NOT NULL DEFAULT '0',
  632. `TorrentID` int unsigned NOT NULL DEFAULT '0',
  633. `Type` varchar(25) DEFAULT '', -- todo: 25 vs. 50 vs. 255?
  634. `UserComment` text,
  635. `ResolverID` int unsigned NOT NULL DEFAULT '0',
  636. `Status` enum('New','InProgress','Resolved') DEFAULT 'New',
  637. `ReportedTime` datetime,
  638. `LastChangeTime` datetime,
  639. `ModComment` text,
  640. `Track` text,
  641. `Image` text,
  642. `ExtraID` text,
  643. `Link` text,
  644. `LogMessage` text,
  645. PRIMARY KEY (`ID`),
  646. KEY `Status` (`Status`),
  647. KEY `Type` (`Type`(1)),
  648. KEY `LastChangeTime` (`LastChangeTime`),
  649. KEY `TorrentID` (`TorrentID`),
  650. KEY `ResolverID` (`ResolverID`)
  651. ) ENGINE=InnoDB CHARSET=utf8mb4;
  652. -- 2020-03-09
  653. CREATE TABLE `reports_email_blacklist` (
  654. `ID` int NOT NULL AUTO_INCREMENT,
  655. `Type` tinyint NOT NULL DEFAULT '0',
  656. `UserID` int NOT NULL,
  657. `Time` datetime,
  658. `Checked` tinyint NOT NULL DEFAULT '0',
  659. `ResolverID` int DEFAULT '0',
  660. `Email` varchar(255) NOT NULL DEFAULT '',
  661. PRIMARY KEY (`ID`),
  662. KEY `Time` (`Time`),
  663. KEY `UserID` (`UserID`)
  664. ) ENGINE=InnoDB CHARSET=utf8mb4;
  665. -- 2020-03-09
  666. CREATE TABLE `requests` (
  667. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  668. `UserID` int unsigned NOT NULL DEFAULT '0',
  669. `TimeAdded` datetime,
  670. `LastVote` datetime DEFAULT NULL,
  671. `CategoryID` int NOT NULL,
  672. `Title` varchar(255) DEFAULT NULL,
  673. `Title2` varchar(255) DEFAULT NULL,
  674. `TitleJP` varchar(255) DEFAULT NULL,
  675. `Image` varchar(255) DEFAULT NULL,
  676. `Description` text,
  677. `CatalogueNumber` varchar(50) NOT NULL,
  678. `FillerID` int unsigned NOT NULL DEFAULT '0',
  679. `TorrentID` int unsigned NOT NULL DEFAULT '0',
  680. `TimeFilled` datetime,
  681. `Visible` binary(1) NOT NULL DEFAULT '1',
  682. `GroupID` int DEFAULT '0',
  683. PRIMARY KEY (`ID`),
  684. KEY `Userid` (`UserID`),
  685. KEY `Name` (`Title`),
  686. KEY `Filled` (`TorrentID`),
  687. KEY `FillerID` (`FillerID`),
  688. KEY `TimeAdded` (`TimeAdded`),
  689. KEY `TimeFilled` (`TimeFilled`),
  690. KEY `LastVote` (`LastVote`),
  691. KEY `GroupID` (`GroupID`),
  692. KEY `NameJP` (`TitleJP`)
  693. ) ENGINE=InnoDB CHARSET=utf8mb4;
  694. -- 2020-03-09
  695. CREATE TABLE `requests_artists` (
  696. `RequestID` int unsigned NOT NULL,
  697. `ArtistID` int NOT NULL,
  698. PRIMARY KEY (`RequestID`, `ArtistID`)
  699. ) ENGINE=InnoDB CHARSET=utf8mb4;
  700. -- 2020-03-09
  701. CREATE TABLE `requests_tags` (
  702. `TagID` int NOT NULL DEFAULT '0',
  703. `RequestID` int NOT NULL DEFAULT '0',
  704. PRIMARY KEY (`TagID`,`RequestID`),
  705. KEY `TagID` (`TagID`),
  706. KEY `RequestID` (`RequestID`)
  707. ) ENGINE=InnoDB CHARSET=utf8mb4;
  708. -- 2020-03-09
  709. CREATE TABLE `requests_votes` (
  710. `RequestID` int NOT NULL DEFAULT '0',
  711. `UserID` int NOT NULL DEFAULT '0',
  712. `Bounty` bigint unsigned NOT NULL,
  713. PRIMARY KEY (`RequestID`,`UserID`),
  714. KEY `RequestID` (`RequestID`),
  715. KEY `UserID` (`UserID`),
  716. KEY `Bounty` (`Bounty`)
  717. ) ENGINE=InnoDB CHARSET=utf8mb4;
  718. -- 2020-03-09
  719. CREATE TABLE `schedule` (
  720. `NextHour` int NOT NULL DEFAULT '0',
  721. `NextDay` int NOT NULL DEFAULT '0',
  722. `NextBiWeekly` int NOT NULL DEFAULT '0'
  723. ) ENGINE=InnoDB CHARSET=utf8mb4;
  724. -- 2020-03-09
  725. CREATE TABLE `shop_freeleeches` (
  726. `TorrentID` int NOT NULL,
  727. `ExpiryTime` datetime,
  728. PRIMARY KEY (`TorrentID`),
  729. KEY `ExpiryTime` (`ExpiryTime`)
  730. ) ENGINE=InnoDB CHARSET=utf8mb4;
  731. -- 2020-03-09
  732. CREATE TABLE `slaves` (
  733. `UserID` int NOT NULL DEFAULT '0',
  734. `OwnerID` int NOT NULL DEFAULT '0',
  735. PRIMARY KEY (`UserID`)
  736. ) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci;
  737. -- 2020-03-09
  738. CREATE TABLE `sphinx_a` (
  739. `gid` int DEFAULT NULL,
  740. `aname` text,
  741. KEY `gid` (`gid`)
  742. ) ENGINE=InnoDB CHARSET=utf8mb4;
  743. -- 2020-03-09
  744. CREATE TABLE `sphinx_delta` (
  745. `ID` int NOT NULL,
  746. `GroupID` int NOT NULL DEFAULT '0',
  747. `GroupName` varchar(255) DEFAULT NULL,
  748. `GroupTitle2` varchar(255) DEFAULT NULL,
  749. `GroupNameJP` varchar(255) DEFAULT NULL,
  750. `ArtistName` varchar(2048) DEFAULT NULL, -- todo: 2048 vs. 255?
  751. `TagList` varchar(728) DEFAULT NULL, -- todo: 728 vs. 500?
  752. `Year` int DEFAULT NULL,
  753. `CatalogueNumber` varchar(50) DEFAULT NULL,
  754. `CategoryID` tinyint DEFAULT NULL,
  755. `Time` int DEFAULT NULL,
  756. `Size` bigint DEFAULT NULL,
  757. `Snatched` int DEFAULT NULL,
  758. `Seeders` int DEFAULT NULL,
  759. `Leechers` int DEFAULT NULL,
  760. `FreeTorrent` tinyint DEFAULT NULL,
  761. `Media` varchar(255) DEFAULT NULL,
  762. `Container` varchar(255) DEFAULT NULL,
  763. `Codec` varchar(255) DEFAULT NULL,
  764. `Resolution` varchar(255) DEFAULT NULL,
  765. `Version` varchar(255) DEFAULT NULL,
  766. `Studio` varchar(100) DEFAULT NULL, -- todo: 100 vs. 255?
  767. `Series` varchar(100) DEFAULT NULL, -- todo
  768. `Censored` tinyint NOT NULL DEFAULT '1',
  769. `FileList` mediumtext,
  770. `Description` text,
  771. `VoteScore` float NOT NULL DEFAULT '0',
  772. `LastChanged` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  773. PRIMARY KEY (`ID`),
  774. KEY `GroupID` (`GroupID`),
  775. KEY `Size` (`Size`)
  776. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  777. -- 2020-03-09
  778. CREATE TABLE `sphinx_index_last_pos` (
  779. `Type` varchar(16) NOT NULL DEFAULT '', -- todo: 16 vs. 25 vs. 50?
  780. `ID` int DEFAULT NULL,
  781. PRIMARY KEY (`Type`)
  782. ) ENGINE=InnoDB CHARSET=utf8mb4;
  783. -- 2020-03-09
  784. CREATE TABLE `sphinx_requests` (
  785. `ID` int unsigned NOT NULL,
  786. `UserID` int unsigned NOT NULL DEFAULT '0',
  787. `TimeAdded` int unsigned NOT NULL,
  788. `LastVote` int unsigned NOT NULL,
  789. `CategoryID` int NOT NULL,
  790. `Title` varchar(255) DEFAULT NULL,
  791. `Title2` varchar(255) DEFAULT NULL,
  792. `TitleJP` varchar(255) DEFAULT NULL,
  793. `Year` int DEFAULT NULL,
  794. `ArtistList` varchar(2048) DEFAULT NULL, -- todo: 2048 vs. 255?
  795. `CatalogueNumber` varchar(50) NOT NULL,
  796. `FillerID` int unsigned NOT NULL DEFAULT '0',
  797. `TorrentID` int unsigned NOT NULL DEFAULT '0',
  798. `TimeFilled` int unsigned NOT NULL,
  799. `Visible` binary(1) NOT NULL DEFAULT '1',
  800. `Bounty` bigint unsigned NOT NULL DEFAULT '0',
  801. `Votes` int unsigned NOT NULL DEFAULT '0',
  802. PRIMARY KEY (`ID`),
  803. KEY `Userid` (`UserID`),
  804. KEY `Name` (`Title`),
  805. KEY `Filled` (`TorrentID`),
  806. KEY `FillerID` (`FillerID`),
  807. KEY `TimeAdded` (`TimeAdded`),
  808. KEY `Year` (`Year`),
  809. KEY `TimeFilled` (`TimeFilled`),
  810. KEY `LastVote` (`LastVote`)
  811. ) ENGINE=InnoDB CHARSET=utf8mb4;
  812. -- 2020-03-09
  813. CREATE TABLE `sphinx_requests_delta` (
  814. `ID` int unsigned NOT NULL,
  815. `UserID` int unsigned NOT NULL DEFAULT '0',
  816. `TimeAdded` int unsigned DEFAULT NULL,
  817. `LastVote` int unsigned DEFAULT NULL,
  818. `CategoryID` tinyint DEFAULT NULL,
  819. `Title` varchar(255) DEFAULT NULL,
  820. `Title2` varchar(255) DEFAULT NULL,
  821. `TitleJP` varchar(255) DEFAULT NULL,
  822. `TagList` varchar(728) NOT NULL DEFAULT '', -- todo: 728 vs. 500?
  823. `ArtistList` varchar(2048) DEFAULT NULL, -- todo: 2048 vs. 255?
  824. `CatalogueNumber` varchar(50) DEFAULT NULL,
  825. `FillerID` int unsigned NOT NULL DEFAULT '0',
  826. `TorrentID` int unsigned NOT NULL DEFAULT '0',
  827. `TimeFilled` int unsigned DEFAULT NULL,
  828. `Visible` binary(1) NOT NULL DEFAULT '1',
  829. `Bounty` bigint unsigned NOT NULL DEFAULT '0',
  830. `Votes` int unsigned NOT NULL DEFAULT '0',
  831. PRIMARY KEY (`ID`),
  832. KEY `Userid` (`UserID`),
  833. KEY `Name` (`Title`),
  834. KEY `Filled` (`TorrentID`),
  835. KEY `FillerID` (`FillerID`),
  836. KEY `TimeAdded` (`TimeAdded`),
  837. KEY `TimeFilled` (`TimeFilled`),
  838. KEY `LastVote` (`LastVote`)
  839. ) ENGINE=InnoDB CHARSET=utf8mb4;
  840. -- 2020-03-09
  841. CREATE TABLE `sphinx_t` (
  842. `id` int NOT NULL,
  843. `gid` int NOT NULL,
  844. `uid` int NOT NULL,
  845. `size` bigint NOT NULL,
  846. `snatched` int NOT NULL,
  847. `seeders` int NOT NULL,
  848. `leechers` int NOT NULL,
  849. `time` int NOT NULL,
  850. `freetorrent` tinyint NOT NULL,
  851. `media` varchar(25) NOT NULL,
  852. `container` varchar(25) NOT NULL,
  853. `resolution` varchar(25) NOT NULL,
  854. `codec` varchar(25) NOT NULL,
  855. `Version` varchar(25) NOT NULL,
  856. `filelist` mediumtext,
  857. `description` text,
  858. `censored` tinyint NOT NULL,
  859. PRIMARY KEY (`id`),
  860. KEY `gid` (`gid`)
  861. ) ENGINE=InnoDB CHARSET=utf8mb4;
  862. -- 2020-03-09
  863. CREATE TABLE `sphinx_tg` (
  864. `id` int NOT NULL,
  865. `name` varchar(255) DEFAULT NULL,
  866. `Title2` varchar(255) DEFAULT NULL,
  867. `namejp` varchar(255) DEFAULT NULL,
  868. `tags` varchar(500) DEFAULT NULL,
  869. `year` smallint DEFAULT NULL,
  870. `cnumber` varchar(50) DEFAULT NULL,
  871. `studio` varchar(255) DEFAULT NULL,
  872. `series` varchar(255) DEFAULT NULL,
  873. `catid` smallint DEFAULT NULL,
  874. `dlsid` varchar(15) NOT NULL, -- todo: 15 vs. 25 vs. 50?
  875. PRIMARY KEY (`id`)
  876. ) ENGINE=InnoDB CHARSET=utf8mb4;
  877. -- 2020-03-09
  878. CREATE TABLE `staff_pm_conversations` (
  879. `ID` int NOT NULL AUTO_INCREMENT,
  880. `Subject` text,
  881. `UserID` int DEFAULT NULL,
  882. `Status` enum('Open','Unanswered','Resolved') DEFAULT NULL,
  883. `Level` int DEFAULT NULL,
  884. `AssignedToUser` int DEFAULT NULL,
  885. `Date` datetime DEFAULT NULL,
  886. `Unread` tinyint DEFAULT NULL,
  887. `ResolverID` int DEFAULT NULL,
  888. PRIMARY KEY (`ID`),
  889. KEY `StatusAssigned` (`Status`,`AssignedToUser`),
  890. KEY `StatusLevel` (`Status`,`Level`)
  891. ) ENGINE=InnoDB CHARSET=utf8mb4;
  892. -- 2020-03-09
  893. CREATE TABLE `staff_pm_messages` (
  894. `ID` int NOT NULL AUTO_INCREMENT,
  895. `UserID` int DEFAULT NULL,
  896. `SentDate` datetime DEFAULT NULL,
  897. `Message` text,
  898. `ConvID` int DEFAULT NULL,
  899. PRIMARY KEY (`ID`)
  900. ) ENGINE=InnoDB CHARSET=utf8mb4;
  901. -- 2020-03-09
  902. CREATE TABLE `staff_pm_responses` (
  903. `ID` int NOT NULL AUTO_INCREMENT,
  904. `Message` text,
  905. `Name` text,
  906. PRIMARY KEY (`ID`)
  907. ) ENGINE=InnoDB CHARSET=utf8mb4;
  908. -- 2020-03-09
  909. CREATE TABLE `stylesheets` (
  910. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  911. `Name` varchar(255) NOT NULL,
  912. `Description` varchar(255) NOT NULL,
  913. `Default` enum('0','1') NOT NULL DEFAULT '0',
  914. `Additions` text,
  915. `Color` varchar(7), -- #deadbe
  916. PRIMARY KEY (`ID`)
  917. ) ENGINE=InnoDB CHARSET=utf8mb4;
  918. -- todo: Start again here
  919. CREATE TABLE `tag_aliases` (
  920. `ID` int NOT NULL AUTO_INCREMENT,
  921. `BadTag` varchar(255) DEFAULT NULL,
  922. `AliasTag` varchar(255) DEFAULT NULL,
  923. PRIMARY KEY (`ID`),
  924. KEY `BadTag` (`BadTag`),
  925. KEY `AliasTag` (`AliasTag`)
  926. ) ENGINE=InnoDB CHARSET=utf8mb4;
  927. CREATE TABLE `tags` (
  928. `ID` int NOT NULL AUTO_INCREMENT,
  929. `Name` varchar(100) DEFAULT NULL,
  930. `TagType` enum('genre','other') NOT NULL DEFAULT 'other',
  931. `Uses` int NOT NULL DEFAULT '1',
  932. `UserID` int DEFAULT NULL,
  933. PRIMARY KEY (`ID`),
  934. UNIQUE KEY `Name_2` (`Name`),
  935. KEY `TagType` (`TagType`),
  936. KEY `Uses` (`Uses`),
  937. KEY `UserID` (`UserID`)
  938. ) ENGINE=InnoDB CHARSET=utf8mb4;
  939. CREATE TABLE `top10_history` (
  940. `ID` int NOT NULL AUTO_INCREMENT,
  941. `Date` datetime,
  942. `Type` enum('Daily','Weekly') DEFAULT NULL,
  943. PRIMARY KEY (`ID`)
  944. ) ENGINE=InnoDB CHARSET=utf8mb4;
  945. CREATE TABLE `top10_history_torrents` (
  946. `HistoryID` int NOT NULL DEFAULT '0',
  947. `Rank` tinyint NOT NULL DEFAULT '0',
  948. `TorrentID` int NOT NULL DEFAULT '0',
  949. `TitleString` varchar(150) NOT NULL DEFAULT '',
  950. `TagString` varchar(100) NOT NULL DEFAULT ''
  951. ) ENGINE=InnoDB CHARSET=utf8mb4;
  952. CREATE TABLE `top_snatchers` (
  953. `UserID` int unsigned NOT NULL,
  954. PRIMARY KEY (`UserID`)
  955. ) ENGINE=InnoDB CHARSET=utf8mb4;
  956. CREATE TABLE `torrents` (
  957. `ID` int NOT NULL AUTO_INCREMENT,
  958. `GroupID` int NOT NULL DEFAULT '0',
  959. `UserID` int DEFAULT NULL,
  960. `Media` varchar(25) DEFAULT NULL,
  961. `Container` varchar(25) DEFAULT NULL,
  962. `Codec` varchar(25) DEFAULT NULL,
  963. `Resolution` varchar(25) DEFAULT NULL,
  964. `Version` varchar(10) DEFAULT NULL,
  965. `Censored` tinyint NOT NULL DEFAULT '1',
  966. `Anonymous` tinyint NOT NULL DEFAULT '0',
  967. `info_hash` blob NOT NULL,
  968. `FileCount` int NOT NULL DEFAULT '0',
  969. `FileList` mediumtext,
  970. `FilePath` varchar(255) NOT NULL DEFAULT '',
  971. `Size` bigint NOT NULL DEFAULT '0',
  972. `Leechers` int NOT NULL DEFAULT '0',
  973. `Seeders` int NOT NULL DEFAULT '0',
  974. `last_action` datetime,
  975. `FreeTorrent` enum('0','1','2') NOT NULL DEFAULT '0',
  976. `FreeLeechType` enum('0','1','2','3','4') NOT NULL DEFAULT '0',
  977. `Time` datetime,
  978. `Description` text,
  979. `Snatched` int unsigned NOT NULL DEFAULT '0',
  980. `balance` bigint NOT NULL DEFAULT '0',
  981. `LastReseedRequest` datetime,
  982. `Archive` varchar(10) NOT NULL DEFAULT '',
  983. PRIMARY KEY (`ID`),
  984. UNIQUE KEY `InfoHash` (`info_hash`(40)),
  985. KEY `GroupID` (`GroupID`),
  986. KEY `UserID` (`UserID`),
  987. KEY `Media` (`Media`),
  988. KEY `Container` (`Container`),
  989. KEY `Codec` (`Codec`),
  990. KEY `Resolution` (`Resolution`),
  991. KEY `Version` (`Version`),
  992. KEY `FileCount` (`FileCount`),
  993. KEY `Size` (`Size`),
  994. KEY `Seeders` (`Seeders`),
  995. KEY `Leechers` (`Leechers`),
  996. KEY `last_action` (`last_action`),
  997. KEY `Time` (`Time`),
  998. KEY `FreeTorrent` (`FreeTorrent`)
  999. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1000. CREATE TABLE `torrents_artists` (
  1001. `GroupID` int NOT NULL,
  1002. `ArtistID` int NOT NULL,
  1003. `UserID` int unsigned NOT NULL DEFAULT '0',
  1004. PRIMARY KEY (`GroupID`,`ArtistID`),
  1005. KEY `ArtistID` (`ArtistID`),
  1006. KEY `GroupID` (`GroupID`),
  1007. KEY `UserID` (`UserID`)
  1008. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1009. CREATE TABLE `torrents_bad_files` (
  1010. `TorrentID` int NOT NULL DEFAULT '0',
  1011. `UserID` int NOT NULL DEFAULT '0',
  1012. `TimeAdded` datetime
  1013. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1014. CREATE TABLE `torrents_bad_folders` (
  1015. `TorrentID` int NOT NULL,
  1016. `UserID` int NOT NULL,
  1017. `TimeAdded` datetime,
  1018. PRIMARY KEY (`TorrentID`)
  1019. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1020. CREATE TABLE `torrents_bad_tags` (
  1021. `TorrentID` int NOT NULL DEFAULT '0',
  1022. `UserID` int NOT NULL DEFAULT '0',
  1023. `TimeAdded` datetime,
  1024. KEY `TimeAdded` (`TimeAdded`)
  1025. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1026. CREATE TABLE `torrents_group` (
  1027. `ID` int NOT NULL AUTO_INCREMENT,
  1028. `CategoryID` int DEFAULT NULL,
  1029. `Name` varchar(255) DEFAULT NULL,
  1030. `Title2` varchar(255) DEFAULT NULL,
  1031. `NameJP` varchar(255) DEFAULT NULL,
  1032. `Year` int DEFAULT NULL,
  1033. `Studio` varchar(100) NOT NULL DEFAULT '',
  1034. `Series` varchar(100) NOT NULL DEFAULT '',
  1035. `CatalogueNumber` varchar(50) NOT NULL DEFAULT '',
  1036. `TagList` varchar(500) NOT NULL DEFAULT '',
  1037. `Time` datetime,
  1038. `RevisionID` int DEFAULT NULL,
  1039. `WikiBody` text,
  1040. `WikiImage` varchar(255) NOT NULL,
  1041. PRIMARY KEY (`ID`),
  1042. KEY `CategoryID` (`CategoryID`),
  1043. KEY `Name` (`Name`(255)),
  1044. KEY `Year` (`Year`),
  1045. KEY `Time` (`Time`),
  1046. KEY `RevisionID` (`RevisionID`)
  1047. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1048. CREATE TABLE `torrents_logs_new` (
  1049. `LogID` int NOT NULL AUTO_INCREMENT,
  1050. `TorrentID` int NOT NULL DEFAULT '0',
  1051. `Log` mediumtext,
  1052. `Details` mediumtext,
  1053. `Score` int NOT NULL,
  1054. `Revision` int NOT NULL,
  1055. `Adjusted` enum('1','0') NOT NULL DEFAULT '0',
  1056. `AdjustedBy` int NOT NULL DEFAULT '0',
  1057. `NotEnglish` enum('1','0') NOT NULL DEFAULT '0',
  1058. `AdjustmentReason` text,
  1059. PRIMARY KEY (`LogID`),
  1060. KEY `TorrentID` (`TorrentID`)
  1061. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1062. CREATE TABLE `torrents_peerlists` (
  1063. `TorrentID` int NOT NULL,
  1064. `GroupID` int DEFAULT NULL,
  1065. `Seeders` int DEFAULT NULL,
  1066. `Leechers` int DEFAULT NULL,
  1067. `Snatches` int DEFAULT NULL,
  1068. PRIMARY KEY (`TorrentID`),
  1069. KEY `GroupID` (`GroupID`),
  1070. KEY `Stats` (`TorrentID`,`Seeders`,`Leechers`,`Snatches`)
  1071. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1072. CREATE TABLE `torrents_peerlists_compare` (
  1073. `TorrentID` int NOT NULL,
  1074. `GroupID` int DEFAULT NULL,
  1075. `Seeders` int DEFAULT NULL,
  1076. `Leechers` int DEFAULT NULL,
  1077. `Snatches` int DEFAULT NULL,
  1078. PRIMARY KEY (`TorrentID`),
  1079. KEY `GroupID` (`GroupID`),
  1080. KEY `Stats` (`TorrentID`,`Seeders`,`Leechers`,`Snatches`)
  1081. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1082. CREATE TABLE `torrents_recommended` (
  1083. `GroupID` int NOT NULL,
  1084. `UserID` int NOT NULL,
  1085. `Time` datetime,
  1086. PRIMARY KEY (`GroupID`),
  1087. KEY `Time` (`Time`)
  1088. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1089. CREATE TABLE `torrents_doi` (
  1090. `ID` int NOT NULL AUTO_INCREMENT,
  1091. `TorrentID` int NOT NULL,
  1092. `UserID` int NOT NULL,
  1093. `Time` datetime,
  1094. `URI` varchar(255) NOT NULL,
  1095. PRIMARY KEY (`ID`,`GroupID`,`URI`)
  1096. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1097. CREATE TABLE `torrents_mirrors` (
  1098. `ID` int NOT NULL AUTO_INCREMENT,
  1099. `GroupID` int NOT NULL,
  1100. `UserID` int NOT NULL,
  1101. `Time` datetime,
  1102. `URI` varchar(255) NOT NULL,
  1103. PRIMARY KEY (`ID`,`GroupID`,`URI`)
  1104. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1105. CREATE TABLE `torrents_tags` (
  1106. `TagID` int NOT NULL DEFAULT '0',
  1107. `GroupID` int NOT NULL DEFAULT '0',
  1108. `UserID` int DEFAULT NULL,
  1109. PRIMARY KEY (`TagID`,`GroupID`),
  1110. KEY `TagID` (`TagID`),
  1111. KEY `GroupID` (`GroupID`),
  1112. KEY `UserID` (`UserID`)
  1113. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1114. CREATE TABLE `u2f` (
  1115. `UserID` int NOT NULL,
  1116. `KeyHandle` varchar(255) NOT NULL,
  1117. `PublicKey` varchar(255) NOT NULL,
  1118. `Certificate` text,
  1119. `Counter` int NOT NULL DEFAULT '-1',
  1120. `Valid` enum('0','1') NOT NULL DEFAULT '1',
  1121. PRIMARY KEY (`UserID`,`KeyHandle`)
  1122. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1123. CREATE TABLE `users_badges` (
  1124. `UserID` int NOT NULL,
  1125. `BadgeID` int NOT NULL,
  1126. `Displayed` tinyint DEFAULT '0',
  1127. PRIMARY KEY (`UserID`,`BadgeID`)
  1128. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1129. CREATE TABLE `users_collage_subs` (
  1130. `UserID` int NOT NULL,
  1131. `CollageID` int NOT NULL,
  1132. `LastVisit` datetime DEFAULT NULL,
  1133. PRIMARY KEY (`UserID`,`CollageID`),
  1134. KEY `CollageID` (`CollageID`)
  1135. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1136. CREATE TABLE `users_comments_last_read` (
  1137. `UserID` int NOT NULL,
  1138. `Page` enum('artist','collages','requests','torrents') NOT NULL,
  1139. `PageID` int NOT NULL,
  1140. `PostID` int NOT NULL,
  1141. PRIMARY KEY (`UserID`,`Page`,`PageID`),
  1142. KEY `Page` (`Page`,`PageID`)
  1143. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1144. CREATE TABLE `users_donor_ranks` (
  1145. `UserID` int NOT NULL DEFAULT '0',
  1146. `Rank` tinyint NOT NULL DEFAULT '0',
  1147. `DonationTime` datetime DEFAULT NULL,
  1148. `Hidden` tinyint NOT NULL DEFAULT '0',
  1149. `TotalRank` int NOT NULL DEFAULT '0',
  1150. `SpecialRank` tinyint DEFAULT '0',
  1151. `InvitesRecievedRank` tinyint DEFAULT '0',
  1152. `RankExpirationTime` datetime DEFAULT NULL,
  1153. PRIMARY KEY (`UserID`),
  1154. KEY `DonationTime` (`DonationTime`),
  1155. KEY `SpecialRank` (`SpecialRank`),
  1156. KEY `Rank` (`Rank`),
  1157. KEY `TotalRank` (`TotalRank`)
  1158. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1159. CREATE TABLE `users_downloads` (
  1160. `UserID` int NOT NULL,
  1161. `TorrentID` int NOT NULL,
  1162. `Time` datetime,
  1163. PRIMARY KEY (`UserID`,`TorrentID`,`Time`),
  1164. KEY `TorrentID` (`TorrentID`),
  1165. KEY `UserID` (`UserID`)
  1166. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1167. CREATE TABLE `users_dupes` (
  1168. `GroupID` int unsigned NOT NULL,
  1169. `UserID` int unsigned NOT NULL,
  1170. UNIQUE KEY `UserID` (`UserID`),
  1171. KEY `GroupID` (`GroupID`),
  1172. CONSTRAINT `users_dupes_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users_main` (`ID`) ON DELETE CASCADE,
  1173. CONSTRAINT `users_dupes_ibfk_2` FOREIGN KEY (`GroupID`) REFERENCES `dupe_groups` (`ID`) ON DELETE CASCADE
  1174. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1175. CREATE TABLE `users_enable_recommendations` (
  1176. `ID` int NOT NULL,
  1177. `Enable` tinyint DEFAULT NULL,
  1178. PRIMARY KEY (`ID`),
  1179. KEY `Enable` (`Enable`)
  1180. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1181. CREATE TABLE `users_enable_requests` (
  1182. `ID` int NOT NULL AUTO_INCREMENT,
  1183. `UserID` int unsigned NOT NULL,
  1184. `Email` varchar(255) NOT NULL,
  1185. `IP` varchar(255) NOT NULL DEFAULT 'mIbUEUEmV93bF6C5i6cITAlcw3H7TKcaPzZZIMIZQNQ=',
  1186. `UserAgent` text,
  1187. `Timestamp` datetime,
  1188. `HandledTimestamp` datetime DEFAULT NULL,
  1189. `Token` char(32) DEFAULT NULL,
  1190. `CheckedBy` int unsigned DEFAULT NULL,
  1191. `Outcome` tinyint DEFAULT NULL COMMENT '1 for approved, 2 for denied, 3 for discarded',
  1192. PRIMARY KEY (`ID`),
  1193. KEY `UserId` (`UserID`),
  1194. KEY `CheckedBy` (`CheckedBy`),
  1195. CONSTRAINT `users_enable_requests_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users_main` (`ID`),
  1196. CONSTRAINT `users_enable_requests_ibfk_2` FOREIGN KEY (`CheckedBy`) REFERENCES `users_main` (`ID`)
  1197. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1198. CREATE TABLE `users_freeleeches` (
  1199. `UserID` int NOT NULL,
  1200. `TorrentID` int NOT NULL,
  1201. `Time` datetime,
  1202. `Expired` tinyint NOT NULL DEFAULT '0',
  1203. `Downloaded` bigint NOT NULL DEFAULT '0',
  1204. `Uses` int NOT NULL DEFAULT '1',
  1205. PRIMARY KEY (`UserID`,`TorrentID`),
  1206. KEY `Time` (`Time`),
  1207. KEY `Expired_Time` (`Expired`,`Time`)
  1208. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1209. CREATE TABLE `users_history_emails` (
  1210. `UserID` int NOT NULL,
  1211. `Email` varchar(255) DEFAULT NULL,
  1212. `Time` datetime DEFAULT NULL,
  1213. `IP` varchar(90) DEFAULT NULL,
  1214. KEY `UserID` (`UserID`)
  1215. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1216. CREATE TABLE `users_history_ips` (
  1217. `UserID` int NOT NULL,
  1218. `IP` varchar(90) NOT NULL DEFAULT '0.0.0.0',
  1219. `StartTime` datetime,
  1220. `EndTime` datetime DEFAULT NULL,
  1221. PRIMARY KEY (`UserID`,`IP`,`StartTime`),
  1222. KEY `UserID` (`UserID`),
  1223. KEY `IP` (`IP`),
  1224. KEY `StartTime` (`StartTime`),
  1225. KEY `EndTime` (`EndTime`)
  1226. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1227. CREATE TABLE `users_history_passkeys` (
  1228. `UserID` int NOT NULL,
  1229. `OldPassKey` varchar(32) DEFAULT NULL,
  1230. `NewPassKey` varchar(32) DEFAULT NULL,
  1231. `ChangeTime` datetime DEFAULT NULL,
  1232. `ChangerIP` varchar(90) DEFAULT NULL
  1233. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1234. CREATE TABLE `users_history_passwords` (
  1235. `UserID` int NOT NULL,
  1236. `ChangeTime` datetime DEFAULT NULL,
  1237. `ChangerIP` varchar(90) DEFAULT NULL,
  1238. KEY `User_Time` (`UserID`,`ChangeTime`)
  1239. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1240. CREATE TABLE `users_info` (
  1241. `UserID` int unsigned NOT NULL,
  1242. `StyleID` int unsigned NOT NULL,
  1243. `StyleURL` varchar(255) DEFAULT NULL,
  1244. `Info` text,
  1245. `Avatar` varchar(255),
  1246. `AdminComment` text,
  1247. `SiteOptions` text,
  1248. `ViewAvatars` enum('0','1') NOT NULL DEFAULT '1',
  1249. `Donor` enum('0','1') NOT NULL DEFAULT '0',
  1250. `Artist` enum('0','1') NOT NULL DEFAULT '0',
  1251. `Warned` datetime,
  1252. `SupportFor` varchar(255),
  1253. `TorrentGrouping` enum('0','1','2') NOT NULL COMMENT '0=Open,1=Closed,2=Off',
  1254. `ShowTags` enum('0','1') NOT NULL DEFAULT '1',
  1255. `NotifyOnQuote` enum('0','1','2') NOT NULL DEFAULT '0',
  1256. `AuthKey` varchar(32) NOT NULL DEFAULT '',
  1257. `ResetKey` varchar(32) NOT NULL DEFAULT '',
  1258. `ResetExpires` datetime,
  1259. `JoinDate` datetime,
  1260. `Inviter` int DEFAULT NULL,
  1261. `WarnedTimes` int NOT NULL DEFAULT '0',
  1262. `DisableAvatar` enum('0','1') NOT NULL DEFAULT '0',
  1263. `DisableInvites` enum('0','1') NOT NULL DEFAULT '0',
  1264. `DisablePosting` enum('0','1') NOT NULL DEFAULT '0',
  1265. `DisableForums` enum('0','1') NOT NULL DEFAULT '0',
  1266. `DisableIRC` enum('0','1') DEFAULT '0',
  1267. `DisableTagging` enum('0','1') NOT NULL DEFAULT '0',
  1268. `DisableUpload` enum('0','1') NOT NULL DEFAULT '0',
  1269. `DisableWiki` enum('0','1') NOT NULL DEFAULT '0',
  1270. `DisablePM` enum('0','1') NOT NULL DEFAULT '0',
  1271. `DisablePoints` enum('0','1') NOT NULL DEFAULT '0',
  1272. `DisablePromotion` enum('0','1') NOT NULL DEFAULT '0',
  1273. `DisableRequests` enum('0','1') NOT NULL DEFAULT '0',
  1274. `RatioWatchEnds` datetime,
  1275. `RatioWatchDownload` bigint unsigned NOT NULL DEFAULT '0',
  1276. `RatioWatchTimes` tinyint unsigned NOT NULL DEFAULT '0',
  1277. `BanDate` datetime,
  1278. `BanReason` enum('0','1','2','3','4') NOT NULL DEFAULT '0',
  1279. `CatchupTime` datetime DEFAULT NULL,
  1280. `LastReadNews` int NOT NULL DEFAULT '0',
  1281. `HideCountryChanges` enum('0','1') NOT NULL DEFAULT '0',
  1282. `RestrictedForums` varchar(150) NOT NULL DEFAULT '',
  1283. `PermittedForums` varchar(150) NOT NULL DEFAULT '',
  1284. `UnseededAlerts` enum('0','1') NOT NULL DEFAULT '0',
  1285. `LastReadBlog` int NOT NULL DEFAULT '0',
  1286. `InfoTitle` varchar(255) NOT NULL DEFAULT '',
  1287. UNIQUE KEY `UserID` (`UserID`),
  1288. KEY `SupportFor` (`SupportFor`),
  1289. KEY `DisableInvites` (`DisableInvites`),
  1290. KEY `Donor` (`Donor`),
  1291. KEY `Warned` (`Warned`),
  1292. KEY `JoinDate` (`JoinDate`),
  1293. KEY `Inviter` (`Inviter`),
  1294. KEY `RatioWatchEnds` (`RatioWatchEnds`),
  1295. KEY `RatioWatchDownload` (`RatioWatchDownload`),
  1296. KEY `AuthKey` (`AuthKey`),
  1297. KEY `ResetKey` (`ResetKey`)
  1298. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1299. CREATE TABLE `users_levels` (
  1300. `UserID` int unsigned NOT NULL,
  1301. `PermissionID` int unsigned NOT NULL,
  1302. PRIMARY KEY (`UserID`,`PermissionID`),
  1303. KEY `PermissionID` (`PermissionID`)
  1304. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1305. CREATE TABLE `users_main` (
  1306. `ID` int unsigned NOT NULL AUTO_INCREMENT,
  1307. `Username` varchar(25) NOT NULL DEFAULT '',
  1308. `Email` varchar(255) NOT NULL DEFAULT '',
  1309. `PassHash` varchar(60) NOT NULL DEFAULT '',
  1310. `TwoFactor` varchar(255) DEFAULT NULL,
  1311. `PublicKey` text,
  1312. `IRCKey` char(32) DEFAULT NULL,
  1313. `LastLogin` datetime,
  1314. `LastAccess` datetime,
  1315. `IP` varchar(90) NOT NULL DEFAULT '0.0.0.0',
  1316. `Class` tinyint NOT NULL DEFAULT '5',
  1317. `Uploaded` bigint unsigned NOT NULL DEFAULT '0',
  1318. `Downloaded` bigint unsigned NOT NULL DEFAULT '0',
  1319. `Title` text,
  1320. `Enabled` enum('0','1','2') NOT NULL DEFAULT '0',
  1321. `Paranoia` text,
  1322. `Visible` enum('1','0') NOT NULL DEFAULT '1',
  1323. `Invites` int unsigned NOT NULL DEFAULT '0',
  1324. `PermissionID` int unsigned NOT NULL DEFAULT '0',
  1325. `CustomPermissions` text,
  1326. `can_leech` tinyint NOT NULL DEFAULT '1',
  1327. `torrent_pass` char(32) NOT NULL DEFAULT '',
  1328. `RequiredRatio` double(10,8) NOT NULL DEFAULT '0.00000000',
  1329. `RequiredRatioWork` double(10,8) NOT NULL DEFAULT '0.00000000',
  1330. `FLTokens` int NOT NULL DEFAULT '0',
  1331. `BonusPoints` int unsigned NOT NULL DEFAULT '0',
  1332. `IRCLines` int unsigned NOT NULL DEFAULT '0',
  1333. `HnR` int NOT NULL DEFAULT '0',
  1334. PRIMARY KEY (`ID`),
  1335. UNIQUE KEY `Username` (`Username`),
  1336. KEY `Email` (`Email`),
  1337. KEY `PassHash` (`PassHash`),
  1338. KEY `LastAccess` (`LastAccess`),
  1339. KEY `IP` (`IP`),
  1340. KEY `Class` (`Class`),
  1341. KEY `Uploaded` (`Uploaded`),
  1342. KEY `Downloaded` (`Downloaded`),
  1343. KEY `Enabled` (`Enabled`),
  1344. KEY `Invites` (`Invites`),
  1345. KEY `torrent_pass` (`torrent_pass`),
  1346. KEY `RequiredRatio` (`RequiredRatio`),
  1347. KEY `PermissionID` (`PermissionID`)
  1348. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1349. CREATE TABLE `users_notifications_settings` (
  1350. `UserID` int NOT NULL DEFAULT '0',
  1351. `Inbox` tinyint DEFAULT '1',
  1352. `StaffPM` tinyint DEFAULT '1',
  1353. `News` tinyint DEFAULT '1',
  1354. `Blog` tinyint DEFAULT '1',
  1355. `Torrents` tinyint DEFAULT '1',
  1356. `Collages` tinyint DEFAULT '1',
  1357. `Quotes` tinyint DEFAULT '1',
  1358. `Subscriptions` tinyint DEFAULT '1',
  1359. `SiteAlerts` tinyint DEFAULT '1',
  1360. `RequestAlerts` tinyint DEFAULT '1',
  1361. `CollageAlerts` tinyint DEFAULT '1',
  1362. `TorrentAlerts` tinyint DEFAULT '1',
  1363. `ForumAlerts` tinyint DEFAULT '1',
  1364. PRIMARY KEY (`UserID`)
  1365. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1366. CREATE TABLE `users_notify_filters` (
  1367. `ID` int NOT NULL AUTO_INCREMENT,
  1368. `UserID` int NOT NULL,
  1369. `Label` varchar(128) NOT NULL DEFAULT '',
  1370. `Artists` mediumtext,
  1371. `RecordLabels` mediumtext,
  1372. `Users` mediumtext,
  1373. `Tags` varchar(500) NOT NULL DEFAULT '',
  1374. `NotTags` varchar(500) NOT NULL DEFAULT '',
  1375. `Categories` varchar(500) NOT NULL DEFAULT '',
  1376. `Formats` varchar(500) NOT NULL DEFAULT '',
  1377. `Encodings` varchar(500) NOT NULL DEFAULT '',
  1378. `Media` varchar(500) NOT NULL DEFAULT '',
  1379. `FromYear` int NOT NULL DEFAULT '0',
  1380. `ToYear` int NOT NULL DEFAULT '0',
  1381. `ExcludeVA` enum('1','0') NOT NULL DEFAULT '0',
  1382. `NewGroupsOnly` enum('1','0') NOT NULL DEFAULT '0',
  1383. `ReleaseTypes` varchar(500) NOT NULL DEFAULT '',
  1384. PRIMARY KEY (`ID`),
  1385. KEY `UserID` (`UserID`),
  1386. KEY `FromYear` (`FromYear`),
  1387. KEY `ToYear` (`ToYear`)
  1388. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1389. CREATE TABLE `users_notify_quoted` (
  1390. `UserID` int NOT NULL,
  1391. `QuoterID` int NOT NULL,
  1392. `Page` enum('forums','artist','collages','requests','torrents') NOT NULL,
  1393. `PageID` int NOT NULL,
  1394. `PostID` int NOT NULL,
  1395. `UnRead` tinyint NOT NULL DEFAULT '1',
  1396. `Date` datetime,
  1397. PRIMARY KEY (`UserID`,`Page`,`PostID`)
  1398. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1399. CREATE TABLE `users_notify_torrents` (
  1400. `UserID` int NOT NULL,
  1401. `FilterID` int NOT NULL,
  1402. `GroupID` int NOT NULL,
  1403. `TorrentID` int NOT NULL,
  1404. `UnRead` tinyint NOT NULL DEFAULT '1',
  1405. PRIMARY KEY (`UserID`,`TorrentID`),
  1406. KEY `TorrentID` (`TorrentID`),
  1407. KEY `UserID_Unread` (`UserID`,`UnRead`)
  1408. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1409. CREATE TABLE `users_points` (
  1410. `UserID` int NOT NULL,
  1411. `GroupID` int NOT NULL,
  1412. `Points` tinyint NOT NULL DEFAULT '1',
  1413. PRIMARY KEY (`UserID`,`GroupID`),
  1414. KEY `UserID` (`UserID`),
  1415. KEY `GroupID` (`GroupID`)
  1416. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1417. CREATE TABLE `users_points_requests` (
  1418. `UserID` int NOT NULL,
  1419. `RequestID` int NOT NULL,
  1420. `Points` tinyint NOT NULL DEFAULT '1',
  1421. PRIMARY KEY (`RequestID`),
  1422. KEY `UserID` (`UserID`),
  1423. KEY `RequestID` (`RequestID`)
  1424. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1425. CREATE TABLE `users_seedtime` (
  1426. `UserID` int unsigned NOT NULL,
  1427. `TorrentID` int unsigned NOT NULL,
  1428. `SeedTime` int unsigned NOT NULL DEFAULT '0',
  1429. `Uploaded` bigint NOT NULL DEFAULT '0',
  1430. `LastUpdate` datetime NOT NULL,
  1431. `Downloaded` bigint unsigned NOT NULL DEFAULT '0',
  1432. PRIMARY KEY (`UserID`,`TorrentID`)
  1433. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1434. CREATE TABLE `users_sessions` (
  1435. `UserID` int NOT NULL,
  1436. `SessionID` char(64) NOT NULL,
  1437. `KeepLogged` enum('0','1') NOT NULL DEFAULT '0',
  1438. `Browser` varchar(40) DEFAULT NULL,
  1439. `OperatingSystem` varchar(13) DEFAULT NULL,
  1440. `IP` varchar(90) NOT NULL,
  1441. `LastUpdate` datetime,
  1442. `Active` tinyint NOT NULL DEFAULT '1',
  1443. `FullUA` text,
  1444. PRIMARY KEY (`UserID`,`SessionID`),
  1445. KEY `UserID` (`UserID`),
  1446. KEY `LastUpdate` (`LastUpdate`),
  1447. KEY `Active` (`Active`),
  1448. KEY `ActiveAgeKeep` (`Active`,`LastUpdate`,`KeepLogged`)
  1449. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1450. CREATE TABLE `users_subscriptions` (
  1451. `UserID` int NOT NULL,
  1452. `TopicID` int NOT NULL,
  1453. PRIMARY KEY (`UserID`,`TopicID`)
  1454. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1455. CREATE TABLE `users_subscriptions_comments` (
  1456. `UserID` int NOT NULL,
  1457. `Page` enum('artist','collages','requests','torrents') NOT NULL,
  1458. `PageID` int NOT NULL,
  1459. PRIMARY KEY (`UserID`,`Page`,`PageID`)
  1460. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1461. CREATE TABLE `users_torrent_history` (
  1462. `UserID` int unsigned NOT NULL,
  1463. `NumTorrents` int unsigned NOT NULL,
  1464. `Date` int unsigned NOT NULL,
  1465. `Time` int unsigned NOT NULL DEFAULT '0',
  1466. `LastTime` int unsigned NOT NULL DEFAULT '0',
  1467. `Finished` enum('1','0') NOT NULL DEFAULT '1',
  1468. `Weight` bigint unsigned NOT NULL DEFAULT '0',
  1469. PRIMARY KEY (`UserID`,`NumTorrents`,`Date`),
  1470. KEY `Finished` (`Finished`),
  1471. KEY `Date` (`Date`)
  1472. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1473. CREATE TABLE `users_torrent_history_snatch` (
  1474. `UserID` int unsigned NOT NULL,
  1475. `NumSnatches` int unsigned NOT NULL DEFAULT '0',
  1476. PRIMARY KEY (`UserID`),
  1477. KEY `NumSnatches` (`NumSnatches`)
  1478. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1479. CREATE TABLE `users_torrent_history_temp` (
  1480. `UserID` int unsigned NOT NULL,
  1481. `NumTorrents` int unsigned NOT NULL DEFAULT '0',
  1482. `SumTime` bigint unsigned NOT NULL DEFAULT '0',
  1483. `SeedingAvg` int unsigned NOT NULL DEFAULT '0',
  1484. PRIMARY KEY (`UserID`)
  1485. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1486. CREATE TABLE `users_warnings_forums` (
  1487. `UserID` int unsigned NOT NULL,
  1488. `Comment` text,
  1489. PRIMARY KEY (`UserID`)
  1490. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1491. CREATE TABLE `wiki_aliases` (
  1492. `Alias` varchar(50) NOT NULL,
  1493. `UserID` int NOT NULL,
  1494. `ArticleID` int DEFAULT NULL,
  1495. PRIMARY KEY (`Alias`)
  1496. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1497. CREATE TABLE `wiki_articles` (
  1498. `ID` int NOT NULL AUTO_INCREMENT,
  1499. `Revision` int NOT NULL DEFAULT '1',
  1500. `Title` varchar(100) DEFAULT NULL,
  1501. `Body` mediumtext,
  1502. `MinClassRead` int DEFAULT NULL,
  1503. `MinClassEdit` int DEFAULT NULL,
  1504. `Date` datetime DEFAULT NULL,
  1505. `Author` int DEFAULT NULL,
  1506. PRIMARY KEY (`ID`)
  1507. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1508. CREATE TABLE `wiki_artists` (
  1509. `RevisionID` int NOT NULL AUTO_INCREMENT,
  1510. `PageID` int NOT NULL DEFAULT '0',
  1511. `Body` text,
  1512. `UserID` int NOT NULL DEFAULT '0',
  1513. `Summary` varchar(100) DEFAULT NULL,
  1514. `Time` datetime,
  1515. `Image` varchar(255) DEFAULT NULL,
  1516. PRIMARY KEY (`RevisionID`),
  1517. KEY `PageID` (`PageID`),
  1518. KEY `UserID` (`UserID`),
  1519. KEY `Time` (`Time`)
  1520. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1521. CREATE TABLE `wiki_revisions` (
  1522. `ID` int NOT NULL,
  1523. `Revision` int NOT NULL,
  1524. `Title` varchar(100) DEFAULT NULL,
  1525. `Body` mediumtext,
  1526. `Date` datetime DEFAULT NULL,
  1527. `Author` int DEFAULT NULL,
  1528. KEY `ID_Revision` (`ID`,`Revision`)
  1529. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1530. CREATE TABLE `wiki_torrents` (
  1531. `RevisionID` int NOT NULL AUTO_INCREMENT,
  1532. `PageID` int NOT NULL DEFAULT '0',
  1533. `Body` text,
  1534. `UserID` int NOT NULL DEFAULT '0',
  1535. `Summary` varchar(100) DEFAULT NULL,
  1536. `Time` datetime,
  1537. `Image` varchar(255) DEFAULT NULL,
  1538. PRIMARY KEY (`RevisionID`),
  1539. KEY `PageID` (`PageID`),
  1540. KEY `UserID` (`UserID`),
  1541. KEY `Time` (`Time`)
  1542. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1543. CREATE TABLE `xbt_client_whitelist` (
  1544. `id` int unsigned NOT NULL AUTO_INCREMENT,
  1545. `peer_id` varchar(25) DEFAULT NULL,
  1546. `vstring` varchar(255) DEFAULT '',
  1547. PRIMARY KEY (`id`),
  1548. UNIQUE KEY `peer_id` (`peer_id`)
  1549. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1550. CREATE TABLE `xbt_files_users` (
  1551. `uid` int NOT NULL,
  1552. `active` tinyint NOT NULL DEFAULT '0',
  1553. `announced` int NOT NULL,
  1554. `completed` tinyint NOT NULL DEFAULT '0',
  1555. `downloaded` bigint NOT NULL DEFAULT '0',
  1556. `remaining` bigint NOT NULL DEFAULT '0',
  1557. `uploaded` bigint NOT NULL DEFAULT '0',
  1558. `upspeed` int unsigned NOT NULL DEFAULT '0',
  1559. `downspeed` int unsigned NOT NULL DEFAULT '0',
  1560. `corrupt` bigint NOT NULL DEFAULT '0',
  1561. `timespent` int unsigned NOT NULL,
  1562. `useragent` varchar(51) NOT NULL DEFAULT '',
  1563. `connectable` tinyint NOT NULL DEFAULT '1',
  1564. `peer_id` binary(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  1565. `fid` int NOT NULL,
  1566. `mtime` int NOT NULL,
  1567. `ip` varchar(15) NOT NULL DEFAULT '', -- Max IPv4 address length
  1568. `seeder` tinyint NOT NULL DEFAULT '0',
  1569. PRIMARY KEY (`peer_id`,`fid`,`uid`),
  1570. KEY `remaining_idx` (`remaining`),
  1571. KEY `fid_idx` (`fid`),
  1572. KEY `mtime_idx` (`mtime`),
  1573. KEY `uid_active` (`uid`,`active`)
  1574. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1575. CREATE TABLE `xbt_snatched` (
  1576. `uid` int NOT NULL DEFAULT '0',
  1577. `tstamp` int NOT NULL,
  1578. `fid` int NOT NULL,
  1579. `IP` varchar(15) NOT NULL, -- Max IPv4 address length
  1580. `seedtime` int NOT NULL DEFAULT '0',
  1581. KEY `fid` (`fid`),
  1582. KEY `tstamp` (`tstamp`),
  1583. KEY `uid_tstamp` (`uid`,`tstamp`)
  1584. ) ENGINE=InnoDB CHARSET=utf8mb4;
  1585. -- Okay, that's all for the schema structure
  1586. -- Now we have the default values to initialize the DB with
  1587. SET FOREIGN_KEY_CHECKS = 1;
  1588. INSERT INTO `permissions` (`ID`, `Level`, `Name`, `Values`, `DisplayStaff`) VALUES
  1589. (15, 1000, 'Sysop', 'a:100:{s:10:\"site_leech\";i:1;s:11:\"site_upload\";i:1;s:9:\"site_vote\";i:1;s:20:\"site_submit_requests\";i:1;s:20:\"site_advanced_search\";i:1;s:10:\"site_top10\";i:1;s:19:\"site_advanced_top10\";i:1;s:16:\"site_album_votes\";i:1;s:20:\"site_torrents_notify\";i:1;s:20:\"site_collages_create\";i:1;s:20:\"site_collages_manage\";i:1;s:20:\"site_collages_delete\";i:1;s:23:\"site_collages_subscribe\";i:1;s:22:\"site_collages_personal\";i:1;s:28:\"site_collages_renamepersonal\";i:1;s:19:\"site_make_bookmarks\";i:1;s:14:\"site_edit_wiki\";i:1;s:22:\"site_can_invite_always\";i:1;s:27:\"site_send_unlimited_invites\";i:1;s:22:\"site_moderate_requests\";i:1;s:18:\"site_delete_artist\";i:1;s:20:\"site_moderate_forums\";i:1;s:17:\"site_admin_forums\";i:1;s:23:\"site_forums_double_post\";i:1;s:14:\"site_view_flow\";i:1;s:18:\"site_view_full_log\";i:1;s:28:\"site_view_torrent_snatchlist\";i:1;s:18:\"site_recommend_own\";i:1;s:27:\"site_manage_recommendations\";i:1;s:15:\"site_delete_tag\";i:1;s:23:\"site_disable_ip_history\";i:1;s:14:\"zip_downloader\";i:1;s:10:\"site_debug\";i:1;s:17:\"site_proxy_images\";i:1;s:16:\"site_search_many\";i:1;s:20:\"users_edit_usernames\";i:1;s:16:\"users_edit_ratio\";i:1;s:20:\"users_edit_own_ratio\";i:1;s:17:\"users_edit_titles\";i:1;s:18:\"users_edit_avatars\";i:1;s:18:\"users_edit_invites\";i:1;s:22:\"users_edit_watch_hours\";i:1;s:21:\"users_edit_reset_keys\";i:1;s:19:\"users_edit_profiles\";i:1;s:18:\"users_view_friends\";i:1;s:20:\"users_reset_own_keys\";i:1;s:19:\"users_edit_password\";i:1;s:19:\"users_promote_below\";i:1;s:16:\"users_promote_to\";i:1;s:16:\"users_give_donor\";i:1;s:10:\"users_warn\";i:1;s:19:\"users_disable_users\";i:1;s:19:\"users_disable_posts\";i:1;s:17:\"users_disable_any\";i:1;s:18:\"users_delete_users\";i:1;s:18:\"users_view_invites\";i:1;s:20:\"users_view_seedleech\";i:1;s:19:\"users_view_uploaded\";i:1;s:15:\"users_view_keys\";i:1;s:14:\"users_view_ips\";i:1;s:16:\"users_view_email\";i:1;s:18:\"users_invite_notes\";i:1;s:23:\"users_override_paranoia\";i:1;s:12:\"users_logout\";i:1;s:20:\"users_make_invisible\";i:1;s:9:\"users_mod\";i:1;s:13:\"torrents_edit\";i:1;s:15:\"torrents_delete\";i:1;s:20:\"torrents_delete_fast\";i:1;s:18:\"torrents_freeleech\";i:1;s:20:\"torrents_search_fast\";i:1;i:1;s:19:\"torrents_fix_ghosts\";i:1;s:17:\"admin_manage_news\";i:1;s:17:\"admin_manage_blog\";i:1;s:18:\"admin_manage_polls\";i:1;s:19:\"admin_manage_forums\";i:1;s:16:\"admin_manage_fls\";i:1;s:13:\"admin_reports\";i:1;s:26:\"admin_advanced_user_search\";i:1;s:18:\"admin_create_users\";i:1;s:15:\"admin_donor_log\";i:1;s:19:\"admin_manage_ipbans\";i:1;i:1;s:17:\"admin_clear_cache\";i:1;s:15:\"admin_whitelist\";i:1;s:24:\"admin_manage_permissions\";i:1;s:14:\"admin_schedule\";i:1;s:17:\"admin_login_watch\";i:1;s:17:\"admin_manage_wiki\";i:1;i:1;s:21:\"site_collages_recover\";i:1;s:19:\"torrents_add_artist\";i:1;s:13:\"edit_unknowns\";i:1;s:19:\"forums_polls_create\";i:1;s:21:\"forums_polls_moderate\";i:1;s:12:\"project_team\";i:1;s:25:\"torrents_edit_vanityhouse\";i:1;s:23:\"artist_edit_vanityhouse\";i:1;s:21:\"site_tag_aliases_read\";i:1;}', '1'),
  1590. (11, 800, 'Moderator', 'a:89:{s:26:\"admin_advanced_user_search\";i:1;s:17:\"admin_clear_cache\";i:1;s:18:\"admin_create_users\";i:1;i:1;s:15:\"admin_donor_log\";i:1;s:17:\"admin_login_watch\";i:1;s:17:\"admin_manage_blog\";i:1;s:19:\"admin_manage_ipbans\";i:1;s:17:\"admin_manage_news\";i:1;s:18:\"admin_manage_polls\";i:1;s:17:\"admin_manage_wiki\";i:1;s:13:\"admin_reports\";i:1;s:23:\"artist_edit_vanityhouse\";i:1;s:13:\"edit_unknowns\";i:1;s:19:\"forums_polls_create\";i:1;s:21:\"forums_polls_moderate\";i:1;s:12:\"project_team\";i:1;s:17:\"site_admin_forums\";i:1;s:20:\"site_advanced_search\";i:1;s:19:\"site_advanced_top10\";i:1;s:16:\"site_album_votes\";i:1;s:22:\"site_can_invite_always\";i:1;s:20:\"site_collages_create\";i:1;s:20:\"site_collages_delete\";i:1;s:20:\"site_collages_manage\";i:1;s:22:\"site_collages_personal\";i:1;s:21:\"site_collages_recover\";i:1;s:28:\"site_collages_renamepersonal\";i:1;s:23:\"site_collages_subscribe\";i:1;s:18:\"site_delete_artist\";i:1;s:15:\"site_delete_tag\";i:1;s:23:\"site_disable_ip_history\";i:1;s:14:\"site_edit_wiki\";i:1;s:23:\"site_forums_double_post\";i:1;s:10:\"site_leech\";i:1;s:19:\"site_make_bookmarks\";i:1;s:27:\"site_manage_recommendations\";i:1;s:20:\"site_moderate_forums\";i:1;s:22:\"site_moderate_requests\";i:1;s:17:\"site_proxy_images\";i:1;s:18:\"site_recommend_own\";i:1;s:16:\"site_search_many\";i:1;s:27:\"site_send_unlimited_invites\";i:1;s:20:\"site_submit_requests\";i:1;s:21:\"site_tag_aliases_read\";i:1;s:10:\"site_top10\";i:1;s:20:\"site_torrents_notify\";i:1;s:11:\"site_upload\";i:1;s:14:\"site_view_flow\";i:1;s:18:\"site_view_full_log\";i:1;s:28:\"site_view_torrent_snatchlist\";i:1;s:9:\"site_vote\";i:1;s:19:\"torrents_add_artist\";i:1;s:15:\"torrents_delete\";i:1;s:20:\"torrents_delete_fast\";i:1;s:13:\"torrents_edit\";i:1;s:25:\"torrents_edit_vanityhouse\";i:1;s:19:\"torrents_fix_ghosts\";i:1;s:18:\"torrents_freeleech\";i:1;i:1;s:20:\"torrents_search_fast\";i:1;s:18:\"users_delete_users\";i:1;s:17:\"users_disable_any\";i:1;s:19:\"users_disable_posts\";i:1;s:19:\"users_disable_users\";i:1;s:18:\"users_edit_avatars\";i:1;s:18:\"users_edit_invites\";i:1;s:20:\"users_edit_own_ratio\";i:1;s:19:\"users_edit_password\";i:1;s:19:\"users_edit_profiles\";i:1;s:16:\"users_edit_ratio\";i:1;s:21:\"users_edit_reset_keys\";i:1;s:17:\"users_edit_titles\";i:1;s:16:\"users_give_donor\";i:1;s:12:\"users_logout\";i:1;s:20:\"users_make_invisible\";i:1;s:9:\"users_mod\";i:1;s:23:\"users_override_paranoia\";i:1;s:19:\"users_promote_below\";i:1;s:20:\"users_reset_own_keys\";i:1;s:10:\"users_warn\";i:1;s:16:\"users_view_email\";i:1;s:18:\"users_view_friends\";i:1;s:18:\"users_view_invites\";i:1;s:14:\"users_view_ips\";i:1;s:15:\"users_view_keys\";i:1;s:20:\"users_view_seedleech\";i:1;s:19:\"users_view_uploaded\";i:1;s:14:\"zip_downloader\";i:1;}', '1'),
  1591. (2, 100, 'User', 'a:7:{s:10:\"site_leech\";i:1;s:11:\"site_upload\";i:1;s:9:\"site_vote\";i:1;s:20:\"site_advanced_search\";i:1;s:10:\"site_top10\";i:1;s:14:\"site_edit_wiki\";i:1;s:19:\"torrents_add_artist\";i:1;}', '0'),
  1592. (3, 150, 'Member', 'a:10:{s:10:\"site_leech\";i:1;s:11:\"site_upload\";i:1;s:9:\"site_vote\";i:1;s:20:\"site_submit_requests\";i:1;s:20:\"site_advanced_search\";i:1;s:10:\"site_top10\";i:1;s:20:\"site_collages_manage\";i:1;s:19:\"site_make_bookmarks\";i:1;s:14:\"site_edit_wiki\";i:1;s:19:\"torrents_add_artist\";i:1;}', '0'),
  1593. (4, 200, 'Power User', 'a:14:{s:10:\"site_leech\";i:1;s:11:\"site_upload\";i:1;s:9:\"site_vote\";i:1;s:20:\"site_submit_requests\";i:1;s:20:\"site_advanced_search\";i:1;s:10:\"site_top10\";i:1;s:20:\"site_torrents_notify\";i:1;s:20:\"site_collages_create\";i:1;s:20:\"site_collages_manage\";i:1;s:19:\"site_make_bookmarks\";i:1;s:14:\"site_edit_wiki\";i:1;s:14:\"zip_downloader\";i:1;s:19:\"forums_polls_create\";i:1;s:19:\"torrents_add_artist\";i:1;} ', '0'),
  1594. (5, 250, 'Elite', 'a:18:{s:10:\"site_leech\";i:1;s:11:\"site_upload\";i:1;s:9:\"site_vote\";i:1;s:20:\"site_submit_requests\";i:1;s:20:\"site_advanced_search\";i:1;s:10:\"site_top10\";i:1;s:20:\"site_torrents_notify\";i:1;s:20:\"site_collages_create\";i:1;s:20:\"site_collages_manage\";i:1;s:19:\"site_advanced_top10\";i:1;s:19:\"site_make_bookmarks\";i:1;s:14:\"site_edit_wiki\";i:1;s:15:\"site_delete_tag\";i:1;s:14:\"zip_downloader\";i:1;s:19:\"forums_polls_create\";i:1;s:13:\"torrents_edit\";i:1;s:19:\"torrents_add_artist\";i:1;s:17:\"admin_clear_cache\";i:1;}', '0'),
  1595. (20, 202, 'Donor', 'a:9:{s:9:\"site_vote\";i:1;s:20:\"site_submit_requests\";i:1;s:20:\"site_advanced_search\";i:1;s:10:\"site_top10\";i:1;s:20:\"site_torrents_notify\";i:1;s:20:\"site_collages_create\";i:1;s:20:\"site_collages_manage\";i:1;s:14:\"zip_downloader\";i:1;s:19:\"forums_polls_create\";i:1;}', '0'),
  1596. (19, 201, 'Artist', 'a:9:{s:10:\"site_leech\";s:1:\"1\";s:11:\"site_upload\";s:1:\"1\";s:9:\"site_vote\";s:1:\"1\";s:20:\"site_submit_requests\";s:1:\"1\";s:20:\"site_advanced_search\";s:1:\"1\";s:10:\"site_top10\";s:1:\"1\";s:19:\"site_make_bookmarks\";s:1:\"1\";s:14:\"site_edit_wiki\";s:1:\"1\";s:18:\"site_recommend_own\";s:1:\"1\";}', '0');
  1597. INSERT INTO `stylesheets` (`ID`, `Name`, `Description`, `Default`, `Additions`, `Color`) VALUES
  1598. (1, 'matcha', 'BioTorrents.de Stylesheet', '0', 'radio=noto_sans;radio=noto_serif;radio=opendyslexic;radio=comic_neue', '#000000'),
  1599. (2, 'bookish', 'Template Stylesheet', '1', 'radio=noto_sans;radio=noto_serif;radio=opendyslexic;radio=comic_neue', '#000000'),
  1600. (3, 'oppai', 'Oppaitime Stylesheet', '0', 'radio=noto_sans;radio=noto_serif;radio=opendyslexic;radio=comic_neue', '#fbc2e5'),
  1601. (4, 'beluga', 'Beluga Stylesheet', '0', 'radio=noto_sans;radio=noto_serif;radio=opendyslexic;radio=comic_neue;checkbox=pink;checkbox=haze', '#23252a'),
  1602. (5, 'genaviv', 'Genaviv Stylesheet', '0', 'radio=noto_sans;radio=noto_serif;radio=opendyslexic;radio=comic_neue;checkbox=fixed_header', '#0a0a0a'),
  1603. (6, 'postmod', 'What.cd Stylesheet', '0', 'radio=noto_sans;radio=noto_serif;radio=opendyslexic;radio=comic_neue', '#000000');
  1604. INSERT INTO `wiki_articles` (`ID`, `Revision`, `Title`, `Body`, `MinClassRead`, `MinClassEdit`, `Date`, `Author`) VALUES
  1605. (1, 1, 'Wiki', 'Welcome to your new wiki! Hope this works.', 100, 475, NOW(), 1);
  1606. INSERT INTO `wiki_aliases` (`Alias`, `UserID`, `ArticleID`) VALUES ('wiki', 1, 1);
  1607. INSERT INTO `wiki_revisions` (`ID`, `Revision`, `Title`, `Body`, `Date`, `Author`) VALUES
  1608. (1, 1, 'Wiki', 'Welcome to your new wiki! Hope this works.', NOW(), 1);
  1609. INSERT INTO `forums` (`ID`, `CategoryID`, `Sort`, `Name`, `Description`, `MinClassRead`, `MinClassWrite`, `MinClassCreate`, `NumTopics`, `NumPosts`, `LastPostID`, `LastPostAuthorID`, `LastPostTopicID`, `LastPostTime`) VALUES
  1610. (1, 1, 20, 'Your Site', 'Totally rad forum', 100, 100, 100, 0, 0, 0, 0, 0, NULL),
  1611. (2, 5, 30, 'Chat', 'Expect this to fill up with spam', 100, 100, 100, 0, 0, 0, 0, 0, NULL),
  1612. (3, 10, 40, 'Help!', 'I fell down and I cant get up', 100, 100, 100, 0, 0, 0, 0, 0, NULL),
  1613. (4, 20, 100, 'Trash', 'Every thread ends up here eventually', 100, 500, 500, 0, 0, 0, 0, 0, NULL);
  1614. INSERT INTO `tags` (`ID`, `Name`, `TagType`, `Uses`, `UserID`) VALUES
  1615. (1, 'one', 'genre', 0, 1),
  1616. (2, 'two', 'genre', 0, 1),
  1617. (3, 'three', 'genre', 0, 1),
  1618. (4, 'four', 'genre', 0, 1),
  1619. (5, 'five', 'genre', 0, 1);
  1620. INSERT INTO `schedule` (`NextHour`, `NextDay`, `NextBiWeekly`) VALUES (0,0,0);
  1621. INSERT INTO `forums_categories` (`ID`, `Sort`, `Name`) VALUES (1,1,'Site');
  1622. INSERT INTO `forums_categories` (`ID`, `Sort`, `Name`) VALUES (5,5,'Community');
  1623. INSERT INTO `forums_categories` (`ID`, `Sort`, `Name`) VALUES (10,10,'Help');
  1624. INSERT INTO `forums_categories` (`ID`, `Sort`, `Name`) VALUES (8,8,'Music');
  1625. INSERT INTO `forums_categories` (`ID`, `Sort`, `Name`) VALUES (20,20,'Trash');
  1626. INSERT INTO `misc` (`ID`, `Name`, `First`, `Second`) VALUES (1, 'FreeleechPool', '100', '200');
  1627. -- One last thing: a trigger to update seeding stats
  1628. DELIMITER ;;
  1629. CREATE TRIGGER update_seedtime
  1630. AFTER UPDATE ON `xbt_files_users`
  1631. FOR EACH ROW BEGIN
  1632. IF ( (OLD.timespent < NEW.timespent) AND (OLD.active = 1) AND (NEW.active = 1) ) THEN
  1633. INSERT INTO `users_seedtime`
  1634. (`UserID`, `TorrentID`, `SeedTime`, `Uploaded`, `Downloaded`, `LastUpdate`)
  1635. VALUES
  1636. (NEW.uid, NEW.fid, NEW.timespent, NEW.uploaded, NEW.downloaded, NOW())
  1637. ON DUPLICATE KEY UPDATE
  1638. `SeedTime` = `SeedTime` + (NEW.timespent - OLD.timespent),
  1639. `Uploaded` = `Uploaded` + (NEW.uploaded - OLD.uploaded),
  1640. `Downloaded` = `Downloaded` + (NEW.downloaded - OLD.downloaded),
  1641. `LastUpdate` = NOW();
  1642. END IF;
  1643. END;;
  1644. DELIMITER ;