Introduction to Inlines

Tour guide: Jolle Carlestam

This is a summary of a presentation held at the LDC in Amsterdam 2009. It's a quick tour on what you can, can't, should and shouldn't do with inlines and Lasso.

You use inlines for your database interactions and for file access and file manipulation. This tutorial focuses on interactions with databases. Feel free to add information on how to use them for file actions.

Here's the most basic inline available. The syntax is correct and it's executable. Won't do much good since it actually doesn't do anything. Run it and it will say "hello world" on your screen. Remove the inline and it will still say "hello world".

inline();
	'hello world';
/inline;

To make use of the inline we need to give it some kind of action. The most useful database actions you can do with an inline are:

inline(
	-add
	-update
	-delete
	-search
	-findall
	-sql // <- Powerful but dangerous!
);
/inline;

 

But, it's not enough to give an inline an action. Some other basic params are needed as well. The inline want to make sure you're allowed to do whatever it is you want to do. So you need to show credentials. The username and password provided in the inline are checked against the users entered in the site admin. It determines what actions are allowed for the user.

Stuff needed for any database action to perform:

inline(
   -username    = 'mysecretname',
   -password    = 'mysecretpassword',
   -database    = 'mydatabase',
   -table       = 'mytable'
   );

/inline;

 

Keep secrets secret

Use an array for secrets and basic info
As soon as you start building a solution there will be inlines on almost every page. Used for searching or manipulating information in your database. It's not such a good idea to hardcode your user credentials on all these places. Luckily Lasso provides a solution for that. Inlines accept arrays with name/value pairs as params. So you can store your secret credential info and other common stuff in an array and insert it into inlines wherever you need them.

var('inlineconfig' = array(
   -username    = 'mysecretname',
   -password    = 'mysecretpassword',
   -database    = 'mydatabase',
   -table       = 'mytable'
));

inline(
   $inlineconfig
   );

/inline;

 

Only, storing the config array in the same page as the inline doesn't do much good. Better to put the secret and repetitive info in a config page stored in a secure place and call it when needed. You call an include page using, that's right, include!
The array above is in this case stored in a page called global__config.inc and called like this: include('../_cfg/global__config.inc');

inline(
   $inlineconfig
   );

/inline;

 

It's furthermore a good idea to make it hard to get access to the include file for spooks and evil visitors. I have Apache configured to refuse serving files ending with ".inc". And also prohibit access to anything in folders starting with an underscore. That way it's impossible to get access to the content of the file without having it processed by Lasso first.

 

Override the array

Another smart thing with inlines and array is that it's easy to override the default params in the array when needed. When Lasso encounters several params of the same kind it uses the last one found. By placing the config array first in the inline we can override specific settings by entering them again. In this example there's a table name "mytable" set in the array. But the inline will use the table "another_table" since it's set later in the inline.

var('inlineconfig' = array(
   -username    = 'mysecretname',
   -password    = 'mysecretpassword',
   -database    = 'mydatabase',
   -table       = 'mytable'
));

inline(
   $inlineconfig,
   -table = 'another_table'
   );

/inline;

 

Add a record

To add a record to a table you simply include the fields that should have some content and their content as name/value pairs. Content can be hardcoded, as in this example, supplied by a user or calculated by Lasso. It's also perfectly alrigth to supply the content in an array as in previous examples. I prefer to not use the built in auto incremented record values that most database engines provide. It's a security risk to have records identified by increasing numbers. In my examples I use a unique string created by Lasso. In the tutorial it's lasso_uniqueid. In real life I use knop_unique instead.
include('../_cfg/global__config.inc');

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,
   'keyfield'          = lasso_uniqueid,
   'datetime_create'   = date -> format('%Q %T'),
   'datetime_mod'      = date -> format('%Q %T'),
   'user_create'       = 'LDC Demo',
   'user_mod'          = 'LDC Demo',
   'LDC09DM_char'      = 'Hand me that Sandwich',
   'LDC09DM_smallint'  = math_random(-lower = 1, -upper = 5),
   'LDC09DM_datetime'  = date -> format('%Q %T'),
   'LDC09DM_date'      = date -> format('%Q'),
   'LDC09DM_time'      = date -> format('%T'),
   -add
   );

   error_msg;

/inline;

 

Update a record

