RE: [viewvc-dev] [viewvc-users] checkin DB svn log queries without additional tree change/changeset info

Here's a an update:

The SQL query: "SELECT DISTINCT t1.revision, t1.repositoryid, t1.ci_when, t2.description FROM commits t1 LEFT JOIN descs t2 ON t1.descid = t2.id" may be good for determining the number of unique commits, i.e. it can be used to generate a full list of all the revisions/changesets recorded in the ViewVC Commit DB DB, i.e. surrogate of "svn log -r M:N <repo_uri>". Of course it can be further parameterized to reduce the result set to a one or more specific repositories and revision range as to give more flexibility to the user and reduce the result set returned.

The way which I have verified the above query to return a result per unique commit is as follows:

1. Load the ViewVC DB with data from the repositories of interest. Each repository will have a certain number of commits, i.e. revisions, lets say for 
repo1 there are r1 revisions, repo2 has r2 revisions, and so on down to repoN 
which has rN revisions. For each repository 1 to N I did 


 
- ./svndbadmin -v rebuild <path_to_repoX> > viewvc_db_import_repoX.txt

2. Find out the number of identical log messages/commits, i.e. log collisions
Each of the above file will contain a line of the form "Building commit info for revision <some_number> ... skipped (no changes)", for each identical log message in the repository, i.e. revision referencing (changeset) collision as described below due to ViewVC Commit DB data loading methodology (i) assumed (and described) in the previous post.
 
- for each viewvc_db_import_repoX.txt (X being 1 to N) generated in step 1 above, obtain a count of the number of occurrences of the string "skipped", i.e.
  - grep skipped viewvc_db_import_repoX.txt | wc -l
- let call the output of the above, c1, c2, ... cN (for collisions)

3. Now it follows that if the above SQL query is correct, the number of result rows it should return is: (r1 +1 - c1) + (r2 +1 - c2) + ... + (rN +1 - cN) = (r1 - c1) + (r2 - c2) + ... + (rN - cN) + N
...which I can proudly say that it does :)

Naturally I have only tested with the limited amount of repositories I have on hand and cannot vouch for complete accuracy, however I have not encountered a test case which contradicts the above assertion... maybe someone else can, and then we are back again where we started :D.

So my hopes are that:
1. My conclusion is a correct one w.r.t the above query.
2. Someone will implement an enhancement to the current query interface of viewvc.cgi based on the above SQL example, with better parameterization of course
3. Someone will implement the ViewVC DB data loading mechanism for svndbadmin as per methodology (ii) below... or better :)
4. That implementations of 2 & 3 are sufficient fix to 433... which has been open for some time now :D

I would be more than happy to test patches resulting from the above.

DISCLAIMER: Use the above & below (previously posted) recommendations, logic, content at your own risk, I make no representation of correctness/accuracy, fitness, merchantability, etc., etc. and take no responsibility if it causes any type of loss(es) directly or indirectly. USE AT YOUR OWN RISK!!! There... I can sleep better now :).

Thanks, Most.


From: [email protected]
To: [email protected]
Subject: RE: [viewvc-users] checkin DB svn log queries without additional tree change/changeset info
Date: Tue, 14 Jun 2011 21:33:24 -0400








Hi,

I read bug 433... yes in its entirety :), and I hate to say 
it, but this smells to me like a data generation/semantics issue. Maybe 
I'm jumping the gun, but based on the symptoms, this is the feeling I 
get.

The immediate question I found myself asking while reading bug 433 was:

Why
 should the query result return file/dir information pertaining to a 
particular commit when all we're interested in is a "revision logs" 
listing? Why are we getting the penalty for data we don't want/care for?
 Is is not possible to separate the two?

I see that 
commits.revision in conjunction with 
commits.repositoryid is a good starting point for performing a "unique 
value" query filter for commit log data only, and then using the commits.descid as a FKEY 
to descs.id to get at the log message. Of course the accuracy of this 
depends on how the tables were populated (see methods i and ii below). That is, what uniquely 
represents a commit? Hence the semantics side of things, i.e. do we use the SVN implementation uniquely identifying change sets via global revision IDs or the more looser CVS way of unique log messages. But never the less, supposing all is good and well on that side of things, an an SQL query that might do the job is:

SELECT DISTINCT t1.revision, t1.repositoryid, t1.ci_when, t2.description FROM commits t1 LEFT JOIN descs t2 ON t1.descid = t2.id
 
I haven't fully tested the accuracy of the above vs. my SVN log messages due to point i. below but the count looks in the ballpark of all commits in all repositories used for loading the ViewVC Commit DB.

Now to the ways of loading data into the ViewVC Commit DB, there are two ways.

 i. If the tables were populated in a manner where log messages represent 
(keys) uniquely identify a commit, then for both CVS and SVN based data we might have 
collisions when duplicate log messages occur, e.g. the very common 
"Initial import" log messages. That is, different commits appear as one 
and the same simply because they have the same log message... unique change sets, as defined by SVN, melt into one.
ii. If 
the tables were to be populated by global revision numbers then we are 
good on the SVN-based data side, since this is directly available from 
the repository; uniqueness guaranteed by SVN. But we need to compensate 
for CVS' lack of unique commit ID's.

