This same database schema is used for the PostgreSQL, MySQL and ODBC examples. It is simple, but very capable. This ODBC example uses the PostgreSQL database as its ODBC database, so there will be a large similarity in this example to the PostgreSQL one.

Below is a description of the "dns_records" table. This output was obtained by executing "\dt dns_records" from within the psql utility after it connected to the database.

    Table "public.dns_records"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 zone        | text    | 
 host        | text    | 
 ttl         | integer | 
 type        | text    | 
 mx_priority | integer | 
 data        | text    | 
 resp_person | text    | 
 serial      | integer | 
 refresh     | integer | 
 retry       | integer | 
 expire      | integer | 
 minimum     | integer | 
Indexes: host_index btree (host),
         type_index btree ("type"),
         zone_index btree ("zone")

Below is a description of the "xfr_table" table. This output was obtained by executing "\dt xfr_table" from within the psql utility. This table is only necessary if you will be supporting zone transfers from DLZ. See the Worst Practices section of the site for reasons you should not use zone transfers when using DLZ.

 Table "public.xfr_table"
 Column | Type | Modifiers 
--------+------+-----------
 zone   | text | 
 client | text | 
Indexes: zone_client_index unique btree ("zone", client)

This configuration will provide the best performance because it uses the fewest queries to obtain the information it needs. BIND is built single threaded on most UN*X based systems so one database connection (as configured here) is generally sufficient. If your system builds BIND multi-threaded by default, then you should configure your server to use more database connections. The MySQL documentation has a list of which systems are built single threaded by default.

When setting up your configuration, pay very close attention to spaces! If you include a space where you shouldn't, or don't include a space where you should, either your query will not work with the database, or BIND may not be able to properly parse the query results. If in doubt, copy the SQL queries directly from this page (copy / paste).

dlz "odbc zone" {
   database "odbc 1
   {dsn=postgres user=postgres pass=postgres}
   {select zone from dns_records where zone = '%zone%'}
   {select ttl, type, mx_priority, case when lower(type)='txt' then '\"'
         || data || '\"' when lower(type)='soa' then data || ' ' || resp_person || ' '
         || serial || ' ' || refresh || ' ' || retry || ' ' || expire || ' ' || minimum
         else data end from dns_records where zone = '%zone%' and host = '%record%'}";             
};

If you must support zone transfers with DLZ, use the configuration below with this example schema. Notice the section of the configuration in GREEN. It is a pair of brackets with NO SPACES between it. It is important that the brackets have no spaces. We are telling the driver not to use an SQL query there. If there are any spaces between the brackets, the driver will think there is an SQL query to execute, and then fail when it does try to run it.

dlz "odbc zone" {
   database "odbc 1
   {dsn=postgres user=postgres pass=postgres}
   {select zone from dns_records where zone = '%zone%'}
   {select ttl, type, mx_priority, case when lower(type)='txt' then '\"'
         || data || '\"' when lower(type)='soa' then data || ' ' || resp_person || ' '
         || serial || ' ' || refresh || ' ' || retry || ' ' || expire || ' ' || minimum
         else data end from dns_records where zone = '%zone%' and host = '%record%'}
   {}
   {select ttl, type, host, mx_priority, case when lower(type)='txt' then '\"'
         || data || '\"' else data end, resp_person, serial, refresh, retry, expire,
        minimum from dns_records where zone = '%zone%'}
   {select zone from xfr_table where zone = '%zone%' and client = '%client%'}";
};