Third party nested json configurations

[twoxfh]

I just went through an exercise of implementing Tabulator in Sqlpage and my longest task was trying to figure out how to manage the JSON configuration. I first, tried to find a way to populate a component parameter of json type and pass it to the template to create a json data node to be read by JavaScript. I kept running into escaping issues which is likely for safety reasons. I thought about putting it all in JS, but that somewhat defeats the purpose. I ended up changing the json endpoint to return data and UI information and separated it in JavaScript. Not sure I like this idea either but at least the thought processes are in one spot.

How have others tackled large json configurations for libraries in a way that continues minimalistic easy development?

Thanks for reading.

[lovasoa]

Hello !
This is an interesting feedback. Can you give some concrete examples of code you have tried that doesn't work ?
If you need examples of how to interact with third party javascript libraries, you can have a look at the default chart and map components:

• chart:
  • https://github.com/lovasoa/SQLpage/blob/main/sqlpage/templates/chart.handlebars (the data is passed in a data element)
  • https://github.com/lovasoa/SQLpage/blob/main/sqlpage/apexcharts.js (look at the c.querySelector("data"))
• map :
  • https://github.com/lovasoa/SQLpage/blob/main/sqlpage/templates/map.handlebars (look at data-geojson, for instance)
  • https://github.com/lovasoa/SQLpage/blob/main/sqlpage/sqlpage.js#L25-L98

If you need some more concrete advice, I'll be happy to answer here. If you are ready to contribute a new "tabulator" component to the official sqlpage distribution, that would be welcome too !

[lovasoa]

Another important thing to note is that, as a security measure, SQLPage currently serves all pages with a content-security-policy of script-src 'self' https://cdn.jsdelivr.net/, which forbids you from including raw javascript in your custom components. That may sometimes be too restrictive, and make things more cumbersome than they need to be (you need to create a separate javascript file). I plan on making the CSP optional in future releases.

[twoxfh]

Thank you for your feedback lovasoa, in the examples you mention I did not see where json was the parameter, they all appeared to be strings. When I use json_set('{"test":"me"}') as param in the SQL page, and {{stringify param}} in the component template it prints encoded strings, (eg &quote; for ") which would not be ideal for an application/json data tag.

I am a bit lazy, and the more I thought about defining parameters in the SQL, handlebars, and javascript files the less it appealed to me. So I wrote a json endpoint using the following and included the needed configuration in the ui object endpoint response:

JSON SQL Page:

SELECT 'json' AS component,
json_object('data',
  json_group_array(
    json_object('email', user.email)
  ),
  'ui', json_set('{
    "layout":"fitColumns",
    "columns": [{"title":"Email", "fielTd":"email"}],
    "pagination": "local",
   "paginationSize": 10,
   "paginationSizeSelector": [10, 100, 1000],
   "footerElement":"<label>Total <span id=total-count></label> <label>Filtered:</span><span id=search-count></span></label>"}
')) AS contents
from (
  select email
  from user
  LIMIT 1000
  OFFSET IFNULL($offset, 0)
) user;

My component has a library and javascript file having a one to many relationship with the instances of tabulator I created a quick customjs component to insert the script tags at the end of the page. If both can be wrapped into a single component it would be easier to package.
INDEX SQL page:

SELECT 'tabulator' AS component, 'table' as htmlid, '/usersjson.sql' as datauri;
select 'customjs' as  component, 'tabulator.css' as css,'tabulator-lib.js' as javascript, 'tabulator.js' as javascript;

Tabulator handlebars:

<div class="card my-2">
    <div class="card-body">
        <div id="{{htmlid}}" tabulator-data-url="{{datauri}}"></div>
    </div>
</div>

Customjs handlebars:

{{#each (to_array css)}}
        {{#if this}}
            <link rel="stylesheet" href="{{this}}">
        {{/if}}
   {{/each}}

    {{#each (to_array javascript)}}
        {{#if this}}
            <script src="{{this}}" defer></script>
        {{/if}}
    {{/each}}

I apologies for the dirty clothes but I wanted to make sure it fits before I clean it.

Tabulator Javascript:

document.addEventListener("DOMContentLoaded", function () {
const matches = document.querySelectorAll("div[tabulator-data-url]");
matches.forEach((tabulatorItem) => {
const url = tabulatorItem.getAttribute('tabulator-data-url')
fetch(url).then(r => r.json())
  .then(function(data) {
data.ui['data'] = data.data;
const dataTable = new Tabulator("#"+tabulatorItem.id, data.ui);
dataTable.on("dataLoaded", function(data){
  updateFooter('total-count', data.length);
});
dataTable.on("dataFiltered", function(filters, rows){
  updateFooter('search-count', rows.length);
});
function updateFooter(element, data){
  var el = document.getElementById(element);
  el.innerHTML = data;
}
}).catch(e => console.log("Booo"))
});
});

This is my current approach, please feel free to comment, improve, advise. I appreciate the feedback.

[lovasoa]

The " you see in the raw html source are a way for the template renderer to render everything in a safe way whatever the context it is in. They are interpreted by the browser, and do not end up in the DOM. If you access the contents of an attribute or a data element from js, you won't see them. A picture being worth a thousand words:

However, your approach of building a separate api endpoint is also totally valid, and may even be more performant for large datasets.

[twoxfh]

Thank you .. My apologies..must of been something I scratched and sniffed and thought it was an encoding problem. Prolly stepping on my own toes. I really like this project and have built similar approaches over the years but never to the degree you took it.. Appreciate you sharing the project.

It's the getting and setting of variables I find most tedious and why I just set them in one place, but certainly has its disadvantages of accidentally changing a working data payload while editing the UI or vice versa.