Added a new eDBregister_login2() to replace eDBregister_login() database function

This will save the access profile in the lastlog table.  However, it will not save
the VPN IP address and netmask any more.  This should be saved in the vpnaddr_history
table, using the eDBregister_vpnclientaddr() function.

eDBregister_login() is now just a wrapper around the eDBregister_login2(), ignoring
the access profile id and VPN addresses.  This exists purely as a compatibility layer
if the updated driver is used against an older eurephia-auth.so plug-in.

Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>

1 files changed, 48 insertions, 0 deletions

diff --git a/database/sqlite/sql-schema-delta-2.sql b/database/sqlite/sql-schema-delta-2.sql
new file mode 100644
index 0000000..52ca9ab
--- /dev/null
+++ b/database/sqlite/sql-schema-delta-2.sql
@@ -0,0 +1,48 @@
+--
+-- eurephia database schema for SQLite3
+--
+-- This SQL scripts updates the previous SQL schema to the
+-- new schema needed by edb-sqlite v1.4
+--
+-- GPLv2 only - Copyright (C) 2012
+--              David Sommerseth <dazo@users.sourceforge.net>
+--
+--  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 RENAME TO openvpn_lastlog_old;
+DROP INDEX  openvpn_lastlog_sessionkey;
+
+CREATE TABLE openvpn_lastlog (
+       uid              integer      ,
+       certid           integer      ,
+       accessprofile    integer      ,
+       protocol         varchar(4)   NOT NULL,
+       remotehost       varchar(128) NOT NULL,
+       remoteport       integer      NOT NULL,
+       sessionstatus    integer      NOT NULL DEFAULT 0,
+       sessionkey       varchar(128) ,
+       login            timestamp    ,
+       logout           timestamp    ,
+       session_deleted  timestamp    ,
+       session_duration timestamp,
+       bytes_sent       integer      ,
+       bytes_received   integer      ,
+       llid             integer      PRIMARY KEY AUTOINCREMENT
+);
+
+INSERT INTO openvpn_lastlog SELECT uid, certid, NULL, protocol, remotehost, remoteport, sessionstatus, sessionkey, login, logout, session_deleted, session_duration, bytes_sent, bytes_received, llid FROM openvpn_lastlog_old;
+UPDATE sqlite_sequence SET seq = (SELECT max(llid) FROM openvpn_lastlog) WHERE name = 'openvpn_lastlog';
+CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey);