Re: Database Schema for Commit Logs

Mark Phippard wrote:
> I want to create a database schema where I will log all of my commits, via
> a hook script, and perhaps also capture and store other statistics in the
> process such as diff stats.

I have done this. I have a Perl script which puts data in Posgresql and some pages which are part
of our HTML::Mason based intranet which query that database. It enables finding changes based on
user, repo path and commit messages. It also generates some internal documentation. I was planning
on creating a more generic MySQL and CGI script version but I don't have the time right now. I
would like to add properties to this database and be able to query based on those property values.

I have attached the schema - but without explanation some of it will be a mystery to you!

I have a similar but far more complex version of this for CVS and had to produce the Subversion
version of it to keep CVS users moving to Subversion happy.

-- 
Martin Tomes
echo 'martin at tomes x org x uk'\
  | sed -e 's/ x /\./g' -e 's/ at /@/'
The Subversion Wiki is at http://www.subversionary.org/

/* $Id: svndb_schema.sql 8 2004-02-17 16:52:16Z martinto $ */

drop table repositories;
drop table commits;
drop table files;
drop table filerev;
drop sequence fileseq;
drop table authors;
drop table copies;

create table repositories (
  repoid integer not null primary key,
  shortname varchar(20) not null,
  url varchar(256) not null,
  path varchar(256) not null,
  viewcvs varchar(20) not null
);

create table commits (
  repoid integer not null,
  revision integer not null, /* repository revision no. */
  ctimestamp timestamp,
  author varchar(80),
  message text,
  PRIMARY KEY (repoid, revision)
);

create sequence fileseq;
create table files (
  fileid integer primary key default nextval('fileseq'),
                                              /* Id for the path name. */
  repoid integer not null,
  filename varchar(256) unique not null
);

create table packages (
  repoid integer not null,
  package varchar(256) unique not null
);

create table filerev (
  repoid integer not null,
  revision integer not null, /* repo rev. */
  fileid integer not null, /* File changed in repo rev. */
  action char(1)
);
create index filerev_fileid on filerev (fileid);

create table authors (
  repoid integer not null,
  author varchar(80) unique not null
);

create table copies (
  repoid integer not null,
  revision integer not null,
  fileid integer not null, /* File which is a copy. */
  copyfromid integer not null, /* Where is came from. */
  copyfromrev integer not null /* The revision in came from. */
);
grant select on repositories, commits, files, packages, filerev, authors, copies to webpage;

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@subversion.tigris.org
For additional commands, e-mail: dev-help@subversion.tigris.org
Received on Fri Aug 6 10:17:38 2004