Support non-public PostgreSQL schema

Indeed it's a ruby on rails defect. Is this search_path setting critical for you ?

Well, it simplifies work considerably. It means I can issue Join Queries with tables from other schema. in particular our bug tracking system.

This issue duplicates SONAR-178, fixed in version 1.9.

I guess this means I need to press the ruby people to add the fix...

Sorry, I mixed up this issue and SONAR-178. It's reopened.

Hi Rodolfo,
How can I configure Postgre to reproduce the issue ? Is there a setting to activate when creating the database ?
Thank you

Hi Simon-

I was able to reproduce this error on a recent sonar install.

Here are the specifics:

Postgres 8.4
Mac OS X 10.6.2
Sonar 1.12

I created the database using the following script:

— Start script —

drop database sonar;
drop user sonar;

create user sonar with password 'sonar';
create database sonar with owner=sonar encoding='UTF8';

\connect sonar

create schema sonar authorization sonar;

— End Script —

I made the appropriate adjustsments to the sonar.properties file in the WEB-INF/classes directory and commented out the derby database configuration properties and uncommented the postgres configuration properties. And restarted tomcat and sonar attempted to create the database and perform the necessary migrations.

Here is the start of the exception trace:

org.jruby.rack.RackInitializationException: An error has occurred, all later migrations canceled:

Table rules_categories does not exist
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/gems/gems/activerecord-2.2.2/lib/active_record/base.rb:2839:in `attributes_from_column_definition'
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/gems/gems/activerecord-2.2.2/lib/active_record/locking/optimistic.rb:55:in `attributes_from_column_definition_with_lock'
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/gems/gems/activerecord-2.2.2/lib/active_record/base.rb:2279:in `initialize'
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/gems/gems/activerecord-2.2.2/lib/active_record/base.rb:691:in `create'
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/config/../lib/../db/migrate//003_populate_database.rb:33:in `insert_rule_categories'
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/config/../lib/../db/migrate//003_populate_database.rb:22:in `up'
from /opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:280:in `migrate'
from file:/opt/local/share/java/tomcat6/webapps/sonar/WEB-INF/lib/jruby-complete-1.3.1.jar!/benchmark.rb:293:in `measure'

I hope this helps. If you need some assistance in testing a fix let me know and I'd be happy to help to try and install it.

Derek

Good news, I can reproduce the issue. To fix it, edit war/sonar-web/WEB-INF/config/environment.rb and replace the module PostgreSQL (from line 128) by the following :

module PostgreSQL
    def modify_types(tp)
      tp[:primary_key] = "serial primary key"
      tp[:integer][:limit] = nil
      tp[:boolean][:limit] = nil

      # sonar hack : remove the following line
      # tp[:string][:limit] = 255
      tp
    end
    
    # See SONAR-862 on Postgre search_path setting.
    # The issue is fixed in next activerecord-jdbc-adapter version: http://github.com/nicksieger/activerecord-jdbc-adapter/commit/2575700d3aee2eb395cac3e7933bb4d129fa2f03 
    # More details on https://rails.lighthouseapp.com/projects/8994/tickets/918-postgresql-tables-not-generating-correct-schema-list
    def columns(table_name, name=nil)
      # schema_name must be nil instead of "public"
      schema_name = nil
      if table_name =~ /\./
        parts = table_name.split(/\./)
        table_name = parts.pop
        schema_name = parts.join(".")
      end
      @connection.columns_internal(table_name, name, schema_name)
    end
  end

Could give it a try please ? If it's ok I'll apply the patch in trunk.
Thank you for your help.

Hi Simon-

It worked. I made the changes you suggested and deleted/re-created the sonar database to force the sql creation and the schema creation was successful. If there are any other issues that stem from this fix I'll be sure to report them.

Derek

I applied the patch to sonar 1.12 and I was unable to upgrade or create a schema.

I was also unable to create the database from scratch, should I try with 2.0?

== CreateGroups: migrating ===================================================
– create_table(:groups, {:force=>true})
2010.01.13 18:24:53 ERROR rails

