20 lines
646 B
SQL
20 lines
646 B
SQL
-- Database schema for testing drop
|
|
-- Table naming convention: <drop-name>_<table-name>
|
|
|
|
CREATE TABLE IF NOT EXISTS testing_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
text TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Index for faster queries
|
|
CREATE INDEX IF NOT EXISTS idx_testing_items_created
|
|
ON testing_items(created_at);
|
|
|
|
-- Trigger to update updated_at timestamp
|
|
CREATE TRIGGER IF NOT EXISTS update_testing_items_timestamp
|
|
AFTER UPDATE ON testing_items
|
|
BEGIN
|
|
UPDATE testing_items SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
|
|
END;
|