ReplaceArfReportBreakdownView migration error under sqlite3 database

[domcleal]

==  ReplaceArfReportBreakdownView: migrating ==================================
-- execute("CREATE OR REPLACE VIEW scaptimony_arf_report_breakdowns AS\n  SELECT\n    arf.id as arf_report_id,\n    COUNT(CASE WHEN result.name IN ('pass','fixed') THEN 1 ELSE null END) as passed,\n    COUNT(CASE result.name WHEN 'fail' THEN 1 ELSE null END) as failed,\n    COUNT(CASE WHEN result.name NOT IN ('pass', 'fixed', 'fail', 'notselected', 'notapplicable') THEN 1 ELSE null END) as othered\n  FROM\n    scaptimony_arf_reports arf\n  LEFT OUTER JOIN\n    scaptimony_xccdf_rule_results rule\n    ON arf.id = rule.arf_report_id\n  LEFT OUTER JOIN scaptimony_xccdf_results result\n    ON rule.xccdf_result_id = result.id\n  GROUP BY arf.id;\n")
rake aborted!
An error has occurred, this and all later migrations canceled:
SQLite3::SQLException: near "OR": syntax error: CREATE OR REPLACE VIEW scaptimony_arf_report_breakdowns AS
  SELECT
    arf.id as arf_report_id,
    COUNT(CASE WHEN result.name IN ('pass','fixed') THEN 1 ELSE null END) as passed,
    COUNT(CASE result.name WHEN 'fail' THEN 1 ELSE null END) as failed,
    COUNT(CASE WHEN result.name NOT IN ('pass', 'fixed', 'fail', 'notselected', 'notapplicable') THEN 1 ELSE null END) as othered
  FROM
    scaptimony_arf_reports arf
  LEFT OUTER JOIN
    scaptimony_xccdf_rule_results rule
    ON arf.id = rule.arf_report_id
  LEFT OUTER JOIN scaptimony_xccdf_results result
    ON rule.xccdf_result_id = result.id
  GROUP BY arf.id;
/usr/share/gems/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:in `initialize'
/usr/share/gems/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:in `new'
/usr/share/gems/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:in `prepare'
/usr/share/gems/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:134:in `execute'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/connection_adapters/sqlite_adapter.rb:278:in `block in execute'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/usr/share/gems/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/connection_adapters/sqlite_adapter.rb:278:in `execute'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:466:in `block in method_missing'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:438:in `block in say_with_time'
/usr/share/ruby/benchmark.rb:281:in `measure'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:438:in `say_with_time'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:458:in `method_missing'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:334:in `method_missing'
/usr/share/gems/gems/scaptimony-0.3.1/db/migrate/20141121164042_replace_arf_report_breakdown_view.rb:3:in `up'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:370:in `up'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:410:in `block (2 levels) in migrate'
/usr/share/ruby/benchmark.rb:281:in `measure'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:410:in `block in migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:389:in `migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:528:in `migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:775:in `call'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:775:in `block in ddl_transaction'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:192:in `transaction'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/transactions.rb:208:in `transaction'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:775:in `ddl_transaction'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:719:in `block in migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:700:in `each'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:700:in `migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:570:in `up'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/migration.rb:551:in `migrate'
/usr/share/gems/gems/activerecord-3.2.13/lib/active_record/railties/databases.rake:193:in `block (2 levels) in '
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:246:in `call'
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:246:in `block in execute'
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:241:in `each'
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:241:in `execute'
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:184:in `block in invoke_with_call_chain'
/usr/share/ruby/monitor.rb:211:in `mon_synchronize'
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:177:in `invoke_with_call_chain'
/usr/share/gems/gems/rake-10.0.4/lib/rake/task.rb:170:in `invoke'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:143:in `invoke_task'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:101:in `block (2 levels) in top_level'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:101:in `each'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:101:in `block in top_level'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:110:in `run_with_threads'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:95:in `top_level'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:73:in `block in run'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:160:in `standard_exception_handling'
/usr/share/gems/gems/rake-10.0.4/lib/rake/application.rb:70:in `run'
/usr/share/gems/gems/rake-10.0.4/bin/rake:33:in `'
/usr/bin/rake:23:in `load'
/usr/bin/rake:23:in `
'
Tasks: TOP => db:migrate

The sqlite3 database is supported by Foreman and is used in package builds when building apipie docs, so basic operations need to work under it.

(It may also be worth checking MySQL compatibility, as that is also supported.)

[shlomizadok]

Should be fixed with https://github.com/OpenSCAP/scaptimony/pull/25

[shlomizadok]

25 is now merged. Will you kindly test if it works on your end?

[domcleal]

It doesn't appear to work on sqlite3, as it tries to create a duplicate view. I would hazard a guess that table_exists? returns false for a view so the old view isn't deleted in the ReplaceArfReportBreakdownView migration.

See https://gist.githubusercontent.com/domcleal/008dac763b46dc2246c9/raw/build.log for a full log.