| 1 |
Read this first |
| 2 |
--------------- |
| 3 |
|
| 4 |
As of version 2.2, Services no longer supports SQL databases. The |
| 5 |
documentation concern SQL is available below. To use Services 2.2, you |
| 6 |
will need to switch to the flatfile backend. |
| 7 |
|
| 8 |
|
| 9 |
SQL Notes |
| 10 |
--------- |
| 11 |
|
| 12 |
Atheme supports SQL databases. SQL is not "live" for a number of |
| 13 |
reasons. Most significantly, better, more portable, and safer |
| 14 |
functionality is provided by Atheme's XML-RPC interface. |
| 15 |
|
| 16 |
To repeat, the SQL backends do not pick up any changes to the |
| 17 |
database at run time and only write changes to the database every |
| 18 |
5 minutes (or configurable time), and therefore they cannot (or only |
| 19 |
to a very limited extent) be used to create web interfaces to services. |
| 20 |
|
| 21 |
Additionally the SQL backends are less well tested than flatfile, and |
| 22 |
may lack some recent features (nickname access lists (/ns access), |
| 23 |
services ignores). |
| 24 |
|
| 25 |
Schema files are available in the SQL/ directory. |
| 26 |
|
| 27 |
Note that during 0.3 development, several columns changed from |
| 28 |
VARCHAR to TEXT. |
| 29 |
|
| 30 |
|
| 31 |
PostgreSQL |
| 32 |
---------- |
| 33 |
|
| 34 |
To initialize the database, use: |
| 35 |
|
| 36 |
psql <database> < atheme_init.pgsql |
| 37 |
|
| 38 |
Upgrade information: |
| 39 |
|
| 40 |
Note that the several aspects of the schema have changed. Some |
| 41 |
information was moved to metadata. The only major issue with this is |
| 42 |
with the ENTRYMSG and URL columns in the CHANNELS table. Run |
| 43 |
0.3beta2 against your database and the values will be automatically |
| 44 |
converted to metadata. Then, it is safe to drop those columns. |
| 45 |
|
| 46 |
Note also that the FAILNUM, LASTFAIL, and LASTFAILON columns in the |
| 47 |
ACCOUNTS table have been removed. |
| 48 |
|
| 49 |
The PostgreSQL FAQ has this to say on changing a column's data type: |
| 50 |
|
| 51 |
Changing the data type of a column can be done easily in 8.0 |
| 52 |
and later with ALTER TABLE ALTER COLUMN TYPE. |
| 53 |
|
| 54 |
In earlier releases, do this: |
| 55 |
|
| 56 |
BEGIN; |
| 57 |
ALTER TABLE tab ADD COLUMN new_col new_data_type; |
| 58 |
UPDATE tab SET new_col = CAST(old_col AS new_data_type); |
| 59 |
ALTER TABLE tab DROP COLUMN old_col; |
| 60 |
COMMIT; |
| 61 |
|
| 62 |
You might then want to do VACUUM FULL tab to reclaim |
| 63 |
the disk space used by the expired rows. |
| 64 |
|
| 65 |
(http://www.postgresql.org/docs/faqs.FAQ.html#4.3) |
| 66 |
|
| 67 |
|
| 68 |
Switching to a SQL backend |
| 69 |
-------------------------- |
| 70 |
|
| 71 |
Switching to a SQL backend is relatively painless; just make sure |
| 72 |
that your database {} block is configured properly. |
| 73 |
Hint: /msg operserv rehash |
| 74 |
|
| 75 |
Once you have a properly configured database{} block, you can simply |
| 76 |
load the replacement backend via: |
| 77 |
/msg operserv modload ../backend/<replacement backend> |
| 78 |
|
| 79 |
After the backend is loaded, you can export your data by using |
| 80 |
OperServ's UPDATE command. |
| 81 |
|
| 82 |
Once you have loaded the replacement backend, you are now on |
| 83 |
SQL. You will probably want to switch your backend module out in |
| 84 |
the configuration file, as your flatfile database will no longer |
| 85 |
be updated. You may want to restart Services to ensure that the |
| 86 |
transition was successful after updating the config file. |
| 87 |
|
| 88 |
Switching from a SQL backend to flatfile can be done similarly. |
