Hello, please sign in or register
You are here: Home

MVC

Synopsis

The information schema is best thought of as a data model. A set of instructions that describe a dataset ( a collection of database tables linked by unique keys ). The information schema is a PHP file, which is configurable. Using the information schema we are able to extract data in a logical format. Because this includes an instruction set we can build tools that understand the database structure, as the structure grows and changes so will the tools adapt dynamically to include the features of the new data model.

A tool is an application that interacts with the whole or part of the data.

Tools included

  • Downloading a dataset of many records in a flatfile.
  • Upload a flatfile and have it mapped to database tables and fields.
  • Search for items based upon parameters generated by the information schema.
  • Add / Edit an item
  • View an item.

Building an Information Schema

Summary of Schema attributes


Core attributes (common)

table string
Provides the table from which all fields and will be adopted by a branch, unless specified otherwise. It ommitted from a branch it adopts its parents table name.
field string
Reference to a database field, or an array of fields. When used in the context of handler::field this may only be a string.
key string
The key field in the table, this must be a UID field. And have a sibling attribute 'table'
type string/int/array
The type of field being accessed is part of the validation process. This utilises the validation function input_regex() which is an additional function utilised within the schema classes
branch array
In the root of the schema this was known as 'column' which has been deprecated for simplicity and scalability. A branch is made up of many "reference name => array" elements. The array value in each entry in branch has the same attributes available to the root of the schema. This is scalability.
order_field string
If this is provided all results coming from the database are ordered according to the value in this field. When inserting values this field is autmatically populated. By default it this is omitted the results are ordered alphabetically.
default string
The default attribute can be attached to most elements. And provides a way of offering a default value, when extracting data from the database. The default value can be a string. ToDo? make it caclulate its value based upon a composition of another field within the schema.

Referencing data using Handlers

handler array
In our database we might want to reference a name from another dataset. This is normally connecting to another table via a UID. E.g. To link a company to a particular product we have a many->to->one relationship. All handlers are defined by many->to->one relationships. Because we can only return one field from the handler, e.g. the company name it is imperative that this value is unique.
key_name string (deprecated, see field)
This must be a child of 'handler'. And references a name that we wish to use as our reference.
create array/bool
This is an optional parameter within 'handler'. If this is defined the handler can use the settings to create additional records within the handler table.
additional array
This is an optional parameter within 'handler'. If this is defined then
alias mixed
This is an optional parameter within 'handler'. If the value cannot be found within the user table it can be acquired from an alias table. The alias elements are the same as any handlers. If a new field is provided on its own then its contents is linked
handler_ref string (deprecated)
If the handler is defined. This is the name of the field that the handlers value is defined in.

Spreadsheet Specific

delimiter string
Defines the separation delimiter of records used when downloading or processing a flat-file.
sub-delimiter string
Defines the separation delimiter of fields (within records) when downloading and processing a flat-file


Presentation

group string
A display parameter. To help group the contents from various different sections.

Validation

min int
Define the minimum number of elements that can be included within the current branch.
max int
Define the maximum number of elements that can be included within the current branch.

Deprecated Elements

columns array deprecated
Alias with 'branch'. A list of all the elements. This has been deprecated in an effort to make the schema modular from the start
parent_keys array/string
schema_ref string (decremented)
The schema_ref is used to define the key with which to merge with the parent key

Core attributes (common)

table string - all

A scalar value referring to a table other than the schema[table] where the record(s) are stored.

Example.

    'table' => 'tb_underlying',

This uses the 'field' tag element to determine which fields in the above table the data belongs.

field array/string - all

The name is a reference to the table field that the TAB value will be assigned to. The name can be a string value of a table field. This can be a child of the schema::field, branch::field, or handler::field. Please note that in handler this is otherwise referred to as handler::key_name

Example 1

	'field'	=> 'var_column',

Alternatively it can be an array containing table fields as keys and a reference to the value as array element values.

Example 2

	'field' => array
(
'int_column_one' => '@from_type_1',
'var_column_two' => '@from_type_2',
'var_enu' => 'a constant'
)

In example 2 the array is made up of keys which represent a database column name and values which if prefixed with ‘@’ represent a user defined variable. ( derived from the key names in ‘type’). If there is not a '@' prefix then the value is assumed to be a constant.

key string - all

The key is a UID reference that a table might have. Many tables have composite keys and these are very useful. In such a circumstance the composite Keys must be made up of all the parent elements keys getting to this element node, which ever key currently referring too (!!! makes little sense, yes? needs re-articulating). This is in keeping with a normative design.

type array/string - all

If the 'type' is a scalar value then the the value represents a validation method. This can either be a defined by a regular expression or an INPUT_CONSTANT ( see input_regex() ).

	'type'	=> INPUT_something

An example of a regular expression might be multiple values, e.g. 'on|off' which means that the value can contain one of either string 'on' or 'off'.

If an array of mixed types then the key will be the name of the incoming sub-set e.g. Knockout 1 (date:level:payout), will be defined by a type array of …

	'type' 	=> array
(
'date' => INPUT_DATE
'level' => INPUT_FLOAT,
'payout'=> INPUT_FLOAT
),

branch array – all

Branching is a scalable tag node that allows us to join multiple database tables in a one to many join. The branch adopts the values of its parents described by the elements 'parents_id' or alternatively 'parents'.

order_field

If this is provided all results coming from the database for this branch or table are ordered according to the value of this attribute. When inserting values into the field defined by this attribute, the order is derived from the key. Other records during a reshuffle will first be deleted. More on this reason later.

By default it this is omitted the results are ordered alphabetically.

default

This is a string representation of the default value. If the value is not in the database the default value is given. This is different from the propposed derived value which modifies stored data.

Referencing data using Handlers

handler string - all

An array representing a data node ( or reference ), like a Company. There all 3 mandatory fields. The 'key_name' is the value to display. 'key' is the index of the value, that we use to reference.

Whilst 'table' is the name of the node table.

    "handler"	=> array
(
'key_name' => “var_company_name”,
'key' => “int_company_id”,
'table' => “tb_companies”,
),

alias array – update

If the current data is not found in the current handler, the alias is another handler which will be searched, to find an alias of the incoming data.

create array/bool - update

If this is set, the record if unfound will attempt to be created.

additional array – update

The additional parameter says to search with an additional condition. This can be a fixed value e.g.

Example

   'additional' => array
(
'int_field_cond_id' => 1
)

Alternatively we can use a variable which is prefixed by ‘@@’ this is evaluated at the time it is used. See: Setting User variables.

default array/string – view

The default element allows us to substitute a value if the value we are looking for is not present. This can take two types: a scalar value a straight forward string replace or an array: similar to the handler. (SQL: key, key_name, table)

handler_ref string - all (deprecated)

The column name associated with the returning value from a handler. This is automatically added to the list returned.

Some of these keys are in practice mutually exclusive ( two will rarely appear in the same element).

schema_ref string - all (deprecated)

Is an alternative reference to the schema key by default the schema key is already set. But this can be used if you want to change the name of the schema key.

Spreadsheet Specific

delimiter string - (update, view) spreadsheet

The delimiter field is used to overide any default settings for presenting the data in the spreadsheet. By default the 'delimiter' adopts the parent delimiter value. This can be overwritten by defining in a branch instances. This expects to be defined alongside a table attribute. If the attribute is not defined then this is ignored, because the data would be incorrect.

One special value is MULTIPLE_FIELDS. This forces the each record to occupy separate spreadsheet cells. When reading the data from a spreadsheet this attribute forces the reader to find every cell that starts with the elements reference name. This can be defined as schema::delimiter or branch[]::delimiter

Example

    'delimiter' => MULTIPLE_FIELDS,

sub-delimiter string – (update, view) spreadsheet

A scalar value which defines the sub-record delimiter. If your subset of data crosses multiple db fields then this would specify the delimiter in which to separate the TAB contents. Alike delimiter this may be a MULTIPLE FIELD type so can scan across many TAB fields.

Example

    'sub-delimiter' => ':'

Would use values from TAB columns headed by

Callable 1 (date:payout) Callable 2 (date:payout) ,… … Callable n (date:payout)
12/05/2007:100 12/05/2007:100 12/05/2007:100

Using MULTIPLE FIELDS

Example

"Callable" => array
(
"delimiter" => MULTIPLE_FIELDS,
"sub-delimiter" => MULTIPLE_FIELDS,
"type" => array
(
"date" => INPUT_DATE,
"payout"=> INPUT_INTEGER
),
)
Callable 1 Date Callable 1 Payout Callable n Date Callable n Payout
12/05/2007 100 12/05/2007 100

Presentation

group string – (view) form

  • This attribute was created to group the element into tab sections on the Edit/Add form.
  • It is used no where else, at the present time.

Validation

min int

Define the minimum number of records that can be included within the current branch/table.

max int

Define the maximum number of records that can be included within the current branch/table.

Deprecated Elements

columns array deprecated Alias with 'branch'. A list of all the elements. This has been deprecated in an effort to make the schema modular from the start

parent_keys array/string

Parent keys was used to associate

schema_ref string (decremented)

The schema_ref is used to define the key with which to merge with the parent key

Building A Spreadsheet. Understanding relational database structure

A schema element may be defined by either:

  • Single Field: Scalar
  • Single Field: Set
  • Single Field: Multiple Set
  • Multiple Field: Multiple Set

Read on for examples and descriptions of element types.

Single field: Scalar:

A single field on the TAB we wish to contain a single scalar value. E.g. 'ID number'.

This is a scalar match in the schema. We wish to validate the contents of the scalar variable by specifying a ‘type’. Since it is a single value we need only insert only one record per product (TAB row) into a field known as ‘field’

The schema entry would look like this:

 'ID number' => array
(
'field' => 'tb_product.int_product_id',
'type' => INPUT_INTEGER
)

Where INPUT_INTEGER is a constant and marks the TAB contents for validation against the integer regular expression. And tb_product.int_product_id is the DB field for the variable to be stored.

Single field: Set:

A single field may contain a set, with a delimiter, e.g. ';;'

Action: Make a N:M reference.

Example.

Target Markets
FRANCE;;UK

In the process we wish to validate all the set items separately as strings. Find the key_id values for each of the set items – according to a SQL handler. Create records in a relational table - Using the set key_id and referencing the ‘schema_key’.

The schema_key is a reference ID to match tables. Remove previous rows which match the schema_key but are now obsolete. The schema entry would look this:

   'Target Market' => array
(
'delimiter'=> ';;',
'type' => INPUT_STRING,
'handler' => array
(
'field' => 'var_name',
'key' => 'int_market_id',
'table' => 'tb_market'
)
'table' => 'tb_product_market'
)

Defining a delimiter implies the TAB field contains a set. The handler will return a single value.

By specifying a table a handler_reference and a schema_reference a query such as INSERT INTO table (handler_ref, schema_ref) VALUES( $handler, $schema_key) will be built

Single field: Multiple Set:

A single field may contain a set or multiple records, with a delimiter '.', and a sub delimiter to denote multiple fields “::”.

Action: Make a N:M reference with multiple data.

E.g.

Strike Level (underlying:level)
FTSE100::3578.00, Nikkei225::5704.40, SMI::7682.69

In the process we wish to validate all the set sub items separately as strings:float.

Find the key_id values for each of the set items – according to a SQL handler.

Additionally if no key is returned attempt to insert a new record and return the insert_id.

Create records in a relational table - Using the set key_id and referencing the ‘schema_key’.

The schema_key is a reference ID to match tables. In case of either an update or an insert. previous rows which match the schema_key but are now obsolete.

The schema entry would look this:

  'Strike_level' => array
(
'delimiter' => ',',
'sub-delimit' => ':',
'type' => array
(
'level' => INPUT_STRING,
'underlying' => INPUT_FLOAT
),
'handler => array
(
'underlying' => array
(
'key' => 'int_underlying_id',
'field' => 'var_underlying_name',
'table' => 'tb_underlying'
)
),
'field' => array( 'flo_strike_level', '@level')
'table' => 'tb_product_underlying'
)

Defining a 'table' implies the TAB field contains a set.

The handler will be used to determine the value of the 'underlying' in the example above. Likewise when inserting data it will look in the handler::table to determine the ID of the underling we want to reference. Then it will use the UID of the underlying to make a reference to our current product such that the following insert statement will be created.

INSERT INTO tb_product_underlying(int_underlying_id, parent_key[0], flo_strike_level ) VALUES( '@underlying_UID', '@parent_key[0]', '@level')

Multiple Fields: Multiple Set

To separate our branch records across multiple fields the have the same name and used to express another instance of a dataset.

E.g. We want to display

Knockout (date:level:payout) column headers:

Knockout 1 (Date:Level:Payout) Knockout 2 (Date:Level:Payout) Knockout … (Date:Level:Payout) Knockout n (Date:Level:Payout)

This is known as a multiple set because whilst in our example there is only one instance in each field. The content is similar to Single Field:Multiple Set, only we don’t need to specify a delimiter. But we will treat it the same.

In the example, the TAB content of one field being Knockout 1:n may contain something like:

21/05/2007:0:103.375

To process this we need, to collect all instances of the columns prefixed with Knockout followed by an integer.

The following process is alike Single Field: Multiple Set

In the process we wish to

  • Validate all the set sub items separately as date:float:float.
  • Create records in a relational table 1:n - Referencing the ‘schema_key’
    'Knockout' => array
(
'delimiter' => MULTIPLE_FIELDS,
'sub-delimit' => ':',
'type' => array
(
'date' => INPUT_DATE,
'level' => INPUT_FLOAT,
'payout'=> INPUT_FLOAT
),
'table' => 'tb_knockout',
'field' => array
(
'dat_knockout_date' => '@date',
'flo_perc_level' => '@level',
'flo_perc_payout' => '@underlying'
)
),

In our knockout example it is not necessary to specify a handler since the table does not reference another, besides tb_product.

The delimiter is assigned a constant MULTIPLE_FIELDS to tell the schema to find all TAB fields which have the prefix of this schema entry row key .i.e. in our example it is Knockout.

Multiple Fields: Multiple x2 Set

If TAB multiple fields have the same name and used to express another instance of a dataset. And those fields intern have sets within them, so that there are 3 types of delimiting.

This is best represented as a case study of the ISIN code.

E.g.

The TAB fields are for the ISIN code have two dimensions, whilst the inner content of the wrapper has another. So we can say the total number of dimensions is 3 and we need to specify the third….

The TAB columns for the ISIN code look like:

Code ISIN 1 Code Code ISIN 1 Wrapper Code ISIN 1 Volume Code ISIN 1 Status Code ISIN ... Code Code ISIN ... Wrapper Code ISIN ... Volume Code ISIN ... Status
"Code ISIN" 	=> array
(
"group" => "Details",
"delimiter" => MULTIPLE_CELLS,
"sub-delimiter" => DELIMITER_FIELD,
"type" => array
(
"code" => INPUT_ISIN | INPUT_NOTNULL,
"wrapper" => INPUT_VARCHAR,
"volume" => INPUT_FLOAT,
"status" => "A|E"
),

"table" => T_PRODUCT_ISIN,
"field" => array
(
"var_isin" => "@code",
"enu_act_or_est"=> "@status",
"flo_volume" => "@volume"
),

/**
* Branch
* The distinction in the handler and the branch is the way it links to the tables.
* The handler uses a link which is a many:one link from the main table or row.table
* Where as a branch table is a one:many relationship with the current selected table.
* for this reason it is treated seperatly.
* In this example our db schema looks like
*
* key: -> (one:many), <- ( many:one )
*
* tb_products -> tb_product_isin -> tb_product_isin_wrapper <- tb_wrapper
*/

"branch" => array
(
"wrapper" => array
(
"delimiter" => DELIMITER_RECORD,
"type" => INPUT_VARCHAR,
"table" => T_PRODUCT_ISIN_WRAPPER,
"handler" => array
(
array
(
"key" => "int_wrapper_id",
"key_name" => "var_wrapper_full_name",
"table" => T_WRAPPER,
"create" => true,
"alias" => array
(
"table" => T_WRAPPER_ALIAS,
"key" => "int_wrapper_id",
"key_name" => "var_name"
)
)
),
"parent_keys" => array
(
"int_product_id",
"var_isin"
)
)
)
),

In this example we have used the ‘branch’ array to handle the inserting into the second table (tb_product_code_wrapper ) We have parsed each iteration of the parameters from the initial settings into our new branch by defining the variables to pass by the pass parameter.

Another feature different from the previous examples is in the type array where each element has explicitly assigned strings for keys, and thus where we reference the variables we can use these keys. It also serves a greater purpose. The key values associate ( by a case insensitive match ) with the sub-delimited items e.g. “ISIN Code integer keys” where keys in our example is one of “Code”, “Wrapper”, “Volume” or “Status”. By default the keys are assumed to be 1, 2, 3 ,… etc.

/** Alternatively: we can, use the handler_func. Deprecated Avoiding the complexity of handling this function by passing ‘branch’ We can send to an external function isin_wrapper_func with the variables @code, @wrapper. */

By omitting the handler_ref, calling the handler will not force the returning value into our current Query build.

Multiple Fields: Single Set

Multiple fields correspond to a single record in a table

Example

company:name company:group company:type
"Issuer"			=> array
(
"group" => "General",
"sub-delimiter" => MULTIPLE_CELLS,

"field" => "int_company_id",

"type" => array
(
'name' => INPUT_VARCHAR | INPUT_AJAX,
'group' => INPUT_VARCHAR | INPUT_AJAX,
'type' => INPUT_VARCHAR
),

"handler" => array
(
"name" => array
(
"key_name" => "var_company_name",
"key" => "int_company_id",
"table" => T_COMPANY,
"create" => true,
"alias" => array
(
"key" => "int_company_id",
"key_name" => "var_name",
"table" => T_COMPANY_ALIAS
),
"handler" => array
(
'group' => array
(
"key_name" => "var_company_group_name",
"key" => "int_company_group_id",
"table" => T_COMPANY_GROUP,
"create" => true,
"alias" => array
(
"key" => "int_company_group_id",
"key_name" => "var_name",
"table" => T_COMPANY_GROUP_ALIAS
),
),
)
)
)
),

Rules, and validation checking

When uploading datasets the data is validated on three criteria.

  1. Checked format. Each item of data is checked dependent on the value you define in the 'type' attribute. Additionally if the type attribute is given a "INPUT_NOTNULL" parameter then any value which is empty "" will return an error. See input_regex() for understanding these constants. And how to include this.

Example. This will match a value 'A' or 'E', and treats the definition of 'type' as a regular expression.

    'type' => 'A|E'
  1. Check existence. If the data item label is defined in handler. The corresponding key for the item will be sought in the table defined in the that handler. Should the data items not exist and the 'create' attribute is absent from the handler tag. Then the result causes a a critical error. Should the data items not exist and the 'create' attribute is available then the result will return a report. Because upon processing the item in the handler will be created.

Example. This will build the query.

    'handler' => array
(
'table' => 'tb_category',
'key' => 'int_category_id',
'field' => 'var_name'
);

When we define our value as 'Offshore' and it is handled by the above configuration. It'll build our query.

SELECT int_category_id FROM tb_category WHERE var_name LIKE 'Offshore';
  1. Quantity. Setting the 'max' and 'min' attribute on a branch where the 'table' is defined. Will ensure that the data being inserted is within these bounds.

Checking Format

All user defined values are validated by a corresponding type attribute in the information schema. This can either be an INPUT_constant or a regular expression. When we come to pass the information in the schema, the validation of a user insert it checked corresponding to the value given. By a function input_regex() which is global defined.

When processing user data the type and the value are passed to input_regex(). This function does several things.

  1. Determines what attributes the value must have.
  2. Performs any rudimentary trimming spaces, seperation or merging. e.g. If the value is INPUT_MATCH there should be two values in an array and a comparision in checked. Or if the value is multiple seperated the value is exploded by the default seperator.
  3. Then the values are checked against a regular expression. Corresponding to the type parameter if this is a string then it is treated like it is a regular expression if it is an integer it is treated like a tag( see list below) and a matching regular expression is used.
  4. If the regular expression matches the function returns boolean true. And the $message returns "Successful". Otherwise the function returns boolean false. And the message returns is related to the specific regular expression that the parameter failed on.

All values are trimmed of white spaces. And depending on the type the value maybe modified. e.g. the order of dd-mm-yyyy may be changed to work with the database, yyyy-mm-dd.

Example

input_regex(''type'', ''value'', ''message'');

The INPUT_constants corresponding regular expressions formed.

        /**
* Loop through the following array. Create a constant based upon "INPUT_" and the key.
*/

$arr_link = array
(
"VARCHAR" => ".+",
"INTEGER" => "-?[0-9]+",
"STRING" => "[a-z0-9]+",
"DATE" => "([0-9]{1,2})/([0-9]{1,2})/([0-9]{2,4})", /** DD/MM/YYYY */
"DATETIME" => "[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}( [0-9]{2}:[0-9]{2})?", /** DD/MM/YYYY HH:ii*/
"CUSIP" => "[a-z0-9]{9,10}",
"ISIN" => "[a-z]{2}[0-9][a-z0-9]{9}",
"YESNO" => "yes|no",
"FLOAT" => "[-£$ ]*[0-9]+.?[0-9]*[ %]*",
"URL" => "(http[s]?://)?([a-z0-9.-]+(:[0-9]+)?)(/[a-z0-9./_-]*)?",
"EMAIL" => "[a-z0-9._-]+@[a-z0-9.-]+.[a-z.]{2,6} ?(([^)]+))?",
"NOTNULL" => ".+",
"TEXT" => ".+",
"KEY" => NULL,
"AJAX" => NULL,
"AJAX_POST_ROW" => NULL,
"ENUM" => NULL,
"FILE" => NULL,
"MULTI" => NULL,
"SELECT" => ".+",
"RADIOS" => NULL,
"CHECKBOX" => NULL,
"TIME" => NULL,
"EDITOR" => NULL,
"CATEGORY" => NULL,
"FIXED" => NULL,
"PASSWORD" => NULL,
"HIDDEN" => NULL,
"UNIQUE" => NULL,
"CONFIRMATION" => NULL,
);

/**
* Error messages for each of the data types.
*/
$arr_report = array
(
INPUT_VARCHAR => "text",
INPUT_INTEGER => "an integer",
INPUT_FLOAT => "a decimal number",
INPUT_STRING => "a string",
INPUT_EMAIL => "an email address",
INPUT_URL => "a URL",
INPUT_YESNO => "either 'yes' or 'no' boolean",
INPUT_CUSIP => "a CUSIP code",
INPUT_ISIN => "an ISIN code",
INPUT_DATE => "a Date",
INPUT_NOTNULL => "non-empty",
);

Checking Handler Existence

This is wrapped up in obtaining the integer key reference of a handler. Such that it is out of the scope of this section see function schema_upload::handler()

