Ticket #1409: seagull.patch_sq.sql

File seagull.patch_sq.sql, 37.9 kB (added by zeph1r, 5 years ago)

sqlite installer schemas

Line 
1 Index: modules/default/data/schema.pg.sql
2 ===================================================================
3 --- modules/default/data/schema.pg.sql  (revision 163)
4 +++ modules/default/data/schema.pg.sql  (working copy)
5 @@ -10,8 +10,7 @@
6     logtime              TIMESTAMP            not null,
7     ident                CHAR(16)             not null,
8     priority             INT4                 not null,
9 -   message              VARCHAR(200)         null,
10 -   constraint PK_LOG_TABLE primary key (id)
11 +   message              VARCHAR(200)         null
12  );
13  
14  -- ==============================================================
15 @@ -27,8 +26,7 @@
16  (
17     lockID               CHAR(32)             not null,
18     lockTable            CHAR(32)             not null,
19 -   lockStamp            INT4                 null,
20 -   constraint PK_TABLE_LOCK primary key (lockID, lockTable)
21 +   lockStamp            INT4                 null
22  );
23  
24  -- ==============================================================
25 @@ -46,8 +44,7 @@
26     data_value                    TEXT            null,
27     usr_id                        INT4            not null default 0,
28     username                      VARCHAR(64)     null,
29 -   expiry                        INT4            not null,
30 -   constraint PK_SESSION primary key (session_id)
31 +   expiry                        INT4            not null
32  );
33  
34  -- ==============================================================
35 @@ -86,7 +83,7 @@
36  -- ==============================================================
37  
38  create table module (
39 -   module_id         INT4 not null,
40 +   module_id         INTEGER PRIMARY KEY,
41     is_configurable   INT2 null,
42     name              VARCHAR(255) null,
43     title             VARCHAR(255) null,
44 @@ -96,8 +93,7 @@
45     maintainers       TEXT,
46     version           VARCHAR(8)   null,
47     license           VARCHAR(16)  null,
48 -   state             VARCHAR(8)   null,
49 -   constraint PK_MODULE primary key (module_id)
50 +   state             VARCHAR(8)   null
51  );
52  
53  -- ==============================================================
54 Index: modules/block/data/schema.sq.sql
55 ===================================================================
56 --- modules/block/data/schema.sq.sql    (revision 0)
57 +++ modules/block/data/schema.sq.sql    (revision 0)
58 @@ -0,0 +1,49 @@
59 +/*==============================================================*/
60 +/* Table: block                                                 */
61 +/*==============================================================*/
62 +create table block
63 +(
64 +   block_id                       INTEGER PRIMARY KEY,
65 +   name                           varchar(64),
66 +   title                          varchar(32),
67 +   title_class                    varchar(32),
68 +   body_class                     varchar(32),
69 +   blk_order                      smallint,
70 +   position                       varchar(16),
71 +   is_enabled                     smallint,
72 +   is_cached                      smallint,
73 +   params                         longtext
74 +);
75 +
76 +/*==============================================================*/
77 +/* Table: block_assignment                                      */
78 +/*==============================================================*/
79 +create table block_assignment
80 +(
81 +   block_id                       int                            not null,
82 +   section_id                     int                            not null
83 +);
84 +
85 +/*==============================================================*/
86 +/* Index: block_assignment_fk                                   */
87 +/*==============================================================*/
88 +create index block_assignment_fk on block_assignment
89 +(
90 +   block_id
91 +);
92 +
93 +/*==============================================================*/
94 +/* Index: block_assignment_fk2                                  */
95 +/*==============================================================*/
96 +create index block_assignment_fk2 on block_assignment
97 +(
98 +   section_id
99 +);
100 +-- ==============================================================
101 +--  table block_role
102 +--  DK
103 +-- ==============================================================
104 +create table block_role (
105 +    block_id integer not null,
106 +    role_id integer not null
107 +);
108 Index: modules/block/data/data.sample.sq.sql
109 ===================================================================
110 --- modules/block/data/data.sample.sq.sql       (revision 0)
111 +++ modules/block/data/data.sample.sq.sql       (revision 0)
112 @@ -0,0 +1,28 @@
113 +#
114 +# Dumping data for table block
115 +#
116 +
117 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Default_Block_SampleRight1', 'Sample Right Block', '', '', 5, 'Right', 0, 0, 'N;');
118 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Default_Block_Calendar', 'Calendar', '', '', 5, 'Left', 0, 1, 'N;');
119 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'User_Block_OnlineUsers', 'Online', '', '', 6, 'Left', 0, 0, 'N;');
120 +
121 +
122 +SELECT @blockIdSampleRight1 := block_id FROM block WHERE name = 'Default_Block_SampleRight1';
123 +SELECT @blockIdCalendar := block_id FROM block WHERE name = 'Default_Block_Calendar';
124 +SELECT @blockIdOnlineUsers := block_id FROM block WHERE name = 'User_Block_OnlineUsers';
125 +
126 +--
127 +-- Dumping data for table block_assignment
128 +--
129 +
130 +INSERT INTO block_assignment VALUES (@blockIdSampleRight1, 0);
131 +INSERT INTO block_assignment VALUES (@blockIdCalendar, 0);
132 +INSERT INTO block_assignment VALUES (@blockIdOnlineUsers, 0);
133 +
134 +--
135 +-- Dumping data for table block_role
136 +--
137 +
138 +INSERT INTO block_role VALUES (@blockIdSampleRight1, -2);
139 +INSERT INTO block_role VALUES (@blockIdCalendar, -2);
140 +INSERT INTO block_role VALUES (@blockIdOnlineUsers, -2);
141 Index: modules/block/data/data.block.add.sq.sql
142 ===================================================================
143 --- modules/block/data/data.block.add.sq.sql    (revision 0)
144 +++ modules/block/data/data.block.add.sq.sql    (revision 0)
145 @@ -0,0 +1,25 @@
146 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'User_Block_Login2', 'Login', '', '', 5, 'Right', 1, 0, 'a:2:{s:13:"loginTemplate";s:15:"blockLogin.html";s:14:"logoutTemplate";s:16:"blockLogout.html";}');
147 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Default_Block_Sample1', 'Community', '', '', 7, 'Left', 1, 1, 'N;');
148 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Default_Block_Sample2', 'Syndication', '', '', 3, 'Left', 1, 1, 'N;');
149 +-- INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Export_Block_ShowRss', 'Latest Seagull News', '', '', 2, 'Left', 1, 1, 'a:2:{s:9:"rssSource";s:37:"http://seagullproject.org/export/rss/";s:11:"itemsToShow";s:1:"5";}');
150 +
151 +-- SELECT @blockIdLogin := block_id FROM block WHERE name = 'User_Block_Login2';
152 +-- SELECT @blockIdSample1 := block_id FROM block WHERE name = 'Default_Block_Sample1';
153 +-- SELECT @blockIdSample2 := block_id FROM block WHERE name = 'Default_Block_Sample2';
154 +-- SELECT @blockIdSampleRss := block_id FROM block WHERE name = 'Export_Block_ShowRss';
155 +
156 +INSERT INTO block_assignment VALUES (
157 + (SELECT block_id FROM block WHERE name = 'User_Block_Login2'), 0);
158 +INSERT INTO block_assignment VALUES (
159 + (SELECT block_id FROM block WHERE name = 'Default_Block_Sample1'), 0);
160 +INSERT INTO block_assignment VALUES (
161 + (SELECT block_id FROM block WHERE name = 'Default_Block_Sample2'), 0);
162 +-- INSERT INTO block_assignment VALUES (@blockIdSampleRss, 0);
163 +
164 +INSERT INTO block_role VALUES (
165 + (SELECT block_id FROM block WHERE name = 'User_Block_Login2'), -2);
166 +INSERT INTO block_role VALUES (
167 + (SELECT block_id FROM block WHERE name = 'Default_Block_Sample1'), -2);
168 +INSERT INTO block_role VALUES (
169 + (SELECT block_id FROM block WHERE name = 'Default_Block_Sample2'), -2);
170 +-- INSERT INTO block_role VALUES (@blockIdSampleRss, -2);
171 Index: modules/block/data/data.default.sq.sql
172 ===================================================================
173 --- modules/block/data/data.default.sq.sql      (revision 0)
174 +++ modules/block/data/data.default.sq.sql      (revision 0)
175 @@ -0,0 +1,45 @@
176 +#module
177 +INSERT INTO module VALUES ({SGL_NEXT_ID}, 1, 'block', 'Blocks', 'Use the ''Blocks'' module to configure the contents of the blocks in the left and right hand columns, or anywhere in a page.', 'block/block', '48/module_block.png', 'Andrey Podshivalov', NULL, 'BSD', 'beta');
178 +
179 +#perms
180 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr', 'Permission to use block manager',
181 + (SELECT max(module_id) FROM module));
182 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_add', 'Permission to add new block',
183 + (SELECT max(module_id) FROM module));
184 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_edit', 'Permission to edit existing block',
185 + (SELECT max(module_id) FROM module));
186 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_delete', 'Permission to remove block',
187 + (SELECT max(module_id) FROM module));
188 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_reorder', 'Permission to reorder blocks',
189 + (SELECT max(module_id) FROM module));
190 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_list', 'Permission to view block listing',
191 + (SELECT max(module_id) FROM module));
192 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_insert', 'Permission to view block listing',
193 + (SELECT max(module_id) FROM module));
194 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'blockmgr_cmd_update', 'Permission to view block listing',
195 + (SELECT max(module_id) FROM module));
196 +
197 +--
198 +-- Dumping data for table block
199 +--
200 +
201 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Navigation_Block_Navigation', 'Admin menu', '', '', 1, 'AdminNav', 1, 0, 'a:9:{s:15:"startParentNode";s:1:"4";s:10:"startLevel";s:1:"0";s:14:"levelsToRender";s:1:"0";s:9:"collapsed";s:1:"1";s:10:"showAlways";s:1:"1";s:12:"cacheEnabled";s:1:"1";s:11:"breadcrumbs";s:1:"0";s:8:"renderer";s:14:"SimpleRenderer";s:8:"template";s:0:"";}');
202 +INSERT INTO block VALUES ({SGL_NEXT_ID}, 'Navigation_Block_Navigation', 'User menu', '', '', 1, 'MainNav', 1, 0, 'a:9:{s:15:"startParentNode";s:1:"2";s:10:"startLevel";s:1:"0";s:14:"levelsToRender";s:1:"0";s:9:"collapsed";s:1:"1";s:10:"showAlways";s:1:"1";s:12:"cacheEnabled";s:1:"1";s:11:"breadcrumbs";s:1:"0";s:8:"renderer";s:14:"SimpleRenderer";s:8:"template";s:0:"";}');
203 +
204 +--
205 +-- Dumping data for table block_assignment
206 +--
207 +
208 +-- admin menu
209 +INSERT INTO block_assignment VALUES ({SGL_NEXT_ID}, 0);
210 +-- user menu
211 +INSERT INTO block_assignment VALUES ({SGL_NEXT_ID}, 0);
212 +
213 +--
214 +-- Dumping data for table block_role
215 +--
216 +
217 +-- admin menu
218 +INSERT INTO block_role VALUES ({SGL_NEXT_ID}, 1);
219 +-- user menu
220 +INSERT INTO block_role VALUES ({SGL_NEXT_ID}, -2);
221 Index: modules/navigation/data/schema.sq.sql
222 ===================================================================
223 --- modules/navigation/data/schema.sq.sql       (revision 0)
224 +++ modules/navigation/data/schema.sq.sql       (revision 0)
225 @@ -0,0 +1,33 @@
226 +/*==============================================================*/
227 +/* Table: section                                               */
228 +/*==============================================================*/
229 +create table section
230 +(
231 +   section_id                     INTEGER PRIMARY KEY,
232 +   title                          varchar(32),
233 +   resource_uri                   varchar(128),
234 +   perms                          varchar(32),
235 +   trans_id                       int,
236 +   parent_id                      int,
237 +   root_id                        int,
238 +   left_id                        int,
239 +   right_id                       int,
240 +   order_id                       int,
241 +   level_id                       int,
242 +   is_enabled                     smallint,
243 +   is_static                      smallint,
244 +   access_key                     char(1)                       default NULL,
245 +   rel                            varchar(16)                   default NULL
246 +);
247 +
248 +/*==============================================================*/
249 +/* Table: uri_alias                                             */
250 +/*==============================================================*/
251 +CREATE TABLE uri_alias (
252 +  uri_alias_id INTEGER PRIMARY KEY ,
253 +  uri_alias varchar(255) default NULL,
254 +  section_id int(11) default NULL,
255 +  title varchar(255) default NULL,
256 +  keywords text,
257 +  description text
258 +) ;
259 Index: modules/navigation/data/data.default.sq.sql
260 ===================================================================
261 --- modules/navigation/data/data.default.sq.sql (revision 0)
262 +++ modules/navigation/data/data.default.sq.sql (revision 0)
263 @@ -0,0 +1,33 @@
264 +INSERT INTO module VALUES ({SGL_NEXT_ID}, 1, 'navigation', 'Navigation', 'The ''Navigation'' module is what you use to build your site navigation, it creates menus that you can customise in terms of look and feel, and allows you to link to any site resource.', 'navigation/page', 'navigation.png', 'Andrey Podshivalov', NULL, 'BSD', 'beta');
265 +
266 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'navstylemgr', '',
267 + (SELECT max(module_id) FROM module));
268 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'navstylemgr_cmd_changeStyle', '',
269 + (SELECT max(module_id) FROM module));
270 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'navstylemgr_cmd_list', '',
271 + (SELECT max(module_id) FROM module));
272 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr', '',
273 + (SELECT max(module_id) FROM module));
274 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_add', '',
275 + (SELECT max(module_id) FROM module));
276 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_insert', '',
277 + (SELECT max(module_id) FROM module));
278 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_edit', '',
279 + (SELECT max(module_id) FROM module));
280 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_update', '',
281 + (SELECT max(module_id) FROM module));
282 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_delete', '',
283 + (SELECT max(module_id) FROM module));
284 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_reorder', '',
285 + (SELECT max(module_id) FROM module));
286 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'sectionmgr_cmd_list', '',
287 + (SELECT max(module_id) FROM module));
288 +
289 +#
290 +# Dumping data for table section
291 +#
292 +
293 +INSERT INTO section VALUES (1, 'root', 'uriEmpty:', '1', 1, 0, 0, 0, 0, 0, 0, 0, 0, '', '');
294 +INSERT INTO section VALUES (2, 'User menu', 'uriEmpty:', '-2', 2, 0, 2, 1, 2, 1, 1, 1, 0, '', '');
295 +INSERT INTO section VALUES (4, 'Admin menu', 'uriEmpty:', '1', 4, 0, 4, 1, 2, 2, 1, 1, 0, '', '');
296 +
297 Index: modules/navigation/blocks/Navigation.php
298 ===================================================================
299 --- modules/navigation/blocks/Navigation.php    (revision 163)
300 +++ modules/navigation/blocks/Navigation.php    (working copy)
301 @@ -89,11 +89,14 @@
302          $nav->setParams($aDefaultParams);
303  
304          //  call navigation renderer
305 +       if (isset($aParams['renderer']))
306          $aNav = $nav->render($aParams['renderer']);
307  
308          //  return $aNav[1] - return rendered navigation menu
309          //  return $aNav[2] - return breadcrumbs
310 -        return $aNav[1];
311 +       if(isset($aNav[1]))
312 +               return $aNav[1];
313 +       else return;
314      }
315  }
316 -?>
317 \ No newline at end of file
318 +?>
319 Index: modules/user/data/schema.sq.sql
320 ===================================================================
321 --- modules/user/data/schema.sq.sql     (revision 0)
322 +++ modules/user/data/schema.sq.sql     (revision 0)
323 @@ -0,0 +1,203 @@
324 +/*==============================================================*/
325 +/* Table: login                                                 */
326 +/*==============================================================*/
327 +create table login
328 +(
329 +   login_id                       INTEGER PRIMARY KEY,
330 +   usr_id                         int,
331 +   date_time                      datetime,
332 +   remote_ip                      varchar(16)
333 +);
334 +
335 +/*==============================================================*/
336 +/* Index: usr_login_fk                                          */
337 +/*==============================================================*/
338 +create index usr_login_fk on login
339 +(
340 +   usr_id
341 +);
342 +
343 +/*==============================================================*/
344 +/* Table: preference                                            */
345 +/*==============================================================*/
346 +create table preference
347 +(
348 +   preference_id                  INTEGER PRIMARY KEY,
349 +   name                           varchar(128),
350 +   default_value                  varchar(128)
351 +);
352 +
353 +/*==============================================================*/
354 +/* Table: organisation                                          */
355 +/*==============================================================*/
356 +CREATE TABLE organisation (
357 +  organisation_id INTEGER PRIMARY KEY,
358 +  parent_id int(11) NOT NULL default '0',
359 +  root_id int(11) NOT NULL default '0',
360 +  left_id int(11) NOT NULL default '0',
361 +  right_id int(11) NOT NULL default '0',
362 +  order_id int(11) NOT NULL default '0',
363 +  level_id int(11) NOT NULL default '0',
364 +  role_id int(11) NOT NULL default '0',
365 +  organisation_type_id int(11) NOT NULL default '0',
366 +  name varchar(128) default NULL,
367 +  description text,
368 +  addr_1 varchar(128) NOT NULL default '',
369 +  addr_2 varchar(128) default NULL,
370 +  addr_3 varchar(128) default NULL,
371 +  city varchar(32) NOT NULL default '',
372 +  region varchar(32) default NULL,
373 +  country char(2) default NULL,
374 +  post_code varchar(16) default NULL,
375 +  telephone varchar(32) default NULL,
376 +  website varchar(128) default NULL,
377 +  email varchar(128) default NULL,
378 +  date_created datetime default NULL,
379 +  created_by int(11) default NULL,
380 +  last_updated datetime default NULL,
381 +  updated_by int(11) default NULL
382 +) ;
383 +
384 +/*==============================================================*/
385 +/* Table: organisation_type                                     */
386 +/*==============================================================*/
387 +CREATE TABLE organisation_type (
388 +  organisation_type_id INTEGER PRIMARY KEY,
389 +  name varchar(64) default NULL
390 +) ;
391 +
392 +/*==============================================================*/
393 +/* Table: permission                                            */
394 +/*==============================================================*/
395 +CREATE TABLE permission (
396 +  permission_id INTEGER PRIMARY KEY,
397 +  name varchar(255) default NULL,
398 +  description text,
399 +  module_id int(11) NOT NULL default '0'
400 +) ;
401 +
402 +/*==============================================================*/
403 +/* Table: role                                                  */
404 +/*==============================================================*/
405 +CREATE TABLE role (
406 +  role_id INTEGER PRIMARY KEY,
407 +  name varchar(255) default NULL,
408 +  description text,
409 +  date_created datetime default NULL,
410 +  created_by int(11) default NULL,
411 +  last_updated datetime default NULL,
412 +  updated_by int(11) default NULL
413 +) ;
414 +
415 +/*==============================================================*/
416 +/* Table: role_permission                                       */
417 +/*==============================================================*/
418 +CREATE TABLE role_permission (
419 +  role_permission_id INTEGER PRIMARY KEY,
420 +  role_id int(11) NOT NULL default '0',
421 +  permission_id int(11) NOT NULL default '0'
422 +) ;
423 +
424 +/*==============================================================*/
425 +/* Table: user_preference                                       */
426 +/*==============================================================*/
427 +create table user_preference
428 +(
429 +   user_preference_id             INTEGER PRIMARY KEY,
430 +   usr_id                         int                            not null,
431 +   preference_id                  int                            not null,
432 +   value                          varchar(128)
433 +);
434 +
435 +/*==============================================================*/
436 +/* Index: usr_user_preference_fk                               */
437 +/*==============================================================*/
438 +create index usr_user_preference_fk on user_preference
439 +(
440 +   usr_id
441 +);
442 +
443 +/*==============================================================*/
444 +/* Index: preference_user_preference_fk                          */
445 +/*==============================================================*/
446 +create index preference_user_preference_fk on user_preference
447 +(
448 +   preference_id
449 +);
450 +
451 +/*==============================================================*/
452 +/* Table: org_preference                                        */
453 +/*==============================================================*/
454 +CREATE TABLE org_preference (
455 +  org_preference_id INTEGER PRIMARY KEY,
456 +  organisation_id int(11) NOT NULL default '0',
457 +  preference_id int(11) NOT NULL default '0',
458 +  value varchar(128) default NULL
459 +) ;
460 +
461 +/*==============================================================*/
462 +/* Index: usr_user_preference_fk                               */
463 +/*==============================================================*/
464 +create index organisation_org_preference_fk on org_preference
465 +(
466 +   organisation_id
467 +);
468 +
469 +/*==============================================================*/
470 +/* Index: preferene_org_preference_fk                          */
471 +/*==============================================================*/
472 +create index preference_org_preference_fk on org_preference
473 +(
474 +   preference_id
475 +);
476 +
477 +
478 +/*==============================================================*/
479 +/* Table: usr                                                   */
480 +/*==============================================================*/
481 +CREATE TABLE usr (
482 +  usr_id INTEGER PRIMARY KEY,
483 +  organisation_id int(11) NULL default '0',
484 +  role_id int(11) NOT NULL default '0',
485 +  username varchar(64) default NULL,
486 +  passwd varchar(32) default NULL,
487 +  first_name varchar(128) default NULL,
488 +  last_name varchar(128) default NULL,
489 +  telephone varchar(16) default NULL,
490 +  mobile varchar(16) default NULL,
491 +  email varchar(128) default NULL,
492 +  addr_1 varchar(128) default NULL,
493 +  addr_2 varchar(128) default NULL,
494 +  addr_3 varchar(128) default NULL,
495 +  city varchar(64) default NULL,
496 +  region varchar(32) default NULL,
497 +  country char(2) default NULL,
498 +  post_code varchar(16) default NULL,
499 +  is_email_public smallint(6) default NULL,
500 +  is_acct_active smallint(6) default NULL,
501 +  security_question smallint(6) default NULL,
502 +  security_answer varchar(128) default NULL,
503 +  date_created datetime default NULL,
504 +  created_by int(11) default NULL,
505 +  last_updated datetime default NULL,
506 +  updated_by int(11) default NULL
507 +) ;
508 +
509 +/* we'll see if dbdo fixes this problem */
510 +-- CREATE UNIQUE INDEX usr_email ON usr (
511 +--    email
512 +-- );
513 +--
514 +-- CREATE UNIQUE INDEX usr_username ON usr (
515 +--    username
516 +-- );
517 +
518 +/*==============================================================*/
519 +/* Table: user_permission                                       */
520 +/*==============================================================*/
521 +CREATE TABLE user_permission (
522 +  user_permission_id INTEGER PRIMARY KEY,
523 +  usr_id int(11) NOT NULL default '0',
524 +  permission_id int(11) NOT NULL default '0'
525 +) ;
526 +
527 Index: modules/user/data/data.default.sq.sql
528 ===================================================================
529 --- modules/user/data/data.default.sq.sql       (revision 0)
530 +++ modules/user/data/data.default.sq.sql       (revision 0)
531 @@ -0,0 +1,264 @@
532 +INSERT INTO module VALUES ({SGL_NEXT_ID}, 1, 'user', 'Users and Security', 'The ''Users and Security'' module allows you to manage all your users, administer the roles they belong to, change their passwords, setup permissions and alter the global default preferences.', 'user/user', '48/module_user.png', 'Demian Turner', NULL, 'BSD', 'beta');
533 +
534 +#
535 +# Dumping data for table `permission`
536 +#
537 +
538 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_duplicate', '',
539 + (SELECT max(module_id) FROM module));
540 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'accountmgr_cmd_edit', '',
541 + (SELECT max(module_id) FROM module));
542 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'accountmgr_cmd_update', '',
543 + (SELECT max(module_id) FROM module));
544 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'accountmgr_cmd_viewProfile', '',
545 + (SELECT max(module_id) FROM module));
546 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'accountmgr_cmd_summary', '',
547 + (SELECT max(module_id) FROM module));
548 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'loginmgr_cmd_login', '',
549 + (SELECT max(module_id) FROM module));
550 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'loginmgr_cmd_list', '',
551 + (SELECT max(module_id) FROM module));
552 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr_cmd_add', '',
553 + (SELECT max(module_id) FROM module));
554 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr_cmd_insert', '',
555 + (SELECT max(module_id) FROM module));
556 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr_cmd_edit', '',
557 + (SELECT max(module_id) FROM module));
558 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr_cmd_update', '',
559 + (SELECT max(module_id) FROM module));
560 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr_cmd_delete', '',
561 + (SELECT max(module_id) FROM module));
562 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr_cmd_list', '',
563 + (SELECT max(module_id) FROM module));
564 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'passwordmgr_cmd_retrieve', '',
565 + (SELECT max(module_id) FROM module));
566 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'passwordmgr_cmd_forgot', '',
567 + (SELECT max(module_id) FROM module));
568 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userpasswordmgr', '',
569 + (SELECT max(module_id) FROM module));
570 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userpasswordmgr_cmd_edit', '',
571 + (SELECT max(module_id) FROM module));
572 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userpasswordmgr_cmd_update', '',
573 + (SELECT max(module_id) FROM module));
574 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_add', '',
575 + (SELECT max(module_id) FROM module));
576 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_insert', '',
577 + (SELECT max(module_id) FROM module));
578 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_edit', '',
579 + (SELECT max(module_id) FROM module));
580 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_update', '',
581 + (SELECT max(module_id) FROM module));
582 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_delete', '',
583 + (SELECT max(module_id) FROM module));
584 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_list', '',
585 + (SELECT max(module_id) FROM module));
586 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr_cmd_add', '',
587 + (SELECT max(module_id) FROM module));
588 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr_cmd_insert', '',
589 + (SELECT max(module_id) FROM module));
590 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr_cmd_edit', '',
591 + (SELECT max(module_id) FROM module));
592 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr_cmd_update', '',
593 + (SELECT max(module_id) FROM module));
594 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr_cmd_delete', '',
595 + (SELECT max(module_id) FROM module));
596 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr_cmd_list', '',
597 + (SELECT max(module_id) FROM module));
598 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'profilemgr_cmd_view', '',
599 + (SELECT max(module_id) FROM module));
600 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'registermgr_cmd_add', '',
601 + (SELECT max(module_id) FROM module));
602 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'registermgr_cmd_insert', '',
603 + (SELECT max(module_id) FROM module));
604 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_add', '',
605 + (SELECT max(module_id) FROM module));
606 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_insert', '',
607 + (SELECT max(module_id) FROM module));
608 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_edit', '',
609 + (SELECT max(module_id) FROM module));
610 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_update', '',
611 + (SELECT max(module_id) FROM module));
612 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_delete', '',
613 + (SELECT max(module_id) FROM module));
614 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_list', '',
615 + (SELECT max(module_id) FROM module));
616 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_editPerms', '',
617 + (SELECT max(module_id) FROM module));
618 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr_cmd_updatePerms', '',
619 + (SELECT max(module_id) FROM module));
620 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_add', '',
621 + (SELECT max(module_id) FROM module));
622 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_insert', '',
623 + (SELECT max(module_id) FROM module));
624 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_edit', '',
625 + (SELECT max(module_id) FROM module));
626 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_update', '',
627 + (SELECT max(module_id) FROM module));
628 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_delete', '',
629 + (SELECT max(module_id) FROM module));
630 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_list', '',
631 + (SELECT max(module_id) FROM module));
632 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_requestPasswordReset', '',
633 + (SELECT max(module_id) FROM module));
634 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_resetPassword', '',
635 + (SELECT max(module_id) FROM module));
636 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_editPerms', '',
637 + (SELECT max(module_id) FROM module));
638 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_updatePerms', '',
639 + (SELECT max(module_id) FROM module));
640 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userpreferencemgr_cmd_editAll', '',
641 + (SELECT max(module_id) FROM module));
642 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userpreferencemgr_cmd_updateAll', '',
643 + (SELECT max(module_id) FROM module));
644 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'accountmgr', '',
645 + (SELECT max(module_id) FROM module));
646 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'loginmgr', '',
647 + (SELECT max(module_id) FROM module));
648 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'loginmgr_cmd_logout', '',
649 + (SELECT max(module_id) FROM module));
650 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgmgr', '',
651 + (SELECT max(module_id) FROM module));
652 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgpreferencemgr', '',
653 + (SELECT max(module_id) FROM module));
654 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgpreferencemgr_cmd_editAll', '',
655 + (SELECT max(module_id) FROM module));
656 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgpreferencemgr_cmd_updateAll', '',
657 + (SELECT max(module_id) FROM module));
658 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'passwordmgr', '',
659 + (SELECT max(module_id) FROM module));
660 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr', '',
661 + (SELECT max(module_id) FROM module));
662 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_scanNew', '',
663 + (SELECT max(module_id) FROM module));
664 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_insertNew', '',
665 + (SELECT max(module_id) FROM module));
666 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_scanOrphaned', '',
667 + (SELECT max(module_id) FROM module));
668 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'permissionmgr_cmd_deleteOrphaned', '',
669 + (SELECT max(module_id) FROM module));
670 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'preferencemgr', '',
671 + (SELECT max(module_id) FROM module));
672 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'profilemgr', '',
673 + (SELECT max(module_id) FROM module));
674 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'registermgr', '',
675 + (SELECT max(module_id) FROM module));
676 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'rolemgr', '',
677 + (SELECT max(module_id) FROM module));
678 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userimportmgr', '',
679 + (SELECT max(module_id) FROM module));
680 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userimportmgr_cmd_list', '',
681 + (SELECT max(module_id) FROM module));
682 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userimportmgr_cmd_insertImportedUsers', '',
683 + (SELECT max(module_id) FROM module));
684 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr', '',
685 + (SELECT max(module_id) FROM module));
686 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_syncToRole', '',
687 + (SELECT max(module_id) FROM module));
688 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'userpreferencemgr', '',
689 + (SELECT max(module_id) FROM module));
690 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr', NULL,
691 + (SELECT max(module_id) FROM module));
692 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr_cmd_add', NULL,
693 + (SELECT max(module_id) FROM module));
694 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr_cmd_insert', NULL,
695 + (SELECT max(module_id) FROM module));
696 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr_cmd_edit', NULL,
697 + (SELECT max(module_id) FROM module));
698 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr_cmd_update', NULL,
699 + (SELECT max(module_id) FROM module));
700 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr_cmd_delete', NULL,
701 + (SELECT max(module_id) FROM module));
702 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'orgtypemgr_cmd_list', NULL,
703 + (SELECT max(module_id) FROM module));
704 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_requestChangeUserStatus', NULL,
705 + (SELECT max(module_id) FROM module));
706 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_changeUserStatus', NULL,
707 + (SELECT max(module_id) FROM module));
708 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_viewLogin', NULL,
709 + (SELECT max(module_id) FROM module));
710 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usermgr_cmd_truncateLoginTbl', NULL,
711 + (SELECT max(module_id) FROM module));
712 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usersearchmgr', NULL,
713 + (SELECT max(module_id) FROM module));
714 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usersearchmgr_cmd_add', NULL,
715 + (SELECT max(module_id) FROM module));
716 +INSERT INTO permission VALUES ({SGL_NEXT_ID}, 'usersearchmgr_cmd_search', NULL,
717 + (SELECT max(module_id) FROM module));
718 +
719 +
720 +
721 +#
722 +# Dumping data for table `preference`
723 +#
724 +
725 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'sessionTimeout', '1800');
726 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'timezone', 'UTC');
727 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'theme', 'default');
728 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'dateFormat', 'UK');
729 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'language', 'en-iso-8859-15');
730 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'resPerPage', '10');
731 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'showExecutionTimes', '1');
732 +INSERT INTO preference VALUES ({SGL_NEXT_ID}, 'locale', 'en_GB');
733 +
734 +#
735 +# Dumping data for table `role`
736 +#
737 +
738 +
739 +INSERT INTO role VALUES (-1,'unassigned','not assigned a role',NULL,NULL,NULL,NULL);
740 +INSERT INTO role VALUES (0,'guest','public user',NULL,NULL,NULL,NULL);
741 +INSERT INTO role VALUES (1,'root','super user',NULL,NULL,NULL,NULL);
742 +INSERT INTO role VALUES (2,'member','has a limited set of privileges',NULL,NULL,NULL,NULL);
743 +
744 +#
745 +# Dumping data for table `role_permission`
746 +#
747 +
748 +#member role perms
749 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
750 + (SELECT permission_id FROM permission WHERE name = 'accountmgr'));
751 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
752 + (SELECT permission_id FROM permission WHERE name = 'accountmgr_cmd_edit'));
753 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
754 + (SELECT permission_id FROM permission WHERE name = 'accountmgr_cmd_summary'));
755 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
756 + (SELECT permission_id FROM permission WHERE name = 'accountmgr_cmd_update'));
757 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
758 + (SELECT permission_id FROM permission WHERE name = 'accountmgr_cmd_viewProfile'));
759 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
760 + (SELECT permission_id FROM permission WHERE name = 'loginmgr'));
761 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
762 + (SELECT permission_id FROM permission WHERE name = 'loginmgr_cmd_list'));
763 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
764 + (SELECT permission_id FROM permission WHERE name = 'loginmgr_cmd_login'));
765 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
766 + (SELECT permission_id FROM permission WHERE name = 'loginmgr_cmd_logout'));
767 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
768 + (SELECT permission_id FROM permission WHERE name = 'userpasswordmgr_cmd_edit'));
769 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
770 + (SELECT permission_id FROM permission WHERE name = 'userpasswordmgr_cmd_update'));
771 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
772 + (SELECT permission_id FROM permission WHERE name = 'preferencemgr_cmd_edit'));
773 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
774 + (SELECT permission_id FROM permission WHERE name = 'preferencemgr_cmd_update'));
775 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
776 + (SELECT permission_id FROM permission WHERE name = 'profilemgr_cmd_view'));
777 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
778 + (SELECT permission_id FROM permission WHERE name = 'registermgr_cmd_add'));
779 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
780 + (SELECT permission_id FROM permission WHERE name = 'registermgr_cmd_insert'));
781 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
782 + (SELECT permission_id FROM permission WHERE name = 'userpreferencemgr_cmd_editAll'));
783 +INSERT INTO role_permission VALUES ({SGL_NEXT_ID}, 2,
784 + (SELECT permission_id FROM permission WHERE name = 'userpreferencemgr_cmd_updateAll'));
785 +
786 +# add 'nobody' user
787 +INSERT INTO usr VALUES (0, 1, 0, 'nobody', '21232f297a57a5a743894a0e4a801fc3', 'Nobody', 'Nobody', '', '', 'none@none.com', 'none', '', '', 'None', '', 'GB', '55555', 0, 0, 1, 'rover', '2003-12-09 18:02:44', 1, '2004-06-10 11:07:27', 1);
788 +
789 +
790 +#
791 +# Dumping data for table `organisation`
792 +#
793 +
794 +#INSERT INTO organisation VALUES (1,0,1,1,2,1,1,2,0,'default org','test','aasdfasdf','','','asdfadf','AL','BJ','55555','325 652 5645','http://example.com','test@test.com','2004-01-12 16:13:21',NULL,'2004-06-23 10:44:52',1);
795 +#INSERT INTO organisation VALUES (2,0,2,1,2,2,1,2,0,'sainsburys','test','aasdfasdf','','','asdfadf','AL','BJ','asdfasdf','325 652 5645','http://sainsburys.com','info@sainsburys.com','2004-01-12 16:13:21',NULL,'2004-06-23 10:44:56',1);
796 Index: modules/user/classes/DA_User.php
797 ===================================================================
798 --- modules/user/classes/DA_User.php    (revision 163)
799 +++ modules/user/classes/DA_User.php    (working copy)
800 @@ -345,7 +345,7 @@
801                          {$this->conf['table']['module']} m
802                  WHERE p.module_id = m.module_id
803                  $filter
804 -                ORDER BY name";
805 +                ORDER BY p.name";
806              $aAllPerms = $this->dbh->getAll($query, DB_FETCHMODE_ASSOC);
807              break;
808