39
39
CREATE TABLE system (
40
40
id SERIAL NOT NULL PRIMARY KEY,
41
model CHARACTER VARYING(80) NOT NULL,
41
42
make CHARACTER VARYING(80),
42
model CHARACTER VARYING(80),
43
43
counter INTEGER DEFAULT 0,
44
44
text_search_vector tsvector)
61
61
CREATE TABLE systemunit (
62
62
id SERIAL NOT NULL PRIMARY KEY,
63
date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
64
DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
63
65
sequence INTEGER NOT NULL,
64
66
system INTEGER NOT NULL,
65
67
identifiers TEXT[] NOT NULL)
68
GRANT SELECT, INSERT, DELETE
70
GRANT SELECT, INSERT, UPDATE, DELETE
69
71
ON TABLE systemunit
83
85
ON systemunit (sequence, system)
86
CREATE TABLE processorplatform (
87
id SERIAL NOT NULL PRIMARY KEY,
88
name CHARACTER VARYING(16) NOT NULL UNIQUE)
91
GRANT SELECT, INSERT, DELETE
92
ON TABLE processorplatform
97
ON TABLE processorplatform_id_seq
101
CREATE TABLE processor (
102
id SERIAL NOT NULL PRIMARY KEY,
103
platform INTEGER NOT NULL,
104
type CHARACTER VARYING(16) NOT NULL,
105
model CHARACTER VARYING(48) NOT NULL,
106
model_number CHARACTER VARYING(8) NOT NULL,
107
model_version CHARACTER VARYING(8) NOT NULL,
108
model_revision CHARACTER VARYING(8) NOT NULL,
109
cache INTEGER NOT NULL,
110
bogomips INTEGER NOT NULL,
111
speed INTEGER NOT NULL,
113
UNIQUE(platform, type, model, model_number, model_version,
114
model_revision, cache, bogomips, speed, other))
117
GRANT SELECT, INSERT, DELETE
123
ON TABLE processor_id_seq
127
88
CREATE TABLE devicebus (
128
89
id SERIAL NOT NULL PRIMARY KEY,
129
90
name CHARACTER VARYING(16) NOT NULL UNIQUE,
314
275
WHERE id=OLD.id AND date_deleted='infinity';)
278
CREATE TABLE processorplatform (
279
id SERIAL NOT NULL PRIMARY KEY,
280
name CHARACTER VARYING(16) NOT NULL UNIQUE)
283
GRANT SELECT, INSERT, DELETE
284
ON TABLE processorplatform
289
ON TABLE processorplatform_id_seq
293
CREATE TABLE processor (
294
id SERIAL NOT NULL PRIMARY KEY,
295
platform INTEGER NOT NULL,
296
make CHARACTER VARYING(16) NOT NULL,
297
model CHARACTER VARYING(48) NOT NULL,
298
model_number CHARACTER VARYING(8) NOT NULL,
299
model_version CHARACTER VARYING(8) NOT NULL,
300
model_revision CHARACTER VARYING(8) NOT NULL,
301
cache INTEGER NOT NULL,
303
UNIQUE(platform, make, model, model_number, model_version,
304
model_revision, cache, other))
307
GRANT SELECT, INSERT, DELETE
313
ON TABLE processor_id_seq
317
CREATE TABLE processorstate (
318
id SERIAL NOT NULL PRIMARY KEY,
319
system_unit INTEGER NOT NULL,
320
processor INTEGER NOT NULL,
321
bogomips INTEGER NOT NULL,
322
speed INTEGER NOT NULL,
323
count INTEGER NOT NULL,
324
date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
325
DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
326
date_deleted TIMESTAMP WITHOUT TIME ZONE NOT NULL
327
DEFAULT '3000-01-01')
330
GRANT SELECT, INSERT, UPDATE, DELETE
331
ON TABLE processorstate
336
ON TABLE processorstate_id_seq
317
340
CREATE LANGUAGE plpythonu
423
446
EXECUTE PROCEDURE systemunit_insert_before()
449
CREATE FUNCTION systemunit_insert_after() RETURNS TRIGGER
453
UPDATE devicestate SET date_deleted = NEW.date_created
454
WHERE date_deleted = '3000-01-01'
455
AND system_unit = NEW.id;
456
UPDATE processorstate SET date_deleted = NEW.date_created
457
WHERE date_deleted = '3000-01-01'
458
AND system_unit = NEW.id;
466
ON FUNCTION systemunit_insert_after()
470
CREATE TRIGGER systemunit_insert_after_trigger
471
BEFORE INSERT ON systemunit
473
EXECUTE PROCEDURE systemunit_insert_after()
426
476
CREATE FUNCTION get_device_bus(bus_name TEXT) RETURNS INTEGER
427
477
LANGUAGE plpgsql AS
901
951
FOR EACH ROW EXECUTE PROCEDURE devicestate_update()
954
CREATE FUNCTION processorstate_update() RETURNS TRIGGER
958
IF NOT OLD.date_deleted = 'infinity' THEN -- can be non-infinity
959
RETURN NULL; -- no update
961
IF NEW.date_deleted = 'infinity' THEN
962
INSERT INTO processorstate VALUES (
969
OLD.date_created, now());
970
NEW.date_created = now();
978
ON FUNCTION processorstate_update()
982
CREATE TRIGGER processorstate_update BEFORE UPDATE ON processorstate
983
FOR EACH ROW EXECUTE PROCEDURE processorstate_update()
904
986
CREATE FUNCTION system_make_model() RETURNS TRIGGER
905
987
LANGUAGE plpgsql AS
990
1072
REFERENCES processorplatform(id) ON DELETE CASCADE
1075
ALTER TABLE ONLY processorstate
1076
ADD CONSTRAINT processorstate__processor__fk
1077
FOREIGN KEY (processor)
1078
REFERENCES processor(id) ON DELETE CASCADE
1081
ALTER TABLE ONLY processorstate
1082
ADD CONSTRAINT processorstate__system_unit__fk
1083
FOREIGN KEY (system_unit)
1084
REFERENCES systemunit(id) ON DELETE CASCADE
993
1087
ALTER TABLE ONLY systemunit
994
1088
ADD CONSTRAINT systemunit__system__fk
995
1089
FOREIGN KEY (system)
1095
"DROP FUNCTION system_make_model()",
1096
"DROP FUNCTION processorstate_update()",
1001
1097
"DROP FUNCTION devicestate_update()",
1002
1098
"DROP FUNCTION deviceproduct_product()",
1003
1099
"DROP FUNCTION devicevendor_vendor()",
1012
1108
"DROP FUNCTION get_device_category(TEXT)",
1013
1109
"DROP FUNCTION get_device_bus(TEXT)",
1014
1110
"DROP LANGUAGE plpgsql",
1111
"DROP TABLE processorstate",
1112
"DROP TABLE processor",
1113
"DROP TABLE processorplatform",
1015
1114
"DROP TABLE devicestate",
1016
1115
"DROP TABLE device",
1017
1116
"DROP TABLE deviceproduct",
1019
1118
"DROP TABLE devicedriver",
1020
1119
"DROP TABLE devicecategory",
1021
1120
"DROP TABLE devicebus",
1022
"DROP TABLE processor",
1023
"DROP TABLE processorplatform",
1024
1121
"DROP TABLE systemunit",
1025
1122
"DROP TABLE system",
1126
"DELETE FROM processorstate",
1127
"DELETE FROM processor",
1128
"DELETE FROM processorplatform",
1029
1129
"DELETE FROM devicestate",
1030
1130
"DELETE FROM device",
1031
1131
"DELETE FROM deviceproduct",
1033
1133
"DELETE FROM devicedriver",
1034
1134
"DELETE FROM devicecategory",
1035
1135
"DELETE FROM devicebus",
1036
"DELETE FROM processor",
1037
"DELETE FROM processorplatform",
1038
1136
"DELETE FROM systemunit",
1039
1137
"DELETE FROM system",