View | Details | Raw Unified | Return to bug 8628
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl (-30 / +40 lines)
Lines 1-36 Link Here
1
$DBversion = 'XXX';
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do(q{DROP TABLE IF EXISTS sign_streams });
3
    if( TableExists('sign_streams') ){
4
    $dbh->do(q{DROP TABLE IF EXISTS signs });
4
        $dbh->do(q{ ALTER TABLE sign_streams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci });
5
    $dbh->do(q{CREATE TABLE sign_streams (
5
    } else {
6
                sign_stream_id int(11) NOT NULL auto_increment,    -- primary key, used to identify streams
6
        $dbh->do(q{ CREATE TABLE sign_streams (
7
                saved_sql_id int(11) NOT NULL,              -- foreign key from the saved_sql table
7
                    sign_stream_id int(11) NOT NULL auto_increment,    -- primary key, used to identify streams
8
                name varchar(64),                           -- name/title of the sign
8
                    saved_sql_id int(11) NOT NULL,              -- foreign key from the saved_sql table
9
                PRIMARY KEY (sign_stream_id),
9
                    name varchar(64),                           -- name/title of the sign
10
                CONSTRAINT sign_streams_ibfk_1 FOREIGN KEY (saved_sql_id) REFERENCES saved_sql (id) ON DELETE CASCADE
10
                    PRIMARY KEY (sign_stream_id),
11
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8 });
11
                    CONSTRAINT sign_streams_ibfk_1 FOREIGN KEY (saved_sql_id) REFERENCES saved_sql (id) ON DELETE CASCADE
12
    $dbh->do(q{CREATE TABLE signs (
12
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci });
13
                sign_id int(11) NOT NULL auto_increment,     -- primary key, used to identify signs
13
    }
14
                name varchar(64),                            -- name/title of the deck
15
                webapp int(1) NOT NULL DEFAULT 0,            -- display as web app or normal page
16
                transition char(16) NOT NULL DEFAULT 'none', -- transition to use between pages, must be one of the transitions defined by jQuery Mobile (see their docs)
17
                swatch char(1) NOT NULL DEFAULT '',          -- swatches determine the colors of page elements
18
                idleafter int(11) NOT NULL default 0,        -- seconds to wait before automatic page turning cicks in
19
                pagedelay int(11) NOT NULL default 0,        -- seconds to wait before turning to a new page/record
20
                reloadafter int(11) NOT NULL default 0,      -- seconds to wait before reloading all the pages
21
                PRIMARY KEY (sign_id)
22
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8 });
23
14
24
    $dbh->do(q{DROP TABLE IF EXISTS signs_to_streams });
15
    if( TableExists('signs') ){
25
    $dbh->do(q{CREATE TABLE signs_to_streams (
16
        $dbh->do(q{ ALTER TABLE signs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci });
26
                sign_to_stream_id int(11) NOT NULL auto_increment, -- primary key, used to identify connections between signs and streams (the same stream can be attached to a sign more than once, with different parameters)
17
    } else {
27
                sign_stream_id int(11) NOT NULL,                   -- foreign key from the decks sign_streams table
18
        $dbh->do(q{ CREATE TABLE signs (
28
                sign_id int(11) NOT NULL,                          -- foreign key from the signs table
19
                    sign_id int(11) NOT NULL auto_increment,     -- primary key, used to identify signs
29
                params varchar(255) NOT NULL DEFAULT '',           -- list of parameters for the SQL associated with the sign_stream
20
                    name varchar(64),                            -- name/title of the deck
30
                PRIMARY KEY (sign_to_stream_id),
21
                    webapp int(1) NOT NULL DEFAULT 0,            -- display as web app or normal page
31
                CONSTRAINT signs_to_streams_ibfk_1 FOREIGN KEY (sign_stream_id) REFERENCES sign_streams (sign_stream_id) ON DELETE CASCADE,
22
                    transition char(16) NOT NULL DEFAULT 'none', -- transition to use between pages, must be one of the transitions defined by jQuery Mobile (see their docs)
32
                CONSTRAINT signs_to_streams_ibfk_2 FOREIGN KEY (sign_id) REFERENCES signs (sign_id) ON DELETE CASCADE
23
                    swatch char(1) NOT NULL DEFAULT '',          -- swatches determine the colors of page elements
33
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8 });
24
                    idleafter int(11) NOT NULL default 0,        -- seconds to wait before automatic page turning cicks in
25
                    pagedelay int(11) NOT NULL default 0,        -- seconds to wait before turning to a new page/record
26
                    reloadafter int(11) NOT NULL default 0,      -- seconds to wait before reloading all the pages
27
                    PRIMARY KEY (sign_id)
28
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci });
29
    }
30
31
    if( TableExists('signs_to_streams') ){
32
        $dbh->do(q{ ALTER TABLE signs_to_streams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci });
33
    } else {
34
        $dbh->do(q{ CREATE TABLE signs_to_streams (
35
                    sign_to_stream_id int(11) NOT NULL auto_increment, -- primary key, used to identify connections between signs and streams (the same stream can be attached to a sign more than once, with different parameters)
36
                    sign_stream_id int(11) NOT NULL,                   -- foreign key from the decks sign_streams table
37
                    sign_id int(11) NOT NULL,                          -- foreign key from the signs table
38
                    params varchar(255) NOT NULL DEFAULT '',           -- list of parameters for the SQL associated with the sign_stream
39
                    PRIMARY KEY (sign_to_stream_id),
40
                    CONSTRAINT signs_to_streams_ibfk_1 FOREIGN KEY (sign_stream_id) REFERENCES sign_streams (sign_stream_id) ON DELETE CASCADE,
41
                    CONSTRAINT signs_to_streams_ibfk_2 FOREIGN KEY (sign_id) REFERENCES signs (sign_id) ON DELETE CASCADE
42
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci });
43
    }
34
44
35
    SetVersion( $DBversion );
45
    SetVersion( $DBversion );
36
    print "Upgrade to $DBversion done (Bug 8628 - add signs, sign_streams and signs_to_streams tables)\n";
46
    print "Upgrade to $DBversion done (Bug 8628 - add signs, sign_streams and signs_to_streams tables)\n";
(-)a/installer/data/mysql/kohastructure.sql (-4 / +3 lines)
Lines 5337-5343 CREATE TABLE sign_streams ( Link Here
5337
  `name` varchar(64), -- name/title of the sign
5337
  `name` varchar(64), -- name/title of the sign
5338
  PRIMARY KEY (`sign_stream_id`),
5338
  PRIMARY KEY (`sign_stream_id`),
5339
  CONSTRAINT `sign_streams_ibfk_1` FOREIGN KEY (`saved_sql_id`) REFERENCES `saved_sql` (`id`) ON DELETE CASCADE
5339
  CONSTRAINT `sign_streams_ibfk_1` FOREIGN KEY (`saved_sql_id`) REFERENCES `saved_sql` (`id`) ON DELETE CASCADE
5340
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
5340
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5341
5341
5342
--
5342
--
5343
-- Table structure for table 'signs'
5343
-- Table structure for table 'signs'
Lines 5354-5360 CREATE TABLE signs ( Link Here
5354
  `pagedelay` int(11) NOT NULL default 0, -- seconds to wait before turning to a new page/record
5354
  `pagedelay` int(11) NOT NULL default 0, -- seconds to wait before turning to a new page/record
5355
  `reloadafter` int(11) NOT NULL default 0, -- seconds to wait before reloading all the pages
5355
  `reloadafter` int(11) NOT NULL default 0, -- seconds to wait before reloading all the pages
5356
  PRIMARY KEY (`sign_id`)
5356
  PRIMARY KEY (`sign_id`)
5357
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
5357
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5358
5358
5359
--
5359
--
5360
-- Table structure for table 'signs_to_streams'
5360
-- Table structure for table 'signs_to_streams'
Lines 5369-5375 CREATE TABLE signs_to_streams ( Link Here
5369
  PRIMARY KEY (`sign_to_stream_id`),
5369
  PRIMARY KEY (`sign_to_stream_id`),
5370
  CONSTRAINT `signs_to_streams_ibfk_1` FOREIGN KEY (`sign_stream_id`) REFERENCES `sign_streams` (`sign_stream_id`) ON DELETE CASCADE,
5370
  CONSTRAINT `signs_to_streams_ibfk_1` FOREIGN KEY (`sign_stream_id`) REFERENCES `sign_streams` (`sign_stream_id`) ON DELETE CASCADE,
5371
  CONSTRAINT `signs_to_streams_ibfk_2` FOREIGN KEY (`sign_id`) REFERENCES `signs` (`sign_id`) ON DELETE CASCADE
5371
  CONSTRAINT `signs_to_streams_ibfk_2` FOREIGN KEY (`sign_id`) REFERENCES `signs` (`sign_id`) ON DELETE CASCADE
5372
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
5372
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5373
5373
5374
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
5374
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
5375
5375
5376
- 

Return to bug 8628