Sqlite Database 2019(1)Sqlite3 Installation and Docker phpsqliteadmin

Sqlite Database 2019(1)Sqlite3 Installation and Docker phpsqliteadmin

Install SQLite database
On MAC, it already there
> sqlite3 -version
3.24.0 2018-06-04 14:10:15

On Ubuntu, it is already there as well
> sqlite3 -version
3.22.0 2018-01-22 18:45:57

On CentOS 7, it is already there as well
> sqlite3 -version
3.7.17 2013-05-20

We can also download from here
https://www.sqlite.org/download.html
> wget https://www.sqlite.org/2019/sqlite-autoconf-3290000.tar.gz
> tar zxvf sqlite-autoconf-3290000.tar.gz

Compile and install
> cd sqlite-autoconf-3290000
> ./configure --prefix=/usr/local
> make
> sudo make install

Check version again
> sqlite3 -version
3.29.0 2019-07-10

Command line tool to open one sqlite database file
> sqlite3 ./SpiderKeeper.db

Check all tables
> .tables
sk_job_execution  sk_job_instance   sk_project        sk_spider 

Exit if needed
> sqlite> .quit

Check database schema
> .schema sk_spider
CREATE TABLE sk_spider (
id INTEGER NOT NULL,
date_created DATETIME,
date_modified DATETIME,
spider_name VARCHAR(100),
project_id INTEGER NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX ix_sk_spider_project_id ON sk_spider (project_id);

> .schema sk_project
CREATE TABLE sk_project (
id INTEGER NOT NULL,
date_created DATETIME,
date_modified DATETIME,
project_name VARCHAR(50),
PRIMARY KEY (id)
);

> .schema sk_job_instance
CREATE TABLE sk_job_instance (
id INTEGER NOT NULL,
date_created DATETIME,
date_modified DATETIME,
spider_name VARCHAR(100) NOT NULL,
project_id INTEGER NOT NULL,
tags TEXT,
spider_arguments TEXT,
priority INTEGER,
"desc" TEXT,
cron_minutes VARCHAR(20),
cron_hour VARCHAR(20),
cron_day_of_month VARCHAR(20),
cron_day_of_week VARCHAR(20),
cron_month VARCHAR(20),
enabled INTEGER,
run_type VARCHAR(20),
PRIMARY KEY (id)
);
CREATE INDEX ix_sk_job_instance_spider_name ON sk_job_instance (spider_name);
CREATE INDEX ix_sk_job_instance_project_id ON sk_job_instance (project_id);

> .schema sk_job_execution
CREATE TABLE sk_job_execution (
id INTEGER NOT NULL,
date_created DATETIME,
date_modified DATETIME,
project_id INTEGER NOT NULL,
service_job_execution_id VARCHAR(50) NOT NULL,
job_instance_id INTEGER NOT NULL,
create_time DATETIME,
start_time DATETIME,
end_time DATETIME,
running_status INTEGER,
running_on TEXT,
PRIMARY KEY (id)
);
CREATE INDEX ix_sk_job_execution_project_id ON sk_job_execution (project_id);
CREATE INDEX ix_sk_job_execution_service_job_execution_id ON sk_job_execution (service_job_execution_id);
CREATE INDEX ix_sk_job_execution_job_instance_id ON sk_job_execution (job_instance_id);

There is a webUI tool, haha. I should use command line, but UI is easy as well.
https://www.phpliteadmin.org/
Currently, stable version is 1.9.8 https://www.phpliteadmin.org/download/
Then I get the file named  phpLiteAdmin_v1-9-8.zip

Create directory, Unzip the file in that directory
> mkdir phpliteadmin-1.9.8
> unzip phpLiteAdmin_v1-9-8.zip

It seems need php and web server for that following this https://bitbucket.org/phpliteadmin/public/wiki/Installation
It may be better to directly use this docker
https://github.com/shadowcodex/phpliteadmin

> git clone https://github.com/shadowcodex/phpliteadmin

Use a text editor to change the configuration, I use sublime here, or you can directly use vi on the server
> subl phpliteadmin/


Make sure I have docker on my CentOS
> sudo yum install docker-ce
> sudo usermod -aG docker $(whoami)
> sudo systemctl enable docker.service
> sudo systemctl start docker.service

If there is permission issue, you may need to restart the machines.

If we can not find the docker-ce package there “ No package docker-ce available."
Solution:
https://docs.docker.com/v17.09/engine/installation/linux/docker-ce/centos/#install-using-the-repository

> sudo yum install -y yum-utils   device-mapper-persistent-data   lvm2
> sudo yum-config-manager     --add-repo     https://download.docker.com/linux/centos/docker-ce.repo

Then the install command will work
> sudo yum install docker-ce


Go to the directory of phpliteadmin
> chmod a+x go.sh

Start the service
> ./go.sh /home/carl/data

It is running fine now
> docker ps
CONTAINER ID        IMAGE                      COMMAND                  CREATED             STATUS              PORTS                                     NAMES
1af127d6da44        shadowcodex/phpliteadmin   "/bin/parent caddy -…"   3 seconds ago       Up 2 seconds        80/tcp, 443/tcp, 0.0.0.0:2015->2015/tcp   websql

Visit the webpage from this URL
http://centos-dev1:2015/phpliteadmin.php

Change the configuration, specially password here
> vi phpliteadmin.config.php

Restart the service
> ./go.sh /home/carl/data

The password is not working as designed. Open the debug=true, and I saw this in the logging
2019-09-04T22:55:22.608410410Z 04/Sep/2019:22:55:22 +0000 [ERROR 0 /phpliteadmin.php] PHP message: PHP Deprecated:  crypt(): Supplied salt is not valid for DES. Possible bug in provided salt format. in /srv/phpliteadmin.php on line 4194

Plan to build the ENV my own, Check my PHP
Content in info.php
<?php phpinfo();?>

http://localhost:8081/info.php

Unzip the latest file
> unzip phpLiteAdmin_v1-9-8-1.zip
Copy them to my html directory
> cp ~/install/phpliteadmin.php ./html/
> cp ~/install/phpliteadmin.config.sample.php ./html/phpliteadmin.config.php

The docker thing is just normal php7, put the phpsqliteadmin under the html directory, that is it.

Check my centos7-sqlitephpadmin

References:
https://www.sqlite.org/download.html
https://www.yiibai.com/sqlite/installation.html
https://bitbucket.org/phpliteadmin/public/wiki/Installation