Create a new version of paste:

#include "wet.h"
#include <stdio.h>
#include <string.h>
#include <libpq-fe.h>


// ===================== HELPER FUNCTIONS =============================
int	cmpDates (char* date1, char* date2)	{
	//returns -1 if date1 < date2, +1 if date1>date2, 0 if date1==date2.
	char yr1[4], yr2[4];
	char mon1[2], mon2[2];
	int i;
	for (i=0; i<4;i++ )	{
		yr1[i] = date1[i];
		yr2[i] = date2[i];
	}
	if (atoi(yr1) < atoi(yr2))
		return -1;
	else if (atoi(yr1) > atoi(yr2))
		return 1;
	
	//else if years are equal
	for (i=0; i<2;i++ )	{
		mon1[i] = date1[i+5];
		mon2[i] = date2[i+5];
	}
	if (atoi(mon1) < atoi(mon2))
		return -1;
	else if (atoi(mon1) > atoi(mon2))
		return 1;
	//else if months are equal
	for (i=0; i<2;i++ )	{
		mon1[i] = date1[i+8];
		mon2[i] = date2[i+8];
	}
	if (atoi(mon1) < atoi(mon2))
		return -1;
	else if (atoi(mon1) > atoi(mon2))
		return 1;
	else
		return 0;
}


void  checkQuery(PGresult* res)
{
	if(!res || PQresultStatus(res) != PGRES_TUPLES_OK){
		fprintf(stdout, "Error executing query: %s\n",
			PQresultErrorMessage(res));
		PQclear(res);
		exit(1);
	}
}

int dropView (PGconn* conn, char* cmd, char* viewName, int viewsToDrop) {
		if (0 > viewsToDrop--)	{
			PQfinish(conn);
			exit(1);
		}
		sprintf(cmd,viewName);
		PQexec(conn,cmd);
		return viewsToDrop;
}

void  checkCmd(PGresult* res)	{

	if(!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stdout, "Error executing query: %s\n",
			PQresultErrorMessage(res));
		PQclear(res);
		return;
	}
}

void  checkCmdDropViews(PGconn* conn, PGresult* res, int viewsToDrop)	{

	if(!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
		char cmd[50];
		fprintf(stdout, "Error executing query: %s\n",
			PQresultErrorMessage(res));
		PQclear(res);
		fprintf(stdout, "Dropping temp views.\n");
		
		viewsToDrop = dropView (conn, cmd, "DROP VIEW loser_versions", viewsToDrop);
		viewsToDrop = dropView (conn, cmd, "DROP VIEW first_update", viewsToDrop);
		viewsToDrop = dropView (conn, cmd, "DROP VIEW hostiles", viewsToDrop);
		viewsToDrop = dropView (conn, cmd, "DROP VIEW hos_count", viewsToDrop);
		viewsToDrop = dropView (conn, cmd, "DROP VIEW hostileNoriginal", viewsToDrop);
		viewsToDrop = dropView (conn, cmd, "DROP VIEW pre_report", viewsToDrop);
		viewsToDrop = dropView (conn, cmd, "DROP VIEW usersusers", viewsToDrop);
		exit (1);
		
	}
}
	

void  checkConn(PGconn* conn)	{
	
	if (!conn || PQstatus(conn) == CONNECTION_BAD) {
		fprintf(stdout, "Connection to server failed: %s\n",
			PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}
}
// ============================ MAIN FUNCTIONS ================================
	
void * addUser(int id, char * name)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res;
	PGconn *conn;
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM users
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
 	checkQuery(res);
	if (PQntuples(res)!=0){
		printf (ILL_PARAMS);
		return NULL;
	}
	cmd[0] = '\0';
	sprintf(cmd, "INSERT INTO users VALUES %d %s", id, name);
	res = PQexec(conn, cmd);
	checkCmd(res);
	PQfinish(conn);
	return NULL;
}

