RPOWER.SQL Product History
- Retro-fitted the bucket_template.xml file to work with older time classes (the 4, 6, 10, 3 type of classes instead of the 104001, 106001, 110001, 130001 type of time classes. The importer itself will work with both the older time classes as well as the newer ones.
- The importer will automatically create a new log if the current log size reaches 3 MB.
- This importer can now warehouse multiple totals for a single time class. It used to be that you could warehouse daily sales totals for a menu item, for example, but making that decision prevented the importer from being able to warehouse menu item sales by employee or customer. With this new importer, for a daily time class, you can now warehouse sales totals for a menu item, sales totals for menuitem by employee, and sales totals for menu item by customer.
- Database schema updated to 3.046 (released 09-10-2010)
- This importer once again works with MySQL. The last two releases had an issue where they could not generate indexes on tables in MySQL databases. It would throw an 'Object reference not found' error.
- Is now able to generate meta-data for time-classes. Meta-data is data created from other time-classed data, such as in the case of generating time-classed sales totals for store groups. The importer can now generate store group totals by combining the sales totals from each of the stores in the group. Currently, it can only generate meta-data for all stores. In the future, you will be able to create your own custom store groups, and the importer will generate meta-data for those store groups.
- There is now a MetaBucketGenerator dialog that can be reached from the pop-up menu by right-clicking on the icon in the Windows Notification Area on the bottom right, Select Use Meta-data, and then selecting Generate Meta-data...
- This importer provides a more targeted method for creating time-classed data. On the Export Warehoused Data dialog, you can now select the time-classes to generate data for. When the importer exports data for data warehousing, it will mark the data for import as a specific time_class. This way, you don't have to delete all you time-classed data to add a new time-class into the data warehouse tables.
- Database schema updated to 3.041 (released 07-13-2010)
- There is now an Export Data for Warehousing menu option which will export data from a transaction table for the explicit purpose of re-import to create warehoused data. You can select the table to export, the store, and the consolidation group.
- The SQL Server script has been updated. Certain reports call SQL queries built into the stored procedures created by the script. These SQL queries have been updated to handle multiple consolidation group parameters. They used to only support a single consolidation group parameter.
- Re-worked RPData.SQLManager and RPData.SQLDatabaseCommander so that the ExecuteScalar, ExecuteCommand, and ExecuteReader methods can now handle SQL Server "Timeout expired" errors. The timeouts have been changed from 30 to 120 seconds, and if a timeout error occurs these methods retry the sql command after waiting 1 minute.
- The outer import loop has also been re-worked so that a non-fatal database error (such as a "Timeout expired" error doesn't cause the importer to hang on a single file. If a non-fatal database error is encountered and cannot be resolved, the import file is moved to failed, and the next file in the list is imported.
- Removed the last back quote from the name string which goes into the to_name field of the tbl_master_id table.
- This importer no longer removes and then re-creates the custom tables when it performs its pre-import steps.
- fixed a bug in the importer so that empty name collision fields are now being included into the "to name" string written to the to_name field of the tbl_master_id table.
- changed the way unmatched mids are handled. If the file throws an unmatched mid exception, it is thrown into the pending folder, unless the file was already in pending, in which case it is thrown into failed. Also, the importer no longer resets mids when requesting non-transaction data from FPOWER.
- Email settings have changed in that the pop3 settings are now gone, and only the smtp settings remain. The importer internally maintains default settings for smtp:
smtp email account: firstname.lastname@example.org smtp server: smtp.fatcow.com smtp port:587 smtp user: email@example.com smtp password: rpower123
These settings can be changed in the Data_importer.settings file, by setting usedefaults=no, and specifying any of the following: sitename, fromemail, fromsmtp, smtpport; Any thing not specified will use the default value.
useemail=yes -- yes or no, determines whether email is to be used usedefaults=yes -- yes or no, determines whether to use default smtp settings sitename=RPOWER DB Importer -- the name of the site or application (ex: Hale DB Importer, Lenny's Gift) firstname.lastname@example.org -- the email account email is being sent from fromsmtp=smtp.fatcow.com -- the smtp server email@example.com -- the email account to send email to
- added a command line switch: -i will cause the importer to ignore any warnings thrown by records that reference non-existent puns/mids. This comes in handy when importing historical data into a SQL Server database when there is a question about the referential integrity of the data.
- implemented performance enhancements for the time class import process
- Fixed a time classing bug which would throw a 'Key not found in dictionary' exception if the imported record had the delete flag set to true because records flagged to be deleted do not contain all fields, only primary key fields.
- Fixed a bug in the new time classing functionality dealing with missing fields in exported ticket records. If a ticket has been cancelled, because of a void or comp, RPOWER exports a different sub-set of record information than if the ticket was a completed transaction. The time classing function was having problems with this when trying to match the incoming record with already existing time class records.
- Fixed a bug dealing with apostrophes in strings for fields that are not primary and/or name collision fields.
- Fixed a bug dealing with resolving foreign mids which had been introduced in the previous version.
- Fixed a bug in the importer where a character also used as an escape character (\ and ') were being used too many times. In MySQL you would see too many \ 's, and in SQL Server you would see too many ' 's. This would cause problems with the data the importer routinely returns to RPOWER after a close day or intraday import because item names would not match up.
- Fixed a bug that dealt with finding an item in the cross-reference table. It would attempt to read a value from the table without actually reading from the table. It would cause an incomplete record to be read from the import document.
- Fixed a bug that wrote an empty exception node to the end of the import document when there was a record import error (as opposed to a document mid scan error). The importer now writes out a TABLE EXPORT ERROR message containing the error and the record which caused the error to occur.
- Fixed a bug in the code that finds the maximum mid in the import file which was causing an 'Object too small or too big for an Int32'.
- The importer now generates a PUN (Publicly Unique Number) if the importer gets a new LID.
- The importer now translates MIDs to other MIDs when
- made the default value for foreign mids 1 for NONE. The previous default value, 3, meant UNKNOWN, which RPOWER sends when it believes there should be a foreign mid but doesn't know what the value should be. This will fix new databases.
- Inserted a fix for older databases which forces any foreign mid to be 1 unless a value is sent from RPOWER.
- the default value for foreign mid fields is now 3
- Fixed a bug dealing with connecting to SQL Server databases. Localhost was being resolved to 127.0.0.1 (loopback interface) and SQL Server 2008 does not allow connections over the loopback interface
- Is now compatible with SQL Server 2008
- Now compatible with new MySQL ODBC driver version 5.1. NOTE: MySQL ODBC driver version 3.51 WILL NOT work with this importer. ODBC 5.1 driver is backward compatible with previous versions of the database.
- a build to match the Hale & Hearty custom application build. No new features
- Fixed a bug that was escaping \ (backslash) in SQL Server when it only needed to do that for MySQL
- Fixed a bug that was causing an mid mis-match to not be listed in the export file or in the log file.
- This new importer is fully compatible with Micosoft SQL Server.
- The importer can now be told to delete data from transaction tables. In the Data_Importer.settings file, one can add the consolidation group number and a number designating the number of days to retain data. The importer will sweep the database deleting data older than the number of days it was told to keep data for. Detailed instructions can be found in the Data_Importer.settings file.
- IMPORTANT: This data importer requires .NET Framework 3.5
- The process of deleting records was broken out into a separate step. This was done to handle a specific situation happening at Blue Moon where a ticket record was being added during the intraday exports. When the day was closed, a deletion record was created for that record and then a new record was created to insert new totals. Because all the ticket records were turned into SQL commands before the deletion was run on that record, the insert record was turned into an update SQL command. This update command failed because the importer ended up deleting the record and then trying to update it. This is the first of three planned enhancements which will correct the handling of ticket records.
- Provided some support for PostgreSQL. The importer will not be able to handle major version updates to database tables, but will perform minor version updates. The importer can also export data from a PostgreSQL database and, of course, import data into a PostgreSQL database. This version of the importer was tested with PostgreSQL 8.3 which, by the way, is the first PostgreSQL version compatible with Microsoft Vista.
- Retro-fitted the importer so that it works with older schemas. There is a small amount of touch-up to do to older schemas, such as ensuring that all plain FKs are changed to FK0As, and all INs need to be suffixed with a group number and sequence letter. Example: IN -> IN1A. Also, sizes can be removed from bigint, tinyint, bool, and int data types.
- To support the retro-fitting mentioned above, the RPOWER Database Importer has lost some backward compatibility with older versions of MySQL. It now is only compatible with MySQL versions 5.0.2 and later.
- Reworked the SQL Server code so that the importer can now handle a major version change on a SQL Server database table.
- Fixed a bug during database generation which limited the number of indexes or foreign keys to 9. Up to 99 (inclusive) indexes and foreign keys can be added to tables now.
- Fixed a bug where the importer would continuously write "Updating database..." and "Finished update" to the log file until the importer imported its first file
- The Export Database process now begins each export file with a store record, a requirement for the database importer to import any data from an export file.
- Fixed a bug that was keeping time clock records from updating. If the store_mid was an lid, the importer would not be able to find the record in the target table and it would try to insert a new record into the table but fail silently.
- A build for the RPOWER Database Sanitizer.
- The importer can now import data into SQL Server databases. There have been some changes made to the schema to facilitate this.
1) SQL Server does not support date fields. So, all date fields have been changed to date times. Plain Jane dates will import into a datetime field, they will just look like '2007-08-07 00:00:00'
2) Foreign keys now work like indexes. They take a grouping number and a position letter (ex: FK1A).
3) Indexes (which already contain grouping numbers and sequence letters) may now have a U appended to them (ex:IN1AU). The U indicates that this index is a unique index. In the current schema, this is a rare occurrence.
- The importer now takes an optional parameter: alternate path. The alternate path is a path to another installation of the importer that lives on a mapped drive. This was done to bypass .NET 2.0 security which will not permit an application to be run off a mapped drive.
The way to implement this functionality is to install the RPOWER Database Importer on the same computer as the MySQL database and then install it again on the mapped drive with the ServerIn diretory. Next, create a shortcut from the importer executable file sitting with MySQL. Right click that shortcut and select properties. Click the Target textbox and hit the End key. Next, hit the space key, and then enter the path to the importer executable directory (the directory path, do not terminate the directory path with the executable file name) sitting with the ServerIn directory. Click OK and run the importer. The importer will now use the ini file off the mapped drive as well as import files from the ServerIn directory on the mapped drive. It still uses the local log file and the local custom_imports directory, if it is there.
- A change was made to how the importer handles import situations where an import record's name matches with a record in the target table, but the record's mid does not match that in the target table. The old way of handling it was to create an xref record with the change and then update all records in tables referencing that record and then delete the old record. Now, all that happens is that an xref record is created so that RPOWER gets the mid correct.
- This version of the importer is able to email RPOWER, or anyone else specified in the email list, whenever errors occur or a store does not import that day. This is so that we can provide timely (and invisible) support to our customers.
- The valid DATACNX api_version numbers are now read from the Data_Importer.settings file.
- A Data_Importer.settings file was created to hold configuration settings that are not set at runtime. The valid DATACNX api_version numbers are kept there as is email configuration settings and settings for the 'Store Roll Call' functionality.
- The importer now has a 'Store Roll Call' capability where each night it checks to see which stores have exported data. By default, it checks around 3am (this is configurable). If a store does not export data by the configured time, an email is sent to RPOWER alerting the developers there that a store has not exported data.
- The date in the tbl_idxref records which are sent back to RPOWER is now properly formatted.
- When updating database tables, the importer now recognizes a minor update and a major update. A minor update is when the minor version number changes, for example, from 1.300 to 1.400. A major update is when the major version number changes, for example, from 1.300 to 2.300. During a minor update, the importer generates an alter table statement and alters the column or index. During a major update, the importer will actually export the table's data, delete the old table, add the table with the new schema, and then import the data.
- A bug was fixed which caused the importer to overlook files in the pending directory.
- Fixed a bug that placed the consolidation group into the cross-reference document's to_name field. That field should not contain the cg, but it should contain all other name collision fields.
- This version of the importer supports the new file names of the RPOWER message files (the messages: reset mids, reset export flags, and export now).
- When errors are generated, they are much clearer now indicating not only the record where the error occurred, but also the field that caused the error.
- The importer now has the ability to force RPOWER to export its data immediately instead of waiting for the usual export which occurs during RPOWER's close of day processing.
- Fixed a bug in the code where names are the same but mids are different. The problem was that the wrong mid was being placed in the from_id and to_mid fields in the tbl_master_id table. This was causing the mid in the database to be changed to the mid coming in from RPOWER. This has been fixed, and now the mid in the database is placed in the to_mid field so that it is broadcast back out to RPOWER. This way mids are never changed in the database.
- The verification step will now check for an mid not only against a target table but also against the tbl_master_id table. If the mid can be found in the tbl_master_id table, the importer will insert that value into the document. If a mid cannot be found, nothing is done, and the document will be moved to failed.
- Fixed a bug in the importer when handling import records where the names are the same but the mids are different. Previously, this case would create an entry for the cross-reference table, but, errorneously, did not update the record in the database table. It now creates that update statement.
- Modifications were made to the import process so that all lids are checked against the tbl_master_id table and a speed enhancement was done so that fms are not checked when importing data from tables with no mids (such as tbl_ticket, tbl_menuitem_sales, tbl_ticket_payment) since they are already checked during the verification step for those tables.
- Updated RESET_EXPORTS.xml, RESET_MIDS.xml, and EXPORT_NOW.xml to work with new RPOWER DATACNX API version 126.96.36.199, in app version 6.092D.
- Fixed a bug which did not regulate the number of digits in the X-Ref file name (the file which gets exported back to RPOWER after an import. All X-Ref file names should now be the same length.
- The importer no longer checks for valid foreign mids in a record if that record is tagged for deletion.
- The importer can now discern between tbl_master_id records it has imported, and those it has not. It now only imports those records it has not imported before (the previous behaviour was to export all records in the tbl_master_id table).
- The tbl_master_id purge duration has changed from 3 to 8 days.
- when an LID in a non-mid fm table (a table like tbl_menuitem_sales) cannot be resolved, it sends the file into pending (the old behaviour was to send the file to failed). This allows the next first post to pick up this file and import it.
- Fixed some minor issues dealing with database updates. The importer now handles the fact that these files do not contain an rfx_post attribute. It did handle it until the 2007-03-02 build which introduced the bug.
- Database update files now begin with !UPDATE.
- The importer better handles potential import errors. It imports a file in three stages:
- Imports all the mid tables that do not have foreign mid columns.
- This step does not usually fail; if it does, the file is moved to failed.
- With a successful import of the data for this table, the imported elements are removed from the import file.
- Imports all the mid tables that do have foreign mid columns.
- If there is a failure in matching up an incoming lid, the file is moved to pending where it waits for the next post. It sends a "reset" file to RPOWER, telling it to reset its mids and export flags. All non-transaction data will be re-exported on its next extraction.
- If there is a failure in matching up an mid, the file is moved to failed.
- If successful, the imported elements are deleted from the import file.
- Verifies all the foreign mid fields in tables that do not contain a mid column. It then imports the data into the database.
- If there is a failure with either a lid or a mid, the file is moved to failed. # Fixed a bug dealing with printing the x-ref file that gets returned to RPOWER.
- Imports all the mid tables that do not have foreign mid columns.
- Fixed a bug which would not set the from_local value in the tbl_master_id table.
- It the importer encounters files that are either of zero size or are truncated, it deletes them.
- A bug that was not allowing a custom import to run was fixed, as was a bug that was causing the from_local attribute to not print in the cross-reference file.
- A bug was fixed in the version number validation. Because the validator no longer looks at the minor version number, it can check to see if the current minor version fits into a range of acceptable major versions. Previously, the validator had to check each minor version separately.
- This version requires a new version of the hh_tables.xml file. The format was changed to be similar to that of the RPOWERSchema.xml file. One way to tell that the hh_tables.xml file is in the old format is if you get an "Object reference not set to an instance of an object" error appearing in the log file after the "checking database" line.
- The update capability was reworked. It now will export the data into multiple files which it creates under a ServerIn/Update directory. Once all the data has been exported to these files, it moves these files up one level to the ServerIn directory and will import those files. Doing this makes it much easier to export data from large databases.
- The valid version numbers used in validating import files and database schema files have been moved to a versions.ver file. This makes it possible to add version numbers without re-building the importer application. This is especially useful in the case of a database schema change that requires no changes to the importer code.
- In accordance with the changes made to the update capability, the Export... menu option has also changed. Instead of a Save File dialog, the client now is shown a Select Folder dialog which will allow him/her to select a directory to save the exported data to.
- The importer now runs at Below Normal priority. Previously, only the import thread had been set to run at Below Normal priority. Now the whole application runs at a Below Normal priority so that it doesn't consume too many processor cycles when it imports a large file.
- This version of the importer handles the situation where an incoming LID cannot be matched to an existing MID in the database. When the importer encounters a file with an unmatchable LID, that file is moved to a pending folder. The importer then sends a reset mid message and a reset export flags message to the restaurant. At the close of day, the restraunt's version of RPOWER exports a "clean" file — a file without any mids in it. The database importer matches all those records to already existing records in the database and then imports the file that had been moved to the pending directory.
- This version includes version validation for import files and the database schema.
It does this by checking the XyzzyHeader api_version in import files and the Database api_version in schema files. If the import file api_version does not match the api_version hard-coded in the application, that file is deleted.
- When checking in the tbl_master_id table for an MID match for an incoming LID, the application now guarantees it will pull the last used MID for that LID. This prevents older MIDS from being used causing slight database thrashing.
- The database table creation has been changed to use MyISAM tables. Previous versions created InnoDB tables which enforced foreign key constraints. We no longer need to enforce foreign key constraints. With the greater understanding we have of SQL and MySQL, it is believed we can now formulate our own queries and use those in Crystal Reports.
- supports changes to the tbl_master_id table schema
- supports the from_name field, a new field which is being used to track name changes
- the name of the date field has changed to dttm
- resolved the issue where the RPOWER Database Importer could not create its own ini file
- check to see if archive directory exists. If not, the importer creates it.
- the importer now can handle tbl_time_clock export records that do not contain a record id. It will now generate a record_id for that export record using the emp_mid + job_mid + in_dttm and calculates a hash value off these strings.
- the importer now has the ability to update transaction tables (already had the ability to update non-transaction tables).
- Data Importer now works with .NET Framework 2.0.
- Changed the tbl_master_id table definition: renamed the name column to to_name
- Set size of to_name to 62 characters
- Data Importer now works with .NET Framework 2.0.
- Changed the tbl_master_id table definition: renamed the name column to to_name
- Set size of to_name to 62 characters
- fixed a bug with the export portion of this program. It now checks to see if the column list is null instead of checking to see if it is empty. Keeps me from having to create an empty ArrayList just to send into the export method.
- importer now supports the I_delete parameter. Including this parameter in a record will delete that record from the database.
- using the new UPDATEStatementGenerator
- added a DELETEStatementGenerator
- name change issues are resolved. Problem was that one of our customers was seeing a product list report that was 100+ pages long. This was because old data was not cleared out of the database. The way this was fixed was to first import all the data in the import file. As the import is occurring, the importer checks for incoming records that might have a name that matches a record already in the database, but the incoming record itself contains a mid for another record in the target table. The assumption is that the incoming record contains an old mid. The importer, once it identifies an incoming record as containing an old mid, then creates SQL update statements for each of the 'kin' tables (those tables which reference the target table's mid field) and also generates a delete statement for the database record with the old mid. After the file is fully imported, the importer then updates the kin tables and deletes the old record in the target table.
- importer now runs at the Below Normal priority
- using the new SELECTStatementGenerator
- DBExporter-GenerateExportXmlDocument: fixed a spelling mistake which would keep datetime fields from being converted properly
- added in support for custom tables. The importer can now call other applications to do post-import operations on the database.
- Changed preferences so that it can take a server name. Once the Save button is clicked, the importer will then ping server to ensure that it exits out on the network.