jpayne@68: # tdbcmysql.tcl -- jpayne@68: # jpayne@68: # Class definitions and Tcl-level methods for the tdbc::mysql bridge. jpayne@68: # jpayne@68: # Copyright (c) 2008 by Kevin B. Kenny jpayne@68: # See the file "license.terms" for information on usage and redistribution jpayne@68: # of this file, and for a DISCLAIMER OF ALL WARRANTIES. jpayne@68: # jpayne@68: # RCS: @(#) $Id: tdbcmysql.tcl,v 1.47 2008/02/27 02:08:27 kennykb Exp $ jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: package require tdbc jpayne@68: jpayne@68: ::namespace eval ::tdbc::mysql { jpayne@68: jpayne@68: namespace export connection datasources drivers jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::mysql::connection -- jpayne@68: # jpayne@68: # Class representing a connection to a database through MYSQL. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------- jpayne@68: jpayne@68: ::oo::class create ::tdbc::mysql::connection { jpayne@68: jpayne@68: superclass ::tdbc::connection jpayne@68: jpayne@68: # The constructor is written in C. It takes alternating keywords jpayne@68: # and values pairs as its argumenta. (See the manual page for the jpayne@68: # available options.) jpayne@68: jpayne@68: variable foreignKeysStatement jpayne@68: jpayne@68: # The 'statementCreate' method delegates to the constructor of the jpayne@68: # statement class jpayne@68: jpayne@68: forward statementCreate ::tdbc::mysql::statement create jpayne@68: jpayne@68: # The 'columns' method returns a dictionary describing the tables jpayne@68: # in the database jpayne@68: jpayne@68: method columns {table {pattern %}} { jpayne@68: jpayne@68: # To return correct lengths of CHARACTER and BINARY columns, jpayne@68: # we need to know the maximum lengths of characters in each jpayne@68: # collation. We cache this information only once, on the first jpayne@68: # call to 'columns'. jpayne@68: jpayne@68: if {[my NeedCollationInfo]} { jpayne@68: my SetCollationInfo {*}[my allrows -as lists { jpayne@68: SELECT coll.id, cs.maxlen jpayne@68: FROM INFORMATION_SCHEMA.COLLATIONS coll, jpayne@68: INFORMATION_SCHEMA.CHARACTER_SETS cs jpayne@68: WHERE cs.CHARACTER_SET_NAME = coll.CHARACTER_SET_NAME jpayne@68: ORDER BY coll.id DESC jpayne@68: }] jpayne@68: } jpayne@68: jpayne@68: return [my Columns $table $pattern] jpayne@68: } jpayne@68: jpayne@68: # The 'preparecall' method gives a portable interface to prepare jpayne@68: # calls to stored procedures. It delegates to 'prepare' to do the jpayne@68: # actual work. jpayne@68: jpayne@68: method preparecall {call} { jpayne@68: regexp {^[[:space:]]*(?:([A-Za-z_][A-Za-z_0-9]*)[[:space:]]*=)?(.*)} \ jpayne@68: $call -> varName rest jpayne@68: if {$varName eq {}} { jpayne@68: my prepare "CALL $rest" jpayne@68: } else { jpayne@68: my prepare \\{:$varName=$rest\\} jpayne@68: } jpayne@68: } jpayne@68: jpayne@68: # The 'init', 'begintransaction', 'commit, 'rollback', 'tables' jpayne@68: # 'NeedCollationInfo', 'SetCollationInfo', and 'Columns' methods jpayne@68: # are implemented in C. jpayne@68: jpayne@68: # The 'BuildForeignKeysStatements' method builds a SQL statement to jpayne@68: # retrieve the foreign keys from a database. (It executes once the jpayne@68: # first time the 'foreignKeys' method is executed, and retains the jpayne@68: # prepared statements for reuse.) It is slightly nonstandard because jpayne@68: # MYSQL doesn't name the PRIMARY constraints uniquely. jpayne@68: jpayne@68: method BuildForeignKeysStatement {} { jpayne@68: jpayne@68: foreach {exists1 clause1} { jpayne@68: 0 {} jpayne@68: 1 { AND fkc.REFERENCED_TABLE_NAME = :primary} jpayne@68: } { jpayne@68: foreach {exists2 clause2} { jpayne@68: 0 {} jpayne@68: 1 { AND fkc.TABLE_NAME = :foreign} jpayne@68: } { jpayne@68: set stmt [my prepare " jpayne@68: SELECT rc.CONSTRAINT_SCHEMA AS \"foreignConstraintSchema\", jpayne@68: rc.CONSTRAINT_NAME AS \"foreignConstraintName\", jpayne@68: rc.UPDATE_RULE AS \"updateAction\", jpayne@68: rc.DELETE_RULE AS \"deleteAction\", jpayne@68: fkc.REFERENCED_TABLE_SCHEMA AS \"primarySchema\", jpayne@68: fkc.REFERENCED_TABLE_NAME AS \"primaryTable\", jpayne@68: fkc.REFERENCED_COLUMN_NAME AS \"primaryColumn\", jpayne@68: fkc.TABLE_SCHEMA AS \"foreignSchema\", jpayne@68: fkc.TABLE_NAME AS \"foreignTable\", jpayne@68: fkc.COLUMN_NAME AS \"foreignColumn\", jpayne@68: fkc.ORDINAL_POSITION AS \"ordinalPosition\" jpayne@68: FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc jpayne@68: INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkc jpayne@68: ON fkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME jpayne@68: AND fkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA jpayne@68: WHERE 1=1 jpayne@68: $clause1 jpayne@68: $clause2 jpayne@68: "] jpayne@68: dict set foreignKeysStatement $exists1 $exists2 $stmt jpayne@68: } jpayne@68: } jpayne@68: } jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::mysql::statement -- jpayne@68: # jpayne@68: # The class 'tdbc::mysql::statement' models one statement against a jpayne@68: # database accessed through an MYSQL connection jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: ::oo::class create ::tdbc::mysql::statement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the constructor of the jpayne@68: # result set. jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::mysql::resultset create jpayne@68: jpayne@68: # Methods implemented in C: jpayne@68: # jpayne@68: # constructor connection SQLCode jpayne@68: # The constructor accepts the handle to the connection and the SQL code jpayne@68: # for the statement to prepare. It creates a subordinate namespace to jpayne@68: # hold the statement's active result sets, and then delegates to the jpayne@68: # 'init' method, written in C, to do the actual work of preparing the jpayne@68: # statement. jpayne@68: # params jpayne@68: # Returns descriptions of the parameters of a statement. jpayne@68: # paramtype paramname ?direction? type ?precision ?scale?? jpayne@68: # Declares the type of a parameter in the statement jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::mysql::resultset -- jpayne@68: # jpayne@68: # The class 'tdbc::mysql::resultset' models the result set that is jpayne@68: # produced by executing a statement against an MYSQL database. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: ::oo::class create ::tdbc::mysql::resultset { jpayne@68: jpayne@68: superclass ::tdbc::resultset jpayne@68: jpayne@68: # Methods implemented in C include: jpayne@68: jpayne@68: # constructor statement ?dictionary? jpayne@68: # -- Executes the statement against the database, optionally providing jpayne@68: # a dictionary of substituted parameters (default is to get params jpayne@68: # from variables in the caller's scope). jpayne@68: # columns jpayne@68: # -- Returns a list of the names of the columns in the result. jpayne@68: # nextdict jpayne@68: # -- Stores the next row of the result set in the given variable in jpayne@68: # the caller's scope as a dictionary whose keys are jpayne@68: # column names and whose values are column values, or else jpayne@68: # as a list of cells. jpayne@68: # nextlist jpayne@68: # -- Stores the next row of the result set in the given variable in jpayne@68: # the caller's scope as a list of cells. jpayne@68: # rowcount jpayne@68: # -- Returns a count of rows affected by the statement, or -1 jpayne@68: # if the count of rows has not been determined. jpayne@68: jpayne@68: }