#.TH SqlIngres Onyx
#.SH NAME
#	SqlIngres.ppg \- Grammar for SQL to Ingres

include "onyx_config.h"
include "LibEquel.h"

class SqlIngres : SqlParser {
	HashList *sql_table_hash;
	HashList *sql_field_hash;

	Text *sql_tables;
	Text *sql_fields;

	char *quel_cmd;
	char range[64];
	int like_flag;

	SqlIngres();
	virtual ~SqlIngres();
	void GetInfo();

	virtual int sql_open();
	virtual int sql_close();

	virtual int sql();
	virtual int statement();

	virtual int colid();
	virtual int svalue();
	virtual int value();
	virtual int compop();
	virtual int boolop();
	virtual int numop();
	virtual int expression();
	virtual int condition();

	virtual int where();
	virtual int del();
	virtual int update();
	virtual int insert();
	virtual int select();
	virtual int type();
	virtual int create();
	virtual int drop();
	virtual int load();
	virtual int unload();
	virtual int grant();
	virtual int revoke();

	virtual int to_din(char *str);
	virtual int from_din(char *str);
	}

#.SH DESCRIPTION
#	SqlIngres is Ingres89 as a SQL-Engine. It makes the necessary
#	translations from SQL to QUEL, and calls EQUEL-libq directly.
#
#	I dont know if this is portable with ASK/Ingres ?
#
#	SqlIngres has some disadwantages related to this stupid parser.
#
#	SqlIngres doesnt have aggegate functions now, I think I'll
#	implement it in one of next versions. If Y need it ask me
#	for my support and hack it.
#
#	SqlIngres mostly works like SqlGawk.

sql : {	sql_purge(); }

	statement ";"

	| true {
		message("error:illegal statement");
		}
	;
#	
#       A Statement has to end with ";" or a message "error:illegal statement"
#       is raised.

statement :
	  database
	| del
	| update
	| insert
	| select
	| create
	| drop
	| load
	| unload
	| grant
	| revoke
	| matchexp
	;
#
#       Only a small subset of SQL is valid here.

colid : {	char *p,*q;
		}

	id {	if ((p=strchr(p_val,'_')))
			q=strdup(p+1);
		else	q=strdup(p_val);
		}
	  [ "." id {
	  	q=strrepcat(q,".");
		if ((p=strchr(p_val,'_')))
	  		q=strrepcat(q,p+1);
	  	else	q=strrepcat(q,p_val);
	  	}
	  ] {	free(p_val); p_val=q;
	  	}
	;
#
#	This matches a colum identifier.

svalue : {	char *q,*p;
		}
	"\"" match "\"" {
		quel_cmd = strrepcat(quel_cmd,"\"");
		if (like_flag) 
			quel_cmd = strrepcat(quel_cmd,"*");
		to_din(p_matched->lines[0]);
		quel_cmd = strrepcat(quel_cmd,p_matched->lines[0]);
		if (like_flag)
			quel_cmd = strrepcat(quel_cmd,"*");
		quel_cmd = strrepcat(quel_cmd,"\"");
		quel_cmd = strrepcat(quel_cmd," ");
		}
	| num {
		quel_cmd = strrepcat(quel_cmd,p_val);
		quel_cmd = strrepcat(quel_cmd," ");
		}
	;

value : {	char *q,*p;
		}
	svalue
	| "not" {
		quel_cmd = strrepcat(quel_cmd,"!(");
		}
	  expression {
		quel_cmd = strrepcat(quel_cmd,")");
		}
	| "(" {
		quel_cmd = strrepcat(quel_cmd,"(");
		}
	  expression 
	  ")" {	
	  	quel_cmd = strrepcat(quel_cmd,")");
	  	}
	| colid {
		if (strchr(p_val,'.'))
			p=sql_field_hash->get_buck(p_val);
		else 	p=sql_field_hash->get_buck(sql_tables->lines[0],p_val);

	  	if (!p) {
			message("field %s %s",p_val,"does not exist");
			return(0);
			}

		quel_cmd = strrepcat(quel_cmd,p);
		quel_cmd = strrepcat(quel_cmd," ");
		}
	;
#
#	A value can be a string, a number, a not expression, a expression
#	in braces or a identifier. Identifiers are translated here betwen
#	SQL and QUEL acording previous range statemenst.

compop :
	  "=" 
	| "!=" | "<>" { free(p_val); p_val=strdup("!="); }
	| "<=" | ">=" | "<" | ">" 
	| "like" { free(p_val); p_val=strdup("="); like_flag=1; }
	;
