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