Release Notes for P4toDB, the Perforce Integration with SQL Databases Version 2017.1 Introduction This document lists all user-visible changes to P4toDB, starting from the first release of 2009.2. P4toDB replicates Helix Server metadata into an SQL database, enabling you to use your preferred relational database for reporting and analysis. This approach reduces the load on the Helix server for these activities. Perforce numbers releases YYYY.R/CCCCC, for example, 2009.1/206108. YYYY is the year; R is the release of that year; CCCCC is the bug fix change level. Each bug fix in these release notes is marked by its change number. Any build includes (1) all bug fixes of all previous releases and (2) all bug fixes of the current release up to the bug fix change level. Please send all feedback to support@perforce.com. Requirements * P4toDB requires a Helix server at version 2016.1 or higher. * Helix server checkpoints and journals must be stored in uncompressed form. They must have been created with the same version of the server that is being used for the replication. * P4toDB is a Java program. It requires a standard JRE 7 or later and runs on the following platforms: - Windows 7/8/10, 2008/2012/2016 on Intel (x86, x86_64) - Linux 2.6 Intel (x86, x86_64) - Mac OS X 10.5, 10.6/... (Mac/Carbon) * Supported target databases are: Oracle 11, MySQL 5, MS SqlServer 2012/2017, DB2 9/11, Derby 10.7.1/10.12.1, and Postgres 9/10. The vendor's JDBC version 4 driver must be used. Installation To install P4toDB, unzip the distribution archive into a location of your choosing. For any target databases you plan to use, put the JDBC 4 driver jar in P4toDB's "lib" directory. Configuration For detailed documentation, see the README.txt file, command-line help, and the Perforce Knowledge Base (http://answers.perforce.com/) Platform-specific wrappers are the supported interface to the underlying JAR file, but for the help output, invoking it directly is fine. Wrapper: p4todb.[bat|sh] --help Directly: java -jar p4todb.jar --help The instructions there will tell you where to go next. P4toDB requires a configuration file for its settings and sufficient SQL privileges to create tables, etc. -------------------------------------------------------------------------------- Major new functionality in 2017.1 None Minor new functionality in 2017.1 None Bugs fixed in 2017.1 #1603221 (Bug #91852) Database tables with periods in the name (e.g. 'db.have.rp') can now be replicated. The format of the P4TODB_CFG.tables column has been changed to accommodate this. Users wishing to keep their existing replicated data must manually update the column's format. #1599996 (Bug #74283) The p4todb_cfg.tables column has been widened to account for new Helix server tables. #1599996 (Bug #89754) P4toDB has been updated for changes to recent Helix server schemas. -------------------------------------------------------------------------------- Major new functionality in 2012.1 None Minor new functionality in 2012.1 #908178 (Bug #73050) Schema generation can now use the epoch format for timestamps instead of an SQL timestamp. See the CONVERT_TIMESTAMPS option. Bugs fixed in 2012.1 None -------------------------------------------------------------------------------- Major new functionality in 2012.1 #411375 (Bug #51933) P4toDB->P4D communication can now be encrypted via the P4SSL config key. This requires a 2012.1 server. Minor new functionality in 2012.1 None Bugs fixed in 2012.1 #430112 (Bug #53360) Fixed the caching of domainID values for the lrxref table. -------------------------------------------------------------------------------- Major new functionality in 2011.1 None Minor new functionality in 2011.1 #289277 The 'init-and-quit' optional define has been moved to the command-line. #288923 The bulk loader now works with MySQL. #288541 (Bug #43587) Improved the the initial label-to-file cross-reference table population. #none Added support for Postgres 9, MSSQL 2008 and Derby 10.7. #286466 (Bug #43310) Add columns containing the last-replicated journal transaction time and server pid to the config table. #286239 (Bug #36164) Sped up journal processing. #282456 (Bug #42078) The bulk loader now accepts the '-I' flag to emit identity columns. Bugs fixed in 2011.1 #289840 (Bug #43761) The values for the error message indicating mismatched database and Perforce tables were swapped. #287872 (Bug #43556) When restarting after certain recoverable SQL exceptions, the wrong starting position could be used, resulting in duplicate or missing records. This has been fixed. #287055 (Bug #43501) When using identity columns and explicitly turning column prefixes off, the name of the identity column incorrectly had the word 'false' in it. #284556 (Bug #43143) Add an index on domain(type) for the branches/clients/labels views. #282459 (Bug #42091) No longer attempt to populate the label-to-file cross-reference table with the --build-lrxref flag if its configuration key isn't present. #282442,282455,284556 (Bug #42941, #42942, #43143) Identity columns are no longer included in the indexes, as they were interfering with index performance. -------------------------------------------------------------------------------- Major new functionality in 2010.1 #262545 (Bug #40530) Add an optional label-to-file cross-reference table to speed up certain queries. This only works on static (non-automatic) labels on normal depot files. #261210 (Bug #39650) Add convenience configuration-key options for common schema changes. One for treating CLOBs as VARCHARs (CLOB_AS_VARCHAR), and one for specifying the size (P4IDENTIFIER_LENGTH) of the maximum VARCHAR used with Perforce identifiers (paths, names, etc) Note that by using these settings, that data coming from Perforce may no longer fit into the SQL column (see the COL_TRUNC options.) They are disabled by default. #260549 (Bug #39982) Add optional identity/sequence columns to tables without pre-existing numeric keys such as db.rev. They are enabled by default. #260205 (Bug #36315) Add support for Apache Derby / JavaDB as a target database. The auto-generated jobs view has changed as a result of this. Minor new functionality in 2010.1 #264261 (Bug #040720) P4toDB jar file is now digitally signed. Certificate information can be verified by using the jarsigner utility. #262938 (Bug #40557) The optional-define "p4todb.dont_create_indexes" has been removed and replaced with the CREATE_INDEXES configuration-file key. #261592 (Bug #40312) Indexes are now created on all the keys in a table instead of having a separate index per key. #261376 Index creation is now delayed until checkpoint conversion has completed. #261368 (Bug #40304) Display the percentage of checkpoint conversion completed in the log file. Note that the progress is relative to the current position in the checkpoint relative to its total size, so if a subset of tables is being replicated, the percentage indicated will not necessarily be indicative of how much work is left to do. Also note that determining the size of the checkpoint will create some (harmless 'p4 export') errors in the Perforce server log file. This happens once during startup. #261128 (Bug #39519) Create upper-case identifiers (UPPERCASE_IDS) by default. The config table is now always upper-case. #261001 (Bug #40529) Change the default 'p4 logout' behavior so it does not logout unless the P4LOGOUT configuration key is set. #260998 (Bug #39519) Add support for specifying a target database schema (DB_SCHEMA.) #260750 (Bug #39519, #36950) Add support for custom column/table name prefixes. This change also renamed the configuration key DB_PREFIX to COLUMN_PREFIX for clarity. The generated branch/client/label view columns now inherit the column prefix setting (default or custom.) #260245 Added an extra index between the "key" and "attr" keys on the "bodtext" table when the auto-generated jobs view is being used. #260208 (Bug #37438) Moved the "updating state" log message from the FINER to FINE logging level, and the "committing" message from FINE to FINER. #259927 (Bug #40150) INSERT statements now qualify their column names. #259794 Two new configuration keys (COL_TRUNC*) were added to control what happens when incoming data exceeds the width of the target column. #259497 (Bug #40121) When errors occur, extra debugging info is now printed out. #259488 (Bug #39521) An INSERT statement to record the starting position of initial replication in the configuration table is now included in the --print-ddl output. #259453 (Bug #39282) Empty strings are now converted to a SQL NULL. #259449 Key columns are now defined as being 'NOT NULL'. Bugs fixed in 2010.1 #260230 (Bug #39859) Reduced the maximum size of identifier columns in the schema. They were too big before. I.e. VARCHAR(2000) -> VARCHAR(1024). #259496 (Bug #37821) Documented that the journal patch number must be less than the current journal number. #259494 (Bug #37611) The DB_TO_REPL and DB_NO_REPL configuration keys are now validated. -------------------------------------------------------------------------------- Major new functionality in 2009.2 None Minor new functionality in 2009.2 None Bugs fixed in 2009.2 #249469 (Bug #39281) Date fields would be stored incorrectly when using MSSQL. A checkpoint re-import is necessary to correct the data. #235979 (Bug #37884) Using a DB_PREFIX with upper-case letters and the DB_NO_REPL list could prevent replication from restarting. Prefixes now must not differ only by case when using multiple replication. #233339 (Bug #37231) The PID_FILE is now treated as a semaphore to prevent concurrent instances of P4toDB from using the same configuration. #233164 (Bug #37641) The LOB datatypes for MSSQL have been changed from the deprecated IMAGE/(N)TEXT to VARBINARY(max)/(N)VARCHAR(max). #233150 (Bug #37640,#37606) Various documentation improvements, such as moving the the JDBC driver connection examples out of the README and into the sample configuration. #233150 (Bug #37585) A non-default value for the PID_FILE variable is now treated as the explicit name for the PID file (no extra ".pid" appended.) #233150 (Bug #37463) The non-standard P4TICKETHASH variable has been removed. Tickets are now handled in the standard manner. #232924 (Bug #37627) The FETCH_DELAY variable is now correctly acknowledged. --------------------------------------------------------------------------------