1
<?php
2
/**
3
* SQLite Data Access Object
4
* The username/password is not supported by the sqlite/sqlite3 package
5
*
6
* @author Zhou Yuan <yuanzhou19@gmail.com>
7
* @link http://www.infopotato.com/
8
* @copyright Copyright © 2009-2011 Zhou Yuan
9
* @license http://www.opensource.org/licenses/mit-license.php MIT Licence
10
*/
11
12
class SQLite_DAO extends Base_DAO {
13
/**
14
* Database connection handler
15
*
16
* @var resource
17
*/
18
public $dbh;
19
20
/**
21
* Constructor
22
*
23
* Allow the user to perform a connect at the same time as initialising the class
24
*/
25
public function __construct(array $config = NULL) {
26
// If there is no existing database connection then try to connect
27
if ( ! $this->dbh) {
28
// No username and password required
29
if ($config['path'] === '') {
30
halt('An Error Was Encountered', 'Require $dbpath to open an SQLite database', 'sys_error');
31
}
32
if ( ! $this->dbh = sqlite_open($config['path'])) {
33
halt('An Error Was Encountered', 'Could not connect: '.sqlite_error_string(sqlite_last_error($this->dbh)), 'sys_error');
34
}
35
}
36
}
37
38
/**
39
* Escapes special characters in a string for use in an SQL statement,
40
* taking into account the current charset of the connection
41
*/
42
public function escape($string) {
43
// Only quote and escape string
44
// is_string() will take '' will as string
45
if (is_string($string)) {
46
$string = sqlite_escape_string($string);
47
}
48
return $string;
49
}
50
51
/**
52
* USAGE: prepare( string $query [, array $params ] )
53
* The following directives can be used in the query format string:
54
* %d (decimal number)
55
* %s (string)
56
*
57
* Both %d and %s are to be left unquoted in the query string and they need an argument passed for them.
58
*/
59
public function prepare($query, array $params = NULL) {
60
if (count($params) > 0) {
61
foreach ($params as $k => $v) {
62
// Only quote and escape string
63
if (is_string($v)) {
64
if (isset($this->dbh)) {
65
$params[$k] = "'".sqlite_escape_string($v)."'";
66
} else {
67
// addslashes() should NOT be used to quote your strings for SQLite queries;
68
// it will lead to strange results when retrieving your data.
69
//$params[$k] = addslashes($v);
70
}
71
}
72
}
73
// vsprintf - replacing all %d and %s to parameters
74
$query = vsprintf($query, $params);
75
}
76
return $query;
77
}
78
79
/**
80
* Perform a unique query (multiple queries are not supported) and try to determine result value
81
*
82
* @return int Number of rows affected/selected
83
*/
84
public function exec_query($query) {
85
// For reg exp
86
$query = str_replace("/[\n\r]/", '', trim($query));
87
88
// Initialize return flag
89
$return_val = 0;
90
91
// Reset stored query result
92
$this->query_result = array();
93
94
// Executes a query against a given database and returns a result handle (resource)
95
$result = sqlite_query($this->dbh, $query);
96
97
// If there is an error then take note of it.
98
if (sqlite_last_error($this->dbh)) {
99
$err_msg = sqlite_error_string(sqlite_last_error($this->dbh));
100
halt('An Error Was Encountered', $err_msg, 'sys_error');
101
}
102
103
// Query was an insert, delete, drop, update, replace, alter
104
if (preg_match("/^(insert|delete|drop|update|replace|alter)\s+/i", $query)) {
105
$rows_affected = sqlite_changes($this->dbh);
106
107
// Take note of the last_insert_id
108
// REPLACE works exactly like INSERT, except that if an old row in the table has the same value
109
// as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
110
if (preg_match("/^(insert|replace)\s+/i", $query)) {
111
$this->last_insert_id = sqlite_last_insert_rowid($this->dbh);
112
}
113
114
// Return number fo rows affected
115
$return_val = $rows_affected;
116
} elseif (preg_match("/^(select|describe|desc|show|explain)\s+/i", $query)) {
117
// Store Query Results
118
$num_rows = 0;
119
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
120
// Store relults as an objects within main array
121
$obj = (object) $row; //convert to object
122
$this->query_result[$num_rows] = $obj;
123
$num_rows++;
124
}
125
126
// Log number of rows the query returned
127
$this->num_rows = $num_rows;
128
129
// Return number of rows selected
130
$return_val = $this->num_rows;
131
} elseif (preg_match("/^create\s+/i", $query)) {
132
// Table creation returns TRUE on success, or FALSE on error.
133
$return_val = $result;
134
}
135
136
return $return_val;
137
}
138
139
/**
140
* Begin Transaction using standard sql
141
*
142
* @return bool
143
*/
144
public function trans_begin() {
145
sqlite_query($this->dbh, 'BEGIN TRANSACTION');
146
}
147
148
/**
149
* Commit Transaction using standard sql
150
*
151
* @return bool
152
*/
153
public function trans_commit() {
154
sqlite_query($this->dbh, 'COMMIT');
155
}
156
157
/**
158
* Rollback Transaction using standard sql
159
*
160
* @return bool
161
*/
162
public function trans_rollback() {
163
sqlite_query($this->dbh, 'ROLLBACK');
164
}
165
166
}
167
168
// End of file: ./system/core/sqlite_dao.php
Page URI: http://www.infopotato.com/index.php/code/core/sqlite_dao/