I started to work on the PostgreSQL support for Sonar. All our project/build infrastructure is built on top of PostgreSQL and I'd really like it to stay that way for consistency reasons.
I know PostgreSQL well but I'm really not a Rails guy so, even if I made it work after struggling for a few hours, I'd appreciate any input on how to properly fix the problems in a maintainable way.
I'd like to have Sonar working out of the box with PostgreSQL, even if it's not supported officially.
1/ the problem above
It's fixed by adding a "driver: org.postgresql.Driver" in the war/sonar-web/WEB-INF/config/database.yml file. I don't know how it's done for the other database though and why it doesn't work for PostgreSQL specifically.
My database.yml is now:
Then you have the setup page working and you can try to setup the database.
2/ Problem with decimal types
There is an inconsistency in how Sonar uses the decimal type. Either you use a float type with no precision or a decimal/numeric one with precision but you cannot have a float type with a type modifier (e.g. here precision).
In war/sonar-web/WEB-INF/config/environment.rb, there is a few PostgreSQL specific lines which maps decimal to float8. I tried to remove it to let Rails map decimal to numeric, it works for the database creation but it's not consistent with the type present in the Java files (I have an Hibernate exception).
I temporarily fixed it by removing precision, scale and so on from all decimal types in the database creation scripts.
I think we can either:
- map decimal to numeric as it's supposed to be in Rails and change the according type in the Java files (it's the best solution if we really need precision);
- consider that we don't need the precision, that float arithmetic is OK and do as I did it currently.
3/ Sequence naming convention
Default sequence naming for PostgreSQL is <tablename>_<primary key name>_seq: that's how Rails mapped them by default but it's not consistent with the @SequenceGenerator annotations present in the Java code.
I didn't find any good solution so I roughly changed all the @SequenceGenerator lines to map the sequence naming convention of PostgreSQL (I added ID before _SEQ in sequenceName) to see if I could go a bit further and have a running Sonar/PostgreSQL. I know it's ugly and not portable as Oracle has a different naming convention for sequences: we need to find another way to do it correctly.
One of the possibility to fix this issue is to have a special PostgreSQL dialect which overrides the method related to sequences and rewrite the sequence name before returning the sequence related queries.
Any other idea?
4/ It works
I tested one of our projects and the UI seems to work correctly.
I had to add these lines in my settings.xml but it's what we have to do when we don't use the default database so nothing specific:
<!-- EXAMPLE FOR MYSQL -->
<!-- SERVER ON A REMOTE HOST -->