Container crashes on start with sqlite3.OperationalError: no such column: oauthProvider.oauth_userinfo_url #1

New Issue

2022-07-12T11:54:49-07:00

Jafner commented

2022-07-12 11:54:49 -07:00

(Migrated from gitlab.jafner.net)

Container was created with this docker-compose.yml and run with the following:

docker stop calibre-web_dev && docker rm calibre-web_dev && docker volume rm calibre-web_calibre-web-dev_data
docker-compose up -d --force-recreate calibre-web-dev && docker logs -f calibre-web_dev

Full container logs:

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service 00-legacy: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service 00-legacy successfully started
cont-init: info: running /etc/cont-init.d/01-envfile
cont-init: info: /etc/cont-init.d/01-envfile exited 0
cont-init: info: running /etc/cont-init.d/01-migrations
[migrations] started
[migrations] no migrations found
cont-init: info: /etc/cont-init.d/01-migrations exited 0
cont-init: info: running /etc/cont-init.d/02-tamper-check
cont-init: info: /etc/cont-init.d/02-tamper-check exited 0
cont-init: info: running /etc/cont-init.d/10-adduser

-------------------------------------
          _         ()
         | |  ___   _    __
         | | / __| | |  /  \
         | | \__ \ | | | () |
         |_| |___/ |_|  \__/


Brought to you by linuxserver.io
-------------------------------------

To support LSIO projects visit:
https://www.linuxserver.io/donate/
-------------------------------------
GID/UID
-------------------------------------

User uid:    1000
User gid:    1000
-------------------------------------

cont-init: info: /etc/cont-init.d/10-adduser exited 0
cont-init: info: running /etc/cont-init.d/30-config
cont-init: info: /etc/cont-init.d/30-config exited 0
cont-init: info: running /etc/cont-init.d/90-custom-folders
cont-init: info: /etc/cont-init.d/90-custom-folders exited 0
cont-init: info: running /etc/cont-init.d/99-custom-scripts
[custom-init] no custom files found exiting...
cont-init: info: /etc/cont-init.d/99-custom-scripts exited 0
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service legacy-services: starting
services-up: info: copying legacy longrun calibre-web (no readiness notification)
s6-rc: info: service legacy-services successfully started
s6-rc: info: service 99-ci-service-check: starting
[ls.io-init] done.
s6-rc: info: service 99-ci-service-check successfully started
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: oauthProvider.oauth_userinfo_url

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/calibre-web/cps.py", line 31, in <module>
    main()
  File "/app/calibre-web/cps/main.py", line 28, in main
    from .web import web
  File "/app/calibre-web/cps/web.py", line 65, in <module>
    from .oauth_bb import oauth_check, register_user_with_oauth, logout_oauth_user, get_oauth_status
  File "/app/calibre-web/cps/oauth_bb.py", line 294, in <module>
    oauthblueprints = generate_oauth_blueprints()
  File "/app/calibre-web/cps/oauth_bb.py", line 214, in generate_oauth_blueprints
    if not ub.session.query(ub.OAuthProvider).count():
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 3163, in count
    return self._from_self(col).enable_eagerloads(False).scalar()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 2888, in scalar
    ret = self.one()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 2865, in one
    return self._iter().one()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 2903, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: oauthProvider.oauth_userinfo_url
