1 /* 2 * Copyright (C) 2023 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package android.healthconnect.cts.database; 18 19 import androidx.annotation.NonNull; 20 21 import java.util.HashMap; 22 import java.util.Objects; 23 import java.util.regex.Matcher; 24 import java.util.regex.Pattern; 25 26 /** 27 * HealthConnectDatabaseSchemaParser takes the sql file as input in string format and stores all 28 * information about tables,corresponding columns,foreign keys and indexes of the HealthConnect 29 * database. 30 */ 31 class HealthConnectDatabaseSchemaParser { 32 33 /** 34 * It matches one word(table name) followed by opening parenthesis followed by anything(table 35 * definitions including column definitions and foreign key definitions) followed by closing 36 * parenthesis(end of table definition). 37 */ 38 public static final String CREATE_TABLE_REGEX = "(\\w+)\\s*\\((.*)\\)"; 39 40 public static final String CHECK_CONSTRAINT_REGEX = "(?i)CHECK\\s*\\(([^)]+)\\)"; 41 public static final String DEFAULT_VALUE_REGEX = "(?i)DEFAULT\\s+(\\S+)"; 42 43 public static final String COMPOSITE_PRIMARY_KEY_REGEX = 44 "(?i)PRIMARY (?i)KEY\\s*\\(\\s*([^\\)]+)\\)"; 45 46 /** 47 * It matches FOREIGN KEY followed by opening parenthesis followed by word (foreign key name) 48 * followed by REFERENCES followed by word(table name) followed by column names of that table 49 * and then finally flags which are optional. 50 */ 51 public static final String FOREIGN_KEY_REGEX = 52 "(?i)FOREIGN (?i)KEY\\s*\\(\\s*([^\\)]+)\\)(?:\\s*(?i)REFERENCES\\s*(\\w+)\\s*\\" 53 + "(\\s*([^\\)]+)\\))?((?: \\w+)*)?"; 54 55 /** 56 * It matches CREATE UNIQUE(optionally) INDEX followed by word (index name) ON followed by 57 * word(table name) followed by column bounded by opening and closing parenthesis. 58 */ 59 public static final String INDEX_REGEX = 60 "(?i)CREATE\\s+(?:\\s*UNIQUE\\s+)?(?i)INDEX\\s+(\\w+)\\s+(?i)ON\\s+(\\w+)\\s*\\((.*)" 61 + "\\)"; 62 63 public static final String WHITE_SPACE_REGEX = "\\s+"; 64 public static final String CREATE_REGEX = "(?i)\\bcreate\\b"; 65 public static final String INDEX_KEY_REGEX = "(?i)\\bINDEX\\b"; 66 public static final String FOREIGN_REGEX = "(?i)\\bFOREIGN\\b"; 67 public static final String KEY_REGEX = "(?i)\\bKEY\\b"; 68 public static final String PRIMARY_REGEX = "(?i)\\bPRIMARY\\b"; 69 public static final String NOT_NULL_REGEX = "(?i)\\bNOT NULL\\b"; 70 public static final String AUTO_INCREMENT_REGEX = "(?i)\\bAUTOINCREMENT\\b"; 71 public static final String UNIQUE_REGEX = "(?i)\\bUNIQUE\\b"; 72 public static final String CHECK_REGEX = "(?i)\\bCHECK\\b"; 73 74 /** Stores all the tables . */ getTableInfo(@onNull String inputSchema, HashMap<String, TableInfo> mTableMap)75 static void getTableInfo(@NonNull String inputSchema, HashMap<String, TableInfo> mTableMap) { 76 Objects.requireNonNull(inputSchema); 77 String sqlStatement = inputSchema; 78 /** 79 * sqlStatement string has been processed so that it can work correctly with our regex . For 80 * that we have replaced more than one whitespace with one whitespace and broken the line so 81 * that string after 'CREATE' can start from a new line. 82 */ 83 sqlStatement = sqlStatement.replaceAll(WHITE_SPACE_REGEX, " "); 84 sqlStatement = sqlStatement.replaceAll(CREATE_REGEX, "\nCREATE"); 85 String[] createTableStatements = sqlStatement.split("(?i)CREATE"); 86 for (String statement : createTableStatements) { 87 88 if (statement.contains("INDEX")) continue; 89 statement = statement.replaceAll("\\bTABLE\\b", ""); 90 String trimmedStatement = statement.trim(); 91 92 if (!trimmedStatement.isEmpty()) { 93 94 Pattern tablePattern = Pattern.compile(CREATE_TABLE_REGEX); 95 Matcher tableMatcher = tablePattern.matcher(trimmedStatement); 96 97 if (tableMatcher.find()) { 98 String tableName = tableMatcher.group(1); 99 TableInfo.Builder tableInfo = new TableInfo.Builder(tableName); 100 String tableDefinition = tableMatcher.group(2); 101 String[] columns = tableDefinition.split(",\\s*"); 102 for (String column : columns) { 103 /** 104 * Since we are splitting the column definitions from each other with the 105 * help of comma, we will need to take care of the case when composite 106 * foreign key is created as the columns will also be separated with 107 * comma.So in order to identify only the valid column definitions ,we are 108 * adding a small check to ensure that we consider only that part in which 109 * either both opening and closing parenthesis are present or none of them 110 * are present. e.g.: Suppose we have a composite foreign key as FOREIGN 111 * KEY(col1,col2,col3) REFERENCES table(pk1,ok2,pk3) So in this case, when 112 * we will split with comma ,the parts will be: FOREIGN KEY (col1 {ignored 113 * as we have only opening parenthesis} col2 col3 ) REFERENCES table(pk1 114 * {ignored as we have closing parenthesis before opening} 115 */ 116 int first = column.indexOf('('), second = column.indexOf(')'); 117 if ((first == -1 && second != -1) 118 || (first != -1 && second == -1) 119 || (first > second)) { 120 continue; 121 } else { 122 String[] parts = column.trim().split("\\s+"); 123 124 if (parts.length > 0 125 && ((parts[0].equals("FOREIGN")) 126 || parts[0].equals("PRIMARY"))) { 127 continue; 128 } else if (parts.length >= 2) { 129 String columnName = parts[0]; 130 String dataType = parts[1]; 131 String defaultValue = null; 132 Pattern defaultPattern = Pattern.compile(DEFAULT_VALUE_REGEX); 133 Matcher defaultMatcher = defaultPattern.matcher(column); 134 if (defaultMatcher.find()) { 135 defaultValue = defaultMatcher.group(1); 136 } 137 ColumnInfo.Builder col = 138 new ColumnInfo.Builder(columnName, dataType); 139 col.setDefaultValue(defaultValue); 140 141 boolean isUnique = column.contains("UNIQUE"); 142 if (isUnique) { 143 col.addConstraint(ColumnInfo.UNIQUE_CONSTRAINT); 144 } 145 146 boolean isNotNull = column.contains("NOT NULL"); 147 if (isNotNull) { 148 col.addConstraint(ColumnInfo.NOT_NULL_CONSTRAINT); 149 } 150 151 boolean hasAutoIncrement = column.contains("AUTOINCREMENT"); 152 if (hasAutoIncrement) { 153 col.addConstraint(ColumnInfo.AUTO_INCREMENT_CONSTRAINT); 154 } 155 if (column.contains("PRIMARY KEY")) { 156 tableInfo.addPrimaryKeyColumn(columnName); 157 } 158 if (column.contains("CHECK")) { 159 Pattern checkPattern = Pattern.compile(CHECK_CONSTRAINT_REGEX); 160 Matcher checkMatcher = checkPattern.matcher(column); 161 if (checkMatcher.find()) { 162 String checkConstraint = checkMatcher.group(1); 163 col.addCheckConstraint(checkConstraint); 164 } 165 } 166 tableInfo.addColumnInfoMapping(columnName, col.build()); 167 } 168 } 169 } 170 /** Regular expression for composite Primary Key */ 171 Pattern primaryKeyPattern = Pattern.compile(COMPOSITE_PRIMARY_KEY_REGEX); 172 Matcher primaryKeyMatcher = primaryKeyPattern.matcher(trimmedStatement); 173 while (primaryKeyMatcher.find()) { 174 String pkColumns = primaryKeyMatcher.group(1); 175 String[] pkColumnList = pkColumns.split(","); 176 for (String primaryKeyColumn : pkColumnList) { 177 tableInfo.addPrimaryKeyColumn(primaryKeyColumn); 178 } 179 } 180 181 Pattern foreignKeyPattern = Pattern.compile(FOREIGN_KEY_REGEX); 182 Matcher foreignKeyMatcher = foreignKeyPattern.matcher(trimmedStatement); 183 while (foreignKeyMatcher.find()) { 184 String columnNames = foreignKeyMatcher.group(1); 185 String referencedTable = foreignKeyMatcher.group(2); 186 String referencedPrimaryKey = foreignKeyMatcher.group(3); 187 String sqlForeignKeyStatement = foreignKeyMatcher.group(); 188 String[] foreignColumn = columnNames.split(","); 189 190 if (referencedTable != null) { 191 String[] referencedPk = referencedPrimaryKey.split(","); 192 for (int i = 0; i < foreignColumn.length; i++) { 193 addForeignKeyInfo( 194 tableInfo, 195 foreignColumn[i], 196 referencedTable, 197 referencedPk[i], 198 sqlForeignKeyStatement); 199 } 200 } else { 201 for (String foreignKey : foreignColumn) { 202 addForeignKeyInfo(tableInfo, foreignKey, null, null, null); 203 } 204 } 205 } 206 mTableMap.put(tableName, tableInfo.build()); 207 } 208 } 209 } 210 } 211 212 /** Stores all the corresponding indexes of a table. */ getIndexInfo(@onNull String inputSchema, HashMap<String, TableInfo> mTableMap)213 static void getIndexInfo(@NonNull String inputSchema, HashMap<String, TableInfo> mTableMap) { 214 String sqlStatement = inputSchema; 215 sqlStatement = sqlStatement.replaceAll(WHITE_SPACE_REGEX, " "); 216 sqlStatement = sqlStatement.replaceAll(CREATE_REGEX, "\nCREATE"); 217 218 Pattern indexPattern = Pattern.compile(INDEX_REGEX); 219 Matcher indexMatcher = indexPattern.matcher(sqlStatement); 220 221 while (indexMatcher.find()) { 222 boolean uniqueFlag = (indexMatcher.group().contains("UNIQUE")); 223 String indexName = indexMatcher.group(1); 224 String tableName = indexMatcher.group(2); 225 String columnList = indexMatcher.group(3); 226 IndexInfo.Builder indexInfo = new IndexInfo.Builder(indexName, tableName); 227 indexInfo.setUniqueFlag(uniqueFlag); 228 String[] columns = columnList.split("\\s*,\\s*"); 229 230 for (String column : columns) { 231 indexInfo.addIndexCols(column); 232 } 233 TableInfo table1 = mTableMap.get(tableName); 234 table1.getIndexInfoMapping().put(indexName, indexInfo.build()); 235 } 236 } 237 238 /** Populate the HashMap of HealthConnect database Schema. */ getSchemaMap(@onNull String inputSchema)239 public static HashMap<String, TableInfo> getSchemaMap(@NonNull String inputSchema) { 240 Objects.requireNonNull(inputSchema); 241 HashMap<String, TableInfo> mTableMap = new HashMap<>(); 242 inputSchema = convertToUpperCase(inputSchema); 243 getTableInfo(inputSchema, mTableMap); 244 getIndexInfo(inputSchema, mTableMap); 245 return mTableMap; 246 } 247 248 /** 249 * Checks the flags that are being used in the table while creating the foreign key and adds 250 * those flags to the flag list of the ForeignKeyInfo. 251 */ setForeignKeyFlags( String sqlForeignKeyStatement, ForeignKeyInfo.Builder foreignKeyInfo)252 static void setForeignKeyFlags( 253 String sqlForeignKeyStatement, ForeignKeyInfo.Builder foreignKeyInfo) { 254 if (sqlForeignKeyStatement == null) { 255 return; 256 } 257 sqlForeignKeyStatement = sqlForeignKeyStatement.toUpperCase(); 258 259 if (sqlForeignKeyStatement.contains("ON DELETE CASCADE")) { 260 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_DELETE_CASCADE); 261 } 262 if (sqlForeignKeyStatement.contains("ON DELETE SET NULL")) { 263 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_DELETE_SET_NULL); 264 } 265 if (sqlForeignKeyStatement.contains("ON DELETE SET DEFAULT")) { 266 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_DELETE_SET_DEFAULT); 267 } 268 if (sqlForeignKeyStatement.contains("ON DELETE RESTRICT")) { 269 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_DELETE_RESTRICT); 270 } 271 if (sqlForeignKeyStatement.contains("ON UPDATE CASCADE")) { 272 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_UPDATE_CASCADE); 273 } 274 if (sqlForeignKeyStatement.contains("ON UPDATE SET NULL")) { 275 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_UPDATE_SET_NULL); 276 } 277 if (sqlForeignKeyStatement.contains("ON UPDATE SET DEFAULT")) { 278 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_UPDATE_SET_DEFAULT); 279 } 280 if (sqlForeignKeyStatement.contains("ON UPDATE RESTRICT")) { 281 foreignKeyInfo.addFlag(ForeignKeyInfo.ON_UPDATE_RESTRICT); 282 } 283 if (sqlForeignKeyStatement.contains("DEFERRABLE")) { 284 foreignKeyInfo.addFlag(ForeignKeyInfo.DEFERRABLE_FLAG); 285 } 286 if (sqlForeignKeyStatement.contains("INITIALLY DEFERRED")) { 287 foreignKeyInfo.addFlag(ForeignKeyInfo.INITIALLY_DEFERRED); 288 } 289 } 290 convertToUpperCase(String inputSchema)291 public static String convertToUpperCase(String inputSchema) { 292 293 inputSchema = inputSchema.replaceAll(INDEX_KEY_REGEX, "INDEX"); 294 inputSchema = inputSchema.replaceAll(FOREIGN_REGEX, "FOREIGN"); 295 inputSchema = inputSchema.replaceAll(KEY_REGEX, "KEY"); 296 inputSchema = inputSchema.replaceAll(PRIMARY_REGEX, "PRIMARY"); 297 inputSchema = inputSchema.replaceAll(UNIQUE_REGEX, "UNIQUE"); 298 inputSchema = inputSchema.replaceAll(NOT_NULL_REGEX, "NOT NULL"); 299 inputSchema = inputSchema.replaceAll(AUTO_INCREMENT_REGEX, "AUTOINCREMENT"); 300 inputSchema = inputSchema.replaceAll(CHECK_REGEX, "CHECK"); 301 return inputSchema; 302 } 303 addForeignKeyInfo( TableInfo.Builder tableInfo, String foreignKeyColumn, String referencedTable, String referencedPrimaryKey, String sqlForeignKeyStatement)304 public static void addForeignKeyInfo( 305 TableInfo.Builder tableInfo, 306 String foreignKeyColumn, 307 String referencedTable, 308 String referencedPrimaryKey, 309 String sqlForeignKeyStatement) { 310 ForeignKeyInfo.Builder foreignKeyInfo = 311 new ForeignKeyInfo.Builder(foreignKeyColumn, referencedTable, referencedPrimaryKey); 312 if (sqlForeignKeyStatement != null) { 313 setForeignKeyFlags(sqlForeignKeyStatement, foreignKeyInfo); 314 } 315 316 tableInfo.addForeignKeyInfoMapping(foreignKeyColumn, foreignKeyInfo.build()); 317 } 318 } 319