Developers of Sympa

[IKEDA Soji <address@concealed>]

Hi developers,

My question is at the end.


Problem
-------

This is a code to insert a user into admin_table (partly omitted):

```
if ($sdm->do_prepared_query(
    q{INSERT INTO admin_table
      (..., role_admin, user_admin, list_admin, robot_admin)
      VALUES (..., ?, ?, ?, ?)},
    ..., $role, $email, $list->{'name'}, $list->{'domain'}
    )
) {
    # Success
} else {
    # Error or duplicate key
}
```

Code above fails when error occurred (e.g. database server gone)
_or_ when the user has already been stored in the table.

There seems no portable way to distinguish between error and
duplicate key.

As a result, just as current Sympa does, everytime user try to add
owner/editor already exists, syslog records database error.


Possible solution
-----------------

OTOH code below can distingush between error and duplicate key,
because query does not fail even when the user has already been
stored.

Note:

- This looks *portable*, i.e. looks working at least with four
  RDBMs Sympa supports.

- This is *atomic*, because test and insersion are completed with
  single query.

```
if ($sth = $sdm->do_prepared_query(
    q{INSERT INTO admin_table
      (..., role_admin, user_admin, list_admin, robot_admin)
      SELECT ..., ?, ?, ?, ?
      FROM dual
      WHERE NOT EXISTS(
        SELECT 1
        FROM admin_table
        WHERE role_admin = ? AND user_admin = ? AND
              list_admin = ? AND robot_admin = ?
      )},
    ..., $role, $email, $list->{'name'}, $list->{'domain'},
    $role, $email, $list->{'name'}, $list->{'domain'}
    )
) {
    if ($sth->rows) {
        # Success
    } else {
        # Duplicate key
    }
} else {
    # Error
}
```

However, PostgreSQL and SQLite do not have dummy table "dual"
(Oracle and MySQL/MariaDB have it).  To make query above work, we
have to make it.

With PostgreSQL:
```
CREATE VIEW dual AS SELECT 'X'::varchar(1) AS dummy;
```

With SQLite:
```
CREATE VIEW dual AS SELECT 'X' AS dummy;
```

Question
--------

* Is use of table (or view) "dual" appropriate?

* Is there portable way else not using "dual"?


Regards,
-- Soji

-- 
株式会社 コンバージョン
ITソリューション部 システムソリューション1グループ 池田荘児
〒140-0014 東京都品川区大井1-49-15 アクセス大井町ビル4F
e-mail address@concealed  TEL 03-6429-2880
https://www.conversion.co.jp/