SQL

SQL
	Chapter 9. Office

Structured Query Language is a standard to access databases. See https://www.w3schools.com/sql/default.asp

There are 2 major GPL implementations:

Mysql that is client server based and suitable for networks and multi users
SQLite that is targeted for embedded applications, easy to go and has all database in a single file. It is widely used, since android makes use of it.

SQLite

SQLite http://www.sqlite.org/is ready to be used and needs no administrative server/client configurations. It is ready to use (the 3 is important otherwise the incompatible version 2 is used):

sqlite3 my.db

then the sqlite> prompt pops up and commands can be entered to create the table adr:

CREATE TABLE adr(first VARCHAR(20), family VARCHAR(20));

INSERT INTO adr VALUES('Urs','Lindegger');

SELECT * FROM adr; the * stands for the column names, and means all columns, alternatively a list of column names separated by , can be applied

If some column got forgotten add it with

ALTER TABLE adr ADD COLUMN email VARCHAR(20);

SQLite is not case sensitive and follows human readable syntax, however to distinguish what is SQL and what are user terms upper and lowercase is commonly used to style the commands.

.exit, .quit .q or Ctrl + D exits and the file my.db is left behind containing the SQLite database.

Alternatively it can be worked without entering the interactive prompt by adding the command to the command line:

sqlite3 my.db ".dump" will dump the database to the screen. or to be more efficient, type all commands in a file and run .tables will show all tables in the database

Note

SQLite understands additional commands that have names starting with a dot and written in lowercase as .tables

sqlite3 my.db < mysqlcommands.sql

Many bindings to programming language exist as c and python (where it is even part of the standard library. pysqlite is an alternative).

There are also gui's as sqliteman, sqlitebrowser or just use gda-browser the database browser that might come with gnome (emerge libgda).

SQLite has triggers that can be configured to create events when something gets modified.

MySQL

Due to license issues MySQL got forked itno MariaDB.

It is a key component in LAMP web severs.

MySQL can be operated via console. On OpenRC first start the sql server:

/etc/init.d/mysql start or on systemd service mysql start

mysql -u guest -h localhost -p

Or as root (press enter to not give a password, note that you can do this also without being Linux root)

mysql -u root -h localhost -p

Since this looks scary give it a password:

/usr/bin/mysqladmin -u root -h localhost password 'new-password'

Note

Unfortunately those user names and passwords are an other set of user-names and passwords and need to be remembered. On some Linux distributions sudo mysql logs in as root

exit or quit to leave

Accessing the database

on Gentoo /etc/mysql/mariadb.d/50-distro_server.cnf or Raspberry PI OS /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address            = 127.0.0.1

bind-address            = 0.0.0.0

Database users

To see the known users

SELECT User,Host FROM mysql.user;

As the result shows, the users are linked to a Host (as localhost) accessing from an other host will fail.

CREATE USER '<username>'@'<ip_address>' IDENTIFIED BY '<password>';

<ip_address> can be:

a number as 192.168.1.8
a string as localhost
the wildchart character % to allow all addresses
or 192.168.1.% to allow access from local network

FLUSH PRIVILEGES;

mysql -u <username> -h <ip_address> -p

to see the privileges

SHOW GRANTS FOR '<username>'@'<ip_address>';

GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<ip_address>';

DROP USER '<username>'@'<ip_address>'

Give privileges to create databases

GRANT CREATE ON *.* TO '<username>'@'<ip_address>';

Exporting and Importing databases

Databases can be exported as <name>.sql or zipped as <name>.sql.zip looking into those files shows that it contains sql code. So importing means just run this code. Export and import can be done via phpmyadmin. For the import an empty database of the same name must be created.

On command line to export the db

mysqldump -u <username> -p <database_name> > <data-dump>.sql

For a backup there might be good to zip it and add a date info:

mysqldump --user=<user> --password=<password> --dump-date <db name> |gzip > <path>/MySQLDBBK$(date +%Y_%m_%d__%H_%M_%S$%M%S).sql.gz

to import log in as mysql -u <username> -p or as root mysql

create a new empty db CREATE DATABASE <database>;

exit the db

If zipped then gunzip <data-dump>.sql.gz to get <data-dump>.sql

an mysql -u <username> -p <database> < <data-dump>.sql or as root mysql <database> < <data-dump>.sql

Since this is a new database the users might not have access so grant the permissions

For backups on the same machine there is a faster way:

mysqlhotcopy

SQL Commands

Some commands

SHOW DATABASES;

CREATE DATABASE <my database>;

USE <my database>;

DROP DATABASE <my database>; to delete the database and loose everything

SHOW TABLES;

CREATE TABLE <name of table> ( <name of 1 column> VARCHAR(128), <name of 2 column> VARCHAR(128), <name of 3 column> VARCHAR(128));

DESCRIBE <name of table>; Shows the structure of the table

To get a web application that runs in a web browser: emerge phpmyadmin to have a gui. Read the post install instructions, how to create the config file:

mkdir /var/www/localhost/htdocs/phpmyadmin/config

chown apache:apache /var/www/localhost/htdocs/phpmyadmin/config

then go to http://localhost//phpmyadmin/scripts/setup.php

cp /var/www/localhost/htdocs/phpmyadmin/config/config.inc.php /var/www/localhost/htdocs/phpmyadmin/config.inc.php

rm -rf /var/www/localhost/htdocs/phpmyadmin/config

To work with it use: http://localhost/phpmyadmin/index.php

See https://wiki.gentoo.org/wiki/MySQL/Startup_Guide.

The config files are in /etc/mysql per default the data is in /var/lib/mysql.

Important

Not everything sql is doing is written immediately to the disk. A cache is used to boost performance and lower disk stress. Closing the connection to the database will flush the data to the disk. If a program crashes or does not exit in a clean way the data in the cache is lost. To avoid this, commands as FLUSH TABLES; can be used to write the data immediately to the disk.


Spell Checkers		Korganizer