1package com.xtremelabs.robolectric.util;
2
3import android.content.ContentValues;
4import android.database.sqlite.SQLiteException;
5
6import java.sql.SQLException;
7import java.util.ArrayList;
8import java.util.Iterator;
9import java.util.List;
10import java.util.Map.Entry;
11
12/**
13 * SQL utility methods to support the database-related shadows, such as
14 * {@code ShadowSQLiteDatabase} and {@code ShadowSQLiteCursor}.
15 */
16public class SQLite {
17    private static final String[] CONFLICT_VALUES = {"", "OR ROLLBACK ", "OR ABORT ", "OR FAIL ", "OR IGNORE ", "OR REPLACE "};
18
19    /**
20     * Create a SQL INSERT string.  Returned values are then bound via
21     * JDBC to facilitate various data types.
22     *
23     * @param table  table name
24     * @param values column name/value pairs
25     * @param conflictAlgorithm the conflict algorithm to use
26     * @return insert string
27     */
28    public static SQLStringAndBindings buildInsertString(String table, ContentValues values, int conflictAlgorithm) throws SQLException {
29        StringBuilder sb = new StringBuilder();
30
31        sb.append("INSERT ");
32        sb.append(CONFLICT_VALUES[conflictAlgorithm]);
33        sb.append("INTO ");
34
35        sb.append(table);
36        sb.append(" ");
37
38        SQLStringAndBindings columnsValueClause = buildColumnValuesClause(values);
39        sb.append(columnsValueClause.sql);
40        sb.append(";");
41
42        String sql = DatabaseConfig.getScrubSQL(sb.toString());
43        return new SQLStringAndBindings(sql, columnsValueClause.columnValues);
44    }
45
46    /**
47     * Create a SQL UPDATE string.  Returned values are then bound via
48     * JDBC to facilitate various data types.
49     *
50     * @param table       table name
51     * @param values      column name/value pairs
52     * @param whereClause SQL where clause fragment
53     * @param whereArgs   Array of substitutions for args in whereClause
54     * @return update string
55     */
56    public static SQLStringAndBindings buildUpdateString(String table, ContentValues values, String whereClause, String[] whereArgs) {
57        StringBuilder sb = new StringBuilder();
58
59        sb.append("UPDATE ");
60        sb.append(table);
61        sb.append(" SET ");
62
63        SQLStringAndBindings columnAssignmentsClause = buildColumnAssignmentsClause(values);
64        sb.append(columnAssignmentsClause.sql);
65
66        if (whereClause != null) {
67            String where = whereClause;
68            if (whereArgs != null) {
69                where = buildWhereClause(whereClause, whereArgs);
70            }
71            sb.append(" WHERE ");
72            sb.append(where);
73        }
74        sb.append(";");
75
76        return new SQLStringAndBindings(sb.toString(), columnAssignmentsClause.columnValues);
77    }
78
79    /**
80     * Create a SQL DELETE string.
81     *
82     * @param table       table name
83     * @param whereClause SQL where clause fragment
84     * @param whereArgs   Array of substitutions for args in whereClause
85     * @return delete string
86     */
87    public static String buildDeleteString(String table, String whereClause, String[] whereArgs) {
88        StringBuilder sb = new StringBuilder();
89
90        sb.append("DELETE FROM ");
91        sb.append(table);
92
93        if (whereClause != null) {
94            String where = whereClause;
95            if (whereArgs != null) {
96                where = buildWhereClause(whereClause, whereArgs);
97            }
98            sb.append(" WHERE ");
99            sb.append(where);
100        }
101        sb.append(";");
102
103        return sb.toString();
104    }
105
106    /**
107     * Build a WHERE clause used in SELECT, UPDATE and DELETE statements.
108     *
109     * @param selection     SQL where clause fragment
110     * @param selectionArgs Array of substitutions for args in selection
111     * @return where clause
112     */
113    public static String buildWhereClause(String selection, String[] selectionArgs) throws SQLiteException {
114        String whereClause = selection;
115        int argsNeeded = 0;
116        int args = 0;
117
118        for (char c : selection.toCharArray()) {
119            if (c == '?') argsNeeded++;
120        }
121        if (selectionArgs != null) {
122            for (int x = 0; x < selectionArgs.length; x++) {
123                if (selectionArgs[x] == null) {
124                    throw new IllegalArgumentException("the bind value at index " + x + " is null");
125                } else {
126                    args++;
127                }
128                whereClause = whereClause.replaceFirst("\\?", "'" + selectionArgs[x] + "'");
129            }
130        }
131        if (argsNeeded != args) {
132            throw new SQLiteException("bind or column index out of range: count of selectionArgs does not match count of (?) placeholders for given sql statement!");
133        }
134
135        return whereClause;
136    }
137
138   /**
139     * Build the '(columns...) VALUES (values...)' clause used in INSERT
140     * statements.
141     *
142     * @param values column name/value pairs
143     * @return SQLStringAndBindings
144     */
145    public static SQLStringAndBindings buildColumnValuesClause(ContentValues values) {
146        StringBuilder clause = new StringBuilder("(");
147        List<Object> columnValues = new ArrayList<Object>(values.size());
148
149        Iterator<Entry<String, Object>> itemEntries = values.valueSet().iterator();
150        while (itemEntries.hasNext()) {
151            Entry<String, Object> entry = itemEntries.next();
152            clause.append(entry.getKey());
153            if (itemEntries.hasNext()) {
154                clause.append(", ");
155            }
156            columnValues.add(entry.getValue());
157        }
158
159        clause.append(") VALUES (");
160        for (int i = 0; i < values.size() - 1; i++) {
161            clause.append("?, ");
162        }
163        clause.append("?)");
164
165        return new SQLStringAndBindings(clause.toString(), columnValues);
166    }
167
168    /**
169     * Build the '(col1=?, col2=? ... )' clause used in UPDATE statements.
170     *
171     * @param values column name/value pairs
172     * @return SQLStringAndBindings
173     */
174    public static SQLStringAndBindings buildColumnAssignmentsClause(ContentValues values) {
175        StringBuilder clause = new StringBuilder();
176        List<Object> columnValues = new ArrayList<Object>(values.size());
177
178        Iterator<Entry<String, Object>> itemsEntries = values.valueSet().iterator();
179        while (itemsEntries.hasNext()) {
180            Entry<String, Object> entry = itemsEntries.next();
181            clause.append(entry.getKey());
182            clause.append("=?");
183            if (itemsEntries.hasNext()) {
184                clause.append(", ");
185            }
186            columnValues.add(entry.getValue());
187        }
188
189        return new SQLStringAndBindings(clause.toString(), columnValues);
190    }
191
192    /**
193     * Container for a SQL fragment and the objects which are to be
194     * bound to the arguments in the fragment.
195     */
196    public static class SQLStringAndBindings {
197        public String sql;
198        public List<Object> columnValues;
199
200        public SQLStringAndBindings(String sql, List<Object> columnValues) {
201            this.sql = sql;
202            this.columnValues = columnValues;
203        }
204    }
205
206}
207