Encompass Application Data Framework
In prior releases of Encompass, all user data was stored and managed in the "userdata" directory as a set of XML files, within each users directory. This data has been moved to a relational database, to afford higher reliability, scalability, and to support easier integration to usage reporting and analytics.
As of Encompass 2.3.x a Database is now required to store all of the following information.
1) User logins
2) Session logging
3) Query logging with result counts
4) User profiles
5) User Saved Queries
6) User Shopping Carts
7) Integrated User database (no used with SSO)
500MB - 1GB table space is required as a minimum starting point. Usage will grow with number of users accessing the system, and frequency and number of queries users make. Typical usage will see 1MB per user per year. however this can be highly variable. Additionally data can be culled, and archived.
External database support is via Oracle 10G and above, via JDBC connector. Support for Microsoft SQL Server is planned at a later date.
Encompass ships out of the box with embedded database, preconfigured, suitable for single node production deployments, test and development. The embedded database is not recommended for large scale production environments, where multi node support is utilized.
Perception Software as part of annual maintenance will assist the customers in migrating data from the file system to database. Perception will assist with migration scripts and verification of successful migration.
1. Gain access to an appropriate Oracle schema.
2. Execute db script for Oracle. The latest scripts are provided as part of an the Encompass 2.3.x release or Contact Support.
3. Configure adfdb.properties as part of the $ENCOMPASS_HOME/config directory.
adf.cp.maxActive=100 adf.cp.testOnBorrow=true adf.cp.validationQuery=select 1 from dual adf.db.type=ORACLE
These tables are assumed to be 100% under the control of the application, and should not be accessed directly. These tables may be useful for read/only access for reporting. Please contact Perception Software Support for details on properly querying these tables for reporting usecases. Tables are common to all database implementations.
Common Tables to all Features of Encompass
Encompass Engineering/ Product Navigation Specific Tables
EE_SHOPPING_LIST - data imported from the shoppinglists.xml, stores information about the user's shopping lists
EE_SHOPPING_LIST_ITEM - stores shopping lists items
EE_SHOPPING_LIST_ITEM_ATTRIBUTE - each shopping list's item consists of attributes (attribute name, attribute value pair), stores shopping list items attributes
EE_USER_TAG - stores user's tags, it was used in the older webtop, now it is not used, table for the future usage if we decide restore user's tags functionality
EE_SAVED_QUERY_FOLDER - data imported from the queries.xml, stores user's saved queries folders, each folder may contain queries
EE_SAVED_QUERY - stores user's saved queries
EE_USER_PROFILE_OPTION - stores common user's profile options, for example show_selection_dialog_on_login, active_search_results_left_panel_width, skid pdf preview properties
EE_SEARCH_PROFILE - data imported from the searchprofiles.xml file, stores user's search profiles
EE_BOOLEAN_FILTER - each search profile has set of boolean filters, stores search profiles boolean filters (example: Show Only Latest, Hide EOL Parts, Use Synonyms, Escape Queries... )
EE_FIELD_FILTER - each search profile may have set of field filters, stores search profiles field filters
EE_INCLUDED_VALUES - each field filter may have set of included values, stores field filters included values
EE_VIEW_PROFILE_GROUP - data imported from the viewprofiles.xml, stores user's view profile groups, each view profile group may contain view profiles
EE_VIEW_PROFILE - stores user's view profiles
EE_COLOR_SETTINGS - each view profile has color settings: for bom and for parts, stores view profiles color settings
EE_COLOR_CODING - each color settings consists of color codings, stores color settings color codings
EE_GRID - view profiles has set of grids, stores view profiles grids
EE_GRID_COLUMN - grid consists of columns, stores grid's columns
EE_SORT_CONFIG - each grid has sort config definition, stores grids sort configs
EE_SORT_COLUMN - sort config consists of sort columns, stores sort configs sort columns
P_USER - table for storing platform's users information
P_SESSION - table for storing platform's users sessions. Each session has login time, logout time, logout type and remote address. We have two logout types: LOGOUT - when user click Logout in the application, TIMEOUT - when session expired. Session is the most important table when it comes to the history, all actions are binded to the user's session.
P_ROLE - table for storing platform's/app roles. In case of EE we have two: ADMIN, DATA_ADMIN
P_ROLE_PLATFORM_USER - binding roles to the users
P_ACTION - table for storing action information. Each action has date, type, session and user. Is_error flag indicates if action had an error - this field is for future usage - not used yet (always false value). Following action types are used now: QUERY, GET_BOM_ITEM, GET_CHILDREN, PART_DETAILS, WHERE_USED, BOM_EXPORT, PART_EXPORT
P_ACTION_ERROR - table for storing action errors information. Not used yet - designed for future usage. Records are not inserted to this table.
EE_QUERY - table for storing queries information. Query is binded to the action. Query has query string and result count info. Normalized_query and query_context_type (Catalog, FreeText) are not used yet - have always NULL value, these fields was designed for the future usage. This table is used for storing queries - when user types query in the searchbox and performs search, but also other actions have foreign key to this table for storing queries info.
EE_BOM_EXPORT - stores information about BOM export actions. Table is connected to the query table for storing results count info.
EE_BOM_EXPORT_ITEM - stores information about BOM Export items actions - so info what cpns,revisions was exported in this ee_bom_export action.
EE_BOM_GET_CHILDREN - stores information about BOM Get Children actions - when we expand assembly item in the Bom Panel. Table is connected to the query table for storing query and results count info.
EE_GET_BOM_ITEM - stores information about get BOM item action. Action is performed when we enter into part details or change revision in the combobox and data is loaded to the bom panel. Table is connected to the query table for storing query and results count info.
EE_BOM_WHERE_USED - stores information about BOM Where used actions. Action is performed when we 'traverse up' in the bom panel. Table is connected to the query table for storing query and results count info. Worth to notice: there are a few bom where used actions saved per this action: one per each cpn, revision and one summary, which constains all the items in the ee_bom_where_used_item table, query (in this case nul)l and sum of the results count.
EE_BOM_WHERE_USED_ITEM - stores information about where used items for which we perform 'traverse up'.
EE_PART_EXPORT - stores information about part results export when user performs export of results from the results grid. Has the grouping_fields column (now comma separated) to store information about all export groups from fields come from. Please note: this column has always empty value - it seems that fields come from one group and the group name which comes from the client is just empty - not an error. May change in the future. Table is connected to the ee_query table for storing query and results count.
EE_PART_EXPORT_FIELD - stores information about fields which was exported, connected to the ee_part_export.
EE_PART_DETAILS - stores information about part details action. Table is connected to the ee_query to store query string and results count. Please note: we can enter part details in two ways: