Myna. DataSet

A specialized array for working with tabular data

Summary
Myna. DataSetA specialized array for working with tabular data
Functions and Properties
DataSetCreates a new DataSet Object
columnsArray of column names in this DataSet
containsByColreturns true if any row in the DataSet has a column value that matches compare
findFirstByColreturns the first row in the DataSet whose column value matches compare, or null if no matches
findAllByColreturns a new DataSet of all the rows in this DataSet whose column value matches compare
valueArrayreturns an array of the values of a column.
mapCreates a new DataSet with the results of calling a provided function on every element in this array.
filterPerforms Array.filter, but returns a new DataSet with the same columns as this one
concatPerforms Array.concat, but returns a new DataSet with the same columns as this one
slicePerforms Array.slice, but returns a new DataSet with the same columns as this one
mergemerges another DataSet into this one, by a common column value
minByColreturns the “smallest” value of a column.
maxByColreturns the “largest” value of a column.
sumByColreturns a sum of the values of a column.
sortByColsorts the DataSet by the supplied column and compare function.
avgByColreturns an average of the column.
toHtmlTablereturns an HTML table of this dataset
pivotreturns a new DataSet pivoted around a key, category, and value
toStructconverts a DataSet into an hierarchical object
toMapreturn a JS object where a column is used for the keys
Myna. DataSet (Server-side Only)Myna.DataSet features only available server side
Functions
getDsreturns a java DataSource object that can be used as the “ds” option in queries
queryqueries this DataSet as a table named “data”, and returns a Myna.Query object

Functions and Properties

DataSet

var DataSet =function DataSet(options,
accessFunction,
lengthFunction)

Creates a new DataSet Object

Parameters

optionsEither an array to be converted to a DataSet, or and object containing detailed options.  If an array, the array must contain at least one record, and the record should have all the non-function properties expected in the DataSet so that DataSet.columns can be inferred.  If this is an object it should conform to the Options Object defined below.
accessFunctionOptional, default function(obj,index){return obj[index]} Only used if options is an array like object.  Function that takes obj and an index and returns the item at that index
lengthFunctionOptional, default function(obj){return obj.length} Only used if options is an array like object.  Function that takes obj and returns the length of the collection

Options Object

dataOptional default [] This is an array of initial data.  May be empty.
columnsOptional default [] Either a comma separated list, an array of column names, or an object whose non-function properties represent the column names.  These define the known properties of the objects in a DataSet array.  If columns is not provided, but data contains at least one row, columns will be calculated as all the non-function properties of the first row.

Detail

DataSet is a wrapper for an array of objects.  This is treated much like the result set of a query, but does not need to come from a query.  DataSet’s provide a normalized way to represent any tabular data

columns

Array of column names in this DataSet

containsByCol

DataSet.prototype.containsByCol = function(column,
compare)

returns true if any row in the DataSet has a column value that matches compare

Parameters

columnname of the column to search.
compareRegExp, string regular expresion, or function to compare. if compare is a function, it will be called with the “Compare Function Arguments” below.  The supplied compare function should return true if the current row should be output

Compare Function Arguments

columnValueValue of column in the current row,
dataAn object that represents all the columns in this row
indexThe index of the current row
datasetA reference to this dataset

findFirstByCol

DataSet.prototype.findFirstByCol = function DataSet_findFirst(column,
compare)

returns the first row in the DataSet whose column value matches compare, or null if no matches

Parameters

columnname of the column to search.
compareRegExp, string regular expresion, or function to compare. if compare is a function, it will be called with the “Compare Function Arguments” below.  The supplied compare function should return true if the current row should be output

Compare Function Arguments

columnValueValue of column in the current row,
dataAn object that represents all the columns in this row
indexThe index of the current row
datasetA reference to this dataset

findAllByCol

DataSet.prototype.findAllByCol = function DataSet_findAll(column,
compare)

returns a new DataSet of all the rows in this DataSet whose column value matches compare

Parameters

columnname of the column to search.
compareRegExp, string regular expression, or function to compare. if compare is a function, it will be called with the “Compare Function Arguments” below.  The supplied compare function should return true if the current row should be output

Compare Function Arguments

columnValueValue of column in the current row,
dataAn object that represents all the columns in this row
indexThe index of the current row
datasetA reference to this DataSet

valueArray

DataSet.prototype.valueArray=function(columnName)

returns an array of the values of a column.

Parameters

columnNameString Column name to return

map

DataSet.prototype.map = function(func)

Creates a new DataSet with the results of calling a provided function on every element in this array.

See

Array.map

filter

DataSet.prototype.filter = function()

Performs Array.filter, but returns a new DataSet with the same columns as this one

See

Array.filter

concat

DataSet.prototype.concat = function(otherArray)

Performs Array.concat, but returns a new DataSet with the same columns as this one

See

<Array.concat>

slice

DataSet.prototype.slice = function()

Performs Array.slice, but returns a new DataSet with the same columns as this one

See

<Array.slice>

merge

DataSet.prototype.merge = function(ds,
column)

merges another DataSet into this one, by a common column value

Parameters

dsOther dataset to merge into this one
columnString name of column that the two DataSets have in common

Example

var a = new Myna.DataSet([{
id:1,
name:"bob"
}])

var a = new Myna.DataSet([{
id:1,
age:15
}])

a.merge(b,"id")

minByCol

DataSet.prototype.minByCol = function(column,
compare)

returns the “smallest” value of a column.

Parameters

columncolumn to compare
compareOptiional, default: function(a,b){return a < b} A compare function like sort() uses to determine the minimum value

maxByCol

DataSet.prototype.maxByCol = function(column,
compare)

returns the “largest” value of a column.

Parameters

columncolumn to compare
compareOptiional, default: function(a,b){return a > b} A compare function like sort() uses to determaxe the maximum value

sumByCol

DataSet.prototype.sumByCol = function(column,
accessor)

returns a sum of the values of a column.

Parameters

columncolumn to sum
accessorOptional, default: function(element){return element} A function that takes an element of the column and returns a value to be summed.  This is useful to force integer math or to sum a property of the objects in the column rather than the objects themselves.

sortByCol

DataSet.prototype.sortByCol = function(column,
compare)

sorts the DataSet by the supplied column and compare function.

Parameters

columncolumn to sort
compareOptiional, default: String.compareAlpha A compare function that takes 2 elements and returns either 1, 0, or -1

Example

var files = new Myna.File("/").listFiles()
files.sortByCol("fileName",String.compareNatural)

Returns:

  • a reference to this DataSet

avgByCol

DataSet.prototype.avgByCol = function(column,
accessor)

returns an average of the column.

Parameters

columncolumn to average
accessorOptional, default: function(element){return element} A function that takes an element of the column and returns a value to be averaged.  This is useful to force integer math or to average a property of the objects in the column rather than the objects themselves.

Note

null values are ignored.  If you want to count nulls as 0, use this accessor

function(element){
return element===null?0:element;
}

toHtmlTable

DataSet.prototype.toHtmlTable = function()

returns an HTML table of this dataset

pivot

DataSet.prototype.pivot = function(keyField,
categoryField,
valueField)

returns a new DataSet pivoted around a key, category, and value

Parameters

keyFieldColumn name that contains the unique value for every row in the result.  Duplicate values for calculated columns will overwrite, missing values will be set to null
categoryFieldColumn name that contains the new columns that should be created.  These names will be cleaned such that invalid characters are replaced with “_” and the result is lower cased.  If this would result in a blank column (such as numeric values) then “category_<value>” is used for the column name.  If that still doesn’t work, then “category__unknown” is used for the column name
valueFieldColumn name that contains the values for each key

Detail

The purpose of this function is to convert a data set that looks like this

user  | category       | value

bob | Age | 35
sally | Age | 25
bob | Favorite Color | blue
sally | Favorite Color | yellow
bob | Start Date | 01/01/2001
sally | Start Date | 05/16/1997

into something like this

user   | age | favorite_color | start _date

bob | 35 | blue | 01/01/2001
sally | 25 | yellow | 05/16/1997

The above transform would be accomplished with

ds.pivot("user","category","value")

toStruct

DataSet.prototype.toStruct = function(keyCols,
remainingProperty,
full)

converts a DataSet into an hierarchical object

Parameters

keyColsArray of column names, in order of significance,
remainingPropertyOptional, default null if keyCols does not uniquely identify every row in the DataSet, and remainingProperty is defined, then the remain rows will be added to this property as an array.
fullOptional, default false If true, each level in the hierarchy contains all the values of the first row of that branch, and sub trees branch of the col name

The purpose of this function is to convert flat result sets into a structured hierarchy.  This is best illustrated by examples

Examples

// Original Set
// employee_id | title | position_code | department_name | department_code
// ----------- | --------------------- | ------------- | -------------------------- | ---------------
// 100000001 | Cp Tech-Mental Health | 01021C | MILAGRO | 01106550
// 100000003 | Universal Interviewer | 054700 | MED SPECIALTIES CLINIC B | 01017120
// 100000075 | Clerk Outpt | 054700 | MED SPECIALTIES CLINIC B | 01017120
// 100001035 | Clerk Outpt | 054700 | MED SPECIALTIES CLINIC B | 01017120

var simple = original_set.toStruct(["position_code","department_code"])
[ Object ]
+-[01021C] [ Object ]
| \-[01106550] [ Array ]
| \-[0] [ Object ]
| +-[department_code] 01106550
| +-[department_name] MILAGRO
| +-[employee_id] 100000001
| +-[position_code] 01021C
| \-[title] Cp Tech-Mental Health
\-[054700] [ Object ]
\-[01017120] [ Array ]
+-[0] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000003
| +-[position_code] 054700
| \-[title] Universal Interviewer
+-[1] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000075
| +-[position_code] 054700
| \-[title] Clerk Outpt
\-[2] [ Object ]
+-[department_code] 01017120
+-[department_name] MED SPECIALTIES CLINIC B
+-[employee_id] 100001035
+-[position_code] 054700
\-[title] Clerk Outpt
var simple_with_rows = original_set.toStruct(["position_code","department_code"],"rows")
[ Object ]
+-[01021C] [ Object ]
| \-[01106550] [ Object ]
| \-[rows] [ Array ]
| \-[0] [ Object ]
| +-[department_code] 01106550
| +-[department_name] MILAGRO
| +-[employee_id] 100000001
| +-[position_code] 01021C
| \-[title] Cp Tech-Mental Health
\-[054700] [ Object ]
\-[01017120] [ Object ]
\-[rows] [ Array ]
+-[0] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000003
| +-[position_code] 054700
| \-[title] Universal Interviewer
+-[1] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000075
| +-[position_code] 054700
| \-[title] Clerk Outpt
\-[2] [ Object ]
+-[department_code] 01017120
+-[department_name] MED SPECIALTIES CLINIC B
+-[employee_id] 100001035
+-[position_code] 054700
\-[title] Clerk Outpt
var full_with_rows = original_set.toStruct(["position_code","department_code"],"rows")
[ Object ]
+-[department_code] 01106550
+-[department_name] MILAGRO
+-[employee_id] 100000001
+-[position_code] [ Object ]
| +-[01021C] [ Object ]
| | +-[department_code] [ Object ]
| | | \-[01106550] [ Object ]
| | | +-[department_code] 01106550
| | | +-[department_name] MILAGRO
| | | +-[employee_id] 100000001
| | | +-[position_code] 01021C
| | | +-[rows] [ Array ]
| | | | \-[0] [ Object ]
| | | | +-[department_code] 01106550
| | | | +-[department_name] MILAGRO
| | | | +-[employee_id] 100000001
| | | | +-[position_code] 01021C
| | | | \-[title] Cp Tech-Mental Health
| | | \-[title] Cp Tech-Mental Health
| | +-[department_name] MILAGRO
| | +-[employee_id] 100000001
| | +-[position_code] 01021C
| | \-[title] Cp Tech-Mental Health
| \-[054700] [ Object ]
| +-[department_code] [ Object ]
| | \-[01017120] [ Object ]
| | +-[department_code] 01017120
| | +-[department_name] MED SPECIALTIES CLINIC B
| | +-[employee_id] 100000003
| | +-[position_code] 054700
| | +-[rows] [ Array ]
| | | +-[0] [ Object ]
| | | | +-[department_code] 01017120
| | | | +-[department_name] MED SPECIALTIES CLINIC B
| | | | +-[employee_id] 100000003
| | | | +-[position_code] 054700
| | | | \-[title] Universal Interviewer
| | | +-[1] [ Object ]
| | | | +-[department_code] 01017120
| | | | +-[department_name] MED SPECIALTIES CLINIC B
| | | | +-[employee_id] 100000075
| | | | +-[position_code] 054700
| | | | \-[title] Clerk Outpt
| | | \-[2] [ Object ]
| | | +-[department_code] 01017120
| | | +-[department_name] MED SPECIALTIES CLINIC B
| | | +-[employee_id] 100001035
| | | +-[position_code] 054700
| | | \-[title] Clerk Outpt
| | \-[title] Universal Interviewer
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000003
| +-[position_code] 054700
| \-[title] Universal Interviewer
\-[title] Cp Tech-Mental Health

toMap

DataSet.prototype.toMap = function(keyCol,
valueCol,
valuesInArray)

return a JS object where a column is used for the keys

Parameters

keyColColumn to use for keys
valueColOptional, default null If defined, column to use for values.  Otherwise the entire row will be the value
valuesInArrayOptional, default false If true, values will be in an array in order of appearance.  Otherwise the last value for valueCol will be assigned

Example

var empMap = empDataSet.toMap("employee_id","employee_name")
var selectedName = empMap[selectedEmpId];

Myna. DataSet (Server-side Only)

Myna.DataSet features only available server side

Summary
Functions
getDsreturns a java DataSource object that can be used as the “ds” option in queries
queryqueries this DataSet as a table named “data”, and returns a Myna.Query object

Functions

getDs

Myna.DataSet.prototype.getDs =function()

returns a java DataSource object that can be used as the “ds” option in queries

Detail

This will create an in-memory H2 database containing a single table “data” which contains the contents of this DataSet.  The return value will be a DataSource that can be used to query this table

query

Myna.DataSet.prototype.query =function(options)

queries this DataSet as a table named “data”, and returns a Myna.Query object

Detail

This will create an in-memory H2 database containing a single table “data” which contains the contents of this DataSet.  This function takes the same options as Myna.Query, except that the ds/dataSource parameter is ignored

var DataSet =function DataSet(options,
accessFunction,
lengthFunction)
Creates a new DataSet Object
DataSet.prototype.containsByCol = function(column,
compare)
returns true if any row in the DataSet has a column value that matches compare
DataSet.prototype.findFirstByCol = function DataSet_findFirst(column,
compare)
returns the first row in the DataSet whose column value matches compare, or null if no matches
DataSet.prototype.findAllByCol = function DataSet_findAll(column,
compare)
returns a new DataSet of all the rows in this DataSet whose column value matches compare
DataSet.prototype.valueArray=function(columnName)
returns an array of the values of a column.
DataSet.prototype.map = function(func)
Creates a new DataSet with the results of calling a provided function on every element in this array.
DataSet.prototype.filter = function()
Performs Array.filter, but returns a new DataSet with the same columns as this one
DataSet.prototype.concat = function(otherArray)
Performs Array.concat, but returns a new DataSet with the same columns as this one
DataSet.prototype.slice = function()
Performs Array.slice, but returns a new DataSet with the same columns as this one
DataSet.prototype.merge = function(ds,
column)
merges another DataSet into this one, by a common column value
DataSet.prototype.minByCol = function(column,
compare)
returns the “smallest” value of a column.
DataSet.prototype.maxByCol = function(column,
compare)
returns the “largest” value of a column.
DataSet.prototype.sumByCol = function(column,
accessor)
returns a sum of the values of a column.
DataSet.prototype.sortByCol = function(column,
compare)
sorts the DataSet by the supplied column and compare function.
DataSet.prototype.avgByCol = function(column,
accessor)
returns an average of the column.
DataSet.prototype.toHtmlTable = function()
returns an HTML table of this dataset
DataSet.prototype.pivot = function(keyField,
categoryField,
valueField)
returns a new DataSet pivoted around a key, category, and value
DataSet.prototype.toStruct = function(keyCols,
remainingProperty,
full)
converts a DataSet into an hierarchical object
DataSet.prototype.toMap = function(keyCol,
valueCol,
valuesInArray)
return a JS object where a column is used for the keys
Myna.DataSet.prototype.getDs =function()
returns a java DataSource object that can be used as the “ds” option in queries
Myna.DataSet.prototype.query =function(options)
queries this DataSet as a table named “data”, and returns a Myna.Query object
Sql query object Overview: The Query object can be used for queries that return a result set (select statements), as well as those that do not (create, update, insert, delete etc...)
Array of column names in this DataSet
if (!Array.prototype.map)
Creates a new array with the results of calling a provided function on every element in this array.
if (!Array.prototype.filter)
Creates a new array with all elements that pass the test implemented by the provided function.