Skip to content

Working with data sources — ds

Jump to bottom
Ke- edited this page Sep 27, 2014 · 14 revisions

The primary component of Datasource is the ds type, this is used to connect to and interact with data sources or used to define datasource connections. ds types can be stored and worked with as locals or defined for convenient reuse.

Connecting to a data source

When using ds to connect to a data source there are a number of approaches you can take. The most convenient way is with the below signature which leverages Lasso Admin and the configuration specified there:

ds(::database.table) or datasource(::database.table)

The first part of the tag specifies the database name and the second part the default table. These details are then used to lookup the host details using an optimised sqlite query. ::tags are used throughout Datasource due to the input restrictions they enforce (encouraging good naming conventions) and the clean distinct style they provide. That said, you can also use the below:

ds('database','table')

The fastest method to specify the data source is to do so directly and bypass Lasso admin:

ds(::mysqlds,'127.0.0.1',::database.table,'user','pass')

This is effectively one less database call (the host information search in Lasso Admin) and allows you to skip configuring your database in Lasso Admin completely. You don't need to specify a table, but when one is specified the table becomes the default table for the data source.

You can also use -named parameters:

ds(
  -host = '127.0.0.1',
  -database = 'database',
  -table = 'table',
  -username = 'username', 
  -password = 'password'
)

The full list of natively supported -params and defaults are:

	-datasource::string='mysqlds'
	-database::string=''
	-table::string=''
	-keycolumn::string='id'
	-sql::string=''
	-host::string=''
	-port::integer=3306
	-username::string=''
	-password::string=''
	-schema::string=''
	-key::string=''
	-encoding::string='UTF-8'
	-maxrows::integer=50

Defining ds connections

As opposed to specifying the connection details for each call, it makes sense to define ds connections for reuse. If a table is specified then this becomes the default table for the data source.

define store_ds => ds(::store.products)

This approach allows you to manage all of your connections within a single file independent of Lasso Admin and the current Lasso Instance. From then on you can reference the ds anywhere in your code:

with row in store_ds->all->rows do {
    // do something with each #row
}

You can also assign ds to a local to work with:

local(store_ds) = ds(::store.products)

Specifying tables

You can reference alternative tables via the ->table(::tag) method — it returns a reference to the ds making all of the standard methods applicable to that table:

with row in store_ds->table(::users)->allrows do {
    // do something with each user
}

You can also stack multiple ds definitions (effectively leveraging other connection details):

define store_ds   => ds(::store.products)
define product_ds => store_ds->table(::products)
define user_ds    => store_ds->table(::users)

The above approach allows you to relocate databases and tables without having to update your working code. Your code will still refer to product_ds even after it's been moved to a different server, database or has been simply renamed. The above definitions would only be loaded once at startup (unless being redefined).

Specifying key columns

The default key column is id — you can also specify an alternative key columns:

ds(::store.products)->keycolumn(::guid)
ds(::store.products)->keycolumn('guid')

Or like this:

ds(
   -database = 'store',
   -table = 'products',
   -keycolumn = 'guid'
)

Specifying max rows

By default the number of rows returned is 50, you can can specify the maximum number of rows on a connection level like so:

define product_ds => ds(::store.products)->maxrows(1000)

Or like this:

ds(
   -database = 'store',
   -table = 'products',
   -maxrows = 1000
)

Alternatively the various search methods often allow for a maxrows parameter.

Global settings

Datasource supports global settings, you can override them with the below definitions.

	define ds_default_silent => false
	define ds_default_maxrows => 50

Searching the data source

The following methods invoke the data source and stores the results internally within Datasource. They also return a self reference allowing chainability and convenient querying of the ds type.

####-> sql(statement::string,max::integer=50)::ds Execute the specified sql statement/s — if the string contains multiple SQL statements multiple result sets are stored. Multiple result sets can be accessed via the ->results method. By default ->rows returns the rows from the first result set.

with row in product_ds->sql(`
    SELECT *
    FROM products 
    WHERE brand = "example"
`)->rows do {
    // do something with #row
}

Once working with rows you can also use Lasso's query syntax:

with row in product_ds->sql(...)->rows
where #row(::name) >> 'example' do {
    // do something with #row
}

####-> search(...)::ds This allows for search input using classic inline parameters.

####-> all::ds, all(maxrows::integer) Finds all rows in the table (max rows defaults to all rows).

Methods that return rows

The following invoke the data source and return a static array containing the found rows.

####-> findrows(...)::staticarray This allows for search input using classic inline parameters using classic inline parameters.

