= databases == mysql \g SHOW DATABASES; \r phplist; SHOW TABLES; == postgresql apt install postgresql-client sudo -u postgres psql -d knuth_db -U postgres -W -f ./init_db.sql sudo -u postgres psql -d knuth_db -U postgres -W psql -U -h -p -w ... no-password -W ... force password prompt \connect # connect to a database \? # help for \ commands \d # describe name \du # list roles \dn # list schemas \l # list databases \dt # list tables \d # list tables, views, and sequences === postgresql via Hans-Jürgen Schönig (GLT 2018) ==== moving average (with 5 rows window) time=# SELECT year, production, consumption, avg(production) OVER (ORDER BY year ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) FROM t_oil WHERE country = 'USA' ; ==== get decade (1950, 1960, 1970, ...) of year (1954, 1961, 1977, ...) test=# SELECT *, round(year - 5, -1) as decade FROM t_oil WHERE country = 'USA'; ==== lag to push one column down by 1 row SELECT *, t - lag(t, 1) OVER (ORDER BY t) AS diff FROM t_series; === Clustering clustering Primary node will read/write with application Standby nodes will replicate data of Primary node off-site backups, monitoring, alerting, testing, training, documentation ... is required availability per year: 90% ... 36.35 days down 99% ... 3.65 days 99.9% ... 8.8h 99.99% ... 52.6min 99.999% ... 5.3min auto failover := standby node becomes Primary node and takes over work if Primary node fails RTO := recovery time objective == amount of time acceptable to take to recover entire database RPO := recovery point objective == amount of data acceptable to lose in case of a crash Shared-Everything architecture := all nodes have the same roles, read/write operations can go to any node !! requires load balancing for read/write !! conflict resolution must be done at application layer synchronous replication := acknowledgement of replica is waited for before commit asynchronous replication := acknowledgement of replica is not waited for before commit; scales better; data may be inconsistent Split Brain := more than one node thinks it is a Primary Node or because of lack of connectivity all nodes function independently !! monitoring and alerting required ++ concepts: {cluster manager, quorum-based decision making, witness server to resolve a tie} 1. disable application traffic 2. isolate nodes 3. identify most up-to-date node (logs are crucial: transaction IDs, timestamps, ...) 4. restore data consistency (older nodes shoud get data from most up-to-date one) 5. assign most up-to-date node as Primary 6. verify cluster integrity and working replication 7. enable application traffic 8. post mortem analysis Tools helping to manager PostgreSQL clusters: {repmgr, pgpool-II, Patroni} == OracleDB === system administration • Connection strings for thin clients have the structure jdbc:oracle:thin:@:/ • If you have an emergency and need to restart the listener, you can use (as oracle user) lsnrctl start && lsnrctl stop. If you want to restart the entire Oracle DB, use sqlplus sys as sysdba, shutdown immediate, and startup. • The lsnrctl utility might not be on your $PATH. Then use the absolute path /kisoft/db/product/*/db_1/bin/lsnrctl (where * is a version like 12.1.0) === properties • SQL • You can have duplicate use of resulting column names as long as the list after the column names after CREATE OR REPLACE FORCE VIEW viewname ( are unique. • If you need a date at some day, use the default format TO_DATE('31-DEC-2030') • To limit the number of rows returned, Oracle DB requires this awkward long constraint: OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; • If you have several tables in a FROM statement (do not it, in general) and try to introduce a JOIN statement, it will complain about an "invalid identifier" in the JOIN criterion. You cannot mix old-style JOINs (i.e. multiple tables in FROM) and modern ANSI JOINs. • PL/SQL • PL/SQL procedures/functions are only externally visible if they are mentioned in the declaration file as well. • If you define a type TABLE OF ... and have a value of such, you can get the number of entries with VARIABLE.COUNT. • If you want to represent a BOOLEAN as string in an expression (w/o using wmw_util.to_string or util_bool.to_string): sys.diutil.bool_to_int(my_bool_var) is helpful. • FOR cur IN ( substatement ) LOOP will not throw no_data_found exceptions if substatement does not yield any row. • utl_lms.format_message is neat for formatting, but limited in applicability. For example, DECLARE v_num PLS_INTEGER := 4; BEGIN dbms_output.put_line(utl_lms.format_message('<%d>', v_num)); END; works but if you replace PLS_INTEGER by NUMBER it won't. === SQL*Plus usage • Be sure to end every declaration/statement/… with a single line with a slash to enforce processing. Otherwise the parser will continue which might lead to unexpected syntax errors and previous results are not printed out. • If you use SQL*Plus interactively, you might want to set the following output configuration first: SET SERVEROUTPUT ON; SET LINESIZE 20000; or more exhaustively: SET ECHO ON; SET SERVEROUTPUT ON; SET TERMOUT ON; SET PAGESIZE 20000; SET LINESIZE 20000; SET LONG 20000; • CLOBs are represented in SQL*Plus as string of hexadecimal digits mapping to the stored bytes. You can turn it into a UTF-8 string using python. 1. First declare the hex digits as h (e.g. h = '2C616562632C616562632C616562632C66696E6F0A') 2. Then run ''.join([chr(int(h[2*i:2*i+2],16)) for i in range(len(h)//2)]) === Snippets Print SYSDATE in ISO 8601: SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') || 'T' || TO_CHAR(SYSDATE, 'HH24:MI:SS') || 'Z' FROM dual; If you want to write a PL/SQL script, you can start with the following template: DECLARE v_timestamp VARCHAR2(42); v_err error_code.token%TYPE; v_my_variable CONSTANT VARCHAR(30) := 'my content'; BEGIN SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') || 'T' || TO_CHAR(SYSDATE, 'HH24:MI:SS') || 'Z' INTO v_timestamp FROM dual; dbms_output.put_line( v_timestamp || ': start.' ); -- [[ your PL/SQL code goes here ]] SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') || 'T' || TO_CHAR(SYSDATE, 'HH24:MI:SS') || 'Z' INTO v_timestamp FROM dual; dbms_output.put_line( v_timestamp || ': end.' ); END;