Buckets:

download
raw
15.4 kB
"""
Datasets API Endpoints
Handles CRUD operations for datasets
"""
import logging
import json
import re
from fastapi import HTTPException, Request
from connection_manager import connection_manager
from psycopg2 import sql
from error_utils import log_and_raise_error
def validate_sql_identifier(name: str, field_name: str) -> str:
"""Validate that a string is a safe SQL identifier (alphanumeric + underscore only)"""
if not name:
return name
if not re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', name):
raise HTTPException(
status_code=400,
detail=f"Invalid {field_name}: must start with a letter or underscore and contain only letters, numbers, and underscores"
)
return name
async def get_all_datasets():
"""Get all datasets from the database"""
try:
logging.info("Fetching all datasets from database")
with connection_manager.get_connection() as pg:
datasets_data = pg.execute("""
SELECT
d.id,
d.name,
d.description,
d.connection_id,
d.source_type,
d.table_name,
d.sql_query,
d.schema_name,
d.columns,
d.filters,
d.config,
d.file_path,
d.original_filename,
d.file_size_bytes,
d.created_at,
d.updated_at
FROM datasets d
ORDER BY d.created_at DESC
""", fetch=True)
all_datasets = []
if datasets_data:
for dataset in datasets_data:
dataset_dict = {
'id': dataset['id'],
'name': dataset['name'],
'description': dataset['description'],
'connection_id': dataset['connection_id'],
'source_type': dataset['source_type'],
'table_name': dataset['table_name'],
'sql_query': dataset['sql_query'],
'schema_name': dataset['schema_name'],
'columns': dataset['columns'] if dataset['columns'] else [],
'filters': dataset['filters'] if dataset['filters'] else [],
'config': dataset['config'] if dataset['config'] else {},
'file_path': dataset.get('file_path'),
'original_filename': dataset.get('original_filename'),
'file_size_bytes': dataset.get('file_size_bytes'),
'created_at': str(dataset['created_at']) if dataset['created_at'] else None,
'updated_at': str(dataset['updated_at']) if dataset['updated_at'] else None
}
all_datasets.append(dataset_dict)
logging.info(f"Fetched {len(all_datasets)} datasets from database")
return {"datasets": all_datasets}
except Exception as e:
log_and_raise_error("fetching datasets", e)
async def get_dataset_by_id(dataset_id: str):
"""Get a single dataset by ID"""
try:
logging.info(f"Fetching dataset: {dataset_id}")
with connection_manager.get_connection() as pg:
dataset_data = pg.execute("""
SELECT
d.id,
d.name,
d.description,
d.connection_id,
d.source_type,
d.table_name,
d.sql_query,
d.schema_name,
d.columns,
d.filters,
d.config,
d.file_path,
d.original_filename,
d.file_size_bytes,
d.created_at,
d.updated_at
FROM datasets d
WHERE d.id = %s
""", (dataset_id,), fetch=True)
if not dataset_data or len(dataset_data) == 0:
raise HTTPException(status_code=404, detail=f"Dataset {dataset_id} not found")
dataset = dataset_data[0]
dataset_dict = {
'id': dataset['id'],
'name': dataset['name'],
'description': dataset['description'],
'connection_id': dataset['connection_id'],
'source_type': dataset['source_type'],
'table_name': dataset['table_name'],
'sql_query': dataset['sql_query'],
'schema_name': dataset['schema_name'],
'columns': dataset['columns'] if dataset['columns'] else [],
'filters': dataset['filters'] if dataset['filters'] else [],
'config': dataset['config'] if dataset['config'] else {},
'file_path': dataset.get('file_path'),
'original_filename': dataset.get('original_filename'),
'file_size_bytes': dataset.get('file_size_bytes'),
'created_at': str(dataset['created_at']) if dataset['created_at'] else None,
'updated_at': str(dataset['updated_at']) if dataset['updated_at'] else None
}
return {"dataset": dataset_dict}
except HTTPException:
raise
except Exception as e:
log_and_raise_error(f"fetching dataset {dataset_id}", e)
async def create_dataset(request: Request):
"""Create a new dataset"""
try:
body = await request.json()
logging.info(f"Creating dataset: {body}")
# Extract dataset fields
dataset_id = body.get("id")
name = body.get("name")
description = body.get("description", "")
connection_id = body.get("connection_id")
source_type = body.get("source_type", "table") # 'table' or 'sql'
table_name = body.get("table_name")
sql_query = body.get("sql_query")
schema_name = body.get("schema_name")
columns = body.get("columns", [])
filters = body.get("filters", [])
config = body.get("config", {})
# Validation
if not dataset_id or not name:
raise HTTPException(status_code=400, detail="Missing required fields: id, name")
if source_type == "table" and not table_name:
raise HTTPException(status_code=400, detail="table_name is required when source_type is 'table'")
if source_type == "sql" and not sql_query:
raise HTTPException(status_code=400, detail="sql_query is required when source_type is 'sql'")
with connection_manager.get_connection() as pg:
# Insert the dataset
pg.execute("""
INSERT INTO datasets (
id, name, description, connection_id,
source_type, table_name, sql_query, schema_name,
columns, filters, config
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""", (
dataset_id,
name,
description,
connection_id,
source_type,
table_name,
sql_query,
schema_name,
json.dumps(columns) if columns else None,
json.dumps(filters) if filters else None,
json.dumps(config) if config else None
))
logging.info(f"Dataset {dataset_id} created successfully")
return {
"success": True,
"message": f"Dataset '{name}' created successfully",
"dataset_id": dataset_id
}
except HTTPException:
raise
except Exception as e:
log_and_raise_error("creating dataset", e)
async def update_dataset(dataset_id: str, request: Request):
"""Update an existing dataset"""
try:
body = await request.json()
logging.info(f"Updating dataset {dataset_id}: {body}")
# Extract fields to update
name = body.get("name")
description = body.get("description")
connection_id = body.get("connection_id")
source_type = body.get("source_type")
table_name = body.get("table_name")
sql_query = body.get("sql_query")
schema_name = body.get("schema_name")
columns = body.get("columns")
filters = body.get("filters")
config = body.get("config")
with connection_manager.get_connection() as pg:
# Check if dataset exists
existing = pg.execute(
"SELECT id FROM datasets WHERE id = %s",
(dataset_id,),
fetch=True
)
if not existing or len(existing) == 0:
raise HTTPException(status_code=404, detail=f"Dataset {dataset_id} not found")
# Update the dataset
pg.execute("""
UPDATE datasets SET
name = COALESCE(%s, name),
description = COALESCE(%s, description),
connection_id = COALESCE(%s, connection_id),
source_type = COALESCE(%s, source_type),
table_name = COALESCE(%s, table_name),
sql_query = COALESCE(%s, sql_query),
schema_name = COALESCE(%s, schema_name),
columns = COALESCE(%s, columns),
filters = COALESCE(%s, filters),
config = COALESCE(%s, config),
updated_at = CURRENT_TIMESTAMP
WHERE id = %s
""", (
name,
description,
connection_id,
source_type,
table_name,
sql_query,
schema_name,
json.dumps(columns) if columns is not None else None,
json.dumps(filters) if filters is not None else None,
json.dumps(config) if config is not None else None,
dataset_id
))
logging.info(f"Dataset {dataset_id} updated successfully")
return {
"success": True,
"message": f"Dataset updated successfully",
"dataset_id": dataset_id
}
except HTTPException:
raise
except Exception as e:
log_and_raise_error(f"updating dataset {dataset_id}", e)
async def delete_dataset(dataset_id: str):
"""Delete a dataset"""
try:
logging.info(f"Deleting dataset: {dataset_id}")
with connection_manager.get_connection() as pg:
# Check if dataset exists
existing = pg.execute(
"SELECT id, name FROM datasets WHERE id = %s",
(dataset_id,),
fetch=True
)
if not existing or len(existing) == 0:
raise HTTPException(status_code=404, detail=f"Dataset {dataset_id} not found")
dataset_name = existing[0]['name']
# Check if any charts reference this dataset
charts_using = pg.execute(
"SELECT COUNT(*) as count FROM charts WHERE dataset_id = %s",
(dataset_id,),
fetch=True
)
charts_count = charts_using[0]['count'] if charts_using else 0
if charts_count > 0:
raise HTTPException(
status_code=400,
detail=f"Cannot delete dataset '{dataset_name}'. It is used by {charts_count} chart(s). Please delete or reassign those charts first."
)
# Delete the dataset
pg.execute("DELETE FROM datasets WHERE id = %s", (dataset_id,))
logging.info(f"Dataset {dataset_id} deleted successfully")
return {
"success": True,
"message": f"Dataset '{dataset_name}' deleted successfully"
}
except HTTPException:
raise
except Exception as e:
log_and_raise_error(f"deleting dataset {dataset_id}", e)
async def preview_dataset(request: Request):
"""Preview data from a dataset (execute query and return sample rows)"""
try:
body = await request.json()
logging.info(f"Previewing dataset data")
connection_id = body.get("connection_id")
source_type = body.get("source_type", "table")
table_name = body.get("table_name")
sql_query = body.get("sql_query")
schema_name = body.get("schema_name", "public")
filters = body.get("filters", [])
limit = body.get("limit", 100) # Default to 100 rows
# Validate limit is a positive integer within bounds
if not isinstance(limit, int) or limit < 1 or limit > 10000:
raise HTTPException(status_code=400, detail="limit must be an integer between 1 and 10000")
# Build the query based on source type
if source_type == "table":
if not table_name:
raise HTTPException(status_code=400, detail="table_name is required")
# Validate identifiers to prevent SQL injection
validate_sql_identifier(schema_name, "schema_name")
validate_sql_identifier(table_name, "table_name")
# Build query safely using psycopg2.sql module
if schema_name:
query = sql.SQL("SELECT * FROM {schema}.{table} LIMIT {limit}").format(
schema=sql.Identifier(schema_name),
table=sql.Identifier(table_name),
limit=sql.Literal(limit)
)
else:
query = sql.SQL("SELECT * FROM {table} LIMIT {limit}").format(
table=sql.Identifier(table_name),
limit=sql.Literal(limit)
)
# Apply filters if any
if filters:
# TODO: Implement filter logic with parameterized queries
pass
elif source_type == "sql":
# Direct SQL execution is disabled for security
raise HTTPException(
status_code=403,
detail="Direct SQL execution is disabled for security. Use table-based datasets instead."
)
else:
raise HTTPException(status_code=400, detail=f"Invalid source_type: {source_type}")
# Execute the query
# TODO: Use the specified connection_id to get the right connection
with connection_manager.get_connection() as pg:
results = pg.execute(query, fetch=True)
# Convert to list of dicts
data = []
columns = []
if results and len(results) > 0:
# Get column names from first row
columns = list(results[0].keys())
# Convert rows to dicts
for row in results:
data.append(dict(row))
return {
"success": True,
"columns": columns,
"data": data,
"row_count": len(data)
}
except HTTPException:
raise
except Exception as e:
log_and_raise_error("previewing dataset", e)

Xet Storage Details

Size:
15.4 kB
·
Xet hash:
b615b8c9eda5329e132a8753cf8a526ab99f3a2bcabfc82a13321e99a11aab3e

Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.