Imported eurephia into git

1 files changed, 105 insertions, 0 deletions

diff --git a/database/sqlite/sql-schema.sql b/database/sqlite/sql-schema.sql
new file mode 100644
index 0000000..d387d02
--- /dev/null
+++ b/database/sqlite/sql-schema.sql
@@ -0,0 +1,105 @@
+CREATE TABLE openvpn_certificates (
+       depth         integer      NOT NULL,
+       digest        varchar(64)  NOT NULL,
+       common_name   varchar(64)  NOT NULL,
+       organisation  varchar(64)  NOT NULL,
+       email         varchar(256) NOT NULL,
+       registered    timestamp    DEFAULT CURRENT_TIMESTAMP,
+       certid        integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE UNIQUE INDEX opevpn_certificates_digest ON openvpn_certificates(digest);
+
+CREATE TABLE openvpn_users (
+       username      varchar(32)  NOT NULL,
+       password      varchar(64)  NOT NULL,
+       activated     timestamp    ,
+       deactivated   timestamp    ,
+       last_accessed timestamp    ,
+       uid           integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_users(username);
+
+CREATE TABLE openvpn_usercerts (
+       uid           integer      NOT NULL,
+       certid        integer      NOT NULL,
+       accessprofile integer      ,
+       registered    timestamp    DEFAULT CURRENT_TIMESTAMP,
+       uicid         integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid);
+CREATE INDEX openvpn_usercerts_certid ON openvpn_usercerts(certid);
+
+CREATE TABLE openvpn_accesses (
+       access_descr   varchar(128) ,
+       fw_profile     varchar(64)  NOT NULL,
+       accessprofile  integer      PRIMARY KEY AUTOINCREMENT
+);
+
+CREATE TABLE openvpn_lastlog (
+       uid            integer      ,
+       certid         integer      ,
+       protocol       varchar(4)   NOT NULL,
+       remotehost     varchar(128) NOT NULL,
+       remoteport     integer      NOT NULL,
+       macaddr        varchar(20)  ,
+       vpnipaddr      varchar(32)  NOT NULL,
+       vpnipmask      varchar(32)  NOT NULL,
+       sessionstatus  integer      NOT NULL DEFAULT 0,
+       sesskey        varchar(128) ,
+       login          timestamp    ,
+       logout         timestamp    ,
+       session_del    timestamp    ,
+       bytes_sent     integer      ,
+       bytes_received integer      ,
+       llid           integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE UNIQUE INDEX openvpn_lastlog_sesskey ON openvpn_lastlog(sesskey);
+
+CREATE TABLE openvpn_sessions (
+       sesskey        varchar(128) NOT NULL,
+       datakey        varchar(256) NOT NULL,
+       dataval        text         ,
+       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
+       sessid         integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE INDEX openvpn_sessions_sesskey ON openvpn_sessions(sesskey);
+CREATE UNIQUE INDEX openvpn_sessions_sess_datakey ON openvpn_sessions(sesskey, datakey);
+
+CREATE TABLE openvpn_blacklist (
+       digest         varchar(64)  ,
+       username       varchar(32)  ,
+       remoteip       varchar(32)  ,
+       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
+       last_accessed  timestamp    ,
+       blid           integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE UNIQUE INDEX openvpn_blacklist_digest ON openvpn_blacklist(digest);
+CREATE UNIQUE INDEX openvpn_blacklist_username ON openvpn_blacklist(username);
+CREATE UNIQUE INDEX openvpn_blacklist_remoteip ON openvpn_blacklist(remoteip);
+
+CREATE TABLE openvpn_attempts (
+       username       varchar(32)  ,
+       digest         varchar(64)  ,
+       remoteip       varchar(32)  ,
+       attempts       integer      DEFAULT 0,
+       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
+       last_attempt   timestamp    ,
+       atpid          integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE UNIQUE INDEX openvpn_attempts_username ON openvpn_attempts(username);
+CREATE UNIQUE INDEX openvpn_attempts_digest ON openvpn_attempts(digest);
+CREATE UNIQUE INDEX openvpn_attempts_remoteip ON openvpn_attempts(remoteip);
+
+CREATE TABLE openvpn_sessionkeys (
+       sessionseed      varchar(64)  NOT NULL,
+       sessionkey       varchar(64)  NOT NULL,
+       PRIMARY KEY(sessionseed)
+);
+
+CREATE TABLE openvpn_config (
+       datakey        varchar(64)  NOT NULL,
+       dataval        text         ,
+       cfgid          integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE UNIQUE INDEX openvpn_config_key ON openvpn_config(datakey);
+