[p4] Accessing the Perforce database from *nix

[Robert Cowham]

P4SQL which is the Windows interface is an ODBC driver which translates SQL
into perforce commands.

So "select * from users" becomes "p4 users" under the covers. It's a clever
bit of code!

SQL> select * from users where user like 'Ann%';

user            email                   fullname        updated
accessed
====            =====                   ========        =======
========
Anna_Schmidt    Anna_Schmidt at p4demo.com Anna_Schmidt    2005-05-03 20:17:01
2006-09-14 21:39:05

1 row(s) retrieved.

In p4d.log:

Perforce server info:
        2007/12/13 15:38:04 pid 12284 bruno at bruno_ws 127.0.0.1 [unknown/v60]
'user-users'
Perforce server info:
        2007/12/13 15:38:04 pid 12284 completed .000s


Using a query from the P4Report manual:

SQL> select file, count(*) from files
>      where file like '//depot/Jam/R%' group
>      by file having count(*) > 1;

file                                    (expr)
====                                    ======
//depot/Jam/ROBERT/src/Build.com        3
//depot/Jam/ROBERT/src/Build.mpw        3
//depot/Jam/ROBERT/src/command.c        2

3 row(s) retrieved.

Perforce server info:
        2007/12/13 15:41:57 pid 12284 bruno at bruno_ws 127.0.0.1 [unknown/v60]
'user-files -a //depot/Jam/R...'
Perforce server info:
        2007/12/13 15:41:57 pid 12284 completed .031s

Thus it does not directly access the database. Equally it doesn't run on
*nix.

As Steve mentions, if you run the wrong command or join the wrong tables
then you can end up placing a heavy load on your Perforce server - be
careful!

If using Python, you might want to use P4Python to access the p4d server -
significantly faster for repeated p4 commands. 

> -----Original Message-----
> From: perforce-user-bounces at perforce.com 
> [mailto:perforce-user-bounces at perforce.com] On Behalf Of Stephen Vance
> Sent: 12 December 2007 21:37
> To: Looney, James B
> Cc: perforce-user at perforce.com
> Subject: Re: [p4] Accessing the Perforce database from *nix
> 
> They started with BerkeleyDB, but have modified it heavily since. 
> Accessing it that way is entirely unsupported and can 
> adversely affect your server performance depending on locking 
> interactions. The official line is that for all practical 
> purposes, it is proprietary.
> 
> I believe there are ODBC client software versions for those 
> platforms. 
> You can use those against the Windows ODBC server for 
> Perforce to access the data. You should also be aware that 
> heavy use of this mechanism can also adversely affect your 
> server performance. Data mining has different access patterns 
> than the running server and ad hoc queries won't be optimized 
> for server performance like server data access.
> 
> Steve
> 
> Looney, James B wrote:
> > I recently discovered that we could access Perforce's database from 
> > Windows with ODBC, and wanted to similarly access the database (for 
> > some datamining to improve our scripts' performance) on a 
> *nix platform.
> > However, I'm a little confused on how to do so in *nix-land.
> >
> > Our scripts are written in Python, and are run on SunSolaris, SGI's 
> > IRIX and HP-UX.  So, from those systems, how would I setup 
> the connection?
> > Or, what areas/topics are recommended to read up on to 
> figure it out?
> >
> > As a side question, I was wondering what kind of database it is, 
> > whether they use SQL, MySQL, etc?