void * addEditor(int id)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res;
	PGconn *conn;
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM users
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)==0){
		printf (ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "SELECT * FROM editor
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)!=0){
		printf (ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "INSERT INTO editor VALUES %d", id);
	res = PQexec(conn, cmd);
	checkCmd(res);
	PQfinish(conn);
	return NULL;
}

void * addAdmin(int id)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res;
	PGconn *conn;
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM users
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)==0){
		printf (ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "SELECT * FROM admin
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)!=0){
		printf (ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "SELECT * FROM editor
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)==0){
			
			sprintf(cmd, "INSERT INTO editor VALUES %d", id);
			res = PQexec(conn, cmd);
			checkCmd(res);
	}
	
	sprintf(cmd, "INSERT INTO admin VALUES %d", id);
	res = PQexec(conn, cmd);
	checkCmd(res);
	PQfinish(conn);
	return NULL;
}
void * addLink(char * fromTitle, char * toTitle)
{
	char cmd[200];
	PGresult *res1, *res2;
	PGconn *conn;
	char connect_param[80];
		sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM article
					WHERE title=%s", fromTitle);
	res1 = PQexec(conn, cmd);
	checkQuery(res1);
	
	sprintf(cmd, "SELECT * FROM article
					WHERE title=%s", toTitle);
	res2 = PQexec(conn, cmd);
	checkQuery(res2);
	if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
		printf(ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "INSERT INTO link VALUES '%s' '%s'", fromTitle, toTitle);
	res1 = PQexec(conn, cmd);
	checkCmd(res1);
	PQfinish(conn);
	return NULL;
}

void * addViewed(int id, char * title, char * date)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res1, *res2;
	PGconn *conn;
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM users
					WHERE id=%d", id);
	res1 = PQexec(conn, cmd);
	checkQuery(res1);
	
	sprintf(cmd, "SELECT * FROM version
					WHERE title=%s", title);
	res2 = PQexec(conn, cmd);
	checkQuery(res2);
	if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
		printf(ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "INSERT INTO viewed VALUES %d %s DATE(%s)", id, title, date);
	res1 = PQexec(conn, cmd);
	checkCmd(res1);
	PQfinish(conn);
	return NULL;
}

void * addVersion(int id, char * date, char * title, char * content)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res;
	PGconn *conn;
	printf("in addVer().\n");
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)==0) {
		printf(ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "SELECT * FROM article	WHERE title='%s'", title);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)==0) {
		sprintf(cmd, "INSERT INTO article VALUES ('%s') ", title);
		res = PQexec(conn, cmd);
		checkCmd(res);
	}
	sprintf(cmd, "INSERT INTO version VALUES (%d,'%s','%s','%s')", id, date, title, content);
	res = PQexec(conn, cmd);
	checkCmd(res);
	PQfinish(conn);
	printf("not in addVer().\n");
	return NULL;
}

