blob: 3efa04f1d41ba7bfdcc240221f9b2b228f7fbc9b [file] [log] [blame]
admin3b60ae42006-09-25 23:26:03 +00001<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
2<html>
3<head>
4
5<title>Code Igniter User Guide</title>
6
7<style type='text/css' media='all'>@import url('../userguide.css');</style>
8<link rel='stylesheet' type='text/css' media='all' href='../userguide.css' />
9
admin17a890d2006-09-27 20:42:42 +000010<script type="text/javascript" src="../nav/nav.js"></script>
admin2296fc32006-09-27 21:07:02 +000011<script type="text/javascript" src="../nav/prototype.lite.js"></script>
admin17a890d2006-09-27 20:42:42 +000012<script type="text/javascript" src="../nav/moo.fx.js"></script>
admin3b60ae42006-09-25 23:26:03 +000013<script type="text/javascript">
14window.onload = function() {
15 myHeight = new fx.Height('nav', {duration: 400});
16 myHeight.hide();
17}
18</script>
19
20<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
21<meta http-equiv='expires' content='-1' />
22<meta http-equiv= 'pragma' content='no-cache' />
23<meta name='robots' content='all' />
24<meta name='author' content='Rick Ellis' />
25<meta name='description' content='Code Igniter User Guide' />
26
27</head>
28<body>
29
30<!-- START NAVIGATION -->
31<div id="nav"><div id="nav_inner"><script type="text/javascript">create_menu('../');</script></div></div>
32<div id="nav2"><a name="top"></a><a href="javascript:void(0);" onclick="myHeight.toggle();"><img src="../images/nav_toggle.jpg" width="153" height="44" border="0" title="Toggle Table of Contents" alt="Toggle Table of Contents" /></a></div>
33<div id="masthead">
34<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
35<tr>
admin41a16852006-09-26 18:17:19 +000036<td><h1>Code Igniter User Guide Version 1.5.0</h1></td>
admin3b60ae42006-09-25 23:26:03 +000037<td id="breadcrumb_right"><a href="../toc.html">Full Table of Contents</a></td>
38</tr>
39</table>
40</div>
41<!-- END NAVIGATION -->
42
43
44<!-- START BREADCRUMB -->
45<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
46<tr>
47<td id="breadcrumb">
48<a href="http://www.codeigniter.com/">Code Igniter Home</a> &nbsp;&#8250;&nbsp;
49<a href="../index.html">User Guide Home</a> &nbsp;&#8250;&nbsp;
50<a href="index.html">Database Library</a> &nbsp;&#8250;&nbsp;
admin24dd7e72006-10-01 19:02:29 +000051Database Utility Class
admin3b60ae42006-09-25 23:26:03 +000052</td>
53<td id="searchbox"><form method="get" action="http://www.google.com/search"><input type="hidden" name="as_sitesearch" id="as_sitesearch" value="www.codeigniter.com/user_guide/" />Search User Guide&nbsp; <input type="text" class="input" style="width:200px;" name="q" id="q" size="31" maxlength="255" value="" />&nbsp;<input type="submit" class="submit" name="sa" value="Go" /></form></td>
54</tr>
55</table>
56<!-- END BREADCRUMB -->
57
58
59<br clear="all" />
60
61
62<!-- START CONTENT -->
63<div id="content">
64
admin24dd7e72006-10-01 19:02:29 +000065<h1>Database Utility Class</h1>
admin3b60ae42006-09-25 23:26:03 +000066
admin24dd7e72006-10-01 19:02:29 +000067<p>The Database Utility Class contains functions that help you manage your database.</p>
admin3b60ae42006-09-25 23:26:03 +000068
admin32cb0e52006-09-30 20:36:59 +000069<h3>Table of Contents</h3>
70
71<ul>
admin24dd7e72006-10-01 19:02:29 +000072<li><a href="#init">Initializing the Utility Class</a></li>
admin32cb0e52006-09-30 20:36:59 +000073<li><a href="#create">Creating a Database</a></li>
74<li><a href="#drop">Dropping a Database</a></li>
75<li><a href="#list">Listing your Databases</a></li>
76<li><a href="#opttb">Optimizing your Tables</a></li>
77<li><a href="#repair">Repairing your Databases</a></li>
78<li><a href="#optdb">Optimizing your Database</a></li>
admin24dd7e72006-10-01 19:02:29 +000079<li><a href="#csv">CSV Files from a Database Result</a></li>
admin32cb0e52006-09-30 20:36:59 +000080<li><a href="#xml">XML Files from a Database Result</a></li>
81<li><a href="#backup">Backing up your Database</a></li>
82</ul>
83
84
85<a name="init"></a>
admin24dd7e72006-10-01 19:02:29 +000086<h2>Initializing the Utility Class</h2>
admin32cb0e52006-09-30 20:36:59 +000087
admin3b60ae42006-09-25 23:26:03 +000088<p class="important"><strong>Important:</strong>&nbsp; This class must be initialized independently since it is a separate class from the main Database class.
89More info below...</p>
90
admin3b60ae42006-09-25 23:26:03 +000091<p>To initialize this class please use the following code:</p>
92
93<code>$this->load->dbutil()</code>
94
95<p>You can also autoload this class from within your <dfn>config/autoload.php</dfn> file by specifying <kbd>dbutil</kbd> in the <samp>$autoload['libraries']</samp> array.</p>
96
97<p>Once initialized you will access the functions using the <dfn>$this->dbutil</dfn> object:</p>
98
99<code>$this->dbutil->some_function()</code>
100
101
102
admin32cb0e52006-09-30 20:36:59 +0000103<a name="create"></a>
admin3b60ae42006-09-25 23:26:03 +0000104<h2>$this->dbutil->create_database('db_name')</h2>
105
106<p>Permits you to create a database using the name specified in the first parameter. Returns TRUE/FALSE based on success or failure:</p>
107
108<code>if ($this->dbutil->create_database('my_db'))<br />
109{<br />
110&nbsp;&nbsp;&nbsp; echo 'Database created!';<br />
111}</code>
112
113
admin32cb0e52006-09-30 20:36:59 +0000114
115<a name="drop"></a>
admin3b60ae42006-09-25 23:26:03 +0000116<h2>$this->dbutil->drop_database('table_name')</h2>
117
118<p>Permits you to drop a database using the table name specified in the first parameter. Returns TRUE/FALSE based on success or failure:</p>
119
120<code>if ($this->dbutil->drop_database('my_db'))<br />
121{<br />
122&nbsp;&nbsp;&nbsp; echo 'Database deleted!';<br />
123}</code>
124
125
admin32cb0e52006-09-30 20:36:59 +0000126<a name="list"></a>
admin3b60ae42006-09-25 23:26:03 +0000127<h2>$this->dbutil->list_databases()</h2>
admin3b60ae42006-09-25 23:26:03 +0000128<p>Returns an array of database names:</p>
129
130
131<code>
132$dbs = $this->dbutil->list_databases();<br />
133<br />
134foreach($dbs as $db)<br />
135{<br />
136&nbsp;&nbsp;&nbsp; echo $db;<br />
137}</code>
138
139
admin32cb0e52006-09-30 20:36:59 +0000140<a name="opttb"></a>
admin3b60ae42006-09-25 23:26:03 +0000141<h2>$this->dbutil->optimize_table('table_name');</h2>
142
143<p>Permits you to optimize a table using the table name specified in the first parameter. Returns TRUE/FALSE based on success or failure:</p>
144
145<code>
146if ($this->dbutil->optimize_table('table_name'))<br />
147{<br />
148&nbsp;&nbsp;&nbsp; echo 'Success!'<br />
149}
150</code>
151
152<p><strong>Note:</strong> Not all database platforms support table optimization.</p>
153
154
admin32cb0e52006-09-30 20:36:59 +0000155<a name="repair"></a>
admin3b60ae42006-09-25 23:26:03 +0000156<h2>$this->dbutil->repair_table('table_name');</h2>
157
158<p>Permits you to repair a table using the table name specified in the first parameter. Returns TRUE/FALSE based on success or failure:</p>
159
160<code>
161if ($this->dbutil->optimize_table('table_name'))<br />
162{<br />
163&nbsp;&nbsp;&nbsp; echo 'Success!'<br />
164}
165</code>
166
167<p><strong>Note:</strong> Not all database platforms support table repairs.</p>
168
169
admin32cb0e52006-09-30 20:36:59 +0000170<a name="optdb"></a>
admine5bb9362006-09-27 00:31:22 +0000171<h2>$this->dbutil->optimize_database();</h2>
172
173<p>Permits you to optimize the database your DB class is currently connected to. Returns an array containing the returned status messages or FALSE on failure.</p>
174
175<code>
admin32cb0e52006-09-30 20:36:59 +0000176$result = $this->dbutil->optimize_databass();<br />
admine5bb9362006-09-27 00:31:22 +0000177<br />
178if ($result !== FALSE)<br />
179{<br />
180&nbsp;&nbsp;&nbsp; print_r($result);<br />
181}
182</code>
183
184<p><strong>Note:</strong> Not all database platforms support table optimization.</p>
admin3b60ae42006-09-25 23:26:03 +0000185
186
admin24dd7e72006-10-01 19:02:29 +0000187<a name="csv"></a>
188<h2>$this->dbutil->csv_from_result($db_result)</h2>
admin3ed8c512006-09-29 23:26:28 +0000189
admin24dd7e72006-10-01 19:02:29 +0000190<p>Permits you to generate a CSV file from a query result. The first parameter of the function must contain the result object from your query.
admin3ed8c512006-09-29 23:26:28 +0000191Example:</p>
192
193<code>
194$this->load->dbutil();<br />
195<br />
196$query = $this->db->query("SELECT * FROM mytable");<br />
197<br />
admin24dd7e72006-10-01 19:02:29 +0000198echo $this->dbutil->csv_from_result($query);
admin3ed8c512006-09-29 23:26:28 +0000199</code>
200
201<p>The second and third parameters allows you to
202set the delimiter and newline character. By default tabs are used as the delimiter and "\n" is used as a new line. Example:
203
204<code>
205$delimiter = ",";<br />
206$newline = "\r\n";<br />
207<br />
admin24dd7e72006-10-01 19:02:29 +0000208echo $this->dbutil->csv_from_result($query, $delimiter, $newline);
admin3ed8c512006-09-29 23:26:28 +0000209</code>
210
admin24dd7e72006-10-01 19:02:29 +0000211<p class="important"><strong>Important:</strong>&nbsp; This function will NOT write the CSV file for you. It simply creates the CSV layout.
admin3ed8c512006-09-29 23:26:28 +0000212If you need to write the file use the <a href="../helpers/file_helper.html">File Helper</a>.</p>
213
214
admin32cb0e52006-09-30 20:36:59 +0000215<a name="xml"></a>
admin3ed8c512006-09-29 23:26:28 +0000216<h2>$this->dbutil->xml_from_result($db_result)</h2>
217
218<p>Permits you to generate an XML file from a query result. The first parameter expects a query result object, the second
219may contain an optional array of config parameters. Example:</p>
220
221<code>
222$this->load->dbutil();<br />
223<br />
224$query = $this->db->query("SELECT * FROM mytable");<br />
225<br />
226$config = array (<br />
227&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'root'&nbsp;&nbsp;&nbsp; => 'root',<br />
228&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'element' => 'element', <br />
229&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'newline' => "\n", <br />
230&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';tab'&nbsp;&nbsp;&nbsp;&nbsp;=> "\t"<br />
231&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;);<br />
232<br />
admin24dd7e72006-10-01 19:02:29 +0000233echo $this->dbutil->xml_from_result($query, $config);
admin3ed8c512006-09-29 23:26:28 +0000234</code>
235
admin24dd7e72006-10-01 19:02:29 +0000236<p class="important"><strong>Important:</strong>&nbsp; This function will NOT write the XML file for you. It simply creates the XML layout.
admin3ed8c512006-09-29 23:26:28 +0000237If you need to write the file use the <a href="../helpers/file_helper.html">File Helper</a>.</p>
238
239
admin32cb0e52006-09-30 20:36:59 +0000240<a name="backup"></a>
241<h2>$this->dbutil->backup()</h2>
admin3dd978f2006-09-30 19:24:45 +0000242
admin32cb0e52006-09-30 20:36:59 +0000243<p>Permits you to backup your full database or individual tables. The backup data can be compressed in either Zip or Gzip format,
244and it can be downloaded to your desktop, archived to your
admin0e425542006-10-01 03:37:43 +0000245server, returned as a string, or simply displayed.</p>
246
247<p>Note: Due to the limited execution time and memory available to PHP, backing up very large
248databases may not be possible. If your database is very large you might need to backup directly from your SQL server
249via the command line, or have your server admin do it for you if you do not have root privileges.</p>
250
251<p>Usage example:</p>
admin3ed8c512006-09-29 23:26:28 +0000252
admin32cb0e52006-09-30 20:36:59 +0000253<code>$this->dbutil->backup();<br /><br />
254// When no parameters are passed the full database is backed up and downloaded to your desktop as a Gzip file.
255</code>
admin3ed8c512006-09-29 23:26:28 +0000256
admin32cb0e52006-09-30 20:36:59 +0000257<h3>Setting Backup Preferences</h3>
258
259<p>Preferences are set by submitting an array of values to the first parameter of the backup function. Example:</p>
260
261<code>$prefs = array(<br />
262&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'tables'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> array('table1', 'table2'),&nbsp;&nbsp;// Array of tables to backup.<br />
263&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'ignore'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> array(),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// List of tables to omit from the backup<br />
264&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'format'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> 'txt',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// gzip, zip, txt<br />
265&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'action'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> 'archive',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// download, archive, echo, return<br />
266&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'filename'&nbsp;&nbsp;&nbsp;&nbsp;=> 'sql_archive',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// File name - no file extension!<br />
267&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'filepath'&nbsp;&nbsp;&nbsp;&nbsp;=> '/path/to/folder/',&nbsp;&nbsp;// Path needed only when archiving to your server<br />
268&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'add_drop'&nbsp;&nbsp;&nbsp;&nbsp;=> TRUE,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Whether to add DROP TABLE statements to backup file<br />
269&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'add_insert'&nbsp;&nbsp;=> TRUE,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Whether to add INSERT data to backup file<br />
270&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'newline'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> "\n"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Newline character used in backup file<br />
271&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;);<br />
272<br />
273$this->dbutil->backup($prefs);
274</code>
275
admin0e425542006-10-01 03:37:43 +0000276
277<p class="important"><strong>VERY IMPORTANT:</strong> If you are using the <dfn>download</dfn> action to send the file to your desktop,
278DO NOT display any data in the controller in which you are triggering the backup. Since Code Igniter buffers all output, any data
279sent to the browser will be included in your export file. This issue ONLY applies to downloads. If you are using any other action
280you can display data in your controller.</p>
281
282
admin32cb0e52006-09-30 20:36:59 +0000283<h3>Description of Backup Preferences</h3>
284
285<table cellpadding="0" cellspacing="1" border="0" style="width:100%" class="tableborder">
286<tr>
287<th>Preference</th>
288<th>Default&nbsp;Value</th>
289<th>Options</th>
290<th>Description</th>
291</tr><tr>
292<td class="td"><strong>tables</strong></td><td class="td">empty array</td><td class="td">None</td><td class="td">An array of tables you would liked backed up. If left blank all tables will be exported.</td>
293</tr><tr>
294<td class="td"><strong>ignore</strong></td><td class="td">empty array</td><td class="td">None</td><td class="td">An array of tables you would liked the backup routine to ignore.</td>
295</tr><tr>
296<td class="td"><strong>format</strong></td><td class="td">gzip</td><td class="td">gzip, zip, txt</td><td class="td">The file format of the export file.</td>
297</tr><tr>
298<td class="td"><strong>action</strong></td><td class="td">download</td><td class="td">download, archive, echo, return</td><td class="td">What action you would like applied to the data. "download" sends it to your desktop, "archive" writes the file to your server, "return" returns the backup as a string, and "echo" simply echos it out for viewing.</td>
299</tr><tr>
300<td class="td"><strong>filename</strong></td><td class="td">the current date/time</td><td class="td">None</td><td class="td">The name of the export file. DO NOT include the file extension.</td>
301</tr><tr>
302<td class="td"><strong>filepath</strong></td><td class="td">empty string</td><td class="td">None</td><td class="td">The path to the directory where you would like the archived file written to. Used ONLY when the "archive" action is set.</td>
303</tr><tr>
304<td class="td"><strong>add_drop</strong></td><td class="td">TRUE</td><td class="td">TRUE/FALSE</td><td class="td">Whether to include DROP TABLE statements in your SQL export file.</td>
305</tr><tr>
306<td class="td"><strong>add_insert/strong></td><td class="td">TRUE</td><td class="td">TRUE/FALSE</td><td class="td">Whether to include INSERT statements in your SQL export file.</td>
307</tr><tr>
308<td class="td"><strong>newline/strong></td><td class="td">"\n"</td><td class="td">"\n", "\r", "\r\n"</td><td class="td">Type of newline to use in your SQL export file.</td>
309
310</tr>
311</table>
312
admin3b60ae42006-09-25 23:26:03 +0000313
314</div>
315<!-- END CONTENT -->
316
317
318<div id="footer">
319<p>
320Previous Topic:&nbsp;&nbsp;<a href="call_function.html">Custom Function Calls</a>
321&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
322<a href="#top">Top of Page</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
323<a href="../index.html">User Guide Home</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
admin3ed8c512006-09-29 23:26:28 +0000324Next Topic:&nbsp;&nbsp;<a href="caching.html">Database Caching Class</a>
admin3b60ae42006-09-25 23:26:03 +0000325<p>
326<p><a href="http://www.codeigniter.com">Code Igniter</a> &nbsp;&middot;&nbsp; Copyright &#169; 2006 &nbsp;&middot;&nbsp; <a href="http://www.pmachine.com">pMachine, Inc.</a></p>
327</div>
328
329</body>
330</html>