This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.

SQL Snippets (Nice)¶

User Management¶

Assign All Non-Guest Roles to User ‘tocco’¶

INSERT INTO nice_login_role (
  _nice_version,
  _nice_create_timestamp,
  _nice_update_timestamp,
  _nice_create_user,
  _nice_update_user,
  fk_principal,
  fk_role,
  fk_business_unit,
  manual,
  _system_entity,
  initial_identifier
) SELECT
  1,
  now(),
  now(),
  'tocco',
  'tocco',
  p.pk,
  r.pk,
  u.pk,
  true,
  false,
  ''
FROM
  nice_role AS r LEFT OUTER JOIN nice_role_type AS rt ON r.fk_role_type = rt.pk,
  nice_principal as p,
  nice_business_unit as u
WHERE p.username = 'tocco' AND rt.unique_id <> 'guest'
ON CONFLICT DO NOTHING;

Unblock User ‘tocco’¶

UPDATE nice_principal
SET
    fail_login_attempts = 0,
    fk_principal_status = (SELECT pk FROM nice_principal_status WHERE unique_id = 'active')
WHERE username = 'tocco';

Set Password for User Tocco¶

Set password to NEW_PASSWORD.

UPDATE nice_principal
SET
    password = md5('NEW_PASSWORD'), -- upgraded to PBKDF2 on next login
WHERE username = 'tocco';

Jobs (Task_execution)¶

Distribute Execution Time of a Batch Job Evenly¶

Execute batch job nice2.dms.DeleteUnreferencedBinariesBatchJob hourly at a random time but only between 06:00 and 21:59.

UPDATE nice_batch_job
SET
  minutes = trunc(random()*60)::text,
  hours = '6-21',
  days_of_week = '*',
  months = '*'
WHERE id = 'nice2.dms.DeleteUnreferencedBinariesBatchJob';

Ajust on all DBs at once:

n2sql-on-all-dbs --let-me-destroy-all-databases-at-once-by-enabling-read-write-mode -w --csv "$SQL"

Jobs Grouped by Name and their Longest Duration¶

SELECT
  name,
  round(max(extract(epoch from end_date) - extract(epoch from start_date))) AS longest_duration,
  round(avg(extract(epoch from end_date) - extract(epoch from start_date))) AS avg_duration,
  max(start_date) AS last_execution
FROM nice_task_execution
WHERE start_date > now() - interval '10 days'
GROUP BY name;

On all DBs, sort by longest duration (field 2):

n2sql-on-all-dbs -w --csv "$SQL" | sort -g -t , -k 2,2

List Jobs that Failed the Last Three Runs¶

WITH third AS (
    SELECT
      te.name,
      (SELECT start_date FROM nice_task_execution WHERE te.name = name ORDER BY start_date DESC OFFSET 2 LIMIT 1) as start_date
    FROM nice_task_execution as te
    GROUP BY te.name
) SELECT te.name
FROM nice_task_execution AS te
LEFT OUTER JOIN nice_task_execution_status AS tes ON te.fk_task_execution_status = tes.pk
WHERE start_date >= (SELECT start_date FROM third WHERE name = te.name) and tes.unique_id = 'failed'
GROUP BY te.name
HAVING count(*) = 3
ORDER BY 1;

On all DBs:

n2sql-on-all-dbs -w --csv "$SQL"