Project

General

Profile

Delete Users » History » Version 1

ragna, 01/19/2019 09:44 PM
Since PR #294 isn't happening, I might as well follow the advice of digitalciruit. I hope I'll be able to contribute more to the wiki in the future.

1 1 ragna
h1. Delete Users (SQLite)
2 1 ragna
3 1 ragna
Script to remove quassel users from a SQLite database backend.
4 1 ragna
5 1 ragna
6 1 ragna
<pre><code class="text">
7 1 ragna
#!/bin/sh
8 1 ragna
#
9 1 ragna
# Delete Quasselcore users from your SQLite database
10 1 ragna
#
11 1 ragna
# File: deleteuser-sqlite.sh
12 1 ragna
# Author: Robbe Van der Gucht
13 1 ragna
# License: BSD-3-Clause, GPLv2, GPLv3
14 1 ragna
# License statements can be found at the bottom.
15 1 ragna
# Any of the indicated licenses can be chosen for redistribution 
16 1 ragna
# and only requires one of the license statements to be preserved.
17 1 ragna
18 1 ragna
exeq()
19 1 ragna
{
20 1 ragna
    # Execute SQL Query
21 1 ragna
    result=$(sqlite3 "${QUASSELDB}" "${1}")
22 1 ragna
    echo "${result}"
23 1 ragna
}
24 1 ragna
25 1 ragna
usage()
26 1 ragna
{
27 1 ragna
    echo "Usage: ${SCRIPT} username [database]"
28 1 ragna
}
29 1 ragna
30 1 ragna
print_users()
31 1 ragna
{
32 1 ragna
    sqlite3 "${QUASSELDB}" "SELECT quasseluser.userid, quasseluser.username FROM quasseluser ORDER BY quasseluser.userid;"
33 1 ragna
}
34 1 ragna
35 1 ragna
# Main body
36 1 ragna
37 1 ragna
SCRIPT="${0}"
38 1 ragna
QUASSELDB=""
39 1 ragna
USER=""
40 1 ragna
41 1 ragna
if [ -z "${2}" ] ; then
42 1 ragna
    # No file supplied.
43 1 ragna
    QUASSELDB="quassel-storage.sqlite"
44 1 ragna
else
45 1 ragna
    QUASSELDB="${2}"
46 1 ragna
fi
47 1 ragna
48 1 ragna
if [ -z "${1}" ] ; then
49 1 ragna
    echo "No user supplied."
50 1 ragna
    echo "Pick one: "
51 1 ragna
    print_users
52 1 ragna
    usage
53 1 ragna
    exit 1
54 1 ragna
else
55 1 ragna
    USER="${1}"
56 1 ragna
fi
57 1 ragna
58 1 ragna
if [ -e "${QUASSELDB}" ] ; then
59 1 ragna
    echo "SELECTED DB: ${QUASSELDB}"
60 1 ragna
else
61 1 ragna
    echo "SELECTED DB '${QUASSELDB}' does not exist."
62 1 ragna
    usage
63 1 ragna
    exit 2
64 1 ragna
fi
65 1 ragna
66 1 ragna
if [ -z $(exeq "SELECT quasseluser.username FROM quasseluser WHERE username = '${USER}';") ] ; then
67 1 ragna
    echo "SELECTED USER '${USER}' does not exist."
68 1 ragna
    print_users
69 1 ragna
    usage
70 1 ragna
    exit 3
71 1 ragna
else
72 1 ragna
    echo "SELECTED USER: ${USER}"
