Game with postgreSQL

I developed this game a bit long ago, and would like to share with the world.

Yes, ofcourse, we can optimize the code of c here, but i have concentrated only on desired functionality for this. Once, i got the desired result, i haven't looked into any of the line in this code. {Very bad habbit, i need to over come this.}

I hope you enjoy it, and correct if any problems occurs.

This game is for only 2 players, which will give you the realtime game feel with your opponent.

First find the code, and then instructions.

C Program
-=-=-=-=-=-

#include "stdio.h"
#include "ncurses.h"
#include "/opt/PostgreSQL/9.0/include/libpq-fe.h"
#include "stdlib.h"
#include "string.h"
char symbol[3];
PGconn * PGconnect(char ch)
{
PGconn *conn;
PGresult *res;
FILE *fp;
int cnt,i=0;
char conn_string[500],hostaddr[32],port[7],dbname[50],user[50],password[50],name[10];
const char *paramValues[2];
fp=fopen("/tmp/.cred","r");
if(ch=='y')
{
fscanf(fp,"%[^:]s",hostaddr);
fscanf(fp,":%[^:]s",port);
fscanf(fp,":%[^:]s",dbname);
fscanf(fp,":%[^:]s",user);
fscanf(fp,":%s",password);
sprintf(conn_string,"hostaddr=%s port=%s dbname=%s user=%s password=%s",hostaddr,port,dbname,user,password);
}
else
{
fscanf(fp,"%[^\n]s",conn_string);
conn_string[0]='\0';
fscanf(fp,"\n%[^:]s",hostaddr);
fscanf(fp,":%[^:]s",port);
fscanf(fp,":%[^:]s",dbname);
fscanf(fp,":%[^:]s",user);
fscanf(fp,":%s",password);
sprintf(conn_string,"hostaddr=%s port=%s dbname=%s user=%s password=%s",hostaddr,port,dbname,user,password);
}

conn = PQconnectdb(conn_string);
if (PQstatus(conn) == CONNECTION_BAD)
        {
                 fprintf(stderr,"Not able to connect to the database %s",PQerrorMessage(conn));
                 return NULL;
        }
if(ch=='y')
{
res=PQexec(conn,"BEGIN");
PQclear(res);
res=PQexec(conn,"DECLARE find_taken CURSOR FOR select count(*) from public.pggame_control where taken=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(conn, "FETCH ALL in find_taken");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(conn,"END");
PQclear(res);

if(cnt==0)
{
res=PQexec(conn,"TRUNCATE public.pggame_control");
PQclear(res);
res=PQexec(conn,"INSERT INTO public.pggame_control values(false,false)");
PQclear(res);
}
printf("Enter Your Name -> ");
scanf("%s",name);
paramValues[0]=name;
printf("Enter Your Symbol -> ");
scanf("%s",symbol);
paramValues[1]=symbol;
res=PQexec(conn,"TRUNCATE public.pggame_status");
PQclear(res);
res=PQexecParams(conn,"INSERT INTO public.pggame_status(name,symbol,comments,status) values($1,$2,'',false)",2,NULL,paramValues,NULL,NULL,1);
                if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
                PQclear(res);
                }
                PQclear(res);
}
res=PQexec(conn,"TRUNCATE public.pggame_spots");
PQclear(res);
res=PQexec(conn,"TRUNCATE public.pggame_positions");
PQclear(res);
res=PQexec(conn,"INSERT INTO public.pggame_positions values(0,0)");
PQclear(res);
fclose(fp);
return conn;
}

