database not in correct order

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have a chat script I wrote years ago using DB_File, but as the database gets larger it's becoming much less stable. I've tried to create a dbhack.pl to recreate a mysql version of it. It appears to store all records but not in the correct order.

The script below prints at the bottom and this ALWAYS shows the real order of posts, but somehow it becomes distorted in my mysql database. After 4 attempts, I've noticed the mysql setup always appears in the same wrong order, so it's not randomly throwing things in there. Anyone know what might be the problem?

#!/usr/bin/perl -w

use strict;
use warnings;
use POSIX;
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);

use DB_File;
use DBI;



my $add;
my %chat;
my $chat = "chat.db";

tie %chat, "DB_File", "$chat", O_CREAT|O_RDWR, 0644, $DB_BTREE
    or die "Cannot open file 'chat': $!\n";

my $dbase = "*";
my $mysql_user = "*";
my $mysql_pass = "*";

print header, start_html();

###
# connect to database
###
my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user, $mysql_pass) o
+r
print DBI->errstr;
                                   

foreach my $key (reverse sort keys %chat) {

my ( $name, $message, $userip) = split /~~/, $chat{$key};

my $data = qq(INSERT INTO chat (name, message, mood, ip, posttime) VAL
+UES(?,?,?,?,?));
my $sth = $dbh->prepare($data);
$sth->execute($name, $message, "happy", $userip, "0:0:0") or die $dbh-
+>errstr;

print qq($name $message $userip<br><br>);
}
[download]

Comment on database not in correct order Download Code

Replies are listed 'Best First'.
Re: database not in correct order by dragonchild (Archbishop) on Nov 27, 2007 at 02:46 UTC
You should never assume an order to the results of a SQL query which doesn't have an ORDER BY clause. Without that clause, every single RDBMS states that the ordering is undefined. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re: database not in correct order by Herkum (Parson) on Nov 27, 2007 at 03:51 UTC
If this is a table in a MySQL database, I would change the posttime column to be a TIMESTAMP data type. That will remove the need for you to have insert that field. And as Dragonchild said, you cannot guarantee order in a database without an ORDER BY in your select statement. Primary keys for doing a sort are a bad approach. Do an ORDER BY on the posttime column, especially if you take my advice and have it as a TIMESTAMP.	[reply]
Re^2: database not in correct order by aquarium (Curate) on Nov 27, 2007 at 05:04 UTC
yep...as everyone has already indicated, database tables are sets, with no pre-defined time/sequence for results, unless you implement one (using SQL order by). This allows for the notion/use of atomic updates, e.g. (SQL pseudocode) UPDATE TABLE test SET some_time_column TO now() All the rows will be set with the same value returned by now() function. "set theory" applies to relational databases, and it breaks if values change over time. the hardest line to type correctly is: stty erase ^H	[reply]
Re^2: database not in correct order by CountZero (Bishop) on Nov 27, 2007 at 15:24 UTC
But given the speed at which the records will be included into the database, you are likely to end up with multiple records sharing the same `TIMESTAMP` value. In my version of MySQL the `TIMESTAMP` has only a one second granularity. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re: database not in correct order by graff (Chancellor) on Nov 27, 2007 at 03:34 UTC
Since you have the good fortune of knowing that the old DBM file is always returning records in the desired order, all you have to do is make up a "pseudo" posttime value for each record so that these values preserve the ordering in mysql. E.g. the first record would have a posttime that is (roughly) when the service began, the last record from the DBM file would be set to the time when the DBM file was phased out, and all intermediate records would have values that differ by a regular increment ((end_time - begin_time)/(record_count+2) would probably suffice). I gather from one of your other replies that the DBM file does not include posttime values, but that as new posts are added to the mysql table, these values will be properly stored. So just be sure that the "pseudo" posstime values that you make up for the old data do not overlap with the "true" values being generated by the ongoing collection. BTW, shouldn't posttime values include a date as well as hours:minutes:sec? UPDATE: A couple other points I forgot to notice/mention: First, it looks like you actually do have key values in the DBM file that, when sorted, produce the desired ordering. What kind of string makes up a key value? Can that string be reasonably converted into some sort of time expreassion for use as the "posttime" value? Second, if you have a lot of records to insert, you'll want to move the "prepare()" statement outside the foreach loop -- you are already using placeholders, so you might as well use them as they were intended to be used: prepare once, execute many times with different values passed in each iteration of the execute call.	[reply]
Re: database not in correct order by tuxz0r (Pilgrim) on Nov 27, 2007 at 02:42 UTC
I don't understand what order you want to see them in in the database. You are inserting them into the table with a post time of 0:0:0, so what is the primary key in the `chat` table? If the post time is stored in your DB_File implementation, I would suggest keeping it and making it part of the key to each entry in the chat table. Otherwise, DBMS's typically sort (on queries) based on the primary key, so if you have one defined, that's the order they probably come back in from your select. YOu can also use the SQL `ORDER BY` statement to get a sort order other than the primary key as well. --- `echo S 1 [ Y V U \| perl -ane 'print reverse map { $_ = chr(ord($_)-1) } @F;'` Warning: Any code posted by tuxz0r is untested, unless otherwise stated, and is used at your own risk.	[reply]
Re^2: database not in correct order by Anonymous Monk on Nov 27, 2007 at 03:03 UTC
The sort is done by the primary key ID. The time is entered in as 0:0:0 because I originally didn't store time in my original DB_File database but I'm going to start recording it now.	[reply]
Re^3: database not in correct order by dws (Chancellor) on Nov 27, 2007 at 05:42 UTC
The sort is done by the primary key ID. Assuming that you created the table with `... id int auto_increment, primary key (id), ...` [download] and you don't specify an `ORDER BY` clause for something other than `id`, you should get the records back in the order you put them in. Is it possible you forgot the `auto_increment`?	[reply] [d/l] [select]