JRuby

[activerecord-jdbcsqlite3 adapter] Date/Datetime/Time fields simplification to INTEGER sqlite storage type is not correctly handled

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Blocker Blocker
  • Resolution: Fixed
  • Affects Version/s: JRuby 1.1.2
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None
  • Environment:
    rails 2.1 (but any 2.x.x should behave the same)
    jruby 1.1.2
    Windows XP 32bits/Gentoo Linux 2006
  • Number of attachments :
    5

Description

steps to reproduce :
1) jruby -S rails myapp -d sqlite3
2) change manually sqlite3 to jdbcsqlite3 in config/database.yaml
3) jruby -S rake db:create:all
4) jruby script/generate scaffold note title:string message:text publish_date:date
5) jruby -S rake db:migrate
6) jruby script/server -p 3000

go to the page : http://localhost:3000/notes/new
fill the form, and submit, it gives : 1 error(s) on assignment of multiparameter attributes

The same test with a jdbcmysql adapter is correct, that's why i think jdbcsqlite3 adapter is guilty.

Attachments

  1. File
    development.log-jruby-113
    02/Sep/08 7:29 PM
    1 kB
    Rene Androsch
  2. File
    development.log-jruby-114
    02/Sep/08 7:29 PM
    1 kB
    Rene Androsch
  3. File
    development.log-mri
    02/Sep/08 7:29 PM
    3 kB
    Rene Androsch
  4. File
    jdbc_sqlite3_add_proper_date_time_columns.diff
    21/Aug/08 12:24 AM
    1 kB
    Dylan Stamat

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Charles Oliver Nutter added a comment -

Any possibility you could look at jdbcsqlite3 source and figure out where this is happening? We're a little short-handed on sqlite3 resources right now

Show
Charles Oliver Nutter added a comment - Any possibility you could look at jdbcsqlite3 source and figure out where this is happening? We're a little short-handed on sqlite3 resources right now
Hide
Permalink
Yonsy Solis added a comment -

If i create the project under matz ruby an run under jruby (webrick, mongrel-java, glassfish_rails and jetty_rails tested) work without problem

the problem apparently is in the migration of the bbdd if i do a

jruby -S rake db:migrate i obtain the next schema.rb (tested with rails 2.1)

ActiveRecord::Schema.define(:version => 20080717202218) do
create_table "notes", :force => true do |t|
t.string "title"
t.string "message", :limit => 2000000000
t.integer "publish_date", :limit => 2000000000
t.integer "created_at", :limit => 2000000000
t.integer "updated_at", :limit => 2000000000
end
end

but with ruby -S rake db:migrate (matz version) i obtain the next schema.rb

ActiveRecord::Schema.define(:version => 20080717200735) do
create_table "notes", :force => true do |t|
t.string "title"
t.text "message"
t.date "publish_date"
t.datetime "created_at"
t.datetime "updated_at"
end
end

the ar-jdbcsqlite adapter add the strange :limit to all the fields

if i transfer the database from the application migrated with matz ruby to the apllication generated with jruby this one runs without problems

Show
Yonsy Solis added a comment - If i create the project under matz ruby an run under jruby (webrick, mongrel-java, glassfish_rails and jetty_rails tested) work without problem the problem apparently is in the migration of the bbdd if i do a jruby -S rake db:migrate i obtain the next schema.rb (tested with rails 2.1) ActiveRecord::Schema.define(:version => 20080717202218) do create_table "notes", :force => true do |t| t.string "title" t.string "message", :limit => 2000000000 t.integer "publish_date", :limit => 2000000000 t.integer "created_at", :limit => 2000000000 t.integer "updated_at", :limit => 2000000000 end end but with ruby -S rake db:migrate (matz version) i obtain the next schema.rb ActiveRecord::Schema.define(:version => 20080717200735) do create_table "notes", :force => true do |t| t.string "title" t.text "message" t.date "publish_date" t.datetime "created_at" t.datetime "updated_at" end end the ar-jdbcsqlite adapter add the strange :limit to all the fields if i transfer the database from the application migrated with matz ruby to the apllication generated with jruby this one runs without problems
Hide
Permalink
Yonsy Solis added a comment -

