#include <stdio.h>
#include <string.h>
#include <tlmplib.h>
#include "tlmpsql.h"
#include "sample.h"

// #Specbeg: sample / sql_query component
/*
	sql_query is a general purpose way to perform an SQL query and
	iterate over the result. It provides enough functags to avoid
	all the redundant stuff normally associated with databases query.
*/
<mod>
static void sample_query()
{
	// Define the default database.
	// Use the sample_table.sh to create and stuff the sample database
	// sh sample_table.sh create
	// sh sample_table.sh stuff
	query_setdefaultdb ("localhost","sample");
	<call sql_query>("select * from test");
	<f empty>
		printf ("No record found\n");
	</f>
	<f dostart>
		printf ("List of records in table \"items\"\n");
	</f>
	<f doend>
		printf ("--------\n");
	</f>
	<f onerow>
		printf ("record %03d: %-30s\t%-30s\t%-20s\t%s\n",rownum
			,row[0],row[1],row[2],row[3]);
	</f>
	</call>
}
</mod>
// #Specend:

// #Specbeg: sample / sql_query and sqlgen_interface
/*
	Managing records in SQL database is often cumbersome. You end up
	with complex insert or update statement, with many fields. It is
	easy to get it wrong, mixing fields and so on.

	A "name" safe solution is needed. Using the sqlgen_interface utility
	one may create class definitions suitable to solve this. You can
	either create a class definition "by hand" or by extracting definitions
	from a database. The later is generally used. The goal is to produce
	a C++ include file. For example, using the sample database created
	by sample_table.sh, one will do

	sqlgen_interface --prefix --database sample test >sample.h

	where sample is the database name and test is one table.
*/
<mod>
static void sample_sqlgen_interface()
{
	query_setdefaultdb ("localhost","sample");
	// Here is a sample query using a  safe interface
	<call sql_query>("select * from test");
	<f onerow>
		C_test rec;
		rec.set (fields,row);
		printf ("record %03d: name=%s address=%s telephone=%s age=%d\n"
			,rownum
			,rec.name.get(),rec.address.get(),rec.telephone.get(),rec.age.getval());
	</f>
	</call>

	// Do some updates. We will change the phone number for member jack
	<glocal>
		C_test rec;
	</glocal>
	<call sql_query>("select * from test where name='jack'");
	<f onerow>
		glocal.rec.set (fields,row);
	</f>
	</call>
	// We change a field
	glocal.rec.telephone.setfrom ("666-7777");
	// Only the changed field will be updated.
	glocal.rec.update("test","name='%s'","jack");
}
</mod>

// #Specend:

<mod>
int main(int argc, char *argv[])
{
	<call tlmpprogram>(argc,argv,"tlmpsql");
	<f usage>
		fprintf (stderr,"available test: query sqlgen_interface\n");
	</f>
	<f main>
		if (strcmp(argv[0],"query")==0){
			sample_query();
		}else if (strcmp(argv[0],"sqlgen_interface")==0){
			sample_sqlgen_interface();
		}else{
			usage();
		}
		return 0;
	</f>
	</call>
	return 0;
}
</mod>