AtomSQL

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.

Table/Metadata

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

  1. Download
git clone https://github.com/nareshv/atomsql/
  1. 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

  1. Download
git clone https://github.com/nareshv/atomsql/
  1. 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

Author

nareshv@

License

MIT

Contributing

Please submit a PR.

~~~