/** -*-C-*-ish
    Kaya standard library
    Copyright (C) 2004, 2005 Edwin Brady

    This file is distributed under the terms of the GNU Lesser General
    Public Licence. See COPYING for licence.
*/
"<summary>Generic database API</summary>
<prose>A generic database connection and querying API. Separate database-specific modules are then used to set up connections to particular databases. Kaya currently distributes:</prose>
<list>
<item><moduleref>MyDB</moduleref> (MySQL)</item>
<item><moduleref>PostgresDB</moduleref> (Postgres)</item>
<item><moduleref>SQLiteDB</moduleref> (SQLite)</item>
</list>"
module DB;

import Prelude;
import Time;
import Regex;
// A database independent API

"<summary>Converting to Time failed</summary>
<prose>The DBValue couldn't be converted to a Time</prose>"
Exception InvalidTime;

"<summary>Query result values</summary>
<prose><code>DBValue</code> represents the possible values returned by a query.
Anything that the database-specific library cannot assign a type to should be converted to <code>DBText</code>.</prose>
<related><dataref>DBIncResult</dataref></related>
<related><dataref>DBResult</dataref></related>"
public data DBValue 
    = DBText(String text)
    | DBInt(Int num)
    | DBFloat(Float float)
    | DBTime(Int timestamp)
    | DBBool(Bool bool);

