SergeyGurin

2022-11-09 22:30:31 /*
PostgreSQL
Work with trigger
*/

:::1. Create new function
--drop function sync_record;
create function sync_record() returns trigger
language plpgsql
as
$$
BEGIN
NEW.updated_at = current_timestamp;
// ....
insert into sync_hcard (hcard_id, sync_event_type) values (NEW.id, 1);
RETURN NEW;
END
$$;

:::2. Create new trigger
--drop trigger check_update on hcard_data;
create trigger check_update
before insert or update
on hcard_data
for each row
execute procedure sync_record();

:::3. Check
SELECT event_object_table
,trigger_name
,event_manipulation
,action_statement
,action_timing
FROM information_schema.triggers
WHERE event_object_table = 'hcard_data' -- Your table name comes here
ORDER BY event_object_table
,event_manipulation; Відкрити / Коментувати

2022-08-31 13:36:14 /*
PostgreSQL
Work with sequence
*/

:::1. Create new sequence
CREATE SEQUENCE guest_engine.store_location_hours_of_operation_id_seq;

:::2. Test sequence
select nextval('guest_engine.store_location_hours_of_operation_id_seq'::regclass);

:::3. Reset sequence
ALTER SEQUENCE guest_engine.store_location_hours_of_operation_id_seq RESTART WITH 1; Відкрити / Коментувати

2022-04-25 09:26:01 /*
Java
Mapstruct
*/

How to useMappinginorg.mapstruct
https://www.tabnine.com/code/java/classes/org.mapstruct.Mapping Відкрити / Коментувати

2022-03-24 19:29:14 /*
Spring
JPA Repositories
*/

Query Methods
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation Відкрити / Коментувати

2022-03-18 18:57:54 /*
PostgreSQL
Cleaning and shrinking the table
*/

:::1. Delete unnecessary data from the table
do
$$
DECLARE
r guest_engine.dino_speed_of_service%rowtype;
i integer := 0;
BEGIN
FOR r IN
select *
from guest_engine.dino_speed_of_service
where insert_date <= current_date - 100 * INTERVAL '1 DAY'
--and date_part('year',insert_date) = date_part('year', CURRENT_DATE)
--limit 1000000
LOOP
BEGIN
delete from guest_engine.dino_speed_of_service where uid = r.uid;
i := i + 1;
IF (i > 1000) THEN
commit;
i := 0;
END IF;
END;
END LOOP;
commit;
END
$$ language plpgsql;

:::2. Reclaim storage occupied by dead tuples
VACUUM (FULL, VERBOSE, ANALYZE) guest_engine.dino_speed_of_service; Відкрити / Коментувати

2022-03-18 18:17:35 /*
PostgreSQL
CTAS
*/

:::1. Create a copy of the table
create table replication.item_data2
(
like replication.item_data INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
);

:::2. Insert the required data into the new table
insert into replication.item_data2
select *
from replication.item_data
where system_date > current_date - 90 * INTERVAL '1 DAY';

:::3. Rename original and new tables
alter table replication.item_data rename to item_data_for_drop;
alter table replication.item_data2 rename to item_data;

:::4. Delete old table
drop table replication.item_data_for_drop; Відкрити / Коментувати

2022-03-18 18:09:10 /*
PostgreSQL
*/

:::Schema DB-objects size
SELECT schema_name,
relname,
pg_size_pretty(table_size) AS size,
table_size

FROM (
SELECT pg_catalog.pg_namespace.nspname AS schema_name,
relname,
pg_relation_size(pg_catalog.pg_class.oid) AS table_size

FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name = 'OWNER'
ORDER BY table_size DESC; Відкрити / Коментувати

2022-01-26 13:40:43 /*
ORACLE
SYS_CONTEXT
*/

:::Predefined Parameters of Namespace USERENV
DECLARE
a sys.ku$_vcnt := sys.ku$_vcnt ('ACTION', 'AUDITED_CURSORID', 'AUTHENTICATED_IDENTITY', 'AUTHENTICATION_DATA', 'AUTHENTICATION_METHOD', 'BG_JOB_ID', 'CLIENT_IDENTIFIER', 'CLIENT_INFO', 'CURRENT_BIND', 'CURRENT_SCHEMA', 'CURRENT_SCHEMAID', 'CURRENT_SQL', 'CURRENT_SQLn', 'CURRENT_SQL_LENGTH', 'DB_DOMAIN', 'DB_NAME', 'DB_UNIQUE_NAME', 'ENTRYID', 'ENTERPRISE_IDENTITY', 'FG_JOB_ID', 'GLOBAL_CONTEXT_MEMORY', 'GLOBAL_UID', 'HOST', 'IDENTIFICATION_TYPE', 'INSTANCE', 'INSTANCE_NAME', 'IP_ADDRESS', 'ISDBA', 'LANG', 'LANGUAGE', 'MODULE', 'NETWORK_PROTOCOL', 'NLS_CALENDAR', 'NLS_CURRENCY', 'NLS_DATE_FORMAT', 'NLS_DATE_LANGUAGE', 'NLS_SORT', 'NLS_TERRITORY', 'OS_USER', 'POLICY_INVOKER', 'PROXY_ENTERPRISE_IDENTITY', 'PROXY_GLOBAL_UID', 'PROXY_USER', 'PROXY_USERID', 'SERVER_HOST', 'SERVICE_NAME', 'SESSION_USER', 'SESSION_USERID', 'SESSIONID', 'SID', 'STATEMENTID', 'TERMINAL' );
BEGIN
FOR i IN a.FIRST .. a.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (a(i) ' : ' SYS_CONTEXT ('USERENV', a(i)) );
END LOOP;
END; Відкрити / Коментувати

2022-01-26 12:49:29 /*
ORACLE
DBlink
*/

:::Create database link
CREATE DATABASE LINK
CONNECT TO IDENTIFIED BY ""
USING
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)';

:::Usage
SELECT * FROM dual@;

10 views09:49

Відкрити / Коментувати

2022-01-25 10:30:53 /*
ORACLE
DDL trigger
*/

:::Create audit table
CREATE TABLE audit_ddl
(
t_sysdate DATE,
t_osuser VARCHAR2 (255),
t_current_user VARCHAR2 (255),
t_host VARCHAR2 (255),
t_terminal VARCHAR2 (255),
t_owner VARCHAR2 (30),
t_type VARCHAR2 (30),
t_name VARCHAR2 (30),
t_sysevent VARCHAR2 (30)
);

:::Create DDL trigger
CREATE OR REPLACE TRIGGER audit_ddl_trg
AFTER DDL
ON SCHEMA /*ON DATABASE*/
BEGIN
IF (ora_sysevent = 'DROP') THEN
INSERT
INTO audit_ddl
(
t_sysdate,
t_osuser,
t_current_user,
t_host,
t_terminal,
t_owner,
t_type,
t_name,
t_sysevent
)
VALUES
(
SYSDATE,
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
SYS_CONTEXT ('USERENV', 'HOST'),
SYS_CONTEXT ('USERENV', 'TERMINAL'),
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent
);
END IF;
END;

:::Get result
SELECT * FROM audit_ddl ORDER BY 1 DESC; Відкрити / Коментувати