No option for built-in functions on Insert

[SimeonC]

The following query gives two examples where I'm having trouble with automatic quotes and I want to turn it off for one specifically.

squel.insert()
    .into "tasks"
        .set "orderid", "((#{squel.select()
            .field "COUNT(*)", "count"
            .from "tasks"
            .where "ticketid = ?", ticketId}) + 1)"
        .set "createdat", "GETDATE()"

Results in: (Note the quoted as string values)

INSERT INTO tasks (orderid, createdat)
VALUES ('((SELECT COUNT(*) AS count FROM tasks WHERE (ticketid = 4198)) + 1)', 'GETDATE()')

I found #46 but the docs state that his workaround is depreciated, is there any way I can tell squel NOT to quote a particular string in a set?

[SimeonC]

FYI I have a workaround as follows (Plus MSSQL output clause as I'm too lazy to edit it out):

# (INSERT INTO) ... field ... value
class InsertFieldValueBlock extends squel.cls.InsertFieldValueBlock
    constructor: (options) ->
        super options
        @outputs = []
        @sqlFields = []
        @sqlValues = []

    # add fields to the output clause
    output: (fields) -> @outputs.push "INSERTED.#{f}" for f in fields

    # Update the given field with the given value.
    # This will override any previously set value for the given field.
    setSQL: (field, value) ->
        throw new Error "Cannot call set or setSQL or setFields on multiple rows of fields."  if @sqlValues.length > 1 or @values.length > 1

        # Explicity overwrite existing fields
        index = @sqlFields.indexOf(@_sanitizeField(field))
        if index isnt -1
            @sqlValues[0][index] = value
        else
            @sqlFields.push @_sanitizeField(field)
            index = @sqlFields.length - 1

            # The first value added needs to create the array of values for the row
            if Array.isArray(@sqlValues[0])
                @sqlValues[0][index] = value
            else
                @sqlValues.push [value]
        @

    buildStr: (queryBuilder) ->
        if 0 >= @fields.length and 0 >= @sqlFields.length  then throw new Error "set() needs to be called"

        vals = []
        for i in [0...@values.length]
            for j in [0...@values[i].length]
                formattedValue = @_formatValue(@values[i][j])
                if 'string' is typeof vals[i]
                    vals[i] += ', ' + formattedValue          
                else 
                    vals[i] = '' + formattedValue
        for i in [0...@sqlValues.length]
            for j in [0...@sqlValues[i].length]
                if 'string' is typeof vals[i]
                    vals[i] += ', ' + @sqlValues[i][j]          
                else 
                    vals[i] = '' + @sqlValues[i][j]

        "(#{@fields.concat(@sqlFields).join(', ')}) #{if @outputs.length isnt 0 then ("OUTPUT #{@outputs.join ', '} ") else ''}VALUES (#{vals.join('), (')})"

origInsert = squel.insert
squel.insert = -> origInsert options, [
    new squel.cls.StringBlock(options, 'INSERT'),
    new squel.cls.IntoTableBlock(options),
    new InsertFieldValueBlock(options)
]

Means that this:

squel.insert()
    .into "tasks"
        .output ["id","orderid"]
        .setSQL "orderid", "((#{squel.select()
            .field "COUNT(*)", "count"
            .from "tasks"
            .where "ticketid = ?", 24}) + 1)"
        .setSQL "createdat", "GETDATE()"

Outputs this (Line breaks for readability):

INSERT INTO tasks (orderid, createdat)
OUTPUT INSERTED.id, INSERTED.orderid
VALUES (((SELECT COUNT(*) AS count FROM tasks WHERE (ticketid = 24)) + 1), GETDATE())

[hiddentao]

It seems that it might be nice to be able to tell Squel not to quote a particular string. I'll look into adding something of this sort.

[hiddentao]

Also see #55 for alternative viewpoint - we could just have Squel recognize its own query builders.

[SimeonC]

Yea, #55 would definitely solve my first case where I'm using a sub-query. How would it solve my second use case of sql function calls like GETDATE()?

[hiddentao]

Ok, field values can now be query builder instances (will be published in 3.5 release).

As for doing this like GETDATE(), since that's not a built-in query type supported by squel I suggest registering a custom value handler, like so:

var SqlValue = function(str) {this._str = str;}

squel.registerValueHandler(SqlValue, function(sqlValue) {
  return sqlValue.str;
});

 squel.insert()
        .into("students")
        .set("field", new SqlValue('GETDATE()'))
;
/*  INSERT INTO students (field) VALUES (GETDATE()) */

Please give that a shot and let me know.

[SimeonC]

Doesn't seem to work it still quotes the value. Did I do something wrong?

[hiddentao]

No, you did it right. Let me see what solution I can conjure up.

[hiddentao]

Ability to disable quotes on a per-set() basis committed. Will be in 3.5.