#
#       Here the different compare opperators are translated.
#
#	A like operator is translated to "=" and a flag is raised
#	for later be used in string values.
#
#	So its posible to write 'colid like "string"' but not
#	'colid like colid'

boolop :
	"and" 
	| "or"
	;
#
#       Here the boolean operators are translated.

numop :	  "+"
	| "-"
	| "*"
	| "/"
	;
#
#       Here the numeric operators are translated.

expression : {	if (!quel_cmd) quel_cmd = strdup("");
		}

	value
	( compop {
		quel_cmd = strrepcat(quel_cmd,p_val);
		}
	  value {
		like_flag=0; }
	| boolop {
		quel_cmd = strrepcat(quel_cmd,p_val); 
		quel_cmd = strrepcat(quel_cmd," "); 
		}
	  value
	| numop {
		quel_cmd = strrepcat(quel_cmd,p_val);
		}
	  value
	)
	;
#
#       A expression starts with a value and perhaps continues with
#       operator value pairs.

condition :
	expression
	;
#
#       A condition is nothing else than a expression. This is done
#       because PPG is only LL(1) and I dont have any better solution
#       in mind.
#
#       Caution no type checking is done, perhaps in a later version.

where : [ "where" { quel_cmd = strrepcat(quel_cmd,"where "); }
	  condition ]
	;
#
#       The "where" statement only has a condition.

del : {		char *p;
		}

	"delete" {
		if (quel_cmd) free(quel_cmd);
		quel_cmd = strdup("delete ");

		sql_fields->purge();
		sql_tables->purge();
		}
	"from" id {
		if (!(p=sql_table_hash->get_buck(p_val))) {
			message("table %s %s",p_val,"does not exist");
			return(0);
			}
		sql_tables->add_line(p_val);

		quel_cmd=strrepcat(quel_cmd,p);
		quel_cmd=strrepcat(quel_cmd," ");

		sprintf(range,"range of %s=%s",p,p_val);

		message(range);

		IIwrite(range);
		IIsync(0);
		}
	where {	message(quel_cmd);

		IIwrite(quel_cmd);
		IIsync(0);
		}
	;
#
#	This is doing the job of translating a delete statment to
#	a range and a quel-delete statement and
#	calling IIwrite and IIsync for doing the job.

update : {	char *p,*q;
		}

	"update" {
		if (quel_cmd) free(quel_cmd);
		quel_cmd = strdup("replace ");

		sql_fields->purge();
		sql_tables->purge();
		}

	id {	if (!(p=sql_table_hash->get_buck(p_val))) {
			message("table %s %s",p_val,"does not exist");
			return(0);
			}

		sql_tables->add_line(p_val);

		quel_cmd=strrepcat(quel_cmd,p);
		quel_cmd=strrepcat(quel_cmd,"(");

		sprintf(range,"range of %s=%s",p,p_val);

		message(range);

		IIwrite(range);
		IIsync(0);
		}
	"set" 

	( colid {	if (strchr(p_val,'.'))
			p=sql_field_hash->get_buck(p_val);
		else 	p=sql_field_hash->get_buck(sql_tables->lines[0],p_val);

		if (!p) {
			message("field %s %s",p_val,"does not exist");
			return(0);
			}

		q=strchr(p,'.')+1; if (!q) q=p;

		quel_cmd=strrepcat(quel_cmd,q);
		}
	  "=" { quel_cmd=strrepcat(quel_cmd,"=");
	  	}

	  expression

	  ! "," {
	  	quel_cmd=strrepcat(quel_cmd,", ");
	  	}
	) {	quel_cmd=strrepcat(quel_cmd,") ");
		}

	where {	message(quel_cmd);

		IIwrite(quel_cmd);
		IIsync(0);
		}
	;
#
#	This is doing the job of translating a update statment to
#	a range and a quel-replace statement and
#	calling IIwrite and IIsync for doing the job.

