src/ddns/database.py

   1 #!/usr/bin/python
   2 ###############################################################################
   3 #                                                                             #
   4 # ddns - A dynamic DNS client for IPFire                                      #
   5 # Copyright (C) 2014 IPFire development team                                  #
   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 import datetime
  23 import os.path
  24 import sqlite3
  25
  26 # Initialize the logger.
  27 import logging
  28 logger = logging.getLogger("ddns.database")
  29 logger.propagate = 1
  30
  31 class DDNSDatabase(object):
  32         def __init__(self, core, path):
  33                 self.core = core
  34
  35                 # Open the database file
  36                 self._db = self._open_database(path)
  37
  38         def __del__(self):
  39                 self._close_database()
  40
  41         def _open_database(self, path):
  42                 logger.debug("Opening database %s" % path)
  43
  44                 exists = os.path.exists(path)
  45
  46                 conn = sqlite3.connect(path, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
  47                 conn.isolation_level = None
  48
  49                 if not exists:
  50                         logger.debug("Initialising database layout")
  51                         c = conn.cursor()
  52                         c.executescript("""
  53                                 CREATE TABLE updates (
  54                                         hostname  TEXT NOT NULL,
  55                                         status    TEXT NOT NULL,
  56                                         message   TEXT,
  57                                         timestamp timestamp NOT NULL
  58                                 );
  59
  60                                 CREATE TABLE settings (
  61                                         k TEXT NOT NULL,
  62                                         v TEXT NOT NULL
  63                                 );
  64
  65                                 CREATE INDEX idx_updates_hostname ON updates(hostname);
  66                         """)
  67                         c.execute("INSERT INTO settings(k, v) VALUES(?, ?)", ("version", "1"))
  68
  69                 return conn
  70
  71         def _close_database(self):
  72                 if self._db:
  73                         self._db_close()
  74                         self._db = None
  75
  76         def _execute(self, query, *parameters):
  77                 c = self._db.cursor()
  78                 try:
  79                         c.execute(query, parameters)
  80                 finally:
  81                         c.close()
  82
  83         def add_update(self, hostname, status, message=None):
  84                 self._execute("INSERT INTO updates(hostname, status, message, timestamp) \
  85                         VALUES(?, ?, ?, ?)", hostname, status, message, datetime.datetime.utcnow())
  86
  87         def log_success(self, hostname):
  88                 logger.debug("Logging successful update for %s" % hostname)
  89
  90                 return self.add_update(hostname, "success")
  91
  92         def log_failure(self, hostname, exception):
  93                 if exception:
  94                         message = "%s: %s" % (exception.__class__.__name__, exception.reason)
  95                 else:
  96                         message = None
  97
  98                 logger.debug("Logging failed update for %s: %s" % (hostname, message or ""))
  99
 100                 return self.add_update(hostname, "failure", message=message)
 101
 102         def last_update(self, hostname, status=None):
 103                 """
 104                         Returns the timestamp of the last update (with the given status code).
 105                 """
 106                 c = self._db.cursor()
 107
 108                 try:
 109                         if status:
 110                                 c.execute("SELECT timestamp FROM updates WHERE hostname = ? AND status = ? \
 111                                         ORDER BY timestamp DESC LIMIT 1", (hostname, status))
 112                         else:
 113                                 c.execute("SELECT timestamp FROM updates WHERE hostname = ? \
 114                                         ORDER BY timestamp DESC LIMIT 1", (hostname,))
 115
 116                         for row in c:
 117                                 return row[0]
 118                 finally:
 119                         c.close()
 120
 121         def last_update_status(self, hostname):
 122                 """
 123                         Returns the update status of the last update.
 124                 """
 125                 c = self._db.cursor()
 126
 127                 try:
 128                         c.execute("SELECT status FROM updates WHERE hostname = ? \
 129                                 ORDER BY timestamp DESC LIMIT 1", (hostname,))
 130
 131                         for row in c:
 132                                 return row[0]
 133                 finally:
 134                         c.close()
 135
 136         def last_update_failure_message(self, hostname):
 137                 """
 138                         Returns the reason string for the last failed update (if any).
 139                 """
 140                 c = self._db.cursor()
 141
 142                 try:
 143                         c.execute("SELECT message FROM updates WHERE hostname = ? AND status = ? \
 144                                 ORDER BY timestamp DESC LIMIT 1", (hostname, "failure"))
 145
 146                         for row in c:
 147                                 return row[0]
 148                 finally:
 149                         c.close()