0

I'm seeing some odd behaviour when inserting records to a table of a sqlite DB in a Ruby application.

I have an application that fetches data from a series of API's, manipulates it and stitches it back together before inserting to a sqlite table.

I'm seeing an issue on insertions for one of the tables where the same 20 or so records appear to be inserted (I can see them in the SQLite viewer in the first few seconds after they are inserted), but then vanish. It's the same records that vanish each time.

The table i'm inserting to can be replicated like this:

CREATE TABLE cont (cont_id INTEGER PRIMARY KEY, type TEXT, title TEXT, creation_date DATE, total_count INTEGER, unique_count INTEGER, uri TEXT, ml_id INTEGER, FOREIGN KEY(ml_id) REFERENCES ml(id));

The method for inserting the record looks like this:

def save(table, columns, values, alternate = "IGNORE")
    tb_conf = @tables[table.upcase]
    placeholders = values.map {|v| '?'}.join(',')
    sql = "INSERT OR #{alternate} INTO #{table} (#{columns}) VALUES (#{placeholders})"

    begin   
        db = SQLite3::Database.new(@db_path)
        statement = db.prepare(sql)
        values.each_with_index {|value, index| statement.bind_param(index + 1, value)}
        statement.execute
        statement.close
    rescue  SQLite3::Exception => e
        p "SQLITE ERROR #{e}"
    ensure 
        db.close if db
    end        
    
end

This method is called with the following:

db.save(@table,@columns.join(','),values,'REPLACE')

Where the table is a string, the columns a comma delimited string of field names and the values an array like:

[172546, "ext_link", "https://example.net/dept/sub/eur/documents/report_Final.pdf", "2023-01-24", 9, 6, "https://api.example.com/v1/account_id/31889?cont=172546", 366597]

I'm not sure why they would appear in the table for a few seconds before disappearing. There are no exceptions thrown so I assume that this might be expected behaviour as a result of something i'm missing.

Bill Dukelow
  • 464
  • 4
  • 21
  • You might have to [commit](https://www.rubydoc.info/github/luislavena/sqlite3-ruby/SQLite3%2FDatabase:commit). Or something is dropping and creating your table, or deleting everything. – Schwern Feb 25 '23 at 01:30
  • It's only about 20 records out of 1000 that actually vanish. The same 20 each time. There is nothing to drop the table or to delete anything. Outside of the initial schema build, i'm only inserting or selecting. I assumed this autocommits as it's outside of a transaction block? – Bill Dukelow Feb 25 '23 at 01:37
  • I'm not sure if it autocomnits or not. However, the most likely explanation is something is deleting those rows. Either in your code, or another process. There are tools to watch what processes open a file, you can monitor your SQLite database. – Schwern Feb 25 '23 at 16:54
  • What is `db`? A DB connection? You are defining a method called `.save` for it. What is its class and how does it come to be? Could there already be a `.save` method for the class that `db` belongs to? – Beartech Feb 25 '23 at 18:18
  • I see that you are calling `db` inside the `.save` method... Scope shouldn't be an issue here... But that also makes me wonder if you are using `db` somewhere else where scope IS an issue. Just for readability I'd probably change it to something else. or if the original `db` is a database connection I'd call it `db_connection`. – Beartech Feb 25 '23 at 18:26
  • db is a variable to persist the DB connection until it's closed with the scope of the method it's established in (most methods in the DB class). Save is a method on a DB class. The issue was a data quality one at one of the source API's. The cont_id retrieved from this source is supposed to be unique according to the docs, but it's not. This was acknowledged by the owners of that API who are looking into the issue. Because i'm passing REPLACE with my insert, the records that i'm missing get replaced by the records that had a duplicate cont_id. – Bill Dukelow Feb 27 '23 at 17:42

0 Answers0