ruby-sequel - Project Hosting on Google Code

Project Home		Issues

Last 7 days Dec 30, 2009 issue 281 (Sequel-3.7.0 fails using a Model.create call and mssql jdbc ...) commented on by pa...@semiosix.com - Your patch works perfectly. Thanks. Your patch works perfectly. Thanks. Dec 30, 2009 issue 281 (Sequel-3.7.0 fails using a Model.create call and mssql jdbc ...) Status changed by jeremyevans0 - Fixed: http://github.com/jeremyevans/sequel/commit/56f6ef336ab46d604d1877c88ecfeca19fe1b65a Status: Fixed Fixed: http://github.com/jeremyevans/sequel/commit/56f6ef336ab46d604d1877c88ecfeca19fe1b65a Status: Fixed Dec 30, 2009 issue 281 (Sequel-3.7.0 fails using a Model.create call and mssql jdbc ...) commented on by jeremyevans0 - Please try the attached patch. It ignores the OUTPUT clause on versions less than 2005+. As a workaround, you could set disable_insert_output on the Model's dataset, which should prevent the error you are seeing. Please try the attached patch. It ignores the OUTPUT clause on versions less than 2005+. As a workaround, you could set disable_insert_output on the Model's dataset, which should prevent the error you are seeing. Dec 30, 2009 issue 281 (Sequel-3.7.0 fails using a Model.create call and mssql jdbc ...) commented on by pa...@semiosix.com - db.server_version => 8000760 The version string (from select @@VERSION) is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) on Windows NT 5.2 (Build 3790: Service Pack 2) Seems the OUTPUT clause is for 2005 and newer. FWIW, select scope_identity() will return the value of the id of the last row inserted. db.server_version => 8000760 The version string (from select @@VERSION) is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) on Windows NT 5.2 (Build 3790: Service Pack 2) Seems the OUTPUT clause is for 2005 and newer. FWIW, select scope_identity() will return the value of the id of the last row inserted. Dec 30, 2009 issue 281 (Sequel-3.7.0 fails using a Model.create call and mssql jdbc ...) changed by jeremyevans0 - It's possible the OUTPUT clause isn't supported by the version of SQL Server you are using. Could you please let me know what version you are using, both the product name (e.g. SQL Server 2008) and the output of db.server_version (e.g. 10001600). Currently, I only test Sequel with SQL Server 2008, so it's certainly possible that older versions (especially 2000 and older) aren't currently supported. Status: Accepted Owner: jeremyevans0 It's possible the OUTPUT clause isn't supported by the version of SQL Server you are using. Could you please let me know what version you are using, both the product name (e.g. SQL Server 2008) and the output of db.server_version (e.g. 10001600). Currently, I only test Sequel with SQL Server 2008, so it's certainly possible that older versions (especially 2000 and older) aren't currently supported. Status: Accepted Owner: jeremyevans0 Dec 30, 2009 issue 281 (Sequel-3.7.0 fails using a Model.create call and mssql jdbc ...) reported by pa...@semiosix.com - #!/usr/bin/jruby require 'sequel' require 'logger' db = Sequel.jdbc 'jdbc:sqlserver://server:1422;databaseName=test' db.loggers << Logger.new( $stdout ) db.create_table! :failures do primary_key :id String :source end class Failure < Sequel::Model end begin Failure.create :source => 'error file' ensure db.drop_table :failures end I, [2009-12-30T12:25:09.624000 #27705] INFO -- : DROP TABLE [FAILURES] I, [2009-12-30T12:25:09.841000 #27705] INFO -- : CREATE TABLE [FAILURES] ([ID] integer PRIMARY KEY IDENTITY(1,1), [SOURCE] varchar(255)) I, [2009-12-30T12:25:10.085000 #27705] INFO -- : BEGIN TRANSACTION I, [2009-12-30T12:25:10.100000 #27705] INFO -- : INSERT INTO [FAILURES] ([SOURCE]) OUTPUT [INSERTED].* VALUES (N'error file') I, [2009-12-30T12:25:10.237000 #27705] INFO -- : IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION I, [2009-12-30T12:25:10.254000 #27705] INFO -- : DROP TABLE [FAILURES] com.microsoft.sqlserver.jdbc.SQLServerException:-1:in `makeFromDatabaseError': NativeException: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near 'OUTPUT'. (Sequel::DatabaseError) from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `getNextResult' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `doExecuteStatement' from com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd:-1:in `doExecute' from com.microsoft.sqlserver.jdbc.TDSCommand:-1:in `execute' from com.microsoft.sqlserver.jdbc.SQLServerConnection:-1:in `executeCommand' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `executeCommand' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `executeStatement' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `executeQuery' ... 251 levels... from /usr/share/jruby/lib/ruby/gems/1.8/gems/sequel-3.7.0/lib/sequel/model/base.rb:729:in `save' from /usr/share/jruby/lib/ruby/gems/1.8/gems/sequel-3.7.0/lib/sequel/model/base.rb:103:in `create' from bin/mssql-fail:18 #!/usr/bin/jruby require 'sequel' require 'logger' db = Sequel.jdbc 'jdbc:sqlserver://server:1422;databaseName=test' db.loggers << Logger.new( $stdout ) db.create_table! :failures do primary_key :id String :source end class Failure < Sequel::Model end begin Failure.create :source => 'error file' ensure db.drop_table :failures end I, [2009-12-30T12:25:09.624000 #27705] INFO -- : DROP TABLE [FAILURES] I, [2009-12-30T12:25:09.841000 #27705] INFO -- : CREATE TABLE [FAILURES] ([ID] integer PRIMARY KEY IDENTITY(1,1), [SOURCE] varchar(255)) I, [2009-12-30T12:25:10.085000 #27705] INFO -- : BEGIN TRANSACTION I, [2009-12-30T12:25:10.100000 #27705] INFO -- : INSERT INTO [FAILURES] ([SOURCE]) OUTPUT [INSERTED].* VALUES (N'error file') I, [2009-12-30T12:25:10.237000 #27705] INFO -- : IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION I, [2009-12-30T12:25:10.254000 #27705] INFO -- : DROP TABLE [FAILURES] com.microsoft.sqlserver.jdbc.SQLServerException:-1:in `makeFromDatabaseError': NativeException: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near 'OUTPUT'. (Sequel::DatabaseError) from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `getNextResult' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `doExecuteStatement' from com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd:-1:in `doExecute' from com.microsoft.sqlserver.jdbc.TDSCommand:-1:in `execute' from com.microsoft.sqlserver.jdbc.SQLServerConnection:-1:in `executeCommand' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `executeCommand' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `executeStatement' from com.microsoft.sqlserver.jdbc.SQLServerStatement:-1:in `executeQuery' ... 251 levels... from /usr/share/jruby/lib/ruby/gems/1.8/gems/sequel-3.7.0/lib/sequel/model/base.rb:729:in `save' from /usr/share/jruby/lib/ruby/gems/1.8/gems/sequel-3.7.0/lib/sequel/model/base.rb:103:in `create' from bin/mssql-fail:18 Older Nov 30, 2009 issue 280 (Oracle seq key bug after insert) Status changed by jeremyevans0 - Slightly different patch committed: http://github.com/jeremyevans/sequel/commit/c2198d0d296285caca2730cea990484c1a8436b2 This includes your change for the .to_i, but moves the code to the shared Oracle adapter, so it should work on Oracle accessed via JDBC. Please test and let me know if it doesn't work. The picking the sequence value before insert is one solution, but it doesn't change the fact that the sequence still has to be specified manually. You cannot assume the sequence exists for all tables/models, because it isn't required. I realize that setting the sequence in every model is a little more work, but creating a plugin that handles it automatically shouldn't be difficult. The shared PostgreSQL adapter has code that automatically detects related sequences, but the shared Oracle adapter does not. I'll accept a patch for it, but as it's probably a lot of work that requires access to an Oracle database, it's not something I plan to work on myself. Status: Fixed Slightly different patch committed: http://github.com/jeremyevans/sequel/commit/c2198d0d296285caca2730cea990484c1a8436b2 This includes your change for the .to_i, but moves the code to the shared Oracle adapter, so it should work on Oracle accessed via JDBC. Please test and let me know if it doesn't work. The picking the sequence value before insert is one solution, but it doesn't change the fact that the sequence still has to be specified manually. You cannot assume the sequence exists for all tables/models, because it isn't required. I realize that setting the sequence in every model is a little more work, but creating a plugin that handles it automatically shouldn't be difficult. The shared PostgreSQL adapter has code that automatically detects related sequences, but the shared Oracle adapter does not. I'll accept a patch for it, but as it's probably a lot of work that requires access to an Oracle database, it's not something I plan to work on myself. Status: Fixed Nov 30, 2009 issue 280 (Oracle seq key bug after insert) commented on by RobertCMircea - The patch works, I've made a small modification to the code in order to convert the returned seq value to integer. All Oracle sequences are integer values. with_sql("SELECT #{literal(s)}.currval FROM dual").single_value.to_i Although it solves the immediate problem, I believe that for Oracle adapter it is quite cumbersome to provide sequence name for each class. It should be something inferred automatically, for all classes in the model, since the sequence is the only "native" way to autoincrement a column. I used for many years Hibernate and, for Oracle dialect, they were selecting first the next sequence value from db and they were assigning it in the ORM to the PK column during the insert. This eliminated the need for triggers for every table in the database and I should say it is quite and elegant solution. The patch works, I've made a small modification to the code in order to convert the returned seq value to integer. All Oracle sequences are integer values. with_sql("SELECT #{literal(s)}.currval FROM dual").single_value.to_i Although it solves the immediate problem, I believe that for Oracle adapter it is quite cumbersome to provide sequence name for each class. It should be something inferred automatically, for all classes in the model, since the sequence is the only "native" way to autoincrement a column. I used for many years Hibernate and, for Oracle dialect, they were selecting first the next sequence value from db and they were assigning it in the ORM to the PK column during the insert. This eliminated the need for triggers for every table in the database and I should say it is quite and elegant solution. Nov 29, 2009 issue 280 (Oracle seq key bug after insert) commented on by jeremyevans0 - Try the attached patch (completely untested) with the following code: Subscription.dataset = Subscription.dataset.sequence(:seq_subscriptions_id) # or whatever the table's sequence's name is Try the attached patch (completely untested) with the following code: Subscription.dataset = Subscription.dataset.sequence(:seq_subscriptions_id) # or whatever the table's sequence's name is Nov 29, 2009 issue 280 (Oracle seq key bug after insert) changed by jeremyevans0 - The Oracle adapter doesn't currently support Dataset#insert returning the autogenerated primary key value, which is necessary for Model.create to work correctly. So this is currently unsupported. I'll look at Oracle's documentation when I get a chance, but I don't have access to an Oracle database, so at the very least I'll need an Oracle user to test patches. Status: Accepted Owner: jeremyevans0 The Oracle adapter doesn't currently support Dataset#insert returning the autogenerated primary key value, which is necessary for Model.create to work correctly. So this is currently unsupported. I'll look at Oracle's documentation when I get a chance, but I don't have access to an Oracle database, so at the very least I'll need an Oracle user to test patches. Status: Accepted Owner: jeremyevans0 Nov 29, 2009 issue 280 (Oracle seq key bug after insert) reported by RobertCMircea - In Oracle database, primary key is not correctly set after inserting records. I have the following code: DB.transaction { offer_type.each do \|ot\| Subscription.create(:created_at => Time.now, :offer_type => ot, :account => account) end } which produces the following SQL: Transaction.begin INSERT INTO SUBSCRIPTIONS (OFFER_TYPE, CREATED_AT, ACCOUNT_ID) VALUES ('ea', TIMESTAMP '2009-11-29 18:29:03.502000 +02:00', 1) SELECT * FROM (SELECT * FROM SUBSCRIPTIONS WHERE (ID = 1)) WHERE ROWNUM <= 1 INSERT INTO SUBSCRIPTIONS (OFFER_TYPE, CREATED_AT, ACCOUNT_ID) VALUES ('blackberry', TIMESTAMP '2009-11-29 18:29:03.526000 +02:00', 1) SELECT * FROM (SELECT * FROM SUBSCRIPTIONS WHERE (ID = 1)) WHERE ROWNUM <= 1 Transaction.commit ID is a auto-incremented correctly in the database by table trigger created using Sequel::Migration. As a side note, there is one-to-many relation between Account and Subscription classes. There is a problem with the SELECTs because they both select record with ID=1 when they actually should be first: ID=1 and second ID=2. How does Sequel picks up the auto-incremented ID after INSERT? In Oracle database, primary key is not correctly set after inserting records. I have the following code: DB.transaction { offer_type.each do \|ot\| Subscription.create(:created_at => Time.now, :offer_type => ot, :account => account) end } which produces the following SQL: Transaction.begin INSERT INTO SUBSCRIPTIONS (OFFER_TYPE, CREATED_AT, ACCOUNT_ID) VALUES ('ea', TIMESTAMP '2009-11-29 18:29:03.502000 +02:00', 1) SELECT * FROM (SELECT * FROM SUBSCRIPTIONS WHERE (ID = 1)) WHERE ROWNUM <= 1 INSERT INTO SUBSCRIPTIONS (OFFER_TYPE, CREATED_AT, ACCOUNT_ID) VALUES ('blackberry', TIMESTAMP '2009-11-29 18:29:03.526000 +02:00', 1) SELECT * FROM (SELECT * FROM SUBSCRIPTIONS WHERE (ID = 1)) WHERE ROWNUM <= 1 Transaction.commit ID is a auto-incremented correctly in the database by table trigger created using Sequel::Migration. As a side note, there is one-to-many relation between Account and Subscription classes. There is a problem with the SELECTs because they both select record with ID=1 when they actually should be first: ID=1 and second ID=2. How does Sequel picks up the auto-incremented ID after INSERT? Nov 18, 2009 issue 279 (Use of a '0000-00-00 00:00:00' DateTime) changed by jeremyevans0 - This is expected behavior. Taps should be doing the following if MySQL is being used: Sequel::MySQL.convert_invalid_date_time = true So you should probably raise this issue on the Taps bug tracker. Status: WontFix Owner: jeremyevans0 This is expected behavior. Taps should be doing the following if MySQL is being used: Sequel::MySQL.convert_invalid_date_time = true So you should probably raise this issue on the Taps bug tracker. Status: WontFix Owner: jeremyevans0 Nov 18, 2009 issue 279 (Use of a '0000-00-00 00:00:00' DateTime) reported by flornet - I don't directly use Sequel but the problem was encountered while using by Taps. The problem is related with that function (from file /sequel-3.6.0/lib/sequel/core.rb) : # Converts the given string into a Time or DateTime object, depending on the # value of Sequel.datetime_class. def self.string_to_datetime(s) begin if datetime_class == DateTime DateTime.parse(s, convert_two_digit_years) else datetime_class.parse(s) end rescue => e raise convert_exception_class(e, InvalidValue) end end Where "datetime_class.parse(s)" won't work with s = '0000-00-00 00:00:00' To bypass that problem, I updated my records so that there are no '0000-00-00 00:00:00' datetime values in my database (MySQL). Best regards, Florent Jaouali (flornet@gmail.com) I don't directly use Sequel but the problem was encountered while using by Taps. The problem is related with that function (from file /sequel-3.6.0/lib/sequel/core.rb) : # Converts the given string into a Time or DateTime object, depending on the # value of Sequel.datetime_class. def self.string_to_datetime(s) begin if datetime_class == DateTime DateTime.parse(s, convert_two_digit_years) else datetime_class.parse(s) end rescue => e raise convert_exception_class(e, InvalidValue) end end Where "datetime_class.parse(s)" won't work with s = '0000-00-00 00:00:00' To bypass that problem, I updated my records so that there are no '0000-00-00 00:00:00' datetime values in my database (MySQL). Best regards, Florent Jaouali (flornet@gmail.com) Oct 29, 2009 issue 278 (ADO schema_parse_table missing table alias) changed by jeremyevans0 - Thanks for the report, fixed: http://github.com/jeremyevans/sequel/commit/2a5bc3eeceff421860367056674c86708587c0a8 Status: Fixed Owner: jeremyevans0 Thanks for the report, fixed: http://github.com/jeremyevans/sequel/commit/2a5bc3eeceff421860367056674c86708587c0a8 Status: Fixed Owner: jeremyevans0 Oct 29, 2009 issue 278 (ADO schema_parse_table missing table alias) reported by rohit.namjoshi - If a non nil schema name is specified, schema_parse_table filters the dataset with :table_schema=>schema. However, that column is ambiguous. It should filter with :c__table_schema=>schema If a non nil schema name is specified, schema_parse_table filters the dataset with :table_schema=>schema. However, that column is ambiguous. It should filter with :c__table_schema=>schema Oct 06, 2009 issue 277 (Sequel::Model::ClassMethods#inherited ignores parent's #inhe...) Status changed by jeremyevans0 - Fixed: http://github.com/jeremyevans/sequel/commit/b10244f81d637e40cf472028d5e2ee5548cb6544 Status: Fixed Fixed: http://github.com/jeremyevans/sequel/commit/b10244f81d637e40cf472028d5e2ee5548cb6544 Status: Fixed Oct 06, 2009 issue 277 (Sequel::Model::ClassMethods#inherited ignores parent's #inhe...) changed by jeremyevans0 - Makes sense to me. I'll commit this later today. Status: Accepted Owner: jeremyevans0 Makes sense to me. I'll commit this later today. Status: Accepted Owner: jeremyevans0 Oct 06, 2009 issue 277 (Sequel::Model::ClassMethods#inherited ignores parent's #inhe...) reported by antage - Sequel 3.5.0, ActiveSupport 2.3.4 Due to Sequel::Model.inherited doesn't invoke "super"-method, ActiveSupport's inheritable attributes doesn't work with Sequel::Model. Look at following script to reproduce the problem: irb(main):001:0> require 'rubygems' => false irb(main):002:0> require 'activesupport' => true irb(main):003:0> require 'sequel' => [] irb(main):004:0> Sequel.connect "sqlite:/" => #<Sequel::SQLite::Database: "sqlite:/"> irb(main):005:0> class A < Sequel::Model; end => nil irb(main):006:0> A.write_inheritable_attribute(:test1, 1) => 1 irb(main):007:0> A.inheritable_attributes => {:test1=>1} irb(main):008:0> class B < A; end => nil irb(main):009:0> B.inheritable_attributes => {} irb(main):010:0> class AA; end => nil irb(main):011:0> AA.write_inheritable_attribute(:test2, 2) => 2 irb(main):012:0> AA.inheritable_attributes => {:test2=>2} irb(main):013:0> class BB < AA; end => nil irb(main):014:0> BB.inheritable_attributes => {:test2=>2} Patch: diff --git a/lib/sequel/model/base.rb b/lib/sequel/model/base.rb index 783056a..5638dd5 100644 --- a/lib/sequel/model/base.rb +++ b/lib/sequel/model/base.rb @@ -173,6 +173,7 @@ module Sequel # is created. Also, make sure the inherited class instance variables # are copied into the subclass. def inherited(subclass) + super ivs = subclass.instance_variables.collect{\|x\| x.to_s} EMPTY_INSTANCE_VARIABLES.each{\|iv\| subclass.instance_variable_set(iv, nil) unless ivs.include?(iv.to_s)} INHERITED_INSTANCE_VARIABLES.each do \|iv, dup\| Sequel 3.5.0, ActiveSupport 2.3.4 Due to Sequel::Model.inherited doesn't invoke "super"-method, ActiveSupport's inheritable attributes doesn't work with Sequel::Model. Look at following script to reproduce the problem: irb(main):001:0> require 'rubygems' => false irb(main):002:0> require 'activesupport' => true irb(main):003:0> require 'sequel' => [] irb(main):004:0> Sequel.connect "sqlite:/" => #<Sequel::SQLite::Database: "sqlite:/"> irb(main):005:0> class A < Sequel::Model; end => nil irb(main):006:0> A.write_inheritable_attribute(:test1, 1) => 1 irb(main):007:0> A.inheritable_attributes => {:test1=>1} irb(main):008:0> class B < A; end => nil irb(main):009:0> B.inheritable_attributes => {} irb(main):010:0> class AA; end => nil irb(main):011:0> AA.write_inheritable_attribute(:test2, 2) => 2 irb(main):012:0> AA.inheritable_attributes => {:test2=>2} irb(main):013:0> class BB < AA; end => nil irb(main):014:0> BB.inheritable_attributes => {:test2=>2} Patch: diff --git a/lib/sequel/model/base.rb b/lib/sequel/model/base.rb index 783056a..5638dd5 100644 --- a/lib/sequel/model/base.rb +++ b/lib/sequel/model/base.rb @@ -173,6 +173,7 @@ module Sequel # is created. Also, make sure the inherited class instance variables # are copied into the subclass. def inherited(subclass) + super ivs = subclass.instance_variables.collect{\|x\| x.to_s} EMPTY_INSTANCE_VARIABLES.each{\|iv\| subclass.instance_variable_set(iv, nil) unless ivs.include?(iv.to_s)} INHERITED_INSTANCE_VARIABLES.each do \|iv, dup\| Sep 04, 2009 issue 276 (Annoying result using "<<") commented on by i...@aliax.net - Ops, sorry, my fault. And yes, I'm using prepared staments right now :) Thanks and sorry for the wrong report (I was getting crazy). Ops, sorry, my fault. And yes, I'm using prepared staments right now :) Thanks and sorry for the wrong report (I was getting crazy). Sep 04, 2009 issue 276 (Annoying result using "<<") changed by jeremyevans0 - Database#<< is used to execute code that doesn't return results. If you want results returned, use Database['SQL'].all. Note that Sequel supports prepared statements, so what you want to do is possible without dropping down to raw SQL. Status: WontFix Owner: jeremyevans0 Database#<< is used to execute code that doesn't return results. If you want results returned, use Database['SQL'].all. Note that Sequel supports prepared statements, so what you want to do is possible without dropping down to raw SQL. Status: WontFix Owner: jeremyevans0 Sep 04, 2009 issue 276 (Annoying result using "<<") reported by i...@aliax.net - I'm experimenting an annoying issue when using raw SQL to generate prepared staments (for MySQL). First of all, I do it manually using "mysql" commandline interface: sql> PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"; Query OK, 0 rows affected (0.00 sec) sql> SET @arg = "alice@domain.org/2"; Query OK, 0 rows affected (0.00 sec) sql >EXECUTE get_doc USING @arg; __DOCUMENT IS CORRECTLY RETRIEVED HERE __ 1 row in set (0.00 sec) By inspecting the mysql.log I see how the queries arrive to MySQL server: ------------------------- 9114 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9114 Prepare [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9114 Query SET @arg = "alice@domain.org/2" 9114 Query EXECUTE get_doc USING @arg 9114 Execute [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 ------------------------- Now I try to do the SAME using raw SQL in Sequel: ---------------------- DB << %{PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"} DB << %{SET @arg = "alice@domain.org/2"} res = DB << %{EXECUTE get_doc USING @arg} puts res.inspect ---------------------- It's annoying but I get no result and 'res' is nil. But more annoying is the fact the the SQL arriving to the server is exactly the same as above: --------------------- 9115 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9115 Prepare [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9115 Query SET @arg = "alice@domain.org/2" 9115 Query EXECUTE get_doc USING @arg 9115 Execute [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 --------------------- Perhaps I miss something? I've just realized that if by error I do a wrong query (note @arggg instead of @arg): SET @arg = "alice@domain.org/2" EXECUTE get_doc USING @arggg then the behavior is identical. This is, MySQL doesn't raise an error if the argument name hasn't been set before. However, it doesn't explain my problem since the mysql.log shows the same (letter by letter) for both cases (mysql command line and Sequel raw SAL). I'm experimenting an annoying issue when using raw SQL to generate prepared staments (for MySQL). First of all, I do it manually using "mysql" commandline interface: sql> PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"; Query OK, 0 rows affected (0.00 sec) sql> SET @arg = "alice@domain.org/2"; Query OK, 0 rows affected (0.00 sec) sql >EXECUTE get_doc USING @arg; __DOCUMENT IS CORRECTLY RETRIEVED HERE __ 1 row in set (0.00 sec) By inspecting the mysql.log I see how the queries arrive to MySQL server: ------------------------- 9114 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9114 Prepare [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9114 Query SET @arg = "alice@domain.org/2" 9114 Query EXECUTE get_doc USING @arg 9114 Execute [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 ------------------------- Now I try to do the SAME using raw SQL in Sequel: ---------------------- DB << %{PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"} DB << %{SET @arg = "alice@domain.org/2"} res = DB << %{EXECUTE get_doc USING @arg} puts res.inspect ---------------------- It's annoying but I get no result and 'res' is nil. But more annoying is the fact the the SQL arriving to the server is exactly the same as above: --------------------- 9115 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9115 Prepare [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9115 Query SET @arg = "alice@domain.org/2" 9115 Query EXECUTE get_doc USING @arg 9115 Execute [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 --------------------- Perhaps I miss something? I've just realized that if by error I do a wrong query (note @arggg instead of @arg): SET @arg = "alice@domain.org/2" EXECUTE get_doc USING @arggg then the behavior is identical. This is, MySQL doesn't raise an error if the argument name hasn't been set before. However, it doesn't explain my problem since the mysql.log shows the same (letter by letter) for both cases (mysql command line and Sequel raw SAL). Aug 13, 2009 issue 275 (Missing foreign keys using extension Sequel::Database#dump_s...) changed by jeremyevans0 - That's the expected behavior. The schema parser doesn't parse foreign key constraints. It parses out primary keys, columns, and some indexes. That's pretty much what it is limited to. Status: WontFix Owner: jeremyevans0 That's the expected behavior. The schema parser doesn't parse foreign key constraints. It parses out primary keys, columns, and some indexes. That's pretty much what it is limited to. Status: WontFix Owner: jeremyevans0 Aug 13, 2009 issue 275 (Missing foreign keys using extension Sequel::Database#dump_s...) reported by giovanni.cristelli - What steps will reproduce the problem? 1. Use an existent DB with foreign keys defined 2. Execute the following commands from irb or jirb: require 'sequel' require 'sequel/extensions/schema_dumper' DB=Sequel.connect('jdbc:postgresql://localhost/.....') puts DB.dump_schema_migration What is the expected output? What do you see instead? The table definition does not contain any command to define the foreign keys, the foreign key columns are defined like a simple column with an additional index like: ... Integer :id_centrale, :null=>false index [:id_centrale], :name=>:FK_stabile_1 .. What version of the product are you using? On what operating system? sequel 3.3.0, jruby 1.3.1 What steps will reproduce the problem? 1. Use an existent DB with foreign keys defined 2. Execute the following commands from irb or jirb: require 'sequel' require 'sequel/extensions/schema_dumper' DB=Sequel.connect('jdbc:postgresql://localhost/.....') puts DB.dump_schema_migration What is the expected output? What do you see instead? The table definition does not contain any command to define the foreign keys, the foreign key columns are defined like a simple column with an additional index like: ... Integer :id_centrale, :null=>false index [:id_centrale], :name=>:FK_stabile_1 .. What version of the product are you using? On what operating system? sequel 3.3.0, jruby 1.3.1 Jul 13, 2009 issue 274 (Assocations dont't searches constants in same scope they are...) Status changed by jeremyevans0 - Fixed: http://github.com/jeremyevans/sequel/commit/0ebe58ed686bd44273409b6c429c2d8c56f0f352 http://github.com/jeremyevans/sequel/commit/a0d069d0791608c5e68811c99a6595824b3c9a07 Basically the same as the original patch, but with the second patch to support anonymous classes on ruby 1.9. Status: Fixed Fixed: http://github.com/jeremyevans/sequel/commit/0ebe58ed686bd44273409b6c429c2d8c56f0f352 http://github.com/jeremyevans/sequel/commit/a0d069d0791608c5e68811c99a6595824b3c9a07 Basically the same as the original patch, but with the second patch to support anonymous classes on ruby 1.9. Status: Fixed Jul 11, 2009 issue 274 (Assocations dont't searches constants in same scope they are...) commented on by jeremyevans0 - Here's a preliminary patch: http://pastie.org/542498 I haven't run it through my full test suite yet, but after I do I'll be committing it. Here's a preliminary patch: http://pastie.org/542498 I haven't run it through my full test suite yet, but after I do I'll be committing it. Jul 11, 2009 issue 274 (Assocations dont't searches constants in same scope they are...) changed by jeremyevans0 - This isn't a bug, this is how Sequel was designed. If you are using models inside modules, all associations need to include a :class or :class_name option specifying the full class name (including modules). That being said, while this is a slight breakage of backwards compatibility, I think it makes sense. I'll use your patch as a starting point. Status: Accepted Owner: jeremyevans0 This isn't a bug, this is how Sequel was designed. If you are using models inside modules, all associations need to include a :class or :class_name option specifying the full class name (including modules). That being said, while this is a slight breakage of backwards compatibility, I think it makes sense. I'll use your patch as a starting point. Status: Accepted Owner: jeremyevans0 Jul 11, 2009 issue 274 (Assocations dont't searches constants in same scope they are...) reported by nougad - What steps will reproduce the problem? See minimal example in patchfile What is the expected output? What do you see instead? See error in patchfile What version of the product are you using? On what operating system? sequel - Trunk debian - lenny Please provide any additional information below. Sorry for short description but I think the patch should explain everything. What steps will reproduce the problem? See minimal example in patchfile What is the expected output? What do you see instead? See error in patchfile What version of the product are you using? On what operating system? sequel - Trunk debian - lenny Please provide any additional information below. Sorry for short description but I think the patch should explain everything. Jul 06, 2009 issue 273 (sqlite and mysql wants to set default value to NULL on renam...) Status changed by jeremyevans0 - This should now be fixed in the master branch. Thanks for your report! Status: Fixed This should now be fixed in the master branch. Thanks for your report! Status: Fixed Jul 05, 2009 issue 273 (sqlite and mysql wants to set default value to NULL on renam...) changed by jeremyevans0 - This looks like a problem that should be fixed. I'll use your patches as a starting point and I should be able to commit a real fix soon. Status: Accepted Owner: jeremyevans0 This looks like a problem that should be fixed. I'll use your patches as a starting point and I should be able to commit a real fix soon. Status: Accepted Owner: jeremyevans0 Jul 05, 2009 issue 273 (sqlite and mysql wants to set default value to NULL on renam...) reported by nougad - What steps will reproduce the problem? only testet at mysql 5 and sqlite3 1. Create a column without a default value and set :null => false (e.g. "String :priority, :null=>false, :size=>10") 2. Rename in next migration the column (e.g. rename_column :priority, :severity) 3. Migrate all files => Exception at second migration because default value is NULL but NOT NULL is set. What is the expected output? What do you see instead? Don't set a default value when rename a column. What version of the product are you using? On what operating system? sequel-trunk mysql 5.0.51a-24+lenny1 libsqlite3-ruby - 1.2.2-1 Please provide any additional information below. schema(table).find{\|x\| x.first == :priority}.last[:default] is nil => new default will be NULL. But there is a difference between DEFAULT NULL and no default. Yesterday I wrote a patch for mysql but today I have the same problem with sqlite3. I attach the mysql patch anyway perhaps it could help. I testet to set a default on remane (e.g. rename_column :priority, :severity, :default => "") but mysql-adapter-code overwrite new default value with old (see first patch). What steps will reproduce the problem? only testet at mysql 5 and sqlite3 1. Create a column without a default value and set :null => false (e.g. "String :priority, :null=>false, :size=>10") 2. Rename in next migration the column (e.g. rename_column :priority, :severity) 3. Migrate all files => Exception at second migration because default value is NULL but NOT NULL is set. What is the expected output? What do you see instead? Don't set a default value when rename a column. What version of the product are you using? On what operating system? sequel-trunk mysql 5.0.51a-24+lenny1 libsqlite3-ruby - 1.2.2-1 Please provide any additional information below. schema(table).find{\|x\| x.first == :priority}.last[:default] is nil => new default will be NULL. But there is a difference between DEFAULT NULL and no default. Yesterday I wrote a patch for mysql but today I have the same problem with sqlite3. I attach the mysql patch anyway perhaps it could help. I testet to set a default on remane (e.g. rename_column :priority, :severity, :default => "") but mysql-adapter-code overwrite new default value with old (see first patch). Jun 19, 2009 issue 250 (Sequel always use 3306 port for MySQL) commented on by tommy.midttveit - I had the same problem. Specifying :socket => nil solved it. I had the same problem. Specifying :socket => nil solved it. Jun 08, 2009 issue 272 (Bad loading of database from yaml) Status changed by jeremyevans0 - Fixed: http://github.com/jeremyevans/sequel/commit/4912a7fbbb327cdf3bf5284aa4e4933b0affca4f Status: Fixed Fixed: http://github.com/jeremyevans/sequel/commit/4912a7fbbb327cdf3bf5284aa4e4933b0affca4f Status: Fixed Jun 08, 2009 issue 271 (Graphing on "from_self" sends ruby objects into sql) Status changed by jeremyevans0 - Fixed: http://github.com/jeremyevans/sequel/commit/63efa5dbd028a8369ef22a2798d141586c3711a0 Status: Fixed Fixed: http://github.com/jeremyevans/sequel/commit/63efa5dbd028a8369ef22a2798d141586c3711a0 Status: Fixed Jun 06, 2009 issue 271 (Graphing on "from_self" sends ruby objects into sql) commented on by jeremyevans0 - I've fixed this in my personal repository, I should be pushing it to the master branch on Monday. It took a significant amount of internal restructuring to fix it properly. :) I've fixed this in my personal repository, I should be pushing it to the master branch on Monday. It took a significant amount of internal restructuring to fix it properly. :) Jun 05, 2009 issue 272 (Bad loading of database from yaml) commented on by jeremyevans0 - I've fixed this in my local branch. I need to run it through my full test suite, but the change should go in on Monday. I'll update this ticket after it is available in the master branch. I've fixed this in my local branch. I need to run it through my full test suite, but the change should go in on Monday. I'll update this ticket after it is available in the master branch. Jun 05, 2009 issue 272 (Bad loading of database from yaml) changed by jeremyevans0 - I'm not sure if this is worth fixing. An exception is raised, even if it isn't a Sequel::Error subclass. There are plenty of ways you can get Sequel to raise exceptions that aren't Sequel::Errors. Still, looking at the code, it should definitely be checking if conn_string.is_a?(Hash), or using a case statement, with a else case giving an error if conn_string isn't a String or Hash. Status: Accepted Owner: jeremyevans0 I'm not sure if this is worth fixing. An exception is raised, even if it isn't a Sequel::Error subclass. There are plenty of ways you can get Sequel to raise exceptions that aren't Sequel::Errors. Still, looking at the code, it should definitely be checking if conn_string.is_a?(Hash), or using a case statement, with a else case giving an error if conn_string isn't a String or Hash. Status: Accepted Owner: jeremyevans0 Jun 05, 2009 issue 271 (Graphing on "from_self" sends ruby objects into sql) Status changed by jeremyevans0 - Oops, accidentally picked Fixed instead of Accepted. Status: Accepted Oops, accidentally picked Fixed instead of Accepted. Status: Accepted Jun 05, 2009 issue 271 (Graphing on "from_self" sends ruby objects into sql) changed by jeremyevans0 - This is definitely a bug, it should try to use an alias, I'll try to fix it soon. Status: Fixed Owner: jeremyevans0 This is definitely a bug, it should try to use an alias, I'll try to fix it soon. Status: Fixed Owner: jeremyevans0 Jun 05, 2009 issue 272 (Bad loading of database from yaml) commented on by evgeny.zislis - Actually this has nothing to do with the yaml file - I was parsing the yaml myself, and the problem was that I was passing an empty string to Sequel.connect - and it could not handle it saying can't do nil.merge. Actually this has nothing to do with the yaml file - I was parsing the yaml myself, and the problem was that I was passing an empty string to Sequel.connect - and it could not handle it saying can't do nil.merge. Jun 05, 2009 issue 272 (Bad loading of database from yaml) reported by evgeny.zislis - When the config/database.yml file is not what Sequel expects - the method Sequel::Database.connect receives a parameter conn_string as nil, and then is trying to call ".merge" on it as if it was a hash. (line 137 in database.rb in tag 3.1.0) Something should raise an exception when Sequel.connect or Sequel::Database.connect receive an illegal database uri (for example an empty string) When the config/database.yml file is not what Sequel expects - the method Sequel::Database.connect receives a parameter conn_string as nil, and then is trying to call ".merge" on it as if it was a hash. (line 137 in database.rb in tag 3.1.0) Something should raise an exception when Sequel.connect or Sequel::Database.connect receive an illegal database uri (for example an empty string) Jun 04, 2009 issue 271 (Graphing on "from_self" sends ruby objects into sql) reported by evgeny.zislis - Using Sequel tag 3.1.0 (84db22136d694c13c55f) $ bin/sequel ... irb(main):011:0> DB[:places].sql => "SELECT * FROM `places`" irb(main):012:0> DB[:places].from_self.sql => "SELECT * FROM (SELECT * FROM `places`) AS `t1`" irb(main):013:0> DB[:places].from_self.graph(:properties).sql => "SELECT `#<Sequel::MySQL::Dataset:0x13ea304>`.`id`, `#<Sequel::MySQL::Dataset:0x13ea304>`.`lat`, `#<Sequel::MySQL::Dataset:0x13ea304>`.`lng`, `#<Sequel::MySQL::Dataset:0x13ea304>`.`zoom`, `properties`.`id` AS `properties_id`, `properties`.`key`, `properties`.`value` FROM (SELECT * FROM `places`) AS `t1` LEFT OUTER JOIN `properties`" Using Sequel tag 3.1.0 (84db22136d694c13c55f) $ bin/sequel ... irb(main):011:0> DB[:places].sql => "SELECT * FROM `places`" irb(main):012:0> DB[:places].from_self.sql => "SELECT * FROM (SELECT * FROM `places`) AS `t1`" irb(main):013:0> DB[:places].from_self.graph(:properties).sql => "SELECT `#<Sequel::MySQL::Dataset:0x13ea304>`.`id`, `#<Sequel::MySQL::Dataset:0x13ea304>`.`lat`, `#<Sequel::MySQL::Dataset:0x13ea304>`.`lng`, `#<Sequel::MySQL::Dataset:0x13ea304>`.`zoom`, `properties`.`id` AS `properties_id`, `properties`.`key`, `properties`.`value` FROM (SELECT * FROM `places`) AS `t1` LEFT OUTER JOIN `properties`" Jun 03, 2009 issue 270 (Reciprocal associations sometimes associated to wrong model) changed by jeremyevans0 - Thanks for the bug report, fixed: http://github.com/jeremyevans/sequel/commit/c5f2dc11d5a0dc683c9d357a82a97f5dad64bef2 Status: Fixed Owner: jeremyevans0 Thanks for the bug report, fixed: http://github.com/jeremyevans/sequel/commit/c5f2dc11d5a0dc683c9d357a82a97f5dad64bef2 Status: Fixed Owner: jeremyevans0 Jun 03, 2009 issue 270 (Reciprocal associations sometimes associated to wrong model) reported by zverok.offline - I have some structure like -- class PricePosition belongs_to :category end class SpecDef belongs_to :category end class Category has_many :specdefs, :class => 'SpecDef' has_many :price_positions end -- In some really complex operations I’ve found, that category.specdefs sometimes is an array with models of class SpecDef AND one or two of class PricePosition. After ≈3 hours of happy debugging I’ve determined that problematic construction is this: def reciprocal ... if assoc_reflect[:type] == r_type && assoc_reflect[:key] == key ... It somehow happens to be TOO WIDE condition; this one works for me: if assoc_reflect[:type] == reciprocal_type && assoc_reflect[:key] == key && assoc_reflect[:class_name] == self[:model] I’m sorry for I haven’t pointed concrete line numbers (I’m using not the latest Sequel, but I’ve checked latest sources on GitHub and found the problem still there); as well as for no concrete test case (thei’re really complex). I hope for just logical checking of my words. I have some structure like -- class PricePosition belongs_to :category end class SpecDef belongs_to :category end class Category has_many :specdefs, :class => 'SpecDef' has_many :price_positions end -- In some really complex operations I’ve found, that category.specdefs sometimes is an array with models of class SpecDef AND one or two of class PricePosition. After ≈3 hours of happy debugging I’ve determined that problematic construction is this: def reciprocal ... if assoc_reflect[:type] == r_type && assoc_reflect[:key] == key ... It somehow happens to be TOO WIDE condition; this one works for me: if assoc_reflect[:type] == reciprocal_type && assoc_reflect[:key] == key && assoc_reflect[:class_name] == self[:model] I’m sorry for I haven’t pointed concrete line numbers (I’m using not the latest Sequel, but I’ve checked latest sources on GitHub and found the problem still there); as well as for no concrete test case (thei’re really complex). I hope for just logical checking of my words. Jun 02, 2009 issue 269 (Weird stuff happening with migrations) Status changed by jeremyevans0 - No problem. I recommend using the following format for migrations to reduce the risk of class name reuse: Class.new(Sequel::Migration) do def up end def down end end That'll create an anonymous class, so it's not possible to have a conflict with another migration. Status: Invalid No problem. I recommend using the following format for migrations to reduce the risk of class name reuse: Class.new(Sequel::Migration) do def up end def down end end That'll create an anonymous class, so it's not possible to have a conflict with another migration. Status: Invalid Jun 02, 2009 issue 269 (Weird stuff happening with migrations) commented on by evgeny.zislis - okay - think its my fault again. as i've said, i moved the first file into the second two ... and i forgot to rename the Class in the process. most probably this was the cause of all the confusion. sorry to waste your time :( okay - think its my fault again. as i've said, i moved the first file into the second two ... and i forgot to rename the Class in the process. most probably this was the cause of all the confusion. sorry to waste your time :( Jun 02, 2009 issue 269 (Weird stuff happening with migrations) Owner changed by jeremyevans0 - You should include the -E or -l switches when running the migrations if you want to see what Sequel is doing. It'll log the SQL to the standard output or a file. -M isn't required when using -m. When it is not specified, it migrates to the latest migration version. If you use -M, it should run all migrations between the current version and the version you specify. If you don't think this is happening, run it with -E to see what SQL is being sent. Owner: jeremyevans0 You should include the -E or -l switches when running the migrations if you want to see what Sequel is doing. It'll log the SQL to the standard output or a file. -M isn't required when using -m. When it is not specified, it migrates to the latest migration version. If you use -M, it should run all migrations between the current version and the version you specify. If you don't think this is happening, run it with -E to see what SQL is being sent. Owner: jeremyevans0 Jun 02, 2009 issue 267 (Error in migration.rb code) changed by jeremyevans0 - Thanks for the report, fixed: http://github.com/jeremyevans/sequel/commit/372da429a0cff4de003037786f05f75862e752ec Status: Fixed Owner: jeremyevans0 Thanks for the report, fixed: http://github.com/jeremyevans/sequel/commit/372da429a0cff4de003037786f05f75862e752ec Status: Fixed Owner: jeremyevans0 Jun 02, 2009 issue 269 (Weird stuff happening with migrations) commented on by evgeny.zislis - even after i fixed it .. without -M it migrates only to the latest file, so the tables from (1) and (2) are not there. And when I use -M with a number it migrates only that migration, so other tables are not there. even after i fixed it .. without -M it migrates only to the latest file, so the tables from (1) and (2) are not there. And when I use -M with a number it migrates only that migration, so other tables are not there. Jun 02, 2009 issue 269 (Weird stuff happening with migrations) commented on by evgeny.zislis - my problem again, which makes this problem a bit different. I created 1_create_places.rb that included 5 tables, places, properties, place_property, types, place_type; Then I refactored it into 2_create_properties with just properties and place_property, and 3_create_types with just types and place_types -- but forgot to clean the first file, so the file 1_create_places.rb still had 5 tables created (and dropped in down). But why didn't it shout on me when I did a migration without specifying -M at all??? my problem again, which makes this problem a bit different. I created 1_create_places.rb that included 5 tables, places, properties, place_property, types, place_type; Then I refactored it into 2_create_properties with just properties and place_property, and 3_create_types with just types and place_types -- but forgot to clean the first file, so the file 1_create_places.rb still had 5 tables created (and dropped in down). But why didn't it shout on me when I did a migration without specifying -M at all??? Jun 02, 2009 issue 269 (Weird stuff happening with migrations) reported by evgeny.zislis - Using sequel 3.0.0 tag - commit 10f69a2bd22e39ba03f4687af196cd476e88d162 Scenario: $ ls -al db/migration/ total 24 drwxr-xr-x 5 evgeny evgeny 170 Jun 3 02:01 ./ drwxr-xr-x 6 evgeny evgeny 204 Jun 3 01:27 ../ -rw-r--r-- 1 evgeny evgeny 755 Jun 3 01:57 1_create_places.rb -rw-r--r-- 1 evgeny evgeny 361 Jun 3 01:49 2_create_properties.rb -rw-r--r-- 1 evgeny evgeny 334 Jun 3 01:50 3_create_types.rb $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development place_type schema_info types $ sequel config/database.yml -m db/migration -M 0 $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development schema_info $ sequel config/database.yml -m db/migration -M 1 $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development place_property place_type places properties schema_info types $ sequel config/database.yml -m db/migration -M 2 Sequel::DatabaseError: Mysql::Error Table 'properties' already exists ./vendor/sequel/lib/sequel/adapters/mysql.rb:166:in `query' $ sequel config/database.yml -m db/migration -M 0 $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development schema_info $ sequel config/database.yml -m db/migration $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development place_type schema_info types Using sequel 3.0.0 tag - commit 10f69a2bd22e39ba03f4687af196cd476e88d162 Scenario: $ ls -al db/migration/ total 24 drwxr-xr-x 5 evgeny evgeny 170 Jun 3 02:01 ./ drwxr-xr-x 6 evgeny evgeny 204 Jun 3 01:27 ../ -rw-r--r-- 1 evgeny evgeny 755 Jun 3 01:57 1_create_places.rb -rw-r--r-- 1 evgeny evgeny 361 Jun 3 01:49 2_create_properties.rb -rw-r--r-- 1 evgeny evgeny 334 Jun 3 01:50 3_create_types.rb $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development place_type schema_info types $ sequel config/database.yml -m db/migration -M 0 $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development schema_info $ sequel config/database.yml -m db/migration -M 1 $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development place_property place_type places properties schema_info types $ sequel config/database.yml -m db/migration -M 2 Sequel::DatabaseError: Mysql::Error Table 'properties' already exists ./vendor/sequel/lib/sequel/adapters/mysql.rb:166:in `query' $ sequel config/database.yml -m db/migration -M 0 $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development schema_info $ sequel config/database.yml -m db/migration $ echo 'show tables;' \| mysql5 eatery_development Tables_in_eatery_development place_type schema_info types

