Release Notes for P4Report, the Perforce Reporting System Version 2008.2 December 2008 Introduction This document lists all user-visible changes to the Perforce Reporting System between its first release and 2008.2. For full P4Report instructions, please refer to the P4Report User's Guide, available from the Perforce documentation web page: http://www.perforce.com/perforce/technical.html. System Requirements You must uninstall any previous beta versions of P4Report prior to installing this release version. During the P4Report installation, the P4Report installer runs the Microsoft Data Access Components (MDAC) 2.6.1 update to ensure that the required data access components are up to date. If the MDAC installer does update the data access components, you will be required to reboot after installation. The MDAC update requires Internet Explorer version 4.01 (the associated system files) or higher. If you are using Internet Explorer 4.0 or earlier, you will be required to upgrade to at least version 4.01. P4Report requires Perforce version 2002.1 servers or higher. P4Report does not support the 'p4 login' command introduced in the 2004.2 server. If your Perforce Server security counter is set to the highest security level (security=3), you must first run 'p4 login' in the environment in which P4Report executes. P4Report has been tested with Microsoft Windows 2000/XP/2003/Vista. The Perforce ODBC driver has been tested with the following applications: - Microsoft Query (Office 2007) - Microsoft Excel 2007 - Crystal Reports v10.0 Other ODBC-compliant applications might also work, but are not tested or supported. The supported platforms for the ODBC driver are ntx86 and ntx64. The command line client is supported on ntx86, ntx64, linux26x86, linux26x86_64, freebsd60x86 and freebsd60x8x_64. Running the Crystal Reports Examples The source for the reports is included in the installer for both win32 and win64. This allows users to create stand-alone executables using the Crystal Reports Visual Studio package. The win32 installer includes pre-built versions of these executables. Perforce connection information for the example Crystal Reports is specified in an ODBC Data Source configuration. By default, P4Report creates a Data Source named "Perforce" that inherits Perforce connection information from the Windows registry. To view or modify the default Perforce data source settings, open up the Data Sources (ODBC) applet in the Windows Control Panel, select the User DSN tab, then select the Configure button. A dialog box will then be presented enabling you to enter Perforce environment variables, such as P4PORT. When you run one of the example Crystal Reports, P4Report attempts to connect to the default Perforce ODBC data source named "Perforce". If this data source does not exist (or has been renamed), you will be prompted with a logon dialog (provided by Crystal Reports) displaying a Server value of "Perforce". Enter the name of a valid P4ODBC Data Source into the Server text field. For example, if you have two data sources configured as follows: ODBC Configuration Name: Perforce1 Description: Production server P4PORT: fs1:1666 Name: Perforce2 Description: Test server P4PORT: play:1777 When you run one of the example reports, Crystal Reports displays a login dialog with the following fields (all empty except the "Server" field): Server: Perforce Database: User: Passwd: In the "Server" field, enter "Perforce1" or "Perforce2". If the Perforce server is offline or the P4PORT variable specified in the named P4ODBC configuration is invalid, the logon dialog won't accept the input and loops at the at the logon prompt when trying to Print the report. In this case, cancel the logon process and verify that your Perforce server is running and that your environment variables are correct. Using the P4SQL Command Line Client P4SQL can either be used interactively or to execute a file containing SQL statements. To execute a file containing SQL statements, specify the file name as an argument to P4SQL using the -i flag. For example: P4SQL -i queries.sql Each of the SQL statements in the file must be terminated by a semi-colon. P4SQL can also run queries specified on the command line, by specifying the -s flag. For example: P4SQL -s "select * from users;" For a full list of command line options, type 'p4sql -h'. When using the tool interactively, SQL statements can span more than one line. When a line is terminated with a semi-colon, the command can be executed. P4SQL supports the SQL-92 standard. String literals must be enclosed in single quotes, not double quotes. This practice is part of the standard but it is not enforced by some SQL parsers. Limits and Restrictions P4Report is intended to be used as a reporting tool. P4Report does not provide access to all Perforce metadata nor does it provide the performance required for data mirroring. P4Report can NOT perform the following types of queries: - What clients, branches, labels map a given file or directory? - What access levels does a user have to a given file or directory? Conditional expressions that use special Perforce virtual columns such as CHANGES.FILESPEC and FILES.REVSPEC have a couple of important limitations: You can only use the equals operator "=" to test these columns against another value. For example: Valid: SELECT * FROM CHANGES WHERE FILESPEC = '//depot/xxx/yyy/...' Invalid: SELECT * FROM CHANGES WHERE FILESPEC <> '//depot/xxx/yyy/...' (Cannot use the '<>' operator) You cannot use functions in conditional expressions containing virtual columns. For example: Valid: SELECT * FROM CHANGES WHERE FILESPEC = '//depot/xxx/yyy/...' AND USER = 'batman' Invalid: SELECT * FROM CHANGES WHERE FILESPEC = '//depot/xxx/yyy/...' AND LCASE(USER) = 'batman' (Cannot use the LCASE() function) File names, labels and other specifiers over 1024 characters long are truncated before being sent to the server. Truncation rarely causes problems, because 1024 is the maximum supported by the server. -------------------------------------------------------------------------- Major changes in 2008.2 #8552, #25163, #27446 The P4SQL command line client has been ported to work on Unix and Windows x86_64. #15889, #7713 Added tables for 'fstat' and 'resolved' data. #23271 Added a LIMIT keyword to limit the number of results or return the results after a specified offset. Not part of SQL-92. #30636 The command line client can now output results in XML using the -X flag. The '&', '<' and '>' characters in the results data are returned as "&", '<' and ">" respectively. Minor changes in 2008.2 #30037 The SubmitOptions and LineEnd fields are now available from the "clients" table for client and server versions 2008.2 and above. Bugs fixed in 2008.2 #30851 Labels and other specifiers over a fixed size can cause a crash. The size has been increased to support the server maximum of 1024. Identifiers over 1024 characters are truncated. #30291 Server counters that were strings instead of numbers are now correctly handled. The counter value is now a WVARCHAR instead of an INTEGER. #24208 Output from the serverAddress field of the "info" table is no longer corruptible. #25766 Improved unicode handling. Major changes in 2007.3 No new features. Bug fix release. Bugs fixed in 2007.3 #25266 Query against non-existent user can no longer crash client. #23590 Arguments for RIGHT() and LEFT() functions were reversed. Fixed. #20718 "Views" field in branch, client, label, and user specs no longer removed when running update queries using these specs. #20657 Paths in protections table with embedded space no longer truncated at first embedded space. Major changes in 2007.1 P4-Report now works with unicode- mode Perforce Servers. Bugs fixed in 2007.1/115868 (first release) #21801 "Client" label changed to "Workspace" in ODBC connection dialog. #17135 Internal error no longer reported when using .NET ODBC data adapter. #15858 GROUPS table now reports all group data, enabling joins on the 'username' column and reporting on 'maxresults' and related fields. #15574 Support connecting to Unicode servers. #15269 Fixed problem with ODBC and JDBC interaction. The getPrecision() call now works correctly. #14914 Comments now allowed in the input file for 'p4sql -i '. #13951 P4Report now recognizes tickets and works with 'p4 login'. #12072 Inconsistent results no longer reported when using sub-queries filtered on the 'filespec' virtual column. #9226 Internal stack limit increased to prevent client crash when more than 80 input arguments are provided. Bugs fixed in 2004.2/1977 #14890 Enable queries outside the context of the client root. #13386 Enable queries on submitted changelists that use a file path. #13020 Queries on Perforce entities using the % wildcard in a LIKE clause (for example 'LIKE job%') now work correctly. Bugs fixed in 2003.1/1125 #11235, #11274 P4Report no longer creates an implicit natural join when the USING or ON clause is omitted. The new behavior, as defined by SQL-92, is to report an error when this condition is detected. #10805 Queries on the FILES table now ignore any remote depots. You can force searching of remote depots by running: "select * from files where file like '//%'" #10577 String expression in the LIKE clause can now be any valid string expression not just a string literal. #10486 The LOCATE() function now copes with strings containing special characters such as '-'. #10423 P4Report now works with Java through the Sun ODBC-JDBC bridge. #10263 Perforce settings in ODBC configuration dialog now shown by default. #10262 Blank fields now allowed in ODBC configuration dialog. #10129 The REVSPEC column in the FILES table now allows all valid revision specifications, including: @client, #head, #have #8022 Example reports support CSV and HTML export format options. Bugs fixed in 2002.2/991 #9712 The LIKE clause now works when the string contains special characters including +, -, [, and ]. #9703 Added HOST and MAPSTATE fields to CLIENTS table. #9684 Field names that start with a number now work in the WHERE clause, although they must be enclosed with double quotes. Changed length of JOB column in FIXES table from 10 to 254. #9261 The timestamp in the FILES table is now reported correctly for filespecs.