my mistake, the migration with jdbcsqlite adapter migrate the text field like a string field with a :limit => 2000000000 and the date/datetime like integer fields

Show
Yonsy Solis added a comment - my mistake, the migration with jdbcsqlite adapter migrate the text field like a string field with a :limit => 2000000000 and the date/datetime like integer fields
Hide
Permalink
Dylan Stamat added a comment -

see attached for patch.

all the date/time related columns were being set to integer for some reason. re-declared them to the appropriate data types

Show
Dylan Stamat added a comment - see attached for patch. all the date/time related columns were being set to integer for some reason. re-declared them to the appropriate data types
Hide
Permalink
Rene Androsch added a comment -

I've the same/related issue (and I think JRUBY-2438 (closed), JRUBY-2718 (open) are always about the same problem.

Here is the data I collected:

Using sqlite3 as database driver, the MRI implementation has no problem with the included migrations.
However running the same migration on jruby (1.1.3 or 1.1.4), the migration fail.

The output is provided for:
-) MRI "1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0]"
-) "jruby 1.1.3"
-) "jruby 1.1.4"

Using apple's JRE:
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_13-b05-237)
Java HotSpot(TM) Client VM (build 1.5.0_13-119, mixed mode, sharing)

The migration "20080718151643_change_date_to_datetime_on_bills.rb" is the source of the problem.
Also included the development.log output of each run.

Here is the output of the 3 runs:
##########
##

    1. ruby 1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0]
      ##
      ##########
      MacBook:migration_problem art$ rm db/development.sqlite3 ; rake db:create ; rake db:migrate
      (in /Users/art/development/projects/rails/_mine/migration_problem)
      db/development.sqlite3 already exists
      (in /Users/art/development/projects/rails/_mine/migration_problem)
      == 20080701202925 CreateBills: migrating ======================================
    • create_table(:bills)
      -> 0.0033s
      == 20080701202925 CreateBills: migrated (0.0035s) =============================

== 20080718151643 ChangeDateToDatetimeOnBills: migrating ======================
– change_table(:bills)
-> 0.0193s
== 20080718151643 ChangeDateToDatetimeOnBills: migrated (0.0259s) =============

##########
##

    1. jruby 1.1.3 (ruby 1.8.6 patchlevel 114) (2008-07-20 rev 7243) [i386-java]
      ##
      ##########
      MacBook:migration_problem art$ rm db/development.sqlite3 ; jruby -S rake db:create ; jruby -S rake db:migrate
      (in /Users/art/development/projects/rails/_mine/migration_problem)
      (in /Users/art/development/projects/rails/_mine/migration_problem)
      == 20080701202925 CreateBills: migrating ======================================
    • create_table(:bills)
      -> 0.0307s
      -> 0 rows
      == 20080701202925 CreateBills: migrated (0.0317s) =============================

== 20080718151643 ChangeDateToDatetimeOnBills: migrating ======================
– change_table(:bills)
rake aborted!
ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE bills ALTER COLUMN date INTEGER

(See full trace by running task with --trace)

##########
##

    1. jruby 1.1.4 (ruby 1.8.6 patchlevel 114) (2008-08-28 rev 7570) [i386-java]
      ##
      ##########
      MacBook:migration_problem art$ rm db/development.sqlite3 ; jruby -S rake db:create ; jruby -S rake db:migrate
      (in /Users/art/development/projects/rails/_mine/migration_problem)
      (in /Users/art/development/projects/rails/_mine/migration_problem)
      == 20080701202925 CreateBills: migrating ======================================
    • create_table(:bills)
      -> 0.0339s
      -> 0 rows
      == 20080701202925 CreateBills: migrated (0.0347s) =============================

== 20080718151643 ChangeDateToDatetimeOnBills: migrating ======================
– change_table(:bills)
rake aborted!
ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE bills ALTER COLUMN date INTEGER

(See full trace by running task with --trace)

