<!DOCTYPE html>
<html lang="en-US">

  <head>
    <meta charset='utf-8'>
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width,maximum-scale=2">
    <link rel="stylesheet" href="assets/css/style.css?v=">
    <script src="https://code.jquery.com/jquery-3.3.0.min.js" integrity="sha256-RTQy8VOmNlT6b2PIRur37p6JEBZUE7o8wPgMvu18MC4=" crossorigin="anonymous"></script>
    <script src="assets/js/main.js"></script>
<!-- Begin Jekyll SEO tag v2.6.1 -->
<title>BIRDS | Bidirectional Transformation for Relational View Update Datalog-based Strategies</title>
<meta name="generator" content="Jekyll v4.0.0" />
<meta property="og:title" content="BIRDS" />
<meta property="og:locale" content="en_US" />
<meta name="description" content="Bidirectional Transformation for Relational View Update Datalog-based Strategies" />
<meta property="og:description" content="Bidirectional Transformation for Relational View Update Datalog-based Strategies" />
<meta property="og:site_name" content="BIRDS" />
<script type="application/ld+json">
{"@type":"WebPage","url":"/basic-tutorial.html","headline":"BIRDS","description":"Bidirectional Transformation for Relational View Update Datalog-based Strategies","@context":"https://schema.org"}</script>
<!-- End Jekyll SEO tag -->

  </head>

  <body>

    <!-- HEADER -->
    <div id="header_wrap" class="outer">
        <header class="inner">
          
            <a id="forkme_banner" href="https://github.com/dangtv/BIRDS">View on GitHub</a>
          
          <a id="project_title" href="/BIRDS">BIRDS</a>
           <!-- <h1 id="project_title">  BIRDS </h1>  -->
          <h2 id="project_tagline">Bidirectional Transformation for Relational View Update Datalog-based Strategies</h2>

          
        </header>
    </div>

    <!-- MAIN CONTENT -->
    <div id="main_content_wrap" class="outer, wrapper">
      <div class="inner">
          <nav>
              <h3>Contents</h3>
              <ul></ul>
            </nav>
        <section id="main_content">
          <h1 id="the-basics">The basics</h1>

<h2 id="the-basic-syntax">The basic syntax</h2>

<h3 id="source-and-view-schemas">Source and view schemas</h3>

<p>Example: Consider two base tables <code class="highlighter-rouge">s1(X,Y)</code> and <code class="highlighter-rouge">s2(X,Y)</code> and a view <code class="highlighter-rouge">v(X,Y)</code>:</p>

<p>s1</p>

<table>
  <thead>
    <tr>
      <th>X</th>
      <th>Y</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>2</td>
    </tr>
  </tbody>
</table>

<p>s2</p>

<table>
  <thead>
    <tr>
      <th>X</th>
      <th>Y</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>2</td>
      <td>3</td>
    </tr>
  </tbody>
</table>

<p>v</p>

<table>
  <thead>
    <tr>
      <th>X</th>
      <th>Y</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>2</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
    </tr>
  </tbody>
</table>

<p>The source schema and the view schema are defined as follows:</p>

<div class="language-prolog highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="ss">source</span> <span class="ss">s1</span><span class="p">(</span><span class="ss">'X'</span><span class="o">:</span><span class="ss">int</span><span class="p">,</span> <span class="ss">'Y'</span><span class="o">:</span><span class="ss">int</span><span class="p">).</span>
<span class="ss">source</span> <span class="ss">s2</span><span class="p">(</span><span class="ss">'X'</span><span class="o">:</span><span class="ss">int</span><span class="p">,</span> <span class="ss">'Y'</span><span class="o">:</span><span class="ss">int</span><span class="p">).</span>
<span class="ss">view</span> <span class="ss">v</span><span class="p">(</span><span class="ss">'X'</span><span class="o">:</span><span class="ss">int</span><span class="p">,</span> <span class="ss">'Y'</span><span class="o">:</span><span class="ss">int</span><span class="p">).</span>
</code></pre></div></div>
<ul>
  <li>Using the keywords <code class="highlighter-rouge">source</code> and <code class="highlighter-rouge">view</code> to distinguish the source tables and the view.</li>
  <li>Each column is assigned a data type.</li>
  <li>Supported data types are: <code class="highlighter-rouge">integer</code>, <code class="highlighter-rouge">real</code> and <code class="highlighter-rouge">string</code>.</li>