int main()
{
int y=1,x=1,i=1,one=0;
char ch,x1[10],y1[10],cnt,remote_spot_check[200];
PGconn     *your_conn,*other_conn;
PGresult   *res;
FILE *log;
const char *paramValues[2];
log=fopen("/tmp/pggame.log","w");
if(!((your_conn=PGconnect('y'))&&(other_conn=PGconnect('o'))))
return 1;
initscr();
keypad(stdscr,TRUE);
noecho();
x=1;
y=1;
while(x+10<140)
{
x=x+2;
mvprintw(0,x+9,"-=");
mvprintw(36,x+9,"-=");
y=1;
}
y=0;
while(y<35)
{
++y;
mvprintw(y,12,".");
mvprintw(y,141,".");
}
y=2;
x=15;
refresh();
while(1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_spots CURSOR FOR select count(*) from public.pggame_spots");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_spots");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
if(cnt>=1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_remote_pos CURSOR FOR select * from public.pggame_spots where ctid=(select max(ctid) from public.pggame_spots)");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_remote_pos");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_symbol CURSOR FOR select symbol from public.pggame_status");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_symbol");
mvprintw(y+1,x+15,"%s",PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
}
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_win CURSOR FOR select count(*) from public.pggame_status where status=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_win");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
if(cnt>=1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_win CURSOR FOR select name from public.pggame_status where status=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_win");
mvprintw(15,40,"***** %s WIN The Game ***** .... Press Any Key To Exit ... ",PQgetvalue(res,0,0));
refresh();
PQclear(res);
res=PQexec(other_conn,"END");
getch();
getch();
endwin();
PQfinish(your_conn);
PQfinish(other_conn);
return 0;
}

res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select count(*) from public.pggame_positions");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_table");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
if(cnt==1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select * from public.pggame_positions");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_table");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
mvprintw(37,125,"CONTROL ==>> ");
mvprintw(y,x,"");
}
else
{
mvprintw(37,125,"CONTROL ==>> ");
mvprintw(1,1,"");
}

refresh();
usleep(10000);
one=0;
continue;
}
mvprintw(37,125,"CONTROL <<== ");
mvprintw(y,x,"");
refresh();
if(cnt==0)
{
res=PQexec(other_conn,"UPDATE public.pggame_control SET taken=true");
   if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(other_conn));
                PQclear(res);
                }
                PQclear(res);
if(one==0)
{
one=one+1;
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_spots CURSOR FOR select count(*) from public.pggame_spots");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_spots");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);

if(cnt>=1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_remote_pos CURSOR FOR select * from public.pggame_spots where ctid=(select max(ctid) from public.pggame_spots)");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_remote_pos");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_symbol CURSOR FOR select symbol from public.pggame_status");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_symbol");
mvprintw(y+1,x+15,"%s",PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select * from public.pggame_positions");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_table");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
mvprintw(y,x,"");
refresh();
}
}




res=PQexec(your_conn,"BEGIN");
PQclear(res);
res=PQexec(your_conn,"DECLARE find_spotted CURSOR FOR select count(*) from public.pggame_control where spot=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(your_conn, "FETCH ALL in find_spotted");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(your_conn,"END");
PQclear(res);
if(cnt>=1)
{
res=PQexec(your_conn,"UPDATE public.pggame_control SET taken=true where taken=false");
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);
res=PQexec(other_conn,"UPDATE public.pggame_control SET taken=false where taken=true");
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(other_conn));
                PQclear(res);
                }
                PQclear(res);
res=PQexec(your_conn,"UPDATE public.pggame_control set spot=false where spot=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);
}
}

