42c343391a9de1b7471291d933b96080c8837cf0
[ddns.git] / 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                 c = self._db.cursor()
126
127                 try:
128                         if status:
129                                 c.execute("SELECT timestamp FROM updates WHERE hostname = ? AND status = ? \
130                                         ORDER BY timestamp DESC LIMIT 1", (hostname, status))
131                         else:
132                                 c.execute("SELECT timestamp FROM updates WHERE hostname = ? \
133                                         ORDER BY timestamp DESC LIMIT 1", (hostname,))
134
135                         for row in c:
136                                 return row[0]
137                 finally:
138                         c.close()
139
140         def last_update_status(self, hostname):
141                 """
142                         Returns the update status of the last update.
143                 """
144                 c = self._db.cursor()
145
146                 try:
147                         c.execute("SELECT status FROM updates WHERE hostname = ? \
148                                 ORDER BY timestamp DESC LIMIT 1", (hostname,))
149
150                         for row in c:
151                                 return row[0]
152                 finally:
153                         c.close()
154
155         def last_update_failure_message(self, hostname):
156                 """
157                         Returns the reason string for the last failed update (if any).
158                 """
159                 c = self._db.cursor()
160
161                 try:
162                         c.execute("SELECT message FROM updates WHERE hostname = ? AND status = ? \
163                                 ORDER BY timestamp DESC LIMIT 1", (hostname, "failure"))
164
165                         for row in c:
166                                 return row[0]
167                 finally:
168                         c.close()