void * addAccepted(int eid, int aid, char * title, char * vdate, char * adate)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res1, *res2;
	PGconn *conn;
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM admin
					WHERE id=%d", aid);
	res1 = PQexec(conn, cmd);
	checkQuery(res1);
	
	sprintf(cmd, "SELECT * FROM editor
					WHERE id=%d", eid);
	res2 = PQexec(conn, cmd);
	if(!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK){
		fprintf(stdout, "Error executing query: %s\n",
		PQresultErrorMessage(res2));
		PQclear(res2);
		return NULL;
	}
	if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
		printf(ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "SELECT * FROM version
					WHERE title=%s AND date= %s", title, vdate);
	res1 = PQexec(conn, cmd);
	checkQuery(res1);
	if ((PQntuples(res1)==0) || (cmpDates(adate,vdate)==-1)) {
		printf(ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "INSERT INTO accepted VALUES %d, %d, %s, DATE(%s), DATE(%s)",eid, aid, title,vdate,adate);
	res1 = PQexec(conn, cmd);
	checkCmd(res1);
	PQfinish(conn);
	return NULL;
}

void * removeUser(int id, int opt, int newId)
{
	char cmd[200];
	char connect_param[80];
	PGresult *res,*res1,*res2;
	PGconn *conn;
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "SELECT * FROM users
					WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkQuery(res);
	if (PQntuples(res)==0){
		printf (ILL_PARAMS);
		return NULL;
	}
	
	sprintf(cmd, "SELECT * FROM viewed	WHERE id=%d", id);
	res1 = PQexec(conn, cmd);
	checkCmd(res1);
	
	sprintf(cmd, "SELECT * FROM accepted WHERE aid=%d", id);
	res2 = PQexec(conn, cmd);
	checkCmd(res2);
	if ( (PQntuples(res1)!=0) && (PQntuples(res2)!=0) && (opt==REJECT)) {
		printf(ILL_PARAMS);
		return NULL;
	}
	switch (opt)	{
		case DELETE:
			
			sprintf(cmd, "DELETE FROM viewed WHERE id=%d", id);
			res = PQexec(conn, cmd);
			checkCmd(res);
			
			sprintf(cmd, "DELETE FROM accepted WHERE aid=%d", id);
			res = PQexec(conn, cmd);
			checkCmd(res);
			
			sprintf(cmd, "DELETE FROM accepted WHERE eid=%d",id);
			res = PQexec(conn, cmd);
			checkCmd(res);
			
			sprintf(cmd,"DELETE FROM viewed WHERE title, date IN 
					SELECT title, date FROM viewed
					WHERE date < ALL(SELECT adate FROM accepted WHERE accepted.title=viewed.title)");
			res = PQexec(conn,cmd);
			checkCmd(res);
			
			break;
	
		case CHANGE:
			
			sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
			res1 = PQexec(conn, cmd);
			checkQuery (res1);
			if (PQntuples(res1) >0)	{
				
				sprintf(cmd, "SELECT * FROM editor WHERE id=%d", newId);
				res2 = PQexec(conn, cmd);
				checkQuery (res2);
				if (PQntuples(res2)==0)  {
					printf(ILL_PARAMS);
					return NULL;
				}
			}
			
			sprintf(cmd, "SELECT * FROM accepted WHERE aid=%d", id);
			res1 = PQexec(conn, cmd);
			checkQuery (res1);
			if (PQntuples(res1) >0)	{
				
				sprintf(cmd, "SELECT * FROM admin WHERE id=%d", newId);
				res2 = PQexec(conn, cmd);
				checkQuery (res2);
				if (PQntuples(res2)==0)  {
					printf(ILL_PARAMS);
					return NULL;
				}
			}
			
			sprintf(cmd, "UPDATE version SET id=%d WHERE id=%d", newId, id);
			res = PQexec(conn, cmd);
			checkCmd (res);
			
			sprintf(cmd, "UPDATE accepted SET eid=%d WHERE eid=%d", newId, id);
			res = PQexec(conn, cmd);
			checkCmd (res);
			
			sprintf(cmd, "UPDATE accepted SET aid=%d WHERE aid=%d", newId, id);
			res = PQexec(conn, cmd);
			checkCmd (res);
		break;
		default: printf("error! default case reached in removeUSer()!.\n");
			exit(1);	
			break;
	}
	sprintf(cmd, "DELETE FROM users WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkCmd(res);
	
	
	sprintf(cmd, "DELETE FROM editor WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkCmd(res);
	
	sprintf(cmd, "DELETE FROM admin WHERE id=%d", id);
	res = PQexec(conn, cmd);
	checkCmd(res);
}
void * popularVersions(char * fromDate, char * toDate, int distinct, int aboveAvg)
{
	return NULL;
}
void * hostileEditors()
{
	char cmd[400];
	char connect_param[200];
	PGresult *res,*res1,*res2;
	int i,nTuples;
	PGconn *conn;
	int viewsToDrop = 1;

	printf("in hostileeditors().\n");
	
	sprintf(connect_param,
		"host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
		USERNAME, USERNAME, PASSWORD);
	conn = PQconnectdb(connect_param);
	checkConn(conn);
	sprintf(cmd, "CREATE VIEW loser_versions AS (SELECT id, title, date FROM version WHERE		id IN(			SELECT id FROM editor			WHERE id NOT IN (SELECT id FROM version)))");
	res = PQexec(conn, cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);
	
	sprintf(cmd,"CREATE VIEW TempView1 AS		SELECT N.id AS Editor, V.id AS O_Editor, V.title AS title, N.date AS e_date, V.date AS o_date 		FROM loser_versions N LEFT OUTER JOIN Version V 		ON (N.title = V.title) AND (V.date - N.date BETWEEN 0 AND 7) AND (V.id <> N.id)");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"CREATE VIEW first_update AS	SELECT Editor, title, e_date, min(o_date) AS orig_date	FROM TempView1	GROUP BY Editor,title,e_date");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"CREATE VIEW hostiles AS		SELECT DISTINCT T1.Editor, o_editor		FROM TempView1 T1, first_update F		WHERE (T1.editor = F.editor) AND (T1.o_date = F.orig_date)");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"CREATE VIEW hos_count AS		SELECT * FROM 		(SELECT editor, count(o_editor) as num FROM hostiles GROUP BY editor) T		WHERE num = 1");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"CREATE VIEW hostileNoriginal AS		SELECT hos_count.editor, o_editor FROM		hos_count LEFT OUTER JOIN hostiles ON (hos_count.editor = hostiles.editor)");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"CREATE VIEW pre_report AS		SELECT loser_versions.id AS loser_id, o_editor AS hostile_id FROM		loser_versions LEFT OUTER JOIN hostileNoriginal HN ON (loser_versions.id = HN.editor)		ORDER BY loser_versions.id");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"CREATE VIEW usersusers AS		SELECT U1.id AS u1_id, U1.name AS u1_name, U2.id AS u2_id, U2.name AS u2_name FROM users U1, users U2");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkCmdDropViews(conn, res, viewsToDrop++);


	sprintf(cmd,"SELECT u1_id,u1_name,u2_id,u2_name	FROM pre_report LEFT OUTER JOIN usersusers ON (u1_id=pre_report.loser_id) AND (u2_id=pre_report.hostile_id)");
	PQclear(res);
	res = PQexec(conn,cmd);
	checkQuery(res);
	nTuples=PQntuples(res);
	printf(HOSTILE_EDITOR_HEAD);
	for (i=0;i<nTuples;i++) {
		char value1[11];
		char value2[16];
		char value3[11];
		char value4[16];
		strcpy(value1, PQgetvalue(res, i,1));
		strcpy(value2, PQgetvalue(res, i,2));
		if (!PQgetisnull(res, i, 3)) 
			strcpy(value3, PQgetvalue(res, i,3));
		else 
			strcpy(value3, "");
		if (!PQgetisnull(res, i, 4)) 
			strcpy(value4, PQgetvalue(res, i,4));
		else 
			strcpy(value4, "");
		printf(HOSTILE_EDITOR_REC,value1,value2,value3,value4);
	}
	
	sprintf(cmd,"DROP VIEW loser_versions");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(res, viewsToDrop--);

	sprintf(cmd,"DROP VIEW TempView1");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"DROP VIEW first_update");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"DROP VIEW hostiles");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"DROP VIEW hos_count");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"DROP VIEW hostileNoriginal");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(conn, res, viewsToDrop++);

	sprintf(cmd,"DROP VIEW pre_report");
	res = PQexec(conn,cmd);
	//checkCmdDropViews(conn, res, viewsToDrop++);
	PQclear(res);
	PQfinish(conn);
	printf("not in hostileeditors().\n");
	return NULL;
}

int main()
{
	startParsing();
}