Wednesday, May 14, 2008

Automating Processes by observing the Pattern

It's been said time and again that The REAL Programmer automates his job that they are Long Term Life Planners and have Attention to Detail

Here is just an example of ways in which one can automate processes.

I use Seagull Framework for developing faster, secure and feature rich Web based Applications Today, I had to update the Database to fill some blank columns in a table named sgl_permision

I first tried to edit them through the Seagull's Web based Admin Front end It was taking too long.
Then I tried to edit the same using phpMyAdmin setting the result to go to edit the next page instead of going back to the listing Page still it was very time consuming..

Then I decided to write SQL Queries by Observing the Pattern and using that to my rescue.

Here is the SQL Queries that I wrote to solve the Problem and am documenting so that it can be used in future.

/* To use a Manager */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to use ', TRIM(TRAILING 'mgr' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr'

/* To add a new entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to add a new ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_add' FROM `name`)), ' through ', TRIM(TRAILING 'mgr_cmd_add' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_add'

/* To view the listing of entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to view the listing of ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_list' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_list' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_list'

/* To insert a new entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to insert new ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_insert' FROM `name`)), ' through ', TRIM(TRAILING 'mgr_cmd_insert' FROM `name`), ' Manager in the Database')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_insert'

/* To edit an existing entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to edit an existing ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_edit' FROM `name`)), ' through ', TRIM(TRAILING 'mgr_cmd_edit' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_edit'

/* To update an existing entity in the database */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to update an existing ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_update' FROM `name`)), ' through ', TRIM(TRAILING 'mgr_cmd_update' FROM `name`), ' Manager in the Database')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_update'

/* To delete an entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to delete ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_delete' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_delete' FROM `name`), ' Manager from the Database')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_delete'

/* To archive an entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to archive ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_archive' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_archive' FROM `name`), ' Manager in the Database')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_archive'

/* To View an entity in detail */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to view ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_view' FROM `name`)), ' through ', TRIM(TRAILING 'mgr_cmd_view' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_view'

/* To create a duplicate of an entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to create a duplicate of ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_duplicate' FROM `name`)), ' through ', TRIM(TRAILING 'mgr_cmd_duplicate' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_duplicate'

/* To reorder entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to reorder ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_reorder' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_reorder' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_reorder'

/* To view an overview of an entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to view an overview of ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_overview' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_overview' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_overview'

/* To view a summary of an entity */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to view an summary of ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_summary' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_summary' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_summary'

/* To edit all entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to edit all ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_editAll' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_editAll' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_editAll'

/* To Update all entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to update all ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_updateAll' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_updateAll' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_updateAll'

/* Scan all Orphaned entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to scan all Orphaned ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_scanOrphaned' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_scanOrphaned' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_scanOrphaned'

/* Delete all Orphaned entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to delete all Orphaned ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_deleteOrphaned' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_deleteOrphaned' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_deleteOrphaned'

/* Scan to detect new Entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to scan and detect for new ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_scanNew' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_scanNew' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_scanNew'

/* To insert newly detected Entities */
UPDATE `sgl_permission`
SET `description` = CONCAT('Permission to insert new ', TRIM(LEADING 'admin' FROM TRIM(TRAILING 'mgr_cmd_insertNew' FROM `name`)), '(s) through ', TRIM(TRAILING 'mgr_cmd_insertNew' FROM `name`), ' Manager')
WHERE (`description` = '' OR `description` IS NULL)
AND `name` LIKE '%mgr\_cmd\_insertNew'

5 comments:

RRave said...

Dear Sir,

I hope you are doing well. I got this email address from one of your contribution web site. I have launched a web site www.codegain.com and it is basically aimed C#,JAVA,VB.NET,ASP.NET,AJAX,Sql Server,Oracle,WPF,WCF and etc resources, programming help, articles, code snippet, video demonstrations and problems solving support. I would like to invite you as an author and a supporter.
Looking forward to hearing from you and hope you will join with us soon.

Thank you
RRaveen
Founder CodeGain.com

Nadia said...

Thank you for this posting.....the lamp shade must be nice on it.
Tropical Lamps

nago said...

interesting blog. It would be great if you can provide more details about it. Thanks you



Web Development

Unknown said...

Amazing features of it inhibit many points that give the staring frame in comparison with other languages like Java.Net and many others. Our PHP Development Company is a part of it, and is very practical in the open informant development like Magento development

Unknown said...

Hey!
What a commendable work you have done, with simplest of language.I am obliged that your research has made my work very much easy and simple. I can’t resist myself to leave a comment and trust me it’s hard to impress me.

Vachel
.NET Development Chicago
PHP Developer Chicago
cmscentral.net