From owner-freebsd-pkg@FreeBSD.ORG  Mon Dec 22 15:17:03 2014
Return-Path: <owner-freebsd-pkg@FreeBSD.ORG>
Delivered-To: freebsd-pkg@freebsd.org
Received: from mx1.freebsd.org (mx1.freebsd.org
 [IPv6:2001:1900:2254:206a::19:1])
 (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits))
 (No client certificate requested)
 by hub.freebsd.org (Postfix) with ESMTPS id A129CBF8;
 Mon, 22 Dec 2014 15:17:03 +0000 (UTC)
Received: from mx5.roble.com (mx5.roble.com [206.40.34.5])
 (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits))
 (Client CN "mx5.roble.com", Issuer "mx5.roble.com" (not verified))
 by mx1.freebsd.org (Postfix) with ESMTPS id 90CB8A3E;
 Mon, 22 Dec 2014 15:17:03 +0000 (UTC)
Date: Mon, 22 Dec 2014 07:17:02 -0800 (PST)
From: Roger Marquis <marquis@roble.com>
To: freebsd-ports@freebsd.org
Subject: Re: sqlite error while executing DROP INDEX
In-Reply-To: <mailman.1.1419249600.27230.freebsd-ports@freebsd.org>
References: <mailman.1.1419249600.27230.freebsd-ports@freebsd.org>
User-Agent: Alpine 2.00 (BSF 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
Cc: freebsd-pkg@freebsd.org
X-BeenThere: freebsd-pkg@freebsd.org
X-Mailman-Version: 2.1.18-1
Precedence: list
List-Id: Binary package management and package tools discussion
 <freebsd-pkg.freebsd.org>
List-Unsubscribe: <https://lists.freebsd.org/mailman/options/freebsd-pkg>,
 <mailto:freebsd-pkg-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-pkg/>
List-Post: <mailto:freebsd-pkg@freebsd.org>
List-Help: <mailto:freebsd-pkg-request@freebsd.org?subject=help>
List-Subscribe: <https://lists.freebsd.org/mailman/listinfo/freebsd-pkg>,
 <mailto:freebsd-pkg-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Mon, 22 Dec 2014 15:17:03 -0000

Matthew Seaman wrote:
> SELECT * FROM DEPS WHERE name='pkgconf' AND version='0.8.7_2'
>   AND package_id='7714';
> Given two rows that are exactly the same

No two rows but it apears one of pkg's dependencies may have changed
names at some point:

   # SELECT * FROM DEPS WHERE name='pkgconf' AND version='0.8.7_2'
   #   AND package_id='7714';

   devel/pkgconf|pkgconf|0.8.7_2|7714

   # SELECT * FROM DEPS WHERE name='pkgconf' AND version='0.8.7_2';

   devel/pkg-config|pkgconf|0.8.7_2|7669
   devel/pkg-config|pkgconf|0.8.7_2|7670
   devel/pkg-config|pkgconf|0.8.7_2|7671
   devel/pkg-config|pkgconf|0.8.7_2|7685
   devel/pkg-config|pkgconf|0.8.7_2|7686
   devel/pkgconf|pkgconf|0.8.7_2|7700
   devel/pkgconf|pkgconf|0.8.7_2|7714
   devel/pkg-config|pkgconf|0.8.7_2|7714
   devel/pkgconf|pkgconf|0.8.7_2|7747
   devel/pkgconf|pkgconf|0.8.7_2|7748
   devel/pkg-config|pkgconf|0.8.7_2|7755
   devel/pkg-config|pkgconf|0.8.7_2|7756
   devel/pkg-config|pkgconf|0.8.7_2|7772
   devel/pkg-config|pkgconf|0.8.7_2|7775
   devel/pkgconf|pkgconf|0.8.7_2|7798
   devel/pkgconf|pkgconf|0.8.7_2|7800
   devel/pkgconf|pkgconf|0.8.7_2|7802
   devel/pkg-config|pkgconf|0.8.7_2|7804

Can all of these records can be safely deleted, all of the pkg-configs,
or should the delete be limited to pkg-config|7714?  Also curious about
the significance of the package_id field and how n+1 records were written
in the first place.  I trust someone has filed one or more bugzillas as
it appears pkg should be checking for dupes on at least two dimensions.

Forensics is also hampered by the lack of a date field, ideally
reflecting the package's install date (with epoch/unix timestamp
precision).  Consider this a feature request.

Grazie!
Roger