1/*
2 * Copyright (C) 2006, 2007, 2008 Apple Inc. All rights reserved.
3 *
4 * Redistribution and use in source and binary forms, with or without
5 * modification, are permitted provided that the following conditions
6 * are met:
7 * 1. Redistributions of source code must retain the above copyright
8 *    notice, this list of conditions and the following disclaimer.
9 * 2. Redistributions in binary form must reproduce the above copyright
10 *    notice, this list of conditions and the following disclaimer in the
11 *    documentation and/or other materials provided with the distribution.
12 *
13 * THIS SOFTWARE IS PROVIDED BY APPLE COMPUTER, INC. ``AS IS'' AND ANY
14 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
15 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
16 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL APPLE COMPUTER, INC. OR
17 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
18 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
19 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
20 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
21 * OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
22 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
23 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
24 */
25
26#include "config.h"
27#include "SQLiteStatement.h"
28
29#if ENABLE(DATABASE)
30
31#include "Logging.h"
32#include "SQLValue.h"
33#include <sqlite3.h>
34#include <wtf/Assertions.h>
35#include <wtf/text/CString.h>
36
37namespace WebCore {
38
39#if SQLITE_VERSION_NUMBER < 3003009
40
41// FIXME: This overload helps us compile with older versions of SQLite 3, but things like quotas will not work.
42static inline int sqlite3_prepare16_v2(sqlite3* db, const void* zSql, int nBytes, sqlite3_stmt** ppStmt, const void** pzTail)
43{
44    return sqlite3_prepare16(db, zSql, nBytes, ppStmt, pzTail);
45}
46
47#endif
48
49SQLiteStatement::SQLiteStatement(SQLiteDatabase& db, const String& sql)
50    : m_database(db)
51    , m_query(sql)
52    , m_statement(0)
53#ifndef NDEBUG
54    , m_isPrepared(false)
55#endif
56{
57}
58
59SQLiteStatement::~SQLiteStatement()
60{
61    finalize();
62}
63
64int SQLiteStatement::prepare()
65{
66    ASSERT(!m_isPrepared);
67
68    MutexLocker databaseLock(m_database.databaseMutex());
69    if (m_database.isInterrupted())
70        return SQLITE_INTERRUPT;
71
72    const void* tail = 0;
73    LOG(SQLDatabase, "SQL - prepare - %s", m_query.ascii().data());
74    String strippedQuery = m_query.stripWhiteSpace();
75    int error = sqlite3_prepare16_v2(m_database.sqlite3Handle(), strippedQuery.charactersWithNullTermination(), -1, &m_statement, &tail);
76
77    // Starting with version 3.6.16, sqlite has a patch (http://www.sqlite.org/src/ci/256ec3c6af)
78    // that should make sure sqlite3_prepare16_v2 doesn't return a SQLITE_SCHEMA error.
79    // If we're using an older sqlite version, try to emulate the patch.
80    if (error == SQLITE_SCHEMA) {
81      sqlite3_finalize(m_statement);
82      error = sqlite3_prepare16_v2(m_database.sqlite3Handle(), m_query.charactersWithNullTermination(), -1, &m_statement, &tail);
83    }
84
85    if (error != SQLITE_OK)
86        LOG(SQLDatabase, "sqlite3_prepare16 failed (%i)\n%s\n%s", error, m_query.ascii().data(), sqlite3_errmsg(m_database.sqlite3Handle()));
87    const UChar* ch = static_cast<const UChar*>(tail);
88    if (ch && *ch)
89        error = SQLITE_ERROR;
90#ifndef NDEBUG
91    m_isPrepared = error == SQLITE_OK;
92#endif
93    return error;
94}
95
96int SQLiteStatement::step()
97{
98    ASSERT(m_isPrepared);
99
100    MutexLocker databaseLock(m_database.databaseMutex());
101    if (m_database.isInterrupted())
102        return SQLITE_INTERRUPT;
103
104    if (!m_statement)
105        return SQLITE_OK;
106    LOG(SQLDatabase, "SQL - step - %s", m_query.ascii().data());
107    int error = sqlite3_step(m_statement);
108    if (error != SQLITE_DONE && error != SQLITE_ROW) {
109        LOG(SQLDatabase, "sqlite3_step failed (%i)\nQuery - %s\nError - %s",
110            error, m_query.ascii().data(), sqlite3_errmsg(m_database.sqlite3Handle()));
111    }
112
113    return error;
114}
115
116int SQLiteStatement::finalize()
117{
118#ifndef NDEBUG
119    m_isPrepared = false;
120#endif
121    if (!m_statement)
122        return SQLITE_OK;
123    LOG(SQLDatabase, "SQL - finalize - %s", m_query.ascii().data());
124    int result = sqlite3_finalize(m_statement);
125    m_statement = 0;
126    return result;
127}
128
129int SQLiteStatement::reset()
130{
131    ASSERT(m_isPrepared);
132    if (!m_statement)
133        return SQLITE_OK;
134    LOG(SQLDatabase, "SQL - reset - %s", m_query.ascii().data());
135    return sqlite3_reset(m_statement);
136}
137
138bool SQLiteStatement::executeCommand()
139{
140    if (!m_statement && prepare() != SQLITE_OK)
141        return false;
142    ASSERT(m_isPrepared);
143    if (step() != SQLITE_DONE) {
144        finalize();
145        return false;
146    }
147    finalize();
148    return true;
149}
150
151bool SQLiteStatement::returnsAtLeastOneResult()
152{
153    if (!m_statement && prepare() != SQLITE_OK)
154        return false;
155    ASSERT(m_isPrepared);
156    if (step() != SQLITE_ROW) {
157        finalize();
158        return false;
159    }
160    finalize();
161    return true;
162
163}
164
165int SQLiteStatement::bindBlob(int index, const void* blob, int size)
166{
167    ASSERT(m_isPrepared);
168    ASSERT(index > 0);
169    ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
170    ASSERT(blob);
171    ASSERT(size >= 0);
172
173    if (!m_statement)
174        return SQLITE_ERROR;
175
176    return sqlite3_bind_blob(m_statement, index, blob, size, SQLITE_TRANSIENT);
177}
178
179int SQLiteStatement::bindBlob(int index, const String& text)
180{
181    // String::characters() returns 0 for the empty string, which SQLite
182    // treats as a null, so we supply a non-null pointer for that case.
183    UChar anyCharacter = 0;
184    const UChar* characters;
185    if (text.isEmpty() && !text.isNull())
186        characters = &anyCharacter;
187    else
188        characters = text.characters();
189
190    return bindBlob(index, characters, text.length() * sizeof(UChar));
191}
192
193int SQLiteStatement::bindText(int index, const String& text)
194{
195    ASSERT(m_isPrepared);
196    ASSERT(index > 0);
197    ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
198
199    // String::characters() returns 0 for the empty string, which SQLite
200    // treats as a null, so we supply a non-null pointer for that case.
201    UChar anyCharacter = 0;
202    const UChar* characters;
203    if (text.isEmpty() && !text.isNull())
204        characters = &anyCharacter;
205    else
206        characters = text.characters();
207
208    return sqlite3_bind_text16(m_statement, index, characters, sizeof(UChar) * text.length(), SQLITE_TRANSIENT);
209}
210
211int SQLiteStatement::bindInt(int index, int integer)
212{
213    ASSERT(m_isPrepared);
214    ASSERT(index > 0);
215    ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
216
217    return sqlite3_bind_int(m_statement, index, integer);
218}
219
220int SQLiteStatement::bindInt64(int index, int64_t integer)
221{
222    ASSERT(m_isPrepared);
223    ASSERT(index > 0);
224    ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
225
226    return sqlite3_bind_int64(m_statement, index, integer);
227}
228
229int SQLiteStatement::bindDouble(int index, double number)
230{
231    ASSERT(m_isPrepared);
232    ASSERT(index > 0);
233    ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
234
235    return sqlite3_bind_double(m_statement, index, number);
236}
237
238int SQLiteStatement::bindNull(int index)
239{
240    ASSERT(m_isPrepared);
241    ASSERT(index > 0);
242    ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
243
244    return sqlite3_bind_null(m_statement, index);
245}
246
247int SQLiteStatement::bindValue(int index, const SQLValue& value)
248{
249    switch (value.type()) {
250        case SQLValue::StringValue:
251            return bindText(index, value.string());
252        case SQLValue::NumberValue:
253            return bindDouble(index, value.number());
254        case SQLValue::NullValue:
255            return bindNull(index);
256    }
257
258    ASSERT_NOT_REACHED();
259    return SQLITE_ERROR;
260}
261
262unsigned SQLiteStatement::bindParameterCount() const
263{
264    ASSERT(m_isPrepared);
265    if (!m_statement)
266        return 0;
267    return sqlite3_bind_parameter_count(m_statement);
268}
269
270int SQLiteStatement::columnCount()
271{
272    ASSERT(m_isPrepared);
273    if (!m_statement)
274        return 0;
275    return sqlite3_data_count(m_statement);
276}
277
278bool SQLiteStatement::isColumnNull(int col)
279{
280    ASSERT(col >= 0);
281    if (!m_statement)
282        if (prepareAndStep() != SQLITE_ROW)
283            return false;
284    if (columnCount() <= col)
285        return false;
286
287    return sqlite3_column_type(m_statement, col) == SQLITE_NULL;
288}
289
290String SQLiteStatement::getColumnName(int col)
291{
292    ASSERT(col >= 0);
293    if (!m_statement)
294        if (prepareAndStep() != SQLITE_ROW)
295            return String();
296    if (columnCount() <= col)
297        return String();
298    return String(reinterpret_cast<const UChar*>(sqlite3_column_name16(m_statement, col)));
299}
300
301SQLValue SQLiteStatement::getColumnValue(int col)
302{
303    ASSERT(col >= 0);
304    if (!m_statement)
305        if (prepareAndStep() != SQLITE_ROW)
306            return SQLValue();
307    if (columnCount() <= col)
308        return SQLValue();
309
310    // SQLite is typed per value. optional column types are
311    // "(mostly) ignored"
312    sqlite3_value* value = sqlite3_column_value(m_statement, col);
313    switch (sqlite3_value_type(value)) {
314        case SQLITE_INTEGER:    // SQLValue and JS don't represent integers, so use FLOAT -case
315        case SQLITE_FLOAT:
316            return SQLValue(sqlite3_value_double(value));
317        case SQLITE_BLOB:       // SQLValue and JS don't represent blobs, so use TEXT -case
318        case SQLITE_TEXT:
319            return SQLValue(String(reinterpret_cast<const UChar*>(sqlite3_value_text16(value))));
320        case SQLITE_NULL:
321            return SQLValue();
322        default:
323            break;
324    }
325    ASSERT_NOT_REACHED();
326    return SQLValue();
327}
328
329String SQLiteStatement::getColumnText(int col)
330{
331    ASSERT(col >= 0);
332    if (!m_statement)
333        if (prepareAndStep() != SQLITE_ROW)
334            return String();
335    if (columnCount() <= col)
336        return String();
337    return String(reinterpret_cast<const UChar*>(sqlite3_column_text16(m_statement, col)));
338}
339
340double SQLiteStatement::getColumnDouble(int col)
341{
342    ASSERT(col >= 0);
343    if (!m_statement)
344        if (prepareAndStep() != SQLITE_ROW)
345            return 0.0;
346    if (columnCount() <= col)
347        return 0.0;
348    return sqlite3_column_double(m_statement, col);
349}
350
351int SQLiteStatement::getColumnInt(int col)
352{
353    ASSERT(col >= 0);
354    if (!m_statement)
355        if (prepareAndStep() != SQLITE_ROW)
356            return 0;
357    if (columnCount() <= col)
358        return 0;
359    return sqlite3_column_int(m_statement, col);
360}
361
362int64_t SQLiteStatement::getColumnInt64(int col)
363{
364    ASSERT(col >= 0);
365    if (!m_statement)
366        if (prepareAndStep() != SQLITE_ROW)
367            return 0;
368    if (columnCount() <= col)
369        return 0;
370    return sqlite3_column_int64(m_statement, col);
371}
372
373String SQLiteStatement::getColumnBlobAsString(int col)
374{
375    ASSERT(col >= 0);
376
377    if (!m_statement && prepareAndStep() != SQLITE_ROW)
378        return String();
379
380    if (columnCount() <= col)
381        return String();
382
383    const void* blob = sqlite3_column_blob(m_statement, col);
384    if (!blob)
385        return String();
386
387    int size = sqlite3_column_bytes(m_statement, col);
388    if (size < 0)
389        return String();
390
391    ASSERT(!(size % sizeof(UChar)));
392    return String(static_cast<const UChar*>(blob), size / sizeof(UChar));
393}
394
395void SQLiteStatement::getColumnBlobAsVector(int col, Vector<char>& result)
396{
397    ASSERT(col >= 0);
398
399    if (!m_statement && prepareAndStep() != SQLITE_ROW) {
400        result.clear();
401        return;
402    }
403
404    if (columnCount() <= col) {
405        result.clear();
406        return;
407    }
408
409    const void* blob = sqlite3_column_blob(m_statement, col);
410    if (!blob) {
411        result.clear();
412        return;
413    }
414
415    int size = sqlite3_column_bytes(m_statement, col);
416    result.resize((size_t)size);
417    for (int i = 0; i < size; ++i)
418        result[i] = (static_cast<const unsigned char*>(blob))[i];
419}
420
421const void* SQLiteStatement::getColumnBlob(int col, int& size)
422{
423    ASSERT(col >= 0);
424
425    size = 0;
426
427    if (finalize() != SQLITE_OK)
428        LOG(SQLDatabase, "Finalize failed");
429    if (prepare() != SQLITE_OK) {
430        LOG(SQLDatabase, "Prepare failed");
431        return 0;
432    }
433    if (step() != SQLITE_ROW) {
434        LOG(SQLDatabase, "Step wasn't a row");
435        return 0;
436    }
437
438    if (columnCount() <= col)
439        return 0;
440
441    const void* blob = sqlite3_column_blob(m_statement, col);
442    if (!blob)
443        return 0;
444
445    size = sqlite3_column_bytes(m_statement, col);
446    return blob;
447}
448
449bool SQLiteStatement::returnTextResults(int col, Vector<String>& v)
450{
451    ASSERT(col >= 0);
452
453    v.clear();
454
455    if (m_statement)
456        finalize();
457    if (prepare() != SQLITE_OK)
458        return false;
459
460    while (step() == SQLITE_ROW)
461        v.append(getColumnText(col));
462    bool result = true;
463    if (m_database.lastError() != SQLITE_DONE) {
464        result = false;
465        LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
466    }
467    finalize();
468    return result;
469}
470
471bool SQLiteStatement::returnIntResults(int col, Vector<int>& v)
472{
473    v.clear();
474
475    if (m_statement)
476        finalize();
477    if (prepare() != SQLITE_OK)
478        return false;
479
480    while (step() == SQLITE_ROW)
481        v.append(getColumnInt(col));
482    bool result = true;
483    if (m_database.lastError() != SQLITE_DONE) {
484        result = false;
485        LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
486    }
487    finalize();
488    return result;
489}
490
491bool SQLiteStatement::returnInt64Results(int col, Vector<int64_t>& v)
492{
493    v.clear();
494
495    if (m_statement)
496        finalize();
497    if (prepare() != SQLITE_OK)
498        return false;
499
500    while (step() == SQLITE_ROW)
501        v.append(getColumnInt64(col));
502    bool result = true;
503    if (m_database.lastError() != SQLITE_DONE) {
504        result = false;
505        LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
506    }
507    finalize();
508    return result;
509}
510
511bool SQLiteStatement::returnDoubleResults(int col, Vector<double>& v)
512{
513    v.clear();
514
515    if (m_statement)
516        finalize();
517    if (prepare() != SQLITE_OK)
518        return false;
519
520    while (step() == SQLITE_ROW)
521        v.append(getColumnDouble(col));
522    bool result = true;
523    if (m_database.lastError() != SQLITE_DONE) {
524        result = false;
525        LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
526    }
527    finalize();
528    return result;
529}
530
531bool SQLiteStatement::isExpired()
532{
533    return !m_statement || sqlite3_expired(m_statement);
534}
535
536} // namespace WebCore
537
538#endif // ENABLE(DATABASE)
539