From 2ebf7e3a91fee5663ead92474ec6fca7908cb78d Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Thu, 13 Sep 2012 17:56:45 +0200 Subject: sqlite3: Implement eDBregister_vpnclientaddr() This commit implements the eDBregister_vpnclientaddr() needed by the newer eurephia-auth plug-in. This is needed to improve the tun support in eurephia. In addition, this also updates the SQL schema to include IPv4 and in the future IPv6 addresses in the lastlog and VPN address history (openvpn_vpnaddr_history). The old openvpn_macaddr_history table is deprecated. Signed-off-by: David Sommerseth --- database/sqlite/sql-schema-delta-1.sql | 47 ++++++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) create mode 100644 database/sqlite/sql-schema-delta-1.sql (limited to 'database/sqlite/sql-schema-delta-1.sql') diff --git a/database/sqlite/sql-schema-delta-1.sql b/database/sqlite/sql-schema-delta-1.sql new file mode 100644 index 0000000..05028ff --- /dev/null +++ b/database/sqlite/sql-schema-delta-1.sql @@ -0,0 +1,47 @@ +-- +-- eurephia database schema for SQLite3 +-- +-- This SQL scripts updates the previous SQL schema to the +-- new schema needed by edb-sqlite v1.3 +-- +-- GPLv2 only - Copyright (C) 2012 +-- David Sommerseth +-- +-- This program is free software; you can redistribute it and/or +-- modify it under the terms of the GNU General Public License +-- as published by the Free Software Foundation; version 2 +-- of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. +-- + + +ALTER TABLE openvpn_lastlog ADD COLUMN vpnipv6addr varchar(48); + +-- openvpn_vpnaddr_history - This keeps an overview over which VPN client addresses +-- a session have used, in case the client changes the address. +-- This table replaces openvpn_macaddr_history. +CREATE TABLE openvpn_vpnaddr_history ( + sessionkey varchar(64) NOT NULL, + macaddr varchar(20) NOT NULL, + ip4addr varchar(32) , + ip6addr varchar(48) , + registered timestamp DEFAULT CURRENT_TIMESTAMP, + semaid integer PRIMARY KEY AUTOINCREMENT +); +CREATE INDEX openvpn_vpnaddr_hist_sessionkey ON openvpn_vpnaddr_history(sessionkey); +CREATE INDEX openvpn_vpnaddr_hist_macaddr ON openvpn_vpnaddr_history(macaddr); + +-- Copy over the old data from openvpn_macaddr_history to openvpn_vpnaddr_history +INSERT INTO openvpn_vpnaddr_history (sessionkey, macaddr, registered, semaid) + SELECT sessionkey, macaddr, registered, semaid + FROM openvpn_macaddr_history + ORDER BY semaid; + -- cgit