with row in product_ds->findrows(
    'brand' = 'example'
) do {
    // do something with rows matching 'example'
}

####-> allrows(maxrows::integer)::staticarray Returns all rows from the table (max rows defaults to all rows).

with row in product_ds->allrows(100) do {
    // do something with 100 rows
}

Methods that return active_statements.

####-> where(equals::pair, ...), where(query::string, ...) Initialises the select_statement constructor — see the relevant portion of this document.

product_ds->where('brand' = 'example')
product_ds->where('brand LIKE "example"')

####-> select(column::tag,column::tag, ...), select(columns::trait_foreach) Also, initialises the select_statement constructor — see the relevant portion of this document.

product_ds->select(::id,::description)->where('brand' = 'example')

Retrieving rows using key values

By default ds assumes that the key column is id. This can be overridden by providing a pair to ->getrow or by specifying -keycolumn or ->keycolumn(::thecolumn)when defining the ds.

Get single row matching key value

Return the first (and typically only) row matching the supplied key value:

-> getrow(keyvalue::integer)::ds_row or ::void
-> getrow(keyvalue::string)::ds_row or ::void
-> getrow(keyvalue::pair)::ds_row or ::void

	// Get one row with an id of 3
	local(row) = store_ds->getrow(3)

	// Get one row where keycolumn uuid matchings 
	local(row) = store_ds->getrow(
	    'guid' = '8df6cff4-34f5-46b5-84d1-abe0e960cee0'
	)

Get single row matching multiple key values

Return the first (and typically only) row matching the supplied key values:

-> getrow(keyvalue::integer,...)::ds_row or ::void
-> getrow(keyvalue::string,...)::ds_row or ::void
-> getrow(keyvalue::pair,...)::ds_row or ::void

	// Get one row using multiple keycolumns 
	local(row) = store_ds->getrow(
	    'keya' = '8df6cff4-34f5'
	    'keyb' = 'abe0e960cee0'
	)

Get multiple rows matching multiple key values

You can retrieve multiple rows matching the supplied key values. A staticarray is returned containing any matched rows. If it is empty no rows were matched.

-> getrows(keyvalue::integer, ...)::staticarray
-> getrows(keyvalue::string, ...)::staticarray
-> getrows(keyvalues::trait_foreach)::staticarray

// Get rows with id of 1, 2 or 3
local(rows) = ds(::store.users)->getrows(1,2,3)

// Get rows with id of 1, 2 or 3
local(ids) = array(1,2,3,7,9)
local(rows) = ds(::store.users)->getrows(#ids)

Modifying the data source

The most straightforward way of modifying rows is either via rows themselves, with ds_row, activerow or an update_statement — all of which are outlined later in this document. It is more productive to work directly with ds_row and activerow which leverage the below methods.

Update a row

Update the specified row in the current table with the supplied data:

-> updaterow(data::map,key::integer)
-> updaterow(data::map,key::string)
-> updaterow(data::map,key::pair)

Update the specified row and table with the supplied data:

-> updaterow(intable::tag,data::map,key::integer)
-> updaterow(intable::tag,data::map,key::string)
-> updaterow(intable::tag,data::map,key::pair)

Add a row

Add a row to the current table:

-> addrow(p::pair,p2::pair,...)
-> addrow(p::map)

#ds->addrow(
    map(
        'columnA' = 'this',
        'colimnB' = 'that'
    )
)

Add a row to the specified table:

-> addrow(totable::string,data::map)
-> addrow(totable::tag,data::map)

#ds->addrow(::toThisTable
    map(
        'columnA' = 'this',
        'colimnB' = 'that'
    )
)
#ds->addrow('toThisTable'
    map(
        'columnA' = 'this',
        'colimnB' = 'that'
    )
)

Delete a row

Delete the specified row from the current table:

-> deleterow(key::integer)
-> deleterow(key::string)
-> deleterow(key::pair, ...)

// Using an integer keyvalue (default key column id) 
#ds->deleterow(1)

// Using a pair to specify an alternative key column
#ds->deleterow('uuid' = 'EE47590C-A1BA-4639-BAB8-EE8019E7D2DD')

Delete the specified row from the specified table:

-> deleterow(fromtable::tag,key::integer)
-> deleterow(fromtable::tag,key::string)
-> deleterow(fromtable::tag,key::pair)

// Using an integer keyvalue (default key column id) 
#ds->deleterow('fromThisTable',1)

// Using a pair to specify an alternative key column
#ds->deleterow('fromThisTable','uuid' = 'EE47590C-A1BA-4639-BAB8-EE8019E7D2DD')
Clone this wiki locally