Common

Reserved words

  1. MySQL 5.0
  2. PostgreSQL 8.3
  3. Oracle 10.2
  4. MS SQL Server 2000

Avoid using IN clause due to performance issues on most DB and due to a limitation on Oracle which prevents having more than 1'000 elements in the IN clause.

By default, when getting some measures from the project_measures table, the three following filters MUST always being used : "rule_id is null", "characteristic_id is null" and "committer is null".

We can assume that there is no difference between VARCHAR(40) and VARCHAR(1000) in terms of volume in database when storing values less than 40 characters.

This document compares different SQL implementations.

Parameter values must not be hardcoded into the SQL request but must be injected through the JDBC or Hibernate API.

Example :

databaseSession.getEntityManager().createNativeQuery("SELECT * FROM reviews where resource_id = :resourceId", Review.class).setParameter("resourceId", 123).getResultList();
databaseSession.getEntityManager().createNativeQuery("SELECT * FROM reviews where resource_id = 123", Review.class).getResultList();

Unique index

In cases where the index enforces uniqueness, NULL values are excluded from the index and uniqueness is not enforced between NULL values (ANSI SQL 92). 

When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.

That means that unique index should not be used when indexed columns can contain NULL values, for example [PROP_KEY, RESOURCE_ID, USER_ID] in the table PROPERTIES.
Note that this ANSI constraint is not always implemented by vendors. MsSQL has the option "ANSI_NULLS" to achieve standards compliance.

MyBatis

When writings DTOs that have boolean attributes, use the primitive "boolean" instead of the class "Boolean", as MyBatis does not handle correctly null Boolean objects.

Sonar API

  1. Poorness of Sonar API : invocation of createQuery(hql) and createNativeQuery(sql) from org.sonar.api.database.DatabaseSession is required to start new transaction. This actually implies that Query can't be reused to perform two transactions - see commit a8910c8.

Oracle

  1. Index name limited to 30 characters.

  2. Word "AS" should not be used to define alias for tables, i.e. :

    SELECT * FROM table AS row;

    should be replaced by :

    SELECT * FROM table row;

    Otherwise you will get "ORA-00936: missing expression".

  3. This is not possible to link some tables when deleting some rows. The EXISTS clause must be used instead :

    DELETE FROM my_table mt WHERE EXISTS (SELECT 1 FROM another_table at WHERE at.is = mt.id)
  4. MyBatis : instead of constants "TRUE" and "FALSE" use "1" and "0" respectively.

  5. MyBatis : sequence generation

    INSERT INTO table(id, ...) VALUES (table_seq.NEXTVAL, ...)
  6. Conditions on CLOB are not supported, for example SELECT * FROM PROPERTIES WHERE TEXT_VALUE='foo' raises the error "ORA-00932: inconsistent datatypes". The workaround is to use TEXT_VALUE LIKE 'foo'.

Derby

  1. "true" can't be used as a part of query, i.e. :

    createNativeQuery("SELECT * FROM snapshots WHERE islast=true")

    should be replaced by :

    createNativeQuery("SELECT * FROM snapshots WHERE islast=:islast").setParameter("islast", Boolean.TRUE)

    Derby supports boolean type since version 10.7 , but this wasn't supported by Ruby and Hibernate till Sonar 2.12 - see SONAR-2956.

MySQL

  1. Length of VARCHAR index is limited to 767 bytes. It equals 255 characters when assuming a UTF-8 character set and the maximum of 3 bytes for each character.

    add_index :my_table, :my_column, :name => 'index_name', :length => 255

  2. This is possible to link some tables when deleting some rows :

    DELETE mt FROM my_table mt, another_table at WHERE at.id = mt.id

  3. Hibernate native query

    createNativeQuery("delete from duplications_index e where e.snapshot_id in (:ids)")

    fails with message 

    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 'e where e.snapshot_id in (...)' at line 1
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query

    and should be replaced by 

    createNativeQuery("delete from duplications_index where snapshot_id in (:ids)")
  4. Timestamp precision is limited to the second. Starting from MySQL 5.6.4 it is possible to explicitly create a TIMESTAMP column with a finer precision.

MSSQL

  1. MyBatis : instead of constants "TRUE" and "FALSE" use "1" and "0" respectively.
  2. If there is an index on column, which should be resized or modified, then it should be dropped and then re-created after changes for column. Otherwise you will get "The index '...' is dependent on column '...'."
  3. Table names are case-sensitive. In Sonar they are lower-case. That's why MyBatis mappers must be lower-case. Otherwise the error is something like "Did not find table 'RULES'".

  4. Delete with joins. Note that table alias seems to be not supported :

    DELETE my_table FROM my_table INNER JOIN another_table ON my_table.is=another_table.is WHERE another_table.xxx=...