% 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.