Spinner to indicate page is loading

[djyotta]

What are you building with SQLPage ?

Personal file, text sharing.

What is your problem ? A description of the problem, not the solution you are proposing.

I have (rare) situation where some sqlpage.fetch calls are slow (~10 seconds).

In other frameworks, I usually emmit a tag with a spinner container class on it before long running compute, then emmit another tag with a terminating class on it after compute is finished.

I apply the spinner via css. It's display is set to none when the sibling tag with terminating class is emmited.

I tried to do this with the text component, but it seems the partial page is not sent to the browser so it doesn't work.

What are you currently doing ? Since your solution is not implemented in SQLPage currently, what are you doing instead ?

I have no workaround. Page hangs and it blank.

Describe the solution you'd like

I'd like a spinner component that can be selected befor long compute. It could be terminated once next component is selected or by manually selecting a spinner-stop component.

It doesn't have to be a pure css spinner as I described, just whatever works even if it involves JS

It would be ideal if the spinner could be customized by supplying css and/or a class to apply the spinner to.

Describe alternatives you've considered

I'm not aware of any alternatives.

Maybe I can do a custom handlebars component?

[lovasoa]

Hi !

That would indeed be a nice feature, but probably not easy to implement.

What you can do in the meantime is have your long running task run in the background, and update the database on its own once it is done. The sqlpage.exec script would just launch the task in the background and return immediately. The SQLPage UI can then display the status of the computation based on the data in the database.

[djyotta]

@lovasoa would the user have to refresh the page manually then? I suppose I could use the meta tag to auto refresh - but apparently this is frowned upon...

<meta http-equiv="refresh" content="5">

[lovasoa]

There is already a refresh attribute in the shell component

[djyotta]

Is the page buffered in memory server side? It seems no matter whad I do, I can't get partial page sent to browser.

[lovasoa]

No, it's not buffered on the server, and each row is rendered and sent to the browser individually. However, browsers have their own buffering and rendering logic.

[djyotta]

Ok, this put me on the right track. From what I can tell, while sqlpage isn't buffering server side, it appears that sqlpage.fetch is blocking the browsers asynchronous requests for static content linked in the header (css and js etc). When I delay the sqlpage.fetch until after the browser has loaded the static content, then my spinner shows fine.

So this is my "solution" so far:

-- Setting the header seems to be important, but I'm not sure why (disable actix web compression middleware - I have compression provided by my reverse proxy instead)
SELECT 'http_header' AS component, 'Identity' AS "Content-Encoding";
-- This component emits a container tag with a CSS spinner
SELECT 'spinner-start' AS component, 'loader' AS class;

-- Putting the long running task in a call to run_sql seems to prevent the sqlpage.fetch from blocking other requests
SELECT 'dynamic' AS component, sqlpage.run_sql('task.sql') AS properties;

-- This component emits a tag that causes CSS to hide the spinner
SELECT 'spinner-stop' AS component;

task.sql:

SET ":request" = json_object(
    'method', 'POST',
    'url', 'https://example.com/slow-response',
    'headers', json_object(),
    'timeout_ms', 15000,
    'body', :data
);

SET ":response" = json(sqlpage.fetch(:request));

The handlebars

Spinner Start

<div class="spinner-start">
  <span class="{{class}}"></span>

Spinner Stop

</div>
<div class="spinner-stop" />

Spinner CSS

Tested on Chrome and Firefox - seemingly redundant CSS is there to achieve compatibility with both ff and chrome. The positions will need to be adjusted as it isn't centered properly with the SQLPage css...

div.spinner-start {
  position: absolute;
  left: 50vw;
  top: 50vh;
  margin-top: -5.5em;
  margin-left: -87.5px;
  padding-bottom: 2em;
  height: 9em;
  width: 175px;
}
div.spinner-start:not(:has(+ .spinner-stop)) {
  display: block;
}
.spinner-start:not(:last-child) {
  display: none;
}