Show
Rene Androsch added a comment - I've the same/related issue (and I think JRUBY-2438 (closed), JRUBY-2718 (open) are always about the same problem. Here is the data I collected: Using sqlite3 as database driver, the MRI implementation has no problem with the included migrations. However running the same migration on jruby (1.1.3 or 1.1.4), the migration fail. The output is provided for: -) MRI "1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0]" -) "jruby 1.1.3" -) "jruby 1.1.4" Using apple's JRE: Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_13-b05-237) Java HotSpot(TM) Client VM (build 1.5.0_13-119, mixed mode, sharing) The migration "20080718151643_change_date_to_datetime_on_bills.rb" is the source of the problem. Also included the development.log output of each run. Here is the output of the 3 runs: ########## ##
    1. ruby 1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0] ## ########## MacBook:migration_problem art$ rm db/development.sqlite3 ; rake db:create ; rake db:migrate (in /Users/art/development/projects/rails/_mine/migration_problem) db/development.sqlite3 already exists (in /Users/art/development/projects/rails/_mine/migration_problem) == 20080701202925 CreateBills: migrating ======================================
    • create_table(:bills) -> 0.0033s == 20080701202925 CreateBills: migrated (0.0035s) =============================
== 20080718151643 ChangeDateToDatetimeOnBills: migrating ====================== – change_table(:bills) -> 0.0193s == 20080718151643 ChangeDateToDatetimeOnBills: migrated (0.0259s) ============= ########## ##
    1. jruby 1.1.3 (ruby 1.8.6 patchlevel 114) (2008-07-20 rev 7243) [i386-java] ## ########## MacBook:migration_problem art$ rm db/development.sqlite3 ; jruby -S rake db:create ; jruby -S rake db:migrate (in /Users/art/development/projects/rails/_mine/migration_problem) (in /Users/art/development/projects/rails/_mine/migration_problem) == 20080701202925 CreateBills: migrating ======================================
    • create_table(:bills) -> 0.0307s -> 0 rows == 20080701202925 CreateBills: migrated (0.0317s) =============================
== 20080718151643 ChangeDateToDatetimeOnBills: migrating ====================== – change_table(:bills) rake aborted! ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE bills ALTER COLUMN date INTEGER (See full trace by running task with --trace) ########## ##
    1. jruby 1.1.4 (ruby 1.8.6 patchlevel 114) (2008-08-28 rev 7570) [i386-java] ## ########## MacBook:migration_problem art$ rm db/development.sqlite3 ; jruby -S rake db:create ; jruby -S rake db:migrate (in /Users/art/development/projects/rails/_mine/migration_problem) (in /Users/art/development/projects/rails/_mine/migration_problem) == 20080701202925 CreateBills: migrating ======================================
    • create_table(:bills) -> 0.0339s -> 0 rows == 20080701202925 CreateBills: migrated (0.0347s) =============================
== 20080718151643 ChangeDateToDatetimeOnBills: migrating ====================== – change_table(:bills) rake aborted! ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE bills ALTER COLUMN date INTEGER (See full trace by running task with --trace)
Hide
Permalink
Rene Androsch added a comment -

developement.log of MRI, JRuby 1.13, and JRuby 1.1.4

Show
Rene Androsch added a comment - developement.log of MRI, JRuby 1.13, and JRuby 1.1.4
Hide
Permalink
Rene Androsch added a comment -

Sample source -> just run the migration:

MRI:
rm db/development* ; rake db:create ; rake db:migrate

JRuby:
rm db/development* ; jruby -S rake db:create ; jruby -S rake db:migrate

Show
Rene Androsch added a comment - Sample source -> just run the migration: MRI: rm db/development* ; rake db:create ; rake db:migrate JRuby: rm db/development* ; jruby -S rake db:create ; jruby -S rake db:migrate
Hide
Permalink
Miroslav Dubovsky added a comment -

I try authlogic plugin , redmine and others app/gems with Jruby , NetBeans 6.5 and SqLite3 database and my result is, that is not possible switch between JRuby and Ruby(C) if I use SqLite3 database. Another result - majority Rails app in Jruby with SqLite3 can't work properly.

