Experience in creating a conceptual billing system for ISP
Traditionally, when building billing systems, the database (DB) is used as a repository of source and sometimes aggregated data. In some cases, in addition to the main repository, an additional database is also used for the purposes of pre-billing.
With this approach, the main requirement for the used database management system (DBMS) is the minimum time to perform changes, deletion and insertion of rows into database tables. All other properties and capabilities of the DBMS, including the data organization model used, are of secondary importance. In this light, logically (and economically) the use of the popular MySQL class DBMS in automated calculation systems seems justified.
Actually the billing system itself is a certain set of software modules written in various programming languages - from script to high-level.
')
It is important to note that in the end, each of these modules works with data stored in the database. Depending on the purpose of the module, data entry, deletion or modification operations are performed, and almost always reads as well.
The choice of a programming language for creating modules is a matter of taste and habits and is not of a fundamental nature, since for billing tasks the total time for performing a single operation is almost entirely determined by the speed of execution of database queries.
Before performing these operations, the module must provide a connection to the database. There are two possible options: creating a connection to perform each operation or providing a permanent connection. Each of the options has its pros and cons, however, it is obvious that both approaches require a certain expenditure of system computing resources.
A paradoxical situation occurs when a source of primary data, such as a RADIUS server , sends it to the database itself. Some external software module designed to process this data must connect to the database, read the necessary data, and then return the result of its actions to the database. Separately, the question of defining an event in the system arises, which should lead to the launch of the module and the formation of conditions for sampling data to be processed.
Since the entire business logic of the system has been moved beyond the limits of the database, ensuring the correctness of the input data and their consistency rests entirely with the programmer-developer. Special efforts will also be required in the aspect of supporting the transactionality of data operations.
The absence in the database of such a structural element as a view leads to the generation of a significant number of consecutive queries to different tables, which makes it difficult to write and debug program code and creates an additional load on the database server.
I would like to emphasize that billing is, first of all, a system for working with data, often of very significant amounts. And for working with data there is a specialized software - DBMS. If you follow this simple logic, the conclusion suggests itself that the emphasis in designing an automated billing system (ASR) should be shifted to the maximum use of the capabilities of modern DBMS. This postulate formed the basis for the architecture of ASR “ASBS” brought to your attention.
In the presented concept, the core of the billing is the database, so we approached the choice of DBMS with all possible care. As a result, we stopped at the PostgreSQL object-relational database management system. At the moment it is the most developed of the existing open DBMS, which is a real alternative to commercial databases.
The consequence of choosing PostgreSQL as the core system, distributed under the liberal OSD license, was the decision to build the entire ASR on open source software.
The plan was implemented and as a result, the billing system “ASBS” was created, licensed on May 13, 2009, by Infocom CES under number OC-1-CT-0229. It is intended for use by telecom operators, Internet service providers, commercial enterprises and organizations.
One of the main features of the system was the simplicity of the architecture.
The core of the system is a database running under the PostgreSQL server. All system logic is in the database and is described by relationships between entities, a set of constraints, checks, rules, triggers, and functions. The possibility of inheriting tables within the object-oriented model provided by PostgreSQL also came in handy. Careful study of the structure of the database, the choice of the optimal data types of columns in the tables, the use of the entire range of indexing mechanisms provided by the DBMS, and an emphasis on the maximum possible use of triggers made it possible to build a high-performance and reliable system.
Sources of data for billing can serve as a RADIUS server session and IP traffic. Accounting services and the corresponding write-offs occur in real time.
The movement of funds through customer accounts is provided by WEB interfaces of system users and subscribers, written using HTML , PHP and JavaScript , interfaces to automated payment systems (electronic payments, 1C products, etc.) and a management subsystem that produces periodic charge-off for services provided.
The management subsystem also provides the loading and aggregation of IP traffic using the NetFlow and SFlow protocols , allowing and denying access to the network, changing the access speed, dynamically changing session parameters and interrupting them, and launching procedures that maintain the system. It is built on the pgAgent task scheduler , which directly executes SQL queries to the database or runs external control scripts.
The system supports user connections to the dial-up network ( DIALUP ), with a static IP address, via the 802.1x protocol, through tunnels ( VPN ) and using the DHCP information option 82 (CLIPS) . The number of connected users is not licensed.
Operating experience of ASR ASBS by Internet access service providers has shown that the system confidently serves tens of thousands of clients without setting special requirements for server equipment.
On the official website of the system, in addition to additional information, you can get a full-featured demo access to all system interfaces.
With this approach, the main requirement for the used database management system (DBMS) is the minimum time to perform changes, deletion and insertion of rows into database tables. All other properties and capabilities of the DBMS, including the data organization model used, are of secondary importance. In this light, logically (and economically) the use of the popular MySQL class DBMS in automated calculation systems seems justified.
Actually the billing system itself is a certain set of software modules written in various programming languages - from script to high-level.
')
It is important to note that in the end, each of these modules works with data stored in the database. Depending on the purpose of the module, data entry, deletion or modification operations are performed, and almost always reads as well.
The choice of a programming language for creating modules is a matter of taste and habits and is not of a fundamental nature, since for billing tasks the total time for performing a single operation is almost entirely determined by the speed of execution of database queries.
Before performing these operations, the module must provide a connection to the database. There are two possible options: creating a connection to perform each operation or providing a permanent connection. Each of the options has its pros and cons, however, it is obvious that both approaches require a certain expenditure of system computing resources.
A paradoxical situation occurs when a source of primary data, such as a RADIUS server , sends it to the database itself. Some external software module designed to process this data must connect to the database, read the necessary data, and then return the result of its actions to the database. Separately, the question of defining an event in the system arises, which should lead to the launch of the module and the formation of conditions for sampling data to be processed.
Since the entire business logic of the system has been moved beyond the limits of the database, ensuring the correctness of the input data and their consistency rests entirely with the programmer-developer. Special efforts will also be required in the aspect of supporting the transactionality of data operations.
The absence in the database of such a structural element as a view leads to the generation of a significant number of consecutive queries to different tables, which makes it difficult to write and debug program code and creates an additional load on the database server.
I would like to emphasize that billing is, first of all, a system for working with data, often of very significant amounts. And for working with data there is a specialized software - DBMS. If you follow this simple logic, the conclusion suggests itself that the emphasis in designing an automated billing system (ASR) should be shifted to the maximum use of the capabilities of modern DBMS. This postulate formed the basis for the architecture of ASR “ASBS” brought to your attention.
In the presented concept, the core of the billing is the database, so we approached the choice of DBMS with all possible care. As a result, we stopped at the PostgreSQL object-relational database management system. At the moment it is the most developed of the existing open DBMS, which is a real alternative to commercial databases.
The consequence of choosing PostgreSQL as the core system, distributed under the liberal OSD license, was the decision to build the entire ASR on open source software.
The plan was implemented and as a result, the billing system “ASBS” was created, licensed on May 13, 2009, by Infocom CES under number OC-1-CT-0229. It is intended for use by telecom operators, Internet service providers, commercial enterprises and organizations.
One of the main features of the system was the simplicity of the architecture.
The core of the system is a database running under the PostgreSQL server. All system logic is in the database and is described by relationships between entities, a set of constraints, checks, rules, triggers, and functions. The possibility of inheriting tables within the object-oriented model provided by PostgreSQL also came in handy. Careful study of the structure of the database, the choice of the optimal data types of columns in the tables, the use of the entire range of indexing mechanisms provided by the DBMS, and an emphasis on the maximum possible use of triggers made it possible to build a high-performance and reliable system.
Sources of data for billing can serve as a RADIUS server session and IP traffic. Accounting services and the corresponding write-offs occur in real time.
The movement of funds through customer accounts is provided by WEB interfaces of system users and subscribers, written using HTML , PHP and JavaScript , interfaces to automated payment systems (electronic payments, 1C products, etc.) and a management subsystem that produces periodic charge-off for services provided.
The management subsystem also provides the loading and aggregation of IP traffic using the NetFlow and SFlow protocols , allowing and denying access to the network, changing the access speed, dynamically changing session parameters and interrupting them, and launching procedures that maintain the system. It is built on the pgAgent task scheduler , which directly executes SQL queries to the database or runs external control scripts.
The system supports user connections to the dial-up network ( DIALUP ), with a static IP address, via the 802.1x protocol, through tunnels ( VPN ) and using the DHCP information option 82 (CLIPS) . The number of connected users is not licensed.
Operating experience of ASR ASBS by Internet access service providers has shown that the system confidently serves tens of thousands of clients without setting special requirements for server equipment.
On the official website of the system, in addition to additional information, you can get a full-featured demo access to all system interfaces.
Source: https://habr.com/ru/post/114096/
All Articles