Checking Min and Maximum

This is achieved through the function schema_upload::validate_business_rules() however this is not really a good name. The simplicity of checking the minimum and maximum of a new product being inserted is balanced with frustration of checking the value is not being completely removed when it is being inserted. This is awesome.

Processing tools for the information schema

There are some core functions to manage our dataset.

These core tools are described in brief here:

schema_view class

  • file/s:
    • schema/sch.obj.php
  • public methods:
    • execute_view( $id int )
      • Get an item defined by a unique ID. Returns a structured array of the product data.
    • execute_search()

schema_search class

  • file/s:
    • schema/search.sch.obj.php main
    • schema/sch.obj.php

schema_search_dowload class

  • file/s:
    • schema/download.search.sch.obj.php main
    • schema/search.sch.obj.php
    • schema/sch.obj.php

schema_upload class

  • file/s:
    • schema/upload.sch.obj.php main
    • schema/sch.obj.php
  • public methods

schema_upload_flatfile class

  • file/s:
    • schema/flatfile.upload.sch.obj.php
    • schema/upload.sch.obj.php
    • schema/sch.obj.php
  • public methods

These tools

View Tools

The view tool is at the core of the schema tools and is contained within the file.

Technical

Edit Process

Editing a product

When a product item is edited the process goes through the following steps

  1. What has changed? Compare the data given with the data that already exists. Create an indexed 'process' array.
  2. Validate each new element of the process array against type(described in Schema)
  3. Gather the associate key values based on the names given.
  4. Gather the key(s) from the original value (matched by index) And delete entry. If empty: delete, or if not empty: update

The Difference Array

Note: if the product is a new insert this step is skipped

When a product is edited the data through three phases of transmorphism. Based upon what is happening. If you look carefully you'll notice the four things that happen to data. Adding, Changing, Deleting and Nothing.

  • If an item is being added/updated both the key and the value remain included
  • If an item is being deleted both the key is included and the value is NULL or ""
  • If nothing is happening to the element then the value is removed, see striked out elements in the difference array.
Original Item Array
Array
(
[ID Number] => 1033
[Issuer] => Array
(
[name] => Lloyds TSB Life
)
[Product name] => Safety Plus Growth Issue 93
[Market] => Array
(
[0] => Array
(
[country] => Europe
[wrapper] => Array
(
[0] => Warrants
)
[local-payoff] => Array
(
[0] => Early Redemption
)
[currency] => Array
(
[0] => MYR
)
)
)
[Category] => Array
(
[0] => Retail
)
[Product Style] => Continuous
[Product Type] => Income
)

New Item Array
Array
(
[ID Number] => 1033
[Issuer] => Array
(
[name] => Lloyds TSB Life
)
[Product name] => Safety Plus Growth Issue 93
[Market] => Array
(
[0] => Array
(
[country] => Europe
[wrapper] => Array
(
[0] => Warrants
[1] => Wrapper
)

[local-payoff] => Array
(
[0] => Late Redemptions
)
)
)
[Category] => Array
(
[0] =>
[1] => Offshore
)
[Product Style] => Tranche
[Product Type] => Income
)
Difference Array
Array
(
[ID Number] => 1033
[Issuer] => Array
(
[name] => Lloyds TSB Life
)

[Product name] => Safety Plus Growth Issue 93
[Market] => Array
(
[0] => Array
(
[country] => Europe
[wrapper] => Array
(
[0] => Warrants
[1] => Wrapper
)

[local-payoff] => Array
(
[0] => Late Redemptions
)
[currency] => Array
(
[0] => MYR
)
)
)
[Category] => Array
(
[0] => Retail
[1] => Offshore
)
[Product Style] => Tranche
[Product Type] => Income
)

In this example we have

  • Removed: Market -> Currency
  • Added: Market -> Wrapper Wrapper
  • Edited: market -> Local payoff: Early Redemption to Late Redemption
  • Removed: Category Retail and then added Offshore
  • Edited: Product Style: Continuous to Tranche

Validate the Values Given by the new Array

Check the types of the values listed the Schema. Gather the Wrapper and Local-Payoff handler ID's. If these are new handlers (wrappers, payoff's), then attempt to create).

Additionally check the rules. Are we allowed to remove all the currency's? Must we have Product Type defined. Inserting a new item it is important to check that the inserted array has all these features. Whilst when editing an existing it is not improtant to check the values which are ommitted (because they are not changed). Generate the SQL process Array

The SQL process array is a structured data array. It looks like the difference array above but holds table key index information and in place of handler names it holds their UID values.

