{ "content": [ { "type": "text", "text": "# DBD::SQLite (perldoc)\n\n## NAME\n\nDBD::SQLite - Self-contained RDBMS in a DBI Driver\n\n## SYNOPSIS\n\nuse DBI;\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=$dbfile\",\"\",\"\");\n\n## DESCRIPTION\n\nSQLite is a public domain file-based relational database engine that you can find at\n.\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **DESCRIPTION**\n- **SQLITE VERSION**\n- **NOTABLE DIFFERENCES FROM OTHER DRIVERS** (12 subsections)\n- **DRIVER PRIVATE ATTRIBUTES** (2 subsections)\n- **METHODS**\n- **DRIVER PRIVATE METHODS** (4 subsections)\n- **DRIVER FUNCTIONS**\n- **DRIVER CONSTANTS** (2 subsections)\n- **COLLATION FUNCTIONS** (5 subsections)\n- **FULLTEXT SEARCH**\n- **VIRTUAL TABLES IMPLEMENTED IN PERL**\n- **TO DO** (3 subsections)\n- **SUPPORT**\n- **AUTHORS**\n- **COPYRIGHT**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n" } ], "structuredContent": { "command": "DBD::SQLite", "section": "", "mode": "perldoc", "summary": "DBD::SQLite - Self-contained RDBMS in a DBI Driver", "synopsis": "use DBI;\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=$dbfile\",\"\",\"\");", "tldr_summary": null, "tldr_examples": [], "tldr_source": null, "flags": [], "examples": [], "see_also": [], "section_outline": [ { "name": "NAME", "lines": 2, "subsections": [] }, { "name": "SYNOPSIS", "lines": 3, "subsections": [] }, { "name": "DESCRIPTION", "lines": 27, "subsections": [] }, { "name": "SQLITE VERSION", "lines": 11, "subsections": [] }, { "name": "NOTABLE DIFFERENCES FROM OTHER DRIVERS", "lines": 1, "subsections": [ { "name": "Database Name Is A File Name", "lines": 30 }, { "name": "Read-Only Database", "lines": 52 }, { "name": "Accessing A Database With Other Tools", "lines": 10 }, { "name": "Blobs", "lines": 22 }, { "name": "Functions And Bind Parameters", "lines": 81 }, { "name": "Placeholders", "lines": 13 }, { "name": "Pragma", "lines": 42 }, { "name": "Foreign Keys", "lines": 21 }, { "name": "Transactions", "lines": 41 }, { "name": "Transaction and Database Locking", "lines": 44 }, { "name": "Processing Multiple Statements At A Time", "lines": 21 }, { "name": "Performance", "lines": 35 } ] }, { "name": "DRIVER PRIVATE ATTRIBUTES", "lines": 1, "subsections": [ { "name": "Database Handle Attributes", "lines": 81 }, { "name": "Statement Handle Attributes", "lines": 4 } ] }, { "name": "METHODS", "lines": 116, "subsections": [] }, { "name": "DRIVER PRIVATE METHODS", "lines": 1, "subsections": [ { "name": "func", "lines": 138 }, { "name": "new", "lines": 4 }, { "name": "step", "lines": 2 }, { "name": "finalize", "lines": 277 } ] }, { "name": "DRIVER FUNCTIONS", "lines": 21, "subsections": [] }, { "name": "DRIVER CONSTANTS", "lines": 15, "subsections": [ { "name": "Authorizer Return Codes", "lines": 4 }, { "name": "Action Codes", "lines": 41 } ] }, { "name": "COLLATION FUNCTIONS", "lines": 1, "subsections": [ { "name": "Definition", "lines": 5 }, { "name": "Builtin collation sequences", "lines": 22 }, { "name": "Usage", "lines": 12 }, { "name": "Unicode handling", "lines": 13 }, { "name": "Adding user-defined collations", "lines": 35 } ] }, { "name": "FULLTEXT SEARCH", "lines": 43, "subsections": [] }, { "name": "VIRTUAL TABLES IMPLEMENTED IN PERL", "lines": 47, "subsections": [] }, { "name": "TO DO", "lines": 2, "subsections": [ { "name": "Leak Detection", "lines": 3 }, { "name": "Stream API for Blobs", "lines": 2 }, { "name": "Support for custom callbacks for R-Tree queries", "lines": 4 } ] }, { "name": "SUPPORT", "lines": 19, "subsections": [] }, { "name": "AUTHORS", "lines": 14, "subsections": [] }, { "name": "COPYRIGHT", "lines": 19, "subsections": [] } ], "sections": { "NAME": { "content": "DBD::SQLite - Self-contained RDBMS in a DBI Driver\n", "subsections": [] }, "SYNOPSIS": { "content": "use DBI;\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=$dbfile\",\"\",\"\");\n", "subsections": [] }, "DESCRIPTION": { "content": "SQLite is a public domain file-based relational database engine that you can find at\n.\n\nDBD::SQLite is a Perl DBI driver for SQLite, that includes the entire thing in the distribution.\nSo in order to get a fast transaction capable RDBMS working for your perl project you simply\nhave to install this module, and nothing else.\n\nSQLite supports the following features:\n\nImplements a large subset of SQL92\nSee for details.\n\nA complete DB in a single disk file\nEverything for your database is stored in a single disk file, making it easier to move\nthings around than with DBD::CSV.\n\nAtomic commit and rollback\nYes, DBD::SQLite is small and light, but it supports full transactions!\n\nExtensible\nUser-defined aggregate or regular functions can be registered with the SQL parser.\n\nThere's lots more to it, so please refer to the docs on the SQLite web page, listed above, for\nSQL details. Also refer to DBI for details on how to use DBI itself. The API works like every\nDBI module does. However, currently many statement attributes are not implemented or are limited\nby the typeless nature of the SQLite database.\n", "subsections": [] }, "SQLITE VERSION": { "content": "DBD::SQLite is usually compiled with a bundled SQLite library (SQLite version 3.36.0 as of this\nrelease) for consistency. However, a different version of SQLite may sometimes be used for some\nreasons like security, or some new experimental features.\n\nYou can look at $DBD::SQLite::sqliteversion (\"3.x.y\" format) or\n$DBD::SQLite::sqliteversionnumber (\"3xxxyyy\" format) to find which version of SQLite is\nactually used. You can also check \"DBD::SQLite::Constants::SQLITEVERSIONNUMBER()\".\n\nYou can also find how the library is compiled by calling \"DBD::SQLite::compileoptions()\" (see\nbelow).\n", "subsections": [] }, "NOTABLE DIFFERENCES FROM OTHER DRIVERS": { "content": "", "subsections": [ { "name": "Database Name Is A File Name", "content": "SQLite creates a file per a database. You should pass the \"path\" of the database file (with or\nwithout a parent directory) in the DBI connection string (as a database \"name\"):\n\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=$dbfile\",\"\",\"\");\n\nThe file is opened in read/write mode, and will be created if it does not exist yet.\n\nAlthough the database is stored in a single file, the directory containing the database file\nmust be writable by SQLite because the library will create several temporary files there.\n\nIf the filename $dbfile is \":memory:\", then a private, temporary in-memory database is created\nfor the connection. This in-memory database will vanish when the database connection is closed.\nIt is handy for your library tests.\n\nNote that future versions of SQLite might make use of additional special filenames that begin\nwith the \":\" character. It is recommended that when a database filename actually does begin with\na \":\" character you should prefix the filename with a pathname such as \"./\" to avoid ambiguity.\n\nIf the filename $dbfile is an empty string, then a private, temporary on-disk database will be\ncreated. This private database will be automatically deleted as soon as the database connection\nis closed.\n\nAs of 1.4101, you can pass URI filename (see ) as well for\nfiner control:\n\nmy $dbh = DBI->connect(\"dbi:SQLite:uri=file:$pathtodbfile?mode=rwc\");\n\nNote that this is not for remote SQLite database connection. You can only connect to a local\ndatabase.\n" }, { "name": "Read-Only Database", "content": "You can set sqliteopenflags (only) when you connect to a database:\n\nuse DBD::SQLite::Constants qw/:fileopen/;\nmy $dbh = DBI->connect(\"dbi:SQLite:$dbfile\", undef, undef, {\nsqliteopenflags => SQLITEOPENREADONLY,\n});\n\nSee for details.\n\nAs of 1.4905, you can also make a database read-only by setting \"ReadOnly\" attribute to true\n(only) when you connect to a database. Actually you can set it after you connect, but in that\ncase, it can't make the database read-only, and you'll see a warning (which you can hide by\nturning \"PrintWarn\" off).\n\nDBD::SQLite And File::Temp\nWhen you use File::Temp to create a temporary file/directory for SQLite databases, you need to\nremember:\n\ntempfile may be locked exclusively\nYou may want to use \"tempfile()\" to create a temporary database filename for DBD::SQLite,\nbut as noted in File::Temp's POD, this file may have an exclusive lock under some operating\nsystems (notably Mac OSX), and result in a \"database is locked\" error. To avoid this, set\nEXLOCK option to false when you call tempfile().\n\n($fh, $filename) = tempfile($template, EXLOCK => 0);\n\nCLEANUP may not work unless a database is disconnected\nWhen you set CLEANUP option to true when you create a temporary directory with \"tempdir()\"\nor \"newdir()\", you may have to disconnect databases explicitly before the temporary\ndirectory is gone (notably under MS Windows).\n\n(The above is quoted from the pod of File::Temp.)\n\nIf you don't need to keep or share a temporary database, use \":memory:\" database instead. It's\nmuch handier and cleaner for ordinary testing.\n\nDBD::SQLite and fork()\nFollow the advice in the SQLite FAQ ().\n\nUnder Unix, you should not carry an open SQLite database across a fork() system call into\nthe child process. Problems will result if you do.\n\nYou shouldn't (re)use a database handle you created (probably to set up a database schema etc)\nbefore you fork(). Otherwise, you might see a database corruption in the worst case.\n\nIf you need to fork(), (re)open a database after you fork(). You might also want to tweak\n\"sqlitebusytimeout\" and \"sqliteuseimmediatetransaction\" (see below), depending on your\nneeds.\n\nIf you need a higher level of concurrency than SQLite supports, consider using other\nclient/server database engines.\n" }, { "name": "Accessing A Database With Other Tools", "content": "To access the database from the command line, try using \"dbish\" which comes with the DBI::Shell\nmodule. Just type:\n\ndbish dbi:SQLite:foo.db\n\nOn the command line to access the file foo.db.\n\nAlternatively you can install SQLite from the link above without conflicting with DBD::SQLite\nand use the supplied \"sqlite3\" command line tool.\n" }, { "name": "Blobs", "content": "As of version 1.11, blobs should \"just work\" in SQLite as text columns. However this will cause\nthe data to be treated as a string, so SQL statements such as length(x) will return the length\nof the column as a NUL terminated string, rather than the size of the blob in bytes. In order to\nstore natively as a BLOB use the following code:\n\nuse DBI qw(:sqltypes);\nmy $dbh = DBI->connect(\"dbi:SQLite:dbfile\",\"\",\"\");\n\nmy $blob = `cat foo.jpg`;\nmy $sth = $dbh->prepare(\"INSERT INTO mytable VALUES (1, ?)\");\n$sth->bindparam(1, $blob, SQLBLOB);\n$sth->execute();\n\nAnd then retrieval just works:\n\n$sth = $dbh->prepare(\"SELECT * FROM mytable WHERE id = 1\");\n$sth->execute();\nmy $row = $sth->fetch;\nmy $blobo = $row->[1];\n\n# now $blobo == $blob\n" }, { "name": "Functions And Bind Parameters", "content": "As of this writing, a SQL that compares a return value of a function with a numeric bind value\nlike this doesn't work as you might expect.\n\nmy $sth = $dbh->prepare(q{\nSELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;\n});\n$sth->execute(5);\n\nThis is because DBD::SQLite assumes that all the bind values are text (and should be quoted) by\ndefault. Thus the above statement becomes like this while executing:\n\nSELECT bar FROM foo GROUP BY bar HAVING count(*) > \"5\";\n\nThere are four workarounds for this.\n\nUse bindparam() explicitly\nAs shown above in the \"BLOB\" section, you can always use \"bindparam()\" to tell the type of\na bind value.\n\nuse DBI qw(:sqltypes); # Don't forget this\n\nmy $sth = $dbh->prepare(q{\nSELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;\n});\n$sth->bindparam(1, 5, SQLINTEGER);\n$sth->execute();\n\nAdd zero to make it a number\nThis is somewhat weird, but works anyway.\n\nmy $sth = $dbh->prepare(q{\nSELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);\n});\n$sth->execute(5);\n\nUse SQL cast() function\nThis is more explicit way to do the above.\n\nmy $sth = $dbh->prepare(q{\nSELECT bar FROM foo GROUP BY bar HAVING count(*) > cast(? as integer);\n});\n$sth->execute(5);\n\nSet \"sqliteseeifitsanumber\" database handle attribute\nAs of version 1.3202, you can use \"sqliteseeifitsanumber\" to let DBD::SQLite to see if\nthe bind values are numbers or not.\n\n$dbh->{sqliteseeifitsanumber} = 1;\nmy $sth = $dbh->prepare(q{\nSELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;\n});\n$sth->execute(5);\n\nYou can set it to true when you connect to a database.\n\nmy $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {\nAutoCommit => 1,\nRaiseError => 1,\nsqliteseeifitsanumber => 1,\n});\n\nThis is the most straightforward solution, but as noted above, existing data in your\ndatabases created by DBD::SQLite have not always been stored as numbers, so this *might*\ncause other obscure problems. Use this sparingly when you handle existing databases. If you\nhandle databases created by other tools like native \"sqlite3\" command line tool, this\nattribute would help you.\n\nAs of 1.4104, \"sqliteseeifitsanumber\" works only for bind values with no explicit\ntype.\n\nmy $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {\nAutoCommit => 1,\nRaiseError => 1,\nsqliteseeifitsanumber => 1,\n});\nmy $sth = $dbh->prepare('INSERT INTO foo VALUES(?)');\n# '1.230' will be inserted as a text, instead of 1.23 as a number,\n# even though sqliteseeifitsanumber is set.\n$sth->bindparam(1, '1.230', SQLVARCHAR);\n$sth->execute;\n" }, { "name": "Placeholders", "content": "SQLite supports several placeholder expressions, including \"?\" and \":AAAA\". Consult the DBI and\nSQLite documentation for details.\n\n\n\nNote that a question mark actually means a next unused (numbered) placeholder. You're advised\nnot to use it with other (numbered or named) placeholders to avoid confusion.\n\nmy $sth = $dbh->prepare(\n'update TABLE set a=?1 where b=?2 and a IS NOT ?1'\n);\n$sth->execute(1, 2);\n" }, { "name": "Pragma", "content": "SQLite has a set of \"Pragma\"s to modify its operation or to query for its internal data. These\nare specific to SQLite and are not likely to work with other DBD libraries, but you may find\nsome of these are quite useful, including:\n\njournalmode\nYou can use this pragma to change the journal mode for SQLite databases, maybe for better\nperformance, or for compatibility.\n\nIts default mode is \"DELETE\", which means SQLite uses a rollback journal to implement\ntransactions, and the journal is deleted at the conclusion of each transaction. If you use\n\"TRUNCATE\" instead of \"DELETE\", the journal will be truncated, which is usually much faster.\n\nA \"WAL\" (write-ahead log) mode is introduced as of SQLite 3.7.0. This mode is persistent,\nand it stays in effect even after closing and reopening the database. In other words, once\nthe \"WAL\" mode is set in an application or in a test script, the database becomes\ninaccessible by older clients. This tends to be an issue when you use a system \"sqlite3\"\nexecutable under a conservative operating system.\n\nTo fix this, You need to issue \"PRAGMA journalmode = DELETE\" (or \"TRUNCATE\") beforehand, or\ninstall a newer version of \"sqlite3\".\n\nlegacyfileformat\nIf you happen to need to create a SQLite database that will also be accessed by a very old\nSQLite client (prior to 3.3.0 released in Jan. 2006), you need to set this pragma to ON\nbefore you create a database.\n\nreverseunorderedselects\nYou can set this pragma to ON to reverse the order of results of SELECT statements without\nan ORDER BY clause so that you can see if applications are making invalid assumptions about\nthe result order.\n\nNote that SQLite 3.7.15 (bundled with DBD::SQLite 1.3802) enhanced its query optimizer and\nthe order of results of a SELECT statement without an ORDER BY clause may be different from\nthe one of the previous versions.\n\nsynchronous\nYou can set set this pragma to OFF to make some of the operations in SQLite faster with a\npossible risk of database corruption in the worst case. See also \"Performance\" section\nbelow.\n\nSee for more details.\n" }, { "name": "Foreign Keys", "content": "SQLite has started supporting foreign key constraints since 3.6.19 (released on Oct 14, 2009;\nbundled in DBD::SQLite 1.2605). To be exact, SQLite has long been able to parse a schema with\nforeign keys, but the constraints has not been enforced. Now you can issue a \"foreignkeys\"\npragma to enable this feature and enforce the constraints, preferably as soon as you connect to\na database and you're not in a transaction:\n\n$dbh->do(\"PRAGMA foreignkeys = ON\");\n\nAnd you can explicitly disable the feature whenever you like by turning the pragma off:\n\n$dbh->do(\"PRAGMA foreignkeys = OFF\");\n\nAs of this writing, this feature is disabled by default by the SQLite team, and by us, to secure\nbackward compatibility, as this feature may break your applications, and actually broke some for\nus. If you have used a schema with foreign key constraints but haven't cared them much and\nsupposed they're always ignored for SQLite, be prepared, and please do extensive testing to\nensure that your applications will continue to work when the foreign keys support is enabled by\ndefault.\n\nSee for details.\n" }, { "name": "Transactions", "content": "DBI/DBD::SQLite's transactions may be a bit confusing. They behave differently according to the\nstatus of the \"AutoCommit\" flag:\n\nWhen the AutoCommit flag is on\nYou're supposed to always use the auto-commit mode, except you explicitly begin a\ntransaction, and when the transaction ended, you're supposed to go back to the auto-commit\nmode. To begin a transaction, call \"beginwork\" method, or issue a \"BEGIN\" statement. To end\nit, call \"commit/rollback\" methods, or issue the corresponding statements.\n\n$dbh->{AutoCommit} = 1;\n\n$dbh->beginwork; # or $dbh->do('BEGIN TRANSACTION');\n\n# $dbh->{AutoCommit} is turned off temporarily during a transaction;\n\n$dbh->commit; # or $dbh->do('COMMIT');\n\n# $dbh->{AutoCommit} is turned on again;\n\nWhen the AutoCommit flag is off\nYou're supposed to always use the transactional mode, until you explicitly turn on the\nAutoCommit flag. You can explicitly issue a \"BEGIN\" statement (only when an actual\ntransaction has not begun yet) but you're not allowed to call \"beginwork\" method (if you\ndon't issue a \"BEGIN\", it will be issued internally). You can commit or roll it back freely.\nAnother transaction will automatically begin if you execute another statement.\n\n$dbh->{AutoCommit} = 0;\n\n# $dbh->do('BEGIN TRANSACTION') is not necessary, but possible\n\n...\n\n$dbh->commit; # or $dbh->do('COMMIT');\n\n# $dbh->{AutoCommit} stays intact;\n\n$dbh->{AutoCommit} = 1; # ends the transactional mode\n\nThis \"AutoCommit\" mode is independent from the autocommit mode of the internal SQLite library,\nwhich always begins by a \"BEGIN\" statement, and ends by a \"COMMIT\" or a \"ROLLBACK\".\n" }, { "name": "Transaction and Database Locking", "content": "The default transaction behavior of SQLite is \"deferred\", that means, locks are not acquired\nuntil the first read or write operation, and thus it is possible that another thread or process\ncould create a separate transaction and write to the database after the \"BEGIN\" on the current\nthread has executed, and eventually cause a \"deadlock\". To avoid this, DBD::SQLite internally\nissues a \"BEGIN IMMEDIATE\" if you begin a transaction by calling \"beginwork\" or by turning off\n\"AutoCommit\" (since 1.3801).\n\nIf you really need to turn off this feature for some reasons, set\n\"sqliteuseimmediatetransaction\" database handle attribute to false, and the default\n\"deferred\" transaction will be used.\n\nmy $dbh = DBI->connect(\"dbi:SQLite::memory:\", \"\", \"\", {\nsqliteuseimmediatetransaction => 0,\n});\n\nOr, issue a \"BEGIN\" statement explicitly each time you begin a transaction.\n\nSee for locking details.\n\n\"$sth->finish\" and Transaction Rollback\nAs the DBI doc says, you almost certainly do not need to call \"finish\" in DBI method if you\nfetch all rows (probably in a loop). However, there are several exceptions to this rule, and\nrolling-back of an unfinished \"SELECT\" statement is one of such exceptional cases.\n\nSQLite prohibits \"ROLLBACK\" of unfinished \"SELECT\" statements in a transaction (See\n for details). So you need to call \"finish\" before you\nissue a rollback.\n\n$sth = $dbh->prepare(\"SELECT * FROM t\");\n$dbh->beginwork;\neval {\n$sth->execute;\n$row = $sth->fetch;\n...\ndie \"For some reason\";\n...\n};\nif($@) {\n$sth->finish; # You need this for SQLite\n$dbh->rollback;\n} else {\n$dbh->commit;\n}\n" }, { "name": "Processing Multiple Statements At A Time", "content": "DBI's statement handle is not supposed to process multiple statements at a time. So if you pass\na string that contains multiple statements (a \"dump\") to a statement handle (via \"prepare\" or\n\"do\"), DBD::SQLite only processes the first statement, and discards the rest.\n\nIf you need to process multiple statements at a time, set a \"sqliteallowmultiplestatements\"\nattribute of a database handle to true when you connect to a database, and \"do\" method takes\ncare of the rest (since 1.3001, and without creating DBI's statement handles internally since\n1.4701). If you do need to use \"prepare\" or \"preparecached\" (which I don't recommend in this\ncase, because typically there's no placeholder nor reusable part in a dump), you can look at\n\"$sth->{sqliteunpreparedstatements}\" to retrieve what's left, though it usually contains\nnothing but white spaces.\n\nTYPE statement attribute\nBecause of historical reasons, DBD::SQLite's \"TYPE\" statement handle attribute returns an array\nref of string values, contrary to the DBI specification. This value is also less useful for\nSQLite users because SQLite uses dynamic type system (that means, the datatype of a value is\nassociated with the value itself, not with its container).\n\nAs of version 1.6102, if you set \"sqliteprefernumerictype\" database handle attribute to\ntrue, \"TYPE\" statement handle attribute returns an array of integer, as an experiment.\n" }, { "name": "Performance", "content": "SQLite is fast, very fast. Matt processed his 72MB log file with it, inserting the data\n(400,000+ rows) by using transactions and only committing every 1000 rows (otherwise the\ninsertion is quite slow), and then performing queries on the data.\n\nQueries like count(*) and avg(bytes) took fractions of a second to return, but what surprised\nhim most of all was:\n\nSELECT url, count(*) as count\nFROM accesslog\nGROUP BY url\nORDER BY count desc\nLIMIT 20\n\nTo discover the top 20 hit URLs on the site (), and it returned within 2\nseconds. He was seriously considering switching his log analysis code to use this little speed\ndemon!\n\nOh yeah, and that was with no indexes on the table, on a 400MHz PIII.\n\nFor best performance be sure to tune your hdparm settings if you are using linux. Also you might\nwant to set:\n\nPRAGMA synchronous = OFF\n\nWhich will prevent SQLite from doing fsync's when writing (which slows down non-transactional\nwrites significantly) at the expense of some peace of mind. Also try playing with the cachesize\npragma.\n\nThe memory usage of SQLite can also be tuned using the cachesize pragma.\n\n$dbh->do(\"PRAGMA cachesize = 800000\");\n\nThe above will allocate 800M for DB cache; the default is 2M. Your sweet spot probably lies\nsomewhere in between.\n" } ] }, "DRIVER PRIVATE ATTRIBUTES": { "content": "", "subsections": [ { "name": "Database Handle Attributes", "content": "sqliteversion\nReturns the version of the SQLite library which DBD::SQLite is using, e.g., \"3.26.0\". Can\nonly be read.\n\nsqlitestringmode\nSQLite strings are simple arrays of bytes, but Perl strings can store any arbitrary Unicode\ncode point. Thus, DBD::SQLite has to adopt some method of translating between those two\nmodels. This parameter defines that translation.\n\nAccepted values are the following constants:\n\n* DBDSQLITESTRINGMODEBYTES: All strings are assumed to represent bytes. A Perl string\nthat contains any code point above 255 will trigger an exception. This is appropriate\nfor Latin-1 strings, binary data, pre-encoded UTF-8 strings, etc.\n\n* DBDSQLITESTRINGMODEUNICODEFALLBACK: All Perl strings are encoded to UTF-8 before\nbeing given to SQLite. Perl will try to decode SQLite strings as UTF-8 when giving them\nto Perl. Should any such string not be valid UTF-8, a warning is thrown, and the string\nis left undecoded.\n\nThis is appropriate for strings that are decoded to characters via, e.g., \"decode\" in\nEncode.\n\nAlso note that, due to some bizarreness in SQLite's type system (see\n), if you want to retain blob-style behavior for\nsome columns under DBDSQLITESTRINGMODEUNICODEFALLBACK (say, to store images in the\ndatabase), you have to state so explicitly using the 3-argument form of \"bindparam\" in\nDBI when doing updates:\n\nuse DBI qw(:sqltypes);\nuse DBD::SQLite::Constants ':dbdsqlitestringmode';\n$dbh->{sqlitestringmode} = DBDSQLITESTRINGMODEUNICODEFALLBACK;\nmy $sth = $dbh->prepare(\"INSERT INTO mytable (blobcolumn) VALUES (?)\");\n\n# Binarydata will be stored as is.\n$sth->bindparam(1, $binarydata, SQLBLOB);\n\nDefining the column type as \"BLOB\" in the DDL is not sufficient.\n\n* DBDSQLITESTRINGMODEUNICODESTRICT: Like DBDSQLITESTRINGMODEUNICODEFALLBACK but\nusually throws an exception rather than a warning if SQLite sends invalid UTF-8. (In\nPerl callbacks from SQLite we still warn instead.)\n\n* DBDSQLITESTRINGMODEUNICODENAIVE: Like DBDSQLITESTRINGMODEUNICODEFALLBACK but\nuses a \"naïve\" UTF-8 decoding method that forgoes validation. This is marginally faster\nthan a validated decode, but it can also corrupt Perl itself!\n\n* DBDSQLITESTRINGMODEPV (default, but DO NOT USE): Like DBDSQLITESTRINGMODEBYTES,\nbut when translating Perl strings to SQLite the Perl string's internal byte buffer is\ngiven to SQLite. This is bad, but it's been the default for many years, and changing\nthat would break existing applications.\n\n\"sqliteunicode\" or \"unicode\" (deprecated)\nIf truthy, equivalent to setting \"sqlitestringmode\" to\nDBDSQLITESTRINGMODEUNICODENAIVE; if falsy, equivalent to DBDSQLITESTRINGMODEPV.\n\nPrefer \"sqlitestringmode\" in all new code.\n\nsqliteallowmultiplestatements\nIf you set this to true, \"do\" method will process multiple statements at one go. This may be\nhandy, but with performance penalty. See above for details.\n\nsqliteuseimmediatetransaction\nIf you set this to true, DBD::SQLite tries to issue a \"begin immediate transaction\" (instead\nof \"begin transaction\") when necessary. See above for details.\n\nAs of version 1.3801, this attribute is set to true by default. If you really need to use\n\"deferred\" transactions for some reasons, set this to false explicitly.\n\nsqliteseeifitsanumber\nIf you set this to true, DBD::SQLite tries to see if the bind values are number or not, and\ndoes not quote if they are numbers. See above for details.\n\nsqliteextendedresultcodes\nIf set to true, DBD::SQLite uses extended result codes where appropriate (see\n).\n\nsqlitedefensive\nIf set to true, language features that allow ordinary SQL to deliberately corrupt the\ndatabase file are prohibited.\n" }, { "name": "Statement Handle Attributes", "content": "sqliteunpreparedstatements\nReturns an unprepared part of the statement you pass to \"prepare\". Typically this contains\nnothing but white spaces after a semicolon. See above for details.\n" } ] }, "METHODS": { "content": "See also to the DBI documentation for the details of other common methods.\n\ntableinfo\n$sth = $dbh->tableinfo(undef, $schema, $table, $type, \\%attr);\n\nReturns all tables and schemas (databases) as specified in \"tableinfo\" in DBI. The schema and\ntable arguments will do a \"LIKE\" search. You can specify an ESCAPE character by including an\n'Escape' attribute in \\%attr. The $type argument accepts a comma separated list of the following\ntypes 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE' (by default all are returned). Note\nthat a statement handle is returned, and not a direct list of tables.\n\nThe following fields are returned:\n\nTABLECAT: Always NULL, as SQLite does not have the concept of catalogs.\n\nTABLESCHEM: The name of the schema (database) that the table or view is in. The default schema\nis 'main', temporary tables are in 'temp' and other databases will be in the name given when the\ndatabase was attached.\n\nTABLENAME: The name of the table or view.\n\nTABLETYPE: The type of object returned. Will be one of 'TABLE', 'VIEW', 'LOCAL TEMPORARY' or\n'SYSTEM TABLE'.\n\nprimarykey, primarykeyinfo\n@names = $dbh->primarykey(undef, $schema, $table);\n$sth = $dbh->primarykeyinfo(undef, $schema, $table, \\%attr);\n\nYou can retrieve primary key names or more detailed information. As noted above, SQLite does not\nhave the concept of catalogs, so the first argument of the methods is usually \"undef\", and\nyou'll usually set \"undef\" for the second one (unless you want to know the primary keys of\ntemporary tables).\n\nforeignkeyinfo\n$sth = $dbh->foreignkeyinfo(undef, $pkschema, $pktable,\nundef, $fkschema, $fktable);\n\nReturns information about foreign key constraints, as specified in \"foreignkeyinfo\" in DBI,\nbut with some limitations :\n\n* information in rows returned by the $sth is incomplete with respect to the\n\"foreignkeyinfo\" in DBI specification. All requested fields are present, but the content\nis \"undef\" for some of them.\n\nThe following nonempty fields are returned :\n\nPKTABLENAME: The primary (unique) key table identifier.\n\nPKCOLUMNNAME: The primary (unique) key column identifier.\n\nFKTABLENAME: The foreign key table identifier.\n\nFKCOLUMNNAME: The foreign key column identifier.\n\nKEYSEQ: The column sequence number (starting with 1), when several columns belong to a same\nconstraint.\n\nUPDATERULE: The referential action for the UPDATE rule. The following codes are defined:\n\nCASCADE 0\nRESTRICT 1\nSET NULL 2\nNO ACTION 3\nSET DEFAULT 4\n\nDefault is 3 ('NO ACTION').\n\nDELETERULE: The referential action for the DELETE rule. The codes are the same as for\nUPDATERULE.\n\nDEFERRABILITY: The following codes are defined:\n\nINITIALLY DEFERRED 5\nINITIALLY IMMEDIATE 6\nNOT DEFERRABLE 7\n\nUNIQUEORPRIMARY: Whether the column is primary or unique.\n\nNote: foreign key support in SQLite must be explicitly turned on through a \"PRAGMA\" command; see\n\"Foreign keys\" earlier in this manual.\n\nstatisticsinfo\n$sth = $dbh->statisticsinfo(undef, $schema, $table,\n$uniqueonly, $quick);\n\nReturns information about a table and it's indexes, as specified in \"statisticsinfo\" in DBI,\nbut with some limitations :\n\n* information in rows returned by the $sth is incomplete with respect to the \"statisticsinfo\"\nin DBI specification. All requested fields are present, but the content is \"undef\" for some\nof them.\n\nThe following nonempty fields are returned :\n\nTABLESCHEM: The name of the schema (database) that the table is in. The default schema is\n'main', temporary tables are in 'temp' and other databases will be in the name given when the\ndatabase was attached.\n\nTABLENAME: The name of the table\n\nNONUNIQUE: Contains 0 for unique indexes, 1 for non-unique indexes\n\nINDEXNAME: The name of the index\n\nTYPE: SQLite uses 'btree' for all it's indexes\n\nORDINALPOSITION: Column sequence number (starting with 1).\n\nCOLUMNNAME: The name of the column\n\nping\nmy $bool = $dbh->ping;\n\nreturns true if the database file exists (or the database is in-memory), and the database\nconnection is active.\n", "subsections": [] }, "DRIVER PRIVATE METHODS": { "content": "The following methods can be called via the func() method with a little tweak, but the use of", "subsections": [ { "name": "func", "content": " for\ndetails). So, if you're using DBI >= 1.608, use these \"sqlite\" methods. If you need to use an\nolder DBI, you can call these like this:\n\n$dbh->func( ..., \"(method name without sqlite prefix)\" );\n\nException: \"sqlitetrace\" should always be called as is, even with \"func()\" method (to avoid\nconflict with DBI's trace() method).\n\n$dbh->func( ..., \"sqlitetrace\");\n\n$dbh->sqlitelastinsertrowid()\nThis method returns the last inserted rowid. If you specify an INTEGER PRIMARY KEY as the first\ncolumn in your table, that is the column that is returned. Otherwise, it is the hidden ROWID\ncolumn. See the SQLite docs for details.\n\nGenerally you should not be using this method. Use the DBI lastinsertid method instead. The\nusage of this is:\n\n$h->lastinsertid($catalog, $schema, $tablename, $fieldname [, \\%attr ])\n\nRunning \"$h->lastinsertid(\"\",\"\",\"\",\"\")\" is the equivalent of running\n\"$dbh->sqlitelastinsertrowid()\" directly.\n\n$dbh->sqlitedbfilename()\nRetrieve the current (main) database filename. If the database is in-memory or temporary, this\nreturns an empty string, or \"undef\".\n\n$dbh->sqlitebusytimeout()\nRetrieve the current busy timeout.\n\n$dbh->sqlitebusytimeout( $ms )\nSet the current busy timeout. The timeout is in milliseconds.\n\n$dbh->sqlitecreatefunction( $name, $argc, $coderef, $flags )\nThis method will register a new function which will be usable in an SQL query. The method's\nparameters are:\n\n$name\nThe name of the function. This is the name of the function as it will be used from SQL.\n\n$argc\nThe number of arguments taken by the function. If this number is -1, the function can take\nany number of arguments.\n\n$coderef\nThis should be a reference to the function's implementation.\n\n$flags\nYou can optionally pass an extra flag bit to createfunction, which then would be ORed with\nSQLITEUTF8 (default). As of 1.4702 (SQLite 3.8.9), only meaning bit is\nSQLITEDETERMINISTIC (introduced at SQLite 3.8.3), which can make the function perform\nbetter. See C API documentation at for\ndetails.\n\nFor example, here is how to define a now() function which returns the current number of seconds\nsince the epoch:\n\n$dbh->sqlitecreatefunction( 'now', 0, sub { return time } );\n\nAfter this, it could be used from SQL as:\n\nINSERT INTO mytable ( now() );\n\nThe function should return a scalar value, and the value is treated as a text (or a number if\nappropriate) by default. If you do need to specify a type of the return value (like BLOB), you\ncan return a reference to an array that contains the value and the type, as of 1.6501.\n\n$dbh->sqlitecreatefunction( 'md5', 1, sub { return [md5($[0]), SQLBLOB] } );\n\nREGEXP function\nSQLite includes syntactic support for an infix operator 'REGEXP', but without any\nimplementation. The \"DBD::SQLite\" driver automatically registers an implementation that performs\nstandard perl regular expression matching, using current locale. So for example you can search\nfor words starting with an 'A' with a query like\n\nSELECT * from table WHERE column REGEXP '\\bA\\w+'\n\nIf you want case-insensitive searching, use perl regex flags, like this :\n\nSELECT * from table WHERE column REGEXP '(?i:\\bA\\w+)'\n\nThe default REGEXP implementation can be overridden through the \"createfunction\" API described\nabove.\n\nNote that regexp matching will not use SQLite indices, but will iterate over all rows, so it\ncould be quite costly in terms of performance.\n\n$dbh->sqlitecreatecollation( $name, $coderef )\nThis method manually registers a new function which will be usable in an SQL query as a COLLATE\noption for sorting. Such functions can also be registered automatically on demand: see section\n\"COLLATION FUNCTIONS\" below.\n\nThe method's parameters are:\n\n$name\nThe name of the function exposed to SQL.\n\n$coderef\nReference to the function's implementation. The driver will check that this is a proper\nsorting function.\n\n$dbh->sqlitecollationneeded( $coderef )\nThis method manually registers a callback function that will be invoked whenever an undefined\ncollation sequence is required from an SQL statement. The callback is invoked as\n\n$coderef->($dbh, $collationname)\n\nand should register the desired collation using \"sqlitecreatecollation\".\n\nAn initial callback is already registered by \"DBD::SQLite\", so for most common cases it will be\nsimpler to just add your collation sequences in the %DBD::SQLite::COLLATION hash (see section\n\"COLLATION FUNCTIONS\" below).\n\n$dbh->sqlitecreateaggregate( $name, $argc, $pkg, $flags )\nThis method will register a new aggregate function which can then be used from SQL. The method's\nparameters are:\n\n$name\nThe name of the aggregate function, this is the name under which the function will be\navailable from SQL.\n\n$argc\nThis is an integer which tells the SQL parser how many arguments the function takes. If that\nnumber is -1, the function can take any number of arguments.\n\n$pkg\nThis is the package which implements the aggregator interface.\n\n$flags\nYou can optionally pass an extra flag bit to createaggregate, which then would be ORed with\nSQLITEUTF8 (default). As of 1.4702 (SQLite 3.8.9), only meaning bit is\nSQLITEDETERMINISTIC (introduced at SQLite 3.8.3), which can make the function perform\nbetter. See C API documentation at for\ndetails.\n\nThe aggregator interface consists of defining three methods:\n" }, { "name": "new", "content": "This method will be called once to create an object which should be used to aggregate the\nrows in a particular group. The step() and finalize() methods will be called upon the\nreference return by the method.\n" }, { "name": "step", "content": "This method will be called once for each row in the aggregate.\n" }, { "name": "finalize", "content": "This method will be called once all rows in the aggregate were processed and it should\nreturn the aggregate function's result. When there is no rows in the aggregate, finalize()\nwill be called right after new().\n\nHere is a simple aggregate function which returns the variance (example adapted from pysqlite):\n\npackage variance;\n\nsub new { bless [], shift; }\n\nsub step {\nmy ( $self, $value ) = @;\n\npush @$self, $value;\n}\n\nsub finalize {\nmy $self = $[0];\n\nmy $n = @$self;\n\n# Variance is NULL unless there is more than one row\nreturn undef unless $n || $n == 1;\n\nmy $mu = 0;\nforeach my $v ( @$self ) {\n$mu += $v;\n}\n$mu /= $n;\n\nmy $sigma = 0;\nforeach my $v ( @$self ) {\n$sigma += ($v - $mu)2;\n}\n$sigma = $sigma / ($n - 1);\n\nreturn $sigma;\n}\n\n$dbh->sqlitecreateaggregate( \"variance\", 1, 'variance' );\n\nThe aggregate function can then be used as:\n\nSELECT groupname, variance(score)\nFROM results\nGROUP BY groupname;\n\nFor more examples, see the DBD::SQLite::Cookbook.\n\n$dbh->sqliteprogresshandler( $nopcodes, $coderef )\nThis method registers a handler to be invoked periodically during long running calls to SQLite.\n\nAn example use for this interface is to keep a GUI updated during a large query. The parameters\nare:\n\n$nopcodes\nThe progress handler is invoked once for every $nopcodes virtual machine opcodes in SQLite.\n\n$coderef\nReference to the handler subroutine. If the progress handler returns non-zero, the SQLite\noperation is interrupted. This feature can be used to implement a \"Cancel\" button on a GUI\ndialog box.\n\nSet this argument to \"undef\" if you want to unregister a previous progress handler.\n\n$dbh->sqlitecommithook( $coderef )\nThis method registers a callback function to be invoked whenever a transaction is committed. Any\ncallback set by a previous call to \"sqlitecommithook\" is overridden. A reference to the\nprevious callback (if any) is returned. Registering an \"undef\" disables the callback.\n\nWhen the commit hook callback returns zero, the commit operation is allowed to continue\nnormally. If the callback returns non-zero, then the commit is converted into a rollback (in\nthat case, any attempt to *explicitly* call \"$dbh->rollback()\" afterwards would yield an error).\n\n$dbh->sqliterollbackhook( $coderef )\nThis method registers a callback function to be invoked whenever a transaction is rolled back.\nAny callback set by a previous call to \"sqliterollbackhook\" is overridden. A reference to the\nprevious callback (if any) is returned. Registering an \"undef\" disables the callback.\n\n$dbh->sqliteupdatehook( $coderef )\nThis method registers a callback function to be invoked whenever a row is updated, inserted or\ndeleted. Any callback set by a previous call to \"sqliteupdatehook\" is overridden. A reference\nto the previous callback (if any) is returned. Registering an \"undef\" disables the callback.\n\nThe callback will be called as\n\n$coderef->($actioncode, $database, $table, $rowid)\n\nwhere\n\n$actioncode\nis an integer equal to either \"DBD::SQLite::INSERT\", \"DBD::SQLite::DELETE\" or\n\"DBD::SQLite::UPDATE\" (see \"Action Codes\");\n\n$database\nis the name of the database containing the affected row;\n\n$table\nis the name of the table containing the affected row;\n\n$rowid\nis the unique 64-bit signed integer key of the affected row within that table.\n\n$dbh->sqlitesetauthorizer( $coderef )\nThis method registers an authorizer callback to be invoked whenever SQL statements are being\ncompiled by the \"prepare\" in DBI method. The authorizer callback should return \"DBD::SQLite::OK\"\nto allow the action, \"DBD::SQLite::IGNORE\" to disallow the specific action but allow the SQL\nstatement to continue to be compiled, or \"DBD::SQLite::DENY\" to cause the entire SQL statement\nto be rejected with an error. If the authorizer callback returns any other value, then \"prepare\"\ncall that triggered the authorizer will fail with an error message.\n\nAn authorizer is used when preparing SQL statements from an untrusted source, to ensure that the\nSQL statements do not try to access data they are not allowed to see, or that they do not try to\nexecute malicious statements that damage the database. For example, an application may allow a\nuser to enter arbitrary SQL queries for evaluation by a database. But the application does not\nwant the user to be able to make arbitrary changes to the database. An authorizer could then be\nput in place while the user-entered SQL is being prepared that disallows everything except\nSELECT statements.\n\nThe callback will be called as\n\n$coderef->($actioncode, $string1, $string2, $database, $triggerorview)\n\nwhere\n\n$actioncode\nis an integer that specifies what action is being authorized (see \"Action Codes\").\n\n$string1, $string2\nare strings that depend on the action code (see \"Action Codes\").\n\n$database\nis the name of the database (\"main\", \"temp\", etc.) if applicable.\n\n$triggerorview\nis the name of the inner-most trigger or view that is responsible for the access attempt, or\n\"undef\" if this access attempt is directly from top-level SQL code.\n\n$dbh->sqlitebackupfromfile( $filename )\nThis method accesses the SQLite Online Backup API, and will take a backup of the named database\nfile, copying it to, and overwriting, your current database connection. This can be particularly\nhandy if your current connection is to the special :memory: database, and you wish to populate\nit from an existing DB.\n\n$dbh->sqlitebackuptofile( $filename )\nThis method accesses the SQLite Online Backup API, and will take a backup of the currently\nconnected database, and write it out to the named file.\n\n$dbh->sqlitebackupfromdbh( $anotherdbh )\nThis method accesses the SQLite Online Backup API, and will take a backup of the database for\nthe passed handle, copying it to, and overwriting, your current database connection. This can be\nparticularly handy if your current connection is to the special :memory: database, and you wish\nto populate it from an existing DB. You can use this to backup from an in-memory database to\nanother in-memory database.\n\n$dbh->sqlitebackuptodbh( $anotherdbh )\nThis method accesses the SQLite Online Backup API, and will take a backup of the currently\nconnected database, and write it out to the passed database handle.\n\n$dbh->sqliteenableloadextension( $bool )\nCalling this method with a true value enables loading (external) SQLite3 extensions. After the\ncall, you can load extensions like this:\n\n$dbh->sqliteenableloadextension(1);\n$sth = $dbh->prepare(\"select loadextension('libsqlitefunctions.so')\")\nor die \"Cannot prepare: \" . $dbh->errstr();\n\n$dbh->sqliteloadextension( $file, $proc )\nLoading an extension by a select statement (with the \"loadextension\" SQLite3 function like\nabove) has some limitations. If you need to, say, create other functions from an extension, use\nthis method. $file (a path to the extension) is mandatory, and $proc (an entry point name) is\noptional. You need to call \"sqliteenableloadextension\" before calling\n\"sqliteloadextension\".\n\nIf the extension uses SQLite mutex functions like \"sqlite3mutexenter\", then the extension\nshould be compiled with the same \"SQLITETHREADSAFE\" compile-time setting as this module, see\n\"DBD::SQLite::compileoptions()\".\n\n$dbh->sqlitetrace( $coderef )\nThis method registers a trace callback to be invoked whenever SQL statements are being run.\n\nThe callback will be called as\n\n$coderef->($statement)\n\nwhere\n\n$statement\nis a UTF-8 rendering of the SQL statement text as the statement first begins executing.\n\nAdditional callbacks might occur as each triggered subprogram is entered. The callbacks for\ntriggers contain a UTF-8 SQL comment that identifies the trigger.\n\nSee also \"TRACING\" in DBI for better tracing options.\n\n$dbh->sqliteprofile( $coderef )\nThis method registers a profile callback to be invoked whenever a SQL statement finishes.\n\nThe callback will be called as\n\n$coderef->($statement, $elapsedtime)\n\nwhere\n\n$statement\nis the original statement text (without bind parameters).\n\n$elapsedtime\nis an estimate of wall-clock time of how long that statement took to run (in milliseconds).\n\nThis method is considered experimental and is subject to change in future versions of SQLite.\n\nSee also DBI::Profile for better profiling options.\n\n$dbh->sqlitetablecolumnmetadata( $dbname, $tablename, $columnname )\nis for internal use only.\n\n$dbh->sqlitedbstatus()\nReturns a hash reference that holds a set of status information of database connection such as\ncache usage. See for details. You may\nalso pass 0 as an argument to reset the status.\n\n$sth->sqliteststatus()\nReturns a hash reference that holds a set of status information of SQLite statement handle such\nas full table scan count. See for\ndetails. Statement status only holds the current value.\n\nmy $status = $sth->sqliteststatus();\nmy $cur = $status->{fullscanstep};\n\nYou may also pass 0 as an argument to reset the status.\n\n$dbh->sqlitedbconfig( $id, $newintegervalue )\nYou can change how the connected database should behave like this:\n\nuse DBD::SQLite::Constants qw/:databaseconnectionconfigurationoptions/;\n\nmy $dbh = DBI->connect('dbi:SQLite::memory:');\n\n# This disables language features that allow ordinary SQL\n# to deliberately corrupt the database file\n$dbh->sqlitedbconfig( SQLITEDBCONFIGDEFENSIVE, 1 );\n\n# This disables two-arg version of fts3tokenizer.\n$dbh->sqlitedbconfig( SQLITEDBCONFIGENABLEFTS3TOKENIZER, 0 );\n\n\"sqlitedbconfig\" returns the new value after the call. If you just want to know the current\nvalue without changing anything, pass a negative integer value.\n\nmy $currentvalue = $dbh->sqlitedbconfig( SQLITEDBCONFIGDEFENSIVE, -1 );\n\nAs of this writing, \"sqlitedbconfig\" only supports options that set an integer value.\n\"SQLITEDBCONFIGLOOKASIDE\" and \"SQLITEDBCONFIGMAINDBNAME\" are not supported. See also\n\"https://www.sqlite.org/capi3ref.html#sqlite3dbconfig\" for details.\n\n$dbh->sqlitecreatemodule()\nRegisters a name for a *virtual table module*. Module names must be registered before creating a\nnew virtual table using the module and before using a preexisting virtual table for the module.\nVirtual tables are explained in DBD::SQLite::VirtualTable.\n\n$dbh->sqlitelimit( $categoryid, $newvalue )\nSets a new run-time limit for the category, and returns the current limit. If the new value is a\nnegative number (or omitted), the limit is unchanged and just returns the current limit.\nCategory ids (SQLITELIMITLENGTH, SQLITELIMITVARIABLENUMBER, etc) can be imported from\nDBD::SQLite::Constants.\n\n$dbh->sqlitegetautocommit()\nReturns true if the internal SQLite connection is in an autocommit mode. This does not always\nreturn the same value as \"$dbh->{AutoCommit}\". This returns false if you explicitly issue a\n\" statement.\n\n$dbh->sqlitetxnstate()\nReturns the internal transaction status of SQLite (not of DBI). Return values (SQLITETXNNONE,\nSQLITETXNREAD, SQLITETXNWRITE) can be imported from DBD::SQLite::Constants. You may pass an\noptional schema name (usually \"main\"). If SQLite does not support this function, or if you pass\na wrong schema name, -1 is returned.\n" } ] }, "DRIVER FUNCTIONS": { "content": "DBD::SQLite::compileoptions()\nReturns an array of compile options (available since SQLite 3.6.23, bundled in DBD::SQLite\n1.3001), or an empty array if the bundled library is old or compiled with\nSQLITEOMITCOMPILEOPTIONDIAGS.\n\nDBD::SQLite::sqlitestatus()\nReturns a hash reference that holds a set of status information of SQLite runtime such as memory\nusage or page cache usage (see for\ndetails). Each of the entry contains the current value and the highwater value.\n\nmy $status = DBD::SQLite::sqlitestatus();\nmy $cur = $status->{memoryused}{current};\nmy $high = $status->{memoryused}{highwater};\n\nYou may also pass 0 as an argument to reset the status.\n\nDBD::SQLite::strlike($pattern, $string, $escapechar), DBD::SQLite::strglob($pattern, $string)\nAs of 1.4905 (SQLite 3.10.0), you can use these two functions to see if a string matches a\npattern. These may be useful when you create a virtual table or a custom function. See\n and for details.\n", "subsections": [] }, "DRIVER CONSTANTS": { "content": "A subset of SQLite C constants are made available to Perl, because they may be needed when\nwriting hooks or authorizer callbacks. For accessing such constants, the \"DBD::SQLite\" module\nmust be explicitly \"use\"d at compile time. For example, an authorizer that forbids any DELETE\noperation would be written as follows :\n\nuse DBD::SQLite;\n$dbh->sqlitesetauthorizer(sub {\nmy $actioncode = shift;\nreturn $actioncode == DBD::SQLite::DELETE ? DBD::SQLite::DENY\n: DBD::SQLite::OK;\n});\n\nThe list of constants implemented in \"DBD::SQLite\" is given below; more information can be found\nad at .\n", "subsections": [ { "name": "Authorizer Return Codes", "content": "OK\nDENY\nIGNORE\n" }, { "name": "Action Codes", "content": "The \"setauthorizer\" method registers a callback function that is invoked to authorize certain\nSQL statement actions. The first parameter to the callback is an integer code that specifies\nwhat action is being authorized. The second and third parameters to the callback are strings,\nthe meaning of which varies according to the action code. Below is the list of action codes,\ntogether with their associated strings.\n\n# constant string1 string2\n# ======== ======= =======\nCREATEINDEX Index Name Table Name\nCREATETABLE Table Name undef\nCREATETEMPINDEX Index Name Table Name\nCREATETEMPTABLE Table Name undef\nCREATETEMPTRIGGER Trigger Name Table Name\nCREATETEMPVIEW View Name undef\nCREATETRIGGER Trigger Name Table Name\nCREATEVIEW View Name undef\nDELETE Table Name undef\nDROPINDEX Index Name Table Name\nDROPTABLE Table Name undef\nDROPTEMPINDEX Index Name Table Name\nDROPTEMPTABLE Table Name undef\nDROPTEMPTRIGGER Trigger Name Table Name\nDROPTEMPVIEW View Name undef\nDROPTRIGGER Trigger Name Table Name\nDROPVIEW View Name undef\nINSERT Table Name undef\nPRAGMA Pragma Name 1st arg or undef\nREAD Table Name Column Name\nSELECT undef undef\nTRANSACTION Operation undef\nUPDATE Table Name Column Name\nATTACH Filename undef\nDETACH Database Name undef\nALTERTABLE Database Name Table Name\nREINDEX Index Name undef\nANALYZE Table Name undef\nCREATEVTABLE Table Name Module Name\nDROPVTABLE Table Name Module Name\nFUNCTION undef Function Name\nSAVEPOINT Operation Savepoint Name\n" } ] }, "COLLATION FUNCTIONS": { "content": "", "subsections": [ { "name": "Definition", "content": "SQLite v3 provides the ability for users to supply arbitrary comparison functions, known as\nuser-defined \"collation sequences\" or \"collating functions\", to be used for comparing two text\nvalues. explains how collations are used in\nvarious SQL expressions.\n" }, { "name": "Builtin collation sequences", "content": "The following collation sequences are builtin within SQLite :\n\nBINARY\nCompares string data using memcmp(), regardless of text encoding.\n\nNOCASE\nThe same as binary, except the 26 upper case characters of ASCII are folded to their lower\ncase equivalents before the comparison is performed. Note that only ASCII characters are\ncase folded. SQLite does not attempt to do full UTF case folding due to the size of the\ntables required.\n\nRTRIM\nThe same as binary, except that trailing space characters are ignored.\n\nIn addition, \"DBD::SQLite\" automatically installs the following collation sequences :\n\nperl\ncorresponds to the Perl \"cmp\" operator\n\nperllocale\nPerl \"cmp\" operator, in a context where \"use locale\" is activated.\n" }, { "name": "Usage", "content": "You can write for example\n\nCREATE TABLE foo(\ntxt1 COLLATE perl,\ntxt2 COLLATE perllocale,\ntxt3 COLLATE nocase\n)\n\nor\n\nSELECT * FROM foo ORDER BY name COLLATE perllocale\n" }, { "name": "Unicode handling", "content": "If the attribute \"$dbh->{sqliteunicode}\" is set, strings coming from the database and passed to\nthe collation function will be properly tagged with the utf8 flag; but this only works if the\n\"sqliteunicode\" attribute is set before the first call to a perl collation sequence . The\nrecommended way to activate unicode is to set the parameter at connection time :\n\nmy $dbh = DBI->connect(\n\"dbi:SQLite:dbname=foo\", \"\", \"\",\n{\nRaiseError => 1,\nsqliteunicode => 1,\n}\n);\n" }, { "name": "Adding user-defined collations", "content": "The native SQLite API for adding user-defined collations is exposed through methods\n\"sqlitecreatecollation\" and \"sqlitecollationneeded\".\n\nTo avoid calling these functions every time a $dbh handle is created, \"DBD::SQLite\" offers a\nsimpler interface through the %DBD::SQLite::COLLATION hash : just insert your own collation\nfunctions in that hash, and whenever an unknown collation name is encountered in SQL, the\nappropriate collation function will be loaded on demand from the hash. For example, here is a\nway to sort text values regardless of their accented characters :\n\nuse DBD::SQLite;\n$DBD::SQLite::COLLATION{noaccents} = sub {\nmy ( $a, $b ) = map lc, @;\ntr[����������������������������]\n[aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;\n$a cmp $b;\n};\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=dbfile\");\nmy $sql = \"SELECT ... FROM ... ORDER BY ... COLLATE noaccents\");\nmy $rows = $dbh->selectallarrayref($sql);\n\nThe builtin \"perl\" or \"perllocale\" collations are predefined in that same hash.\n\nThe COLLATION hash is a global registry within the current process; hence there is a risk of\nundesired side-effects. Therefore, to prevent action at distance, the hash is implemented as a\n\"write-only\" hash, that will happily accept new entries, but will raise an exception if any\nattempt is made to override or delete a existing entry (including the builtin \"perl\" and\n\"perllocale\").\n\nIf you really, really need to change or delete an entry, you can always grab the tied object\nunderneath %DBD::SQLite::COLLATION --- but don't do that unless you really know what you are\ndoing. Also observe that changes in the global hash will not modify existing collations in\nexisting database handles: it will only affect new *requests* for collations. In other words, if\nyou want to change the behaviour of a collation within an existing $dbh, you need to call the\n\"createcollation\" method directly.\n" } ] }, "FULLTEXT SEARCH": { "content": "SQLite is bundled with an extension module for full-text indexing. Tables with this feature\nenabled can be efficiently queried to find rows that contain one or more instances of some\nspecified words, in any column, even if the table contains many large documents.\n\nExplanations for using this feature are provided in a separate document: see\nDBD::SQLite::Fulltextsearch.\n\nR* TREE SUPPORT\nThe RTREE extension module within SQLite adds support for creating a R-Tree, a special index for\nrange and multidimensional queries. This allows users to create tables that can be loaded with\n(as an example) geospatial data such as latitude/longitude coordinates for buildings within a\ncity :\n\nCREATE VIRTUAL TABLE citybuildings USING rtree(\nid, -- Integer primary key\nminLong, maxLong, -- Minimum and maximum longitude\nminLat, maxLat -- Minimum and maximum latitude\n);\n\nthen query which buildings overlap or are contained within a specified region:\n\n# IDs that are contained within query coordinates\nmy $containedsql = <<\"\";\nSELECT id FROM citybuildings\nWHERE minLong >= ? AND maxLong <= ?\nAND minLat >= ? AND maxLat <= ?\n\n# ... and those that overlap query coordinates\nmy $overlapsql = <<\"\";\nSELECT id FROM citybuildings\nWHERE maxLong >= ? AND minLong <= ?\nAND maxLat >= ? AND minLat <= ?\n\nmy $contained = $dbh->selectcolarrayref($containedsql,undef,\n$minLong, $maxLong, $minLat, $maxLat);\n\nmy $overlapping = $dbh->selectcolarrayref($overlapsql,undef,\n$minLong, $maxLong, $minLat, $maxLat);\n\nFor more detail, please see the SQLite R-Tree page (). Note\nthat custom R-Tree queries using callbacks, as mentioned in the prior link, have not been\nimplemented yet.\n", "subsections": [] }, "VIRTUAL TABLES IMPLEMENTED IN PERL": { "content": "SQLite has a concept of \"virtual tables\" which look like regular tables but are implemented\ninternally through specific functions. The fulltext or R* tree features described in the\nprevious chapters are examples of such virtual tables, implemented in C code.\n\n\"DBD::SQLite\" also supports virtual tables implemented in *Perl code*: see\nDBD::SQLite::VirtualTable for using or implementing such virtual tables. These can have many\ninteresting uses for joining regular DBMS data with some other kind of data within your Perl\nprograms. Bundled with the present distribution are :\n\n* DBD::SQLite::VirtualTable::FileContent : implements a virtual column that exposes file\ncontents. This is especially useful in conjunction with a fulltext index; see\nDBD::SQLite::Fulltextsearch.\n\n* DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within the Perl program. This\ncan be used for simple import/export operations, for debugging purposes, for joining data\nfrom different sources, etc.\n\nOther Perl virtual tables may also be published separately on CPAN.\n\nFOR DBD::SQLITE EXTENSION AUTHORS\nSince 1.3001, you can retrieve the bundled SQLite C source and/or header like this:\n\nuse File::ShareDir 'distdir';\nuse File::Spec::Functions 'catfile';\n\n# the whole sqlite3.h header\nmy $sqlite3h = catfile(distdir('DBD-SQLite'), 'sqlite3.h');\n\n# or only a particular header, amalgamated in sqlite3.c\nmy $whatiwant = 'parse.h';\nmy $sqlite3c = catfile(distdir('DBD-SQLite'), 'sqlite3.c');\nopen my $fh, '<', $sqlite3c or die $!;\nmy $code = do { local $/; <$fh> };\nmy ($parseh) = $code =~ m{(\n/\\*+[ ]Begin[ ]file[ ]$whatiwant[ ]\\*+\n.+?\n/\\*+[ ]End[ ]of[ ]$whatiwant[ ]\\*+/\n)}sx;\nopen my $out, '>', $whatiwant or die $!;\nprint $out $parseh;\nclose $out;\n\nYou usually want to use this in your extension's \"Makefile.PL\", and you may want to add\nDBD::SQLite to your extension's \"CONFIGUREREQUIRES\" to ensure your extension users use the same\nC source/header they use to build DBD::SQLite itself (instead of the ones installed in their\nsystem).\n", "subsections": [] }, "TO DO": { "content": "The following items remain to be done.\n", "subsections": [ { "name": "Leak Detection", "content": "Implement one or more leak detection tests that only run during AUTOMATEDTESTING and\nRELEASETESTING and validate that none of the C code we work with leaks.\n" }, { "name": "Stream API for Blobs", "content": "Reading/writing into blobs using \"sqlite2blobopen\" / \"sqlite2blobclose\".\n" }, { "name": "Support for custom callbacks for R-Tree queries", "content": "Custom queries of a R-Tree index using a callback are possible with the SQLite C API\n(), so one could potentially use a callback that narrowed the\nresult set down based on a specific need, such as querying for overlapping circles.\n" } ] }, "SUPPORT": { "content": "Bugs should be reported to GitHub issues:\n\n\n\nor via RT if you prefer:\n\n\n\nNote that bugs of bundled SQLite library (i.e. bugs in \"sqlite3.[ch]\") should be reported to the\nSQLite developers at sqlite.org via their bug tracker or via their mailing list.\n\nThe master repository is on GitHub:\n\n.\n\nWe also have a mailing list:\n\n\n", "subsections": [] }, "AUTHORS": { "content": "Matt Sergeant \n\nFrancis J. Lacoste \n\nWolfgang Sourdeau \n\nAdam Kennedy \n\nMax Maischein \n\nLaurent Dami \n\nKenichi Ishigaki \n", "subsections": [] }, "COPYRIGHT": { "content": "The bundled SQLite code in this distribution is Public Domain.\n\nDBD::SQLite is copyright 2002 - 2007 Matt Sergeant.\n\nSome parts copyright 2008 Francis J. Lacoste.\n\nSome parts copyright 2008 Wolfgang Sourdeau.\n\nSome parts copyright 2008 - 2013 Adam Kennedy.\n\nSome parts copyright 2009 - 2013 Kenichi Ishigaki.\n\nSome parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey Tang.\n\nThis program is free software; you can redistribute it and/or modify it under the same terms as\nPerl itself.\n\nThe full text of the license can be found in the LICENSE file included with this module.\n", "subsections": [] } } } }