Skip to content

Instantly share code, notes, and snippets.

@lf-araujo

lf-araujo/2014-10-10-sql.md

Last active March 31, 2019 10:38
Show Gist options
  • Save lf-araujo/589d77307152eec9b89a1f8fb86b5c3b to your computer and use it in GitHub Desktop.
Save lf-araujo/589d77307152eec9b89a1f8fb86b5c3b to your computer and use it in GitHub Desktop.
Download ZIP
Creating a SQL Database with Genie
Raw
2014-10-10-sql.md

This is part of a series of posts about the Genie programming language. I am updating my progress in learning the language from scratch in the hopes it will be useful for anyone also trying to get to programming in the linux/Gtk world. This exercise was taken from a similar one that was posted at Full Circle Magazine course of basic python. I tried to reproduce the same code, but now using the Genie programming language.

Creating the database

Here is a simple example of how to create a SQL database with Genie and to inject information into it. Note that the use of checked_exec is to improve the safety and speed of the data injection.

[indent=4]
uses Sqlite

exception DatabaseError
    FAILED_TO_CREATE_DATABASE
    FAILED_TO_CREATE_TABLES
    FAILED_TO_LOAD_DATA

init
    try
        database:Database = create_database( "example.sqlite" )
        create_tables( database )
        load_data( database )
    except error:DatabaseError
        print error.message
        Process.exit( -1 )

def load_data( db:Database ) raises DatabaseError
    user_insert_stmnt:Statement = prepare_user_insert_stmnt( db )
    posts_insert_stmnt:Statement = prepare_posts_insert_stmnt( db )

    var data = new DataGenerator()
    user_id:int64 = 0
    db.exec( "BEGIN TRANSACTION" )
    while data.read()
        user_insert_stmnt.bind_text(
                    user_insert_stmnt.bind_parameter_index( "@name" ),
                    data.user_name
                    )
        user_insert_stmnt.step()
        user_insert_stmnt.reset()
        user_id = db.last_insert_rowid()
        for var reference_id in data.reference_ids
            posts_insert_stmnt.bind_int64(
                        posts_insert_stmnt.bind_parameter_index( "@user_id" ),
                        user_id
                        )
            posts_insert_stmnt.bind_int64(
                        posts_insert_stmnt.bind_parameter_index( "@reference_id" ),
                        reference_id
                        )
            posts_insert_stmnt.step()
            posts_insert_stmnt.reset()
    db.exec( "END TRANSACTION" )

def prepare_user_insert_stmnt( db:Database ):Statement
    statement:Statement
    db.prepare_v2( """
insert into users(
    name
    )
    values( @name )
""", -1, out statement )
    return statement

def prepare_posts_insert_stmnt( db:Database ):Statement
    statement:Statement
    db.prepare_v2( """
insert into posts(
    user_id,
    reference_id
    )
    values( @user_id, @reference_id )
""", -1, out statement )
    return statement

class DataGenerator
    user_name:string = ""
    reference_ids:array of uint = new array of uint[ 2 ]

    _iteration:int = 0
    _max_iterations:int = 10000

    def read():bool
        user_name = "User%06d".printf( _iteration )
        _iteration++

        for a:int = 0 to (reference_ids.length -1)
            reference_ids[ a ] = Random.next_int()

        more:bool = true
        if _iteration > _max_iterations
            more = false
        return more

def create_database( db_name:string ):Database raises DatabaseError
    db:Database
    result:int = Database.open( db_name, out db )
    if result != OK
        raise new DatabaseError.FAILED_TO_CREATE_DATABASE(
                                 "Can't create %s SQLite error %d, \"%s\"",
                                 db_name,
                                 db.errcode(),
                                 db.errmsg()
                                 )
    return db

def create_tables( db:Database ) raises DatabaseError
    sql:string = """
create table users ( id integer primary key,
                    name varchar not null
                    );
create table posts ( id integer primary key,
                    user_id integer not null,
                    reference_id integer not null
                    );
"""
    if db.exec( sql ) != OK
        raise new DatabaseError.FAILED_TO_CREATE_TABLES(
                                 "Can't create tables. SQLite error %d, \"%s\"",
                                 db.errcode(),
                                 db.errmsg()
                                 )

Adding data to the database

Ǹow let's add the first information to the newly created database.

[indent=4]

def check_ok (db : Sqlite.Database, ec : int)
    if (ec != Sqlite.OK)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

