1 /** 2 * DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC. 3 * 4 * Source file ddbc/core.d. 5 * 6 * DDBC library attempts to provide implementation independent interface to different databases. 7 * 8 * Set of supported RDBMSs can be extended by writing Drivers for particular DBs. 9 * Currently it only includes MySQL Driver which uses patched version of MYSQLN (native D implementation of MySQL connector, written by Steve Teale) 10 * 11 * JDBC documentation can be found here: 12 * $(LINK http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html)$(BR) 13 * 14 * Limitations of current version: readonly unidirectional resultset, completely fetched into memory. 15 * 16 * Its primary objects are: 17 * $(UL 18 * $(LI Driver: $(UL $(LI Implements interface to particular RDBMS, used to create connections))) 19 * $(LI Connection: $(UL $(LI Connection to the server, and querying and setting of server parameters.))) 20 * $(LI Statement: Handling of general SQL requests/queries/commands, with principal methods: 21 * $(UL $(LI executeUpdate() - run query which doesn't return result set.) 22 * $(LI executeQuery() - execute query which returns ResultSet interface to access rows of result.) 23 * ) 24 * ) 25 * $(LI PreparedStatement: Handling of general SQL requests/queries/commands which having additional parameters, with principal methods: 26 * $(UL $(LI executeUpdate() - run query which doesn't return result set.) 27 * $(LI executeQuery() - execute query which returns ResultSet interface to access rows of result.) 28 * $(LI setXXX() - setter methods to bind parameters.) 29 * ) 30 * ) 31 * $(LI ResultSet: $(UL $(LI Get result of query row by row, accessing individual fields.))) 32 * ) 33 * 34 * You can find usage examples in unittest{} sections. 35 * 36 * Copyright: Copyright 2013 37 * License: $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0). 38 * Author: Vadim Lopatin 39 */ 40 module ddbc.core; 41 42 import std.exception; 43 import std.variant; 44 import std.datetime; 45 46 class SQLException : Exception { 47 this(string msg, string f = __FILE__, size_t l = __LINE__) { super(msg, f, l); } 48 this(Throwable causedBy, string f = __FILE__, size_t l = __LINE__) { super(causedBy.msg, f, l); } 49 } 50 51 /// JDBC java.sql.Types from http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html 52 enum SqlType { 53 //sometimes referred to as a type code, that identifies the generic SQL type ARRAY. 54 //ARRAY, 55 ///sometimes referred to as a type code, that identifies the generic SQL type BIGINT. 56 BIGINT, 57 ///sometimes referred to as a type code, that identifies the generic SQL type BINARY. 58 //BINARY, 59 //sometimes referred to as a type code, that identifies the generic SQL type BIT. 60 BIT, 61 ///sometimes referred to as a type code, that identifies the generic SQL type BLOB. 62 BLOB, 63 ///somtimes referred to as a type code, that identifies the generic SQL type BOOLEAN. 64 BOOLEAN, 65 ///sometimes referred to as a type code, that identifies the generic SQL type CHAR. 66 CHAR, 67 ///sometimes referred to as a type code, that identifies the generic SQL type CLOB. 68 CLOB, 69 //somtimes referred to as a type code, that identifies the generic SQL type DATALINK. 70 //DATALINK, 71 ///sometimes referred to as a type code, that identifies the generic SQL type DATE. 72 DATE, 73 ///sometimes referred to as a type code, that identifies the generic SQL type DATETIME. 74 DATETIME, 75 ///sometimes referred to as a type code, that identifies the generic SQL type DECIMAL. 76 DECIMAL, 77 //sometimes referred to as a type code, that identifies the generic SQL type DISTINCT. 78 //DISTINCT, 79 ///sometimes referred to as a type code, that identifies the generic SQL type DOUBLE. 80 DOUBLE, 81 ///sometimes referred to as a type code, that identifies the generic SQL type FLOAT. 82 FLOAT, 83 ///sometimes referred to as a type code, that identifies the generic SQL type INTEGER. 84 INTEGER, 85 //sometimes referred to as a type code, that identifies the generic SQL type JAVA_OBJECT. 86 //JAVA_OBJECT, 87 ///sometimes referred to as a type code, that identifies the generic SQL type LONGNVARCHAR. 88 LONGNVARCHAR, 89 ///sometimes referred to as a type code, that identifies the generic SQL type LONGVARBINARY. 90 LONGVARBINARY, 91 ///sometimes referred to as a type code, that identifies the generic SQL type LONGVARCHAR. 92 LONGVARCHAR, 93 ///sometimes referred to as a type code, that identifies the generic SQL type NCHAR 94 NCHAR, 95 ///sometimes referred to as a type code, that identifies the generic SQL type NCLOB. 96 NCLOB, 97 ///The constant in the Java programming language that identifies the generic SQL value NULL. 98 NULL, 99 ///sometimes referred to as a type code, that identifies the generic SQL type NUMERIC. 100 NUMERIC, 101 ///sometimes referred to as a type code, that identifies the generic SQL type NVARCHAR. 102 NVARCHAR, 103 ///indicates that the SQL type is database-specific and gets mapped to a object that can be accessed via the methods getObject and setObject. 104 OTHER, 105 //sometimes referred to as a type code, that identifies the generic SQL type REAL. 106 //REAL, 107 //sometimes referred to as a type code, that identifies the generic SQL type REF. 108 //REF, 109 //sometimes referred to as a type code, that identifies the generic SQL type ROWID 110 //ROWID, 111 ///sometimes referred to as a type code, that identifies the generic SQL type SMALLINT. 112 SMALLINT, 113 //sometimes referred to as a type code, that identifies the generic SQL type XML. 114 //SQLXML, 115 //sometimes referred to as a type code, that identifies the generic SQL type STRUCT. 116 //STRUCT, 117 ///sometimes referred to as a type code, that identifies the generic SQL type TIME. 118 TIME, 119 //sometimes referred to as a type code, that identifies the generic SQL type TIMESTAMP. 120 //TIMESTAMP, 121 ///sometimes referred to as a type code, that identifies the generic SQL type TINYINT. 122 TINYINT, 123 ///sometimes referred to as a type code, that identifies the generic SQL type VARBINARY. 124 VARBINARY, 125 ///sometimes referred to as a type code, that identifies the generic SQL type VARCHAR. 126 VARCHAR, 127 } 128 129 interface Connection { 130 /// Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. 131 void close(); 132 /// Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object. 133 void commit(); 134 /// Retrieves this Connection object's current catalog name. 135 string getCatalog(); 136 /// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work. 137 void setCatalog(string catalog); 138 /// Retrieves whether this Connection object has been closed. 139 bool isClosed(); 140 /// Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object. 141 void rollback(); 142 /// Retrieves the current auto-commit mode for this Connection object. 143 bool getAutoCommit(); 144 /// Sets this connection's auto-commit mode to the given state. 145 void setAutoCommit(bool autoCommit); 146 // statements 147 /// Creates a Statement object for sending SQL statements to the database. 148 Statement createStatement(); 149 /// Creates a PreparedStatement object for sending parameterized SQL statements to the database. 150 PreparedStatement prepareStatement(string query); 151 } 152 153 interface ResultSetMetaData { 154 //Returns the number of columns in this ResultSet object. 155 int getColumnCount(); 156 157 // Gets the designated column's table's catalog name. 158 string getCatalogName(int column); 159 // Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column. 160 //string getColumnClassName(int column); 161 // Indicates the designated column's normal maximum width in characters. 162 int getColumnDisplaySize(int column); 163 // Gets the designated column's suggested title for use in printouts and displays. 164 string getColumnLabel(int column); 165 // Get the designated column's name. 166 string getColumnName(int column); 167 // Retrieves the designated column's SQL type. 168 int getColumnType(int column); 169 // Retrieves the designated column's database-specific type name. 170 string getColumnTypeName(int column); 171 // Get the designated column's number of decimal digits. 172 int getPrecision(int column); 173 // Gets the designated column's number of digits to right of the decimal point. 174 int getScale(int column); 175 // Get the designated column's table's schema. 176 string getSchemaName(int column); 177 // Gets the designated column's table name. 178 string getTableName(int column); 179 // Indicates whether the designated column is automatically numbered, thus read-only. 180 bool isAutoIncrement(int column); 181 // Indicates whether a column's case matters. 182 bool isCaseSensitive(int column); 183 // Indicates whether the designated column is a cash value. 184 bool isCurrency(int column); 185 // Indicates whether a write on the designated column will definitely succeed. 186 bool isDefinitelyWritable(int column); 187 // Indicates the nullability of values in the designated column. 188 int isNullable(int column); 189 // Indicates whether the designated column is definitely not writable. 190 bool isReadOnly(int column); 191 // Indicates whether the designated column can be used in a where clause. 192 bool isSearchable(int column); 193 // Indicates whether values in the designated column are signed numbers. 194 bool isSigned(int column); 195 // Indicates whether it is possible for a write on the designated column to succeed. 196 bool isWritable(int column); 197 } 198 199 interface ParameterMetaData { 200 // Retrieves the fully-qualified name of the Java class whose instances should be passed to the method PreparedStatement.setObject. 201 //String getParameterClassName(int param); 202 /// Retrieves the number of parameters in the PreparedStatement object for which this ParameterMetaData object contains information. 203 int getParameterCount(); 204 /// Retrieves the designated parameter's mode. 205 int getParameterMode(int param); 206 /// Retrieves the designated parameter's SQL type. 207 int getParameterType(int param); 208 /// Retrieves the designated parameter's database-specific type name. 209 string getParameterTypeName(int param); 210 /// Retrieves the designated parameter's number of decimal digits. 211 int getPrecision(int param); 212 /// Retrieves the designated parameter's number of digits to right of the decimal point. 213 int getScale(int param); 214 /// Retrieves whether null values are allowed in the designated parameter. 215 int isNullable(int param); 216 /// Retrieves whether values for the designated parameter can be signed numbers. 217 bool isSigned(int param); 218 } 219 220 interface DataSetReader { 221 bool getBoolean(int columnIndex); 222 ubyte getUbyte(int columnIndex); 223 ubyte[] getUbytes(int columnIndex); 224 byte[] getBytes(int columnIndex); 225 byte getByte(int columnIndex); 226 short getShort(int columnIndex); 227 ushort getUshort(int columnIndex); 228 int getInt(int columnIndex); 229 uint getUint(int columnIndex); 230 long getLong(int columnIndex); 231 ulong getUlong(int columnIndex); 232 double getDouble(int columnIndex); 233 float getFloat(int columnIndex); 234 string getString(int columnIndex); 235 DateTime getDateTime(int columnIndex); 236 Date getDate(int columnIndex); 237 TimeOfDay getTime(int columnIndex); 238 Variant getVariant(int columnIndex); 239 bool isNull(int columnIndex); 240 bool wasNull(); 241 } 242 243 interface DataSetWriter { 244 void setFloat(int parameterIndex, float x); 245 void setDouble(int parameterIndex, double x); 246 void setBoolean(int parameterIndex, bool x); 247 void setLong(int parameterIndex, long x); 248 void setInt(int parameterIndex, int x); 249 void setShort(int parameterIndex, short x); 250 void setByte(int parameterIndex, byte x); 251 void setBytes(int parameterIndex, byte[] x); 252 void setUlong(int parameterIndex, ulong x); 253 void setUint(int parameterIndex, uint x); 254 void setUshort(int parameterIndex, ushort x); 255 void setUbyte(int parameterIndex, ubyte x); 256 void setUbytes(int parameterIndex, ubyte[] x); 257 void setString(int parameterIndex, string x); 258 void setDateTime(int parameterIndex, DateTime x); 259 void setDate(int parameterIndex, Date x); 260 void setTime(int parameterIndex, TimeOfDay x); 261 void setVariant(int columnIndex, Variant x); 262 263 void setNull(int parameterIndex); 264 void setNull(int parameterIndex, int sqlType); 265 } 266 267 interface ResultSet : DataSetReader { 268 void close(); 269 bool first(); 270 bool isFirst(); 271 bool isLast(); 272 bool next(); 273 274 //Retrieves the number, types and properties of this ResultSet object's columns 275 ResultSetMetaData getMetaData(); 276 //Retrieves the Statement object that produced this ResultSet object. 277 Statement getStatement(); 278 //Retrieves the current row number 279 int getRow(); 280 //Retrieves the fetch size for this ResultSet object. 281 int getFetchSize(); 282 283 // from DataSetReader 284 bool getBoolean(int columnIndex); 285 ubyte getUbyte(int columnIndex); 286 ubyte[] getUbytes(int columnIndex); 287 byte[] getBytes(int columnIndex); 288 byte getByte(int columnIndex); 289 short getShort(int columnIndex); 290 ushort getUshort(int columnIndex); 291 int getInt(int columnIndex); 292 uint getUint(int columnIndex); 293 long getLong(int columnIndex); 294 ulong getUlong(int columnIndex); 295 double getDouble(int columnIndex); 296 float getFloat(int columnIndex); 297 string getString(int columnIndex); 298 Variant getVariant(int columnIndex); 299 300 bool isNull(int columnIndex); 301 bool wasNull(); 302 303 // additional methods 304 int findColumn(string columnName); 305 bool getBoolean(string columnName); 306 ubyte getUbyte(string columnName); 307 ubyte[] getUbytes(string columnName); 308 byte[] getBytes(string columnName); 309 byte getByte(string columnName); 310 short getShort(string columnName); 311 ushort getUshort(string columnName); 312 int getInt(string columnName); 313 uint getUint(string columnName); 314 long getLong(string columnName); 315 ulong getUlong(string columnName); 316 double getDouble(string columnName); 317 float getFloat(string columnName); 318 string getString(string columnName); 319 DateTime getDateTime(int columnIndex); 320 Date getDate(int columnIndex); 321 TimeOfDay getTime(int columnIndex); 322 Variant getVariant(string columnName); 323 324 /// to iterate through all rows in result set 325 int opApply(int delegate(DataSetReader) dg); 326 327 } 328 329 interface Statement { 330 ResultSet executeQuery(string query); 331 int executeUpdate(string query); 332 int executeUpdate(string query, out Variant insertId); 333 void close(); 334 } 335 336 /// An object that represents a precompiled SQL statement. 337 interface PreparedStatement : Statement, DataSetWriter { 338 /// Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement. 339 int executeUpdate(); 340 /// Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement. 341 int executeUpdate(out Variant insertId); 342 /// Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query. 343 ResultSet executeQuery(); 344 345 /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. 346 ResultSetMetaData getMetaData(); 347 /// Retrieves the number, types and properties of this PreparedStatement object's parameters. 348 ParameterMetaData getParameterMetaData(); 349 /// Clears the current parameter values immediately. 350 void clearParameters(); 351 352 // from DataSetWriter 353 void setFloat(int parameterIndex, float x); 354 void setDouble(int parameterIndex, double x); 355 void setBoolean(int parameterIndex, bool x); 356 void setLong(int parameterIndex, long x); 357 void setInt(int parameterIndex, int x); 358 void setShort(int parameterIndex, short x); 359 void setByte(int parameterIndex, byte x); 360 void setBytes(int parameterIndex, byte[] x); 361 void setUlong(int parameterIndex, ulong x); 362 void setUint(int parameterIndex, uint x); 363 void setUshort(int parameterIndex, ushort x); 364 void setUbyte(int parameterIndex, ubyte x); 365 void setUbytes(int parameterIndex, ubyte[] x); 366 void setString(int parameterIndex, string x); 367 void setDateTime(int parameterIndex, DateTime x); 368 void setDate(int parameterIndex, Date x); 369 void setTime(int parameterIndex, TimeOfDay x); 370 void setVariant(int parameterIndex, Variant x); 371 372 void setNull(int parameterIndex); 373 void setNull(int parameterIndex, int sqlType); 374 } 375 376 interface Driver { 377 Connection connect(string url, string[string] params); 378 } 379 380 interface DataSource { 381 Connection getConnection(); 382 }