Updating is similar to adding. Difference is you need to provide a keyfield and keyvalue so Lasso knows what record to update. You need only provide fields that you actually want to change. Be careful, if you include a field without content it's the same as saying you want that field to be empty.

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,
   -keyfield           ='keyfield', 
   -keyvalue           = '5AE5EA94168f924A54SKm1E30704',
   'datetime_mod'      = date -> format('%Q %T'),
   'user_mod'          = 'Another user',
   'LDC09DM_char'      = 'Ah, that Shrimp Sandwich was tasty!',
   -update
   );

   'datetime_create: ';
   field('datetime_create');
   
   'datetime_mod: ';
   field('datetime_mod');
   
   'LDC09DM_char: ';
   field('LDC09DM_char');

/inline;

 

This is the result from the previous update example. Note that we can get the content from a field that wasn't included in the inline action:
datetime_create: 2009-09-24 15:01:00 datetime_mod: 2009-09-30 14:50:47 LDC09DM_char: Ah, that Shrimp Sandwich was tasty!

Delete inline with nested inline

To remove a record from a table we do as when we update. Provide a keyfield and keyvalue and tell the inline you want to delete the record. In this example you can also see that it's possible to nest inlines in eachother.
The credentials supplied in the first inline carries over to the nested one and need not be repeated.

include('../_cfg/global__config.inc');

var('key' = lasso_uniqueid);
var('date' = date);
inline(
   $inlineconfig,
   'keyfield'          = $key,
   'datetime_create'   = $date -> format('%Q %T'),
   'datetime_mod'      = $date -> format('%Q %T'),
   'user_create'       = 'LDC Demo',
   'user_mod'          = 'LDC Demo',
   'LDC09DM_char'      = 'I'll be deleted soon!',
   -add
   );

   error_code == 0 ? 'A record with id ' +
   $key + ' was created at ' + $date -> format('%Q %T');

  inline(
     -keyfield           ='keyfield', 
     -keyvalue           = $key,
     -delete
  );
      
      error_code == 0 ? 'The record was deleted at ' +  
     date -> format('%Q %T');

  /inline;

/inline;

 

Searching with inlines

Simplest possible search inline. Provide the field(s) you want to search in and the search value as name/value pair(s). Lasso will perform a "Begins with" search on the fields provided.

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,

   'LDC09DM_smallint'   = 1,
   -search

 

This is the result from the above search:

SELECT SQL_CALC_FOUND_ROWS * FROM cojan_se.LDC2009_inlinedemo WHERE (`LDC09DM_smallint` LIKE '1%') LIMIT 0,50 4 keyfield = 5AE5EA94168f92535CSsFF58429D datetime_create = 2009-09-24 15:24:12 datetime_mod = 2009-09-24 15:24:12 user_create = LDC Demo user_mod = LDC Demo lockfield = LDC09DM_char = Hand me that Shrimp Sandwich LDC09DM_smallint = 1 LDC09DM_datetime = 2009-09-24 15:24:12 LDC09DM_date = 2009-09-24 LDC09DM_time = 15:24:12

You may note that it's finding 4 records but only displaying the contents of one. That's as expected. If you don't use a records container Lasso will only display the first record found within the inline.

 

Lets add some more params to the search.

Starting with a field operator. In this case "Not equal to" (neq). The param will only operate on the field that's directly after it. We also add returnfield as a param. This will prevent Lasso from returning fields that we are not interested in having in this particular case. If you have a table with a lot of fields this can speed up the reply a lot. You can have as many returnfield params as needed. Finally we add maxrecords and skiprecords params. The default setting for maxrecords is 50 records. If you don't set a different value you'll only get max 50 records returned by Lasso regardless if the found count is 200. Skiprecords is used when you're paging thru a found set. It allows you to skip the first xx records and retrive the ones following. Say you want to display 20 records per page. Set maxrecords to 20 and skiprecords to 0 on the first search. When the next page is called you bump skiprecords to 20 and keep maxrecords the same. That will give you record 21 - 40. Maxrecords can be an integer -maxrecords = 1,
or it can be 'all'

-maxrecords = 'all',

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,

   -neq,
   'LDC09DM_smallint'   = 1,
   -returnfield = 'LDC09DM_char',
   -maxrecords = 2,
   -skiprecords = 2,
   -search

   );
   action_statement;
   
   error_msg;
   
   found_count;
   
   iterate(field_names, var('name'));
      $name;
      ' = ';
      field($name);
      
   /iterate;

/inline;

 

Possible field operators

As said before the default field operator is "Begins with". Lasso provides a number of other operators to allow you to construct a fine grained search. Field operators work on the field that's directly followed in the inline.

• -bw -> Begins With. Default if no operator is set.
• -cn -> Contains.
• -ew -> Ends With.
• -eq -> Equals.
• -ft -> Full Text. MySQL databases only.
• -gt -> Greater Than.
• -gte -> Greater Than or Equals.
• -lt -> Less Than.
• -lte -> Less Than or Equals.
• -neq -> Not Equals.

 

