docs/README.sequence
author rejo
Mon, 25 Jun 2007 21:55:33 +0000
changeset 27 782f142d096d
parent 8 47dd15d8bb8c
permissions -rw-r--r--
[feladat @ 74] Added missing record_owners SQL table creation during install. Fix by John Morris.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
8
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     1
README.sequence - Additional documentation on manual inserts (using
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     2
poweradmin along with other interfaces to the powerdns database).
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     3
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     4
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     5
Removal of "sequence updater"
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     6
-----------------------------------------------------------------------
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     7
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     8
Up to poweradmin version 1.2.7-patched, the code included the so
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
     9
called "sequence updater". It was written to synchronize poweradmin
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    10
with the database after manual inserts of zones and records. This
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    11
happens if you insert new zones or records by hand, or have other
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    12
interfaces than poweradmin talking to the database as well.
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    13
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    14
The 1.2.7-patched version of poweradmin was using the PEAR::DB module,
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    15
using it's nextID() function. It tells the application what will be
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    16
the next ID for insertion. However, mysql has "auto_increment" and
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    17
pgsql has "serial". Both allow to do insert with ID's created on the
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    18
run, without the need of seperate sequence numbers.
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    19
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    20
In order to get rid of the incidental errors and the need to hit the
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    21
"synchronize database" from time to time, the "sequence updater" has
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    22
been removed and the code has been updated to use the "auto_increment"
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    23
and "serial" functionality. As long as you do manual inserts the
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    24
correct way, this will not cause any problems.
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    25
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    26
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    27
Insert new records and zones the correct way (using other tools)
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    28
-----------------------------------------------------------------------
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    29
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    30
According to the documentation of powerdns (2.9.20), the id column of
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    31
the tables domains and records in a mysql setup are created using the
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    32
"auto_increment" option and in a pgsql setup using the "serial"
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    33
option. [1] 
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    34
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    35
This will allow for auto increments of the id field, as long as no one
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    36
does a manual insert, specifying the id. So, let's say we have created
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    37
a table, one in mysql, one in pgsql:
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    38
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    39
  CREATE TABLE xmpl_tbl (id INT auto_increment, text TEXT);
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    40
  CREATE TABLE xmpl_tbl (id SERIAL PRIMARY KEY, text TEXT);
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    41
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    42
This insert will work in pgsql only:
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    43
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    44
  INSERT INTO xmpl_tbl VALUES (DEFAULT,'First insert test");
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    45
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    46
These inserts will work in mysql only:
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    47
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    48
  INSERT INTO xmpl_tbl VALUES (NULL,'Third insert test"); 
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    49
  INSERT INTO xmpl_tbl VALUES ('','Fourth insert test"); 
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    50
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    51
This will work in both mysql and pgsql (as long as id didn't exist
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    52
already), but it will break "auto increment" feature in pgsql [2] (can
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    53
be fixed using setval() functionality in pgsql):
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    54
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    55
  INSERT INTO xmpl_tbl VALUES (42,'Fifth insert test");
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    56
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    57
This will work in both mysql and pgsql:
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    58
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    59
  INSERT INTO xmpl_tbl (text) VALUES ('Second insert test");
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    60
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    61
So, in other words: if you want to insert records and zones into the
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    62
powerdns database using other tools and interfaces than poweradmin,
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    63
you should make sure you are using the correct syntax.
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    64
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    65
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    66
Limitations
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    67
-----------------------------------------------------------------------
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    68
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    69
Removing the "sequence updater" removes the possibilty to use the
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    70
poweradmin code against other databases than mysql and pgsql (or at
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    71
least, it is untested).
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    72
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    73
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    74
More information
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    75
-----------------------------------------------------------------------
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    76
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    77
More information can be found here:
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    78
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    79
 - PowerDNS manual, section backends in detail
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    80
   <http://downloads.powerdns.com/documentation/html/generic-mypgsql-backends.html>
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    81
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    82
 - Postgresql documentation, section Operational Questions 
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    83
   <http://www.postgresql.org/files/documentation/books/aw_pgsql/node196.html#SECTION0029916000000000000000>
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    84
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    85
 - pgsql-bugs mailinglist archive
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    86
   <http://archives.postgresql.org/pgsql-bugs/2004-11/msg00340.php>
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    87
   <http://archives.postgresql.org/pgsql-bugs/2004-11/msg00344.php>
47dd15d8bb8c [feladat @ 20]
rejo
parents:
diff changeset
    88