|
// retoor <retoor@molodetz.nl>
|
|
|
|
import "sqlite" for Database
|
|
|
|
System.print("=== SQLite Module Demo ===\n")
|
|
|
|
System.print("--- Creating In-Memory Database ---")
|
|
var db = Database.memory()
|
|
System.print("Database opened successfully")
|
|
|
|
System.print("\n--- Creating Table ---")
|
|
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER)")
|
|
System.print("Table 'users' created")
|
|
|
|
System.print("\n--- Inserting Data ---")
|
|
db.execute("INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30)")
|
|
System.print("Last insert ID: %(db.lastInsertId)")
|
|
|
|
db.execute("INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 25)")
|
|
System.print("Last insert ID: %(db.lastInsertId)")
|
|
|
|
db.execute("INSERT INTO users (name, email, age) VALUES ('Charlie', 'charlie@example.com', 35)")
|
|
System.print("Last insert ID: %(db.lastInsertId)")
|
|
|
|
System.print("Rows changed: %(db.changes)")
|
|
|
|
System.print("\n--- Parameterized Insert ---")
|
|
db.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", ["Diana", "diana@example.com", 28])
|
|
System.print("Inserted Diana with ID: %(db.lastInsertId)")
|
|
|
|
System.print("\n--- Querying All Data ---")
|
|
var allUsers = db.query("SELECT * FROM users")
|
|
System.print("All users:")
|
|
for (user in allUsers) {
|
|
System.print(" %(user["id"]): %(user["name"]) <%(user["email"])> age %(user["age"])")
|
|
}
|
|
|
|
System.print("\n--- Parameterized Query ---")
|
|
var olderUsers = db.query("SELECT * FROM users WHERE age > ?", [27])
|
|
System.print("Users older than 27:")
|
|
for (user in olderUsers) {
|
|
System.print(" %(user["name"]) (age %(user["age"]))")
|
|
}
|
|
|
|
System.print("\n--- Single Result Query ---")
|
|
var singleUser = db.query("SELECT * FROM users WHERE name = ?", ["Alice"])
|
|
if (singleUser.count > 0) {
|
|
System.print("Found Alice: %(singleUser[0]["email"])")
|
|
}
|
|
|
|
System.print("\n--- Aggregate Query ---")
|
|
var avgAge = db.query("SELECT AVG(age) as avg_age, COUNT(*) as count FROM users")
|
|
System.print("Average age: %(avgAge[0]["avg_age"])")
|
|
System.print("Total users: %(avgAge[0]["count"])")
|
|
|
|
System.print("\n--- Updating Data ---")
|
|
db.execute("UPDATE users SET age = ? WHERE name = ?", [31, "Alice"])
|
|
System.print("Rows updated: %(db.changes)")
|
|
|
|
var updated = db.query("SELECT age FROM users WHERE name = 'Alice'")
|
|
System.print("Alice's new age: %(updated[0]["age"])")
|
|
|
|
System.print("\n--- Deleting Data ---")
|
|
db.execute("DELETE FROM users WHERE name = ?", ["Charlie"])
|
|
System.print("Rows deleted: %(db.changes)")
|
|
|
|
System.print("\n--- Remaining Users ---")
|
|
var remaining = db.query("SELECT name FROM users ORDER BY name")
|
|
System.print("Users: %(remaining)")
|
|
|
|
System.print("\n--- Creating Another Table ---")
|
|
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT, amount REAL)")
|
|
db.execute("INSERT INTO orders (user_id, product, amount) VALUES (1, 'Widget', 19.99)")
|
|
db.execute("INSERT INTO orders (user_id, product, amount) VALUES (1, 'Gadget', 29.99)")
|
|
db.execute("INSERT INTO orders (user_id, product, amount) VALUES (2, 'Widget', 19.99)")
|
|
|
|
System.print("\n--- Join Query ---")
|
|
var ordersWithUsers = db.query("SELECT u.name, o.product, o.amount FROM users u JOIN orders o ON u.id = o.user_id")
|
|
System.print("Orders with user names:")
|
|
for (row in ordersWithUsers) {
|
|
System.print(" %(row["name"]) ordered %(row["product"]) for $%(row["amount"])")
|
|
}
|
|
|
|
System.print("\n--- Transaction Example ---")
|
|
db.execute("BEGIN TRANSACTION")
|
|
db.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", ["Eve", "eve@example.com", 22])
|
|
db.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", ["Frank", "frank@example.com", 45])
|
|
db.execute("COMMIT")
|
|
System.print("Transaction committed")
|
|
|
|
var afterTransaction = db.query("SELECT COUNT(*) as count FROM users")
|
|
System.print("Total users after transaction: %(afterTransaction[0]["count"])")
|
|
|
|
System.print("\n--- Closing Database ---")
|
|
db.close()
|
|
System.print("Database closed")
|
|
|
|
System.print("\n--- File-based Database Example ---")
|
|
var fileDb = Database.open("/tmp/wren_demo.db")
|
|
fileDb.execute("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)")
|
|
fileDb.execute("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", ["version", "1.0.0"])
|
|
fileDb.execute("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", ["theme", "dark"])
|
|
|
|
var settings = fileDb.query("SELECT * FROM settings")
|
|
System.print("Settings:")
|
|
for (s in settings) {
|
|
System.print(" %(s["key"]) = %(s["value"])")
|
|
}
|
|
|
|
fileDb.close()
|
|
System.print("File database closed")
|
|
System.print("Database saved to: /tmp/wren_demo.db")
|