def test_sql_storage_package_url_argument(sqlite_url):
source = Package(resources=[Resource(path="data/table.csv")])
source.to_sql(url=sqlite_url)
target = Package.from_sql(url=sqlite_url)
assert target.get_resource("table").schema == {
"fields": [
{
"name": "id",
"type": "integer"
},
{
"name": "name",
"type": "string"
},
]
}
assert target.get_resource("table").read_rows() == [
{
"id": 1,
"name": "english"
},
{
"id": 2,
"name": "中国人"
},
]
def test_sql_storage_mysql_constraints(mysql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/constraints.json")
storage = source.to_sql(mysql_url, dialect=dialect)
target = Package.from_sql(mysql_url, dialect=dialect)
# Assert metadata
assert target.get_resource("constraints").schema == {
"fields": [
{"name": "required", "type": "string", "constraints": {"required": True}},
{"name": "minLength", "type": "string"}, # constraint removal
{"name": "maxLength", "type": "string"}, # constraint removal
{"name": "pattern", "type": "string"}, # constraint removal
{"name": "enum", "type": "string"}, # constraint removal
{"name": "minimum", "type": "integer"}, # constraint removal
{"name": "maximum", "type": "integer"}, # constraint removal
],
}
# Assert data
assert target.get_resource("constraints").read_rows() == [
{
"required": "passing",
"minLength": "passing",
"maxLength": "passing",
"pattern": "passing",
"enum": "passing",
"minimum": 5,
"maximum": 5,
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_sql_storage_postgresql_integrity_different_order_issue_957(postgresql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/integrity.json")
source.add_resource(source.remove_resource("integrity_main"))
storage = source.to_sql(postgresql_url, dialect=dialect)
target = Package.from_sql(postgresql_url, dialect=dialect)
assert len(target.resources) == 2
storage.delete_package(target.resource_names)
def datapackage_to_datasette(dbname,
data_package,
metadata_filename,
write_mode=None):
validate_write_mode(write_mode)
if path.exists(metadata_filename) and not write_mode:
raise DataImportError(
f"File {metadata_filename} already exists. "
"Use write_mode to replace or merge the existing file.")
dp = Package(data_package)
dp.to_sql(f"sqlite:///{dbname}")
metadata = get_metadata_object(dbname, dp)
write_file(metadata, metadata_filename, write_mode)
def package(c):
Path("build/").mkdir(parents=True, exist_ok=True)
metadata = {
"title": "Podnebnik",
"description": "TODO",
}
for datapackage_path in discover_datapackages():
pkg = Package(datapackage_path)
dbname = os.path.basename(os.path.dirname(datapackage_path))
# convert Package to sqlite db
pkg.to_sql(f"sqlite:///build/{dbname}.db")
# extract metadata from Package
pkg_meta = extract_metadata(pkg, dbname)
# merge metadata
metadata = always_merger.merge(metadata, pkg_meta)
with open('build/metadata.json', 'w') as f:
json.dump(metadata, f, indent=4, sort_keys=True)
def test_sql_storage_mysql_integrity(mysql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/integrity.json")
storage = source.to_sql(mysql_url, dialect=dialect)
target = Package.from_sql(mysql_url, dialect=dialect)
# Assert metadata (main)
assert target.get_resource("integrity_main").schema == {
"fields": [
# added required
{"name": "id", "type": "integer", "constraints": {"required": True}},
{"name": "parent", "type": "integer"},
{"name": "description", "type": "string"},
],
"primaryKey": ["id"],
"foreignKeys": [
{"fields": ["parent"], "reference": {"resource": "", "fields": ["id"]}}
],
}
# Assert metadata (link)
assert target.get_resource("integrity_link").schema == {
"fields": [
# added required
{"name": "main_id", "type": "integer", "constraints": {"required": True}},
# added required; removed unique
{"name": "some_id", "type": "integer", "constraints": {"required": True}},
# removed unique
{"name": "description", "type": "string"},
],
"primaryKey": ["main_id", "some_id"],
"foreignKeys": [
{
"fields": ["main_id"],
"reference": {"resource": "integrity_main", "fields": ["id"]},
}
],
}
# Assert data (main)
assert target.get_resource("integrity_main").read_rows() == [
{"id": 1, "parent": None, "description": "english"},
{"id": 2, "parent": 1, "description": "中国人"},
]
# Assert data (link)
assert target.get_resource("integrity_link").read_rows() == [
{"main_id": 1, "some_id": 1, "description": "note1"},
{"main_id": 2, "some_id": 2, "description": "note2"},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_sql_storage_mysql_types(mysql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/types.json")
storage = source.to_sql(mysql_url, dialect=dialect)
target = Package.from_sql(mysql_url, dialect=dialect)
# Assert metadata
assert target.get_resource("types").schema == {
"fields": [
{"name": "any", "type": "string"}, # type fallback
{"name": "array", "type": "string"}, # type fallback
{"name": "boolean", "type": "integer"}, # type downgrade
{"name": "date", "type": "date"},
{"name": "date_year", "type": "date"}, # format removal
{"name": "datetime", "type": "datetime"},
{"name": "duration", "type": "string"}, # type fallback
{"name": "geojson", "type": "string"}, # type fallback
{"name": "geopoint", "type": "string"}, # type fallback
{"name": "integer", "type": "integer"},
{"name": "number", "type": "number"},
{"name": "object", "type": "string"}, # type fallback
{"name": "string", "type": "string"},
{"name": "time", "type": "time"},
{"name": "year", "type": "integer"}, # type downgrade
{"name": "yearmonth", "type": "string"}, # type fallback
],
}
# Assert data
assert target.get_resource("types").read_rows() == [
{
"any": "中国人",
"array": '["Mike", "John"]',
"boolean": True,
"date": datetime.date(2015, 1, 1),
"date_year": datetime.date(2015, 1, 1),
"datetime": datetime.datetime(2015, 1, 1, 3, 0),
"duration": "P1Y1M",
"geojson": '{"type": "Point", "coordinates": [33, 33.33]}',
"geopoint": "30,70",
"integer": 1,
"number": 7,
"object": '{"chars": 560}',
"string": "english",
"time": datetime.time(3, 0),
"year": 2015,
"yearmonth": "2015-01",
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_mysql_storage_integrity():
engine = sa.create_engine(os.environ["MYSQL_URL"])
prefix = "prefix_"
# Export/Import
source = Package("data/storage/integrity.json")
storage = source.to_sql(engine=engine, prefix=prefix, force=True)
target = Package.from_sql(engine=engine, prefix=prefix)
# Assert metadata (main)
assert target.get_resource("integrity_main").schema == {
"fields": [
# added required
{
"name": "id",
"type": "integer",
"constraints": {
"required": True
}
},
{
"name": "parent",
"type": "integer"
},
{
"name": "description",
"type": "string"
},
],
"primaryKey": ["id"],
"foreignKeys": [{
"fields": ["parent"],
"reference": {
"resource": "",
"fields": ["id"]
}
}],
}
# Assert metadata (link)
assert target.get_resource("integrity_link").schema == {
"fields": [
# added required
{
"name": "main_id",
"type": "integer",
"constraints": {
"required": True
}
},
# added required; removed unique
{
"name": "some_id",
"type": "integer",
"constraints": {
"required": True
}
},
# removed unique
{
"name": "description",
"type": "string"
},
],
"primaryKey": ["main_id", "some_id"],
"foreignKeys": [{
"fields": ["main_id"],
"reference": {
"resource": "integrity_main",
"fields": ["id"]
},
}],
}
# Assert data (main)
assert target.get_resource("main").read_rows() == [
{
"id": 1,
"parent": None,
"description": "english"
},
{
"id": 2,
"parent": 1,
"description": "中国人"
},
]
# Assert data (link)
assert target.get_resource("link").read_rows() == [
{
"main_id": 1,
"some_id": 1,
"description": "note1"
},
{
"main_id": 2,
"some_id": 2,
"description": "note2"
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_postgresql_storage_constraints(database_url):
engine = sa.create_engine(os.environ["POSTGRESQL_URL"])
prefix = "prefix_"
# Export/Import
source = Package("data/storage/constraints.json")
storage = source.to_sql(engine=engine, prefix=prefix, force=True)
target = Package.from_sql(engine=engine, prefix=prefix)
# Assert metadata
assert target.get_resource("constraints").schema == {
"fields": [
{
"name": "required",
"type": "string",
"constraints": {
"required": True
}
},
{
"name": "minLength",
"type": "string"
}, # constraint removal
{
"name": "maxLength",
"type": "string"
}, # constraint removal
{
"name": "pattern",
"type": "string"
}, # constraint removal
{
"name": "enum",
"type": "string"
}, # constraint removal
{
"name": "minimum",
"type": "integer"
}, # constraint removal
{
"name": "maximum",
"type": "integer"
}, # constraint removal
],
}
# Assert data
assert target.get_resource("constraints").read_rows() == [
{
"required": "passing",
"minLength": "passing",
"maxLength": "passing",
"pattern": "passing",
"enum": "passing",
"minimum": 5,
"maximum": 5,
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_postgresql_storage_types():
engine = sa.create_engine(os.environ["POSTGRESQL_URL"])
prefix = "prefix_"
# Export/Import
source = Package("data/storage/types.json")
storage = source.to_sql(engine=engine, prefix=prefix, force=True)
target = Package.from_sql(engine=engine, prefix=prefix)
# Assert metadata
assert target.get_resource("types").schema == {
"fields": [
{
"name": "any",
"type": "string"
}, # type fallback
{
"name": "array",
"type": "object"
}, # type downgrade
{
"name": "boolean",
"type": "boolean"
},
{
"name": "date",
"type": "date"
},
{
"name": "date_year",
"type": "date"
}, # format removal
{
"name": "datetime",
"type": "datetime"
},
{
"name": "duration",
"type": "string"
}, # type fallback
{
"name": "geojson",
"type": "object"
}, # type downgrade
{
"name": "geopoint",
"type": "string"
}, # type fallback
{
"name": "integer",
"type": "integer"
},
{
"name": "number",
"type": "number"
},
{
"name": "object",
"type": "object"
},
{
"name": "string",
"type": "string"
},
{
"name": "time",
"type": "time"
},
{
"name": "year",
"type": "integer"
}, # type downgrade
{
"name": "yearmonth",
"type": "string"
}, # type fallback
],
}
# Assert data
assert target.get_resource("types").read_rows() == [
{
"any": "中国人",
"array": None, # TODO: fix array
"boolean": True,
"date": datetime.date(2015, 1, 1),
"date_year": datetime.date(2015, 1, 1),
"datetime": datetime.datetime(2015, 1, 1, 3, 0),
"duration": "P1Y1M",
"geojson": {
"type": "Point",
"coordinates": [33, 33.33]
},
"geopoint": "30,70",
"integer": 1,
"number": 7,
"object": {
"chars": 560
},
"string": "english",
"time": datetime.time(3, 0),
"year": 2015,
"yearmonth": "2015-01",
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_storage_types(database_url):
engine = sa.create_engine(database_url)
prefix = "prefix_"
# Export/Import
source = Package("data/storage/types.json")
storage = source.to_sql(engine=engine, prefix=prefix, force=True)
target = Package.from_sql(engine=engine, prefix=prefix)
# Assert metadata
assert target.get_resource("main").schema == {
"fields": [
{
"name": "any",
"type": "string"
}, # type fallback
{
"name": "array",
"type": "string"
}, # type fallback
{
"name": "boolean",
"type": "boolean"
},
{
"name": "date",
"type": "date"
},
{
"name": "date_year",
"type": "date"
}, # format removal
{
"name": "datetime",
"type": "datetime"
},
{
"name": "duration",
"type": "string"
}, # type fallback
{
"name": "geojson",
"type": "string"
}, # type fallback
{
"name": "geopoint",
"type": "string"
}, # type fallback
{
"name": "integer",
"type": "integer"
},
{
"name": "number",
"type": "number"
},
{
"name": "object",
"type": "string"
}, # type fallback
{
"name": "string",
"type": "string"
},
{
"name": "time",
"type": "time"
},
{
"name": "year",
"type": "integer"
}, # type downgrade
{
"name": "yearmonth",
"type": "string"
}, # type fallback
],
}
# Assert data
assert target.get_resource("main").read_rows() == [
{
"any": "note1",
"array": '["Mike", "John"]',
"boolean": True,
"date": datetime.date(2015, 1, 1),
"date_year": datetime.date(2015, 1, 1),
"datetime": datetime.datetime(2015, 1, 1, 3, 0),
"duration": "P1Y1M",
"geojson": '{"type": "Point", "coordinates": [33, 33.33]}',
"geopoint": "30,70",
"integer": 1,
"number": 7,
"object": '{"chars": 560}',
"string": "good",
"time": datetime.time(3, 0),
"year": 2015,
"yearmonth": "2015-01",
},
]
# Cleanup storage
storage.delete_package(target.resource_names)