But my dog does.

Migrating Prosody from text store and sqlite3 to PostgreSQL

Assumptions

  • You started off from the basic storage config:
    • Regular data is in filesystem.
    • mam (xep0313) message archive is in SQLite3.
  • You want to transition to using just PostgreSQL.
  • PostgreSQL version is 9.4.

Installation

# # largest version of pgsql you have
~# apt install postgresql-9.4-client

# # get lua-dbi module for pgsql
~# apt install lua-dbi-postgresql

Creating PostgreSQL user

# # PostgreSQL trusts users connecting over unix domain socket to be the
# # same as their local account.
# # Therefore, become postgres -- the admin account.
user:~$ sudo su postgres

# # run user creation
postgres:~$ createuser --interactive
# # name: prosody
# # no other administrative options

# # run postgresql client
postgres:~$ psql
-- create database
CREATE DATABASE prosody;

-- give the prosody user all rights on it
GRANT ALL ON DATABASE prosody TO prosody;

-- in case of connecting over network and using md5 trust,
-- set prosody account password:
ALTER ROLE prosody WITH ENCRYPTED PASSWORD 'here_some_password';

Over network?

Assuming you want to connect over the network, edit /etc/postgresql/9.4/main/pg_hba.conf. Append:

# type, database, user, address, auth method
host prosody prosody 172.16.0.0/16 md5
  • Try to minimize your permitted netmask.
  • Can you configure a more secure auth method than md5? Do so.

Migrate data from filesystem

# # as prosody local user

prosody:$ cd prosody-hg/tools/migrator

# # overwrite the config.
prosody:$ cat > migrator.cfg.lua << _EOF
local data_path = "../../data";

input {
        type = "prosody_files";
        path = data_path;
}
output {
        type = "prosody_sql";
        driver = "PostgreSQL";
        database = "prosody";
        username = "prosody";
        password = "here_some_password";
        host = "database.host.here"; -- this assumes network connection; migration with local user credentials was not attempted.
}
_EOF

# # run the migrator in ~/prosody-hg/tools/migrator
prosody:$ lua prosody-migrator.lua input output

Migrate mam archive from SQLite3

# # as prosody user

prosody:~$ cd prosody-hg/data

# # Having first verified there is nothing in Prosody table...
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosody;'
# # ...drop prosody table. All its data (roster etc) was until now stored on the filesystem.
prosody:$ sqlite3 prosody.sqlite 'DROP TABLE prosody;'

# # dump and massage the sqlite3 output, piping it into psql.
# # psql authenticates as the 'prosody' user and does not require the password.
prosody:$ sqlite3 prosody.sqlite .dump | \
    grep -v 'BEGIN TRANSACTION;' | \
    sed 's/PRAGMA foreign_keys=OFF;/BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED;/' | \
    grep -v sqlite_sequence | \
    awk '/CREATE TABLE/{gsub("`","\"");} 1' | \
    awk '/CREATE UNIQUE INDEX/{gsub("`","\"");} 1' | \
    sed 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | \
    psql

# # manual step 🙁
# # fix the autoincrement.
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosodyarchive;'
# # use this number + 1 in:
prosody:$ echo 'ALTER SEQUENCE prosodyarchive_sort_id_seq RESTART WITH 123456;' | psql

Update Prosody config

In prosody.cfg.lua:

storage = {
        archive2 = "sql";
}
sql = { driver = "PostgreSQL", database = "prosody", username = "prosody", password = "here_some_password", host = "database.host.here" }

Getting sound and other panels in gnome-control-center outside GNOME

Missing some panels in gnome-control-center? No “Online Accounts”? No “Sound”?

Apparently, panels are defined through .desktop files in /usr/share/applications (!), and they mostly have the setting of OnlyShowIn=GNOME; or OnlyShowIn=GNOME;Unity;.

To trigger them appearing in, say, i3, xfce or similar, you need to pretend you’re actually running GNOME by exporting the XDG_CURRENT_DESKTOP variable with the value of GNOME.

#!/bin/sh
XDG_CURRENT_DESKTOP=GNOME gnome-control-center

Vidalia crashing on Ubuntu 14.04 due to incorrect AppArmor profile

This has been going unfixed for years now.

For me, the fix involved adding the following into /etc/apparmor.d/usr.bin.vidalia.

