... | ... | @@ -85,4 +85,26 @@ The contents of the U-container are not persisted. They are stored in memory onl |
|
|
|
|
|
# MySQL and Postgres
|
|
|
|
|
|
Describe BALS for SQL here..... |
|
|
The SQL backends store information about allocated leases in the `lease4` and `lease6` tables. Similarly to the Memfile backend, finding an available lease using the information about allocated leases is inefficient and thus storing the information about unallocated leases is required.
|
|
|
|
|
|
The challenging part is how to efficiently populate the unallocated leases to the `lease4_avail` and `lease6_avail` tables. Let's start with the divagations about the IPv4 case first.
|
|
|
|
|
|
The available addresses are populated for individual address pools. For each such address pool we have to make a query which finds "gaps" in the `lease4` table and creates the corresponding entries in the `lease4_avail` table. IPv4 addresses are represented as 4 byte integers in the database. They can be sorted in the ascending order as a sequence of numbers and the typical techniques for finding gaps in a sequence of numbers can be applied.
|
|
|
|
|
|
The following query in PostgreSQL:
|
|
|
|
|
|
```sql
|
|
|
SELECT avail FROM generate_series(('192.0.2.10'::inet - '0.0.0.0'::inet),
|
|
|
('192.0.2.100'::inet - '0.0.0.0'::inet), 1) AS avail
|
|
|
LEFT JOIN lease4 on avail = lease4.address
|
|
|
WHERE lease4.address IS NULL;
|
|
|
```
|
|
|
|
|
|
selects all unallocated addresses (not found in the `lease4` table) for a pool of `192.0.2.10 - 192.0.2.100`. Those addresses can be inserted into the `lease4_avail` table to mark they are available for allocation. The `ON CONFLICT/DO NOTHING` construct can be used with this insert to bypass the duplicates if some of these entries already exist in the `lease4_avail` table.
|
|
|
|
|
|
The trigger should be added on the `lease4_avail` table to delete entries as a result of inserting leases into the `lease4` table. Similarly, deleting the lease from the `lease4` table should trigger insertion into the `lease4_avail` table.
|
|
|
|
|
|
*IPv6 to be described*
|
|
|
|
|
|
|
|
|
|