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
|
-- Sample employee database
-- See changelog table for details
-- Copyright (C) 2007,2008, MySQL AB
--
-- Original data created by Fusheng Wang and Carlo Zaniolo
-- http://www.cs.aau.dk/TimeCenter/software.htm
-- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
--
-- Current schema by Giuseppe Maxia
-- Data conversion from XML to relational by Patrick Crews
--
-- This work is licensed under the
-- Creative Commons Attribution-Share Alike 3.0 Unported License.
-- To view a copy of this license, visit
-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to
-- Creative Commons, 171 Second Street, Suite 300, San Francisco,
-- California, 94105, USA.
--
-- DISCLAIMER
-- To the best of our knowledge, this data is fabricated, and
-- it does not correspond to real people.
-- Any similarity to existing people is purely coincidental.
--
USE employees;
SELECT 'TESTING INSTALLATION' as 'INFO';
SET storage_engine=MyISAM;
DROP TABLE IF EXISTS expected_values, found_values;
CREATE TABLE expected_values (
table_name varchar(30) not null primary key,
recs int not null,
crc_sha varchar(100) not null,
crc_md5 varchar(100) not null
) ENGINE=MyISAM;
CREATE TABLE found_values LIKE expected_values;
INSERT INTO `expected_values` VALUES
('employees', 300024,'4d4aa689914d8fd41db7e45c2168e7dcb9697359',
'4ec56ab5ba37218d187cf6ab09ce1aa1'),
('departments', 9,'4b315afa0e35ca6649df897b958345bcb3d2b764',
'd1af5e170d2d1591d776d5638d71fc5f'),
('dept_manager', 24,'9687a7d6f93ca8847388a42a6d8d93982a841c6c',
'8720e2f0853ac9096b689c14664f847e'),
('dept_emp', 331603,'f16f6ce609d032d6b1b34748421e9195c5083da8',
'c2c4fc7f0506e50959a6c67ad55cac31'),
('titles', 443308,'d12d5f746b88f07e69b9e36675b6067abb01b60e',
'bfa016c472df68e70a03facafa1bc0a8'),
('salaries', 2844047,'b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f',
'fd220654e95aea1b169624ffe3fca934');
SELECT table_name, recs AS expected_records, crc_md5 AS expected_crc FROM expected_values;
DROP TABLE IF EXISTS tchecksum;
CREATE TABLE tchecksum (chk char(100)) ENGINE=myisam;
SET @crc= '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc,
emp_no,birth_date,first_name,last_name,gender,hire_date))
FROM employees ORDER BY emp_no;
INSERT INTO found_values VALUES ('employees', (SELECT COUNT(*) FROM employees), @crc,@crc);
TRUNCATE tchecksum; -- if BlackHole is not available
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,dept_name))
FROM departments ORDER BY dept_no;
INSERT INTO found_values values ('departments', (SELECT COUNT(*) FROM departments), @crc,@crc);
TRUNCATE tchecksum;
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date))
FROM dept_manager ORDER BY dept_no,emp_no;
INSERT INTO found_values values ('dept_manager', (SELECT COUNT(*) FROM dept_manager), @crc,@crc);
TRUNCATE tchecksum;
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date))
FROM dept_emp ORDER BY dept_no,emp_no;
INSERT INTO found_values values ('dept_emp', (SELECT COUNT(*) FROM dept_emp), @crc,@crc);
TRUNCATE tchecksum;
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, title, from_date,to_date))
FROM titles order by emp_no,title,from_date;
INSERT INTO found_values values ('titles', (SELECT COUNT(*) FROM titles), @crc,@crc);
TRUNCATE tchecksum;
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, salary, from_date,to_date))
FROM salaries order by emp_no,from_date,to_date;
INSERT INTO found_values values ('salaries', (SELECT COUNT(*) FROM salaries), @crc,@crc);
DROP TABLE tchecksum;
SELECT table_name, recs as 'found_records ', crc_md5 as found_crc from found_values;
SELECT
e.table_name,
IF(e.recs=f.recs,'OK', 'not ok') AS records_match,
IF(e.crc_md5=f.crc_md5,'ok','not ok') AS crc_match
from
expected_values e INNER JOIN found_values f USING (table_name);
DROP TABLE expected_values,found_values;
|