#!/usr/bin/env ruby -w
#
# ex:ts=10000
# vim:sts=4:sw=4:tw=0
#

#
# An example invocation of delete
#
def delete_example(args, attr)
    # Example from AMS
    sql = "DELETE FROM PublicationAttributes WHERE publication_id = #{args['PublicationId']} AND customer_id = #{args['Customer']} AND Name = '#{attr}'"
    #
    # Example of how to do this using the selector API.
    #
    pubattr = PublicationAttributes.select
    pubattr.publication_id = args['PublicationId']
    pubattr.customer_id    = args['Customer']
    pubattr.name =           attr
    pubattr.delete!

    #
    # A somewhat simpler API (?)
    #
    PublicationAttributes.select(:publication_id => args['PublicationId'], :customer_id => args['Customer'], :name => attr).delete!
end

#
# An example invocation of insert
#
def insert_example(dbh, args, arg, attr)
    # The original SQL (arg/attr is looped)
    dbh.do("INSERT INTO PublicationAttributes (publication_id, customer_id, name, value) VALUES (#{args['PublicationId']}, #{args['Customer']}, '#{attr}', '#{args[arg]}')")
    # Slightly improved SQL, which is what we'll attempt to match
    dbh.do("INSERT INTO PublicationAttributes (publication_id, customer_id, name, value) VALUES (?, ?, ?, ?)", args['PublicationId'], args['Customer'], attr, args[arg])

    # Preamble
    insertor = PublicationAttributes.insertor
    insertor.publication_id = args['PublicationId']
    insertor.customer_id = args['Customer']

    # Only the below needs to loop
    insertor.name = attr
    insertor.value = args[arg]
    insertor.insert             # Returns an object that can be used.  insert! turns the insertor into that object.
end

def select_join_example(typeid, field, aditemid)
    # Example from AMS
    dbh.select_all("SELECT * FROM amsValues LEFT JOIN amsValueSets ON amsValues.ValueId = amsValueSets.Value WHERE amsValues.Type=? AND amsValues.Field=? AND (amsValueSets.Ref=? OR amsValueSets.Ref IS NULL) ORDER BY amsValues.Name", typeid, field, aditemid) { ... }
    # The example as it ended up when we actually got it to work:
    dbh.select_all("SELECT * FROM amsValues LEFT JOIN amsValueSets ON amsValues.ValueId = amsValueSets.Value AND amsValueSets.Ref=? WHERE amsValues.Type=? AND amsValues.Field=? ORDER BY amsValues.Name", aditem, typeid, field) { ... }

    # And the rephrase (single-line)
    Values.select(:type => typeid, :field => field).leftjoin(ValueSet.select(:ref => aditemid)).order_by(:name).each { |value, set|
    }

    # And another, clearer rephrase:
    valueS = Values.select(:type => typeid, :field => field)
    setS   = ValueSet.select(:ref => aditemid)
    valueS.leftjoin(setS).order_by(:name).each { |value, set|
        # value is each of the values in valueS.
        # set is a ValueSet if available in setS, and nil otherwise.
    }

    # Hmmm - should perhaps this should be rephrased as (on SQL level)
    dbh.select_all("SELECT amsValues.*, exists(SELECT * FROM amsValueSets WHERE amsValues.ValueId = amsValueSets.Value AND amsValues.Ref = ?) AS has_set FROM amsValues WHERE Type=? AND Field=? ORDER BY Name", aditemid, typeid, field) { |value|
        # Here, value has an extra field "has_set", which is a boolean.
        # This assumes a flexible SQL interpreter, though - mysql can't do it.  Not sure about pgsql.
    }

    # Is the following a good reprase?
    valueS.leftjoin(setS.exists).order_by(:name).each { |value, set|
        # value is each of the values in valueS.
        # set is true if available in setS, and false otherwise.
    }


end

#
#
#
def partial_select_example()
        dbh.select_all("SELECT amsValue.Name,amsValue.ValueId FROM amsValue,amsValueSet WHERE amsValue.ValueId = amsValueSet.Value AND amsValueSet.AdItemId = ?", aditemid) { ... }

        # FIXME Not sure I like the "join" syntax below.  And this is all fairly long.
        setS = ValueSet.select(:aditem_id => aditemid)
        valueS = Value.join(setS)  
        valueS.project(:name, :value_id).each { |obj|
        }

        # Better?
        Value.ref_by(ValueSet.aditem_id == aditemid).project(:name, :value_id).each { |obj|
        }

        # Other option
end

def update_example
    "update amsImages set Parent=ImageId where Tag='PARENT';"

    selector = Image.select(:tag => "PARENT")
    selector.parent = selector.image_id
    selector.commit!

    # OR
    selector = Image.tag == "PARENT"
    selector.parent = selector.image_id
    selector.commit!

    # OR
    (Image.tag == "PARENT").instance_eval {
        parent = image_id
        commit!
    }

    # OR
    (Image.tag == "PARENT").update {
        parent = image_id
    }
end

def distinct_example
    "SELECT DISTINCT ticket, addr FROM NewUser"
    # Should not be necessary, methinks?
    NewUser.distinct.each { |stuff|
    }
    # Is this a valid relational query?
    "SELECT DISTINCT ON (ticket) ticket, addr FROM NewUser"
    NewUser.distinct( {
    }
end

def group_by_example
    "SELECT max(stuff) AS stuff_max, grouper FROM Table GROUP BY grouper"

    Table.project(:grouper) * Table.project { :grouper, stuff.max }
    "SELECT INTO TABLE tmp max(stuff) AS stuff_max, grouper FROM Table GROUP BY grouper"
    "SELECT min(stuff_max) FROM tmp"
    (Table.project(:grouper) * Table.project { :grouper, stuff.max }).stuff_max.min
    # FIXME Somehow, something that end up as a single data entry should just return the value
end

def other_example
    'Where',   "WHERE AdItem=${\($this->Get('AdItem'))} AND Parent=ImageId", 'OrderBy', "Priority",
end

def group_cy_example
    "SELECT max(foo),baz FROM Bar GROUP BY baz HAVING foobar > 3"
    # max et al ALWAYS cause a group by on the remaining fields.
    # available "group operations" are max, min, count (XXX non-nulls ?), sum, average, *.count, stddev, variance,
    ((Bar.foobar > 3).project(:baz) * Bar.project { :baz, foo.max }).each { |obh|
        obj.baz
        obj.foo_max
    }
    # Other expressions that may or may not fit in here:
    # exists(subquery)
    # in(subquery)
    # notin(subquery)
    # operator any(subquery) ???  (any operator must be true - similar to in)
    # operator all(subquery) ???  (ALL cases from subquery must be true)
 
    # Multi-level:
    "select x,y,z from (select x,y,z from table group by x,y,z having blah blah) as foo  group by x,y,z"
end