CINXE.COM

<!DOCTYPE html> <html lang="en" dir="ltr" class="no-js"> <head> <meta charset="utf-8" /> <title>plugin:sqlite [DokuWiki]</title> <meta name="generator" content="DokuWiki"/> <meta name="theme-color" content="#008800"/> <meta name="robots" content="index,follow"/> <meta name="keywords" content="plugin,sqlite"/> <link rel="search" type="application/opensearchdescription+xml" href="/lib/exe/opensearch.php" title="DokuWiki"/> <link rel="start" href="/"/> <link rel="contents" href="/plugin:sqlite?do=index" title="Sitemap"/> <link rel="manifest" href="/lib/exe/manifest.php"/> <link rel="alternate" type="application/rss+xml" title="Recent Changes" href="/feed.php"/> <link rel="alternate" type="application/rss+xml" title="Current namespace" href="/feed.php?mode=list&ns=plugin"/> <link rel="edit" title="Edit this page" href="/plugin:sqlite?do=edit"/> <link rel="alternate" type="text/html" title="Plain HTML" href="/_export/xhtml/plugin:sqlite"/> <link rel="alternate" type="text/plain" title="Wiki Markup" href="/_export/raw/plugin:sqlite"/> <link rel="canonical" href="https://www.dokuwiki.org/plugin:sqlite"/> <link rel="stylesheet" href="/lib/exe/css.php?t=dokuwiki&tseed=f1005bad3d81fc9c803c7f93d32a390e"/> <script >var NS='plugin';var JSINFO = {"plugins":{"edittable":{"default columnwidth":""}},"id":"plugin:sqlite","namespace":"plugin","ACT":"show","useHeadingNavigation":0,"useHeadingContent":0};(function(H){H.className=H.className.replace(/\bno-js\b/,'js')})(document.documentElement);</script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.4/jquery.min.js" defer="defer"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.13.2/jquery-ui.min.js" defer="defer"></script> <script src="/lib/exe/js.php?t=dokuwiki&tseed=f1005bad3d81fc9c803c7f93d32a390e&lang=en" defer="defer"></script> <meta name="viewport" content="width=device-width,initial-scale=1" /> <link rel="shortcut icon" href="/lib/tpl/dokuwiki/images/favicon.ico" /> <link rel="apple-touch-icon" href="/lib/tpl/dokuwiki/images/apple-touch-icon.png" /> <meta name="verify-v1" content="OVxl3gsCv2MhZqh1cBQyl0JytWXSwXMjyvwc+4w3WtA=" /> <meta name="google-site-verification" content="YhTVK69hW94ZXUtc2zSLPxTkZKbZIn0zK67mz5WQB-E" />  <script async src="https://www.googletagmanager.com/gtag/js?id=UA-83791-1"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-83791-1', { 'anonymize_ip': true }); </script> </head> <body> <div id="dokuwiki__site"><div id="dokuwiki__top" class="site dokuwiki mode_show tpl_dokuwiki showSidebar hasSidebar">  <header id="dokuwiki__header"><div class="pad group"> <div class="headings group"> <ul class="a11y skip"> <li><a href="#dokuwiki__content">skip to content</a></li> </ul> <h1 class="logo"><a href="/start" accesskey="h" title="Home [h]"><img src="/lib/tpl/dokuwiki/images/logo.png" width="64" height="64" alt="" /><span>DokuWiki</span></a></h1> <p class="claim">It's better when it's simple</p> </div> <div class="tools group">  <div id="dokuwiki__usertools"> <h3 class="a11y">User Tools</h3> <ul> <li class="action login"><a href="/plugin:sqlite?do=login&sectok=" title="Log In" rel="nofollow"><span>Log In</span><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24"><path d="M10 17.25V14H3v-4h7V6.75L15.25 12 10 17.25M8 2h9a2 2 0 0 1 2 2v16a2 2 0 0 1-2 2H8a2 2 0 0 1-2-2v-4h2v4h9V4H8v4H6V4a2 2 0 0 1 2-2z"/></svg></a></li> </ul> </div>  <div id="dokuwiki__sitetools"> <h3 class="a11y">Site Tools</h3> <form action="/start" method="get" role="search" class="search doku_form" id="dw__search" accept-charset="utf-8"><input type="hidden" name="do" value="search" /><input type="hidden" name="id" value="plugin:sqlite" /><div class="no"><input name="q" type="text" class="edit" title="[F]" accesskey="f" placeholder="Search" autocomplete="on" id="qsearch__in" value="" /><button value="1" type="submit" title="Search">Search</button><div id="qsearch__out" class="ajax_qsearch JSpopup"></div></div></form> <div class="mobileTools"> <form action="/doku.php" method="get" accept-charset="utf-8"><div class="no"><input type="hidden" name="id" value="plugin:sqlite" /><select name="do" class="edit quickselect" title="Tools"><option value="">Tools</option><optgroup label="Page Tools"><option value="edit">Edit this page</option><option value="revisions">Old revisions</option><option value="backlink">Backlinks</option></optgroup><optgroup label="Site Tools"><option value="recent">Recent Changes</option><option value="media">Media Manager</option><option value="index">Sitemap</option></optgroup><optgroup label="User Tools"><option value="login">Log In</option></optgroup></select><button type="submit">></button></div></form> </div> <ul> <li class="action recent"><a href="/plugin:sqlite?do=recent" title="Recent Changes [r]" rel="nofollow" accesskey="r">Recent Changes</a></li><li class="action media"><a href="/plugin:sqlite?do=media&ns=plugin" title="Media Manager" rel="nofollow">Media Manager</a></li><li class="action index"><a href="/plugin:sqlite?do=index" title="Sitemap [x]" rel="nofollow" accesskey="x">Sitemap</a></li> </ul> </div> </div>  <div class="breadcrumbs"> <div class="trace"><span class="bchead">Trace:</span> <span class="bcsep">•</span> <span class="curid"><bdi><a href="/plugin:sqlite" class="breadcrumbs" title="plugin:sqlite">sqlite</a></bdi></span></div> </div> <hr class="a11y" /> </div></header> <div class="wrapper group">  <nav id="dokuwiki__aside" aria-label="Sidebar"><div class="pad aside include group"> <h3 class="toggle">Sidebar</h3> <div class="content"><div class="group"> <p> <strong>Learn about DokuWiki</strong> </p> <ul> <li class="level1"><div class="li"> <a href="/features" class="wikilink1" title="features" data-wiki-id="features">Features</a> & <a href="/blogroll" class="wikilink1" title="blogroll" data-wiki-id="blogroll">reviews</a> </div> </li> <li class="level1"><div class="li"> <a href="/install" class="wikilink1" title="install" data-wiki-id="install">Installation guide</a></div> </li> <li class="level1"><div class="li"> <a href="/manual" class="wikilink1" title="manual" data-wiki-id="manual">User manual</a> & <a href="/wiki:syntax" class="wikilink1" title="wiki:syntax" data-wiki-id="wiki:syntax">syntax</a></div> </li> <li class="level1"><div class="li"> <a href="/changes" class="wikilink1" title="changes" data-wiki-id="changes">Release notes</a></div> </li> <li class="level1"><div class="li"> <a href="/faq" class="wikilink1" title="faq" data-wiki-id="faq">FAQ</a></div> </li> </ul> <p> <strong>Advanced Use</strong> </p> <ul> <li class="level1"><div class="li"> <a href="/extensions" class="wikilink1" title="extensions" data-wiki-id="extensions">Extensions</a></div> </li> <li class="level1"><div class="li"> <a href="/development" class="wikilink1" title="development" data-wiki-id="development">Development manual</a></div> </li> </ul> <p> <strong>Corporate Use</strong> </p> <ul> <li class="level1"><div class="li"> <a href="/faq:support" class="wikilink1" title="faq:support" data-wiki-id="faq:support">Get support</a></div> </li> <li class="level1"><div class="li"> <a href="/donate" class="wikilink1" title="donate" data-wiki-id="donate">Donations</a></div> </li> </ul> <p> <strong>Our Community</strong> </p> <ul> <li class="level1"><div class="li"> <a href="/teams:getting_involved" class="wikilink1" title="teams:getting_involved" data-wiki-id="teams:getting_involved">Get involved</a></div> </li> <li class="level1"><div class="li"> <a href="/dokuinstall" class="wikilink1" title="dokuinstall" data-wiki-id="dokuinstall">Users</a></div> </li> <li class="level1"><div class="li"> <a href="http://forum.dokuwiki.org" class="urlextern" title="http://forum.dokuwiki.org">User forum</a></div> </li> <li class="level1"><div class="li"> <a href="/mailinglist" class="wikilink1" title="mailinglist" data-wiki-id="mailinglist">Development mailinglist</a></div> </li> </ul> <hr /> <p> Follow us on <a href="https://phpc.social/@dokuwiki" class="urlextern" title="https://phpc.social/@dokuwiki">Mastodon</a> and other <a href="/social" class="wikilink1" title="social" data-wiki-id="social">social networks</a>. </p> <p> <a href="/privacy" class="wikilink1" title="privacy" data-wiki-id="privacy">Our Privacy Policy</a> </p> </div></div> </div></nav>  <main id="dokuwiki__content"><div class="pad group"> <div class="pageId"><span>plugin:sqlite</span></div> <div class="page group">   <div id="dw__toc" class="dw__toc"> <h3 class="toggle">Table of Contents</h3> <div> <ul class="toc"> <li class="level1"><div class="li"><a href="#sqlite_plugin">sqlite Plugin</a></div> <ul class="toc"> <li class="level2"><div class="li"><a href="#requirements">Requirements</a></div></li> <li class="level2"><div class="li"><a href="#download_and_installation">Download and Installation</a></div></li> <li class="level2"><div class="li"><a href="#admin_interface">Admin Interface</a></div></li> <li class="level2"><div class="li"><a href="#developer_documentation">Developer Documentation</a></div> <ul class="toc"> <li class="level3"><div class="li"><a href="#database_setup_and_migration">Database Setup and Migration</a></div></li> <li class="level3"><div class="li"><a href="#public_api">Public API</a></div></li> <li class="level3"><div class="li"><a href="#sql_extensions">SQL Extensions</a></div></li> <li class="level3"><div class="li"><a href="#events">Events</a></div></li> <li class="level3"><div class="li"><a href="#slow_query_log">Slow Query Log</a></div></li> <li class="level3"><div class="li"><a href="#changes_from_earlier_releases">Changes from earlier Releases</a></div></li> </ul></li> </ul></li> </ul> </div> </div>  <h1 class="sectionedit1" id="sqlite_plugin">sqlite Plugin</h1> <div class="level1"> <div class="pluginrepo_entry"><div class="usageInfo"><div class="compatibility"><p class="label">Compatible with DokuWiki</p><div class="versions"><ul><li class="yes">2024-02-06 "Kaos" <strong><span>yes</span></strong></li><li class="yes">2023-04-04 "Jack Jackrum" <strong><span>yes</span></strong></li><li class="yes">2022-07-31 "Igor" <strong><span>yes</span></strong></li><li class="yes">2020-07-29 "Hogfather" <strong><span>yes</span></strong></li></ul></div></div><ul class="actions"><li><a class="download" href="https://github.com/cosmocode/sqlite/tarball/master">Download</a></li><li><a class="bugs" href="https://github.com/cosmocode/sqlite/issues">Report bugs</a></li></ul><div class="clearer"></div></div><div class="mainInfo"><p class="description"><a class="media" href="/plugins"><img alt="plugin" class="medialeft" src="/lib/plugins/pluginrepo/images/dwplugin.png" width="60" height="60" /></a> A helper plugin to easily access a SQLite database</p></div><div class="metaInfo"><dl><dt>Last updated on</dt><dd>2024-06-25</dd><dt>Provides</dt><dd><a href="/plugins?plugintype=16#extension__table" class="wikilink1" title="List all Helper plugins">Helper</a></dd><dt>Repository</dt><dd><a class="urlextern" href="https://github.com/cosmocode/sqlite">Source</a></dd><dt>Conflicts with</dt><dd><a href="/plugin:searchtext" class="wikilink1" title="plugin:searchtext" data-wiki-id="plugin:searchtext">searchtext</a></dd></dl></div><div class="moreInfo"><p class="tags">Tagged with <a href="/plugins?plugintag=database#extension__table" class="wikilink1" title="List all plugins with this tag">database</a>, <a href="/plugins?plugintag=sqlite#extension__table" class="wikilink1" title="List all plugins with this tag">sqlite</a></p><p class="needed">Needed for <a href="/plugin:addressbook" class="wikilink1" title="plugin:addressbook" data-wiki-id="plugin:addressbook">addressbook</a>, <a href="/plugin:aichat" class="wikilink1" title="plugin:aichat" data-wiki-id="plugin:aichat">aichat</a>, <a href="/plugin:approve" class="wikilink1" title="plugin:approve" data-wiki-id="plugin:approve">approve</a>, <a href="/plugin:bez" class="wikilink1" title="plugin:bez" data-wiki-id="plugin:bez">bez</a>, <a href="/plugin:blogtng" class="wikilink1" title="plugin:blogtng" data-wiki-id="plugin:blogtng">blogtng</a>, <a href="/plugin:combo" class="wikilink1" title="plugin:combo" data-wiki-id="plugin:combo">combo</a>, <a href="/plugin:data" class="wikilink1" title="plugin:data" data-wiki-id="plugin:data">data</a>, <a href="/plugin:data-au" class="wikilink1" title="plugin:data-au" data-wiki-id="plugin:data-au">data-au</a>, <a href="/plugin:datagraph" class="wikilink1" title="plugin:datagraph" data-wiki-id="plugin:datagraph">datagraph</a>, <a href="/plugin:dataloop" class="wikilink1" title="plugin:dataloop" data-wiki-id="plugin:dataloop">dataloop</a>, <a href="/plugin:datatemplate" class="wikilink1" title="plugin:datatemplate" data-wiki-id="plugin:datatemplate">datatemplate</a>, <a href="/plugin:davcal" class="wikilink1" title="plugin:davcal" data-wiki-id="plugin:davcal">davcal</a>, <a href="/plugin:davcard" class="wikilink1" title="plugin:davcard" data-wiki-id="plugin:davcard">davcard</a>, <a href="/plugin:do" class="wikilink1" title="plugin:do" data-wiki-id="plugin:do">do</a>, <a href="/plugin:dwcommits" class="wikilink1" title="plugin:dwcommits" data-wiki-id="plugin:dwcommits">dwcommits</a>, <a href="/plugin:extendpage" class="wikilink1" title="plugin:extendpage" data-wiki-id="plugin:extendpage">extendpage</a>, <a href="/plugin:ireadit" class="wikilink1" title="plugin:ireadit" data-wiki-id="plugin:ireadit">ireadit</a>, <a href="/plugin:issuelinks" class="wikilink1" title="plugin:issuelinks" data-wiki-id="plugin:issuelinks">issuelinks</a>, <a href="/plugin:judge" class="wikilink1" title="plugin:judge" data-wiki-id="plugin:judge">judge</a>, <a href="/plugin:labeled" class="wikilink1" title="plugin:labeled" data-wiki-id="plugin:labeled">labeled</a>, <a href="/plugin:notification" class="wikilink1" title="plugin:notification" data-wiki-id="plugin:notification">notification</a>, <a href="/plugin:questionnaire" class="wikilink1" title="plugin:questionnaire" data-wiki-id="plugin:questionnaire">questionnaire</a>, <a href="/plugin:randomtables" class="wikilink1" title="plugin:randomtables" data-wiki-id="plugin:randomtables">randomtables</a>, <a href="/plugin:rating" class="wikilink1" title="plugin:rating" data-wiki-id="plugin:rating">rating</a>, <a href="/plugin:sql2wiki" class="wikilink1" title="plugin:sql2wiki" data-wiki-id="plugin:sql2wiki">sql2wiki</a>, <a href="/plugin:starred" class="wikilink1" title="plugin:starred" data-wiki-id="plugin:starred">starred</a>, <a href="/plugin:struct" class="wikilink1" title="plugin:struct" data-wiki-id="plugin:struct">struct</a>, <a href="/plugin:structnotification" class="wikilink1" title="plugin:structnotification" data-wiki-id="plugin:structnotification">structnotification</a>, <a href="/plugin:structnumber" class="wikilink1" title="plugin:structnumber" data-wiki-id="plugin:structnumber">structnumber</a>, <a href="/plugin:structtasks" class="wikilink1" title="plugin:structtasks" data-wiki-id="plugin:structtasks">structtasks</a>, <a href="/plugin:swarmwebhook" class="wikilink1" title="plugin:swarmwebhook" data-wiki-id="plugin:swarmwebhook">swarmwebhook</a>, <a href="/plugin:tagging" class="wikilink1" title="plugin:tagging" data-wiki-id="plugin:tagging">tagging</a>, <a href="/plugin:telleveryone" class="wikilink1" title="plugin:telleveryone" data-wiki-id="plugin:telleveryone">telleveryone</a>, <a href="/plugin:timetrack" class="wikilink1" title="plugin:timetrack" data-wiki-id="plugin:timetrack">timetrack</a>, <a href="/plugin:top" class="wikilink1" title="plugin:top" data-wiki-id="plugin:top">top</a>, <a href="/plugin:watchcycle" class="wikilink1" title="plugin:watchcycle" data-wiki-id="plugin:watchcycle">watchcycle</a>, <a href="/plugin:webdavclient" class="wikilink1" title="plugin:webdavclient" data-wiki-id="plugin:webdavclient">webdavclient</a></p></div><div class="authorInfo"><strong>By <a href="mailto:dokuwiki%20%5Bat%5D%20cosmocode%20%5Bdot%5D%20de" class="mail" title="dokuwiki [at] cosmocode [dot] de">Andreas Gohr</a></strong><ul><li><a href="/plugin:csv" class="wikilink1" title="plugin:csv" data-wiki-id="plugin:csv">csv</a></li> <li><a href="/plugin:clearhistory" class="wikilink1" title="plugin:clearhistory" data-wiki-id="plugin:clearhistory">clearhistory</a></li> <li><a href="/plugin:preview" class="wikilink1" title="plugin:preview" data-wiki-id="plugin:preview">preview</a></li> <li><a href="/plugin:explain" class="wikilink1" title="plugin:explain" data-wiki-id="plugin:explain">explain</a></li> <li><a href="/plugin:logstats" class="wikilink1" title="plugin:logstats" data-wiki-id="plugin:logstats">logstats</a></li> <li><a href="/plugin:oiddelegate" class="wikilink1" title="plugin:oiddelegate" data-wiki-id="plugin:oiddelegate">oiddelegate</a></li> <li><a href="/plugin:labeled" class="wikilink1" title="plugin:labeled" data-wiki-id="plugin:labeled">labeled</a></li> <li><a href="/plugin:slider" class="wikilink1" title="plugin:slider" data-wiki-id="plugin:slider">slider</a></li> <li><a href="/plugin:groupusers" class="wikilink1" title="plugin:groupusers" data-wiki-id="plugin:groupusers">groupusers</a></li> <li><a href="/plugin:navi" class="wikilink1" title="plugin:navi" data-wiki-id="plugin:navi">navi</a></li> <li>and 126 more</li></ul></div></div> <p> <a href="https://www.cosmocode.de/en/open-source/dokuwiki-plugins/" class="media" title="https://www.cosmocode.de/en/open-source/dokuwiki-plugins/"><img src="/lib/exe/fetch.php?cache=recache&tok=a307b5&media=https%3A%2F%2Fwww.cosmocode.de%2Fstatic%2Fimg%2Fdokuwiki%2Fdwplugins.png" class="mediaright" loading="lazy" title="A CosmoCode Plugin" alt="A CosmoCode Plugin" /></a> </p> <p> This plugin is not useful on its own. Instead it provides mechanisms for other plugins to create, manage and access a SQLite database. You only need to install it if another plugin requires it. </p> </div> <div class="secedit editbutton_section editbutton_1"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[sqlite Plugin] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="sqlite_plugin" /><input type="hidden" name="codeblockOffset" value="0" /><input type="hidden" name="range" value="1-863" /><button type="submit" title="sqlite Plugin">Edit</button></div></form></div> <h2 class="sectionedit2" id="requirements">Requirements</h2> <div class="level2"> <p> This plugin needs <a href="https://en.wikipedia.org/wiki/SQLite" class="interwiki iw_wp" title="https://en.wikipedia.org/wiki/SQLite">SQLite</a> and PDO support in your PHP installation. Be sure the <a href="http://www.php.net/manual/en/ref.pdo-sqlite.php" class="urlextern" title="http://www.php.net/manual/en/ref.pdo-sqlite.php">pdo-sqlite</a> extension is installed and loaded. </p> <p> E.g., on <a href="https://www.debian.org/" class="urlextern" title="https://www.debian.org/"> Debian GNU/Linux</a> be sure to install the <a href="https://packages.debian.org/stable/php-sqlite3" class="urlextern" title="https://packages.debian.org/stable/php-sqlite3"> php-sqlite3</a> package by <code>sudo apt install php-sqlite3</code> which will provide <em>php-pdo-sqlite</em> as appropriate. Afterwards perform a restart to the respective web server (e.g., by <code>sudo systemctl restart apache2</code> if using the <a href="https://en.wikipedia.org/wiki/Apache HTTP Server" class="interwiki iw_wp" title="https://en.wikipedia.org/wiki/Apache HTTP Server">Apache HTTP Server</a>) to make the functionalities available to the DokuWiki engine. </p> </div> <div class="secedit editbutton_section editbutton_2"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Requirements] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="requirements" /><input type="hidden" name="codeblockOffset" value="0" /><input type="hidden" name="range" value="864-1523" /><button type="submit" title="Requirements">Edit</button></div></form></div> <h2 class="sectionedit3" id="download_and_installation">Download and Installation</h2> <div class="level2"> <p> Search and install the plugin using the <a href="/plugin:extension" class="wikilink1" title="plugin:extension" data-wiki-id="plugin:extension">Extension Manager</a>. Refer to <a href="/plugins" class="wikilink1" title="plugins" data-wiki-id="plugins">Plugins</a> on how to install plugins manually. </p> </div> <h4 id="changes">Changes</h4> <div class="level4"> <ul class="rss"><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/aa8878b9678403592bd15ec9b69fad8b5e9b5dc1" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/aa8878b9678403592bd15ec9b69fad8b5e9b5dc1">Merge pull request #92 from dokuwiki-translate/lang_update_939_172526…</a> (2024-09-03 11:03)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/58585cf75988f093ad089f1beff2554ab46f4a4d" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/58585cf75988f093ad089f1beff2554ab46f4a4d">translation update</a> (2024-09-02 09:40)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/62ec89287788c66c58144d767ba27718dc7da192" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/62ec89287788c66c58144d767ba27718dc7da192">Version upped</a> (2024-06-25 23:51)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/c062217a88cb4b90848564d49bd9e29165c01b01" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/c062217a88cb4b90848564d49bd9e29165c01b01">Admin Interface: preserve whitespace in results</a> (2024-06-25 19:54)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/6a7ba7e1b09e18236e3ddbd8ae2e2bddec90305f" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/6a7ba7e1b09e18236e3ddbd8ae2e2bddec90305f">Version upped</a> (2024-03-05 23:51)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/cd6717da22ff2b748ad4145b39604f641db8677c" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/cd6717da22ff2b748ad4145b39604f641db8677c">Merge pull request #88 from cosmocode/bot/autofix</a> (2024-03-05 08:57)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/e062c24746ec4c9c598cea7cb12f0486c953293e" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/e062c24746ec4c9c598cea7cb12f0486c953293e">🤖 Automatic code style fixes</a> (2024-03-05 08:40)</div></li><li><div class="li"><a href="https://github.com/cosmocode/sqlite/commit/78ba3cf28f3e2a6aeffa78edc4c81192a2c13c6e" class="urlextern" title="https://github.com/cosmocode/sqlite/commit/78ba3cf28f3e2a6aeffa78edc4c81192a2c13c6e">Merge pull request #90 from dokuwiki-translate/lang_update_832_170962…</a> (2024-03-05 08:39)</div></li></ul> </div> <div class="secedit editbutton_section editbutton_3"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Download and Installation] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="download_and_installation" /><input type="hidden" name="codeblockOffset" value="0" /><input type="hidden" name="range" value="1524-1788" /><button type="submit" title="Download and Installation">Edit</button></div></form></div> <h2 class="sectionedit4" id="admin_interface">Admin Interface</h2> <div class="level2"> <p> The plugin comes with a simple admin interface that gives you low level access to any of the available databases. This includes: </p> <ul> <li class="level1"><div class="li"> executing arbitrary queries</div> </li> <li class="level1"><div class="li"> run predefined queries</div> </li> <li class="level1"><div class="li"> export and import database contents</div> </li> <li class="level1"><div class="li"> save and recall self-defined queries</div> </li> </ul> <p> To use any of its functionalities, first select the database you want to work on from the Table of Contents. </p> <p> <img src="/lib/images/smileys/exclaim.svg" class="icon smiley" alt=":!:" /> Be sure you know what you're doing. This interface provides no safety nets against deleting or corrupting your database contents! </p> <p> <a href="/lib/exe/fetch.php?tok=0223cd&media=https%3A%2F%2Fi.imgur.com%2FVTpa13C.png" class="media" title="https://i.imgur.com/VTpa13C.png"><img src="/lib/exe/fetch.php?tok=0223cd&media=https%3A%2F%2Fi.imgur.com%2FVTpa13C.png" class="mediacenter" loading="lazy" title=" Admin Interface Screenshot " alt=" Admin Interface Screenshot " /></a> </p> </div> <div class="secedit editbutton_section editbutton_4"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Admin Interface] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="admin_interface" /><input type="hidden" name="codeblockOffset" value="0" /><input type="hidden" name="range" value="1789-2403" /><button type="submit" title="Admin Interface">Edit</button></div></form></div> <h2 class="sectionedit5" id="developer_documentation">Developer Documentation</h2> <div class="level2"> <p> This plugin provides you with all the mechanisms needed to use a sqlite database in your plugin. All functionality is provided through the <code>\dokuwiki\plugin\sqlite\SQLiteDB</code> class. </p> <p> It is recommended to lazy load the class if needed, for example in a <a href="/devel:helper_plugins" class="wikilink1" title="devel:helper_plugins" data-wiki-id="devel:helper_plugins">helper component</a> of your plugin. Since your helper is (usually) a singleton, it will ensure that all your code parts use the same database connection. And using a lazy loading function ensures the connection is only established (and initialized) when actually required. </p> <p> Below you find an example of a helper component that should get you started. </p> <pre class="code php"><span class="kw2"><?php</span>   <span class="kw2">use</span> dokuwiki\ErrorHandler<span class="sy0">;</span> <span class="kw2">use</span> dokuwiki\Extension\Plugin<span class="sy0">;</span> <span class="kw2">use</span> dokuwiki\plugin\sqlite\SQLiteDB<span class="sy0">;</span>     <span class="kw2">class</span> helper_plugin_example <span class="kw2">extends</span> Plugin <span class="br0">{</span> <span class="kw2">protected</span> <span class="re0">$db</span><span class="sy0">;</span>   <span class="co4">/** * Get SQLiteDB instance * * @return SQLiteDB|null */</span> <span class="kw2">public</span> <span class="kw2">function</span> getDB<span class="br0">(</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">if</span> <span class="br0">(</span><span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span> <span class="sy0">===</span> <span class="kw4">null</span><span class="br0">)</span> <span class="br0">{</span> try <span class="br0">{</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span> <span class="sy0">=</span> <span class="kw2">new</span> SQLiteDB<span class="br0">(</span><span class="st_h">'example'</span><span class="sy0">,</span> DOKU_PLUGIN <span class="sy0">.</span> <span class="st_h">'example/db/'</span><span class="br0">)</span><span class="sy0">;</span> <span class="br0">}</span> catch <span class="br0">(</span>\Exception <span class="re0">$exception</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">if</span> <span class="br0">(</span><a href="http://www.php.net/defined"><span class="kw3">defined</span></a><span class="br0">(</span><span class="st_h">'DOKU_UNITTEST'</span><span class="br0">)</span><span class="br0">)</span> <span class="kw1">throw</span> <span class="kw2">new</span> \RuntimeException<span class="br0">(</span><span class="st_h">'Could not load SQLite'</span><span class="sy0">,</span> <span class="nu0">0</span><span class="sy0">,</span> <span class="re0">$exception</span><span class="br0">)</span><span class="sy0">;</span> ErrorHandler<span class="sy0">::</span><span class="me2">logException</span><span class="br0">(</span><span class="re0">$exception</span><span class="br0">)</span><span class="sy0">;</span> msg<span class="br0">(</span><span class="st_h">'Couldn\'t load sqlite.'</span><span class="sy0">,</span> <span class="sy0">-</span><span class="nu0">1</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">return</span> <span class="kw4">null</span><span class="sy0">;</span> <span class="br0">}</span> <span class="br0">}</span> <span class="kw1">return</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">;</span> <span class="br0">}</span>   <span class="co4">/** * @param string $foo * @return array */</span> <span class="kw2">public</span> <span class="kw2">function</span> getAllBazForFoo<span class="br0">(</span><span class="re0">$foo</span><span class="br0">)</span> <span class="br0">{</span> <span class="re0">$db</span> <span class="sy0">=</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">getDB</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">if</span> <span class="br0">(</span><span class="sy0">!</span><span class="re0">$db</span><span class="br0">)</span> <span class="kw1">return</span> <span class="br0">[</span><span class="br0">]</span><span class="sy0">;</span>   <span class="re0">$result</span> <span class="sy0">=</span> <span class="re0">$db</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span><span class="st_h">'SELECT id, meta, baz FROM example WHERE foo = ?'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$foo</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">return</span> <span class="re0">$result</span><span class="sy0">;</span> <span class="br0">}</span>   <span class="br0">}</span></pre> </div> <div class="secedit editbutton_section editbutton_5"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Developer Documentation] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="developer_documentation" /><input type="hidden" name="codeblockOffset" value="0" /><input type="hidden" name="range" value="2404-4155" /><button type="submit" title="Developer Documentation">Edit</button></div></form></div> <h3 class="sectionedit6" id="database_setup_and_migration">Database Setup and Migration</h3> <div class="level3"> <p> As you've seen above, the <strong>constructor</strong> of the <code>SQLiteDB</code> class requires two parameters. A <strong>database name</strong> and a <strong>migration directory</strong>. </p> <p> The database name is the file name your SQLite database. Unless you have a very good reason, this should be the name of your plugin. In the example above, the name is <code>example</code>, resulting in a database file <code>data/meta/example.sqlite3</code> to be created. </p> <p> The second parameter defines the directory where the database migration files are to be found. Most plugins use a <code>db</code> directory in their plugin structure. Use the <code>DOKU_PLUGIN</code> or <code><em class="u">DIR</em></code> constants to create the appropriate absolute path. </p> <p> <a href="/lib/exe/fetch.php?tok=85d252&media=https%3A%2F%2Fi.imgur.com%2FW4Xl6TY.png" class="media" title="https://i.imgur.com/W4Xl6TY.png"><img src="/lib/exe/fetch.php?tok=85d252&media=https%3A%2F%2Fi.imgur.com%2FW4Xl6TY.png" class="mediaright" loading="lazy" title="Migration directory example" alt="Migration directory example" /></a> </p> <p> So what is the migration directory? Your plugin will need to create a database schema and maybe fill it with some initial data. When you release new versions of your plugin you might need to update the schema. This is done by migration files within the specified directory. It is all handled automatically when the constructor is called. </p> <p> Each file correspondents to a certain database version. Version 1 is the very first setup that is done when the database is created. Each subsequent version is then applied above the previous version. </p> <p> The number of the most recent version has to be stored in a file called <code>latest.version</code>. The update files it self have to be named <code>updateXXXX.sql</code> where XXXX is a zero-padded 4 digit number, starting with <code>0001</code>. </p> <p> The update mechanism will execute all SQL queries found in the migration files automatically. The execution of each update is wrapped in a transaction, you should not to do that yourself. If an update fails, the transaction is rolled back and the update is aborted. </p> <p> The sqlite plugin keeps track of the current version of the database using a table called <code>opts</code>. You may not have a table named like that! </p> <p> Note: sometimes migrations are more complex than what can be done with simple SQL queries. You can write your own migration code using an action component and handling the <a href="#plugin_sqlite_database_upgrade" title="plugin:sqlite ↵" class="wikilink1">PLUGIN_SQLITE_DATABASE_UPGRADE</a> event. </p> </div> <div class="secedit editbutton_section editbutton_6"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Database Setup and Migration] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="database_setup_and_migration" /><input type="hidden" name="codeblockOffset" value="1" /><input type="hidden" name="range" value="4156-6300" /><button type="submit" title="Database Setup and Migration">Edit</button></div></form></div> <h3 class="sectionedit7" id="public_api">Public API</h3> <div class="level3"> <p> The following methods are exposed by the <code>SQLiteDB</code> class and can be used in your code. Please note that <code>PDO::ERRMODE_EXCEPTION</code> option of PDO is enabled and all errors will throw Exceptions. Be sure to handle them in your code where necessary. </p> <p> The list below should give you an rough overview on the available methods. For details, please refer to the source code and comments of the class itself: <a href="https://github.com/cosmocode/sqlite/blob/master/SQLiteDB.php" class="urlextern" title="https://github.com/cosmocode/sqlite/blob/master/SQLiteDB.php">SQLiteDB.php</a>. </p> <blockquote><div class="no"> Always use the provided parameter replacement mechanisms when passing user provided data into queries to avoid SQL injection attacks! See <a href="#query" title="plugin:sqlite ↵" class="wikilink1">query()</a> method.</div></blockquote> </div> <h4 id="getpdo">getPdo()</h4> <div class="level4"> <p> This method returns the underlying <a href="https://www.php.net/manual/en/class.pdo.php" class="urlextern" title="https://www.php.net/manual/en/class.pdo.php">PDO object</a> of the connection. You can use it to call all methods defined by the PDO interface. </p> <p> This is useful to manage transactions or register your own functions, for example. </p> </div> <h4 id="query">query()</h4> <div class="level4"> <p> Basic query execution. </p> <p> Placeholder in the query will be replaced by the given replacements. You can either use positional <code>?</code> or named <code>:</code> placeholders. This mechanism is the same in all other query methods below. </p> <p> This method returns a <a href="https://www.php.net/manual/en/class.pdostatement.php" class="urlextern" title="https://www.php.net/manual/en/class.pdostatement.php">PDOStatement</a> object. Be sure to close it's cursor when you're done. You usually want to use one of the convenience methods below. </p> <pre class="code php"><span class="re0">$res</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">query</span><span class="br0">(</span><span class="st_h">'SELECT foo FROM example WHERE baz = ?'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$baz</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="re0">$row</span> <span class="sy0">=</span> <span class="re0">$res</span><span class="sy0">-></span><span class="me1">fetch</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span> <span class="re0">$res</span><span class="sy0">-></span><span class="me1">closeCursor</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span></pre> </div> <h4 id="exec">exec()</h4> <div class="level4"> <p> Similar to query, but used to run data modifying queries. Will return the last insert ID on INSERT statements or the number of affected rows otherwise. </p> <pre class="code php"><span class="re0">$id</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><a href="http://www.php.net/exec"><span class="kw3">exec</span></a><span class="br0">(</span><span class="st_h">'INSERT INTO example (bar) VALUES (?)'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$bar</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">echo</span> <span class="st0">"new ID is <span class="es4">$id</span>"</span><span class="sy0">;</span></pre> </div> <h4 id="queryall">queryAll()</h4> <div class="level4"> <p> Convenience method to execute a query and return all rows as associative arrays. Keep memory limits in mind when querying large data sets! </p> <pre class="code php"><span class="re0">$rows</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span><span class="st_h">'SELECT * FROM example WHERE bar = ?'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$bar</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">foreach</span><span class="br0">(</span><span class="re0">$rows</span> <span class="kw1">as</span> <span class="re0">$row</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">foreach</span><span class="br0">(</span><span class="re0">$row</span> <span class="kw1">as</span> <span class="re0">$col</span> <span class="sy0">=></span> <span class="re0">$val</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">echo</span> <span class="st0">"<span class="es4">$col</span>: <span class="es4">$val</span><span class="es1">\n</span>"</span><span class="sy0">;</span> <span class="br0">}</span> <span class="kw1">echo</span> <span class="st0">"------<span class="es1">\n</span>"</span><span class="sy0">;</span> <span class="br0">}</span></pre> </div> <h4 id="queryrecord">queryRecord()</h4> <div class="level4"> <p> Convenience method to execute a query and return a single row only. </p> <pre class="code php"><span class="re0">$row</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryRecord</span><span class="br0">(</span><span class="st_h">'SELECT * FROM example WHERE bar = ?'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$bar</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">foreach</span><span class="br0">(</span><span class="re0">$row</span> <span class="kw1">as</span> <span class="re0">$col</span> <span class="sy0">=></span> <span class="re0">$val</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">echo</span> <span class="st0">"<span class="es4">$col</span>: <span class="es4">$val</span><span class="es1">\n</span>"</span><span class="sy0">;</span> <span class="br0">}</span></pre> </div> <h4 id="saverecord">saveRecord()</h4> <div class="level4"> <p> Convenience method to insert or replace a single entry in a table. The function expects an associative array for the $data parameter, where the array keys correspond to the column names. </p> <p> If the given data violates a constraint, it is either ignored or the existing data is replaced. </p> <p> Note: this method only provides <code>INSERT OR IGNORE</code> and <code>INSERT OR REPLACE</code> functionality. If you need UPSERT functionality, build your <a href="https://www.sqlite.org/lang_UPSERT.html" class="urlextern" title="https://www.sqlite.org/lang_UPSERT.html">own query</a> and use <a href="#exec" title="plugin:sqlite ↵" class="wikilink1">exec()</a>. </p> <pre class="code php"><span class="re0">$data</span> <span class="sy0">=</span> <span class="br0">[</span> <span class="st_h">'foo'</span> <span class="sy0">=></span> <span class="st_h">'Florb'</span><span class="sy0">,</span> <span class="st_h">'baz'</span> <span class="sy0">=></span> <span class="nu0">12932</span> <span class="br0">]</span><span class="sy0">;</span> <span class="re0">$new</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">saveRecord</span><span class="br0">(</span><span class="st_h">'example'</span><span class="sy0">,</span> <span class="re0">$data</span><span class="br0">)</span><span class="sy0">;</span> <a href="http://www.php.net/var_dump"><span class="kw3">var_dump</span></a><span class="br0">(</span><span class="re0">$new</span><span class="br0">)</span><span class="sy0">;</span></pre> </div> <h4 id="queryvalue">queryValue()</h4> <div class="level4"> <p> Convenience method to query a single value from the database. It will return the first value in the first row of the result set. </p> <pre class="code php"><span class="re0">$foo</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryValue</span><span class="br0">(</span><span class="st_h">'SELECT foo FROM example WHERE bar = ?'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$bar</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">echo</span> <span class="re0">$foo</span><span class="sy0">;</span></pre> </div> <h4 id="querykeyvaluelist">queryKeyValueList()</h4> <div class="level4"> <p> Convenience method to query a key-value list from the database. The first value in each result row becomes the key, the second value the value. You need to take care of key uniqueness yourself, otherwise later values overwrite earlier ones. </p> <pre class="code php"><span class="re0">$res</span> <span class="sy0">=</span> <span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryKeyValueList</span><span class="br0">(</span><span class="st_h">'SELECT foo,baz FROM example WHERE bar = ?'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$bar</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">foreach</span><span class="br0">(</span><span class="re0">$res</span> <span class="kw1">as</span> <span class="re0">$key</span> <span class="sy0">=></span> <span class="re0">$val</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">echo</span> <span class="st0">"<span class="es4">$key</span>: <span class="es4">$val</span><span class="es1">\n</span>"</span><span class="sy0">;</span> <span class="br0">}</span></pre> </div> <div class="secedit editbutton_section editbutton_7"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Public API] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="public_api" /><input type="hidden" name="codeblockOffset" value="1" /><input type="hidden" name="range" value="6301-10091" /><button type="submit" title="Public API">Edit</button></div></form></div> <h3 class="sectionedit8" id="sql_extensions">SQL Extensions</h3> <div class="level3"> <p> SQLite has the unique feature that PHP code can be registered as native SQL functions that then can be used within your queries. The sqlite plugin registers a few custom functions described below. </p> <p> Check the Functions class for details: <a href="https://github.com/cosmocode/sqlite/blob/master/Functions.php" class="urlextern" title="https://github.com/cosmocode/sqlite/blob/master/Functions.php">Functions.php</a>. </p> <p> Your plugin can register it's own functions using the <code>getPDO()->sqliteCreateFunction()</code> and <code>getPDO()->sqliteCreateAggregate()</code>. </p> </div> <h4 id="getaccesslevel">GETACCESSLEVEL</h4> <div class="level4"> <p> Returns the integer DokuWiki permission level of a given pageid. </p> <p> Note: this function will also return AUTH_NONE for hidden pages. </p> <pre class="code php"><span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span> <span class="st_h">'SELECT page, foo, bar FROM example WHERE GETACCESSLEVEL(page) > ?'</span><span class="sy0">,</span> <span class="br0">[</span>AUTH_READ<span class="br0">]</span> <span class="br0">)</span><span class="sy0">;</span></pre> </div> <h4 id="pageexists">PAGEEXISTS</h4> <div class="level4"> <p> Checks if the given pagename exists. Returns <code>1</code> for existing pages, <code>0</code> otherwise. </p> <pre class="code php"><span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span> <span class="st_h">'SELECT page, foo, bar FROM example WHERE PAGEEXISTS(page) = 1'</span> <span class="br0">)</span><span class="sy0">;</span></pre> </div> <h4 id="regexp">REGEXP</h4> <div class="level4"> <p> Matches a value against a given regular expression. The expression is used with <code>/</code> delimiters and the <code>u</code> Unicode modifier. </p> <pre class="code php"><span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span> <span class="st_h">'SELECT page, foo, bar FROM example WHERE REGEXP("^wiki:.*", page)'</span> <span class="br0">)</span><span class="sy0">;</span></pre> </div> <h4 id="resolvepage">RESOLVEPAGE</h4> <div class="level4"> <p> Resolves a given relative page against a given context page. </p> <pre class="code php"><span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span> <span class="st_h">'SELECT RESOLVEPAGE(page, ?), foo, bar FROM example'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$context</span><span class="br0">]</span> <span class="br0">)</span><span class="sy0">;</span></pre> </div> <h4 id="group_concat_distinct">GROUP_CONCAT_DISTINCT</h4> <div class="level4"> <p> This is an aggregate functions that works like the built-in <a href="https://www.sqlite.org/lang_aggfunc.html#group_concat" class="urlextern" title="https://www.sqlite.org/lang_aggfunc.html#group_concat">GROUP_CONCAT</a> function. However it will ensure that only unique values are returned. </p> <p> Note: whenever possible you should use the native method. Unfortunately the native method does not support the DISTINCT keyword when using a custom separator. Only if other options are not feasible, use this registered function. </p> <pre class="code php"><span class="re0">$sqlite</span><span class="sy0">-></span><span class="me1">queryAll</span><span class="br0">(</span> <span class="st_h">'SELECT foo, GROUP_CONCAT_DISTINCT(bar,'</span> <span class="st_h">') FROM example GROUP BY foo'</span><span class="sy0">,</span> <span class="br0">[</span><span class="re0">$context</span><span class="br0">]</span> <span class="br0">)</span><span class="sy0">;</span></pre> </div> <div class="secedit editbutton_section editbutton_8"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[SQL Extensions] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="sql_extensions" /><input type="hidden" name="codeblockOffset" value="8" /><input type="hidden" name="range" value="10092-12130" /><button type="submit" title="SQL Extensions">Edit</button></div></form></div> <h3 class="sectionedit9" id="events">Events</h3> <div class="level3"> <p> The plugin will trigger the following events that your plugin can register for. </p> </div> <h4 id="plugin_sqlite_database_upgrade">PLUGIN_SQLITE_DATABASE_UPGRADE</h4> <div class="level4"> <p> This event is triggered when any new migration (see above) is applied. This makes it possible to do more complex migrations that require more than just SQL. </p> <p> The provided data looks like this: </p> <pre class="code php"><span class="re0">$data</span> <span class="sy0">=</span> <span class="br0">[</span> <span class="st_h">'database'</span> <span class="sy0">=></span> <span class="st_h">'example'</span><span class="sy0">,</span> <span class="co1">// the database name</span> <span class="st_h">'from'</span> <span class="sy0">=></span> <span class="nu0">5</span><span class="sy0">,</span> <span class="co1">// previous version</span> <span class="st_h">'to'</span> <span class="sy0">=></span> <span class="nu0">6</span><span class="sy0">,</span> <span class="co1">// version this migration migrates to</span> <span class="st_h">'file'</span> <span class="sy0">=></span> <span class="sy0">/</span>some<span class="sy0">/</span>path<span class="sy0">/</span>lib<span class="sy0">/</span>plugin<span class="sy0">/</span>example<span class="sy0">/</span>db<span class="sy0">/</span>update0006<span class="sy0">.</span>sql<span class="sy0">,</span> <span class="co1">// the associated update file</span> <span class="st_h">'sqlite'</span> <span class="sy0">=></span> <span class="br0">[</span>object<span class="br0">]</span> <span class="co1">// deprecated instance of the old helper, do not use</span> <span class="st_h">'adapter'</span> <span class="sy0">=></span> SQLiteDB <span class="co1">// instance of the SQLiteDB class</span> <span class="br0">]</span><span class="sy0">;</span></pre> <p> <img src="/lib/images/smileys/exclaim.svg" class="icon smiley" alt=":!:" /> When handling this event, be sure to check that you are only handling your own database upgrade. </p> <p> Here's an example to get you started: </p> <pre class="code php"><span class="kw2">public</span> <span class="kw2">function</span> handle_migrations<span class="br0">(</span>Doku_Event <span class="re0">$event</span><span class="sy0">,</span> <span class="re0">$param</span><span class="br0">)</span> <span class="br0">{</span> <span class="co1">// replace example with name of your plugin</span> <span class="kw1">if</span> <span class="br0">(</span><span class="re0">$event</span><span class="sy0">-></span><span class="me1">data</span><span class="br0">[</span><span class="st_h">'database'</span><span class="br0">]</span> <span class="sy0">!==</span> <span class="st_h">'example'</span><span class="br0">)</span> <span class="br0">{</span> <span class="kw1">return</span><span class="sy0">;</span> <span class="br0">}</span>   <span class="co1">// code to handle update to version 7</span> <span class="kw1">if</span><span class="br0">(</span><span class="re0">$data</span><span class="br0">[</span><span class="st_h">'to'</span><span class="br0">]</span> <span class="sy0">===</span> <span class="nu0">7</span><span class="br0">)</span> <span class="br0">{</span> <span class="re0">$data</span><span class="br0">[</span><span class="st_h">'adapter'</span><span class="br0">]</span><span class="sy0">-></span><a href="http://www.php.net/exec"><span class="kw3">exec</span></a><span class="br0">(</span><span class="st_h">'....'</span><span class="br0">)</span><span class="sy0">;</span> <span class="re0">$event</span><span class="sy0">-></span><span class="me1">result</span> <span class="sy0">=</span> <span class="kw4">true</span><span class="sy0">;</span> <span class="br0">}</span> <span class="br0">}</span></pre> <p> The <code>BEFORE</code> event is executed before the migration file is applied. The default action is to apply that file and increase the <code>dbversion</code> field in the <code>opts</code> table. </p> <p> When an event prevents the default, the proper <code>$event->result</code> has to be set! When the result is true, the <code>dbversion</code> is increased and any additional migrations are run. When the result is false, the migration process is aborted. </p> <p> The <code>BEFORE</code> event can optionally change the <code>file</code> attribute of the data to load a different file. </p> <p> The <code>AFTER</code> event is only triggered after the migration file has been applied or the <code>BEFORE</code> event set a truish result. At the <code>AFTER</code> time, the <code>dbversion</code> has already been increased. </p> <p> <a href="https://codesearch.dokuwiki.org/search?project=dokuwiki&project=plugin&project=template&full=PLUGIN_SQLITE_DATABASE_UPGRADE" class="interwiki iw_codesearch" title="https://codesearch.dokuwiki.org/search?project=dokuwiki&project=plugin&project=template&full=PLUGIN_SQLITE_DATABASE_UPGRADE">Code related to this event</a> used in any files of plugins and templates </p> </div> <h4 id="plugin_sqlite_query_save">PLUGIN_SQLITE_QUERY_SAVE</h4> <div class="level4"> <p> This event is triggered when a query is saved in the admin interface. </p> <p> You can prevent the saving in the BEFORE event, or modify the name or query. </p> <pre class="code php"><span class="re0">$event</span><span class="sy0">-></span><span class="me1">data</span> <span class="sy0">=</span> <span class="br0">[</span> <span class="st_h">'sqlitedb'</span> <span class="sy0">=></span> SQLiteDB<span class="sy0">,</span> <span class="co1">// an instance of the SQLiteDB class for access to the ''sqlite'' database.</span> <span class="st_h">'upstream'</span> <span class="sy0">=></span> <span class="st_h">'example'</span><span class="sy0">,</span> <span class="co1">// the name of the selected database, for which the query is stored</span> <span class="st_h">'name'</span> <span class="sy0">=></span> <span class="sy0">&</span><span class="re0">$name</span><span class="sy0">,</span> <span class="co1">// The name the user picked for this query</span> <span class="st_h">'query'</span> <span class="sy0">=></span> <span class="sy0">&</span><span class="re0">$query</span><span class="sy0">,</span> <span class="co1">// The SQL for this query</span> <span class="br0">]</span></pre> <p> <a href="https://codesearch.dokuwiki.org/search?project=dokuwiki&project=plugin&project=template&full=PLUGIN_SQLITE_QUERY_SAVE" class="interwiki iw_codesearch" title="https://codesearch.dokuwiki.org/search?project=dokuwiki&project=plugin&project=template&full=PLUGIN_SQLITE_QUERY_SAVE">Code related to this event</a> used in any files of plugins and templates </p> </div> <h4 id="plugin_sqlite_query_delete">PLUGIN_SQLITE_QUERY_DELETE</h4> <div class="level4"> <p> This event is triggered when a query is deleted in the admin interface. </p> <p> You can prevent the deletion in the BEFORE event, or modify the name (this would delete a different query). </p> <pre class="code php"><span class="re0">$event</span><span class="sy0">-></span><span class="me1">data</span> <span class="sy0">=</span> <span class="br0">[</span> <span class="st_h">'sqlitedb'</span> <span class="sy0">=></span> SQLiteDB<span class="sy0">,</span> <span class="co1">// an instance of the SQLiteDB class for access to the ''sqlite'' database.</span> <span class="st_h">'upstream'</span> <span class="sy0">=></span> <span class="st_h">'example'</span><span class="sy0">,</span> <span class="co1">// the name of the selected database, for which the query is stored</span> <span class="st_h">'name'</span> <span class="sy0">=></span> <span class="sy0">&</span><span class="re0">$name</span><span class="sy0">,</span> <span class="co1">// The name the user picked for this query</span> <span class="br0">]</span></pre> <p> <a href="https://codesearch.dokuwiki.org/search?project=dokuwiki&project=plugin&project=template&full=PLUGIN_SQLITE_QUERY_DELETE" class="interwiki iw_codesearch" title="https://codesearch.dokuwiki.org/search?project=dokuwiki&project=plugin&project=template&full=PLUGIN_SQLITE_QUERY_DELETE">Code related to this event</a> used in any files of plugins and templates </p> </div> <div class="secedit editbutton_section editbutton_9"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Events] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="events" /><input type="hidden" name="codeblockOffset" value="13" /><input type="hidden" name="range" value="12131-15486" /><button type="submit" title="Events">Edit</button></div></form></div> <h3 class="sectionedit10" id="slow_query_log">Slow Query Log</h3> <div class="level3"> <p> When debug logging is enabled (see <a href="/config:dontlog" class="wikilink1" title="config:dontlog" data-wiki-id="config:dontlog">dontlog</a> option), queries that take longer than 0.2 seconds will be logged as slow. Use this to optimize and debug your queries. </p> </div> <div class="secedit editbutton_section editbutton_10"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Slow Query Log] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="slow_query_log" /><input type="hidden" name="codeblockOffset" value="17" /><input type="hidden" name="range" value="15487-15687" /><button type="submit" title="Slow Query Log">Edit</button></div></form></div> <h3 class="sectionedit11" id="changes_from_earlier_releases">Changes from earlier Releases</h3> <div class="level3"> <p> In July 2023 a completely refactored version was released that introduced the <code>SQLiteDB</code> class in favor of an <code>helper_plugin_sqlite</code>. The latter is still provided and internally uses the <code>SQLiteDB</code> class. This means the new plugin should be fully backwards compatible with older plugins. </p> <p> Discussions in the appropriate <a href="https://github.com/cosmocode/sqlite/pull/73" class="urlextern" title="https://github.com/cosmocode/sqlite/pull/73">Pull Request</a> might be instructive. </p> <p> However minor things might work slightly differently. Most important is that the previously custom registered GROUP_CONCAT method was renamed to GROUP_CONCAT_DISTINCT. Please see it's <a href="#group_concat_distinct" title="plugin:sqlite ↵" class="wikilink1">documentation</a> above and adjust your plugin if needed. </p> <p> Plugin developers are advised to update their plugin to make use of the newer, cleaner SQLiteDB interface. The helper is now deprecated. </p> </div> <div class="secedit editbutton_section editbutton_11"><form class="button btn_secedit" method="post" action="/plugin:sqlite"><div class="no"><input type="hidden" name="do" value="edit" /><input type="hidden" name="rev" value="1719352312" /><input type="hidden" name="summary" value="[Changes from earlier Releases] " /><input type="hidden" name="target" value="section" /><input type="hidden" name="hid" value="changes_from_earlier_releases" /><input type="hidden" name="codeblockOffset" value="17" /><input type="hidden" name="range" value="15688-" /><button type="submit" title="Changes from earlier Releases">Edit</button></div></form></div>  </div> <div class="docInfo"><bdi>plugin/sqlite.txt</bdi> · Last modified: <time datetime="2024-06-25T23:51:52+0200">2024-06-25 23:51</time> by <bdi>andi</bdi></div> <hr class="a11y" /> </div></main>  <nav id="dokuwiki__pagetools" aria-labelledby="dokuwiki__pagetools__heading"> <h3 class="a11y" id="dokuwiki__pagetools__heading">Page Tools</h3> <div class="tools"> <ul> <li class="edit"><a href="/plugin:sqlite?do=edit" title="Edit this page [e]" rel="nofollow" accesskey="e"><span>Edit this page</span><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24"><path d="M20.71 7.04c.39-.39.39-1.04 0-1.41l-2.34-2.34c-.37-.39-1.02-.39-1.41 0l-1.84 1.83 3.75 3.75M3 17.25V21h3.75L17.81 9.93l-3.75-3.75L3 17.25z"/></svg></a></li><li class="revs"><a href="/plugin:sqlite?do=revisions" title="Old revisions [o]" rel="nofollow" accesskey="o"><span>Old revisions</span><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24"><path d="M11 7v5.11l4.71 2.79.79-1.28-4-2.37V7m0-5C8.97 2 5.91 3.92 4.27 6.77L2 4.5V11h6.5L5.75 8.25C6.96 5.73 9.5 4 12.5 4a7.5 7.5 0 0 1 7.5 7.5 7.5 7.5 0 0 1-7.5 7.5c-3.27 0-6.03-2.09-7.06-5h-2.1c1.1 4.03 4.77 7 9.16 7 5.24 0 9.5-4.25 9.5-9.5A9.5 9.5 0 0 0 12.5 2z"/></svg></a></li><li class="backlink"><a href="/plugin:sqlite?do=backlink" title="Backlinks" rel="nofollow"><span>Backlinks</span><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24"><path d="M10.59 13.41c.41.39.41 1.03 0 1.42-.39.39-1.03.39-1.42 0a5.003 5.003 0 0 1 0-7.07l3.54-3.54a5.003 5.003 0 0 1 7.07 0 5.003 5.003 0 0 1 0 7.07l-1.49 1.49c.01-.82-.12-1.64-.4-2.42l.47-.48a2.982 2.982 0 0 0 0-4.24 2.982 2.982 0 0 0-4.24 0l-3.53 3.53a2.982 2.982 0 0 0 0 4.24m2.82-4.24c.39-.39 1.03-.39 1.42 0a5.003 5.003 0 0 1 0 7.07l-3.54 3.54a5.003 5.003 0 0 1-7.07 0 5.003 5.003 0 0 1 0-7.07l1.49-1.49c-.01.82.12 1.64.4 2.43l-.47.47a2.982 2.982 0 0 0 0 4.24 2.982 2.982 0 0 0 4.24 0l3.53-3.53a2.982 2.982 0 0 0 0-4.24.973.973 0 0 1 0-1.42z"/></svg></a></li><li class="top"><a href="#dokuwiki__top" title="Back to top [t]" rel="nofollow" accesskey="t"><span>Back to top</span><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24"><path d="M13 20h-2V8l-5.5 5.5-1.42-1.42L12 4.16l7.92 7.92-1.42 1.42L13 8v12z"/></svg></a></li> </ul> </div> </nav> </div>  <footer id="dokuwiki__footer"><div class="pad"> <div class="license">Except where otherwise noted, content on this wiki is licensed under the following license: <bdi><a href="https://creativecommons.org/licenses/by-sa/4.0/deed.en" rel="license" class="urlextern">CC Attribution-Share Alike 4.0 International</a></bdi></div> <div class="buttons"> <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.en" rel="license"><img src="/lib/images/license/button/cc-by-sa.png" alt="CC Attribution-Share Alike 4.0 International" /></a> <a href="https://www.dokuwiki.org/donate" title="Donate" ><img src="/lib/tpl/dokuwiki/images/button-donate.gif" width="80" height="15" alt="Donate" /></a> <a href="https://php.net" title="Powered by PHP" ><img src="/lib/tpl/dokuwiki/images/button-php.gif" width="80" height="15" alt="Powered by PHP" /></a> <a href="//validator.w3.org/check/referer" title="Valid HTML5" ><img src="/lib/tpl/dokuwiki/images/button-html5.png" width="80" height="15" alt="Valid HTML5" /></a> <a href="//jigsaw.w3.org/css-validator/check/referer?profile=css3" title="Valid CSS" ><img src="/lib/tpl/dokuwiki/images/button-css.png" width="80" height="15" alt="Valid CSS" /></a> <a href="https://dokuwiki.org/" title="Driven by DokuWiki" ><img src="/lib/tpl/dokuwiki/images/button-dw.png" width="80" height="15" alt="Driven by DokuWiki" /></a> </div> <div style="margin-top: 2em; font-size:90%" class="dokuwiki"><div style="float:right"><a href="http://www.splitbrain.org/personal#imprint" style="float:right; text-decoration: none; color:#333">Imprint</a></div></div> <style type="text/css"> @media screen { body { padding-top: 30px; } #global__header { position: absolute; top: 0; left: 0; text-align: left; vertical-align: middle; line-height: 1.5; background-color: #333; box-shadow: 0 0 8px rgba(0,0,0,0.5); width: 100%; margin: 0; padding: 5px 20px; -moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box; white-space: nowrap; overflow: hidden; } #global__header h2 { position: absolute; left: -99999em; top: 0; overflow: hidden; display: inline; } #global__header ul, #global__header li { margin: 0; padding: 0; list-style: none; display: inline; line-height: 1.5; } #global__header a { color: #bbb; text-decoration: none; margin-right: 20px; font-size: 14px; font-weight: normal; } #global__header a:hover, #global__header a:active, #global__header a:focus { color: #fff; text-decoration: underline; } #global__header form { float: right; margin: 0 0 0 20px; } #global__header input { background-color: #333; background-image: none; border: 1px solid #bbb; color: #fff; box-shadow: none; border-radius: 2px; margin: 0; line-height: normal; padding: 1px 0 1px 0; height: auto; } #global__header input.button { border: none; color: #bbb; } #global__header input.button:hover, #global__header input.button:active, #global__header input.button:focus { color: #fff; text-decoration: underline; } } /* /@media */ @media only screen and (min-width: 601px) { /* changes specific for www.dokuwiki.org */ #dokuwiki__header { padding-top: 3em; } #dokuwiki__usertools { top: 3em; } /* changes specific for bugs.dokuwiki.org */ div#container div#showtask { top: 40px; } } /* /@media */ @media only screen and (max-width: 600px) { body { padding-top: 0; } #global__header { position: static; white-space: normal; overflow: auto; } #global__header form { float: none; display: block; margin: 0 0 .4em; } } /* /@media */ @media print { #global__header { display: none; } } /* /@media */ </style> <div id="global__header"> <h2>Global DokuWiki Links</h2> <form method="get" action="https://search.dokuwiki.org/" target="_top"> <input type="text" name="q" title="Search all DokuWiki sites at once" class="input" /> <input type="submit" title="Search all DokuWiki sites at once" value="Search" class="button" /> </form> <ul> <li><a href="https://download.dokuwiki.org" title="Download the latest release" target="_top">Download</a></li> <li><a href="https://www.dokuwiki.org" title="Read the DokuWiki documentation" target="_top">Wiki</a></li> <li><a href="https://forum.dokuwiki.org" title="Ask questions in the DokuWiki forum" target="_top">Forum</a></li> <li><a href="https://irc.dokuwiki.org" title="Check IRC chat logs or join the chat" target="_top">IRC</a></li> <li><a href="https://github.com/splitbrain/dokuwiki/issues" title="Report and track bugs" target="_top">Bugs</a></li> <li><a href="https://translate.dokuwiki.org/" title="Help translating the DokuWiki interface" target="_top">Translate</a></li> <li><a href="https://github.com/splitbrain/dokuwiki" title="Access the most recent git commits" target="_top">Git</a></li> <li><a href="https://xref.dokuwiki.org/reference/dokuwiki/" title="Cross-Reference of the DokuWiki source code" target="_top">XRef</a></li> <li><a href="https://codesearch.dokuwiki.org/" title="Search through the sources of DokuWiki, plugins and templates" target="_top">Code Search</a></li> </ul> </div>  </div></footer> </div></div> <div class="no"><img src="/lib/exe/taskrunner.php?id=plugin%3Asqlite&1742563851" width="2" height="1" alt="" /></div> <div id="screen__mode" class="no"></div></body> </html>