$Id: sqlite_drv.txt,v 1.3 2011/07/08 23:01:42 pcockings Exp $ COPYRIGHT (C) 2002-2012 DSPAM Project http://dspam.sourceforge.net LICENSE This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. You should have received a copy of the GNU Affero General Public License along with this program. If not, see . ABOUT - - ----------------------------------------------------------------------- - - - - Note: The sqlite_drv for using SQLite2 (>2.7.7) is not widely used and - - - - is likely to be removed in a future release. Please consider - - - - moving to SQLite3 and the sqlite3_drv instead. - - - - ----------------------------------------------------------------------- - - sqlite_drv and sqlite3_drv are SQLite storage drivers for DSPAM v3.0 and above. These drivers enables DSPAM to read and write all token, signature, and statistics data from individual SQLite databases created for each user. WARNING: SQLite is not designed to function over NFS. If your system is large enough to require NFS, it is strongly recommended you use a stateful SQL driver, such as MySQL or PostgreSQL. 1. CONFIGURING DSPAM To configure DSPAM to use a driver, use the following arguments while running DSPAM's configure: --with-storage-driver=sqlite_drv Tells DSPAM to use the sqlite_drv driver --with-storage-driver=sqlite3_drv Tells DSPAM to use the sqlite3_drv driver (for SQLite v3.x) --with-sqlite-libraries=/path/to/libs Tells DSPAM where to find the SQLite library (libsqlite.so). --with-sqlite-includes=/path/to/includes Tells DSPAM where to find the SQLite headers. After configure has successfully finished, build and install DSPAM using the instructions from DSPAM's readme. 2. OBJECT CREATION The drivers will automatically create the necessary database objects for each user upon first use of DSPAM by that user. 3. PURGING The purge script should be run against all databases periodically. The best way to do this is to use a find recipe such as: find /usr/local/var/dspam/data -name "*.sdb" -exec sqlite {} < /var/dspam/purge-2.sql \; or use sqlite3 and purge-3.sql if you're running sqlite3_drv. You may also wish to occasionally compact your databases. This should only be run once in a great while, and is not entirely necessary as SQLite will automatically reclaim space the next time it is used. Nevertheless, some databases can grow quite large if not purged well enough. Another find recipe such as the one below can be used to compact all databases on a system. find /usr/local/var/dspam/data -name "*.sdb" -exec echo 'vacuum;' \| sqlite {} \; or sqlite3, if you're running sqlite3_drv. 4. TUNING There are some tuning parameters which can be set, discussed here: http://sqlite.org/pragma.html#syntax Whenever sqlite_drv connects to a database, it will read $DSPAM_HOME/sqlite.pragma and execute any commands found in the file. For example, if you wish to turn off synchronous mode (which speeds up queries by as much as 50x, but could potentially risk database corruption on system crash), you could add this line to sqlite.pragma: PRAGMA synchronous = OFF Note: - Do not put a semicolon at the end of any statements in sqlite.pragma. - Comments are not allowed in sqlite.pragma. Anything you add to the file will be executed without being filtered. ERRORS Any SQL errors will be reported to LOGDIR/sql.errors as well as the standard syslog facilities (although the query will be truncated). QUESTIONS Please contact the dspam-dev mailing list with any questions or constructive feedback. Initial storage driver written by Jonathan A. Zdziarski and later enhanced by Stevan Bajic for DSPAM 3.9.0.