I think, ther is some problems, how column type is interpreted in Ruby-Sqlite3 adapter and jdbc_sqlite3 adapter. All Date-Time and boolean is interpreted different , and default values for boolean are not exported by rake db:export: in Jruby

class TestDb < ActiveRecord::Migration
def self.up
create_table :test_column, :force => true do |t|
t.column "is_closed", :boolean, :default => false, :null => false
t.column "is_enabled", :boolean, :default => true, :null => false
t.column "created_on", :timestamp
t.column "modified_at", :datetime, :null => false
t.column "start_on", :date, :null => false
t.column "start_at", :time, :null => false
end
end
....
end

C -Ruby:
CREATE TABLE "test_column" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "is_closed" boolean DEFAULT 'f' NOT NULL, "is_enabled" boolean DEFAULT 't' NOT NULL, "created_on" datetime, "modified_at" datetime NOT NULL, "start_on" date NOT NULL, "start_at" time NOT NULL);

rake db:export:
create_table "test_column", :force => true do |t|
t.boolean "is_closed", :default => false, :null => false
t.boolean "is_enabled", :default => true, :null => false
t.datetime "created_on"
t.datetime "modified_at", :null => false
t.date "start_on", :null => false
t.time "start_at", :null => false
end

Jruby:
CREATE TABLE test_column (id INTEGER PRIMARY KEY AUTOINCREMENT, is_closed INTEGER(1) DEFAULT 0 NOT NULL, is_enabled INTEGER(1) DEFAULT 1 NOT NULL, created_on INTEGER, modified_at INTEGER NOT NULL, start_on INTEGER NOT NULL, start_at INTEGER NOT NULL);

rake db:export:
create_table "test_column", :force => true do |t|
t.integer "is_closed", :limit => 1
t.integer "is_enabled", :limit => 1
t.integer "created_on", :limit => 2000000000
t.integer "modified_at", :limit => 2000000000
t.integer "start_on", :limit => 2000000000
t.integer "start_at", :limit => 2000000000
end

Ther are some code from jdbc_sqlite3.rb, where ther is clarify :

def modify_types(tp)
tp[:primary_key] = "INTEGER PRIMARY KEY AUTOINCREMENT"
tp[:float] = { :name => "REAL" }
tp[:decimal] = { :name => "REAL" }
tp[:datetime] = { :name => "INTEGER" }
tp[:timestamp] = { :name => "INTEGER" }
tp[:time] = { :name => "INTEGER" }
tp[:date] = { :name => "INTEGER" }
tp[:boolean] = { :name => "INTEGER", :limit => 1}
tp
end

I think, there cause too many problems in Rails app and would be better if behavior for both adapters are similar.

