Skip to content

Active Statements

Jump to bottom
Ke edited this page Sep 12, 2019 · 9 revisions

Active statements allow you to construct and modify SQL statements on the fly. When tied to a ds connection these statements can be invoked at the results worked with. These are only compatible to data sources supporting SQL statements.

Active Statements do not invoke the data source until they are either queried or invoked with a given block of code. This means they are extremely efficient and flexible by being reusable.

The below chart measures statement construction time, select represents active_statements.

Select Statements — select_statement

The select_statement constructor allows you compose SELECT SQL statements. It can be called via ds with either the ->select or the ->where methods.

// Set the products (data source is not invoked)
define products = ds(::store.products)->where('status =1')

// Work with products (retrieved from data source)
with row in products->rows do {
    #row // 200 rows with status of 1
}

// Work with sub set of products (subset retrieved from data source)
with row in products->where('brand'='lasso')->rows do {
   #row // 10 rows matching Lasso and status = 1
}

// Work with products again (retrieved from data source)
with row in products->rows do {
    #row // 200 rows with status of 1
}

Methods that return a select statement from ds

The below methods return a select_statment bound to a data source. When invoked the select_statement is executed on the the data source.

ds->select(...) ds->where(...)

Methods that construct / modify the statement

All of these methods will invoke the data source if supplied a given block.

-> select

-> from

-> join

-> where

-> group

-> having

-> orderby

-> limit

-> update

This returns a update_statement that corresponds to the current select_statement. It inherits the select_statements where clause and table — allowing bulk updates on matching rows.

Methods that invoke the Data source

-> do

-> rows

-> rows(astype::tag)

-> rows(invokewith::memberstream)

-> asstring (if tied to a datasource)

-> invoke

Calculating found count

Often when working with large datasets we want to use the LIMIT clause to limit the amount of data returned by the data source. This has implications on calculating the number of rows found. The count method executes the SQL statement without the LIMIT or ORDER BY elements and only requests COUNT(*) in terms of data. The select_statement must be bound to a ds for this to work.

-> count

This executes the SQL statement and returns an integer representing the found count.

-> ascount

This returns the select_statement used by the count method.

OOP and Select Statements

Active statements can also automatically cast objects as an activerow, types which inherit activerow or support ->oncreate(row::ds_row). This is a very efficient to work with types in an OOP fashion.

// Define products (typically at startup)
define products => ds(::store.products)->where('status = 1')->as(::product)

// Work with live products
with product in products->rows do {
	#product // ::product type where status = 1
}

Update Statements — update_statement

You can update multiple rows and generate UPDATE SQL statements with this constructor.

Methods that construct / modify the statement All of these methods will also invoke the data source if supplied with a given block.

-> update(table::tag), update(table::string)

-> set(pair,..), set(::string,...)

-> where(::pair,...), where(::string,...)

Methods that invoke the data source

-> do

-> affected

-> invoke

Insert Statements — insert_statement

The insert_statement contrustructor allows you compose INSERT statements. The advantage they provide is the ability to insert multiple rows sourced from different data types. They support automatic batch inserting and duplicate key handling with MySQL.

Like the select_statement the insert_statment can be bound to a ds.

Methods that return a select statement from ds

The below methods return a select_statment bound to a data source. When invoked the insert_statement is executed by the the data source.

ds->insert(...)

Methods that construct / modify the statement

All of these methods will invoke the data source if supplied a given block.

-> into(table::tag, column1, column2, ...), into(table::string, column1, column2, ...)

The into method allows you to specify both the table and optionally the columns.

-> columns(::column1,::column2,...), columns('column1','column2',...)

Specify the columns based on the supplied paramters:

	insert_statement->columns(::brand,::description,::price)

-> columns(array(::column1,::column2)), columns(array('column1','column2'))

Specify columns based on a supplied array of columns.

	insert_statement->columns(
		array('brand','description','price')
	)

-> addrow(row_values::array)

Add the supplied row to the insert queue, number of array elements must match the sequence and number of columns specified.

	#ds->insert->into(::mytable)
	->columns(::description,::price)
	->addrow(
		array(
           	::description='from a map',
            ::price = 9.95
        	)
	)->do

-> addrow(row::map)

Add the supplied row to the insert queue, the map will be queried for each column specified and any non existent values will be inserted as null values:

	#ds->insert->into(::mytable)
		->columns(::description,::price)
		->addrow(
			map(
            	::notthere = 'OK',
            	::price = 9.95
            	::description='from a map',
			)
		)->do

-> addrow(column::pair.column::pair,...)

Add a row based on the supplied parameters. The number of should match each column specified and any non existent values will be inserted as null values:

	#ds ->insert->into(::mytable)
		->columns(::description,::price)
		->addrow(
			::description='from a map',
			::price = 9.95
		)->do

-> addrows(row::trait_foreach)

Add multiple rows to be inserted, each specified row can either be a map or an array:

	#ds ->insert->into(::mytable)
		->columns(::description,::price)
		->addrows(
			array(
				array('row1',9.98),
				array('row2',9.98),
			)
		)->do

Automatic Inserting

When ->insertevery is specified, the insert statement will automatically be invoked once the number of added rows reaches the supplied value (the inserted rows are then cleared from the queue). This allows flexible batch inserting of rows. It's important to invoke / execute the statement even when specified to insert an outstanding rows.

-> insertevery(numberofrows::integer)

	local(insert) = example_ds->insert->into(::products,::col1,::col2)

	#insert->insertevery(50)

	with item in #newproducts do {
		#insert->addrow(#item->get(1),#item->get(2))
	}
	#insert->do

On duplicate key update

The insert_statement supports on duplicate key handling when working with MySQL (and it's variants / flavours).

-> onduplicate(keyupdate::boolean)

All inserted columns will be updated with the new values on duplicate key:

	insert_statement->onduplicate(true)

-> onduplicate(p::tag), onduplicate(p::string)

Add this column to the list of columns to update with the new value on duplicate key:

	insert_statement->onduplicate(::column)

-> onduplicate(p::pair)

The specified column will be updated with the supplied value or expression:

	insert_statement->onduplicate(
		::column = 'IF(column < 20,column + 1,values(column))'
	)

-> onduplicate(p1, p2, ...)

Update the specified column or pairs on duplicate key:

insert_statement->onduplicate(::column1,::column2,::column3 = 1)

-> onduplicate(keyupdate::array)

Update these columns on duplicate key:

insert_statement->onduplicate(array('column1','column2' = 1))
Clone this wiki locally