API tools faq
paste
Advertisement
iptvipbr

Untitled

iptvipbr
Mar 21st, 2018
98
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.72 KB | None | 0 0
raw download clone embed print report
  1. /*SCRIPT DATABASE GENERATOR*/
  2.  
  3. USE FATTO;
  4.  
  5. /*CREATE TABLE GENRES*/
  6.  
  7. DROP TABLE IF EXISTS `genres`;
  8. CREATE TABLE `genres` (
  9. `id_gr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  10. `name` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
  11. `description` varchar(100) NOT NULL COMMENT'description of genre',
  12. KEY `idx_genres_id_gr` (`id_gr`) COMMENT'index',
  13. PRIMARY KEY (id_gr) COMMENT'unique key'
  14. )
  15. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the genre type:';
  16. ;
  17.  
  18. /*CREATE TABLE GENRES*/
  19.  
  20. DROP TABLE IF EXISTS `genres_audit`;
  21. CREATE TABLE `genres_audit` (
  22. `id_gr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  23. `name_old` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
  24. `name_new` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
  25. `description_old` varchar(100) NOT NULL COMMENT'description of genre',
  26. `description_new` varchar(100) NOT NULL COMMENT'description of genre',
  27. KEY `idx_genres_id_gr` (`id_gr`) COMMENT'index',
  28. PRIMARY KEY (id_gr) COMMENT'unique key'
  29. )
  30. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the genre type:';
  31. ;
  32.  
  33. insert into `genres`(`name`, `description`) values('Ação','');
  34. insert into `genres`(`name`, `description`) values('Animação','');
  35. insert into `genres`(`name`, `description`) values('Aventura','');
  36. insert into `genres`(`name`, `description`) values('Cinema de arte','');
  37. insert into `genres`(`name`, `description`) values('Chanchada','');
  38. insert into `genres`(`name`, `description`) values('Cinema catástrofe','');
  39. insert into `genres`(`name`, `description`) values('Comédia','');
  40. insert into `genres`(`name`, `description`) values('Comédia romântica','');
  41. insert into `genres`(`name`, `description`) values('Comédia dramática','');
  42. insert into `genres`(`name`, `description`) values('Comédia de ação','');
  43. insert into `genres`(`name`, `description`) values('Cult','');
  44. insert into `genres`(`name`, `description`) values('Dança','');
  45. insert into `genres`(`name`, `description`) values('Documentários','');
  46. insert into `genres`(`name`, `description`) values('Drama','');
  47. insert into `genres`(`name`, `description`) values('Espionagem','');
  48. insert into `genres`(`name`, `description`) values('Erótico','');
  49. insert into `genres`(`name`, `description`) values('Fantasia','');
  50. insert into `genres`(`name`, `description`) values('Faroeste','');
  51. insert into `genres`(`name`, `description`) values('Ficção científica','');
  52. insert into `genres`(`name`, `description`) values('Series','');
  53. insert into `genres`(`name`, `description`) values('Guerra','');
  54. insert into `genres`(`name`, `description`) values('Machinima','');
  55. insert into `genres`(`name`, `description`) values('Masala','');
  56. insert into `genres`(`name`, `description`) values('Musical','');
  57. insert into `genres`(`name`, `description`) values('Filme noir','');
  58. insert into `genres`(`name`, `description`) values('Policial','');
  59. insert into `genres`(`name`, `description`) values('Pornochanchada','');
  60. insert into `genres`(`name`, `description`) values('Pornográfico','');
  61. insert into `genres`(`name`, `description`) values('Robologia','');
  62. insert into `genres`(`name`, `description`) values('Romance','');
  63. insert into `genres`(`name`, `description`) values('Seriado','');
  64. insert into `genres`(`name`, `description`) values('Suspense','');
  65. insert into `genres`(`name`, `description`) values('Terror','');
  66. insert into `genres`(`name`, `description`) values('Trash','');
  67.  
  68. ==============================================================================================
  69.  
  70. /*CREATE TABLE title basics*/
  71.  
  72. DROP TABLE IF EXISTS `title.basics`;
  73. CREATE TABLE `title.basics` (
  74. `id_tb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  75. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the title',
  76. `titleType` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
  77. `primaryTitle` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
  78. `originalTitle` varchar(100) NOT NULL COMMENT'original title, in the original language',
  79. `isAdult` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
  80. `startYear` smallint(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
  81. `endYear` smallint(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
  82. `runtimeMinutes` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
  83. `genres` SMALLINT COMMENT 'includes up to three genres associated with the title',
  84. KEY `idx_title.basics_id_tb` (`id_tb`) COMMENT'index',
  85. KEY `idx_title.basics_tconst` (`tconst`) COMMENT'index',
  86. PRIMARY KEY (tconst) COMMENT'unique key'
  87. )
  88. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the following information for titles:';
  89. ;
  90.  
  91. ALTER TABLE `title.basics` ADD CONSTRAINT `fk_genre` FOREIGN KEY ( `genres` ) REFERENCES `genres` (`id_gr`);
  92. -------------------------------------------------------------------------------------------------
  93.  
  94. /*CREATE TABLE title basics audit*/
  95.  
  96. DROP TABLE IF EXISTS `title.basics.Audit`;
  97. CREATE TABLE `title.basics.Audit` (
  98. `id_tba` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  99. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the title',
  100. `titleType_old` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
  101. `titleType_new` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
  102. `primaryTitle_old` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
  103. `primaryTitle_new` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
  104. `originalTitle_old` varchar(100) NOT NULL COMMENT'original title, in the original language',
  105. `originalTitle_new` varchar(100) NOT NULL COMMENT'original title, in the original language',
  106. `isAdult_old` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
  107. `isAdult_new` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
  108. `startYear_old` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
  109. `startYear_new` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
  110. `endYear_old` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
  111. `endYear_new` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
  112. `runtimeMinutes_old` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
  113. `runtimeMinutes_new` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
  114. `genres_old` smallint DEFAULT NULL COMMENT 'includes up to three genres associated with the title',
  115. `genres_new` smallint DEFAULT NULL COMMENT 'includes up to three genres associated with the title',
  116. `logged_user` nvarchar(50) COMMENT'user action',
  117. `oper_type` varchar(10) COMMENT'Operation type',
  118. `tstamp` datetime,
  119. KEY `idx_title.basics.Audit_id_tba` (`id_tba`) COMMENT'index',
  120. KEY `idx_title.basics.Audit_tconst` (`tconst`) COMMENT'index',
  121. PRIMARY KEY (tconst) COMMENT'unique key'
  122. )
  123. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the following information for titles:';
  124. ;
  125. ALTER TABLE `title.basics.Audit` ADD CONSTRAINT `fk_genre_old` FOREIGN KEY ( `genres_old` ) REFERENCES `genres` (`id_gr`);
  126. ALTER TABLE `title.basics.Audit` ADD CONSTRAINT `fk_genre_new` FOREIGN KEY ( `genres_new` ) REFERENCES `genres` (`id_gr`);
  127.  
  128. -------------------------------------------------------------------------------------------------
  129.  
  130. /*CREATE TABLE title episode*/
  131.  
  132. DROP TABLE IF EXISTS `title.episode`;
  133. CREATE TABLE `title.episode`(
  134. `id_te` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  135. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier of episode',
  136. `parentTconst` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
  137. `seasonNumber` SMALLINT COMMENT'season number the episode belongs to',
  138. `episodeNumber` SMALLINT COMMENT'episode number of the tconst in the TV series',
  139. KEY `idx_title.episode_id_te` (`id_te`) COMMENT'index',
  140. KEY `idx_title.episode_tconst` (`tconst`) COMMENT'index',
  141. PRIMARY KEY (tconst) COMMENT'unique key'
  142. )
  143. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the tv episode information. Fields include'
  144. ;
  145. -------------------------------------------------------------------------------------------------
  146.  
  147. /*CREATE TABLE title episode audit*/
  148.  
  149. DROP TABLE IF EXISTS `title.episode.Audit`;
  150. CREATE TABLE `title.episode.Audit`(
  151. `id_tea` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  152. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier of episode',
  153. `parentTconst_old` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
  154. `parentTconst_new` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
  155. `seasonNumber_old` SMALLINT COMMENT'season number the episode belongs to',
  156. `seasonNumber_new` SMALLINT COMMENT'season number the episode belongs to',
  157. `episodeNumber_old` SMALLINT COMMENT'episode number of the tconst in the TV series',
  158. `episodeNumber_new` SMALLINT COMMENT'episode number of the tconst in the TV series',
  159. `logged_user` nvarchar(50) COMMENT'user action',
  160. `oper_type` varchar(10) COMMENT'Operation type',
  161. `tstamp` datetime,
  162. KEY `idx_title.episode.Audit_id_tea` (`id_tea`) COMMENT'index',
  163. KEY `idx_title.episode.Audit_tconst` (`tconst`) COMMENT'index',
  164. PRIMARY KEY (tconst) COMMENT'unique key'
  165. )
  166. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the tv episode information. Fields include'
  167. ;
  168. -------------------------------------------------------------------------------------------------
  169.  
  170. /*CREATE TABLE resources basics*/
  171.  
  172. DROP TABLE IF EXISTS `resource.basics`;
  173. CREATE TABLE `resource.basics`(
  174. `id_rb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  175. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
  176. `url` varchar(100) COMMENT'Media URL',
  177. `poster` varchar(100) COMMENT'Poster URL',
  178. /*`views` bigint COMMENT'vews count',*/
  179. KEY `idx_resource.basics_id_rb` (`id_rb`) COMMENT'index',
  180. KEY `idx_tresource.basics_tconst` (`tconst`) COMMENT'index',
  181. PRIMARY KEY (tconst) COMMENT'unique key'
  182. )
  183. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contem os recursos para assistir'
  184. ;
  185. -------------------------------------------------------------------------------------------------
  186.  
  187. /*CREATE TABLE resources basics audit*/
  188.  
  189. DROP TABLE IF EXISTS `resource.basics.Audit`;
  190. CREATE TABLE `resource.basics.Audit`(
  191. `id_rba` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  192. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
  193. `url_old` varchar(100) COMMENT'Media URL',
  194. `url_new` varchar(100) COMMENT'Media URL',
  195. `poster_old` varchar(100) COMMENT'Poster URL',
  196. `poster_new` varchar(100) COMMENT'Poster URL',
  197. /*`views` bigint COMMENT'vews count',*/
  198. `logged_user` nvarchar(50) COMMENT'user action',
  199. `oper_type` varchar(10) COMMENT'Operation type',
  200. `tstamp` datetime,
  201. KEY `idx_resource.basics.Audit_id_rba` (`id_rba`) COMMENT'index',
  202. KEY `idx_resource.basics.Audit_tconst` (`tconst`) COMMENT'index',
  203. PRIMARY KEY (tconst) COMMENT'unique key'
  204. )
  205. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contem os recursos para assistir'
  206. ;
  207.  
  208. ==============================================================================================
  209.  
  210. /* create trigger audit ins_resource.basics*/
  211.  
  212. DELIMITER //
  213. drop trigger `ins_resource.basics`;
  214. create trigger `ins_resource.basics`
  215. after Insert on `resource.basics`
  216. FOR EACH ROW
  217. BEGIN
  218. INSERT INTO `resource.basics.audit`
  219. (
  220. `tconst`
  221. ,`titleType_new`
  222. ,`primaryTitle_new`
  223. ,`originalTitle_new`
  224. ,`isAdult_new`
  225. ,`startYear_new`
  226. ,`endYear_new`
  227. ,`runtimeMinutes_new`
  228. ,`genres_new`
  229. ,`logged_user`
  230. ,`oper_type`
  231. ,`tstamp`
  232. )
  233. Values
  234. (
  235. `new.tconst`
  236. ,`new.titleType_new`
  237. ,`new.primaryTitle`
  238. ,`new.originalTitle`
  239. ,`new.isAdult`
  240. ,`new.startYear`
  241. ,`new.endYear`
  242. ,`new.runtimeMinutes`
  243. ,`new.genres`
  244. , current_user()
  245. ,'Insert'
  246. ,getDate()
  247. );
  248. END//
  249.  
  250. -------------------------------------------------------------------------------------------------
  251.  
  252. DELIMITER //
  253.  
  254. drop trigger `upd_resource.basics`;
  255. create trigger `upd_resource.basics`
  256. after update on `resource.basics`
  257. FOR EACH ROW
  258. BEGIN
  259. INSERT INTO `resource.basics.audit`
  260. (
  261. `tconst`
  262. ,`titleType_old`
  263. ,`titleType_new`
  264. ,`primaryTitle_old`
  265. ,`primaryTitle_new`
  266. ,`originalTitle_old`
  267. ,`originalTitle_new`
  268. ,`isAdult_old`
  269. ,`isAdult_new`
  270. ,`startYear_old`
  271. ,`startYear_new`
  272. ,`endYear_old`
  273. ,`endYear_new`
  274. ,`runtimeMinutes_old`
  275. ,`runtimeMinutes_new`
  276. ,`genres_old`
  277. ,`genres_new`
  278. ,`logged_user`
  279. ,`oper_type`
  280. ,`tstamp`
  281. )
  282. Values
  283. (
  284. `new.tconst`
  285. ,`old.titleType_new`
  286. ,`new.titleType_new`
  287. ,`old.primaryTitle`
  288. ,`new.primaryTitle`
  289. ,`old.originalTitle`
  290. ,`new.originalTitle`
  291. ,`old.isAdult`
  292. ,`new.isAdult`
  293. ,`old.startYear`
  294. ,`new.startYear`
  295. ,`old.endYear`
  296. ,`new.endYear`
  297. ,`old.runtimeMinutes`
  298. ,`new.runtimeMinutes`
  299. ,`old.genres`
  300. ,`new.genres`
  301. , current_user()
  302. ,'update'
  303. ,getDate()
  304. );
  305. END//
  306.  
  307. -------------------------------------------------------------------------------------------------
  308.  
  309. DELIMITER //
  310.  
  311. drop trigger `del_resource.basics`;
  312. create trigger `del_resource.basics`
  313. after delete on `resource.basics`
  314. FOR EACH ROW
  315. BEGIN
  316. INSERT INTO `resource.basics.audit`
  317. (
  318. `tconst`
  319. ,`titleType_old`
  320. ,`primaryTitle_old`
  321. ,`originalTitle_old`
  322. ,`isAdult_old`
  323. ,`startYear_old`
  324. ,`endYear_old`
  325. ,`runtimeMinutes_old`
  326. ,`genres_old`
  327. ,`logged_user`
  328. ,`oper_type`
  329. ,`tstamp`
  330. )
  331. Values
  332. (
  333. `new.tconst`
  334. ,`old.titleType_new`
  335. ,`old.primaryTitle`
  336. ,`old.originalTitle`
  337. ,`old.isAdult`
  338. ,`old.startYear`
  339. ,`old.endYear`
  340. ,`old.runtimeMinutes`
  341. ,`old.genres`
  342. , current_user()
  343. ,'delete'
  344. ,getDate()
  345. );
  346. END//
  347.  
  348. ==============================================================================================
  349.  
  350.  
  351. /* model #2*/
  352.  
  353. DROP TABLE IF EXISTS `title.ratings`;
  354. CREATE TABLE `title.ratings`(
  355. `id_tr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  356. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
  357. `averageRating` decimal(5,2) NOT NULL COMMENT'weighted average of all the individual user ratings',
  358. `numVotes` int(10) COMMENT'number of votes the title has received',
  359. KEY `idx_title.ratings_id_tr` (`id_tr`) COMMENT'index',
  360. KEY `idx_title.ratings_tconst` (`tconst`) COMMENT'index',
  361. PRIMARY KEY (tconst) COMMENT'unique key'
  362. )
  363. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT' Contains the IMDb rating and votes information for titles'
  364. ;
  365.  
  366. DROP TABLE IF EXISTS `title.crew`;
  367. CREATE TABLE `title.crew`(
  368. `id_tc` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  369. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
  370. `directors` varchar(500) NOT NULL COMMENT'(array of nconsts) - director(s) of the given title',
  371. `writers` varchar(500) NOT NULL COMMENT'(array of nconsts) – writer(s) of the given title',
  372. KEY `idx_title.crew_iid_tc` (`id_tc`) COMMENT'index',
  373. KEY `idx_title.crew_tconst` (`tconst`) COMMENT'index',
  374. PRIMARY KEY (tconst) COMMENT'unique key'
  375. )
  376. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the director and writer information for all the titles in IMDb'
  377. ;
  378.  
  379. DROP TABLE IF EXISTS `title.principals`;
  380. CREATE TABLE `title.principals`(
  381. `id_tp` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  382. `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
  383. `principalCast` varchar(200) NOT NULL COMMENT'(array of nconsts) – title’s top-billed cast/crew',
  384. KEY `idx_title.principals_id_tp` (`id_tp`) COMMENT'index',
  385. KEY `idx_title.principals_tconst` (`tconst`) COMMENT'index',
  386. PRIMARY KEY (tconst) COMMENT'unique key'
  387. )
  388. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT' – Contains the principal cast/crew for titles'
  389. ;
  390.  
  391. DROP TABLE IF EXISTS `name.basics`;
  392. CREATE TABLE `name.basics`(
  393. `id_nb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
  394. `nconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the name/person',
  395. `primaryName` varchar(100) NOT NULL COMMENT'(string)– name by which the person is most often credited',
  396. `birthYear` smallint(4) NULL COMMENT'in YYYY format',
  397. `deathYear` smallint(4) NULL COMMENT'in YYYY format if applicable, else ‘\N’',
  398. `primaryProfession` varchar(250) NOT NULL COMMENT'(array of strings)– the top-3 professions of the person',
  399. `knownForTitles` varchar(1000) NOT NULL COMMENT'(array of tconsts) – titles the person is known for',
  400. KEY `idx_name.basics_id_nb` (`id_nb`) COMMENT'index',
  401. KEY `idx_name.basics_tconst` (`nconst`) COMMENT'index',
  402. PRIMARY KEY (nconst) COMMENT'unique key'
  403. )
  404. ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the following information for names'
  405. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!