pdeffendol / spatial_adapter

Spatial Adapter for ActiveRecord and Rails 2.x and 3.0.x - no longer in active development (try RGeo for Rails 3.1+)
MIT License
198 stars 99 forks source link

SQL Syntax error when using YAML fixtures unless srid is present #24

Open haxney opened 13 years ago

haxney commented 13 years ago

I have a fixture defined like so:

one:
  long_name: Long Name
  short_name: LN
  bounds: !ruby/object:Polygon
    rings:
    - !ruby/object:LinearRing
      points:
      - !ruby/object:Point {x: 0, y: 0}
      - !ruby/object:Point {x: 20, y: 0}
      - !ruby/object:Point {x: 20, y: 20}
      - !ruby/object:Point {x: 0, y: 20}
      - !ruby/object:Point {x: 0, y: 0}

But trying to use it in a test suite results in the following error:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 400743110)' at line 1: INSERT INTO `locations` (`long_name`, `short_name`, `bounds`, `id`) VALUES ('Positive Location', 'PL', GeomFromWKB(0x010300000001000000050000000000000000000000000000000000000000000000000034400000000000000000000000000000344000000000000034400000000000000000000000000000344000000000000000000000000000000000,), 400743110)

The key part of the problem is:

GeomFromWKB(0x01030000[...]0000,),

Where there is an extra comma inside the GeomFromWKB function call. Looking through the source, at lib/spatial_adapter/mysql2.rb:20 it looks like if value.srid is nil, then it would cause the erroneous SQL statement to be returned. I'm not sure how or why the srid isn't being set to DEFAULT_SRID when the object is being created, but if I manually set it in the YAML like so:

one:
  long_name: Long Name
  short_name: LN
  bounds: !ruby/object:Polygon
    srid: -1
    rings:
    - !ruby/object:LinearRing
      points:
      - !ruby/object:Point {x: 0, y: 0}
      - !ruby/object:Point {x: 20, y: 0}
      - !ruby/object:Point {x: 20, y: 20}
      - !ruby/object:Point {x: 0, y: 20}
      - !ruby/object:Point {x: 0, y: 0}

Then it works fine. Also, it doesn't need to be set in the child objects, since the GeoRuby srid= method takes care of propagating srid changes down through the tree.

This isn't the end of the world because of the simple workaround, but it does seem to go against the expected behavior.

pdeffendol commented 13 years ago

Thanks, I'm pretty sure how to fix it, but I'm trying to get the tests updated first, and having trouble with the dependencies, not having worked on this for a while.

The problem seems to lie here: https://github.com/fragility/spatial_adapter/blob/master/lib/spatial_adapter/mysql2.rb#L20

haxney commented 13 years ago

Yup, it seems like the srid isn't being set for the objects, event though it should be defaulting to DEFAULT_SRID. I don't know why it would be set to nil, it might be something within the way fixtures create instances.