Relational and Non-relational Databases

Overview

Author: Mario Lassnig, CERN PH-ADP-CO, [1]

File:Slides.pdf

PostgreSQL (Setup)

vim /var/lib/pgsql/9.4/data/pg_hba.conf

set all to trust

add line

host all all 0.0.0.0/0 trust

psql -U gridka01 gridka_db

should not ask for password ctrl-d to exit

sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE ROLE gridka01 PASSWORD 'asdf1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;"
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE DATABASE gridka_db OWNER gridka01 ENCODING 'UTF8'"
sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "CREATE SCHEMA gridka_schema AUTHORIZATION gridka01"
sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON SCHEMA gridka_schema TO gridka01"
sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON DATABASE gridka_db TO gridka01"

vim /var/lib/pgsql/9.4/data/pg_hba.conf

set all to md5

systemctl restart postgresql-9.4.service

psql -U gridka01 gridka_db

should ask for password

ldconfig

ls -la /var/lib/pgsql/9.4/data

PostgreSQL (SQL)

psql -U gridka01 gridka_db

\l .. list all databases
\d .. list all relations

CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR );

\d

SELECT * FROM test_table;

INSERT INTO test_table VALUES ('Vincent', 'Vega');

SELECT * FROM test_table;

INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');

SELECT * FROM test_table;

DELETE FROM test_table;

SELECT * FROM test_table;

DROP TABLE test_table;

SELECT * FROM test_table;

PostgreSQL (C)

#include <stdio.h>
#include <stdlib.h>

#include <libpq-fe.h>

int main()
{
        PGconn *conn;
        conn = PQconnectdb("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'");
        if (PQstatus(conn) != CONNECTION_OK) {
                printf("%s\n", PQerrorMessage(conn));
                exit(1);
        }

        PGresult *res;
        res = PQexec(conn, "CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )");
        if (!res)
                printf("%s\n", PQerrorMessage(conn));
        PQclear(res);

        res = PQexec(conn, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )");
        if (!res)
                printf("%s\n", PQerrorMessage(conn));
        PQclear(res);

        res = PQexec(conn, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )");
        if (!res)
                printf("%s\n", PQerrorMessage(conn));
        PQclear(res);

        res = PQexec(conn, "SELECT * FROM test_table");
        if (PQresultStatus(res) == PGRES_TUPLES_OK)
                for(int i = 0; i < PQntuples(res); ++i)
                        printf("%s %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));
        else
                printf("%s\n", PQerrorMessage(conn));
        PQclear(res);

        res = PQexec(conn, "DELETE FROM test_table");
        if (!res)
                printf("%s\n", PQerrorMessage(conn));
        PQclear(res);

        PQfinish(conn);
}

gcc -g -std=c11 -Wall -Wextra -I$(/usr/pgsql-9.4/bin/pg_config --includedir) -L$(/usr/pgsql-9.4/bin/pg_config --libdir) -lpq test_pg.c -o test_pg