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
|
--disable_warnings
DROP TABLE IF EXISTS graph_base;
DROP TABLE IF EXISTS graph;
--enable_warnings
CREATE TABLE graph_base (
from_id INT UNSIGNED NOT NULL,
to_id INT UNSIGNED NOT NULL,
PRIMARY KEY (from_id,to_id),
INDEX (to_id)
) ENGINE=MyISAM;
# Backwards compatibility test
# First we ensure the scaffolding is disabled (default situation)
# and check we cant create a table with an integer latch
# Assume this is the default, so don't explicity set false yet:
# SET GLOBAL oqgraph_allow_create_integer_latch=false;
--echo The next error 140 + 1005 is expected
--error 140
--error 1005
CREATE TABLE graph (
latch SMALLINT UNSIGNED NULL,
origid BIGINT UNSIGNED NULL,
destid BIGINT UNSIGNED NULL,
weight DOUBLE NULL,
seq BIGINT UNSIGNED NULL,
linkid BIGINT UNSIGNED NULL,
KEY (latch, origid, destid) USING HASH,
KEY (latch, destid, origid) USING HASH
) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';
# Here we enable scaffolding to let us create a deprecated table
# so we can check that the new code will still allow queries to be performed
# on a legacy database
# It should still generate a warning (1287) - but I don't know how to test for that
#
# latch SMALLINT UNSIGNED NULL' is deprecated and will be removed in a future
# release. Please use 'latch VARCHAR(32) NULL' instead
#
SET GLOBAL oqgraph_allow_create_integer_latch=true;
--echo The next warning 1287 is expected
CREATE TABLE graph (
latch SMALLINT UNSIGNED NULL,
origid BIGINT UNSIGNED NULL,
destid BIGINT UNSIGNED NULL,
weight DOUBLE NULL,
seq BIGINT UNSIGNED NULL,
linkid BIGINT UNSIGNED NULL,
KEY (latch, origid, destid) USING HASH,
KEY (latch, destid, origid) USING HASH
) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';
# Prevent further tables being create this way again
# and make sure the set is effective ...
SET GLOBAL oqgraph_allow_create_integer_latch=false;
--echo The next error 140 + 1005 is expected
--error 140
--error 1005
CREATE TABLE graph_again (
latch SMALLINT UNSIGNED NULL,
origid BIGINT UNSIGNED NULL,
destid BIGINT UNSIGNED NULL,
weight DOUBLE NULL,
seq BIGINT UNSIGNED NULL,
linkid BIGINT UNSIGNED NULL,
KEY (latch, origid, destid) USING HASH,
KEY (latch, destid, origid) USING HASH
) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';
# Regression test expected v2 behaviour in this situation
--echo # Populating base table
INSERT INTO graph_base(from_id, to_id) VALUES (1,2), (2,1);
INSERT INTO graph_base(from_id, to_id) VALUES (1,3), (3,1);
INSERT INTO graph_base(from_id, to_id) VALUES (3,4), (4,3);
INSERT INTO graph_base(from_id, to_id) VALUES (5,6), (6,5);
--echo # Exercising latch==2
SELECT * FROM graph WHERE latch = 2 AND origid = 1 AND weight = 1;
SELECT * FROM graph WHERE latch = 2 AND origid = 1 AND weight = 2;
SELECT * FROM graph WHERE latch = 2 AND origid = 1 AND (weight = 1 OR weight = 2);
--echo # Exercising latch==1
SELECT * FROM graph WHERE latch=1 AND origid=1 AND destid=6;
SELECT * FROM graph WHERE latch=1 AND origid=1 AND destid=4;
SELECT * FROM graph WHERE latch=1 AND origid=4 AND destid=1;
SELECT * FROM graph WHERE latch=0 and destid=2 and origid=1;
--echo # Adding new row to base table
INSERT INTO graph_base (from_id,to_id) VALUES (4,6);
--echo # Deleting rows from base table
DELETE FROM graph_base WHERE from_id=5;
DELETE FROM graph_base WHERE from_id=3 AND to_id=5;
--echo # Execising latch==1 on new data
SELECT * FROM graph WHERE latch=1 AND origid=1 AND destid=6;
SELECT * FROM graph WHERE latch=1 AND origid=6 AND destid=1;
# FIXME - if the following DROPs are missing then mysql will segfault on exit
# indicating an ordering dependency on destruction somewhere...
DROP TABLE IF EXISTS graph;
DROP TABLE IF EXISTS graph_base;
|
