· 8 years ago · Jul 16, 2018, 09:48 PM
1delimiter $$
2DROP PROCEDURE IF EXISTS create_db$$
3
4CREATE PROCEDURE create_db(IN nr CHAR(64))
5BEGIN
6
7SET @sql := CONCAT('CREATE DATABASE ','test_', nr); PREPARE mySt FROM @sql; EXECUTE mySt;
8SET @sql := CONCAT('USE test_',nr); PREPARE mySt FROM @sql; EXECUTE mySt;
9
10CREATE TABLE `bleble` (
11 `id` bigint(10) unsigned NOT NULL auto_increment,
12 `type` varchar(16) collate utf8_unicode_ci NOT NULL,
13 PRIMARY KEY (`id`)
14) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
15/*
16
17INSERT INTO `idx` (`id`, `type`) VALUES
18(1, 'folder'),
19(2, 'folder'),
20(3, 'folder'),
21(4, 'folder'),
22(5, 'folder'),
23(6, 'folder'),
24(7, 'folder'),
25(8, 'folder'),
26(9, 'folder'),
27(10, 'folder'),
28(11, 'user'),
29(12, 'folder'),
30(13, 'folder'),
31(14, 'folder'),
32(15, 'article'),
33(16, 'article'),
34(17, 'article'),
35(18, 'folder');
36
37
38CREATE TABLE `module_article` (
39 `id` bigint(10) NOT NULL,
40 `parent_id` bigint(10) unsigned NOT NULL,
41 `childrens` text collate utf8_unicode_ci NOT NULL,
42 `type` varchar(16) collate utf8_unicode_ci NOT NULL,
43 `deleted` tinyint(1) unsigned NOT NULL default '0',
44 `protected` tinyint(1) unsigned NOT NULL default '0',
45 `status` tinyint(1) unsigned NOT NULL default '1',
46 `hpage` tinyint(1) unsigned NOT NULL default '0',
47 `language` varchar(2) collate utf8_unicode_ci NOT NULL,
48 `sequence` bigint(10) unsigned NOT NULL,
49 `owner` int(10) unsigned NOT NULL,
50 `privileges` int(3) unsigned NOT NULL,
51 `client_privileges` int(3) unsigned NOT NULL,
52 `counter` bigint(5) unsigned NOT NULL,
53 `date_created` datetime NOT NULL,
54 `date_modified` datetime default NULL,
55 `date_deleted` datetime default NULL,
56 `date_activ` datetime default NULL,
57 `date_expired` datetime default NULL,
58 `layout_position` varchar(16) collate utf8_unicode_ci NOT NULL,
59 `name` varchar(64) collate utf8_unicode_ci NOT NULL,
60 `teaser` text collate utf8_unicode_ci NOT NULL,
61 `body` text collate utf8_unicode_ci NOT NULL,
62 PRIMARY KEY (`id`)
63) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
64
65
66INSERT INTO `module_article` (`id`, `parent_id`, `childrens`, `type`, `deleted`, `protected`, `status`, `hpage`, `language`, `sequence`, `owner`, `privileges`, `client_privileges`, `counter`, `date_created`, `date_modified`, `date_deleted`, `date_activ`, `date_expired`, `layout_position`, `name`, `teaser`, `body`) VALUES
67(15, 1, '', 'article', 0, 0, 1, 0, 'PL', 1, 11, 0, 0, 0, '2009-06-23 17:41:00', NULL, NULL, NULL, NULL, 'left', 'Artykuł testowy', '<p>Opublikowany artykuł testowy</p>', '<p>Opublikowany artykuł testowy. Opublikowany artykuł testowy. Opublikowany artykuł testowy.</p>'),
68(16, 1, '', 'article', 0, 0, 0, 0, 'PL', 2, 11, 0, 0, 0, '2009-06-23 17:41:23', NULL, NULL, NULL, NULL, 'left', 'Artykuł testowy 2', '<p>Nie opublikowany artykuł testowy.</p>', '<p>Nie opublikowany artykuł testowy. Nie opublikowany artykuł testowy. Nie opublikowany artykuł testowy.</p>'),
69(17, 12, '', 'article', 0, 0, 1, 0, 'PL', 1, 11, 0, 0, 0, '2009-06-23 17:42:06', NULL, NULL, NULL, NULL, 'left', 'Artykuł wewnątrz folderu', '<p>Testowy artykuł, który znajduje się wewnątrz folderu.</p>', '<p>Testowy artykuł, który znajduje się wewnątrz folderu. Testowy artykuł, który znajduje się wewnątrz folderu.</p>');
70
71
72
73CREATE TABLE `module_folder` (
74 `id` bigint(10) NOT NULL,
75 `parent_id` bigint(10) unsigned NOT NULL,
76 `childrens` text collate utf8_unicode_ci NOT NULL,
77 `type` varchar(16) collate utf8_unicode_ci NOT NULL,
78 `deleted` tinyint(1) unsigned NOT NULL default '0',
79 `protected` tinyint(1) unsigned NOT NULL default '0',
80 `status` tinyint(1) unsigned NOT NULL default '1',
81 `hpage` tinyint(1) unsigned NOT NULL default '0',
82 `language` varchar(2) collate utf8_unicode_ci NOT NULL,
83 `sequence` bigint(10) unsigned NOT NULL,
84 `owner` int(10) unsigned NOT NULL,
85 `privileges` int(3) unsigned NOT NULL,
86 `client_privileges` int(3) unsigned NOT NULL,
87 `counter` bigint(5) unsigned NOT NULL default '0',
88 `date_created` datetime NOT NULL,
89 `date_modified` datetime default NULL,
90 `date_deleted` datetime default NULL,
91 `date_activ` datetime default NULL,
92 `date_expired` datetime default NULL,
93 `layout_position` varchar(16) collate utf8_unicode_ci NOT NULL,
94 `name` varchar(64) collate utf8_unicode_ci NOT NULL,
95 `short_name` varchar(16) collate utf8_unicode_ci NOT NULL,
96 `content` text collate utf8_unicode_ci NOT NULL,
97 `description` varchar(128) collate utf8_unicode_ci NOT NULL,
98 `url` varchar(100) collate utf8_unicode_ci NOT NULL,
99 PRIMARY KEY (`id`)
100) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
101
102INSERT INTO `module_folder` (`id`, `parent_id`, `childrens`, `type`, `deleted`, `protected`, `status`, `hpage`, `language`, `sequence`, `owner`, `privileges`, `client_privileges`, `counter`, `date_created`, `date_modified`, `date_deleted`, `date_activ`, `date_expired`, `layout_position`, `name`, `short_name`, `content`, `description`) VALUES
103(1, 0, '15,16,', 'folder', 0, 1, 1, 0, 'PL', 1, 0, 0, 0, 0, '2008-05-23 23:11:18', NULL, NULL, NULL, NULL, '', 'home', '', '', ''),
104(2, 0, '', 'folder', 0, 1, 1, 0, 'PL', 1, 0, 0, 0, 0, '2008-05-23 23:11:18', NULL, NULL, NULL, NULL, '', 'users', '', '', ''),
105(12, 1, '17', 'folder', 0, 0, 1, 0, 'PL', 1, 11, 0, 0, 0, '2009-06-23 16:34:55', NULL, NULL, NULL, NULL, 'left', 'Folder testowy 1', '', 'article', ''),
106(13, 1, '', 'folder', 0, 0, 1, 0, 'PL', 2, 11, 0, 0, 0, '2009-06-23 16:35:05', NULL, NULL, NULL, NULL, 'left', 'Folder testowy 2', '', 'article', ''),
107(14, 1, '', 'folder', 0, 0, 1, 0, 'PL', 3, 11, 0, 0, 0, '2009-06-23 16:35:28', NULL, NULL, NULL, NULL, 'left', 'Folder testowy 3', '', '', ''),
108(18, 1, '', 'folder', 1, 0, 0, 0, 'PL', 4, 11, 0, 0, 0, '2009-06-23 17:43:14', NULL, '2009-06-23 17:43:19', NULL, NULL, 'left', 'Folder usunięty do kosza', '', '', '');
109
110
111CREATE TABLE `module_user` (
112 `id` int(10) unsigned NOT NULL,
113 `parent_id` int(10) unsigned NOT NULL,
114 `childrens` text collate utf8_unicode_ci NOT NULL,
115 `parent_user_id` int(10) unsigned default NULL,
116 `type` varchar(16) collate utf8_unicode_ci NOT NULL,
117 `privileges` int(3) unsigned NOT NULL default '0',
118 `enabled_items` text collate utf8_unicode_ci NOT NULL,
119 `deleted` tinyint(1) unsigned NOT NULL default '0',
120 `status` tinyint(1) unsigned NOT NULL default '1',
121 `protected` tinyint(1) unsigned NOT NULL default '0',
122 `first_name` varchar(32) collate utf8_unicode_ci NOT NULL,
123 `last_name` varchar(32) collate utf8_unicode_ci NOT NULL,
124 `login` varchar(32) collate utf8_unicode_ci NOT NULL,
125 `password` varchar(64) collate utf8_unicode_ci NOT NULL,
126 `email` varchar(64) collate utf8_unicode_ci NOT NULL,
127 `date_created` datetime NOT NULL,
128 `date_modified` datetime default NULL,
129 `date_deleted` datetime default NULL,
130 `date_activ` datetime default NULL,
131 `date_expired` datetime default NULL,
132 PRIMARY KEY (`id`),
133 UNIQUE KEY `INDEX_LOGIN` (`login`)
134) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
135
136
137INSERT INTO `module_user` (`id`, `parent_id`, `type`, `privileges`, `enabled_items`, `deleted`, `status`, `protected`, `first_name`, `last_name`, `login`, `login_hash`, `password`, `email`, `invoice_line`, `date_created`, `date_modified`, `date_deleted`, `date_activ`, `date_expired`) VALUES
138(11, 2, 'user', 127, '', 0, 1, 1, 'Test', 'Testowy', 'test', '68358d5d9cbbf39fe571ba41f26524b6', '68358d5d9cbbf39fe571ba41f26524b6', 'test@rad-com.pl', 1, '2008-05-23 23:22:55', '2009-06-23 17:36:39', NULL, NULL, NULL);
139
140
141CREATE TABLE `sessions2` (
142 `sesskey` varchar(64) collate utf8_unicode_ci NOT NULL default '',
143 `expiry` datetime NOT NULL,
144 `expireref` varchar(250) collate utf8_unicode_ci default '',
145 `created` datetime NOT NULL,
146 `modified` datetime NOT NULL,
147 `sessdata` longtext collate utf8_unicode_ci,
148 PRIMARY KEY (`sesskey`),
149 KEY `sess2_expiry` (`expiry`),
150 KEY `sess2_expireref` (`expireref`)
151) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
152
153
154
155CREATE OR REPLACE
156 VIEW `module_article_view` AS
157SELECT
158 `module_article`.`id`,
159 `module_article`.`parent_id`,
160 `module_article`.`childrens`,
161 `module_article`.`type`,
162 `module_article`.`deleted`,
163 `module_article`.`protected`,
164 `module_article`.`status`,
165 `module_article`.`hpage`,
166 `module_article`.`language`,
167 `module_article`.`sequence`,
168 `module_article`.`privileges`,
169 `module_article`.`client_privileges`,
170 `module_article`.`counter`,
171 `module_article`.`date_created`,
172 `module_article`.`date_modified`,
173 `module_article`.`date_deleted`,
174 `module_article`.`date_activ`,
175 `module_article`.`date_expired`,
176 `module_article`.`layout_position`,
177 `module_article`.`name`,
178 `module_article`.`teaser`,
179 `module_article`.`body`,
180 `module_user`.`login` as `owner`,
181 `module_user`.`first_name` as `owner_first_name`,
182 `module_user`.`last_name` as `owner_last_name`
183FROM `module_article`
184LEFT JOIN `module_user` ON `module_article`.`owner` = `module_user`.`id`;
185
186
187CREATE OR REPLACE
188 VIEW `module_folder_view` AS
189SELECT
190 `module_folder`.`id`,
191 `module_folder`.`parent_id`,
192 `module_folder`.`childrens`,
193 `module_folder`.`type`,
194 `module_folder`.`deleted`,
195 `module_folder`.`protected`,
196 `module_folder`.`status`,
197 `module_folder`.`hpage`,
198 `module_folder`.`language`,
199 `module_folder`.`sequence`,
200 `module_folder`.`privileges`,
201 `module_folder`.`client_privileges`,
202 `module_folder`.`counter`,
203 `module_folder`.`date_created`,
204 `module_folder`.`date_modified`,
205 `module_folder`.`date_deleted`,
206 `module_folder`.`date_activ`,
207 `module_folder`.`date_expired`,
208 `module_folder`.`layout_position`,
209 `module_folder`.`name`,
210 `module_folder`.`short_name`,
211 `module_folder`.`content`,
212 `module_folder`.`description`,
213 `module_folder`.`url`,
214 `module_user`.`login` as `owner`
215FROM `module_folder`
216LEFT JOIN `module_user` ON `module_folder`.`owner` = `module_user`.`id`;
217
218*/
219END$$
220delimiter ;