let’s do the timewaste again – or just another example on how some popular JavaScript libraries or frameworks freak me out

Let’s talk about code quality and about quality of documentation. One example. I’m using underscore. So there is ‘findWhere’ on collections. All you can get is “Just like where, but directly returns only the first model in the collection that matches the passed attributes.”

Ok, fine, you might say, but what if it doesn’t find an element? It’s not like this never happened before. Shouldn’t that ‘rare’ case be covered in the documentation? Given that ‘where’ returns an array and that might be empty, the case what’s the first element then could be quite interesting.

So let’s look at the implementation:

_.findWhere = function(obj, attrs) {
    return _.find(obj, _.matcher(attrs));
  };

Oooookay. Didn’t the docs say it’s like ‘where’? It starts to get confusing here. What does ‘where’ look like then?

_.where = function(obj, attrs) {
    return _.filter(obj, _.matcher(attrs));
  };

Doesn’t look like almost the same thing to me. Anyway, leave that behind, let’s proceed. What does ‘find’ look like, you might ask my dear friend?

 _.find = _.detect = function(obj, predicate, context) {
    var keyFinder = isArrayLike(obj) ? _.findIndex : _.findKey;
    var key = keyFinder(obj, predicate, context);
    if (key !== void 0 && key !== -1) return obj[key];
  };

WTF! Even my linter flashes the code all red at that place already: “anonymous function does not always return a value”. Yes, the function does not return anything in case no element is found. I know, ‘fuck types’ you might think, but no, fuck you and your laziness. This is just crap and the opposite of what’s good. Code quality, I haz it! #not

Dockered tinyproxy for dev environment

The other day I was looking for a solution to replace my Charles based proxy setup for our legacy system’s dev environment. Mostly because a colleague doesn’t have a license and didn’t want to pay for it. And we’re only using it for a single use case. The functionality they call “Map Remote”. Basically it redirects calls to remote services to some other other service making it look like it’s the original system. Ideal for a development system where you can’t just change hostnames (for reasons…).

So what I want to do, I want to type http://foo.example.com in my browser and not end up at the actual example.com live system but instead on services running locally on my computer.

We’ve switched to using docker, so running them locally is a breeze anyway already. So why not just add another docker component that acts as a proxy and does that job for me? I’d then just have to change settings in my browser to make it use that proxy and here I go.
After a short search on duckduckgo I’ve found tinyproxy and after inspecting it a bit deeper I found a way to make it work for me the way I wanted.
Here’s the Dockerfile I came up with, just change the hostnames that suit you and you’re all set.

FROM alpine
MAINTAINER Stefan Strigler 

RUN apk add --no-cache tinyproxy

RUN { \
    echo 'upstream web_app:80 "example.org"'; \
    echo 'upstream web_app:80 ".example.org"'; \
    echo 'upstream some_other:80 "foo.example.org"'; \
    } >> /etc/tinyproxy/tinyproxy.conf

RUN sed -i 's/^Allow/\#Allow/' /etc/tinyproxy/tinyproxy.conf

EXPOSE 8888

ENTRYPOINT /usr/sbin/tinyproxy -d

Since it’s based on alpine the whole image is only less than 5MB. Crazy!

I then added it to our docker-compose.yml like

version: '2'
services:

  web_app: ...

  some_other: ...

  tinyproxy:
    image: steve/tinyproxy
    ports:
      - 8888:8888
    links:
      - web_app
      - some_other


and off we go.

Compile MongooseIM on OS X with openssl

Another tricky thing I always struggle with: Get MongooseIM compiled on OS X (since they removed openssl). So if you have openssl installed via homebrew just do

