Pluggable datamanager, with support for SQL backends

[GoogleCodeExporter]

Support for various SQL backends, with configurable schemas. Flat file mode
not going anywhere.

Original issue reported on code.google.com by MWild1 on 24 Feb 2009 at 3:52

[GoogleCodeExporter]

I am very keen to see progress on this. I'm am very willing to contribute schema
design requirements or anything else that can help on this.

Original comment by simon%im...@gtempaccount.com on 26 Jun 2009 at 4:26

[GoogleCodeExporter]

This is a primary goal for 0.6. Waqas - I'd like to discuss my ideas with you 
for a
second pair of eyes.

Simon - if you have custom schemas for anything, feel free to email me so we can
ensure they are easy to support. Thanks for your offer, I'll let you know if 
there's
anything else you can help with.

Original comment by MWild1 on 28 Jun 2009 at 3:49

• Added labels: Milestone-0.6
• Removed labels: Milestone-0.5

[GoogleCodeExporter]

Heh, start a Wave ;) I can probably cook up a greasemonkey/jetpack script to 
extract 
publishable HTML out of it.

Original comment by waqas20 on 28 Jun 2009 at 6:51

[GoogleCodeExporter]

I am a big fan of the Tigase schema for usernames:  

create table tig_users (
    uid BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    user_id varchar(2049) NOT NULL,
    user_pw varchar(255) default NULL,
    last_login timestamp,
    last_logout timestamp,
    online_status int default 0,
    failed_logins int default 0,
    account_status int default 1
);

Additionally, email addresses are a big timesaver in terms of password recovery 
- I
seem to spend my days helping people recover passwords.

Original comment by simon%im...@gtempaccount.com on 28 Jun 2009 at 7:37

[GoogleCodeExporter]

How about using a key-value storage..
Memcached is memory only, but there is a nice one, which has persistence as 
well:
http://code.google.com/p/redis/

Original comment by macaron...@gmail.com on 28 Jul 2009 at 3:32

[GoogleCodeExporter]

Original comment by MWild1 on 28 Oct 2009 at 3:13

• Added labels: Milestone-0.7
• Removed labels: Milestone-0.6

[GoogleCodeExporter]

waqas hasn't pushed his work to trunk yet, but shall do after branching 0.7. 
Setting 
milestone to 0.8.

Original comment by MWild1 on 10 Jan 2010 at 3:42

• Added labels: Milestone-0.8
• Removed labels: Milestone-0.7

[GoogleCodeExporter]

what about extending mod_auth_external to allow more information to be passed 
down the external process. Most of the work can then be external 
authentification processes, all using the same interface.

Nothing prevents the external process to not use the standard prosody 
configuration file.

If will allow non prosody hackers to add the features, or tweak prosody to fit 
with their current authentification schemes.

For the presented database format the only thing missing would be the logout 
information.

Original comment by thomas.mangin on 12 Nov 2010 at 8:51

[GoogleCodeExporter]

Any updates on this?

Original comment by simon%im...@gtempaccount.com on 15 Nov 2010 at 3:44

[GoogleCodeExporter]

Ok, so during some more endless schema debating, we have some conclusions:

1) No schema we choose is going to suit everyone.
2) Any fixed schema is going to be hostile to custom extensions, something that 
Prosody has an awful lot of. Users of such a schema would have to accept that 
some plugins that say, add extra data into the roster, will simply not work 
(without extra code).

A possible solution is a "loose" schema. This would allow the storage of 
freeform data, as Prosody has internally. A simple example would be a simple 
key->value schema, where we store in text form (Lua/JSON) what we currently 
store in files on-disk. This isn't particularly pretty, or friendly to other 
applications extracting the data.

So as an in-between, how is the following schema? All columns are strings.

| host | user | store | key | type | value |

As an example, a user's account would become:

| example.com | user1 | account | password | string | thisissecret |

Using this kind of schema we can support any extensions, it is trivial to 
perform queries and extract data outside of Prosody, and the schema would be 
entirely future-proof (no schema changes between releases).

More traditional schemas for authentication and storage of user accounts only 
would be supported via auth plugins, which are another new feature of 0.8. Such 
an auth plugin would also be able to accept custom queries to run against an 
SQL server, and the above "loose" schema would not be used for 
usernames/passwords in this case.

Thoughts anyone?

Original comment by MWild1 on 2 Dec 2010 at 3:46

[GoogleCodeExporter]

One thing to bear in mind is that with the proposed highly-normalised table: 
you loose the ability to create sensible indexes. 

You also loose the ability to easily apply constrainsts (you probably could but 
the SQL would be very very messy).

To me this is this is a non-problem: If xmpp operators need to create highly 
abstracted stores they problably also have the resources to write a new plugin 
to wangle data to their needs.

I would guess that 99% of Prosody installers already has a table of users. 
Either from a web-app or from another xmpp server.  Make it easy for them to 
plug into that existing table (be it from an existing web-app like Drupal or an 
existing xmpp server).

If you do want to abstract provide a way to map username to the correct column 
in an existing table (perhaps your existing app uses "User_name").

Perhaps save the highly normalised table solution  for when there is a real 
that justifies the additional complexity or until there is a way to manage 
indexes and constrainsts in a nice way or a real user need.

S.

PS: perhaps also add an "is_endabled" boolean in there too.

Original comment by simon%im...@gtempaccount.com on 2 Dec 2010 at 9:12

[GoogleCodeExporter]

datastore plugins are now supported, and mod_storage_sql is available in 
Prosody 0.8.0rc1. Marking as fixed.

Original comment by waqas20 on 19 Jan 2011 at 5:47

• Changed state: Fixed

[GoogleCodeExporter]

oh happy days!

Original comment by simon%im...@gtempaccount.com on 19 Jan 2011 at 6:12

[GoogleCodeExporter]

Thanks for your help and pushing Simon & all ;)

Original comment by MWild1 on 19 Jan 2011 at 7:09