OpenEuphoria

Pastey Euphoria MVC SQLite3 Example

• Posted by ghaberek (admin) Jan 12, 2022

include std/filesys.e
include std/pretty.e 
include db/sqlite3.e 
 
constant TRUE=1, FALSE=0 
 
sequence PRETTY_INLINE = PRETTY_DEFAULT 
PRETTY_INLINE[DISPLAY_ASCII] = 3 
PRETTY_INLINE[LINE_BREAKS] = FALSE 
 
-- pretty print a value on a single line 
procedure pretty_inline( integer fn, object data ) 
    pretty_print( fn, data, PRETTY_INLINE ) 
    puts( fn, "\n" ) 
end procedure 
 
procedure main() 
 
    atom rc, db, stmt 
    sequence sql, data, cols 
 
    -- delete the database file if it exists 
    if file_exists( "test.db" ) then 
        delete_file( "test.db" ) 
    end if 
 
    -- open the database file (this will create it) 
    {rc,db} = sqlite3_open( "test.db" ) 
 
    -- check for errors 
    if rc != SQLITE_OK then 
        -- use sqlite3_errstr() here since 
        -- we don't have a database handle. 
        printf( 2, "SQLite3 Error: %s\n", {sqlite3_errstr(rc)} ) 
    end if 
 
    -- use backtick or triple-quote 
    -- string to span multiple lines 
    sql = `CREATE TABLE users ( 

        id INTEGER PRIMARY KEY, 
        username TEXT NOT NULL, 
        password TEXT NOT NULL, 
        email TEXT NOT NULL, 
        enabled INTEGER NOT NULL 
    );` 
 
    -- execute the command 
    rc = sqlite3_exec( db, sql ) 
 
    -- check teh result 
    if rc != SQLITE_OK then 
        -- use sqlite3_errmsg() to get the 
        -- error from the database handle. 
        printf( 2, "CREATE error: %s\n", {sqlite3_errmsg(db)} ) 
        return 
    end if 
 
    -- build a parameterized insert query 
    sql = `INSERT INTO users ( 

        id, 
        username, 
        password, 
        email, 
        enabled 
    ) VALUES( 
        ?1, -- id 
        ?2, -- username 
        ?3, -- password 
        ?4, -- email 
        ?5  -- enabled 
    );` 
 
    -- build a list of data to insert 
    data = { 
        { 1, "alice", "88ba2bd3a810cd41f57e0f8d1774d2c7", "alice@example.com", TRUE  }, 
        { 2, "bob",   "a914dff4d3a2fe744bbe6b693c1255b8", "bob@example.com",   TRUE  }, 
        { 3, "carol", "aec9ab0b98650be2624b01e2e26d7f14", "carol@example.com", FALSE }, 
        { 4, "dave",  "da417cadb3f7383922024686d88fa6a3", "dave@example.com",  FALSE }, 
        { 5, "eve",   "31bec0ebc9aaf2c6d91bb36dfe592e4c", "eve@example.com",   FALSE } 
    } 
 
    -- loop through the data 
    for i = 1 to length( data ) do 
 
        -- insert each row using a separate statement 
        rc = sqlite3_exec_stmt( db, sql, data[i] ) 
 
        -- insert, etc. should return SQLITE_DONE not SQLITE_OK 
        if rc != SQLITE_DONE then 
            printf( 2, "INSERT error: %s\n", {sqlite3_errmsg(db)} ) 
            return 
        end if 
 
    end for 
 
    -- build a parameterized select query 
    sql = `SELECT * 

         FROM users 
        WHERE username = ?1 
           OR enabled = ?2; 
    ` 

 
    -- prepare the statement 
    {rc,stmt} = sqlite3_prepare_v2( db, sql ) 
 
    if rc != SQLITE_OK then 
        printf( 2, "SELECT error: %s\n", {sqlite3_errmsg(db)} ) 
        return 
    end if 
 
    -- assign the parameters 
    sqlite3_assign_params( stmt, {"carol",TRUE} ) -- ?1, ?2 
 
    cols = {} 
 
    -- collect the resulting column names 
    for i = 1 to sqlite3_column_count( stmt ) do 
        cols = append( cols, sqlite3_column_name(stmt,i-1) ) 
    end for 
 
    -- print the column names 
    pretty_inline( 1, cols ) 
 
    -- loop through all the results 
    while TRUE do 
 
        -- fetch the next result 
        rc = sqlite3_step( stmt ) 
 
        -- did we get a row back? 
        if rc != SQLITE_ROW then 
            exit 
        end if 
 
        -- fetch the current row data 
        data = sqlite3_fetch_row( stmt ) 
 
        -- print the row data 
        pretty_inline( 1, data ) 
 
    end while 
 
    -- close the database 
    sqlite3_close( db ) 
 
end procedure 
 
main()