[lug] Databases

[metcalf at attglobal.net]

This is great info, the sort of stuff we need to evaluate databases.
Thanks, George!
----- Original Message -----
From: "George Sexton" <gsexton at mhsoftware.com>
To: "bill ehlert" <bill_ehlert at yahoo.com>; <lug at lug.boulder.co.us>
Sent: Thursday, June 29, 2000 10:11 AM
Subject: RE: [lug] Databases


> I would be happy to present on SQL Anywhere.
>
> Wayde: Just let me know the proposed date and I will coordinate with Sean.
>
> Here is my take on why the features I listed are important.
>
> 1) Declarative Referential Integrity
>
> This means that you define the relationships in the data and the database
> enforces them. If you have a coding error that would violate the
> relationship, then the update or insert fails. This just makes programs
> faster to develop and results in fewer coding bugs. Postgresql supports
this
> in version 7.0. MySQL does not.
>
> 2) True Transaction Log
>
> The transaction log stores a history of updates. If the drive containing
the
> database fails, and you have the transaction log on another drive (which I
> do), then you can restore from your last backup and apply the transaction
> log. IOW, you lose almost 0 data.
>
> 3) Stored Procedure Language
>
> On any sufficiently complex database you will have reports that cannot be
> created without resorting to procedural code. People will want some
complex
> set of queries that cannot be coded in a single statement. The simplest
way
> to handle this is to use a stored procedure and call that stored procedure
> from your reporting tool. It can also be useful for performing data
> intensive operations because only the result set is returned by the
server.
> If you have some very complex operation that requires a table scan of a
> 400MB table, this can really boost performance. The down side to using
> stored procedures is that if you want to change databases, this is code
that
> must be re-written.
>
> 4) Outer Joins
>
> Again, given any sufficiently complex database you will need these. A
normal
> or equijoin, says something like:
>
> select *
> from Customer
> Inner Join Invoice
> ON Invoice.Cust_ID=Customer.Cust_ID
>
> or in older style
>
> Select * from Customer, invoice where Invoice.Cust_ID=Customer.Cust_ID
>
> This join fails in the instance for customers where there is no invoice. A
> typical report might be: Prepare a summary report of customers that shows
> all customers, the number of invoices, and the sum of sales for a given
date
> range. With an outer join you would write this as:
>
> select
> Customer.Cust_ID,
> Customer_Name,
> count(Invoice.Invoice_No) as InvCount,
> sum(Invoice.Total) as InvSum
> From customer
> Left Outer Join Invoice
> On Invoice.Cust_ID=Customer.Cust_ID
> where
> Invoice_Date between '1 Jan 1999' and '1 Jan 2000'
> group by Customer.Cust_ID,Customer_Name
>
> If the database does not support outer joins you have to write something
> like:
>
> select
> Customer.Cust_ID,
> Customer_Name,
> count(Invoice.Invoice_No) as InvCount,
> sum(Invoice.Total) as InvSum
> From customer
> Inner Join Invoice
> On Invoice.Cust_ID=Customer.Cust_ID
> where
> Invoice_Date between '1 Jan 1999' and '1 Jan 2000'
> group by Customer.Cust_ID,Customer_Name
> UNION
> select Customer.Cust_ID,
> Customer_Name,
> 0,
> 0.00
> From Customer
> where Customer.Cust_ID NOT IN
> (select distinct Cust_ID from Invoice where where
> Invoice_Date between '1 Jan 1999' and '1 Jan 2000')
>
> The additional UNION clause can get very, very cumbersome on a complex
> statement. Postgresql is supposed to support this in version 7.1.
>
>
> 5) JDBC suport
>
> We do Java Servlet development so this is a requirement for us. Postgresql
> supports this, but the driver is kind of shaky in some respects.
>
> 6) Replication
>
> If you need it, there is no substitute. A friend of mine wrote an app for
a
> company with 16 branch offices and multiple travelling salesmen. Using
> replication, the sales people can have a complete disconnected copy of the
> database on their laptop at all times without requiring a LAN/WAN/Internet
> connection. When they reconnect, changes that they make to the data are
> replicated up to their parent office, and from there up to the
headquarters.
> SQL Anywhere supports replication in such a way that only each branch's
data
> is replicated downwards to it. Some large commercial apps like Siebel use
> SQL Anywhere for this purpose. It's also useful when you need remote data,
> but you are dependent upon an unstable connection. I.E. you have a
> monitoring system stuck on a hilltop someplace that needs the database to
> perform lookups and also log information. Using a replication system, it's
> transparent to your application when the WAN link goes down. When the WAN
> link comes up, the database just re-synchronizes. You can do this yourself
> but it takes code and is hard to get right.
>
>
> > -----Original Message-----
> > From: bill ehlert [mailto:bill_ehlert at yahoo.com]
> > Sent: Thursday, June 29, 2000 12:52 AM
> > To: George Sexton
> > Subject: RE: [lug] Databases
> >
> >
> >
> >
> > **  would you mind posting a short, general
> >     discussion of the nature and usefulness
> >     of each of those 6 features?
> >
> >     1 or 2 i think i understand, but ...
> >
> >     what the heck is the use of replicating
> >     data on various machines, other than
> >     perhaps super-backup???
> >
> >     also, hope you'll take up the offer
> >     to do a presentation on SQLA
> >
> >
> > --- George Sexton <gsexton at mhsoftware.com> wrote:
> > > We have used Sybase SQL Anywhere on Linux. It's
> > > less expensive than Oracle,
> > > but has the features that I think make up an
> > > industrial strength DB:
> > >
> > > Declarative Referential Integrity
> > > True Transaction Log
> > > Stored Procedure Language
> > > Outer Joins
> > > JDBC Support
> > >
> > > In addition to these features, perhaps the
> > > killer feature of SQLA is the
> > > replication.
> >
> >   <chop>
> >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Get Yahoo! Mail - Free email you can access from anywhere!
> > http://mail.yahoo.com/
>
>
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
>