Upsert fails silently in Rails when run with SQlite3

[batter]

I am running on a rails 3.2.13 application, along with sqlite3 1.3.7, attempting to use upsert 1.2.0 in my application but it doesn't seem to want to work and I'm stumped as to what I'm doing wrong. Here's what I've done so far:

1. Added gem 'upsert', '~> 1.2.0' to my Gemfile
2. Ran rake db:test:prepare
3. Run rails c test, attempt to use upsert:

> require 'upsert/active_record_upsert'
=> true
> Program.upsert({uid: UUIDTools::UUID.timestamp_create.to_s}, name: 'Foobar')
=> nil
> Program.count
=> 0

Examining the log/test.log here is the output:

Connecting to database specified by database.yml
[upsert] PRAGMA table_info("programs")
[upsert] INSERT OR IGNORE INTO "programs" ("name","uid") VALUES (?,?) with ["Foobar", "72736c0c-addd-11e2-a105-c82a14fffe94"]
[upsert] UPDATE "programs" SET "name"=?,"uid"=? WHERE "uid"=? with ["Foobar", "72736c0c-addd-11e2-a105-c82a14fffe94", "72736c0c-addd-11e2-a105-c82a14fffe94"]

Any idea what I might be doing wrong?

[seamusabshere]

it should work:

$ sqlite3
SQLite version 3.7.16 2013-03-18 11:39:23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table programs (uid varchar(255), name varchar(255));
sqlite>  PRAGMA table_info("programs");
0|uid|varchar(255)|0||0
1|name|varchar(255)|0||0
sqlite> INSERT OR IGNORE INTO "programs" ("name","uid") VALUES ("Foobar", "72736c0c-addd-11e2-a105-c82a14fffe94");
sqlite> select * from programs;
72736c0c-addd-11e2-a105-c82a14fffe94|Foobar

maybe Program.count is cached (memoized in the process)? maybe open a new console and try Program.count from it? (you could also use sqlite3 db/test.db etc.)

[batter]

Hmm... That doesn't work on my local for some reason. Here is what happens when I run this series of commands:

SQLite version 3.7.16 2013-03-18 11:39:23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA table_info("programs");
0|id|INTEGER|1||1
1|uid|varchar(36)|1||0
2|name|varchar(255)|1||0
3|url|varchar(255)|0||0
4|created_at|datetime|1||0
5|updated_at|datetime|1||0
6|deleted_at|datetime|0||0
sqlite> INSERT OR IGNORE INTO "programs" ("name","uid") VALUES ("Foobar", "72736c0c-addd-11e2-a105-c82a14fffe94");
sqlite> select * from programs;
sqlite>

(nothing gets for the select statement returned). So perhaps its an issue with my local install of SQLite?

Could this have something to do with the fact that the 'ID' field is an auto incrementing ID #?

[seamusabshere]

i bet the problem is a non-autoincrement primary key

0|id|INTEGER|1||1

you should probably either remove that column or (if you really need it) make it auto-increment. if you want to make it auto-increment, you will have to follow the standard sqlite procedure, which is to create a new table with the correct structure and then copy over the data:

http://sqlite.org/faq.html#q11

[batter]

It is auto-incrementing.. or at least it should be. Or does rails handle the auto-increment itself? I assumed that that was the databases responsibility.

[seamusabshere]

what's the output of .schema programs ?

[batter]

sqlite> .schema programs
CREATE TABLE "programs" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "uid" varchar(36) NOT NULL, "name" varchar(255) NOT NULL, "url" varchar(255), "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "deleted_at" datetime);
CREATE INDEX "index_programs_on_uid" ON "programs" ("uid");

[seamusabshere]

ok, check it out:

$ sqlite3
SQLite version 3.7.16 2013-03-18 11:39:23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE "programs" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "uid" varchar(36) NOT NULL, "name" varchar(255) NOT NULL, "url" varchar(255), "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "deleted_at" datetime);
sqlite> CREATE INDEX "index_programs_on_uid" ON "programs" ("uid");
sqlite> .schema programs
CREATE TABLE "programs" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "uid" varchar(36) NOT NULL, "name" varchar(255) NOT NULL, "url" varchar(255), "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "deleted_at" datetime);
CREATE INDEX "index_programs_on_uid" ON "programs" ("uid");

wait for it... (i changed INSERT OR IGNORE to just INSERT so that we could see errors)

sqlite> INSERT INTO "programs" ("name","uid") VALUES ("Foobar", "72736c0c-addd-11e2-a105-c82a14fffe94");
Error: programs.created_at may not be NULL

in a nutshell, upsert is NOT designed to detect created_at columns and auto-fill them like normal Rails record creation does. so, you should do:

Program.upsert({uid: UUIDTools::UUID.timestamp_create.to_s}, name: 'Foobar', created_at: Time.now, updated_at: Time.now)

thanks to @derekharmel's #15, however, it IS smart enough to discard created_at if the record already exists.

please let me know if this solves your issue!

[batter]

That worked, cheers! Guess it was a matter of those timestamped columns being marked as not null, but the database not auto-populating the fields as I was expecting.

[ali-bugdayci]

I am facing the same problem. It updates, but does not create new rows. Seems like "INSERT OR IGNORE INTO" statement does not work:

Versions:

 upsert (2.0.3)
➜  ~  sqlite3 -version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

Rails console logs:

>> upsert = Upsert.new Deneme.connection, Deneme.table_name                
=> #<Upsert:0x007fef39ae06c0 @table_name="denemes", @flavor="Sqlite3", @ada
pter="SQLite3_Database", @connection=#<Upsert::Connection::SQLite3_Database
:0x007fef39b042c8 @controller=#<Upsert:0x007fef39ae06c0 ...>, @metal=#<SQLi
te3::Database:0x007fef37f0f3b0 @tracefunc=nil, @authorizer=nil, @encoding=#
<Encoding:UTF-8>, @busy_handler=nil, @collations={}, @functions={}, @result
s_as_hash=true, @type_translation=nil, @readonly=false>>, @merge_function_c
lass=Upsert::MergeFunction::SQLite3_Database, @assume_function_exists=false
>                                                                          
>> Deneme.all                                                              
  Deneme Load (0.4ms)  SELECT "denemes".* FROM "denemes"                   
=> [#<Deneme id: 1, i: 1, str: "str4", val: "val", created_at: "2014-02-08 22:49:47", updated_at: "2014-02-08 22:49:47">]
>> upsert.row({:i => 1}, {str: "str5"})                                    
=> nil                                                                     
>> Deneme.all                                                              
  Deneme Load (0.4ms)  SELECT "denemes".* FROM "denemes"                   
   [#<Deneme id: 1, i: 1, str: "str5", val: "val", created_at: "2014-02-08 22:49:47", updated_at: "2014-02-08 22:49:47">]                             
>> upsert.row({:i => 2}, {str: "str5"})                                    
=> nil                                                                     
>> Deneme.all                                                              
  Deneme Load (0.4ms)  SELECT "denemes".* FROM "denemes"                   
   [#<Deneme id: 1, i: 1, str: "str5", val: "val", created_at: "2014-02-08 22:49:47", updated_at: "2014-02-08 22:49:47">]

development.log file entries:

[upsert] PRAGMA table_info("denemes")
[upsert] INSERT OR IGNORE INTO "denemes" ("i","str") VALUES (?,?) with [1,"str5"]
[upsert] UPDATE "denemes" SET "i"=?,"str"=? WHERE "i"=? with [1, "str5", 1]
[upsert] INSERT OR IGNORE INTO "denemes" ("i","str") VALUES (?,?) with [2,"str5"]
[upsert] UPDATE "denemes" SET "i"=?,"str"=? WHERE "i"=? with [2, "str5", 2]

[seamusabshere]

any chance this is an in-memory database?

[ali-bugdayci]

It is not. It actually behaves differently form rails 4.0.2 and 3.2.16. I will be creating a different bug for 4.0.2 with its own test.

In rails 3.2.16 (and possibly 3.2.13 which is used by the main creator of this bug):

➜  ~  sqlite3 --version 
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668
➜  upsert_demo2  ruby -v
ruby 2.0.0p247 (2013-06-27 revision 41674) [x86_64-linux]

Lets generate a 3.2.16 application:

rails _3.2.16_ new upsert_demo2
cd upsert_demo2
echo 'gem "upsert"' >> Gemfile
echo 'gem "rb-readline", "0.4.2"' >> Gemfile
bundle
rails g model try i:integer s
rake db:migrate
rails c

Try creating a row, upsert fails silently:

➜  upsert_demo2  rails c
Loading development environment (Rails 3.2.16)
irb(main):001:0> u= Upsert.new Try.connection, Try.table_name 
=> #<Upsert:0x007fd7b016dd18 @table_name="tries", @flavor="Sqlite3", @adapter="SQLite3_Database", @connection=#<Upsert::Connection::SQLite3_Database:0x007fd7b0e414e0 @controller=#<Upsert:0x007fd7b016dd18 ...>, @metal=#<SQLite3::Database:0x007fd7b3219ae8 @tracefunc=nil, @authorizer=nil, @encoding=nil, @busy_handler=nil, @collations={}, @functions={}, @results_as_hash=true, @type_translation=nil, @readonly=false>>, @merge_function_class=Upsert::MergeFunction::SQLite3_Database, @assume_function_exists=false>
irb(main):002:0> u.row({i: 1},{s: "String1"})
=> nil
irb(main):003:0> Try.all
  Try Load (0.3ms)  SELECT "tries".* FROM "tries" 
=> []

development.log:

Connecting to database specified by database.yml
[upsert] PRAGMA table_info("tries")
[upsert] INSERT OR IGNORE INTO "tries" ("i","s") VALUES (?,?) with [1, "String1"]
[upsert] UPDATE "tries" SET "i"=?,"s"=? WHERE "i"=? with [1, "String1", 1]

Gemfile.lock:

GEM
  remote: https://rubygems.org/
  specs:
    actionmailer (3.2.16)
      actionpack (= 3.2.16)
      mail (~> 2.5.4)
    actionpack (3.2.16)
      activemodel (= 3.2.16)
      activesupport (= 3.2.16)
      builder (~> 3.0.0)
      erubis (~> 2.7.0)
      journey (~> 1.0.4)
      rack (~> 1.4.5)
      rack-cache (~> 1.2)
      rack-test (~> 0.6.1)
      sprockets (~> 2.2.1)
    activemodel (3.2.16)
      activesupport (= 3.2.16)
      builder (~> 3.0.0)
    activerecord (3.2.16)
      activemodel (= 3.2.16)
      activesupport (= 3.2.16)
      arel (~> 3.0.2)
      tzinfo (~> 0.3.29)
    activeresource (3.2.16)
      activemodel (= 3.2.16)
      activesupport (= 3.2.16)
    activesupport (3.2.16)
      i18n (~> 0.6, >= 0.6.4)
      multi_json (~> 1.0)
    arel (3.0.3)
    builder (3.0.4)
    coffee-rails (3.2.2)
      coffee-script (>= 2.2.0)
      railties (~> 3.2.0)
    coffee-script (2.2.0)
      coffee-script-source
      execjs
    coffee-script-source (1.7.0)
    erubis (2.7.0)
    execjs (2.0.2)
    hike (1.2.3)
    i18n (0.6.9)
    journey (1.0.4)
    jquery-rails (3.1.0)
      railties (>= 3.0, < 5.0)
      thor (>= 0.14, < 2.0)
    json (1.8.1)
    mail (2.5.4)
      mime-types (~> 1.16)
      treetop (~> 1.4.8)
    mime-types (1.25.1)
    multi_json (1.8.4)
    polyglot (0.3.3)
    rack (1.4.5)
    rack-cache (1.2)
      rack (>= 0.4)
    rack-ssl (1.3.3)
      rack
    rack-test (0.6.2)
      rack (>= 1.0)
    rails (3.2.16)
      actionmailer (= 3.2.16)
      actionpack (= 3.2.16)
      activerecord (= 3.2.16)
      activeresource (= 3.2.16)
      activesupport (= 3.2.16)
      bundler (~> 1.0)
      railties (= 3.2.16)
    railties (3.2.16)
      actionpack (= 3.2.16)
      activesupport (= 3.2.16)
      rack-ssl (~> 1.3.2)
      rake (>= 0.8.7)
      rdoc (~> 3.4)
      thor (>= 0.14.6, < 2.0)
    rake (10.1.1)
    rb-readline (0.4.2)
    rdoc (3.12.2)
      json (~> 1.4)
    sass (3.2.14)
    sass-rails (3.2.6)
      railties (~> 3.2.0)
      sass (>= 3.1.10)
      tilt (~> 1.3)
    sprockets (2.2.2)
      hike (~> 1.2)
      multi_json (~> 1.0)
      rack (~> 1.0)
      tilt (~> 1.1, != 1.3.0)
    sqlite3 (1.3.8)
    thor (0.18.1)
    tilt (1.4.1)
    treetop (1.4.15)
      polyglot
      polyglot (>= 0.3.1)
    tzinfo (0.3.38)
    uglifier (2.4.0)
      execjs (>= 0.3.0)
      json (>= 1.8.0)
    upsert (2.0.3)

PLATFORMS
  ruby

DEPENDENCIES
  coffee-rails (~> 3.2.1)
  jquery-rails
  rails (= 3.2.16)
  rb-readline (= 0.4.2)
  sass-rails (~> 3.2.3)
  sqlite3
  uglifier (>= 1.0.3)
  upsert