Quassel IRC

h1. Delete Users (SQLite)

Script to remove Quassel users from a SQLite database backend. It was discussed in [[https://github.com/quassel/quassel/pull/294|PR 294]]

<pre><code class="text">
#!/bin/sh
#
# Delete Quasselcore users from your SQLite database
#
# File: deleteuser-sqlite.sh
# Author: Robbe Van der Gucht
# License: BSD-3-Clause, GPLv2, GPLv3
# License statements can be found at the bottom.
# Any of the indicated licenses can be chosen for redistribution
# and only requires one of the license statements to be preserved.

exeq()
{
# Execute SQL Query
result=$(sqlite3 "${QUASSELDB}" "${1}")
echo "${result}"
}

usage()
{
echo "Usage: ${SCRIPT} username [database]"
}

print_users()
{
sqlite3 "${QUASSELDB}" "SELECT quasseluser.userid, quasseluser.username FROM quasseluser ORDER BY quasseluser.userid;"
}

# Main body

SCRIPT="${0}"
QUASSELDB=""
USER=""

if [ -z "${2}" ] ; then
# No file supplied.
QUASSELDB="quassel-storage.sqlite"
else
QUASSELDB="${2}"
fi

if [ -z "${1}" ] ; then
echo "No user supplied."
echo "Pick one: "
print_users
usage
exit 1
else
USER="${1}"
fi

if [ -e "${QUASSELDB}" ] ; then
echo "SELECTED DB: ${QUASSELDB}"
else
echo "SELECTED DB '${QUASSELDB}' does not exist."
usage
exit 2
fi

if [ -z $(exeq "SELECT quasseluser.username FROM quasseluser WHERE username = '${USER}';") ] ; then
echo "SELECTED USER '${USER}' does not exist."
print_users
usage
exit 3
else
echo "SELECTED USER: ${USER}"
fi

# Sadly SQLITE does not allow DELETE statements that JOIN tables.
# All queries are written with a subquery.
# Contact me if you know a better way.

backlogq="DELETE
FROM backlog
WHERE backlog.bufferid in (
SELECT bufferid
FROM buffer, quasseluser
WHERE buffer.userid = quasseluser.userid
AND quasseluser.username = '${USER}'
);"

bufferq="DELETE
FROM buffer
WHERE buffer.userid in (
SELECT userid
FROM quasseluser
WHERE quasseluser.username = '${USER}'
);"

ircserverq="DELETE
FROM ircserver
WHERE ircserver.userid in (
SELECT userid
FROM quasseluser
WHERE quasseluser.username = '${USER}'
);"

identity_nickq="DELETE
FROM identity_nick
WHERE identity_nick.identityid in (
SELECT identityid
FROM quasseluser, identity
WHERE quasseluser.userid = identity.userid
AND quasseluser.username = '${USER}'
);"

identityq="DELETE
FROM identity
WHERE identity.userid in (
SELECT userid
FROM quasseluser
WHERE quasseluser.username = '${USER}'
);"

networkq="DELETE
FROM network
WHERE network.userid in (
SELECT userid
FROM quasseluser
WHERE quasseluser.username = '${USER}'
);"

usersettingq="DELETE
FROM user_setting
WHERE user_setting.userid in (
SELECT userid
FROM quasseluser
WHERE quasseluser.username = '${USER}'
);"

quasseluserq="DELETE
FROM quasseluser
WHERE quasseluser.username = '${USER}'
;"

exeq "${backlogq}"
exeq "${bufferq}"
exeq "${ircserverq}"
exeq "${identity_nickq}"
exeq "${identityq}"
exeq "${networkq}"
exeq "${usersettingq}"
exeq "${quasseluserq}"

#-----------------------------------------------------------------------------#
# BSD-3-Clause
# Copyright (c) 2018, Robbe Van der Gucht
# All rights reserved.

# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
# 1. Redistributions of source code must retain the above copyright
# notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in the
# documentation and/or other materials provided with the distribution.
# 3. All advertising materials mentioning features or use of this software
# must display the following acknowledgement:
# This product includes software developed by the <organization>.
# 4. Neither the name of the <organization> nor the
# names of its contributors may be used to endorse or promote products
# derived from this software without specific prior written permission.

# THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ''AS IS'' AND ANY
# EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
# WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
# DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

#-----------------------------------------------------------------------------#
# GPL Clauses
# Copyright (c) 2018, Robbe Van der Gucht
# All rights reserved.

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
</code></pre>

It was decided in "PR 294":https://github.com/quassel/quassel/pull/294 to put this on the wiki instead of the main repo.