Tables archiving user manual

Tables archiving user manual

Attention! One must make a full backup copy of the UTM5 database and check if it restores correctly BEFORE performing any operations on the database. The following manual is intended for specialists proficient in database manipulations. We highly recommend that you perform all the manipulations on a test machine BEFORE doing that on a working database.

Archiving is applied to the tables whose size is quickly increasing, to reduce the complexity and accompanying load for insert operations. Archiving a table means moving it to the archive (renaming it) and creating a new empty table with the same name and structure for new incoming data. Archiving is applied when a table reaches its size limit (described below).

1. Currently archiving is applied to the following tables:

2. Charges for consumed IP traffic are a subset of transactions of the account balance changes, which means that records in these tables are interconnected. If a discount_transactions_all table has a record with service_type 3 (IP traffic), the discount_transactions_iptraffic_all table must have a record with the same id, discount_date and discount. In other case the logical integrity of the database is considered to be violated.
3. Starting with version 5.2.1-006, an archives table was introduced. This table has the following fields:

• id – a unique record ID;
• archive_id – the archive ID, can't be set to 0, must be the same for all the tables that are being archived together;
• table_type – an archived table type (see above);
• table_name – an archived table name;
• start_date – the date of the first record in the archived tables (UNIX TIMESTAMP);
• end_date – the date of the last record in the archived tables (UNIX TIMESTAMP);

All archived tables should be listed in the archives table.

4. Every archived table must have exactly the same structure as the corresponding table in the currently installed UTM5 build.
5. Every archived table must contain every single record made in the time period, limited by the start_date and the end_date, stored in the corresponding archives table record. No other records (outside this time period) are permitted.
6. All the data that is not included in the archived tables, should be present in the current versions of those tables.
7. All the tables that archiving is applicable to, should be archived at the same time. The archived tables, created at the same time should have the same start_date, end_date and archive_id.
8. The minimum id field value for the current payment_transactions, dhs_sessions_log, dhs_sessions_detail, tel_sessions_log and tel_sessions_detail tables should be higher than the maximum id field value for the corresponding archived tables.
9. In UTM 5.2.1-006, only discount_transactions_all and discount_transactions_iptraffic_all tables were archived. In order to use those archived tables, one has to create archives for the rest of the tables that are included in the archive in the current version. This means that for each archived table one should create the missing archived tables for the same time period and move the data from the current tables to those archived tables. See the names of the fields, containing date, above.
10. Starting with version 5.3-002, dhcp_leases_log table (type 9) was added to the archived tables list. Furthermore, starting with version 5.3-001, the date field is not used anymore when archiving tel_sessions_detail table.
11. Tables that do not use the date field when archiving should be archived together with all the other tables they are associated with. Currently, there are the following table fields associations:

• tel_sessions_log.id = tel_sessions_detail.dhs_sess_id
• dhs_sessions_log.id = dhs_sessions_detail.dhs_sess_id
• invoices.id = invoice_entry.invoice_id
• invoice_entry.id = invoice_entry_details.entry_id

12. In order to simplify manual operations with archived tables, we recommend naming them using simple, intuitive names, like main_table_name_timestamp, where timestamp is the date of the archived table creation.
13. Transactions support is not mandatory for an archived table and it may be available for read access only.