insert : {	char *p,*q;

		char **fields;
		int i;
		char tupvar;
		}

	"insert" {
		if (quel_cmd) free(quel_cmd);
		quel_cmd = strdup("append to ");

		sql_fields->purge();
		sql_tables->purge();
		}

	"into" id {
		if (!(p=sql_table_hash->get_buck(p_val))) {
			message("table %s %s",p_val,"does not exist");
			return(0);
			}

		sql_tables->add_line(p_val);

		quel_cmd=strrepcat(quel_cmd,p_val);
		quel_cmd=strrepcat(quel_cmd,"(");

		sprintf(range,"range of %s=%s",p,p_val);
		tupvar=*p;

		message(range);

		IIwrite(range);
		IIsync(0);
		}
	[ "(" 
	  ( colid {
		if (strchr(p_val,'.'))
			p=sql_field_hash->get_buck(p_val);
		else 	p=sql_field_hash->get_buck(sql_tables->lines[0],p_val);

	  	if (!p) {
			message("field %s %s",p_val,"does not exist");
			return(0);
			}
		q=strchr(p,'.')+1; if (!q) q=p;

	  	sql_fields->add_line(q);
	  	}
	  ! ","
	  )
	  ")"
	| true {
		for (i=0; i<sql_field_hash->nlines; i++) {
			p=sql_field_hash->get_val(i);
			if (p && (*p==tupvar) && (p[1]=='.')) 
				sql_fields->add_line(p+2);
			}
		}
	]

	"values" "(" {
		fields = sql_fields->lines;
		}

	( num {	
		quel_cmd=strrepcat(quel_cmd,*fields);
		quel_cmd=strrepcat(quel_cmd,"=");
		quel_cmd=strrepcat(quel_cmd,p_val);
		}
	| "\""
	  match {
		quel_cmd=strrepcat(quel_cmd,*fields);
		quel_cmd=strrepcat(quel_cmd,"=\"");
		to_din(p_matched->lines[0]);
		quel_cmd=strrepcat(quel_cmd,p_matched->lines[0]);
		quel_cmd=strrepcat(quel_cmd,"\"");
		}
	  "\""
	! "," {
		fields++;
		if (*fields) 
			quel_cmd=strrepcat(quel_cmd,",");
		else {	quel_cmd=strrepcat(quel_cmd,")");

			message(quel_cmd);

			IIwrite(quel_cmd);
			IIsync(0);

			q = strchr(quel_cmd,'(');
			q[1]=0;

			fields = sql_fields->lines;
			}
		}
	)

	")" {	q=quel_cmd+strlen(quel_cmd)-1;
		if (*q != '(') {
			quel_cmd=strrepcat(quel_cmd,")");

			message(quel_cmd);

			IIwrite(quel_cmd);
			IIsync(0);
			}
		}
	;
#
#	This is doing the job of translating a insert statment to
#	a range and a quel-append statement and
#	calling IIwrite and IIsync for doing the job.

select : {	char *p,*q;

		char **fields;
		char **tables;

		char line[1024];
		}

	"select" {
		if (quel_cmd) free(quel_cmd);
		quel_cmd = strdup("retrieve (");

		sql_fields->purge();
		sql_tables->purge();
		}

	( colid {
		sql_fields->add_line(p_val);
	  	}

	| "*" {	sql_fields->add_line("*");
		}
	! ","
	)

	"from"

	( id {	if (!(p=sql_table_hash->get_buck(p_val))) {
			message("table %s %s",p_val,"does not exist");
			return(0);
			}
		sql_tables->add_line(p_val);

		sprintf(range,"range of %s=%s",p,p_val);

		message(range);

		IIwrite(range);
		IIsync(0);
		}
	! ","
	) {	fields=sql_fields->lines;
		tables=sql_tables->lines;

		if (!strcmp(*fields,"*")) {
			p=sql_table_hash->get_buck(*tables);
			quel_cmd=strrepcat(quel_cmd,p);
			quel_cmd=strrepcat(quel_cmd,".all");
			}
		else
		while (*fields) {
			if ((p=strchr(*fields,'.')))
				q=sql_field_hash->get_buck(*fields);
			else 	q=sql_field_hash->get_buck(*tables,*fields);

			if (!q) {
				message("field %s does not exist",*fields);
				return(0);
				}

			quel_cmd=strrepcat(quel_cmd,q);

			fields++;

			if (*fields)
				quel_cmd=strrepcat(quel_cmd,",");
			}


		quel_cmd=strrepcat(quel_cmd,") ");
		}

	where 

	{	message(quel_cmd);

		IIwrite(quel_cmd);
		IIsetup();

		while (IIn_get(0)) {
			char *l;

			l = strdup("");

			while(IIr_sym.len) {
				IIn_ret(line,3); l = strrepcat(l,line);
				if (IIr_sym.len) l = strrepcat(l,"\t");
				}
			from_din(l);
			sql_row_text->add_pos(l);
			}

		IIflushtup(0);
		}
	;