The actual spinner (from https://cssloaders.github.io/):

.loader {
  width: 175px;
  height: 80px;
  display: block;
  margin:auto;
  background-image: radial-gradient(circle 25px at 25px 25px, #FFF 100%, transparent 0), radial-gradient(circle 50px at 50px 50px, #FFF 100%, transparent 0), radial-gradient(circle 25px at 25px 25px, #FFF 100%, transparent 0), linear-gradient(#FFF 50px, transparent 0);
  background-size: 50px 50px, 100px 76px, 50px 50px, 120px 40px;
  background-position: 0px 30px, 37px 0px, 122px 30px, 25px 40px;
  background-repeat: no-repeat;
  position: relative;
  box-sizing: border-box;
}
.loader::before {
  content: '';
  left: 60px;
  bottom: 18px;
  position: absolute;
  width: 36px;
  height: 36px;
  border-radius: 50%;
  background-color: #555555;
  background-image: radial-gradient(circle 8px at 18px 18px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 18px 0px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 0px 18px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 36px 18px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 18px 36px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 30px 5px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 30px 5px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 30px 30px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 5px 30px, #FFF 100%, transparent 0), radial-gradient(circle 4px at 5px 5px, #FFF 100%, transparent 0);
  background-repeat: no-repeat;
  box-sizing: border-box;
  animation: rotationBack 3s linear infinite;
}
.loader::after {
  content: '';
  left: 94px;
  bottom: 15px;
  position: absolute;
  width: 24px;
  height: 24px;
  border-radius: 50%;
  background-color: #555555;
  background-image: radial-gradient(circle 5px at 12px 12px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 12px 0px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 0px 12px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 24px 12px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 12px 24px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 20px 3px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 20px 3px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 20px 20px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 3px 20px, #FFF 100%, transparent 0), radial-gradient(circle 2.5px at 3px 3px, #FFF 100%, transparent 0);
  background-repeat: no-repeat;
  box-sizing: border-box;
  animation: rotationBack 4s linear infinite reverse;
}

@keyframes rotationBack {
  0% {
    transform: rotate(0deg);
  }
  100% {
    transform: rotate(-360deg);
  }
}

It unfortunately doesn't work so good due to sqlpage.fetch blocking the browser loading other files and thus blocking the browser rendering. I haven't tested with sqlpage.exec so not sure if that also blocks other requests. Wrapping the call to sqlpage.fetch seems to prevent the fetch blocking the browser from being blocked.

While I could do the approach @lovasoa suggested earlier with periodic page refreshes, the approach posted here is a nicer work flow for simple case of somewhat long-running exec or fetch as it doesn't require task progress tracking implemented in the db.

[lovasoa]

Hi, It's great that you got it working ! Would you be interested in "productionizing" to include it by default in sqlpage ?

I know @Pieter3033 needed something similar and also came up with their own custom solution.

I'm not sure how the run_sql can make a difference. When you run the long-running process directly, the spinner doesn't appear, and when you wrap it in a run_sql call, it works ?

[lovasoa]

I opened an issue in actix to discuss the problem that compression hinders streaming: https://github.com/actix/actix-web/issues/3410

[lovasoa]

After my latest two changes, running sqlpage with SQLPAGE_COMPRESS_RESPONSES=0 and the following sql file:

select 'shell' as component, 'hello' as title;
set slow = sqlpage.exec('sleep', 5);
select 'alert' as component, 'done' as title;

Results, as expected, in the shell appearing first, and five seconds later, the alert component appearing.

[djyotta]

Yeah I'll look at adding the handlebars and css to sqlpage and document an example.

Component	Top-level property	Description
spinner-start	id	id for container element
	spinner	CSS class name of spinner (default: sqlpage-loader). Can provide a custom spinner by adding custom CSS for it in the shell component and referencing the class name here
spinner-progress	id	elemeet id
	bar	CSS class name of progress bar (default: sqlpage-spinner-progress). Can be customized
	stage	Name of loading stage. Defaults to empty
	percent	Percentage of bar to fill. Defaults to indeterminate
spinner-stop	none	none

Default loader (need to test with both ligh and dark theme):

.loader {
    width: 48px;
    height: 48px;
    border: 5px solid #FFF;
    border-bottom-color: transparent;
    border-radius: 50%;
    display: inline-block;
    box-sizing: border-box;
    animation: rotation 1s linear infinite;
}

@keyframes rotation {
    0% {
        transform: rotate(0deg);
    }
    100% {
        transform: rotate(360deg);
    }
} 

Default propress bar

.loader {
  width: 100%;
  height: 4.8px;
  display: inline-block;
  position: relative;
  background: rgba(255, 255, 255, 0.15);
  overflow: hidden;
}
.loader::after {
  content: '';  
  box-sizing: border-box;
  width: 0;
  height: 4.8px;
  background: #FFF;
  position: absolute;
  top: 0;
  left: 0;
  animation: animFw 10s linear infinite;
}

@keyframes animFw {
  0% {
    width: 0;
  }
  100% {
    width: 100%;
  }
}

[lovasoa]

I think we can do that with tabler without any custom css: https://tabler.io/docs/components/spinners

[Pieter3033]

Hi, It's great that you got it working ! Would you be interested in "productionizing" to include it by default in sqlpage ?

I know @Pieter3033 needed something similar and also came up with their own custom solution.

I'm not sure how the run_sql can make a difference. When you run the long-running process directly, the spinner doesn't appear, and when you wrap it in a run_sql call, it works ?

Hi Gents, apologies for jumping in this late. Not sure if this is relevant anymore but I feel the need to share my experience.

I've achieved this in mostly the same manner that you have resolved the issue.

Note: function page (a page with no shell component, executing a procedure and redirecting afterwards)

The way I discovered this was making a call, in a function page to a linked database in SQLPage (I linked the DB in SQL server), and it took about 6 seconds to respond, which resulted in a blank screen.

The way I worked around this was to update a Bit = 0 in my table, in the function page, which executes a stored procedure in SQL server, which then populates my table with the relevant information and updates Bit = 1.

While the information is being populated (Bit = 0) I display the "loading" screen. Which is essentially a page that refreshes every 2 seconds, with a custom CSS animation (spinner). This page only redirects only if Bit = 1.

I have realized this can cause issues for example if there is a error generated in the stored procedure, it will load forever, a workaround is to count the number of times the page has refreshed and if it exceeds the max time (set by dev) it redirects to a pre-defined error message.

[djyotta]

@Pieter3033 thanks for detailing your solution. It sounds similar to @lovasoa's recommendation.

Here I'm proposing a solution that doesn't involve a periodic refresh. Therefore errors are rendered if hit. However, my solution only works for tasks that block the page load. If simply running SQL that actually renders page elements then it's no good.

SELECT 'loader-start' AS component;
-- don't try to render other rows here
SET ":resp" = sqlpage.fetch(:request);
-- other non-rendered rows are fine
SET ":result" = sqlpage.exec(:prog, :arg);
SELECT 'loader-stop' AS component;

Sounds like this may fit your use case as you call a stored procedure. If that is the case, you won't need the status indicator Bit = 1 in your db neither the refresh. Page will stream with the spinner showing activity until page is loaded.

You will need to disable compression though: SQLPAGE_COMPRESS_RESPONSES=0 or in sqlpage.json:

"compress_responses": false

[djyotta]

Hi, It's great that you got it working ! Would you be interested in "productionizing" to include it by default in sqlpage ?

I know @Pieter3033 needed something similar and also came up with their own custom solution.

I'm not sure how the run_sql can make a difference. When you run the long-running process directly, the spinner doesn't appear, and when you wrap it in a run_sql call, it works ?

@lovasoa Seems like compression middleware was the culprit. Thogh it's weird because I thought I had disabled compression the whole time via the header with and without the run_sql wrapper... but I can confirm it works fine without the run sql wrapper if compression is disabled via the config option you provided,