· 6 years ago · Oct 11, 2019, 03:50 PM
1#!/bin/bash
2#############################################################################
3#
4# Script: advanced_delete.sh
5#
6# Usage: ./advanced_delete.sh -r "DATE/TIME RANGE"
7# [ -d "DN1 [ DN2 DN3 DN-RANGE DN-RANGE2 ... ]" ]
8# [ -e "KEY=VALUE1|[VALUE2]|[KEY2=VALUE1]...]" ]
9# [ -n "KEY1|KEY2|..." ]
10# [ -f "/FILE/PATH" ]
11# [ -l "LENGTH SPECIFICATION"] [ -t "TEXT DESCRIPTION" ]
12# [ -D ]
13# [ -C [ -a | -p | -i | -v | -s ] ]
14# [ -A ] [ -S ] [ -P ] [ -R ] [ -T ]
15# [ -m ]
16# [ -h | -x ] [ -N ]
17#
18# Description: This script deletes calls from DB and
19# media files from the filesystem based on provided parameters.
20# By default the script deletes all media files and DB records.
21#
22# Options: -h show help
23# -x show more detailed help
24# -r specify date/time range of processed couples
25# -d specify DNs (calling&called numbers) as a condition
26# -e specify extdata KVPs that are present in couples' metadata
27# -n specify extdata keys that are NOT present in couples' metadata
28# -f specify path to media files
29# -l specify length of processed couples
30# -t specify text of description field of processed couples
31# -D delete ONLY DB records && keep media files on the filesystem
32# (if used with -C, calls and couples are kept,
33# cfiles are deleted and couples are marked as deleted)
34# -C delete just cfiles DB records and media files && mark couples as deleted
35# (if used with -D, media files are not deleted)
36# -a delete only AUDIO(mp3,wav) files, must be used together with -C flag
37# -p delete only PCAP files, must be used together with -C flag
38# -i delete only INDEX files, must be used together with -C flag
39# -v delete only VIDEO(mp4,avi) files, must be used together with -C flag
40# -s delete only RECD files, must be used together with -C flag
41# -A delete ONLY archived couples (A)
42# -S delete ONLY synchronized couples (S,N,E)
43# -P delete also protected couples
44# -R delete also restored couples (R,Q)
45# -T delete also partially archived couples, must be used together with -A flag
46# -m do not send amqp messages to Encourage
47# -N do not delete anything (dry run), only log IDs/paths of DB records/files that would be deleted
48# Expert options:
49#(do not use if you do not know the consequences!)
50# -M set max limit of DNs per job
51# -Q set max limit of DB records processed per cycle
52#
53#
54# Author: Jiri Valenta
55#
56# Version: 1.7
57#
58# History: 1.7 - enhanced performance of AMQP messages generation
59# 1.6 - added support for 6.4, 6.5
60# - the script now, by default, sends messages via AMQP about deleted data
61# - added option (-m) to disable this behavior
62# 1.5 - added possibility to specify non-existent external data keys
63# 1.4 - added dry run option
64# - added support for "starts with", "ends with" and "contains" for option -e
65# 1.3 - added support for 5.8, 6.0, 6.1
66# - dropped support for older versions due to DB structure changes
67# 1.2 - added logging of progress to DB operations that go through multiple loops
68# - added logging for start of media files removal
69# - added support for versions 5.5 and 5.6
70# - updated help/examples
71# 1.1 - restored calls are now not deleted by default, an option (-R) was added to override the new default behavior
72# - separated cfile selections to AUDIO(mp3,wav), PCAP, IMAGE(index files), VIDEO(mp4,avi) and RECD
73# - added possibility to specify length of couples to be deleted
74# - added possibility to specify description of couples to be deleted
75# - added possibility to specify file path to the media files
76# - added possibility to specify the 'float' and 'older than' date/time intervals in minutes
77# 1.0 - initial version
78#
79# Copyright: (c) ZOOM INTERNATIONAL
80#
81#############################################################################
82
83# DATABASE CONFIGURATION:
84DBHOST="127.0.0.1"
85DBUSER="postgres"
86DBNAME="callrec"
87
88# DO NOT MODIFY ANYTHING BELOW THIS POINT
89
90# Determine absolute path to this script
91MYDIR="$(dirname $(readlink -f $0))"
92
93# Log file location
94LOGFILE=/opt/callrec/logs/adv_delete.log
95
96# Lock file (PID file) location
97LOCKFILE=/var/lock/adv_delete.lock
98
99function print_usage {
100# Desc: Print usage
101# Input: none
102
103echo "
104Usage: ./advanced_delete.sh -r \"DATE/TIME RANGE\"
105 [ -d \"DN1 [ DN2 DN3 DN-RANGE DN-RANGE2 ... ]\" ]
106 [ -e \"KEY=VALUE1|[VALUE2]|[KEY2=VALUE1]...]\" ]
107 [ -n \"KEY1|KEY2|...\" ]
108 [ -f \"/FILE/PATH\" ]
109 [ -l \"LENGTH SPECIFICATION\"] [ -t \"TEXT DESCRIPTION\" ]
110 [ -D ]
111 [ -C [ -a | -p | -i | -v | -s ] ]
112 [ -A ] [ -S ] [ -P ] [ -R ] [ -T ]
113 [ -h | -x ] [ -N ]
114Options:
115 -r <- specify range of processed couples example: -r \"older than X minutes/days/months\"
116 or: -r \"from yyyy-mm-dd hh:mm:ss to yyyy-mm-dd hh:mm:ss\"
117 or: -r \"float X minutes/days/months Y minutes/days/months\"
118 -d <- specify DN(s), example: -d \"9001 9002 9004-9009 9013\"
119 -e <- specify extdata key and value(s) example: -e \"GEN_TEV_AgentID=1000|1001|1002|GEN_USR_Department=Marketing|&GEN_CFG_Tenant=Alpha|Beta\"
120 -n <- specify non-existent extdata keys example: -n \"GEN_USR_CustomTag|MY_Custom_Tag\"
121 -f <- specify path to media files example: -f \"/opt/callrec/data/calls/\"
122 (trailing slash - this will match any files inside the specified folder)
123 or: -f \"/opt/callrec/data/calls\"
124 (no trailing slash - this will also match other directories like \"/opt/callrec/data/calls2\")
125 -l <- specify length of couples (in seconds) example: -l \"eq 90\" (=equals to 90 seconds)
126 or: -l \"lt 30\" (=shorter than 30 seconds)
127 or: -l \"gt 180\" (=longer than 3 minutes)
128 or: -l \"between 5 15\" (=longer than 5 seconds AND shorter than 15 seconds)
129 -t <- specify text of description field example: -t \"equals useless call\"
130 or: -t \"starts complaint\"
131 or: -t \"ends something\"
132 or: -t \"contains something else\"
133 -D <- delete only DB records, keep media files on the filesystem
134 -C <- delete only media files, mark DB records as deleted
135 -a <- delete only audio(mp3,wav) files, must be used together with -C flag
136 -p <- delete only PCAP files, must be used together with -C flag
137 -i <- delete only index files, must be used together with -C flag
138 -v <- delete only video(mp4,avi) files, must be used together with -C flag
139 -s <- delete only RECD files, must be used together with -C flag
140 -A <- delete only archived couples
141 -S <- delete only synchronized couples
142 -P <- delete also protected couples
143 -R <- delete also restored couples
144 -T <- delete also partially archived couples, must be used together with -A flag
145 -m <- do not send AMQP messages about deleted data to Encourage
146 -N <- do not delete anything, only log IDs/paths of DB records/files that would get deleted
147 -h <- show this help
148 -x <- show detailed help with descriptions and examples
149" >&2
150rm -f $LOCKFILE
151exit
152}
153
154function print_examples {
155# Desc: Print more detailed usage
156# Input: none
157
158echo "
159# DATE/TIME RANGE
160#
161# -r \"older than X months|days|minutes\" | \"from yyyy-mm-dd hh:mm:ss to yyyy-mm-dd hh:mm:ss\" | \"float 2 days 1 month\"
162#
163# Select range of calls that should be processed.
164# This range MUST be configured. Only 1 range can be set up for each job.
165# You can use either \"older than\" or \"from - to\" or \"floating\" interval type
166#
167# If you plan to run the script on a scheduled basis, it is recommended
168# to use the \"floating\" closed interval.
169# When properly configured, it has the smallest footprint on the database.
170#
171# When setting up the \"from - to\" interval, make sure that you
172# follow this syntax: \"from YYYY-MM-DD HH:MM:SS to YYYY-MM-DD HH:MM:SS\"
173#
174# When setting up the \"older than\" interval, you can use \"days\" or \"months\"
175#
176# Examples:
177Select calls older than 14 days:
178-r \"older than 14 days\"
179
180Select calls older than 3 months:
181-r \"older than 3 months\"
182
183Select calls older than 5 minutes:
184-r \"older than 5 minutes\"
185
186Select calls from specific from-to interval, for example the whole January 2013:
187-r \"from 2013-01-01 00:00:00 to 2013-01-31 23:59:59\"
188
189Select calls older than 2 days AND newer than 1 month (i.e. from 1 month back till 2 days ago):
190-r \"float 2 days 1 month\"
191
192# EXTENSION DNs
193#
194# -d \"DN|DN-RANGE [DN2|DN-RANGE2 ...]\"
195#
196# Select DN(s) whose calls should be purged.
197# If multiple values are specified, they are joined with 'OR'
198# You can specify either single DNs, or a range of DNs in a format: STARTDN-ENDDN,
199# or combinations, like displayed below.
200#
201# Examples:
202Select calls made from/to DN 9001
203-d \"9001\"
204
205Select calls made from/to DN 9001 OR 9002 OR 9003 OR 9004
206-d \"9001-9004\"
207
208Select calls made from/to DN 9001 OR 9005 OR 9006 OR 9007 OR 9008 OR 9010
209-d \"9001 9005-9008 9010\"
210
211# EXTDATA KEY+VALUES
212#
213# -e \"KEY=VALUE1|[VALUE2|VALUE3 ...]\" <- equals (exact match)
214# -e \"KEY=SW~VALUE1|[VALUE2|VALUE3 ...]\" <- starts with
215# -e \"KEY=EW~VALUE1|[VALUE2|VALUE3 ...]\" <- ends with
216# -e \"KEY=CT~VALUE1|[VALUE2|VALUE3 ...]\" <- contains
217#
218# Select extdata key and value(s) as a condition, each element has to be separated using a pipe character '|'
219# Values separated by spaces are simply considered as a single string that contains spaces
220#
221# If multiple values for a single key are specified, they are joined with OR.
222#
223# By default the KVPs are joined with OR. If you want to join specific KVPs with AND,
224# prepend '&' to the key name. (see example below)
225#
226# Examples:
227Selects calls with GEN_USR_CAMPAIGN_NUMBER=15:
228-e \"GEN_USR_CAMPAIGN_NUMBER=15\"
229
230Select calls with GEN_USR_CAMPAIGN_NUMBER=14 OR GEN_USR_CAMPAIGN_NUMBER=23
231-e \"GEN_USR_CAMPAIGN_NUMBER=14|23\"
232
233Select calls with (AgentID=1111 OR AgentID=2222) OR (SomeData=Bad rating OR SomeData=Good rating OR SomeData=Some other rating):
234-e \"GEN_TEV_AgentID=1111|2222|GEN_USR_SomeData=Bad rating|Good rating|Some other rating\"
235
236Select calls with (AgentID=1234) AND (Tenant=Resources):
237-e \"GEN_TEV_AgentID=1234|&GEN_CFG_Tenant=Resources\"
238
239# NON-EXISTENT EXTDATA KEYS
240#
241# -n \"KEY1\"
242# -n \"KEY1|KEY2|KEY3|...\"
243#
244# Specify extdata keys that are NOT present in the couples' external data.
245# Multiple keys can be specified, seperated by pipe '|'
246#
247# Examples:
248Select calls that do not have MY_TAG in their extdata
249-n \"MY_TAG\"
250
251Select calls that do not have keys TAG1, TAG2 and TAG3 in their external data
252-n \"TAG1|TAG2|TAG3\"
253
254# FILE PATH
255#
256# -f \"/opt/callrec/data/calls\"
257#
258# By specifying the path to media files, one can limit the selection
259# of calls being deleted.
260# Please note that if option -C is not specified, then if a couple has one of its cfiles in
261# given path, then all other cfiles are deleted too.
262# This is due to the cascade-style relations between the database objects, and once a couple is deleted
263# then all its related cfiles are deleted as well.
264#
265# Examples:
266Delete the media files from the exact specified folder (trailing slash):
267-f \"/opt/callrec/data/calls2/\"
268
269Delete the media from several folders (no trailing slash):
270-f \"/mnt/data/call\" Note that this will match any directories under /mnt/data which start with \"call\"
271 for example /mnt/data/callrec
272 /mnt/data/calls
273 /mnt/data/calligraphy
274 /mnt/data/calls-imporant-backup/DO_NOT_DELETE
275
276# LENGTH
277#
278# -l \"lt INT | gt INT | between INT1 INT2\"
279#
280# Select couples only with specified length.
281# You can specify to delete couples which are either shorter than X seconds,
282# longer than X seconds, or the length is between X and Y seconds.
283#
284# Examples:
285Select couples with length of 10 seconds:
286-l \"eq 10\"
287
288Select couples longer than 5 minutes:
289-l \"gt 300\"
290
291Select couples with length between 5 to 30 seconds:
292-l \"between 5 30\"
293
294# DESCRIPTION
295#
296# -t \"equals STRING | contains STRING | ends STRING | starts STRING\"
297#
298# Specify a string to match the description of couples to be deleted
299# The first word of the argument is either 'equals', 'contains', 'ends' or 'starts',
300# which specifies how the string will be matched against the data in the DB.
301#
302# Examples:
303Select couples which contain the exact string \"bad call\" in the description field:
304-t \"equals bad call\"
305
306Select couples with description where the value of this field starts with \"123\":
307-t \"starts 123\"
308
309# DELETE ONLY DB RECORDS
310#
311# -D
312#
313# By default this script deletes all media files and database records for given calls.
314# Use the -D parameter if you want to delete only the DB records, but keep the media files on the filesystem.
315#
316# Please keep in mind that this results in having media files on the filesystem with no DB links.
317# This option should be used only in certain scenarios.
318# Do not use it if you are unsure about the consequences!
319#
320# Example:
321-D
322
323# DELETE ONLY MEDIA FILES
324#
325# -C
326#
327# By default this script deletes all media files and database records for given calls.
328# Use the -C parameter if you want only to delete the media files, but keep the DB records (which will be marked deleted).
329# Note that DB records in 'cfiles' table will be deleted, otherwise they would point to non-existent files.
330# DB records in 'couples' table will be marked as deleted, but only if they end up having zero linked records in 'cfiles' table.
331# DB records in 'calls' table remain untouched
332#
333# Example:
334-C
335
336# DELETE ONLY AUDIO (MP3,WAV) MEDIA FILES
337#
338# -a
339#
340# This flag must be used in combination with the -C flag.
341# Can be used if you want to delete only audio files, when deleting media files.
342#
343# Example:
344-C -a
345
346# DELETE ONLY PCAP MEDIA FILES
347#
348# -p
349#
350# This flag must be used in combination with the -C flag.
351# Can be used if you want to delete only PCAP files, when deleting media files.
352#
353# Example:
354-C -p
355
356# DELETE ONLY INDEX MEDIA FILES
357#
358# -a
359#
360# This flag must be used in combination with the -C flag.
361# Can be used if you want to delete only index files, when deleting media files.
362#
363# Example:
364-C -i
365
366# DELETE ONLY VIDEO (MP4,AVI) MEDIA FILES
367#
368# -v
369#
370# This flag must be used in combination with the -C flag.
371# Can be used if you want to delete only video files, when deleting media files.
372#
373# Example:
374-C -v
375
376# DELETE ONLY RECD MEDIA FILES
377#
378# -s
379#
380# This flag must be used in combination with the -C flag.
381# Can be used if you want to delete only RECD files, when deleting media files.
382#
383# Example:
384-C -s
385
386# SYNCHRONIZED CALLS
387#
388# -S
389#
390# Use the -S parameter if you want to process only calls that have been marked as synchronized with either one flags below:
391# synchronized='S' (synchronized and used)
392# synchronized='N' (synchronized and not used)
393# synchronized='E' (only external data synchronized)
394#
395# Example:
396-S
397
398# ARCHIVED CALLS
399#
400# -A
401#
402# Use the -A parameter if you want to process only calls that have been archived.
403# Note that only fully archived calls are deleted (i.e. all cfiles of given segment have been archived)
404#
405# Example:
406-A
407
408# PARTIALLY ARCHIVED CALLS
409#
410# -T
411#
412# This flag must be used in combination with the -A flag.
413# Use the -T parameter if you want to process calls that have been only partially archived.
414# (i.e. only some cfiles of given segment have been archived)
415#
416# Example:
417-A -T
418
419# PROTECTED CALLS
420#
421# -P
422#
423# By default, this script does not delete protected calls
424# If you want it to also delete calls that are protected, use the -P flag.
425#
426# Example:
427-P
428
429# RESTORED CALLS
430#
431# -R
432#
433# By default the script does not delete couples which have been restored,
434# or potentially also couples that are queued for restoration.
435# To override this behavior, supply this parameter to the script in order
436# to delete couples with restored='R' or restored='Q'.
437#
438# Example:
439-R
440
441# NO AMQP MESSAGES
442#
443# -m
444#
445# By default, this script will send messages via AMQP to Encourage
446# This is required for versions 6.4 and higher when QM is enabled,
447# as the standard Delete tool sends those messages so that Encourage knows
448# what was deleted.
449# When running this on an older version, or when QM is not enabled, use this option
450# to disable sending these messages
451#
452# Example:
453-m
454
455# DRY RUN
456#
457# -N
458#
459# When this option is specified, the script will not delete any data.
460# Instead, it will write the IDs of DB records and file paths that would be deleted
461# into a text file in a temp folder.
462#
463# Example:
464-N
465
466" | less
467rm -f $LOCKFILE
468exit
469}
470
471function root_user_check {
472# Desc: Check if the script was started under 'root' user
473# Input: none
474
475 if [[ $EUID -ne 0 ]]; then
476 bail_out "User is not root. Aborting..."
477 fi
478}
479
480function lockfile_check {
481# Desc: Check if another instance of the script is running
482# Input: none
483
484# If lockfile doesn't exist, let's create a new one and store our PID into the file.
485 if [[ ! -e $LOCKFILE ]]; then
486 echo $$ > $LOCKFILE
487# If it exists, check if there's a running process with the PID from the file
488 else
489 ps -p < $(LOCKFILE) > /dev/null 2>/dev/null
490 if [[ $? -ne 0 ]]; then
491 echoo "Lock file exists, but process is not running. Ignoring..."
492 echo $$ > $LOCKFILE
493 else
494 echoo "Lock file exists and PID exists i.e. another instance is running. Aborting..."
495 exit 1
496 fi
497 fi
498}
499
500function db_version_check {
501# Desc: Check if the db version is supported
502# Input: none
503
504# Define supported versions:
505# This script was tested on version 6.1, 6.4 and 6.5
506# If you want to use it on a different version, please test it
507# on some lab server first, then add the major version into
508# the DB_SUPP_VERSIONS variable and run the script again.
509 DB_SUPP_VERSIONS="5.8 6.0 6.1 6.2 6.3 6.4 6.5"
510
511# Determine the DB version
512 DB_VERSION=$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "select get_version();" | tr -d " ")
513
514# Check if the version is supported
515 for DB_VERS_ITEM in $DB_SUPP_VERSIONS; do
516 if [[ $DB_VERSION == $DB_VERS_ITEM* ]]; then
517 return
518 fi
519 done
520 # If we get to this point, neither of supported DB versions
521 # have matched the version reported by the DB itself.
522 bail_out "Detected version of database ($DB_VERSION) is not supported by this script. Aborting..."
523}
524
525function set_defaults {
526# Desc: Set some default values to work with
527# Input: none
528
529DB_ONLY=0
530CFILES_ONLY=0
531CFILES_SUM=0
532AUDIO_ONLY=0
533PCAP_ONLY=0
534INDEX_ONLY=0
535VIDEO_ONLY=0
536RECD_ONLY=0
537
538LENGTH_SET=0
539DESCRIPTION_SET=0
540FILE_PATH_SET=0
541DEL_PROTECTED=0
542DEL_RESTORED=0
543
544MEDIA_COND_CFTYPES=""
545CFTYPE_COND_PRE_QUERY=""
546
547DRY_RUN=0
548}
549
550function pre_flight_check {
551# Desc: Check if mandatory parameters have been provided
552# Input: none
553
554# Check if at least one DN or one extdata KVP has been provided
555 IS_DN_LIST_EMPTY=$(printf "%s" "$DN_LIST" | tr -d " ")
556 IS_EXT_EMPTY=$(printf "%s" "$EXTDATA" | tr -d " ")
557 IS_MISSING_EXT_EMPTY=$(printf "%s" "$MISSING_EXTDATA" | tr -d " ")
558 # No actual check here anymore, but we still use this information later
559
560# Check if range has been provided
561 IS_RANGE_EMPTY=$(printf "%s" "$RANGE" | tr -d " ")
562 if [[ -z $IS_RANGE_EMPTY ]]; then
563 bail_out "Script is not properly configured. Please specify proper range of calls to be processed."
564 fi
565
566# Check if -C flag is present in case that -a/-p/-i/-v/-s is present
567 if [[ $CFILES_ONLY -eq 0 ]] && [[ $(wc -w <<< $MEDIA_COND_CFTYPES) -gt 0 ]]; then
568 bail_out "Flags -a/-p/-i/-v/-s must be used only in combination with -C flag. Aborting..."
569 fi
570
571# Check if we should care about protected couples
572 if [[ $DEL_PROTECTED -eq 1 ]]; then
573 # Unlock and delete protected couples
574 # (unlock is handled later on, here we just unset the condition)
575 DEL_PROT_QUERY=""
576 else
577 # Do not delete protected couples - set appropriate condition
578 DEL_PROT_QUERY="AND (couples.protected=false OR couples.protected IS NULL)"
579 fi
580
581# Check if we should care about restored calls
582 if [[ $DEL_RESTORED -eq 1 ]]; then
583 # Do not set condition for restored calls,
584 # i.e. delete them as well as others.
585 RESTORED_QUERY=""
586 else
587 # Keep restored calls - set condition
588 # to delete only calls NOT marked by Restore tool
589 RESTORED_QUERY="AND couples.restored IS NULL"
590 fi
591
592# Check if we should delete also only partially archived calls
593 if [[ $PARTIALLY_ARCHIVED -eq 1 ]] && [[ -z $ARC_QUERY ]]; then
594 bail_out "Flag -T can be used only in combination with -A flag. Aborting..."
595 elif [[ $PARTIALLY_ARCHIVED -eq 1 ]]; then
596 ARC_QUERY="AND couples.archived='A'"
597 CFILE_ARC_QUERY=""
598 fi
599
600# Related to advanced settings
601# If -M is not provided, use the default value as a max count of DNs per job
602 if [[ -z $DN_LIMIT ]] || [[ $DN_LIMIT = *[!0-9]* ]]; then
603 DN_LIMIT=3000
604 fi
605 DN_LIMIT_PLUS=$((DN_LIMIT + 1))
606
607# If -Q is not provided, use the default value of a maximum 10000 records to be processed at once
608 if [[ -z $QUERY_LIMIT ]] || [[ $QUERY_LIMIT = *[!0-9]* ]]; then
609 QUERY_LIMIT=10000
610 fi
611 QUERY_LIMIT_PLUS=$((QUERY_LIMIT + 1))
612}
613
614function parse_input {
615# Desc: Parse and verify provided input parameters
616# Inputs: None
617
618# Parse provided date/time range - common for all combinations, generate SQL parameter ($RANGE_QUERY)
619 verify_range "$RANGE"
620
621# Prepare DNs
622 if [[ -n $IS_DN_LIST_EMPTY ]]; then
623 # Parse provided DNs and generate array of DNs to be processed by wrapper ($DN_QUERY_LIST)
624 prepare_dns "$DN_LIST"
625 fi
626
627# Prepare extdata
628 if [[ -n $IS_EXT_EMPTY ]]; then
629 # Parse provided extdata and generate SQL parameter ($EXTDATA_QUERY)
630 prepare_extdata "$EXTDATA"
631 fi
632
633# Prepare non-existent extdata keys
634 if [[ -n $IS_MISSING_EXT_EMPTY ]]; then
635 # Parse provided keys and generate SQL parameter ($MISSING_EXTDATA_QUERY)
636 prepare_missing_extdata "$MISSING_EXTDATA"
637 fi
638
639# Verify length (if specified) and generate SQL parameter ($LENGTH_QUERY)
640 if [[ $LENGTH_SET -eq 1 ]]; then
641 verify_length "$LENGTH"
642 fi
643
644# Verify text description (if specified) and generate SQL parameter ($DESC_QUERY)
645 if [[ $DESCRIPTION_SET -eq 1 ]]; then
646 verify_description "$DESCRIPTION"
647 fi
648
649# Verify provided file path and generate SQL parameter ($FILE_PATH_QUERY)
650 if [[ $FILE_PATH_SET -eq 1 ]]; then
651 verify_file_path "$FILE_PATH"
652 fi
653
654# Check specified cftypes, if any, and generate SQL parameter
655 if [[ -n $MEDIA_COND_CFTYPES ]]; then
656 check_cftype_selections
657 fi
658}
659
660function start_wrapper {
661# Desc: Run preparation and execution tasks
662# Inputs: None
663
664# If any DNs have been provided, loop through the dn lists,
665# generated by the prepare_dns function
666 if [[ ${#DN_QUERY_LIST[@]} -gt 0 ]]; then
667 # Loop through all DN lists
668 TOTALJOBS="${#DN_QUERY_LIST[@]}"
669 for DN_Q_ID in "${!DN_QUERY_LIST[@]}"; do
670 if [[ ${#DN_QUERY_LIST[@]} -gt 1 ]]; then
671 echoo "Running job #$DN_Q_ID out of total $TOTALJOBS"
672 echoo "Currently processing DNs starting ${DN_LIST_START[$DN_Q_ID]} and ending ${DN_LIST_STOP[$DN_Q_ID]}"
673 fi
674 # Construct SQL parameter
675 DN_QUERY="AND (couples.callingnr IN (${DN_QUERY_LIST[$DN_Q_ID]}) OR couples.originalcallednr IN (${DN_QUERY_LIST[$DN_Q_ID]}) OR couples.finalcallednr IN (${DN_QUERY_LIST[$DN_Q_ID]}))"
676 # Prepare SQL queries
677 prepare_queries
678 # Execute delete operations
679 execute_ops
680 # Flush some variables
681 flush_vars
682 done
683# Else - no DNs were provided as a condition,
684# therefore just execute the operations
685 else
686 # Prepare SQL queries
687 prepare_queries
688 # Execute delete operations
689 execute_ops
690 fi
691}
692
693function prepare_queries {
694# Desc: Check if we are searching by DN, extdata or range (or combinations) and generate SQL queries
695# Input: none
696
697# Add JOIN condition if extdata are filtered
698 if [[ -n $IS_EXT_EMPTY ]] || [[ -n $IS_MISSING_EXT_EMPTY ]]; then
699 EXTDATA_JOIN_QUERY="JOIN couple_fixed_extdata ON couples.id=couple_fixed_extdata.cplid"
700 else
701 EXTDATA_JOIN_QUERY=""
702 fi
703
704# Same for file path filtering
705 if [[ $FILE_PATH_SET -eq 1 ]]; then
706 CFILES_JOIN_QUERY="JOIN cfiles ON couples.id=cfiles.cplid"
707 else
708 CFILES_JOIN_QUERY=""
709 fi
710
711 # Set up queries for both DN and extdata selection
712 SELECT_CFPATHS_VAR="SELECT cfpath, cftype, sid FROM cfiles
713 JOIN couples ON cfiles.cplid=couples.id
714 $EXTDATA_JOIN_QUERY
715 WHERE 1=1
716 $DN_QUERY $EXTDATA_QUERY $MISSING_EXTDATA_QUERY $RANGE_QUERY
717 $FILE_PATH_QUERY $LENGTH_QUERY $DESC_QUERY
718 $CFILE_ARC_QUERY $SYNC_QUERY $MEDIA_COND_QUERY
719 $DEL_PROT_QUERY $RESTORED_QUERY;"
720 SELECT_CPLIDS_VAR="SELECT couples.id FROM couples
721 $EXTDATA_JOIN_QUERY $CFILES_JOIN_QUERY
722 WHERE (couples.deleted!='D' OR couples.deleted is null)
723 $DN_QUERY $EXTDATA_QUERY $MISSING_EXTDATA_QUERY $RANGE_QUERY
724 $FILE_PATH_QUERY $LENGTH_QUERY $DESC_QUERY
725 $MEDIA_ARC_QUERY $SYNC_QUERY $CFTYPE_COND_QUERY
726 $DEL_PROT_QUERY $RESTORED_QUERY;"
727 SELECT_CPLID_CALLID_VAR="SELECT couples.id, callid, sid FROM couples
728 $EXTDATA_JOIN_QUERY $CFILES_JOIN_QUERY
729 WHERE 1=1
730 $DN_QUERY $EXTDATA_QUERY $MISSING_EXTDATA_QUERY $RANGE_QUERY
731 $FILE_PATH_QUERY $LENGTH_QUERY $DESC_QUERY
732 $MEDIA_ARC_QUERY $SYNC_QUERY $CFTYPE_COND_QUERY
733 $DEL_PROT_QUERY $RESTORED_QUERY;"
734}
735
736function execute_ops {
737# Desc: Execute file and DB operations
738# Input: none
739
740# If 'delete DB records only' option (-D) was not specified,
741# delete media files from the filesystem
742 if [[ $DB_ONLY -ne 1 ]]; then
743 # SELECT paths to media files
744 db_query SELECT_CFPATHS "$SELECT_CFPATHS_VAR"
745 # Unless this is a dry run, remove media files from the filesystem
746 if [[ $DRY_RUN -eq 1 ]]; then
747 echoo "Dry run only - list of files that would be deleted to will be written to: $TEMP_DIR/cfiles.txt"
748 echo $CFPATHS >> $TEMP_DIR/cfiles.txt
749 else
750 remove_files
751 fi
752 fi
753
754# UPDATE/DELETE database records
755 # Only delete cfiles database records (with -C parameter), keep calls & couples
756 if [[ $CFILES_ONLY -eq 1 ]]; then
757 # Select couple IDs to be processed
758 db_query SELECT_CPLIDS "$SELECT_CPLIDS_VAR"
759 # If no results were found, return from this function
760 if [[ $? -ne 0 ]]; then
761 return
762 # If this is a dry run, just log couple IDs to be removed and return
763 elif [[ $DRY_RUN -eq 1 ]]; then
764 echoo "Dry run only - list of couple IDs that would be deleted to will be written to: $TEMP_DIR/coupleids.txt"
765 echo $CPLIDS >> $TEMP_DIR/coupleids.txt
766 return
767 fi
768 # Unprotect protected couples (if -P flag was specified)
769 if [[ $DEL_PROTECTED -eq 1 ]]; then
770 db_query SPLIT UNPROTECT_COUPLES "$CPLIDS"
771 fi
772 # Delete database records in cfiles table
773 db_query SPLIT DELETE_CFILES_RECORDS "$CPLIDS"
774 # Check if we should refresh the list of couples
775 db_query SPLIT SELECT_CPLIDS_MARK "$CPLIDS"
776 # Mark couples that ended up with zero cfiles as deleted
777 db_query SPLIT UPDATE_RECORDS "$CPLIDS_TO_MARK"
778 # Send updates to Encourage
779 send_updates_to_enc MEDIA "$SIDS_CFTYPES"
780 else
781 # Else we should just delete everything (without -C parameter)
782 # First let's retrieve call IDs that we work with - so that later we can check
783 # if any are left with realcplcnt=0 and delete them
784 db_query SELECT_CPLID_CALLID "$SELECT_CPLID_CALLID_VAR"
785 # If no results were found, return from this function (and go to another job, if applicable)
786 if [[ $? -ne 0 ]]; then
787 return
788 # If this is a dry run, just log couple IDs to be removed and return
789 elif [[ $DRY_RUN -eq 1 ]]; then
790 echoo "Dry run only - list of couple IDs that would be deleted to will be written to: $TEMP_DIR/coupleids.txt"
791 echo $CPLIDS >> $TEMP_DIR/coupleids.txt
792 return
793 fi
794 # Unprotect protected couples (if -P flag was specified)
795 if [[ $DEL_PROTECTED -eq 1 ]]; then
796 db_query SPLIT UNPROTECT_COUPLES "$CPLIDS"
797 fi
798 # If only fully archived couples should be deleted, delete archived cfiles first,
799 # then reload the list of couples and fetch only the fully archived ones
800 if [[ $PARTIALLY_ARCHIVED -ne 1 ]] && [[ -n $ARC_QUERY ]]; then
801 # Delete database records in cfiles table
802 db_query SPLIT DELETE_CFILES_RECORDS "$CPLIDS"
803 # Filter out partially archived couples
804 db_query SPLIT FILTER_COUPLE_IDS "$CPLIDS"
805 CPLIDS=$(echo -e "$FILTERED_RES" | awk -F '|' '{print $1}' | sort -n | uniq)
806 CPL_SIDS=$(echo -e "$FILTERED_RES" | awk -F '|' '{print $2}' | sort -n | uniq)
807 fi
808 # Delete couples
809 db_query SPLIT DELETE_COUPLES_RECORDS "$CPLIDS"
810 # Delete calls (only those with realcplcnt=0)
811 db_query SPLIT DELETE_RECORDS "$CALLIDS"
812 # Send updates to Encourage
813 send_updates_to_enc SEGMENT "$CPL_SIDS"
814 fi
815}
816
817function prepare_dns {
818# Desc: Parse provided DNs (argument of -d), construct SQL parameter
819# Input: $1 = DN(s) or DN range(s) from user input
820
821# Make the DN list usable for SQL query
822 for dn in $1; do
823 # Try to detect a DN range
824 if [[ $dn == *-* ]]; then
825 DN_RNG_START=$(awk -F'-' '{print $1}' <<< $dn)
826 DN_RNG_END=$(awk -F'-' '{print $2}' <<< $dn)
827 # Check if the range seems valid
828 if [[ ! $DN_RNG_START -lt $DN_RNG_END ]]; then
829 bail_out "Invalid DN range specified ($dn). Aborting..."
830 fi
831 DN_PARSED_RANGE="$(eval echo {$DN_RNG_START..$DN_RNG_END})"
832 DN_WORK_LIST="$DN_WORK_LIST $DN_PARSED_RANGE"
833 else
834 DN_WORK_LIST="$DN_WORK_LIST $dn"
835 fi
836 done
837
838 # Remove any duplicates
839 DN_WORK_LIST="$(echo $DN_WORK_LIST | sed -e 's/ /\n/g' | sort -n | uniq)"
840
841 # Check if we should split the execution jobs,
842 # in case that more than -M <value> (default 3000) DNs were provided
843 DN_COUNT=$(wc -w <<< $DN_WORK_LIST)
844 if [[ $DN_COUNT -gt $DN_LIMIT ]]; then
845 echo "INFO: You have specified $DN_COUNT DNs, which is more than $DN_LIMIT DNs.
846 The delete job will be split into multiple jobs,
847 each covering a maximum of $DN_LIMIT DNs.
848 Depending on your input, the numbers inside the ranges
849 displayed below may not necessarily be in a sequence."
850 fi
851
852 # Split the DN list into array, each value with a maximum of -M <value> DNs
853 DNLIST_ID=1
854 while [[ $(wc -w <<< $DN_WORK_LIST) -gt 0 ]]; do
855 # Get the next $DN_LIMIT (-M) DNs to process
856 TMP_DN_LIST=$(head -n $DN_LIMIT <<< "$DN_WORK_LIST")
857 # Get start-stop of the range (may not be continuous, it is just for some verbose output)
858 DN_LIST_START[$DNLIST_ID]=$(head -n 1 <<<"$TMP_DN_LIST")
859 DN_LIST_STOP[$DNLIST_ID]=$(tail -n 1 <<< "$TMP_DN_LIST")
860 # Prepare the SQL parameter
861 DN_QUERY_LIST[$DNLIST_ID]=$(tr '\n' ' ' <<< "$TMP_DN_LIST" | sed -e "s/^/\'/g" -e "s/ $/\'/g" -e "s/ /','/g")
862 # Remove processed DNs from the work list
863 DN_WORK_LIST=$(tail -n +$DN_LIMIT_PLUS <<< "$DN_WORK_LIST")
864 ((DNLIST_ID++))
865 done
866}
867
868function prepare_extdata {
869# Desc: Parse extdata KVP(s) provided as -e parameter from user input, prepare SQL query params
870# Input: $1 = the whole value of the -e parameter
871
872# Prepare input
873 declare ITEM_ARR_KEY
874 declare ITEM_ARR_VALUE
875 declare ITEM_ARR_JOINER
876 declare ITEM_ARR_COMPARATOR
877 # We expect that the elements are separated by pipe:
878 IFS='|'
879 KEY_ID=0
880 for ITEM in $1; do
881 # Try to detect if this element contains Key definition
882 if [[ $ITEM == *=* ]]; then
883 # Increment key ID
884 ((KEY_ID++))
885 # If equals sign has been found in the element, we expect that
886 # the string before '=' is the key and the string after '=' is the first value
887 ITEM_KEY=$(sed -e 's/=.*//g' <<< $ITEM)
888 # If '&' char is detected at the beginning of the key,
889 # we will join the current and previous KVPs with AND
890 if [[ $ITEM_KEY == \&* ]]; then
891 ITEM_KEY=${ITEM_KEY#&}
892 ITEM_ARR_JOINER[$KEY_ID]="AND"
893 else
894 ITEM_ARR_JOINER[$KEY_ID]="OR"
895 fi
896 # Check if the key is unique
897 if [[ ${ITEM_ARR_KEY[@]} =~ $ITEM_KEY ]]; then
898 bail_out "Each extdata key can be defined only once at maximum. Aborting..."
899 else
900 ITEM_ARR_KEY[$KEY_ID]="$ITEM_KEY"
901 fi
902 # Check if LIKE should be used for this key
903 if [[ $ITEM == *~* ]]; then
904 ITEM_ARR_COMPARATOR[$KEY_ID]="$(sed -e 's/.*=//' -e 's/~.*//' <<< $ITEM)"
905 else
906 ITEM_ARR_COMPARATOR[$KEY_ID]="EQ"
907 fi
908 # Add the value to the array of current key
909 ITEM_ARR_VALUE[$KEY_ID]="$(sed -e 's/.*=//' -e 's/.*~//' <<< $ITEM)"
910 elif [[ -n $ITEM_KEY ]]; then
911 # Else if there is no '=' found in this element, assume that it is another
912 # value for the last defined key and add it into its array
913 ITEM_ARR_VALUE[$KEY_ID]="${ITEM_ARR_VALUE[$KEY_ID]}|$ITEM"
914 else
915 # If we get here, it means that there was no Key found during the first run,
916 # which indicates that the parameter was not correctly specified.
917 bail_out "The syntax of external data doesn't seem to be correct. Aborting..."
918 fi
919 done
920 IFS=' '
921
922# This loop checks if there's Advanced Search field configured
923# for each key that was provided by the user and constructs
924# the SQL parameter.
925
926 # Determine column ID in couple_fixed_extdata for each given key.
927 ITEM_ID=1
928 for KVP_KEY in ${ITEM_ARR_KEY[@]}; do
929 EXTDATA_TMP_QUERY=""
930 EXT_COLUMN_ID=$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "select columnid from extdata_map where key='$KVP_KEY';" | tr -d " ")
931 if [[ -z $EXT_COLUMN_ID ]]; then
932 bail_out "It seems that there is no Advanced Search field set up for $KVP_KEY. Aborting..."
933 fi
934 # Determine operator
935 case ${ITEM_ARR_COMPARATOR[$ITEM_ID]} in
936 SW|EW|CT)
937 ITEM_OPERATOR="SIMILAR TO"
938 ITEM_SEPARATOR="|"
939 ITEM_WRAPPER=""
940 ;;
941 EQ)
942 ITEM_OPERATOR="IN"
943 ITEM_SEPARATOR=","
944 ITEM_WRAPPER="'"
945 ;;
946 *)
947 bail_out "Invalid operator specified for $KVP_KEY key. Aborting..."
948 ;;
949 esac
950 # Join the values
951 IFS='|'
952 for EXT_VALUE in ${ITEM_ARR_VALUE[$ITEM_ID]}; do
953 EXTDATA_TMP_QUERY="${EXTDATA_TMP_QUERY}${ITEM_WRAPPER}${EXT_VALUE}${ITEM_WRAPPER}${ITEM_SEPARATOR}"
954 done
955 IFS=' '
956 # Remove the last extra separator from the list
957 EXTDATA_TMP_QUERY=${EXTDATA_TMP_QUERY%$ITEM_SEPARATOR}
958 # Wrap all values accordingly
959 case ${ITEM_ARR_COMPARATOR[$ITEM_ID]} in
960 SW)
961 EXTDATA_TMP_QUERY="'($EXTDATA_TMP_QUERY)%'"
962 ;;
963 EW)
964 EXTDATA_TMP_QUERY="'%($EXTDATA_TMP_QUERY)'"
965 ;;
966 CT)
967 EXTDATA_TMP_QUERY="'%($EXTDATA_TMP_QUERY)%'"
968 ;;
969 EQ)
970 EXTDATA_TMP_QUERY="(${EXTDATA_TMP_QUERY})"
971 ;;
972 esac
973 # This is to remove the first AND/OR from the query to avoid SQL exception
974 if [[ $ITEM_ID -eq 1 ]]; then
975 ITEM_ARR_JOINER[$ITEM_ID]=""
976 fi
977 # Construct the SQL parameter
978 EXTDATA_QUERY="$EXTDATA_QUERY ${ITEM_ARR_JOINER[$ITEM_ID]} (couple_fixed_extdata.col_$EXT_COLUMN_ID $ITEM_OPERATOR $EXTDATA_TMP_QUERY)"
979
980 ((ITEM_ID++))
981 done
982
983 # When the loop is finished, just enclose the query with 'AND ()'
984 EXTDATA_QUERY="AND ($EXTDATA_QUERY)"
985}
986
987function prepare_missing_extdata {
988# Desc: Parse missing extdata keys provided as -n parameter from user input, prepare SQL query params
989# Input: $1 = the whole value of the -n parameter
990
991 IFS='|'
992 NOEX_KEY_ID=1
993 for NOEX_KEY in $1; do
994 # Determine column ID for the key
995 EXT_COLUMN_ID=$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "select columnid from extdata_map where key='$NOEX_KEY';" | tr -d " ")
996 if [[ -z $EXT_COLUMN_ID ]]; then
997 bail_out "It seems that there is no Advanced Search field set up for $NOEX_KEY. Aborting..."
998 fi
999 if [[ $NOEX_KEY_ID -eq 1 ]]; then
1000 MISSING_EXTDATA_QUERY+="couple_fixed_extdata.col_$EXT_COLUMN_ID IS NULL"
1001 else
1002 MISSING_EXTDATA_QUERY+=" AND couple_fixed_extdata.col_$EXT_COLUMN_ID IS NULL"
1003 fi
1004 ((NOEX_KEY_ID++))
1005 done
1006 IFS=' '
1007
1008 # When all keys are processed, enclose the condition with 'AND ()'
1009 MISSING_EXTDATA_QUERY="AND ($MISSING_EXTDATA_QUERY)"
1010}
1011
1012function verify_range {
1013# Desc: Verify that a valid date/time range has been specified
1014# Input: $1 = the whole value of the -r parameter
1015
1016# Parse provided parameters (keywords)
1017 KW_I=1
1018 for KW in $(echo $1); do
1019 KWS[$KW_I]=$KW
1020 ((KW_I++))
1021 done
1022
1023 case ${KWS[1]} in
1024 older)
1025 KW_LIST="_minute_ _minutes_ _day_ _days_ _month_ _months_"
1026 if [[ ${KWS[2]} == "than" ]] && [[ ${KWS[3]} != *[!0-9]* ]] && [[ $(grep "_${KWS[4]}_" <<< $KW_LIST) ]]; then
1027 RANGE_QUERY="AND (couples.stop_ts < now() - interval '${KWS[3]} ${KWS[4]}')"
1028 else
1029 bail_out "Interval \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1030 fi
1031 ;;
1032 from)
1033 date -d "${KWS[2]} ${KWS[3]}" >/dev/null 2>&1 && date -d "${KWS[5]} ${KWS[6]}" >/dev/null 2>&1
1034 DATE_TEST=$?
1035 if [[ ${KWS[1]} == "from" ]] && [[ ${KWS[4]} == "to" ]] && [[ $DATE_TEST -eq 0 ]]; then
1036 RANGE_QUERY="AND (couples.stop_ts > '${KWS[2]} ${KWS[3]}' AND couples.stop_ts < '${KWS[5]} ${KWS[6]}')"
1037 else
1038 bail_out "Interval \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1039 fi
1040 ;;
1041 float)
1042 KW_LIST="_minute_ _minutes_ _day_ _days_ _month_ _months_"
1043 if [[ ${KWS[1]} == "float" ]] && [[ ${KWS[2]} != *[!0-9]* ]] && [[ ${KWS[4]} != *[!0-9]* ]] && [[ $(grep "_${KWS[3]}_" <<< $KW_LIST) ]] && [[ $(grep "_${KWS[5]}_" <<< $KW_LIST) ]]; then
1044 RANGE_QUERY="AND (couples.stop_ts > now() - interval '${KWS[4]} ${KWS[5]}' AND couples.stop_ts < now() - interval '${KWS[2]} ${KWS[3]}')"
1045 else
1046 bail_out "Interval \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1047 fi
1048 ;;
1049 ?)
1050 bail_out "Unknown range option $1"
1051 ;;
1052 *)
1053 bail_out "Unknown range option $1"
1054 ;;
1055 esac
1056}
1057
1058function verify_length {
1059# Desc: Verify that a valid length has been specified
1060# Input: $1 = the whole value of the -l parameter
1061
1062# Parse provided parameters (keywords)
1063 LENGTH_KW_I=1
1064 for LENGTH_KW in $(echo $1); do
1065 LENGTH_KWS[$LENGTH_KW_I]=$LENGTH_KW
1066 ((LENGTH_KW_I++))
1067 done
1068
1069 case ${LENGTH_KWS[1]} in
1070 eq)
1071 if [[ ${LENGTH_KWS[2]} != *[!0-9]* ]]; then
1072 LENGTH_QUERY="AND couples.length = ${LENGTH_KWS[2]}"
1073 else
1074 bail_out "Length \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1075 fi
1076 ;;
1077 gt)
1078 if [[ ${LENGTH_KWS[2]} != *[!0-9]* ]]; then
1079 LENGTH_QUERY="AND couples.length > ${LENGTH_KWS[2]}"
1080 else
1081 bail_out "Length \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1082 fi
1083 ;;
1084 lt)
1085 if [[ ${LENGTH_KWS[2]} != *[!0-9]* ]]; then
1086 LENGTH_QUERY="AND couples.length < ${LENGTH_KWS[2]}"
1087 else
1088 bail_out "Length \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1089 fi
1090 ;;
1091 between)
1092 if [[ ${LENGTH_KWS[2]} != *[!0-9]* ]] && [[ ${LENGTH_KWS[3]} != *[!0-9]* ]]; then
1093 LENGTH_QUERY="AND (couples.length > ${LENGTH_KWS[2]} AND couples.length < ${LENGTH_KWS[3]})"
1094 else
1095 bail_out "Length \"$1\" does not seem to be valid. Please review your syntax. Aborting..."
1096 fi
1097 ;;
1098 ?)
1099 bail_out "Unknown length comparator ${LENGTH_KWS[1]}"
1100 ;;
1101 *)
1102 bail_out "Unknown length comparator ${LENGTH_KWS[1]}"
1103 ;;
1104 esac
1105}
1106
1107function verify_description {
1108# Desc: Verify that valid description and matching type has been specified, prepare SQL parameter
1109# Input: $1 = the whole value of -t parameter
1110
1111# We expect that the match type is the first word and the rest is the actual description text
1112 DESC_MATCH="$(cut -d ' ' -f 1 <<< $1)"
1113 DESC_TEXT="$(cut -d ' ' -f 2- <<< $1)"
1114
1115 case $DESC_MATCH in
1116 equals|EQUALS)
1117 DESC_QUERY="AND couples.description = '$DESC_TEXT'"
1118 ;;
1119 contains|CONTAINS)
1120 DESC_QUERY="AND couples.description LIKE '%$DESC_TEXT%'"
1121 ;;
1122 starts|STARTS)
1123 DESC_QUERY="AND couples.description LIKE '$DESC_TEXT%'"
1124 ;;
1125 ends|ENDS)
1126 DESC_QUERY="AND couples.description LIKE '%$DESC_TEXT'"
1127 ;;
1128 ?)
1129 bail_out "Unknown description match type $DESC_MATCH"
1130 ;;
1131 *)
1132 bail_out "Unknown description match type $DESC_MATCH"
1133 ;;
1134 esac
1135}
1136
1137function verify_file_path {
1138# Desc: Check if user-provided file path seems legit, prepare SQL parameter
1139# Input: File path, argument of -f
1140
1141 # Check if not empty:
1142 if [[ -z $FILE_PATH ]]; then
1143 bail_out "Specified path \"$FILE_PATH\" does not seem to be valid. Aborting..."
1144 # If we are not deleting files from the filesystem,
1145 # do not check the path and print a message about that:
1146 elif [[ $DB_ONLY -eq 1 ]]; then
1147 echoo "INFO: Deleting DB records only (no files), specified path \"$FILE_PATH\" will not be checked for validity."
1148 # If we are about to delete files from the filesystem,
1149 # check if it at least starts with slash:
1150 elif [[ ! $FILE_PATH == /* ]]; then
1151 bail_out "Specified path \"$FILE_PATH\" does not seem to start with a slash (/). Aborting..."
1152 # And also if the path exists:
1153 elif [[ ! -d "$FILE_PATH" ]]; then
1154 bail_out "Specified path \"$FILE_PATH\" does not seem to exist on this server. Aborting..."
1155 fi
1156
1157 # If we did not bail out on anything above, let's set the SQL parameter:
1158 FILE_PATH_QUERY="AND cfiles.cfpath LIKE '$FILE_PATH%'"
1159}
1160
1161function check_cftype_selections {
1162# Desc: Check selected cftypes, prepare SQL parameter
1163# Input: None
1164
1165# Check cftype selections - used for operations on cfiles table (cfiles.cftype)
1166 # Set up parameter for cfiles.cftype selections
1167 # Remove the last comma from the list
1168 MEDIA_COND_CFTYPES=${MEDIA_COND_CFTYPES%,}
1169 # Construct the SQL parameter
1170 MEDIA_COND_QUERY="AND cfiles.cftype IN ($MEDIA_COND_CFTYPES)"
1171
1172# Check cftype selections - used for operations on couples table (couples.cftypes)
1173 # Loop through enabled cfile types
1174 CFTYPES="AUDIO_ONLY PCAP_ONLY INDEX_ONLY VIDEO_ONLY RECD_ONLY"
1175 for CFTYPE in $CFTYPES; do
1176 # Skip this cftype if it was not selected
1177 [[ $CFTYPE -eq 0 ]] && continue
1178 # Otherwise read value from enabled cftype..
1179 CFTYPE_VALUE=${!CFTYPE}
1180 # and add the cftype as a condition
1181 CFTYPE_COND_PRE_QUERY="$CFTYPE_COND_PRE_QUERY CAST(couples.cftypes & $CFTYPE_VALUE::bit(32) as integer) = $CFTYPE_VALUE OR"
1182 done
1183 # Remove the last OR
1184 CFTYPE_COND_PRE_QUERY=${CFTYPE_COND_PRE_QUERY%OR}
1185 # Construct the SQL parameter
1186 CFTYPE_COND_QUERY="AND ($CFTYPE_COND_PRE_QUERY)"
1187}
1188
1189function db_query {
1190# Desc: Execute SQL query
1191# Input: $1= Type of operation
1192# $2= SQL query to be executed / list of couple IDs (depending on the type of operation)
1193
1194 case $1 in
1195 # Query for cfpaths and fill variable CFPATHS
1196 SELECT_CFPATHS)
1197 echoo "Searching for media files to be removed..."
1198 CFPATHS_RES=$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "$2" | tr -d " " | sed -e '/^$/d')
1199 CFPATHS=$(awk -F '|' '{print $1}' <<< "$CFPATHS_RES")
1200 SIDS_CFTYPES=$(awk -F '|' '{print $3" "$2}' <<< "$CFPATHS_RES")
1201 if [[ $(wc -w <<< $CFPATHS) -lt 1 ]]; then
1202 echoo "Did not find any media files for removal."
1203 else
1204 echoo "Found $(wc -w <<< $CFPATHS) media files to be removed."
1205 fi
1206 ;;
1207 # Query for cplids, remove duplicates and fill variable CPLIDS
1208 SELECT_CPLIDS)
1209 echoo "Searching for couples to be processed..."
1210 CPLIDS=$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "$2" | sed -e 's/ //g' -e '/^$/d' | sort -n | uniq)
1211 if [[ $(wc -w <<< $CPLIDS) -lt 1 ]]; then
1212 echoo "Did not find any couples for removal."
1213 return 1
1214 else
1215 echoo "Found $(wc -w <<< $CPLIDS) couples whose cfiles might be deleted."
1216 return 0
1217 fi
1218 ;;
1219 # Select callids and cplids, remove dupes and fill variables CALLIDS and CPLIDS
1220 SELECT_CPLID_CALLID)
1221 echoo "Searching for couples to be processed..."
1222 CPLID_CALLID_RES=$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "$2" | sed -e '/^$/d')
1223 CPLIDS=$(awk -F '|' '{print $1}' <<< "$CPLID_CALLID_RES" | sort -n | uniq)
1224 CALLIDS=$(awk -F '|' '{print $2}' <<< "$CPLID_CALLID_RES" | sort -n | uniq)
1225 CPL_SIDS=$(awk -F '|' '{print $3}' <<< "$CPLID_CALLID_RES" | sort -n | uniq)
1226 if [[ $(wc -w <<< $CPLIDS) -lt 1 ]]; then
1227 echoo "Did not find any couples for removal."
1228 return 1
1229 else
1230 echoo "Found $(wc -w <<< $CPLIDS) couples to be deleted."
1231 return 0
1232 fi
1233 ;;
1234 # We go here if we need to be careful about large list of IDs and process them in batches
1235 SPLIT)
1236 # Check what will be done and read the query parameters (list of IDs)
1237 OPERATION="$2"
1238 QUERY_PARAMS="$3"
1239 QUERY_PARAMS_CNT=$(echo -e "$QUERY_PARAMS" | wc -l)
1240 # Counters
1241 local CURR=1
1242 if [[ $((QUERY_PARAMS_CNT % QUERY_LIMIT)) -eq 0 ]]; then
1243 local MAX=$((QUERY_PARAMS_CNT / QUERY_LIMIT))
1244 else
1245 local MAX=$((QUERY_PARAMS_CNT / QUERY_LIMIT + 1))
1246 fi
1247
1248 while [[ $(wc -w <<< $QUERY_PARAMS) -gt 0 ]]; do
1249 # Load batch to work list
1250 QUERY_WORK_PARAMS=$(echo -e "$QUERY_PARAMS" | head -n $QUERY_LIMIT | sed -e '/^$/d' -e 's/$/,/g' -e '$ s/,$//g')
1251 # Remove loaded batch from the to-do list
1252 QUERY_PARAMS=$(echo -e "$QUERY_PARAMS" | tail -n +$QUERY_LIMIT_PLUS)
1253 # Here we check what we are required to do with the IDs
1254 case $OPERATION in
1255 # Mark couples as deleted
1256 SELECT_CPLIDS_MARK)
1257 # If certain cftype is being deleted (-a/-p/-i/-v/-s),
1258 # we need to retrieve a new list of couple IDs that we need to mark as deleted
1259 if [[ -n $MEDIA_COND_QUERY ]]; then
1260 echoo "[ $CURR/$MAX ] Searching for couples to be marked as deleted..."
1261 SCMQ="SELECT id FROM couples
1262 WHERE cfcnt=0
1263 AND id IN ($QUERY_WORK_PARAMS);"
1264 CPLIDS_TO_MARK+="\n$(psql -U $DBUSER -d $DBNAME -h $DBHOST -t -c "$SCMQ" | sed -e 's/ //g' -e '/^$/d')"
1265 # Else just use the original list of couples and mark them as deleted (because we're deleting all cfiles)
1266 else
1267 CPLIDS_TO_MARK="$3"
1268 break
1269 fi
1270 ;;
1271 # Unprotect couples for deletion
1272 UNPROTECT_COUPLES)
1273 if [[ -n $QUERY_WORK_PARAMS ]]; then
1274 echoo "[ $CURR/$MAX ] Unprotecting 'couples' DB records...\c"
1275 psql -U $DBUSER -d $DBNAME -h $DBHOST -c "UPDATE callrec.couples
1276 SET protected=false
1277 WHERE protected=true
1278 AND id IN ($QUERY_WORK_PARAMS);" 2>&1 | tee -a $LOGFILE
1279 else
1280 echoo "No couples to be unprotected..."
1281 fi
1282 ;;
1283 # Mark couples as deleted
1284 UPDATE_RECORDS)
1285 if [[ -n $QUERY_WORK_PARAMS ]]; then
1286 echoo "[ $CURR/$MAX ] Updating 'couples' DB records...\c"
1287 psql -U $DBUSER -d $DBNAME -h $DBHOST -c "UPDATE callrec.couples
1288 SET deleted='D'
1289 WHERE id IN ($QUERY_WORK_PARAMS);" 2>&1 | tee -a $LOGFILE
1290 else
1291 echoo "No couples to be updated..."
1292 fi
1293 ;;
1294 # Delete cfiles
1295 DELETE_CFILES_RECORDS)
1296 echoo "[ $CURR/$MAX ] Deleting 'cfiles' DB records...\c"
1297 psql -U $DBUSER -d $DBNAME -h $DBHOST -c "DELETE FROM callrec.cfiles
1298 WHERE cplid IN ($QUERY_WORK_PARAMS)
1299 $MEDIA_COND_QUERY $FILE_PATH_QUERY $CFILE_ARC_QUERY;" 2>&1 | tee -a $LOGFILE
1300 ;;
1301 # Delete couples
1302 DELETE_COUPLES_RECORDS)
1303 echoo "[ $CURR/$MAX ] Deleting 'couples' DB records...\c"
1304 psql -U $DBUSER -d $DBNAME -h $DBHOST -c "DELETE FROM callrec.couples
1305 WHERE id IN ($QUERY_WORK_PARAMS);" 2>&1 | tee -a $LOGFILE
1306 ;;
1307 # Delete calls (with 0 couples)
1308 DELETE_RECORDS)
1309 echoo "[ $CURR/$MAX ] Deleting 'calls' DB records...\c"
1310 psql -U $DBUSER -d $DBNAME -h $DBHOST -c "DELETE FROM callrec.calls
1311 WHERE realcplcnt=0
1312 AND id IN ($QUERY_WORK_PARAMS);" 2>&1 | tee -a $LOGFILE
1313 ;;
1314 # Filter out partially archived couples
1315 FILTER_COUPLE_IDS)
1316 FILTERED_RES+="\n$(psql -U $DBUSER -d $DBNAME -h $DBHOST -At -c "SELECT id, sid FROM callrec.couples
1317 WHERE id IN ($QUERY_WORK_PARAMS) $ARC_QUERY;" 2>&1)"
1318 ;;
1319 esac
1320 ((CURR++))
1321 done
1322 ;;
1323 *)
1324 bail_out "Unknown option ($1) provided for db_query function. Aborting..."
1325 ;;
1326 esac
1327}
1328
1329function remove_files {
1330# Desc: Delete media files from the filesystem
1331# Input: none
1332
1333 if [[ -n $CFPATHS ]]; then
1334 # No need to worry about the number of files
1335 # xargs is intelligent and passes the arguments to rm in batches
1336 echoo "Removing media files from the filesystem..."
1337 echo $CFPATHS | xargs rm -f
1338 echoo "Removal of media files from the filesystem has finished."
1339 fi
1340}
1341
1342function send_updates_to_enc {
1343# Desc: Generate AMQP messages - info about deleted media/records for Encourage
1344# Input: $1 = Type of messages to be generated, $2 = IDs of deleted couples / types of deleted cfiles
1345
1346 # If required based on input parameters, do not generate AMQP messages for Encourage
1347 if [[ $NO_AMQP -eq 1 ]]; then
1348 return
1349 fi
1350
1351 # Input
1352 local MSGTYPE=$1 SIDS=$2
1353 # Counter
1354 local PROCESSED_SIDS=0 CHECKPOINT=$((QUERY_LIMIT / 10))
1355 local TOTAL_SIDS=$(wc -l <<< "$SIDS")
1356 local START_TIME=$(date +%s)
1357
1358 echoo "Generating $TOTAL_SIDS updates for Encourage. This might take a while..."
1359 echoo "Please wait..."
1360
1361 # Python script to publish AMQP messages
1362 local MSG_SENDER=$MYDIR/send.py
1363 local MSG_PAYLOADS=$MYDIR/msgs.json
1364 spawn_msg_sender
1365
1366 # Create "header" in JSON file
1367 gen_json_header
1368
1369 # Create payloads in JSON file
1370 case $MSGTYPE in
1371 MEDIA)
1372 local EXEC_FUNC="gen_json_media_deleted"
1373 local MSG_TYPEID="cz.zoom.callrec.data.access.model.MediaDeleted"
1374 ;;
1375 SEGMENT)
1376 local EXEC_FUNC="gen_json_segment_deleted"
1377 local MSG_TYPEID="cz.zoom.callrec.data.access.model.SegmentDeleted"
1378 ;;
1379 esac
1380
1381 while read SID CFTYPE; do
1382 $EXEC_FUNC $SID $CFTYPE
1383 ((PROCESSED_SIDS++))
1384 if [[ $((PROCESSED_SIDS % CHECKPOINT)) -eq 0 ]]; then
1385 local ELAPSED=$(($(date +%s) - START_TIME))
1386 local REMAINING=$(printf "%0.0f\n" $(echo "($TOTAL_SIDS - $PROCESSED_SIDS) / $PROCESSED_SIDS * $ELAPSED" | bc -l))
1387 local REMM=$((REMAINING/60)) REMS=$((REMAINING%60))
1388 overwrite "Generated [ $PROCESSED_SIDS/$TOTAL_SIDS ] messages, estimated time remaining: ${REMM}min ${REMS}s"
1389 fi
1390 done <<< "$SIDS"
1391
1392 # Create "footer" in JSON file
1393 gen_json_footer
1394 overwrite "Done. Generated [ $PROCESSED_SIDS/$TOTAL_SIDS ] messages. Now publishing the messages..."
1395 /bin/python $MSG_SENDER $MSG_PAYLOADS $MSG_TYPEID
1396
1397 echoo "All messages have been published."
1398
1399 # Clean up
1400 rm -f $MSG_SENDER $MSG_PAYLOADS
1401}
1402
1403function spawn_msg_sender {
1404# Desc: Creates a python script to handle publishing of AMQP messages
1405# Input: none
1406cat > $MSG_SENDER <<ENDOFSCRIPT
1407#!/usr/bin/env python
1408# -*- coding: utf-8 -*-
1409
1410import sys
1411import pika
1412import json
1413
1414def send_msg():
1415 inputfile = sys.argv[1]
1416 msgtype = sys.argv[2]
1417 connection = pika.BlockingConnection(pika.URLParameters('amqp://callrec:callrec@localhost'))
1418 channel = connection.channel()
1419 msgprops = pika.spec.BasicProperties(content_type='application/json',
1420 content_encoding='UTF-8',
1421 headers={'__TypeId__': msgtype},
1422 delivery_mode=2,
1423 priority=0)
1424
1425 with open(inputfile, "r") as f:
1426 data = json.load(f)
1427 for delmsg in data["deletemsgs"]:
1428 channel.basic_publish('amq.topic', 'com.zoomint.tools.couples.deleted', delmsg["payload"], msgprops)
1429
1430 connection.close()
1431
1432if __name__ == '__main__':
1433 send_msg()
1434ENDOFSCRIPT
1435}
1436
1437function gen_json_header {
1438cat > $MSG_PAYLOADS <<ENDOFHEAD
1439{
1440 "deletemsgs": [
1441ENDOFHEAD
1442}
1443
1444function gen_json_media_deleted {
1445cat >> $MSG_PAYLOADS <<ENDOFSIDS
1446 {
1447 "payload": "{\"type\":\"mediaDeleted\",\"timestamp\":\"$(date -u +%Y-%m-%dT%H:%M:%S.000Z)\",\"sid\":\"$1\",\"reason\":\"advanced_delete\",\"mediaType\":\"$2\"}"
1448 },
1449ENDOFSIDS
1450}
1451
1452function gen_json_segment_deleted {
1453cat >> $MSG_PAYLOADS <<ENDOFSIDS
1454 {
1455 "payload": "{\"type\":\"segmentDeleted\",\"timestamp\":\"$(date -u +%Y-%m-%dT%H:%M:%S.000Z)\",\"sid\":\"$1\",\"reason\":\"advanced_delete\"}"
1456 },
1457ENDOFSIDS
1458}
1459
1460function gen_json_footer {
1461sed -i '$d' $MSG_PAYLOADS
1462
1463cat >> $MSG_PAYLOADS <<ENDOFFOOT
1464 }
1465 ]
1466}
1467ENDOFFOOT
1468}
1469
1470function flush_vars {
1471# Desc: Flush some variables that are re-used in loops and need to be flushed outside of them
1472# Input: None
1473
1474 CPLIDS_TO_MARK=""
1475 FILTERED_RES=""
1476}
1477
1478function echoo {
1479# Desc: Display a message to the console and print it into the logfile
1480# Input: $1= Message to be displayed
1481
1482 echo -e "$(date +%d.%m.%Y\ %H:%M:%S) :: [$$] :: $@" | tee -a $LOGFILE
1483}
1484
1485function overwrite {
1486# Desc: Overwrite the last line in the console and in the log file
1487# Input: $1= Message to be displayed
1488
1489 local MSG="$(date +%d.%m.%Y\ %H:%M:%S) :: [$$] :: $@"
1490 echo -e "\r\033[1A\033[0K$MSG"
1491 sed -i '$d' $LOGFILE
1492 echo "$MSG" >> $LOGFILE
1493}
1494
1495function bail_out {
1496# Desc: Exit with an error message
1497# Input: $1= Error message to be displayed
1498
1499 echoo "$1 \n"
1500 rm -f $LOCKFILE
1501 exit 1
1502}
1503
1504function read_options {
1505# Desc: Read input parameters
1506# Input: $@ = all parameters, $1 = first parameter
1507
1508# If no parameter is provided, print usage
1509 if [[ $(printf "%s" "$1" | tr -d " ") == "" ]]; then
1510 print_usage
1511 fi
1512
1513# Save the parameters into the log file
1514 echoo "Script has been started with following parameters: $@"
1515
1516# Read parameters
1517 while getopts ":hxd:e:n:r:l:t:f:SAPRTDCapivsM:Q:Nm" opt; do
1518 case $opt in
1519 d)
1520 DN_LIST="$OPTARG"
1521 ;;
1522 e)
1523 EXTDATA="$OPTARG"
1524 ;;
1525 n)
1526 MISSING_EXTDATA="$OPTARG"
1527 ;;
1528 r)
1529 RANGE="$OPTARG"
1530 ;;
1531 l)
1532 LENGTH_SET=1
1533 LENGTH="$OPTARG"
1534 ;;
1535 t)
1536 DESCRIPTION_SET=1
1537 DESCRIPTION="$OPTARG"
1538 ;;
1539 f)
1540 FILE_PATH_SET=1
1541 FILE_PATH="$OPTARG"
1542 ;;
1543 h)
1544 print_usage
1545 ;;
1546 x)
1547 print_examples
1548 ;;
1549 C)
1550 CFILES_ONLY=1
1551 ;;
1552 a)
1553 AUDIO_ONLY=1
1554 MEDIA_COND_CFTYPES="$MEDIA_COND_CFTYPES 'AUDIO',"
1555 ;;
1556 p)
1557 PCAP_ONLY=2
1558 MEDIA_COND_CFTYPES="$MEDIA_COND_CFTYPES 'MAG',"
1559 ;;
1560 i)
1561 INDEX_ONLY=8
1562 MEDIA_COND_CFTYPES="$MEDIA_COND_CFTYPES 'IMAGE',"
1563 ;;
1564 v)
1565 VIDEO_ONLY=16
1566 MEDIA_COND_CFTYPES="$MEDIA_COND_CFTYPES 'VIDEO',"
1567 ;;
1568 s)
1569 RECD_ONLY=32
1570 MEDIA_COND_CFTYPES="$MEDIA_COND_CFTYPES 'RECD',"
1571 ;;
1572 A)
1573 ARC_QUERY="AND couples.archived='A' AND NOT EXISTS (SELECT 1 FROM cfiles WHERE cplid=couples.id AND (cfiles.archived!='A' OR cfiles.archived IS NULL))"
1574 MEDIA_ARC_QUERY="AND couples.archived='A'"
1575 CFILE_ARC_QUERY="AND cfiles.archived='A'"
1576 ;;
1577 S)
1578 SYNC_QUERY="AND couples.synchronized IN ('S', 'N', 'E')"
1579 ;;
1580 D)
1581 DB_ONLY=1
1582 ;;
1583 P)
1584 DEL_PROTECTED=1
1585 ;;
1586 R)
1587 DEL_RESTORED=1
1588 ;;
1589 T)
1590 PARTIALLY_ARCHIVED=1
1591 ;;
1592 m)
1593 NO_AMQP=1
1594 ;;
1595 M)
1596 DN_LIMIT="$OPTARG"
1597 ;;
1598 N)
1599 DRY_RUN=1
1600 TEMP_DIR=$(mktemp -d)
1601 ;;
1602 Q)
1603 QUERY_LIMIT="$OPTARG"
1604 ;;
1605 \?)
1606 bail_out "Unknown option -$OPTARG !"
1607 ;;
1608 :)
1609 bail_out "Option -$OPTARG requires an argument."
1610 ;;
1611 esac
1612 done
1613}
1614
1615# ACTION !
1616root_user_check
1617lockfile_check
1618db_version_check
1619set_defaults
1620read_options "$@"
1621pre_flight_check
1622parse_input
1623start_wrapper
1624
1625echoo "Script has finished \n"
1626rm -f $LOCKFILE
1627exit 0
1628#EOF