Rotating tables (and sybase)

Got a question on IRC today about the equivalent of MySQLs MAX_ROW setting on a table. The problem at hand was more specific. The table should always contain N number of rows, if a new row was inserted, the oldest should be deleted. I wrote this quick example to provide a possible solution.

CREATE TABLE top10data (
        id      NUMBER(2) NOT NULL,
        name    VARCHAR2(20) NOT NULL
);

CREATE VIEW top10 AS
        SELECT id,name FROM top10data;

INSERT INTO top10data VALUES(1,'one');
INSERT INTO top10data VALUES(2,'two');
INSERT INTO top10data VALUES(3,'three');
INSERT INTO top10data VALUES(4,'four');
INSERT INTO top10data VALUES(5,'five');
INSERT INTO top10data VALUES(6,'six');
INSERT INTO top10data VALUES(7,'seven');
INSERT INTO top10data VALUES(8,'eight');
INSERT INTO top10data VALUES(9,'nine');
INSERT INTO top10data VALUES(10,'ten');
COMMIT;

-- trigger on the view, catch the insert and 
-- do it on the actuall table
CREATE OR REPLACE TRIGGER top10_trigger
        INSTEAD OF INSERT ON top10 FOR EACH ROW
BEGIN
        UPDATE top10data SET id=id+1;
        DELETE FROM top10data WHERE id>10;
        INSERT INTO top10data VALUES(1,:new.name);
END top10_trigger;
/

-- insert example, note that we insert on the view
SQL> select * from top10 order by id;

        ID NAME
---------- --------------------
         1 one
         2 two
         3 three
         4 four
         5 five
         6 six
         7 seven
         8 eight
         9 nine
        10 ten

10 rows selected.

SQL> insert into top10(name) values('New one');

1 row created.

SQL> select * from top10 order by id;

        ID NAME
---------- --------------------
         1 New one
         2 one
         3 two
         4 three
         5 four
         6 five
         7 six
         8 seven
         9 eight
        10 nine

10 rows selected.

This solution may have some concurrency problems if it is heavly populated. To get around that problem you could use a sequence and increment the ID from the trigger and delete based on rank() of the numbers.

On another issue
I've been working a bit with Sybase lately.
To say the least, I do not like it.

• The way it handles users, permissions and the "database owner" thing. I don't like it.
  
• Data space and log space, whats this about. Isn't it just better to define logs and a number of "data storage thingies" (why don't we call them tablespaces) for anyone to use. Why pre-assign space and devices to a database (schema'ish) . I don't like it.
  
• The structure of transaction logging and checkpointing. It just feels ancient. I don't like it.
  
• If you do a database dump on Solaris/SPARC and try to load it on a Linux/x86 machine you need to do a data conversion process thingie. Whats that about, sure big endian vs. small endian when using raw devices. But a dump should be universal. Sybase should forget about always working with plain raw devices (disk, tape etc) and allow people to actually make use of the file system provided. Besides if you want "endian less" raw devices just use a cds volume under vxvm. Transportable tablespaces anyone? I don't like it.
  
• The basic footprint is tiny compared to Oracle. The installation takes about 10 minutes and the sybase server uses about 40Mb of memory when you first start it up. So for a basic testing / dev database it is quite nice. I do like it.
  

And I've been messing around a bit with DB 2 as well. I'm not sure I like it yet but it is miles better than Sybase. I'll have to think about that one for a while (and play with it a lot more).