|
|
|
|
Bookmark this on Delicious
Share on Facebook
 Slashdot It!
Digg SQLite FunctionsThis is an extension for the SQLite Embeddable SQL Database Engine. SQLite is a C library that implements an embeddable SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process. SQLite is not a client library used to connect to a big database server. SQLite is the server. The SQLite library reads and writes directly to and from the database files on disk.
Note:
For further information see the SQLite Website (» http://sqlite.org/). Read the INSTALL file, which comes with the package. Or just use the PEAR installer with pecl install sqlite. SQLite itself is already included, You do not need to install any additional software. Windows users may download the DLL version of the SQLite extension here: (» php_sqlite.dll).
In PHP 5, the SQLite extension and the engine itself are bundled and
compiled by default. However, since PHP 5.1.0 you need to manually
activate the extension in extension=php_pdo.dllOn Linux or Unix operating systems, if you build PDO as a shared extension, you must build SQLite as a shared extension using the --with-sqlite=shared configure option. SQLite 3 is supported through PDO SQLite.
Windows installation for unprivileged accounts:
On Windows operating systems, unprivileged accounts don't have the
SetEnv TMP c:/tempIf you are unable to establish this setting at the server level, you can implement the setting in your script: putenv('TMP=C:/temp'); ?>
The setting must refer to a directory that the web server
has permission to create files in and subsequently write
to and delete the files it created.
Otherwise, you may receive the following error message:
In order to have these functions available, you must compile PHP with
SQLite support, or load the SQLite extension dynamically from your
There are two resources used in the SQLite Interface. The first one is the database connection, the second one the result set. The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime. The functions sqlite_fetch_array() and sqlite_current() use a constant for the different types of result arrays. The following constants are defined: SQLite result type constants
A number of functions may return status codes. The following constants are defined: SQLite status code constants
Represents an opened SQLite database.
Represents a buffered SQLite result set.
Represents an unbuffered SQLite result set. Unbuffered results sets are sequential, forward-seeking only.
The behaviour of these functions is affected by settings in Table 301. SQLite Configure Options
Here's a short explanation of the configuration directives.
Table of Contents
Code Examples / Notes » ref.sqlitedavid
You can use the PECL SQLite extension as a static (built into the executable) PHP module with PHP 4. Download the extension tarball and extract it. Move it to ext/sqlite in the PHP source directory. Delete configure and run buildconf. Example below. Change version numbers as appropriate: $ tar -xzvf php-4.3.8.tar.gz $ tar -xzvf SQLite-1.0.2.tgz $ mv SQLite-1.0.2 php-4.3.8/ext/sqlite $ cd php-4.3.8 $ rm configure $ ./buildconf --force If everything worked, then you should now be able to build PHP with SQLite support: $ ./configure --help | grep sqlite --with-sqlite Include sqlite support I think this method will work for other PECL extensions. ng4rrjanbiah
Very nice MS PowerPoint presentation titled "SQLite and PHP" (author: Wez Furlong) can be downloaded at http://www.php.net/~wez/SQLite_and_PHP.ppt [134 KB] HTH, R. Rajesh Jeba Anbiah cricket
To elaborate on vpupkin at comcast dot net's post about the INSERT query problem, you will be unable to execute any manipulation queries (INSERT/UPDATE/DELETE/ect) on the SQLite database file unless the directory the SQLite database file resides in is writable by the webserver. The reason for this is because SQLite needs to write a lock file to the hard drive. After a processes finishes writting, it deletes the lock file. Other processes check for the lock file before writting to the SQLite database file and if present, delay writting until the lock file is no longer present. dotwho
This may have been obvious to others, but I had a tough time finding the info. The default location for the actual database file is the same location of the php doc that created the database. You can alter this behavior by specifying the full path in the creation call: <php $db = sqlite_open("/absolute/path/my_sqlite.db"); ?> Note that if you used the default location, the db file may be served up by the webserver if it is in a the http document path. This is obviously a security risk that should be avoided. //Max nicolas dot toniazzi
The same in 3 lines. <?php function sqlite_table_exists($db,$mytable) { /* counts the tables that match the name given */ $result = sqlite_query($db,"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$mytable'"); /* casts into integer */ $count = intval(sqlite_fetch_single($result)); /* returns true or false */ return $count > 0; } ?> alexs
the OO version. <?php function sqlite_table_exists($db,$mytable) { // ask the database kindly what tables is has. $result = $db->query("SELECT name FROM sqlite_master WHERE type='table'"); $tables = $result->fetchAll(); // if there's no items in the array then our taget is not going to exists. if (count($tables) == 0) { return FALSE ; } // loop over the tables to see if there's a table we're looking for. foreach ($tables as $table) { if ($table['name'] == $mytable) { return(TRUE); } } // there was no such table, informe the user the bad news. return(FALSE); } ?> notshrockc_inhs_org
The correct url for the windows dll is: http://pecl4win.php.net/ext.php/php_sqlite.dll philip
SQLite tutorials: * http://www.zend.com/php5/articles/php5-sqlite.php * http://www.zend.com/php5/abs/php101-9.php * http://www.oreilly.com/catalog/upgradephp5/chapter/ch04.pdf bens
Sqlite by default allows for foreign keys, but doesn't enforce them. I found a nice article on how to enforce foreign keys at http://www.justatheory.com/computers/databases/sqlite/ It would be nice if this could be handled with a wrapper class, so that adding foreign keys is done thru a wrapper API to automate the generation of the necessary enforcement triggers. If nobody's done this by the time I start coding against SqlLite/PHP5 in 6-12 months, I'll write one myself, possibly by modifying "jon at jenseng dot com"s alter table wrapper Anyway, it's not PHP specific, but anybody using Sqlite really should know this! 05-feb-2004 04:11
Since this extension is rather new and few tutorials/articles talk about it... here's one worthy of a link: http://www.php-mag.net/itr/online_artikel/psecom,id,447,nodeid,114 jon
Since SQLite doesn't support ALTER TABLE statements or renaming tables, modifying an existing table is a bit cumbersome. You have to: 1. create a temporary table 2. copy the original table into the temporary table 3. delete the original 4. recreate the original with new column definitions 5. copy the contents back 6. delete the temporary table As this is rather unwieldy, I've created a wrapper class that allows for ALTER TABLE queries and does the dirty work for you. It has integrated error handling to ensure that queries are completely valid and it allows for complex statements such as: ALTER TABLE foo ADD bar VARCHAR(27), DROP bar2, CHANGE bar3 foobar INTEGER, ADD bar4 DATE Documentation: http://code.jenseng.com/db/ Source: http://code.jenseng.com/db/sql.txt heinemann.juergen
Simple Example for using SQLite for small Buffering Clipboard ;) <?php // $Id: SQLiteClipboard.php5,v 1.1 2005/09/09 10:15:26 heinemann Exp $ /** * @callgraph * @class SQLiteClipboard * @short Simple Read/Write Clipboard Class * @version 0.0.1 * @since Fr Sep 9 08:14:45 CEST 2005 * @code $sqlite = new SQLiteClipboard( MY_TEMP_DIR ); if ( $sqlite->INSERTING = time() ) var_dump( $sqlite->INSERTING ); else echo "Nothing done!"; * @endcode */ final class SQLiteClipboard { private $SQL; private $DB = "Clipboard.sqlite"; private $TB = "Clipboard"; private $ID = "ID"; private $PA = "PARAM"; private $VA = "VAL"; function __construct( $path ) { $this->DB = chop( $path . "/" . $this->DB ); $this->sql_init(); } private function sql_init() { $this->SQL = new SQLiteDatabase( $this->DB, 0660 ); if ( ! file_exists( $this->DB ) ) die( "Permission Denied!" ); $q = $this->SQL->query("PRAGMA table_info(" . $this->TB . ")"); if ( $q->numRows() == 0 ) { $this->SQL->query( "CREATE TABLE " . $this->TB . " ( " . $this->ID . " INTEGER PRIMARY KEY, " . $this->PA . " CHAR(255), " . $this->VA . " CHAR(255) );" ); } } private function sql_check( $p ) { $o = null; $q = $this->SQL->query( "SELECT " . $this->ID . " FROM " . $this->TB . " WHERE ( " . $this->PA . "='$p' ) ORDER BY " . $this->ID . " LIMIT 1" ); while( $q->valid() ) { $r = $q->current(); return $r[$this->ID]; $q->next(); } return false; } public function __get( $p ) { $q = $this->SQL->query( "SELECT " . $this->VA . " FROM " . $this->TB . " WHERE ( " . $this->PA . "='$p' ) ORDER BY " . $this->ID ); while( $q->valid() ) { $r = $q->current(); $o = $r[$this->VA]; $q->next(); } return $o; } public function __set( $p, $v ) { if ( $this->sql_check( $p ) && ! empty( $v ) ) return $this->SQL->query( "UPDATE " . $this->TB . " SET " . $this->VA . "='$v' WHERE ( " . $this->PA . "='$p' );" ); elseif ( ! $this->sql_check( $p ) && ! empty( $v ) ) return $this->SQL->query( "INSERT INTO " . $this->TB . " ( " . $this->PA . ", " . $this->VA . " ) VALUES ('$p', '$v' );" ); elseif ( $this->sql_check( $p ) && empty( $v ) ) return $this->SQL->query( "DELETE FROM " . $this->TB . " WHERE ( " . $this->PA . "='$p' );" ); else return false; } } // end Class ?> bart
SELECT tablename.columnname FROM table; will cause SQLite to return an array having tablename.field_name as the array index. (e.g. $result['tablename.field_name']) To let SQLite return an array having only field_name as the array index (e.g. $result['field_name']) you can issue a 'PRAGMA short_column_names = 1' query: sqlite_query($connection_id, 'PRAGMA short_column_names = 1'); This behaviour is more consistent with the other database extensions. For a full list of all pragmas visit: http://sqlite.org/pragma.html 13-may-2004 03:46
PHP5 compile time option --enable-sqlite-utf8 See the note about buggy utf-8 support http://uk.php.net/manual/en/function.sqlite-libencoding.php vincent
on win 32 the error "malformed database schema - unable to open a temporary database file for storing temporary tables" may happen if the temp and tmp env vars are typed like this : %WINDIR/Temp Replace by C:/Windows/Temp and the error will disappear. csaba
If you want to get the list of all the columns in a table (and associated information), PRAGMA is helpful (see http://sqlite.org/lang.html#pragma for details): if (!($db=@sqlite_open("delme.db",0666,$sqliteerror))) die("Can't open database"); @sqlite_query ($db, 'DROP TABLE foo;'); sqlite_query($db, "CREATE TABLE foo (bar INTEGER PRIMARY KEY, baz VARCHAR(5));"); $aTableStruct = sqlite_array_query($db, "PRAGMA table_info('foo');", SQLITE_ASSOC); for ($i=0,$aNames=array();$i<sizeof($aTableStruct);++$i) $aNames[]=$aTableStruct[$i]['name']; var_dump ($aNames); // => ['bar', 'baz'] Note also that if you want to use more than one database in the same connection using "ATTACH DATABASE ..." then you should supply the complete database filename. Csaba Gabor j-pieper
If you want to create a table in your database which should have an integer primary key, you cannot declare the row like this: id int(16) primary key or id integer(16) primary key When you declare it like this it could be that the id isn´t auto increment. You have to declare it like this: id integer primary key artooro
If you need to check if a table exists, you can use a function like this: <?php //Returns TRUE if table exists and FALSE otherwise // $mytable = table you want to check for function sqlite_table_exists($mytable) { $db = sqlite_open('mydb.sqlite', 0666, $sqliteerror); $query = sqlite_query($db, "SELECT name FROM sqlite_master WHERE type='table'"); $tables = sqlite_fetch_array($query); if ($tables != '') { foreach ($tables as $table) { if ($table == $mytable) { return("TRUE"); } else { return("FALSE"); } } } else { return("FALSE"); } } ?> A function like this could be used to create the table if it's not already created, etc. minots estichá
If you gone in trouble while/with installation of sqlite, you can try the installation steps I´ve done at RedHat9 with PHP4.3.4 and Apache 1.3.28 via the Linux shell: # wget http://pecl.php.net/get/SQLite-1.0.tgz # tar xzf SQLite-1.0.tgz # cd sqlite # export PHP_PREFIX="/usr" # $PHP_PREFIX/bin/phpize # ./configure # make # make install After that add following to php.ini and restart Apache: [sqlite] extension="sqlite.so" papafox888
If you get an error message: Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database then most probably your code is accessing a SQLite3 database. sqlite_open() only supports SQLite2. Use PDO to access SQLite3. vpupkin
if you are going to send INSERT queries, you will need to make the folder, where you put your "file.db", writable by a web server user, otherwise you'll receive error message - "Unable to open database . . . ". File permissions are not enough (phpinfo - SQLite Lib 2.8.3)
filip
I've written a short article about the (lack of) use of SQLite in-memory databases: http://www.filipdewaard.com/21_SQLite_inmemory_databases.html . Hopefully it will help somebody out there ;)
stephen leaf
I think it would be good to point out that currently (as of 5.0.4) this extention does not support SQLite 3 databases. So if your getting an error message like: Error: file is encrypted or is not a database That is why. jlsalinas
For those looking for a function similar to mysql_list_tables, here you have: if (! function_exists ('sqlite_list_tables')) { function sqlite_list_tables (&$dblink) { $tables = array (); $sql = "SELECT name FROM sqlite_master WHERE (type = 'table')"; if ($res = sqlite_query ($dblink, $sql)) { while (sqlite_has_more($res)) { $tables[] = sqlite_fetch_single($res); } } return $tables; } } And a related funtion, to test if a given table exists: if (! function_exists ('sqlite_table_exists')) { function sqlite_table_exists (&$dblink, $table) { $sql = "SELECT count(name) FROM sqlite_master WHERE ((type = 'table') and (name = '$table'))"; if ($res = sqlite_query ($dblink, $sql)) { return sqlite_fetch_single($res) > 0; } else { return false; // or throw exception } } } Of course it would be preferable to have these functions included in the library, to avoid potential changes internal to SQLite; but we'll have to stick to this method until then. hunreal+php
Check the db/table if exist $db_name='db'; $db=new SQLiteDatabase($db_name, 0666, $error); if ($error) exit($error); $q=$db->query("PRAGMA table_info(test)"); if ($q->numRows()!=2) { if (!@$db->queryexec(" CREATE TABLE test ( id INTEGER NOT NULL PRIMARY KEY, text VARCHAR ( 255 ) NOT NULL )") ) exit ("Create SQLite Database Error\n"); } 01-sep-2005 12:21
A function to check whether a table exists: <?php function sqlite_table_exists(&$sqlite, $table) { $result = $sqlite->query("SELECT name FROM sqlite_master WHERE type='table' AND name='$table'"); return $result->numRows() > 0; } ?> egon
<$php public function set_by_db_with_join_to_users_department($col, $foo){ $list = "da.ROWID, da.dept_id, da.script, da.action, da.description, ud.name"; $query = "SELECT $list FROM dept_access AS da, users_department AS ud WHERE da.dept_id = ud.ROWID AND da.$col = '$foo';"; $db = new SQLiteDatabase (dept_access::db_path, 0666, &$error) or die("Failed: $error"); $db->queryExec("ATTACH DATABASE '".dept_access::db_users_path."' AS users"); $row = $db->arrayQuery($query, SQLITE_ASSOC); unset($db); echo $query."\n"; echo var_dump($row); $this->ROWID = $row[0]['da.ROWID']; $this->dept_id = $row[0]['da.dept_id']; $this->dept_name = $row[0]['ud.name']; $this->script = $row[0]['da.script']; $this->action = $row[0]['da.action']; $this->description = $row[0]['da.description']; } ?> For ATTACH DATABASE to work you need to run it in a $db->queryExec(ATTACH DATABASE...); before using $db->arrayQuery. This is most likly the case with other OO methods to. This on stumped me for a while. anonymous
Ð”Ð»Ñ Ð¿Ð¾Ð»ÑŒÐ·Ð¾Ð²Ð°Ñ‚ÐµÐ»ÐµÐ¹ руÑÑкой документации. (Ð´Ð»Ñ PHP 5-й верÑии) Ð’ файл php.ini надо добавлÑÑ‚ÑŒ не указанные Ñтроки: php_extension=php_pdo.dll php_extension=php_sqlite.dll а Ñледующие: extension=php_pdo.dll extension=php_sqlite.dll |
Change Language
.NET Functions Apache-specific Functions Alternative PHP Cache Advanced PHP debugger Array Functions Aspell functions [deprecated] BBCode Functions BCMath Arbitrary Precision Mathematics Functions PHP bytecode Compiler Bzip2 Compression Functions Calendar Functions CCVS API Functions [deprecated] Class/Object Functions Classkit Functions ClibPDF Functions [deprecated] COM and .Net (Windows) Crack Functions Character Type Functions CURL Cybercash Payment Functions Credit Mutuel CyberMUT functions Cyrus IMAP administration Functions Date and Time Functions DB++ Functions Database (dbm-style) Abstraction Layer Functions dBase Functions DBM Functions [deprecated] dbx Functions Direct IO Functions Directory Functions DOM Functions DOM XML Functions enchant Functions Error Handling and Logging Functions Exif Functions Expect Functions File Alteration Monitor Functions Forms Data Format Functions Fileinfo Functions filePro Functions Filesystem Functions Filter Functions Firebird/InterBase Functions Firebird/Interbase Functions (PDO_FIREBIRD) FriBiDi Functions FrontBase Functions FTP Functions Function Handling Functions GeoIP Functions Gettext Functions GMP Functions gnupg Functions Net_Gopher Haru PDF Functions hash Functions HTTP Hyperwave Functions Hyperwave API Functions i18n Functions IBM Functions (PDO_IBM) IBM DB2 iconv Functions ID3 Functions IIS Administration Functions Image Functions Imagick Image Library IMAP Informix Functions Informix Functions (PDO_INFORMIX) Ingres II Functions IRC Gateway Functions PHP / Java Integration JSON Functions KADM5 LDAP Functions libxml Functions Lotus Notes Functions LZF Functions Mail Functions Mailparse Functions Mathematical Functions MaxDB PHP Extension MCAL Functions Mcrypt Encryption Functions MCVE (Monetra) Payment Functions Memcache Functions Mhash Functions Mimetype Functions Ming functions for Flash Miscellaneous Functions mnoGoSearch Functions Microsoft SQL Server Functions Microsoft SQL Server and Sybase Functions (PDO_DBLIB) Mohawk Software Session Handler Functions mSQL Functions Multibyte String Functions muscat Functions MySQL Functions MySQL Functions (PDO_MYSQL) MySQL Improved Extension Ncurses Terminal Screen Control Functions Network Functions Newt Functions NSAPI-specific Functions Object Aggregation/Composition Functions Object property and method call overloading Oracle Functions ODBC Functions (Unified) ODBC and DB2 Functions (PDO_ODBC) oggvorbis OpenAL Audio Bindings OpenSSL Functions Oracle Functions [deprecated] Oracle Functions (PDO_OCI) Output Control Functions Ovrimos SQL Functions Paradox File Access Parsekit Functions Process Control Functions Regular Expression Functions (Perl-Compatible) PDF Functions PDO Functions Phar archive stream and classes PHP Options&Information POSIX Functions Regular Expression Functions (POSIX Extended) PostgreSQL Functions PostgreSQL Functions (PDO_PGSQL) Printer Functions Program Execution Functions PostScript document creation Pspell Functions qtdom Functions Radius Rar Functions GNU Readline GNU Recode Functions RPM Header Reading Functions runkit Functions SAM - Simple Asynchronous Messaging Satellite CORBA client extension [deprecated] SCA Functions SDO Functions SDO XML Data Access Service Functions SDO Relational Data Access Service Functions Semaphore SESAM Database Functions PostgreSQL Session Save Handler Session Handling Functions Shared Memory Functions SimpleXML functions SNMP Functions SOAP Functions Socket Functions Standard PHP Library (SPL) Functions SQLite Functions SQLite Functions (PDO_SQLITE) Secure Shell2 Functions Statistics Functions Stream Functions String Functions Subversion Functions Shockwave Flash Functions Swish Functions Sybase Functions TCP Wrappers Functions Tidy Functions Tokenizer Functions Unicode Functions URL Functions Variable Handling Functions Verisign Payflow Pro Functions vpopmail Functions W32api Functions WDDX Functions win32ps Functions win32service Functions xattr Functions xdiff Functions XML Parser Functions XML-RPC Functions XMLReader functions XMLWriter Functions XSL functions XSLT Functions YAZ Functions YP/NIS Functions Zip File Functions Zlib Compression Functions |