[SQL: SELECT count(*) AS count_1 
FROM (SELECT "oauthProvider".id AS "oauthProvider_id", "oauthProvider".provider_name AS "oauthProvider_provider_name", "oauthProvider".oauth_client_id AS "oauthProvider_oauth_client_id", "oauthProvider".oauth_client_secret AS "oauthProvider_oauth_client_secret", "oauthProvider".oauth_userinfo_url AS "oauthProvider_oauth_userinfo_url", "oauthProvider".oauth_base_url AS "oauthProvider_oauth_base_url", "oauthProvider".oauth_auth_url AS "oauthProvider_oauth_auth_url", "oauthProvider".oauth_token_url AS "oauthProvider_oauth_token_url", "oauthProvider".scope AS "oauthProvider_scope", "oauthProvider".username_mapper AS "oauthProvider_username_mapper", "oauthProvider".email_mapper AS "oauthProvider_email_mapper", "oauthProvider".login_button AS "oauthProvider_login_button", "oauthProvider".active AS "oauthProvider_active" 
FROM "oauthProvider") AS anon_1]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Container was created with this [docker-compose.yml](https://gitlab.jafner.net/Jafner/homelab/-/blob/main/server/config/calibre-web/docker-compose.yml) and run with the following: 1. `docker stop calibre-web_dev && docker rm calibre-web_dev && docker volume rm calibre-web_calibre-web-dev_data` 2. `docker-compose up -d --force-recreate calibre-web-dev && docker logs -f calibre-web_dev` Full container logs: ``` s6-rc: info: service s6rc-oneshot-runner: starting s6-rc: info: service s6rc-oneshot-runner successfully started s6-rc: info: service fix-attrs: starting s6-rc: info: service 00-legacy: starting s6-rc: info: service fix-attrs successfully started s6-rc: info: service legacy-cont-init: starting s6-rc: info: service 00-legacy successfully started cont-init: info: running /etc/cont-init.d/01-envfile cont-init: info: /etc/cont-init.d/01-envfile exited 0 cont-init: info: running /etc/cont-init.d/01-migrations [migrations] started [migrations] no migrations found cont-init: info: /etc/cont-init.d/01-migrations exited 0 cont-init: info: running /etc/cont-init.d/02-tamper-check cont-init: info: /etc/cont-init.d/02-tamper-check exited 0 cont-init: info: running /etc/cont-init.d/10-adduser ------------------------------------- _ () | | ___ _ __ | | / __| | | / \ | | \__ \ | | | () | |_| |___/ |_| \__/ Brought to you by linuxserver.io ------------------------------------- To support LSIO projects visit: https://www.linuxserver.io/donate/ ------------------------------------- GID/UID ------------------------------------- User uid: 1000 User gid: 1000 ------------------------------------- cont-init: info: /etc/cont-init.d/10-adduser exited 0 cont-init: info: running /etc/cont-init.d/30-config cont-init: info: /etc/cont-init.d/30-config exited 0 cont-init: info: running /etc/cont-init.d/90-custom-folders cont-init: info: /etc/cont-init.d/90-custom-folders exited 0 cont-init: info: running /etc/cont-init.d/99-custom-scripts [custom-init] no custom files found exiting... cont-init: info: /etc/cont-init.d/99-custom-scripts exited 0 s6-rc: info: service legacy-cont-init successfully started s6-rc: info: service legacy-services: starting services-up: info: copying legacy longrun calibre-web (no readiness notification) s6-rc: info: service legacy-services successfully started s6-rc: info: service 99-ci-service-check: starting [ls.io-init] done. s6-rc: info: service 99-ci-service-check successfully started Traceback (most recent call last): File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context self.dialect.do_execute( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: no such column: oauthProvider.oauth_userinfo_url The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/app/calibre-web/cps.py", line 31, in <module> main() File "/app/calibre-web/cps/main.py", line 28, in main from .web import web File "/app/calibre-web/cps/web.py", line 65, in <module> from .oauth_bb import oauth_check, register_user_with_oauth, logout_oauth_user, get_oauth_status File "/app/calibre-web/cps/oauth_bb.py", line 294, in <module> oauthblueprints = generate_oauth_blueprints() File "/app/calibre-web/cps/oauth_bb.py", line 214, in generate_oauth_blueprints if not ub.session.query(ub.OAuthProvider).count(): File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 3163, in count return self._from_self(col).enable_eagerloads(False).scalar() File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 2888, in scalar ret = self.one() File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 2865, in one return self._iter().one() File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 2903, in _iter result = self.session.execute( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 1712, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection return connection._execute_clauseelement( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement ret = self._execute_context( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context self._handle_dbapi_exception( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception util.raise_( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 208, in raise_ raise exception File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context self.dialect.do_execute( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: oauthProvider.oauth_userinfo_url [SQL: SELECT count(*) AS count_1 FROM (SELECT "oauthProvider".id AS "oauthProvider_id", "oauthProvider".provider_name AS "oauthProvider_provider_name", "oauthProvider".oauth_client_id AS "oauthProvider_oauth_client_id", "oauthProvider".oauth_client_secret AS "oauthProvider_oauth_client_secret", "oauthProvider".oauth_userinfo_url AS "oauthProvider_oauth_userinfo_url", "oauthProvider".oauth_base_url AS "oauthProvider_oauth_base_url", "oauthProvider".oauth_auth_url AS "oauthProvider_oauth_auth_url", "oauthProvider".oauth_token_url AS "oauthProvider_oauth_token_url", "oauthProvider".scope AS "oauthProvider_scope", "oauthProvider".username_mapper AS "oauthProvider_username_mapper", "oauthProvider".email_mapper AS "oauthProvider_email_mapper", "oauthProvider".login_button AS "oauthProvider_login_button", "oauthProvider".active AS "oauthProvider_active" FROM "oauthProvider") AS anon_1] (Background on this error at: https://sqlalche.me/e/14/e3q8) ```

Jafner commented

2022-07-12 11:56:27 -07:00

(Migrated from gitlab.jafner.net)

changed the description

Ace_Archer commented

2022-07-12 13:40:13 -07:00

(Migrated from gitlab.jafner.net)

This is likely related to the definition of OAuthProviders in cps/up.py, where the table itself is defined. I added an entry for oauth_userinfo_url, but for some reason it doesn't seem to properly pick up on it (potentially loosing other fields?). I was able to get it to not throw this error by live editing the file and recreating the db in the container, but making changes here seemingly didn't work.
There's no error messages beign logged atm, but I almost suspect it might be an encoding issue? When edited within this repo, the Gitlab WebIDE was used, and then failed. But when edited live in the container using nano, things seemed to work.

Will investigate further

This is likely related to the definition of `OAuthProviders` in `cps/up.py`, where the table itself is defined. I added an entry for `oauth_userinfo_url`, but for some reason it doesn't seem to properly pick up on it (potentially loosing other fields?). I was able to get it to not throw this error by live editing the file and recreating the db in the container, but making changes here seemingly didn't work. There's no error messages beign logged atm, but I almost suspect it might be an encoding issue? When edited within this repo, the Gitlab WebIDE was used, and then failed. But when edited live in the container using `nano`, things seemed to work. Will investigate further

Jafner commented

2022-07-12 13:58:08 -07:00

(Migrated from gitlab.jafner.net)

For version: adbc4cde
via:

apt update && \
apt install sqlite3 && \
sqlite3

followed by

.open /app/calibre-web/app.db
.dump

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE settings (
        id INTEGER NOT NULL, 
        mail_server VARCHAR, 
        mail_port INTEGER, 
        mail_use_ssl SMALLINT, 
        mail_login VARCHAR, 
        mail_password VARCHAR, 
        mail_from VARCHAR, 
        config_calibre_dir VARCHAR, 
        config_port INTEGER, 
        config_certfile VARCHAR, 
        config_keyfile VARCHAR, 
        config_calibre_web_title VARCHAR, 
        config_books_per_page INTEGER, 
        config_random_books INTEGER, 
        config_read_column INTEGER, 
        config_title_regex VARCHAR, 
        config_log_level SMALLINT, 
        config_uploading SMALLINT, 
        config_anonbrowse SMALLINT, 
        config_public_reg SMALLINT, 
        config_default_role SMALLINT, 
        config_default_show SMALLINT, 
        config_columns_to_ignore VARCHAR, 
        config_use_google_drive BOOLEAN, 
        config_google_drive_folder VARCHAR, 
        config_google_drive_watch_changes_response VARCHAR, 
        config_remote_login BOOLEAN, 
        config_use_goodreads BOOLEAN, 
        config_goodreads_api_key VARCHAR, 
        config_goodreads_api_secret VARCHAR, 
        config_mature_content_tags VARCHAR, 
        config_logfile VARCHAR, 
        config_ebookconverter INTEGER, 
        config_converterpath VARCHAR, 
        config_calibre VARCHAR, 
        config_rarfile_location VARCHAR, `config_authors_max` INTEGER DEFAULT 0, `config_theme` INTEGER DEFAULT 0, `config_access_log` SMALLINT DEFAULT 0, `config_access_logfile` VARCHAR, `config_kobo_sync` BOOLEAN DEFAULT 0, `config_denied_tags` VARCHAR DEFAULT '', `config_allowed_tags` VARCHAR DEFAULT '', `config_restricted_column` SMALLINT DEFAULT 0, `config_denied_column_value` VARCHAR DEFAULT '', `config_allowed_column_value` VARCHAR DEFAULT '', `config_login_type` INTEGER DEFAULT 0, `config_kobo_proxy` BOOLEAN DEFAULT 0, `config_ldap_provider_url` VARCHAR DEFAULT 'localhost', `config_ldap_port` SMALLINT DEFAULT 389, `config_ldap_schema` VARCHAR DEFAULT 'ldap', `config_ldap_serv_username` VARCHAR, `config_ldap_serv_password` VARCHAR, `config_ldap_use_ssl` BOOLEAN DEFAULT 0, `config_ldap_use_tls` BOOLEAN DEFAULT 0, `config_ldap_require_cert` BOOLEAN DEFAULT 0, `config_ldap_cert_path` VARCHAR, `config_ldap_dn` VARCHAR, `config_ldap_user_object` VARCHAR, `config_ldap_openldap` BOOLEAN DEFAULT 0, `config_updatechannel` INTEGER DEFAULT 0, `config_reverse_proxy_login_header_name` VARCHAR, `config_allow_reverse_proxy_header_login` BOOLEAN DEFAULT 0, `mail_size` INTEGER DEFAULT `26214400`, `mail_server_type` SMALLINT DEFAULT `0`, `mail_gmail_token` JSON DEFAULT `{}`, `config_calibre_uuid` VARCHAR, `config_external_port` INTEGER DEFAULT `8083`, `config_trustedhosts` VARCHAR DEFAULT ``, `config_default_language` VARCHAR(3) DEFAULT `all`, `config_default_locale` VARCHAR(2) DEFAULT `en`, `config_register_email` BOOLEAN DEFAULT 0, `config_ldap_authentication` SMALLINT DEFAULT `0`, `config_ldap_encryption` SMALLINT DEFAULT `0`, `config_ldap_cacert_path` VARCHAR DEFAULT ``, `config_ldap_key_path` VARCHAR DEFAULT ``, `config_ldap_member_user_object` VARCHAR DEFAULT ``, `config_ldap_group_object_filter` VARCHAR DEFAULT `(&(objectclass=posixGroup)(cn=%s))`, `config_ldap_group_members_field` VARCHAR DEFAULT `memberUid`, `config_ldap_group_name` VARCHAR DEFAULT `calibreweb`, `config_kepubifypath` VARCHAR, `config_upload_formats` VARCHAR DEFAULT `pdf,mp3,mp4,mobi,doc,rtf,ogg,azw3,docx,djvu,m4a,prc,txt,lit,cbt,opus,html,kepub,flac,m4b,epub,azw,odt,cbz,cbr,wav,fb2`, `config_unicode_filename` BOOLEAN DEFAULT 0, `schedule_start_time` INTEGER DEFAULT `4`, `schedule_duration` INTEGER DEFAULT `10`, `schedule_generate_book_covers` BOOLEAN DEFAULT 0, `schedule_generate_series_covers` BOOLEAN DEFAULT 0, `schedule_reconnect` BOOLEAN DEFAULT 0, 
        PRIMARY KEY (id), 
        CHECK (config_use_google_drive IN (0, 1)), 
        CHECK (config_remote_login IN (0, 1)), 
        CHECK (config_use_goodreads IN (0, 1))
);
INSERT INTO settings VALUES(1,'mail.example.com',25,0,'mail@example.com','mypassword','automailer <mail@example.com>',NULL,8083,NULL,NULL,'Calibre-Web',60,4,0,'^(A|The|An|Der|Die|Das|Den|Ein|Eine|Einen|Dem|Des|Einem|Eines)\s+',20,0,0,0,0,6143,NULL,0,NULL,'{}',0,0,NULL,NULL,'','',0,'',NULL,'/usr/bin/unrar',0,0,0,NULL,0,'','',0,'','',0,0,'localhost',389,'ldap','cn=admin,dc=example,dc=org','',0,0,0,'','dc=example,dc=org','uid=%s',0,0,NULL,0,26214400,0,'{}',NULL,8083,'','all','en',0,0,0,'','','','(&(objectclass=posixGroup)(cn=%s))','memberUid','calibreweb','','pdf,mp3,mp4,mobi,doc,rtf,ogg,azw3,docx,djvu,m4a,prc,txt,lit,cbt,opus,html,kepub,flac,m4b,epub,azw,odt,cbz,cbr,wav,fb2',0,4,10,0,0,0);
CREATE TABLE registration (
        id INTEGER NOT NULL, 
        domain VARCHAR, 'allow' INTEGER, 
        PRIMARY KEY (id)
);
INSERT INTO registration VALUES(1,'%.%',1);
CREATE TABLE bookmark (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        format VARCHAR COLLATE "NOCASE", 
        bookmark_key VARCHAR, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE shelf (
        id INTEGER NOT NULL, 
        name VARCHAR, 
        is_public INTEGER, 
        user_id INTEGER, 'uuid' STRING, 'created' DATETIME, 'last_modified' DATETIME, 'kobo_sync' BOOLEAN DEFAULT false, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE downloads (
        id INTEGER NOT NULL, 
        book_id INTEGER, 
        user_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE remote_auth_token (
        id INTEGER NOT NULL, 
        auth_token VARCHAR(8), 
        user_id INTEGER, 
        verified BOOLEAN, 
        expiration DATETIME, 'token_type' INTEGER DEFAULT 0, 
        PRIMARY KEY (id), 
        UNIQUE (auth_token), 
        FOREIGN KEY(user_id) REFERENCES user (id), 
        CHECK (verified IN (0, 1))
);
CREATE TABLE book_read_link (
        id INTEGER NOT NULL, 
        book_id INTEGER, 
        user_id INTEGER, 
        is_read BOOLEAN, 'read_status' INTEGER DEFAULT 0, 'last_modified' DATETIME, 'last_time_started_reading' DATETIME, 'times_started_reading' INTEGER DEFAULT 0, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id), 
        CHECK (is_read IN (0, 1))
);
CREATE TABLE book_shelf_link (
        id INTEGER NOT NULL, 
        book_id INTEGER, 
        "order" INTEGER, 
        shelf INTEGER, 'date_added' DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(shelf) REFERENCES shelf (id)
);
CREATE TABLE IF NOT EXISTS "oauthProvider" (
        id INTEGER NOT NULL, 
        provider_name VARCHAR, 
        oauth_client_id VARCHAR, 
        oauth_client_secret VARCHAR, 
        active BOOLEAN, 
        PRIMARY KEY (id), 
        CHECK (active IN (0, 1))
);
CREATE TABLE flask_dance_oauth (
        id INTEGER NOT NULL, 
        provider VARCHAR(50) NOT NULL, 
        created_at DATETIME NOT NULL, 
        token JSON NOT NULL, 
        provider_user_id VARCHAR(256), 
        user_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE user_session (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        session_key VARCHAR, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE shelf_archive (
        id INTEGER NOT NULL, 
        uuid VARCHAR, 
        user_id INTEGER, 
        last_modified DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE archived_book (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        is_archived BOOLEAN, 
        last_modified DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE kobo_synced_books (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE kobo_reading_state (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        last_modified DATETIME, 
        priority_timestamp DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE thumbnail (
        id INTEGER NOT NULL, 
        entity_id INTEGER, 
        uuid VARCHAR, 
        format VARCHAR, 
        type SMALLINT, 
        resolution SMALLINT, 
        filename VARCHAR, 
        generated_at DATETIME, 
        expiration DATETIME, 
        PRIMARY KEY (id), 
        UNIQUE (uuid)
);
CREATE TABLE kobo_bookmark (
        id INTEGER NOT NULL, 
        kobo_reading_state_id INTEGER, 
        last_modified DATETIME, 
        location_source VARCHAR, 
        location_type VARCHAR, 
        location_value VARCHAR, 
        progress_percent FLOAT, 
        content_source_progress_percent FLOAT, 
        PRIMARY KEY (id), 
        FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id)
);
CREATE TABLE kobo_statistics (
        id INTEGER NOT NULL, 
        kobo_reading_state_id INTEGER, 
        last_modified DATETIME, 
        remaining_time_minutes INTEGER, 
        spent_reading_minutes INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id)
);
CREATE TABLE IF NOT EXISTS "user" (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name VARCHAR(64),email VARCHAR(120),role SMALLINT,password VARCHAR,kindle_mail VARCHAR(120),locale VARCHAR(2),sidebar_view INTEGER,default_language VARCHAR(3),denied_tags VARCHAR,allowed_tags VARCHAR,denied_column_value VARCHAR,allowed_column_value VARCHAR,view_settings JSON,kobo_only_shelves_sync SMALLINT,UNIQUE (name),UNIQUE (email));
INSERT INTO user VALUES(1,'admin','',159,'pbkdf2:sha256:50000$Nyc0MwDU$cee9472a0f20e259efaa37f8ff60ab2dbd426871803226234c95f6c0b58547d1','','en',8191,'all','','','','','{}',0);
INSERT INTO user VALUES(2,'Guest','no@email',32,'','','en',1,'all','','','','','{}',0);
CREATE TABLE flask_settings (
        id INTEGER NOT NULL, 
        flask_session_key BLOB, 
        PRIMARY KEY (id)
);
INSERT INTO flask_settings VALUES(1,X'9e6ce6c1b32b44e53da3bb438423d9fc099cb1b755bf9588662ee2f274b42992');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('user',2);
COMMIT;

For version: adbc4cde via: ``` apt update && \ apt install sqlite3 && \ sqlite3 ``` followed by ``` .open /app/calibre-web/app.db .dump ``` ```sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE settings ( id INTEGER NOT NULL, mail_server VARCHAR, mail_port INTEGER, mail_use_ssl SMALLINT, mail_login VARCHAR, mail_password VARCHAR, mail_from VARCHAR, config_calibre_dir VARCHAR, config_port INTEGER, config_certfile VARCHAR, config_keyfile VARCHAR, config_calibre_web_title VARCHAR, config_books_per_page INTEGER, config_random_books INTEGER, config_read_column INTEGER, config_title_regex VARCHAR, config_log_level SMALLINT, config_uploading SMALLINT, config_anonbrowse SMALLINT, config_public_reg SMALLINT, config_default_role SMALLINT, config_default_show SMALLINT, config_columns_to_ignore VARCHAR, config_use_google_drive BOOLEAN, config_google_drive_folder VARCHAR, config_google_drive_watch_changes_response VARCHAR, config_remote_login BOOLEAN, config_use_goodreads BOOLEAN, config_goodreads_api_key VARCHAR, config_goodreads_api_secret VARCHAR, config_mature_content_tags VARCHAR, config_logfile VARCHAR, config_ebookconverter INTEGER, config_converterpath VARCHAR, config_calibre VARCHAR, config_rarfile_location VARCHAR, `config_authors_max` INTEGER DEFAULT 0, `config_theme` INTEGER DEFAULT 0, `config_access_log` SMALLINT DEFAULT 0, `config_access_logfile` VARCHAR, `config_kobo_sync` BOOLEAN DEFAULT 0, `config_denied_tags` VARCHAR DEFAULT '', `config_allowed_tags` VARCHAR DEFAULT '', `config_restricted_column` SMALLINT DEFAULT 0, `config_denied_column_value` VARCHAR DEFAULT '', `config_allowed_column_value` VARCHAR DEFAULT '', `config_login_type` INTEGER DEFAULT 0, `config_kobo_proxy` BOOLEAN DEFAULT 0, `config_ldap_provider_url` VARCHAR DEFAULT 'localhost', `config_ldap_port` SMALLINT DEFAULT 389, `config_ldap_schema` VARCHAR DEFAULT 'ldap', `config_ldap_serv_username` VARCHAR, `config_ldap_serv_password` VARCHAR, `config_ldap_use_ssl` BOOLEAN DEFAULT 0, `config_ldap_use_tls` BOOLEAN DEFAULT 0, `config_ldap_require_cert` BOOLEAN DEFAULT 0, `config_ldap_cert_path` VARCHAR, `config_ldap_dn` VARCHAR, `config_ldap_user_object` VARCHAR, `config_ldap_openldap` BOOLEAN DEFAULT 0, `config_updatechannel` INTEGER DEFAULT 0, `config_reverse_proxy_login_header_name` VARCHAR, `config_allow_reverse_proxy_header_login` BOOLEAN DEFAULT 0, `mail_size` INTEGER DEFAULT `26214400`, `mail_server_type` SMALLINT DEFAULT `0`, `mail_gmail_token` JSON DEFAULT `{}`, `config_calibre_uuid` VARCHAR, `config_external_port` INTEGER DEFAULT `8083`, `config_trustedhosts` VARCHAR DEFAULT ``, `config_default_language` VARCHAR(3) DEFAULT `all`, `config_default_locale` VARCHAR(2) DEFAULT `en`, `config_register_email` BOOLEAN DEFAULT 0, `config_ldap_authentication` SMALLINT DEFAULT `0`, `config_ldap_encryption` SMALLINT DEFAULT `0`, `config_ldap_cacert_path` VARCHAR DEFAULT ``, `config_ldap_key_path` VARCHAR DEFAULT ``, `config_ldap_member_user_object` VARCHAR DEFAULT ``, `config_ldap_group_object_filter` VARCHAR DEFAULT `(&(objectclass=posixGroup)(cn=%s))`, `config_ldap_group_members_field` VARCHAR DEFAULT `memberUid`, `config_ldap_group_name` VARCHAR DEFAULT `calibreweb`, `config_kepubifypath` VARCHAR, `config_upload_formats` VARCHAR DEFAULT `pdf,mp3,mp4,mobi,doc,rtf,ogg,azw3,docx,djvu,m4a,prc,txt,lit,cbt,opus,html,kepub,flac,m4b,epub,azw,odt,cbz,cbr,wav,fb2`, `config_unicode_filename` BOOLEAN DEFAULT 0, `schedule_start_time` INTEGER DEFAULT `4`, `schedule_duration` INTEGER DEFAULT `10`, `schedule_generate_book_covers` BOOLEAN DEFAULT 0, `schedule_generate_series_covers` BOOLEAN DEFAULT 0, `schedule_reconnect` BOOLEAN DEFAULT 0, PRIMARY KEY (id), CHECK (config_use_google_drive IN (0, 1)), CHECK (config_remote_login IN (0, 1)), CHECK (config_use_goodreads IN (0, 1)) ); INSERT INTO settings VALUES(1,'mail.example.com',25,0,'mail@example.com','mypassword','automailer <mail@example.com>',NULL,8083,NULL,NULL,'Calibre-Web',60,4,0,'^(A|The|An|Der|Die|Das|Den|Ein|Eine|Einen|Dem|Des|Einem|Eines)\s+',20,0,0,0,0,6143,NULL,0,NULL,'{}',0,0,NULL,NULL,'','',0,'',NULL,'/usr/bin/unrar',0,0,0,NULL,0,'','',0,'','',0,0,'localhost',389,'ldap','cn=admin,dc=example,dc=org','',0,0,0,'','dc=example,dc=org','uid=%s',0,0,NULL,0,26214400,0,'{}',NULL,8083,'','all','en',0,0,0,'','','','(&(objectclass=posixGroup)(cn=%s))','memberUid','calibreweb','','pdf,mp3,mp4,mobi,doc,rtf,ogg,azw3,docx,djvu,m4a,prc,txt,lit,cbt,opus,html,kepub,flac,m4b,epub,azw,odt,cbz,cbr,wav,fb2',0,4,10,0,0,0); CREATE TABLE registration ( id INTEGER NOT NULL, domain VARCHAR, 'allow' INTEGER, PRIMARY KEY (id) ); INSERT INTO registration VALUES(1,'%.%',1); CREATE TABLE bookmark ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, format VARCHAR COLLATE "NOCASE", bookmark_key VARCHAR, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE shelf ( id INTEGER NOT NULL, name VARCHAR, is_public INTEGER, user_id INTEGER, 'uuid' STRING, 'created' DATETIME, 'last_modified' DATETIME, 'kobo_sync' BOOLEAN DEFAULT false, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE downloads ( id INTEGER NOT NULL, book_id INTEGER, user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE remote_auth_token ( id INTEGER NOT NULL, auth_token VARCHAR(8), user_id INTEGER, verified BOOLEAN, expiration DATETIME, 'token_type' INTEGER DEFAULT 0, PRIMARY KEY (id), UNIQUE (auth_token), FOREIGN KEY(user_id) REFERENCES user (id), CHECK (verified IN (0, 1)) ); CREATE TABLE book_read_link ( id INTEGER NOT NULL, book_id INTEGER, user_id INTEGER, is_read BOOLEAN, 'read_status' INTEGER DEFAULT 0, 'last_modified' DATETIME, 'last_time_started_reading' DATETIME, 'times_started_reading' INTEGER DEFAULT 0, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id), CHECK (is_read IN (0, 1)) ); CREATE TABLE book_shelf_link ( id INTEGER NOT NULL, book_id INTEGER, "order" INTEGER, shelf INTEGER, 'date_added' DATETIME, PRIMARY KEY (id), FOREIGN KEY(shelf) REFERENCES shelf (id) ); CREATE TABLE IF NOT EXISTS "oauthProvider" ( id INTEGER NOT NULL, provider_name VARCHAR, oauth_client_id VARCHAR, oauth_client_secret VARCHAR, active BOOLEAN, PRIMARY KEY (id), CHECK (active IN (0, 1)) ); CREATE TABLE flask_dance_oauth ( id INTEGER NOT NULL, provider VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL, token JSON NOT NULL, provider_user_id VARCHAR(256), user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE user_session ( id INTEGER NOT NULL, user_id INTEGER, session_key VARCHAR, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE shelf_archive ( id INTEGER NOT NULL, uuid VARCHAR, user_id INTEGER, last_modified DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE archived_book ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, is_archived BOOLEAN, last_modified DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE kobo_synced_books ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE kobo_reading_state ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, last_modified DATETIME, priority_timestamp DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE thumbnail ( id INTEGER NOT NULL, entity_id INTEGER, uuid VARCHAR, format VARCHAR, type SMALLINT, resolution SMALLINT, filename VARCHAR, generated_at DATETIME, expiration DATETIME, PRIMARY KEY (id), UNIQUE (uuid) ); CREATE TABLE kobo_bookmark ( id INTEGER NOT NULL, kobo_reading_state_id INTEGER, last_modified DATETIME, location_source VARCHAR, location_type VARCHAR, location_value VARCHAR, progress_percent FLOAT, content_source_progress_percent FLOAT, PRIMARY KEY (id), FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id) ); CREATE TABLE kobo_statistics ( id INTEGER NOT NULL, kobo_reading_state_id INTEGER, last_modified DATETIME, remaining_time_minutes INTEGER, spent_reading_minutes INTEGER, PRIMARY KEY (id), FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id) ); CREATE TABLE IF NOT EXISTS "user" (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name VARCHAR(64),email VARCHAR(120),role SMALLINT,password VARCHAR,kindle_mail VARCHAR(120),locale VARCHAR(2),sidebar_view INTEGER,default_language VARCHAR(3),denied_tags VARCHAR,allowed_tags VARCHAR,denied_column_value VARCHAR,allowed_column_value VARCHAR,view_settings JSON,kobo_only_shelves_sync SMALLINT,UNIQUE (name),UNIQUE (email)); INSERT INTO user VALUES(1,'admin','',159,'pbkdf2:sha256:50000$Nyc0MwDU$cee9472a0f20e259efaa37f8ff60ab2dbd426871803226234c95f6c0b58547d1','','en',8191,'all','','','','','{}',0); INSERT INTO user VALUES(2,'Guest','no@email',32,'','','en',1,'all','','','','','{}',0); CREATE TABLE flask_settings ( id INTEGER NOT NULL, flask_session_key BLOB, PRIMARY KEY (id) ); INSERT INTO flask_settings VALUES(1,X'9e6ce6c1b32b44e53da3bb438423d9fc099cb1b755bf9588662ee2f274b42992'); DELETE FROM sqlite_sequence; INSERT INTO sqlite_sequence VALUES('user',2); COMMIT; ```

Jafner commented

2022-07-12 14:23:15 -07:00

(Migrated from gitlab.jafner.net)

CREATE TABLE IF NOT EXISTS "oauthProvider" (
        id INTEGER NOT NULL, 
        provider_name VARCHAR, 
        oauth_client_id VARCHAR, 
        oauth_client_secret VARCHAR, 
        active BOOLEAN, 
        PRIMARY KEY (id), 
        CHECK (active IN (0, 1))
);

Should be configured here: ub.py#L256
But it seems to be missing several columns:

oauth_userinfo_url
oauth_base_url
oauth_auth_url
oauth_token_url
scope
username_mapper
email_mapper
login_button

```sql CREATE TABLE IF NOT EXISTS "oauthProvider" ( id INTEGER NOT NULL, provider_name VARCHAR, oauth_client_id VARCHAR, oauth_client_secret VARCHAR, active BOOLEAN, PRIMARY KEY (id), CHECK (active IN (0, 1)) ); ``` Should be configured here: [ub.py#L256](https://gitlab.jafner.net/Jafner/calibre-web/-/blob/master/cps/ub.py#L256) But it seems to be missing several columns: ```py oauth_userinfo_url oauth_base_url oauth_auth_url oauth_token_url scope username_mapper email_mapper login_button ```

Jafner commented

2022-07-12 15:34:34 -07:00

(Migrated from gitlab.jafner.net)

https://stackoverflow.com/questions/50878513/sqlite-no-such-column-error-while-using-flask-and-sqlalchemy

I guess you've first created the database without this date column in the model Item. The method create_all cannot add this missing column to an existing table

If you can drop all the data inside your database, just delete the file catalogs.db (or better rename it as a backup), then the create_all method will generate a brand new working database.

https://stackoverflow.com/questions/50878513/sqlite-no-such-column-error-while-using-flask-and-sqlalchemy > I guess you've first created the database without this date column in the model Item. The method create_all cannot add this missing column to an existing table > If you can drop all the data inside your database, just delete the file catalogs.db (or better rename it as a backup), then the create_all method will generate a brand new working database.

Jafner commented

2022-07-12 15:51:52 -07:00

(Migrated from gitlab.jafner.net)

Babe wake up new error message just dropped:

Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 2073, in wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1518, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1516, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1502, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/dist-packages/flask_dance/consumer/oauth2.py", line 278, in authorized
results = oauth_authorized.send(self, token=token) or []
File "/usr/local/lib/python3.8/dist-packages/blinker/base.py", line 266, in send
return [(receiver, receiver(sender, **kwargs))
File "/usr/local/lib/python3.8/dist-packages/blinker/base.py", line 266, in <listcomp>
return [(receiver, receiver(sender, **kwargs))
File "/app/calibre-web/cps/oauth_bb.py", line 341, in generic_logged_in
resp = blueprint.session.get(blueprint.base_url + oauth_ids[2].get('oauth_userinfo_url'))
NameError: name 'oauth_ids' is not defined

This is using the nodb-156aeb28 image. Otherwise run identically to root issue,

Babe wake up new error message just dropped: ``` Traceback (most recent call last): File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 2073, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1518, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1516, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1502, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args) File "/usr/local/lib/python3.8/dist-packages/flask_dance/consumer/oauth2.py", line 278, in authorized results = oauth_authorized.send(self, token=token) or [] File "/usr/local/lib/python3.8/dist-packages/blinker/base.py", line 266, in send return [(receiver, receiver(sender, **kwargs)) File "/usr/local/lib/python3.8/dist-packages/blinker/base.py", line 266, in <listcomp> return [(receiver, receiver(sender, **kwargs)) File "/app/calibre-web/cps/oauth_bb.py", line 341, in generic_logged_in resp = blueprint.session.get(blueprint.base_url + oauth_ids[2].get('oauth_userinfo_url')) NameError: name 'oauth_ids' is not defined ``` This is using the `nodb-156aeb28` image. Otherwise run identically to root issue,

Jafner commented

2022-07-12 16:15:05 -07:00

(Migrated from gitlab.jafner.net)

New db dump:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        name VARCHAR(64), 
        email VARCHAR(120), 
        role SMALLINT, 
        password VARCHAR, 
        kindle_mail VARCHAR(120), 
        locale VARCHAR(2), 
        sidebar_view INTEGER, 
        default_language VARCHAR(3), 
        denied_tags VARCHAR, 
        allowed_tags VARCHAR, 
        denied_column_value VARCHAR, 
        allowed_column_value VARCHAR, 
        view_settings JSON, 
        kobo_only_shelves_sync INTEGER, 
        UNIQUE (name), 
        UNIQUE (email)
);
INSERT INTO user VALUES(1,'admin','',479,'pbkdf2:sha256:260000$KP97hMjPNEFJy7d8$373464f45391f1bb8dc27d2bef6affbc53110b7a03fd10c9089d9ab3a1a419f8','','en',262143,'all','','','','','{}',0);
INSERT INTO user VALUES(2,'Guest','no@email',32,'','','en',1,'all','','','','','{}',0);
CREATE TABLE IF NOT EXISTS "oauthProvider" (
        id INTEGER NOT NULL, 
        provider_name VARCHAR, 
        oauth_client_id VARCHAR, 
        oauth_client_secret VARCHAR, 
        oauth_userinfo_url VARCHAR, 
        oauth_base_url VARCHAR, 
        oauth_auth_url VARCHAR, 
        oauth_token_url VARCHAR, 
        scope VARCHAR, 
        username_mapper VARCHAR, 
        email_mapper VARCHAR, 
        login_button VARCHAR, 
        active BOOLEAN, 
        PRIMARY KEY (id)
);
INSERT INTO oauthProvider VALUES(1,'github','','',NULL,NULL,'/protocol/openid-connect/auth','/protocol/openid-connect/token','openid profile email','preferred_username','email',NULL,0);
INSERT INTO oauthProvider VALUES(2,'google','','',NULL,NULL,'/protocol/openid-connect/auth','/protocol/openid-connect/token','openid profile email','preferred_username','email',NULL,0);
INSERT INTO oauthProvider VALUES(3,'generic','6cab0a0cdf992a14019d82210c74650fd3530628','a2c9ffa5d7b4432acf27c52a8a8d91612e5bd77e8349d8f3d74edb47ea79a939e0fa6a52e2556f0f371433d3e96cdde33c06ac6206c7dc16f91038d8dcaecd0b','/application/o/userinfo/','https://authentik.jafner.net','/application/o/authorize/','/application/o/token/','openid profile email','preferred_username','email','',1);
CREATE TABLE registration (
        id INTEGER NOT NULL, 
        domain VARCHAR, 
        allow INTEGER, 
        PRIMARY KEY (id)
);
INSERT INTO registration VALUES(1,'%.%',1);
CREATE TABLE thumbnail (
        id INTEGER NOT NULL, 
        entity_id INTEGER, 
        uuid VARCHAR, 
        format VARCHAR, 
        type SMALLINT, 
        resolution SMALLINT, 
        filename VARCHAR, 
        generated_at DATETIME, 
        expiration DATETIME, 
        PRIMARY KEY (id), 
        UNIQUE (uuid)
);
CREATE TABLE flask_dance_oauth (
        id INTEGER NOT NULL, 
        provider VARCHAR(50) NOT NULL, 
        created_at DATETIME NOT NULL, 
        token JSON NOT NULL, 
        provider_user_id VARCHAR(256), 
        user_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
INSERT INTO flask_dance_oauth VALUES(1,'3','2022-07-12 23:04:46.835984','{"access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5MDg1LCJpYXQiOjE2NTc2NjcwODUsImF1dGhfdGltZSI6MTY1NzY2NzA4NCwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6bnVsbCwiZW1haWwiOiJqYWZuZXI0MjVAZ21haWwuY29tIiwiZW1haWxfdmVyaWZpZWQiOnRydWUsIm5hbWUiOiJKb2V5IiwiZ2l2ZW5fbmFtZSI6IkpvZXkiLCJmYW1pbHlfbmFtZSI6IiIsInByZWZlcnJlZF91c2VybmFtZSI6IkphZm5lciIsIm5pY2tuYW1lIjoiSmFmbmVyIiwiZ3JvdXBzIjpbIkphZm5lci5uZXQgQWRtaW5zIl0sImNpZCI6IjZjYWIwYTBjZGY5OTJhMTQwMTlkODIyMTBjNzQ2NTBmZDM1MzA2MjgiLCJ1aWQiOiIlOGtJLGVELlZdJDV1Zi0jOEchJlNYRjxQWHAqVzpLayY3Y2wua2IsQHs2Z2V5X0lCVTN8PFt-WzpTdzRpXCJ7SjU3MVdHWVI6ZEJLejl1fWBxd3hKN1c6TWppeW5QMVNXRlNTLHw1MztreD4hRFwibUY7dHZqMVIrKi5gVTJ0Jy95In0.OcQFSbmPoR0xjIpVOOTKA6avaIH36f45DZgT5IAzPahtrbH2TYmVVkVbLIaLyD4Vsi5VFk30R8onTP7x4e7LR3HXXEUT8A-EVD6_vlY3pw984DJsvPYonIAcoSiEEoJ7xDfwqi4Hrc1w1nIKFhIeCI2kiPYtFPPS6DTr-PHFytmDBauG4Ju5AMzfVqXnbARIZGHJnu3lm12pi0imgz09KeKH-yHV6lt_giXSVeNw4UHyXMRzunCoM_hZzvULhun3xWINYwnflokqkQ4cj_l9kWwTKxSWlFrKjx6qnTjVwP70WGTwyltDdlNxFknZ3-nfAotPlh4BlQrv-6LJ2AZaBeJrX47qdEfr-qoD3yHi7w4mm0ytUTjR6eCFDqQuOkLWIPP-nQujKvJbvWcm43edOazABcq4Tf7dbbA38a2A09Jh4MOOM3Vuagt12bZ98hNzk09GhhGKQ1NubrgXtrwyyIpHAWPOQ1WAQ5bnEIsjnTpbWV5OkubrQbttvKrMXnL1w_JFqKt7PwdT2GPtprMAznbj3d6Ylns14vzN3PItmeSNsIfEWVBFh-GgOsDaEaKpPedsthPXiGrs68Vez21cGdDS3TdTC61n58ZPEHUcPZb2KWI0xGqBZAteVHMilkobi-t1IvPL4rQnXDUOgveFUIhElidM8Ot0qTTMQ7TPUSk", "refresh_token": "Q3pdKFAic0VpLCUoW0lLJyJ4am1iUVhmXl0jXVlKW2koZ3gqUWUkOFN9JD5CeC9BTjx7ZlVwZDtdYjxeKSFDJj5OaDtrN0A9NUBrYXcjW0pmcyVJe0tIWEFIfnxuXkVKTzpbLEolUDs-aClPTygmIydkJSokMjc9fmFbLmhJelc=", "token_type": "bearer", "expires_in": 2592000, "id_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5MDg1LCJpYXQiOjE2NTc2NjcwODYsImF1dGhfdGltZSI6MTY1NzY2NzA4NCwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6ImNZUkxPczVGemE4dmdVcnU5MXhuemciLCJlbWFpbCI6ImphZm5lcjQyNUBnbWFpbC5jb20iLCJlbWFpbF92ZXJpZmllZCI6dHJ1ZSwibmFtZSI6IkpvZXkiLCJnaXZlbl9uYW1lIjoiSm9leSIsImZhbWlseV9uYW1lIjoiIiwicHJlZmVycmVkX3VzZXJuYW1lIjoiSmFmbmVyIiwibmlja25hbWUiOiJKYWZuZXIiLCJncm91cHMiOlsiSmFmbmVyLm5ldCBBZG1pbnMiXX0.I2DWcDdRP07IygR9vJCzqCsPLx2RjjMoDN9Sh-GtQerfYLFMkUgaY4tFF_OVQKEKqaKNIjeec_7poN73Mn97tSn8UCB1ojN4D9BQ1Iq_7j1sAk0kxFiopbVFwwc4d9rTMv4WCHRrYZ34SmYxP6n9Bpqxkmb-ZcNZt7ixJK0VVbCjVFqisM2mqjuMuq8ru4dBRYkS5wWUIMksvqVQErGP9TIS1EEupY1Wyz48ruMAD1zCwb4re0fwtcUNyCbS-3tHXbKRc_yy3eLLAV0k76gJWfrhzKxyh3mOB91a2tMYNWjBf4IJqRwQaHbL62ug800O0qPMXG6i7pDI4-n3zI7GyMzW7pvay2HV2YS4x2ZKV4yJt0v45X8jwpo0xKUXS2scgCk0v7FqNU6WgQzXc0ajhpcBD37sTyGwQomC7Rd_BHE7T7lJ2g4MrwQ9_-4zJlUV0ze8WXDxHocdXJoX_JgXSquTI-1CYPdDGJw8IMLSBu6M4QXQ1zPpFfTY2fJ7R-RtUc5BwIycgwy8XvkHLYtO7r-NPorRar6_hk9ED5N0-SFv6tgl7qAgSJqYGeg_RRACVxnnHSb0JpXvHk83KVY1xE71ZGblcFRe8SDky_7ZtNJ6FpFD5lNiyM23dalBvpoVTd8EJ1hPSU1HA29uogOjcgqWxUJUiMLJ7gQDqztgO9s", "expires_at": 1660259086.802951}','3',3);
INSERT INTO flask_dance_oauth VALUES(2,'3','2022-07-12 23:11:27.927211','{"access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5NDg2LCJpYXQiOjE2NTc2Njc0ODYsImF1dGhfdGltZSI6MTY1NzY2NzQxMSwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6bnVsbCwiZW1haWwiOiJqYWZuZXI0MjVAZ21haWwuY29tIiwiZW1haWxfdmVyaWZpZWQiOnRydWUsIm5hbWUiOiJKb2V5IiwiZ2l2ZW5fbmFtZSI6IkpvZXkiLCJmYW1pbHlfbmFtZSI6IiIsInByZWZlcnJlZF91c2VybmFtZSI6IkphZm5lciIsIm5pY2tuYW1lIjoiSmFmbmVyIiwiZ3JvdXBzIjpbIkphZm5lci5uZXQgQWRtaW5zIl0sImNpZCI6IjZjYWIwYTBjZGY5OTJhMTQwMTlkODIyMTBjNzQ2NTBmZDM1MzA2MjgiLCJ1aWQiOiJ3SH1LcmxobyY8RURuZEVHSk41NiVlJCwqSU9wSy1oOHdQI1JRZXBpP0JbZjJSLHx4WCprVCp1bmBLezdwQ0poXCJ1e2ExaUI6XCJJM0ZYZmtiUzlMVDI7SWBIfi1QJUUmLSNDSDxxW31PVmJ3QCVkeUQuLVRrc1YwJ30kUSY-P3haIn0.DfmX7Zs5M2QMV5Dk8ICgwhqQOLUvOKkZONB75ZCZG4KGgNIXP-QhBhwXnrCZ_Lj6aDp-1AUdtpziVMq-FwEOYTObKsrqaSD8_nKxPQKRJAnbJChZa1zWJ0tsuEWHXuYQVxtsgUMtF2whizAGaaUeF57t9CoNanWCTwqWQbt08WtU4RZg1HjfpW6WlrBf0YiwQcHO4FiYeewvAN07YDIQtXnn700VgQY0aXyzt0q92Azoy4jLCaA1Uqa0ZTDLiHCoc-2lDnRgdiR88QS4crctdW0sjTnNwCHnEHwwfwpw8GRL2ZmZeyGmn_gldlHiFZdmO2M089hd0sDDgq1I9YK6J7NxIOYjHcet035jB6Hcv10QHSTQmP8daaAxFxcXuPmj2MmmbDFSn9KytfsfUqmr-WPTC1zHnmQUrqUzdIDSTQJWM6LEUg4AXteJl_qWRb331YvI99jsqmTD42Gs3tccxQS20KTrjGY6B3vCQENIPj4tu3AXKmS9TS08nsRtxiUJsFvEAQKnCzyCktwdePqomoy8eGWShHOFKQiWgAZ8Y0GaiJbJe6VRnz20fs5SEGmn4f70Hy-sMKkx0J5UE-b69un-SG4PNJ-sY9CNEPiztq6ZuMBOS4fk_JBYbV00Dt-8CW4L2XyG8fA0CBUG_m9VFTTwj3PffwX_hwIpKCNPHf8", "refresh_token": "ODEnQGN5V0o_Tj9gVGFsYio-Z0F4UHkobnZHLHZQIkc3e1lnKXFDTTEjY1ZmXGZSNWViMHVDfU47T2ovPnc-XDVGSHxHRkdvNiomNVMzdjFLP3xtUXcsYC5xcmxUY0REbiQwdCEtTzo1bXBcVC4zUVRhJDA8MXMvdWdjTlNNRGM=", "token_type": "bearer", "expires_in": 2592000, "id_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5NDg2LCJpYXQiOjE2NTc2Njc0ODcsImF1dGhfdGltZSI6MTY1NzY2NzQxMSwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6ImxOa0x0dFE0aFd6SFhkbnQtTXI4UWciLCJlbWFpbCI6ImphZm5lcjQyNUBnbWFpbC5jb20iLCJlbWFpbF92ZXJpZmllZCI6dHJ1ZSwibmFtZSI6IkpvZXkiLCJnaXZlbl9uYW1lIjoiSm9leSIsImZhbWlseV9uYW1lIjoiIiwicHJlZmVycmVkX3VzZXJuYW1lIjoiSmFmbmVyIiwibmlja25hbWUiOiJKYWZuZXIiLCJncm91cHMiOlsiSmFmbmVyLm5ldCBBZG1pbnMiXX0.Lj5KSKte0GAceqeOJ15ZObS-jbxmkZ4L9h6m4YhU9E8hsYWOwjQ2TaC24ZkCnG8CXtpwZEr-z95WVatSCuzKk7szUAib73t8Sy22MamfD3hM_w8Pn_kI81xmgCNU8_jdVYCPN3qwCVmo1EiSozmNKDnrqPO90hXCQMh9TAv9xRIQ0Mp6GQIzcKHofyhDyQ-q29bpgpjZ_ZuRESoi4dB7QHS7XnEHrWCQy0iwa5HuGOylretRMwKcAsKEWuCIj2Ed_nBykq6q2BUYBAX0koSkvnY1WtIFls6HTlq2QZOcOYPozA3al8sAW-E20tnL9J68-xY7rSRw5HLldue60HMWLTtUw5mwFe9FoRWz54d57Xjf52CrO0TcbYnhotP-6ApfulBmOw6rVXCfBodQhn1_7eYAIaIDfKpAlZB97RBJWBrxPgH6_Fu3D83yeEaI0JpwXaLA4C15sUhakg45xi2TTczpFUVovIhkBJzCyf1Rq9EuuVxHd-TJiKMzPiIjvP5UNK1QL_lVQXTM9-qKYg4taU28UyQiPC27HD4Nb4VefadP_EK3vdcsvNqg-6EHVltaCF-9t9u6u_kSC7OdO6Dp_19QF6VdBZb_s9GGjCefWkVcpAg09PZC00NJ92eCtBjR0bn-EkrM_KAB6cmC6ykPIHf2e0_gf98IM1W7WzXnD0E", "expires_at": 1660259487.896511}','4',4);
CREATE TABLE user_session (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        session_key VARCHAR, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
INSERT INTO user_session VALUES(1,1,'0e2004a677da741f4e43a66b73866b7c0134c9059c438f155d92546bf640e4cc4d2573dfd5fb2b86d31a6a1f62d8017f6e8c94740d3a0225b85d1c2c32c1c70a');
CREATE TABLE shelf (
        id INTEGER NOT NULL, 
        uuid VARCHAR, 
        name VARCHAR, 
        is_public INTEGER, 
        user_id INTEGER, 
        kobo_sync BOOLEAN, 
        created DATETIME, 
        last_modified DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE shelf_archive (
        id INTEGER NOT NULL, 
        uuid VARCHAR, 
        user_id INTEGER, 
        last_modified DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE book_read_link (
        id INTEGER NOT NULL, 
        book_id INTEGER, 
        user_id INTEGER, 
        read_status INTEGER NOT NULL, 
        last_modified DATETIME, 
        last_time_started_reading DATETIME, 
        times_started_reading INTEGER NOT NULL, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE bookmark (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        format VARCHAR COLLATE "NOCASE", 
        bookmark_key VARCHAR, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE archived_book (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        is_archived BOOLEAN, 
        last_modified DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE kobo_synced_books (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE kobo_reading_state (
        id INTEGER NOT NULL, 
        user_id INTEGER, 
        book_id INTEGER, 
        last_modified DATETIME, 
        priority_timestamp DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE downloads (
        id INTEGER NOT NULL, 
        book_id INTEGER, 
        user_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE remote_auth_token (
        id INTEGER NOT NULL, 
        auth_token VARCHAR, 
        user_id INTEGER, 
        verified BOOLEAN, 
        expiration DATETIME, 
        token_type INTEGER, 
        PRIMARY KEY (id), 
        UNIQUE (auth_token), 
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE book_shelf_link (
        id INTEGER NOT NULL, 
        book_id INTEGER, 
        "order" INTEGER, 
        shelf INTEGER, 
        date_added DATETIME, 
        PRIMARY KEY (id), 
        FOREIGN KEY(shelf) REFERENCES shelf (id)
);
CREATE TABLE kobo_bookmark (
        id INTEGER NOT NULL, 
        kobo_reading_state_id INTEGER, 
        last_modified DATETIME, 
        location_source VARCHAR, 
        location_type VARCHAR, 
        location_value VARCHAR, 
        progress_percent FLOAT, 
        content_source_progress_percent FLOAT, 
        PRIMARY KEY (id), 
        FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id)
);
CREATE TABLE kobo_statistics (
        id INTEGER NOT NULL, 
        kobo_reading_state_id INTEGER, 
        last_modified DATETIME, 
        remaining_time_minutes INTEGER, 
        spent_reading_minutes INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id)
);
CREATE TABLE flask_settings (
        id INTEGER NOT NULL, 
        flask_session_key BLOB, 
        PRIMARY KEY (id)
);
INSERT INTO flask_settings VALUES(1,X'03dd283a9409b533e70a597a450aa1b7f3342ccfe3e1097024b330422e7a33fd');
CREATE TABLE settings (
        id INTEGER NOT NULL, 
        mail_server VARCHAR, 
        mail_port INTEGER, 
        mail_use_ssl SMALLINT, 
        mail_login VARCHAR, 
        mail_password VARCHAR, 
        mail_from VARCHAR, 
        mail_size INTEGER, 
        mail_server_type SMALLINT, 
        mail_gmail_token JSON, 
        config_calibre_dir VARCHAR, 
        config_calibre_uuid VARCHAR, 
        config_port INTEGER, 
        config_external_port INTEGER, 
        config_certfile VARCHAR, 
        config_keyfile VARCHAR, 
        config_trustedhosts VARCHAR, 
        config_calibre_web_title VARCHAR, 
        config_books_per_page INTEGER, 
        config_random_books INTEGER, 
        config_authors_max INTEGER, 
        config_read_column INTEGER, 
        config_title_regex VARCHAR, 
        config_theme INTEGER, 
        config_log_level SMALLINT, 
        config_logfile VARCHAR, 
        config_access_log SMALLINT, 
        config_access_logfile VARCHAR, 
        config_uploading SMALLINT, 
        config_anonbrowse SMALLINT, 
        config_public_reg SMALLINT, 
        config_remote_login BOOLEAN, 
        config_kobo_sync BOOLEAN, 
        config_default_role SMALLINT, 
        config_default_show SMALLINT, 
        config_default_language VARCHAR(3), 
        config_default_locale VARCHAR(2), 
        config_columns_to_ignore VARCHAR, 
        config_denied_tags VARCHAR, 
        config_allowed_tags VARCHAR, 
        config_restricted_column SMALLINT, 
        config_denied_column_value VARCHAR, 
        config_allowed_column_value VARCHAR, 
        config_use_google_drive BOOLEAN, 
        config_google_drive_folder VARCHAR, 
        config_google_drive_watch_changes_response JSON, 
        config_use_goodreads BOOLEAN, 
        config_goodreads_api_key VARCHAR, 
        config_goodreads_api_secret VARCHAR, 
        config_register_email BOOLEAN, 
        config_login_type INTEGER, 
        config_kobo_proxy BOOLEAN, 
        config_ldap_provider_url VARCHAR, 
        config_ldap_port SMALLINT, 
        config_ldap_authentication SMALLINT, 
        config_ldap_serv_username VARCHAR, 
        config_ldap_serv_password VARCHAR, 
        config_ldap_encryption SMALLINT, 
        config_ldap_cacert_path VARCHAR, 
        config_ldap_cert_path VARCHAR, 
        config_ldap_key_path VARCHAR, 
        config_ldap_dn VARCHAR, 
        config_ldap_user_object VARCHAR, 
        config_ldap_member_user_object VARCHAR, 
        config_ldap_openldap BOOLEAN, 
        config_ldap_group_object_filter VARCHAR, 
        config_ldap_group_members_field VARCHAR, 
        config_ldap_group_name VARCHAR, 
        config_kepubifypath VARCHAR, 
        config_converterpath VARCHAR, 
        config_calibre VARCHAR, 
        config_rarfile_location VARCHAR, 
        config_upload_formats VARCHAR, 
        config_unicode_filename BOOLEAN, 
        config_updatechannel INTEGER, 
        config_reverse_proxy_login_header_name VARCHAR, 
        config_allow_reverse_proxy_header_login BOOLEAN, 
        schedule_start_time INTEGER, 
        schedule_duration INTEGER, 
        schedule_generate_book_covers BOOLEAN, 
        schedule_generate_series_covers BOOLEAN, 
        schedule_reconnect BOOLEAN, 
        PRIMARY KEY (id)
);
INSERT INTO settings VALUES(1,'mail.example.org',25,0,'mail@example.com','mypassword','automailer <mail@example.com>',26214400,0,'{}','/books','e8d63001-b32b-4a9c-b054-5675c94ca3ff',8083,8083,'','','','Calibre-Web',60,4,0,0,'^(A|The|An|Der|Die|Das|Den|Ein|Eine|Einen|Dem|Des|Einem|Eines)\s+',0,20,'',0,'',0,0,0,0,0,0,262143,'all','en',NULL,'','',0,'','',0,NULL,'{}',0,'','',0,2,0,'example.org',389,0,'cn=admin,dc=example,dc=org','',0,'','','','dc=example,dc=org','uid=%s','',1,'(&(objectclass=posixGroup)(cn=%s))','memberUid','calibreweb','','','','/usr/bin/unrar','djvu,cbr,mp3,m4b,prc,cbt,opus,flac,m4a,ogg,fb2,mp4,odt,wav,azw,html,pdf,cbz,txt,doc,kepub,azw3,epub,mobi,docx,rtf,lit',0,0,'',0,4,10,0,0,0);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('user',4);
COMMIT;

New db dump: ```sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE user ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(64), email VARCHAR(120), role SMALLINT, password VARCHAR, kindle_mail VARCHAR(120), locale VARCHAR(2), sidebar_view INTEGER, default_language VARCHAR(3), denied_tags VARCHAR, allowed_tags VARCHAR, denied_column_value VARCHAR, allowed_column_value VARCHAR, view_settings JSON, kobo_only_shelves_sync INTEGER, UNIQUE (name), UNIQUE (email) ); INSERT INTO user VALUES(1,'admin','',479,'pbkdf2:sha256:260000$KP97hMjPNEFJy7d8$373464f45391f1bb8dc27d2bef6affbc53110b7a03fd10c9089d9ab3a1a419f8','','en',262143,'all','','','','','{}',0); INSERT INTO user VALUES(2,'Guest','no@email',32,'','','en',1,'all','','','','','{}',0); CREATE TABLE IF NOT EXISTS "oauthProvider" ( id INTEGER NOT NULL, provider_name VARCHAR, oauth_client_id VARCHAR, oauth_client_secret VARCHAR, oauth_userinfo_url VARCHAR, oauth_base_url VARCHAR, oauth_auth_url VARCHAR, oauth_token_url VARCHAR, scope VARCHAR, username_mapper VARCHAR, email_mapper VARCHAR, login_button VARCHAR, active BOOLEAN, PRIMARY KEY (id) ); INSERT INTO oauthProvider VALUES(1,'github','','',NULL,NULL,'/protocol/openid-connect/auth','/protocol/openid-connect/token','openid profile email','preferred_username','email',NULL,0); INSERT INTO oauthProvider VALUES(2,'google','','',NULL,NULL,'/protocol/openid-connect/auth','/protocol/openid-connect/token','openid profile email','preferred_username','email',NULL,0); INSERT INTO oauthProvider VALUES(3,'generic','6cab0a0cdf992a14019d82210c74650fd3530628','a2c9ffa5d7b4432acf27c52a8a8d91612e5bd77e8349d8f3d74edb47ea79a939e0fa6a52e2556f0f371433d3e96cdde33c06ac6206c7dc16f91038d8dcaecd0b','/application/o/userinfo/','https://authentik.jafner.net','/application/o/authorize/','/application/o/token/','openid profile email','preferred_username','email','',1); CREATE TABLE registration ( id INTEGER NOT NULL, domain VARCHAR, allow INTEGER, PRIMARY KEY (id) ); INSERT INTO registration VALUES(1,'%.%',1); CREATE TABLE thumbnail ( id INTEGER NOT NULL, entity_id INTEGER, uuid VARCHAR, format VARCHAR, type SMALLINT, resolution SMALLINT, filename VARCHAR, generated_at DATETIME, expiration DATETIME, PRIMARY KEY (id), UNIQUE (uuid) ); CREATE TABLE flask_dance_oauth ( id INTEGER NOT NULL, provider VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL, token JSON NOT NULL, provider_user_id VARCHAR(256), user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); INSERT INTO flask_dance_oauth VALUES(1,'3','2022-07-12 23:04:46.835984','{"access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5MDg1LCJpYXQiOjE2NTc2NjcwODUsImF1dGhfdGltZSI6MTY1NzY2NzA4NCwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6bnVsbCwiZW1haWwiOiJqYWZuZXI0MjVAZ21haWwuY29tIiwiZW1haWxfdmVyaWZpZWQiOnRydWUsIm5hbWUiOiJKb2V5IiwiZ2l2ZW5fbmFtZSI6IkpvZXkiLCJmYW1pbHlfbmFtZSI6IiIsInByZWZlcnJlZF91c2VybmFtZSI6IkphZm5lciIsIm5pY2tuYW1lIjoiSmFmbmVyIiwiZ3JvdXBzIjpbIkphZm5lci5uZXQgQWRtaW5zIl0sImNpZCI6IjZjYWIwYTBjZGY5OTJhMTQwMTlkODIyMTBjNzQ2NTBmZDM1MzA2MjgiLCJ1aWQiOiIlOGtJLGVELlZdJDV1Zi0jOEchJlNYRjxQWHAqVzpLayY3Y2wua2IsQHs2Z2V5X0lCVTN8PFt-WzpTdzRpXCJ7SjU3MVdHWVI6ZEJLejl1fWBxd3hKN1c6TWppeW5QMVNXRlNTLHw1MztreD4hRFwibUY7dHZqMVIrKi5gVTJ0Jy95In0.OcQFSbmPoR0xjIpVOOTKA6avaIH36f45DZgT5IAzPahtrbH2TYmVVkVbLIaLyD4Vsi5VFk30R8onTP7x4e7LR3HXXEUT8A-EVD6_vlY3pw984DJsvPYonIAcoSiEEoJ7xDfwqi4Hrc1w1nIKFhIeCI2kiPYtFPPS6DTr-PHFytmDBauG4Ju5AMzfVqXnbARIZGHJnu3lm12pi0imgz09KeKH-yHV6lt_giXSVeNw4UHyXMRzunCoM_hZzvULhun3xWINYwnflokqkQ4cj_l9kWwTKxSWlFrKjx6qnTjVwP70WGTwyltDdlNxFknZ3-nfAotPlh4BlQrv-6LJ2AZaBeJrX47qdEfr-qoD3yHi7w4mm0ytUTjR6eCFDqQuOkLWIPP-nQujKvJbvWcm43edOazABcq4Tf7dbbA38a2A09Jh4MOOM3Vuagt12bZ98hNzk09GhhGKQ1NubrgXtrwyyIpHAWPOQ1WAQ5bnEIsjnTpbWV5OkubrQbttvKrMXnL1w_JFqKt7PwdT2GPtprMAznbj3d6Ylns14vzN3PItmeSNsIfEWVBFh-GgOsDaEaKpPedsthPXiGrs68Vez21cGdDS3TdTC61n58ZPEHUcPZb2KWI0xGqBZAteVHMilkobi-t1IvPL4rQnXDUOgveFUIhElidM8Ot0qTTMQ7TPUSk", "refresh_token": "Q3pdKFAic0VpLCUoW0lLJyJ4am1iUVhmXl0jXVlKW2koZ3gqUWUkOFN9JD5CeC9BTjx7ZlVwZDtdYjxeKSFDJj5OaDtrN0A9NUBrYXcjW0pmcyVJe0tIWEFIfnxuXkVKTzpbLEolUDs-aClPTygmIydkJSokMjc9fmFbLmhJelc=", "token_type": "bearer", "expires_in": 2592000, "id_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5MDg1LCJpYXQiOjE2NTc2NjcwODYsImF1dGhfdGltZSI6MTY1NzY2NzA4NCwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6ImNZUkxPczVGemE4dmdVcnU5MXhuemciLCJlbWFpbCI6ImphZm5lcjQyNUBnbWFpbC5jb20iLCJlbWFpbF92ZXJpZmllZCI6dHJ1ZSwibmFtZSI6IkpvZXkiLCJnaXZlbl9uYW1lIjoiSm9leSIsImZhbWlseV9uYW1lIjoiIiwicHJlZmVycmVkX3VzZXJuYW1lIjoiSmFmbmVyIiwibmlja25hbWUiOiJKYWZuZXIiLCJncm91cHMiOlsiSmFmbmVyLm5ldCBBZG1pbnMiXX0.I2DWcDdRP07IygR9vJCzqCsPLx2RjjMoDN9Sh-GtQerfYLFMkUgaY4tFF_OVQKEKqaKNIjeec_7poN73Mn97tSn8UCB1ojN4D9BQ1Iq_7j1sAk0kxFiopbVFwwc4d9rTMv4WCHRrYZ34SmYxP6n9Bpqxkmb-ZcNZt7ixJK0VVbCjVFqisM2mqjuMuq8ru4dBRYkS5wWUIMksvqVQErGP9TIS1EEupY1Wyz48ruMAD1zCwb4re0fwtcUNyCbS-3tHXbKRc_yy3eLLAV0k76gJWfrhzKxyh3mOB91a2tMYNWjBf4IJqRwQaHbL62ug800O0qPMXG6i7pDI4-n3zI7GyMzW7pvay2HV2YS4x2ZKV4yJt0v45X8jwpo0xKUXS2scgCk0v7FqNU6WgQzXc0ajhpcBD37sTyGwQomC7Rd_BHE7T7lJ2g4MrwQ9_-4zJlUV0ze8WXDxHocdXJoX_JgXSquTI-1CYPdDGJw8IMLSBu6M4QXQ1zPpFfTY2fJ7R-RtUc5BwIycgwy8XvkHLYtO7r-NPorRar6_hk9ED5N0-SFv6tgl7qAgSJqYGeg_RRACVxnnHSb0JpXvHk83KVY1xE71ZGblcFRe8SDky_7ZtNJ6FpFD5lNiyM23dalBvpoVTd8EJ1hPSU1HA29uogOjcgqWxUJUiMLJ7gQDqztgO9s", "expires_at": 1660259086.802951}','3',3); INSERT INTO flask_dance_oauth VALUES(2,'3','2022-07-12 23:11:27.927211','{"access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5NDg2LCJpYXQiOjE2NTc2Njc0ODYsImF1dGhfdGltZSI6MTY1NzY2NzQxMSwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6bnVsbCwiZW1haWwiOiJqYWZuZXI0MjVAZ21haWwuY29tIiwiZW1haWxfdmVyaWZpZWQiOnRydWUsIm5hbWUiOiJKb2V5IiwiZ2l2ZW5fbmFtZSI6IkpvZXkiLCJmYW1pbHlfbmFtZSI6IiIsInByZWZlcnJlZF91c2VybmFtZSI6IkphZm5lciIsIm5pY2tuYW1lIjoiSmFmbmVyIiwiZ3JvdXBzIjpbIkphZm5lci5uZXQgQWRtaW5zIl0sImNpZCI6IjZjYWIwYTBjZGY5OTJhMTQwMTlkODIyMTBjNzQ2NTBmZDM1MzA2MjgiLCJ1aWQiOiJ3SH1LcmxobyY8RURuZEVHSk41NiVlJCwqSU9wSy1oOHdQI1JRZXBpP0JbZjJSLHx4WCprVCp1bmBLezdwQ0poXCJ1e2ExaUI6XCJJM0ZYZmtiUzlMVDI7SWBIfi1QJUUmLSNDSDxxW31PVmJ3QCVkeUQuLVRrc1YwJ30kUSY-P3haIn0.DfmX7Zs5M2QMV5Dk8ICgwhqQOLUvOKkZONB75ZCZG4KGgNIXP-QhBhwXnrCZ_Lj6aDp-1AUdtpziVMq-FwEOYTObKsrqaSD8_nKxPQKRJAnbJChZa1zWJ0tsuEWHXuYQVxtsgUMtF2whizAGaaUeF57t9CoNanWCTwqWQbt08WtU4RZg1HjfpW6WlrBf0YiwQcHO4FiYeewvAN07YDIQtXnn700VgQY0aXyzt0q92Azoy4jLCaA1Uqa0ZTDLiHCoc-2lDnRgdiR88QS4crctdW0sjTnNwCHnEHwwfwpw8GRL2ZmZeyGmn_gldlHiFZdmO2M089hd0sDDgq1I9YK6J7NxIOYjHcet035jB6Hcv10QHSTQmP8daaAxFxcXuPmj2MmmbDFSn9KytfsfUqmr-WPTC1zHnmQUrqUzdIDSTQJWM6LEUg4AXteJl_qWRb331YvI99jsqmTD42Gs3tccxQS20KTrjGY6B3vCQENIPj4tu3AXKmS9TS08nsRtxiUJsFvEAQKnCzyCktwdePqomoy8eGWShHOFKQiWgAZ8Y0GaiJbJe6VRnz20fs5SEGmn4f70Hy-sMKkx0J5UE-b69un-SG4PNJ-sY9CNEPiztq6ZuMBOS4fk_JBYbV00Dt-8CW4L2XyG8fA0CBUG_m9VFTTwj3PffwX_hwIpKCNPHf8", "refresh_token": "ODEnQGN5V0o_Tj9gVGFsYio-Z0F4UHkobnZHLHZQIkc3e1lnKXFDTTEjY1ZmXGZSNWViMHVDfU47T2ovPnc-XDVGSHxHRkdvNiomNVMzdjFLP3xtUXcsYC5xcmxUY0REbiQwdCEtTzo1bXBcVC4zUVRhJDA8MXMvdWdjTlNNRGM=", "token_type": "bearer", "expires_in": 2592000, "id_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6IjJiMmU2NzJhMTkzMWI1M2EzMzc0MjMxNDM4MDRiY2FmIn0.eyJpc3MiOiJodHRwczovL2F1dGhlbnRpay5qYWZuZXIubmV0L2FwcGxpY2F0aW9uL28vY2FsaWJyZS13ZWItZGV2LyIsInN1YiI6ImVhMDI1NDU0YzJjOWNlZTQ5ZDFmNmE4NGE4ZmNkZmJlNzYxNzhkNmVjYmVhZGUwMDY5MGQwYjE3MDg5YzVmNjkiLCJhdWQiOiI2Y2FiMGEwY2RmOTkyYTE0MDE5ZDgyMjEwYzc0NjUwZmQzNTMwNjI4IiwiZXhwIjoxNjYwMjU5NDg2LCJpYXQiOjE2NTc2Njc0ODcsImF1dGhfdGltZSI6MTY1NzY2NzQxMSwiYWNyIjoiZ29hdXRoZW50aWsuaW8vcHJvdmlkZXJzL29hdXRoMi9kZWZhdWx0IiwiY19oYXNoIjpudWxsLCJub25jZSI6bnVsbCwiYXRfaGFzaCI6ImxOa0x0dFE0aFd6SFhkbnQtTXI4UWciLCJlbWFpbCI6ImphZm5lcjQyNUBnbWFpbC5jb20iLCJlbWFpbF92ZXJpZmllZCI6dHJ1ZSwibmFtZSI6IkpvZXkiLCJnaXZlbl9uYW1lIjoiSm9leSIsImZhbWlseV9uYW1lIjoiIiwicHJlZmVycmVkX3VzZXJuYW1lIjoiSmFmbmVyIiwibmlja25hbWUiOiJKYWZuZXIiLCJncm91cHMiOlsiSmFmbmVyLm5ldCBBZG1pbnMiXX0.Lj5KSKte0GAceqeOJ15ZObS-jbxmkZ4L9h6m4YhU9E8hsYWOwjQ2TaC24ZkCnG8CXtpwZEr-z95WVatSCuzKk7szUAib73t8Sy22MamfD3hM_w8Pn_kI81xmgCNU8_jdVYCPN3qwCVmo1EiSozmNKDnrqPO90hXCQMh9TAv9xRIQ0Mp6GQIzcKHofyhDyQ-q29bpgpjZ_ZuRESoi4dB7QHS7XnEHrWCQy0iwa5HuGOylretRMwKcAsKEWuCIj2Ed_nBykq6q2BUYBAX0koSkvnY1WtIFls6HTlq2QZOcOYPozA3al8sAW-E20tnL9J68-xY7rSRw5HLldue60HMWLTtUw5mwFe9FoRWz54d57Xjf52CrO0TcbYnhotP-6ApfulBmOw6rVXCfBodQhn1_7eYAIaIDfKpAlZB97RBJWBrxPgH6_Fu3D83yeEaI0JpwXaLA4C15sUhakg45xi2TTczpFUVovIhkBJzCyf1Rq9EuuVxHd-TJiKMzPiIjvP5UNK1QL_lVQXTM9-qKYg4taU28UyQiPC27HD4Nb4VefadP_EK3vdcsvNqg-6EHVltaCF-9t9u6u_kSC7OdO6Dp_19QF6VdBZb_s9GGjCefWkVcpAg09PZC00NJ92eCtBjR0bn-EkrM_KAB6cmC6ykPIHf2e0_gf98IM1W7WzXnD0E", "expires_at": 1660259487.896511}','4',4); CREATE TABLE user_session ( id INTEGER NOT NULL, user_id INTEGER, session_key VARCHAR, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); INSERT INTO user_session VALUES(1,1,'0e2004a677da741f4e43a66b73866b7c0134c9059c438f155d92546bf640e4cc4d2573dfd5fb2b86d31a6a1f62d8017f6e8c94740d3a0225b85d1c2c32c1c70a'); CREATE TABLE shelf ( id INTEGER NOT NULL, uuid VARCHAR, name VARCHAR, is_public INTEGER, user_id INTEGER, kobo_sync BOOLEAN, created DATETIME, last_modified DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE shelf_archive ( id INTEGER NOT NULL, uuid VARCHAR, user_id INTEGER, last_modified DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE book_read_link ( id INTEGER NOT NULL, book_id INTEGER, user_id INTEGER, read_status INTEGER NOT NULL, last_modified DATETIME, last_time_started_reading DATETIME, times_started_reading INTEGER NOT NULL, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE bookmark ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, format VARCHAR COLLATE "NOCASE", bookmark_key VARCHAR, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE archived_book ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, is_archived BOOLEAN, last_modified DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE kobo_synced_books ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE kobo_reading_state ( id INTEGER NOT NULL, user_id INTEGER, book_id INTEGER, last_modified DATETIME, priority_timestamp DATETIME, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE downloads ( id INTEGER NOT NULL, book_id INTEGER, user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE remote_auth_token ( id INTEGER NOT NULL, auth_token VARCHAR, user_id INTEGER, verified BOOLEAN, expiration DATETIME, token_type INTEGER, PRIMARY KEY (id), UNIQUE (auth_token), FOREIGN KEY(user_id) REFERENCES user (id) ); CREATE TABLE book_shelf_link ( id INTEGER NOT NULL, book_id INTEGER, "order" INTEGER, shelf INTEGER, date_added DATETIME, PRIMARY KEY (id), FOREIGN KEY(shelf) REFERENCES shelf (id) ); CREATE TABLE kobo_bookmark ( id INTEGER NOT NULL, kobo_reading_state_id INTEGER, last_modified DATETIME, location_source VARCHAR, location_type VARCHAR, location_value VARCHAR, progress_percent FLOAT, content_source_progress_percent FLOAT, PRIMARY KEY (id), FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id) ); CREATE TABLE kobo_statistics ( id INTEGER NOT NULL, kobo_reading_state_id INTEGER, last_modified DATETIME, remaining_time_minutes INTEGER, spent_reading_minutes INTEGER, PRIMARY KEY (id), FOREIGN KEY(kobo_reading_state_id) REFERENCES kobo_reading_state (id) ); CREATE TABLE flask_settings ( id INTEGER NOT NULL, flask_session_key BLOB, PRIMARY KEY (id) ); INSERT INTO flask_settings VALUES(1,X'03dd283a9409b533e70a597a450aa1b7f3342ccfe3e1097024b330422e7a33fd'); CREATE TABLE settings ( id INTEGER NOT NULL, mail_server VARCHAR, mail_port INTEGER, mail_use_ssl SMALLINT, mail_login VARCHAR, mail_password VARCHAR, mail_from VARCHAR, mail_size INTEGER, mail_server_type SMALLINT, mail_gmail_token JSON, config_calibre_dir VARCHAR, config_calibre_uuid VARCHAR, config_port INTEGER, config_external_port INTEGER, config_certfile VARCHAR, config_keyfile VARCHAR, config_trustedhosts VARCHAR, config_calibre_web_title VARCHAR, config_books_per_page INTEGER, config_random_books INTEGER, config_authors_max INTEGER, config_read_column INTEGER, config_title_regex VARCHAR, config_theme INTEGER, config_log_level SMALLINT, config_logfile VARCHAR, config_access_log SMALLINT, config_access_logfile VARCHAR, config_uploading SMALLINT, config_anonbrowse SMALLINT, config_public_reg SMALLINT, config_remote_login BOOLEAN, config_kobo_sync BOOLEAN, config_default_role SMALLINT, config_default_show SMALLINT, config_default_language VARCHAR(3), config_default_locale VARCHAR(2), config_columns_to_ignore VARCHAR, config_denied_tags VARCHAR, config_allowed_tags VARCHAR, config_restricted_column SMALLINT, config_denied_column_value VARCHAR, config_allowed_column_value VARCHAR, config_use_google_drive BOOLEAN, config_google_drive_folder VARCHAR, config_google_drive_watch_changes_response JSON, config_use_goodreads BOOLEAN, config_goodreads_api_key VARCHAR, config_goodreads_api_secret VARCHAR, config_register_email BOOLEAN, config_login_type INTEGER, config_kobo_proxy BOOLEAN, config_ldap_provider_url VARCHAR, config_ldap_port SMALLINT, config_ldap_authentication SMALLINT, config_ldap_serv_username VARCHAR, config_ldap_serv_password VARCHAR, config_ldap_encryption SMALLINT, config_ldap_cacert_path VARCHAR, config_ldap_cert_path VARCHAR, config_ldap_key_path VARCHAR, config_ldap_dn VARCHAR, config_ldap_user_object VARCHAR, config_ldap_member_user_object VARCHAR, config_ldap_openldap BOOLEAN, config_ldap_group_object_filter VARCHAR, config_ldap_group_members_field VARCHAR, config_ldap_group_name VARCHAR, config_kepubifypath VARCHAR, config_converterpath VARCHAR, config_calibre VARCHAR, config_rarfile_location VARCHAR, config_upload_formats VARCHAR, config_unicode_filename BOOLEAN, config_updatechannel INTEGER, config_reverse_proxy_login_header_name VARCHAR, config_allow_reverse_proxy_header_login BOOLEAN, schedule_start_time INTEGER, schedule_duration INTEGER, schedule_generate_book_covers BOOLEAN, schedule_generate_series_covers BOOLEAN, schedule_reconnect BOOLEAN, PRIMARY KEY (id) ); INSERT INTO settings VALUES(1,'mail.example.org',25,0,'mail@example.com','mypassword','automailer <mail@example.com>',26214400,0,'{}','/books','e8d63001-b32b-4a9c-b054-5675c94ca3ff',8083,8083,'','','','Calibre-Web',60,4,0,0,'^(A|The|An|Der|Die|Das|Den|Ein|Eine|Einen|Dem|Des|Einem|Eines)\s+',0,20,'',0,'',0,0,0,0,0,0,262143,'all','en',NULL,'','',0,'','',0,NULL,'{}',0,'','',0,2,0,'example.org',389,0,'cn=admin,dc=example,dc=org','',0,'','','','dc=example,dc=org','uid=%s','',1,'(&(objectclass=posixGroup)(cn=%s))','memberUid','calibreweb','','','','/usr/bin/unrar','djvu,cbr,mp3,m4b,prc,cbt,opus,flac,m4a,ogg,fb2,mp4,odt,wav,azw,html,pdf,cbz,txt,doc,kepub,azw3,epub,mobi,docx,rtf,lit',0,0,'',0,4,10,0,0,0); DELETE FROM sqlite_sequence; INSERT INTO sqlite_sequence VALUES('user',4); COMMIT; ```

Jafner commented

2022-07-12 16:34:04 -07:00

(Migrated from gitlab.jafner.net)

bruh

Jafner commented

2022-07-12 16:35:05 -07:00

(Migrated from gitlab.jafner.net)

Currently, with the untracked changes we've made to oauth_bb.py we can successfully authenticate with OAuth2 if and only if the user name is all lower case...

Currently, with the untracked changes we've made to `oauth_bb.py` we can successfully authenticate with OAuth2 *if and only if* the user name is all lower case...

Jafner commented

2022-07-12 16:36:09 -07:00

(Migrated from gitlab.jafner.net)

Dump of oauth_bb.py with untracked changes.

#  This file is part of the Calibre-Web (https://github.com/janeczku/calibre-web)
#    Copyright (C) 2018-2019 OzzieIsaacs, cervinko, jkrehm, bodybybuddha, ok11,
#                            andy29485, idalin, Kyosfonica, wuqi, Kennyl, lemmsh,
#                            falgh1, grunjol, csitko, ytils, xybydy, trasba, vrabe,
#                            ruben-herold, marblepebble, JackED42, SiphonSquirrel,
#                            apetresc, nanu-c, mutschler
#
#  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 3 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/>

import json
from functools import wraps

from flask import session, request, make_response, abort
from flask import Blueprint, flash, redirect, url_for
from flask_babel import gettext as _
from flask_dance.consumer import oauth_authorized, oauth_error, OAuth2ConsumerBlueprint
from flask_dance.contrib.github import make_github_blueprint, github
from flask_dance.contrib.google import make_google_blueprint, google
from oauthlib.oauth2 import TokenExpiredError, InvalidGrantError
from flask_login import login_user, current_user, login_required
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.sql.expression import func, and_

from . import constants, logger, config, app, ub

try:
    from .oauth import OAuthBackend, backend_resultcode
except NameError:
    pass


oauth_check = {}
oauthblueprints = []
oauth = Blueprint('oauth', __name__)
log = logger.create()
generic = None


def oauth_required(f):
    @wraps(f)
    def inner(*args, **kwargs):
        if config.config_login_type == constants.LOGIN_OAUTH:
            return f(*args, **kwargs)
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            data = {'status': 'error', 'message': 'Not Found'}
            response = make_response(json.dumps(data, ensure_ascii=False))
            response.headers["Content-Type"] = "application/json; charset=utf-8"
            return response, 404
        abort(404)

    return inner


def register_oauth_blueprint(cid, show_name):
    oauth_check[cid] = show_name


def register_user_with_oauth(user=None):
    all_oauth = {}
    for oauth_key in oauth_check.keys():
        if str(oauth_key) + '_oauth_user_id' in session and session[str(oauth_key) + '_oauth_user_id'] != '':
            all_oauth[oauth_key] = oauth_check[oauth_key]
    if len(all_oauth.keys()) == 0:
        return
    if user is None:
        flash(_(u"Register with %(provider)s", provider=", ".join(list(all_oauth.values()))), category="success")
    else:
        for oauth_key in all_oauth.keys():
            # Find this OAuth token in the database, or create it
            query = ub.session.query(ub.OAuth).filter_by(
                provider=oauth_key,
                provider_user_id=session[str(oauth_key) + "_oauth_user_id"],
            )
            try:
                oauth_key = query.one()
                oauth_key.user_id = user.id
            except NoResultFound:
                # no found, return error
                return
            ub.session_commit("User {} with OAuth for provider {} registered".format(user.name, oauth_key))


def logout_oauth_user():
    for oauth_key in oauth_check.keys():
        if str(oauth_key) + '_oauth_user_id' in session:
            session.pop(str(oauth_key) + '_oauth_user_id')
            unlink_oauth(oauth_key)


def oauth_update_token(provider_id, token, provider_user_id):
    session[provider_id + "_oauth_user_id"] = provider_user_id
    session[provider_id + "_oauth_token"] = token

    # Find this OAuth token in the database, or create it
    query = ub.session.query(ub.OAuth).filter_by(
        provider=provider_id,
        provider_user_id=provider_user_id,
    )
    try:
        oauth_entry = query.one()
        # update token
        oauth_entry.token = token
    except NoResultFound:
        oauth_entry = ub.OAuth(
            provider=provider_id,
            provider_user_id=provider_user_id,
            token=token,
        )
    ub.session.add(oauth_entry)
    ub.session_commit()

    # Disable Flask-Dance's default behavior for saving the OAuth token
    # Value differrs depending on flask-dance version
    return backend_resultcode


def bind_oauth_or_register(provider_id, provider_user_id, redirect_url, provider_name):
    query = ub.session.query(ub.OAuth).filter_by(
        provider=provider_id,
        provider_user_id=provider_user_id,
    )
    try:
        oauth_entry = query.first()
        # already bind with user, just login
        if oauth_entry.user:
            login_user(oauth_entry.user)
            log.debug(u"You are now logged in as: '%s'", oauth_entry.user.name)
            flash(_(u"you are now logged in as: '%(nickname)s'", nickname= oauth_entry.user.name),
                  category="success")
            return redirect(url_for('web.index'))
        else:
            # bind to current user
            if current_user and current_user.is_authenticated:
                oauth_entry.user = current_user
                try:
                    ub.session.add(oauth_entry)
                    ub.session.commit()
                    flash(_(u"Link to %(oauth)s Succeeded", oauth=provider_name), category="success")
                    log.info("Link to {} Succeeded".format(provider_name))
                    return redirect(url_for('web.profile'))
                except Exception as ex:
                    log.error_or_exception(ex)
                    ub.session.rollback()
            else:
                flash(_(u"Login failed, No User Linked With OAuth Account"), category="error")
            log.info('Login failed, No User Linked With OAuth Account')
            return redirect(url_for('web.login'))
            # return redirect(url_for('web.login'))
            # if config.config_public_reg:
            #   return redirect(url_for('web.register'))
            # else:
            #    flash(_(u"Public registration is not enabled"), category="error")
            #    return redirect(url_for(redirect_url))
    except (NoResultFound, AttributeError):
        return redirect(url_for(redirect_url))


def get_oauth_status():
    status = []
    query = ub.session.query(ub.OAuth).filter_by(
        user_id=current_user.id,
    )
    try:
        oauths = query.all()
        for oauth_entry in oauths:
            status.append(int(oauth_entry.provider))
        return status
    except NoResultFound:
        return None


def unlink_oauth(provider):
    if request.host_url + 'me' != request.referrer:
        pass
    query = ub.session.query(ub.OAuth).filter_by(
        provider=provider,
        user_id=current_user.id,
    )
    try:
        oauth_entry = query.one()
        if current_user and current_user.is_authenticated:
            oauth_entry.user = current_user
            try:
                ub.session.delete(oauth_entry)
                ub.session.commit()
                logout_oauth_user()
                flash(_(u"Unlink to %(oauth)s Succeeded", oauth=oauth_check[provider]), category="success")
                log.info("Unlink to {} Succeeded".format(oauth_check[provider]))
            except Exception as ex:
                log.error_or_exception(ex)
                ub.session.rollback()
                flash(_(u"Unlink to %(oauth)s Failed", oauth=oauth_check[provider]), category="error")
    except NoResultFound:
        log.warning("oauth %s for user %d not found", provider, current_user.id)
        flash(_(u"Not Linked to %(oauth)s", oauth=provider), category="error")
    return redirect(url_for('web.profile'))

def generate_oauth_blueprints():
    global generic

    if not ub.session.query(ub.OAuthProvider).count():
        for provider in ("github", "google", "generic"):
            oauthProvider = ub.OAuthProvider()
            oauthProvider.provider_name = provider
            oauthProvider.active = False
            ub.session.add(oauthProvider)
            ub.session_commit("{} Blueprint Created".format(provider))

    oauth_ids = ub.session.query(ub.OAuthProvider).all()
    ele1 = dict(provider_name='github',
                id=oauth_ids[0].id,
                active=oauth_ids[0].active,
                oauth_client_id=oauth_ids[0].oauth_client_id,
                scope=None,
                oauth_client_secret=oauth_ids[0].oauth_client_secret,
                obtain_link='https://github.com/settings/developers')
    ele2 = dict(provider_name='google',
                id=oauth_ids[1].id,
                active=oauth_ids[1].active,
                scope=["https://www.googleapis.com/auth/userinfo.email"],
                oauth_client_id=oauth_ids[1].oauth_client_id,
                oauth_client_secret=oauth_ids[1].oauth_client_secret,
                obtain_link='https://console.developers.google.com/apis/credentials')
    ele3 = dict(provider_name='generic',
                id=oauth_ids[2].id,
                active=oauth_ids[2].active,
                scope=oauth_ids[2].scope,
                oauth_client_id=oauth_ids[2].oauth_client_id,
                oauth_client_secret=oauth_ids[2].oauth_client_secret,
                oauth_base_url=oauth_ids[2].oauth_base_url,
                oauth_auth_url=oauth_ids[2].oauth_auth_url,
                oauth_token_url=oauth_ids[2].oauth_token_url,
                oauth_userinfo_url=oauth_ids[2].oauth_userinfo_url,
                username_mapper=oauth_ids[2].username_mapper,
                email_mapper=oauth_ids[2].email_mapper,
                login_button=oauth_ids[2].login_button)
    oauthblueprints.append(ele1)
    oauthblueprints.append(ele2)
    oauthblueprints.append(ele3)

    for element in oauthblueprints:
        if element['provider_name'] == 'github':
            blueprint_func = make_github_blueprint
        elif element['provider_name'] == 'google':
            blueprint_func = make_google_blueprint
        else:
            blueprint_func = OAuth2ConsumerBlueprint

        if element['provider_name'] in ('github', 'google'):
            blueprint = blueprint_func(
                client_id=element['oauth_client_id'],
                client_secret=element['oauth_client_secret'],
                redirect_url="oauth."+element['provider_name']+"_login",
                scope=element['scope']
            )
        else:
            base_url = element.get('oauth_base_url') or ''
            token_url = element.get('oauth_token_url') or ''
            auth_url = element.get('oauth_auth_url') or ''
            blueprint = blueprint_func(
                "generic",
                __name__,
                client_id=element['oauth_client_id'],
                client_secret=element['oauth_client_secret'],
                base_url=base_url,
                authorization_url=base_url + auth_url,
                token_url=base_url + token_url,
                redirect_to='oauth.'+element['provider_name']+'_login',
            )
            generic = blueprint
        element['blueprint'] = blueprint
        element['blueprint'].backend = OAuthBackend(ub.OAuth, ub.session, str(element['id']),
                                                    user=current_user, user_required=True)
        app.register_blueprint(blueprint, url_prefix="/login")
        if element['active']:
            register_oauth_blueprint(element['id'], element['provider_name'])
    return oauthblueprints


if ub.oauth_support:
    oauthblueprints = generate_oauth_blueprints()

    @oauth_authorized.connect_via(oauthblueprints[0]['blueprint'])
    def github_logged_in(blueprint, token):
        if not token:
            flash(_(u"Failed to log in with GitHub."), category="error")
            log.error("Failed to log in with GitHub")
            return False

        resp = blueprint.session.get("/user")
        if not resp.ok:
            flash(_(u"Failed to fetch user info from GitHub."), category="error")
            log.error("Failed to fetch user info from GitHub")
            return False

        github_info = resp.json()
        github_user_id = str(github_info["id"])
        return oauth_update_token(str(oauthblueprints[0]['id']), token, github_user_id)


    @oauth_authorized.connect_via(oauthblueprints[1]['blueprint'])
    def google_logged_in(blueprint, token):
        if not token:
            flash(_(u"Failed to log in with Google."), category="error")
            log.error("Failed to log in with Google")
            return False

        resp = blueprint.session.get("/oauth2/v2/userinfo")
        if not resp.ok:
            flash(_(u"Failed to fetch user info from Google."), category="error")
            log.error("Failed to fetch user info from Google")
            return False

        google_info = resp.json()
        google_user_id = str(google_info["id"])
        return oauth_update_token(str(oauthblueprints[1]['id']), token, google_user_id)


    @oauth_authorized.connect_via(oauthblueprints[2]['blueprint'])
    def generic_logged_in(blueprint, token):
        global generic

        if not token:
            flash(_(u"Failed to log in with generic OAuth provider."), category="error")
            log.error("Failed to log in with generic OAuth2 provider")
            return False
        
        resp = blueprint.session.get(blueprint.base_url + oauthblueprints[2].get('oauth_userinfo_url'))
        if not resp.ok:
            flash(_(u"Failed to fetch user info from generic OAuth2 provider."), category="error")
            log.error("Failed to fetch user info from generic OAuth2 provider")
            return False

        username_mapper = oauthblueprints[2].get('username_mapper') or 'username'
        email_mapper = oauthblueprints[2].get('email_mapper') or 'email'

        generic_info = resp.json()
        generic_user_email = str(generic_info[email_mapper])
        generic_user_username = str(generic_info[username_mapper])

        user = (
            ub.session.query(ub.User)
            .filter(and_(func.lower(ub.User.name) == generic_user_username,
                    func.lower(ub.User.email) == generic_user_email))
        ).first()

        if user is None:
            user = ub.User()
            user.name = generic_user_username
            user.email = generic_user_email
            user.role = constants.ROLE_USER
            ub.session.add(user)
            ub.session_commit()

        result = oauth_update_token(str(oauthblueprints[2].get('id')), token, user.id)

        query = ub.session.query(ub.OAuth).filter_by(
            provider=str(oauthblueprints[2].get('id')),
            provider_user_id=user.id,
        )
        oauth_entry = query.first()
        oauth_entry.user = user
        ub.session_commit()

        return result


    # notify on OAuth provider error
    @oauth_error.connect_via(oauthblueprints[0]['blueprint'])
    def github_error(blueprint, error, error_description=None, error_uri=None):
        msg = (
            u"OAuth error from {name}! "
            u"error={error} description={description} uri={uri}"
        ).format(
            name=blueprint.name,
            error=error,
            description=error_description,
            uri=error_uri,
        )  # ToDo: Translate
        flash(msg, category="error")

    @oauth_error.connect_via(oauthblueprints[1]['blueprint'])
    def google_error(blueprint, error, error_description=None, error_uri=None):
        msg = (
            u"OAuth error from {name}! "
            u"error={error} description={description} uri={uri}"
        ).format(
            name=blueprint.name,
            error=error,
            description=error_description,
            uri=error_uri,
        )  # ToDo: Translate
        flash(msg, category="error")


    @oauth_error.connect_via(oauthblueprints[2]['blueprint'])
    def generic_error(blueprint, error, error_description=None, error_uri=None):
        msg = (
            u"OAuth error from {name}! "
            u"error={error} description={description} uri={uri}"
        ).format(
            name=blueprint.name,
            error=error,
            description=error_description,
            uri=error_uri,
        )  # ToDo: Translate
        flash(msg, category="error")


@oauth.route('/link/github')
@oauth_required
def github_login():
    if not github.authorized:
        return redirect(url_for('github.login'))
    try:
        account_info = github.get('/user')
        if account_info.ok:
            account_info_json = account_info.json()
            return bind_oauth_or_register(oauthblueprints[0]['id'], account_info_json['id'], 'github.login', 'github')
        flash(_(u"GitHub Oauth error, please retry later."), category="error")
        log.error("GitHub Oauth error, please retry later")
    except (InvalidGrantError, TokenExpiredError) as e:
        flash(_(u"GitHub Oauth error: {}").format(e), category="error")
        log.error(e)
    return redirect(url_for('web.login'))


@oauth.route('/unlink/github', methods=["GET"])
@login_required
def github_login_unlink():
    return unlink_oauth(oauthblueprints[0]['id'])


@oauth.route('/link/google')
@oauth_required
def google_login():
    if not google.authorized:
        return redirect(url_for("google.login"))
    try:
        resp = google.get("/oauth2/v2/userinfo")
        if resp.ok:
            account_info_json = resp.json()
            return bind_oauth_or_register(oauthblueprints[1]['id'], account_info_json['id'], 'google.login', 'google')
        flash(_(u"Google Oauth error, please retry later."), category="error")
        log.error("Google Oauth error, please retry later")
    except (InvalidGrantError, TokenExpiredError) as e:
        flash(_(u"Google Oauth error: {}").format(e), category="error")
        log.error(e)
    return redirect(url_for('web.login'))


@oauth.route('/unlink/google', methods=["GET"])
@login_required
def google_login_unlink():
    return unlink_oauth(oauthblueprints[1]['id'])


@oauth.route('/link/generic')
@oauth_required
def generic_login():
    global generic

    if not generic.session.authorized:
        return redirect(url_for("generic.login"))
    try:
        resp = generic.session.get(generic.base_url + oauthblueprints[2].get('oauth_userinfo_url'))
        if resp.ok:
            account_info_json = resp.json()

            username_mapper = oauthblueprints[2].get('username_mapper') or 'username'
            email_mapper = oauthblueprints[2].get('email_mapper') or 'email'

            email = str(account_info_json[email_mapper])
            username = str(account_info_json[username_mapper])

            user = (
                ub.session.query(ub.User)
                .filter(and_(func.lower(ub.User.name) == username,
                        func.lower(ub.User.email) == email))
            ).first()

            return bind_oauth_or_register(oauthblueprints[2].get('id'), user.id, 'generic.login', 'generic')
        flash(_(u"generic OAuth2 error, please retry later."), category="error")
        log.error("generic OAuth2 error, please retry later")
    except (InvalidGrantError, TokenExpiredError) as e:
        log.error(e)
    return redirect(url_for("generic.login"))


@oauth.route('/unlink/generic', methods=["GET"])
@login_required
def generic_login_unlink():
    return unlink_oauth(oauthblueprints[2].get('id'))

Dump of `oauth_bb.py` with untracked changes. ```py # This file is part of the Calibre-Web (https://github.com/janeczku/calibre-web) # Copyright (C) 2018-2019 OzzieIsaacs, cervinko, jkrehm, bodybybuddha, ok11, # andy29485, idalin, Kyosfonica, wuqi, Kennyl, lemmsh, # falgh1, grunjol, csitko, ytils, xybydy, trasba, vrabe, # ruben-herold, marblepebble, JackED42, SiphonSquirrel, # apetresc, nanu-c, mutschler # # 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 3 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/> import json from functools import wraps from flask import session, request, make_response, abort from flask import Blueprint, flash, redirect, url_for from flask_babel import gettext as _ from flask_dance.consumer import oauth_authorized, oauth_error, OAuth2ConsumerBlueprint from flask_dance.contrib.github import make_github_blueprint, github from flask_dance.contrib.google import make_google_blueprint, google from oauthlib.oauth2 import TokenExpiredError, InvalidGrantError from flask_login import login_user, current_user, login_required from sqlalchemy.orm.exc import NoResultFound from sqlalchemy.sql.expression import func, and_ from . import constants, logger, config, app, ub try: from .oauth import OAuthBackend, backend_resultcode except NameError: pass oauth_check = {} oauthblueprints = [] oauth = Blueprint('oauth', __name__) log = logger.create() generic = None def oauth_required(f): @wraps(f) def inner(*args, **kwargs): if config.config_login_type == constants.LOGIN_OAUTH: return f(*args, **kwargs) if request.headers.get('X-Requested-With') == 'XMLHttpRequest': data = {'status': 'error', 'message': 'Not Found'} response = make_response(json.dumps(data, ensure_ascii=False)) response.headers["Content-Type"] = "application/json; charset=utf-8" return response, 404 abort(404) return inner def register_oauth_blueprint(cid, show_name): oauth_check[cid] = show_name def register_user_with_oauth(user=None): all_oauth = {} for oauth_key in oauth_check.keys(): if str(oauth_key) + '_oauth_user_id' in session and session[str(oauth_key) + '_oauth_user_id'] != '': all_oauth[oauth_key] = oauth_check[oauth_key] if len(all_oauth.keys()) == 0: return if user is None: flash(_(u"Register with %(provider)s", provider=", ".join(list(all_oauth.values()))), category="success") else: for oauth_key in all_oauth.keys(): # Find this OAuth token in the database, or create it query = ub.session.query(ub.OAuth).filter_by( provider=oauth_key, provider_user_id=session[str(oauth_key) + "_oauth_user_id"], ) try: oauth_key = query.one() oauth_key.user_id = user.id except NoResultFound: # no found, return error return ub.session_commit("User {} with OAuth for provider {} registered".format(user.name, oauth_key)) def logout_oauth_user(): for oauth_key in oauth_check.keys(): if str(oauth_key) + '_oauth_user_id' in session: session.pop(str(oauth_key) + '_oauth_user_id') unlink_oauth(oauth_key) def oauth_update_token(provider_id, token, provider_user_id): session[provider_id + "_oauth_user_id"] = provider_user_id session[provider_id + "_oauth_token"] = token # Find this OAuth token in the database, or create it query = ub.session.query(ub.OAuth).filter_by( provider=provider_id, provider_user_id=provider_user_id, ) try: oauth_entry = query.one() # update token oauth_entry.token = token except NoResultFound: oauth_entry = ub.OAuth( provider=provider_id, provider_user_id=provider_user_id, token=token, ) ub.session.add(oauth_entry) ub.session_commit() # Disable Flask-Dance's default behavior for saving the OAuth token # Value differrs depending on flask-dance version return backend_resultcode def bind_oauth_or_register(provider_id, provider_user_id, redirect_url, provider_name): query = ub.session.query(ub.OAuth).filter_by( provider=provider_id, provider_user_id=provider_user_id, ) try: oauth_entry = query.first() # already bind with user, just login if oauth_entry.user: login_user(oauth_entry.user) log.debug(u"You are now logged in as: '%s'", oauth_entry.user.name) flash(_(u"you are now logged in as: '%(nickname)s'", nickname= oauth_entry.user.name), category="success") return redirect(url_for('web.index')) else: # bind to current user if current_user and current_user.is_authenticated: oauth_entry.user = current_user try: ub.session.add(oauth_entry) ub.session.commit() flash(_(u"Link to %(oauth)s Succeeded", oauth=provider_name), category="success") log.info("Link to {} Succeeded".format(provider_name)) return redirect(url_for('web.profile')) except Exception as ex: log.error_or_exception(ex) ub.session.rollback() else: flash(_(u"Login failed, No User Linked With OAuth Account"), category="error") log.info('Login failed, No User Linked With OAuth Account') return redirect(url_for('web.login')) # return redirect(url_for('web.login')) # if config.config_public_reg: # return redirect(url_for('web.register')) # else: # flash(_(u"Public registration is not enabled"), category="error") # return redirect(url_for(redirect_url)) except (NoResultFound, AttributeError): return redirect(url_for(redirect_url)) def get_oauth_status(): status = [] query = ub.session.query(ub.OAuth).filter_by( user_id=current_user.id, ) try: oauths = query.all() for oauth_entry in oauths: status.append(int(oauth_entry.provider)) return status except NoResultFound: return None def unlink_oauth(provider): if request.host_url + 'me' != request.referrer: pass query = ub.session.query(ub.OAuth).filter_by( provider=provider, user_id=current_user.id, ) try: oauth_entry = query.one() if current_user and current_user.is_authenticated: oauth_entry.user = current_user try: ub.session.delete(oauth_entry) ub.session.commit() logout_oauth_user() flash(_(u"Unlink to %(oauth)s Succeeded", oauth=oauth_check[provider]), category="success") log.info("Unlink to {} Succeeded".format(oauth_check[provider])) except Exception as ex: log.error_or_exception(ex) ub.session.rollback() flash(_(u"Unlink to %(oauth)s Failed", oauth=oauth_check[provider]), category="error") except NoResultFound: log.warning("oauth %s for user %d not found", provider, current_user.id) flash(_(u"Not Linked to %(oauth)s", oauth=provider), category="error") return redirect(url_for('web.profile')) def generate_oauth_blueprints(): global generic if not ub.session.query(ub.OAuthProvider).count(): for provider in ("github", "google", "generic"): oauthProvider = ub.OAuthProvider() oauthProvider.provider_name = provider oauthProvider.active = False ub.session.add(oauthProvider) ub.session_commit("{} Blueprint Created".format(provider)) oauth_ids = ub.session.query(ub.OAuthProvider).all() ele1 = dict(provider_name='github', id=oauth_ids[0].id, active=oauth_ids[0].active, oauth_client_id=oauth_ids[0].oauth_client_id, scope=None, oauth_client_secret=oauth_ids[0].oauth_client_secret, obtain_link='https://github.com/settings/developers') ele2 = dict(provider_name='google', id=oauth_ids[1].id, active=oauth_ids[1].active, scope=["https://www.googleapis.com/auth/userinfo.email"], oauth_client_id=oauth_ids[1].oauth_client_id, oauth_client_secret=oauth_ids[1].oauth_client_secret, obtain_link='https://console.developers.google.com/apis/credentials') ele3 = dict(provider_name='generic', id=oauth_ids[2].id, active=oauth_ids[2].active, scope=oauth_ids[2].scope, oauth_client_id=oauth_ids[2].oauth_client_id, oauth_client_secret=oauth_ids[2].oauth_client_secret, oauth_base_url=oauth_ids[2].oauth_base_url, oauth_auth_url=oauth_ids[2].oauth_auth_url, oauth_token_url=oauth_ids[2].oauth_token_url, oauth_userinfo_url=oauth_ids[2].oauth_userinfo_url, username_mapper=oauth_ids[2].username_mapper, email_mapper=oauth_ids[2].email_mapper, login_button=oauth_ids[2].login_button) oauthblueprints.append(ele1) oauthblueprints.append(ele2) oauthblueprints.append(ele3) for element in oauthblueprints: if element['provider_name'] == 'github': blueprint_func = make_github_blueprint elif element['provider_name'] == 'google': blueprint_func = make_google_blueprint else: blueprint_func = OAuth2ConsumerBlueprint if element['provider_name'] in ('github', 'google'): blueprint = blueprint_func( client_id=element['oauth_client_id'], client_secret=element['oauth_client_secret'], redirect_url="oauth."+element['provider_name']+"_login", scope=element['scope'] ) else: base_url = element.get('oauth_base_url') or '' token_url = element.get('oauth_token_url') or '' auth_url = element.get('oauth_auth_url') or '' blueprint = blueprint_func( "generic", __name__, client_id=element['oauth_client_id'], client_secret=element['oauth_client_secret'], base_url=base_url, authorization_url=base_url + auth_url, token_url=base_url + token_url, redirect_to='oauth.'+element['provider_name']+'_login', ) generic = blueprint element['blueprint'] = blueprint element['blueprint'].backend = OAuthBackend(ub.OAuth, ub.session, str(element['id']), user=current_user, user_required=True) app.register_blueprint(blueprint, url_prefix="/login") if element['active']: register_oauth_blueprint(element['id'], element['provider_name']) return oauthblueprints if ub.oauth_support: oauthblueprints = generate_oauth_blueprints() @oauth_authorized.connect_via(oauthblueprints[0]['blueprint']) def github_logged_in(blueprint, token): if not token: flash(_(u"Failed to log in with GitHub."), category="error") log.error("Failed to log in with GitHub") return False resp = blueprint.session.get("/user") if not resp.ok: flash(_(u"Failed to fetch user info from GitHub."), category="error") log.error("Failed to fetch user info from GitHub") return False github_info = resp.json() github_user_id = str(github_info["id"]) return oauth_update_token(str(oauthblueprints[0]['id']), token, github_user_id) @oauth_authorized.connect_via(oauthblueprints[1]['blueprint']) def google_logged_in(blueprint, token): if not token: flash(_(u"Failed to log in with Google."), category="error") log.error("Failed to log in with Google") return False resp = blueprint.session.get("/oauth2/v2/userinfo") if not resp.ok: flash(_(u"Failed to fetch user info from Google."), category="error") log.error("Failed to fetch user info from Google") return False google_info = resp.json() google_user_id = str(google_info["id"]) return oauth_update_token(str(oauthblueprints[1]['id']), token, google_user_id) @oauth_authorized.connect_via(oauthblueprints[2]['blueprint']) def generic_logged_in(blueprint, token): global generic if not token: flash(_(u"Failed to log in with generic OAuth provider."), category="error") log.error("Failed to log in with generic OAuth2 provider") return False resp = blueprint.session.get(blueprint.base_url + oauthblueprints[2].get('oauth_userinfo_url')) if not resp.ok: flash(_(u"Failed to fetch user info from generic OAuth2 provider."), category="error") log.error("Failed to fetch user info from generic OAuth2 provider") return False username_mapper = oauthblueprints[2].get('username_mapper') or 'username' email_mapper = oauthblueprints[2].get('email_mapper') or 'email' generic_info = resp.json() generic_user_email = str(generic_info[email_mapper]) generic_user_username = str(generic_info[username_mapper]) user = ( ub.session.query(ub.User) .filter(and_(func.lower(ub.User.name) == generic_user_username, func.lower(ub.User.email) == generic_user_email)) ).first() if user is None: user = ub.User() user.name = generic_user_username user.email = generic_user_email user.role = constants.ROLE_USER ub.session.add(user) ub.session_commit() result = oauth_update_token(str(oauthblueprints[2].get('id')), token, user.id) query = ub.session.query(ub.OAuth).filter_by( provider=str(oauthblueprints[2].get('id')), provider_user_id=user.id, ) oauth_entry = query.first() oauth_entry.user = user ub.session_commit() return result # notify on OAuth provider error @oauth_error.connect_via(oauthblueprints[0]['blueprint']) def github_error(blueprint, error, error_description=None, error_uri=None): msg = ( u"OAuth error from {name}! " u"error={error} description={description} uri={uri}" ).format( name=blueprint.name, error=error, description=error_description, uri=error_uri, ) # ToDo: Translate flash(msg, category="error") @oauth_error.connect_via(oauthblueprints[1]['blueprint']) def google_error(blueprint, error, error_description=None, error_uri=None): msg = ( u"OAuth error from {name}! " u"error={error} description={description} uri={uri}" ).format( name=blueprint.name, error=error, description=error_description, uri=error_uri, ) # ToDo: Translate flash(msg, category="error") @oauth_error.connect_via(oauthblueprints[2]['blueprint']) def generic_error(blueprint, error, error_description=None, error_uri=None): msg = ( u"OAuth error from {name}! " u"error={error} description={description} uri={uri}" ).format( name=blueprint.name, error=error, description=error_description, uri=error_uri, ) # ToDo: Translate flash(msg, category="error") @oauth.route('/link/github') @oauth_required def github_login(): if not github.authorized: return redirect(url_for('github.login')) try: account_info = github.get('/user') if account_info.ok: account_info_json = account_info.json() return bind_oauth_or_register(oauthblueprints[0]['id'], account_info_json['id'], 'github.login', 'github') flash(_(u"GitHub Oauth error, please retry later."), category="error") log.error("GitHub Oauth error, please retry later") except (InvalidGrantError, TokenExpiredError) as e: flash(_(u"GitHub Oauth error: {}").format(e), category="error") log.error(e) return redirect(url_for('web.login')) @oauth.route('/unlink/github', methods=["GET"]) @login_required def github_login_unlink(): return unlink_oauth(oauthblueprints[0]['id']) @oauth.route('/link/google') @oauth_required def google_login(): if not google.authorized: return redirect(url_for("google.login")) try: resp = google.get("/oauth2/v2/userinfo") if resp.ok: account_info_json = resp.json() return bind_oauth_or_register(oauthblueprints[1]['id'], account_info_json['id'], 'google.login', 'google') flash(_(u"Google Oauth error, please retry later."), category="error") log.error("Google Oauth error, please retry later") except (InvalidGrantError, TokenExpiredError) as e: flash(_(u"Google Oauth error: {}").format(e), category="error") log.error(e) return redirect(url_for('web.login')) @oauth.route('/unlink/google', methods=["GET"]) @login_required def google_login_unlink(): return unlink_oauth(oauthblueprints[1]['id']) @oauth.route('/link/generic') @oauth_required def generic_login(): global generic if not generic.session.authorized: return redirect(url_for("generic.login")) try: resp = generic.session.get(generic.base_url + oauthblueprints[2].get('oauth_userinfo_url')) if resp.ok: account_info_json = resp.json() username_mapper = oauthblueprints[2].get('username_mapper') or 'username' email_mapper = oauthblueprints[2].get('email_mapper') or 'email' email = str(account_info_json[email_mapper]) username = str(account_info_json[username_mapper]) user = ( ub.session.query(ub.User) .filter(and_(func.lower(ub.User.name) == username, func.lower(ub.User.email) == email)) ).first() return bind_oauth_or_register(oauthblueprints[2].get('id'), user.id, 'generic.login', 'generic') flash(_(u"generic OAuth2 error, please retry later."), category="error") log.error("generic OAuth2 error, please retry later") except (InvalidGrantError, TokenExpiredError) as e: log.error(e) return redirect(url_for("generic.login")) @oauth.route('/unlink/generic', methods=["GET"]) @login_required def generic_login_unlink(): return unlink_oauth(oauthblueprints[2].get('id')) ```

Jafner commented

2022-07-12 17:16:50 -07:00

(Migrated from gitlab.jafner.net)

mentioned in issue #3

Sign in to join this conversation.

No Label

No Milestone

No project

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: Jafner/calibre-web#1

Container crashes on start with sqlite3.OperationalError: no such column: oauthProvider.oauth_userinfo_url #1

Container crashes on start with `sqlite3.OperationalError: no such column: oauthProvider.oauth_userinfo_url` #1