#!/usr/local/bin/perl -w

# www-sql2php
#
# This is filter which converts www-sql files into php3
# Reports are dumped to STDERR
#
# 2000-01-10 Dobrica Pavlinusic <dpavlin@rot13.org>
#
# Warnings:
# - parser it's pretty stiff about multi-line www-sql tabs. Only allowed
#   multiline tags are: print_rows, query
# - like operators in PostgreSQL ARE case sensitive -- your appl. may not
#   work because of that!
# - you WILL NEED TO CHANGE links and/or missed form actions by hand!
# - www-sql permits variables with slash (-) in name, php doesn't!

# configuration part

$db_user=$ENV{'USER'};	# connect to database as current user
$php_prefix='?php';	# type of php code prefix ('?' is also possible)

# end of configuration part


# report subroutine (future flag -q will shut this down)
sub r { print STDERR @_,"\n"; }

# read whole file into $wsql
@wsql=<>; $wsql=join("",@wsql);

# non-buffered output
$|=1;

# start relacements
$php=$php_prefix;

#---- variables convert
$wsql=~s#\.sql\?#\.php_que_#smg;		# protect vars in a href calls

while ($wsql=~ s#(<!\s*sql.+)\@(\w+)\.(\d+)#$1\$row_${2}[$3]#sm) {
	$conv{var_db_handle}++;
}
while ($wsql=~ s#(<!\s*sql.+)\?(\w+)#$1\$${2}#sm) {
	$conv{var_mysql_escape}++;
}
while ($wsql=~ s,(<!\s*sql.+)\#(\w+),$1rawUrlEncode(\$${2}),sm) {
	$conv{var_http_escape}++;
}


#---- database
$patt='<!\s*sql\s+database\s+(\w+)\s*>';
while ($wsql=~ m,$patt,mi) {
	$database=$1;
	$wsql=~s#$patt##mi;
	$conv{database}++;
}

#---- connect			FIX: user/password opt. args
$patt='<!\s*sql\s+connect\s*>';
while ($wsql=~ m,$patt,mi) {
	die "can't find database name!" if (!defined($database));
	$wsql=~s#$patt#
	<$php \$conn = pg_connect("dbname=$database user=$db_user"); ?>
	#mi;
	$conv{connect}++;
}

#---- print
$patt1='<!\s*sql\s+print\s+([^"][^>]+)\s*>';	# without quotes
$patt2='<!\s*sql\s+print\s+("[^"]+")\s*>';	# with quotes
while ($wsql=~m,$patt1,mi) {
	$wsql=~s#$patt1#<$php echo $1 ?>#mi;
	$conv{print1}++;
}
while ($wsql=~m,$patt2,mi) {
	$wsql=~s#$patt2#<$php echo $1 ?>#mi;
	$conv{print2}++;
}

#---- query

# ugly clugde to replace \" inside sql queryies with '
$wsql=~s#\\"#_qm_#smg;

$patt='<!\s*sql\s+query\s+"([^"]+)"\s*(\w*)\s*>';
while ($wsql=~ m,<!\s*sql\s+query\s+"([^"]+)"\s*(\w*)\s*>,smi) {
	die "can't find database name!" if (!defined($database));
	r "can't find connect, you should insert one!" if (! $conv{connect}>0);
	$query=$1;
	if (defined($2)) {
		$q_handle=$2;
	} else {
		$q_handle="tmp";
	}
	$wsql=~s#$patt#
	<$php
		\$result_${q_handle}=pg_Exec(\$conn,"$query");
		\$NUM_FIELDS=pg_NumFields(\$result_${q_handle});
		\$NUM_ROWS=pg_NumRows(\$result_${q_handle});
		\$AFFECTED_ROWS=pg_CmdTuples(\$result_${q_handle});
	//	\$INSERT_ID=pg_GetLastOid(\$result_${q_handle}); // FIX
		if (\$NUM_ROWS > 0) {
			\$row_${q_handle}=pg_fetch_row(\$result_${q_handle},0);
		}
	?>
	#smi;
	$handles{$q_handle}++;
	$conv{query}++;
}

#---- print_rows
$patt='<!\s*sql\s+print_rows\s+(\w+)\s+"([^"]+)"\s*>';
while ($wsql=~ m,$patt,smi) {
	die "can't find database name!" if (!defined($database));
	r "can't find connect, you should insert one!" if (! $conv{connect} > 0);
	$q_handle=$1;
	$format=$2;
	$wsql=~s#$patt#
	<$php
	for (\$k = 0; \$k < pg_numrows(\$result_${q_handle}); \$k++) {
		\$row_${q_handle}=pg_fetch_row(\$result_${q_handle},\$k);
		echo "$format";
	} //for ?>
	#smi;
	$handles{$q_handle}++;
	$conv{print_rows}++;
}

