xref: /netbsd-src/external/bsd/openldap/dist/servers/slapd/back-sql/rdbms_depend/README (revision e670fd5c413e99c2f6a37901bb21c537fcd322d2) 
1Author: Pierangelo Masarati <ando@OpenLDAP.org>
2
3Back-sql can be tested with sql-test000-read; it requires a bit of work
4to get everything up and running appropriately.
5
6This document briefly describes the steps that are required to prepare
7a quick'n'dirty installation of back-sql and of the related RDBMS
8and ODBC; Examples are provided, but by no means they pretent
9to represent an exhaustive source of info about how to setup the ODBC;
10refer to the docs for any problem or detail.
11
12Currently, the system has been tested with IBM db2, PostgreSQL and MySQL;
13basic support and test data for other RDBMSes is in place, but as of
14today (November 2004) it's totally untested.  If you succeed in running
15any of the other RDBMSes, please provide feedback about any required
16change either in the code or in the test scripts by means of OpenLDAP's
17Issue Tracking System (http://www.openldap.org/its/).
18
191) slapd must be compiled with back-sql support, i.e. configure
20with --enable-sql switch.  This requires an implementation of the ODBC
21to be installed.
22
232) The ODBC must be set up appropriately, by editing the odbc.ini file
24in /etc/ (or wherever your installation puts it) and, if appropriate,
25the odbcinst.ini file.  Note: you can also use custom odbc.ini and
26odbcinst.ini files, provided you export in ODBCINI the full path to the
27odbc.ini file, and in ODBCSYSINI the directory where the odbcinst.ini
28file resides.
29Relevant info for our test setup is highlighted with '<===' on the right.
30
312.1) PostgreSQL
32
332.1.1) Add to the odbc.ini file a block of the form
34
35[example]                        <===
36Description         = Example for OpenLDAP's back-sql
37Driver              = PostgreSQL
38Trace               = No
39Database            = example    <===
40Servername          = localhost
41UserName            = manager    <===
42Password            = secret     <===
43Port                = 5432
44;Protocol            = 6.4
45ReadOnly            = No
46RowVersioning       = No
47ShowSystemTables    = No
48ShowOidColumn       = No
49FakeOidIndex        = No
50ConnSettings        =
51
522.1.2) Add to the odbcinst.ini file a block of the form
53
54[PostgreSQL]
55Description     = ODBC for PostgreSQL
56Driver          = /usr/lib/libodbcpsql.so
57Setup           = /usr/lib/libodbcpsqlS.so
58FileUsage       = 1
59
602.2) MySQL
61
622.2.1) Add to the odbc.ini file a block of the form
63
64[example]                        <===
65Description         = Example for OpenLDAP's back-sql
66Driver              = MySQL
67Trace               = No
68Database            = example    <===
69Servername          = localhost
70UserName            = manager    <===
71Password            = secret     <===
72ReadOnly            = No
73RowVersioning       = No
74ShowSystemTables    = No
75ShowOidColumn       = No
76FakeOidIndex        = No
77ConnSettings        =
78SOCKET              = /var/lib/mysql/mysql.sock
79
802.2.2) Add to the odbcinst.ini file a block of the form
81
82[MySQL]
83Description     = ODBC for MySQL
84Driver          = /usr/lib/libmyodbc.so
85FileUsage       = 1
86
872.3) IBM db2
88[n.a.]
89
903) The RDBMS must be setup; examples are provided for my installations
91of PostgreSQL and MySQL, but details may change; other RDBMSes should
92be configured in a similar manner, you need to find out the details by
93reading their documentation.
94
953.1) PostgreSQL
96
973.1.1) Start the server
98on RedHat:
99[root@localhost]# service postgresql start
100on other systems: read the docs...
101
1023.1.2) Create the database:
103[root@localhost]# su - postgres
104[postgres@localhost]$ createdb example
105
1063.1.3) Create the user:
107[root@localhost]# su - postgres
108[postgres@localhost]$ psql example
109example=> create user manager with password 'secret';
110example=> <control-D>
111
1123.1.4) Populate the database:
113[root@localhost]# cd $SOURCES/servers/slapd/back-sql/rdbms_depend/pgsql/
114[root@localhost]# psql -U manager -W example
115example=> <control-D>
116[root@localhost]# psql -U manager example < backsql_create.sql
117[root@localhost]# psql -U manager example < testdb_create.sql
118[root@localhost]# psql -U manager example < testdb_data.sql
119[root@localhost]# psql -U manager example < testdb_metadata.sql
120
1213.1.5) Run the test:
122[root@localhost]# cd $SOURCES/tests
123[root@localhost]# SLAPD_USE_SQL=pgsql ./run sql-test000
124
1253.2) MySQL
126
1273.2.1) Start the server
128on RedHat:
129[root@localhost]# service mysqld start
130on other systems: read the docs...
131
1323.2.2) Create the database:
133[root@localhost]# mysqladmin -u root -p create example
134(hit <return> for the empty password).
135
1363.2.3) Create the user:
137[root@localhost]# mysql -u root -p example
138(hit <return> for the empty password)
139mysql> grant all privileges on *.* \
140  to 'manager'@'localhost' identified by 'secret' with grant option;
141mysql> exit;
142
1433.2.4) Populate the database:
144[root@localhost]# cd $SOURCES/servers/slapd/back-sql/rdbms_depend/mysql/
145[root@localhost]# mysql -u manager -p example < backsql_create.sql
146[root@localhost]# mysql -u manager -p example < testdb_create.sql
147[root@localhost]# mysql -u manager -p example < testdb_data.sql
148[root@localhost]# mysql -u manager -p example < testdb_metadata.sql
149
1503.2.5) Run the test:
151[root@localhost]# cd $SOURCES/tests
152[root@localhost]# SLAPD_USE_SQL=mysql ./run sql-test000
153
1543.3) IBM db2
155[n.a.]
156
1573.3.1) Start the server:
158
1593.3.2) Create the database:
160
1613.3.3) Create the user:
162
1633.3.4) Populate the database:
164connect to the database as user manager, and execute the test files
165in auto-commit mode (-c)
166[root@localhost]# su - manager
167[manager@localhost]$ db2 "connect to example user manager using secret"
168[manager@localhost]$ db2 -ctvf backsql_create.sql
169[manager@localhost]$ db2 -ctvf testdb_create.sql
170[manager@localhost]$ db2 -ctvf testdb_data.sql
171[manager@localhost]$ db2 -ctvf testdb_metadata.sql
172[manager@localhost]$ db2 "connect reset"
173
1743.3.5) Run the test:
175[root@localhost]# cd $SOURCES/tests
176[root@localhost]# SLAPD_USE_SQL=ibmdb2 ./run sql-test000
177
1784) Cleanup:
179The test is basically readonly; this can be performed by all RDBMSes
180(listed above).
181
182There is another test, sql-test900-write, which is currently enabled
183only for PostgreSQL and IBM db2.  Note that after a successful run
184of the write test, the database is no longer in the correct state
185to restart either of the tests, and step 3.X.4 needs to be re-run first.
186
187More tests are to come; PostgreSQL is known to allow a full reload
188of the test database starting from an empty database.
189
190