Changeset 82

Timestamp:

01/30/08 16:41:05 (10 months ago)

Author:

aaronc542

Message:

Added SqlGenerator? helper methods. Added support for DBIException's to MysqlPreparedStatement?.

Files:

• trunk/dbi/Database.d (modified) (2 diffs)
• trunk/dbi/PreparedStatement.d (modified) (3 diffs)
• trunk/dbi/SqlGen.d (added)
• trunk/dbi/mysql/MysqlDatabase.d (modified) (3 diffs)
• trunk/dbi/mysql/MysqlPreparedStatement.d (modified) (7 diffs)

trunk/dbi/Database.d

@@ -8 +8 @@
-private static import tango.io.Stdout;
-private import dbi.DBIException, dbi.Result, dbi.Row, dbi.Statement;
+public import dbi.SqlGen;
-
-/**
@@ -205 +206 @@
-        return keywords;
-    }
+    
+    static this()
+    {
+        sqlGen = new SqlGenerator;
+    }
+    private static SqlGenerator sqlGen;
+    
+    SqlGenerator getSqlGenerator()
+    {
+        return sqlGen;
+    }
-}
-

trunk/dbi/PreparedStatement.d

@@ +1 @@
+/**
+ * Authors: The D DBI project
+ * Copyright: BSD license
+ */
-module dbi.PreparedStatement;
-
@@ -19 +23 @@
-    void setParamTypes(BindType[] paramTypes);
-    void setResultTypes(BindType[] resTypes);
-bool
-bool
+void
+void
-    bool fetch(void*[] bind);
-    void prefetchAll();
@@ -94 +98 @@
-    else return BindType.Null;
-}
+/+
+class Statement : IPreparedStatement {
+    /**
+     * Make a new instance of Statement.
+     *
+     * Params:
+     *  database = The database connection to use.
+     *  sql = The SQL code to prepare.
+     */
+    this (Database database, char[] sql) {
+        this.database = database;
+        this.sql = sql;
+    }
+
+    /**
+     * Execute a SQL statement that returns no results.
+     */
+    void execute () {
+        database.execute(getSql());
+    }
+    
+    uint getParamCount();
+    FieldInfo[] getResultMetadata();
+    void setParamTypes(BindType[] paramTypes);
+    void setResultTypes(BindType[] resTypes);
+    void execute();
+    void execute(void*[] bind);
+    bool fetch(void*[] bind);
+    void prefetchAll();
+    void reset();
+    ulong getLastInsertID();
+    char[] getLastErrorMsg();
+
+    /**
+     * Query the database.
+     *
+     * Returns:
+     *  A Result object with the queried information.
+     */
+    Result query () {
+        return database.query(getSql());
+    }
+
+    private:
+    Database database;
+    char[] sql;
+    char[][] binds;
+
+    /**
+     * Escape a SQL statement.
+     *
+     * Params:
+     *  string = An unescaped SQL statement.
+     *
+     * Returns:
+     *  The escaped form of string.
+     */
+    char[] escape (char[] string) {
+        if (database !is null) {
+            return database.escape(string);
+        } else {
+            char[] result;
+            size_t count = 0;
+
+            // Maximum length needed if every char is to be quoted
+            result.length = string.length * 2;
+
+            for (size_t i = 0; i < string.length; i++) {
+                switch (string[i]) {
+                    case '"':
+                    case '\'':
+                    case '\\':
+                        result[count++] = '\\';
+                        break;
+                    default:
+                        break;
+                }
+                result[count++] = string[i];
+            }
+
+            result.length = count;
+            return result;
+        }
+    }
+
+    /**
+     * Replace every "?" in the current SQL statement with its bound value.
+     *
+     * Returns:
+     *  The current SQL statement with all occurences of "?" replaced.
+     *
+     * Todo:
+     *  Raise an exception if binds.length != count(sql, "?")
+     */
+    char[] getSqlByQM () {
+        char[] result;
+        size_t i = 0, j = 0, count = 0;
+
+        // binds.length is for the '', only 1 because we replace the ? too
+        result.length = sql.length + binds.length;
+        for (i = 0; i < binds.length; i++) {
+            result.length = result.length + binds[i].length;
+        }
+
+        for (i = 0; i < sql.length; i++) {
+            if (sql[i] == '?') {
+                result[j++] = '\'';
+                result[j .. j + binds[count].length] = binds[count];
+                j += binds[count++].length;
+                result[j++] = '\'';
+            }
+            else {
+                result[j++] = sql[i];
+            }
+        }
+
+        sql = result;
+        return result;
+    }
+
+    /**
+     * Replace every ":name:" in the current SQL statement with its bound value.
+     *
+     * Returns:
+     *  The current SQL statement with all occurences of ":name:" replaced.
+     *
+     * Todo:
+     *  Raise an exception if binds.length != (count(sql, ":") * 2)
+     */
+    char[] getSqlByFN () {
+        char[] result = sql;
+        version (Phobos) {
+            ptrdiff_t beginIndex = 0, endIndex = 0;
+            while ((beginIndex = std.string.find(result, ":")) != -1 && (endIndex = std.string.find(result[beginIndex + 1 .. length], ":")) != -1) {
+                result = result[0 .. beginIndex] ~ "'" ~ getBoundValue(result[beginIndex + 1.. beginIndex + endIndex + 1]) ~ "'" ~ result[beginIndex + endIndex + 2 .. length];
+            }
+        } else {
+            uint beginIndex = 0, endIndex = 0;
+            while ((beginIndex = tango.text.Util.locate(result, ':')) != result.length && (endIndex = tango.text.Util.locate(result, ':', beginIndex + 1)) != result.length) {
+                result = result[0 .. beginIndex] ~ "'" ~ getBoundValue(result[beginIndex + 1 .. endIndex]) ~ "'" ~ result[endIndex + 1 .. length];
+            }
+        }
+        return result;
+    }
+
+    /**
+     * Replace all variables with their bound values.
+     *
+     * Returns:
+     *  The current SQL statement with all occurences of variables replaced.
+     */
+    char[] getSql () {
+        version (Phobos) {
+            if (std.string.find(sql, "?") != -1) {
+                return getSqlByQM();
+            } else if (std.string.find(sql, ":") != -1) {
+                return getSqlByFN();
+            } else {
+                return sql;
+            }
+        } else {
+            if (tango.text.Util.contains(sql, '?')) {
+                return getSqlByQM();
+            } else if (tango.text.Util.contains(sql, ':')) {
+                return getSqlByFN();
+            } else {
+                return sql;
+            }
+        }
+    }
+
+    /**
+     * Get the value bound to a ":name:".
+     *
+     * Params:
+     *  fn = The ":name:" to return the bound value of.
+     *
+     * Returns:
+     *  The bound value of fn.
+     *
+     * Throws:
+     *  DBIException if fn is not bound
+     */
+    char[] getBoundValue (char[] fn) {
+        for (size_t index = 0; index < bindsFNs.length; index++) {
+            if (bindsFNs[index] == fn) {
+                return binds[index];
+            }
+        }
+        throw new DBIException(fn ~ " is not bound in the Statement.");
+    }
+}
+
+debug(UnitTest) {
+unittest {
+    version (Phobos) {
+        void s1 (char[] s) {
+            std.stdio.writefln("%s", s);
+        }
+
+        void s2 (char[] s) {
+            std.stdio.writefln("   ...%s", s);
+        }
+    } else {
+        void s1 (char[] s) {
+            tango.io.Stdout.Stdout(s).newline();
+        }
+
+        void s2 (char[] s) {
+            tango.io.Stdout.Stdout("   ..." ~ s).newline();
+        }
+    }
+
+    s1("dbi.Statement:");
+    Statement stmt = new Statement(null, "SELECT * FROM people");
+    char[] resultingSql = "SELECT * FROM people WHERE id = '10' OR name LIKE 'John Mc\\'Donald'";
+
+    s2("escape");
+    assert (stmt.escape("John Mc'Donald") == "John Mc\\'Donald");
+
+    s2("simple sql");
+    stmt = new Statement(null, "SELECT * FROM people");
+    assert (stmt.getSql() == "SELECT * FROM people");
+
+    s2("bind by '?'");
+    stmt = new Statement(null, "SELECT * FROM people WHERE id = ? OR name LIKE ?");
+    stmt.bind(1, "10");
+    stmt.bind(2, "John Mc'Donald");
+    assert (stmt.getSql() == resultingSql);
+
+    /+
+    s2("bind by '?' sent to getSql via variable arguments");
+    stmt = new Statement("SELECT * FROM people WHERE id = ? OR name LIKE ?");
+    assert (stmt.getSql("10", "John Mc'Donald") == resultingSql);
+    +/
+
+    s2("bind by ':fieldname:'");
+    stmt = new Statement(null, "SELECT * FROM people WHERE id = :id: OR name LIKE :name:");
+    stmt.bind("id", "10");
+    stmt.bind("name", "John Mc'Donald");
+    assert (stmt.getBoundValue("name") == "John Mc\\'Donald");
+    assert (stmt.getSql() == resultingSql);
+}
+}
++/

