Skip to content

sqlite.md

Latest commit

 

History

History
190 lines (132 loc) · 4.22 KB

sqlite.md

File metadata and controls

190 lines (132 loc) · 4.22 KB
Raw

SQLite Module

The sqlite module provides full SQLite database access: opening/closing databases, executing non-query statements, and running queries that return rows as arrays of dictionaries.

Import

import sqlite;

Types

PIE type What it represents
database An open SQLite database handle

Declare database variables as:

database db = sqlite.open("myapp.sqlite");

Functions

sqlite.open(path)

Open (or create) a SQLite database file.

database sqlite.open(string path)
  • Returns a database handle on success, or null on failure.
  • If the file does not exist, SQLite creates it.
database db = sqlite.open("myapp.sqlite");
if (db == null) {
    output("Error: Could not open database", string);
    return;
}

sqlite.close(db)

Close the database and release resources.

void sqlite.close(database db)

Always close the database when done:

sqlite.close(db);

sqlite.exec(db, sql)

Execute a SQL statement that does not return rows (DDL or DML).

void sqlite.exec(database db, string sql)

Use for CREATE TABLE, INSERT, UPDATE, DELETE:

sqlite.exec(db, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
sqlite.exec(db, "INSERT INTO users (name, age) VALUES ('Alice', 30)");
sqlite.exec(db, "UPDATE users SET age = 31 WHERE name = 'Alice'");
sqlite.exec(db, "DELETE FROM users WHERE age < 18");

sqlite.query(db, sql)

Execute a SELECT statement and return the rows as an array of dictionaries.

dict[] sqlite.query(database db, string sql)
  • Each row becomes a dict where the keys are column names.
  • Column values are automatically typed: integers become int, reals become float, text becomes string.
dict rows[] = sqlite.query(db, "SELECT * FROM users");
int count = arr_size(rows);
for (int i = 0; i < count; i++) {
    dict row = rows[i];
    string name = dict_get(row, "name");
    int age = dict_get(row, "age");
    output(name + " is " + int_to_string(age) + " years old", string);
}

Parameterized Queries (string building)

PIE does not have prepared statements — build query strings with string concatenation. Always sanitize or constrain user input before embedding it:

string username = "Alice";
string sql = "SELECT * FROM users WHERE name = '" + username + "'";
dict rows[] = sqlite.query(db, sql);

Transactions

Wrap multiple writes in a transaction for atomicity and performance:

sqlite.exec(db, "BEGIN TRANSACTION");

sqlite.exec(db, "INSERT INTO orders (item, qty) VALUES ('apple', 3)");
sqlite.exec(db, "INSERT INTO orders (item, qty) VALUES ('banana', 5)");
sqlite.exec(db, "UPDATE inventory SET stock = stock - 3 WHERE item = 'apple'");

sqlite.exec(db, "COMMIT");

To roll back on error:

sqlite.exec(db, "BEGIN TRANSACTION");
// ... operations ...
sqlite.exec(db, "ROLLBACK");

Complete Example

import sqlite;

void main() {
    database db = sqlite.open("school.sqlite");
    if (db == null) {
        output("Failed to open database", string);
        return;
    }

    // Create table
    sqlite.exec(db, "CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, grade REAL)");

    // Insert rows
    sqlite.exec(db, "INSERT INTO students (name, grade) VALUES ('Alice', 92.5)");
    sqlite.exec(db, "INSERT INTO students (name, grade) VALUES ('Bob', 87.0)");
    sqlite.exec(db, "INSERT INTO students (name, grade) VALUES ('Carol', 95.0)");

    // Query
    dict rows[] = sqlite.query(db, "SELECT name, grade FROM students ORDER BY grade DESC");
    int count = arr_size(rows);

    output("Students by grade:", string);
    for (int i = 0; i < count; i++) {
        dict row = rows[i];
        string name = dict_get(row, "name");
        float grade = dict_get(row, "grade");
        output(name, string);
        output(grade, float, 1);
    }

    sqlite.close(db);
}

See Also