Useful SQL database queries

From Globalsight
Jump to navigation Jump to search
Developers
Go to: Getting Started Guide
Footer-logo.gif
System requirements

Installing GlobalSight
3rd party comps: Windows | Ubuntu
GlobalSight: Windows | Ubuntu
Desktop Icon

Setting up GlobalSight
Running GlobalSight Behind an Apache Reverse Proxy

Developing GlobalSight
GlobalSight Architecture
Getting the Code
Building and Deploying GlobalSight
GlobalSight Developer’s Guide
Setting up the Development Environment
Debugging GlobalSight
Designing the GlobalSight Adapters
GlobalSight Web Services API
Using the GlobalSight Web Services API Test Tool
Using the GlobalSight CVS Connector
Connecting to a CVS with the GlobalSight Desktop Icon

Upgrading GlobalSight
Comparing GlobalSight with WorldServer

A collection of useful database queries

All active translation activities for a certain locale (in this example de-DE)

Select name,id from job where id in 
  (select job_id from workflow where target_locale_id = 25 and state = 'DISPATCHED' and iflow_instance_id in 
    (select workflow_id from task_info where name like '%Translation%' and (state = 'ACTIVE' or state='ACCEPTED')))

Average job size (word count) by target locale

Select locale.iso_lang_code, locale.iso_country_code, AVG(total_word_count) from locale, workflow 
  where workflow.state != "cancelled" and locale.id = workflow.target_locale_id GROUP BY workflow.target_locale_id

Total job size (word count) by target locale

Select locale.iso_lang_code, locale.iso_country_code, SUM(total_word_count) from locale, workflow 
   where workflow.state != "cancelled" and locale.id = workflow.target_locale_id GROUP BY workflow.target_locale_id

Average time to complete activities in hours

Select name, AVG( HOUR( TIMEDIFF(completed_date, accepted_date) ) ) from task_info 
  where state = 'COMPLETED' group by name

Average time to complete activities in hours and days (rounded) by target locale and activity name

Select locale.iso_lang_code, locale.iso_country_code, task_info.name, 
  ROUND( AVG( HOUR( TIMEDIFF(task_info.completed_date, task_info.accepted_date) ) ), 2), 
  ROUND( ( AVG( HOUR( TIMEDIFF(task_info.completed_date, task_info.accepted_date) ) ) / 24 ),1) 
    from workflow, task_info, where task_info.state = 'COMPLETED' and task_info.workflow_id = workflow.iflow_instance_id and 
    locale.id = workflow.target_locale_id group by name,target_locale_id

All active activities, that is the ones that are accepted and not yet completed; without test jobs

Select job.id, job.name, task_info.name, accepted_date, user_id from task_info,workflow,job where task_info.state != "complete" and 
  task_info.accepted_date IS NOT NULL and task_info.completed_date IS NULL and job.name NOT LIKE "%test%" and 
  task_info.workflow_id = workflow.iflow_instance_id and workflow.job_ID = job.ID

All active activities with the users that they are available to

SELECT job.id, job.name As 'Job name', CONCAT(locale.iso_lang_code,'-',locale.iso_country_code) As 'Target Locale',
jbpm_taskinstance.name_, jbpm_pooledactor.actorid_ As 'Available to'
FROM job, workflow, task_info, locale, jbpm_taskinstance, jbpm_task, jbpm_pooledactor, jbpm_taskactorpool
WHERE jbpm_pooledactor.id_ = jbpm_taskactorpool.pooledactor_ AND
jbpm_taskactorpool.taskinstance_ = jbpm_taskinstance.id_ AND
jbpm_taskinstance.actorid_ IS NULL
AND workflow.job_id = job.id AND
workflow.iflow_instance_id = task_info.workflow_id AND
jbpm_taskinstance.task_ = jbpm_task.ID_ AND
jbpm_task.tasknode_ = task_info.task_id AND
workflow.target_locale_id = locale.ID AND
job.state = 'DISPATCHED'