Difference between revisions of "Relational and Non-relational Databases"

From Gridkaschool
(PostgreSQL (Setup))
(PostgreSQL (SQL))
Line 71: Line 71:
   
 
SELECT * FROM test_table;
 
SELECT * FROM test_table;
  +
  +
== PostgreSQL (C) ==
  +
  +
<code>
  +
#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);
  +
}
  +
</code>
  +
  +
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

Revision as of 10:38, 8 September 2015

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)

  1. include <stdio.h>
  2. include <stdlib.h>
  1. 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