AtomSQL - Simple Database Query Language for RDBMS & NoSQL Systems

AtomSQL came from the frustration of working too much with RDBMS systems and typing too much to get the necessary data from the Databases.

This post is an introduction to AtomSQL

Lets go through the following examples to see how we use SQL queries normally and in AtomSQL Format.

1. Create a new table

BEFORE:

CREATE TABLE myTable (id INTEGER PRIMARYKEY, name VARCHAR(255) ENGINE=INNODB

AFTER:

myTable { id INTEGER PRIMARY KEY, name VARCHAR(255) } ENGINE=INNODB

So, what has changed ???

We just got rid of the keywords and are letting the {} do the necessary JOB.

TIP: Remember C/C++ Structures ?

2. Inserting new records to the table

Before:

INSERT INTO myTable VALUES (1, 'Tom'), (2, 'Jerry')

After:

myTable = [ (1, 'Tom'), (2, 'Jerry') ]

Again, We use () for Tuples and [] for Array representation to get the job done. Keywords have gone away !
TIP: Remember JSON, Perl Arrays ?

3. Updating records

Before

UPDATE myTable SET name = 'Jerry Mouse' Where name = 'Jerry'

After

myTable /name = 'Jerry Mouse'/ (name = 'Jerry')

Again, we are using // for replacing data and () are used for condition to be used in WHERE Clause
TIP: Remember perl, sed, vim for data replacement ?

4. Deleting records

Before

DELETE FROM myTable Where name = 'Jerry'

After

! myTable (name = 'Jerry')

! Bang is the operator for destroying stuff. () is used for WHERE Clause

5. Seleting Records

Before

SELECT * from myTable where name != 'Jerry'

After

myTable <*> (name != 'Jerry')

<> is used for Column selection and () is used for WHERE clause

More Features/Limitations/Installation can be read from http://nareshv.github.io/atomsql/

Read More

Install TokuDB Mysql Server along with Existing mysql-server on Centos/Ubuntu Server

Tokudb has fractal tree engine which is mentioned to perform bettern than innodb in certain instances. In some scenarios you might want to run mysql-server which is patched with tokudb engine along with your current mysql server (for performance testing, evaluation etc).

A straightforward installation will mess up /var/lib/mysql, so follow these instructions to make sure that the server installation doesn't mess up existing installation.

1. Download and unzip the tokudb to /usr/local/

nareshv@fallenangel:~/Downloads$ sudo mv mysql-5.5.30-tokudb-7.0.1-linux-x86_64 /usr/local/
nareshv@fallenangel:/usr/local$ sudo ln -s mysql-5.5.30-tokudb-7.0.1-linux-x86_64 mysql
nareshv@fallenangel:/usr/local$ cd mysql
nareshv@fallenangel:/usr/local/mysql$ ls -l
total 80
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 bin
-rw-r--r--  1 nareshv nareshv 17987 Apr 19 03:11 COPYING
drwxrwxr-x  4 nareshv nareshv  4096 Apr 19 04:57 data
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 docs
drwxrwxr-x  3 nareshv nareshv  4096 Apr 19 04:57 include
-rw-r--r--  1 nareshv nareshv  7469 Apr 19 03:11 INSTALL-BINARY
drwxrwxr-x  3 nareshv nareshv  4096 Apr 19 04:57 lib
drwxrwxr-x  4 nareshv nareshv  4096 Apr 19 04:57 man
drwxrwxr-x 10 nareshv nareshv  4096 Apr 19 04:57 mysql-test
-rw-r--r--  1 nareshv nareshv  2552 Apr 19 03:11 README
-rw-r--r--  1 nareshv nareshv  3659 Apr 19 04:52 README-TOKUDB
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 scripts
drwxrwxr-x 27 nareshv nareshv  4096 Apr 19 04:57 share
drwxrwxr-x  4 nareshv nareshv  4096 Apr 19 04:57 sql-bench
drwxrwxr-x  2 nareshv nareshv  4096 Apr 19 04:57 support-files

2. Install the mysqlserver to /usr/local/mysql (not /var/lib/mysql)

nareshv@fallenangel:/usr/local/mysql$ sudo -u mysql ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h fallenangel password 'new-password'

Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

3. Create a new startup script to launch mysqld on port 3307

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$ sudo vi run.sh
#!/bin/bash

/usr/bin/env MYSQL_HOME=/usr/local/mysql ./bin/mysqld --user=mysql \
    --pid-file=/usr/local/mysql/mysqld.pid \
    --socket=/usr/local/mysql/mysqld.sock \
    --port=3307 \
    --basedir=/usr/local/mysql \
    --datadir=/usr/local/mysql/data \
    --lc-messages-dir=/usr/local/mysql/share \
    --tmpdir=/tmp \
    --skip-external-locking \
    --bind-address=127.0.0.1 \

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$

4. Run the mysql server in another terminal as root user

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$ sudo ./run.sh

5. Connect and check if mysql is available on 3307 / 127.0.0.1

nareshv@fallenangel:/usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64$ mysql -u root -h 127.0.0.1 -P 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30-tokudb-7.0.1 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> quit
Bye

Confirm that variables point to /usr/share/mysql only
mysql> show variables like '%dir%';
+-----------------------------------------+-------------------------------------------------------------------+
| Variable_name                           | Value                                                             |
+-----------------------------------------+-------------------------------------------------------------------+
| basedir                                 | /usr/local/mysql                                                  |
| binlog_direct_non_transactional_updates | OFF                                                               |
| character_sets_dir                      | /usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64/share/charsets/ |
| datadir                                 | /usr/local/mysql/data/                                            |
| innodb_data_home_dir                    | /usr/local/mysql/data                                             |
| innodb_log_group_home_dir               | /usr/local/mysql/data                                             |
| innodb_max_dirty_pages_pct              | 75                                                                |
| lc_messages_dir                         | /usr/local/mysql/share/                                           |
| plugin_dir                              | /usr/local/mysql-5.5.30-tokudb-7.0.1-linux-x86_64/lib/plugin/     |
| slave_load_tmpdir                       | /tmp                                                              |
| tmpdir                                  | /tmp                                                              |
| tokudb_data_dir                         |                                                                   |
| tokudb_directio                         | OFF                                                               |
| tokudb_log_dir                          |                                                                   |
| tokudb_tmp_dir                          |                                                                   |
+-----------------------------------------+-------------------------------------------------------------------+
15 rows in set (0.00 sec)

Read More