/usr/share/glib-2.0/schemas/ r,
/usr/share/glib-2.0/schemas/** r,

Then, I reloaded AppArmor /etc/init.d/apparmor reload and the contraption worked. If you get asked about your Tor control password, restart tor /etc/init.d/tor restart.

Where Buddypress stores the custom username?

I’ve installed new WordPress. I renamed an existing user ivucica to an administrative name (let’s say goblin), then I created a new user ivucica. This was done low level, by typing in manual MySQL queries, manipulating the wp_users table.

Then I installed Buddypress. Buddypress thought goblin was named @ivucica, while ivucica got named @ivucica-2.

Looks like there is a column in wp_users named user_nicename which is used by Buddypress and where this is pulled from. It doesn’t match the user_login column.

Tip for XMPP users: adjust your priority!

To declare which of my connections to my XMPP server has the ‘most important’ and ‘most chattable’ status, I use XMPP’s <priority /> mechanism.

Basics of XMPP connections

XMPP connection is actually two streams of XML, one going from the client to the server, and the other from the server to the client. (If your client exposes a feature typically called “XML Console”, use it to see the traffic.) Each XMPP connection has a “resource” string attached to it (and generally requested by the client — though the server can opt to override it). Typically, clients will set it to a random string, to the client name, or to the hostname. Together with your account name (“Jabber ID”), resource forms a globally-unique way to reach you in the XMPP network; for example: username@example.net/GajimAtWork.

Basics of XMPP presences

Each XMPP connection’s status text and status type are declared using a <presence /> tag. Just an empty <presence /> tag means “My status type is ‘online’, with no status text and with priority set to zero”. To specify each of these, according to RFC3921’s section 2.2.2, you add extra tags inside the <presence />1:

<presence>
  <show>away</show>
  <status>This is my status</status>
  <priority>20</priority>
</presence>

The <presence /> will be broadcast to users subscribed to them, whom you authorized to receive them.

How priority is used

The <priority /> of a <presence /> is used by clients for a variety of things, including prioritizing which status to display to users. And, even more importantly, servers use <priority /> to determine where to deliver <message />s that are directed towards a bare JID, and not towards a full JID. (A bare JID does not include the resource string, and thus describes an account; a full JID includes a resource string, and thus described a connection.)

Servers will deliver <message />s aimed at a bare JID to all connections that have the top priority. For example, if you have the following connections:

Resource Status Priority
GajimAtWork online 15
PidginAtWork online 15
OldPhone online 14
NewPhone online 14
GajimAtHome away 2
Tablet away 1
Webmail online 1

you will receive the message to GajimAtWork and PidginAtWork. (Specifics of this may be overridden by the server, especially if some XEP2 such as ‘carbon copies’ is in use.) See more information in RFC3921, section 11.1 which discusses how the server should handle incoming stanzas (incl. those directed at bare JID).

And let’s say you absolutely don’t want to be disturbed to a certain device, unless this device is directly contacted (by specifying full JID). In that case, specifying a negative priority (say, -1) is handy, which tells the server not to deliver the message at all, even if it’s the top priority. You can still initiate outgoing chats; receiving a message commonly makes the client switch to sending to a bare JID from which it was received.

How priority is useful

Many of the better clients let you associate a priority with a status type; that is, if you set your status type (<show />) to dnd3, you can declare that your <priority /> should also change to 20; and if you set your status type to online (that is, <show /> is missing), your <priority /> should change to 40.

Combined with the fact that, all things equal, I would prefer to answer to incoming chats on my desktop, I began using the following setup in clients that support the aforementioned functionality:

show Usual name in UI Device type Priority
chat Free for chat desktop 50
none Available desktop 30
dnd Busy desktop 25
away Away desktop 10
xa Extended Away desktop 2

For mobile, just decrease by one:

show Usual name in UI Device type Priority
chat Free for chat mobile 49
none Available mobile 29
dnd Busy mobile 24
away Away mobile 9
xa Extended Away mobile 1

What happens if your client does not support <priority/ >?

Useful clients which don’t support setting <priority /> — for example, Conversations for Android (source) — will have priority set to zero. Such a client, however, will be useful mainly because it supports and uses replacement XEPs such as XEP-0280: Message Carbons, which will ensure the message is still delivered to that device. There will be dark sorcery involving XEP-0333: Chat Markers which will help to reduce the number of devices that are making noises, similar to experience in Hangouts.

Some say statuses and priority are not useful

Some say statuses and priority are not useful. I respect this opinion, but my personal experience with Hangouts where exactly this is the norm tells me otherwise. I’d rather automate declaring my status than have it disappear from my contacts’ feeds. “Locked my workstation? It’s 12:00-14:00? Probably at lunch.” and similar personalized heuristics. “I’ve been toying with my phone for more than 5 min? I am probably free for chat — but do tell the contacts that I am on my phone.”

That said, I do like and appreciate much of the modern experiences certain statusless client(s) have. There is something to be said for simply receiving messages where they should arrive and notify based on actual activity. I like the simplicity of it.

Then again, if I am at work, I probably don’t want to chat with you; how will non-personalized client know that I’m busy out of the box? Or even more importantly, how will it relay that to my contacts? Can I more simply teach my phone to shut up based on my personal daily routine?

shrug I think I can. Your mileage may vary.


  1. <presence />s are used for more than this even in base RFCs. They can be directed towards one
    specific JID, instead of server just broadcasting them. This can in turn be used as a mechanism
    to declare that you would like to subscribe to a particular user’s presences. But, this is a
    discussion on <priority /> tag, so refer to the RFC for more information. 

  2. XEP: XMPP Extension Protocol. 
  3. dnd maps to ‘busy` in UI. 

Convert MySQL tables to use UTF8 character set and collation

I moved a Gerrit installation to use MySQL. The tables turned out to be using latin1 encoding.

Here’s a vile concoction, composed of many StackOverflow answers and swamp creatures:

-- update database and each table to use utf8 according to http://stackoverflow.com/a/6115705/39974.
-- to apply to each table, used http://stackoverflow.com/a/18294520/39974
-- use utf8_unicode_ci due to: http://stackoverflow.com/a/766996/39974

DELIMITER $$

DROP PROCEDURE IF EXISTS utfAllTables $$

CREATE PROCEDURE utfAllTables()

BEGIN
        DECLARE table_name VARCHAR(256);
        DECLARE end_of_tables INT DEFAULT 0;

        DECLARE cur CURSOR FOR
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;

        OPEN cur;

        tables_loop: LOOP
            FETCH cur INTO table_name;

            IF end_of_tables = 1 THEN
                LEAVE tables_loop;
            END IF;

            SET @s = CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
            PREPARE stmt FROM @s;
            EXECUTE stmt;

        END LOOP;

        CLOSE cur;
    END $$

DELIMITER ;

Let’s use this stored procedure, and then drop it.

ALTER DATABASE reviewdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE reviewdb;
CALL utfAllTables;
DROP PROCEDURE utfAllTables;

Sadly, it looks like ALTER DATABASE is not PREPAREable, nor does it accept a variable or a function
call as its database name. Therefore, I could not pass DATABASE() here, nor could I move the ALTER DATABASE
statement inside the stored procedure.