HTML template engine using PostgreSQL

Description
Replacement example
Template example
Important qualities
Full example
Video

Description

Tool is designed to display on the site static information from database, which:

rarely updates (e.g. dictionaries)
preparation takes a long time
does not require real-time relevance (e.g. TOP products)

PGHtml is a service that creates HTML, JS, JSON and other file types using data obtained from a PostgreSQL database.
The file is created from a template file in which SQL queries, tags and variables are replaced.
Template extension started with pg, i.e. index.html will be created based on the index.pghtml file

Replacements

$$ [SQL query] $$	-	SQL query result
<pghtml-include>	-	file contents
${[variable]}	-	variable value

<!-- single value from database --> 
$$
select user
$$

<!-- multiple values --> 
$$
select tablename from pg_tables
  where schemaname='pg_catalog' limit 5
$$

<!-- inclusion file example_include.html -->
<pghtml-include>include-simple.html</pghtml-include>

→

→

<!-- single value from database --> 
postgres

<!-- multiple values --> 
pg_statistic
pg_type
pg_foreign_table
pg_proc
pg_attribute

<!-- inclusion file example_include.html -->
[ file example-header.html ]

Template

<!DOCTYPE html>
<head>
<title>${title}</title>
</head>

<body>
<h2>${caption}</h2>
${text}
</body>

</html>

Source and result pages

<pghtml-var name="title">Example</pghtml-var>
<pghtml-var name="caption">Multiline text</pghtml-var>
<pghtml-var name="text">
Text line 1
<br>
<i>Text line 2</i>
</pghtml-var>
<pghtml-include>/template/template-common.html</pghtml-include>

→

→

→

<!DOCTYPE html>
<head>
<title>Example</title>
</head>

<body>
<h2>Multiline text</h2>

Text line 1
<br>
<i>Text line 2</i>

</body>

</html>

<!-- single value from database --> 
$$
select user
$$

<!-- multiple values --> 
$$
select tablename from pg_tables
  where schemaname='pg_catalog' limit 5
$$

<!-- inclusion file example_include.html -->
<pghtml-include>include-simple.html</pghtml-include>

→

→

<!-- single value from database --> 
postgres

<!-- multiple values --> 
pg_statistic
pg_type
pg_foreign_table
pg_proc
pg_attribute

<!-- inclusion file example_include.html -->
[ file example-header.html ]

Check for updates - if contents of resulting file have not updated, then the file is not overwritten and web server does not redeploy it
Import - support for importing other files (such as HTML fragment or JSON data)
Change list - it is possible to create list of changed files
Error handling - if an error occurs (for example, when executing a SQL query), processing continues, extended information is output to log (stderr) and at end of execution, the tool returns the code of unsuccessful execution to OS. This allows you to configure monitoring of data updates on the site
Selecting processed files - possible processing of specific files, directories with recursion and setting file prefixes. At same time, built-in variables are created that can be used in referencies and in SQL queries. Usually root directory of site is specified
Variables - support variables from the command line, built-in variables (directories, paths to files, etc.) and tag attributes when importing file
Unlimited nesting - support including files inside an included file, redefining variables multiple times and using them inside another variable
Conditions and loops - programming inside files not supported, PostgreSQL stored procedures are used to implement logic

Call from command line

[postgres@pgsuite ~]$ pghtml execute -hd /tmp/example -d pgsuite
PGHtml is HTML template engine using PostgreSQL
version 24.2.7, linux 64 bits

Parameters
  HTTP
    directory:         /tmp/example
    sync interval:     600s
    port:              5480
  database
    host:              127.0.0.1
    port:              5432
    database:          pgsuite
    host for service:  127.0.0.1
    user for service:  postgres
  administration
    port:              15480