diff --git a/apps/ejabberd/rebar.config b/apps/ejabberd/rebar.config
index c693471..4e5ff1f 100644
--- a/apps/ejabberd/rebar.config
+++ b/apps/ejabberd/rebar.config
@@ -8,8 +8,15 @@
 {require_otp_vsn, "R?1[678]"}.

 {port_specs,
- [{".*", "priv/lib/tls_drv.so", ["c_src/tls_drv.c"], [{env, [{"LDFLAGS", "$LDFLAGS -lssl"}]}]},
-  {".*", "priv/lib/ejabberd_zlib_drv.so", ["c_src/ejabberd_zlib_drv.c"], [{env, [{"LDFLAGS", "$LDFLAGS -lz"}]}]}]}.
+ [{".*", "priv/lib/tls_drv.so", ["c_src/tls_drv.c"], [{env, [{".*apple-darwin.*", "CFLAGS", "$CFLAGS -I/usr/local/opt/openssl/include"},
+                                                             {".*apple-darwin.*", "LDFLAGS", "$LDFLAGS -L/usr/local/opt/openssl/lib"},
+                                                             {"LDFLAGS", "$LDFLAGS -lssl"}]}]},
+  {".*", "priv/lib/ejabberd_zlib_drv.so", ["c_src/ejabberd_zlib_drv.c"], [{env, [{"LDFLAGS", "$LDFLAGS -lz"}]}]},
+  {".*", "priv/lib/sha_drv.so", ["c_src/sha_drv.c"], [{env, [{".*apple-darwin.*", "CFLAGS", "$CFLAGS -I/usr/local/opt/openssl/include"},
+                                                             {".*apple-darwin.*", "LDFLAGS", "$LDFLAGS -L/usr/local/opt/openssl/lib"},
+                                                             {"LDFLAGS", "$LDFLAGS -lcrypto"}]}]},
+  {".*", "priv/lib/xml.so", ["c_src/xml.c"], []},
+  {".*", "priv/lib/expat_erl.so", ["c_src/expat_erl.c"], [{env, [{"LDFLAGS", "$LDFLAGS -lexpat"}]}]}]}.

 {xref_checks, [undefined_function_calls,
                undefined_functions,

Erlang with openssl using kerl on El Capitan (or recent OS X) with homebrew

Since I struggle with this every time I have to setup a new machine, here’s the best solution IMHO: https://github.com/kerl/kerl/issues/103#issuecomment-176704372

In short


$ echo 'KERL_CONFIGURE_OPTIONS="--with-ssl=/usr/local/opt/openssl"' >> ~/.kerlrc

given you don’t have anything else in KERL_CONFIGURE_OPTIONS.

Otalk IM

A customer of mine asked for a WebRTC based, self hosted solution. So I started investigating and stumbled upon the great Otalk IM Client. Unfortunately it has not been maintained for a while. I decided to go with it nonetheless. I updated dependencies as required to make them work with latest stable NPM (3.x) and Node.js (6.x) as applicable and fixed bugs all along the way. Check out my fork if you are interested! Feedback would be very welcome. I think it makes for a very good starting point if you were about to create your own WebRTC solution. Or maybe even just a regular chat client.

CORS and basic authentication with cowboy and XmlHttpRequest

So this seems to be anything but straight forward so I document here for the sake of it.

Note that

  • you may not require an Authorization header for the preflight OPTIONS request,
  • you may not set Access-Control-Allow-Origin to the wildcard in that case but to the originating domain specifically,
  • of course Access-Control-Allow-Credentials must be set to true,
  • and last but not least you must advertise the content-type and authorization (for Firefox) header with Access-Control-Allow-Headers (if you want to have some content POST’ed to you),
  • all those headers better be sent with every response.

So in my REST resource I did:

rest_init(Req0, State) ->
    case cowboy_req:header(<<"origin">>, Req0) of
        {undefined, Req1} -> {ok, Req1, State};
        {Origin, Req1}    ->
            %% set CORS headers
            Req2 = cowboy_req:set_resp_header(<<"access-control-allow-origin">>,
                                              Origin, Req1),
            Req3 = cowboy_req:set_resp_header(<<"access-control-allow-methods">>,
                                              <<"POST, OPTIONS">>, Req2),
            Req4 = cowboy_req:set_resp_header(
                     <<"access-control-allow-credentials">>, <<"true">>, Req3),
            Req5 = cowboy_req:set_resp_header(
                     <<"access-control-allow-headers">>, <<"authorization,content-type">>, Req4),
            {ok, Req5, State}
    end.

allowed_methods(Req, State) -> {[<<"OPTIONS">>, <<"POST">>], Req, State}.

is_authorized(Req, State) ->
    case cowboy_req:method(Req) of
        {<<"OPTIONS">>, Req0} -> {true, Req0, State};
        {_Any,          Req0} ->
            case cowboy_req:parse_header(<<"authorization">>, Req0) of
                {ok, {<<"basic">>, {AppId, Token}}, Req1} ->
                    case is_token_valid(AppId, Token) of
                        true ->
                            {true, Req1, AppId};
                        false -> {{false, <<"Basic realm=\"myrealm\"">>}, Req1, State}
                    end;
                {ok, undefined, Req1} ->
                    {{false, <<"Basic realm=\"myrealm\"">>}, Req1, State}
            end
    end.

As for the AJAX request you need to set withCredentials to true (doesn’t work with Firefox, do it manually):

    var r = new XMLHttpRequest();
    r.open("POST", api_uri, true);
    r.setRequestHeader("Authorization", "Basic " + btoa(app_id + ":" + app_token));
    r.setRequestHeader("Content-type", "application/json");
    r.send(JSON.stringify(msg));

I’ve tested with Safari, Chrome and Firefox.

Update: So apparently to make that work with Firefox you have to add authorization explicitly to Access-Control-Allow-Headers. And as for the XmlHttpRequest you have to set the Authorization manually as shown above. Found at http://stackoverflow.com/questions/27001894/firefox-does-not-allow-cors-request-even-when-the-server-returns-the-appropriate

jwchat.org Updates

I have been working a lot on my XMPP service at jwchat.org recently. The idea was to bring you more security, privacy and modern features. In detail what has changed:

I hope you’ll enjoy the extended and improved service.

If you want to have your own XMPP (Jabber) server hosted, feel free to contact me!

Migrate ejabberd DB schema (mysql) from 2.1.x

Hiya! Long time no see. So I was upgrading my beloved ejabberd a while back and when I looked closer I found that most of the new functionality just wouldn’t work. Mostly because my ejabberd’s DB schema dating back to some 2.1.x install didn’t fit anymore. So but how to upgrade? After playing weird ideas back and forth I came across a script called ‘php-mysql-diff‘. According to its description it sounded very promising to what I wanted to achieve. Just that it wouldn’t work right of the box. After tearing it apart and putting it back together again I found that this was because my dump files as created by mysqldump sport a ‘COLLATE’ keyword which wasn’t supported by the built-in regexp used by its parser. After I fixed this I got the desired result, a migration script that I could use to upgrade my schemas. Hooray!


# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = '';

# Deleted Tables

# Changed Tables

-- changed table `last`

ALTER TABLE `last`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `seconds` `seconds` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `state` `state` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `seconds`;

-- changed table `privacy_default_list`

ALTER TABLE `privacy_default_list`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`;

-- changed table `privacy_list`

ALTER TABLE `privacy_list`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `id`;

-- changed table `privacy_list_data`

ALTER TABLE `privacy_list_data`
CHANGE COLUMN `t` `t` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `id`,
CHANGE COLUMN `value` `value` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `t`,
CHANGE COLUMN `action` `action` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `value`,
ADD KEY `i_privacy_list_data_id` (`id`);

-- changed table `private_storage`

ALTER TABLE `private_storage`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `namespace` `namespace` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `data` `data` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `namespace`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `data`;

-- changed table `rostergroups`

ALTER TABLE `rostergroups`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `jid` `jid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `grp` `grp` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `jid`;

-- changed table `rosterusers`

ALTER TABLE `rosterusers`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `jid` `jid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `nick` `nick` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `jid`,
CHANGE COLUMN `subscription` `subscription` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `nick`,
CHANGE COLUMN `ask` `ask` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `subscription`,
CHANGE COLUMN `askmessage` `askmessage` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `ask`,
CHANGE COLUMN `server` `server` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `askmessage`,
CHANGE COLUMN `subscribe` `subscribe` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `server`,
CHANGE COLUMN `type` `type` text COLLATE utf8mb4_unicode_ci AFTER `subscribe`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `type`;

-- changed table `spool`

ALTER TABLE `spool`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `xml` `xml` blob NOT NULL AFTER `username`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `seq`,
ADD KEY `i_spool_created_at` (`created_at`) USING BTREE;

-- changed table `users`

ALTER TABLE `users`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `password` `password` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
ADD COLUMN `serverkey` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' AFTER `password`,
ADD COLUMN `salt` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' AFTER `serverkey`,
ADD COLUMN `iterationcount` int(11) NOT NULL DEFAULT '0' AFTER `salt`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `iterationcount`;

-- changed table `vcard`

ALTER TABLE `vcard`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `vcard` `vcard` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `vcard`;

-- changed table `vcard_search`

ALTER TABLE `vcard_search`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `lusername` `lusername` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `fn` `fn` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lusername`,
CHANGE COLUMN `lfn` `lfn` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `fn`,
CHANGE COLUMN `family` `family` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lfn`,
CHANGE COLUMN `lfamily` `lfamily` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `family`,
CHANGE COLUMN `given` `given` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lfamily`,
CHANGE COLUMN `lgiven` `lgiven` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `given`,
CHANGE COLUMN `middle` `middle` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lgiven`,
CHANGE COLUMN `lmiddle` `lmiddle` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `middle`,
CHANGE COLUMN `nickname` `nickname` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lmiddle`,
CHANGE COLUMN `lnickname` `lnickname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `nickname`,
CHANGE COLUMN `bday` `bday` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lnickname`,
CHANGE COLUMN `lbday` `lbday` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `bday`,
CHANGE COLUMN `ctry` `ctry` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lbday`,
CHANGE COLUMN `lctry` `lctry` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `ctry`,
CHANGE COLUMN `locality` `locality` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lctry`,
CHANGE COLUMN `llocality` `llocality` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `locality`,
CHANGE COLUMN `email` `email` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `llocality`,
CHANGE COLUMN `lemail` `lemail` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `email`,
CHANGE COLUMN `orgname` `orgname` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lemail`,
CHANGE COLUMN `lorgname` `lorgname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `orgname`,
CHANGE COLUMN `orgunit` `orgunit` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lorgname`,
CHANGE COLUMN `lorgunit` `lorgunit` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `orgunit`;

# New Tables

-- new table `archive`

CREATE TABLE `archive` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`timestamp` bigint(20) unsigned NOT NULL,
`peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`bare_peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
`txt` text COLLATE utf8mb4_unicode_ci,
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kind` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`nick` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `i_bare_peer` (`bare_peer`) USING BTREE,
KEY `i_peer` (`peer`) USING BTREE,
FULLTEXT KEY `i_text` (`txt`),
KEY `i_timestamp` (`timestamp`) USING BTREE,
KEY `i_username` (`username`) USING BTREE,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `archive_prefs`

CREATE TABLE `archive_prefs` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`def` text COLLATE utf8mb4_unicode_ci NOT NULL,
`always` text COLLATE utf8mb4_unicode_ci NOT NULL,
`never` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `caps_features`

CREATE TABLE `caps_features` (
`node` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`subnode` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`feature` text COLLATE utf8mb4_unicode_ci,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `i_caps_features_node_subnode` (`node`(75),`subnode`(75))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `irc_custom`

CREATE TABLE `irc_custom` (
`jid` text COLLATE utf8mb4_unicode_ci NOT NULL,
`host` text COLLATE utf8mb4_unicode_ci NOT NULL,
`data` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `i_irc_custom_jid_host` (`jid`(75),`host`(75)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `motd`

CREATE TABLE `motd` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`xml` text COLLATE utf8mb4_unicode_ci,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `muc_registered`

CREATE TABLE `muc_registered` (
`jid` text COLLATE utf8mb4_unicode_ci NOT NULL,
`host` text COLLATE utf8mb4_unicode_ci NOT NULL,
`nick` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `i_muc_registered_jid_host` (`jid`(75),`host`(75)) USING BTREE,
KEY `i_muc_registered_nick` (`nick`(75)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `muc_room`

CREATE TABLE `muc_room` (
`name` text COLLATE utf8mb4_unicode_ci NOT NULL,
`host` text COLLATE utf8mb4_unicode_ci NOT NULL,
`opts` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `i_muc_room_name_host` (`name`(75),`host`(75)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_item`

CREATE TABLE `pubsub_item` (
`nodeid` bigint(20) DEFAULT NULL,
`itemid` text COLLATE utf8mb4_unicode_ci,
`publisher` text COLLATE utf8mb4_unicode_ci,
`creation` text COLLATE utf8mb4_unicode_ci,
`modification` text COLLATE utf8mb4_unicode_ci,
`payload` text COLLATE utf8mb4_unicode_ci,
KEY `i_pubsub_item_itemid` (`itemid`(36)),
UNIQUE KEY `i_pubsub_item_tuple` (`nodeid`,`itemid`(36)),
CONSTRAINT `pubsub_item_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_node`

CREATE TABLE `pubsub_node` (
`host` text COLLATE utf8mb4_unicode_ci,
`node` text COLLATE utf8mb4_unicode_ci,
`parent` text COLLATE utf8mb4_unicode_ci,
`type` text COLLATE utf8mb4_unicode_ci,
`nodeid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`nodeid`),
KEY `i_pubsub_node_parent` (`parent`(120)),
UNIQUE KEY `i_pubsub_node_tuple` (`host`(20),`node`(120))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_node_option`

CREATE TABLE `pubsub_node_option` (
`nodeid` bigint(20) DEFAULT NULL,
`name` text COLLATE utf8mb4_unicode_ci,
`val` text COLLATE utf8mb4_unicode_ci,
KEY `i_pubsub_node_option_nodeid` (`nodeid`),
CONSTRAINT `pubsub_node_option_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_node_owner`

CREATE TABLE `pubsub_node_owner` (
`nodeid` bigint(20) DEFAULT NULL,
`owner` text COLLATE utf8mb4_unicode_ci,
KEY `i_pubsub_node_owner_nodeid` (`nodeid`),
CONSTRAINT `pubsub_node_owner_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_state`

CREATE TABLE `pubsub_state` (
`nodeid` bigint(20) DEFAULT NULL,
`jid` text COLLATE utf8mb4_unicode_ci,
`affiliation` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`subscriptions` text COLLATE utf8mb4_unicode_ci,
`stateid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`stateid`),
KEY `i_pubsub_state_jid` (`jid`(60)),
UNIQUE KEY `i_pubsub_state_tuple` (`nodeid`,`jid`(60)),
CONSTRAINT `pubsub_state_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_subscription_opt`

CREATE TABLE `pubsub_subscription_opt` (
`subid` text COLLATE utf8mb4_unicode_ci,
`opt_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`opt_value` text COLLATE utf8mb4_unicode_ci,
UNIQUE KEY `i_pubsub_subscription_opt` (`subid`(32),`opt_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `roster_version`

CREATE TABLE `roster_version` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`version` text COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `sm`

CREATE TABLE `sm` (
`usec` bigint(20) NOT NULL,
`pid` text COLLATE utf8mb4_unicode_ci NOT NULL,
`node` text COLLATE utf8mb4_unicode_ci NOT NULL,
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`resource` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`priority` text COLLATE utf8mb4_unicode_ci NOT NULL,
`info` text COLLATE utf8mb4_unicode_ci NOT NULL,
KEY `i_node` (`node`(75)),
UNIQUE KEY `i_sid` (`usec`,`pid`(75)),
KEY `i_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `sr_group`

CREATE TABLE `sr_group` (
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`opts` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `sr_user`

CREATE TABLE `sr_user` (
`jid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`grp` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `i_sr_user_grp` (`grp`),
KEY `i_sr_user_jid` (`jid`),
UNIQUE KEY `i_sr_user_jid_group` (`jid`(75),`grp`(75))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `vcard_xupdate`

CREATE TABLE `vcard_xupdate` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`hash` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 1;

For convenience you can also download the file created: ejabberd_migrate2.sql.

PS: The fixed version of php-mysql-diff can be found at https://github.com/sstrigler/php-mysql-diff, a PR has been submitted.

Update: First version was working with an outdated schema for the users table. This is fixed at ejabberd_migrate2.sql now. Apparently my ejabberd-16.01 with shipped with an incorrect schema. :-/

Random Rant on Ubuntu/Linux

Dear Ubuntu or whom it might concern,

I understand or lets say, I’ve learned after a fresh install your default email client is thunderbird. Lets rather call it a strong suggestion instead of default cause default would mean you could seamlessly switch away from it. Which you can’t. Anyway. At the same time I discovered that thunderbird does not support any calenders or management of such and as such it does not support the calendar widget in your taskabar. Something I really like and want to see on my desktop. You know, I like it when my computer reminds me of things I could easily forget otherwise. Ok. I switched to evolution because of that. And also it’s kind of advertised as a possible replacement of thunderbird. And also I’ve been using it. Years ago. My calendar works now. Thanks for that. But evolution is not able to set Emails to read when they come with an attachment. Or sth evolutions thinks is an attachment. Lets not get too much into details here. How hard can it be to deploy a decent solution here? You know, we’re talking about fundamental things here. But you don’t even get the WindowManager right. Or at least stable. So why am I even complaining?

WebRTC based Jingle for JSJaC

Thanks to Valérian Saliou from jappix.org there’s now an implementation of Jingle for JSJaC. Jingle is a protocol that allows audio and video calls on top of XMPP. The implementation makes use of WebRTC as the underlying transport. And as such by definition it’s the hottest shit. The project is called JSJaCJingle.js and can be found over at github.