% SQL-Report
% Version 0.1
% Licensed under GNU GPL v3 or later version
\def\title{SQL--Report}
@mp@-
``u{YJ"@<Predeclaration of procedures@>=
qJA";
J"@
"@<Procedure codes@>=
B" {
@)
@s FILE int
@s time_t int
@s tm int
@*Introduction. This program is making a report from an SQL database and a
template file specifying the format of the report. The output will be on
standard output, and can be a plain text file or it could be a \TeX\ input
file that can be processed into DVI to print out.
@c
@<Include files@>@;
@h
@<Global variables@>@;
@<Predeclaration of procedures@>@;
@<Procedure codes@>@;
@ @<Include files@>=
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
@ This is include file and types of SQLite.
@f sqlite3 int
@f sqlite3_stmt int
@f sqlite3_context int
@f sqlite3_value int
@<Include files@>=
#include "sqlite/sqlite3.h"
@*String Functions. Some string functions might not be in standard C but
are useful here to make the new one. Here it is.
@-p char*restrcat(char*a,const char*b) {
a=realloc(a,strlen(a)+strlen(b)+1);
if(!a) return 0;
strcat(a,b);
return a;
}
@ @-p char*skipspaces(char*b) {
while(*b && *b<=' ') b++;
return b;
}
@*Main. The first parameter is database name, second is template name, and
the rest are optional and mean extra parameters, inserted as strings in
SQL statements wherever something like |"?1"| is found (except in a custom
function, in which they instead represent parameters to the function).
You can put a plus sign in front of the database name to allow writing as
well as reading.
@<Global variables@>=
char**arg;
int numarg;
@ @-p int main(int argc,char**argv) {
char*d; // Database name
char*t; // Template name
if(argc<2) @<Usage and exit@>;
d=argv[1];
t=argv[2];
arg=argv+3;
numarg=argc-3;
@<Load the database |d|@>;
@<Run the template |t|@>;
}
@ @<Usage and exit@>= {
fprintf(stderr,"usage: sqlreport <db> <tmpl>\n");
return 1;
}
@*Database Access. This is a SQLite database, loaded into a global
variable named |db|.
@<Global variables@>=
sqlite3*db;
@ @<Load the database |d|@>= {
int m=(*d=='+'&&d++)?SQLITE_OPEN_READWRITE:SQLITE_OPEN_READONLY;
if(sqlite3_open_v2(d,&db,m,0)!=SQLITE_OK) {
fprintf(stderr,"Cannot open database: %s\n",sqlite3_errmsg(db));
return 2;
}
}
@*SQL Functions. This program allows the user template file to define
custom SQL functions.
@-p void custom_function(sqlite3_context*cxt,int N,sqlite3_value**val) {
sqlite3_stmt*st=sqlite3_user_data(cxt);
sqlite3_reset(st);
while(N--) sqlite3_bind_value(st,N+1,val[N]);
if(sqlite3_step(st)==SQLITE_ROW)
sqlite3_result_value(cxt,sqlite3_column_value(st,0));
else
sqlite3_result_null(cxt);
}
@ And this one defines it.
@-p void def_custom_fn(char*nm,char*ex) {
sqlite3_stmt*st;
sqlite3_prepare_v2(db,ex,-1,&st,NULL);
sqlite3_create_function(db,nm,-1,SQLITE_UTF8,st,custom_function,0,0);
}
@*Reading Templates. Template files are text files, where lines starting
with \.\# are special commands used by this program, dermined by the next
character.
The sequence |"#!"| is a comment, allowing you to have shebang lines in
the template file.
@<Run the template |t|@>= {
run_template(fopen(t,"r"),NULL);
}
@ Files can be called recursively, which means there is a function to do
it. There might be a SQL statement associated with it.
@-p void run_template(FILE*f,sqlite3_stmt*st) {
char buf[256];
char*col[128];
if(st) @<Store column names in |col|@>;
while(!feof(f)) {
if(!fgets(buf,255,f)) break;
@<Remove trailing whitespace if |'#'| at first@>;
if(*buf=='#') @<Run a special command@>@;
else @<Output |buf| filling in values of fields@>;
}
}
@ @<Remove trailing whitespace if |'#'| at first@>= {
char*k=buf+strlen(buf)-1;
if(*buf=='#') {
while(*k<=' ') *k--=0;
}
}
@ @<Run a special command@>= {
switch(buf[1]) {
case '!': break; // Comment or shebang line
case 'I': @<Load include file@>; @+break;
case 'X': @<Output hex bytes@>; @+break;
case 'F': @<Define a user function@>; @+break;
case 'Q': @<Run a query@>; @+break;
case 'Z': return;
}
}
@ @<Load include file@>= {
FILE*x=fopen(skipspaces(buf+2),"r");
if(x) {
run_template(x,st);
fclose(x);
}
}
@ @<Output hex bytes@>= {
int i;
char*b=skipspaces(buf+2);
for(i=0;b[i];i+=2) {
if(buf[i]>='A') buf[i]=(buf[i]&7)+9;
if(buf[i+1]>='A') buf[i+1]=(buf[i+1]&7)+9;
fputc((buf[i]<<4)|buf[i+1],f);
}
}
@ A user function is defined by specifying the name on first line, and the
SQL statement on the second line.
@<Define a user function@>= {
char b[256];
fgets(b,255,f);
def_custom_fn(skipspaces(buf+2),b);
}
@*Queries.
@<Run a query@>= {
sqlite3_stmt*s;
long o=ftell(f);
sqlite3_prepare_v2(db,buf+2,-1,&s,NULL);
if(!s) exit(3);
@<Fill in parameters of the statement@>;
@<Find the end of this query block@>;
while(sqlite3_step(s)==SQLITE_ROW) {
fseek(f,o,SEEK_SET);
run_template(f,s);
}
sqlite3_finalize(s);
}
@ @<Store column names in |col|@>= {
int i=sqlite3_column_count(st);
while(i--) col[i]=sqlite3_column_name(st,i);
}
@ There are two kind of parameters. Starting with |'?'| are global program
arguments, and starting with |'$'| are result fields of the parent query.
@<Fill in parameters of the statement@>= {
int i;
for(i=0;i<numarg;i++)
sqlite3_bind_text(s,i+1,arg[i],-1,SQLITE_STATIC);
if(st) {
i=sqlite3_column_count(st);
while(i--) {
char*c=sqlite3_column_name(st,i);
if(c) {
char*u=malloc(strlen(c)+2);
int n;
strcpy(u+1,c);
*u='$';
n=sqlite3_bind_parameter_index(s,u);
if(n) sqlite3_bind_value(s,n,sqlite3_column_value(st,i));
free(u);
}
}
}
}
@ @<Find the end of this query block@>= {
int n=1;
while(n && !feof(f)) {
if(!fgets(buf,255,f)) break;
if(*buf=='#') {
n+=(buf[1]=='Q');
n-=(buf[1]=='Z');
}
}
}
@ Now the important part. Anything with a |'`'| character means to use a
result field value from the query. Use |"``"| for a literal |'`'|.
@<Output |buf| filling in values of fields@>= {
char*p=buf;
while(*p) {
if(*p=='`' && p[1]=='`') {
fputc('`',stdout);
p+=2;
} @+else if(*p=='`') {
@<Read field name and output its value@>;
} @+else {
fputc(*p++,stdout);
}
}
}
@ @<Read field name and output its value@>= {
char*n=p+1;
char*c=strchr(n,'`');
*c=0;
int i=sqlite3_column_count(st);
while(i--) if(!strcmp(col[i],n)) break;
if(i>=0) fputs(sqlite3_column_text(st,i),stdout);
p=c+1;
}
@*Index.