How to use alasql - 10 common examples

if(0===sql.trim().length){
	yargs.showHelp();
	process.exit(1);
}

for(var i=1;i

const TfFeature = new Terraformer.Primitive(feature)
  return filter.intersects(TfFeature)
}

sql.fn.ST_EnvelopeIntersects = function (feature = {}, filterGeom = {}) {
  if (!feature) return false
  if (!(feature.type || feature.coordinates)) feature = convertFromEsri(feature) // TODO: remove ? temporary esri geometry conversion
  if (!(feature.type && feature.coordinates && feature.coordinates.length > 0)) return false
  if (feature.type === 'Point') return sql.fn.ST_Contains(feature, filterGeom)
  const filter = new Terraformer.Primitive(filterGeom)
  const envelope = transformArray(new Terraformer.Primitive(feature).bbox())
  const TfFeature = new Terraformer.Polygon(envelope)
  return filter.intersects(TfFeature)
}

sql.fn.geohash = function (geometry = {}, precision) {
  if (!geometry || !geometry.type || !geometry.coordinates) return
  precision = precision || 8
  if (geometry.type !== 'Point') geometry = centroid(geometry).geometry
  const pnt = geometry.coordinates
  return geohash.encode(pnt[1], pnt[0], precision)
}

sql.fn.pick = function (properties, fields) {
  const parsedFields = fields.split(',')
  return _.pick(properties, parsedFields)
}

/**
 * Select a subset of properties and modify propterties to fit ESRI specs
 * @param {object} properties GeoJSON properties
 * @param {object} geometry GeoJSON geometry

module.exports = (http) => {
  const io = require ('socket.io')(http);
  const ConspectioNode = require('./conspectioNode.js');
  const alasql = require('alasql');
  // custom function that returns array length
  alasql.fn.arrlen = function(arr) { return arr.length; };

  //create nodeTracker table instead of nodeTracker[] due to compatiability with alasql CRUD operations
  alasql('CREATE TABLE nodeTracker');
  //max number of leechers per broadcast relayer
  const maxRelayers = 1;
  const maxBroadcasters = 3;


  io.on('connection', (socket) => {
  
    console.log('socket connected', socket.id);

    // NOTE: handle more than 1 broadcaster per eventId
    //listens for event tag from broadcaster
    socket.on('addBroadcaster', (eventId) => {
      // add a new broadcaster asssociated with that event id into nodeTracker table 

const sqlColumns = this._sqlHelper.generateSqlColumns( columnRow.cells, sqlTypes );
        const createCommand = this._sqlHelper.generateCreateWithTypes( table.internalName, sqlColumns );
        try {
            // console.log( 'TABLE CREATION COMMAND: ', createCommand );
            dbConnection.exec( createCommand ); // Creates the table if it does not exist
        } catch ( e ) {
            const msg = `Error creating the table "${table.name}": ${e.message}`;
            throw new RuntimeException( msg, table.location );
        }

        // Prepares a parameterized insert

        const insertCommand = this._sqlHelper.generateParameterizedInsert(
            table.internalName, columnRow.cells );
        // @ts-ignore
        let insert = alasql.compile( insertCommand );
        if ( ! isDefined( insert ) ) {
            const msg = `Error compiling the insert command at the table "${table.name}".`;
            throw new RuntimeException( msg, table.location );
        }

        // Inserts the values

        for ( let i = 1; i < rowCount; ++i ) { // starts at the second row
            const row = table.rows[ i ];
            try {
                // console.log( 'row', row );
                let params = this.normalizeValues( row.cells, valTypes );
                // console.log( 'params', params );
                insert( params );
            } catch ( e ) {
                const msg = `Error inserting values in the table "${table.name}": ${e.message}`;

const sqlColumns = this._sqlHelper.generateSqlColumns( row.cells, sqlTypes );
                        //console.log( sqlColumns );
                        const createCommand = this._sqlHelper.generateCreateWithTypes( table.name, sqlColumns );

                        try {
                            // Create the table if it does not exist
                            db.exec( createCommand );
                    
                            // Prepare a parameterized insert
                            const insertCommand = this._sqlHelper.generateParameterizedInsert(
                                table.name, row.cells );
                            //console.log( insertCommand );

                            // @ts-ignore
                            insert = alasql.compile( insertCommand );
                        } catch ( e ) {
                            return reject( e );
                        }                        

                    } else {
                        // Create the table if it does not exist
                        const createCommand = this._sqlHelper.generateCreate( table.name, row.cells );
                        //console.log( createCommand );
                        try {
                            db.exec( createCommand );
                        } catch ( e ) {
                            return reject( e );
                        }
                    }

                } else {

const valTypes = this.detectTableColumnTypes(table);
            const sqlTypes = valTypes.map(v => this._sqlHelper.convertToSQLType(v));
            const sqlColumns = this._sqlHelper.generateSqlColumns(columnRow.cells, sqlTypes);
            const createCommand = this._sqlHelper.generateCreateWithTypes(table.internalName, sqlColumns);
            try {
                // console.log( 'TABLE CREATION COMMAND: ', createCommand );
                dbConnection.exec(createCommand); // Creates the table if it does not exist
            }
            catch (e) {
                const msg = `Error creating the table "${table.name}": ${e.message}`;
                throw new error_1.RuntimeException(msg, table.location);
            }
            // Prepares a parameterized insert
            const insertCommand = this._sqlHelper.generateParameterizedInsert(table.internalName, columnRow.cells);
            // @ts-ignore
            let insert = alasql.compile(insertCommand);
            if (!util_1.isDefined(insert)) {
                const msg = `Error compiling the insert command at the table "${table.name}".`;
                throw new error_1.RuntimeException(msg, table.location);
            }
            // Inserts the values
            for (let i = 1; i < rowCount; ++i) { // starts at the second row
                const row = table.rows[i];
                try {
                    // console.log( 'row', row );
                    let params = this.normalizeValues(row.cells, valTypes);
                    // console.log( 'params', params );
                    insert(params);
                }
                catch (e) {
                    const msg = `Error inserting values in the table "${table.name}": ${e.message}`;
                    throw new error_1.RuntimeException(msg, table.location);

// Async select

var db = require('alasql');

db("CREATE TABLE test (language INT, hello STRING)");
db("INSERT INTO test VALUES (1,'Hello!')");
db("INSERT INTO test VALUES (2,'Aloha!')");
db("INSERT INTO test VALUES (3,'Bonjour!')");

db	.promise("SELECT * FROM test WHERE language > 1")
	.then(function(res){
		console.log(res);
	});

//
// AlaSQL node.js sample
//

var alasql = require('alasql');

var db = new alasql.Database();

db.exec("CREATE TABLE test (one INT, two INT)");
db.tables.test.data = [   // You can mix SQL and JavaScript
    {one:3,two:4},
    {one:5,two:6},
];

var res = db.exec("SELECT * FROM test ORDER BY two DESC");

console.log(res);

#!/usr/bin/env node
//
// Alacon - Command line interface for Alasql
// Version: 0.1
// Date: 07.04.2015
// (c) 2014-2015, Andrey Gershun
//

var alasql = require('alasql');
var fs = require('fs');


if(process.argv.length <= 2) {
	console.log('alacon - Alasql command-line utility (version '+alasql.version+') ');
	console.log();
	console.log('Usage:');
	console.log('  node alacon "sql-statement" [ param0 ]... - run SQL statement');
	console.log('  node alacon -f file.sql [ param0 ]...     - run SQL from file');
	console.log();
	console.log('Samples:');
	console.log('  node alacon \'select 2+2\'');	
	console.log('  node alacon \'select count(*) from txt()\'  2) {
	var sql = process.argv[2];
	var parami = 3;
	if(sql == '-f') {
		console.log(sql);
		sql = fs.readFile(sql).toString();

addSkipper() {
    const { name, displacement, country } = this.refs;
    if (!name.value) return;
    // console.log(dicplacement.value); // Kontrol amaçlı. Browser'dan F12 ile değerlere bakılabilir
    alasql('INSERT INTO Submarine VALUES ?',
      [{
        id: alasql.autoval('Submarine', 'id', true),
        name: name.value,
        displacement: displacement.value,
        country: country.value
      }]
    );
    this.getAll();
  }