Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #2788 (closed defect: fixed)

Opened 3 years ago

Last modified 2 years ago

[PATCH] Oracle column defaults not properly parsed

Reported by: rick@rickbradley.com Assigned to: Michael Schoen <schoenm@earthlink.net>
Priority: normal Milestone:
Component: ActiveRecord Version: 0.14.3
Severity: normal Keywords: oracle oci column default boolean string error parsed
Cc: rails@bitsweat.net

Description

We noticed that we were having problems with the defaults for single character columns with the OCI adapter. We're using the latest OCI driver and Rails 0.14.3 on both Ruby 1.8.2 and 1.8.3. I believe this server is running 10g.

What we were seeing is that when we had a default like:

is_primary char(1) default 'F' not null,

that AR on Oracle (not on other DBs) was ultimately attempting to insert a value like:

F '

Note the extra space before the closing single quote.

We noticed a patch in 0.14.3 that did away with the extraneous quotes, but didn't catch the trailing spaces (this is probably an Oracle version problem I'm guessing). The LONG column Oracle uses to describe the default data leaves much to be desired (in fact, they've deprecated its use for quite some time, as far as I'm aware, but still use it themselves ;-) in terms of parsing.

Anyway, the attached patch to trunk handles the trailing whitespace.

Attachments

oracle_default_whitespace.patch (0.8 kB) - added by rick@rickbradley.com on 11/08/05 18:55:37.
patch to trunk for default column values whitespace problem in OCI
oci_parse_defaults.patch (2.2 kB) - added by mschoen on 11/14/05 04:36:59.

Change History

11/08/05 18:55:37 changed by rick@rickbradley.com

  • attachment oracle_default_whitespace.patch added.

patch to trunk for default column values whitespace problem in OCI

11/08/05 18:58:08 changed by rick@rickbradley.com

That original comment didn't come out clearly in the Trac markup, we were basically seeing:

[quote][quote][quote]F[quote][quote][space][quote]

11/08/05 19:09:01 changed by bitsweat

IANAOU (... Oracle User), but that gsub looks fishy. What does it accomplish? Should it be a sub! Could it be moved into the query itself?

I'll apply the fix in the spirit of pragmatism, but that code smell needs to be addressed.

11/08/05 19:11:11 changed by bitsweat

  • owner changed from David to Michael Schoen <schoenm@earthlink.net>.

11/08/05 19:11:28 changed by bitsweat

  • status changed from new to closed.
  • resolution set to fixed.

(In [2942]) Correct whitespace problem in Oracle default column value parsing. Closes #2788.

11/08/05 19:15:46 changed by rick@rickbradley.com

In response to the question about that sub, I'm not sure who wrote that original gsub there that we modified, but the problem is that Oracle doesn't provide much in the way of useful functions for working with their deprecated LONG type, which they're using in that meta-table to describe the default data. The query results that come back can't easily be munged w/in the query itself (the guy sitting next to me while we were working on that used to work at Oracle and ultimately threw his hands up on getting a SELECT that would do the Right Thing) and it looks like the most robust (ironically) way to get the thing to work is to fall back out to Ruby to get it going.

I agree, the code reeks to high heaven. Being a regular (but mostly unwilling) Oracle user myself I have to say that the smell is coming from deep within Oracle itself.

11/08/05 19:25:01 changed by mschoen

The smelly gsub! is mine, and could be changed to a sub!

As Rick notes, it's there because Oracle will only give up the default value in an odd format -- as a LONG, and quoted.

11/13/05 21:50:13 changed by rick@rickbradley.com

  • status changed from closed to reopened.
  • resolution deleted.

This was undone by [2997] when closing #2848.

11/13/05 22:40:06 changed by bitsweat

Thanks for spotting the regression. This behavior needs a unit test to prevent future regressions.

11/13/05 22:46:17 changed by bitsweat

(In [3009]) Reapply [2942] which was elided by [2997]. References #2788. References #2848.

11/13/05 22:49:22 changed by bitsweat

(In [3010]) r3055@asus: jeremy | 2005-11-13 14:48:48 -0800

Apply [3009] to stable: Reapply [2942] which was elided by [2997]. References #2788.

11/13/05 22:50:37 changed by bitsweat

  • cc set to rails@bitsweat.net.

(Kept ticket open pending unit test.)

11/14/05 04:36:16 changed by mschoen

Sorry about that -- totally sloppy on my part. I had gone back to an earlier revision to resolve #2848, and overwrote the patch by mistake.

I've attached another patch that makes this a sub! rather than a gsub!, and includes a regression test so this doesn't happen again.

Also, can you please remove the following files: 

    activerecord/test/fixtures/db_definitions/create_oracle_db.bat
    activerecord/test/fixtures/db_definitions/create_oracle_db.sh

