Bug#425027: libapache2-mod-log-sql: scoreboard patch uses non-portable sql incompatible with pgsql

[James Cloos]

>>>>> "Thomas" == Thomas Goirand <[EMAIL PROTECTED]> writes:

Thomas> The goal of making a INSERT IGNORE query is to create a record for the
Thomas> current month and year, plus the specified domain. As there is a UNIQUE
Thomas> KEY, there will be only ONE record, and the INSERT will fail if the
Thomas> record exists. The INSERT IGNORE make it works if there was a record
Thomas> already for the current month.

I understand the reason behind using insert ignore

Thomas> As you seem to know about pgsql, do you know what I could do so it does
Thomas> the same as when using MySQL? Any help would be much appreciated.

I couldn't find any drop-in replacement for insert ignore.  I'm sure the
definitive answer from the pgsql lists would be to call a function to do
it.  This page has an example which I've copied below:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html

,----
| CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
| CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
| $$
| BEGIN
|   LOOP
|     UPDATE db SET b = data WHERE a = key;
|     IF found THEN
|       RETURN;
|     END IF;
| 
|     BEGIN
|       INSERT INTO db(a,b) VALUES (key, data);
|       RETURN;
|     EXCEPTION WHEN unique_violation THEN
|       -- do nothing
|     END;
|   END LOOP;
| END;
| $$
| LANGUAGE plpgsql;
`----

So I'd do something like:

CREATE OR REPLACE FUNCTION update_scoreboard(vh TEXT, dom TEXT, bytes INT) 
RETURNS VOID AS
$$
DECLARE
m INT;
y INT;
BEGIN
  SELECT INTO m to_char(now(), 'MM')::INT;
  SELECT INTO y to_char(now(), 'YYYY')::INT;
  LOOP
    UPDATE scoreboard SET count_impressions = count_impressions+1, bytes_sent = 
bytes_sent+bytes  WHERE domain=dom AND vhost=vh and month=m and year=y;
    IF found THEN
      RETURN;
    END IF;

    BEGIN
      INSERT INTO scoreboard(domain,vhost,month,year,count_impressions) VALUES 
(dom, vh, m, y, 0);
      RETURN;
    EXCEPTION WHEN unique_violation THEN
      -- do nothing
    END;
  END LOOP;
END;
$$
LANGUAGE plpgsql;


and then have the C call:  'SELECT update_scoreboard(%s, %s, %d)',
  scoreboard_domain,scoreboard_subdomain,extract_bytes_sent(r, a)

I'm not sure whether specifying the table name as an arg to the function
will do the right thing.  If it will, then the function might look like:

CREATE OR REPLACE FUNCTION update_scoreboard(tbl TEXT, vh TEXT, dom TEXT, bytes 
INT) RETURNS VOID AS
$$
DECLARE
m INT;
y INT;
BEGIN
  SELECT INTO m to_char(now(), 'MM')::INT;
  SELECT INTO y to_char(now(), 'YYYY')::INT;
  LOOP
    UPDATE tbl SET count_impressions = count_impressions+1, bytes_sent = 
bytes_sent+bytes  WHERE domain=dom AND vhost=vh and month=m and year=y;
    IF found THEN
      RETURN;
    END IF;

    BEGIN
      INSERT INTO tbl (domain,vhost,month,year,count_impressions) VALUES (dom, 
vh, m, y, 0);
      RETURN;
    EXCEPTION WHEN unique_violation THEN
      -- do nothing
    END;
  END LOOP;
END;
$$
LANGUAGE plpgsql;


-JimC
-- 
James Cloos <[EMAIL PROTECTED]>         OpenPGP: 1024D/ED7DAEA6


-- 
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]