Perforce 2008.2 P4Report User's Guide
<< Previous Chapter
Codeline Management
 Table of Contents
Index
Perforce on the Web
 Next Chapter >>
P4Report Functions

Appendix A
Database Schema and SQL Keywords

P4Report includes an ODBC driver that renders a subset of the information in the Perforce database as a set of tables. This chapter describes the SQL data types of the data returned by P4Report. You can use the information in this chapter to create SQL queries. Note that the P4Report engine adds some virtual columns for query purposes; these columns are not physically present in the Perforce metadata.

This chapter also includes a list of SQL keywords reserved by P4Report.

P4Report System Catalogs

The system catalogs are tables that contain data describing the Perforce metadata tables.

SYSTABS

Lists all tables

Column Name
 Data Type
 Description

CATALOG

CHAR(32)

The name of the catalog in which the table resides (system or perforce).

TABNAME

CHAR(32)

The name of the table.

SYSTYPES

Lists all mapped types. This system catalog is included for completeness. The information stored in the SYSTYPES table is not of general utility for creating Perforce-related reports.

Column Name
 Data Type
 Description

TYPE_NAME

CHAR(32)

Name of the data type.

DATA_TYPE

SMALLINT

Corresponding ODBC or driver-specific SQL data type.

COLUMN_SIZE

INTEGER

  • Numeric data types: the maximum precision.
  • String data types: the length in characters.
  • Datetime data types: the length of the string representation.
  • Interval data types: the number of characters in the character representation of the interval.

    NULL for data types for which column size is not applicable.

LITERAL_PREFIX

CHAR(5)