#
#	This is doing the job of translating a select statment to
#	a range and a quel-retrieve statement and
#	calling IIwrite, IIsync, IIsetup, IIn_get, IIn_ret
#	for doing the job.
#
#	Important note : Y need a patched IIn_ret to run this !

type : {	char *p;
		p=0;
		}

	  "integer" { quel_cmd = strrepcat(quel_cmd,"i4"); }
	| "int" { quel_cmd = strrepcat(quel_cmd,"i4"); }
	| "smallint" { quel_cmd = strrepcat(quel_cmd,"i2"); }
	| "serial" { quel_cmd = strrepcat(quel_cmd,"i4"); }
	| "smallfloat" { quel_cmd = strrepcat(quel_cmd,"f4"); }
	| "real" { quel_cmd = strrepcat(quel_cmd,"f8"); }
	| "float" { quel_cmd = strrepcat(quel_cmd,"f8"); }
	| "date" { quel_cmd = strrepcat(quel_cmd,"c10"); }
	| "double" "precision" { quel_cmd = strrepcat(quel_cmd,"f8"); }
	| "char" [ "(" num { p=strdup(p_val); } ")" ] {
		quel_cmd = strrepcat(quel_cmd,"c");
		if (p)
			quel_cmd = strrepcat(quel_cmd,p);
		else
			quel_cmd = strrepcat(quel_cmd,"1");
		}
	| "varchar" "(" num { p=strdup(p_val); } ")" {
		quel_cmd = strrepcat(quel_cmd,"c");
		if (p)
			quel_cmd = strrepcat(quel_cmd,p);
		else
			quel_cmd = strrepcat(quel_cmd,"1");
		}
	| "decimal" [ "(" num [ "," num ] ")" ]
		{ quel_cmd = strrepcat(quel_cmd,"f8"); }
	| "dec" [ "(" num [ "," num ] ")" ]
		{ quel_cmd = strrepcat(quel_cmd,"f8"); }
	| "numeric" [ "(" num [ "," num ] ")" ]
		{ quel_cmd = strrepcat(quel_cmd,"f8"); }
	| "money" [ "(" num [ "," num ] ")" ]
		{ quel_cmd = strrepcat(quel_cmd,"f8"); }
	;

create : {	int first;
		char *key;
		char *rel;
		char *ind;
		char *p,*q;
		}
	"create" {
		first = 1;
		key = 0;
		if (quel_cmd) free(quel_cmd);

		sql_fields->purge();
		sql_tables->purge();
		}
	  "table" id {
		rel = strdup(p_val);
		quel_cmd = strdup("create ");
		quel_cmd = strrepcat(quel_cmd,p_val);
		quel_cmd = strrepcat(quel_cmd," ( ");
		} "(" 
	    ( "constraint" id "check" "(" match ")"
	    | "primary" "key" "(" match {
	    	key = strdup(p_matched->lines[0]); }
	    	")"
	    | "foreign" "key" "(" match ")" "references" id
	        [ "on"
	          [ "delete" | "update" ]
	          [ "cascade" | "restricted" | "set" id ]
	        ]
	    | colid {
		if (!first)
			quel_cmd = strrepcat(quel_cmd,",");
		first=0;
		quel_cmd = strrepcat(quel_cmd,p_val);
		quel_cmd = strrepcat(quel_cmd," is ");
		}
	      type
	    ! ","
	    )
	    ")" matchexp {
	    	quel_cmd = strrepcat(quel_cmd," ) ");
		message(quel_cmd);

		IIwrite(quel_cmd);
		IIsync(0);

		GetInfo();

		if (key) {
			free(quel_cmd);
			quel_cmd = strdup("modify ");
			quel_cmd = strrepcat(quel_cmd,rel);
			quel_cmd = strrepcat(quel_cmd," to isam on ");

			q=key;

			do {	p=strchr(q,'_');
				if (!p) p=q;
				else    p++;

				q=strchr(q,',');
				if (q) { *q=0; q++; }

				quel_cmd = strrepcat(quel_cmd,p);

				if (q) quel_cmd = strrepcat(quel_cmd,",");
				} while (q);

			free(key);

			message(quel_cmd);

			IIwrite(quel_cmd);
			IIsync(0);
			}
		free(rel);
		}
	 | "index" id {	ind = strdup(p_val); }
	   "on" id { rel = strdup(p_val); }
	   "(" id { key = strdup(p_val); }
	   ")" {
	   	quel_cmd = strdup("index on ");
	   	quel_cmd = strrepcat(quel_cmd,rel);
	   	quel_cmd = strrepcat(quel_cmd," is ");
	   	quel_cmd = strrepcat(quel_cmd,ind);
	   	quel_cmd = strrepcat(quel_cmd," ");
	   	quel_cmd = strrepcat(quel_cmd,key);

	   	free(rel); free(ind); free(key);
		message(quel_cmd);

		IIwrite(quel_cmd);
		IIsync(0);
		}
	 ;

