Amazon

2015年3月7日土曜日

データベースのdiff(その2)

schemacrawlerを使うとデータベースのスキーマやデータをdiffできるテキストで出力してくれます。
mysqlにscottデータベースを作り、mysqldumpでコピーした結果を確認してみます。

mysqldumpでテーブルとデータを一式コピーします。
vagrant@trusty64:~/work/talend$ mysqldump -h 192.168.10.10 -u scott -ptiger scott | mysql -h 192.168.10.10 -u scott -ptiger sample
vagrant@trusty64:~/work/talend$ mysql -h 192.168.10.10 -u scott -ptiger sample
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 213
Server version: 5.5.41-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| bonus            |
| dept             |
| emp              |
| salgrade         |
+------------------+
4 rows in set (0.00 sec)

mysql> 

スキーマ名で修飾されているテーブル名で差がでますが、定義やデータに差がないことがわかります。
vagrant@trusty64:~/work/sc/schemacrawler-12.04.02-main/_schemacrawler$ diff -u <(./sc.sh --server=mysql -url=jdbc:mysql://192.168.10.10/scott -u=scott -password=tiger -c=details,dump -infolevel=maximum -schemas=scott -tables=scott.emp -tabletype=TABLE) <(./sc.sh --server=mysql -url=jdbc:mysql://192.168.10.10/scott -u=scott -password=tiger -c=details,dump -infolevel=maximum -schemas=sample -tables=sample.emp -tabletype=TABLE)
--- /dev/fd/63  2015-03-07 23:58:06.998866552 +0900
+++ /dev/fd/62  2015-03-07 23:58:06.998866552 +0900
@@ -1523,7 +1523,7 @@
 
 
 
-scott.emp
+sample.emp
 ------------------------------------------------------------------------
                                                                  [table]
   empno                             DECIMAL(4, 0) NOT NULL      
@@ -1539,7 +1539,7 @@
   empno                             ascending                   
 
 emp_ibfk_1                                 [foreign key, with no action]
-  deptno --> scott.dept.deptno                                  
+  deptno --> sample.dept.deptno                                 
 
 deptno                                                [non-unique index]
   deptno                            ascending                   
@@ -1552,7 +1552,7 @@
 
 
 
-scott.emp
+sample.emp
 ------------------------------------------------------------------------
 empno  ename   job     mgr     hiredate        sal     comm    deptno
 7369   smith   clerk   7902    1980-12-17      800.00  NULL    20
vagrant@trusty64:~/work/sc/schemacrawler-12.04.02-main/_schemacrawler$ 
データをちょっと変えてみます。
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | turner | salesman  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | adams  | clerk     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> update emp set sal=1000 where ename='smith';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp where ename='smith';
+-------+-------+-------+------+------------+---------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-------+------+------------+---------+------+--------+
|  7369 | smith | clerk | 7902 | 1980-12-17 | 1000.00 | NULL |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

mysql> 
変更が差分に出ます。
vagrant@trusty64:~/work/sc/schemacrawler-12.04.02-main/_schemacrawler$ diff -u <(./sc.sh --server=mysql -url=jdbc:mysql://192.168.10.10/scott -u=scott -password=tiger -c=details,dump -infolevel=maximum -schemas=scott -tables=scott.emp -tabletype=TABLE) <(./sc.sh --server=mysql -url=jdbc:mysql://192.168.10.10/scott -u=scott -password=tiger -c=details,dump -infolevel=maximum -schemas=sample -tables=sample.emp -tabletype=TABLE)
--- /dev/fd/63  2015-03-07 23:56:30.634866394 +0900
+++ /dev/fd/62  2015-03-07 23:56:30.634866394 +0900
@@ -1523,7 +1523,7 @@
 
 
 
-scott.emp
+sample.emp
 ------------------------------------------------------------------------
                                                                  [table]
   empno                             DECIMAL(4, 0) NOT NULL      
@@ -1539,7 +1539,7 @@
   empno                             ascending                   
 
 emp_ibfk_1                                 [foreign key, with no action]
-  deptno --> scott.dept.deptno                                  
+  deptno --> sample.dept.deptno                                 
 
 deptno                                                [non-unique index]
   deptno                            ascending                   
@@ -1552,10 +1552,10 @@
 
 
 
-scott.emp
+sample.emp
 ------------------------------------------------------------------------
 empno  ename   job     mgr     hiredate        sal     comm    deptno
-7369   smith   clerk   7902    1980-12-17      800.00  NULL    20
+7369   smith   clerk   7902    1980-12-17      1000.00 NULL    20
 7499   allen   salesman        7698    1981-02-20      1600.00 300.00  30
 7521   ward    salesman        7698    1981-02-22      1250.00 500.00  30
 7566   jones   manager 7839    1981-04-02      2975.00 NULL    20
vagrant@trusty64:~/work/sc/schemacrawler-12.04.02-main/_schemacrawler$ 

0 件のコメント: