# tdbcmysql.tcl -- # # Class definitions and Tcl-level methods for the tdbc::mysql 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: tdbcmysql.tcl,v 1.47 2008/02/27 02:08:27 kennykb Exp $ # #------------------------------------------------------------------------------ package require tdbc ::namespace eval ::tdbc::mysql { namespace export connection datasources drivers } #------------------------------------------------------------------------------ # # tdbc::mysql::connection -- # # Class representing a connection to a database through MYSQL. # #------------------------------------------------------------------------------- ::oo::class create ::tdbc::mysql::connection { superclass ::tdbc::connection # The constructor is written in C. It takes alternating keywords # and values pairs as its argumenta. (See the manual page for the # available options.) variable foreignKeysStatement # The 'statementCreate' method delegates to the constructor of the # statement class forward statementCreate ::tdbc::mysql::statement create # The 'columns' method returns a dictionary describing the tables # in the database method columns {table {pattern %}} { # To return correct lengths of CHARACTER and BINARY columns, # we need to know the maximum lengths of characters in each # collation. We cache this information only once, on the first # call to 'columns'. if {[my NeedCollationInfo]} { my SetCollationInfo {*}[my allrows -as lists { SELECT coll.id, cs.maxlen FROM INFORMATION_SCHEMA.COLLATIONS coll, INFORMATION_SCHEMA.CHARACTER_SETS cs WHERE cs.CHARACTER_SET_NAME = coll.CHARACTER_SET_NAME ORDER BY coll.id DESC }] } return [my Columns $table $pattern] } # 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=$rest\\} } } # The 'init', 'begintransaction', 'commit, 'rollback', 'tables' # 'NeedCollationInfo', 'SetCollationInfo', and 'Columns' methods # are implemented in C. # The 'BuildForeignKeysStatements' method builds a SQL statement to # retrieve the foreign keys from a database. (It executes once the # first time the 'foreignKeys' method is executed, and retains the # prepared statements for reuse.) It is slightly nonstandard because # MYSQL doesn't name the PRIMARY constraints uniquely. method BuildForeignKeysStatement {} { foreach {exists1 clause1} { 0 {} 1 { AND fkc.REFERENCED_TABLE_NAME = :primary} } { foreach {exists2 clause2} { 0 {} 1 { AND fkc.TABLE_NAME = :foreign} } { set stmt [my prepare " SELECT rc.CONSTRAINT_SCHEMA AS \"foreignConstraintSchema\", rc.CONSTRAINT_NAME AS \"foreignConstraintName\", rc.UPDATE_RULE AS \"updateAction\", rc.DELETE_RULE AS \"deleteAction\", fkc.REFERENCED_TABLE_SCHEMA AS \"primarySchema\", fkc.REFERENCED_TABLE_NAME AS \"primaryTable\", fkc.REFERENCED_COLUMN_NAME AS \"primaryColumn\", fkc.TABLE_SCHEMA AS \"foreignSchema\", fkc.TABLE_NAME AS \"foreignTable\", fkc.COLUMN_NAME AS \"foreignColumn\", fkc.ORDINAL_POSITION AS \"ordinalPosition\" FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkc ON fkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND fkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA WHERE 1=1 $clause1 $clause2 "] dict set foreignKeysStatement $exists1 $exists2 $stmt } } } } #------------------------------------------------------------------------------ # # tdbc::mysql::statement -- # # The class 'tdbc::mysql::statement' models one statement against a # database accessed through an MYSQL connection # #------------------------------------------------------------------------------ ::oo::class create ::tdbc::mysql::statement { superclass ::tdbc::statement # The 'resultSetCreate' method forwards to the constructor of the # result set. forward resultSetCreate ::tdbc::mysql::resultset create # Methods implemented in C: # # constructor connection SQLCode # The constructor 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. # params # Returns descriptions of the parameters of a statement. # paramtype paramname ?direction? type ?precision ?scale?? # Declares the type of a parameter in the statement } #------------------------------------------------------------------------------ # # tdbc::mysql::resultset -- # # The class 'tdbc::mysql::resultset' models the result set that is # produced by executing a statement against an MYSQL database. # #------------------------------------------------------------------------------ ::oo::class create ::tdbc::mysql::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, or else # as a list of cells. # 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. }