def test_integration():
tg = TableGroup.from_file(FIXTURES / 'csv.txt-metadata.json')
orig = tg.read()
db = Database(tg)
db.write_from_tg()
for table, items in db.read().items():
assert items == orig[table]
def test_required(tg):
tg.tables[0].tableSchema.columns.append(
Column.fromvalue({
'required': 'True',
'name': 'v'
}))
db = Database(tg)
with pytest.raises(sqlite3.IntegrityError):
db.write(data=[{'v': None}])
def test_datatypes(tg, datatype):
tg.tables[0].tableSchema.columns.extend([
Column.fromvalue({
'datatype': datatype.name,
'name': 'v1'
}),
Column.fromvalue({
'datatype': datatype.name,
'name': 'v2'
}),
])
db = Database(tg)
v = datatype.to_python(datatype.example)
db.write(data=[{'v1': v, 'v2': None}])
data = db.read()['data']
assert data[0]['v1'] == v
assert data[0]['v2'] is None
def test_foreign_keys(tg, translate):
tg.tables[0].tableSchema.columns.append(Column.fromvalue({'name': 'v'}))
tg.tables[0].tableSchema.primaryKey = ['v']
tg.tables.append(
Table.fromvalue({
'url': 'ref',
'tableSchema': {
'columns': [{
'name': 'ref'
}],
'foreignKeys': [{
'columnReference': 'ref',
'reference': {
'resource': 'data',
'columnReference': 'v',
}
}]
}
}))
db = Database(tg, translate=translate)
with pytest.raises(sqlite3.IntegrityError):
db.write(ref=[{'ref': 'y'}], data=[{'v': 'x'}])
db.write(ref=[{'ref': 'x'}], data=[{'v': 'x'}])
# 'vv' is used as column name as specified by the translate fixture:
assert 'vv' in db.read()['data'][0]
def test_file(tmpdir, tg):
fname = tmpdir.join('test.sqlite')
tg.tables[0].tableSchema.columns.append(Column.fromvalue({'name': 'v'}))
db = Database(tg, fname=str(fname))
db.write()
assert fname.check()
with pytest.raises(ValueError):
db.write()
def test_list_valued(tg):
tg.tables[0].tableSchema.columns.append(
Column.fromvalue({
'separator': '#',
'name': 'v'
}))
db = Database(tg)
with pytest.raises(TypeError):
db.write(data=[{'v': [1, 2, 3]}])
db.write(data=[{'v': ['a', 'b', ' c']}, {'v': []}])
data = db.read()['data']
assert data[0]['v'] == ['a', 'b', ' c']
assert data[1]['v'] == []
def test_write_file_exists(tmpdir):
target = pathlib.Path(str(tmpdir / 'db.sqlite3'))
target.touch(exist_ok=False)
mtime = target.stat().st_mtime
tg = TableGroup.from_file(FIXTURES / 'csv.txt-metadata.json')
db = Database(tg, fname=target)
with pytest.raises(ValueError, match=r'already exists'):
db.write()
time.sleep(0.1)
db.write(force=True)
assert target.stat().st_mtime > mtime
def test_constraints(tg, datatype, value, error):
tg.tables[0].tableSchema.columns.append(
Column.fromvalue({
'datatype': datatype,
'name': 'v'
}))
db = Database(tg)
if error:
with pytest.raises(sqlite3.IntegrityError):
db.write(data=[{'v': value}])
else:
db.write(data=[{'v': value}])
def test_many_to_many(tg_with_foreign_keys):
db = Database(tg_with_foreign_keys)
with pytest.raises(sqlite3.IntegrityError):
# Foreign key violates referential integrity:
db.write(ref=[{'pk': '1', 'ref1': ['y']}], data=[{'v': 'x'}])
db.write(ref=[{
'pk': '1',
'ref1': ['y', 'x']
}],
data=[{
'v': 'x'
}, {
'v': 'y'
}])
res = db.read()['ref'][0]
# Associations between the same pair of tables are grouped by foreign key column:
assert res['ref1'] == ['y', 'x']
assert res['ref2'] == []
def test_many_to_many_self_referential(tg):
tg.tables[0].tableSchema.columns.append(Column.fromvalue({'name': 'v'}))
tg.tables[0].tableSchema.columns.append(
Column.fromvalue({
'name': 'ref',
'separator': ';'
}))
tg.tables[0].tableSchema.primaryKey = ['v']
tg.tables[0].tableSchema.foreignKeys.append(
ForeignKey.fromdict({
'columnReference': 'ref',
'reference': {
'resource': 'data',
'columnReference': 'v',
}
}))
db = Database(tg)
with pytest.raises(sqlite3.IntegrityError):
db.write(data=[{'v': 'x', 'ref': ['y']}])
db.write(data=[{'v': 'x', 'ref': []}, {'v': 'y', 'ref': ['x', 'y']}])
assert db.read()['data'][1]['ref'] == ['x', 'y']
def test_extra_columns(tmpdir):
tmpdir.join('md.json').write_text("""{
"@context": ["http://www.w3.org/ns/csvw",{"@language": "en"}],
"dialect": {"header": true,"encoding": "utf-8-sig"},
"tables": [
{"url": "csv.txt","tableSchema": {"columns": [{"name": "ID", "datatype": "string"}]}}
]
}
""",
encoding='utf8')
tmpdir.join('csv.txt').write_text('ID,extra\n1,ex', encoding='utf8')
tg = TableGroup.from_file(str(tmpdir.join('md.json')))
with warnings.catch_warnings():
warnings.simplefilter("ignore")
db = Database(tg, fname=str(tmpdir.join('test.sqlite')))
with pytest.raises(ValueError):
db.write_from_tg()
db.write_from_tg(_force=True, _skip_extra=True)
import csvw
from csvw.db import Database
tg = csvw.TableGroup.from_file(
'tests/testModules/myTestModule1/metadata_inkl_schemas.json')
tg.check_referential_integrity()
db = Database(tg, fname='test.sqlite')
db.write_from_tg()
# for row in tg.tables[1].iterdicts():
# print("Row:")
# for k, v in row.items():
# print(f" {k}: {v}")
# tg.check_referential_integrity()
def select_many_to_many(self, db, table, context):
# Tables with at most one foreign key to another table can use the context
# to store something else.
return Database.select_many_to_many(self, db, table, None)