Autopsy  4.20.0
Graphical digital forensics platform for The Sleuth Kit and other tools.
RdbmsCentralRepoFactory.java
Go to the documentation of this file.
1 /*
2  * Central Repository
3  *
4  * Copyright 2020 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.autopsy.centralrepository.datamodel;
20 
21 import java.sql.Connection;
22 import java.sql.PreparedStatement;
23 import java.sql.ResultSet;
24 import java.sql.SQLException;
25 import java.sql.Statement;
26 import java.util.List;
27 import java.util.logging.Level;
30 import static org.sleuthkit.autopsy.centralrepository.datamodel.RdbmsCentralRepo.SOFTWARE_CR_DB_SCHEMA_VERSION;
32 import org.sleuthkit.datamodel.Account;
33 
39 
40  private final static Logger LOGGER = Logger.getLogger(RdbmsCentralRepoFactory.class.getName());
41 
42 
46 
47 
48  // SQLite pragmas
49  private final static String PRAGMA_SYNC_OFF = "PRAGMA synchronous = OFF";
50  private final static String PRAGMA_JOURNAL_WAL = "PRAGMA journal_mode = WAL";
51  private final static String PRAGMA_READ_UNCOMMITTED_TRUE = "PRAGMA read_uncommitted = True";
52  private final static String PRAGMA_ENCODING_UTF8 = "PRAGMA encoding = 'UTF-8'";
53  private final static String PRAGMA_PAGE_SIZE_4096 = "PRAGMA page_size = 4096";
54  private final static String PRAGMA_FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON";
55 
56 
57 
59  this.selectedPlatform = selectedPlatform;
60  this.sqliteCentralRepoSettings = repoSettings;
61  this.postgresCentralRepoSettings = null;
62 
63  }
64 
66  this.selectedPlatform = selectedPlatform;
67  this.postgresCentralRepoSettings = repoSettings;
68  this.sqliteCentralRepoSettings = null;
69  }
70 
71 
83  public boolean initializeDatabaseSchema() {
84 
85  String createArtifactInstancesTableTemplate = getCreateArtifactInstancesTableTemplate(selectedPlatform);
86  String createAccountInstancesTableTemplate = getCreateAccountInstancesTableTemplate(selectedPlatform);
87 
88  String instancesCaseIdIdx = getAddCaseIdIndexTemplate();
89  String instancesDatasourceIdIdx = getAddDataSourceIdIndexTemplate();
90  String instancesValueIdx = getAddValueIndexTemplate();
91  String instancesKnownStatusIdx = getAddKnownStatusIndexTemplate();
92  String instancesObjectIdIdx = getAddObjectIdIndexTemplate();
93 
94  // NOTE: the db_info table currenly only has 1 row, so having an index
95  // provides no benefit.
96  try (Connection conn = this.getEphemeralConnection();) {
97 
98  if (null == conn) {
99  LOGGER.log(Level.SEVERE, "Cannot initialize CR database, don't have a valid connection."); // NON-NLS
100  return false;
101  }
102 
103  try (Statement stmt = conn.createStatement();) {
104 
105  // these setting PRAGMAs are SQLIte spcific
106  if (selectedPlatform == CentralRepoPlatforms.SQLITE) {
107  stmt.execute(PRAGMA_JOURNAL_WAL);
108  stmt.execute(PRAGMA_SYNC_OFF);
109  stmt.execute(PRAGMA_READ_UNCOMMITTED_TRUE);
110  stmt.execute(PRAGMA_ENCODING_UTF8);
111  stmt.execute(PRAGMA_PAGE_SIZE_4096);
112  stmt.execute(PRAGMA_FOREIGN_KEYS_ON);
113  }
114 
115  // Create Organizations table
116  stmt.execute(getCreateOrganizationsTableStatement(selectedPlatform));
117 
118  // Create Cases table and indexes
119  stmt.execute(getCreateCasesTableStatement(selectedPlatform));
120  stmt.execute(getCasesOrgIdIndexStatement());
121  stmt.execute(getCasesCaseUidIndexStatement());
122 
123  stmt.execute(getCreateDataSourcesTableStatement(selectedPlatform));
124  stmt.execute(getAddDataSourcesNameIndexStatement());
125  stmt.execute(getAddDataSourcesObjectIdIndexStatement());
126 
127  stmt.execute(getCreateReferenceSetsTableStatement(selectedPlatform));
128  stmt.execute(getReferenceSetsOrgIdIndexTemplate());
129 
130  stmt.execute(getCreateCorrelationTypesTableStatement(selectedPlatform));
131 
132  stmt.execute(getCreateDbInfoTableStatement(selectedPlatform));
133  stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.SCHEMA_MAJOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() + "')");
134  stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.SCHEMA_MINOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMinor() + "')");
135  stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.CREATION_SCHEMA_MAJOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() + "')");
136  stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.CREATION_SCHEMA_MINOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMinor() + "')");
137 
138  // Create account_types and accounts tables which are referred by X_instances tables
139  stmt.execute(getCreateAccountTypesTableStatement(selectedPlatform));
140  stmt.execute(getCreateAccountsTableStatement(selectedPlatform));
141 
142  // Create a separate instance and reference table for each artifact type
144 
145  String reference_type_dbname;
146  String instance_type_dbname;
147  for (CorrelationAttributeInstance.Type type : defaultCorrelationTypes) {
148  reference_type_dbname = CentralRepoDbUtil.correlationTypeToReferenceTableName(type);
149  instance_type_dbname = CentralRepoDbUtil.correlationTypeToInstanceTableName(type);
150 
151  // use the correct create table template, based on whether the attribute type represents an account or not.
152  String createTableTemplate = (CentralRepoDbUtil.correlationAttribHasAnAccount(type))
153  ? createAccountInstancesTableTemplate
154  : createArtifactInstancesTableTemplate;
155 
156  stmt.execute(String.format(createTableTemplate, instance_type_dbname, instance_type_dbname));
157 
158  stmt.execute(String.format(instancesCaseIdIdx, instance_type_dbname, instance_type_dbname));
159  stmt.execute(String.format(instancesDatasourceIdIdx, instance_type_dbname, instance_type_dbname));
160  stmt.execute(String.format(instancesValueIdx, instance_type_dbname, instance_type_dbname));
161  stmt.execute(String.format(instancesKnownStatusIdx, instance_type_dbname, instance_type_dbname));
162  stmt.execute(String.format(instancesObjectIdIdx, instance_type_dbname, instance_type_dbname));
163 
164  // FUTURE: allow more than the FILES type
165  if (type.getId() == CorrelationAttributeInstance.FILES_TYPE_ID) {
166  stmt.execute(String.format(getReferenceTypesTableTemplate(selectedPlatform), reference_type_dbname, reference_type_dbname));
167  stmt.execute(String.format(getReferenceTypeValueIndexTemplate(), reference_type_dbname, reference_type_dbname));
168  stmt.execute(String.format(getReferenceTypeValueKnownstatusIndexTemplate(), reference_type_dbname, reference_type_dbname));
169  }
170  }
171  // create Persona tables.
172  createPersonaTables(stmt, selectedPlatform);
173  } catch (SQLException ex) {
174  LOGGER.log(Level.SEVERE, "Error initializing db schema.", ex); // NON-NLS
175  return false;
176  } catch (CentralRepoException ex) {
177  LOGGER.log(Level.SEVERE, "Error getting default correlation types. Likely due to one or more Type's with an invalid db table name."); // NON-NLS
178  return false;
179  }
180  } catch (SQLException ex) {
181  LOGGER.log(Level.SEVERE, "Error connecting to database.", ex); // NON-NLS
182  return false;
183  }
184 
185  return true;
186  }
187 
193  public boolean insertDefaultDatabaseContent() {
194 
195  boolean result;
196  try (Connection conn = this.getEphemeralConnection();) {
197  if (null == conn) {
198  return false;
199  }
200 
202  && CentralRepoDbUtil.insertDefaultOrganization(conn)
203  && RdbmsCentralRepoFactory.insertDefaultAccountsTablesContent(conn, selectedPlatform )
204  && insertDefaultPersonaTablesContent(conn, selectedPlatform);
205 
206  } catch (SQLException ex) {
207  LOGGER.log(Level.SEVERE, String.format("Failed to populate default data in CR tables."), ex);
208  return false;
209  }
210 
211  return result;
212  }
213 
214  private static String getCreateDbInfoTableStatement(CentralRepoPlatforms selectedPlatform) {
215  /*
216  * Note that the essentially useless id column in the following
217  * table is required for backwards compatibility. Otherwise, the
218  * name column could be the primary key.
219  */
220 
221  return "CREATE TABLE db_info ("
222  + getNumericPrimaryKeyClause("id", selectedPlatform)
223  + "name TEXT UNIQUE NOT NULL,"
224  + "value TEXT NOT NULL "
225  + ")";
226 
227  }
235  private static String getCreateOrganizationsTableStatement(CentralRepoPlatforms selectedPlatform) {
236  // The "id" column is an alias for the built-in 64-bit int "rowid" column.
237  // It is autoincrementing by default and must be of type "integer primary key".
238  // We've omitted the autoincrement argument because we are not currently
239  // using the id value to search for specific rows, so we do not care
240  // if a rowid is re-used after an existing rows was previously deleted.
241 
242  return "CREATE TABLE IF NOT EXISTS organizations ("
243  + getNumericPrimaryKeyClause("id", selectedPlatform)
244  + "org_name text NOT NULL,"
245  + "poc_name text NOT NULL,"
246  + "poc_email text NOT NULL,"
247  + "poc_phone text NOT NULL,"
248  + "CONSTRAINT org_name_unique UNIQUE (org_name)"
249  + ")";
250  }
251 
259  private static String getCreateCasesTableStatement(CentralRepoPlatforms selectedPlatform) {
260 
261  return ("CREATE TABLE IF NOT EXISTS cases (")
262  + getNumericPrimaryKeyClause("id", selectedPlatform)
263  + "case_uid text NOT NULL,"
264  + "org_id integer,"
265  + "case_name text NOT NULL,"
266  + "creation_date text NOT NULL,"
267  + "case_number text,"
268  + "examiner_name text,"
269  + "examiner_email text,"
270  + "examiner_phone text,"
271  + "notes text,"
272  + "foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,"
273  + "CONSTRAINT case_uid_unique UNIQUE(case_uid)" + getOnConflictIgnoreClause(selectedPlatform)
274  + ")";
275  }
276 
277  private static String getCasesOrgIdIndexStatement() {
278  return "CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
279  }
280 
281  private static String getCasesCaseUidIndexStatement() {
282  return "CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";
283  }
284 
285  private static String getCreateReferenceSetsTableStatement(CentralRepoPlatforms selectedPlatform) {
286 
287  return "CREATE TABLE IF NOT EXISTS reference_sets ("
288  + getNumericPrimaryKeyClause("id", selectedPlatform)
289  + "org_id integer NOT NULL,"
290  + "set_name text NOT NULL,"
291  + "version text NOT NULL,"
292  + "known_status integer NOT NULL,"
293  + "read_only boolean NOT NULL,"
294  + "type integer NOT NULL,"
295  + "import_date text NOT NULL,"
296  + "foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,"
297  + "CONSTRAINT hash_set_unique UNIQUE (set_name, version)"
298  + ")";
299 
300  }
301 
306  private static String getReferenceSetsOrgIdIndexTemplate() {
307  return "CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";
308  }
309 
317  private static String getReferenceTypesTableTemplate(CentralRepoPlatforms selectedPlatform) {
318  // Each "%s" will be replaced with the relevant reference_TYPE table name.
319 
320  return "CREATE TABLE IF NOT EXISTS %s ("
321  + getNumericPrimaryKeyClause("id", selectedPlatform)
322  + "reference_set_id integer,"
323  + "value text NOT NULL,"
324  + "known_status integer NOT NULL,"
325  + "comment text,"
326  + "CONSTRAINT %s_multi_unique UNIQUE(reference_set_id, value)" + getOnConflictIgnoreClause(selectedPlatform) + ","
327  + "foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL"
328  + ")";
329  }
330 
335  private static String getReferenceTypeValueIndexTemplate() {
336  return "CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
337  }
338 
344  return "CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
345  }
346 
354  private static String getCreateCorrelationTypesTableStatement(CentralRepoPlatforms selectedPlatform) {
355 
356  return "CREATE TABLE IF NOT EXISTS correlation_types ("
357  + getNumericPrimaryKeyClause("id", selectedPlatform)
358  + "display_name text NOT NULL,"
359  + "db_table_name text NOT NULL,"
360  + "supported integer NOT NULL,"
361  + "enabled integer NOT NULL,"
362  + "CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)"
363  + ")";
364  }
372  static String getCreateArtifactInstancesTableTemplate(CentralRepoPlatforms selectedPlatform) {
373  // Each "%s" will be replaced with the relevant TYPE_instances table name.
374 
375  return "CREATE TABLE IF NOT EXISTS %s ("
376  + getNumericPrimaryKeyClause("id", selectedPlatform)
377  + "case_id integer NOT NULL,"
378  + "data_source_id integer NOT NULL,"
379  + "value text NOT NULL,"
380  + "file_path text NOT NULL,"
381  + "known_status integer NOT NULL,"
382  + "comment text,"
383  + "file_obj_id " + getBigIntType(selectedPlatform) + " ,"
384  + "CONSTRAINT %s_multi_unique UNIQUE(data_source_id, value, file_path)" + getOnConflictIgnoreClause(selectedPlatform) + ","
385  + "foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,"
386  + "foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL)";
387  }
388 
396  static String getCreateAccountInstancesTableTemplate(CentralRepoPlatforms selectedPlatform) {
397  // Each "%s" will be replaced with the relevant TYPE_instances table name.
398 
399  return "CREATE TABLE IF NOT EXISTS %s ("
400  + getNumericPrimaryKeyClause("id", selectedPlatform)
401  + "case_id integer NOT NULL,"
402  + "data_source_id integer NOT NULL,"
403  + "account_id " + getBigIntType(selectedPlatform) + " DEFAULT NULL,"
404  + "value text NOT NULL,"
405  + "file_path text NOT NULL,"
406  + "known_status integer NOT NULL,"
407  + "comment text,"
408  + "file_obj_id " + getBigIntType(selectedPlatform) + " ,"
409  + "CONSTRAINT %s_multi_unique UNIQUE(data_source_id, value, file_path)" + getOnConflictIgnoreClause(selectedPlatform) + ","
410  + "foreign key (account_id) references accounts(id),"
411  + "foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,"
412  + "foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL)";
413  }
414 
422  static String getCreateDataSourcesTableStatement(CentralRepoPlatforms selectedPlatform) {
423  return "CREATE TABLE IF NOT EXISTS data_sources ("
424  + getNumericPrimaryKeyClause("id", selectedPlatform)
425  + "case_id integer NOT NULL,"
426  + "device_id text NOT NULL,"
427  + "name text NOT NULL,"
428  + "datasource_obj_id " + getBigIntType(selectedPlatform) + " ,"
429  + "md5 text DEFAULT NULL,"
430  + "sha1 text DEFAULT NULL,"
431  + "sha256 text DEFAULT NULL,"
432  + "foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,"
433  + "CONSTRAINT datasource_unique UNIQUE (case_id, datasource_obj_id))";
434  }
435 
444  static String getAddCaseIdIndexTemplate() {
445  // Each "%s" will be replaced with the relevant TYPE_instances table name.
446  return "CREATE INDEX IF NOT EXISTS %s_case_id ON %s (case_id)";
447  }
448 
457  static String getAddDataSourceIdIndexTemplate() {
458  // Each "%s" will be replaced with the relevant TYPE_instances table name.
459  return "CREATE INDEX IF NOT EXISTS %s_data_source_id ON %s (data_source_id)";
460  }
461 
470  static String getAddValueIndexTemplate() {
471  // Each "%s" will be replaced with the relevant TYPE_instances table name.
472  return "CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
473  }
474 
483  static String getAddKnownStatusIndexTemplate() {
484  // Each "%s" will be replaced with the relevant TYPE_instances table name.
485  return "CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
486  }
487 
496  static String getAddObjectIdIndexTemplate() {
497  // Each "%s" will be replaced with the relevant TYPE_instances table name.
498  return "CREATE INDEX IF NOT EXISTS %s_file_obj_id ON %s (file_obj_id)";
499  }
500 
508  static String getAddDataSourcesNameIndexStatement() {
509  return "CREATE INDEX IF NOT EXISTS data_sources_name ON data_sources (name)";
510  }
511 
519  static String getAddDataSourcesObjectIdIndexStatement() {
520  return "CREATE INDEX IF NOT EXISTS data_sources_object_id ON data_sources (datasource_obj_id)";
521  }
522 
532  private static String getNumericPrimaryKeyClause(String pkName, CentralRepoPlatforms selectedPlatform) {
533  switch (selectedPlatform) {
534  case POSTGRESQL:
535  return String.format(" %s SERIAL PRIMARY KEY, ", pkName);
536  case SQLITE:
537  return String.format(" %s integer primary key autoincrement NOT NULL ,", pkName);
538  default:
539  return "";
540  }
541 
542  }
543 
550  private static String getOnConflictIgnoreClause(CentralRepoPlatforms selectedPlatform) {
551  switch (selectedPlatform) {
552  case POSTGRESQL:
553  return "";
554  case SQLITE:
555  return " ON CONFLICT IGNORE ";
556  default:
557  return "";
558  }
559  }
560 
567  static String getBigIntType(CentralRepoPlatforms selectedPlatform) {
568  switch (selectedPlatform) {
569  case POSTGRESQL:
570  return " BIGINT ";
571  case SQLITE:
572  return " INTEGER ";
573  default:
574  return "";
575  }
576  }
577 
578  private static String getOnConflictDoNothingClause(CentralRepoPlatforms selectedPlatform) {
579  switch (selectedPlatform) {
580  case POSTGRESQL:
581  return "ON CONFLICT DO NOTHING";
582  case SQLITE:
583  return "";
584  default:
585  return "";
586  }
587  }
593  private Connection getEphemeralConnection() {
594  switch (selectedPlatform) {
595  case POSTGRESQL:
596  return this.postgresCentralRepoSettings.getEphemeralConnection(false);
597  case SQLITE:
598  return this.sqliteCentralRepoSettings.getEphemeralConnection();
599  default:
600  return null;
601  }
602  }
603 
609  static boolean createPersonaTables(Statement stmt, CentralRepoPlatforms selectedPlatform) throws SQLException {
610 
611  stmt.execute(getCreateConfidenceTableStatement(selectedPlatform));
612  stmt.execute(getCreateExaminersTableStatement(selectedPlatform));
613  stmt.execute(getCreatePersonaStatusTableStatement(selectedPlatform));
614 
615  stmt.execute(getCreatePersonasTableStatement(selectedPlatform));
616  stmt.execute(getCreatePersonaAliasTableStatement(selectedPlatform));
617  stmt.execute(getCreatePersonaMetadataTableStatement(selectedPlatform));
618  stmt.execute(getCreatePersonaAccountsTableStatement(selectedPlatform));
619 
620  return true;
621  }
622 
623 
630  static String getCreateAccountTypesTableStatement(CentralRepoPlatforms selectedPlatform) {
631 
632  return "CREATE TABLE IF NOT EXISTS account_types ("
633  + getNumericPrimaryKeyClause("id", selectedPlatform)
634  + "type_name TEXT NOT NULL,"
635  + "display_name TEXT NOT NULL,"
636  + "correlation_type_id " + getBigIntType(selectedPlatform) + " ,"
637  + "CONSTRAINT type_name_unique UNIQUE (type_name),"
638  + "FOREIGN KEY (correlation_type_id) REFERENCES correlation_types(id)"
639  + ")";
640  }
641 
648  static String getCreateConfidenceTableStatement(CentralRepoPlatforms selectedPlatform) {
649 
650  return "CREATE TABLE IF NOT EXISTS confidence ("
651  + getNumericPrimaryKeyClause("id", selectedPlatform)
652  + "confidence_id integer NOT NULL,"
653  + "description TEXT,"
654  + "CONSTRAINT level_unique UNIQUE (confidence_id)"
655  + ")";
656  }
657 
664  static String getCreateExaminersTableStatement(CentralRepoPlatforms selectedPlatform) {
665 
666  return "CREATE TABLE IF NOT EXISTS examiners ("
667  + getNumericPrimaryKeyClause("id", selectedPlatform)
668  + "login_name TEXT NOT NULL,"
669  + "display_name TEXT,"
670  + "CONSTRAINT login_name_unique UNIQUE(login_name)"
671  + ")";
672  }
673 
680  static String getCreatePersonaStatusTableStatement(CentralRepoPlatforms selectedPlatform) {
681 
682  return "CREATE TABLE IF NOT EXISTS persona_status ("
683  + getNumericPrimaryKeyClause("id", selectedPlatform)
684  + "status_id integer NOT NULL,"
685  + "status TEXT NOT NULL,"
686  + "CONSTRAINT status_unique UNIQUE(status_id)"
687  + ")";
688  }
689 
690 
697  static String getCreateAccountsTableStatement(CentralRepoPlatforms selectedPlatform) {
698 
699  return "CREATE TABLE IF NOT EXISTS accounts ("
700  + getNumericPrimaryKeyClause("id", selectedPlatform)
701  + "account_type_id integer NOT NULL,"
702  + "account_unique_identifier TEXT NOT NULL,"
703  + "CONSTRAINT account_unique UNIQUE(account_type_id, account_unique_identifier),"
704  + "FOREIGN KEY (account_type_id) REFERENCES account_types(id)"
705  + ")";
706  }
707 
714  static String getCreatePersonasTableStatement(CentralRepoPlatforms selectedPlatform) {
715 
716  return "CREATE TABLE IF NOT EXISTS personas ("
717  + getNumericPrimaryKeyClause("id", selectedPlatform)
718  + "uuid TEXT NOT NULL,"
719  + "comment TEXT NOT NULL,"
720  + "name TEXT NOT NULL,"
721  + "created_date " + getBigIntType(selectedPlatform) + " ,"
722  + "modified_date " + getBigIntType(selectedPlatform) + " ,"
723  + "status_id integer NOT NULL,"
724  + "examiner_id integer NOT NULL,"
725  + "CONSTRAINT uuid_unique UNIQUE(uuid),"
726  + "FOREIGN KEY (status_id) REFERENCES persona_status(status_id), "
727  + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
728  + ")";
729  }
730 
737  static String getCreatePersonaAliasTableStatement(CentralRepoPlatforms selectedPlatform) {
738 
739  return "CREATE TABLE IF NOT EXISTS persona_alias ("
740  + getNumericPrimaryKeyClause("id", selectedPlatform)
741  + "persona_id " + getBigIntType(selectedPlatform) + " ,"
742  + "alias TEXT NOT NULL, "
743  + "justification TEXT NOT NULL,"
744  + "confidence_id integer NOT NULL,"
745  + "date_added " + getBigIntType(selectedPlatform) + " ,"
746  + "examiner_id integer NOT NULL,"
747  + "FOREIGN KEY (persona_id) REFERENCES personas(id),"
748  + "FOREIGN KEY (confidence_id) REFERENCES confidence(confidence_id),"
749  + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
750  + ")";
751  }
752 
759  static String getCreatePersonaMetadataTableStatement(CentralRepoPlatforms selectedPlatform) {
760 
761  return "CREATE TABLE IF NOT EXISTS persona_metadata ("
762  + getNumericPrimaryKeyClause("id", selectedPlatform)
763  + "persona_id " + getBigIntType(selectedPlatform) + " ,"
764  + "name TEXT NOT NULL,"
765  + "value TEXT NOT NULL,"
766  + "justification TEXT NOT NULL,"
767  + "confidence_id integer NOT NULL,"
768  + "date_added " + getBigIntType(selectedPlatform) + " ,"
769  + "examiner_id integer NOT NULL,"
770  + "CONSTRAINT unique_metadata UNIQUE(persona_id, name),"
771  + "FOREIGN KEY (persona_id) REFERENCES personas(id),"
772  + "FOREIGN KEY (confidence_id) REFERENCES confidence(confidence_id),"
773  + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
774  + ")";
775  }
776 
783  static String getCreatePersonaAccountsTableStatement(CentralRepoPlatforms selectedPlatform) {
784 
785  return "CREATE TABLE IF NOT EXISTS persona_accounts ("
786  + getNumericPrimaryKeyClause("id", selectedPlatform)
787  + "persona_id " + getBigIntType(selectedPlatform) + " ,"
788  + "account_id " + getBigIntType(selectedPlatform) + " ,"
789  + "justification TEXT NOT NULL,"
790  + "confidence_id integer NOT NULL,"
791  + "date_added " + getBigIntType(selectedPlatform) + " ,"
792  + "examiner_id integer NOT NULL,"
793  + "FOREIGN KEY (persona_id) REFERENCES personas(id),"
794  + "FOREIGN KEY (account_id) REFERENCES accounts(id),"
795  + "FOREIGN KEY (confidence_id) REFERENCES confidence(confidence_id),"
796  + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
797  + ")";
798  }
799 
800 
809  static boolean insertDefaultPersonaTablesContent(Connection conn, CentralRepoPlatforms selectedPlatform) {
810 
811  try (Statement stmt = conn.createStatement()) {
812  // populate the confidence table
813  for (Confidence confidence : Persona.Confidence.values()) {
814  String sqlString = "INSERT INTO confidence (confidence_id, description) VALUES ( " + confidence.getLevelId() + ", '" + confidence.toString() + "')" //NON-NLS
815  + getOnConflictDoNothingClause(selectedPlatform);
816  stmt.execute(sqlString);
817  }
818 
819  // populate the persona_status table
820  for (PersonaStatus status : Persona.PersonaStatus.values()) {
821  String sqlString = "INSERT INTO persona_status (status_id, status) VALUES ( " + status.getStatusId() + ", '" + status.toString() + "')" //NON-NLS
822  + getOnConflictDoNothingClause(selectedPlatform);
823  stmt.execute(sqlString);
824  }
825 
826  } catch (SQLException ex) {
827  LOGGER.log(Level.SEVERE, String.format("Failed to populate default data in Persona tables."), ex);
828  return false;
829  }
830 
831  return true;
832  }
833 
841  static boolean insertDefaultAccountsTablesContent(Connection conn, CentralRepoPlatforms selectedPlatform) {
842 
843  try (Statement stmt = conn.createStatement();) {
844 
845  // Populate the account_types table
846  for (Account.Type type : Account.Type.PREDEFINED_ACCOUNT_TYPES) {
847  if (type != Account.Type.DEVICE) {
848  int correlationTypeId = getCorrelationTypeIdForAccountType(conn, type);
849  if (correlationTypeId > 0) {
850  String sqlString = String.format("INSERT INTO account_types (type_name, display_name, correlation_type_id) VALUES ('%s', '%s', %d)" + getOnConflictDoNothingClause(selectedPlatform),
851  type.getTypeName(), type.getDisplayName(), correlationTypeId);
852  stmt.execute(sqlString);
853  }
854  }
855  }
856 
857  } catch (SQLException ex) {
858  LOGGER.log(Level.SEVERE, String.format("Failed to populate default data in account_types table."), ex);
859  return false;
860  }
861 
862  return true;
863  }
864 
874  static int getCorrelationTypeIdForAccountType(Connection conn, Account.Type accountType) {
875 
876  int typeId = -1;
877  if (accountType == Account.Type.EMAIL) {
878  typeId = CorrelationAttributeInstance.EMAIL_TYPE_ID;
879  } else if (accountType == Account.Type.PHONE) {
880  typeId = CorrelationAttributeInstance.PHONE_TYPE_ID;
881  } else {
882  String querySql = "SELECT * FROM correlation_types WHERE display_name=?";
883  try ( PreparedStatement preparedStatementQuery = conn.prepareStatement(querySql)) {
884  preparedStatementQuery.setString(1, accountType.getDisplayName());
885  try (ResultSet resultSet = preparedStatementQuery.executeQuery();) {
886  if (resultSet.next()) {
887  typeId = resultSet.getInt("id");
888  }
889  }
890  } catch (SQLException ex) {
891  LOGGER.log(Level.SEVERE, String.format("Failed to get correlation typeId for account type %s.", accountType.getTypeName()), ex);
892  }
893  }
894 
895  return typeId;
896  }
897 }
static String correlationTypeToReferenceTableName(CorrelationAttributeInstance.Type type)
static String getCreateCasesTableStatement(CentralRepoPlatforms selectedPlatform)
static String getCreateReferenceSetsTableStatement(CentralRepoPlatforms selectedPlatform)
static String getCreateCorrelationTypesTableStatement(CentralRepoPlatforms selectedPlatform)
static String correlationTypeToInstanceTableName(CorrelationAttributeInstance.Type type)
static String getNumericPrimaryKeyClause(String pkName, CentralRepoPlatforms selectedPlatform)
RdbmsCentralRepoFactory(CentralRepoPlatforms selectedPlatform, PostgresCentralRepoSettings repoSettings)
RdbmsCentralRepoFactory(CentralRepoPlatforms selectedPlatform, SqliteCentralRepoSettings repoSettings)
static String getCreateDbInfoTableStatement(CentralRepoPlatforms selectedPlatform)
synchronized static Logger getLogger(String name)
Definition: Logger.java:124
static String getOnConflictIgnoreClause(CentralRepoPlatforms selectedPlatform)
static String getReferenceTypesTableTemplate(CentralRepoPlatforms selectedPlatform)
static String getOnConflictDoNothingClause(CentralRepoPlatforms selectedPlatform)
static String getCreateOrganizationsTableStatement(CentralRepoPlatforms selectedPlatform)

Copyright © 2012-2022 Basis Technology. Generated on: Tue Aug 1 2023
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.