Handling user supplied values

Inlines would be of little use if they only dealt with hardcoded or calculated values. To deal with user supplied values, from a post or get action, Lasso provides several different helpers.
To get access to get or post params we have: action_params action_param client_getparams client_postparams

They all have issues. Client_getparams and client_postparams are, as their names suggest, restricted to only deal with get respective post params. This is probably the result of some kind of security thinking from the Lassos developers. A way to make sure that if you expect only get params you get only get params and vice versa. This is no security in real life. Evil-doers have no problem masking their data as one or the other. I find I have very little use of these two tags.

The problem with action_param is that it's context sensitive. The content doesn't carry over inside inlines. Try the following example and you can see how the param "example" change when displayed outside and inside the inline.

action_param('example');

inline('example' = 'something else');
	action_param('example');
/inline;

 

The tag action_params is provided partly as a convinient way to include into an inline with all params set automatically based on what's provided from the user. Convinient but dangerous. I prefer to have better control over what goes into my inlines. I've also seen a lot of examples where developers iterate action_params and then have to filter out all the unneccesary stuff that Lasso put in there.

Personally I prefer some tags available on tagswap.net. (Thanks Bil Corry!) lp_client_params and lp_client_param

Try them yourself:

lp_client_params;

lp_client_param('example');

inline('example' = 'something else');
	lp_client_param('example');
/inline;

 

Search inline with user supplied search value

Let's try a search where the user supplies a value for a param "val". Perhaps as part of a form or in this case an URL: letusdoasearch.lasso?val=1

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,

   'LDC09DM_smallint'   = 1,
   -search

   );
   action_statement;
   
   found_count;
   
   iterate(field_names, var('name'));
      $name;
      ' = ';
      field($name);
      
   /iterate;

/inline;

 

Add inline with values from a form

In this example we use the same page to both display the form and deal with the submitted result. The conditional "if(lp_client_param('key'))" kicks in if the form was submitted. If not it draws the form.

if(lp_client_param('key'));
// a form was submitted, add it to the DB

   include('../_cfg/global__config.inc');

   var('key' = lp_client_param('key'));
   var('date' = date);

   inline(
      $inlineconfig,
      'keyfield'          = $key,
      'datetime_create'   = $date -> format('%Q %T'),
      'datetime_mod'      = $date -> format('%Q %T'),
      'user_create'       = 'LDC Demo',
      'user_mod'          = 'LDC Demo',
      'LDC09DM_char'      = lp_client_param('char'),
      'LDC09DM_smallint'  = lp_client_param('int'),
      -add
      );

      error_code == 0 ? 'A record with id ' +
      $key + 
      ' was created at ' + $date -> format('%Q %T') +
      '<br>' +
      'This is the info you supplied:' +
      '<br>' +
      'Text: ' +
      lp_client_param('char') +
      '<br>' +
      'Integer: ' +
      lp_client_param('int') |
      error_msg;

   /inline;
else;
// first time to show page, display a form
   var('key' = lasso_uniqueid);
   '   <form action="demo_search_part7.lasso" method="post"
      id="demoform" name="demoform" target="_blank">
      <input name="key" type="hidden" value="' + $key + '">
      Text:<br>
      <input name="char" type="text" size="25"><br>
      Integer (1-9):<br>
      <input name="int" type="text" size="5" maxlength="1"><br>
      <input name="add_button" type="submit" value="Add">
   </form>';

/if;

 

Handling results using records container

To get hold of the found record set you need to use a records container. Place it inside the inline container and it will loop thru all records returned by the search. By calling the returned fields inside the records container you can display or further process each found record.

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,

   -neq,
   'LDC09DM_smallint'  = lp_client_param('val'),
   -returnfield        = 'LDC09DM_char',
   -maxrecords         = 4,
   -skiprecords        = 4,
   -search

   );
   
   'Found: ' + found_count + ' - Shown: ' + shown_count;
   ' - Skipped: ' + skiprecords_value;
   '<table border="1"><tr>';
   iterate(field_names, var('name'));
      '<th>';
      $name;
      '</th>';
   /iterate;
      '</tr>';

   records;
      '<tr>';
      iterate(field_names, var('name'));
         '<td>';
         field($name);
         '</td>';
      /iterate;
      '</tr>';
   /records;
   '</table>';

/inline;

 

Handling results using -inlinename

