I created a small ruby-script, that adjusts the values and column-defaults for our case. Maybe it is useful for anyone else with this problem:
SOURCE_DB_NAME = 'db_work.sqlite'
TARGET_DB_NAME = 'db_new.sqlite'
TEMP_FILE_NAME = 'SQL_temp_file.sql'
puts "changing the default values via sql-dump"
dump_string = %x{sqlite3 #{SOURCE_DB_NAME} ".dump"}
new_sql_string = dump_string
.sub(/"admin" boolean DEFAULT 'f'/, '"admin" boolean DEFAULT 0')
.sub(/"delivery_enabled" boolean DEFAULT 't'/, '"delivery_enabled" boolean DEFAULT 1')
puts "puting adjusted sql into temp-file: #{TEMP_FILE_NAME}"
File.write(TEMP_FILE_NAME, new_sql_string)
puts "creat new empty db"
%x{ sqlite3 #{TARGET_DB_NAME} "VACUUM;" }
puts "restoring the data back into the new db"
%x{ sqlite3 #{TARGET_DB_NAME} < #{TEMP_FILE_NAME} }
all_columns = {
subscriptions: %w[
admin
delivery_enabled
],
lists: %w[
send_encrypted_only
receive_encrypted_only
receive_signed_only
receive_authenticated_only
receive_from_subscribed_emailaddresses_only
receive_admin_only
keep_msgid
bounces_drop_all
bounces_notify_admins
include_list_headers
include_openpgp_header
forward_all_incoming_to_admins
deliver_selfsent
include_autocrypt_header
set_reply_to_to_sender
munge_from
]
}
puts "### updating values in all boolean columns"
all_columns.each do |table, columns|
columns.each do |column|
sql_01 = "UPDATE #{table} SET #{column} = 1 WHERE #{column} = 't';"
sql_02 = "UPDATE #{table} SET #{column} = 0 WHERE #{column} = 'f';"
cmd_01 = "sqlite3 #{TARGET_DB_NAME} \"#{sql_01}\""
cmd_02 = "sqlite3 #{TARGET_DB_NAME} \"#{sql_02}\""
puts "cmd: #{cmd_01}"
puts "cmd: #{cmd_02}"
%x{ #{cmd_01} }
%x{ #{cmd_02} }
end
end
Thanks for the suggestions. But I am still stuck on how to access schleuder console on a debian install.
I figured out, that I probably have to move to /usr/lib/ruby/vendor_ruby/schleuder
fist.
But rake -T
only gives me all the db:tasks
. Looking at /usr/lib/ruby/vendor_ruby/schleuder/Rakefile
I kinda understand, why they are available there. But there is not rake colsole
available. Should it?
Hi I'm working on the problem, with the legacy boolean values in the sqlite3-tables. 't' & 'f' instead of 1 & 0 .
Updating the values is as easy as:
UPDATE subscriptions SET admin = 1 WHERE admin = 't';
UPDATE subscriptions SET admin = 0 WHERE admin = 'f';
UPDATE subscriptions SET delivery_enabled = 1 WHERE delivery_enabled = 't';
UPDATE subscriptions SET delivery_enabled = 0 WHERE delivery_enabled = 'f';
...
I'm using raw SQL because I'm not sure how to use ActiveRecord-tooling with a schleuder-installation from deb-packages. E.g. where would I put rake-tasks or migrations?? Or what classes do I require from where...
This is why the instructions linked here:
ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 1)
are not really working for me.
But I think scripting the adjustments in SQL is a feasible way to get this over with.
Where I'm stuck now is the wrong default values for the boolean columns. First observations: in our db the subscriptions-table has default values 'f' & 't'. But the lists table with the many boolean switches for the lists already has 0 & 1 as default values. The actual values in the columns are still t&f and need to be corrected.
Since sqlite3 does NOT support changing columns default values at all, the advice is to
To avoid all this hassle I tried .dump-ing the db into a text file, end the edit
CREATE TABLE IF NOT EXISTS "subscriptions"
("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"list_id" integer,
"email" varchar(255),
"fingerprint" varchar(255),
"admin" boolean DEFAULT 'f',
"delivery_enabled" boolean DEFAULT 't',
"created_at" datetime,
"updated_at" datetime);
To:
...
"admin" boolean DEFAULT 0,
"delivery_enabled" boolean DEFAULT 1,
...
After reading the text-dump back in the subscription table now looks like this:
sqlite3 db2.sqlite "pragma table_info('subscriptions');"
0|id|INTEGER|1||1
1|list_id|integer|0||0
2|email|varchar(255)|0||0
3|fingerprint|varchar(255)|0||0
4|admin|boolean|0|0|0
5|delivery_enabled|boolean|0|1|0
6|created_at|datetime|0||0
7|updated_at|datetime|0||0
Do you think this is a feasible way to change the default values for the two boolean columns?
I think a found the underlying problem:
In the sqlite3
documentation it says:
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
If, on our installation, I do sqlite3 db.sqlite "pragma table_info('subscriptions');"
I get:
0|id|INTEGER|1||1
1|list_id|integer|0||0
2|email|varchar(255)|0||0
3|fingerprint|varchar(255)|0||0
4|admin|boolean|0|'f'|0
5|delivery_enabled|boolean|0|'t'|0
6|created_at|datetime|0||0
7|updated_at|datetime|0||0
So our boolean columns default to t&f instead of 1&0. This is probably a legacy way of storing boolean in sqlite.
and
sqlite3 db.sqlite "SELECT DISTINCT admin from subscriptions;"
t
f
After we applied the fix on one list
schleuder-cli subscriptions set foo@bar.org admin false
schleuder-cli subscriptions set foo@bar.org admin true
I get:
sqlite3 db.sqlite "SELECT DISTINCT admin from subscriptions;"
1 <---- !!!
f
t
So it looks like after the upgrade we use a newer sqlite-adapter, that is not as forgiving with BOOLEAN values stored as 't'
and 'f'
in a column in sqlite3.
This has been an issue with ActiveRecord since 2014: https://github.com/rails/rails/issues/17062
ActiveRecord uses 't' and 'f' constants when talking to SQLite columns for true and false, but it would probably be better if it used 1 and 0.