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.
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()
)
Ǹ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.