====== SQLite ======

I like to use [[https://www.sqlite.org/|sqlite]] in most of my projects that require simple/small databases. It has a [[https://www.sqlite.org/c3ref/intro.html|C/C++ interface library]], very simple to use (because no server required), contained in a single file and even [[http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html|available on Android]]! So, here are my notes on using them... mainly for my own personal reference.

Main documentation can be found [[https://www.sqlite.org/docs.html|here]].

===== Basic Commands =====

Most SQL commands in SQLite are similar to other SQL found in major database software (e.g. [[https://mariadb.com/|MariaDB]], [[http://www.postgresql.org/|PostgreSQL]], [[http://www.mysql.com/|MySQL]]). So, if you have done SQL before, this should be very familiar.

To create a new table:<code sql>
CREATE TABLE IF NOT EXISTS tablename (col-name col-type [,col-name col-type]);
</code>Notice that ''IF NOT EXISTS'' is optional.

//to be continued...//

===== Modify rows with decimal points =====

Assuming we want to modify rows (with column //col// that contains decimal points) in table //tablename//:<code sql>
UPDATE tablename SET col=substr(col,1,length(col)-3) WHERE col LIKE '%.00';
</code>

===== Common DB Search =====

Selecting specific column (operations) within specific time range:<code sql>
SELECT col1,(col2*2.0),datetime(col3,'8 hours') FROM table1
  WHERE id=1 AND datetime(col3,'8 hours')<datetime('2016-10-15T23:59:59');
</code>

===== Multiple DB Files =====

One thing to note is that there is no database concept - a file IS a database. But we can attach another database for simultaneous access.

First open a DB file:<code sql>sqlite3 'file1.sqlite'</code>Within sqlite, this file will be known as the ''main'' database. There is another system database called ''temp'' (so you should not use this name as an alias for the other database).

So, to access another database:<code sql>ATTACH 'file2.sqlite' as next;</code> You can now access any table like: <code sql>SELECT * from next.tablename;</code>

To copy from/to the same table (exactly same schema) between both database:<code sql>INSERT INTO main.tablename SELECT * from next.tablename;</code>

Or to copy selective columns:<code sql>INSERT INTO main.tablename(column1, column2) SELECT (column3, column4) from next.tablename;</code>

====== MariaDB/MySQL ======

===== Initial Setup =====

start installation
<code shell># mysql_install_db --user=mysql</code>

if forgot to supply user, make sure permission for /var/lib/msql is given to user mysql
<code shell># chown -R mysql:mysql /var/lib/mysql</code>

start daemon
<code shell># sh /etc/rc.d/rc.mysqld start</code>

as the name implies, run
<code shell># /usr/bin/mysql_secure_installation</code>

===== Common Commands =====

create database
<code shell>$ mysql -u root -p</code>
<code sql>
mysql> create database app_db;
mysql> grant all privileges on app_db.* to 'user_app'@'localhost' identified by 'pass_app';
mysql> set password for 'user_app'@'%' = password('pass_app');
mysql> flush privileges;
</code> //**note** not sure if i need that set password line... will test that//

recover root password
<code shell>
$ /etc/rc.d/rc.mysqld stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root
</code>
<code sql>
mysql> use mysql;
mysql> update user set password=PASSWORD('<newpass>') where user='root';
mysql> flush privileges;
mysql> exit
</code>

show permission / grants
<code shell>$ mysql -u root -p -e "show grants for 'user_app'@'localhost';"</code>

delete database
<code shell>$ mysql -u root -p -e "drop database app_db;"</code>

dumping this here for now.... not sure why i have this in my note m(
<code>select db,user,select_priv from mysql.db where db='my1vmsdb' \g</code>