->execute($value) does not return the rowid.

See last_insert_id of DBI on how to use ->last_insert_id. Also, you can use the RETURNING SQL clause:

my $sql = <<"SQL";
    INSERT INTO $table ($column) VALUES (?)
    RETURNING id
SQL

my $sth = $dbh->prepare($sql);
$sth->execute(...);
my $rows = $sth->fetchall_arrayref( {} );
say "New id: " . $rows->[0]->{id};
[download]

From my code... You can call last_insert_id with no args (all undef) which probably would be fine in your app. Or in my code I asked specifically about the ScoreCard Table.

my $scoreCardrow = $dbh->last_insert_id(undef,undef,"ScoreCard",undef)
+; # last_insert_id($catalog, $schema, $table, $field);
[download]

CREATE TABLE Songs (id        INTEGER PRIMARY KEY,
[download]

Update: Complications can occur with multiple writers, but I don't think that is an issue with your code.

my $rowid = $sths{$table}{SELECT}->execute($value) || die;
I think we covered how to get last rowid. You do not need "|| die" because you have RaiseError =1 which is what I recommend.

Another fine point, 0E0 this is the DB's way of saying logical "true", but numeric "zero".

$dbh->do("CREATE INDEX ix_song ON Songs (song)");
I would leave the index off completely to start with and see how the performance is. You may be surprised at how well SQLite does on its own. The index adds considerably to the time for each insert. There are weird things that can happen in complex tables if you "overindex" them. This confuses the execution strategy engine and you can wind up with slower code! Anyway be careful.

sub insert_unique( $table, $column, $value ) {
    state %sths;
    if ( !exists $sths{$table} ) {
        $sths{$table}{SELECT} =
            $dbh->prepare("SELECT id FROM $table WHERE $column = (?)")
+;
        $sths{$table}{INSERT} =
            $dbh->prepare("INSERT INTO $table ($column) VALUES (?) RET
+URNING id");
    }
    my $rowid;
    my $sth = $sths{$table}{SELECT};
    $sth->execute($value);
    my $rows = $sth->fetchall_arrayref( {} );
    if ($rows->@*) {
        $rowid = $rows->[0]->{id};
    }
    else {
        $sth = $sths{$table}{INSERT};
        $sth->execute($value);
        $rows = $sth->fetchall_arrayref( {} );
        $rowid = $rows->[0]->{id};
    }
    return $rowid;
}
[download]

It does seem to me a lot of code to accomplish a seemingly basic requirement. I'm implementing UNIQUEness, for one thing. But it works, and it's pretty fast adding 60,000 songs. (I didn't notice much difference whether or not the column was indexed.)

It does seem to me a lot of code

It could be streamlined for sure, particularly if you make the database enforce the uniqueness constraint (as in the OP) and just trap the exceptions. But really the worrying part is that $table and $column are SQL injections just waiting to happen.

Also the fact that you've used a state hash to hold the prepared statement handles means that these variables only have relevance on the first invocation, which is an unusual situation. eg. call it once on table foo and then again on table bar and the second one will still use table foo. That's confusing at best. (edit: ignore this part)

---

🦛

Regarding SQL injection -- had to look it up. Is my use of variables $table and $column in those prepared SELECTs necessarily dangerous? I can see how they could be if an enduser were prompted for them, but that's not how I intend to populate them. It's for a specific application with specific table and column names that are hardcoded into the program. As I say, I'm very much a novice at SQL apps. Maybe there's more to this than I'm aware of ... ? Thanks.

I don't understand your comment about the state hash. I copied this subroutine into my actual program, which calls insert_unique() repeatedly for six different tables, and it worked correctly.

I don't know what "it's pretty fast adding 60,000 songs" means. I would add the line $dbh->begin_work; before the loop that puts the 60,000 things into the DB. And the line $dbh->comitt; after the loop is over. This will run all 60,000 inserts as a single transaction. There should be a very noticeable decrease in execution time.

I thought you were saying that the addition of an index to the table would dramatically increase the time it took to load the table. Without begin_work / commit, it took 23 seconds to load the table with an index, and 20 seconds without one. However, running it as a single transaction as you suggest, those numbers drop to 0.77 and 0.72.

I haven't run any comparisons to see how much the index benefits retrieval, however...