"<summary>A query result</summary>
<prose>DBResult holds data about the result of a query.</prose>
<list>
<item><code>table</code> gives the result of the query. It will usually be empty for non-SELECT queries.</item>
<item><code>rows</code> is the number of rows in the result set.</item>
<item><code>cols</code> is the number of columns in each row of the result set.</item>
<item><code>colnames</code> is a list of the names of the columns</item>
<item><code>resptr</code> is an internal pointer kept by the relevant database interface.</item>
</list>
<example>res = exec(conn,\"SELECT id,name FROM People\");
if (res.rows > 0) {
    for row in res.table {
        // row[0] = DBInt(id)
        // row[1] = DBText(name)
    }
}</example>
<related><dataref>DBIncResult</dataref></related>
<related><dataref>DBValue</dataref></related>
<related><functionref>exec</functionref></related>"
public data DBResult = DBRes([[DBValue]] table,
			     Int rows,
			     Int cols,
			     [String] colnames,
			     Ptr resptr);

"<summary>An incremental query result</summary>
<prose>DBIncResult holds data about the result of an incremental query, if the database-specific library supports these.</prose>
<list>
<item><code>rows</code> is the number of rows in the result set.</item>
<item><code>cols</code> is the number of columns in each row of the result set.</item>
<item><code>colnames</code> is a list of the names of the columns</item>
<item><code>resptr</code> is an internal pointer kept by the relevant database interface.</item>
</list>
<prose>An incremental query returns data only one row at a time, which may save memory on large result sets. Depending on the internal implementation in the database-specific library, the <code>rows</code> field may not contain useful information. Database-specific libraries should throw an Exception if an attempt is made to read more rows than exist.</prose>
<example>res = incExec(conn,\"SELECT id,name FROM People\");
row = getRow(res);
// row[0] = DBInt(id)
// row[1] = DBText(name)
</example>
<related><dataref>DBResult</dataref></related>
<related><dataref>DBValue</dataref></related>
<related><functionref>getRow</functionref></related>
<related><functionref>incDiscard</functionref></related>
<related><functionref>incExec</functionref></related>"
public data DBIncResult<a> = DBIncRes(DBHandle<a> con,
				      Int rows,
				      Int cols,
				      [String] colnames,
				      Ptr resptr);

"<summary>A prepared database statement</summary>
<prose>A prepared database statement, for use with the prepared statement execution functions.</prose>
<related><functionref>execPrepared</functionref></related>
<related><functionref>incExecPrepared</functionref></related>
<related><functionref>prepare</functionref></related>"
public data DBStatement<a> = DBStatement(DBHandle<a> con,
					 Ptr statement);

"<summary>A database handle</summary>
<prose>A database connection handle. The parameter is the specific handle type for a database, for example <code>PGConnection</code> for Postgres. The database-specific library will supply a function to create the handle.</prose>
<prose>Note that this data type is not declared <code>abstract</code> as the database connection libraries need to modify its data - users should <emphasis>not</emphasis> do so!</prose>
<related><functionref>MyDB::connect</functionref></related>
<related><functionref>PostgresDB::connect</functionref></related>
<related><functionref>SQLiteDB::connect</functionref></related>"
public data DBHandle<a> = DBh(a handle, 
			      DBResult(a,String) exec,
			      DBIncResult<a>(a,String,DBHandle<a>) incexec,
			      DBStatement<a>(DBHandle<a>,String) prep,
			      DBResult(DBStatement<a>,[Maybe<String>]) execp,
			      DBIncResult<a>(DBStatement<a>,[Maybe<String>]) incexecp,
			      [DBValue](DBIncResult<a>) getrow,
			      Void(DBIncResult<a>) incdiscard,
			      Void(a) close);

"<summary>Execute a query</summary>
<prose>Execute a query with the given connection handle.</prose>
<example>res = exec(conn,\"SELECT id,name FROM People\");
if (res.rows > 0) {
    for row in res.table {
        // row[0] = DBInt(id)
        // row[1] = DBText(name)
    }
}</example>
<prose>Non-SELECT queries will generally not return any result rows.</prose>
<related><dataref>DBHandle</dataref></related>
<related><dataref>DBResult</dataref></related>
<related><dataref>DBValue</dataref></related>
<related><functionref>incExec</functionref></related>"
public DBResult exec(DBHandle<a> con, String query) {
//    fn = con.exec; 
    return con.exec(con.handle, query);
}

"<summary>Execute an incremental query</summary>
<prose>Execute a query with the given connection handle for incremental retrieval of results. Results are then retrieved one row at a time using <functionref>getRow</functionref>. Once you have finished with the query you must call <functionref>incDiscard</functionref> to discard any remaining rows.</prose>
<example>res = incExec(conn,\"SELECT id,name FROM People\");
row = getRow(res);
// row[0] = DBInt(id)
// row[1] = DBText(name)
incDiscard(res);</example>
<related><dataref>DBHandle</dataref></related>
<related><dataref>DBIncResult</dataref></related>
<related><dataref>DBValue</dataref></related>
<related><functionref>exec</functionref></related>
<related><functionref>getRow</functionref></related>
<related><functionref>incDiscard</functionref></related>"
public DBIncResult<a> incExec(DBHandle<a> con, String query) {
//    fn = con.exec; 
    return con.incexec(con.handle, query, con);
}

"<summary>Retrieve a row from an incremental query</summary>
<prose>Retrieve a row from an incremental query. The row, once retrieved, is identical to one retrieved from a normal query. An Exception will be thrown if <code>getRow</code> is called on a result with no remaining rows.</prose>
<example>res = incExec(\"SELECT id,name FROM People\");
try {
    while(true) {
        row = getRow(res);
        processRow(row);
    }
} catch(TooManyRows) {
    incDiscard(res);
}</example>
<related><dataref>DBIncResult</dataref></related>
<related><dataref>DBValue</dataref></related>
<related><functionref>incDiscard</functionref></related>
<related><functionref>incExec</functionref></related>"
public [DBValue] getRow(DBIncResult<a> res) {
    return res.con.getrow(res);
}

"<summary>Discard an incremental query</summary>
<prose>Discard an incremental query once it is no longer needed. This may be once all rows have been read, or it may be sooner. Failing to use this function may cause memory usage to rise.</prose>
<related><dataref>DBIncResult</dataref></related>
<related><functionref>getRow</functionref></related>
<related><functionref>incExec</functionref></related>"
public Void incDiscard(DBIncResult<a> res) {
  fn = res.con.incdiscard;
  fn(res);
}

"<summary>Close a connection</summary>
<prose>Closes a database connection.</prose>
<related><dataref>DBHandle</dataref></related>"
public Void close(DBHandle<a> con) {
/// FIXME: Bah. The parser should be fixed to allow this to be done properly.
    fn = con.close;
    fn(con.handle);
}

"<summary>Get a column name</summary>
<prose>Get a column name from a result.</prose>
<example>res = exec(conn,\"SELECT id,name FROM People\");
col = column(res,1); // \"name\"</example>
<related><dataref>DBResult</dataref></related>"
public String column(DBResult res, Int col) {
    return (res.colnames[col]);
}

"<summary>Default function</summary>
<prose>Databases that do not support incremental retrieval should use this function as the <code>getrow</code> field of their <dataref>DBHandle</dataref>. It will throw the <exceptref>Builtins::Not_Implemented</exceptref> Exception if called.</prose>"
public [DBValue] defaultGetRow(DBIncResult<a> res) {
  throw(Not_Implemented);
}

"<summary>Default function</summary>
<prose>Databases that do not support incremental retrieval should use this function as the <code>incexec</code> field of their <dataref>DBHandle</dataref>. It will throw the <exceptref>Builtins::Not_Implemented</exceptref> Exception if called.</prose>"
public DBIncResult<a> defaultIncExec(a con, String query, DBHandle<a> cptr) {
  throw(Not_Implemented);
}

"<summary>Default function</summary>
<prose>Databases that do not support incremental retrieval should use this function as the <code>incdiscard</code> field of their <dataref>DBHandle</dataref>. It will throw the <exceptref>Builtins::Not_Implemented</exceptref> Exception if called.</prose>"
public Void defaultIncDiscard(DBIncResult<a> res) {
  throw(Not_Implemented);
}

"<summary>Default function</summary>
<prose>Databases that do not support prepared statements should use this function in the <code>prep</code> field of their <dataref>DBHandle</dataref>. It will throw the <exceptref>Builtins::Not_Implemented</exceptref> Exception if called.</prose>"
public DBStatement<a> defaultPrepare(DBHandle<a> con, String query) {
  throw(Not_Implemented);
}

"<summary>Default function</summary>
<prose>Databases that do not support prepared statements should use this function in the <code>execp</code> field of their <dataref>DBHandle</dataref>. It will throw the <exceptref>Builtins::Not_Implemented</exceptref> Exception if called.</prose>"
public DBResult defaultExecPrepared(DBStatement<a> con, [Maybe<String>] params) {
  throw(Not_Implemented);
}

"<summary>Default function</summary>
<prose>Databases that do not support prepared statements should use this function in the <code>incexecp</code> field of their <dataref>DBHandle</dataref>. It will throw the <exceptref>Builtins::Not_Implemented</exceptref> Exception if called.</prose>"
public DBIncResult<a> defaultIncExecPrepared(DBStatement<a> con, [Maybe<String>] params) {
  throw(Not_Implemented);
}

"<argument name='val'>A database result cell</argument>
<summary>Convert a database result to a String</summary>
<prose>Convert a database result to a String</prose>
<related><dataref>DBValue</dataref></related>
<related><functionref>bool</functionref></related>
<related><functionref>float</functionref></related>
<related><functionref>int</functionref></related>
<related><functionref>time</functionref></related>"
public String string(DBValue val) {
  case val of {
    DBText(t) -> return t;
    | DBInt(i) -> return String(i);
    | DBFloat(f) -> return String(f);
    | DBBool(b) -> return String(b);
//    | DBTime(e) -> return mySQLTime(localTime(e)); 
    | DBTime(e) -> return isoTime(e, [TwoFields]); 
  }
}

"<argument name='val'>A database result cell</argument>
<summary>Convert a database result to a Time</summary>
<prose>Convert a database result to a Time. The coercion from <code>DBBool</code> is undefined and throws an Exception.</prose>
<related><dataref>DBValue</dataref></related>
<related><dataref>Time::Time</dataref></related>
<related><functionref>bool</functionref></related>
<related><functionref>float</functionref></related>
<related><functionref>int</functionref></related>
<related><functionref>string</functionref></related>"
public Time time(DBValue val) {
  case val of {
    DBText(t) -> return strToTime(t);
    | DBInt(i) -> return localTime(i);
    | DBFloat(f) -> return localTime(Int(f));
    | DBBool(b) -> return throw(Not_Implemented); // doesn't make sense
    | DBTime(e) -> return localTime(e); 
  }
}

Time strToTime(String tstr) {
  return parseISO8601(tstr);
}

"<argument name='val'>A database result cell</argument>
<summary>Convert a database result to an Int</summary>
<prose>Convert a database result to an Int</prose>
<related><dataref>DBValue</dataref></related>
<related><functionref>bool</functionref></related>
<related><functionref>float</functionref></related>
<related><functionref>string</functionref></related>
<related><functionref>time</functionref></related>"
public Int int(DBValue val) {
  case val of {
    DBInt(i) -> return i;
    | DBText(t) -> return Int(t);
    | DBFloat(f) -> return Int(f);
    | DBBool(b) -> if(b) { return 1; } else { return 0; }
    | DBTime(e) -> return e; // a Unix Timestamp
  }
}

"<argument name='val'>A database result cell</argument>
<summary>Convert a database result to a Bool</summary>
<prose>Convert a database result to an Bool</prose>
<related><dataref>DBValue</dataref></related>
<related><functionref>float</functionref></related>
<related><functionref>int</functionref></related>
<related><functionref>string</functionref></related>
<related><functionref>time</functionref></related>"
public Bool bool(DBValue val) {
  case val of {
    DBInt(i) -> return i!=0;
    | DBText(t) -> return Int(t)!=0;
    | DBFloat(f) -> return f!=0.0;
    | DBBool(b) -> return b;
    | DBTime(e) -> return true; // doesn't make much sense
  }
}

"<argument name='val'>A database result cell</argument>
<summary>Convert a database result to a Float</summary>
<prose>Convert a database result to a Float</prose>
<related><dataref>DBValue</dataref></related>
<related><functionref>bool</functionref></related>
<related><functionref>int</functionref></related>
<related><functionref>string</functionref></related>
<related><functionref>time</functionref></related>"
public Float float(DBValue val) {
  case val of {
    DBInt(i) -> return Float(i);
    | DBText(t) -> return Float(t);
    | DBFloat(f) -> return f;
    | DBBool(b) -> if(b) { return 1.0; } else { return 0.0; }
    | DBTime(e) -> return Float(e); // not very meaningful
  }
}

"<argument name='con'>The database connection</argument>
<argument name='query'>The query template. This may contain replacement characters at some places (consult the manual for the specific database to find out what replacement syntax should be used) which will be replaced with variables in the <functionref>execPrepared</functionref> function.</argument>
<summary>Prepare a database query</summary>
<prose>Prepare a database query. Prepared queries can then be executed later, using a parameter-replacement method that generally avoids SQL injection attacks.</prose>
<example>st = prepare(con,\"SELECT * FROM Users WHERE username = $1\");</example>
<related><dataref>DBStatement</dataref></related>
<related><functionref>execPrepared</functionref></related>"
public DBStatement<a> prepare(DBHandle<a> con, String query) {
  return con.prep(con,query);
}

"<argument name='statement'>The prepared database statement</argument>
<argument name='params'>A list of parameters to be inserted into the query. An Exception may be thrown if the number of parameters does not match the expected number. <code>nothing</code> should be used for parameters that are to be replaced by <code>NULL</code>. If none of the parameters are NULL, then the other version of this function may be easier to use.</argument>
<summary>Execute a prepared statement</summary>
<prose>Execute a prepared statement, replacing parameters as specified. A single prepared statement may be used by multiple <code>execPrepared</code> functions, which increases efficiency.</prose>
<related><dataref>DBResult</dataref></related>
<related><dataref>DBStatement</dataref></related>
<related><functionref>exec</functionref></related>
<related><functionref index='1'>execPrepared</functionref></related>
<related><functionref>incExecPrepared</functionref></related>
<related><functionref>prepare</functionref></related>"
public DBResult execPrepared(DBStatement<a> statement, [Maybe<String>] params) {
  return statement.con.execp(statement,params);
}

"<argument name='statement'>The prepared database statement</argument>
<argument name='params'>A list of parameters to be inserted into the query. An Exception may be thrown if the number of parameters does not match the expected number. This version of the function may not be used to insert NULL values.</argument>
<summary>Execute a prepared statement</summary>
<prose>Execute a prepared statement, replacing parameters as specified. A single prepared statement may be used by multiple <code>execPrepared</code> functions, which increases efficiency.</prose>
<related><dataref>DBResult</dataref></related>
<related><dataref>DBStatement</dataref></related>
<related><functionref>exec</functionref></related>
<related><functionref>execPrepared</functionref></related>
<related><functionref>incExecPrepared</functionref></related>
<related><functionref>prepare</functionref></related>"
public DBResult execPrepared(DBStatement<a> statement, [String] params) {
  ps = createArray(size(params));
  for p in params {
    push(ps,just(p));
  }
  return statement.con.execp(statement,ps);
}


"<argument name='statement'>The prepared database statement</argument>
<argument name='params'>A list of parameters to be inserted into the query. An Exception may be thrown if the number of parameters does not match the expected number. <code>nothing</code> should be used for parameters that are to be replaced by <code>NULL</code>.</argument>
<summary>Incrementally execute a prepared statement</summary>
<prose>Incrementally execute a prepared statement, replacing parameters as specified. A single prepared statement may be used by multiple <code>incExecPrepared</code> functions, which increases efficiency.</prose>
<related><dataref>DBIncResult</dataref></related>
<related><dataref>DBStatement</dataref></related>
<related><functionref>exec</functionref></related>
<related><functionref>execPrepared</functionref></related>
<related><functionref>prepare</functionref></related>"
public DBIncResult<a> incExecPrepared(DBStatement<a> statement, [Maybe<String>] params) {
  return statement.con.incexecp(statement,params); 
}

"<argument name='val'>The String to escape</argument>
<summary>Escape a string to be used as part of a query</summary>
<prose>Escapes backslashes, apostrophes and quotes in a string to protect against SQL injection attacks. This function should be used only as a <emphasis>last resort</emphasis> - prepared statements using <functionref>prepare</functionref> and <functionref>execPrepared</functionref> are supported by all database libraries, and are far less prone to human error.</prose>"
public String escape(String val) {
  /*
    // If there's any double quotes or \' somebody is probably trying to
    // outwit us...
    replace(r"'+", "'", val, [Global]);
    replace(r"\\'", "'", val, [Global]);

    // Now we only have single quotes next.
    replace("'", "''", val, [Global]);
  */
    replace("\\\\","\\\\",val,[Global]);
    replace("'","\\'",val,[Global]);
    replace("\"","\\\"",val,[Global]);
    return val;
}