Nicholas S Nicholas S - 5 months ago 16
Ruby Question

Possible to Return a Bool Value When Attempting to Insert Into SQLite Database?

I created an SQLite database from the command line with the the columns: title, date, url and location. Then I added a unique constraint on title, date, url and location with the goal that this would prevent identical rows from being inserted.

I inserted the first batch of data and it worked perfectly. To test the unique constraint I attempted to re-insert all the data that and got a:

...SQLite3::ConstraintException: UNIQUE constraint failed: events.title...


error as expected.

However, I don't want the program to stop running should an insert fail, rather I want it to return a Bool so I can increment a variable called new events when inserts are successful.

Do I need to change how I setup my database or is there a way of doing this?

Incidentally I am using Ruby and Sequel.

Here's the source code for the insert

newEventCount = 0
events.each do |event|
result = eventDB.insert(:title => event.get_title, :date => event.get_date, :url => event.get_url, :location => event.get_location)
if result == false
puts "Already have it"
else
newEventCount = newEventCount + 1
end
end


Any and all guidance is deeply appreciated and please forgive my ignorance if this question seems completely obtuse. Working with databases is very new to me.

Answer

Returning false means errors can be ignored, plus it means propagating those errors can require a lot of work. Exceptions bubble up automatically, and if they get to the top this is what happens: Your program crashes.

The way to mitigate this is to handle the exception:

events.each do |event|
  begin
    eventDB.insert(
      title: event.get_title,
      date: event.get_date,
      url: event.get_url,
      location: event.get_location
     )

     newEventCount += 1
  rescue SQLite3::ConstraintException
    # Already inserted, can be ignored.
  end
end

That pattern of rescuing a very specific, expected exception is often necessary.