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'