73 1 ragna
fi
74 1 ragna
75 1 ragna
# Sadly SQLITE does not allow DELETE statements that JOIN tables.
76 1 ragna
# All queries are written with a subquery.
77 1 ragna
# Contact me if you know a better way.
78 1 ragna
79 1 ragna
backlogq="DELETE
80 1 ragna
FROM backlog
81 1 ragna
WHERE backlog.bufferid in (
82 1 ragna
    SELECT bufferid
83 1 ragna
    FROM buffer, quasseluser
84 1 ragna
    WHERE buffer.userid = quasseluser.userid
85 1 ragna
    AND quasseluser.username = '${USER}'
86 1 ragna
);"
87 1 ragna
88 1 ragna
bufferq="DELETE
89 1 ragna
FROM buffer
90 1 ragna
WHERE buffer.userid in (
91 1 ragna
    SELECT userid
92 1 ragna
    FROM quasseluser
93 1 ragna
    WHERE quasseluser.username = '${USER}'
94 1 ragna
);"
95 1 ragna
96 1 ragna
ircserverq="DELETE
97 1 ragna
FROM ircserver
98 1 ragna
WHERE ircserver.userid in (
99 1 ragna
    SELECT userid
100 1 ragna
    FROM quasseluser
101 1 ragna
    WHERE quasseluser.username = '${USER}'
102 1 ragna
);"
103 1 ragna
104 1 ragna
identity_nickq="DELETE
105 1 ragna
FROM identity_nick
106 1 ragna
WHERE identity_nick.identityid in (
107 1 ragna
    SELECT identityid
108 1 ragna
    FROM quasseluser, identity
109 1 ragna
    WHERE quasseluser.userid = identity.userid
110 1 ragna
    AND quasseluser.username = '${USER}'
111 1 ragna
);"
112 1 ragna
113 1 ragna
identityq="DELETE
114 1 ragna
FROM identity
115 1 ragna
WHERE identity.userid in (
116 1 ragna
    SELECT userid
117 1 ragna
    FROM quasseluser
118 1 ragna
    WHERE quasseluser.username = '${USER}'
119 1 ragna
);"
120 1 ragna
121 1 ragna
networkq="DELETE
122 1 ragna
FROM network
123 1 ragna
WHERE network.userid in (
124 1 ragna
    SELECT userid
125 1 ragna
    FROM quasseluser
126 1 ragna
    WHERE quasseluser.username = '${USER}'
127 1 ragna
);"
128 1 ragna
129 1 ragna
usersettingq="DELETE
130 1 ragna
FROM user_setting
131 1 ragna
WHERE user_setting.userid in (
132 1 ragna
    SELECT userid
133 1 ragna
    FROM quasseluser
134 1 ragna
    WHERE quasseluser.username = '${USER}'
135 1 ragna
);"
136 1 ragna
137 1 ragna
quasseluserq="DELETE
138 1 ragna
FROM quasseluser
139 1 ragna
WHERE quasseluser.username = '${USER}'
140 1 ragna
;"
141 1 ragna
142 1 ragna
143 1 ragna
exeq "${backlogq}"
144 1 ragna
exeq "${bufferq}"
145 1 ragna
exeq "${ircserverq}"
146 1 ragna
exeq "${identity_nickq}"
147 1 ragna
exeq "${identityq}"
148 1 ragna
exeq "${networkq}"
149 1 ragna
exeq "${usersettingq}"
150 1 ragna
exeq "${quasseluserq}"
151 1 ragna
152 1 ragna
153 1 ragna
#-----------------------------------------------------------------------------#
154 1 ragna
# BSD-3-Clause 
155 1 ragna
# Copyright (c) 2018, Robbe Van der Gucht
156 1 ragna
# All rights reserved.
157 1 ragna
158 1 ragna
# Redistribution and use in source and binary forms, with or without
159 1 ragna
# modification, are permitted provided that the following conditions are met:
160 1 ragna
# 1. Redistributions of source code must retain the above copyright
161 1 ragna
#    notice, this list of conditions and the following disclaimer.
162 1 ragna
# 2. Redistributions in binary form must reproduce the above copyright
163 1 ragna
#    notice, this list of conditions and the following disclaimer in the
164 1 ragna
#    documentation and/or other materials provided with the distribution.
165 1 ragna
# 3. All advertising materials mentioning features or use of this software
166 1 ragna
#    must display the following acknowledgement:
167 1 ragna
#    This product includes software developed by the <organization>.
168 1 ragna
# 4. Neither the name of the <organization> nor the
169 1 ragna
#    names of its contributors may be used to endorse or promote products
170 1 ragna
#    derived from this software without specific prior written permission.
171 1 ragna
172 1 ragna
# THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ''AS IS'' AND ANY
173 1 ragna
# EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
174 1 ragna
# WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
175 1 ragna
# DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
176 1 ragna
# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
177 1 ragna
# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
178 1 ragna
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
179 1 ragna
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
180 1 ragna
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
181 1 ragna
# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
182 1 ragna
183 1 ragna
#-----------------------------------------------------------------------------#
184 1 ragna
# GPL Clauses
185 1 ragna
# Copyright (c) 2018, Robbe Van der Gucht
186 1 ragna
# All rights reserved.
187 1 ragna
188 1 ragna
# This program is free software: you can redistribute it and/or modify
189 1 ragna
# it under the terms of the GNU General Public License as published by
190 1 ragna
# the Free Software Foundation, either version 2 of the License, or
191 1 ragna
# (at your option) any later version.
192 1 ragna
193 1 ragna
# This program is distributed in the hope that it will be useful,
194 1 ragna
# but WITHOUT ANY WARRANTY; without even the implied warranty of
195 1 ragna
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
196 1 ragna
# GNU General Public License for more details.
197 1 ragna
198 1 ragna
# You should have received a copy of the GNU General Public License
199 1 ragna
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
200 1 ragna
</code></pre>