StandardError (An error has occurred, all later migrations canceled:

ActiveRecord::ActiveRecordError: ERROR: table "groups" does not exist: DROP TABLE "groups"):
/gems/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in `log'
/gems/gems/activerecord-jdbc-adapter-0.9/lib/active_record/connection_adapters/jdbc_adapter.rb:581:in `execute'
/gems/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/schema_statements.rb:187:in `drop_table'
/gems/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/schema_statements.rb:107:in `create_table'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:346:in `method_missing'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:326:in `say_with_time'
file:/lib/jruby-complete-1.3.1.jar!/benchmark.rb:293:in `measure'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:326:in `say_with_time'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:342:in `method_missing'
/config/../lib/../db/migrate//079_create_groups.rb:22:in `up'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:280:in `migrate'
file:/lib/jruby-complete-1.3.1.jar!/benchmark.rb:293:in `measure'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:280:in `migrate'
(_DELEGATION_):2:in `migrate'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:480:in `migrate'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:556:in `call'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:556:in `ddl_transaction'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:479:in `migrate'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:466:in `each'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:466:in `migrate'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:394:in `up'
/gems/gems/activerecord-2.2.2/lib/active_record/migration.rb:377:in `migrate'
/config/../lib/database_version.rb:63:in `setup'
/app/controllers/setup_controller.rb:43:in `setup_database'
/gems/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `perform_action'

I don't understand why it tries to drop the table GROUPS. Are you sure you install on a fresh schema ? Can you attach the full log please ?
FYI version 2.0 is not released.

Jetty log for a clean schema

(hit enter accidentally)

Added a jetty log for a clean schema.

After it fails, there are 11 sequences and 12 tables created in the schema.

When creating from a fresh schema the error is while creating the user table.

When updating from 1.11 (on a schema, the error is the one posted above).

Just in case: I double checked, and your patch is applied.

Unfortunatly I still don't know why this problem occurs.
Which Postgresql version do you use ? How do you create the schema ?

Linux kindleit.net 2.6.29-gentoo-r5 #2 SMP Tue Jul 28 21:57:13 AST 2009 i686 Dual Core AMD Opteron(tm) Processor 265 AuthenticAMD GNU/Linux
kindleit ~ # sudo -u postgres pg_ctl --version
pg_ctl (PostgreSQL) 8.4.2

kindleit ~ # psql -U postgres kindleit
psql (8.4.2)
Type "help" for help.

kindleit=# drop schema sonar cascade;
NOTICE: drop cascades to 12 other objects
DETAIL: drop cascades to table sonar.schema_migrations
drop cascades to table sonar.projects
drop cascades to table sonar.snapshots
drop cascades to table sonar.metrics
drop cascades to table sonar.project_measures
drop cascades to table sonar.files
drop cascades to table sonar.rules_categories
drop cascades to table sonar.rules
drop cascades to table sonar.parameters
drop cascades to table sonar.rule_failures
drop cascades to table sonar.rules_parameters
drop cascades to table sonar.project_links
DROP SCHEMA
kindleit=# create schema sonar authorization sonar;
CREATE SCHEMA
kindleit=# \q

One important difference is that my sonar user DOES NOT have write access to public.
So if the scripts attempt to write to any other schema, it will fail. (which is correct)

Can you try to edit the file war/sonar-web/WEB-INF/db/migrate/079_create_groups.rb and remove :force => true ?

create_table :groups do |t|

I have problems in the installation:
after:

• Install postgresql
• Create BD and user

-bash-3.2$ createuser -P -s -e sonar
pass: sonar
-bash-3.2$ psql
postgres=# select * from pg_shadow;
postgres=# CREATE DATABASE sonar WITH ENCODING 'UNICODE';
postgres=# GRANT ALL PRIVILEGES ON DATABASE sonar TO sonar;
postgres=# ALTER DATABASE sonar OWNER TO sonar;
postgres=# \l

1. Download unzip sonar-2.12.zip
2. copy file to /usr/sonar/
3. unzip sonar-2.12.zip
4. edit sonar.properties
---------------------------------------------------------------------------------------
sonar.web.host: 127.0.0.1
sonar.web.port: 9000
sonar.web.context: /
---------------------------------------------------------------------------------------
and
---------------------------------------------------------------------------------------

1. sonar.jdbc.url: jdbc:derby://localhost:1527/sonar;create=true
2. sonar.jdbc.driverClassName: org.apache.derby.jdbc.ClientDriver
   ---------------------------------------------------------------------------------------
   and
   ---------------------------------------------------------------------------------------
   sonar.jdbc.url: jdbc:postgresql://localhost/sonar
   sonar.jdbc.driverClassName: org.postgresql.Driver
   sonar.jdbc.validationQuery: select 1
   ---------------------------------------------------------------------------------------
   5. execute build-war.sh
   6. watch in the web browser http://localhost:9000

but only watch error in the page:
We're sorry, but something went wrong.
Please try back in a few minutes and contact support if the problem persists.
<%= link_to "Go back to the homepage", home_path %>

Help. please.
as capable of excellent sound and installation of postgresql?

Hi Nade, please use the mailing-list and provide the server logs.
Thanks