wiki.server17.net

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== SQLite ======
The Standard stuff is pretty easy. There is also a good [[https://www.tutorialspoint.com/sqlite|summeray]]. Here we go with a quick start to create a DB-file with a table in it and do some stuff. 
<code>
# sqlite3 
sqlite> .table mydb.db
sqlite> CREATE TABLE IF NOT EXISTS T_names(name TEXT, birthyear INT);
sqlite> INSERT INTO T_names VALUES ('John Smith', 1999);
sqlite> INSERT INTO T_names VALUES ('Jack Doe', 1998);
sqlite> SELECT * FROM T_names;
sqlite> SELECT name FROM T_names WHERE birthyear=1998;
sqlite> UPDATE T_names SET birthyear=1997 WHERE name = 'Jack Doe';
sqlite> SELECT * FROM T_names LIMIT 1;
</code>

====== mySQL / MariaDB ======
<code>
CREATE DATABASE IF NOT EXISTS meineDB;
CREATE USER 'benutzer'@'localhost' IDENTIFIED BY 'passwort';
GRANT ALL PRIVILEGES ON meineDB . * TO 'benutzer'@'localhost';
</code>

Grant Access from remote IP (10.11.12.13)
<code>
GRANT ALL ON meineDB.* to 'benutzer'@'10.11.12.13' IDENTIFIED BY 'passwort' WITH GRANT OPTION;
</code>

====== PostgreSQL ======
A useful tutorial is [[https://www.tutorialspoint.com/postgresql/|here]].

Create, drop and list DB
<code>
CREATE DATABASE dbname;
DROP DATABASE dbname;
\l #List DBs
</code>

Connect to DB
<code>
\c dbname #Connect to DB
</code>

Select with multiple LIKE and NOT LIKE
<code>
SELECT something, anything FROM T_table WHERE something != 'thing' AND anything LIKE '%any%' AND anything NOT LIKE '%stuff%' AND anything NOT LIKE '%other%';
SELECT something, anything FROM T_table WHERE something LIKE ANY (ARRAY['%som%', '%thing', 'any%', 'stuff%'])
</code>

Create a new DB and User and change DB-Owner
<code>
CREATE DATABASE mynewdb;
CREATE USER myuser WITH PASSWORD 'Aw5s0m4Secur3';
ALTER DATABASE mynewdb OWNER TO myuser;
</code>

Copying PostgreSQL database to another server
<code>
sudo -u postgres pg_dump -C mynewdb | psql -h db.server.host -U myuser mynewdb
</code>
Original command from [[https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server|stackoverflow]].

===== Enable remote connections =====
  - Find your postgresql.conf <code>sudo -u postgres psql -c "SHOW config_file;" </code>
  - Open the postgresql.conf  (/var/lib/pgsql/18/data/postgresql.conf) <code>#listen_addresses = 'localhost'
listen_addresses = '*' # OR a specific IP instead of the wildcard for all IPs</code>
  - Find your pg_hba.conf (on RHEL based distros: /var/lib/pgsql/data/pg_hba.conf)
  - Add the following lines <code># Allow external
host    all             all             10.11.12.0/24              scram-sha-256 # for a network
host    all             all             192.168.1.111/32           scram-sha-256 # for a single IP</code>
  - Restart the DBMS <code>systemctl restart postgresql.service</code>

===== A method to get the lastest timestamp =====
From my point of view I've a good and a bad example

The good example: 
<code>SELECT something FROM table 
WHERE time_stamp = (SELECT MAX(time_stamp) FROM table);</code>

The bad example:
<code>SELECT something FROM table 
WHERE time_stamp = (SELECT time_stamp FROM table ORDER BY time_stamp DESC NULLS LAST LIMIT 1);</code>

===== Other usefull stuff =====
Switch off the pages from CLI(shell)
<code>sudo -u postgres psql -P pager=off -d database -c "SELECT something FROM table;"</code>

Copy a SELECT to a CSV from CLI(shell)
<code>
sudo -u postgres psql -d database -c "COPY (SELECT  something, other_stuff FROM table 
GROUP BY something) TO '/tmp/result.csv' WITH CSV HEADER;"
</code>

Edit summary

Note: By editing this page you agree to license your content under the following license: CC Attribution-Share Alike 4.0 International