Re: [GENERAL] check date validity

[Bill Gribble]

On Fri, 2004-01-16 at 06:58, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> "LitelWang" <[EMAIL PROTECTED]> writes:
> > I need this function :
> > CheckDate('2002-02-29') return false
> > CheckDate('2002-02-28') return true
> 
> Why would you want to do that?  Just try to insert '2002-02-29' into
> your DATE column, and PostgreSQL will complain.

But it won't complain usefully.  It will just abort the transaction. 
It's difficult to determine what went wrong when Postgres craps out,
which is at least in part why many on this list recommend duplicating
all the database validation logic in your application for EVERY type.

To me, this seems like a waste of effort, since both the application and
the DB server have to confirm that every date (for example, but applies
to every other type as well) is valid.  But I can't see how to do it any
other way, since the prevailing consensus among the PG devs seems to be
that any problem with the values of data is an application problem, not
a database problem, so don't expect to get any help from the server
other than "Sorry, that transaction is now gone.  Hope you can reproduce
the work! Have a nice day."

Thanks,
b.g.




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Join query on 1M row table slow

[Bill Gribble]

On Tue, 10 Feb 2004, CSN wrote:
> 
> I have a pretty simple select query that joins a table
> (p) with 125K rows with another table (pc) with almost
> one million rows:
> 
> select p.*
> from product_categories pc
> inner join products p
> on pc.product_id = p.id
> where pc.category_id = $category_id
> order by p.title
> limit 25
> offset $offset

This idiom looks to me a lot like "results paging".  You have a query
that returns a lot of rows, and you are formatting them one page at a
time in your CGI or whatever.  

In PostgreSQL, cursors do this very well:

BEGIN;
DECLARE resultset CURSOR FOR 
   select p.* from product_categories pc 
   inner join products p on pc.product_id = p.id  
   where pc.category_id = $category_id
   order by p.title ;

MOVE $offset IN resultset;
FETCH 25 FROM resultset;
[ repeat as necessary  ];

This does use some resources on the server side, but it is very much
faster than LIMIT/OFFSET. 

The biggest "gotcha" about cursors is that their lifetime is limited to
the enclosing transaction, so they may not be appropriate for CGI-type
applications. 

Bill Gribble



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

MySQL is not free software. Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

[Bill Gribble]

On Wed, 2003-10-08 at 16:23, Joshua D. Drake wrote:
>   Here is the simple thing about MySQL licensing. It is GPL. If you 
> modify the mySQL source or you link a proprietary app to mySQL without
> a commercial license. You must distrubute your changes and or 
> application as GPL or GPL compatibile.

You have two contradictory statements here, which unfortunately
represent the internal contradictions in MySQL's license (at least,
those versions after version 3.23.19, when MySQL AB adopted the current
licensing scheme).

Certainly, if MySQL is licensed under the GPL, you must distribute or
make available source code to any changed version of MySQL that you
distribute, or any other derivative works of MySQL that you distribute. 
However, MySQL's stated license makes far greater requirements on those
who use MySQL. 

Even though many distributors of MySQL, including the normally very
license-conscious Debian GNU/Linux, include only the GPL as its license,
there are in fact additional constraints which limit the rights that are
given by the GPL.   MySQL AB's license information web page [1] includes
in plain language what their intent is, and that intent is not the GPL,
nor is it compatible with the GPL.

The non-commercial (free-of-charge) MySQL license extends the
requirement to make available source code to "your application",
regardless of whether or not your application is a derived work of
MySQL.  All practical interpretations of the GPL, including the FSF's,
exclude from the requirement to distribute source code any works that
are collected by "simple aggregation", meaning they are present on the
same distribution medium or in the same distribution package as the
licensed work, but are not related to the licensed work by the sharing
of licensed components.  MySQL does not distinguish between derivative
works of MySQL and those that are collected along with it by simple
aggregation. 

So, for example, if I wish to sell a version of Debian with a
proprietary, closed-source installation tool (which does not use or
relate to MySQL in any way) and I wish to also include MySQL and its
source code in my distribution, I am required to get a commercial
license from MySQL.  That is not consistent with the terms of the GPL
under which I received MySQL from Debian. 

I don't know how to put it more plainly than that.  Even though MySQL AB
claims that their product is licensed under the GPL, it is not, because
they put significant additional license terms on it that remove some
rights given by the GPL.   The overall license terms of MySQL do not
meet any standard of "Free software" licenses that I know, including the
Debian Free Software Guidelines [2].  I believe that Debian and other
GNU/Linux distributions should move MySQL to their non-free sections,
along with other software that is "free for non-commercial use". 

The consequences for any commercial enterprise using MySQL in any way
must be very closely examined, and certainly aren't obvious in the way
that the consequences of the GPL are obvious.

Thanks,
Bill Gribble

[1] http://www.mysql.com/products/licensing.html
[2] http://www.debian.org/social_contract#guidelines


 









---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings