Consider SQL-ish syntax

[back2dos]

For more SQL-like syntax (for those who prefer it):

• db.SELECT(_).FROM(User).WHERE(id == 42)
• db.INSERT_INTO(User, [name, age]).VALUES(["Jack", 23], ["Joe", 35])
• db.UPDATE(User).SET(age = age + 1).WHERE(birthDay == Date.now())

In particular the select syntax seems a little backward to me (saying what you want before saying where you want it from) and I'm generally not sure it's worth the trouble, but it's worth investigating.

[kevinresol]

This may serve as a good starting reference: http://knexjs.org/

[kevinresol]

I quickly drafted some proposed syntax as follow. I am not sure if they are possible at all. Because I don't know if we can make the compiler properly type this:

saying what you want before saying where you want it from

// init
var sql = createDriver(...);

// create db
sql.createDatabase(database_reference)

// drop db
sql.dropDatabase(database_reference)

// create table
sql.createTable(table_reference);

// drop
sql.dropTable(table_reference);

// basic insert
sql.insert([{col_name: 1}])
    .into(table_reference);

// basic select
sql.select([col_name])
    .from(table_reference)
    .where(col_name > 1);

// functions / aggregations
import tink.sql.expr.Functions.*;
sql.select([count(/* no argument means `*` here */), avg(col_name)])
    .from(table_reference)
    .where(col_name > 1)
    .groupBy(col_name)
    .orderBy([col_a.asc(),col_b.desc()]);

// sub query
var sub = sql.select([id])
    .from(table_reference)
    .where(col_name > 1);
sql.select([col_name])
    .from(table_reference)
    .where(col_name in sub);

// multiple source
sql.select([table1.col_name, table2.col_name])
    .from([table1, table2])
    .where(col_name in sub);

// alias
sql.select([table1.col_name, table3.col_name.as('col3')])
    .from([table1, table2.as('table3')])
    .where(col_name in sub);

// sub query as source
var sub = sql.select([id])
    .from(table_reference)
    .where(col_name > 1);
sql.select([table1.col_name, sub.id])
    .from([table1, sub])
    .where(col_name in sub);

// join
sql.select([table1.col_name, table2.col_name])
    .from(table_reference.as('table1'))
    .leftJoin(tabel_reference.as('table2'))
    .on(table1.col == table2.col);

// update
sql.update(table_reference)
    .set([{col_name: 1}])
    .where(col_name > 1);

// delete
sql.delete()
    .from(table_reference)
    .where(col_name > 1);

// transaction
sql.transaction(function(trx) {
    return trx.insert([...])
        .into(table_reference)
        .run()
        .map(switch o {
            case Success(_): trx.commit();
            case Failure(_): trx.rollback();
        });
});

// mutli query && assigning to variables
sql.multi([
    sql.select(col_a.assignTo('variable_name'))
        .from(table)
        .where(...),
    sql.delete()
        .from(table)
        .where(col > variable('variable_name')),
    sql.update(table)
        .set([...]),
]);

// raw
sql.raw('SELECT * FROM table')
    .run(); // would return Promise<Dynamic>, can't guarantee the return type

[back2dos]

Two questions:

1. It would be possible to do it that way around (select before from). What are the benefits? If we do it the other way round (as it is now), we can have auto completion in the select clause.
2. What is the advantage of multi queries vs. running the queries separately?

[kevinresol]

1. At first I just thought it is closer to the SQL syntax. But I agree that reversing could be better. from(table).join(other).on(...).select(...).where(...) into(table).insert(...)
2. I think this is mostly for saving the roundtrip time and bandwidth.

Basically I want tink_sql to support all the queries in this page: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

And there is this:

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;

While transaction may do the same work. I am no SQL expert and I don't know how to properly translate it into transaction operations. It would be good if tink_sql allows me to directly "port" established SQL snippets.

[back2dos]

Well, the UNLOCK TABLES looks a bit frightening to be honest, as it seems that it could inadvertently unlock other tables, but then again my SQL knowledge is equally limited :D

Anyway, I think the greater question here is how portable we want things to be across multiple SQL dialects. If you take SQLite for example, it has neither variables nor table locking (you can of course lock the whole DB). Maybe support something like parameterized queries or stored procedures would be a good way to have dialect-specific SQL code? Both would make porting a matter of copy and paste.

Transactions are indeed a bit trickier than locking tables, but if you ran the statements in a transaction with sufficient isolation (i.e. read committed), it would work correctly. You're right though that it'd perform slower due to the intermittent roundtrips. On the plus side, it'd be more portable.

[kevinresol]

I think the UNLOCK TABLES statement only unlock those locked by the current session.

About the portability, could that be solved by separating each dialect into separate classes?

mysql.lockTable(); // works
sqlite.lockTable(); // no such method

Anyway I think we can start from the common methods first.