The characters that precede a literal:

  • Character data: a single quotation mark (')
  • Binary data types: 0x

    NULL for data types that have no prefix.

LITERAL_SUFFIX

CHAR(5)

The characters that follow a literal:

  • Character data: a single quotation mark (')

    NULL for data types that have no suffix.

CREATE_PARAMS

CHAR(32)

Lists the parameters that can be specified for each data type.

NULLABLE

SMALLINT

Indicates whether the data type accepts a NULL value.

CASE_SENSITIVE

SMALLINT

Indicates whether a character data type is case-sensitive.

SEARCHABLE

SMALLINT

Specifies how the data type is used in a WHERE clause.

UNSIGNED_ATTRIBUTE

SMALLINT

Indicates whether the data type is unsigned.

FIXED_PREC_SCALE

SMALLINT

Indicates whether the data type has predefined fixed precision and scale.

AUTO_UNIQUE_VALUE

SMALLINT

Indicates whether the data type is autoincrementing.

LOCAL_TYPE_NAME

CHAR(32)

Specifies the localized data source-dependent name of the data type.

MINIMUM_SCALE

SMALLINT

The minimum scale of the data type. For example, an SQL_TYPE_TIMESTAMP column might have a fixed scale for fractional seconds.

MAXIMUM_SCALE

SMALLINT

The maximum scale of the data type.

SQL_DATA_TYPE

SMALLINT

The SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor.

SQL_DATETIME_SUB

SMALLINT

For SQL_DATETIME or SQL_INTERVAL, the datetime/interval subcode.

NUM_PREC_RADIX

INTEGER

  • Approximate numeric types: contains 2 to indicate that COLUMN_SIZE specifies a number of bits.
  • Exact numeric types: contains the value 10 to indicate that COLUMN_SIZE specifies a number of decimal digits.

    Otherwise, this column is NULL.

INTERVAL_PRECISION

SMALLINT

For interval data types, contains the value of the interval leading precision.

SYSCOLS

Lists all columns of all tables

Column Name
 Data Type
 Description

CATALOG

CHAR(32)

The name of the catalog in which the table resides (system or perforce).

TABNAME

CHAR(32)

The name of the table.

COLNAME

CHAR(32)

The name of the column within the table.

COLNO

SMALLINT

The number of the column within the table, starting with 1.

COLTYPE

SQLTYPE

The data type of the column.

SYSIDXS

Lists the primary key for each table.

Column Name
 Data Type
 Description

CATALOG

CHAR(32)

The name of the catalog in which the table resides (system or perforce).

TABNAME

CHAR(32)

The name of the table.

KEYNAME

CHAR(32)

The name of the key.

COLNAME

CHAR(32)

The name of the column that forms this part of the key.

COLNO

SMALLINT

The position of this column within the key, starting with 1.

P4Report Tables

BRANCHES

Contains a row for each branch specification.

Column Name
 Data Type
 Description

BRANCH

VARCHAR(254)

Name of branch

OWNER

VARCHAR(254)

The Perforce user that created this branch.

P4OPTIONS

VARCHAR(254)

Branch update options: locked or unlocked.

UPDATED

TIMESTAMP

The date this specification was last modified

ACCESSED

TIMESTAMP

The date of the last integration performed using this branch.

DESCRIPTION

LONGVARCHAR

A short description of the branch (optional).

CHANGES

Contains a row for each changelist. This table cannot be updated using P4Report.

Column Name
 Data Type
 Description

CHANGE

INTEGER

Changelist number.

DATE

TIMESTAMP

Date the changelist was created.

USER

VARCHAR(254)

Perforce user that created the changelist.

CLIENT

VARCHAR(254)

Perforce client that created the changelist.

STATUS

VARCHAR(15)

pending or submitted

DESCRIPTION

LONGVARCHAR

User-specified description of the changelist.

P4OPTIONS

VARCHAR(254)

Specifies query options. If you specify multiple options, separate them with spaces, commas, semicolons or colons. Valid values are:

  • integrated: include any changes integrated into the specified files. Valid only in conjunction with the FILESPEC column. Must be specified in lower case. For example:

    SELECT * FROM CHANGES WHERE FILESPEC = '//depot/main/release2.0/...'
    AND P4OPTIONS = 'integrated';

  • longdesc: By default, the 31-character short description is returned, to ensure best query performance. To return the full description, specify the longdesc option. Note that queries run more slowly with the longdesc option.

FILESPEC

VARCHAR(254)

The files in the changelist.

You can query this column using Perforce wildcards. For example:

SELECT * FROM CHANGES WHERE FILESPEC = '//depot/main/doc/...'

CLIENTS

Contains a row for each client. For valid settings for specific options, refer to the Perforce Command Reference Manual description of the p4 client command.

Column Name
 Data Type
 Description

CLIENT

VARCHAR(254)

Name of the client.

OWNER

VARCHAR(254)

Perforce user that created the client.

OPTIONS

VARCHAR(254)

  • [no]allwrite
  • [no]clobber
  • [no]compress
  • [un]locked
  • [no]modtime
  • [no]rmdir

ROOT

VARCHAR(254)

Client root directory.

HOST

VARCHAR(254)

The host machine where the client workspace resides.

MAPSTATE

INTEGER

Set to 1 if the client's "have" list (the server's list of file revisions in the client workspace) is in agreement with the client view, or 0 if the "have" list and client view are not in agreement (because the client view has been changed).

UPDATED

TIMESTAMP

Last time the client specification was changed.

ACCESSED

TIMESTAMP

Last time the client performed a Perforce action.

DESCRIPTION

LONGVARCHAR

User-specified description of the client workspace.

LINEEND

VARCHAR(254)

Specifies how line endings are converted when files are synced to this workspace, for cross-platform compatibility.

SUBMITOPTIONS

VARCHAR(254)

Specifies how files are handled when a changelist is submitting from this workspace.

COUNTERS

Contains a row for each counter

Column Name
 Data Type
 Description

COUNTER

VARCHAR(254)

Name of the counter.

VALUE

VARCHAR(254)

Value of the counter.

FILES

Contains a row for each revision of each file in the depot. This table cannot be updated using P4Report.

Column Name
 Data Type
 Description

FILE

VARCHAR(254)

Full file path.

REVISION

SMALLINT

File revision number.

TYPE

VARCHAR(20)

Perforce file type.

ACTION

VARCHAR(10)

Last Perforce action performed on the file.

TIME

TIMESTAMP

Time of last Perforce action.,

CHANGE

INTEGER

Changelist associated with this revision.

REVSPEC

VARCHAR(254)

Revision specifier column, added by P4Report to support queries using Perforce revision specifications such as change numbers, labels and dates. For example:

select * from files where revspec='2002/12/24'

FIXES

Contains a row for each fix.

Column Name
 Data Type
 Description

JOB

VARCHAR(254)

Job number/description (for example job001234).

CHANGE

INTEGER

Changelist that fixed this job.

P4OPTIONS

VARCHAR(254)

Specifies query options. Valid values are:

  • integrated: list fixes associated with changelists that affect files which have been integrated into the files specified in the FILESPEC column. Valid only in conjunction with the FILESPEC column. Must be specified in lower case. For example, to list all fixes affecting files under branch1, including those that have been integrated from other files:

    SELECT * from fixes
    WHERE filespec = '//depot/branch1/...' AND options = 'integrated';

FILESPEC

VARCHAR(254)

Special-purpose column for querying using wildcards; for example:

[...] WHERE FILESPEC = //depot/main/release1.1/...

FSTAT

Returns a row for each file in a file specification that is specified in a SELECT query. The file specification, if omitted from the query, defaults to "//..." (the entire depot, which is an expensive query and most likely not the desired result). The file specification can contain Perforce revision specifiers, (for example @2008/01/01). For more details, refer to the description of the p4 fstat command in the Perforce Command Reference Manual.

Column Name
 Data Type
 Description

CLIENTFILE

WVARCHAR(254)

Client path to file, client local syntax (//workspacename/...)

DEPOTFILE

WVARCHAR(254)

Depot path to file (//depot/...)

PATH

WVARCHAR(254)

Local path to file

ISMAPPED

SMALLINT

Returns 1 if the file is mapped to the workspace

HEADACTION

WVARCHAR(254)

Action taken at head revision

HEADCHANGE

INTEGER

Head revision changelist number

HEADREV

SMALLINT

Head revision number

HEADTYPE

WVARCHAR(254)

File type of head revision

HEADTIME

TIMESTAMP

Time that the head revision was submitted, in seconds since 00:00:00 UTC, January 1, 1970

HEADMODTIME

TIMESTAMP

Time that the head revision was modified in the workspace, in seconds since 00:00:00 UTC, January 1, 1970

HAVEREV

SMALLINT

Revision last synced to workspace

DIGEST

WVARCHAR(254)

MD5 checksum

FILESIZE

INTEGER

File size in bytes

ACTION

WVARCHAR(254)

Returns add, edit, delete, branch, or integrate, if open in current workspace. Returns "none" if not open.

TYPE

WVARCHAR(254)

File type of currently open revision, if open

ACTIONOWNER

WVARCHAR(254)

User who opened file in current workspace, if open

RESOLVED

SMALLINT

Returns 1 if you just resolved a file that required resolving.

UNRESOLVED

SMALLINT

Returns 1 if you have any files that require resolving.

OURLOCK

SMALLINT

Returns 1 if current user and workspace has locked files

FILESPEC

WVARCHAR(254)

File specification for this query

GROUPS

Contains a row for each group. Can be filtered by the Perforce users in the group.

Column Name
 Data Type
 Description

GROUPNAME

VARCHAR(254)

Name of Perforce group.

USERNAME

VARCHAR(254)

User names of group members

SUBGROUP

SMALLINT

Flag: 0 for groups which are not subgroups, 1 for groups which are subgroups.

MAXRESULTS

INTEGER

Maximum number of rows in metadata that queries from group members are allowed to affect.

MAXSCANROWS

INTEGER

Maximum number of rows in metadata that user queries are allowed to scan.

TIMEOUT

INTEGER

Length of time (in seconds) a login ticket remains valid.

USERSPEC

VARCHAR(254)

The Perforce user to be used to qualify groups. For example, to return the groups that contain the Perforce user joe, issue the following query:

select * from groups where userspec = 'joe';

Note: The column is returned blank for the query select * from groups. You must specify a where clause containing the USERSPEC column.

INFO

Contains Perforce connection information, one row for each item.

Column Name
 Data Type
 Description

NAME

VARCHAR(32)

Name of the connection setting (for example, P4PORT, P4USER, etc.)

VALUE

VARCHAR(254)

Value of the setting.

INTEGS

Contains a row for each file integration. This table cannot be updated using P4Report.

Column Name
 Data Type
 Description

TOFILE

VARCHAR(254)

Target file.

FROMFILE

VARCHAR(254)

Source file.

STARTTOREV

SMALLINT

Starting revision number of target file.

ENDTOREV

SMALLINT

Ending revision of target file. If integrating from a single revision (as opposed to a revision range), the STARTTOREV and ENDTOREV fields will be identical

STARTFROMREV

SMALLINT

Starting revision number of source file.

ENDFROMREV

SMALLINT

Ending revision of source file. If integrating from a single revision (as opposed to a revision range), the STARTFROMREV and ENDFROMREV fields will be identical

HOW

VARCHAR(15)

Integration method:

  • merge into
  • branch into
  • copy into
  • ignore into
  • delete into

JOBS

Contains all jobs. This table is dynamically generated from your Perforce job specification, which you can customize. The following columns are always appended to this table:

Column Name
 Data Type
 Description

P4OPTIONS

VARCHAR(254)

Specifies query options. Valid values are:

  • integrated: list jobs associated with changelists that affect files which have been integrated into the files specified in the FILESPEC column. Valid only in conjunction with the FILESPEC column. Must be specified in lower case. For example, to list jobs that have fixes affecting files under branch1, including files that have been integrated from other files.

    SELECT * from jobs
    WHERE filespec = '//depot/branch1/...' AND options = 'integrated';

FILESPEC

VARCHAR(254)

Special-purpose column for querying using wildcards; for example:

WHERE FILESPEC = //depot/main/release1.1/...

LABELS

Lists all labels

Column Name
 Data Type
 Description

LABEL

VARCHAR(254)

Name of the label

OWNER

VARCHAR(254)

Perforce user that created the label.

P4OPTIONS

VARCHAR(254)

Locked or unlocked

UPDATED

TIMESTAMP

Last time the label definition was changed.

ACCESSED

TIMESTAMP

Last time the label was used to perform a Perforce action.

DESCRIPTION

LONGVARCHAR

User-specified description of the label.

OPENED

Contains a record for each opened file

Column Name
 Data Type
 Description

DEPOTFILE

VARCHAR(254)

Depot path of the open file.

CLIENTFILE

VARCHAR(254)

Client location of the open file.

REVISION

SMALLINT

Revision number of the open file

ACTION

VARCHAR(10)

Indicates open for add, edit, delete, branch, integrate, or import.

CHANGE

INTEGER

Number of changelist in which the file is open for the specified action.

TYPE

VARCHAR(20)

Perforce file type (if being changed).

USER

VARCHAR(254)

Perforce user that opened the file.

CLIENT

VARCHAR(254)

Perforce client that opened the file.

PROTECTIONS

Lists Perforce protections. Only the Perforce superuser can access this table.

Column Name
 Data Type
 Description

MODE

VARCHAR(10)

The Perforce permission:

  • list
  • read
  • review
  • open
  • write
  • super

TYPE

VARCHAR(10)

The type of recipient of the permission: group or user.

NAME

VARCHAR(254)

A Perforce group or username.

HOST

VARCHAR(254)

The IP address of the client host.

PATH

VARCHAR(254)

The part of the depot to which the permission applies.

RESOLVED

Lists files that have been resolved but not submitted.

Column Name
 Data Type
 Description

PATH

VARCHAR(254)

The file being resolved (local syntax)

TOFILE

VARCHAR(254)

The target file (same as path, but in client syntax)

FROMFILE

VARCHAR(254)

The donor file (depot syntax)

STARTTOREV

SMALLINT()

The target revision minus one (#none if no target revision)

ENDTOREV

SMALLINT()

The target revision (#none if no target revision)

STARTFROMREV

SMALLINT()

The start of the source revision range minus one

ENDFROMREV

SMALLINT()

The end of the source revision range

HOW

VARCHAR(15)

The type of resolve that was performed

FILESPEC

VARCHAR(254)

Special-purpose column for querying using wildcards; for example:

WHERE FILESPEC = //depot/main/release1.1/...

USERS

Lists all Perforce users

Column Name
 Data Type
 Description

USER

VARCHAR(254)

Name of Perforce user.

EMAIL

VARCHAR(254)

Email of user.

FULLNAME

VARCHAR(254)

User-specified name of user.

UPDATED

TIMESTAMP

Last time user definition was changed.

ACCESSED

TIMESTAMP

Last time this user performed a Perforce action.

SQL Keywords

ALL

INTO

AND

IS

ANY

JOIN

AS

LEFT

ASC

LIKE

AVG

LOCAL

BETWEEN

MATCH

BY

MAX

CAST

MIN

CORRESPONDING

NATURAL

COUNT

NOT

CREATE

NULL

CROSS

ON

DELETE

OR

DESC

ORDER

DISTINCT

OUTER

DROP

RIGHT

ESCAPE

SELECT

EXCEPT

SET

EXISTS

SOME

FALSE

SUM

FROM

TABLE

FULL

TEMPORARY

GLOBAL

TRUE

GROUP

UNION

HAVING

UNIQUE

IN

UNKNOWN

INNER

UPDATE

INSERT

USING

INTERSECT

VALUES

WHERE

Perforce 2008.2 P4Report User's Guide
<< Previous Chapter
Codeline Management
 Table of Contents
Index
Perforce on the Web
 Next Chapter >>
P4Report Functions
Please send comments and questions about this manual to manual@perforce.com.
Copyright 2002-2008 Perforce Software. All rights reserved.
Last updated: 12/05/08