Upgrading Montage Review page

[IgorA100]

I'm starting to think about upgrading the Montage Review page and I can't yet figure out how I can start viewing a recorded event of a specific camera from a specific time.
For example, I want to start viewing a recorded event starting 2024-06-10 20:49:16. Is this even possible or not?

Added: Since most likely I will have to redo almost the entire page, I plan to implement this Timeline: https://github.com/visjs/vis-timeline Examples: https://visjs.github.io/vis-timeline/examples/timeline/

[iconnor]

The way the current page works is, we load all frame data from the db over ajax.
So we do a binary search through the frames to find which frame is the one for the timestamp you want. Then we request that frame from zms.

The issue is that currently this is a mode=single type of transaction. It would be better if it was mode=jpeg and we could just tell zms to seek to that time.

Before you go jumping in too far. Please be aware of the view=timeline view which is an alternate implementation.

If you start from scratch, then I guarantee we will then have 3 competing views doing the same thing.

[IgorA100]

Then we request that frame from zms.

I will need to start the frame not from the very beginning, but from a certain time. I don't know how to do this yet.

It would be better if it was mode=jpeg

Yes.

and we could just tell zms to seek to that time.

How to do it ?

Please be aware of the view=timeline view which is an alternate implementation.

I don't see where I can switch the "view=timeline" mode? I only see the “timeline” scale, when clicked on it you need to start the frame from a certain time.

If you start from scratch, then I guarantee we will then have 3 competing views doing the same thing.

I haven't decided exactly what it will look like yet. This is a complex process.
I think the best option is for the Watch, Events, Montage & Montage Review page to be combined so that switching between live view and recording view will occur without reloading the page. This is a very global and lengthy work, but navigation would be simplified. Perhaps it should be done in stages, for example, based on the Montage page and add viewing of recorded events to it.

[IgorA100]

Work is underway.
A lot has already been done, but much remains to be done.
Montage page and Montage Review page are now on a single Montage page. All switching between modes occurs via AJAX without reloading the page.
The timeline is zoomed and panned with the mouse, and events on it are drawn dynamically. You can also add control buttons (Zoom, Pan), but I’m not yet sure if this is necessary, because... The interface is already overloaded with elements.
From the basic points, it is also necessary to implement a transition to the next event when viewing. Now, when you double-click on the timeline, the search and playback of events starts, taking into account the start from a certain frame relative to the time on the timeline. After the current event has finished playing, nothing happens.
Buttons will also be added for quickly selecting a timeline range (last hour, last 8 hours and last day) as on the Montage Review page.
Also, a full-fledged event filter has not yet been implemented; only the time range and displayed monitors are applied.
I still have a lot of ideas that need to be implemented.
Visually this is what happens.

[IgorA100]

Please clarify the parameters in this line:
src="XXX/zm/cgi-bin/nph-zms?mode=jpeg&frame=7185&scale=50&rate=100&maxfps=2&replay=none&source=event&event=442380&rand=1719316195&auth=b9d5b29fb7423df775832332495d15d1"

"scale" - as far as I understand, the video quality should change depending on this parameter?
"rate" - what is it?
$rate = reScale(RATE_BASE, $monitor->DefaultRate(), ZM_WEB_DEFAULT_RATE); - what does this recalculation do?

[IgorA100]

For the second day now I have been unable to generate an SQL query to obtain the latest event for each monitor from the list.
I get a list of events in a date range without any problems:

Code: Select all

SELECT a.* FROM (SELECT
        E.Id, E.MonitorId AS mId, E.Width, E.Height, E.Length, E.Frames, E.Archived, E.Cause, E.StartDateTime AS StartDateTime, UNIX_TIMESTAMP(E.StartDateTime) AS StartTimeSecs,
          CASE WHEN E.EndDateTime IS NULL THEN (SELECT NOW()) ELSE E.EndDateTime END AS EndDateTime,
          CASE WHEN E.EndDateTime IS NULL THEN (SELECT UNIX_TIMESTAMP(NOW())) ELSE UNIX_TIMESTAMP(EndDateTime) END AS EndTimeSecs
          FROM Events AS E
        WHERE 1 > 0 
      )a  WHERE ((StartTimeSecs<1719384072 AND EndTimeSecs>1719297672) AND (mId IN (5,6,20,15,33,37,38,41)));

Here one SELECT is wrapped in a second SELECT to allow the aliases "StartTimeSecs" and "EndTimeSecs" to be used in the WHERE
GROUP BY and ORDER BY do not work correctly in one query. They also don’t work correctly in a wrapped query, so you need to use JOIN.

