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.