Preparing unified
databases of MR24 and MR18 for Astrad servers
Created on 2012-03-30
by Nicolas Bondier
Switzernet© 2012
Table of Contents
Replication
table on MySQL instance n°1
Replication
table on MySQL instance n°2
Federated
tables for accessing the mixed database.
Federated
table on MySQL instance n°1
Federated
table on MySQL instance n°2
Triggers
filling federated tables
Triggers
on MySQL instance n°2
Unified
porta-billing database
Introduction
This document
describes the development of a unified database of the old and new versions of
porta-billing and the new versions of DBAs and Astrads servers able to
communicate with the two mysql/radius servers. These databases are based on the
previous version of the databases used for propagation of the authentication
data [111019 i] and its updates [110317 i].
The two billing (MR18 and MR24) servers have same databases, porta-sip
and porta-billing. Some tables have been added between the two versions and
some other tables have been modified. The purpose of this project is to use the
Astrads and DBAs servers with the two billing servers. In this intention, we
needed to build a new DBA and Astrad version able to use data from the the two
billing servers. This two sources of data had to be
merged in one in order to be used by the Astrad servers.
In order to do not overload the BDAs servers, we decided to install a
dedicated server for this pupose. This erver name is PBS2.
PBS
modifications
The pbs server has two
mysql instances on TCP ports 3307 and 3308 for replicating the two billing
databases and a third instance on mysql port 3306 for the merged data used by
DBAs servers. We added federated tables (federated tables names are ‘remote_table_name_fed’ )
in the replications databases to access the same tables of the third mysql
instance.
Each federated tables
and tables of the third mysql instance and have a new index called ‘i_billing’,
wich is the id of the billing (‘1’ or ‘2’ for now). The default value of
i_billing field is ‘1’ in the first mysql instance and ‘2’ in the second one.
Triggers on the
replicated tables fill the federated tables adding the default value to
‘i_billing’ field.
All database structure
and triggers is availbale by clicking on the scheme bellow.
Scheme
Replication
tables
Replication
table on MySQL instance n°1
|
Code |
Comments |
|
1.
USE `porta-billing`; 2. DROP TABLE IF EXISTS `Accounts`; 3. /*!40101 SET @saved_cs_client = @@character_set_client
*/; 4. /*!40101 SET character_set_client = utf8 */; 5. CREATE TABLE `Accounts` ( 6. `i_account` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 7. `id` varchar(32) NOT NULL, 8. `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 9. `h323_password` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 10. `balance` decimal(15,5) NOT NULL DEFAULT '0.00000', 11. `opening_balance` decimal(15,5) NOT NULL DEFAULT '0.00000', 12. `issue_date` date NOT NULL DEFAULT '0000-00-00', 13. `zero_balance_date`
date DEFAULT NULL, 14. `i_customer` int(10) UNSIGNED NOT NULL DEFAULT '0', 15. `billing_model` tinyint(2) NOT NULL DEFAULT '0', 16. `activation_date` date NOT NULL DEFAULT '0000-00-00', 17. `expiration_date` date DEFAULT NULL, 18. `i_product` int(10) UNSIGNED NOT NULL DEFAULT '0', 19. `blocked` char(1) NOT NULL DEFAULT 'N', 20. `last_usage` datetime DEFAULT NULL, 21. `i_batch` int(10) UNSIGNED DEFAULT NULL, 22. `i_env` tinyint(1) UNSIGNED NOT NULL DEFAULT '1', 23. `first_usage` date DEFAULT NULL, 24. `life_time` smallint(4) UNSIGNED DEFAULT NULL, 25. `redirect_number` varchar(32) DEFAULT NULL, 26. `control_number` int(10) UNSIGNED DEFAULT NULL, 27. `iso_639_1` char(5) DEFAULT NULL, 28. `i_time_zone` int(10) UNSIGNED NOT NULL DEFAULT '1', 29. `credit_limit` decimal(15,5) DEFAULT NULL, 30. `iso_4217` char(3) CHARACTER SET utf8 NOT NULL, 31. `non_call_related_charges` decimal(15,5) DEFAULT NULL, 32. `refunds` decimal(15,5) DEFAULT NULL, 33. `login` varchar(64) CHARACTER SET utf8 DEFAULT NULL, 34. `email` varchar(128) CHARACTER SET utf8 DEFAULT NULL, 35. `password_timestamp`
datetime DEFAULT NULL, 36. `um_enabled` char(1) NOT NULL DEFAULT 'N', 37. `i_acl` int(10) UNSIGNED NOT NULL DEFAULT '155', 38. `i_subscriber` int(10) UNSIGNED DEFAULT NULL, 39. `i_lang` char(5) DEFAULT NULL, 40. `ua_profile_id` int(10) UNSIGNED DEFAULT NULL, 41. `mac` varchar(23) CHARACTER SET utf8 DEFAULT NULL, 42. `follow_me_enabled`
char(1) NOT NULL DEFAULT 'N', 43. `timer` datetime DEFAULT NULL, 44. `ecommerce_enabled`
char(1) DEFAULT 'N', 45. `out_date_format` varchar(16) CHARACTER SET utf8 NOT NULL, 46. `out_time_format` varchar(16) CHARACTER SET utf8 NOT NULL, 47. `out_date_time_format`
varchar(32) CHARACTER SET utf8 NOT NULL, 48. `in_date_format` varchar(16) CHARACTER SET utf8 NOT NULL, 49. `in_time_format` varchar(16) CHARACTER SET utf8 NOT NULL, 50. `i_vd_plan` int(10) UNSIGNED DEFAULT NULL, 51. `last_recharge` datetime DEFAULT NULL, 52. `i_moh` int(10) UNSIGNED DEFAULT NULL, 53. `service_flags` varchar(32) CHARACTER SET utf8 NOT NULL, 54. `i_master_account` int(10) DEFAULT NULL, 55. `bill_status` enum('O','C','I') NOT NULL DEFAULT 'O', 56. `i_routing_plan` int(10) NOT NULL DEFAULT '-1', 57. `i_distributor` int(10) UNSIGNED DEFAULT NULL, 58. `management_number`
varchar(32) DEFAULT NULL, 59. `profitability` tinyint(3) NOT NULL DEFAULT '100', 60. `i_customer_site` int(10) DEFAULT NULL, 61. PRIMARY KEY (`i_account`), 62. UNIQUE KEY `Accounts_id` (`id`,`i_env`), 63. UNIQUE KEY `Accounts_cn_batch` (`i_batch`,`control_number`), 64. UNIQUE KEY `Accounts_login` (`login`), 65. KEY `Account_customer` (`i_customer`), 66. KEY `AccountsProfileId` (`ua_profile_id`), 67. KEY `Accounts_mac` (`mac`,`i_env`), 68. KEY `Accounts_um_enabled` (`um_enabled`), 69. KEY `Accounts_iso_4217` (`iso_4217`), 70. KEY `env_id` (`i_env`,`id`), 71. KEY `AccountFirstUsage` (`first_usage`), 72. KEY `i_vd_plan` (`i_vd_plan`), 73. KEY `accounts_maintenance` (`i_product`,`blocked`,`expiration_date`), 74. KEY `Accounts_i_acl` (`i_acl`), 75. KEY `i_master_account` (`i_master_account`), 76. KEY `i_routing_plan` (`i_routing_plan`), 77. KEY `management_number` (`management_number`,`i_env`), 78. KEY `distributor` (`i_distributor`) 79. ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; 80. /*!40101 SET character_set_client = @saved_cs_client */; |
Simple table dump
from new MR24 porta-billing. |
Replication
table on MySQL instance n°2
|
Code |
Comments |
|
1. USE `porta-billing`; 2. CREATE TABLE Accounts ( 3. i_account int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 4. id char(32) NOT NULL DEFAULT '', 5. password char(16) DEFAULT NULL, 6. h323_password char(255) DEFAULT NULL, 7. balance decimal(15,5) NOT NULL DEFAULT '0.00000', 8. opening_balance decimal(15,5) NOT NULL DEFAULT '0.00000', 9. issue_date date NOT NULL DEFAULT '0000-00-00', 10. zero_balance_date date DEFAULT NULL, 11. i_customer int(10) UNSIGNED NOT NULL DEFAULT '0', 12. billing_model tinyint(2) NOT NULL DEFAULT '0', 13. activation_date date NOT NULL DEFAULT '0000-00-00', 14. expiration_date date DEFAULT NULL, 15. i_product int(10) UNSIGNED NOT NULL DEFAULT '0', 16. blocked char(1) NOT NULL DEFAULT 'N', 17. last_usage datetime DEFAULT NULL, 18. i_batch int(10) UNSIGNED NOT NULL DEFAULT '0', 19. i_env tinyint(1) UNSIGNED NOT NULL DEFAULT '1', 20. first_usage date DEFAULT NULL, 21. life_time smallint(4) UNSIGNED DEFAULT NULL, 22. redirect_number char(15) DEFAULT NULL, 23. control_number int(10) UNSIGNED NOT NULL DEFAULT '0', 24. iso_639_1 char(2) DEFAULT NULL, 25. i_time_zone int(10) UNSIGNED NOT NULL DEFAULT '1', 26. credit_limit decimal(15,5) DEFAULT NULL, 27. iso_4217 char(3) NOT NULL DEFAULT '', 28. non_call_related_charges decimal(15,5) DEFAULT NULL, 29. refunds decimal(15,5) DEFAULT NULL, 30. login char(32) DEFAULT NULL, 31. email char(128) DEFAULT NULL, 32. password_timestamp datetime DEFAULT NULL, 33. um_enabled char(1) NOT NULL DEFAULT 'N', 34. i_acl int(10) UNSIGNED NOT NULL DEFAULT '155', 35. i_subscriber int(10) UNSIGNED DEFAULT NULL, 36. i_lang char(5) DEFAULT NULL, 37. ua_profile_id int(10) UNSIGNED DEFAULT NULL, 38. mac char(23) DEFAULT NULL, 39. i_ua_profile int(10) UNSIGNED DEFAULT NULL, 40. follow_me_enabled char(1) NOT NULL DEFAULT 'N', 41. timer datetime DEFAULT NULL, 42. ecommerce_enabled char(1) DEFAULT 'N', 43. out_date_format char(16) NOT NULL DEFAULT '', 44. out_time_format char(16) NOT NULL DEFAULT '', 45. out_date_time_format char(32) NOT NULL DEFAULT '', 46. in_date_format char(16) NOT NULL DEFAULT '', 47. in_time_format char(16) NOT NULL DEFAULT '', 48. i_vd_plan int(10) UNSIGNED DEFAULT NULL, 49. last_recharge datetime DEFAULT NULL, 50. i_moh int(10) UNSIGNED DEFAULT NULL, 51. service_flags char(32) NOT NULL DEFAULT '', 52. PRIMARY KEY (i_account), 53. UNIQUE KEY Accounts_id (id,i_env), 54. UNIQUE KEY Accounts_cn_batch (i_batch,control_number), 55. UNIQUE KEY Accounts_login (login), 56. KEY Account_customer (i_customer), 57. KEY Accounts_acl (i_acl), 58. KEY Accounts_i_product (i_product), 59. KEY Accounts_product (i_product), 60. KEY AccountsProfileId (ua_profile_id), 61. KEY Accounts_mac (mac,i_env), 62. KEY Accounts_um_enabled (um_enabled), 63. KEY Accounts_iso_4217 (iso_4217), 64. KEY AccountFirstUsage (first_usage), 65. KEY i_vd_plan (i_vd_plan), 66. KEY accounts_maintenance (i_product,blocked,expiration_date) 67. ) TYPE=InnoDB; |
Simple table dump
from old MR18 porta-billing. |
Federated tables for
accessing the mixed database.
Federated
table on MySQL instance n°1
|
Code |
Comments |
|
1. USE `porta-billing`; 2. –- 3. -- Table structure for table `Accounts_fed` 4. -- 5. DROP TABLE IF EXISTS `Accounts_fed`; 6. /*!40101 SET @saved_cs_client = @@character_set_client
*/; 7. /*!40101 SET character_set_client = utf8 */; 8. CREATE TABLE `Accounts_fed` ( 9. `i_account` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 10. `id` varchar(32) NOT NULL, 11. `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 12. `h323_password` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 13. `balance` decimal(15,5) NOT NULL DEFAULT '0.00000', 14. `opening_balance` decimal(15,5) NOT NULL DEFAULT '0.00000', 15. `issue_date` date NOT NULL DEFAULT '0000-00-00', 16. `zero_balance_date`
date DEFAULT NULL, 17. `i_customer` int(10) UNSIGNED NOT NULL DEFAULT '0', 18. `billing_model` tinyint(2) NOT NULL DEFAULT '0', 19. `activation_date` date NOT NULL DEFAULT '0000-00-00', 20. `expiration_date` date DEFAULT NULL, 21. `i_product` int(10) UNSIGNED NOT NULL DEFAULT '0', 22. `blocked` char(1) NOT NULL DEFAULT 'N', 23. `last_usage` datetime DEFAULT NULL, 24. `i_batch` int(10) UNSIGNED DEFAULT NULL, 25. `i_env` tinyint(1) UNSIGNED NOT NULL DEFAULT '1', 26. `first_usage` date DEFAULT NULL, 27. `life_time` smallint(4) UNSIGNED DEFAULT NULL, 28. `redirect_number` varchar(32) DEFAULT NULL, 29. `control_number` int(10) UNSIGNED DEFAULT NULL, 30. `iso_639_1` char(5) DEFAULT NULL, 31. `i_time_zone` int(10) UNSIGNED NOT NULL DEFAULT '1', 32. `credit_limit` decimal(15,5) DEFAULT NULL, 33. `iso_4217` char(3) CHARACTER SET utf8 NOT NULL, 34. `non_call_related_charges` decimal(15,5) DEFAULT NULL, 35. `refunds` decimal(15,5) DEFAULT NULL, 36. `login` varchar(64) CHARACTER SET utf8 DEFAULT NULL, 37. `email` varchar(128) CHARACTER SET utf8 DEFAULT NULL, 38. `password_timestamp`
datetime DEFAULT NULL, 39. `um_enabled` char(1) NOT NULL DEFAULT 'N', 40. `i_acl` int(10) UNSIGNED NOT NULL DEFAULT '155', 41. `i_subscriber` int(10) UNSIGNED DEFAULT NULL, 42. `i_lang` char(5) DEFAULT NULL, 43. `ua_profile_id` int(10) UNSIGNED DEFAULT NULL, 44. `mac` varchar(23) CHARACTER SET utf8 DEFAULT NULL, 45. `follow_me_enabled`
char(1) NOT NULL DEFAULT 'N', 46. `timer` datetime DEFAULT NULL, 47. `ecommerce_enabled`
char(1) DEFAULT 'N', 48. `out_date_format` varchar(16) CHARACTER SET utf8 NOT NULL, 49. `out_time_format` varchar(16) CHARACTER SET utf8 NOT NULL, 50. `out_date_time_format`
varchar(32) CHARACTER SET utf8 NOT NULL, 51. `in_date_format` varchar(16) CHARACTER SET utf8 NOT NULL, 52. `in_time_format` varchar(16) CHARACTER SET utf8 NOT NULL, 53. `i_vd_plan` int(10) UNSIGNED DEFAULT NULL, 54. `last_recharge` datetime DEFAULT NULL, 55. `i_moh` int(10) UNSIGNED DEFAULT NULL, 56. `service_flags` varchar(32) CHARACTER SET utf8 NOT NULL, 57. `i_master_account` int(10) DEFAULT NULL, 58. `bill_status` enum('O','C','I') NOT NULL DEFAULT 'O', 59. `i_routing_plan` int(10) NOT NULL DEFAULT '-1', 60. `i_distributor` int(10) UNSIGNED DEFAULT NULL, 61. `management_number`
varchar(32) DEFAULT NULL, 62. `profitability` tinyint(3) NOT NULL DEFAULT '100', 63. `i_customer_site` int(10) DEFAULT NULL, 64.
`i_billing`
tinyint (2) UNSIGNED DEFAULT '1', 65.
PRIMARY KEY (`i_account`,`i_billing`), 66.
UNIQUE KEY `Accounts_id` (`id`,`i_env`,`i_billing`), 67.
UNIQUE KEY `Accounts_cn_batch` (`i_batch`,`control_number`,`i_billing`), 68.
UNIQUE KEY `Accounts_login` (`login`,`i_billing`), 69. KEY `Account_customer` (`i_customer`), 70. KEY `AccountsProfileId` (`ua_profile_id`), 71. KEY `Accounts_mac` (`mac`,`i_env`), 72. KEY `Accounts_um_enabled` (`um_enabled`), 73. KEY `Accounts_iso_4217` (`iso_4217`), 74. KEY `env_id` (`i_env`,`id`), 75. KEY `AccountFirstUsage` (`first_usage`), 76. KEY `i_vd_plan` (`i_vd_plan`), 77. KEY `accounts_maintenance` (`i_product`,`blocked`,`expiration_date`), 78. KEY `Accounts_i_acl` (`i_acl`), 79. KEY `i_master_account` (`i_master_account`), 80. KEY `i_routing_plan` (`i_routing_plan`), 81. KEY `management_number` (`management_number`,`i_env`), 82. KEY `distributor` (`i_distributor`) 83. ) ENGINE=FEDERATED AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 84.
CONNECTION='mysql://federated@localhost:3306/porta-billing/Accounts'; 85. /*!40101 SET character_set_client = @saved_cs_client */; |
Federated table
`porta-billing`.Accounts_fed in the first mysql instance (3307) referring to `porta-billing`.Accounts in the third mysql
instance (3306). New ‘i_billing’
index as been added to the columns, its default value is ‘1’ as it is the
code of the first mysql instance. Primary key and unique keys contains the
new ‘i_billing’ index to prevent duplicates keys on these indexes in the
merged table. Last CONNECTION
line is the federated connection to the remote table of the third database. |
Federated
table on MySQL instance n°2
|
Code |
Comments |
|
1. DROP TABLE IF EXISTS `Accounts_fed`; 2. /*!40101 SET @saved_cs_client = @@character_set_client
*/; 3. /*!40101 SET character_set_client = utf8 */; 4. CREATE TABLE `Accounts_fed` ( 5. `i_account` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 6. `id` varchar(32) NOT NULL, 7. `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 8. `h323_password` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 9. `balance` decimal(15,5) NOT NULL DEFAULT '0.00000', 10. `opening_balance` decimal(15,5) NOT NULL DEFAULT '0.00000', 11. `issue_date` date NOT NULL DEFAULT '0000-00-00', 12. `zero_balance_date`
date DEFAULT NULL, 13. `i_customer` int(10) UNSIGNED NOT NULL DEFAULT '0', 14. `billing_model` tinyint(2) NOT NULL DEFAULT '0', 15. `activation_date` date NOT NULL DEFAULT '0000-00-00', 16. `expiration_date` date DEFAULT NULL, 17. `i_product` int(10) UNSIGNED NOT NULL DEFAULT '0', 18. `blocked` char(1) NOT NULL DEFAULT 'N', 19. `last_usage` datetime DEFAULT NULL, 20. `i_batch` int(10) UNSIGNED DEFAULT NULL, 21. `i_env` tinyint(1) UNSIGNED NOT NULL DEFAULT '1', 22. `first_usage` date DEFAULT NULL, 23. `life_time` smallint(4) UNSIGNED DEFAULT NULL, 24. `redirect_number` varchar(32) DEFAULT NULL, 25. `control_number` int(10) UNSIGNED DEFAULT NULL, 26. `iso_639_1` char(5) DEFAULT NULL, 27. `i_time_zone` int(10) UNSIGNED NOT NULL DEFAULT '1', 28. `credit_limit` decimal(15,5) DEFAULT NULL, 29. `iso_4217` char(3) CHARACTER SET utf8 NOT NULL, 30. `non_call_related_charges` decimal(15,5) DEFAULT NULL, 31. `refunds` decimal(15,5) DEFAULT NULL, 32. `login` varchar(64) CHARACTER SET utf8 DEFAULT NULL, 33. `email` varchar(128) CHARACTER SET utf8 DEFAULT NULL, 34. `password_timestamp`
datetime DEFAULT NULL, 35. `um_enabled` char(1) NOT NULL DEFAULT 'N', 36. `i_acl` int(10) UNSIGNED NOT NULL DEFAULT '155', 37. `i_subscriber` int(10) UNSIGNED DEFAULT NULL, 38. `i_lang` char(5) DEFAULT NULL, 39. `ua_profile_id` int(10) UNSIGNED DEFAULT NULL, 40. `mac` varchar(23) CHARACTER SET utf8 DEFAULT NULL, 41. `follow_me_enabled`
char(1) NOT NULL DEFAULT 'N', 42. `timer` datetime DEFAULT NULL, 43. `ecommerce_enabled`
char(1) DEFAULT 'N', 44. `out_date_format` varchar(16) CHARACTER SET utf8 NOT NULL, 45. `out_time_format` varchar(16) CHARACTER SET utf8 NOT NULL, 46. `out_date_time_format`
varchar(32) CHARACTER SET utf8 NOT NULL, 47. `in_date_format` varchar(16) CHARACTER SET utf8 NOT NULL, 48. `in_time_format` varchar(16) CHARACTER SET utf8 NOT NULL, 49. `i_vd_plan` int(10) UNSIGNED DEFAULT NULL, 50. `last_recharge` datetime DEFAULT NULL, 51. `i_moh` int(10) UNSIGNED DEFAULT NULL, 52. `service_flags` varchar(32) CHARACTER SET utf8 NOT NULL, 53. `i_master_account` int(10) DEFAULT NULL, 54. `bill_status` enum('O','C','I') NOT NULL DEFAULT 'O', 55. `i_routing_plan` int(10) NOT NULL DEFAULT '-1', 56. `i_distributor` int(10) UNSIGNED DEFAULT NULL, 57. `management_number`
varchar(32) DEFAULT NULL, 58. `profitability` tinyint(3) NOT NULL DEFAULT '100', 59. `i_customer_site` int(10) DEFAULT NULL, 60.
`i_billing`
tinyint (2) UNSIGNED DEFAULT '2', 61.
PRIMARY KEY (`i_account`,`i_billing`), 62.
UNIQUE KEY `Accounts_id` (`id`,`i_env`,`i_billing`), 63.
UNIQUE KEY `Accounts_cn_batch` (`i_batch`,`control_number`,`i_billing`), 64.
UNIQUE KEY `Accounts_login` (`login`,`i_billing`), 65. KEY `Account_customer` (`i_customer`), 66. KEY `AccountsProfileId` (`ua_profile_id`), 67. KEY `Accounts_mac` (`mac`,`i_env`), 68. KEY `Accounts_um_enabled` (`um_enabled`), 69. KEY `Accounts_iso_4217` (`iso_4217`), 70. KEY `env_id` (`i_env`,`id`), 71. KEY `AccountFirstUsage` (`first_usage`), 72. KEY `i_vd_plan` (`i_vd_plan`), 73. KEY `accounts_maintenance` (`i_product`,`blocked`,`expiration_date`), 74. KEY `Accounts_i_acl` (`i_acl`), 75. KEY `i_master_account` (`i_master_account`), 76. KEY `i_routing_plan` (`i_routing_plan`), 77. KEY `management_number` (`management_number`,`i_env`), 78. KEY `distributor` (`i_distributor`) 79. ) ENGINE=FEDERATED AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 80.
CONNECTION='mysql://federated@localhost:3306/porta-billing/Accounts'; 81. /*!40101 SET character_set_client = @saved_cs_client */; |
Federated table
`porta-billing`.Accounts_fed in the second mysql instance (3308) referring to `porta-billing`.Accounts in the third mysql
instance (3306). New ‘i_billing’
index as been added to the columns, its default value is ‘2’ as it is the code
of the second mysql instance. Primary key and unique keys contains the new
‘i_billing’ index to prevent duplicates keys on these indexes in the merged
table. Last CONNECTION
line is the federated connection to the remote table of the third database. |
Triggers
filling federated tables
Trigger on MySQL
instance n°1
|
Code |
Comments |
1. USE `porta-billing`; 2. –- 3. -- Dumping triggers for table `Accounts` 4. -- 5. DELIMITER | 6. DROP TRIGGER IF EXISTS `Trg_Insert_Accounts_fed`| 7. CREATE TRIGGER `Trg_Insert_Accounts_fed` AFTER INSERT ON `Accounts` FOR EACH ROW 8. BEGIN 9. INSERT INTO `Accounts_fed` ( 10. i_account, 11. id, 12. password, 13. h323_password, 14. balance, 15. opening_balance, 16. issue_date, 17. zero_balance_date, 18. i_customer, 19. billing_model, 20. activation_date, 21. expiration_date, 22. i_product, 23. blocked, 24. last_usage, 25. i_batch, 26. i_env, 27. first_usage, 28. life_time, 29. redirect_number, 30. control_number, 31. iso_639_1, 32. i_time_zone, 33. credit_limit, 34. iso_4217, 35. non_call_related_charges, 36. refunds, 37. login, 38. email, 39. password_timestamp, 40. um_enabled, 41. i_acl, 42. i_subscriber, 43. i_lang, 44. ua_profile_id, 45. mac, 46. follow_me_enabled, 47. timer, 48. ecommerce_enabled, out_date_format, 49. out_time_format, 50. out_date_time_format, 51. in_date_format, 52. in_time_format, 53. i_vd_plan, 54. last_recharge, 55. i_moh, 56. service_flags, 57. i_master_account, 58. bill_status, 59. i_routing_plan, 60. i_distributor, 61. management_number, 62. profitability, 63. i_customer_site, 64. i_billing 65. ) VALUES ( 66. NEW.i_account, 67. NEW.id, 68. NEW.password, 69. NEW.h323_password, 70. NEW.balance, 71. NEW.opening_balance, 72. NEW.issue_date, 73. NEW.zero_balance_date, 74. NEW.i_customer, 75. NEW.billing_model, 76. NEW.activation_date, 77. NEW.expiration_date, 78. NEW.i_product, 79. NEW.blocked, 80. NEW.last_usage, 81. NEW.i_batch, 82. NEW.i_env, 83. NEW.first_usage, 84. NEW.life_time, 85. NEW.redirect_number, 86. NEW.control_number, 87. NEW.iso_639_1, 88. NEW.i_time_zone, 89. NEW.credit_limit, 90. NEW.iso_4217, 91. NEW.non_call_related_charges, 92. NEW.refunds, 93. NEW.login, 94. NEW.email, 95. NEW.password_timestamp, 96. NEW.um_enabled, 97. NEW.i_acl, NEW.i_subscriber, 98. NEW.i_lang, 99. NEW.ua_profile_id, 100. NEW.mac, 101. NEW.follow_me_enabled, 102. NEW.timer, 103. NEW.ecommerce_enabled, 104. NEW.out_date_format, 105. NEW.out_time_format, 106. NEW.out_date_time_format, 107. NEW.in_date_format, 108. NEW.in_time_format, 109. NEW.i_vd_plan, 110. NEW.last_recharge, 111. NEW.i_moh, 112. NEW.service_flags, 113. NEW.i_master_account, 114. NEW.bill_status, 115. NEW.i_routing_plan, 116. NEW.i_distributor, 117. NEW.management_number, 118. NEW.profitability, 119. NEW.i_customer_site, 120. DEFAULT 121. ); 122. END | 123. DELIMITER ; |
The trigger that is
running when inserting an account. Accounts table on mysql instance n°1 and
n°3 are the same, we retrieve here all the fields of the table of mysql
instance n°3. The field ‘i_billing’
is set as the default value wich is ‘1’ here (see Accounts federated table definition
[go]), this way, we can not get a
duplicated key on i_account inserting if i_account index is also present in
the second billing. |
1. DELIMITER | 2. DROP TRIGGER IF EXISTS `Trg_Update_Accounts_fed`| 3. CREATE TRIGGER `Trg_Update_Accounts_fed` AFTER UPDATE ON `Accounts` FOR EACH ROW 4. BEGIN 5. UPDATE `Accounts_fed` SET 6. i_account=NEW.i_account, 7. id=NEW.id, 8. password=NEW.password, 9. h323_password=NEW.h323_password, 10. balance=NEW.balance, 11. opening_balance=NEW.opening_balance, 12. issue_date=NEW.issue_date, 13. zero_balance_date=NEW.zero_balance_date, 14. i_customer=NEW.i_customer, 15. billing_model=NEW.billing_model, 16. activation_date=NEW.activation_date, 17. expiration_date=NEW.expiration_date, 18. i_product=NEW.i_product, 19. blocked=NEW.blocked, 20. last_usage=NEW.last_usage, i_batch=NEW.i_batch, 21. i_env=NEW.i_env, 22. first_usage=NEW.first_usage, 23. life_time=NEW.life_time, 24. redirect_number=NEW.redirect_number, 25. control_number=NEW.control_number, 26. iso_639_1=NEW.iso_639_1, 27. i_time_zone=NEW.i_time_zone, 28. credit_limit=NEW.credit_limit, 29. iso_4217=NEW.iso_4217, 30. non_call_related_charges=NEW.non_call_related_charges, 31. refunds=NEW.refunds, 32. login=NEW.login, 33. email=NEW.email, 34. password_timestamp=NEW.password_timestamp, 35. um_enabled=NEW.um_enabled, 36. i_acl=NEW.i_acl, 37. i_subscriber=NEW.i_subscriber, 38. i_lang=NEW.i_lang, 39. ua_profile_id=NEW.ua_profile_id, 40. mac=NEW.mac, 41. follow_me_enabled=NEW.follow_me_enabled, 42. timer=NEW.timer, 43. ecommerce_enabled=NEW.ecommerce_enabled, 44. out_date_format=NEW.out_date_format, 45. out_time_format=NEW.out_time_format, 46. out_date_time_format=NEW.out_date_time_format, 47. in_date_format=NEW.in_date_format, 48. in_time_format=NEW.in_time_format, 49. i_vd_plan=NEW.i_vd_plan, 50. last_recharge=NEW.last_recharge, 51. i_moh=NEW.i_moh, 52. service_flags=NEW.service_flags, 53. i_master_account=NEW.i_master_account, 54. bill_status=NEW.bill_status, 55. i_routing_plan=NEW.i_routing_plan, 56. i_distributor=NEW.i_distributor, 57. management_number=NEW.management_number, 58. profitability=NEW.profitability, 59. i_customer_site=NEW.i_customer_site, 60. i_billing=DEFAULT 61. WHERE i_account=NEW.i_account AND i_billing='1'; 62. END | 63. DELIMITER ; |
The update trigger on an update on the ‘porta-billing’.Accounts table of
the first mysql instance, only update the lines where ‘i_billing is’ ‘1’. This
way, when an Account with an id present in the two billing, is updated on
billing n°1, only the row with ‘i_billing’ equal ‘1’ is updated. |
1. DELIMITER | 2. DROP TRIGGER IF EXISTS `Trg_Delete_Accounts_fed`| 3. CREATE TRIGGER `Trg_Delete_Accounts_fed` AFTER DELETE ON `Accounts` FOR EACH ROW BEGIN 4. DELETE FROM `Accounts_fed` WHERE i_account=OLD.i_account AND i_billing='1'; 5. END | 6. DELIMITER ; |
The delete trigger
for first mysql instance delete only the rows with billing equal 1 in the
federated table. It can not delete rows from the second billing. |
Triggers on MySQL
instance n°2
|
Code |
Comments |
|
1. USE `porta-billing`; 2. –- 3. -- Dumping triggers for table `Accounts` 4. -- 5. DELIMITER | 6. DROP TRIGGER IF EXISTS `Trg_Insert_Accounts_fed`| 7. CREATE TRIGGER `Trg_Insert_Accounts_fed` AFTER INSERT ON `Accounts` FOR EACH ROW 8. BEGIN 9. INSERT INTO `Accounts_fed` ( 10. i_account, 11. id, 12. password, 13. h323_password, 14. balance, 15. opening_balance, 16. issue_date, 17. zero_balance_date, 18. i_customer, 19. billing_model, 20. activation_date, 21. expiration_date, 22. i_product, 23. blocked, 24. last_usage, 25. i_batch, 26. i_env, 27. first_usage, 28. life_time, 29. redirect_number, 30. control_number, 31. iso_639_1, 32. i_time_zone, 33. credit_limit, 34. iso_4217, 35. non_call_related_charges, 36. refunds, 37. login, 38. email, 39. password_timestamp, 40. um_enabled, 41. i_acl, 42. i_subscriber, 43. i_lang, 44. ua_profile_id, 45. mac, 46. follow_me_enabled, 47. timer, 48. ecommerce_enabled,
out_date_format, 49. out_time_format, 50. out_date_time_format, 51. in_date_format, 52. in_time_format, 53. i_vd_plan, 54. last_recharge, 55. i_moh, 56. service_flags, 57.
i_billing 58. ) VALUES ( 59. NEW.i_account, 60. NEW.id, 61. NEW.password, 62. NEW.h323_password, 63. NEW.balance, 64. NEW.opening_balance, 65. NEW.issue_date, 66. NEW.zero_balance_date, 67. NEW.i_customer, 68. NEW.billing_model, 69. NEW.activation_date, 70. NEW.expiration_date, 71. NEW.i_product, 72. NEW.blocked, 73. NEW.last_usage, 74. NEW.i_batch, 75. NEW.i_env, 76. NEW.first_usage, 77. NEW.life_time, 78. NEW.redirect_number, 79. NEW.control_number, 80. NEW.iso_639_1, 81. NEW.i_time_zone, 82. NEW.credit_limit, 83. NEW.iso_4217, 84. NEW.non_call_related_charges, 85. NEW.refunds, 86. NEW.login, 87. NEW.email, 88. NEW.password_timestamp, 89. NEW.um_enabled, 90. NEW.i_acl, 91. NEW.i_subscriber, 92. NEW.i_lang, 93. NEW.ua_profile_id, 94. NEW.mac, 95. NEW.follow_me_enabled, 96. NEW.timer, 97. NEW.ecommerce_enabled, 98. NEW.out_date_format, 99. NEW.out_time_format, 100. NEW.out_date_time_format, 101. NEW.in_date_format, 102. NEW.in_time_format, 103. NEW.i_vd_plan, 104. NEW.last_recharge, 105. NEW.i_moh, 106. NEW.service_flags, 107.
DEFAULT 108. ); 109. END | 110. DELIMITER ; |
The trigger that is
running when inserting an account. `porta-billing`.Accounts and `porta-billing`.Accounts_fed
tables are not the same in mysql instance n°2. This is due to the fact that
Accounts table is a replication of old porta-billing and federated table
Accounts_fed has the same fields as the remote `porta-billing`.Accounts table
of mysql instance n°3. Some fields of table `porta-billing`.Accounts are not
present in `porta-billing`.Accounts_fed and vice-versa. The trigger only insert
fields presents in both tables. The non filled fields are inserted with
default values of the `porta-billing`.Accounts_fed table. The field ‘i_billing’
is set as the default value wich is ‘1’ here (see Accounts federated table definition
[go]), this way, we can not get a
duplicated key on i_account inserting if i_account index is also present in
the second billing. |
1. DELIMITER | 2. DROP TRIGGER IF EXISTS `Trg_Update_Accounts_fed`| 3. CREATE TRIGGER `Trg_Update_Accounts_fed` AFTER UPDATE ON `Accounts` FOR EACH ROW 4. BEGIN 5. UPDATE `Accounts_fed` SET 6. i_account=NEW.i_account, 7. id=NEW.id, 8. password=NEW.password, 9. h323_password=NEW.h323_password, 10. balance=NEW.balance, 11. opening_balance=NEW.opening_balance, 12. issue_date=NEW.issue_date, 13. zero_balance_date=NEW.zero_balance_date, 14. i_customer=NEW.i_customer, 15. billing_model=NEW.billing_model, 16. activation_date=NEW.activation_date, 17. expiration_date=NEW.expiration_date, 18. i_product=NEW.i_product, 19. blocked=NEW.blocked, 20. last_usage=NEW.last_usage, 21. i_batch=NEW.i_batch, 22. i_env=NEW.i_env, 23. first_usage=NEW.first_usage, 24. life_time=NEW.life_time, 25. redirect_number=NEW.redirect_number, 26. control_number=NEW.control_number, 27. iso_639_1=NEW.iso_639_1, 28. i_time_zone=NEW.i_time_zone, 29. credit_limit=NEW.credit_limit, 30. iso_4217=NEW.iso_4217, 31. non_call_related_charges=NEW.non_call_related_charges, 32. refunds=NEW.refunds, 33. login=NEW.login, 34. email=NEW.email, password_timestamp=NEW.password_timestamp, 35. um_enabled=NEW.um_enabled, 36. i_acl=NEW.i_acl, 37. i_subscriber=NEW.i_subscriber, 38. i_lang=NEW.i_lang, 39. ua_profile_id=NEW.ua_profile_id, 40. mac=NEW.mac, 41. follow_me_enabled=NEW.follow_me_enabled, 42. timer=NEW.timer, 43. ecommerce_enabled=NEW.ecommerce_enabled, 44. out_date_format=NEW.out_date_format, 45. out_time_format=NEW.out_time_format, 46. out_date_time_format=NEW.out_date_time_format, 47. in_date_format=NEW.in_date_format, 48. in_time_format=NEW.in_time_format, 49. i_vd_plan=NEW.i_vd_plan, 50. last_recharge=NEW.last_recharge, 51. i_moh=NEW.i_moh, 52. service_flags=NEW.service_flags, 53. i_billing=DEFAULT 54. WHERE i_account=NEW.i_account AND i_billing='2'; 55. END | 56. DELIMITER ; |
The update trigger is launched on an update on the ‘porta-billing’.Accounts
table of the second mysql instance. It only updates the lines where ‘i_billing’
is ‘2’. This way, when an Account with an id present in the two billing is
updated on billing n°2, only the row with ‘i_billing’ equal ‘2’ is updated. |
1. DELIMITER | 2. DROP TRIGGER IF EXISTS `Trg_Delete_Accounts_fed`| 3. CREATE TRIGGER `Trg_Delete_Accounts_fed` AFTER DELETE ON `Accounts` FOR EACH ROW 4. BEGIN 5. DELETE FROM `Accounts_fed` WHERE i_account=OLD.i_account AND i_billing='2'; 6. END | 7. DELIMITER ; |
The delete trigger
for second mysql instance delete only the rows with ‘i_billing’ equal 2 in
the federated table. It can not delete rows from the first billing. |
Unified
porta-billing database
|
Code |
Comments |
1. USE `porta-billing`; 2. -- a. Table structure for table `Accounts` 3. -- 4. DROP TABLE IF EXISTS `Accounts`; 5. /*!40101 SET @saved_cs_client = @@character_set_client */; 6. /*!40101 SET character_set_client = utf8 */; 7. CREATE TABLE `Accounts` ( 8. `i_account` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 9. `id` VARCHAR(32) NOT NULL, 10. `password` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 11. `h323_password` VARCHAR(255) CHARACTER SET utf8 DEFAULT NULL, 12. `balance` DECIMAL(15,5) NOT NULL DEFAULT '0.00000', 13. `opening_balance` DECIMAL(15,5) NOT NULL DEFAULT '0.00000', 14. `issue_date` DATE NOT NULL DEFAULT '0000-00-00', 15. `zero_balance_date` DATE DEFAULT NULL, 16. `i_customer` INT(10) UNSIGNED NOT NULL DEFAULT '0', 17. `billing_model` tinyint(2) NOT NULL DEFAULT '0', 18. `activation_date` DATE NOT NULL DEFAULT '0000-00-00', 19. `expiration_date` DATE DEFAULT NULL, 20. `i_product` INT(10) UNSIGNED NOT NULL DEFAULT '0', 21. `blocked` CHAR(1) NOT NULL DEFAULT 'N', 22. `last_usage` datetime DEFAULT NULL, 23. `i_batch` INT(10) UNSIGNED DEFAULT NULL, 24. `i_env` tinyint(1) UNSIGNED NOT NULL DEFAULT '1', 25. `first_usage` DATE DEFAULT NULL, `life_time` SMALLINT(4) UNSIGNED DEFAULT NULL, 26. `redirect_number` VARCHAR(32) DEFAULT NULL, 27. `control_number` INT(10) UNSIGNED DEFAULT NULL, 28. `iso_639_1` CHAR(5) DEFAULT NULL, 29. `i_time_zone` INT(10) UNSIGNED NOT NULL DEFAULT '1', 30. `credit_limit` DECIMAL(15,5) DEFAULT NULL, 31. `iso_4217` CHAR(3) CHARACTER SET utf8 NOT NULL, 32. `non_call_related_charges` DECIMAL(15,5) DEFAULT NULL, 33. `refunds` DECIMAL(15,5) DEFAULT NULL, 34. `login` VARCHAR(64) CHARACTER SET utf8 DEFAULT NULL, 35. `email` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL, 36. `password_timestamp` datetime DEFAULT NULL, 37. `um_enabled` CHAR(1) NOT NULL DEFAULT 'N', 38. `i_acl` INT(10) UNSIGNED NOT NULL DEFAULT '155', 39. `i_subscriber` INT(10) UNSIGNED DEFAULT NULL, 40. `i_lang` CHAR(5) DEFAULT NULL, 41. `ua_profile_id` INT(10) UNSIGNED DEFAULT NULL, 42. `mac` VARCHAR(23) CHARACTER SET utf8 DEFAULT NULL, 43. `follow_me_enabled` CHAR(1) NOT NULL DEFAULT 'N', 44. `timer` datetime DEFAULT NULL, 45. `ecommerce_enabled` CHAR(1) DEFAULT 'N', 46. `out_date_format` VARCHAR(16) CHARACTER SET utf8 NOT NULL, 47. `out_time_format` VARCHAR(16) CHARACTER SET utf8 NOT NULL, 48. `out_date_time_format` VARCHAR(32) CHARACTER SET utf8 NOT NULL, 49. `in_date_format` VARCHAR(16) CHARACTER SET utf8 NOT NULL, 50. `in_time_format` VARCHAR(16) CHARACTER SET utf8 NOT NULL, 51. `i_vd_plan` INT(10) UNSIGNED DEFAULT NULL, 52. `last_recharge` datetime DEFAULT NULL, 53. `i_moh` INT(10) UNSIGNED DEFAULT NULL, 54. `service_flags` VARCHAR(32) CHARACTER SET utf8 NOT NULL, 55. `i_master_account` INT(10) DEFAULT NULL, 56. `bill_status` enum('O','C','I') NOT NULL DEFAULT 'O', 57. `i_routing_plan` INT(10) NOT NULL DEFAULT '-1', 58. `i_distributor` INT(10) UNSIGNED DEFAULT NULL, 59. `management_number` VARCHAR(32) DEFAULT NULL, 60. `profitability` tinyint(3) NOT NULL DEFAULT '100', 61. `i_customer_site` INT(10) DEFAULT NULL, 62. `i_billing` tinyint (2) UNSIGNED, 63. PRIMARY KEY (`i_account`,`i_billing`), 64. UNIQUE KEY `Accounts_id` (`id`,`i_env`,`i_billing`), 65. UNIQUE KEY `Accounts_cn_batch` (`i_batch`,`control_number`,`i_billing`), 66. UNIQUE KEY `Accounts_login` (`login`,`i_billing`), 67. KEY `Account_customer` (`i_customer`), 68. KEY `AccountsProfileId` (`ua_profile_id`), 69. KEY `Accounts_mac` (`mac`,`i_env`), 70. KEY `Accounts_um_enabled` (`um_enabled`), 71. KEY `Accounts_iso_4217` (`iso_4217`), 72. KEY `env_id` (`i_env`,`id`), 73. KEY `AccountFirstUsage` (`first_usage`), 74. KEY `i_vd_plan` (`i_vd_plan`), KEY `accounts_maintenance` (`i_product`,`blocked`,`expiration_date`), 75. KEY `Accounts_i_acl` (`i_acl`), 76. KEY `i_master_account` (`i_master_account`), 77. KEY `i_routing_plan` (`i_routing_plan`), 78. KEY `management_number` (`management_number`,`i_env`), 79. KEY `distributor` (`i_distributor`) 80. ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; 81. /*!40101 SET character_set_client = @saved_cs_client */; |
The mixed databases contains table structure from dump of new MR24
porta-billing. The new ‘i_billing’
fields have been added for all tables in order to make difference between the
two sources of datas, old and new porta-billing. Unique keys and
primary keys contains also the i_billing fields. This
way, no duplicate data is possible when the two billings contains
the same ids. |
DBA
modifications
Scheme
Scheme
Triggers
Astrad
modifications
Scheme
Triggers
Radius script
Canvas texts
NEW MASTER PB (1)
OLD MASTER PB (2)
PBS2.SWITZERNET.COM
DBx.SWITZERNET.COM
porta-billing
porta-sip
mysqld1 (port 3307)
mysqld2 (port 3308)
mysqld (port 3306)
+---------------------------------+
|
Tables_in_porta-billing |
+---------------------------------+
| Accounts |
| Active_Calls |
| Connections |
| Customers |
| Destinations |
| Follow_Me |
| Nodes |
| Rates |
| Service_Attributes |
| astrad_confs |
| customer_confs |
| Accounts_fed |
|
Active_Calls_fed |
| Connections_fed |
| Customers_fed |
| Destinations_fed |
| Follow_Me_fed |
| Nodes_fed |
| Rates_fed |
|
Service_Attributes_fed |
|
astrad_confs_fed |
| customer_confs_fed |
| ...... |
| ...... |
+---------------------------------+
+---------------------------------+
|
Tables_in_porta-billing |
+---------------------------------+
| Accounts |
| Active_Calls |
| Connections |
| Customers |
| Destinations |
| Follow_Me |
| Nodes |
| Rates |
| Service_Attributes |
| astrad_confs |
| customer_confs |
| ...... |
+---------------------------------+
+---------------------------------+
|
Tables_in_porta-billing |
+---------------------------------+
| Accounts |
| Active_Calls |
| Connections |
| Customers |
| Destinations |
| Follow_Me |
| Nodes |
| Rates |
| Service_Attributes |
| astrad_confs |
| customer_confs |
+---------------------------------+
+---------------------------------+
|
Tables_in_porta-billing |
+---------------------------------+
| Accounts_fed |
|
Active_Calls_fed |
| Connections_fed |
| Customers_fed |
|
Destinations_fed |
| Follow_Me_fed |
| Nodes_fed |
| Rates_fed |
|
Service_Attributes_fed |
|
astrad_confs_fed |
| customer_confs_fed |
| ...... |
+---------------------------------+
+-----------------------------+
| Tables_in_porta-sip |
+-----------------------------+
| location_fed |
| ...... |
+-----------------------------+
+-----------------------------+
| Tables_in_porta-sip |
+-----------------------------+
| location |
| ...... |
+-----------------------------+
+-----------------------------+
| Tables_in_porta-sip |
+-----------------------------+
| location |
+-----------------------------+
+-----------------------------------+--------+
| TRIGGER_NAME | EVENT. |
+-----------------------------------+--------+
|
Trg_Insert_Accounts_fed |
INSERT |
|
Trg_Update_Accounts_fed | UPDATE
|
|
Trg_Delete_Accounts_fed |
DELETE |
|
Trg_Insert_Active_Calls_fed |
INSERT |
|
Trg_Update_Active_Calls_fed |
UPDATE |
|
Trg_Delete_Active_Calls_fed |
DELETE |
|
Trg_Insert_Connections_fed |
INSERT |
| Trg_Update_Connections_fed | UPDATE |
|
Trg_Delete_Connections_fed |
DELETE |
|
Trg_Insert_Customers_fed |
INSERT |
|
Trg_Update_Customers_fed |
UPDATE |
|
Trg_Delete_Customers_fed |
DELETE |
|
Trg_Insert_Destinations_fed | INSERT
|
|
Trg_Update_Destinations_fed |
UPDATE |
|
Trg_Delete_Destinations_fed |
DELETE |
|
Trg_Insert_Follow_Me_fed |
INSERT |
|
Trg_Update_Follow_Me_fed |
UPDATE |
|
Trg_Delete_Follow_Me_fed |
DELETE |
| Trg_Insert_Nodes_fed | INSERT |
|
Trg_Update_Nodes_fed |
UPDATE |
|
Trg_Delete_Nodes_fed |
DELETE |
|
Trg_Insert_Rates_fed |
INSERT |
|
Trg_Update_Rates_fed |
UPDATE |
|
Trg_Delete_Rates_fed | DELETE |
|
Trg_Insert_Service_Attributes_fed | INSERT |
|
Trg_Update_Service_Attributes_fed | UPDATE |
|
Trg_Delete_Service_Attributes_fed | DELETE |
|
Trg_Insert_astrad_confs_fed |
INSERT |
|
Trg_Update_astrad_confs_fed |
UPDATE |
| Trg_Delete_astrad_confs_fed | DELETE |
|
Trg_Insert_customer_confs_fed |
INSERT |
|
Trg_Update_customer_confs_fed |
UPDATE |
|
Trg_Delete_customer_confs_fed |
DELETE |
|
Trg_Insert_location_fed |
INSERT |
|
Trg_Update_location_fed | UPDATE |
|
Trg_Delete_location_fed |
DELETE |
+-----------------------------------+--------+
+--------------------+-------------------------+--------+
| EVENT_OBJECT_TABLE |
TRIGGER_NAME | EVENT. |
+--------------------+-------------------------+--------+
| ip_customers | Trg_Insert_ip_customers | INSERT |
| ip_customers | Trg_Update_ip_customers | UPDATE |
| ip_customers | Trg_Delete_ip_customers | DELETE |
| location2 | Trg_Insert_location2 | INSERT |
| location2 | Trg_Update_location2 | UPDATE |
| location2 | Trg_Delete_location2 | DELETE |
| realm | Trg_Insert_realm | INSERT |
| realm | Trg_Update_realm | UPDATE |
| realm | Trg_Delete_realm | DELETE |
| users | Trg_Insert_users | INSERT |
| users | Trg_Update_users | UPDATE |
| users | Trg_Delete_users | DELETE |
| vendors |
Trg_Insert_vendors | INSERT |
| vendors |
Trg_Update_vendors | UPDATE |
| vendors | Trg_Delete_vendors | DELETE |
+--------------------+-------------------------+--------+
+--------------------+-------------------------+--------+
| EVENT_OBJECT_TABLE |
TRIGGER_NAME | EVENT. |
+--------------------+-------------------------+--------+
| ip_customers | Trg_Insert_ip_customers | INSERT |
| ip_customers | Trg_Update_ip_customers | UPDATE |
| ip_customers | Trg_Delete_ip_customers | DELETE |
| realm | Trg_Insert_realm | INSERT |
| realm | Trg_Update_realm | UPDATE |
| realm | Trg_Delete_realm | DELETE |
| users | Trg_Insert_users | INSERT |
| users | Trg_Update_users | UPDATE |
| users | Trg_Delete_users | DELETE |
| vendors |
Trg_Insert_vendors | INSERT |
| vendors |
Trg_Update_vendors | UPDATE |
| vendors | Trg_Delete_vendors | DELETE |
+--------------------+-------------------------+--------+
+--------------------+-------------------------+--------+
| EVENT_OBJECT_TABLE |
TRIGGER_NAME | EVENT. |
+--------------------+-------------------------+--------+
| Accounts | Trg_Insert_Accounts | INSERT |
| Accounts | Trg_Update_Accounts | UPDATE |
| Accounts | Trg_Delete_Accounts | DELETE |
| Connections | Trg_Insert_Connections | INSERT |
| Connections | Trg_Update_Connections | UPDATE |
| Connections | Trg_Delete_Connections | DELETE |
| Customers | Trg_Update_Customers | UPDATE |
|
Nodes |
Trg_Insert_Nodes | INSERT |
|
Nodes | Trg_Update_Nodes | UPDATE |
| Nodes | Trg_Delete_Nodes | DELETE |
| astrad_confs | Trg_Insert_astrad_confs | INSERT |
| astrad_confs | Trg_Update_astrad_confs | UPDATE |
| astrad_confs | Trg_Delete_astrad_confs | DELETE |
+--------------------+-------------------------+--------+
+-------------------+
| Tables_in_astrad |
+-------------------+
| config |
| ip_customers |
|
location2 |
| location2_history |
| realm |
| registration |
| request |
| triggers_log |
| users |
| vendors |
+-------------------+
+--------------------+
| Tables_in_asterisk |
+--------------------+
| config |
| multiple_ua |
| sipdevices |
| sippeers |
| sipusers |
+--------------------+
+--------------------------+
| Tables_in_porta-billing |
+--------------------------+
| Accounts |
| Active_Calls |
| Connections |
| Customers |
| Destinations |
| Follow_Me |
| Nodes |
| Rates |
|
Service_Attribute_Values |
| astrad_confs |
| customer_confs |
+--------------------------+
+---------------------+
| Tables_in_porta-sip
|
+---------------------+
| location |
+---------------------+
Legend
mysql instance
triggers
federated tables
replication
server