[SQL] reinitialize a sequence?

[Dan Lyke]

Bruno Boettcher writes:
> is there a simple way to tell all sequences to take the max value +1 of
> their respective tables? (a bit like the vacuum command?)

This is completely gross, but what I've done:


#!/usr/bin/perl -w
use strict;
use DBI;
my ($dbh);
sub BEGIN
{
$dbh = DBI->connect('DBI:Pg:dbname=',
'zzz',
'z')
or die $DBI::errstr;
}
sub END
{
$dbh->disconnect;
}


sub UpdateSequenceFor($)
{
my ($table) = @_;
my ($sql,$sth,$id,$row);

$sql = "SELECT max(id) FROM $table";
$sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
$sth->execute or die $sth->errstr."\n$sql\n";
if ($id = $sth->fetchrow_arrayref)
{
$id = $id->[0];
$sql = "SELECT nextval('".$table."_id_seq')";
$sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
$sth->execute or die $sth->errstr."\n$sql\n";
while (($row = $sth->fetchrow_arrayref) && ($row->[0] < $id))
{
$sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
$sth->execute or die $sth->errstr."\n$sql\n";
}
}
}

# update the sequence for each table:
foreach ('users','blogentries','blogcomments','blogcommenthistory')
{
UpdateSequenceFor($_);
}

Re: [SQL] "'" in SQL INSERT statement

[Dan Lyke]

Alessio Bragadini writes:
> Markus Wagner wrote:
> > I have some data that I wish to transfer into a database using perl/DBI.
> 
> If you use Perl DBI you should issue statements like
> $dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
>   undef, $value1, $value2);

$dbh->quote() also puts in the appropriate escapes, ala:

   $dbh->do('INSERT INTO TABLE (field) VALUES ('.$dbh->quote($value).')');

Dan

Re: [SQL] Serials.

[Dan Lyke]

Grant writes:
> I have a message board. Where users can send each other messages. I
> doubt I will ever get 2147483647 messages, but I want to make sure I
> never get an error where the message isn't sent.

Think about loads. If your users are going to be posting 10
messages/second, that's 864000 messages per day, you won't wrap for
nearly 7 years. I've got a pretty heavy mail load, including spam I
probably get 300 messages/day, weekends are lighter, so if you've got
a bunch of weenies who are subscribed to a gazillion mailing lists
you're talking three thousand users for six and a half years.

A little light if you're planning on being the next Hotmail (A test
account set up there gets 70 spams/day without my ever publishing the
address), but for your average mid-range discussion forum you're
probably good for a while. I doubt that, say, Salon's TableTalk forum
gets even 10k new messages per day.

> I guess the best option is to create a function that changes all the
> ids to the lowest values possible and then set the sequence to the
> maximum value + 1.

Better idea, if one of your deletion criteria is going to be age,
would just be to wrap the IDs.

Dan

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

[SQL] Using Random Sequence as Key

[Dan Lyke]

Bernardo de Barros Franco writes:
> Hello, I was wondering if noone can help me maybe someone could at least
> give me some directions where to look for info or where to ask:
> I wanted to index a table by a random key.

As others have pointed out, making a unique random primary key is
tough. What I do for my cookie on my web based login system is have
two fields in my database, one's the "id SERIAL", the other is a
"magiccookie CHAR(16)" which I populate with 16 random characters on
the initial insert.

My cookie is then of the format "id/magiccookie". In my login
verification code I split on the "/" character and query on "WHERE
id=$id AND magiccookie=$magiccooke". Even though the "id" field is
encoded in the cookie in plain text a cracker can't just guess at the
user id number because that 16 character magiccookie needs to match as
well.

This also lets me be pretty loose about the id information, I can use
it in other public places, because only the magiccookie needs to be
restricted to being known by the logged in user.

Dan

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

[SQL] Case Insensitive Queries

[Dan Lyke]

Mark writes:
> Is it possible to execute a query using a where clause that allows case
> insensitive comparison between a field and text.

select * from account where upper(username) = upper('test')

(Upper used because, as has been remarked on this list and in other places,
folding from richer character sets is likely to get better matches this way).

And yes, you can create an index on upper(fieldname).

Dan

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