issue 276 (Annoying result using "<<") reported by i...@aliax.net - I'm experimenting an annoying issue when using raw SQL to generate prepared staments (for MySQL). First of all, I do it manually using "mysql" commandline interface: sql> PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"; Query OK, 0 rows affected (0.00 sec) sql> SET @arg = "alice@domain.org/2"; Query OK, 0 rows affected (0.00 sec) sql >EXECUTE get_doc USING @arg; __DOCUMENT IS CORRECTLY RETRIEVED HERE __ 1 row in set (0.00 sec) By inspecting the mysql.log I see how the queries arrive to MySQL server: ------------------------- 9114 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9114 Prepare [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9114 Query SET @arg = "alice@domain.org/2" 9114 Query EXECUTE get_doc USING @arg 9114 Execute [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 ------------------------- Now I try to do the SAME using raw SQL in Sequel: ---------------------- DB << %{PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"} DB << %{SET @arg = "alice@domain.org/2"} res = DB << %{EXECUTE get_doc USING @arg} puts res.inspect ---------------------- It's annoying but I get no result and 'res' is nil. But more annoying is the fact the the SQL arriving to the server is exactly the *same* as above: --------------------- 9115 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9115 Prepare [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9115 Query SET @arg = "alice@domain.org/2" 9115 Query EXECUTE get_doc USING @arg 9115 Execute [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 --------------------- Perhaps I miss something? I've just realized that if by error I do a wrong query (note @arggg instead of @arg): SET @arg = "alice@domain.org/2" EXECUTE get_doc USING @arggg then the behavior is identical. This is, MySQL doesn't raise an error if the argument name hasn't been set before. However, it doesn't explain my problem since the mysql.log shows the *same* (letter by letter) for both cases (mysql command line and Sequel raw SAL).

I'm experimenting an annoying issue when using raw SQL to generate prepared staments (for MySQL). First of all, I do it manually using "mysql" commandline interface: sql> PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"; Query OK, 0 rows affected (0.00 sec) sql> SET @arg = "alice@domain.org/2"; Query OK, 0 rows affected (0.00 sec) sql >EXECUTE get_doc USING @arg; __DOCUMENT IS CORRECTLY RETRIEVED HERE __ 1 row in set (0.00 sec) By inspecting the mysql.log I see how the queries arrive to MySQL server: ------------------------- 9114 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9114 Prepare [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9114 Query SET @arg = "alice@domain.org/2" 9114 Query EXECUTE get_doc USING @arg 9114 Execute [2] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 ------------------------- Now I try to do the SAME using raw SQL in Sequel: ---------------------- DB << %{PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1"} DB << %{SET @arg = "alice@domain.org/2"} res = DB << %{EXECUTE get_doc USING @arg} puts res.inspect ---------------------- It's annoying but I get no result and 'res' is nil. But more annoying is the fact the the SQL arriving to the server is exactly the *same* as above: --------------------- 9115 Query PREPARE get_doc FROM "SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1" 9115 Prepare [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = ?) LIMIT 1 9115 Query SET @arg = "alice@domain.org/2" 9115 Query EXECUTE get_doc USING @arg 9115 Execute [1] SELECT `doc` FROM `storage` WHERE (CONCAT(`username`, '@', `domain`, '/', `doc_type`) = 'alice@domain.org/2') LIMIT 1 --------------------- Perhaps I miss something? I've just realized that if by error I do a wrong query (note @arggg instead of @arg): SET @arg = "alice@domain.org/2" EXECUTE get_doc USING @arggg then the behavior is identical. This is, MySQL doesn't raise an error if the argument name hasn't been set before. However, it doesn't explain my problem since the mysql.log shows the *same* (letter by letter) for both cases (mysql command line and Sequel raw SAL).

Last 7 days

Older