SQL server database usage

The database contains all administration, configuration, and reporting data. This database contains all the table definitions, indexes, constraints, and database stored procedures used by the application.

The application uses database tables in five ways:

  1. To store data that is independent of a particular content scan job.

    These types of tables are named in mixed case with the name denoting the data that is stored in the table; for example: Job, UserInfo.

  2. As a template for tables that are used to create tables that store data for a particular content scan job.

    These tables have the suffix _JII_ or _NSI_ or _SJI_ appended to their names; for example: RepEntity_JII_, Vulnerability_JII_.

  3. To store data for a particular content scan job iteration.

    These tables are created (based on the corresponding template table's definition) the first time that the job is run. The table names consist of the template table name with the _JII_ or _NSI_ or _SJI_ suffix replaced by the job identifier and job iteration; for example: RepEntity_32_0, Vulnerability_32_1. The data stored in these tables is temporary.

  4. To store aggregated data from individual scan jobs.

    The tablename_JR job in the reference table identifies the scan job that found the object.

  5. To store default options for a particular content scan job.

    This is a single table, per folder item, that is created the first time the folder item is created. The table name is FolderItemOption, followed by the item identifier and the string _D; for example: FolderItemOption_310_D.

Similarly, there are three types of stored procedures:

  1. Stored procedures that perform operations that are independent of any particular content scan job.

    The names of these stored procedures being with the prefix wp_ and are named according to the operation that they perform; for example: wp_FolderItem_Delete, wp_Folder_Select.

  2. Template stored procedures that are used to create stored procedures that perform an operation on the data for a particular content scan job.

    The names of these stored procedures begin with the prefix wt_; for example: wt_RepEntityInsert, wt_VulnerabilityInsert.

  3. Stored procedures that perform operations on the data for a particular job iteration.

    These stored procedures are created from the template stored procedures the first time an item is run. The name of the stored procedure begins with the prefix wi_, followed by the item identifier, item iteration, and operation name; for example: wi_21_0_RepEntityInsert, wi_21_1_VulnerabilityInsert.

Referential integrity in the application is performed at the database level. All foreign key constraints are defined in the database.