</ul>

<h3 id="view-definition">View definition</h3>

<p><code class="highlighter-rouge">v</code> can be defined over <code class="highlighter-rouge">s1</code> and <code class="highlighter-rouge">s2</code> as follows:</p>
<div class="language-prolog highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="ss">v</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">)</span> <span class="p">:-</span> <span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">).</span>
<span class="ss">v</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">)</span> <span class="p">:-</span> <span class="ss">s2</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">).</span>
</code></pre></div></div>

<h3 id="rules-for-update-strategy">Rules for update strategy:</h3>
<ul>
  <li>Delta predicate: a delta predicate is a normal predicate following a symbol <code class="highlighter-rouge">+</code> or <code class="highlighter-rouge">-</code>
    <ul>
      <li>The predicate <code class="highlighter-rouge">+R</code> corresponds to the delta relation of tuples being inserted into source relation <code class="highlighter-rouge">R</code></li>
      <li>The predicate <code class="highlighter-rouge">âˆ’R</code> corresponds to the delta relation of tuples being deleted from <code class="highlighter-rouge">R</code></li>
    </ul>
  </li>
  <li>
    <p>Delta rule: a delta rule is a rule whose head is a delta predicate of a base table. For example, the following rule means any tuple <code class="highlighter-rouge">(X,Y)</code>, which is in <code class="highlighter-rouge">s1</code> but not in <code class="highlighter-rouge">v</code>, will be deleted from <code class="highlighter-rouge">s1</code>:</p>

    <div class="language-prolog highlighter-rouge"><div class="highlight"><pre class="highlight"><code>  <span class="o">-</span><span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">)</span> <span class="p">:-</span> <span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">),</span> <span class="ss">not</span> <span class="ss">v</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">).</span>
</code></pre></div>    </div>
  </li>
</ul>

<h2 id="an-update-strategy">An update strategy</h2>

<p>The following is a full Datalog program for an update strategy on the view <code class="highlighter-rouge">v(X,Y)</code> (<a href="https://github.com/dangtv/BIRDS/tree/master/examples/basic_sample.dl">basic_sample.dl</a>):</p>

<div class="language-prolog highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">% schema:</span>
<span class="ss">source</span> <span class="ss">s1</span><span class="p">(</span><span class="ss">'X'</span><span class="o">:</span><span class="ss">int</span><span class="p">,</span><span class="ss">'Y'</span><span class="o">:</span><span class="ss">int</span><span class="p">).</span>
<span class="ss">source</span> <span class="ss">s2</span><span class="p">(</span><span class="ss">'X'</span><span class="o">:</span><span class="ss">int</span><span class="p">,</span><span class="ss">'Y'</span><span class="o">:</span><span class="ss">int</span><span class="p">).</span>
<span class="ss">view</span> <span class="ss">v</span><span class="p">(</span><span class="ss">'X'</span><span class="o">:</span><span class="ss">int</span><span class="p">,</span><span class="ss">'Y'</span><span class="o">:</span><span class="ss">int</span><span class="p">).</span>

<span class="c1">% rules for update strategy:</span>
<span class="o">-</span><span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">)</span> <span class="p">:-</span> <span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">),</span> <span class="ss">not</span> <span class="ss">v</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">).</span>
<span class="o">-</span><span class="ss">s2</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">)</span> <span class="p">:-</span> <span class="ss">s2</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">),</span> <span class="ss">not</span> <span class="ss">v</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">).</span>
<span class="o">+</span><span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">)</span> <span class="p">:-</span> <span class="ss">v</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">),</span> <span class="ss">not</span> <span class="ss">s1</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">),</span> <span class="ss">not</span> <span class="ss">s2</span><span class="p">(</span><span class="nv">X</span><span class="p">,</span><span class="nv">Y</span><span class="p">).</span>
</code></pre></div></div>

