From anton@catalystinternet.com Tue Apr 8 16:51:31 2003 Return-Path: Delivered-To: goppelt@merc.goppelt.net Received: from hallwatch.org (mail.goppelt.net [192.168.2.220]) by merc.goppelt.net (Postfix) with ESMTP id C70B7238C4 for ; Tue, 8 Apr 2003 16:51:02 -0400 (EDT) Received: by hallwatch.org (Postfix) id 83E5ABC57; Tue, 8 Apr 2003 16:51:09 -0400 (EDT) Delivered-To: goppelt@goppelt.net Received: from topaz.catalystinternet.com (unknown [207.245.113.66]) by hallwatch.org (Postfix) with ESMTP id 4FE7CBBEC for ; Tue, 8 Apr 2003 16:51:09 -0400 (EDT) Received: from obsidian.catalystinternet.com (obsidian.catalystinternet.com [207.245.113.120]) by topaz.catalystinternet.com (8.12.8/8.12.8) with ESMTP id h38KqOM4000904 for ; Tue, 8 Apr 2003 16:52:24 -0400 Message-Id: <5.2.0.9.0.20030408162832.0358b0c0@pop.catalystinternet.com> X-Sender: anton@pop.catalystinternet.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Tue, 08 Apr 2003 16:50:56 -0400 To: Edmund Goppelt From: Anton Shepps Subject: Re: SQL Extraction Project In-Reply-To: <20030331194351.GD13943@merc.goppelt.net> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=====================_582369983==_" X-Spam-Status: No, hits=-3.4 required=5.0 tests=IN_REP_TO version=2.31 X-Spam-Level: --=====================_582369983==_ Content-Type: text/plain; charset="us-ascii"; format=flowed At 03:43 PM 3/31/2003, you wrote: >Tony, thanks for your help in extracting a month's worth of deed data. > >What I would like you to do is to extract all records with a >record_date in June, 2002 and send it to me. If you compress the >result, it should be small enough to email me. > >The database in question is proptax; the table is sales; the server >hallwatch.org. Let me know if you have any problems logging on. > >Since I intend to use your work as evidence in court, please mention >in your cover letter/bill how many records were extracted, the steps >you followed and how long each took, the sql commands to extract the >data, the size of the resulting file, as well as what you would charge >me to provide this service on a monthly basis going forward. > >If there's anything else you think would help clarify what's involved >to a non-technical audience, please put it in it. > >Thanks again for your help with this. > >-- > >Ed Goppelt >http://www.hallwatch.org Ed, Attached you'll find the file "records.zip" which is the compressed data for June 2002. Uncompressed, it was just over 4 megabytes in size and represents 18,568 records. The data compresses very well, so it's under a megabyte; it'll only take you seconds to retrieve the email. I used the following command to create the data file. The command opens the database and table, then runs a "SELECT" statement to actually specify which data is being retrieved. mysql -uanton -pxxxxxx -hhallwatch.org proptax -B -e"select * from sales where record_date >= '2002-06-01' AND record_date <= '2002-06-31';" >/tmp/records.txt Writing this statement was the bulk of the development work required. It was trivial. The SELECT statement is the most basic of SQL database statements; it would be covered during the two weeks of any modern course in databases. It took perhaps a minute to write the command, but only about five seconds to run and collect the data. I then used a commonly-used file archiver to compress the data into a ZIP file. In short, once access to the database was secured, composing this message to you took longer than any of the technical considerations involved. At my usual hourly rate of $100, this took 15 minutes of time. I'll invoice you $25 separately for it; if you require this data on an ongoing monthly basis, the charge would be lower since the entire process could be easily automated. For such an item I would probably bill you development time to create the automation, which would take an hour of time, then bill $100 per year for the data service, which would run unassisted and would email you results each month on the 1st of the month. If your needs were more rigorous, I could create a web interface at a slightly higher price, which would allow you to query any dates you liked at any time you liked and to retrieve the information via a web site. The bandwidth and computing time for something like this is so trivial that it is not a part of the considerations. Thanks again, Anton Shepps Catalyst Internet Inc. --=====================_582369983==_ Content-Type: application/zip; name="records.zip"; x-mac-type="705A4950"; x-mac-creator="705A4950" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="records.zip"