<commit_before>drop table if exists users CASCADE;

create unique index in_userinfo_username
    on userinfo (username);

create unique index in_userinfo_openid
    on userinfo (openid);

create unique index in_userinfo_ldapid
    on userinfo (ldapid);

create unique index in_userinfo_uuid
    on userinfo (uuid);

-- Clear all users without usernames, this can happen if there is no mach between the userinfo table and users table, these users will be useless anyway, aka. they would not work in the first place.

DELETE FROM usermembership WHERE userinfoid IN (SELECT userinfoid FROM userinfo where username is NULL);
DELETE FROM interpretation_comments WHERE interpretationid IN (SELECT interpretationid FROM interpretation WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM interpretation WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL);
DELETE FROM interpretation_comments WHERE interpretationcommentid IN (SELECT interpretationcommentid FROM interpretationcomment WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM interpretationcomment WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL);
DELETE FROM userdatavieworgunits WHERE userinfoid IN (SELECT userinfoid FROM userinfo where username is NULL);
DELETE FROM usergroupmembers WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL);

DELETE FROM messageconversation_usermessages WHERE usermessageid IN (SELECT usermessage.usermessageid FROM usermessage WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM usermessage WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL);

DELETE FROM visualization_rows WHERE visualizationid IN (SELECT visualization.visualizationid  FROM visualization WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM visualization_organisationunits WHERE visualizationid IN (SELECT visualization.visualizationid  FROM visualization WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM visualization_filters WHERE visualizationid IN (SELECT visualization.visualizationid  FROM visualization WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM visualization_datadimensionitems WHERE visualizationid IN (SELECT visualization.visualizationid  FROM visualization WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM visualization_columns WHERE visualizationid IN (SELECT visualization.visualizationid  FROM visualization WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL));
DELETE FROM visualization WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL);

DELETE FROM dashboarditem_users WHERE userid IN (SELECT userinfoid FROM userinfo where username is NULL);

delete from userinfo where username is NULL ;<commit_msg>fix: Update users migration (#9880)<commit_after>
-- Drop users table

drop table if exists users CASCADE;

-- Add unique indexes on userinfo

create unique index in_userinfo_username
    on userinfo (username);

create unique index in_userinfo_openid
    on userinfo (openid);

create unique index in_userinfo_ldapid
    on userinfo (ldapid);

create unique index in_userinfo_uuid
    on userinfo (uuid);

-- Set primitive user properties to false
-- Required to avoid startup failure for users with no match in userinfo table

update userinfo set twofa = false, externalauth = false, selfregistered = false, invitation = false, disabled = false
where username is null;
{'fraction_non_alphanumeric': 0.036303630363036306, 'fraction_numerical': 0.0011001100110011, 'mean_word_length': 5.967432950191571, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3235019', 'n_tokens_mistral': 865, 'n_tokens_neox': 830, 'n_words': 439}