forked from tango-controls/TangoDatabase
-
Notifications
You must be signed in to change notification settings - Fork 0
/
update_db8.sql.in
130 lines (107 loc) · 4.45 KB
/
update_db8.sql.in
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
USE @TANGO_DB_NAME@;
#
# Table structure for table 'property_pipe_class'
#
CREATE TABLE IF NOT EXISTS property_pipe_class (
class varchar(255) NOT NULL default '',
pipe varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
count int(11) NOT NULL default '0',
value text default NULL,
updated timestamp NOT NULL,
accessed timestamp NOT NULL,
comment text,
KEY index_property_pipe_class (class(64),pipe(64),name(64),count)
) ENGINE=MyISAM;
#
# Table structure for table 'property_pipe_device'
#
CREATE TABLE IF NOT EXISTS property_pipe_device (
device varchar(255) NOT NULL default '',
pipe varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
count int(11) NOT NULL default '0',
value text default NULL,
updated timestamp NOT NULL,
accessed timestamp NOT NULL,
comment text,
KEY index_property_pipe_device (device(64),pipe(64),name(64),count)
) ENGINE=MyISAM;
#
# For history ID
#
CREATE TABLE IF NOT EXISTS device_pipe_history_id (
id bigint unsigned NOT NULL default '0'
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS class_pipe_history_id (
id bigint unsigned NOT NULL default '0'
) ENGINE=MyISAM;
#
# History tables
#
CREATE TABLE IF NOT EXISTS property_pipe_class_hist (
id bigint unsigned NOT NULL default '0',
date timestamp NOT NULL,
class varchar(255) NOT NULL default '',
pipe varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
count int(11) NOT NULL default '0',
value text,
KEY index_id (id),
KEY index_class (class),
KEY index_pipe (pipe),
KEY index_name (name)
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS property_pipe_device_hist (
id bigint unsigned NOT NULL default '0',
date timestamp NOT NULL,
device varchar(255) NOT NULL default '',
pipe varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
count int(11) NOT NULL default '0',
value text,
KEY index_id (id),
KEY index_device (device),
KEY index_pipe (pipe),
KEY index_name (name)
) ENGINE=MyISAM;
#
# Load the new stored procedures
#
source stored_proc.sql
#
# Init new history ID
#
CALL init_history_ids();
#
# Update history id columns to support id on more than 32 bits
#
ALTER TABLE property_attribute_device_hist MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE property_attribute_class_hist MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE property_class_hist MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE property_device_hist MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE property_hist MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE class_attribute_history_id MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE class_history_id MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE device_attribute_history_id MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE device_history_id MODIFY id bigint unsigned NOT NULL default '0';
ALTER TABLE object_history_id MODIFY id bigint unsigned NOT NULL default '0';
#
# Update entries in the property_class tables for database
#
DELETE FROM property_class WHERE class='Database' AND count >= 35;
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',35,'DbGetDeviceClassList',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',36,'DbGetDeviceExportedList',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',37,'DbGetHostServerList',NOW(),NoW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',38,'DbGetAttributeAlias2',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',39,'DbGetAliasAttribute',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',40,'DbGetClassPipeProperty',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',41,'DbGetDevicePipeProperty',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',42,'DbGetClassPipeList',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',43,'DbGetDevicePipeList',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',44,'DbGetAttributeAliasList',NOW(),NOW(),NULL);
INSERT INTO property_class VALUES('Database','AllowedAccessCmd',45,'DbGetForwardedAttributeListForDevice',NOW(),NOW(),NULL);
#
# Remove history entries for the property used by memorized attribute
#
DELETE FROM property_attribute_device_hist WHERE count=1 AND name='__value';