Keys can be either:

  • a numerical reference to a record in a recordset. E.g. Wrapper1, Wrapper2, etc.. These will always have a value of Array or NULL(record highlighted for deletion)
  • a column name, the value will be a scalar.
  • a JTABLE which means that the array is a list of tables as keys with table record data as a values.
  • a table name if its parent node was a JTABLE

This is described below.

Array
(
[JTABLES] => Array
(
[db_product.tb_product_market] => Array
(
[0] => Array
(
[JTABLES] => Array
(
[db_product.tb_product_market_wrapper] => Array
(
[1] => Array
(
[int_wrapper_id] => 613
[int_product_id] => @int_product_id
[int_market_id] => @int_market_id
)
)
[db_product.tb_product_market_payoff] => Array
(
[0] => Array
(
[int_payoff_id] => 123
[int_product_id] => @int_product_id
[int_market_id] => @int_market_id
)
)
[db_product.tb_product_market_currency] => Array
(
[0] => Array
(
[int_currency_id] =>
[int_product_id] => @int_product_id
[int_market_id] => @int_market_id
)
)
)
)
)
[db_product.tb_product_category] => Array
(
[0] => Array
(
[int_category_id] =
[int_product_id] = @int_product_id
)
[1] => Array
(
[int_category_id] = 4
[int_product_id] = @int_product_id
)
)
)
[enu_style] => Continuous
)

Processing SQL data array

This SQL array hold the information as to what is changing and what is being deleted. As the array is recursed the the keys which are prefixed with an "@" are derived from the parent data column value. If the value is @parent_id then the Last_insert_id is called and that value is inserted.

The recursive process

  1. Update the initial schema table "db_product.tb_product". All record values using the Schema ID, Schema Key Name and the Schema Table
  2. Obtain the UID by reference (given as a parameter), or Auto_Increment ( last_insert_id). Save the array(key_column_name => key_value)
  3. Loop through the JTABLES array. If a resultset array has an integer parent key this indicates that it is part of a multiple recordset. To determine the unique key ID of the recordset which is being updated. The Original item Array is parsed through to create_SQL_array() function. The table and key are matched and the values from the fields are used to either make a SQL search to find the Unique ID (UID) of the record, or if the key is provided then just read from the original data. This method has a problem*
  4. Using the derive key. Update delete as appropriate. Repeat step 3.

* Relies on the mysql being consistent in the order it returns the record set.

Uploading of Spreadsheet - change only certain fields.

In the spreadsheet example the nature of the upload is to only include the fields which the user wishes to change.

if the original data represents our total data about a product. When we compare datasets. We are not trimming the dataset of null values.

Original Item Array
Array
(
[ID Number] => 1033
[Issuer] => Array
(
[name] => Lloyds TSB Life
)
[Product name] => Safety Plus Growth Issue 93
[Description] => Array
(
[0] => Array
(
"language" => "English"
"text" => "Something New"
)
)
)
New Item Array
Array
(
[ID Number] => 1033
[Description] => Array
(
[0] => Array
(
text => Something New
)
)
)
Difference Array - only where keys exists
Array
(
[ID Number] => 1033
[Issuer] => Array
(
[name] => Lloyds TSB Life
)

[Product name] => Safety Plus Growth Issue 93
[Description] => Array
(
[0] => Array
(
language => English
text => Something New
)
)
)

Insert vs Update Rule

A product INSERT will be set if there is no ID number in the products TAB sheet.

The following columns must be present and the field value must not be NULL.

1. Product Name 2. Company name 3. Company Group 4. Payoff Type 5. Description 6. Category 7. Target Market 8. Product Style 9. Product Type 10. Underlying 11. Volume Sold in US dollar (table tb_product)

To UPDATE a product an ID value must be present in the ID Field. Additionally there must be one other column which contains a field to be updated.

Error checking and Value Types Field values are defined as a type according to the schema type definition.

They may be referenced by using the “INPUT_name” constants or by string explicitly stating the regular expression.

E.g. Levels Status can be either “A” or “E”.

"Levels status" => array(

"name" => "enu_strike_levels_type",

"type" => "A|E" ),

If there are multiple fields where data is being inserted, then the ‘type’ may be an array of different types. E.g…

"type" => array(

"language" => INPUT_VARCHAR, "text" => INPUT_VARCHAR),

In such cases the value can be referenced using the new key identifier, prefixed by an @ symbol, E.g. “SELECT int_language_id FROM tb_language WHERE var_language_name = '@language'”

Defining field Types: Adding information to a database field. The generic INPUT_something constants are defined in (function) input_regex.

Input_regex is a function used by the toolset, for validating data and formatting certain input data to work with MySQL. Since this is not limited to the toolset, it is maintained outside the scope of the schema mapping. See def.inc.php.


 


