1 package com.xtremelabs.robolectric.util;
2 
3 import android.content.ContentValues;
4 import android.database.sqlite.SQLiteException;
5 
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.Iterator;
9 import java.util.List;
10 import 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  */
16 public 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      */
buildInsertString(String table, ContentValues values, int conflictAlgorithm)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      */
buildUpdateString(String table, ContentValues values, String whereClause, String[] whereArgs)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      */
buildDeleteString(String table, String whereClause, String[] whereArgs)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      */
buildWhereClause(String selection, String[] selectionArgs)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      */
buildColumnValuesClause(ContentValues values)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      */
buildColumnAssignmentsClause(ContentValues values)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 
SQLStringAndBindings(String sql, List<Object> columnValues)200         public SQLStringAndBindings(String sql, List<Object> columnValues) {
201             this.sql = sql;
202             this.columnValues = columnValues;
203         }
204     }
205 
206 }
207