SQLite: Inserting a C struct into multiple tables - help with design.

Question

Hi all

Here's a short program that creates a SQLite database to hold some basic music metadata. There are three tables in the database for three fields of metadata; song title, the album the song comes from and the artist who made the album. Due to the nature of the data, duplicated information in metadata is a certainty (as demonstrated in the code).

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>

#include <sqlite3.h>

// Error checks replaced with assert for brevity

#define STRING_MAX 32

// metadata for a audio track
typedef struct {
    char title[ STRING_MAX ];
 char artist[ STRING_MAX ];
 char album[ STRING_MAX ];
} metadata_t;

// some metadata for testing
static metadata_t tracks[] = {
    { "Mr Self Destruct", "Nine Inch Nails", "The Downward Spiral" },
    { "Sit Down, Stand Up", "Radiohead", "Hail to the Thief" },
    { "March of the Pigs", "Nine Inch Nails", "The Downward Spiral" },
};
// number of test tracks in the above array
#define TRACK_COUNT ( sizeof( tracks ) / sizeof( tracks[ 0 ] ) )

int main( int argc, char **argv ) {
    sqlite3 *db;
    int result = sqlite3_open_v2( "database.db3", &db,
     SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, "unix-none" );
    assert( result == SQLITE_OK );

    // create the three tables

    // artists have a name
    result = sqlite3_exec( db, "CREATE TABLE IF NOT EXISTS artists(\
        artist_id INTEGER PRIMARY KEY,\
        name TEXT UNIQUE\
        );",
        NULL, NULL, NULL );
    assert( result == SQLITE_OK );

    // albums have a name and an artist
    result = sqlite3_exec( db, "CREATE TABLE IF NOT EXISTS albums(\
        album_id INTEGER PRIMARY KEY,\
        name TEXT UNIQUE,\
        artist_id INTEGER,\
        UNIQUE( name, artist_id )\
        );",
        NULL, NULL, NULL );
    assert( result == SQLITE_OK );

    // titles have a name and belong to an album
    result = sqlite3_exec( db, "CREATE TABLE IF NOT EXISTS titles(\
        title_id INTEGER PRIMARY KEY,\
        name TEXT,\
        album_id INTEGER\
        );",
        NULL, NULL, NULL );
    assert( result == SQLITE_OK );

    // insert the metadata into the databse, one track at a time
    int i;
    for ( i = 0; i < TRACK_COUNT; ++i ) {
        char command[ 1024 ]; // The SQL to execute
        int result;
        sqlite3_stmt *stmt;

        // Ignore the UNIQUE error if the artist is already in the table.
        // This makes sqlite3_last_insert_rowid() not work.
        (void)sqlite3_snprintf( 1024, command,
            "INSERT OR IGNORE INTO artists( name )\
            VALUES( '%q' );",
            tracks[ i ].artist );
        result = sqlite3_exec( db, command, NULL, NULL, NULL );
        assert( result == SQLITE_OK );

        // Get the rowid for the newly inserted artist
        (void)sqlite3_snprintf( 1024, command,
            "SELECT artist_id FROM artists WHERE name='%q';",
            tracks[ i ].artist );
        sqlite3_prepare( db, command, strlen( command ), &stmt, NULL );
        assert( sqlite3_column_count( stmt ) == 1 );
        sqlite3_step( stmt );
        int artist_id = sqlite3_column_int( stmt, 0 );
        assert( sqlite3_step( stmt ) == SQLITE_DONE );
        sqlite3_finalize( stmt );

        // Ignore the UNIQUE error if the album/artist_id combo is
        // already in the table
        (void)sqlite3_snprintf( 1024, command,
            "INSERT OR IGNORE INTO albums( name, artist_id )\
            VALUES( '%q', %d );",
            tracks[ i ].album, artist_id );
        result = sqlite3_exec( db, command, NULL, NULL, NULL );
        assert( result == SQLITE_OK );

        // Get the rowid for the newly inserted album
        (void)sqlite3_snprintf( 1024, command,
            "SELECT album_id FROM albums WHERE name='%q';",
            tracks[ i ].album );
        sqlite3_prepare( db, command, strlen( command ), &stmt, NULL );
        assert( sqlite3_column_count( stmt ) == 1 );
        sqlite3_step( stmt );
        int album_id = sqlite3_column_int( stmt, 0 );
        assert( sqlite3_step( stmt ) == SQLITE_DONE );
        sqlite3_finalize( stmt );

        // Finally, insert the track title and the album it came from
        (void)sqlite3_snprintf( 1024, command,
            "INSERT INTO titles( name, album_id )\
            VALUES( '%q', %d );",
            tracks[ i ].title, album_id );
        result = sqlite3_exec( db, command, NULL, NULL, NULL );
        assert( result == SQLITE_OK );
    }
    sqlite3_close( db );
    return ( 0 );
}

Compile and test:
$ gcc -Wall database.c -o database -lsqlite3 && ./database && sqlite3 database.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers on
sqlite> .mode csv
sqlite> SELECT
...> titles.name AS title,
...> albums.name AS album,
...> artists.name AS artist
...> FROM titles
...> INNER JOIN albums USING( album_id )
...> INNER JOIN artists USING( artist_id );
title,album,artist
"Mr Self Destruct","The Downward Spiral","Nine Inch Nails"
"Sit Down, Stand Up","Hail to the Thief",Radiohead
"March of the Pigs","The Downward Spiral","Nine Inch Nails"

Because each INSERT could be dealing with data that is already in the database, I am using INSERT OR IGNORE, which means I can no longer rely on sqlite_last_insert_rowid() to give me the ROWID for artist_id and album_id, hence why I then search the database to get the ROWIDs. Any suggestions on a better design would be great!

Answer 1

What if you used INSERT OR REPLACE? Inserts should take the same amount of time. Duplicates shouldn't affect anything (might not take longer than doing a secondary search overall). The docs say it's treated as a success and the last rowid will be updated. However, I don't know whether the replacement will actually change the existing rowid in the table, throwing off your primary keys.

Answer 2

Answering my own question feels a little bit wrong; I'm still open to better suggestions! This generic insert function will insert the data if it doesn't exist, and return the ROWID.

sqlite3_int64 _insert_generic(
    sqlite3 *db,
    const char *table,
    const char *key,
    const char *value
    ) {

    char sql[ SQL_MAX ];
    int result;
    sqlite3_stmt *stmt;
    sqlite3_int64 id;

    /* check if this key/value is already in table */
    (void)sqlite3_snprintf( SQL_MAX, sql,
    "SELECT %s FROM %s WHERE name='%q';",
        key,
        table,
        value );
    result = sqlite3_prepare_v2( db, sql, strlen( sql ), &stmt, NULL );
    assert( result == SQLITE_OK );

    if ( sqlite3_step( stmt ) == SQLITE_ROW ) {
        /* a key/value was found in table, get the ROWID */
        id = sqlite3_column_int( stmt, 0 );
        assert( sqlite3_step( stmt ) == SQLITE_DONE );
        sqlite3_finalize( stmt );
    } else {
        /* key/value is not in table, so insert it */
        sqlite3_finalize( stmt );
        (void)sqlite3_snprintf( SQL_MAX, sql,
            "INSERT INTO %s( name )\
            VALUES( '%q' );",
            table,
            value );
        result = sqlite3_exec( db, sql, NULL, NULL, NULL );
        assert( result == SQLITE_OK );
        id = sqlite3_last_insert_rowid( db );
    }
    assert( id > 0 );
    return ( id );
}