Some times it's not practical to deal with the returned records from within the inline. Lasso provides means to access the results from elsewhere by setting an inlinename param in the inline. Don't get into the habit of setting an inlinename on all inlines even when not used. Inlines with an inlinename grabs more memory in order to keep the search result than the ones without. The inlinename is then used again in the records tag to direct it to the specific inline you want.
include('../_cfg/global__config.inc');

include('../_cfg/global__config.inc');

inline(
   $inlineconfig,

   -inlinename         = 'myrecords',
   -neq,
   'LDC09DM_smallint'  = lp_client_param('val'),
   -returnfield        = 'LDC09DM_char',
   -maxrecords         = 4,
   -skiprecords        = 4,
   -search

   );

   // these values are only accessible from within the inline
   var('found' = found_count);
   var('shown' = shown_count);
   var('skipped' = skiprecords_value);
   var('names' = field_names);

/inline;

   
   'Found: ' + $found + ' - Shown: ' + $shown;
   ' - Skipped: ' + $skipped;
'<table border="1"><tr>';
   iterate($names, var('name'));
      '<th>';
      $name;
      '</th>';
   /iterate;
      '</tr>';

   records(-inlinename = 'myrecords');
      '<tr>';
      iterate($names, var('name'));
         '<td>';
         field($name);
         '</td>';
      /iterate;
      '</tr>';
   /records;
   '</table>';

 

Here's the result from the above example: Found: 17 - Shown: 4 - Skipped: 4 LDC09DM_char Hand me that Shrimp Sandwich Hand me that Shrimp Sandwich Hand me that Shrimp Sandwich Hand me that Shrimp Sandwich

SQL queries and resultsets

Sql

Writing your own sql statements can be powerful but is also dangerous! Lasso assumes that if you want to write your own sql then you will also take full responsibility for the consequences. Where regular inlines provide a lot of built in security making it hard for hackers to gain unauthorized access sql inlines bypasses most of that. As long as the user has the right to perform sql queries they are left alone. Even if you for example specify a table in the inline params there's nothing stopping the sql query to manipulate another table within the statement. Regular inlines also prevents sql injections. If you write your own statements it's up to you to secure the user provided data. In the example we do that in two ways. For the first field, LDC09DM_smallint, we only want to deal with integers. So we make sure that the provided param is set to an integer before we use it. Should there be any other values than integers they will vanish and not make it into the query. `LDC09DM_smallint` <> ' + integer(lp_client_param('number')) Next we take care of the param text by applying encode_sql on it. This will prevent sql injection attempts to make it thru. `LDC09DM_char` LIKE "%' + encode_sql(lp_client_param('text'))

 

Resultsets

Notice that there're two sql queries in the provided example. To get access to the results we can't use a records container directly. It will only provide us with the records from one of the queries. Instead Lasso has another container called resultset. By first using a resultset for each query and then a records container inside the resultset we can access both results. Resultsets aren't limited to two queries. You can have as many as is meaningful. As with records tags resultsets can use inlinenames.
Here's the full example:

include('../_cfg/global__config.inc');

var('sql' = 'SELECT SQL_CALC_FOUND_ROWS `LDC09DM_char` AS text
   FROM ' + $database + '.' + $table + 
' WHERE (`LDC09DM_smallint` <> ' + 
integer(lp_client_param('number')) +
') LIMIT 0,50000;r');

$sql += 'SELECT `LDC09DM_char`, LDC09DM_smallint 
   FROM ' + $database + '.' + $table + 
' WHERE (`LDC09DM_char` LIKE "%' + 
encode_sql(lp_client_param('text')) +
'%") LIMIT 0,50000;';


inline(
   $inlineconfig,
   -inlinename    = 'myrecords',

   -maxrecords    = 'all',
   -table         = 'LDC2009_inlinedemo',
   -sql           = $sql

   );

   
/inline;

'First resultset:';
resultset(1, -inlinename = 'myrecords');
   '<table border="1">';
   records;
      '<tr>';
         '<td>';
         field('text');
         '</td>';
      '</tr>';
   /records;
   '</table>';
/resultset;

'Second resultset:'; 
resultset(2, -inlinename = 'myrecords');
   records_array -> join('<br>');
/resultset;

But...

With all is said and done... I rarely use inlines for database interactions. Instead I prefer the framework Knop for the convenience of dealing with the entire chain. So once you are acquainted to inlines, check out Knop and enjoy the ease with how Knop helps you build forms, display results and interact with databases.
Learn more about Knop here: http://www.lassotech.com/knop 

Author: Jolle Carlestam
Created: 30 Sep 2009
Last Modified: 29 Mar 2011