Untitled - NFHN Reader

Introduction

AtomSQL is Simple Database Query Language for SQL Like systems.

Supported Features

ALL CRUD Operations are supported
Few Table metadata operations are supported
MySQL, Sqlite✳, Postgresql✳ are supported
Perl, PHP✳, Nodejs✳ are supported

Why ?

Current SQL is way too much to type for a UNIX/Linux user. So, came up with this simpler syntax to do most of the common tasks.

SQL Replacement ?

SQL itself is very complex and AtomSQL is not meant to be a replacement for it. Many Projects like Oracle, Postgresql, MySQL have invested lot of hours building a flexible SQL as per standards. Replacing all that effort in couple of weeks is not the idea.

TD;LR

AtomSQL libraries provide mechanism to convert AtomSQL queries to normal SQL queries.

AtomSQL Syntax

Below sections explain the syntax with few examples.

Few CLUES to Remember

= is an operator
+ is ADD operator
- is DROP operator

AtomSQL	MySQL
`*`	`SHOW TABLES`
`hellotable`	`DESCRIBE hellotable`
`= hellotable`	`SHOW CREATE TABLE hellotable`
`hellotable +tmp_col blob default null`	`ALTER TABLE hellotable ADD COLUMN tmp_col blob default null`
`hellotable -tmp_col`	`ALTER TABLE hellotable DROP COLUMN tmp_col`

Table/Create

Few CLUES to Remember

{} are borrowed from C/C++ Structure Definition. So, you can remember that way.

AtomSQL	MySQL
`hellotable { a int primary key auto_increment, b int, c varchar(255) DEFAULT NULL }`	`CREATE TABLE hellotable ( a int primary key auto_increment, b int, c varchar(255) DEFAULT NULL )`
`hellotable_innodb { a int primary key auto_increment, b int, c varchar(255) DEFAULT NULL } engine=innodb charset=utf8mb4`	`CREATE TABLE hellotable_innodb ( a int primary key auto_increment, b int, c varchar(255) DEFAULT NULL ) engine=innodb charset=utf8mb4`

Table/Select

Few CLUES to Remember

<> - COLUMN selection expression
() - WHERE expression
[] - LIMIT, OFFSET expression

AtomSQL	MySQL
`hellotable <a,b>`	`SELECT a,b FROM hellotable`
`hellotable <a,b> (a > b)`	`SELECT a,b FROM hellotable WHERE (a > b)`
`hellotable <a,b> (a > b) [100]`	`SELECT a,b FROM hellotable WHERE (a > b) LIMIT 100`
`hellotable <a,b> (a > b) [100, 200]`	`SELECT a,b FROM hellotable WHERE (a > b) LIMIT 100 OFFSET 200`

Table/Update

Few CLUES to Remember

// is like regular-expressions for substitution. Read perl, awk for some intro
() - WHERE expression
[] - LIMIT, OFFSET expression

AtomSQL	MySQL
`hellotable /a = 1, b = 2/`	`UPDATE hellotable SET a = 1, b = 2`
`hellotable /a = 1, b = 2/ [50]`	`UPDATE hellotable SET a = 1, b = 2 LIMIT 50`
`hellotable /a = 1, b = 2/ (a > 1)`	`UPDATE hellotable SET a = 1, b = 2 WHERE (a > 1)`
`hellotable /a = 1, b = 2/ (a > 1) [25]`	`UPDATE hellotable SET a = 1, b = 2 WHERE (a > 1) LIMIT 25`

Table/Insert

Few CLUES to Remember

= - Copy Table Structure
> - UNIX Redirection operator. Rows are REPLACEd
>> - UNIX Append operator. Rows are INSERTEd
() - WHERE expression
() - TUPLE representation
[()]- ARRAY of TUPLE representation

AtomSQL	MySQL
`new_hellotable = hellotable`	`CREATE TABLE new_hellotable LIKE hellotable`
`hellotable > new_hellotable`	`REPLACE INTO new_hellotable SELECT * FROM hellotable`
`hellotable <a,b> > new_hellotable <a,b>`	`REPLACE INTO new_hellotable (a,b) SELECT (a,b) FROM hellotable`
`hellotable <a,b> >> new_hellotable <a,b>`	`INSERT INTO new_hellotable (a,b) SELECT (a,b) FROM hellotable`
`hellotable <a,b> (a > 9) > new_hellotable <a,b>`	`REPLACE INTO new_hellotable (a,b) SELECT (a,b) FROM hellotable WHERE (a > 9)`
`hellotable <a,b> (a > 0) >> new_hellotable <a,b>`	`INSERT INTO new_hellotable (a,b) SELECT (a,b) FROM hellotable WHERE (a > 0)`
`hellotable = [ (1, 1, 'a') ]`	`INSERT INTO hellotable VALUES (1, 1, 'a')`
`hellotable = [ (2, 2, 'b'), (22, 22, 'b') ]`	`INSERT INTO hellotable VALUES (2, 2, 'b'), (22, 22, 'b')`
`hellotable = [ (3, 3, 'c'), (33, 33, 'c'), (333, 333, 'c') ]`	`INSERT INTO hellotable VALUES (3, 3, 'c'), (33, 33, 'c'), (333, 333, 'c')`
`hellotable <a,c> = [ (4, 'd') ]`	`INSERT INTO hellotable (a,c) VALUES (4, 'd')`
`hellotable <a,c> = [ (5, 'e'), (55, 'e') ]`	`INSERT INTO hellotable (a,c) VALUES (5, 'e'), (55, 'e')`
`hellotable <a,c> = [ (6, 'f'), (66, 'f'), (66, 'c') ]`	`INSERT INTO hellotable (a,c) VALUES (6, 'f'), (66, 'f'), (66, 'c')`