2024-07-06 10:05:11.404 INFO  ADMIN_SERVER  thread started, thread_id: 27809
2024-07-06 10:05:11.404 INFO  ADMIN_SERVER  listening socket bound to 127.0.0.1:15480
2024-07-06 10:05:11.404 INFO  FILE_MAKER    thread started, thread_id: 27810
2024-07-06 10:05:11.405 INFO  FILE_MAKER    connecting to database, URI: postgresql://127.0.0.1:5432/pgsuite?user=postgres
2024-07-06 10:05:11.411 INFO  FILE_MAKER    connected to database, pid: 27811, user: postgres, client_encoding: UTF8, server version: 140002
2024-07-06 10:05:11.411 INFO  FILE_MAKER    syncing /tmp/example directory
2024-07-06 10:05:11.411 INFO  FILE_MAKER    processing data/top-indexes-scans.pgjson template
2024-07-06 10:05:11.428 INFO  FILE_MAKER    file data/top-indexes-scans.json updated
2024-07-06 10:05:11.428 INFO  FILE_MAKER    processing example-full.pghtml template
2024-07-06 10:05:11.456 INFO  FILE_MAKER    file example-full.html updated
2024-07-06 10:05:11.456 INFO  FILE_MAKER    processing example-simple.pghtml template
2024-07-06 10:05:11.458 INFO  FILE_MAKER    file example-simple.html does not update required
2024-07-06 10:05:11.458 INFO  FILE_MAKER    processing example-template.pghtml template
2024-07-06 10:05:11.458 INFO  FILE_MAKER    file example-template.html does not update required
2024-07-06 10:05:11.458 INFO  FILE_MAKER    processing ru/example-simple-ru.pghtml template
2024-07-06 10:05:11.459 INFO  FILE_MAKER    file ru/example-simple-ru.html does not update required
2024-07-06 10:05:11.459 INFO  FILE_MAKER    directory synced successfully
2024-07-06 10:05:11.459 INFO  FILE_MAKER    disconnected from database, pid: 27811

Converting file example.pghtml to example.html

<!DOCTYPE html>
<html>

<head>

<!-- inclusion of styles for acceleration -->
<style type="text/css">
<pghtml-include>/css/common.css</pghtml-include>
</style>

<!-- inclusion of title with two variables "G_SITE" and "description" -->
<pghtml-include description="Example">/include/title.html</pghtml-include>

</head>
<body>


<!-- single value from database --> 
<p>Database uptime</p>

$$
select now() - pg_postmaster_start_time()
$$

<br><br>


<!-- dataset --> 
<p>Top 10 indexes by size, table</p>

<table>

<tr><th>Schema</th><th>Index</th><th>Size</th></tr>

$$
select '<tr><td>'||schemaname||'</td><td>'||indexname||'</td><td class="td-number">'||size||'</td></tr>' from (
  select schemaname,indexname,pg_total_relation_size((schemaname||'.'||tablename)::regclass) size from pg_indexes order by 3 desc limit 10
) t 
$$

</table>

<br><br>

<!-- dataset from json --> 
<p>Top 10 indexes by scans, json</p>

<div id="top_indexes_scans" style="width: 600px; height: 200px">

<button onclick="loadFile('top_indexes_scans', 'data/top_indexes_scans.json')">
Load file 'data/top_indexes_scans.json'
</button>


</div>


<script>

function loadFile(element_id, filename) {
  var xhr = new XMLHttpRequest();
  xhr.onreadystatechange = function() {
    if (xhr.readyState == 4) {
      if (xhr.status == 200) {
        document.getElementById(element_id).innerHTML = "<pre>"+xhr.responseText+"</pre>";
        document.getElementById(element_id).style.border = "1px solid black";
      } else {
        document.getElementById(element_id).innerHTML =
          "Page must be opened via web server,<br>view file '"+filename+"' manually";
      }
    }
  }
  xhr.open('GET', filename, true);
  xhr.send();
}

</script>

<br><br>

<!-- variables --> 
<p>Variables</p>

<table>

<tr><th>Name</th><th>Value</th></tr>

<tr> <td>${directory_root}</td>  <td>${directory_root}</td>  </tr>
<tr> <td>${directory}</td>       <td>${directory}</td>       </tr>
<tr> <td>${filename}</td>        <td>${filename}</td>        </tr>
<tr> <td>${filepath_source}</td> <td>${filepath_source}</td> </tr>
<tr> <td>${filepath_dest}</td>   <td>${filepath_dest}</td>   </tr>
<tr> <td>${G_SITE}</td>          <td>${G_SITE}</td>          </tr>

</table>

<br><br>


<pghtml-include>include/footer.html</pghtml-include>

</body>
</html>

→

→

→

→

<!DOCTYPE html>
<html>

<head>

<!-- inclusion of styles for acceleration -->
<style type="text/css">
html { font: 12px Verdana, Arial, Helvetica, sans-serif; }

p { font-size: 16px; font-weight: bold; }

table { border-collapse: collapse; }

td, th { padding: 3px; border: 1px solid black; }

th { background: silver; }

.td-number { text-align: right; }
</style>

<!-- inclusion of title with two variables "G_SITE" and "description" -->
<title>Example</title>
                     

</head>
<body>


<!-- single value from database --> 
<p>Database uptime</p>

12 days 00:30:43.890922

<br><br>


<!-- dataset --> 
<p>Top 10 indexes by size, table</p>

