How to INSERT information into a Sqlite database using Genie Programming Language?

Question

This question is a spin off a previous question here in which a database was created. However, when it comes to add information to that dataset I can go manually adding information or going via a programmatically way. The latter is my choice for a didactic reason.

The equivalent of what I am trying to do in python is:

for x in cursor.execute(sql):
    lastid = x[0]
    # Insert data into the instructions table
    sql = 'INSERT INTO Instructions (recipeID,instructions) VALUES( %s,"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.")' % lastid
    cursor.execute(sql)

The way I am going about it is:

//Insert the rest of instructions
var last_id = db.last_insert_rowid()
for var x in last_id
    query = """INSERT INTO Instructions (recipeID,instructions) VALUES(
         %s,
         "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."
         ), x
            """

However, it seems that last_id is a int64 type that cannot be an iterator, as per the error that I get:

valac --pkg sqlite3 cookcreate.gs cookcreate.gs:55.18-55.24: error: int64' does not have aniterator' method for var x in last_id ^^^^^^^ Compilation failed: 1 error(s), 0 warning(s)

How to solve this problem with code in Genie? Should I convert it to another type, that accepts being used as an iterator? Also, is that syntax (%s), x correct?

Thanks

Answer 1

The key point of your problem is how to get the last insert value (the primary key for the Recipes table) and put it into the next statement.

To make the insert completely safe (proof against SQL injection) you should be using a prepared statement.

I also added a lot more error checking.

[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))
    // Warning: Statment.step uses a different return value mechanism
    //          check_ok can't be used here
    if (instr_stmt.step () != Sqlite.DONE)
        stderr.printf ("Error: %d: %s\n", db.errcode (), db.errmsg ())
        Process.exit (-1)

PS: If I were to write a real program, I'd probably first write a higher level SQLite abstraction with error domains. Using this abstraction the code would be a lot shorter.

Answer 2

The problem you seem to have is using the last_insert_rowid() to make the foreign key. last_insert_rowid() is a single value, not a collection of values. So there is no need to loop over it in a for loop.

The following example uses prepared statements to insert values into two tables. The first table holds a user name and the second table holds a foreign key to the user table and a randomly generated reference ID.

The problem area you are looking at is data loading. So this program could form the basis of a data load program that takes advantage of Genie's performance. For example if you want to tidy the data in some way before loading then Genie may be good for this. More details on performance later.

[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()
                                 )

Some points to note:

• The functions to create the database and tables are at the end of the program because they are only there for the example to work
• Using try...except allows the program to stop when an error occurs by de-referencing any object when the try block ends and then the except block can use Process.exit( -1 ) safely. By returning -1 the program can signal to any calling script that the load failed
• The program has been split into separate functions and classes, note that the database connection is passed as an argument to each function, this is the principle of encapsulation in programming
• The DataGenerator class also provides an example of encapsulation, it keeps track of how many examples it has generated and then stops when _max_iterations limit is exceeded
• The DataGenerator class could just as easily be used to read from a file. Hopefully you can start to see how Genie, like any other object oriented programming language, can help to modularise your code
• Each user has two posts, so the program has to store the last_insert_rowid() or the data will be corrupted when last_insert_rowid() changes to the ID of the first post inserted
• The DataGenerator creates ten thousand examples and these are loaded in about a quarter of a second on my machine. Comment out the BEGIN TRANSACTION and END TRANSACTION lines and the program takes about one hundred and sixty seconds! So for data loading in SQLite a transaction is a huge performance boost
• In this example the prepared statements in a transaction are faster than loading a dump of the database

  sqlite3 example.sqlite .dump > backup.sql
  time cat backup.sql | sqlite3 test.sqlite

  takes about 0.8s on my machine, whereas the program takes about 0.25s