You can do as recommended here: https://stackoverflow.com/questions/147 ... e-group-by
But I can’t form the correct SQL query

As the worst option, I considered using UNUION, for example like this:

Code: Select all

(SELECT
        E.Id, E.MonitorId AS mId, E.Width, E.Height, E.Length, E.Frames, E.Archived, E.Cause, E.StartDateTime AS StartDateTime,UNIX_TIMESTAMP(E.StartDateTime) AS StartTimeSecs
          FROM Events AS E
        WHERE MonitorId = 38 ORDER BY StartTimeSecs DESC LIMIT 1)
UNION
(SELECT
        E.Id, E.MonitorId AS mId, E.Width, E.Height, E.Length, E.Frames, E.Archived, E.Cause, E.StartDateTime AS StartDateTime,UNIX_TIMESTAMP(E.StartDateTime) AS StartTimeSecs
          FROM Events AS E
        WHERE MonitorId = 6 ORDER BY StartTimeSecs DESC LIMIT 1)

But I don't want to use this code because...
- firstly, UNION cannot be performed for all monitors without knowing the monitor IDs. In practice, this may not be necessary, but the code should be universal.
- secondly, I don’t know what the request execution speed will be if there are, for example, 100-200 monitors. I think the speed will drop significantly with a large number of monitors.

If the table stored TIMESTAMP for each event, then I could use MAX in the query, but this cannot be applied to an alias.

Please, help!

[IgorA100]

It turns out MAX() works correctly with dates. Or I'm wrong?
SQL query type:

Code: Select all

SELECT p1.* 
FROM Events p1
INNER JOIN
(
    SELECT E.Id, E.MonitorId AS mId, E.Width, E.Height, E.Length, E.Frames, E.Archived, E.Cause, E.StartDateTime AS StartDateTime, MAX(E.StartDateTime) AS maxStartDateTime, UNIX_TIMESTAMP(E.StartDateTime) AS StartTimeSecs,
       CASE WHEN E.EndDateTime IS NULL THEN (SELECT NOW()) ELSE E.EndDateTime END AS EndDateTime,
       CASE WHEN E.EndDateTime IS NULL THEN (SELECT UNIX_TIMESTAMP(NOW())) ELSE UNIX_TIMESTAMP(EndDateTime) END AS EndTimeSecs
    FROM Events AS E
    WHERE (MonitorId IN (5,6,20,15,33,37,38,41))
    GROUP BY mId
) p2
  ON p1.MonitorId = p2.mId
  AND p1.StartDateTime = p2.maxStartDateTime
order by p1.StartDateTime desc
;

works correctly.
But JOIN does not include aliases from the right table.

When receiving the last event for the monitor, I do not need "StartTimeSecs", "EndTimeSecs" & the calculated "EndDateTime" for unfinished events, but I want the SQL query to be correct and universal.

ADDED:
But now I need to get not the last event in general, but the last event before a certain date, i.e. I need to add a condition like:

Code: Select all

WHERE (StartTimeSecs<1719160438)

But because "StartTimeSecs" is an alias, then in table "p2" I cannot add this condition.
In addition, in the “p2” table we are looking for the MAX value from the entire date range
I'm confused...

[iconnor]

Don't use the aliased imestamps. Just work with the DateTime fields.

Unfortunately getting most recent event for a monitor is a fairly expensive operation.
SELECT * FROM Events WHERE MonitorId=1 ORDER BY StartDateTime DESC LIMIT 1;
Will do it, but mysql will scan the entire Events table, sort it, and return the first row. Very painful. If we know that we are looking in the last hour then we can add a WHERE condition on StartDateTime so it only has to look in a small subset of the table.

Anyways, since we have a datetime range, this is ok, just use the StartDateTime field as it has an index on it.
I would advise not joining the Monitors table unless absolutely necessary.

[Edit]
I think since you want to return the last event for all the monitors... you will have to use MAX. It should work fine on the existing columns. You could use Max(Id) as well.
SELECT *, MAX(Id) AS MaxID FROM Events WHERE StartDateTime > $whatever AND MonitorId IN (1,2,3,4) GROUP BY MonitorId;

[iconnor]

"scale" - as far as I understand, the video quality should change depending on this parameter?
"rate" - what is it?
$rate = reScale(RATE_BASE, $monitor->DefaultRate(), ZM_WEB_DEFAULT_RATE); - what does this recalculation do?

scale is percentage of source resolution. So for a 1080p monitor, if we ask for scale=50, we get a 960x540 image.
rate is frame rate. defaults to a max of 30fps. rate=5 means send 5 images per second.

reScale applies all the given scales to the first parameter. I'm not clear on why you would want to do this. RATE_BASE is defined as 100, so monitor->DefaultRate defaults to 1x which is 100. ZM_WEB_DEFAULT_RATE depends on bandwidth setting. High bandwidth also defaults to 100, so generally we will result in rate=100.

[IgorA100]

As a result, I ended up with the following SQL query:

Code: Select all

SELECT p1.Id, p1.MonitorId, p1.Width, p1.Height, p1.Length, p1.Frames, p1.Archived, p1.Cause, p1.StartDateTime,
  UNIX_TIMESTAMP(p1.StartDateTime) AS StartTimeSecs,
  CASE WHEN p1.EndDateTime IS NULL THEN (SELECT NOW()) ELSE p1.EndDateTime END AS EndDateTime,
  CASE WHEN p1.EndDateTime IS NULL THEN (SELECT UNIX_TIMESTAMP(NOW())) ELSE UNIX_TIMESTAMP(p1.EndDateTime) END AS EndTimeSecs
FROM Events AS p1
INNER JOIN
  (
    SELECT a.mId, a.StartDateTime, MAX(a.StartDateTime) AS maxStartDateTime
    FROM (
      SELECT E.MonitorId AS mId, E.StartDateTime, UNIX_TIMESTAMP(E.StartDateTime) AS StartTimeSecs,
        CASE WHEN E.EndDateTime IS NULL THEN (SELECT UNIX_TIMESTAMP(NOW())) ELSE UNIX_TIMESTAMP(EndDateTime) END AS EndTimeSecs
      FROM Events AS E
      #WHERE 1 > 0 
    )a
    WHERE ((StartTimeSecs<1718867418) AND (mId IN (5,6,20,15,33,37,38,41)))
    GROUP BY mId
  ) AS p2
  ON p1.MonitorId = p2.mId
  AND p1.StartDateTime = p2.maxStartDateTime
  order by p1.StartDateTime desc;

If you replace

Code: Select all

WHERE ((StartTimeSecs<1718867418) AND (mId IN (5,6,20,15,33,37,38,41)))

on

Code: Select all

WHERE (mId IN (5,6,20,15,33,37,38,41))

then absolute sampling of the last event will be performed
The execution time of an SQL query is about 200ms, which is certainly a lot. But I don’t have any other thoughts about searching for events yet

When initializing the monitor grid, I want to display not a black square, but the last frame of the last event, i.e. one request 200ms. An event request is also generated for display on the Timeline (I indicated the request text above), this is a fairly fast request of about 15ms.
Then when I double click on the Timeline I run a query to get the events that I want to fire. The query is similar to this:

Code: Select all

SELECT a.* FROM (SELECT
        E.Id, E.MonitorId AS mId, E.Width, E.Height, E.Length, E.Frames, E.Archived, E.Cause, E.StartDateTime AS StartDateTime, UNIX_TIMESTAMP(E.StartDateTime) AS StartTimeSecs,
          CASE WHEN E.EndDateTime IS NULL THEN (SELECT NOW()) ELSE E.EndDateTime END AS EndDateTime,
          CASE WHEN E.EndDateTime IS NULL THEN (SELECT UNIX_TIMESTAMP(NOW())) ELSE UNIX_TIMESTAMP(EndDateTime) END AS EndTimeSecs
          FROM Events AS E
        WHERE 1 > 0 
      )a  WHERE ((StartTimeSecs<1719384072 AND EndTimeSecs>1719297672) AND (mId IN (5,6,20,15,33,37,38,41)));

only in the condition the Timestamp is the same for "StartTimeSecs" and for "EndTimeSecs", i.e.

Code: Select all

WHERE ((StartTimeSecs<1719384072 AND EndTimeSecs>1719384072) AND (mId IN (5,6,20,15,33,37,38,41)))

In this case, the request is executed in 200ms instead of 20ms. This is still a mystery to me.

Next, I need to find the next recorded event, and I haven’t decided how to do it yet.
It will be necessary to send requests at a certain interval. Most likely, requests will be sent for each camera separately, because... events can last for example 5 seconds or 10 minutes.

[IgorA100]

I think since you want to return the last event for all the monitors... you will have to use MAX. It should work fine on the existing columns. You could use Max(Id) as well.
SELECT *, MAX(Id) AS MaxID FROM Events WHERE StartDateTime > $whatever AND MonitorId IN (1,2,3,4) GROUP BY MonitorId;

Yes, it works fine. But if I want to get not the next event after a certain date, but the most recent event, then this code returns not the most recent event for the monitor, but the very first.

Code: Select all

SELECT *, MAX(Id) AS MaxID FROM Events WHERE MonitorId IN (1,2,3,4) GROUP BY MonitorId;

Is the event "id" always in chronological order? Those. can I use "MAX(Id)" instead of "MAX(StartDateTime )"?

[iconnor]

Yes Id is the primary key. Always incrementing unless we are recovering old lost events.

You can use EXPLAIN and EXPLAIN ANALYZE to diagnose query speed. Sometimes mysql does non-obvious things. For example what you ORDER BY on can make it NOT use the index on StartDateTime.

[IgorA100]

Final queries that I plan to use:

*** We get the NEAREST NEXT event after the specified date FOR A GROUP of monitors: (90ms)
* Only the event ID will be correct in the answer, the rest will be random.
* - I don’t use it yet.

Code: Select all

SELECT MonitorId, StartDateTime, MIN(Id) AS maxId
FROM Events
WHERE ((StartDateTime>'2024-06-20 11:08:57')
 AND (MonitorId IN (5,6,20,15,33,37,38,41)))
GROUP BY MonitorId

*** We get the NEAREST NEXT event after the specified date FOR ONE monitor: (1ms) SUPER
* ALL information in the answer will be correct.
* + Called in the background when events are played back.

Code: Select all

SELECT *
FROM Events
WHERE StartDateTime>'2024-06-20 11:08:57'
 AND MonitorId=5 ORDER BY StartDateTime ASC LIMIT 1;

*** We get the NEAREST PREVIOUS event before the specified date FOR A GROUP of monitors: (90ms)
* Only the event ID will be correct in the answer, the rest will be random.
* - I don’t use it yet.

Code: Select all

SELECT MonitorId, StartDateTime, MAX(Id) AS maxId
FROM Events
WHERE ((StartDateTime<'2024-06-20 11:08:57')
 AND (MonitorId IN (5,6,20,15,33,37,38,41)))
GROUP BY MonitorId

*** We get the NEAREST PREVIOUS event before the specified date FOR ONE monitor: (1ms) SUPER
* ALL information in the answer will be correct.
* - I don’t use it yet.

Code: Select all

SELECT *
FROM Events
WHERE StartDateTime<'2024-06-20 11:08:57'
 AND MonitorId=5 ORDER BY StartDateTime DESC LIMIT 1;

*** We get the ABSOLUTELY LAST event FOR A GROUP of monitors: (90ms)
* Only the event ID will be correct in the answer, the rest will be random.
* Used extremely rarely, only at the time of grid formation to initially display a static frame instead of a black square

Code: Select all

SELECT MonitorId, StartDateTime, MAX(Id) AS maxId
FROM Events
WHERE MonitorId IN (5,6,20,15,33,37,38,41)
GROUP BY MonitorId

***Sampling by double clicking on TimeLine (Looking for events to play now FOR A GROUP of monitors) 80ms:
* ALL information in the answer will be correct.

Code: Select all

SELECT Id, MonitorId, StartDateTime, EndDateTime
FROM Events
WHERE StartDateTime < '2024-06-20 10:08:57'
 AND EndDateTime > '2024-06-20 10:08:57'
 AND MonitorId IN (5,6,20,15,33,37,38,41);

***Sampling for displaying events on TimeLine FOR A GROUP of monitors 2-4ms (SUPER FAST):
* ALL information in the answer will be correct.
*Used with Zoom & Pan Timeline

Code: Select all

SELECT MonitorId, StartDateTime, EndDateTime, Id
FROM Events
WHERE EndDateTime > '2024-06-18 10:08:57'
 AND StartDateTime < '2024-06-20 10:08:57'
 AND MonitorId IN (5,6,20,15,33,37,38,41);

Thus, fast queries of 1ms will be executed in the background; when navigating through the TimeLine, fairly fast queries of 2-4ms are executed. And slow queries of 80-90ms are executed extremely rarely.
I think this is a good solution.

[IgorA100]

There is one unpleasant moment that I don’t know how to solve yet.
When changing "Stream quality" (essentially changing Scale) or "Rate" I change the URL for the event. If the event is playing at this moment, it stops and starts playing from the "frame" originally specified in "src". As a result, playback becomes out of sync.
1. I could change “frame” in “src”, but firstly I don’t know how to get the current “frame”, and secondly, replacing “frame” takes time (while the frame is changing it will still jump, although not big)
2. You can try to stop the playback of all events and start them again approximately (+/- 1 second error) from the moment they were stopped.

How to be?

[iconnor]

We can add support for passing frame time instead of frame #.