Table/Delete

Few CLUES to Remember

! - Bang means, somethings is going away !
!! - Double Bang, somethings are really going away.
[] - LIMIT, OFFSET expression
() - WHERE expression

AtomSQL	MySQL
`! hellotable`	`TRUNCATE TABLE hellotable`
`! hellotable [10]`	`DELETE FROM hellotable LIMIT 10`
`! hellotable (a > 100 and c != 'C')`	`DELETE FROM hellotable WHERE (a > 100 and c != 'C')`
`! hellotable (a > 100 and c != 'C') [10]`	`DELETE FROM hellotable WHERE (a > 100 and c != 'C') LIMIT 10`
`!! hellotable`	`DROP TABLE hellotable`

Table/Backup

Few CLUES to Remember

<> - COLUMN selection expression
() - WHERE expression. Ignored by MySQL
[] - LIMIT, OFFSET expression
> - UNIX Redirection operator. Writes data to File.

AtomSQL	MySQL
`hellotable <*> > '/tmp/backup-hellotable.csv'`	`SELECT * INTO OUTFILE '/tmp/backup-hellotable.csv' FROM hellotable`
`hellotable <a,b> > '/tmp/backup-hellotable.csv'`	`SELECT a,b INTO OUTFILE '/tmp/backup-hellotable.csv' FROM hellotable`
`hellotable <a,b> [10] > '/tmp/backup-hellotable.csv'`	`SELECT a,b INTO OUTFILE '/tmp/backup-hellotable.csv' FROM hellotable LIMIT 10`
`hellotable <a,b> [10, 20] > '/tmp/backup-hellotable.csv'`	`SELECT a,b INTO OUTFILE '/tmp/backup-hellotable.csv' FROM hellotable LIMIT 10 OFFSET 20`
`hellotable <a,b> (a = 100 and a != 20) [10, 20] > '/tmp/backup-hellotable.csv'`	`SELECT a,b INTO OUTFILE '/tmp/backup-hellotable.csv' FROM hellotable LIMIT 10 OFFSET 20`

Table/Restore

Few CLUES to Remember

< - UNIX like indirection symbol
<< - Custom redirection symbol
& - Specifies to IGNORE inserts on errors

AtomSQL	MySQL
`hellotable < '/tmp/backup-hellotable.csv'`	`LOAD DATA LOCAL INFILE '/tmp/backup-hellotable.csv' REPLACE INTO TABLE hellotable`
`hellotable << '/tmp/backup-hellotable.csv'`	`LOAD DATA LOCAL INFILE '/tmp/backup-hellotable.csv' INTO TABLE hellotable`
`hellotable &< '/tmp/backup-hellotable.csv'`	`LOAD DATA LOCAL INFILE '/tmp/backup-hellotable.csv' REPLACE IGNORE INTO TABLE hellotable`
`hellotable &<< '/tmp/backup-hellotable.csv'`	`LOAD DATA LOCAL INFILE '/tmp/backup-hellotable.csv' IGNORE INTO TABLE hellotable`

LANGUAGES / PERL

Download

git clone https://github.com/nareshv/atomsql/

Create a simple Perl Script


      #!/usr/bin/env perl
      
      use AtomSQL;
      use strict;
      use warnings;
      
      # set the driver
      $ENV{ATOMSQL_DRIVER} = 'mysql';
      
      # test the api
      my $asql = "DUAL <now()>";
      my $sql  = atomsql_query($asql);
      
      # print the results
      print "ORIGINAL : $asql\n";
      print "SQL      : $sql\n";

Prints the following output


      ORIGINAL : DUAL <now()>
      SQL      : SELECT now() FROM DUAL

LANGUAGES / PHP

Download

git clone https://github.com/nareshv/atomsql/

Create a simple PHP Script


      <?php
      
      // set the driver
      $_ENV{ATOMSQL_DRIVER} = 'mysql';
      
      // test the api
      my $asql = "DUAL <now()>";
      my $sql  = atomsql_query($asql);
      
      // print the results
      echo "ORIGINAL : $asql\n";
      echo "SQL      : $sql\n";

Prints the following output


      ORIGINAL : DUAL <now()>
      SQL      : SELECT now() FROM DUAL

Version

0.1 - Initial version

nareshv@

License

MIT

Contributing

Please submit a PR.

_{_{_{_{_{_{_~}}}}}_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_{_~}}}}}}}}}}}}}}}}}}}}}}_{_~}}