summaryrefslogtreecommitdiffstats
path: root/user_guide_src/source/database/db_driver_reference.rst
blob: 8a4f567848083300d84625837a73411eb7077ea9 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
###################
DB Driver Reference
###################

This is the platform-independent base DB implementation class.
This class will not be called directly. Rather, the adapter
class for the specific database will extend and instantiate it.

The how-to material for this has been split over several articles.
This article is intended to be a reference for them.

.. important:: Not all methods are supported by all database drivers,
	some of them may fail (and return FALSE) if the underlying
	driver does not support them.

.. php:class:: CI_DB_driver

	.. php:method:: initialize()

		:rtype:	void
		:throws:	RuntimeException	In case of failure

		Initialize database settings, establish a connection to
		the database.

	.. php:method:: db_connect($persistent = TRUE)

		:param	bool	$persistent: Whether to establish a persistent connection or a regular one
		:returns:	Database connection resource/object or FALSE on failure
		:rtype:	mixed

		Establish a connection with the database.

		.. note:: The returned value depends on the underlying
			driver in use. For example, a ``mysqli`` instance
			will be returned with the 'mysqli' driver.

	.. php:method:: db_pconnect()

		:returns:	Database connection resource/object or FALSE on failure
		:rtype:	mixed

		Establish a persistent connection with the database.

		.. note:: This method is just an alias for ``db_connect(TRUE)``.

	.. php:method:: reconnect()

		:returns:	TRUE on success, FALSE on failure
		:rtype:	bool

		Keep / reestablish the database connection if no queries
		have been sent for a length of time exceeding the
		server's idle timeout.

	.. php:method:: db_select([$database = ''])

		:param	string	$database: Database name
		:returns:	TRUE on success, FALSE on failure
		:rtype:	bool

		Select / switch the current database.

	.. php:method:: platform()

		:returns:	Platform name
		:rtype:	string

		The name of the platform in use (mysql, mssql, etc...).

	.. php:method:: version()

		:returns:	The version of the database being used
		:rtype:	string

		Database version number.

	.. php:method:: query($sql[, $binds = FALSE[, $return_object = NULL]])

		:param	string	$sql: The SQL statement to execute
		:param	array	$binds: An array of binding data
		:param	bool	$return_object: Whether to return a result object or not
		:returns:	TRUE for successful "write-type" queries, CI_DB_result instance (method chaining) on "query" success, FALSE on failure
		:rtype:	mixed

		Execute an SQL query.

		Accepts an SQL string as input and returns a result object
		upon successful execution of a "read" type query.

		Returns:

		   - Boolean TRUE upon successful execution of a "write type" queries
		   - Boolean FALSE upon failure
		   - ``CI_DB_result`` object for "read type" queries

		.. note: If 'db_debug' setting is set to TRUE, an error
			page will be displayed instead of returning FALSE
			on failures and script execution will stop.

	.. php:method:: simple_query($sql)

		:param	string	$sql: The SQL statement to execute
		:returns:	Whatever the underlying driver's "query" function returns
		:rtype:	mixed

		A simplified version of the ``query()`` method, appropriate
		for use when you don't need to get a result object or to
		just send a query to the database and not care for the result.

	.. php:method:: affected_rows()

		:returns:	Number of rows affected
		:rtype:	int

		Returns the number of rows *changed* by the last executed query.

		Useful for checking how much rows were created, updated or deleted
		during the last executed query.

	.. php:method:: trans_strict([$mode = TRUE])

		:param	bool	$mode: Strict mode flag
		:rtype:	void

		Enable/disable transaction "strict" mode.

		When strict mode is enabled, if you are running multiple
		groups of transactions and one group fails, all subsequent
		groups will be rolled back.

		If strict mode is disabled, each group is treated
		autonomously, meaning a failure of one group will not
		affect any others.

	.. php:method:: trans_off()

		:rtype:	void

		Disables transactions at run-time.

	.. php:method:: trans_start([$test_mode = FALSE])

		:param	bool	$test_mode: Test mode flag
		:returns:	TRUE on success, FALSE on failure
		:rtype:	bool

		Start a transaction.

	.. php:method:: trans_complete()

		:returns:	TRUE on success, FALSE on failure
		:rtype:	bool

		Complete Transaction.

	.. php:method:: trans_status()

		:returns:	TRUE if the transaction succeeded, FALSE if it failed
		:rtype:	bool

		Lets you retrieve the transaction status flag to
		determine if it has failed.
		
	.. php:method:: trans_active()

		:returns:	TRUE if a transaction is active, FALSE if not
		:rtype:	bool

		Determines if a transaction is currently active.

	.. php:method:: compile_binds($sql, $binds)

		:param	string	$sql: The SQL statement 
		:param	array	$binds: An array of binding data
		:returns:	The updated SQL statement
		:rtype:	string

		Compiles an SQL query with the bind values passed for it.

	.. php:method:: is_write_type($sql)

		:param	string	$sql: The SQL statement 
		:returns:	TRUE if the SQL statement is of "write type", FALSE if not
		:rtype:	bool

		Determines if a query is of a "write" type (such as
		INSERT, UPDATE, DELETE) or "read" type (i.e. SELECT).

	.. php:method:: elapsed_time([$decimals = 6])

		:param	int	$decimals: The number of decimal places
		:returns:	The aggregate query elapsed time, in microseconds
		:rtype:	string

		Calculate the aggregate query elapsed time.

	.. php:method:: total_queries()

		:returns:	The total number of queries executed
		:rtype:	int

		Returns the total number of queries that have been
		executed so far.

	.. php:method:: last_query()

		:returns:	The last query executed
		:rtype:	string

		Returns the last query that was executed.

	.. php:method:: escape($str)

		:param	mixed	$str: The value to escape, or an array of multiple ones
		:returns:	The escaped value(s)
		:rtype:	mixed

		Escapes input data based on type, including boolean and
		NULLs.

	.. php:method:: escape_str($str[, $like = FALSE])

		:param	mixed	$str: A string value or array of multiple ones
		:param	bool	$like: Whether or not the string will be used in a LIKE condition
		:returns:	The escaped string(s)
		:rtype:	mixed

		Escapes string values.

		.. warning:: The returned strings do NOT include quotes
			around them.

	.. php:method:: escape_like_str($str)

		:param	mixed	$str: A string value or array of multiple ones
		:returns:	The escaped string(s)
		:rtype:	mixed

		Escape LIKE strings.

		Similar to ``escape_str()``, but will also escape the ``%``
		and ``_`` wildcard characters, so that they don't cause
		false-positives in LIKE conditions.

		.. important:: The ``escape_like_str()`` method uses '!' (exclamation mark)
			to escape special characters for *LIKE* conditions. Because this
			method escapes partial strings that you would wrap in quotes
			yourself, it cannot automatically add the ``ESCAPE '!'``
			condition for you, and so you'll have to manually do that.


	.. php:method:: primary($table)

		:param	string	$table: Table name
		:returns:	The primary key name, FALSE if none
		:rtype:	string

		Retrieves the primary key of a table.

		.. note:: If the database platform does not support primary
			key detection, the first column name may be assumed
			as the primary key.

	.. php:method:: count_all([$table = ''])

		:param	string	$table: Table name
		:returns:	Row count for the specified table
		:rtype:	int

		Returns the total number of rows in a table, or 0 if no
		table was provided.

	.. php:method:: list_tables([$constrain_by_prefix = FALSE])

		:param	bool	$constrain_by_prefix: TRUE to match table names by the configured dbprefix
		:returns:	Array of table names or FALSE on failure
		:rtype:	array

		Gets a list of the tables in the current database.

	.. php:method:: table_exists($table_name)

		:param	string	$table_name: The table name
		:returns:	TRUE if that table exists, FALSE if not
		:rtype:	bool

		Determine if a particular table exists.

	.. php:method:: list_fields($table)

		:param	string	$table: The table name
		:returns:	Array of field names or FALSE on failure
		:rtype:	array

		Gets a list of the field names in a table.

	.. php:method:: field_exists($field_name, $table_name)

		:param	string	$table_name: The table name
		:param	string	$field_name: The field name
		:returns:	TRUE if that field exists in that table, FALSE if not
		:rtype:	bool

		Determine if a particular field exists.

	.. php:method:: field_data($table)

		:param	string	$table: The table name
		:returns:	Array of field data items or FALSE on failure
		:rtype:	array

		Gets a list containing field data about a table.

	.. php:method:: escape_identifiers($item, $split = TRUE)

		:param	mixed	$item: The item or array of items to escape
		:param	bool	$split: Whether to split identifiers when a dot is encountered
		:returns:	The input item(s), escaped
		:rtype:	mixed

		Escape SQL identifiers, such as column, table and names.

	.. php:method:: insert_string($table, $data)

		:param	string	$table: The target table
		:param	array	$data: An associative array of key/value pairs
		:returns:	The SQL INSERT statement, as a string
		:rtype:	string

		Generate an INSERT statement string.

	.. php:method:: update_string($table, $data, $where)

		:param	string	$table: The target table
		:param	array	$data: An associative array of key/value pairs
		:param	mixed	$where: The WHERE statement conditions
		:returns:	The SQL UPDATE statement, as a string
		:rtype:	string

		Generate an UPDATE statement string.

	.. php:method:: call_function($function)

		:param	string	$function: Function name
		:returns:	The function result
		:rtype:	string

		Runs a native PHP function , using a platform agnostic
		wrapper.

	.. php:method:: cache_set_path([$path = ''])

		:param	string	$path: Path to the cache directory
		:rtype:	void

		Sets the directory path to use for caching storage.

	.. php:method:: cache_on()

		:returns:	TRUE if caching is on, FALSE if not
		:rtype:	bool

		Enable database results caching.

	.. php:method:: cache_off()

		:returns:	TRUE if caching is on, FALSE if not
		:rtype:	bool

		Disable database results caching.

	.. php:method:: cache_delete([$segment_one = ''[, $segment_two = '']])

		:param	string	$segment_one: First URI segment
		:param	string	$segment_two: Second URI segment
		:returns:	TRUE on success, FALSE on failure
		:rtype:	bool

		Delete the cache files associated with a particular URI.

	.. php:method:: cache_delete_all()

		:returns:	TRUE on success, FALSE on failure
		:rtype:	bool

		Delete all cache files.

	.. php:method:: close()

		:rtype:	void

		Close the DB Connection.

	.. php:method:: display_error([$error = ''[, $swap = ''[, $native = FALSE]]])

		:param	string	$error: The error message
		:param	string	$swap: Any "swap" values
		:param	bool	$native: Whether to localize the message
		:rtype:	void

		:returns:	Displays the DB error screensends the application/views/errors/error_db.php template
                :rtype:	string

		Display an error message and stop script execution.

		The message is displayed using the
		*application/views/errors/error_db.php* template.

	.. php:method:: protect_identifiers($item[, $prefix_single = FALSE[, $protect_identifiers = NULL[, $field_exists = TRUE]]])

		:param	string	$item: The item to work with
		:param	bool	$prefix_single: Whether to apply the dbprefix even if the input item is a single identifier
		:param	bool	$protect_identifiers: Whether to quote identifiers
		:param	bool	$field_exists: Whether the supplied item contains a field name or not
		:returns:	The modified item
		:rtype:	string

		Takes a column or table name (optionally with an alias)
		and applies the configured *dbprefix* to it.

		Some logic is necessary in order to deal with
		column names that include the path. 

		Consider a query like this::

			SELECT * FROM hostname.database.table.column AS c FROM hostname.database.table

		Or a query with aliasing::

			SELECT m.member_id, m.member_name FROM members AS m

		Since the column name can include up to four segments
		(host, DB, table, column) or also have an alias prefix,
		we need to do a bit of work to figure this out and
		insert the table prefix (if it exists) in the proper
		position, and escape only the correct identifiers.

		This method is used extensively by the Query Builder class.