Support for SQL Prepared Statement

[Fred Eisele]

https://en.wikipedia.org/wiki/Prepared_statement

Is there a mechanism where AQL could generate prepared SQL statements?

I do not see a fundamental problem as the values belong to the typeside and not the schema.

query Qs_02 = simple : S  {
    from ce:cot_event
    where ce.servertime = "201705071635"
    attributes
        cot_type -> ce.cot_type
        how -> ce.how
    }

Would become something like...

query Qs_02 = simple : S  {
    from ce:cot_event
    where ce.servertime = ?
    attributes
        cot_type -> ce.cot_type
        how -> ce.how
    }

...with the value "201705071635" replaced by a '?'.

[Ryan Wisnesky]

I think we'd have to create a new 'kind', say 'parameterized query', whose inhabitants consists of a pair of a typing context and a query.  Then add an operation 'subst' that yields a query from a parameterized query and a choice of parameters, as well as a way to inject a query as a parameterized query.  Finally, we'd add special support to emit the SQL you want.

--
You received this message because you are subscribed to the Google Groups "categoricaldata" group.
To unsubscribe from this group and stop receiving emails from it, send an email to categoricaldata+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[David Spivak]

You could almost do this using imports (applied to the frozen instance of a query).

That is, the prepared query is:

query Qs_prepared = simple : S  {
    from ce:cot_event, myservertime:date
    where ce.servertime = myservertime
    attributes

        cot_type -> ce.cot_type
        how -> ce.how
    }

Then you could activate it by filling in myservertime

query Qs_active = simple : S  {

    import Qs_prepared    

    where myservertime = "201705071635"

    }

Is that useful?

PS. How did you get those neato boxes?

[Fred Eisele]

Yes that would work.

I presume that query composition would work with Qs_prepared ?

On Tuesday, January 16, 2018 at 3:11:57 PM UTC-6, David Spivak wrote:

You could almost do this using imports (applied to the frozen instance of a query).

That is, the prepared query is:

query Qs_prepared = simple : S  {
    from ce:cot_event, myservertime:date
    where ce.servertime = myservertime
    attributes
        cot_type -> ce.cot_type
        how -> ce.how
    }

Then you could activate it by filling in myservertime

query Qs_active = simple : S  {
    import Qs_prepared    

    where myservertime = "201705071635"
    }

Is that useful?

PS. How did you get those neato boxes?

When posting replies in google/groups there is a "highlight code syntax" "{}" option.

 

On Tue, Jan 16, 2018 at 2:36 PM, Ryan Wisnesky <wisn...@gmail.com> wrote:

I think we'd have to create a new 'kind', say 'parameterized query', whose inhabitants consists of a pair of a typing context and a query.  Then add an operation 'subst' that yields a query from a parameterized query and a choice of parameters, as well as a way to inject a query as a parameterized query.  Finally, we'd add special support to emit the SQL you want.

On Jan 16, 2018 2:28 PM, "Fred Eisele" <fredric...@gmail.com> wrote:

https://en.wikipedia.org/wiki/Prepared_statement

Is there a mechanism where AQL could generate prepared SQL statements?

I do not see a fundamental problem as the values belong to the typeside and not the schema.

query Qs_02 = simple : S  {
    from ce:cot_event
    where ce.servertime = "201705071635"
    attributes
        cot_type -> ce.cot_type
        how -> ce.how
    }

Would become something like...

query Qs_02 = simple : S  {
    from ce:cot_event
    where ce.servertime = ?
    attributes
        cot_type -> ce.cot_type
        how -> ce.how
    }

...with the value "201705071635" replaced by a '?'.

--
You received this message because you are subscribed to the Google Groups "categoricaldata" group.

To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "categoricaldata" group.

To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.

[Ryan Wisnesky]

In the approach I proposed you'd have to compose the queries after you instantiated the parameters.

Maybe we could generalize the definition of query to allow parameters directly... I'll give it some thought.

To unsubscribe from this group and stop receiving emails from it, send an email to categoricaldata+unsubscribe@googlegroups.com.

[Ryan Wisnesky]

How about adding two new sections to a query:

Query q = literal : S -> T {

Parameters

x : Nat y : String etc //only types

Instantiations

x = 3 

}

To execute, all parameters must be instantiated.  To compose q1 and q2, the parameters would have to match.  

[David Spivak]

Interesting.

Fred, may I ask how you are generating these questions? What kind of work are you doing that's leading you to ask?

[Fred Eisele]

Do you have any plans for adding support for prepared statements?

[Ryan Wisnesky]

If it's blocking you guys, I can start adding it now.  

On Jan 23, 2018 2:28 PM, "Fred Eisele" <fredric...@gmail.com> wrote:

Do you have any plans for adding support for prepared statements?

--

[Ryan Wisnesky]

There’s an interesting phenomenon going on with these parameters and their instantiations that I wanted to bring to the attention of the mathematicians.  Changing the names of the sections in the example previously proposed gives this:

Query q = literal : S -> T {

from x : Nat y : String etc //only types

where x = 3 

en1 -> { … } 

en2 -> { … } 

}

That is, there is a frozen instance (but only over the type side) over which the sub-queries are extended, and when the frozen instance is empty we’re back to normal AQL queries.  When the instance is not empty evaluating the query won’t be computable (usually) but it can still be given a meaning as a pi and a delta.