Tests sur les BDD du Serveur DB3
(Master MySQL-Astrad)

Nicolas Bondier, 2011-03-21

Switzernet

TESTS. 1

Opérations sur connexion et client IP. 1

[TEST1] 1

[TEST2] 3

[TEST3] 3

[TEST4] 5

Opérations sur les Nodes. 5

[TEST5] 5

[TEST6] 6

Opérations sur les clients et comptes. 7

[TEST7] 7

Reference: 10

TESTS

Opérations sur connexion et client IP.

[TEST1]

Update de la connexion, on change l’adresse IP de la connexion après avoir créé un nouveau client IP :

Situation initiale:

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  2 | 192.168.1.2 | N       |

|  3 | 192.168.2.1 | Y       |

+----+-------------+---------+

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  3 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

Insertion d’un client IP avec l’adresse 192.168.1.5

INSERT INTO Accounts VALUES (6,'192.168.1.5',NULL,'',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',6,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  2 | 192.168.1.2 | N       |

|  3 | 192.168.2.1 | Y       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

4 rows in set (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

Update de l’adresse ip de la connexion de 192.168.1.2 vers 192.168.1.5

mysql> UPDATE Connections SET remote_ip ='192.168.1.5' where remote_ip='192.168.1.2';

ERROR 1062 (23000): Duplicate entry '192.168.1.5' for key 2       NO ERROR

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  3 | 192.168.1.5 |    THIS IS CORRECT

+----+-------------+

3 rows in set (0.00 sec)

INCORRECT

[TEST2]

Delete et rajout d’une connexion.

On enleve une ligne dans ‘connections’

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  3 | 192.168.1.5 |

+----+-------------+

3 rows in set (0.00 sec)

mysql> DELETE from Connections where remote_ip='192.168.1.5';

Query OK, 1 row affected (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

+----+-------------+

2 rows in set (0.00 sec)

OK

On la rajoute, tout est juste

mysql> INSERT INTO Connections VALUES (2,55,'Test2',NULL,228,'originate','VoIP',NULL,NULL,1,'s/^\\+//;','192.168.1.2',10,0.0000,NULL,'s/^(\\d)/\\+$1/;',NULL,'Y','N','s/^(\\d)/\\+$1/;',2);

Query OK, 1 row affected (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

OK

[TEST3]

Update d’un client ayant une adresse IP de ‘vendors’ ;

Situation initiale:

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  2 | 192.168.1.2 | N       |

|  3 | 192.168.2.1 | Y       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

4 rows in set (0.00 sec)

On change l’IP du vendeur:

mysql> UPDATE Accounts SET id='192.168.1.10' where id='192.168.1.2';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |    --à VOIR PLUS BAS

+----+-------------+   

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.ip_customers;

+----+--------------+---------+

| id | remote_ip    | is_node |

+----+--------------+---------+

|  1 | 192.168.1.1  | N       |

|  2 | 192.168.1.10 | N       |

|  3 | 192.168.2.1  | Y       |

|  4 | 192.168.1.5  | N       |

+----+--------------+---------+

4 rows in set (0.00 sec)

JUSTE ?

Quelle règle appliquer lorsque l’on change l’adresse IP d’un compte de vendeur qui est aussi un client ?

Le changement de l’adresse IP lorsque qu’on effectue la modification sur ‘Account’ change ‘ast-replication’ à ‘ip_customers’, mais doit-il aussi changer automatiquement dans ‘ast-replication’ à ‘vendors’ ?

Le fait de changer l’IP de ce compte, transforme le vendeur/client en simple client IP.

[TEST4]

Suppression d’un client IP ayant une adresse IP de ‘vendors’;

Suppression du client

DELETE from Accounts where id='192.168.1.2';

mysql> DELETE from Accounts where id='192.168.1.2';

Query OK, 1 row affected (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  3 | 192.168.2.1 | Y       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

3 rows in set (0.00 sec)

JUSTE ?

Même question que lors du TEST3, notre client\vendeur est devenu juste un vendeur en effaçant son compte. Faut –il qu’il soit également effacé de la table ‘vendors’ de ast-replication ?

Opérations sur les Nodes.

[TEST5]

Updates des Nodes.

Situation initiale:

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  3 | 192.168.2.1 | Y       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

Update du Node

mysql> UPDATE Nodes SET ip='192.168.2.2' where ip='192.168.2.1';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  3 | 192.168.2.2 | Y       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  1 | 192.168.2.2 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

OK

[TEST6]

Suppression d’un Node

Situation initiale:

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  5 | 192.168.2.1 | Y       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

3 rows in set (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  5 | 192.168.2.1 |

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

3 rows in set (0.00 sec)

Suppression du Node

mysql> select * from `ast-replication`.ip_customers;

+----+-------------+---------+

| id | remote_ip   | is_node |

+----+-------------+---------+

|  1 | 192.168.1.1 | N       |

|  4 | 192.168.1.5 | N       |

+----+-------------+---------+

2 rows in set (0.00 sec)

mysql> select * from `ast-replication`.vendors;

+----+-------------+

| id | remote_ip   |

+----+-------------+

|  2 | 192.168.3.1 |

|  4 | 192.168.1.2 |

+----+-------------+

2 rows in set (0.00 sec)

OK

Opérations sur les clients et comptes

[TEST7]

Modifications de l’i_rep d’un compte

Situation initiale:

mysql> select * from astrad.sipusers;

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

| id | name        | context     | md5secret                        | nat  | deny                                |

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

|  1 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  2 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  3 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  4 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

4 rows in set (0.00 sec)

Insertion d’un nouveau client:

mysql> INSERT INTO Customers VALUES (2,NULL,'Swiss offices 2','0000-00-00 00:00:00',"1314.41000",'CHF','','','','','','','','','','','','','','','','','','','','','billing@switzernet.com','billing@switzernet.com','S','41215500330','cl9alero',1,1,NULL,96,"2220.00000",20613,1,160,NULL,0,'N','N',5,'N',NULL,'2011-01-03 03:44:36','YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',0,NULL,NULL,'N','Y',136,"0.00000","0.00000",NULL,'en','local_to_e164($_,{cc=>\'41\',dp=>\'0\',ip=>\'00\'});#% cc=41 dp=0 ip=00',NULL,'80%','N','2011-03-09 15:00:00',NULL,NULL,NULL,'2008-12-24 08:52:10',NULL,1,'NNNNNNN',1,NULL,"0.00000",'O');

Query OK, 1 row affected (0.00 sec)

Insertion d’un nouveau compte pour le nouveau client:

mysql> INSERT INTO Accounts VALUES (9,'41215500005',NULL,'PASSWORD5',"253.06000","0.00000",'2009-09-08',NULL,2,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',9,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');

mysql> select * from astrad.sipusers;

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

| id | name        | context     | md5secret                        | nat  | deny                                |

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

|  1 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  2 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  3 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  4 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  5 | 41215500005 | fromaccount | 67d6f6801e5c2ae06b91ec95766660e4 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

5 rows in set (0.00 sec)

OK

Changements de i_rep=5 à i_rep=10 pour effacer le compte client.

mysql> UPDATE Accounts SET i_rep='10' where i_customer=2;

ERROR 1054 (42S22): Unknown column 'i_rep' in 'field list'

mysql> UPDATE Customers SET i_rep='10' where i_customer=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from astrad.sipusers;

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

| id | name        | context     | md5secret                        | nat  | deny                                |

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

|  1 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  2 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  3 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

|  4 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes  | 192.168.1.1;192.168.1.5;192.168.2.1 |

+----+-------------+-------------+----------------------------------+------+-------------------------------------+

4 rows in set (0.00 sec)

OK

On remet i_rep à 5.

mysql> UPDATE Customers SET i_rep='5' where i_customer=2;

mysql> select * from astrad.sipusers;

+----+-------------+-------------+----------------------------------+------+--------------------------------

| id | name        | context     | md5secret                        | nat  | deny

+----+-------------+-------------+----------------------------------+------+--------------------------------

|  1 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes  | 192.168.1.1;192.168.1.5;192.168

|  2 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes  | 192.168.1.1;192.168.1.5;192.168

|  3 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes  | 192.168.1.1;192.168.1.5;192.168

|  4 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes  | 192.168.1.1;192.168.1.5;192.168

|  6 | 41215500005 | fromaccount | 67d6f6801e5c2ae06b91ec95766660e4 | yes  | 192.168.1.1;192.168.1.5;192.168

+----+-------------+-------------+----------------------------------+------+--------------------------------

5 rows in set (0.00 sec)

OK

Reference:

http://switzernet.com/3/public/110316-db3-setup/

http://switzernet.com/3/public/110317-db3-versions/

http://switzernet.com/3/public/110316-astrad-triggers/

http://switzernet.com/3/public/110314-astrad-triggers/

http://switzernet.com/3/public/110302-test-asterisk-mysql/

http://switzernet.com/3/public/110315-astrad-triggers-tests/

http://switzernet.com/3/public/110316-astrad-call-tests/

*  *  *