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>