def _init_db(stats_db_dict, stats_schema):
"""Create and initialize a database."""
# First, create the database if necessary. If it already exists, an
# exception will be thrown.
try:
weedb.create(stats_db_dict)
except weedb.DatabaseExists:
pass
# Get a connection
_connect = weedb.connect(stats_db_dict)
try:
# Now create all the necessary tables as one transaction:
with weedb.Transaction(_connect) as _cursor:
for _stats_tuple in stats_schema:
# Get the SQL string necessary to create the type:
_sql_create_str = _sql_create_string_factory(_stats_tuple)
_cursor.execute(_sql_create_str)
# Now create the meta table:
_cursor.execute(meta_create_str)
# Set the unit system to 'None' (Unknown) for now
_cursor.execute(meta_replace_str, ('unit_system', 'None'))
# Finally, save the stats schema:
StatsDb._save_schema(_cursor, stats_schema)
except Exception, e:
_connect.close()
syslog.syslog(syslog.LOG_ERR, "stats: Unable to create stats database.")
syslog.syslog(syslog.LOG_ERR, "**** %s" % (e,))
raise
def test_transaction(self):
# Create the database and schema
weedb.create(self.db_dict)
_connect = weedb.connect(self.db_dict)
# With sqlite, a rollback can roll back a table creation. With MySQL, it does not. So,
# create the table outside of the transaction. We're not as concerned about a transaction failing
# when creating a table, because it only happens the first time weewx starts up.
_connect.execute(
"""CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, x REAL );"""
)
# We're going to trigger the rollback by raising a bogus exception. Be prepared to catch it.
try:
with weedb.Transaction(_connect) as _cursor:
for i in range(10):
_cursor.execute(
"""INSERT INTO test1 (dateTime, x) VALUES (?, ?)""",
(i, i + 1))
# Raise an exception:
raise Exception("Bogus exception")
except Exception:
pass
# Now make sure nothing is in the database
_connect = weedb.connect(self.db_dict)
_cursor = _connect.cursor()
_cursor.execute("SELECT dateTime, x from test1")
_row = _cursor.fetchone()
_cursor.close()
_connect.close()
self.assertEqual(_row, None)
def test_no_tables(self):
weedb.create(self.db_dict)
_connect = weedb.connect(self.db_dict)
self.assertEqual(_connect.tables(), [])
self.assertRaises(weedb.ProgrammingError, _connect.columnsOf, 'test1')
self.assertRaises(weedb.ProgrammingError, _connect.columnsOf, 'foo')
_connect.close()
def _create_table(archive_db_dict, archiveSchema, table):
"""Create a SQL table using a given archive schema.
archive_db_dict: A database dictionary holding the information
necessary to open the database.
archiveSchema: The schema to be used
table: The name of the table to be used within the database.
Returns:
A connection"""
# First try to create the database. If it already exists, an exception
# will be thrown.
try:
weedb.create(archive_db_dict)
except weedb.DatabaseExists:
pass
# List comprehension of the types, joined together with commas. Put
# the SQL type in backquotes, because at least one of them ('interval')
# is a MySQL reserved word
_sqltypestr = ', '.join(["`%s` %s" % _type for _type in archiveSchema])
_connect = weedb.connect(archive_db_dict)
try:
with weedb.Transaction(_connect) as _cursor:
_cursor.execute("CREATE TABLE %s (%s);" % (table, _sqltypestr))
except Exception, e:
_connect.close()
syslog.syslog(syslog.LOG_ERR, "archive: Unable to create table '%s' in database '%s': %s" %
(table, os.path.basename(archive_db_dict['database']), e))
raise
def test_transaction(self):
# Create the database and schema
weedb.create(self.db_dict)
with weedb.connect(self.db_dict) as _connect:
# With sqlite, a rollback can roll back a table creation. With MySQL, it does not. So,
# create the table outside of the transaction. We're not as concerned about a transaction failing
# when creating a table, because it only happens the first time weewx starts up.
_connect.execute("""CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, x REAL );""")
# We're going to trigger the rollback by raising a bogus exception. Be prepared to catch it.
try:
with weedb.Transaction(_connect) as _cursor:
for i in range(10):
_cursor.execute("""INSERT INTO test1 (dateTime, x) VALUES (?, ?)""", (i, i+1))
# Raise an exception:
raise Exception("Bogus exception")
except Exception:
pass
# Now make sure nothing is in the database
with weedb.connect(self.db_dict) as _connect:
with _connect.cursor() as _cursor:
_cursor.execute("SELECT dateTime, x from test1")
_row = _cursor.fetchone()
self.assertEqual(_row, None)
def test_variable(self):
weedb.create(self.db_dict)
with weedb.connect(self.db_dict) as _connect:
_v = _connect.get_variable('lower_case_table_names')
self.assertTrue(_v[1] in ['0', '1', '2'], "Unknown lower_case_table_names value")
_v = _connect.get_variable('foo')
self.assertEqual(_v, None)
def test_no_tables(self):
weedb.create(self.db_dict)
_connect = weedb.connect(self.db_dict)
self.assertEqual(_connect.tables(), [])
self.assertRaises(weedb.ProgrammingError, _connect.columnsOf, 'test1')
self.assertRaises(weedb.ProgrammingError, _connect.columnsOf, 'foo')
_connect.close()
def test_rollback(self):
# Create the database and schema
weedb.create(self.db_dict)
_connect = weedb.connect(self.db_dict)
_cursor = _connect.cursor()
_cursor.execute(
"""CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, x REAL );"""
)
# Now start the transaction
_connect.begin()
for i in range(10):
_cursor.execute(
"""INSERT INTO test1 (dateTime, x) VALUES (?, ?)""",
(i, i + 1))
# Roll it back
_connect.rollback()
_cursor.close()
_connect.close()
# Make sure nothing is in the database
_connect = weedb.connect(self.db_dict)
_cursor = _connect.cursor()
_cursor.execute("SELECT dateTime, x from test1")
_row = _cursor.fetchone()
_cursor.close()
_connect.close()
self.assertEqual(_row, None)
def test_variable(self):
weedb.create(self.db_dict)
_connect = weedb.connect(self.db_dict)
_v = _connect.get_variable('journal_mode')
self.assertEqual(_v[1].lower(), 'delete')
_v = _connect.get_variable('foo')
self.assertEqual(_v, None)
_connect.close()
def test_variable(self):
weedb.create(self.db_dict)
with weedb.connect(self.db_dict) as _connect:
_v = _connect.get_variable('lower_case_table_names')
self.assertTrue(_v[1] in ['0', '1', '2'],
"Unknown lower_case_table_names value")
_v = _connect.get_variable('foo')
self.assertEqual(_v, None)
def test_drop_nopermission(self):
weedb.create(mysql1_dict)
with self.assertRaises(weedb.PermissionError):
weedb.drop(mysql2_dict)
weedb.create(sqdb1_dict)
# Can't really test this one without setting up a file where
# we have no write permission
with self.assertRaises(weedb.NoDatabaseError):
weedb.drop(sqdb2_dict)
def test(db_dict):
weedb.create(db_dict)
connect = weedb.connect(db_dict)
cursor = connect.cursor()
cursor.execute("CREATE TABLE bar (col1 int, col2 int)")
with self.assertRaises(weedb.NoColumnError) as e:
cursor.execute("SELECT foo from bar")
cursor.close()
connect.close()
def test(db_dict):
weedb.create(db_dict)
connect = weedb.connect(db_dict)
cursor = connect.cursor()
cursor.execute("CREATE TABLE bar (col1 int, col2 int)")
with self.assertRaises(weedb.TableExistsError) as e:
cursor.execute("CREATE TABLE bar (col1 int, col2 int)")
cursor.close()
connect.close()
def test(db_dict):
weedb.create(db_dict)
connect = weedb.connect(db_dict)
cursor = connect.cursor()
cursor.execute("CREATE TABLE bar (col1 int, col2 int)")
with self.assertRaises(weedb.TableExistsError) as e:
cursor.execute("CREATE TABLE bar (col1 int, col2 int)")
cursor.close()
connect.close()
def test(db_dict):
weedb.create(db_dict)
connect = weedb.connect(db_dict)
cursor = connect.cursor()
cursor.execute("CREATE TABLE bar (col1 int, col2 int)")
with self.assertRaises(weedb.NoColumnError) as e:
cursor.execute("SELECT foo from bar")
cursor.close()
connect.close()
def test_drop_nopermission(self):
weedb.create(mysql1_dict)
with self.assertRaises(weedb.PermissionError):
weedb.drop(mysql2_dict)
weedb.create(sqdb1_dict)
# Can't really test this one without setting up a file where
# we have no write permission
with self.assertRaises(weedb.NoDatabaseError):
weedb.drop(sqdb2_dict)
def test(db_dict):
weedb.create(db_dict)
connect = weedb.connect(db_dict)
cursor = connect.cursor()
cursor.execute("CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, col1 int, col2 int)")
cursor.execute("INSERT INTO test1 (dateTime, col1, col2) VALUES (1, 10, 20)")
with self.assertRaises(weedb.IntegrityError) as e:
cursor.execute("INSERT INTO test1 (dateTime, col1, col2) VALUES (1, 30, 40)")
cursor.close()
connect.close()
def test(db_dict):
weedb.create(db_dict)
connect = weedb.connect(db_dict)
cursor = connect.cursor()
cursor.execute("CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, col1 int, col2 int)")
cursor.execute("INSERT INTO test1 (dateTime, col1, col2) VALUES (1, 10, 20)")
with self.assertRaises(weedb.IntegrityError) as e:
cursor.execute("INSERT INTO test1 (dateTime, col1, col2) VALUES (1, 30, 40)")
cursor.close()
connect.close()
def reconfigMainDatabase(config_dict, db_binding):
"""Create a new database, then populate it with the contents of an old database"""
manager_dict = weewx.manager.get_manager_dict_from_config(config_dict,
db_binding)
# Make a copy for the new database (we will be modifying it)
new_database_dict = dict(manager_dict['database_dict'])
# Now modify the database name
new_database_dict['database_name'] = manager_dict['database_dict']['database_name']+'_new'
# First check and see if the new database already exists. If it does, check
# with the user whether it's ok to delete it.
try:
weedb.create(new_database_dict)
except weedb.DatabaseExists:
ans = None
while ans not in ['y', 'n']:
ans = raw_input("New database '%s' already exists. Delete it first (y/n)? " % (new_database_dict['database_name'],))
if ans == 'y':
weedb.drop(new_database_dict)
elif ans == 'n':
print "Nothing done."
return
# Get the unit system of the old archive:
with weewx.manager.Manager.open(manager_dict['database_dict']) as old_dbmanager:
old_unit_system = old_dbmanager.std_unit_system
# Get the unit system of the new archive:
try:
target_unit_nickname = config_dict['StdConvert']['target_unit']
except KeyError:
target_unit_system = None
else:
target_unit_system = weewx.units.unit_constants[target_unit_nickname.upper()]
ans = None
while ans not in ['y', 'n']:
print "Copying Weewx-WD archive database '%s' to '%s'" % (manager_dict['database_dict']['database_name'], new_database_dict['database_name'])
if target_unit_system is None or old_unit_system==target_unit_system:
print "The new archive will use the same unit system as the old ('%s')." % (weewx.units.unit_nicknames[old_unit_system],)
else:
print "Units will be converted from the '%s' system to the '%s' system." % (weewx.units.unit_nicknames[old_unit_system],
weewx.units.unit_nicknames[target_unit_system])
ans = raw_input("Are you sure you wish to proceed (y/n)? ")
if ans == 'y':
weewx.manager.reconfig(manager_dict['database_dict'],
new_database_dict,
new_unit_system=target_unit_system,
new_schema=manager_dict['schema'])
print "Done."
elif ans == 'n':
print "Nothing done."
def populate_db(self):
weedb.create(self.db_dict)
self.assertRaises(weedb.DatabaseExists, weedb.create, self.db_dict)
with weedb.connect(self.db_dict) as _connect:
with weedb.Transaction(_connect) as _cursor:
_cursor.execute("""CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, descript CHAR(20));""")
_cursor.execute("""CREATE TABLE test2 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, descript CHAR(20));""")
for irec in range(20):
_cursor.execute("INSERT INTO test1 (dateTime, min, mintime) VALUES (?, ?, ?)", (irec, 10*irec, irec))
def test_variable(self):
weedb.create(self.db_dict)
with weedb.connect(self.db_dict) as _connect:
if weedb.sqlite.sqlite_version > '3.4.2':
# Early versions of sqlite did not support journal modes. Not sure exactly when it started,
# but I know that v3.4.2 did not have it.
_v = _connect.get_variable('journal_mode')
self.assertEqual(_v[1].lower(), 'delete')
_v = _connect.get_variable('foo')
self.assertEqual(_v, None)
_connect.close()
def test_variable(self):
weedb.create(self.db_dict)
_connect = weedb.connect(self.db_dict)
if weedb.sqlite.sqlite_version > '3.4.2':
# Early versions of sqlite did not support journal modes. Not sure exactly when it started,
# but I know that v3.4.2 did not have it.
_v = _connect.get_variable('journal_mode')
self.assertEqual(_v[1].lower(), 'delete')
_v = _connect.get_variable('foo')
self.assertEqual(_v, None)
_connect.close()
def populate_db(self):
weedb.create(self.db_dict)
self.assertRaises(weedb.DatabaseExists, weedb.create, self.db_dict)
_connect = weedb.connect(self.db_dict)
with weedb.Transaction(_connect) as _cursor:
_cursor.execute("""CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, descript CHAR(20));""")
_cursor.execute("""CREATE TABLE test2 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, descript CHAR(20));""")
for irec in range(20):
_cursor.execute("INSERT INTO test1 (dateTime, min, mintime) VALUES (?, ?, ?)", (irec, 10*irec, irec))
_connect.close()
def test_double_db_create(self):
weedb.create(mysql1_dict)
with self.assertRaises(weedb.DatabaseExists):
weedb.create(mysql1_dict)
weedb.create(sqdb1_dict)
with self.assertRaises(weedb.DatabaseExists):
weedb.create(sqdb1_dict)
def test_double_db_create(self):
weedb.create(mysql1_dict)
with self.assertRaises(weedb.DatabaseExists):
weedb.create(mysql1_dict)
weedb.create(sqdb1_dict)
with self.assertRaises(weedb.DatabaseExists):
weedb.create(sqdb1_dict)
def test_rollback(self):
# Create the database and schema
weedb.create(self.db_dict)
with weedb.connect(self.db_dict) as _connect:
with _connect.cursor() as _cursor:
_cursor.execute("""CREATE TABLE test1 ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, x REAL );""")
# Now start the transaction
_connect.begin()
for i in range(10):
_cursor.execute("""INSERT INTO test1 (dateTime, x) VALUES (?, ?)""", (i, i+1))
# Roll it back
_connect.rollback()
# Make sure nothing is in the database
with weedb.connect(self.db_dict) as _connect:
with _connect.cursor() as _cursor:
_cursor.execute("SELECT dateTime, x from test1")
_row = _cursor.fetchone()
self.assertEqual(_row, None)
def create_table(db_dict):
"""Create and populate the database table using a 6NF schema"""
# If the following is uncommented, the data will be deleted
# before every run.
# try:
# weedb.drop(db_dict)
# except weedb.NoDatabase:
# pass
# Try to create the database. If it already exists,
# an exception will be raised. Be prepared to catch it
try:
weedb.create(db_dict)
except weedb.DatabaseExists:
pass
connect = weedb.connect(db_dict)
cursor = connect.cursor()
# Create the table and generate the data. If it already exists,
# an exception will be raised. Be prepared to catch it
# and skip generating the data.
try:
# Note that every measurement gets its own row
# The primary key is the combination of the timestamp and observation type
cursor.execute("CREATE TABLE bench ("
"dateTime REAL NOT NULL, "
"obstype VARCHAR(63) NOT NULL, "
"measurement REAL, "
"CONSTRAINT pk PRIMARY KEY (dateTime, obstype))")
except weedb.OperationalError:
print "Benchmark data already exists"
else:
print "Generating fake data"
gen_data(connect)
finally:
cursor.close()
connect.close()
def open_with_create(raw_db_dict, rawSchema):
"""Open a Raw database, initializing it if necessary.
raw_db_dict: A database dictionary holding the information necessary
to open the database.
rawSchema: The schema to be used
Returns:
An instance of RawData"""
try:
rawData = RawData.open(raw_db_dict)
# The database exists and has been initialized. Return it.
return rawData
except (weedb.OperationalError, weewx.UninitializedDatabase):
pass
# First try to create the database. If it already exists, an exception will
# be thrown.
try:
weedb.create(raw_db_dict)
except weedb.DatabaseExists:
pass
# List comprehension of the types, joined together with commas. Put
# the SQL type in backquotes to avoid conflicts with reserved words
_sqltypestr = ', '.join(["`%s` %s" % _type for _type in rawSchema])
_connect = weedb.connect(raw_db_dict)
try:
with weedb.Transaction(_connect) as _cursor:
_cursor.execute("CREATE TABLE raw (%s);" % _sqltypestr)
except Exception, e:
_connect.close()
syslog.syslog(syslog.LOG_ERR, "raw: Unable to create database raw.")
syslog.syslog(syslog.LOG_ERR, "**** %s" % (e,))
raise
def _create_table(archive_db_dict, archiveSchema, table):
"""Create a SQL table using a given archive schema.
archive_db_dict: A database dictionary holding the information necessary
to open the database.
archiveSchema: The schema to be used
table: The name of the table to be used within the database.
Returns:
A connection"""
# First try to create the database. If it already exists, an exception will
# be thrown.
try:
weedb.create(archive_db_dict)
except weedb.DatabaseExists:
pass
# List comprehension of the types, joined together with commas. Put
# the SQL type in backquotes, because at least one of them ('interval')
# is a MySQL reserved word
_sqltypestr = ', '.join(["`%s` %s" % _type for _type in archiveSchema])
_connect = weedb.connect(archive_db_dict)
try:
with weedb.Transaction(_connect) as _cursor:
_cursor.execute("CREATE TABLE %s (%s);" % (table, _sqltypestr))
except Exception, e:
_connect.close()
syslog.syslog(
syslog.LOG_ERR,
"archive: Unable to create database table '%s'." % table)
syslog.syslog(syslog.LOG_ERR, "**** %s" % (e, ))
raise
def test_double_create(self):
weedb.create(self.db_dict)
self.assertRaises(weedb.DatabaseExists, weedb.create, self.db_dict)
def test_drop(self):
self.assertRaises(weedb.NoDatabase, weedb.drop, self.db_dict)
weedb.create(self.db_dict)
def test_create_nopermission(self):
with self.assertRaises(weedb.PermissionError):
weedb.create(mysql2_dict)
with self.assertRaises(weedb.PermissionError):
weedb.create(sqdb2_dict)
def test_drop(self):
self.assertRaises(weedb.NoDatabase, weedb.drop, self.db_dict)
weedb.create(self.db_dict)
def update_to_v3(config_dict, db_binding):
import weedb
import sqlite3
from weewx.manager import Manager
# update an old schema to v3-compatible. this means adding the
# interval column and populating it.
mgr_dict = weewx.manager.get_manager_dict(config_dict['DataBindings'],
config_dict['Databases'],
db_binding)
# see if update is actually required
schema_name = mgr_dict.get('schema')
if schema_name is None:
s = schema
elif isinstance(schema_name, str):
s = weeutil.weeutil._get_object(schema_name)
else:
s = mgr_dict['schema']
if 'interval' in s:
print "Column 'interval' already exists, update not necessary"
return
# make a copy of the database dict
new_db_dict = dict(mgr_dict['database_dict'])
# modify the database name
new_db_dict['database_name'] = mgr_dict['database_dict'][
'database_name'] + '_new'
# see if the new db exists. if so, see if we can delete it
try:
weedb.create(new_db_dict)
except weedb.DatabaseExists:
ans = None
while ans not in ['y', 'n']:
ans = raw_input(
"New database '%s' already exists. Delete it (y/n)? " %
(new_db_dict['database_name'], ))
if ans == 'y':
weedb.drop(new_db_dict)
elif ans == 'n':
print "update aborted"
return
except sqlite3.OperationalError:
pass
# see if we really should do this
ans = None
while ans not in ['y', 'n']:
ans = raw_input("Create new database %s (y/n)? " %
new_db_dict['database_name'])
if ans == 'y':
break
elif ans == 'n':
print "update aborted"
return
# copy the data over
cnt = 0
with Manager.open(mgr_dict['database_dict']) as old_mgr:
with Manager.open_with_create(new_db_dict, schema=s) as new_mgr:
last_ts = None
for r in old_mgr.genBatchRecords():
cnt += 1
print "record %d\r" % cnt,
ival = r['dateTime'] - last_ts if last_ts else 0
r['interval'] = ival
new_mgr.addRecord(r)
last_ts = r['dateTime']
print "copied %d records\n" % cnt
def test_unitialized_archive(self):
_connect = weedb.create(self.archive_db_dict)
self.assertRaises(weewx.UninitializedDatabase,
weewx.manager.Manager(_connect))
def test_create_nopermission(self):
with self.assertRaises(weedb.PermissionError):
weedb.create(mysql2_dict)
with self.assertRaises(weedb.PermissionError):
weedb.create(sqdb2_dict)
def test_unitialized_archive(self):
weedb.create(self.archive_db_dict)
with self.assertRaises(weedb.ProgrammingError):
db_manager = weewx.manager.Manager.open(self.archive_db_dict)
def test_double_create(self):
weedb.create(self.db_dict)
self.assertRaises(weedb.DatabaseExists, weedb.create, self.db_dict)
def test_unitialized_archive(self):
_connect = weedb.create(self.archive_db_dict)
self.assertRaises(weewx.UninitializedDatabase, weewx.archive.Archive(_connect))