--
-- 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 <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 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;