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
  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                 self.path = path
  35
  36                 # We won't open the connection to the database directly
  37                 # so that we do not do it unnecessarily.
  38                 self._db = None
  39
  40         def __del__(self):
  41                 self._close_database()
  42
  43         def _open_database(self, path):
  44                 logger.debug("Opening database %s" % path)
  45
  46                 exists = os.path.exists(path)
  47
  48                 conn = sqlite3.connect(path, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
  49                 conn.isolation_level = None
  50
  51                 if not exists and self.is_writable():
  52                         logger.debug("Initialising database layout")
  53                         c = conn.cursor()
  54                         c.executescript("""
  55                                 CREATE TABLE updates (
  56                                         hostname  TEXT NOT NULL,
  57                                         status    TEXT NOT NULL,
  58                                         message   TEXT,
  59                                         timestamp timestamp NOT NULL
  60                                 );
  61
  62                                 CREATE TABLE settings (
  63                                         k TEXT NOT NULL,
  64                                         v TEXT NOT NULL
  65                                 );
  66
  67                                 CREATE INDEX idx_updates_hostname ON updates(hostname);
  68                         """)
  69                         c.execute("INSERT INTO settings(k, v) VALUES(?, ?)", ("version", "1"))
  70
  71                 return conn
  72
  73         def is_writable(self):
  74                 # Check if the database file exists and is writable.
  75                 ret = os.access(self.path, os.W_OK)
  76                 if ret:
  77                         return True
  78
  79                 # If not, we check if we are able to write to the directory.
  80                 # In that case the database file will be created in _open_database().
  81                 return os.access(os.path.dirname(self.path), os.W_OK)
  82
  83         def _close_database(self):
  84                 if self._db:
  85                         self._db_close()
  86                         self._db = None
  87
  88         def _execute(self, query, *parameters):
  89                 if self._db is None:
  90                         self._db = self._open_database(self.path)
  91
  92                 c = self._db.cursor()
  93                 try:
  94                         c.execute(query, parameters)
  95                 finally:
  96                         c.close()
  97
  98         def add_update(self, hostname, status, message=None):
  99                 if not self.is_writable():
 100                         logger.warning("Could not log any updates because the database is not writable")
 101                         return
 102
 103                 self._execute("INSERT INTO updates(hostname, status, message, timestamp) \
 104                         VALUES(?, ?, ?, ?)", hostname, status, message, datetime.datetime.utcnow())
 105
 106         def log_success(self, hostname):
 107                 logger.debug("Logging successful update for %s" % hostname)
 108
 109                 return self.add_update(hostname, "success")
 110
 111         def log_failure(self, hostname, exception):
 112                 if exception:
 113                         message = "%s: %s" % (exception.__class__.__name__, exception.reason)
 114                 else:
 115                         message = None
 116
 117                 logger.debug("Logging failed update for %s: %s" % (hostname, message or ""))
 118
 119                 return self.add_update(hostname, "failure", message=message)
 120
 121         def last_update(self, hostname, status=None):
 122                 """
 123                         Returns the timestamp of the last update (with the given status code).
 124                 """
 125                 if self._db is None:
 126                         self._db = self._open_database(self.path)
 127
 128                 c = self._db.cursor()
 129
 130                 try:
 131                         if status:
 132                                 c.execute("SELECT timestamp FROM updates WHERE hostname = ? AND status = ? \
 133                                         ORDER BY timestamp DESC LIMIT 1", (hostname, status))
 134                         else:
 135                                 c.execute("SELECT timestamp FROM updates WHERE hostname = ? \
 136                                         ORDER BY timestamp DESC LIMIT 1", (hostname,))
 137
 138                         for row in c:
 139                                 return row[0]
 140                 finally:
 141                         c.close()
 142
 143         def last_update_status(self, hostname):
 144                 """
 145                         Returns the update status of the last update.
 146                 """
 147                 if self._db is None:
 148                         self._db = self._open_database(self.path)
 149
 150                 c = self._db.cursor()
 151
 152                 try:
 153                         c.execute("SELECT status FROM updates WHERE hostname = ? \
 154                                 ORDER BY timestamp DESC LIMIT 1", (hostname,))
 155
 156                         for row in c:
 157                                 return row[0]
 158                 finally:
 159                         c.close()
 160
 161         def last_update_failure_message(self, hostname):
 162                 """
 163                         Returns the reason string for the last failed update (if any).
 164                 """
 165                 if self._db is None:
 166                         self._db = self._open_database(self.path)
 167
 168                 c = self._db.cursor()
 169
 170                 try:
 171                         c.execute("SELECT message FROM updates WHERE hostname = ? AND status = ? \
 172                                 ORDER BY timestamp DESC LIMIT 1", (hostname, "failure"))
 173
 174                         for row in c:
 175                                 return row[0]
 176                 finally:
 177                         c.close()