database: Open database for the search operations, too
[oddments/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 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()