Penn State Mark Penn State Application Services - PHP Web service text graphic Information Technology Services
 
PHP Web Service home | Storing Data | Setting PASS Permissions | Firewall/Database Permissions Info | Errata and Change Log

Using SQLite; file based SQL databases

  • SQLite is an embedded database system that stores data directly to files. Unlike other Relational Database Management Systems (RDBMS) such as MySQL and PostgreSQL, there is no SQLite server to contact. For more info on SQLite, see http://sqlite.org/.

  • SQLite version 2 and version 3 use incompatible database file formats. One will need to convert a version 2 formated database to a version 3 database format before using the version 3 driver.

  • PHP has bundled SQLite version 2 since PHP version 5.0. Access has traditionally been available by way of the SQLite extension to PHP.

  • PHP has bundled SQLite version 3 since PHP version 5.1 by way of the PHP Data Objects (PDO) interface.

    • php.scripts.psu.edu has included SQLite version 3 and PDO since Phase II, Build 4.

    • PHP Data Objects (PDO) has the capability to access both SQLite version 2 (via "sqlite2") and SQLite version 3 (via "sqlite") databases as well as other databases (mysql, pgsql, etc). More info on SQLite via PDO: http://php.net/pdo_sqlite.

  • As of Phase III, Version 1, both the old SQLite extension to PHP and the SQLite version 2 driver in PDO have become deprecated. Their removal is expected in July 2009. Users are strongly encouraged to update their PHP applications that use the SQLite extension and/or SQLite version 2 databases to SQLite version 3 via PDO as soon as possible.

  • Note:  The server group php.scripts.psu.edu needs to be assigned with write permissions on the folder that contains the SQLite database files in order to create/delete temporary transaction files. See also: How to set permissions in PASS for PHP data files.

  • Note:  New SQLite database files will be automatically created if they don't already exist. However, new SQLite database files may not be able to be created unless there is at least one slash (/) in the filename used by sqlite_open() or new PDO(). If you wish to create a new database file in the local folder, prepend the name with "./", such as with:

    • SQLite extension, SQLite version 2:
      <?php sqlite_open("./sqlite.db"); ?>

    • PDO, SQLite version 2:
      <?php $dbh = new PDO("sqlite2:./sqlite.db"); ?>

    • PDO, SQLite version 3:
      <?php $dbh = new PDO("sqlite:./sqlite.db"); ?>

  • NOTE: It is recommended that data files are stored in a separate folder from the PHP code to avoid some security risks.


The Pennsylvania State University ©2008. All rights reserved.
Alternative Media - Nondiscrimination Statement
Information Technology Services.

For assistance please write to helpdesk@psu.edu or see our Help Sources.

Last revised: Monday, July 21, 2008.