Transportation Administration System
Snoezelen Pillows for Dementia
Begeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen | Clean-Up Lasso 8 and MySQLHow to remove dynamically created MySQL tables that remain forever in Lasso 8 |  |
This how-to is about how to cleanup the internal Lasso 8 host 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 created, being 'temporary' for a webpage or query.
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.
Tweet
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:
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:
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
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):
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;
?>
