I have been working on converting cacti (0.8.8a) to use sqlite instead of MySQL and am quite pleased with the results so far. Here is what I have figured out so far:
- dumped the mysql data and loaded into sqlite using the mysql2sqlite.sh script. A few changes I made to the output from the script before importing into sqlite were: 1. change all autoincrement PK fields to be 'integer' instead of int, smallint, mediumint - this automatically enables autoincrement for sqlite and 2. rename the user_auth_realm index to user_auth_realm1 since it conflicts with the table of the same name
- added in a connection method for sqlite using pdo_sqlite
- modified mysql-specific sql to be compatible with sqlite without sacrificing mysql compatibility. Specifically, these were the changes (still a work in progress)
- alias column names in select to their raw column names. This is mysql-compatible and works with sqlite, otherwise sqlite returns names prefixed with the table alias, which breaks array indexes
replace the mysql CONCAT and CONCAT_WS operators with php concatenate (.) instead. sqlite does not have CONCAT or CONCAT_WS and the sqlite (ansi) concat operator (||) is not compatible with mysql, so doing it in php is the safest
convert all RIGHT JOINs to their corresponding LEFT JOINs - sqlite does not support RIGHT JOINs
convert UNIX_TIMESTAMP to the corresponding php strtotime instead - sqlite does not have UNIX_TIMESTAMP
Now here's the question - is anybody interested in a sqlite port for cacti? Would there be any interest in pushing the changes into the mainstream code since they do not break MySQL compatibility and set a direction to allow for other databases to be used as the back-end as well? I am not a programmer, I am a DBA, so my code is more functional than elegant, I would appreciate a review anyway.
I'll be happy to post the code or email it if there's any interest, just let me know!
