Stop using SQLite for Rails dev

Re:Stop using SQLite for Rails dev by nbpoole on Sunday August 28, 2011 @12:39AM (#2933306)

The developer's ;-)

SQLite doesn't have strict types (see http://www.sqlite.org/datatype3.html). MySQL does. It's not SQLite's fault or ActiveRecord's fault: it's one of the many issues you have to take into account when developing software that runs on two very different database systems.

Edit: ActiveRecord specifically notes this behavior in the documentation:

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAd...

    Please be aware of different RDBMS implementations behavior with :decimal columns:

    The SQL standard says the default scale should be 0, :scale <= :precision, and makes no comments about the requirements of :precision.

    MySQL: :precision [1..63], :scale [0..30]. Default is (10,0).

    PostgreSQL: :precision [1..infinity], :scale [0..infinity]. No default.

    SQLite2: Any :precision and :scale may be used. Internal storage as strings. No default.

    SQLite3: No restrictions on :precision and :scale, but the maximum supported :precision is 16. No default.

    Oracle: :precision [1..38], :scale [-84..127]. Default is (38,0).

    DB2: :precision [1..63], :scale [0..62]. Default unknown.

    Firebird: :precision [1..18], :scale [0..18]. Default (9,0). Internal types NUMERIC and DECIMAL have different storage rules, decimal being better.

    FrontBase?: :precision [1..38], :scale [0..38]. Default (38,0). WARNING Max :precision/:scale for NUMERIC is 19, and DECIMAL is 38.

    SqlServer?: :precision [1..38], :scale [0..38]. Default (38,0).

    Sybase: :precision [1..38], :scale [0..38]. Default (38,0).

    OpenBase?: Documentation unclear. Claims storage in double.

[ Parent | on HN ]

Re:Stop using SQLite for Rails dev by prodigal_erik on Sunday August 28, 2011 @09:36PM (#2935550)

SQLite should reject DDL that specifies DECIMAL(n, 0) unless that type is implemented correctly. Until now I only expected MySQL to display reckless disregard for users' stated requirements in favor of guessing what they might want.

[ Parent | on HN ]