Based on the previous 
response on the dev list  (http://viewvc.tigris.org/ds/viewMessage.do?dsForumId=4255&dsMessageId=2765316) and the fact that commits.descid != commits.revision, DB 
population is currently done as per i. So why not do things as per ii with an 
additional functionality by "mimicking" global revision numbers for CVS 
data generation, based again on matching log messages, as is done right 
now? It seems fair that since it is CVS that lacks the data for uniquely identifying a change-set (a very useful/powerful concept for version control), it's data 
should be the only one that might get revision referencing (changeset) collisions. 
Currently this CVS limitation is also spilling over for SVN data in the 
Checkin DB. Instead of rewarding SVN data its punishing it to appease CVS :(. 

Looking back at the big picture, it seems that the 
433 stems from the query limiting values, which I remember reading that 
there are four, cfg.cvsdb.row_limit, cfg.options.limit_changes, etc... 
these are intended to reduce the load on the DB in order to keep up its 
performance. Generally when speaking of RDB's, the biggest bottleneck is
 disk I/O. Yes there are buffers/caches and other software mechanisms to
 help, but in the end disk I/O kills the fun for all :(. So with this in
 mind, it really matters how the schema is designed, and you can get a 
big performance boost there. Limiting query results is a good practice, 
if not for anything else but to prevent being your own DOS source :). 
But you can also keep up/improve the RDB's performance to a particular 
load with tweeks to the schema.

Now on the schema side of things.
 RDBs are blazingly fast when it comes to operating on a single table, 
the less joins/FKEYS the faster the query. Also different tables result 
in different indexes and different data sizes translating to different 
I/O requests which can really throw a wrench in benefiting from the 
hardware pipeline that is available from your controllers and disks. For
 a well performing RDB query predictability/consistency is needed. So if
 you can get away with putting more in one table vs. putting less but in
 more tables I would choose the former. Of course there is a point at 
which to stop, but this is why DBA's and RDB consultants cost so much 
and I am neither so take my opinion simply as that, an opinion, and at 
your own risk :). Thus I would merge the descs and commits tables. The 
spacial overhead of having multiple "description" entries is really 
worth the performance boost... space is really cheap now a days :). Then
 if you really want to boost things up, you can place different tables 
in different table spaces each mapped to a different disk for greater 
parallelism. That's what I learned in RDB deployment 101 thanks to nice 
discussion forums like this one :).

Of course a schema change implies backward compatibility issues, migration and support... we just don't get a break, do we? :) Well, kind of yes, because of the following facts which makes things simpler:

1. The source for the Checkin DB never really disappears, i.e. the CVS and SVN repositories are safe and sound. If they are gone then we have bigger issues on our hand... really big if there are no backups of them :(, but that's beyond the scope of this discussion.
2. The data in the Checkin DB always, or at least it should, reflects the latest CVS/SVN state.
3. The data in the Checkin DB is not modified to reflect things differently that what they are in CVS/SVN repositories.

Based on the above, there is no real issues except for time spent, to ask users to rebuild the Checkin DB in order to benefit from a more "performing" schema. For example when going to the next "major" revision change,i.e. from 1.1.x, 1.2.x.. hint hint :D.

So in summary it looks there are thee issues to tackle, two of which can be done without having to go to a next major rev.

1. Changing the loading of the DB to type ii. This will ensure no "lost" changesets.
2. Changing the query functionality in viewvc.cgi to just give us the tip of a repository's iceberg, i.e. rev, date, log, and only this as it is the typical starting place of any drill-down when searching for "what happened" with source code under version control.
3. Possible schema changes. Here I have to say that I have been very superficial in my analysis as it is meant only to start a discussion if seen as worth while.
- commits.revision, 
commits.repositoryid to used as a PK, or better generate a PK that is independent of this "business logic" and have commits.revision, 
commits.repositoryid as surrogate PKS at the business level if really need be
- maybe start looking at Hibernate, at least its concepts as a persistence layer might reduce some of the hurdles and give some inspiration, maybe even programmatic ones :) ... a quick Goolge search for "Hibernate Python" might give a currently available solution (open source of course :) )
- changing the table structures may be a place to look as well for increasing performance
- commits.revision should be an int and not a varchar(32), this I am sure of... better performance and more real to representing the way things are.

Thanks,
Most.


> Date: Tue, 14 Jun 2011 09:35:18 -0400
> From: [email protected]
> To: [email protected]
> CC: [email protected]
> Subject: Re: [viewvc-users] checkin DB svn log queries without additional tree change/changeset info
> 
> On 06/13/2011 09:11 PM, Asd Asdasd wrote:
> > Hi,
> > 
> > Is it possible to perform queries via viewvc.cgi on the Checkin DB only for
> > svn log information? For example, I want to see all svn logs related to
> > commits with a certain range of dates, pretty much the same way one gets
> > output via "svn log -r {2006-11-20}:{2006-11-29}", but with a nicer
> > interface :). These type of queries are typically used as the first step in
> > drilling down to more details about the source code under version control,
> > i.e. get one's bearings straight.
> > 
> > One reason for this is that so far I get tree changeset information as well,
> > and it looks like the SQL results related to such information is counted
> > against row_limit, thus limiting the output... plus its always nice to have
> > only what is needed in the output :).
> 
> I know this problem all too well.  You'll want to read issue #433's history.
> http://viewvc.tigris.org/issues/show_bug.cgi?id=433
> 
> No, I don't currently have a performance-aware solution for it.
> 
> -- 
> C. Michael Pilato <[email protected]>
> CollabNet   <>   www.collab.net   <>   Distributed Development On Demand
>

------------------------------------------------------
http://viewvc.tigris.org/ds/viewMessage.do?dsForumId=4251&dsMessageId=2766395

To unsubscribe from this discussion, e-mail: [[email protected]].