<table>

<tr><th>Schema</th><th>Index</th><th>Size</th></tr>

<tr><td>pg_catalog</td><td>pg_depend_reference_index</td><td class="td-number">1236992</td></tr>
<tr><td>pg_catalog</td><td>pg_depend_depender_index</td><td class="td-number">1236992</td></tr>
<tr><td>pg_catalog</td><td>pg_proc_oid_index</td><td class="td-number">1138688</td></tr>
<tr><td>pg_catalog</td><td>pg_proc_proname_args_nsp_index</td><td class="td-number">1138688</td></tr>
<tr><td>pg_catalog</td><td>pg_attribute_relid_attnam_index</td><td class="td-number">778240</td></tr>
<tr><td>pg_catalog</td><td>pg_attribute_relid_attnum_index</td><td class="td-number">778240</td></tr>
<tr><td>pg_catalog</td><td>pg_rewrite_rel_rulename_index</td><td class="td-number">688128</td></tr>
<tr><td>pg_catalog</td><td>pg_rewrite_oid_index</td><td class="td-number">688128</td></tr>
<tr><td>pg_catalog</td><td>pg_collation_oid_index</td><td class="td-number">540672</td></tr>
<tr><td>pg_catalog</td><td>pg_collation_name_enc_nsp_index</td><td class="td-number">540672</td></tr>

</table>

<br><br>

<!-- dataset from json --> 
<p>Top 10 indexes by scans, json</p>

<div id="top_indexes_scans" style="width: 600px; height: 200px">

<button onclick="loadFile('top_indexes_scans', 'data/top_indexes_scans.json')">
Load file 'data/top_indexes_scans.json'
</button>


</div>


<script>

function loadFile(element_id, filename) {
  var xhr = new XMLHttpRequest();
  xhr.onreadystatechange = function() {
    if (xhr.readyState == 4) {
      if (xhr.status == 200) {
        document.getElementById(element_id).innerHTML = "<pre>"+xhr.responseText+"</pre>";
        document.getElementById(element_id).style.border = "1px solid black";
      } else {
        document.getElementById(element_id).innerHTML =
          "Page must be opened via web server,<br>view file '"+filename+"' manually";
      }
    }
  }
  xhr.open('GET', filename, true);
  xhr.send();
}

</script>

<br><br>

<!-- variables --> 
<p>Variables</p>

<table>

<tr><th>Name</th><th>Value</th></tr>

<tr> <td>${directory_root}</td>  <td>${directory_root}</td>  </tr>
<tr> <td>${directory}</td>       <td></td>       </tr>
<tr> <td>${filename}</td>        <td>example-full.pghtml</td>        </tr>
<tr> <td>${filepath_source}</td> <td>/tmp/build/example/example-full.pghtml</td> </tr>
<tr> <td>${filepath_dest}</td>   <td>/tmp/build/example/example-full.html</td>   </tr>
<tr> <td>${G_SITE}</td>          <td>${G_SITE}</td>          </tr>

</table>

<br><br>


-----
<br>
<span style="font-size: 75%">
info updated at 2024-07-01 18:56:20.414276+03
</span>


</body>
</html>

Converting file top_indexes_scans.pgjson to top_indexes_scans.json

$$

select json_agg(t) from (
  select schemaname "schema",indexrelname "index",idx_scan scans 
    from pg_catalog.pg_stat_all_indexes
    order by idx_scan desc
    limit 10
) t;  

$$

→

→

[{"schema":"pg_catalog","index":"pg_db_role_setting_databaseid_rol_index","scans":282876}, 
 {"schema":"pg_catalog","index":"pg_database_oid_index","scans":282760}, 
 {"schema":"pg_catalog","index":"pg_attribute_relid_attnum_index","scans":71261}, 
 {"schema":"pg_catalog","index":"pg_database_datname_index","scans":70752}, 
 {"schema":"pg_catalog","index":"pg_class_oid_index","scans":35869}, 
 {"schema":"pg_catalog","index":"pg_index_indexrelid_index","scans":17804}, 
 {"schema":"pg_catalog","index":"pg_opclass_oid_index","scans":17343}, 
 {"schema":"pg_catalog","index":"pg_amproc_fam_proc_index","scans":17336}, 
 {"schema":"pg_catalog","index":"pg_index_indrelid_index","scans":653}, 
 {"schema":"pg_catalog","index":"pg_class_relname_nsp_index","scans":411}]

Example files are available along with the source code in archive, which can be downloaded from page Download

Review posted on Youtube

PGHtml

HTML template engine using PostgreSQL

PGHtml

About