#---- include
while ($wsql=~ m,<!\s*sql\s+include\s+(\S+)\s*>,mi) {
	$include=$1;
	$wsql=~s#<!\s*sql\s+include\s+\S+\s*>#
	<$php include("$include"); ?>
	#mi;
	r "Convert include file $include also!";
	$conv{include}++;
}

#---- if
$patt='<!\s*sql\s+if\s+([^>]+)\s*>';
$wsql=~s#\\>#_gt_#smg;
while ($wsql=~ m,$patt,mi) {
	$wsql=~s#$patt#<$php if ($1): ?>#mi;
	$conv{if}++;
}
$wsql=~s#_gt_#>#smg;

#---- elsif
$patt='<!\s*sql\s+elsif\s+([^>]+)\s*>';
$wsql=~s#\\>#_gt_#smg;
while ($wsql=~ m,$patt,mi) {
	$wsql=~s#$patt#<$php elseif ($1):  ?>#mi;
	$conv{elsif}++;
}
$wsql=~s#_gt_#>#smg;

#---- else
$patt='<!\s*sql\s+else\s*>';
while ($wsql=~ m,$patt,mi) {
	$wsql=~s#$patt#<$php else: ?>#mi;
	$conv{else}++;
}

#---- endif
$patt='<!\s*sql\s+endif\s*>';
while ($wsql=~ m,$patt,mi) {
	$wsql=~s#$patt#<$php endif; ?>#mi;
	$conv{endif}++;
}

#---- ftime
$patt='<!\s*sql\s+ftime\s+([^>]+?)\s*([+-]*)(\d*)\s*>';
while ($wsql=~ m,$patt,mi) {
	if (defined($2)) {
		$op=$2;
	} else {
		$op="+";
	}
	$wsql=~s#$patt#<$php echo strftime($1,time()${op}$3) ?>#mi;
	$conv{ftime}++;
}

#---- qselect
$patt='<!\s*sql\s+qselect\s+(\w+)\s+(\w+)\s*(\w*)\s*>';
while ($wsql=~ m,$patt,smi) {
	die "can't find database name!" if (!defined($database));
	r "can't find connect, you should insert one!" if (! $conv{connect} > 0);
	($q_handle,$formvar)=($1,$2);
	warn "FIX: default value for qselect isn't impmeneted!" if (defined($3));
	$wsql=~s#$patt#
	<$php
	echo "<select name=\\\"$formvar\\\">\n";
	for (\$k = 0; \$k < pg_numrows(\$result_${q_handle}); \$k++) {
		\$row_${q_handle}=pg_fetch_row(\$result_${q_handle},\$k);
		echo "<option value=\\\"\$row_${q_handle}[0]\\\">\$row_${q_handle}[1]</option>\n";
	} //for
	echo "</select>\n"; ?>
	#smi;
	$handles{$q_handle}++;
	$conv{qselect}++;
}

#---- setdefault
$patt='<!\s*sql\s+setdefault\s+(\S+)[\s=](\S+)\s*>';
while ($wsql=~ m,$patt,mi) {
	($name,$value)=($1,$2);
	$wsql=~s#$patt#<$php if (!isset(\$$name)) { \$$name = $value; } ?>#mi;
	$conv{setdefault}++;
}

#---- set
$patt='<!\s*sql\s+set\s+(\S+)[\s=](\S+)\s*>';
while ($wsql=~ m,$patt,mi) {
	($name,$value)=($1,$2);
	$wsql=~s#$patt#<$php \$$name = $value ?>#mi;
	$conv{set}++;
}

#---- setexpr
$patt='<!\s*sql\s+setexpr\s+(\S+)[\s=]([^>]+)\s*>';
while ($wsql=~ m,$patt,mi) {
	($name,$value)=($1,$2);
	$wsql=~s#$patt#<$php \$$name = $value ?>#mi;
	$conv{setexpr}++;
}



# report some statistics

r "Database used: $database" if (defined($database));
r "Handle usage:";
foreach $key (sort keys %handles) { r "	$key: $handles{$key}"; }
r "Converted www-sql tags:";
foreach $key (sort keys %conv) { r "	$key: $conv{$key}"; }
r "THERE ARE UNCONVERTED TAGS IN OUTPUT!" if ($wsql=~m,<!\s*sql,);

# fix escaped chars bach
$wsql=~s#_qm_#\\"#smg;
$wsql=~s#_que_#\?#smg;

# fix form action which points to .sql
$wsql=~s#(form\s+action="*\w+)\.sql"*#$1.php#smg;

# dump converted www-sql onto STDOUT
print "$wsql";

print '<?php include("debug.inc") ?>';