drop : "drop" 
	  "table" id
	| "index" id
	;

load : "load" "from" "\"" match "\""
	"insert" "into" id 
	[ "commit" num ]
	;

unload : "unload" "to" "\"" match "\"" select
	;

grant : "grant" "all" "on" id "to" id
	;
revoke : "revoke" "all" "on" id "from" id
	.

#.SH AUTHOR
#       Michael Koehne <kraehe@bakunin.north.de>

{ /* ----------------------------------------------------------------------- */

SqlIngres::SqlIngres() : SqlParser()
{	debug("SqlIngres::new()");

	sql_table_hash = new HashList(16);
	sql_field_hash = new HashList(16);

	sql_tables = new Text(16);
	sql_fields = new Text(16);

	quel_cmd = 0;

	sql_row_type = 2;
	like_flag=0;
	}

SqlIngres::~SqlIngres()
{	debug("SqlIngres::delete()");

	if (sql_database) sql_close();

	delete sql_table_hash;
	delete sql_field_hash;
	delete sql_tables;
	delete sql_fields;

	if (quel_cmd) free(quel_cmd);
	}

void SqlIngres::GetInfo()
{	char rel[13];
	char name[13];
	char frmt[2];
	int frml;

	char *p;
	char relid[2];
	char col[26];
	char colid[15];

	debug("SqlIngres::getinfo()");

	strcpy(relid,"a"); (*relid)--;

	IIwrite("range of a=attribute");
	IIsync(0);

	IIwrite("retrieve(a.attrelid,a.attname,a.attfrmt,a.attfrml)");
	IIsetup();

	while(IIn_get(0)) {
		IIn_ret(rel,3);
		IIn_ret(name,3);
		IIn_ret(frmt,3);
		IIn_ret(&frml,6);
		if(IIerrtest())continue;

		if ((p=strchr(rel,' '))) *p=0;
		if ((p=strchr(name,' '))) *p=0;

		if (!(p=sql_table_hash->get_buck(rel))) {
			(*relid)++;
			sql_table_hash->add_buck(strdup(rel),strdup(relid));
			p=relid;
			}

		sprintf(col,"%s.%s",rel,name);
		sprintf(colid,"%s.%s",p,name);

		if (!(p=sql_field_hash->get_buck(col)))
			sql_field_hash->add_buck(strdup(col),strdup(colid));
		}

	IIflushtup(0);
	debug("SqlIngres::getinfo()OK");
	}

int SqlIngres::sql_open()
{	char *p;
	char *env;

	debug("SqlIngres::open()");

	env=(char*)stralloc(strlen(getenv("PATH"))+strlen(INGRES_HOME)+20);
	sprintf(env,"PATH=%s/bin:%s",INGRES_HOME,getenv("PATH"));
	debug("PATH="); debug(env); debug(" ");
	putenv(env);

	if (sql_database) {
		if ((p=strchr(sql_database,'%'))) *p=0;
		IIingres(sql_database,0);
		if (p) *p='%';
		GetInfo();
		message("Connected to %s",sql_database);
		return(1);
		}
	return(0);
	}

SqlIngres::sql_close()
{	debug("SqlIngres::close()");

	if (sql_database) {
		IIexit();
		free(sql_database);
		sql_database=0;
		return(1);
		}
	return(0);
	}

static char trans1[] = "\366\326\344\304\374\334\337";
static char trans2[] = "\174\044\173\133\175\135\176";

SqlIngres::to_din(char *str)
{	unsigned char *p;
	char *q;

	for (p=str; *p; p++) {
		if (q=strchr(trans1,*p)) 
		    *p=trans2[q-trans1];
		}
	}

SqlIngres::from_din(char *str)
{	unsigned char *p;
	char *q;

	for (p=str; *p; p++) {
		if (q=strchr(trans2,*p)) 
		    *p=trans1[q-trans2];
		}
	}

/* ----------------------------------------------------------------------- */ }