Administration Screen
Administration Screen
This is a special screen that can be loaded into any version of ITrack (Pro/AX/EE/etc) that allows the user to perform advanced, power-user functions. This screen was developed initially to allow ISoft employees to perform advanced operations and view in-depth information about specific datasources. Some of the features of this screen may be applicable to DBAs, ITrack Enterprise administrators, or other power users.
This screen has the special ability that it dynamically loads other extensions that contain additional administrative tools which are turned into property pages on the screen. The current Administration Screen extensions are listed below with a description of their purpose, who would use them, and what features they implement.
Administration Extensions
EEAdministration.dll
This extension is the Administration screen itself, and is required for the use of any Administrative tools. In addition this extension contains the following tool pages.
Constraint Verification
This page is designed to allow a troubleshooter or ITrack manager to run a set of store constraint checks across their database. This is sometimes called a 'database sanity check'. This screen allows the user to run any number of stored queries (called constraints) against their database. Every violation is reported to the user, along with the severity of the issue, its total impact (which is constraint specific), date, etc.
For example, a common constraint might be to verify that every time a customer WO was invoiced, that the invoice line price (saleosrderline.price) matches the job subtotal. If this was off in any case, that would be a problem, as the Sales/COGS accounts on the invoice wouldn't be hit for the same amount as the work order expected (which could leave value in the WIP accounts).
To add new constraints to this system, an administrator can add queries to the dataconstraint table. The result set and query must be formatted in a specific way in order to utilize the full capability of the interface. For a tutorial on creating data constraints, contact ISoft Data Systems for assistance.
EEDatabaseUtilities.dll
This extension contains 3 tool pages that give the user certain advanced database tools. This extension will usually only ever apply to ISoft employees.
File Import
This page is an extremely advanced file importing tool. Using this interface, the user can add hundreds of .csv (comma separated value) or .tdv (tab delimited value) spreadsheets into the database, with automatic data type conversion, data integrity verification, automatic column matching, and data preview. The screen is capable of simply running queries directly against the destination database, or it can generate a .sql script to be run later. Mass-data insertion can be configured to use either manual bulk-insert statements (limited to stay withing destination server's MAXIMUM_PACKET_SIZE) or LOAD DATA INFILE statements.
If someone ever needs to be able to perform initial data imports for new installs, this screen is designed to facilitate it.
Schema Browser
This page allows the user to visually explore the schema of the currently loaded database. This tool was created for internal ISoft use only. This screen has several 'modes' which allow rapid completion of common functions on a specific database schema.
The main feature of the screen is the ability to see a tree of database/tables in the current database server's schema (to make this quick, items are loaded into memory as needed). The user can see table size, as well as view all columns in that table. A specialized MySQL language parser is used to scan and break apart the table creation statements. What the user can do with this interface depends on what mode they are in. The two currently supported modes are described below.
ITObject Mode
This mode allows the user to rapidly generate an MFC C++ object that is capable of translating the specified table entity into an ITObject (and vice versa). These ITObjects allow for more readable and more reusable user interface code. ITObjects are capable of loading one or more object from the database into memory, tracking changes, and serializing those changes back into the table. In the classic Model/View/Controller structure, these objects represent the Model as well as part of the Controller.
This screen makes the programmer's life easier by automatically parsing the table, guessing which columns are keys (primary or external), guessing the class names of keys, guessing the proper data type for fields, guessing the properly formatted variable name for a column. Automatic table and column name formatting is performed through a dictionary of common ITrack terms. This way, scanning a new table, the screen is already quite certain of the properly formatted variable/function names that correspond to the columns it detected. The user can choose which fields to include in the ITObject, customize/correct data type, variable name, etc.
When the user is finished preparing an object class, they can hit export, which will create <ClassName>.cpp and <ClassName>.h where <ClassName> is a customizable version of the table's name, run through the same variable name parser used on the columns.
Trigger Mode
This mode is used to rapidly generate MySQL trigger creation scripts that log changes made to any number of fields in any number of tables. The impetus of this mode is that often, a customer or company wishes to log all changes made against one or more fields in one or more tables in a MySQL database. Triggers that log said activity can be written by hand, but can be quite tedious, especially if a large number of fields or tables are involved.
Currently, this mode allows the user to check any number of tables within a database (and any number of columns in those tables) to log the changes on. The user is given an opportunity to customize the name of the 3 triggers that are generated for each table being logged this way. The default trigger name is "t_
_<timing>_<event>" where table name is the name of the table being logged, timing is either pre or post (depending on whether the trigger happens BEFORE or AFTER insert/update/delete) and event, which represents the type of transaction tripping the trigger (either UPDATE/INSERT/DELETE). Along with picking the name of the 3 triggers to be created for each table, which tables to add triggers for, which columns in those tables will be logged, they can also choose the name of the output file to write the .sql script to. This script can be run through a MySQL CLI or visual interface such as SQLYog. The SQL script creates the tables log_change and log_changegroup if they don't exist and then creates 3 triggers for each table that has at least one column being logged. If the database already has triggers at the desired timing and event (AFTER/BEFORE INSERT/UPDATE/DELETE), those triggers are read into memory, parsed, and the new trigger content is added to them. The trigger content generated by this system is always inserted between the XML-like tags (in SQL block-comments) /*<AUTO_TRIGGER>*/ and /*</AUTO_TRIGGER>*/. This way, the system can tell automatically generated triggers from manual ones. The opening tag supports attribute tags that hold some header data about the triggers that were generated. Currently, the supported attributes are:- fields. This attribute is followed by a comma separated, backtick enclosed list of SQL columns that are being logged by this trigger. Changing this will not affect the trigger, but is useful for loading the triggers back onto the screen again.
- created. This attribute is the ISO (mysql formatted) date-time that the triggers were generated (not necessarily when they were run on the database).
Databases
This page is currently in progress. This tool is designed for internal ISoft use only. It allows a developer or DBA to manage many database servers from a single location. The user can store connection information as well as network framework information for any number of database servers.
The user can connect to any number of these servers and run one or more queries against any number of these servers. This can be especially helpful when needing to run database updates across many installations at the same time.
Troubleshooting
If the Administration Screen shows in the main menu, but a blank screen loads for you in ITrack, then try changing your [dll] section in host.ini to look like this:
[dll]
total=5
dll1=ITrackOnline
dll2=AppraisalExtension
dll3=EBayExtension
dll4=Administration
dll5=administratedatabases
