# tdbcodbc.tcl --
#
#	Class definitions and Tcl-level methods for the tdbc::odbc bridge.
#
# Copyright (c) 2008 by Kevin B. Kenny
# See the file "license.terms" for information on usage and redistribution
# of this file, and for a DISCLAIMER OF ALL WARRANTIES.
#
# RCS: @(#) $Id: tdbcodbc.tcl,v 1.47 2008/02/27 02:08:27 kennykb Exp $
#
#------------------------------------------------------------------------------

package require tdbc

::namespace eval ::tdbc::odbc {

    namespace export connection datasources drivers

    # Data types that are predefined in ODBC

    variable sqltypes [dict create \
			   1 char \
			   2 numeric \
			   3 decimal \
			   4 integer \
			   5 smallint \
			   6 float \
			   7 real \
			   8 double \
			   9 datetime \
			   12 varchar \
			   91 date \
			   92 time \
			   93 timestamp \
			   -1 longvarchar \
			   -2 binary \
			   -3 varbinary \
			   -4 longvarbinary \
			   -5 bigint \
			   -6 tinyint \
			   -7 bit \
			   -8 wchar \
			   -9 wvarchar \
			   -10 wlongvarchar \
			   -11 guid]
}

#------------------------------------------------------------------------------
#
# tdbc::odbc::connection --
#
#	Class representing a connection to a database through ODBC.
#
#-------------------------------------------------------------------------------

::oo::class create ::tdbc::odbc::connection {

    superclass ::tdbc::connection

    variable statementSeq typemap

    # The constructor is written in C. It takes the connection string
    # as its argument It sets up a namespace to hold the statements
    # associated with the connection, and then delegates to the 'init'
    # method (written in C) to do the actual work of attaching to the
    # database. When that comes back, it sets up a statement to query
    # the support types, makes a dictionary to enumerate them, and
    # calls back to set a flag if WVARCHAR is seen (If WVARCHAR is
    # seen, the database supports Unicode.)

    # The 'statementCreate' method forwards to the constructor of the
    # statement class

    forward statementCreate ::tdbc::odbc::statement create

    # The 'tables' method returns a dictionary describing the tables
    # in the database

    method tables {{pattern %}} {
	set stmt [::tdbc::odbc::tablesStatement create \
		      Stmt::[incr statementSeq] [self] $pattern]
       	set status [catch {
	    set retval {}
	    $stmt foreach -as dicts row {
		if {[dict exists $row TABLE_NAME]} {
		    dict set retval [dict get $row TABLE_NAME] $row
		}
	    }
	    set retval
	} result options]
	catch {rename $stmt {}}
	return -level 0 -options $options $result
    }

    # The 'columns' method returns a dictionary describing the tables
    # in the database

    method columns {table {pattern %}} {
	# Make sure that the type map is initialized
	my typemap

	# Query the columns from the database

	set stmt [::tdbc::odbc::columnsStatement create \
		      Stmt::[incr statementSeq] [self] $table $pattern]
	set status [catch {
	    set retval {}
	    $stmt foreach -as dicts origrow {

		# Map the type, precision, scale and nullable indicators
		# to tdbc's notation

		set row {}
		dict for {key value} $origrow {
		    dict set row [string tolower $key] $value
		}
		if {[dict exists $row column_name]} {
		    if {[dict exists $typemap \
			     [dict get $row data_type]]} {
			dict set row type \
			    [dict get $typemap \
				 [dict get $row data_type]]
		    } else {
			dict set row type [dict get $row type_name]
		    }
		    if {[dict exists $row column_size]} {
			dict set row precision \
			    [dict get $row column_size]
		    }
		    if {[dict exists $row decimal_digits]} {
			dict set row scale \
			    [dict get $row decimal_digits]
		    }
		    if {![dict exists $row nullable]} {
			dict set row nullable \
			    [expr {!![string trim [dict get $row is_nullable]]}]
		    }
		    dict set retval [dict get $row column_name] $row
		}
	    }
	    set retval
	} result options]
	catch {rename $stmt {}}
	return -level 0 -options $options $result
    }

    # The 'primarykeys' method returns a dictionary describing the primary
    # keys of a table

    method primarykeys {tableName} {
	set stmt [::tdbc::odbc::primarykeysStatement create \
		      Stmt::[incr statementSeq] [self] $tableName]
       	set status [catch {
	    set retval {}
	    $stmt foreach -as dicts row {
		foreach {odbcKey tdbcKey} {
		    TABLE_CAT		tableCatalog
		    TABLE_SCHEM		tableSchema
		    TABLE_NAME		tableName
		    COLUMN_NAME		columnName
		    KEY_SEQ		ordinalPosition
		    PK_NAME		constraintName
		} {
		    if {[dict exists $row $odbcKey]} {
			dict set row $tdbcKey [dict get $row $odbcKey]
			dict unset row $odbcKey
		    }
		}
		lappend retval $row
	    }
	    set retval
	} result options]
	catch {rename $stmt {}}
	return -level 0 -options $options $result
    }

    # The 'foreignkeys' method returns a dictionary describing the foreign
    # keys of a table

    method foreignkeys {args} {
	set stmt [::tdbc::odbc::foreignkeysStatement create \
		      Stmt::[incr statementSeq] [self] {*}$args]
       	set status [catch {
	    set fkseq 0
	    set retval {}
	    $stmt foreach -as dicts row {
		foreach {odbcKey tdbcKey} {
		    PKTABLE_CAT		primaryCatalog
		    PKTABLE_SCHEM	primarySchema
		    PKTABLE_NAME	primaryTable
		    PKCOLUMN_NAME	primaryColumn
		    FKTABLE_CAT		foreignCatalog
		    FKTABLE_SCHEM	foreignSchema
		    FKTABLE_NAME	foreignTable
		    FKCOLUMN_NAME	foreignColumn
		    UPDATE_RULE		updateRule
		    DELETE_RULE		deleteRule
		    DEFERRABILITY	deferrable
		    KEY_SEQ		ordinalPosition
		    FK_NAME		foreignConstraintName
		} {
		    if {[dict exists $row $odbcKey]} {
			dict set row $tdbcKey [dict get $row $odbcKey]
			dict unset row $odbcKey
		    }
		}
		# Horrible kludge: If the driver doesn't report FK_NAME,
		# make one up.
		if {![dict exists $row foreignConstraintName]} {
		    if {![dict exists $row ordinalPosition]
			|| [dict get $row ordinalPosition] == 1} {
			set fkname ?[dict get $row foreignTable]?[incr fkseq]
		    }
		    dict set row foreignConstraintName $fkname
		}
		lappend retval $row
	    }
	    set retval
	} result options]
	catch {rename $stmt {}}
	return -level 0 -options $options $result
    }

    # The 'evaldirect' evaluates driver-native SQL code without preparing it,
    # and returns a list of dicts (similar to '$connection allrows -as dicts').

    method evaldirect {sqlStatement} {
	set stmt [::tdbc::odbc::evaldirectStatement create \
		      Stmt::[incr statementSeq] [self] $sqlStatement]
       	set status [catch {
	    $stmt allrows -as dicts
	} result options]
	catch {rename $stmt {}}
	return -level 0 -options $options $result
    }

    # The 'prepareCall' method gives a portable interface to prepare
    # calls to stored procedures.  It delegates to 'prepare' to do the
    # actual work.

    method preparecall {call} {

	regexp {^[[:space:]]*(?:([A-Za-z_][A-Za-z_0-9]*)[[:space:]]*=)?(.*)} \
	    $call -> varName rest
	if {$varName eq {}} {
	    my prepare \\{CALL $rest\\}
	} else {
	    my prepare \\{:$varName=CALL $rest\\}
	}

	if 0 {
	# Kevin thinks this is going to be

	if {![regexp -expanded {
	    ^\s*				   # leading whitespace
	    (?::([[:alpha:]_][[:alnum:]_]*)\s*=\s*) # possible variable name
	    (?:(?:([[:alpha:]_][[:alnum:]_]*)\s*[.]\s*)?   # catalog
	       ([[:alpha:]_][[:alnum:]_]*)\s*[.]\s*)?      # schema
	    ([[:alpha:]_][[:alnum:]_]*)\s*		   # procedure
	    (.*)$					   # argument list
	} $call -> varName catalog schema procedure arglist]} {
	    return -code error \
		-errorCode [list TDBC \
				SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION \
				42000 ODBC -1] \
		"Syntax error in stored procedure call"
	} else {
	    my PrepareCall $varName $catalog $schema $procedure $arglist
	}

	# at least if making all parameters 'inout' doesn't work.

        }

    }

    # The 'typemap' method returns the type map

    method typemap {} {
	if {![info exists typemap]} {
	    set typemap $::tdbc::odbc::sqltypes
	    set typesStmt [tdbc::odbc::typesStatement new [self]]
	    $typesStmt foreach row {
		set typeNum [dict get $row DATA_TYPE]
		if {![dict exists $typemap $typeNum]} {
		    dict set typemap $typeNum [string tolower \
						   [dict get $row TYPE_NAME]]
		}
		switch -exact -- $typeNum {
		    -9 {
			[self] HasWvarchar 1
		    }
		    -5 {
			[self] HasBigint 1
		    }
		}
	    }
	    rename $typesStmt {}
	}
	return $typemap
    }

    # The 'begintransaction', 'commit' and 'rollback' methods are
    # implemented in C.

}

#-------------------------------------------------------------------------------
#
# tdbc::odbc::statement --
#
#	The class 'tdbc::odbc::statement' models one statement against a
#       database accessed through an ODBC connection
#
#-------------------------------------------------------------------------------

::oo::class create ::tdbc::odbc::statement {

    superclass ::tdbc::statement

    # The constructor is implemented in C. It accepts the handle to
    # the connection and the SQL code for the statement to prepare.
    # It creates a subordinate namespace to hold the statement's
    # active result sets, and then delegates to the 'init' method,
    # written in C, to do the actual work of preparing the statement.

    # The 'resultSetCreate' method forwards to the result set constructor

    forward resultSetCreate ::tdbc::odbc::resultset create

    # The 'params' method describes the parameters to the statement

    method params {} {
	set typemap [[my connection] typemap]
	set result {}
	foreach {name flags typeNum precision scale nullable} [my ParamList] {
	    set lst [dict create \
			 name $name \
			 direction [lindex {unknown in out inout} \
					[expr {($flags & 0x06) >> 1}]] \
			 type [dict get $typemap $typeNum] \
			 precision $precision \
			 scale $scale]
	    if {$nullable in {0 1}} {
		dict set list nullable $nullable
	    }
	    dict set result $name $lst
	}
	return $result
    }

    # Methods implemented in C:
    # init statement ?dictionary?
    #     Does the heavy lifting for the constructor
    # connection
    #	Returns the connection handle to which this statement belongs
    # paramtype paramname ?direction? type ?precision ?scale??
    #     Declares the type of a parameter in the statement

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::tablesStatement --
#
#	The class 'tdbc::odbc::tablesStatement' represents the special
#	statement that queries the tables in a database through an ODBC
#	connection.
#
#------------------------------------------------------------------------------

oo::class create ::tdbc::odbc::tablesStatement {

    superclass ::tdbc::statement

    # The constructor is written in C. It accepts the handle to the
    # connection and a pattern to match table names.  It works in all
    # ways like the constructor of the 'statement' class except that
    # its 'init' method sets up to enumerate tables and not run a SQL
    # query.

    # The 'resultSetCreate' method forwards to the result set constructor

    forward resultSetCreate ::tdbc::odbc::resultset create

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::columnsStatement --
#
#	The class 'tdbc::odbc::tablesStatement' represents the special
#	statement that queries the columns of a table or view
#	in a database through an ODBC connection.
#
#------------------------------------------------------------------------------

oo::class create ::tdbc::odbc::columnsStatement {

    superclass ::tdbc::statement

    # The constructor is written in C. It accepts the handle to the
    # connection, a table name, and a pattern to match column
    # names. It works in all ways like the constructor of the
    # 'statement' class except that its 'init' method sets up to
    # enumerate tables and not run a SQL query.

    # The 'resultSetCreate' class forwards to the constructor of the
    # result set

    forward resultSetCreate ::tdbc::odbc::resultset create

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::primarykeysStatement --
#
#	The class 'tdbc::odbc::primarykeysStatement' represents the special
#	statement that queries the primary keys on a table through an ODBC
#	connection.
#
#------------------------------------------------------------------------------

oo::class create ::tdbc::odbc::primarykeysStatement {

    superclass ::tdbc::statement

    # The constructor is written in C. It accepts the handle to the
    # connection and a table name.  It works in all
    # ways like the constructor of the 'statement' class except that
    # its 'init' method sets up to enumerate primary keys and not run a SQL
    # query.

    # The 'resultSetCreate' method forwards to the result set constructor

    forward resultSetCreate ::tdbc::odbc::resultset create

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::foreignkeysStatement --
#
#	The class 'tdbc::odbc::foreignkeysStatement' represents the special
#	statement that queries the foreign keys on a table through an ODBC
#	connection.
#
#------------------------------------------------------------------------------

oo::class create ::tdbc::odbc::foreignkeysStatement {

    superclass ::tdbc::statement

    # The constructor is written in C. It accepts the handle to the
    # connection and the -primary and -foreign options.  It works in all
    # ways like the constructor of the 'statement' class except that
    # its 'init' method sets up to enumerate foreign keys and not run a SQL
    # query.

    # The 'resultSetCreate' method forwards to the result set constructor

    forward resultSetCreate ::tdbc::odbc::resultset create

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::evaldirectStatement --
#
#	The class 'tdbc::odbc::evaldirectStatement' provides a mechanism to
#	execute driver-name SQL code through an ODBC connection.  The SQL code
#	is not prepared and no tokenization or variable substitution is done.
#
#------------------------------------------------------------------------------

oo::class create ::tdbc::odbc::evaldirectStatement {

    superclass ::tdbc::statement

    # The constructor is written in C. It accepts the handle to the
    # connection and a SQL statement.  It works in all
    # ways like the constructor of the 'statement' class except that
    # its 'init' method does not tokenize or prepare the SQL statement, and
    # sets up to run the SQL query without performing variable substitution.

    # The 'resultSetCreate' method forwards to the result set constructor

    forward resultSetCreate ::tdbc::odbc::resultset create

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::typesStatement --
#
#	The class 'tdbc::odbc::typesStatement' represents the special
#	statement that queries the types available in a database through
#	an ODBC connection.
#
#------------------------------------------------------------------------------


oo::class create ::tdbc::odbc::typesStatement {

    superclass ::tdbc::statement

    # The constructor is written in C. It accepts the handle to the
    # connection, and (optionally) a data type number. It works in all
    # ways like the constructor of the 'statement' class except that
    # its 'init' method sets up to enumerate types and not run a SQL
    # query.

    # The 'resultSetCreate' method forwards to the constructor of result sets

    forward resultSetCreate ::tdbc::odbc::resultset create

    # The C code contains a variant implementation of the 'init' method.

}

#------------------------------------------------------------------------------
#
# tdbc::odbc::resultset --
#
#	The class 'tdbc::odbc::resultset' models the result set that is
#	produced by executing a statement against an ODBC database.
#
#------------------------------------------------------------------------------

::oo::class create ::tdbc::odbc::resultset {

    superclass ::tdbc::resultset

    # Methods implemented in C include:

    # constructor statement ?dictionary?
    #     -- Executes the statement against the database, optionally providing
    #        a dictionary of substituted parameters (default is to get params
    #        from variables in the caller's scope).
    # columns
    #     -- Returns a list of the names of the columns in the result.
    # nextdict
    #     -- Stores the next row of the result set in the given variable in
    #        the caller's scope as a dictionary whose keys are
    #        column names and whose values are column values.
    # nextlist
    #     -- Stores the next row of the result set in the given variable in
    #        the caller's scope as a list of cells.
    # rowcount
    #     -- Returns a count of rows affected by the statement, or -1
    #        if the count of rows has not been determined.

}