| 1 |
pippijn |
1.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. |