Show
Miroslav Dubovsky added a comment - I try authlogic plugin , redmine and others app/gems with Jruby , NetBeans 6.5 and SqLite3 database and my result is, that is not possible switch between JRuby and Ruby(C) if I use SqLite3 database. Another result - majority Rails app in Jruby with SqLite3 can't work properly. I think, ther is some problems, how column type is interpreted in Ruby-Sqlite3 adapter and jdbc_sqlite3 adapter. All Date-Time and boolean is interpreted different , and default values for boolean are not exported by rake db:export: in Jruby class TestDb < ActiveRecord::Migration def self.up create_table :test_column, :force => true do |t| t.column "is_closed", :boolean, :default => false, :null => false t.column "is_enabled", :boolean, :default => true, :null => false t.column "created_on", :timestamp t.column "modified_at", :datetime, :null => false t.column "start_on", :date, :null => false t.column "start_at", :time, :null => false end end .... end C -Ruby: CREATE TABLE "test_column" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "is_closed" boolean DEFAULT 'f' NOT NULL, "is_enabled" boolean DEFAULT 't' NOT NULL, "created_on" datetime, "modified_at" datetime NOT NULL, "start_on" date NOT NULL, "start_at" time NOT NULL); rake db:export: create_table "test_column", :force => true do |t| t.boolean "is_closed", :default => false, :null => false t.boolean "is_enabled", :default => true, :null => false t.datetime "created_on" t.datetime "modified_at", :null => false t.date "start_on", :null => false t.time "start_at", :null => false end Jruby: CREATE TABLE test_column (id INTEGER PRIMARY KEY AUTOINCREMENT, is_closed INTEGER(1) DEFAULT 0 NOT NULL, is_enabled INTEGER(1) DEFAULT 1 NOT NULL, created_on INTEGER, modified_at INTEGER NOT NULL, start_on INTEGER NOT NULL, start_at INTEGER NOT NULL); rake db:export: create_table "test_column", :force => true do |t| t.integer "is_closed", :limit => 1 t.integer "is_enabled", :limit => 1 t.integer "created_on", :limit => 2000000000 t.integer "modified_at", :limit => 2000000000 t.integer "start_on", :limit => 2000000000 t.integer "start_at", :limit => 2000000000 end Ther are some code from jdbc_sqlite3.rb, where ther is clarify : def modify_types(tp) tp[:primary_key] = "INTEGER PRIMARY KEY AUTOINCREMENT" tp[:float] = { :name => "REAL" } tp[:decimal] = { :name => "REAL" } tp[:datetime] = { :name => "INTEGER" } tp[:timestamp] = { :name => "INTEGER" } tp[:time] = { :name => "INTEGER" } tp[:date] = { :name => "INTEGER" } tp[:boolean] = { :name => "INTEGER", :limit => 1} tp end I think, there cause too many problems in Rails app and would be better if behavior for both adapters are similar.
Hide
Permalink
Nate Sutton added a comment -

I followed the steps to reproduce and confirmed that it is still broken with jruby 1.1.5, jdbc-sqlite3-3.5.8, and activerecord-jdbcsqlite3-adapter-0.9.

The schema appears as follows:

ActiveRecord::Schema.define(:version => 20081130234148) do

create_table "notes", :force => true do |t|
t.string "title"
t.string "message", :limit => 2000000000
t.integer "publish_date", :limit => 2000000000
t.integer "created_at", :limit => 2000000000
t.integer "updated_at", :limit => 2000000000
end

end

Show
Nate Sutton added a comment - I followed the steps to reproduce and confirmed that it is still broken with jruby 1.1.5, jdbc-sqlite3-3.5.8, and activerecord-jdbcsqlite3-adapter-0.9. The schema appears as follows: ActiveRecord::Schema.define(:version => 20081130234148) do create_table "notes", :force => true do |t| t.string "title" t.string "message", :limit => 2000000000 t.integer "publish_date", :limit => 2000000000 t.integer "created_at", :limit => 2000000000 t.integer "updated_at", :limit => 2000000000 end end
Hide
Permalink
Ferdinand Svehla added a comment -

Still broken on 1.1.6.

Show
Ferdinand Svehla added a comment - Still broken on 1.1.6.
Hide
Permalink
Thomas E Enebo added a comment -

When I applied JRUBY-2967 I also added the smarts to fix this for sqlite3. So this is what the schema looks like now:

ActiveRecord::Schema.define(:version => 20090116223215) do

  create_table "notes", :force => true do |t|
    t.string   "title"
    t.string   "message",      :limit => nil
    t.date     "publish_date"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

end

So t.string "message" is still not right, but it works and it is not this issue. So I am resolving this for 0.9.1 of AR.

Show
Thomas E Enebo added a comment - When I applied JRUBY-2967 I also added the smarts to fix this for sqlite3. So this is what the schema looks like now: 
ActiveRecord::Schema.define(:version => 20090116223215) do

  create_table "notes", :force => true do |t|
    t.string   "title"
    t.string   "message",      :limit => nil
    t.date     "publish_date"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

end
So t.string "message" is still not right, but it works and it is not this issue. So I am resolving this for 0.9.1 of AR.

People

Vote (2)
Watch (4)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Codehaus. Try JIRA - bug tracking software for your team.