Here is a list of the constants => regular expression which we can reference in our information schema.
     
    "VARCHAR"    => ".+",
    "INTEGER"    => "-?[0-9]+",
    "STRING"    => "[a-z0-9]+",
    "DATE"    => "([1-9]|0[1-9]|[12][0-9]|3[01])/([1-9]|0[1-9]|1[012])/(19[0-9][0-9]|20[0-9][0-9])", /** DD/MM/YYYY */
    "DATETIME"    => "([1-9]|0[1-9]|[12][0-9]|3[01])/([1-9]|0[1-9]|1[012])/(19[0-9][0-9]|20[0-9][0-9])( [0-9]{2}:[0-9]{2})?",
    "CUSIP"    => "[a-z0-9]{9,10}",
    "ISIN"        => "[a-z]{2}[0-9][a-z0-9]{9}",
    "YESNO"    => "yes|no",
    "FLOAT"    => "[-£$ ]*[0-9]+.?[0-9]*[ %]*",
    "URL"        => "(http[s]?://)?([a-z0-9.-]+(:[0-9]+)?)(/[a-z0-9./_-]*)?",
    "EMAIL"    => "[a-z0-9._-]+@[a-z0-9.-]+.[a-z.]{2,6} ?(([^)]+))?",
    "NOTNULL"    => ".+",
    "TEXT"        => ".+",
    "KEY"        => NULL,
    "AJAX"        => NULL,
    "AJAX_POST_ROW"    => NULL,
    "ENUM"    => NULL,
    "FILE"        => NULL,
    "MULTI"    => NULL,
    "SELECT"    => ".+",
    "RADIOS"    => NULL,
    "CHECKBOX"    => NULL,
    "TIME"        => NULL,
    "EDITOR"    => NULL,
    "CATEGORY"    => NULL,
    "FIXED"    => NULL,
    "PASSWORD"    => NULL,
    "HIDDEN"  = > NULL,

}

Constants with NULL values provide additional information which is not regular expression checking validation.



Data modification validation and Reporting
Data modification, data is checked against its corresponding type. Using the aforementioned function input_regex. Details of the exception are returned from input_regex in the third referential variable. Messages are stored in $class->exceptions. We then use code from the procedural script on how to handle these error messages. In our process for adds and edits on the web interface and the server: both implementations continue to validate all parameters being submitted.  But do not manipulate the actual data unless there are no exceptions.





Processing Multiple Add/Edit Products (Server).


I.e. for any given data upload what is the… ?
1.    Number of products Inserted/Updated so far
2.    Number of products left to be Inserted/Updated
3.    Number of products failed to be inserted/updated
4.    Person who initiated the upload
5.    Errors
a.    Row
b.    Column
c.    Reason/Description
d.    Notification alert ( email )


Steps involved in processing the dataset.
1.    A spreadsheet is uploaded. Is the upload a correctly formatted TAB file?
a.    Yes, continue.
i.    Move uploaded file to a new location
ii.    Call background script.
b.    No, return an error to the browser. Halt.
2.    Process files in the background
3.    Read file. Grab column headers, and assign to line position.
4.    Validate content of each line and field, against the set of rules?
a.    On fail: Report Error, go to next line.
b.    On Success continue.
5.    Insert or Update row in table.

Steps explained.
1. Form based Spreadsheet upload.

Validate
1.    File is TAB/text from the file type attribute.
2.    The number of rows in the TAB file does not exceed a given number.
3.    The first line contains two or more fields.
4.    The total number of rows is equal to two or more.

On fail: Display an error message to the browser.

Move the uploaded file to the processing path.

    /data/webroot/shared/uploads/new/



Call a script in the background. See db1:crontab -e
Go to next step


2. Process file in the background

Continue if the process is not already running. (see global.ini.php ).


1.    Find a file in the processing path on the server. Halt if no files exist.
2.    Initiate file processing of the file.
3.    Go to next step
4.    Repeat. 1



3. Read file, Schema processing.


TAB data is merged with the array schema.

There are various different types of fields in the TAB file. A Single Field can contain scalar, or multiple data whilst a Series of Data may stretch across many TAB fields.

Every process for handling the TAB data is described by the schema. ( A single multidimensional array ).

The schema describes the fields that are in the TAB file and defines how to map these to the values to the database.




Setting user Variables
User variables are set in the schema and prefixed with ‘@@’ so the process knows to look for the variable definitions in  $this->variables(array)

This was introduced to add information about the user who updated the information.

Comments

wq123
This hilfiger outlet time swarovski jewelry something really timberland outlet strange, ...
Created 30/06/16
ninestab123
ninest123 One canada goose pas cher gucci outlet thing
Created 21/07/16
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share