Gitiles
Code Review Sign In
www.giggi.me / code-igniter-v3-giggi / 32cb0e53301575b57c14760ea01649719a435c5e / . / user_guide / database / utilities.html
blob: 5e59358c541481d4a3a674d54c17939819414856 [file] [log] [blame]
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]1<!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 +0000[diff] [blame]10<script type="text/javascript" src="../nav/nav.js"></script>
admin2296fc32006-09-27 21:07:02 +0000[diff] [blame]11<script type="text/javascript" src="../nav/prototype.lite.js"></script>
admin17a890d2006-09-27 20:42:42 +0000[diff] [blame]12<script type="text/javascript" src="../nav/moo.fx.js"></script>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]13<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 +0000[diff] [blame]36<td><h1>Code Igniter User Guide Version 1.5.0</h1></td>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]37<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;
51Database Utilities Class
52</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
65<h1>Database Utilities Class</h1>
66
67<p>The Database Utilities Class contains functions that help you manage your database.</p>
68
admin32cb0e52006-09-30 20:36:59 +0000[diff] [blame^]69<h3>Table of Contents</h3>
70
71<ul>
72<li><a href="#init">Initializing the Utilities Class</a></li>
73<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>
79<li><a href="#cvs">CVS Files from a Database Result</a></li>
80<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>
86<h2>Initializing the Utilities Class</h2>
87
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]88<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 +0000[diff] [blame]91<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 +0000[diff] [blame^]103<a name="create"></a>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]104<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 +0000[diff] [blame^]114
115<a name="drop"></a>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]116<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 +0000[diff] [blame^]126<a name="list"></a>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]127<h2>$this->dbutil->list_databases()</h2>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]128<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 +0000[diff] [blame^]140<a name="opttb"></a>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]141<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 +0000[diff] [blame^]155<a name="repair"></a>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]156<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 +0000[diff] [blame^]170<a name="optdb"></a>
admine5bb9362006-09-27 00:31:22 +0000[diff] [blame]171<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 +0000[diff] [blame^]176$result = $this->dbutil->optimize_databass();<br />
admine5bb9362006-09-27 00:31:22 +0000[diff] [blame]177<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 +0000[diff] [blame]185
186
admin32cb0e52006-09-30 20:36:59 +0000[diff] [blame^]187<a name="cvs"></a>
admin3ed8c512006-09-29 23:26:28 +0000[diff] [blame]188<h2>$this->dbutil->cvs_from_result($db_result)</h2>
189
190<p>Permits you to generate a CVS file from a query result. The first parameter of the function must contain the result object from your query.
191Example:</p>
192
193<code>
194$this->load->dbutil();<br />
195<br />
196$query = $this->db->query("SELECT * FROM mytable");<br />
197<br />
198echo $this->dbutil->cvs_from_result($query);
199</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 />
208echo $this->dbutil->cvs_from_result($query, $delimiter, $newline);
209</code>
210
211<p class="important"><strong>Important:</strong>&nbsp; This function will NOT write the CVS file for you. It simply creates the CVS layout.
212If 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 +0000[diff] [blame^]215<a name="xml"></a>
admin3ed8c512006-09-29 23:26:28 +0000[diff] [blame]216<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 />
233echo $this->dbutil->cvs_from_result($query, $config);
234</code>
235
236<p class="important"><strong>Important:</strong>&nbsp; This function will NOT write the CVS file for you. It simply creates the CVS layout.
237If 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 +0000[diff] [blame^]240<a name="backup"></a>
241<h2>$this->dbutil->backup()</h2>
admin3dd978f2006-09-30 19:24:45 +0000[diff] [blame]242
admin32cb0e52006-09-30 20:36:59 +0000[diff] [blame^]243<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
245server, returned as a string, or simply displayed. Example:</p>
admin3ed8c512006-09-29 23:26:28 +0000[diff] [blame]246
admin32cb0e52006-09-30 20:36:59 +0000[diff] [blame^]247<code>$this->dbutil->backup();<br /><br />
248// When no parameters are passed the full database is backed up and downloaded to your desktop as a Gzip file.
249</code>
admin3ed8c512006-09-29 23:26:28 +0000[diff] [blame]250
admin32cb0e52006-09-30 20:36:59 +0000[diff] [blame^]251<h3>Setting Backup Preferences</h3>
252
253<p>Preferences are set by submitting an array of values to the first parameter of the backup function. Example:</p>
254
255<code>$prefs = array(<br />
256&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 />
257&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 />
258&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 />
259&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 />
260&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 />
261&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 />
262&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 />
263&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 />
264&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 />
265&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;);<br />
266<br />
267$this->dbutil->backup($prefs);
268</code>
269
270<h3>Description of Backup Preferences</h3>
271
272<table cellpadding="0" cellspacing="1" border="0" style="width:100%" class="tableborder">
273<tr>
274<th>Preference</th>
275<th>Default&nbsp;Value</th>
276<th>Options</th>
277<th>Description</th>
278</tr><tr>
279<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>
280</tr><tr>
281<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>
282</tr><tr>
283<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>
284</tr><tr>
285<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>
286</tr><tr>
287<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>
288</tr><tr>
289<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>
290</tr><tr>
291<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>
292</tr><tr>
293<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>
294</tr><tr>
295<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>
296
297</tr>
298</table>
299
300<p class="important"><strong>VERY IMPORTANT:</strong> If you are using the <dfn>download</dfn> action to send the file to your desktop,
301DO NOT display any data in the controller in which you are triggering the backup. Since Code Igniter buffers all output, any data
302sent to the browser will be included in your export file. This issue ONLY applies to downloads. If you are using any other action
303you can display data in your controller.</p>
admin3ed8c512006-09-29 23:26:28 +0000[diff] [blame]304
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]305
306</div>
307<!-- END CONTENT -->
308
309
310<div id="footer">
311<p>
312Previous Topic:&nbsp;&nbsp;<a href="call_function.html">Custom Function Calls</a>
313&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
314<a href="#top">Top of Page</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
315<a href="../index.html">User Guide Home</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
admin3ed8c512006-09-29 23:26:28 +0000[diff] [blame]316Next Topic:&nbsp;&nbsp;<a href="caching.html">Database Caching Class</a>
admin3b60ae42006-09-25 23:26:03 +0000[diff] [blame]317<p>
318<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>
319</div>
320
321</body>
322</html>