def test_cli_add_geometry_column_options(tmpdir):
# create a rowid table with one column
db_path = tmpdir / "spatial.db"
db = Database(str(db_path))
db.init_spatialite()
table = db["locations"].create({"name": str})
result = CliRunner().invoke(
cli,
[
"add-geometry-column",
str(db_path),
table.name,
"geometry",
"-t",
"POLYGON",
"--srid",
"3857", # https://epsg.io/3857
"--not-null",
],
)
assert 0 == result.exit_code
assert db["geometry_columns"].get(["locations", "geometry"]) == {
"f_table_name": "locations",
"f_geometry_column": "geometry",
"geometry_type": 3, # polygon
"coord_dimension": 2,
"srid": 3857,
"spatial_index_enabled": 0,
}
column = table.columns[1]
assert column.notnull
def test_cli_add_geometry_column(tmpdir):
# create a rowid table with one column
db_path = tmpdir / "spatial.db"
db = Database(str(db_path))
db.init_spatialite()
table = db["locations"].create({"name": str})
result = CliRunner().invoke(
cli,
[
"add-geometry-column",
str(db_path),
table.name,
"geometry",
"--type",
"POINT",
],
)
assert 0 == result.exit_code
assert db["geometry_columns"].get(["locations", "geometry"]) == {
"f_table_name": "locations",
"f_geometry_column": "geometry",
"geometry_type": 1, # point
"coord_dimension": 2,
"srid": 4326,
"spatial_index_enabled": 0,
}
def test_create_spatial_index():
db = Database(memory=True)
spatialite = find_spatialite()
assert db.init_spatialite(spatialite)
# create a table, add a geometry column with default values
table = db.create_table("locations", {"id": str, "properties": str})
assert table.add_geometry_column("geometry", "Point")
# index it
assert table.create_spatial_index("geometry")
assert "idx_locations_geometry" in db.table_names()
def test_cli_create_spatialite(tmpdir):
# sqlite-utils create test.db --init-spatialite
db_path = tmpdir / "created.db"
result = CliRunner().invoke(
cli, ["create-database",
str(db_path), "--init-spatialite"])
assert 0 == result.exit_code
assert db_path.exists()
assert db_path.read_binary()[:16] == b"SQLite format 3\x00"
db = Database(str(db_path))
assert "spatial_ref_sys" in db.table_names()
def test_cli_create_spatial_index(tmpdir):
# create a rowid table with one column
db_path = tmpdir / "spatial.db"
db = Database(str(db_path))
db.init_spatialite()
table = db["locations"].create({"name": str})
table.add_geometry_column("geometry", "POINT")
result = CliRunner().invoke(
cli, ["create-spatial-index",
str(db_path), table.name, "geometry"])
assert 0 == result.exit_code
assert "idx_locations_geometry" in db.table_names()
def test_cli_add_geometry_column_invalid_type(tmpdir):
# create a rowid table with one column
db_path = tmpdir / "spatial.db"
db = Database(str(db_path))
db.init_spatialite()
table = db["locations"].create({"name": str})
result = CliRunner().invoke(
cli,
[
"add-geometry-column",
str(db_path),
table.name,
"geometry",
"--type",
"NOT-A-TYPE",
],
)
assert 2 == result.exit_code
def test_add_geometry_column():
db = Database(memory=True)
spatialite = find_spatialite()
db.init_spatialite(spatialite)
# create a table first
table = db.create_table("locations", {"id": str, "properties": str})
table.add_geometry_column(
column_name="geometry",
geometry_type="Point",
srid=4326,
coord_dimension=2,
)
assert db["geometry_columns"].get(["locations", "geometry"]) == {
"f_table_name": "locations",
"f_geometry_column": "geometry",
"geometry_type": 1, # point
"coord_dimension": 2,
"srid": 4326,
"spatial_index_enabled": 0,
}
def test_analyze_table_save(db_to_analyze_path):
result = CliRunner().invoke(
cli.cli, ["analyze-tables", db_to_analyze_path, "--save"])
assert result.exit_code == 0
rows = list(Database(db_to_analyze_path)["_analyze_tables_"].rows)
assert rows == [
{
"table": "stuff",
"column": "id",
"total_rows": 8,
"num_null": 0,
"num_blank": 0,
"num_distinct": 8,
"most_common": None,
"least_common": None,
},
{
"table": "stuff",
"column": "owner",
"total_rows": 8,
"num_null": 0,
"num_blank": 0,
"num_distinct": 4,
"most_common":
'[["Joan", 3], ["Terryterryterry", 2], ["Kumar", 2], ["Anne", 1]]',
"least_common": None,
},
{
"table": "stuff",
"column": "size",
"total_rows": 8,
"num_null": 0,
"num_blank": 0,
"num_distinct": 2,
"most_common": "[[5, 5], [4, 3]]",
"least_common": None,
},
]
def test_cannot_provide_both_filename_and_memory():
with pytest.raises(
AssertionError,
match="Either specify a filename_or_conn or pass memory=True"):
Database("/tmp/foo.db", memory=True)
def test_works_with_pathlib_path(tmpdir):
path = pathlib.Path(tmpdir / "test.db")
db = Database(path)
db["demo"].insert_all([{"foo": 1}])
assert 1 == db["demo"].count
],
)
def test_virtual_table_using(fresh_db, sql, expected_name, expected_using):
fresh_db.execute(sql)
assert fresh_db[expected_name].virtual_table_using == expected_using
def test_use_rowid(fresh_db):
fresh_db["rowid_table"].insert({"name": "Cleo"})
fresh_db["regular_table"].insert({"id": 1, "name": "Cleo"}, pk="id")
assert fresh_db["rowid_table"].use_rowid
assert not fresh_db["regular_table"].use_rowid
@pytest.mark.skipif(
not Database(memory=True).supports_strict,
reason="Needs SQLite version that supports strict",
)
@pytest.mark.parametrize(
"create_table,expected_strict",
(
("create table t (id integer) strict", True),
("create table t (id integer) STRICT", True),
("create table t (id integer primary key) StriCt, WITHOUT ROWID",
True),
("create table t (id integer primary key) WITHOUT ROWID", False),
("create table t (id integer)", False),
),
)
def test_table_strict(fresh_db, create_table, expected_strict):
fresh_db.execute(create_table)
def test_init_spatialite():
db = Database(memory=True)
spatialite = find_spatialite()
db.init_spatialite(spatialite)
assert "spatial_ref_sys" in db.table_names()
def test_virtual_table_using(sql, expected_name, expected_using):
db = Database(memory=True)
db.execute(sql)
assert db[expected_name].virtual_table_using == expected_using