#!/usr/bin/env perl

use v5.38;
use DBI;
my $memory;    # = ':memory:';
my $dbname = $memory // '/Users/chap/private/data/music';
my $dbh    = create_database();

# insert some songs. watch out for dupes.
for my $song (qw{ Alpha Beta Gamma Alpha Delta Omicron }) {
    my $rowid = insert_unique( 'Songs', 'song', $song );
    say "Inserting '$song', got rowid=$rowid";
}

sub create_database() {
    unlink $dbname unless $memory;
    my $dbms   = 'SQLite';
    my $dbname = $dbname;
    my $user   = '';
    my $passwd = '';
    my $dbh =
        DBI->connect( "DBI:$dbms:dbname=$dbname", $user, $passwd,
        { RaiseError => 1, AutoCommit => 1 } )
        or die "Cannot connect: $DBI::errstr";

    my $songs = <<~'EOF';
    CREATE TABLE Songs (id        INTEGER PRIMARY KEY,
                 song             VARCHAR(40) UNIQUE
                 )
    EOF
    $dbh->do($songs);
    $dbh->do("CREATE INDEX ix_song ON Songs (song)");

    return $dbh;
}

# sub which conditionally inserts a unique value, and returns rowid
sub insert_unique( $table, $column, $value ) {
    state %sths; # cache my prepared statement handles
    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 (?)");
    }
    my $rowid = $sths{$table}{SELECT}->execute($value) || die;
    say "Value='$value': attempt to select yielded rowid=$rowid";
    if ( $rowid == 0 ) {
        say "So value needs to be inserted.";
        $sths{$table}{INSERT}->execute($value);
        $rowid = $dbh->do("SELECT last_insert_rowid()");
    }
    else {
        say "So value has been previously inserted.";
    }
    return $rowid;
}

##</code><code>##

Value='Alpha': attempt to select yielded rowid=0E0
So value needs to be inserted.
Inserting 'Alpha', got rowid=1
Value='Beta': attempt to select yielded rowid=0E0
So value needs to be inserted.
Inserting 'Beta', got rowid=1
Value='Gamma': attempt to select yielded rowid=0E0
So value needs to be inserted.
Inserting 'Gamma', got rowid=1
Value='Alpha': attempt to select yielded rowid=0E0
So value needs to be inserted.
DBD::SQLite::st execute failed: UNIQUE constraint failed: Songs.song at /Users/chap/private/perl/insert_unique line 51.
DBD::SQLite::st execute failed: UNIQUE constraint failed: Songs.song at /Users/chap/private/perl/insert_unique line 51.

##</code><code>##

$ sqlite3 music
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> select * from Songs;
1|Alpha
2|Beta
3|Gamma