Relational and Non-relational Databases: Difference between revisions
From Gridkaschool
Jump to navigationJump to search
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]
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