trunk/dbi/mysql/MysqlDatabase.d

@@ -235 +235 @@
-                return mysql_insert_id(connection);
-        }
+        
+    static this()
+    {
+        mysqlSqlGen = new MysqlSqlGenerator;
+    }
+    private static MysqlSqlGenerator mysqlSqlGen;
+        
+    override SqlGenerator getSqlGenerator()
+    {
+        return mysqlSqlGen;
+    }
-
-    package:
-    MYSQL* connection;
+}
+
+class MysqlSqlGenerator : SqlGenerator
+{
+    override char getIdentifierQuoteCharacter()
+    {
+        return '`'; 
+    }
-}
-
@@ -267 +286 @@
-        tango.io.Stdout.Stdout("   ..." ~ s).newline();
-    }
-
+
-    s1("dbi.mysql.MysqlDatabase:");
-    MysqlDatabase db = new MysqlDatabase();
-
+/+
-    db.connect("dbname=test", "test", "test");
-
@@ -313 +332 @@
-    s2("close");
-    db.close();+/
-
-
-
-
+
+
+
+
+
+
+

trunk/dbi/mysql/MysqlPreparedStatement.d

@@ -18 +18 @@
-    
-import dbi.mysql.MysqlDatabase;
+import dbi.DBIException, dbi.mysql.MysqlError;
-version(Windows) {
-    private import dbi.mysql.imp_win;
@@ -30 +31 @@
-    this(MysqlDatabase db)
-    {
-
+DBI
-        mysql = db.connection;
-    }
@@ -45 +46 @@
-                log.error("Unable to create prepared statement: \"" ~ sql ~"\", errmsg: " ~ toDString(err));
-            }
-
+
+
+
-        }
-        return new MysqlPreparedStatement(stmt);
@@ -172 +175 @@
-    }
-    
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-        
-        uint len = bind.length;
@@ -221 +227 @@
-        
-        auto res = mysql_stmt_bind_param(stmt, paramBind.ptr);
-
+
+
+
-        res = mysql_stmt_execute(stmt);
-
+
+
+
-    }
-    
-    bool fetch(void*[] bind)
-    {
-Exception("Attempting to fetch from a statement without having set parameters types or ba
-
+DBIException("Attempting to fetch from a statement without having set parameters types or pas
+DBI
-        
-        uint len = bind.length;
@@ -607 +617 @@
-    bind[1] = &name;
-    bind[2] = &dateofbirth;
-assert(st.execute)
+st.execute
-    assert(st.fetch(bind));
-    Stdout.formatln("id:{},name:{},dateofbirth:{}",id,name,dateofbirth.ticks);
@@ -621 +631 @@
-    st2.setResultTypes(resTypes);
-    pBind ~= &usID;
-assert(st2.execute(pBind)
+st2.execute(pBind
-    assert(st2.fetch(bind));
-    Stdout.formatln("id:{},name:{},dateofbirth:{}",id,name,dateofbirth.ticks);