void addData(PGconn *conn){
//dodanie przykladowych danych
doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Audi', '80', 6000.0, 1.8, '04.05.1991')");
doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Volkswagen', 'Sharan', 8000.0, 2.0, '16.03.1997')");
doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Audi', 'A3', 10000.0, 1.6, '25.12.1997')");
doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Seat', 'Ibiza', 6000.0, 1.9, '01.09.2001')");
doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Renault', 'Megane', 15000.0, 2.2, '10.02.2006')");
}
int main()
{
PGresult *result;
PGconn *conn;
conn = PQconnectdb("");
if(PQstatus(conn) == CONNECTION_OK) {
printf("connection made\n");
doSQL(conn, "DROP TABLE number");
doSQL(conn, "CREATE TABLE number (
value INTEGER,
name VARCHAR
)");
doSQL(conn, "INSERT INTO number values(42, 'The Answer')");
doSQL(conn, "INSERT INTO number values(29, 'My Age')");
doSQL(conn, "INSERT INTO number values(29, 'Anniversary')");
doSQL(conn, "INSERT INTO number values(66, 'Clickety-Click')");
doSQL(conn, "SELECT * FROM number WHERE value = 29");
doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 42");
doSQL(conn, "DELETE FROM number WHERE value = 29");
}
else
printf("connection failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
return EXIT_SUCCESS;
}
int SetupDocument::GetActiveDocuments(TDocument*& docs, int & count)
{
int state;
char *str;
str = (char *) calloc(strlen(GET_ACTIVE_DOCUMENTS) + 2, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, GET_ACTIVE_DOCUMENTS);
state = doSQL(myconnection, str);
free(str);
if ( state < 0 ) return -1;
count = PQntuples(result);
if ( count == 0 ) {
PQclear(result);
return 0;
}
docs = (TDocument *) calloc(count, sizeof(TDocument));
for ( int i = 0; i < count; i++ ) {
docs[i].id = atoi(PQgetvalue(result, i ,0));
strcpy( docs[i].name, PQgetvalue(result, i ,1) );
strcpy( docs[i].path, PQgetvalue(result, i ,2) );
}
PQclear(result);
return 0;
}
int SetupSamplebarcode::query_Update(TSampleBarcode* sam)
{
//printf("SetupSamplebarcode::UpdateSamples(%s)\n", sam->prefix);
int state;
char * str;
str = (char *) calloc(strlen(UPDATE_SAMPLE) + strlen(UPDATE_SAMPLE2) + 60, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, UPDATE_SAMPLE);
strcat(str, sam->prefix);
strcat(str, ", pattern = '");
strcat(str, sam->sample),
strcat(str, "', multiplier = ");
strcat(str, sam->multiplier);
strcat(str, UPDATE_SAMPLE2);
strcat(str, sam->prefix);
//printf("str = %s\n", str);
state = doSQL(myconnection, str);
free(str);
return state;
}
void searchBook(char dataPar[50]){
char sqlsearchscript[300]="SELECT * FROM ksiazka WHERE tytul='";
strcat(sqlsearchscript,dataPar);
strcat(sqlsearchscript,"';");
doSQL(conn,sqlsearchscript);
}
void deleteAutor(char pom[50]){
char sqlsearchscript[300]="DELETE FROM autor WHERE id_autor='";
strcat(sqlsearchscript,pom);
strcat(sqlsearchscript,"';");
doSQL(conn,sqlsearchscript);
printf("Usunieto pomyslnie !! \n\n");
}
void deleteBook(char pom[50]){
char sqlsearchscript[300]="DELETE FROM ksiazka WHERE id_ksiazki='";
strcat(sqlsearchscript,pom);
strcat(sqlsearchscript,"';");
doSQL(conn,sqlsearchscript);
printf("Usunieto pomyslnie !! \n\n");
}
//dodanie do bazy
void add(PGconn *conn){
char zapytanie[200];
char make[20], model[20], yop[10];
double price, capacity;
printf("Set make: ");
scanf("%s", make);
printf("Set model: ");
scanf("%s", model);
printf("Set price: ");
scanf("%lf", &price);
printf("Set capacity: ");
scanf("%lf", &capacity);
printf("Set year of production: ");
scanf("%s", yop);
printf("Your data: %s, %s, %lf, %lf, %s", make, model, price, capacity, yop);
sprintf(zapytanie, "INSERT INTO cars (make, model, price, capacity, yop) VALUES(\'%s\', \'%s\', %.2lf, %.1lf, \'%s\')", make, model, price, capacity, yop);
doSQL(conn, zapytanie);
}
//wyszukiwanie w bazie
void search(PGconn *conn){
int id;
char zapytanie[200];
printf("Set id car to search: ");
scanf("%d", &id);
sprintf(zapytanie, "SELECT * FROM cars WHERE id_car = %d", id);
doSQL(conn, zapytanie);
}
int SetupSamplebarcode::query_Insert(const char* scheme_name, char prefix[3], char sample[28], char multiplier[22])
{
//printf("SetupSamplebarcode::InsertSample(%s)\n", prefix);
int state;
char * str;
char * scheme_id;
str = (char *) calloc(strlen(GET_SCHEME_ID) +strlen(scheme_name) + 3, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, GET_SCHEME_ID);
strcat(str, scheme_name);
strcat(str, "'");
state = doSQL(myconnection, str);
free(str);
if ( state < 0 ) return state;
if ( PQntuples(result) != 1) { printf("Scheme_name not valid or scheme name was not found!!!\n"); return -4;}
scheme_id = (char*) calloc(PQgetlength(result, 0, 0) + 1, sizeof(char));
strcpy(scheme_id, PQgetvalue(result, 0, 0));
PQclear(result);
str = (char *) calloc(strlen(INSERT_SAMPLE) + strlen(scheme_id) + 160, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, INSERT_SAMPLE);
strcat(str, scheme_id);
strcat(str, ", ");
strcat(str, prefix);
strcat(str, ", '");
strcat(str, sample);
strcat(str, "', ");
strcat(str, multiplier);
strcat(str, ")");
//printf("str = %s\n", str);
state = doSQL(myconnection, str);
free(str);
return state;
}
int SetupDocument::AddDocument(const char* scheme_name, const char* name, const char* path)
{
int state;
char * str;
char * scheme_id;
str = (char *) calloc(strlen(GET_SCHEME_ID) + 2*strlen(scheme_name) + 3, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, GET_SCHEME_ID);
strcat(str, scheme_name);
strcat(str, "'");
state = doSQL(myconnection, str);
free(str);
if ( state < 0 ) return -1;
if ( PQntuples(result) != 1) { printf("Scheme_name not valid or scheme name was not found!!!\n"); return -4;}
scheme_id = (char*) calloc(PQgetlength(result, 0, 0) + 1, sizeof(char));
strcpy(scheme_id, PQgetvalue(result, 0, 0));
PQclear(result);
str = (char *) calloc(strlen(ADD_DOCUMENT) + 2*strlen(scheme_id) + 2*strlen(name) + 2*strlen(path) + 10, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, ADD_DOCUMENT);
strcat(str, scheme_id);
strcat(str, ", '");
strcat(str, name);
strcat(str, "', '");
strcat(str, path);
strcat(str, "')");
//printf("str = %s\n", str);
state = doSQL(myconnection, str);
free(str);
return state > 0 ? 0 : -1;
}
//==================================================================================//
int SetupSamplebarcode::SaveInScheme(const char * scheme_name, TSampleBarcode* sam, int sam_count)
{
//printf("SetupSamplebarcode:: SaveInScheme(%s)\n", scheme_name);
int state = doSQL(myconnection, "BEGIN WORK");
if ( state < 0 ) return state;
state = query_DeleteSamples(scheme_name);
if ( state < 0 ) { state = doSQL(myconnection, "ROLLBACK WORK"); printf("Insert discount was not!!\n"); return -1; }
for (int i = 0; i < sam_count; i++ ) {
state = AddNewSample(scheme_name, &sam[i]);
if ( state < 0 ) { state = doSQL(myconnection, "ROLLBACK WORK"); printf("Update discount was not!!\n"); return -1; }
}
state = doSQL(myconnection, "COMMIT WORK");
return state;
}
//usuniecie elementy z bazy
void drop(PGconn *conn){
int id;
char zapytanie[200];
printf("Set id car to delete: ");
scanf("%d", &id);
searchTMP(conn, id);
if(searchT){
sprintf(zapytanie, "DELETE FROM cars WHERE id_car = %d", id);
doSQL(conn, zapytanie);
}
searchT = 1;
}
int SetupSamplebarcode::query_Delete(TSampleBarcode* sam)
{
//printf("SetupSamplebarcode::DeleteSample(%s)\n", sam->prefix);
int state;
char * str;
str = (char *) calloc(strlen(DELETE_SAMPLE) + 5, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, DELETE_SAMPLE);
strcat(str, sam->prefix);
//printf("str = %s\n", str);
state = doSQL(myconnection, str);
free(str);
return state;
}
//==================================================================================//
int SetupSamplebarcode::query_DeleteSamples(const char*scheme_name)
{
//printf("SetupSamplebarcode::query_DeleteSamples(const char*scheme_name = %s)\n", scheme_name);
int state;
char * str;
str = (char *) calloc(strlen(DELETE_SAMPLES) + strlen(scheme_name) +5, sizeof(char));
strcat(str, DELETE_SAMPLES);
strcat(str, scheme_name);
strcat(str, "'");
strcat(str, ")");
//printf("str = %s\n", str);
state = doSQL(myconnection, str);
free(str);
return state;
}
//==================================================================================//
int SetupDocument::DeleteDocuments(const char*scheme_name)
{
int state;
char * str;
str = (char *) calloc(strlen(DELETE_DOCUMENTS) + 2*strlen(scheme_name) +5, sizeof(char));
strcat(str, DELETE_DOCUMENTS);
strcat(str, scheme_name);
strcat(str, "'");
strcat(str, ")");
//printf("str = %s\n", str);
state = doSQL(myconnection, str);
free(str);
return state > 0 ? 0 : -1;
}
int main()
{
PGresult *result;
PGconn *conn;
char odp, *name, *zapytanie;
int value;
conn = PQconnectdb("");
if(PQstatus(conn) == CONNECTION_OK) {
printf("connection made\n");
doSQL(conn, "DROP TABLE number");
doSQL(conn, "CREATE TABLE number (
value INTEGER PRIMARY KEY,
name VARCHAR
)");
printf("\n\n ---- czy wpisujemy co¶ do tabeli? (t/n) ");
while( (odp = getchar()) == 't' || (odp == 'T') ) {
getchar();
printf(" numer = "); scanf("%i",&value);
printf(" nazwa = "); scanf("%s",name);
getchar();
sprintf(zapytanie, "INSERT INTO number VALUES(%d, \'%s\')",
value, name);
doSQL(conn, zapytanie);
printf("\n\n ---- czy wpisujemy co¶ do tabeli? (t/n) ");
}
doSQL(conn, "SELECT * FROM number");
doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 1");
doSQL(conn, "DELETE FROM number WHERE value = 2");
doSQL(conn, "SELECT * FROM number");
}
else
printf("connection failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
return EXIT_SUCCESS;
}
int SetupSamplebarcode::GetSamples(const char* scheme_name, TSampleBarcode* sambar, int sam_count)
{
//printf("SetupSamplebarcode::GetSamples(%s)\n", scheme_name);
int state;
char *str;
str = (char *) calloc(strlen(GET_SAMPLES) + strlen(scheme_name) + 2, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, GET_SAMPLES);
strcat(str, scheme_name);
strcat(str, "'");
state = doSQL(myconnection, str);
if ( state < 0 ) return state;
if ( PQntuples(result) != sam_count ) { printf("Error: definition discounts count!!\n"); return -4; }
//printf("samP_count = %d \n", sam_count);
int i;
for ( i = 0; i < sam_count; i++ ) {
sambar[i].id = i;
// если в бд текст большей длины, томожет быть ошибка, проверить
strcpy( sambar[i].prefix, PQgetvalue(result, i ,0) );
strcpy( sambar[i].sample, PQgetvalue(result, i ,1) );
strcpy( sambar[i].multiplier, PQgetvalue(result, i ,2) );
//printf("sambar[%d] prefix = <%s>, name = <%s>, multi = <%s>\n", i, sambar[i].prefix, sambar[i].sample, sambar[i].multiplier);
}
free(str);
PQclear(result);
//printf("was get %d discounts \n", i);
return state;
}
int SetupSamplebarcode::GetCountSamples(const char * scheme_name, int& sam_count)
{
int state;
char * str;
str = (char *) calloc(strlen(GET_COUNT_SAMPLES) + strlen(scheme_name) + 2, sizeof(char));
if(str == NULL) printf("NULL CALLOC!!! parameter str\n");
strcat(str, GET_COUNT_SAMPLES);
strcat(str, scheme_name);
strcat(str, "'");
state = doSQL(myconnection, str);
free(str);
if ( state < 0 ) {printf("Error: state = %d\n", state); return state;}
if ( PQntuples(result) != 1) return -4;
sam_count = atoi(PQgetvalue(result, 0, 0) );
PQclear(result);
//printf("dis count = %d\n", (*dis_count));
return state;
}
int main(void){
PGresult *result;
PGconn *conn;
int z;
conn = PQconnectdb("host=localhost port=5432 dbname=dsienkiewicz user=dsienkiewicz password=aplikacje");
if(PQstatus(conn) == CONNECTION_OK) {
printf("connection made\n");
//czyszczenie ekranu
system("clear");
//menu
do{
printf("\n 1 - Create table");
printf("\n 2 - Add sample data");
printf("\n 3 - Print");
printf("\n 4 - Add");
printf("\n 5 - Drop from table");
printf("\n 6 - Update");
printf("\n 7 - Search");
printf("\n 8 - Add Trigger");
printf("\n 9 - Drop Trigger");
printf("\n 10 - Drop Table");
printf("\n 11 - Exit");
printf("\n choice: ");
scanf("%d", &z);
switch(z){
case 1: doSQL(conn, "CREATE TABLE cars(id_car SERIAL PRIMARY KEY, make VARCHAR(20), model VARCHAR(20), price money, capacity numeric(8,1), yop date);");
break;
case 2: addData(conn);
break;
case 3: print(conn);
break;
case 4: add(conn);
break;
case 5: drop(conn);
break;
case 6: update(conn);
break;
case 7: search(conn);
break;
case 8: createTrigger(conn);
break;
case 9: delTrigger(conn);
break;
case 10: dropTable(conn);
break;
}
}while(z != 11);
printf("Bye, bye ...\n");
}
else
printf("connection failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
return EXIT_SUCCESS;
}
void doSQL(PGconn *conn, char *command)
{
PGresult *result;
printf("%s\n", command);
result = PQexec(conn, command);
printf("status is : %s\n", PQresStatus(PQresultStatus(result)));
printf("#rows affected: %s\n", PQcmdTuples(result));
printf("result message: %s\n", PQresultErrorMessage(result));
int dalej;
while(dalej=0)
{
switch(PQresultStatus(result)) {
case PGRES_TUPLES_OK:
{
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n),PQgetvalue(result,r,n));
printf("\n");
}
}
}
PQclear(result);
}
int main()
{
PGresult *result;
PGconn *conn;
char odp, nazwa[20], miasto[20],przydomek[20],data[20],zapytanie[50],wartosc[20];
int id,budzet;
const char *connection_str = "host=localhost port=5432 dbname=pzynis user=pzynis password=alamakota";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str,
PQerrorMessage(conn));
} else {
printf("Connected OK\n");
doSQL(conn, "SELECT * FROM number");
// doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 1");
// doSQL(conn, "DELETE FROM number WHERE value = 2");
doSQL(conn, "SELECT * FROM number");
}
int wybor;
printf("Podaj opcje : \n 1- stworz tabele \n 2- usun tabele \n 3- dodaj rekordy \n 4-Zawartosc tabeli \n 5-podyfikuj dane \n 6-usun rekord\n 7-stworz wyzwalacz\n 8-usun wyzwalacz\n ");
scanf("%i",&wybor);
switch(wybor)
{
case 1:
doSQL(conn, "DROP TABLE druzyny_pilkarskie");
doSQL(conn, "CREATE TABLE druzyny_pilkarskie (id INTEGER PRIMARY KEY, nazwa VARCHAR,miasto VARCHAR,przydomek VARCHAR,rok_zal DATE,budzet MONEY);");
break;
case 2:
doSQL(conn, "DROP TABLE druzyny_pilkarskie");
printf("Usunieto Tabele \n");
break;
case 3:
printf("\n\n ---- czy wpisujemy co do tabeli? (t/n) ");
scanf("%s",&odp);
while( (odp == 't') || (odp == 'T') ) {
getchar();
printf(" id = ");
scanf("%i",&id);
printf(" nazwa = ");
scanf("%s",nazwa);
printf(" miasto = ");
scanf("%s",miasto);
printf(" przydomek = ");
scanf("%s",przydomek);
printf(" data (dd-mm-rrrr)= ");
scanf("%s",data);
printf(" budzet = ");
scanf("%i",&budzet);
sprintf(zapytanie, "INSERT INTO druzyny_pilkarskie VALUES(%d, \'%s\',\'%s\',\'%s\',\'%s\',%d)", id, nazwa,miasto,przydomek,data,budzet);
printf ("zapytanie= %s\n ",zapytanie);
doSQL(conn, zapytanie);
printf("\n\n ---- czy wpisujemy co do tabeli? (t/n) ");
scanf("%s",&odp);
}
break;
case 4:
printf("Zawartosc tabeli :");
doSQL(conn, "SELECT* FROM druzyny_pilkarskie");
break;
case 5:
printf(" Modyfikowanie rekordów: \n\n");
printf(" Podaj id rekodu= ");
scanf("%i",&id);
sprintf(zapytanie, "SELECT * FROM druzyny_pilkarskie WHERE id=%i", id);
doSQL(conn,zapytanie);
printf("\n\nCo chcesz zmienic(id,nazwa,miasto,przydomek,data,budzet)? \n");
scanf("%s",nazwa);
printf("Podaj wartosc dla %s \n",nazwa);
scanf("%s",wartosc);
if((strncmp( nazwa, "id", 2 ) == 0 )||(strncmp( nazwa, "budzet", 6 ) == 0 ))
sprintf(zapytanie, "UPDATE druzyny_pilkarskie SET %s=%s where id=%i", nazwa,wartosc,id);
else
sprintf(zapytanie, "UPDATE druzyny_pilkarskie SET %s='%s' where id=%i", nazwa,wartosc,id);
doSQL(conn, zapytanie);
break;
case 6:
printf(" Podaj id rekordu do usuniecia: \n\n");
printf(" Podaj id rekodu= ");
scanf("%i",&id);
sprintf(zapytanie, " DELETE FROM druzyny_pilkarskie WHERE id=%i", id);
doSQL(conn, zapytanie);
break;
case 7:
doSQL(conn, "DROP TRIGGER wyzwalacz ON druzyny_pilkarskie ");
printf("Wyszukaj dane: \n\n");
printf(" Podaj dane do znalezienia= ");
scanf("%s",nazwa);
sprintf(zapytanie, " CREATE OR REPLACE FUNCTION funkcja() RETURNS TRIGGER AS $$ BEGIN IF NEW.budzet>=0 THEN NEW.budzet := now(); END IF ;RETURN NEW; END; $$ LANGUAGE 'plpgsql';");
doSQL(conn, zapytanie);
sprintf(zapytanie, "CREATE TRIGGER wyzwalacz AFTER INSERT OR UPDATE ON druzyny_pilkarskie FOR EACH ROW EXECUTE PROCEDURE funkcja();");
doSQL(conn, zapytanie);
break;
case 8:
doSQL(conn, "DROP TRIGGER wyzwalacz ON druzyny_pilkarskie ");
break;
case 9:
dalej=1;
break;
}
}
void menu(){
int wyb=0;
int end=1;
char pom[20];
while(end){
char pomInsert[50];
char pomInsert2[50];
char pomInsert3[50];
char sqlscript[300]="INSERT INTO ksiazka(id_autor,tytul,ksiazka_gatunek,cena,rabat)VALUES (";
char sqlscriptAut[300]="INSERT INTO autor(imie,nazwisko,rok_ur,pochodzenie)VALUES ('";
char sqlscriptUp[300]="UPDATE autor SET ";
char sqlscriptUpKs[300]="UPDATE ksiazka SET ";
char sqldeletescript[300]="";
printf("Co chcesz zrobic ?\n");
printf("[1] dodaj rekord\n");//jest
printf("[2] usun rekord\n");//jest
printf("[3] wyswietl wszystko\n");//jest
printf("[4] modyfikuj rekord\n");//brak pomyslu
printf("[5] szukaj\n");//jest
printf("[6] generuj html\n");//jest
printf("[7] zakoncz\n");
printf("Wybierasz : ");
scanf("%d",&wyb);
switch(wyb){
case 1:
printf("Wybierz tabele\n");
printf("[1] autor\n");
printf("[2] ksiazka\n");
printf("Wybierasz : ");
scanf("%d",&wyb);
switch(wyb){
case 1:
printf("Podaj imie autora : ");
scanf("%s",pomInsert);
strcat(sqlscriptAut,pomInsert);
strcat(sqlscriptAut,"','");
printf("Podaj nazwisko autora : ");
scanf("%s",pomInsert);
strcat(sqlscriptAut,pomInsert);
strcat(sqlscriptAut,"','");
printf("Podaj rok urodzenia : ");
scanf("%s",pomInsert);
strcat(sqlscriptAut,pomInsert);
strcat(sqlscriptAut,"','");
printf("Podaj pochodzenie : ");
scanf("%s",pomInsert);
strcat(sqlscriptAut,pomInsert);
strcat(sqlscriptAut,"');");
doSQL(conn,sqlscriptAut);
continue;
case 2:
printf("Podaj id autora : ");
scanf("%s",pomInsert);
strcat(sqlscript,pomInsert);
strcat(sqlscript,",'");
printf("Podaj tytul ksiazki uzwyajac _ zamiast spacji : ");
scanf("%s",pomInsert);
strcat(sqlscript,pomInsert);
strcat(sqlscript,"','");
printf("Podaj gatunek : ");
scanf("%s",pomInsert);
strcat(sqlscript,pomInsert);
strcat(sqlscript,"','");
printf("Podaj cene : ");
scanf("%s",pomInsert);
strcat(sqlscript,pomInsert);
strcat(sqlscript,"','");
printf("Podaj rabat : ");
scanf("%s",pomInsert);
strcat(sqlscript,pomInsert);
strcat(sqlscript,"');");
doSQL(conn,sqlscript);
continue;
}
case 2:
printf("Wybierz tabele\n");
printf("[1] autor\n");
printf("[2] ksiazka\n");
printf("Wybierasz : ");
scanf("%d",&wyb);
switch(wyb){
case 1:
printf("podaj id autora : ");
scanf("%s",pomInsert);
deleteAutor(pomInsert);
continue;
case 2:
printf("podaj id ksiazki : ");
scanf("%s",pomInsert);
deleteBook(pomInsert);
continue;
}
case 3:
printf("\n[KSIAZKA]\n\n");
doSQL(conn,"SELECT * FROM ksiazka;");
printf("\n[AUTOR]\n\n");
doSQL(conn,"SELECT * FROM autor;");
continue;
case 4:
printf("Wybierz tabele\n");
printf("[1] autor\n");
printf("[2] ksiazka\n");
printf("Wybierasz : ");
scanf("%d",&wyb);
switch(wyb){
case 1:
printf("Podaj nazwisko autora ktorego chcesz edytowac : ");
scanf("%s",pomInsert2);
printf("\nCzy zmieniamy imie? Jesli nie wpisz 0 jesli tak podaj nowe imie : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
strcat(sqlscriptUp,"imie='");
strcat(sqlscriptUp,pomInsert);
strcat(sqlscriptUp,"'");
}
strcpy(pomInsert3, pomInsert);
printf("\nCzy zmieniamy nazwisko? Jesli nie wpisz 0 jesli tak podaj nowe nazwisko : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
if(strcmp(pomInsert3,"0")!=0)
strcat(sqlscriptUp,",");
strcat(sqlscriptUp," nazwisko='");
strcat(sqlscriptUp,pomInsert);
strcat(sqlscriptUp,"'");
strcpy(pomInsert3, pomInsert);
}
printf("\nCzy zmieniamy rok_urodzenia? Jesli nie wpisz 0 jesli tak podaj nowy rok_rodzenia : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
if(strcmp(pomInsert3,"0")!=0)
strcat(sqlscriptUp,",");
strcat(sqlscriptUp," rok_ur='");
strcat(sqlscriptUp,pomInsert);
strcat(sqlscriptUp,"'");
strcpy(pomInsert3, pomInsert);
}
printf("\nCzy zmieniamy pochodzenie? Jesli nie wpisz 0 jesli tak podaj nowe pochodzenie : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
if(strcmp(pomInsert3,"0")!=0)
strcat(sqlscriptUp,",");
strcat(sqlscriptUp," pochodzenie='");
strcat(sqlscriptUp,pomInsert);
strcat(sqlscriptUp,"'");
}
strcat(sqlscriptUp," WHERE nazwisko='");
strcat(sqlscriptUp,pomInsert2);
strcat(sqlscriptUp,"';");
doSQL(conn,sqlscriptUp);
continue;
case 2:
printf("Podaj tytul ksiazki ktora chcesz edytowac : ");
scanf("%s",pomInsert2);
printf("\nCzy zmieniamy tytul? Jesli nie wpisz 0 jesli tak podaj nowy tytul : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
strcat(sqlscriptUpKs,"tytul='");
strcat(sqlscriptUpKs,pomInsert);
strcat(sqlscriptUpKs,"'");
}
strcpy(pomInsert3, pomInsert);
printf("\nCzy zmieniamy gatunek? Jesli nie wpisz 0 jesli tak podaj nowy gatunek : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
if(strcmp(pomInsert3,"0")!=0)
strcat(sqlscriptUpKs,",");
strcat(sqlscriptUpKs," ksiazka_gatunek='");
strcat(sqlscriptUpKs,pomInsert);
strcat(sqlscriptUpKs,"'");
strcpy(pomInsert3, pomInsert);
}
printf("\nCzy zmieniamy cene? Jesli nie wpisz 0 jesli tak podaj nowa cene : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
if(strcmp(pomInsert3,"0")!=0)
strcat(sqlscriptUpKs,",");
strcat(sqlscriptUpKs," cena='");
strcat(sqlscriptUpKs,pomInsert);
strcat(sqlscriptUpKs,"'");
strcpy(pomInsert3, pomInsert);
}
printf("\nCzy zmieniamy rabat? Jesli nie wpisz 0 jesli tak podaj nowy rabat : ");
scanf("%s",pomInsert);
if(strcmp(pomInsert,"0")!=0){
if(strcmp(pomInsert3,"0")!=0)
strcat(sqlscriptUpKs,",");
strcat(sqlscriptUpKs," rabat='");
strcat(sqlscriptUpKs,pomInsert);
strcat(sqlscriptUpKs,"'");
}
strcat(sqlscriptUpKs," WHERE tytul='");
strcat(sqlscriptUpKs,pomInsert2);
strcat(sqlscriptUpKs,"';");
doSQL(conn,sqlscriptUpKs);
continue;
}
continue;
case 5:
printf("Wybierz tabele\n");
printf("[1] autor\n");
printf("[2] ksiazka\n");
printf("Wybierasz : ");
scanf("%d",&wyb);
switch(wyb){
case 1:
printf("podaj nazwisko autora : ");
scanf("%s",pomInsert);
searchAutor(pomInsert);
continue;
case 2:
printf("podaj tytul : ");
scanf("%s",pomInsert);
searchBook(pomInsert);
continue;
}
case 6:
generateHTML();
continue;
case 7:
end=0;
printf("\n\t\tKoniec tego dobrego :)\n\n");
break;
default:
printf("\nBledny wybor sprobuj jeszcze raz.\n");
}
}
}
//usuniecie tabeli
void dropTable(PGconn *conn){
doSQL(conn, "DROP TABLE cars");
}
//update elementu w bazie
void update(PGconn *conn){
int id;
char zapytanie[200];
char make[20], model[20], yop[10];
double price, capacity;
char odp;
printf("Set id car to update: ");
scanf("%d", &id);
searchTMP(conn, id);
if(searchT){
sprintf(zapytanie, "SELECT * FROM cars WHERE id_car = %d", id);
doSQL(conn, zapytanie);
printf("Would you like to change make? Y/N: ");
getchar();
scanf("%c", &odp);
if(odp == 89 || odp == 121){
printf("Set new make: ");
scanf("%s", make);
sprintf(zapytanie, "UPDATE cars SET make = \'%s\' WHERE id_car = %d", make, id);
doSQL(conn, zapytanie);
odp = 0;
}
printf("Would you like to change model? Y/N: ");
getchar();
scanf("%c", &odp);
if(odp == 89 || odp == 121){
printf("Set new model: ");
scanf("%s", model);
sprintf(zapytanie, "UPDATE cars SET model = \'%s\' WHERE id_car = %d", model, id);
doSQL(conn, zapytanie);
odp = 0;
}
printf("Would you like to change price? Y/N: ");
getchar();
scanf("%c", &odp);
if(odp == 89 || odp == 121){
printf("Set new price: ");
scanf("%lf", &price);
sprintf(zapytanie, "UPDATE cars SET price = %.2lf WHERE id_car = %d", price, id);
doSQL(conn, zapytanie);
odp = 0;
}
printf("Would you like to change capacity? Y/N: ");
getchar();
scanf("%c", &odp);
if(odp == 89 || odp == 121){
printf("Set new capacity: ");
scanf("%lf", &capacity);
sprintf(zapytanie, "UPDATE cars SET capacity = %.1lf WHERE id_car = %d", capacity, id);
doSQL(conn, zapytanie);
odp = 0;
}
printf("Would you like to change year of production? Y/N: ");
getchar();
scanf("%c", &odp);
if(odp == 89 || odp == 121){
printf("Set new year of production: ");
scanf("%s", yop);
sprintf(zapytanie, "UPDATE cars SET yop = \'%s\' WHERE id_car = %d", yop, id);
doSQL(conn, zapytanie);
odp = 0;
}
}
searchT = 1;
}
void searchAutor(char dataPar[50]){
char sqlsearchscript[300]="SELECT * FROM autor WHERE nazwisko='";
strcat(sqlsearchscript,dataPar);
strcat(sqlsearchscript,"';");
doSQL(conn,sqlsearchscript);
}
void delTrigger(PGconn *conn){
doSQL(conn, "DROP TRIGGER car_insert on cars;");
doSQL(conn, "DROP FUNCTION car_wpis();");
}
//wyszukiwanie w bazie
void searchTMP(PGconn *conn, int id){
char zapytanie[200];
sprintf(zapytanie, "SELECT * FROM cars WHERE id_car = %d", id);
doSQL(conn, zapytanie);
}
//strowrzenie triggera w bazie
void createTrigger(PGconn *conn){
doSQL(conn, "CREATE FUNCTION car_wpis() RETURNS trigger AS $$ BEGIN IF character_length(NEW.make) < 2 THEN RAISE EXCEPTION 'Wrong make'; END IF; IF character_length(NEW.model) < 2 THEN RAISE EXCEPTION 'Wrong model'; END IF; IF NEW.price < 1000 THEN RAISE EXCEPTION 'Price to small'; END IF; IF NEW.capacity > 5 THEN RAISE EXCEPTION 'Wrong capacity'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;");
doSQL(conn, "CREATE TRIGGER car_insert BEFORE INSERT OR UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE car_wpis();");
}