<p>The first two rules say that if a tuple <code class="highlighter-rouge">(X,Y)</code> is in <code class="highlighter-rouge">s1</code> or <code class="highlighter-rouge">s2</code> but not in <code class="highlighter-rouge">v</code>, it will be deleted from <code class="highlighter-rouge">s1</code> or <code class="highlighter-rouge">s2</code>, respectively. The last rule says that if a tuple <code class="highlighter-rouge">(X,Y)</code> is in <code class="highlighter-rouge">v</code> but in neither <code class="highlighter-rouge">s1</code> nor <code class="highlighter-rouge">s2</code>, it will be inserted to <code class="highlighter-rouge">s1</code>.</p>

<h2 id="verifying-and-compiling-into-sql">Verifying and Compiling into SQL</h2>

<p>We verify the correctness of the Datalog program and compile it into SQL by using command line <code class="highlighter-rouge">birds</code>:</p>

<div class="language-bash highlighter-rouge"><div class="highlight"><pre class="highlight"><code>birds <span class="nt">-v</span> <span class="nt">-f</span> basic_sample.dl <span class="nt">-o</span> basic_sample.sql
</code></pre></div></div>

<p>Where the option <code class="highlighter-rouge">-v</code> enables verification process, <code class="highlighter-rouge">-f</code> is for the input Datalog program and <code class="highlighter-rouge">-o</code> is for the output SQL file.</p>

<p><img src="assets/images/basic-compilation.png" alt="basic-compilation" /></p>

<p>The generated SQL file can run directly in a PostgreSQL database to create the corresponding updatable view <code class="highlighter-rouge">v</code> in the database.</p>

<h2 id="running-in-postgresql">Running in PostgreSQL</h2>

<ol>
  <li>Load and run the generated SQL file in PostgreSQL:
    <div class="language-bash highlighter-rouge"><div class="highlight"><pre class="highlight"><code> psql <span class="nt">-U</span> &lt;db_user_name&gt; <span class="nt">-d</span> &lt;db_name&gt; <span class="nt">-f</span> &lt;path_to_sql_file&gt;
</code></pre></div>    </div>
    <p>For example:</p>
    <div class="language-bash highlighter-rouge"><div class="highlight"><pre class="highlight"><code> psql <span class="nt">-U</span> postgres <span class="nt">-d</span> sample_db <span class="nt">-f</span> examples/basic_sample.sql
</code></pre></div>    </div>
  </li>
  <li>Run an UPDATE/INSERT/DELETE statement to modify data on view, for example:
    <div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code> <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">v</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="mi">7</span><span class="p">);</span>
</code></pre></div>    </div>
  </li>
  <li>Request a bigger modification on view by combining multiple UPDATE/INSERT/DELETE statements in one transaction:
    <div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code> <span class="k">BEGIN</span><span class="p">;</span>
     <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">v</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="mi">7</span><span class="p">);</span>
     <span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">v</span> <span class="k">where</span> <span class="n">x</span> <span class="o">=</span> <span class="mi">5</span><span class="p">;</span>
     <span class="k">UPDATE</span> <span class="n">v</span> <span class="k">SET</span> <span class="n">x</span> <span class="o">=</span> <span class="mi">10</span> <span class="k">where</span> <span class="n">x</span> <span class="o">=</span> <span class="mi">4</span><span class="p">;</span>
 <span class="k">END</span><span class="p">;</span>
</code></pre></div>    </div>
  </li>
</ol>

        </section>
      </div>
    </div>
    
    <!-- FOOTER  -->
    <div id="footer_wrap" class="outer">
      <footer class="inner">
        <p style="margin-bottom:0em"><small>
          
          Maintained by <a href="https://dangtv.github.io">Vandang Tran</a>, Hiroyuki Kato, Zhenjiang Hu</small>.
        
        <small>Theme based on <a href="https://github.com/pages-themes/slate">Slate</a></small>.
        <small>Last updated on 2 Dec 2020</small></p>
      </footer>
    </div>

    
  </body>
</html>