How to web-enable a single postgreSQL table

Overview of the rodakino directory structure

|-- app/

|    |-- authentication/

|    |-- database/

|    |-- roda_dbwindows/

|    |-- roda_windows/

|    |-- roda_frame/

|-- config/

|-- node_modules/

|-- sql/

|-- index.js

|-- package.json

 

For implementing a web interface for a postgres table, we just need the bold directories.

 

1. Create a new postgreSQL table

I use a table for zip codes for our example:

Create a new file mycrebas.sql in sql/, edit and save:

create table ZIPCODES (

   COUNTRY varchar(30),

   ZIPCODE varchar(10),

   CITY varchar(50),

   constraint PK_ZIPCODES primary key (COUNTRY, ZIPCODE)

);

grant select,insert,update,delete on ZIPCODES to test;

 

Open a shell and type:

$> cd ~/rodakino/sql

$> psql -U node node

node=# \i mycrebas.sql

node=# \d       # database should contain our table now

node=# \d ZIPCODES

node=# exit   # or use ctl-d instead

 

2. Create a new rodakino database window

$> cd ~/rodakino/app/roda_dbwindows

Create a new file rdbw_zipcodes.hbs for the rdbw here:

{{> roda_dbwindows/rdbw_zipcodes_css}}               <!-- include my stylesheet -->

<div id="rdbw_zipcodes">                                                       <!-- thats my name, must be unique among all rdbw's -->
  <div class="insert_button" rdbw="rdbw_zipcodes">Insert</div>         <!-- display an insert button which works on me -->
  <div class="delete_button" rdbw="rdbw_zipcodes">Delete</div>
  <div id="row_table">                                                                <!-- container for the rdbw, name must NOT be changed -->
    <div id="row_header">                                                          <!-- container for the headlines, name must NOT be changed -->
      <div class="r_header" >Country</div>
      <div class="r_header" >ZIP</div>
      <div class="r_header" >City</div>
    </div>
    <div id="row_data">                                                             <!-- container for the data from postgres, name must NOT be changed -->
      <div id="row_template" class="roda_row", rownum="0" style="display:none">   <!-- template definition for one database row, name must NOT be changed -->
        <div class="roda_field"><input type="text" name="COUNTRY" maxlength="30" value=""/></div>
        <div class="roda_field"><input type="text" name="ZIPCODE"  maxlength="10" value=""/></div>

        <div class="roda_field"><input type="text" name="CITY"  maxlength="50" value=""/></div>
      </div>
    </div>
  </div>
  <div id="query_header" style="display:none">                  <!-- container for the headlines of the query-by-example window name must NOT be changed -->
    <div class="q_header">Country</div>
    <div class="q_header">ZIP</div>
    <div class="q_header">City</div>
  </div>
  <div id="query_template" class="query_row", rownum="0" style="display:none">         <!-- template definition for one row of the query window --> 
    <div class="q_field roda_field"><input type="text" name="COUNTRY" maxlength="50" value=""/></div>
    <div class="q_field roda_field"><input type="text" name="ZIPCODE"  maxlength="10" value=""/></div>
    <div class="q_field roda_field"><input type="text" name="CITY"  maxlength="30" value=""/></div>
  </div>
  <br>
  <div id="rowrows" style="display:none">No. %row% of %rows%</div>              <!-- display actual row and number rows retrieved -->
</div>

 

Create a new file rdbw_zipcodes_css.hbs for the stylesheet here:

<style>

  /* headlines of rdbw */

  .r_header {

    font-weight: bold;
    box-sizing: border-box;
    border: thin solid black;
    float: left;
    width: 33.3%;

  }

  /* for query window */
  .q_header {
    float: left;
    width: 33.3%;
  }

  .q_field {
    float: left;
    width: 33.3%;
  }

  /* activate vertical scrollbar */
  #row_data {
    border:thin solid black;
    border-collapse: collapse;
    height: 300px;
    overflow-y: auto;
  }

  /* stretch all inputs to 100% within their divs */
  input {
    width: 100%;
    box-sizing: border-box;
    background-color: transparent;
  }

  /* describe the data fields */

  #rdbw_zipcodes .roda_row {
    min-height: 1.5em;
  }

  .roda_field {

    float: left;

    width: 33.3%;

  }