def checked_exec (db : Sqlite.Database, sql : string)
    check_ok (db, db.exec (sql))

init
    // Opening/creating database. Database name is cookbook.db3
    db : Sqlite.Database? = null
    if (Sqlite.Database.open ("cookbook.db3", out db) != Sqlite.OK)
        stderr.printf ("Error: %d: %s \n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    checked_exec (db, "CREATE TABLE Recipes (pkiD INTEGER PRIMARY KEY, name TEXT, servings TEXT, source TEXT)")
    checked_exec (db, "CREATE TABLE Instructions (pkID INTEGER PRIMARY KEY, instructions TEXT, recipeID NUMERIC)")
    checked_exec (db, "CREATE TABLE Ingredients (pkID INTEGER PRIMARY KEY, ingredients TEXT, recipeID NUMERIC)")

    // Insert data into Recipe table
    checked_exec (db, """INSERT INTO Recipes (name, servings, source) VALUES ("Spanish Rice", 4, "Greg")""")
    lastid : int64 = db.last_insert_rowid ()

    // Insert data into Inctructions table
    instr_sql : string = """INSERT INTO Instructions (recipeID, instructions) VALUES($recipeID, "Brown hamburger. Stir in all other ingredients. Bring to a boil. Stir. Lower to simmer. Cover and cook for 20 minutes or until all liquid is absorbed.")"""
    instr_stmt : Sqlite.Statement = null
    check_ok (db, db.prepare_v2 (instr_sql, instr_sql.length, out instr_stmt))
    param_position : int = instr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, instr_stmt.bind_int64 (param_position, lastid))

    if (instr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    // Insert data into Ingredients
    ingr_sql:string = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 cup parboiled Rice (uncooked)")"""
    ingr_stmt:Sqlite.Statement = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok(db, ingr_stmt.bind_int64(param_position,lastid))

    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 8 oz can Tomato Sauce")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok(db, ingr_stmt.bind_int64(param_position,lastid))

    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 small Onion chopped")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))

    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 clove Garlic chopped")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 tablespoon Ground Cumin")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 teaspoon Ground Oregano")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"Salt and Pepper to taste")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"Salsa to taste")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    // Insert ONE MORE data into Recipe table
    checked_exec (db, """INSERT INTO Recipes (name,servings,source) VALUES ("Pickled Pepper-Onion Relish","9 half pints","Complete Guide to Home Canning")""")
    lastid = db.last_insert_rowid ()

    // Insert data into Inctructions table
    instr_sql = """INSERT INTO Instructions (recipeID,instructions) VALUES( $recipeID,"Wash and chop vegetables. Combine all ingredients and boil gently until mixture thickens and volume is reduced by 1/2 (about 30 minutes). Fill sterile jars with hot relish, leaving 1/2 inch head space and seal tightly. Store in refrigerator and use within one month or process in boiling water bath if extended storage is desired. Hot pack process time at 0-1000 feet for 5 minutes, 1,001 to 6000 ft 10 minutes, above 6,000 ft 15 minutes.")"""
    instr_stmt = null
    check_ok (db, db.prepare_v2 (instr_sql, instr_sql.length, out instr_stmt))
    param_position = instr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, instr_stmt.bind_int64 (param_position, lastid))

    if (instr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    // Insert data into Ingredients
    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"6 cups finely chopped Onions")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok(db, ingr_stmt.bind_int64(param_position,lastid))

    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"3 cups finely chopped Red Peppers")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok(db, ingr_stmt.bind_int64(param_position,lastid))

    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"3 cups finely chopped Green Peppers")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))

    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"1 1/2 cups sugar")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"6 cups White Vinegar (5 percent)")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    ingr_sql = """INSERT INTO Ingredients (recipeID,ingredients) VALUES ( $recipeID,"2 tablespoons canning or pickling salt")"""
    ingr_stmt = null
    check_ok (db, db.prepare_v2 (ingr_sql, ingr_sql.length, out ingr_stmt))
    param_position = ingr_stmt.bind_parameter_index ("$recipeID")
    assert (param_position > 0)
    check_ok (db, ingr_stmt.bind_int64 (param_position, lastid))
    if (ingr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    print "Done"

Running this small program will produce a cookbook.db3 file in the directory, that can be explored using any SQL database inspection utility you like. Although the code listings are fairly long, they show how expressive Genie language is, it is also relatively easy to interface with SQL as well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment