How to delete video from the database?

  1. Main
  2. For clients
  3. FAQ
  4. Installation and configuration
  5. How to delete video from the database?

Do the following actions to free up the space on Server.

1. Disable nns service (Path: Control Panel\Administrative Tools\Services),

2. Open administrator command prompt,

3. Run PostgreSQL using the command

"C:\Program Files\KickidlerNode\pgsql\bin\pg_ctl.exe" start -D "C:/Program Files/KickidlerNode/pgsql/data" -w -o "--logging-collector=on --log-destination=stderr --log-directory=pg_log --log-filename=ipg-%a.log --log-truncate-on-rotation=on --log-rotation-age=1d --log-line-prefix=\"%m \" --log-timezone=\"Europe/Moscow\" --log-statement=ddl --log-connections=on --log-disconnections=on --listen-addresses=127.0.0.1 --port=5439 --max-connections=100 --shared-buffers=262144kB --wal-buffers=5242kB --effective-cache-size=734317kB --work-mem=7811kB --maintenance-work-mem=39059kB --checkpoint-segments=64 --checkpoint-completion-target=0.9"

4. Connect PostgreSQL using the command

"C:\Program Files\KickidlerNode\pgsql\bin\psql.exe" -h 127.0.0.1 -p 5439 -U kickidler_node

5.Check the video amount using the query

 select pos::date, pg_size_pretty(sum(octet_length(data))) from video_frame group by pos::date;

6.Delete the video using one of the following methods.

a)Deletion of all videos

Run the following commands to delete all videos:

truncate video_frame;
truncate video_sequence;

If errors appear after the command execution, repeat the query, inserting cascade word at the end.

b) Deletion of all videos for specific period

Run the following commands to delete data for specific date.

Display dates, for which there is video in the database:

select pos::date from video_frame group by pos::date;

Delete video for specific date:

delete from video_frame
WHERE pos::date = '20160523';

delete from video_sequence 
WHERE first_frame_pos::date = '20160523';

Delete video for several different dates:

delete from video_frame 
WHERE pos::date = '20160523' OR pos::date = '20160524' OR pos::date = '20160525';

delete from video_sequence 
WHERE first_frame_pos::date = '20160523' OR first_frame_pos::date = '20160524' OR first_frame_pos::date = '20160525';

Delete video for specific period:

delete from video_frame
WHERE pos::date >= '20160523' AND pos::date <= '20160525';

delete from video_sequence
WHERE first_frame_pos::date >= '20160523' AND first_frame_pos::date <= '20160525';

where 20160523 is the date, for which you want to delete the video (in YYYYMMDD format) 

c) Deletion of all videos for specific user

Run the following commands to delete data for specific user.

Display the list of user names-domain-computer name and video amount for them

SELECT online_session.user_name, online_session.user_domain, online_session.computer_name, pg_size_pretty(sum(octet_length(data))) 
FROM video_frame 
JOIN video_sequence ON (video_sequence.id=video_frame.sequence_id) 
JOIN online_session ON (video_sequence.online_session_id=online_session.id) 
GROUP BY online_session.user_name, online_session.user_domain, online_session.computer_name
ORDER BY sum(octet_length(data)) DESC;

Delete the employee’s video

DELETE FROM video_sequence WHERE id IN(
 SELECT vs.id FROM video_sequence AS vs
 JOIN online_session AS s ON(vs.online_session_id=s.id)
 WHERE
 s.user_name='Эдуард' AND
 s.computer_name='win10x64' AND
 s.user_domain='workgroup'
 LIMIT 100
);

Here s.user_name is the user name, s.computer_name is the computer name, s.user_domain is the domain name, for which you want to delete the video. If some of the parameters do not exist, leave them blank in the query. The limit of operations (Limit=100) is also displayed and can be changed if necessary.

7.Execute the command of quick space clear after completing commands of deletion.

vacuum full;