def remove(self, thing):
if not self.is_reified():
raise sqlite3.DatabaseError("Can't remove, %s was not reified." %
self.__class__)
elif not thing.is_reified():
raise sqlite3.DatabaseError("Can't remove, %s was not reified." %
self.__class__)
def edit_damper(self,
old_number,
new_number,
new_type,
new_check_date,
new_location,
new_is_released=False,
new_notes=""):
"""Delete damper from the DB."""
conn = sqlite3.connect("dampers.db")
conn.execute(
"PRAGMA foreign_keys=1") # enable cascade deleting and updating.
cur = conn.cursor()
try:
cur.execute(
"SELECT d_types.id FROM d_types WHERE d_types.d_type=:new_type",
{"new_type": new_type})
except sqlite3.DatabaseError as err:
raise sqlite3.DatabaseError(err)
else:
types_id = cur.fetchone()[0]
sql = """\
UPDATE dampers
SET number=:new_number,
types_id=:types_id,
check_date=:new_check_date,
location=:new_location,
is_released=:new_is_released,
notes=:new_notes
WHERE number=:old_number;
"""
# By number because dampers.number is UNIQUE.
update_dict = {
"new_number": new_number,
"types_id": types_id,
"new_check_date": new_check_date,
"new_location": new_location,
"new_is_released": new_is_released,
"new_notes": new_notes,
"old_number": old_number
}
try:
cur.execute(sql, update_dict)
except sqlite3.DatabaseError as err:
raise sqlite3.DatabaseError(err)
else:
conn.commit() # complete transactions.
finally:
cur.close()
conn.close()
def __init__(self):
self.__logger = Logger()
self._request_exceptions = [type(item) for item in [requests.ConnectionError(), requests.HTTPError(),
requests.TooManyRedirects(), requests.Timeout(),
requests.TooManyRedirects(),
requests.RequestException(), requests.ConnectTimeout(),
requests.ReadTimeout()]]
self._system_errors = [type(item) for item in [KeyError(), AttributeError(), IndexError(),
ZeroDivisionError(), SystemError(), ValueError(),
AssertionError()]]
self._file_errors = [type(item) for item in [FileExistsError(), FileNotFoundError()]]
self._database_errors = [type(item) for item in [sqlite3.Error(), sqlite3.DataError(),
sqlite3.ProgrammingError(), sqlite3.DatabaseError(),
sqlite3.NotSupportedError(), sqlite3.IntegrityError(),
sqlite3.InterfaceError(), sqlite3.InternalError(),
sqlite3.OperationalError()]]
self._speech_recognizer_errors = [type(item) for item in
[sr.RequestError(), sr.UnknownValueError(), sr.WaitTimeoutError(),
sr.RequestError()]]
self.__logger.info('ExceptionsHandler was successfully initialized.', __name__)
def quarantine(self, reason):
"""
The database will be quarantined and a
sqlite3.DatabaseError will be raised indicating the action taken.
"""
prefix_path = os.path.dirname(self.db_dir)
partition_path = os.path.dirname(prefix_path)
dbs_path = os.path.dirname(partition_path)
device_path = os.path.dirname(dbs_path)
quar_path = os.path.join(device_path, 'quarantined',
self.db_type + 's',
os.path.basename(self.db_dir))
try:
renamer(self.db_dir, quar_path, fsync=False)
except OSError as e:
if e.errno not in (errno.EEXIST, errno.ENOTEMPTY):
raise
quar_path = "%s-%s" % (quar_path, uuid4().hex)
renamer(self.db_dir, quar_path, fsync=False)
detail = _('Quarantined %(db_dir)s to %(quar_path)s due to '
'%(reason)s') % {
'db_dir': self.db_dir,
'quar_path': quar_path,
'reason': reason
}
self.logger.error(detail)
raise sqlite3.DatabaseError(detail)
def execute_sqlite3(path, cmds):
"""Execute 'cmds' on SQLite database 'path'"""
import sqlite3
conn = sqlite3.connect(path)
cursor = conn.cursor()
# overwrites deleted content with zeros
# https://www.sqlite.org/pragma.html#pragma_secure_delete
from Options import options
if options.get('shred'):
cursor.execute('PRAGMA secure_delete=ON')
for cmd in cmds.split(';'):
try:
cursor.execute(cmd)
except sqlite3.DatabaseError, exc:
raise sqlite3.DatabaseError('%s: %s' %
(Common.decode_str(exc), path))
except sqlite3.OperationalError, exc:
logger = logging.getLogger(__name__)
if exc.message.find('no such function: ') >= 0:
# fixme: determine why randomblob and zeroblob are not
# available
logger.warning(exc.message)
else:
raise sqlite3.OperationalError('%s: %s' %
(Common.decode_str(exc), path))
def is_the_number_exists(self, the_number):
"""Search if exists in the dampers by the_number."""
conn = sqlite3.connect("dampers.db")
conn.execute(
"PRAGMA foreign_keys=1") # enable cascade deleting and updating.
cur = conn.cursor()
try:
cur.execute(
"SELECT dampers.number FROM dampers WHERE dampers.number=:the_number",
{"the_number": the_number})
# cur.execute("SELECT dampers.number FROM dampers WHERE dampers.number='{}';".format(the_number))
except sqlite3.DatabaseError as err:
raise sqlite3.DatabaseError(err)
else:
if cur.fetchone() is None:
return False # new_number not exists.
else:
return True # new_number exists.
# try:
# cur.__next__()
# except StopIteration:
# return False # the_number not exists.
# else:
# return True # the_number exists.
finally:
cur.close()
conn.close()
def get(self, **kwargs):
try:
if not Base.cursor:
raise sqlite3.DatabaseError('Need to open session')
foreign_fields = self._get_foreign_fields()
query_keys = self.get_keys(table=True)
for field in foreign_fields:
query_keys.extend(field[1].foreign_table().get_keys(table=True))
query = 'SELECT {} FROM {}'.format(",".join(query_keys), self.__table__)
for field in foreign_fields:
query += ' JOIN {0} ON {1}.{2}={0}.id'.format(field[1].foreign_table.__table__, self.__table__, field[0])
if len(kwargs) > 0:
query += ' WHERE '
filters = fields_to_sql_row(kwargs, self.__table__)
query += ' AND '.join(filters)
selected_data = Base.cursor.execute(query).fetchall()
selected_list = []
table_keys = self.get_keys(named_key=True)
foreign_list = []
for row in selected_data:
dict_row = {}
foreign_row = {}
for key, value in dict(row).items():
if key in table_keys:
dict_row[key.replace(self.__table__+'__', '', 1)] = value
foreign_row[key] = value
selected_list.append(dict_row)
if len(foreign_row) > 0:
foreign_list.append(foreign_row)
if len(foreign_fields) == 0:
return result.Result(self, selected_list)
return result.Result(self, selected_list, foreign_fields=foreign_fields, foreign_list=foreign_list)
except Exception as e:
print('Select fail : {}'.format(e))
return []
def __init__(self, db_name="coursehub.db"):
import os
base_path = os.path.dirname(os.path.abspath(__file__)) + "/"
self._db_path = base_path + db_name
self.db_conn = sqlite3.connect(self._db_path)
if self.db_conn is None:
raise sqlite3.DatabaseError("Could not establish a connection to the database.")
def isEmpty(self):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
self.cursor.execute("SELECT RECORD_ID FROM {0}".format(self.tableName))
numRecords = len(self.cursor.fetchall())
return numRecords == 0
def possibly_quarantine(self, exc_type, exc_value, exc_traceback):
"""
Checks the exception info to see if it indicates a quarantine situation
(malformed or corrupted database). If not, the original exception will
be reraised. If so, the database will be quarantined and a new
sqlite3.DatabaseError will be raised indicating the action taken.
"""
if 'database disk image is malformed' in str(exc_value):
exc_hint = 'malformed'
elif 'file is encrypted or is not a database' in str(exc_value):
exc_hint = 'corrupted'
else:
raise exc_type, exc_value, exc_traceback
prefix_path = os.path.dirname(self.db_dir)
partition_path = os.path.dirname(prefix_path)
dbs_path = os.path.dirname(partition_path)
device_path = os.path.dirname(dbs_path)
quar_path = os.path.join(device_path, 'quarantined',
self.db_type + 's',
os.path.basename(self.db_dir))
try:
renamer(self.db_dir, quar_path)
except OSError as e:
if e.errno not in (errno.EEXIST, errno.ENOTEMPTY):
raise
quar_path = "%s-%s" % (quar_path, uuid4().hex)
renamer(self.db_dir, quar_path)
detail = _('Quarantined %s to %s due to %s database') % \
(self.db_dir, quar_path, exc_hint)
self.logger.error(detail)
raise sqlite3.DatabaseError(detail)
def _create_db(self):
"""
Create database and tables if they not exist.
"""
conn = sqlite3.connect("dampers.db")
conn.execute(
"PRAGMA foreign_keys=1") # enable cascade deleting and updating.
cur = conn.cursor()
sql = """\
CREATE TABLE IF NOT EXISTS d_types(
id INTEGER PRIMARY KEY NOT NULL,
d_type TEXT UNIQUE NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS dampers(
id INTEGER PRIMARY KEY NOT NULL,
number TEXT UNIQUE NOT NULL COLLATE NOCASE,
types_id INTEGER NOT NULL,
check_date TEXT NOT NULL COLLATE NOCASE,
location TEXT UNIQUE NOT NULL COLLATE NOCASE,
is_released INTEGER NOT NULL,
notes TEXT COLLATE NOCASE,
FOREIGN KEY(types_id) REFERENCES d_types(id) ON DELETE CASCADE ON UPDATE CASCADE
);
"""
try:
cur.executescript(sql)
except sqlite3.DatabaseError as err:
raise sqlite3.DatabaseError(err) # ("Не удалось создать DB.")
else:
pass
finally:
cur.close()
conn.close()
def is_the_d_type_exists(self, the_d_type):
"""Search if exists in the d_types by damper type."""
conn = sqlite3.connect("dampers.db")
conn.execute(
"PRAGMA foreign_keys=1") # enable cascade deleting and updating.
cur = conn.cursor()
try:
cur.execute(
"SELECT d_types.d_type FROM d_types WHERE d_types.d_type=:d_type",
{"d_type": the_d_type})
# cur.execute("SELECT d_types.d_type FROM d_types WHERE d_types.d_type='{}';".format(the_d_type))
except sqlite3.DatabaseError as err:
raise sqlite3.DatabaseError(err)
else:
if cur.fetchone() is None:
return False # d_type not exists.
else:
return True # d_type exists.
# try:
# cur.__next__()
# except StopIteration:
# return False # d_type not exists.
# else:
# return True # d_type exists.
finally:
cur.close()
conn.close()
def execute_sqlite3(path, cmds):
"""Execute 'cmds' on SQLite database 'path'"""
import sqlite3
import contextlib
with contextlib.closing(sqlite3.connect(path)) as conn:
cursor = conn.cursor()
# overwrites deleted content with zeros
# https://www.sqlite.org/pragma.html#pragma_secure_delete
from bleachbit.Options import options
if options.get('shred'):
cursor.execute('PRAGMA secure_delete=ON')
for cmd in cmds.split(';'):
try:
cursor.execute(cmd)
except sqlite3.OperationalError as exc:
if str(exc).find('no such function: ') >= 0:
# fixme: determine why randomblob and zeroblob are not
# available
logger.exception(exc.message)
else:
raise sqlite3.OperationalError('%s: %s' % (exc, path))
except sqlite3.DatabaseError as exc:
raise sqlite3.DatabaseError('%s: %s' % (exc, path))
cursor.close()
conn.commit()
def get_types(self):
"""
Get list of types.
"""
d_types = []
conn = sqlite3.connect("dampers.db")
conn.execute(
"PRAGMA foreign_keys=1") # enable cascade deleting and updating.
cur = conn.cursor()
sql = """\
SELECT d_types.d_type
FROM d_types;
"""
try:
cur.execute(sql)
except sqlite3.DatabaseError as err:
raise sqlite3.DatabaseError(err)
else:
for d_type in cur:
d_types.append(d_type[0])
self.is_types_in_the_db = True if d_types else False
return d_types
finally:
cur.close()
conn.close()
async def test_purge_old_states_encouters_database_corruption(
hass: HomeAssistant,
async_setup_recorder_instance: SetupRecorderInstanceT):
"""Test database image image is malformed while deleting old states."""
instance = await async_setup_recorder_instance(hass)
await _add_test_states(hass, instance)
await async_wait_recording_done_without_instance(hass)
sqlite3_exception = DatabaseError("statement", {}, [])
sqlite3_exception.__cause__ = sqlite3.DatabaseError()
with patch("homeassistant.components.recorder.move_away_broken_database"
) as move_away, patch(
"homeassistant.components.recorder.purge.purge_old_data",
side_effect=sqlite3_exception,
):
await hass.services.async_call(recorder.DOMAIN, recorder.SERVICE_PURGE,
{"keep_days": 0})
await hass.async_block_till_done()
await async_wait_recording_done_without_instance(hass)
assert move_away.called
# Ensure the whole database was reset due to the database error
with session_scope(hass=hass) as session:
states_after_purge = session.query(States)
assert states_after_purge.count() == 0
def _connect_to_sqlite3(self):
""" private function to connect to the SQLite3 database """
try:
conn = sqlite3.connect(self._db)
return conn
except Exception as e:
raise sqlite3.DatabaseError("Error occurred while connecting to %s\n%s\n" % (self._db, e))
def storeRecord(self, sensorId: str, tempValue: float):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
insertParams = '''INSERT INTO {0} (SENSOR_ID, TEMP) VALUES (?,?);'''.format(self.tableName)
self.cursor.execute(insertParams, (sensorId, tempValue))
self.dbConn.commit()
def create_database(self):
"""
Create a database using the information supplied on creation.
:return:
"""
# If the database already exists...
create_new_database = True
if os.path.exists(self.database_location):
if self.database_is_valid() and not self.overwrite:
create_new_database = False
elif self.overwrite: # We just totally scrap the file. We could just drop the reference table...
self.delete_database()
else:
raise(sqlite3.DatabaseError("Database already exists, but is invalid for writing to."))
if create_new_database:
database_creation_statement = """
CREATE TABLE data(
row_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
ID VARCHAR,
Time DATETIME,
Value REAL,
Debug INTEGER
);
"""
with sqlite3.connect(self.database_location) as conn:
cur = conn.cursor()
cur.execute(database_creation_statement)
async def test_database_corruption_while_running(hass, tmpdir, caplog):
"""Test we can recover from sqlite3 db corruption."""
def _create_tmpdir_for_test_db():
return tmpdir.mkdir("sqlite").join("test.db")
test_db_file = await hass.async_add_executor_job(_create_tmpdir_for_test_db)
dburl = f"{SQLITE_URL_PREFIX}//{test_db_file}"
assert await async_setup_component(hass, DOMAIN, {DOMAIN: {CONF_DB_URL: dburl}})
await hass.async_block_till_done()
caplog.clear()
hass.states.async_set("test.lost", "on", {})
sqlite3_exception = DatabaseError("statement", {}, [])
sqlite3_exception.__cause__ = sqlite3.DatabaseError()
with patch.object(
hass.data[DATA_INSTANCE].event_session,
"close",
side_effect=OperationalError("statement", {}, []),
):
await async_wait_recording_done_without_instance(hass)
await hass.async_add_executor_job(corrupt_db_file, test_db_file)
await async_wait_recording_done_without_instance(hass)
with patch.object(
hass.data[DATA_INSTANCE].event_session,
"commit",
side_effect=[sqlite3_exception, None],
):
# This state will not be recorded because
# the database corruption will be discovered
# and we will have to rollback to recover
hass.states.async_set("test.one", "off", {})
await async_wait_recording_done_without_instance(hass)
assert "Unrecoverable sqlite3 database corruption detected" in caplog.text
assert "The system will rename the corrupt database file" in caplog.text
assert "Connected to recorder database" in caplog.text
# This state should go into the new database
hass.states.async_set("test.two", "on", {})
await async_wait_recording_done_without_instance(hass)
def _get_last_state():
with session_scope(hass=hass) as session:
db_states = list(session.query(States))
assert len(db_states) == 1
assert db_states[0].event_id > 0
return db_states[0].to_native()
state = await hass.async_add_executor_job(_get_last_state)
assert state.entity_id == "test.two"
assert state.state == "on"
hass.bus.async_fire(EVENT_HOMEASSISTANT_STOP)
await hass.async_block_till_done()
hass.stop()
def deleteSensor(self, sensorId: String):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
selectParams = '''DELETE FROM {0} WHERE ID = ?;'''.format(
self.tableName)
self.cursor.execute(selectParams, [sensorId])
def getRecordsByTempRange (self, lowTemp: float, highTemp: float):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
selectParams = '''SELECT RECORD_ID, SENSOR_ID, TEMP, DATETIME FROM {0} WHERE TEMP BETWEEN ? AND ?;'''.format(self.tableName)
self.cursor.execute(selectParams, [lowTemp, highTemp])
return self.cursor.fetchall()
def getRecordsBySensor (self, sensorId: str):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
selectParams = '''SELECT RECORD_ID, TEMP, DATETIME FROM {0} WHERE SENSOR_ID = ?;'''.format(self.tableName)
self.cursor.execute(selectParams, [sensorId])
return self.cursor.fetchall()
def updateSensorStatus(self, sensorId: String, enabled: boolean):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
insertParams = '''UPDATE {0} SET ENABLED = ? WHERE ID = ?;'''.format(
self.tableName)
self.cursor.execute(insertParams, [1 if enabled else 0, sensorId])
self.dbConn.commit()
def getSensor(self, sensorId: String):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
selectParams = '''SELECT ID, NAME, PATH, ENABLED FROM {0} WHERE ID = ?;'''.format(
self.tableName)
self.cursor.execute(selectParams, [sensorId])
return self.getSensorFromRow(self.cursor.fetchall())
def updateSensorPath(self, sensorId: String, path: String):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
insertParams = '''UPDATE {0} SET PATH = ? WHERE ID = ?;'''.format(
self.tableName)
self.cursor.execute(insertParams, [path, sensorId])
self.dbConn.commit()
def drop(self):
try:
if not Base.cursor:
raise sqlite3.DatabaseError('Need to open session')
Base.cursor.execute('DROP TABLE {}'.format(self.__table__))
Base.conn.commit()
except Exception as e:
print('Error on deleting table: {}'.format(e))
def deleteRecordsByDateTimeRange (self, startTime: datetime, endTime: datetime):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
# need some timestamp format validation here
selectParams = ''''DELETE FROM {0} WHERE DATETIME BETWEEN ? AND ?;'''.format(self.tableName)
self.cursor.execute(selectParams, [startTime, endTime])
def getRecordsByDateTimeRange (self, startTime: datetime, endTime: datetime):
if not self.isConnected():
raise sqlite3.DatabaseError("Database not connected")
# need some timestamp format validation here
selectParams = '''SELECT RECORD_ID, SENSOR_ID, TEMP, DATETIME FROM {0} WHERE DATETIME BETWEEN ? AND ?;'''.format(self.tableName)
self.cursor.execute(selectParams, [startTime, endTime])
return self.cursor.fetchall()
def _disconnect_from_sqlite3(self, conn=None):
""" private function to release the database connection """
if not conn:
return
try:
conn.close()
except Exception as e:
raise sqlite3.DatabaseError("Error occurred while closing connection\n%s\n" % e)
def fetch_data(self):
try:
conn = sqlite.connect(self.dbname)
cur = conn.cursor()
cur.execute("select * from %s_version;" % (self.badware_type))
row = cur.fetchall()
except sqlite.DatabaseError:
raise sqlite.DatabaseError(
"Error in a database specific operation.")
# TODO: Python 2.5 only, Python 2.4???
s = string.Template(self.url)
try:
assert row
self.version_number = row[0][0]
except AssertionError:
# Start from Version 1:-1
self.version_number = "1:-1"
self.final_url = s.safe_substitute(key=self.key,
badware_type=self.badware_type,
version=self.version_number)
self.fetch_url_pointer = urllib2.urlopen(self.final_url)
self.url_hashes_data = self.fetch_url_pointer.readlines()
if self.url_hashes_data == []:
# No data, so no point checking version
# number. This case might be because of
# throttling or no updates available.
return 0
for url_hash in self.url_hashes_data[1:-1]:
if re.match("^-\w+", url_hash):
cur.execute("delete from url_hashes_table where "
"badware_type='%s' and url_hash='%s';" %
(self.badware_code, url_hash[1:].strip()))
del self.url_hashes_data[self.url_hashes_data.index(url_hash)]
new_version_number = ":".join(
re.compile("\d\.\d+").search(
self.url_hashes_data[0]).group().split("."))
if self.version_number == "1:-1":
self.version_number = new_version_number
cur.execute("insert into %s_version (version_number) "
"values ('%s');" %
(self.badware_type, self.version_number))
else:
cur.execute(
"update %s_version set version_number='%s' "
"where version_number='%s';" %
(self.badware_type, new_version_number, self.version_number))
for url_hash in self.url_hashes_data[1:]:
if not url_hash == '\n':
cur.execute(
"insert into url_hashes_table (badware_type,url_hash) values ('%s','%s');"
% (self.badware_code, url_hash[1:].strip()))
cur.close()
conn.commit()
conn.close()
return 0