</style>

 

3. Create a new rodakino window

$> cd ~/rodakino/app/roda_windows

$> mkdir rw_zipcodes

$> cd rw_zipcodes

Create a new file index.js here:

module.exports = {
  init: require('./init')
}

 

Create a new file init.js here:

const passport = require('passport')

function initUser (app) {
  app.get('/rw_zipcodes', passport.authenticationMiddleware(), (req, res) => {
    res.render('roda_windows/rw_zipcodes/rw_zipcodes', {
      // dont use the default layout
      layout: false,
      id: req.params.id
    })
  })
}

module.exports = initUser

 

Create a new file rw_zipcodes.hbs here:

<script type="text/javascript">
  // create instance of master dbwindow
  var rdbw_master = $("#rdbw_zipcodes").roda_dbwindow({
    sql: "select COUNTRY,ZIPCODE,CITY from ZIPCODES %WHERE_CLAUSE% order by 1,2",
    insSql: "insert into ZIPCODES values('%COUNTRY%','%ZIPCODE%','%CITY%')",
    updSql: "update ZIPCODES " +
              "set COUNTRY = '%COUNTRY%'," +
              "ZIPCODE = '%ZIPCODE%'," +
              "CITY = '%CITY%' " +
              "where COUNTRY = '%_COUNTRY%' " + // %_ ... means original value of db-field
              "and ZIPCODE = '%_ZIPCODE%' ",
    delSql: "delete from ZIPCODES " +
              "where COUNTRY = '%_COUNTRY%' " + // %_ ... means original value of db-field
              "and ZIPCODE = '%_ZIPCODE%' "
  });
  function onDivLoad(myName) {
    // function is called on ajax success after load
    console.log('rw_roda_users.onDivLoad.1 myName: '+ myName);

    // i am the active roda_window. !! necessary for roda_menubar to work !!
    act_roda_window = myName;

    // resize the dialog
    $("#"+myName).roda_window("option","height",550);
    $("#"+myName).roda_window("option","width",660);

    // set master roda_dbwindow
    $("#"+myName).roda_window("option","rdbw_master","rdbw_zipcodes");
    // call retrieve for master roda_dbwindow
    rdbw_master.roda_dbwindow("retrieve",true);
  }

</script>

<!-- HTML -->
<h4> Zipcodes</h4>

<!-- import Code of Master roda_dbwindow -->
{{> roda_dbwindows/rdbw_zipcodes }}

 

4. Declare the new rodakino window to the application

$> cd ~/rodakino/app

edit file index.js

add following line near the end

require('./roda_windows/rw_zipcodes').init(app);

 

5. Add the rodakino window to the main menu

$> cd ~/rodakino/sql

edit file mycrebas.sql. This create a new menu level "My menu" and a new menu item for my zipcodes window within.

 

insert into RODA_MENUS( MENUID,MENU_PROMPT,MENU_CLASS,OBJECT2CALL,PARENT_MENUID,SORT_ORDER,DELETED)
    values('mymenu','My menu','M','rodakino_menu',null,1005,'N');

 

insert into MENU_PROFILE_ITEMS(MENU_PROFILE,MENUID,READONLY)
  values('TEST','mymenu','N');

 

insert into RODA_MENUS( MENUID,MENU_PROMPT,MENU_CLASS,OBJECT2CALL,PARENT_MENUID,SORT_ORDER,DELETED)
    values('zipcodes','Zipcodes','W','rw_zipcodes','mymenu',1000,'N');

 

insert into MENU_PROFILE_ITEMS(MENU_PROFILE,MENUID,READONLY)
  values('TEST','zipcodes','N');

execute the above statements in psql

 

6. Try it

$> cd ~/rodakino/

$> npm start

 

Browser: http://<ip-address>:3000

Login as user test, you see your new menu/rodakino window

Close browser, login as user node: you dont see your mymenu, because you did'nt define it in mycrebas.sql