functions.db

   1 #!/bin/bash
   2 ###############################################################################
   3 #                                                                             #
   4 # IPFire.org - A linux based firewall                                         #
   5 # Copyright (C) 2010  Michael Tremer & Christian Schmidt                      #
   6 #                                                                             #
   7 # This program is free software: you can redistribute it and/or modify        #
   8 # it under the terms of the GNU General Public License as published by        #
   9 # the Free Software Foundation, either version 3 of the License, or           #
  10 # (at your option) any later version.                                         #
  11 #                                                                             #
  12 # This program is distributed in the hope that it will be useful,             #
  13 # but WITHOUT ANY WARRANTY; without even the implied warranty of              #
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               #
  15 # GNU General Public License for more details.                                #
  16 #                                                                             #
  17 # You should have received a copy of the GNU General Public License           #
  18 # along with this program.  If not, see <http://www.gnu.org/licenses/>.       #
  19 #                                                                             #
  20 ###############################################################################
  21
  22 function db_connection_init() {
  23         if [ -e "${DB_CONNECTION_FILE}" ]; then
  24                 return ${EXIT_OK}
  25         fi
  26
  27         log DEBUG "Creating connection database ${DB_CONNECTION_FILE}."
  28
  29         sqlite3 -batch ${DB_CONNECTION_FILE} <<EOF
  30 CREATE TABLE log(
  31         id INTEGER PRIMARY KEY AUTOINCREMENT,
  32         zone TEXT,
  33         time INTEGER,
  34         state TEXT
  35 );
  36
  37 CREATE VIEW current as
  38         SELECT zone, time, state FROM log GROUP BY zone;
  39
  40 EOF
  41 }
  42
  43 function db_connection_update() {
  44         local zone=${1}
  45         local action=${2}
  46         shift 2
  47
  48         db_connection_init
  49
  50         log DEBUG "Writing connection to database: zone=${zone} action=${action}."
  51
  52         sqlite3 -batch ${DB_CONNECTION_FILE} <<EOF
  53 INSERT INTO log(zone, time, state)
  54         VALUES('${zone}', strftime('%s', 'now', 'utc'), '${action}');
  55 EOF
  56 }
  57
  58 function db_ppp_init() {
  59         local file=${1}
  60
  61         if [ -e "${file}" ]; then
  62                 return ${EXIT_OK}
  63         fi
  64
  65         log DEBUG "Creating ppp database ${file}."
  66
  67         sqlite3 -batch ${file} <<EOF
  68 CREATE TABLE accounting(
  69         id INTEGER PRIMARY KEY AUTOINCREMENT,
  70         time INTEGER,
  71         duration INTEGER,
  72         rcvd INTEGER,
  73         sent INTEGER
  74 );
  75 EOF
  76 }
  77
  78 function db_ppp_update() {
  79         local zone=${1}
  80         shift
  81
  82         local rcvd
  83         local sent
  84         local duration
  85
  86         while [ $# -gt 0 ]; do
  87                 case "${1}" in
  88                         --rcvd=*)
  89                                 rcvd=${1#--rcvd=}
  90                                 ;;
  91                         --sent=*)
  92                                 sent=${1#--sent=}
  93                                 ;;
  94                         --duration=*)
  95                                 duration=${1#--duration=}
  96                                 ;;
  97                 esac
  98                 shift
  99         done
 100
 101         local file="${LOG_DIR}/ppp_${zone}.db"
 102
 103         db_ppp_init ${file}
 104
 105         local time=$(( $(date -u +"%s") - ${duration} ))
 106
 107         log DEBUG "Writing accounting data: time=${time} duration=${duration} rcvd=${rcvd} sent=${sent}."
 108
 109         sqlite3 -batch ${file} <<EOF
 110 INSERT INTO accounting(time, duration, rcvd, sent)
 111         VALUES('${time}', '${duration}', '${rcvd}', '${sent}');
 112 EOF
 113 }