From owner-freebsd-questions Sat Sep 14 19:39:44 2002 Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.FreeBSD.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id A895237B400 for ; Sat, 14 Sep 2002 19:39:41 -0700 (PDT) Received: from new-smtp2.ihug.com.au (new-smtp2.ihug.com.au [203.109.250.28]) by mx1.FreeBSD.org (Postfix) with ESMTP id ADC7043E4A for ; Sat, 14 Sep 2002 19:39:40 -0700 (PDT) (envelope-from john@jmv.com.au) Received: from p128-apx1.syd.ihug.com.au (localhost) [203.173.140.128] (john) by new-smtp2.ihug.com.au with esmtp (Exim 3.22 #1 (Debian)) id 17qPJs-00078r-00; Sun, 15 Sep 2002 12:39:32 +1000 Date: Sun, 15 Sep 2002 12:37:42 +1000 Subject: Re: Pick database in FreeBSD Content-Type: text/plain; charset=US-ASCII; format=flowed Mime-Version: 1.0 (Apple Message framework v482) Cc: Bill Moran To: freebsd-questions@freebsd.org From: John Vender In-Reply-To: <3D83C34E.4090803@potentialtech.com> Message-Id: <1C406202-C854-11D6-A4EF-00039369D83A@jmv.com.au> Content-Transfer-Encoding: 7bit X-Mailer: Apple Mail (2.482) Sender: owner-freebsd-questions@FreeBSD.ORG Precedence: bulk List-ID: List-Archive: (Web Archive) List-Help: (List Instructions) List-Subscribe: List-Unsubscribe: X-Loop: FreeBSD.ORG On Sunday, September 15, 2002, at 09:16 AM, Bill Moran wrote: > This may be totally off-topic, or it may not, so I'll risk asking. > > Does anyone have any experience with the Pick database system from > Raining Data? The software is technically discontinued so I'm having > a lot of trouble finding information on it and I've suddenly picked > up a client that has a Pick system with a LOT of data in it. > > Everything I've been able to find claims that Pick runs on a Unix > server. Has anyone ever run Pick on FreeBSD? It appears that these > folks are currently running Pick under NT, but I can't find any docs > anywhere that claim that this is possible. > > What I'd really like to do is come up with a migration plan to get > these folks away from Pick (since it's no longer supported or > developed) and onto something based on PostgreSQL and FreeBSD. > If anyone has any experience with this, I would greatly appreciate > any advice. > > TIA, > > -- Bill Moran > Potential Technologies > http://www.potentialtech.com Hi Bill, I hope the irc log below is at least some help. Sorry if the formatting comes out bad. Cheers...John john@jmv.com.au http://wwww.jmv.com.au 12:20:53 PM: Janacek: anyone here have any experience with the Pick database system? 12:21:23 PM: bwilsonFBS: I have experience avoiding it... 12:21:28 PM: Janacek: :) 12:21:34 PM: Janacek: can it be run on NT? 12:21:54 PM: bwilsonFBS: I have no idea 12:22:16 PM: Janacek came across it back in the late 80s early 90s, never got familiar with it 12:22:31 PM: bwilsonFBS: My experience has been that when I tell a client that I need data in such-and-such a way and their current system uses Pick, they get really sad. 12:22:52 PM: Janacek: there is no simple dump facility? 12:23:10 PM: Cowbert: the tools are pretty much all dead :) 12:23:18 PM: bwilsonFBS: It's not simple to dump multi-dimensional fields to a flat file. 12:23:35 PM: Janacek: right, so you have to write some dumping routines? 12:23:43 PM: bwilsonFBS: Pick lets you take any given field and arbitrarily decide to store multiple values for that field. 12:23:57 PM: bwilsonFBS: You can nest dimensions within a field to arbitrary depth. 12:24:40 PM: bwilsonFBS: Yes, and you have to generate your own keys to provide the relationship back to the former parent record. 12:25:00 PM: bwilsonFBS: E.G. Table with columns (A, B, C) 12:25:00 PM: bwilsonFBS: E.G. Table with columns (A, B, C) 12:25:09 PM: bwilsonFBS: Record 1 is (AAA, BBB, CCC) 12:25:27 PM: bwilsonFBS: Record 2 is ((AA2, AA3, AA4, AA5), BB6, CC7) 12:25:44 PM: bwilsonFBS: Record 3 is ((AA3, AA4), (BB5, BB6), (CC7, CC8)) 12:26:11 PM: bwilsonFBS: Record 4 is (((AB1, AB2), (AC1, AC2), (AD1, AD2)), BB, CC) 12:26:33 PM: bwilsonFBS: Now, how many flat files do you want from this simple, three column, four row table? 12:26:39 PM: Cowbert: wouldn't you be able to use something like Monarch Data Pump to sort out the many-many relationships? 12:27:09 PM: bwilsonFBS: It's not really many-many, it's one-to-many at the field level, and then one-to-many again. 12:27:45 PM: bwilsonFBS: You almost need the pk for subfields to be (tablename) + (fieldname) + (rowpk) + (value-instance-number) 12:28:55 PM: bwilsonFBS: The PK for sub-sub-fields would then be (tablename) + (fieldname) + (rowpk) + (value-instance-number) + (subvalue-instance-number) 12:29:57 PM: bwilsonFBS: It's a real pain in the tushy. 12:30:23 PM: bwilsonFBS: At some point, it makes more sense just to take all the subfields and output them as comma-separated values in a long character field so you can sort them out on the other side. 12:32:43 PM: bwilsonFBS: It's nasty 12:32:51 PM: bwilsonFBS: I'm told that those who program in Pick love it. 12:33:13 PM: bwilsonFBS: Those additional values are often used to track history of the field, for instance. The last instance is the current value. 12:33:40 PM: bwilsonFBS: Another common use is to pair data together. E.G. First Name and Last Name as two attributes within a single "Name" field. 12:34:17 PM: bwilsonFBS: Area Code/Phone Number, or even formatted/unformatted 12:34:42 PM: bwilsonFBS: [2195554848,(219) 555-4848] 12:35:09 PM: bwilsonFBS: The problem is, you need to be more of a mindreader than usual to do anything worthwhile with the resulting data. To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-questions" in the body of the message