def test_reject_suprious_values(self):
# Create an empty database
tmpfile = tempfile.NamedTemporaryFile(suffix='.sqlite3')
dbpath = tmpfile.name
del(tmpfile)
db = Database(dbpath)
# Create a file with test data in it
testfile = tempfile.NamedTemporaryFile(suffix='.csv', delete=False)
testfile.write("""Ref, Date, Time, Wind 1, Wind 2, Direction, Irradiance Wm-2, Batt V
BB,12-01-2016,10:00:00,1,2,W,0.10,4.72
BB,12-01-2016,10:01:00,1000000,2,N,0.20,4.72
BB,12-01-2016,10:02:00,1,1000000,E,0.30,4.70
BB,12-01-2016,10:03:00,1,2,E,1000000,4.80
BB,12-01-2016,10:04:00,1,2,E,0.30,4.90
""")
testfile.close()
# Add the test data to the test database
db.add([testfile.name])
c = db._conn.cursor()
c.execute("""SELECT * FROM event""")
events = c.fetchall()
print('EVENTS:', events)
self.assertEqual(len(events), 1)
del(db)
os.remove(dbpath)
os.remove(testfile.name)
def remove_data(args):
d = Database(args.database_path)
c = d._conn.cursor()
filt = generate_filter(args, c)
# populate the temp tables tmp_event_rids, tmp_raw_data_rids
filt.select_events()
filt.select_raw_data()
c.execute("""SELECT rid FROM tmp_event_rids""")
event_rowids = c.fetchall()
c.execute("""SELECT rid FROM tmp_raw_data_rids""")
raw_data_rowids = c.fetchall()
if confirmation('Remove %d events and %d raw_date records (y/N)? ' % (
len(event_rowids),
len(raw_data_rowids))):
c.execute("""
DELETE FROM event
WHERE EXISTS (
SELECT 1
FROM tmp_event_rids t
WHERE t.rid = event.rowid
)
""")
c.execute("""
DELETE FROM raw_data
WHERE EXISTS (
SELECT 1
FROM tmp_raw_data_rids t
WHERE t.rid = raw_data.rowid
)
""")
c.execute("""
SELECT id, path
FROM input_file
WHERE NOT EXISTS (
SELECT 1
FROM event e
WHERE e.file_id = input_file.id
)
AND NOT EXISTS (
SELECT 1
FROM raw_data r
WHERE r.file_id = input_file.id
)""")
for r in c.fetchall():
print('Removing input file %s as it no longer has events / raw_data' % r[1])
c.execute("""
DELETE FROM input_file
WHERE NOT EXISTS (
SELECT 1
FROM event e
WHERE e.file_id = input_file.id
)
AND NOT EXISTS (
SELECT 1
FROM raw_data r
WHERE r.file_id = input_file.id
)""")
d.commit()
def setUpClass(cls):
# Create an empty database
tmpfile = tempfile.NamedTemporaryFile(suffix='.sqlite3')
cls._path = tmpfile.name
del (tmpfile)
cls._db = Database(cls._path)
# Create a file with test data in it
cls._testfile = tempfile.NamedTemporaryFile(suffix='.csv',
delete=False)
cls._testfile.write(
"""Ref, Date, Time, Wind 1, Wind 2, Direction, Irradiance Wm-2, Batt V
BB,12-01-2016,19:34:10,1,2,W,0.10,4.72
BB,12-01-2016,19:34:11,1,2,N,0.20,4.72
BB,12-01-2016,19:34:15,1,2,E,0.30,4.70
BB,12-01-2016,19:34:16,1,2,S,0.40,4.72
BB,12-01-2016,19:34:17,1,2,SW,0.50,4.72
BB,13-01-2016,19:34:10,1,2,W,0.10,4.72
BB,13-01-2016,19:34:11,1,2,N,0.20,4.72
BB,13-01-2016,19:34:15,1,2,E,0.30,4.70
BB,13-01-2016,19:34:16,1,2,S,0.40,4.72
BB,13-01-2016,19:34:17,1,2,SW,0.50,4.72
""")
cls._testfile.close()
# Add the test data to the test database
cls._db.add([cls._testfile.name])
def show_files(args):
d = Database(args.database_path)
def want(path, patterns):
if len(patterns) == 0:
return True
dirname, basename = os.path.split(path)
for pat in patterns:
if fnmatch.fnmatch(basename, pat):
return True
return False
print(args.files)
print('%3s %-50s %19s %9s %9s' % (
'ID', 'PATH', 'IMPORTED', 'RECORDS', 'ERRORS'))
print(' '.join(['-' * l for l in [3, 50, 19, 9, 9]]))
for r in d.list_input_files():
if want(r['path'], args.files):
print('%3d %-50s %19s %9d %9d' % (
r['id'], r['path'], r['import_date'][:19], r['records'], r['errors']))
def test_open_existing_database(self):
d, path = self.make_me_a_new_database()
# Insert a marker record so we're not just getting a new DB after re-opening
c = d._conn.cursor()
c.execute("""CREATE TABLE test_open_existing_database (id INT)""")
del (d)
self.assertTrue(os.path.exists(path))
d = Database(path)
self.assertTrue(d.schema_exists())
# Check we have our marker table
c = d._conn.cursor()
c.execute("""
SELECT 1
FROM sqlite_master
WHERE type = 'table'
AND name = 'test_open_existing_database'
""")
self.assertEqual(len(c.fetchall()), 1)
del (d)
os.remove(path)
def test_open_existing_database(self):
d, path = self.make_me_a_new_database()
# Insert a marker record so we're not just getting a new DB after re-opening
c = d._conn.cursor()
c.execute("""CREATE TABLE test_open_existing_database (id INT)""")
del(d)
self.assertTrue(os.path.exists(path))
d = Database(path)
self.assertTrue(d.schema_exists())
# Check we have our marker table
c = d._conn.cursor()
c.execute("""
SELECT 1
FROM sqlite_master
WHERE type = 'table'
AND name = 'test_open_existing_database'
""")
self.assertEqual(len(c.fetchall()), 1)
del(d)
os.remove(path)
def calibrate(args):
log.debug('calibrate(%s)' % args.ref)
d = Database(args.database_path)
c = d._conn.cursor()
c.execute("""SELECT 1 FROM calibration WHERE ref = ?""", (args.ref,))
if len(c.fetchall()) > 0:
log.debug('calibrate() updating existing record for ref %s' % args.ref)
c.execute("""
UPDATE calibration
SET anemometer_1_factor = ?,
anemometer_2_factor = ?,
max_windspeed_ms = ?,
irradiance_factor = ?,
max_irradiance = ?
WHERE ref = ?
""", (args.anemometer_1_factor,
args.anemometer_2_factor,
args.max_windspeed_ms,
args.irradiance_factor,
args.max_irradiance,
args.ref))
else:
log.debug('calibrate() inserting new record for ref %s' % args.ref)
c.execute("""
INSERT INTO calibration (
ref,
anemometer_1_factor,
anemometer_2_factor,
max_windspeed_ms,
irradiance_factor,
max_irradiance
)
VALUES ( ?, ?, ?, ?, ?, ? )
""", (args.ref,
args.anemometer_1_factor,
args.anemometer_2_factor,
args.max_windspeed_ms,
args.irradiance_factor,
args.max_irradiance))
d.commit()
def make_me_a_new_database(self):
tmpfile = tempfile.NamedTemporaryFile(suffix='.sqlite3')
path = tmpfile.name
del (tmpfile)
d = Database(path)
return d, path
def add_files(args):
d = Database(args.database_path)
d.add(args.files)
def database_info(args):
d = Database(args.database_path)
info = d.info()
for k in ['Database file', 'Size', 'Number of files added', 'Number of records']:
print('%-30s%s' % (k + ':', info[k]))
def database_reset(args):
d = Database(args.database_path)
if confirmation():
d.reset()
else:
log.warning("Database reset ABORTED because confirmation not given")
