Open
Description
Version
1.27.0
What happened?
I'm trying to create a virtual table using SQLite's R*Tree functionality described here: https://www.sqlite.org/rtree.html
But I'm getting an error when trying to run generate. Is it supported?
Relevant log output
store/query.sql:24:1: relation "weather_rtree" does not existDatabase schema
-- Create a simple weather table for storing latitude and longitude
CREATE TABLE weather (
id INTEGER PRIMARY KEY AUTOINCREMENT,
latitude REAL NOT NULL,
longitude REAL NOT NULL,
description TEXT
);
-- Create a virtual R-Tree table for spatial indexing
CREATE VIRTUAL TABLE weather_rtree USING rtree(
id,
min_latitude,
max_latitude,
min_longitude,
max_longitude
);SQL queries
-- name: InsertWeather :one
INSERT INTO weather (
latitude, longitude, description
) VALUES (
?, ?, ?
)
RETURNING id;
-- name: GetWeatherByID :one
SELECT
id, latitude, longitude, description
FROM weather
WHERE id = ?;
-- name: GetWeatherByLocation :many
SELECT
w.id, w.latitude, w.longitude, w.description
FROM weather AS w
JOIN weather_rtree AS r
ON w.id = r.id
WHERE r.min_latitude <= ? AND r.max_latitude >= ?
AND r.min_longitude <= ? AND r.max_longitude >= ?;Configuration
version: "1"
packages:
- name: "store"
path: "store"
queries: "./queries.sql"
schema: "./schema.sql"
engine: "sqlite"
sql_package: "sqlc"Playground URL
No response
What operating system are you using?
macOS
What database engines are you using?
SQLite
What type of code are you generating?
Go