mod_storage_sql developer notes
Database layout
Prosody uses a single table called prosody for database storage. It looks as follows:
| host | user | store | key | type | value |This table is indexed by the (host, user, store, key) tuple.
All columns's datatype is TEXT. However further information about how to parse the value-field is available via the type-field. Possible values for the type-field are as follows:
| Value | Description | Example |
|---|---|---|
string |
A regular string | romeo@capulet.lit |
boolean |
A boolean value. In this case the "value"-field is either "true" or "false" | true |
number |
A decimal number, which can (but need not) contain a decimal point | 3.1415 |
json |
A JSON object. This is a Lua table serialized to JSON. The keys depends on the value of the "key"-field | {"ask":"subscribe","subscription":"none","groups":{}} |
Mappings
These are mappings for certain types of datastores. However especially values for the key-field may be subject to loaded modules and configuration. E.g. the account datastore might not contain a password, but hash data if authentication = "internal_hashed" is used.
Accounts
For account data the store-field's value is accounts. Usually the only value for the key-field is going to be password.
Roster
For roster data the store-field's value is roster. The key-field is either empty, pending or a contacts JID, with the following meanings:
| Value | Description | Example |
|---|---|---|
| <empty> | The roster[false] table | {"__hash":[false,{"version":2}]} |
pending |
Pending subscription requests | {"alice@localhost":true} |
| <JID> | A roster entry | {"ask":"subscribe","subscription":"none","groups":{"Friends":true}} |
Private XML Storage
For the private XML storage the store-field's value is private. The key-field contains the name of the stored XML element followed by a colon and the xmlns of the stored XML Element. The value-field contains a JSON representation of the Lua representation of the XML element.