Software development, photography, jokes, ....

Sites by me

 
tas-logoTransportation Administration System
snoezelkussen-logo-kleinstSnoezelkussens voor verstandelijk gehandicapten
ikzoekeenbegeleider-logoBegeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
logo 50x50Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
nettylogo2Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen
Professionele opvang bij Gastouderbureau
Kind-Zijn
Salarisadministratie en belastingadvies bij
De Zaak Loont
Zutphense Bomenstichting

Hosting Favorites

 
ANU Internet Services
XelMedia .internet Services
register.com

Blogroll

 
Bomenstichting
LassoSoft
MacFreak
Quality that computes
The Economy of Motion
Wheel 2.0
IntrAktv



Website Hosting bij Xel Media

Marc's Place


 

 Clean-Up Lasso 8 and MySQL

  How to remove dynamically created MySQL tables that remain forever in Lasso 8


This how-to is about how to cleanup the internal Lasso 8 Site Database from tables that are in fact temporary. With 'temporary' I mean not the MySQL temporary tables (these do not appear in Lasso 8), but tables that you yourself define as 'temporary'.

In my case this story is about tables that are created when a reporting page is called, coded with Lasso 8. These tables should only exist as long as the cookie on the client-side is valid, thus as long as the user keeps on using this reporting-page.


The Problem

If the client-side does nothing for 24hrs, the cookie expires and so should the table that was created for that user. So far nothing new. But ... during those 24 hours, Lasso 8 picks up these newly created tables and adds them to its internal database, where they can be seen under Setup -> Data Sources -> Tables:

lasso-8-table-listing

After a week or a month there are +100 entries of such tables in here. And the problem is that when you delete these tables from MySQL, they remain in Lasso 8’s internal tables, forever. Unless I do something about it. Since there is no quick way in Lasso 8 SiteAdmin to remove these tables, how do we get rid of them?


The Solution

I wrote a Lasso script that gets rid of these tables in Lasso 8 and MySQL at the same time. I analyzed the table structure of the Lasso 8 internal database, which is a SQLite database. In Navicat Premium, you can add this database to the list and re-engineer it into a model:

lasso-8-navicat-props

The Database File to select here, can, for example, be found at:

/Applications/Lasso Professional 8/LassoSites/default-1/SQLiteDBs/lasso_internal

Of course, if you have more than one Lasso Site configured, you need to select the one you want automatically cleaned up.

Then I could access the data and look how the references to the tables are organized. I found out that I only need to delete data from the following three tables:
  • security_database_tables
  • security_table_fields
  • security_group_table_map
The relations between these tables are shown in this model, made in Navicat:

lasso-8-default-site-model
So the first thing to do was to look for my temporary tables and get some data:

select
     d.name as dbname,
     t.id as tblid,
     t.name as tblname
from
     security_database_tables t
join security_datasource_databases d on d.id=t.id_database
where
     t.name like 'REP%'
order by
     d.name, t.id

Now I have a database name (dbname) and with this result, I can check whether any of these REP%-tables still exist in MySQL and when the last update of each has taken place. The way to directly access MySQL from within Lasso is by using the -host parameter:

-host=array(-datasource='mysqlds', -name='127.0.0.1', -username='lasso', -password='secret')

Note: To be able to drop tables in the databases that the script will access, you need a user in MySQL, who is allowed to do that for these databases. The username and password from that user must be used in the -host array, as shown. In this example the MySQL user / password combo is lasso / secret.

And add a 'show table status’-query to the inline-statement:

show table status from " + field('dbname') + " like 'REP%'";

The output of this query goes into a map-type, so it can be accessed quickly later on.

var('in_mysql' = records_map);

The next step is to loop through the rows of the first query and check the map if the file is also present in MySQL. If not, delete the settings from Lasso.
Otherwise, I decided that if any of these REP%-tables still exist, their update-date must not be older than 2 days. If older, delete them from MySQL and from Lasso.

In the end, add the script to Lasso 8’s Event Queue (Utility -> Events -> Schedule Event) and let it run once a week (or once per day if you like):

lasso-8-event-queue

Here is the complete Lasso-script:
<?LassoScript

auth_admin; // You really need admin privileges

var('zzsql' = '',
   'svdb' = '',
   'delflag' = false
   );

// Get the list of TEMP tables currently in Lasso
$zzsql = "
   select
      d.name as dbname,
      t.id as tblid,
      t.name as tblname
   from security_database_tables t
   join security_datasource_databases d on d.id=t.id_database
   where
      t.name like 'REP%'
   order by
      d.name, t.id";
   
inline(-database='lasso_internal', -sql=$zzsql, -maxrecords='all');
   records;
      $delflag = false;
      
      if($svdb != field('dbname'));
         // Get the list of tables currently in MySQL + their last update date
         $zzsql = "
            show table status
            from " + field('dbname') + "
            like 'REP%'";
            
         inline(-host=array(-datasource='mysqlds', -name='127.0.0.1', -username='lasso', -password='secret'), -sql=$zzsql, -maxrecords='all');
            var('in_mysql' = records_map);
         /inline;
         
         $svdb = field('dbname');
      /if;
      
      // Check if Lasso-defined table still present in MySQL.
      // If not, delete all references from Lasso
      if($in_mysql->find(field('tblname'))->size == 0);
         $delflag = true;
      else(date_difference($in_mysql->find(field('tblname'))->find('Update_time'), date, -day) < -2);   // Older than 2 days = delete
         // Remove table from MySQL
         $zzsql = 'drop table ' + field('dbname') + '.' + field('tblname');
         inline(-host=array(-datasource='mysqlds', -name='127.0.0.1', -username='lasso', -password='secret'), -sql=$zzsql);
         /inline;

         $delflag = true;
      /if;
      
      if($delflag);
         // Delete all security_table_fields entries
         $zzsql = 'delete from security_table_fields where id_table=' + field('tblid');
         inline(-sql=$zzsql); /inline;
         
         // Delete all security_group_table_map entries
         $zzsql = 'delete from security_group_table_map where id_table=' + field('tblid');
         inline(-sql=$zzsql); /inline;
         
         // Delete the security_database_tables entry
         $zzsql = 'delete from security_database_tables where id=' + field('tblid');
         inline(-sql=$zzsql); /inline;
      /if;
   /records;
/inline;

?>

© 1997- Marc Vos (and others) Contact Me