Experience in creating a conceptual billing system for ISP
Traditionally, when building billing systems, a database (DB) is used as a storage of source and sometimes aggregated data. In some cases, in addition to the main storage, an additional database is also used for prebilling purposes.
With this approach, the main requirement for the database management system (DBMS) used is the minimum time to perform the operations of changing, deleting, and inserting rows into database tables. All other DBMS features and capabilities, including the data organization model used, are of secondary importance. In this light, it seems logical (and economically) justified to use the popular MySQL class of DBMS in automated calculation systems .
Actually, the billing system itself is a certain set of software modules written in a variety of programming languages - from script to high-level.
It is important to note that in the end, each of these modules works with the data stored in the database. Depending on the purpose of the module, data is entered, deleted or modified, and almost always read.
The choice of a programming language for creating modules is a matter of taste and habit and is not of a fundamental nature, since for billing tasks the total execution time of a single operation is almost entirely determined by the speed of execution of queries to the database.
Before performing these operations, the module must provide a connection to the database. Two options are possible here: creating a connection for each operation or ensuring a permanent connection. Each of the options has its pros and cons, but it is obvious that both approaches require a certain consumption of computing resources of the system.
A paradoxical situation arises when a source of primary data, for example, a RADIUS server , itself sends them to the database. Some external software module designed to process this data should connect to the database, read the necessary data, and then return the result of its actions to the database. Separately, the question arises of determining the event in the system, which should lead to the launch of the module and the formation of conditions for selecting data to be processed.
Since the entire business logic of the system is outside the database, ensuring the correctness of the input data and their consistency lies entirely on the shoulders of the programmer-developer. Special efforts will also require the aspect of supporting transactional data operations.
The absence of such a structural element in the database as the view leads to the generation of a significant number of consecutive queries to different tables, which complicates the writing and debugging of program code and creates an additional load on the database server.
I would like to emphasize that billing is, first of all, a system of working with data, often of very significant volumes. And for working with data there is specialized software - DBMS. If you follow this simple logic, then the conclusion suggests itself that the emphasis in the design of an automated settlement system (ASR) should be shifted to the maximum use of the capabilities of modern DBMSs. This postulate formed the basis of the architecture of the ASBS “ASBS” brought to your attention.
In the presented concept, the core of billing is a database, so we approached the choice of a DBMS with all possible care. As a result, we settled on the object-relational database management system PostgreSQL. At the moment, this is the most developed of the existing open DBMS, which is a real alternative to commercial databases.
The consequence of choosing PostgreSQL as the kernel, 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 ASBS billing system was created, licensed on May 13, 2009 by Infocom Central Electric Network under No. OC-1-CT-0229. It is intended for use by telecom operators, Internet providers, commercial enterprises and organizations.
One of the main features of the system is the simplicity of the architecture.
The core of the system is a database running on a serverThe PostgreSQL . All system logic is in the database and is described by the relationships between entities, a set of constraints, checks, rules, triggers and functions. The opportunity provided by PostgreSQL to inherit tables within the framework of an object-oriented model was also useful. A careful study of the database structure, the selection of the optimal types of column data in tables, the use of the entire range of indexing mechanisms provided by the DBMS and the emphasis on the maximum possible use of triggers made it possible to build a highly efficient and reliable system. RADIUS server sessions and IP traffic
can serve as data sources for charging . The calculation of services and the corresponding write-offs occur in real time.
The movement of funds on personal accounts of clients 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 writing off a subscription fee for the services provided.
The management subsystem also provides loading and aggregation of IP traffic using NetFlow and SFlow protocols, permission and prohibition of access to the network, change of access speed, dynamic change of session parameters and their interruption, launch of procedures serving 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 connecting users to a dial-up network ( DIALUP ), with a static IP address, via 802.1x protocol, through tunnels ( VPN ) and using DHCP information option 82 (CLIPS) . The number of connected users is not licensed.
The operating experience of ASBS ASR by Internet access service providers has shown that the system confidently serves tens of thousands of customers without requiring any special requirements for server hardware.
On the official website of the system, in addition to additional information, you can get a fully functional demo access to all system interfaces.
With this approach, the main requirement for the database management system (DBMS) used is the minimum time to perform the operations of changing, deleting, and inserting rows into database tables. All other DBMS features and capabilities, including the data organization model used, are of secondary importance. In this light, it seems logical (and economically) justified to use the popular MySQL class of DBMS in automated calculation systems .
Actually, the billing system itself is a certain set of software modules written in a variety of programming languages - from script to high-level.
It is important to note that in the end, each of these modules works with the data stored in the database. Depending on the purpose of the module, data is entered, deleted or modified, and almost always read.
The choice of a programming language for creating modules is a matter of taste and habit and is not of a fundamental nature, since for billing tasks the total execution time of a single operation is almost entirely determined by the speed of execution of queries to the database.
Before performing these operations, the module must provide a connection to the database. Two options are possible here: creating a connection for each operation or ensuring a permanent connection. Each of the options has its pros and cons, but it is obvious that both approaches require a certain consumption of computing resources of the system.
A paradoxical situation arises when a source of primary data, for example, a RADIUS server , itself sends them to the database. Some external software module designed to process this data should connect to the database, read the necessary data, and then return the result of its actions to the database. Separately, the question arises of determining the event in the system, which should lead to the launch of the module and the formation of conditions for selecting data to be processed.
Since the entire business logic of the system is outside the database, ensuring the correctness of the input data and their consistency lies entirely on the shoulders of the programmer-developer. Special efforts will also require the aspect of supporting transactional data operations.
The absence of such a structural element in the database as the view leads to the generation of a significant number of consecutive queries to different tables, which complicates the writing and debugging of program code and creates an additional load on the database server.
I would like to emphasize that billing is, first of all, a system of working with data, often of very significant volumes. And for working with data there is specialized software - DBMS. If you follow this simple logic, then the conclusion suggests itself that the emphasis in the design of an automated settlement system (ASR) should be shifted to the maximum use of the capabilities of modern DBMSs. This postulate formed the basis of the architecture of the ASBS “ASBS” brought to your attention.
In the presented concept, the core of billing is a database, so we approached the choice of a DBMS with all possible care. As a result, we settled on the object-relational database management system PostgreSQL. At the moment, this is the most developed of the existing open DBMS, which is a real alternative to commercial databases.
The consequence of choosing PostgreSQL as the kernel, 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 ASBS billing system was created, licensed on May 13, 2009 by Infocom Central Electric Network under No. OC-1-CT-0229. It is intended for use by telecom operators, Internet providers, commercial enterprises and organizations.
One of the main features of the system is the simplicity of the architecture.
The core of the system is a database running on a serverThe PostgreSQL . All system logic is in the database and is described by the relationships between entities, a set of constraints, checks, rules, triggers and functions. The opportunity provided by PostgreSQL to inherit tables within the framework of an object-oriented model was also useful. A careful study of the database structure, the selection of the optimal types of column data in tables, the use of the entire range of indexing mechanisms provided by the DBMS and the emphasis on the maximum possible use of triggers made it possible to build a highly efficient and reliable system. RADIUS server sessions and IP traffic
can serve as data sources for charging . The calculation of services and the corresponding write-offs occur in real time.
The movement of funds on personal accounts of clients 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 writing off a subscription fee for the services provided.
The management subsystem also provides loading and aggregation of IP traffic using NetFlow and SFlow protocols, permission and prohibition of access to the network, change of access speed, dynamic change of session parameters and their interruption, launch of procedures serving 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 connecting users to a dial-up network ( DIALUP ), with a static IP address, via 802.1x protocol, through tunnels ( VPN ) and using DHCP information option 82 (CLIPS) . The number of connected users is not licensed.
The operating experience of ASBS ASR by Internet access service providers has shown that the system confidently serves tens of thousands of customers without requiring any special requirements for server hardware.
On the official website of the system, in addition to additional information, you can get a fully functional demo access to all system interfaces.
