<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Exchange Server">
<!-- converted from text --><style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<meta content="text/html; charset=UTF-8">
<style type="text/css" style="">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div id="x_divtagdefaultwrapper" style="font-size:12pt; color:#000000; font-family:Calibri,Arial,Helvetica,sans-serif">
<p>Okay, so it's just simple `delete` statements. Looking at the schema, I'm going to have to alter the `lease4` table to add a column named "created" of type `timestamptz` with the default value for inserts set to `now()`. Please consider adding this column
in 1.2. Without it, keeping an audit trail will be basically impossible.</p>
<p><br>
</p>
<p>Scenario: we have to process DMCA requests. When that happens, we have to look back at our DHCP logs to see which devices would have had the targeted IP within the timeframe given in the request.</p>
<p><br>
</p>
<p>So what I'm going to do is add an `after delete on lease4 for each row execute procedure archive_lease()` trigger that will merely copy the deleted data over to an archive table. The archive table may have an addition "archived" `timestamptz` column.</p>
</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> Kea-users <kea-users-bounces@lists.isc.org> on behalf of Tomek Mrugalski <tomasz@isc.org><br>
<b>Sent:</b> Wednesday, February 1, 2017 5:39:04 PM<br>
<b>To:</b> kea-users@lists.isc.org<br>
<b>Subject:</b> Re: [Kea-users] PostgreSQL lease management</font>
<div> </div>
</div>
</div>
<font size="2"><span style="font-size:10pt;">
<div class="PlainText">W dniu 01.02.2017 o 22:09, James Sumners pisze:<br>
> Is there a document that describes how the leases database is managed<br>
> when it is stored in PostgreSQL? In particular, I want to look at the<br>
> queries that are involved, so something like [1] would be great.<br>
> <br>
> I want to devise a trigger to archive leases to another table when they<br>
> are being reaped.<br>
> <br>
> [1] —<br>
> <a href="http://kea.isc.org/wiki/HostReservationsHowTo#QueriesUsedbytheKeaServer">
http://kea.isc.org/wiki/HostReservationsHowTo#QueriesUsedbytheKeaServer</a><br>
Not in a such easy format to read, but the information is there.<br>
<br>
The schema itself is available in<br>
src/share/database/scripts/pgsql/dhcpdb_create.pgsql.<br>
<br>
The actual queries Kea code uses are in 2 files:<br>
<br>
src/lib/dhcpsrv/pgsql_lease_mgr.cc (for leases)<br>
src/lib/dhcpsrv/pgsql_host_data_source.cc (for host reservations)<br>
<br>
It's a C++ code, but SQL queries are there in plain text, just search<br>
for "tagged_statements". One way to browse those files would be our<br>
github repo: <a href="https://github.com/isc-projects/kea/">https://github.com/isc-projects/kea/</a><br>
<br>
On one hand writing such a document is useful, but on the other hand<br>
there's the danger of it being outdated without anyone noticing.<br>
<br>
Hope that helps,<br>
Tomek<br>
<br>
_______________________________________________<br>
Kea-users mailing list<br>
Kea-users@lists.isc.org<br>
<a href="https://lists.isc.org/mailman/listinfo/kea-users">https://lists.isc.org/mailman/listinfo/kea-users</a><br>
</div>
</span></font>
</body>
</html>