SQLite の変更点

• 追加された行はこの色です。
• 削除された行はこの色です。
• SQLite へ行く。
• SQLite の差分を削除

#author("2024-12-13T08:05:34+00:00","default:iseki","iseki")
#author("2024-12-13T08:27:13+00:00","default:iseki","iseki")
** SQLite [#q1ed9503]
- http://www.sqlite.org/


*** Install [#e3a9970d]
 zcat sqlite-3.6.21.tar.gz |tar xfv -
 mkdir sqlite_build
 cd sqlite_build
 ../sqlite-3.6.21/configure
 make
 make install


*** 操作 [#bcacf2b8]
- sqlite3 ～.db

- .database
- .tables
- .quit
- .help

**** example
<pre>
sqlite3 /var/lib/jupyterhub/jupyterhub.sqlite
sqlite> .tables

alembic_version   oauth_clients     service_role_map  spawners
api_tokens        oauth_codes       services          user_group_map
group_role_map    roles             share_codes       user_role_map
groups            servers           shares            users

sqlite> .schema users

CREATE TABLE users (
        id INTEGER NOT NULL,
        name VARCHAR(255),
        admin BOOLEAN,
        created DATETIME,
        last_activity DATETIME,
        cookie_id VARCHAR(255) NOT NULL,
        state TEXT,
        encrypted_auth_state BLOB,
        CONSTRAINT pk_users PRIMARY KEY (id),
        CONSTRAINT uq_users_name UNIQUE (name),
        CONSTRAINT uq_users_cookie_id UNIQUE (cookie_id)
);

sqlite> .mode line
sqlite> select * from users;
.....
.....
.....

sqlite> select * from  spawners;

             id = 1
        user_id = 2
      server_id = 1
          state = {"object_id": "8ba99eacf5221c9e2336f2d63036ceffe16735a359e4f381a64771caf6ab69d6", "object_name": "jupyterhub-182-114-el.mml.tuis.ac.jp-8qfypVfb-iseki"}
           name =
        started = 2024-12-13 07:45:44.824935
  last_activity = 2024-12-13 16:45:50.000000
   user_options =
oauth_client_id = jupyterhub-user-iseki

sqlite>
</pre>

*** Troubleshooting [#k0d69195]
- Monoなどを使用していて，「sqlite3_column_origin_name関数がない」というエラーが出た場合には，コンパイルをやり直す（make にオプションをつける）
 make OPTS=-DSQLITE_ENABLE_COLUMN_METADATA=1

**** Memo
sqlite> select * from alembic_version;
version_num = 4621fec11365


[root@castor5 jupyterhub]:1033# docker ps -a
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
CONTAINER ID  IMAGE                                                      COMMAND               CREATED         STATUS                                                    PORTS                      NAMES
d57342bbd8f0  localhost/jupyterhub-ltictr/singleuser:20241210            start-notebook.py...  2 minutes ago   Up 2 min                                 utes (healthy)   127.0.0.1:33555->8888/tcp  jupyterhub-182-114-el.mml.tuis.ac.jp-irLykETc-iseki
426b61ca2ec6  localhost/jupyterhub-ltictr/datascience-notebook:20241210  start-notebook.py...  10 seconds ago  Up 10 se                                 conds (healthy)  127.0.0.1:38117->8888/tcp  jupyterhub-182-35-el.mml.tuis.ac.jp-StCQb0l9-iseki




[root@castor5 jupyterhub]:1036# docker ps
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
CONTAINER ID  IMAGE                                            COMMAND               CREATED        STATUS                  PORTS                      NAMES
d57342bbd8f0  localhost/jupyterhub-ltictr/singleuser:20241210  start-notebook.py...  4 minutes ago  Up 4 minutes (healthy)  127.0.0.1:33555->8888/tcp  jupyterhub-182-114-el.mml.tuis.ac.jp-irLykETc-iseki
3cf6728b4c7f  localhost/jupyterhub-ltictr/singleuser:20241210  start-notebook.py...  3 seconds ago  Up 4 seconds (healthy)  127.0.0.1:33431->8888/tcp  jupyterhub-182-114-el.mml.tuis.ac.jp-njOatmPd-iseki





sqlite> select * from alembic_version;
version_num = 4621fec11365
sqlite> select * from oauth_clients;
            id = 1
    identifier = jupyterhub
   description = JupyterHub
        secret =
  redirect_uri =
allowed_scopes = []


sqlite> select * from oauth_clients;
            id = 1
    identifier = jupyterhub
   description = JupyterHub
        secret =
  redirect_uri =
allowed_scopes = []
sqlite> select * from service_role_map;
     service_id = 1
        role_id = 2
managed_by_auth = 0

     service_id = 2
        role_id = 5
managed_by_auth = 0


sqlite> select * from spawners;
             id = 1
        user_id = 2
      server_id =
          state =
           name =
        started =
  last_activity =
   user_options =
oauth_client_id =