EVOLUTION-MANAGER
Edit File: new-backend.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <meta name="generator" content="pandoc" /> <meta http-equiv="X-UA-Compatible" content="IE=EDGE" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <title>Adding a new DBI backend</title> <script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to // be compatible with the behavior of Pandoc < 2.8). document.addEventListener('DOMContentLoaded', function(e) { var hs = document.querySelectorAll("div.section[class*='level'] > :first-child"); var i, h, a; for (i = 0; i < hs.length; i++) { h = hs[i]; if (!/^h[1-6]$/i.test(h.tagName)) continue; // it should be a header h1-h6 a = h.attributes; while (a.length > 0) h.removeAttribute(a[0].name); } }); </script> <style type="text/css">code{white-space: pre;}</style> <style type="text/css" data-origin="pandoc"> pre > code.sourceCode { white-space: pre; position: relative; } pre > code.sourceCode > span { display: inline-block; line-height: 1.25; } pre > code.sourceCode > span:empty { height: 1.2em; } code.sourceCode > span { color: inherit; text-decoration: inherit; } div.sourceCode { margin: 1em 0; } pre.sourceCode { margin: 0; } @media screen { div.sourceCode { overflow: auto; } } @media print { pre > code.sourceCode { white-space: pre-wrap; } pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; } } pre.numberSource code { counter-reset: source-line 0; } pre.numberSource code > span { position: relative; left: -4em; counter-increment: source-line; } pre.numberSource code > span > a:first-child::before { content: counter(source-line); position: relative; left: -1em; text-align: right; vertical-align: baseline; border: none; display: inline-block; -webkit-touch-callout: none; -webkit-user-select: none; -khtml-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; padding: 0 4px; width: 4em; color: #aaaaaa; } pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; } div.sourceCode { } @media screen { pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; } } code span.al { color: #ff0000; font-weight: bold; } /* Alert */ code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */ code span.at { color: #7d9029; } /* Attribute */ code span.bn { color: #40a070; } /* BaseN */ code span.bu { } /* BuiltIn */ code span.cf { color: #007020; font-weight: bold; } /* ControlFlow */ code span.ch { color: #4070a0; } /* Char */ code span.cn { color: #880000; } /* Constant */ code span.co { color: #60a0b0; font-style: italic; } /* Comment */ code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */ code span.do { color: #ba2121; font-style: italic; } /* Documentation */ code span.dt { color: #902000; } /* DataType */ code span.dv { color: #40a070; } /* DecVal */ code span.er { color: #ff0000; font-weight: bold; } /* Error */ code span.ex { } /* Extension */ code span.fl { color: #40a070; } /* Float */ code span.fu { color: #06287e; } /* Function */ code span.im { } /* Import */ code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */ code span.kw { color: #007020; font-weight: bold; } /* Keyword */ code span.op { color: #666666; } /* Operator */ code span.ot { color: #007020; } /* Other */ code span.pp { color: #bc7a00; } /* Preprocessor */ code span.sc { color: #4070a0; } /* SpecialChar */ code span.ss { color: #bb6688; } /* SpecialString */ code span.st { color: #4070a0; } /* String */ code span.va { color: #19177c; } /* Variable */ code span.vs { color: #4070a0; } /* VerbatimString */ code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */ </style> <script> // apply pandoc div.sourceCode style to pre.sourceCode instead (function() { var sheets = document.styleSheets; for (var i = 0; i < sheets.length; i++) { if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue; try { var rules = sheets[i].cssRules; } catch (e) { continue; } for (var j = 0; j < rules.length; j++) { var rule = rules[j]; // check if there is a div.sourceCode rule if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") continue; var style = rule.style.cssText; // check if color or background-color is set if (rule.style.color === '' && rule.style.backgroundColor === '') continue; // replace div.sourceCode by a pre.sourceCode rule sheets[i].deleteRule(j); sheets[i].insertRule('pre.sourceCode{' + style + '}', j); } } })(); </script> <style type="text/css">body { background-color: #fff; margin: 1em auto; max-width: 700px; overflow: visible; padding-left: 2em; padding-right: 2em; font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 14px; line-height: 1.35; } #TOC { clear: both; margin: 0 0 10px 10px; padding: 4px; width: 400px; border: 1px solid #CCCCCC; border-radius: 5px; background-color: #f6f6f6; font-size: 13px; line-height: 1.3; } #TOC .toctitle { font-weight: bold; font-size: 15px; margin-left: 5px; } #TOC ul { padding-left: 40px; margin-left: -1.5em; margin-top: 5px; margin-bottom: 5px; } #TOC ul ul { margin-left: -2em; } #TOC li { line-height: 16px; } table { margin: 1em auto; border-width: 1px; border-color: #DDDDDD; border-style: outset; border-collapse: collapse; } table th { border-width: 2px; padding: 5px; border-style: inset; } table td { border-width: 1px; border-style: inset; line-height: 18px; padding: 5px 5px; } table, table th, table td { border-left-style: none; border-right-style: none; } table thead, table tr.even { background-color: #f7f7f7; } p { margin: 0.5em 0; } blockquote { background-color: #f6f6f6; padding: 0.25em 0.75em; } hr { border-style: solid; border: none; border-top: 1px solid #777; margin: 28px 0; } dl { margin-left: 0; } dl dd { margin-bottom: 13px; margin-left: 13px; } dl dt { font-weight: bold; } ul { margin-top: 0; } ul li { list-style: circle outside; } ul ul { margin-bottom: 0; } pre, code { background-color: #f7f7f7; border-radius: 3px; color: #333; white-space: pre-wrap; } pre { border-radius: 3px; margin: 5px 0px 10px 0px; padding: 10px; } pre:not([class]) { background-color: #f7f7f7; } code { font-family: Consolas, Monaco, 'Courier New', monospace; font-size: 85%; } p > code, li > code { padding: 2px 0px; } div.figure { text-align: center; } img { background-color: #FFFFFF; padding: 2px; border: 1px solid #DDDDDD; border-radius: 3px; border: 1px solid #CCCCCC; margin: 0 5px; } h1 { margin-top: 0; font-size: 35px; line-height: 40px; } h2 { border-bottom: 4px solid #f7f7f7; padding-top: 10px; padding-bottom: 2px; font-size: 145%; } h3 { border-bottom: 2px solid #f7f7f7; padding-top: 10px; font-size: 120%; } h4 { border-bottom: 1px solid #f7f7f7; margin-left: 8px; font-size: 105%; } h5, h6 { border-bottom: 1px solid #ccc; font-size: 105%; } a { color: #0033dd; text-decoration: none; } a:hover { color: #6666ff; } a:visited { color: #800080; } a:visited:hover { color: #BB00BB; } a[href^="http:"] { text-decoration: underline; } a[href^="https:"] { text-decoration: underline; } code > span.kw { color: #555; font-weight: bold; } code > span.dt { color: #902000; } code > span.dv { color: #40a070; } code > span.bn { color: #d14; } code > span.fl { color: #d14; } code > span.ch { color: #d14; } code > span.st { color: #d14; } code > span.co { color: #888888; font-style: italic; } code > span.ot { color: #007020; } code > span.al { color: #ff0000; font-weight: bold; } code > span.fu { color: #900; font-weight: bold; } code > span.er { color: #a61717; background-color: #e3d2d2; } </style> </head> <body> <h1 class="title toc-ignore">Adding a new DBI backend</h1> <p>This document describes how to add a new SQL backend to dbplyr. To begin:</p> <ul> <li><p>Ensure that you have a DBI compliant database backend. If not, you’ll need to first create it by following the instructions in <code>vignette("backend", package = "DBI")</code>.</p></li> <li><p>You’ll need a working knowledge of S3. Make sure that you’re <a href="http://adv-r.had.co.nz/OO-essentials.html#s3">familiar with the basics</a> before you start.</p></li> </ul> <p>This document is still a work in progress, but it will hopefully get you started. I’d also strongly recommend reading the bundled source code for <a href="https://github.com/tidyverse/dbplyr/blob/master/R/backend-sqlite.R">SQLite</a>, <a href="https://github.com/tidyverse/dbplyr/blob/master/R/backend-mysql.R">MySQL</a>, and <a href="https://github.com/tidyverse/dbplyr/blob/master/R/backend-postgres.R">PostgreSQL</a>.</p> <div id="first-steps" class="section level2"> <h2>First steps</h2> <p>For interactive exploitation, attach dplyr and DBI. If you’re creating a package, you’ll need to import dplyr and DBI.</p> <div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1"></a><span class="kw">library</span>(dplyr)</span> <span id="cb1-2"><a href="#cb1-2"></a><span class="kw">library</span>(DBI)</span></code></pre></div> <p>Check that you can create a tbl from a connection, like:</p> <div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1"></a>con <-<span class="st"> </span>DBI<span class="op">::</span><span class="kw">dbConnect</span>(RSQLite<span class="op">::</span><span class="kw">SQLite</span>(), <span class="dt">path =</span> <span class="st">":memory:"</span>)</span> <span id="cb2-2"><a href="#cb2-2"></a>DBI<span class="op">::</span><span class="kw">dbWriteTable</span>(con, <span class="st">"mtcars"</span>, mtcars)</span> <span id="cb2-3"><a href="#cb2-3"></a></span> <span id="cb2-4"><a href="#cb2-4"></a><span class="kw">tbl</span>(con, <span class="st">"mtcars"</span>)</span> <span id="cb2-5"><a href="#cb2-5"></a><span class="co">#> # Source: table<mtcars> [?? x 11]</span></span> <span id="cb2-6"><a href="#cb2-6"></a><span class="co">#> # Database: sqlite 3.30.1 []</span></span> <span id="cb2-7"><a href="#cb2-7"></a><span class="co">#> mpg cyl disp hp drat wt qsec vs am gear carb</span></span> <span id="cb2-8"><a href="#cb2-8"></a><span class="co">#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl></span></span> <span id="cb2-9"><a href="#cb2-9"></a><span class="co">#> 1 21 6 160 110 3.9 2.62 16.5 0 1 4 4</span></span> <span id="cb2-10"><a href="#cb2-10"></a><span class="co">#> 2 21 6 160 110 3.9 2.88 17.0 0 1 4 4</span></span> <span id="cb2-11"><a href="#cb2-11"></a><span class="co">#> 3 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1</span></span> <span id="cb2-12"><a href="#cb2-12"></a><span class="co">#> 4 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1</span></span> <span id="cb2-13"><a href="#cb2-13"></a><span class="co">#> # … with more rows</span></span></code></pre></div> <p>If you can’t, this likely indicates some problem with the DBI methods. Use <a href="https://github.com/rstats-db/DBItest">DBItest</a> to narrow down the problem.</p> <p>Now is a good time to implement a method for <code>db_desc()</code>. This should briefly describe the connection, typically formatting the information returned from <code>dbGetInfo()</code>. This is what dbplyr does for Postgres connections:</p> <div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1"></a><span class="co">#' @export</span></span> <span id="cb3-2"><a href="#cb3-2"></a>db_desc.PostgreSQLConnection <-<span class="st"> </span><span class="cf">function</span>(x) {</span> <span id="cb3-3"><a href="#cb3-3"></a> info <-<span class="st"> </span><span class="kw">dbGetInfo</span>(x)</span> <span id="cb3-4"><a href="#cb3-4"></a> host <-<span class="st"> </span><span class="cf">if</span> (info<span class="op">$</span>host <span class="op">==</span><span class="st"> ""</span>) <span class="st">"localhost"</span> <span class="cf">else</span> info<span class="op">$</span>host</span> <span id="cb3-5"><a href="#cb3-5"></a></span> <span id="cb3-6"><a href="#cb3-6"></a> <span class="kw">paste0</span>(<span class="st">"postgres "</span>, info<span class="op">$</span>serverVersion, <span class="st">" ["</span>, info<span class="op">$</span>user, <span class="st">"@"</span>,</span> <span id="cb3-7"><a href="#cb3-7"></a> host, <span class="st">":"</span>, info<span class="op">$</span>port, <span class="st">"/"</span>, info<span class="op">$</span>dbname, <span class="st">"]"</span>)</span> <span id="cb3-8"><a href="#cb3-8"></a>}</span></code></pre></div> </div> <div id="copying-computing-collecting-and-collapsing" class="section level2"> <h2>Copying, computing, collecting and collapsing</h2> <p>Next, check that <code>copy_to()</code>, <code>collapse()</code>, <code>compute()</code>, and <code>collect()</code> work.</p> <ul> <li><p>If <code>copy_to()</code> fails, it’s likely you need a method for <code>db_write_table()</code>, <code>db_create_indexes()</code> or <code>db_analyze()</code>.</p></li> <li><p>If <code>collapse()</code> fails, your database has a non-standard way of constructing subqueries. Add a method for <code>sql_subquery()</code>.</p></li> <li><p>If <code>compute()</code> fails, your database has a non-standard way of saving queries in temporary tables. Add a method for <code>db_save_query()</code>.</p></li> </ul> </div> <div id="sql-translation" class="section level2"> <h2>SQL translation</h2> <p>Make sure you’ve read <code>vignette("translation-verb")</code> so you have the lay of the land.</p> <div id="verbs" class="section level3"> <h3>Verbs</h3> <p>Check that SQL translation for the key verbs work:</p> <ul> <li><code>summarise()</code>, <code>mutate()</code>, <code>filter()</code> etc: powered by <code>sql_select()</code></li> <li><code>left_join()</code>, <code>inner_join()</code>: powered by <code>sql_join()</code></li> <li><code>semi_join()</code>, <code>anti_join()</code>: powered by <code>sql_semi_join()</code></li> <li><code>union()</code>, <code>intersect()</code>, <code>setdiff()</code>: powered by <code>sql_set_op()</code></li> </ul> </div> <div id="vectors" class="section level3"> <h3>Vectors</h3> <p>Finally, you may have to provide custom R -> SQL translation at the vector level by providing a method for <code>sql_translate_env()</code>. This function should return an object created by <code>sql_variant()</code>. See existing methods for examples.</p> </div> </div> <!-- code folding --> <!-- dynamically load mathjax for compatibility with self-contained --> <script> (function () { var script = document.createElement("script"); script.type = "text/javascript"; script.src = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"; document.getElementsByTagName("head")[0].appendChild(script); })(); </script> </body> </html>