Database programming guidelines

[PVince81]

• always use the Query Builder

• use IQueryBuilder::createPositionalParameter instead of IQueryBuilder::createNamedParameter when using like()

• don't update more than 1 million rows within a transaction due to DB limitations

• don't add more than 1000 conditions in a WHERE ... IN ... statement but chunk it into separate queries

• when processing big tables, always do this in chunks, don't store the whole table in memory

• Oracle compatibility specifics:

  • for Oracle "null" and empty strings are the same thing. Need special handling to catch these cases
    • when reading values, make sure to convert null to empty string when expected
    • keep this in mind when designing DB schema and values expectations.
    • when using a condition based on empty strings, use "is not null" with Oracle instead
  • Oracle can only compare the first 4000 bytes of a CLOB column
  • make sure to properly escape column names when using custom functions with createFunction. the escaping is usually done automatically by the query builder otherwise. Oracle is the most likely to complain about unquoted columns while other databases will work fine

• always add the table name when calling lastInsertId($tableName), it is required by Oracle to return correct values

• in general don't specify a value for autoincrement column. if you have to, keep in mind that Oracle's autoincrement trigger will get in the way on INSERT, so you'll need a subsequent UPDATE to properly adjust the value

• always make sure there are unit tests for the database operations with queries to verify the result, this will help find out whether the database related code works on all databases and often times might reveal database quirks

• running unit tests with specific databases: make test-php TEST_PHP_SUITE=path/to/test/file.php TEST_DATABASE=$databasetype where "$databasetype" is one of "sqlite", "mysql", "mariadb", "pgsql", "oci" and "mysqlmb4"

TODO: add more detail to all these

[DeepDiver1975]

don't add more than 1000 conditions in a WHERE ... IN ... statement but chunk it into separate queries

999 as per sqlite

[PVince81]

minus other conditions. so if you have where x=2 and y=3 and z in (...) then the IN part only gets 999-2=997 values

[VicDeo]

MySQL / InnoDB indexes are limited to 767 bytes - which is 191 char max for utf8mb4 encoding

[VicDeo]

string concatenation
CONCAT(str1, str2, ... strN) for Mysql
str1 || str1 ... || strN Sqlite/PgSQL/Oracle

[settermjd]

Thanks for all the information, folks. I'll get the docs updated.

[settermjd]

Is this documentation to be updated, or is a new section required?

[PVince81]

Sounds good, let's add it there maybe before all the code ?

[PVince81]

Added "- always add the table name when calling lastInsertId($tableName), it is required by Oracle to return correct values" in top post

[voroyam]

So to sum up, @PVince81 you want something along those lines?

Database programming guidelines

- always use the Query Builder
- don't update more than 1 million rows within a transaction due to DB limitations
- don't add more than 1000 conditions in a WHERE ... IN ... statement but chunk it into separate queries
- don't add more than 999  conditions in a WHERE ... IN ... statement but chunk it into separate queries when using SQLite
- when processing big tables, always do this in chunks, don't store the whole table in memory
- Oracle compatibility specifics:
    - for Oracle "null" and empty strings are the same thing. Need special handling to catch these cases
        - when reading values, make sure to convert null to empty string when expected
        - keep this in mind when designing DB schema and values expectations.
        - when using a condition based on empty strings, use "is not null" with Oracle instead
    - Oracle can only compare the first 4000 bytes of a CLOB column
    - make sure to properly escape column names when using custom functions with `createFunction`. the escaping is usually done automatically by the query builder otherwise. Oracle is the most likely to complain about unquoted columns while other databases will work fine
- always add the table name when calling `lastInsertId($tableName)`, it is required by Oracle to return correct values
- in general don't specify a value for autoincrement column. if you have to, keep in mind that Oracle's autoincrement trigger will get in the way on INSERT, so you'll need a subsequent UPDATE to properly adjust the value
- *always* make sure there are unit tests for the database operations with queries to verify the result, this will help find out whether the database related code works on all databases and often times might reveal database quirks


- running unit tests with specific databases:  `make test-php TEST_PHP_SUITE=path/to/test/file.php TEST_DATABASE=$databasetype` where "$databasetype" is one of "sqlite", "mysql", "mariadb", "pgsql", "oci" and "mysqlmb4"

- string concatenation should be done like this:
- `CONCAT(str1, str2, ... strN)` for Mysql
- `str1 || str1 ... || strN` Sqlite/PgSQL/Oracle

and this should be added here:

[PVince81]

@voroyam yes, would be great. Maybe it should rather appear after "Database Access" since accessing is the first step. Then we go into details about guidelines.

Is this something you'd like to put in yourself ?

[voroyam]

If no one else will - I could.

I'd rather let a developer or engineer write the documentation for developers because of my lack of knowledge. But the chances of that are close to zero :)

You, @DeepDiver1975 @VicDeo made the effort to write up the guidelines in this issue, so I thought if I just copy paste them and adjust the formatting, it would be fine.

What do you think? @PVince81

[PVince81]

It should be possible to plan for someone to put it in, if everyone agrees that the guidelines as they are here make sense.

[DeepDiver1975]

use IQueryBuilder::createPositionalParameter instead of IQueryBuilder::createNamedParameter when using like()

[settermjd]

@voroyam, are you implementing this? Or has it already been done?

[voroyam]

No, but I will.

[voroyam]

Question

Are those both statements correct?

* don't add more than 1000 conditions in a `WHERE ... IN ...` statement but chunk it into separate queries
* don't add more than 999 conditions in a `WHERE ... IN ...` statement but chunk it into separate queries when using SQLite

@PVince81 @DeepDiver1975

[voroyam]

owncloud/docs#71

[PVince81]

@voroyam let's make that a "999 minus number of other parameters", in a single statement, for SQLite (instead of two statements)

[voroyam]

@PVince81

Those two lines in one line?

* Don't add more than 1000 conditions in a `WHERE ... IN ...` statement but chunk it into separate queries
* Don't add more than 999 conditions in a `WHERE ... IN ...` statement but chunk it into separate queries when using SQLite

in

* Don't add more than 999 conditions in a WHERE ... IN ... statement but chunk it into separate queries when using SQLite

[PVince81]

@voroyam sounds good

[voroyam]

@PVince81 done

[voroyam]

PR merged, closing