Perforce 2007.1 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

Column Name
Data Type
Description

CLIENT

VARCHAR(254)

Name of the client.

OWNER

VARCHAR(254)

Perforce user that created the client.

P4OPTIONS

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.

COUNTERS

Contains a row for each counter

Column Name
Data Type
Description

COUNTER

VARCHAR(254)

Name of the counter.

VALUE

INTEGER

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/...

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.

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 2007.1 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-2007 Perforce Software. All rights reserved.
Last updated: 03/06/07