ch=getch();
if(ch=='q'||ch=='Q')
break;
switch(ch)
{
case 'a':case 'A':
if(x==15)
x=137;
else
x=x-2;
mvprintw(y,x,"");
break;
case 's':case 'S':
if(y==34)
y=1;
else
y=y+1;
mvprintw(y,x,"");
break;
case 'w':case 'W':
if(y==1)
y=34;
else
y=y-1;
mvprintw(y,x,"");
break;
case 'd':case 'D':
if(x==137)
x=15;
else
x=x+2;
mvprintw(y,x,"");
break;
case 'j':case 'J':

res=PQexec(other_conn,"BEGIN");
PQclear(res);
sprintf(remote_spot_check,"DECLARE find_same_spot CURSOR FOR select count(*) from public.pggame_spots where y=%d and x=%d",y-1,x-15);
res=PQexec(other_conn,remote_spot_check);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_same_spot");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
if(cnt==0)
{
echo();
mvprintw(y,x,"%s",symbol);
sprintf(y1,"%d",y-1);
sprintf(x1,"%d",x-15);
paramValues[0]=y1;
paramValues[1]=x1;
res=PQexecParams(your_conn,"INSERT INTO public.pggame_spots(y,x) values($1,$2)",2,NULL,paramValues,NULL,NULL,1);
                if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(log, "INSERT failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);
noecho();
refresh();
}
}

sprintf(y1,"%d",y);
sprintf(x1,"%d",x);
paramValues[0]=y1;
paramValues[1]=x1;
res=PQexecParams(your_conn,"INSERT INTO public.pggame_positions(y,x) values($1,$2)",2,NULL,paramValues,NULL,NULL,1);
   if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(log, "INSERT failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);

res=PQexec(your_conn,"BEGIN");
PQclear(res);
res=PQexec(your_conn,"DECLARE find_win CURSOR FOR select count(*) from public.pggame_status where status=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(your_conn, "FETCH ALL in find_win");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(your_conn,"END");
if(cnt>=1)
{
mvprintw(15,40,"***** CONGRATULATIONS  !! ***** You WIN The Game .... Press Any Key To Exit ...");
refresh();
getch();
getch();
PQfinish(your_conn);
PQfinish(other_conn);
endwin();
return 0;
}
}
getch();
PQfinish(your_conn);
PQfinish(other_conn);
endwin();
return 0;
}

SQL for Postgres
-=-=-=-=-=-=-=-=

DROP TABLE public.pggame_positions CASCADE;
DROP TABLE public.pggame_spots CASCADE;
DROP TABLE public.pggame_status CASCADE;
DROP TABLE public.pggame_control CASCADE;
DROP VIEW coord_sum;
CREATE TABLE public.pggame_spots(y int,x int);
CREATE TABLE public.pggame_positions(y int,x int);
create view coord_sum as select e1.y+e1.x as cosum from pggame_spots e,pggame_spots e1 where (e.y,e.x) in (select yy,xx from (select e.y as yy,e1.y as y1,e.y-e1.y diffy,e.x as xx,e1.x as x1,e.x-e1.x diffx from pggame_spots e,pggame_spots e1 where e.y-e1.y between -4 and 4 and e.x-e1.x between -8 and 8) as foo group by yy,xx having array_agg(diffy)@>ARRAY[0,1,2,3,4] and (array_agg(diffx)@>ARRAY[0,-2,-4,-6,-8] or array_agg(diffx)@>ARRAY[0,2,4,6,8]));
CREATE OR REPLACE FUNCTION PUBLIC.PGGAME_INSERT_TO_UPDATE() RETURNS TRIGGER AS $$ DECLARE  CNT INT; BEGIN SELECT COUNT(*) INTO CNT FROM pggame_positions; IF(CNT=0) THEN RETURN NEW; ELSE UPDATE pggame_positions SET y=new.y,x=new.x; END IF; RETURN NULL; END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER PGGAME_INSERT_TO_UPDATE_TRIGG BEFORE INSERT ON public.pggame_positions FOR EACH ROW EXECUTE PROCEDURE public.PGGAME_INSERT_TO_UPDATE();
CREATE TABLE PUBLIC.PGGAME_CONTROL(TAKEN BOOLEAN,SPOT BOOLEAN);
INSERT INTO PUBLIC.PGGAME_CONTROL VALUES(FALSE,FALSE);
create table public.pggame_status(name varchar,symbol char(1),comments varchar,status boolean);
CREATE OR REPLACE FUNCTION PUBLIC.PGGAME_SPOTS_DUP_PREVENT() RETURNS TRIGGER AS $$ DECLARE CNT INT;BEGIN SELECT COUNT(*) INTO CNT FROM public.pggame_spots where y=new.y and x=new.x; 
IF(CNT>=1) THEN RETURN NULL;
ELSE 
UPDATE PUBLIC.PGGAME_CONTROL SET SPOT=TRUE WHERE TAKEN=FALSE;
RETURN NEW;
END IF;
END;
$$ 
LANGUAGE PLPGSQL;
CREATE TRIGGER PGGAME_SPOTS_DUP_PREVENT_TRIGG BEFORE INSERT ON public.pggame_spots FOR EACH ROW EXECUTE PROCEDURE public.PGGAME_SPOTS_DUP_PREVENT();
CREATE OR REPLACE FUNCTION PUBLIC.PGGAME_STATUS_CHECK() RETURNS TRIGGER AS $$ 
DECLARE 
CNT INT;
BEGIN 
select count(x) into cnt from  pggame_spots e where y=new.y and 5=(select count(*) from pggame_spots e1,(select generate_series(0,8,2) as seq) as e2 where e.x=e1.x+e2.seq and e1.y=new.y);
IF(CNT>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
ELSE
select count(Y) into cnt from  pggame_spots e where X=new.X and 5=(select count(*) from pggame_spots e1,(select generate_series(0,4,1) as seq) as e2 where e.y=e1.Y+e2.seq and e1.x=NEW.x);
IF(CNT>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
ELSE
select count(*) into cnt from pggame_spots e where 5=(select count(*) from (select e.y+generate_series(0,4,1),e.x-generate_series(0,8,2) from pggame_spots e1
intersect
select * from pggame_spots )as sub);
IF(cnt>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
else
select count(*) into cnt from pggame_spots e where 5=(select count(*) from (select e.y+generate_series(0,4,1),e.x+generate_series(0,8,2) from pggame_spots e1
intersect
select * from pggame_spots )as sub);
IF(cnt>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
end if;
END IF;
END IF;
END IF;
RETURN NULL;
END;
$$ 
LANGUAGE PLPGSQL;
CREATE TRIGGER PGGAME_STATUS_CHECK_TRIGG AFTER INSERT ON public.pggame_spots FOR EACH ROW EXECUTE PROCEDURE public.PGGAME_STATUS_CHECK();

Steps to run the game
-=-=-=-=-=-=-=-=--=-=
Step 1:- Execute above SQL in your instance and in your opponent instance.

Step 2:- Require second player’s details in your pg_hba.conf Create a file “/tmp/.cred”

192.168.1.2:5432:postgres:postgres:postgres => Your details. 192.168.1.3:5433:postgres:postgres:postgres => Your opponent details.
Syntax:- :::: Caution: - Don’t provide any dbname/user/password which is having “:” symbol. 

Step 3:- If you have the PGHOME=/opt/PostgreSQL/9.0 then game.c will work fine. If you don’t have, then please change the header file “/opt/PostgreSQL/9.0/include/libpq-fe.h” in game.c accordingly.

Step 4:-

export LD_LIBRARY_PATH according to your 9.0 instance.
Ex:-
export LD_LIBRARY_PATH=/opt/PostgreSQL/9.0/lib

Step 5:- Complie game.c Ex:-

gcc –o game game.c –L/opt/PostgreSQL/9.0/lib –lpq –lncurses

Step 6:- Run the game Ex:- ./game Enter Your Name -> Dinesh Enter Your Symbol -> D [Any character]

Instructions
-=-=-=-=-=-=
1. Use a,s,d,w for movements and j for spot the symbol.

a => Left

s => Down

d => Right

w => Up

j => For spot the symbol, and then one of the movement key.

Game Rules

-=-=-=-=-=

If you can frame the linear series of the length 5 then you are the winner. We can frame the series as horizontal/vertical/diagonal. 

 Ex:- Symbol is “*” 

* * * * * => Winner

*

*

*

*

* => Winner 

*

 *

   *

     *

      * => Winner

    

       *

     *

   * 

 *

* => Winner

Winner needs to start the new game.

Sample screen shots

-=-=-=-=-=-=-=-=-=-=

Dinesh Kumar