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