11/14/05 04:36:59 changed by mschoen

  • attachment oci_parse_defaults.patch added.

11/14/05 04:52:00 changed by bitsweat

(In [3019]) Oracle: test case for column default parsing. References #2788.

11/14/05 04:55:24 changed by bitsweat

  • status changed from reopened to closed.
  • resolution set to fixed.

(In [3020]) r3070@asus: jeremy | 2005-11-13 20:54:50 -0800

Apply [3019] to stable. Oracle: test case for column default parsing. Closes #2788.

08/07/06 09:25:23 changed by bitsweat

(In [4708]) r3022@ks: jeremy | 2005-11-12 18:40:42 -0800

merge to stable r3024@ks: jeremy | 2005-11-12 19:00:49 -0800 Apply [2992] to stable. SQLite: the clone_structure_to_test and purge_test_database Rake tasks should always use the test environment. References #2846. r3027@ks: jeremy | 2005-11-12 22:37:45 -0800 Apply [2994] to stable. PostgreSQL: correct the sequence discovery fallback query. References #2594. r3033@ks: jeremy | 2005-11-12 23:27:13 -0800 Apply [2996] to stable. Much faster Oracle column reflection. Closes #2848. r3037@ks: jeremy | 2005-11-13 00:11:26 -0800 Apply [2998] to stable. PostgreSQL: last_insert_id uses select_value rather than using @connection.exec directly. r3053@ks: jeremy | 2005-11-13 14:37:39 -0800 Only include builtin filters whose filenames match /[a-z][a-z_]*_helper.rb$/ to avoid including operating system metadata such as ._foo_helper.rb. Closes #2855. r3055@ks: jeremy | 2005-11-13 14:48:48 -0800 Apply [3009] to stable: Reapply [2942] which was elided by [2997]. References #2788. r3058@ks: jeremy | 2005-11-13 16:09:17 -0800 Apply [3013] to stable. Note that the ruby-memcache bindings are required to use the memcache store. Closes #2857. r3063@ks: jeremy | 2005-11-13 16:13:51 -0800 Apply [3015] to stable. Update documentation for render :file. Closes #2858. r3066@ks: jeremy | 2005-11-13 20:24:18 -0800 Apply [3017] to stable. Update documentation for Migrations. Closes #2861. r3070@ks: jeremy | 2005-11-13 20:54:50 -0800 Apply [3019] to stable. Oracle: test case for column default parsing. Closes #2788. r3073@ks: jeremy | 2005-11-13 23:42:32 -0800 Apply [3021] to stable. Correct documentation for Base.delete_all. Closes #1568. r3077@ks: jeremy | 2005-11-14 14:28:21 -0800 Apply [3035] to stable. SQLServer: insert uses given primary key value if not nil rather than SELECT @@IDENTITY. Closes #2866. r3120@ks: jeremy | 2005-11-16 13:34:45 -0800 Apply [3060] to stable. Remove the unused, slow response_dump and session_dump variables from error pages. Closes #1222. r3130@ks: jeremy | 2005-11-16 14:37:47 -0800 Apply [3063] to stable. Remove CHANGELOG from apidoc Rake task since it isn't included with new apps anymore. r3148@ks: jeremy | 2005-11-17 12:45:44 -0800 Apply [3074] to stable. Don't add the same conditions twice in has_one finder sql. Closes #2916. r3149@ks: jeremy | 2005-11-17 12:47:36 -0800 Apply [3075] to stable. Document :force option to create_table. Closes #2921. r3157@ks: jeremy | 2005-11-17 21:27:39 -0800 Apply [3081] to stable. Eliminate nil from newly generated logfiles. Closes #2927. r3167@ks: jeremy | 2005-11-18 22:47:31 -0800 Apply [3089] to stable. Don't generate read methods for columns whose names are not valid ruby method names. Closes #2946. r3174@ks: jeremy | 2005-11-19 01:53:00 -0800 Apply [3092] to stable. Correct boolean handling in generated reader methods. Closes #2945. r3178@ks: jeremy | 2005-11-19 01:59:47 -0800 Apply [3094] to stable. Use query methods rather than readers in boolean tests for [3092]. References #2949. r3190@ks: jeremy | 2005-11-19 20:20:48 -0800 Apply [3098] to stable. Introducing the session_migration generator. Creates an add_session_table migration. Closes #2958. r3199@ks: jeremy | 2005-11-19 21:13:22 -0800 Apply [3101] to stable. Allows generator to specify migrations directory. Closes #2960. r3201@ks: jeremy | 2005-11-19 21:31:47 -0800 Apply [3103] to stable. Document request.env and request.host. Strip trailing whitespace. r3258@ks: jeremy | 2005-11-21 00:33:59 -0800 Apply [3139] to stable. Use Kernel.binding rather than binding to allow columns of that name. Closes #2973. r3269@ks: jeremy | 2005-11-21 04:46:40 -0800 Apply [3148] to stable. Simpler Mysql load test. r3285@ks: jeremy | 2005-11-22 13:33:04 -0800 Apply [3162] to stable. Model generator: correct relative path to test_helper in unit test. r3296@ks: jeremy | 2005-11-23 13:38:56 -0800 Apply [3170] to stable. Credit ticket author. References #2888. r3303@ks: jeremy | 2005-11-23 17:14:31 -0800 Apply [3051], [3052], [3053], [3059] to stable. Brings the Firebird adapter to 1.0. References #1874. r3305@ks: jeremy | 2005-11-23 17:33:51 -0800 Apply [3151] to stable. Don't put flash in session if sessions are disabled. r3306@ks: jeremy | 2005-11-23 17:39:24 -0800 Apply [3100], [3108] to stable. Makes new ActiveRecordStore sessions work correctly with components. r3307@ks: jeremy | 2005-11-23 17:52:14 -0800 Apply [3110] to stable. PostgreSQL: the purge_test_database Rake task shouldn't explicitly specify the template0 template when creating a fresh test database. References #2964. r3313@ks: jeremy | 2005-11-23 23:03:36 -0800 Apply [3182] to stable. Reloading a model doesn't lose track of its connection. Closes #2996. r3315@ks: jeremy | 2005-11-23 23:13:48 -0800 Apply [3184] to stable. SQLServer: resolve column aliasing/quoting collision when using limit or offset in an eager find. Closes #2974. r3339@ks: jeremy | 2005-12-01 17:03:16 -0800 Apply [3202] to stable. Firebird: updated for FireRuby 0.4.0. Closes #3009. r3363@ks: jeremy | 2005-12-06 22:26:27 -0800 Apply #428 changesets to stable: r3000, r3001, r3002, r3025, r3045, r3096, r3148, r3152, r3165, r3189, r3195, r3205, r3216, r3219, r3221, r3222. Closes #428. r3372@ks: jeremy | 2005-12-07 20:25:20 -0800 Apply [3233] to stable. Oracle: use syntax compatible with Oracle 8. Closes #3131. r3375@ks: jeremy | 2005-12-07 20:36:13 -0800 Apply [3235] to stable. PostgreSQL: more robust sequence name discovery. Closes #3087. r3378@ks: jeremy | 2005-12-07 20:41:47 -0800 Apply [3237] to stable. More robust relative url root discovery for SCGI compatibility. This solves the 'SCGI routes problem' -- you no longer need to prefix all your routes with the name of the SCGI mountpoint. Closes #3070. r3379@ks: jeremy | 2005-12-07 20:42:31 -0800 Don't warn on keep_flash in test. r3387@ks: jeremy | 2005-12-07 20:48:42 -0800 Apply [3242] to stable. Reloading an instance refreshes its aggregations as well as its associations. Closes #3024. r3389@ks: jeremy | 2005-12-07 20:52:39 -0800 Apply [3244] to stable. SQLite: find database file when RAILS_ROOT is a symlink. Closes #3116. r3392@ks: jeremy | 2005-12-07 21:10:42 -0800 Apply [3246] to stable. MySQL: more robust test for nullified result hashes. Closes #3124. r3401@ks: jeremy | 2005-12-07 23:30:11 -0800 Apply [3252] to stable. Oracle: active? performs a select instead of a commit. Closes #3133. r3404@ks: jeremy | 2005-12-08 15:24:44 -0800 Apply [3254] to stable. Fix some test failures due to MySQL assumptions. References #3149. r3407@ks: jeremy | 2005-12-09 10:39:00 -0800 Apply [3256] to stable. Generator copies files in binary mode. Closes #3156. r3410@ks: jeremy | 2005-12-09 10:48:32 -0800 Apply [3258] to stable. Fix shebang handling for empty files. Closes #2927. r3413@ks: jeremy | 2005-12-09 15:03:42 -0800 Apply [3260] to stable. Fix bundled mysql.rb to correctly check for PROTO_41. Fixed scramble41 with nil password. Fixed change_user with PROTO_41. r3416@ks: jeremy | 2005-12-09 15:07:21 -0800 Apply [3262] to stable. Generator looks in vendor/generators also. r3419@ks: jeremy | 2005-12-09 15:36:50 -0800 Apply [3165] to stable. MySQL, PostgreSQL: reconnect! also reconfigures the connection. Otherwise, the connection 'loses' its settings if it times out and is reconnected. References #2978. r3420@ks: jeremy | 2005-12-09 15:38:30 -0800 Apply [3264] to stable. MySQL: ensure that @config is set. r3421@ks: jeremy | 2005-12-09 15:42:40 -0800 Apply [3265] to stable. Name vendor/generators source differently from lib/generators source. r3429@ks: jeremy | 2005-12-09 16:55:16 -0800 Apply [3270] SQLServer rollup to stable. r3435@ks: jeremy | 2005-12-10 13:40:43 -0800 Apply [3274] to stable. MySQL: fixes for the bundled mysql.rb driver. Closes #3160. r3457@ks: jeremy | 2005-12-13 08:52:39 -0800 Apply [3293] to stable. MySQL: allow encoding option for mysql.rb driver. r3458@ks: jeremy | 2005-12-13 09:33:50 -0800 Roll back [3245] on stable. References #3116. r3465@ks: jeremy | 2005-12-13 10:15:33 -0800 Apply [3298] to stable. Don't used defined? on a scoped constant since it results in a const_missing call. r3724@ks: jeremy | 2006-02-09 10:13:41 -0800 Apply [3554] to stable. PostgreSQL: correctly parse negative integer column defaults. Closes #3776. r3730@ks: jeremy | 2006-02-09 11:42:57 -0800 Apply [3559] to stable. Closes #3581. r3846@ks: jeremy | 2006-02-26 15:24:58 -0800 silence test/unit whining DefaultsTest is empty r3847@ks: jeremy | 2006-02-26 15:26:53 -0800 Apply [3674] to stable. Closes #3591. r4960@ks: jeremy | 2006-08-06 23:56:18 -0700 Merged to stable: change the request.env example in AC::Base docs to a var that exists (REMOTE_IP doesn't) and isn't already wrapped by a request method (i.e. request.remote_ip). References #5113. r4961@ks: jeremy | 2006-08-07 00:11:59 -0700 Merged to stable: add :status option to send_data and send_file. Defaults to '200 OK'. References #5243. r4962@ks: jeremy | 2006-08-07 00:18:42 -0700 Merged to stable: real files and symlinks should be treated the same when compiling templates. References #5438. r4963@ks: jeremy | 2006-08-07 00:26:42 -0700 Merged to stable: Added ActionController.filter_parameter_logging that makes it easy to remove passwords, credit card numbers, and other sensitive information from being logged when a request is handled. References #1897. r4964@ks: jeremy | 2006-08-07 00:33:32 -0700 Merged to stable: correct spurious documentation example code which results in a SyntaxError. References [4210]. r4965@ks: jeremy | 2006-08-07 00:38:04 -0700 Merged to stable: remote_form_for can leave out the object parameter and default to the instance variable of the object_name, just like form_for. References [4215]. r4966@ks: jeremy | 2006-08-07 00:48:40 -0700 Merged to stable: update inconsistent migrations documentation. References #4683. r4967@ks: jeremy | 2006-08-07 00:52:34 -0700 Merge to stable: cache CgiRequest#request_parameters so that multiple calls don't re-parse multipart data. References [4256]. r4968@ks: jeremy | 2006-08-07 00:55:51 -0700 Merge to stable: only require redcloth/bluecloth if they are not already loaded. References [4257]. r4977@ks: jeremy | 2006-08-07 01:08:02 -0700 Merge to stable: skip silence stderr test if we can't STDERR.tell r4978@ks: jeremy | 2006-08-07 01:13:00 -0700 Merge to stable: update layout and content_for documentation to use yield rather than magic @content_for_layout instance variables. References [4263]. r4979@ks: jeremy | 2006-08-07 01:16:43 -0700 Merge to stable: add documentation for redirect_to :back's RedirectBackError exception; remove all remaining references to @params in the documentation. References [4267], [4268]. r4980@ks: jeremy | 2006-08-07 01:18:04 -0700 Merge to stable: fix documentation indentation. r4981@ks: jeremy | 2006-08-07 01:28:34 -0700 Merge to stable: mention in docs that config.frameworks doesn't work when getting Rails via Gems; documentation for AbstractRequest. References #4857, #4895. r4982@ks: jeremy | 2006-08-07 01:45:26 -0700 Merge to stable: fix syntax error in documentation. References #4679. r4983@ks: jeremy | 2006-08-07 01:50:47 -0700 Merge to stable: Enhance documentation for setting headers in integration tests. Skip auto HTTP prepending when its already there. References #4079. Add warning about the proper way to validate the presence of a foreign key. References #4147. r4984@ks: jeremy | 2006-08-07 02:08:00 -0700 Merge to stable: ActionController::Base Summary documentation rewrite; Fix text_helper.rb documentation rendering; Fixes bad rendering of JavaScriptMacrosHelper rdoc. References #4725, #4900, #4910. r4985@ks: jeremy | 2006-08-07 02:14:18 -0700 Merge to stable: documentation fix: integration test scripts don't require integration_test. References #4914.