JIRA: How to Select the user and groups mapping 获得用户与用户组关联列表

Categories: Development Notes; Tagged with: ; @ May 28th, 2013 10:09

Requirement:

Export the users and group mapping list.

Solution:

Seems there’s no feature / plugins for this requirement. If you can access the database, then everything is easy:

SELECT  member.[ID]
      ,[membership_type]
      ,[group_type]
      ,[parent_name]
      ,[lower_parent_name]
      ,[child_name]
      ,[lower_child_name]
      ,member.[directory_id], jira_user.display_name
  FROM [jiradb].[jiraschema].[cwd_membership] member
left join [jiradb].[jiraschema].[cwd_user] jira_user
ON member.child_name = jira_user.user_name
order by parent_name

SQL Server: Query Result Order by ‘IN’ Conditions/Filter 按照IN条件排序

Categories: Database; Tagged with: ; @ May 2nd, 2013 12:12

Requirement:

Select some data using ‘IN’ clause, the result should keep the same order as the in conditions.

Solution: Use CharIndex in Order by

SELECT SECCODE, SECSNAME FROM SECURITY
WHERE SECCODE IN
    ('1U68',
    '1F77')
ORDER BY
CharIndex(LTRIM(RTRIM([SECCODE])), '1U68, 1F77')

In MySQL, you can use “Order By Field()
http://www.electrictoolbox.com/mysql-order-specific-field-values/

WordPress: batch deleting spam comments 批量删除垃圾回复

Categories: Development NotesWordPress; Tagged with: ; @ March 16th, 2013 10:50

Use sql to delete all the spam comments quickly:

Status of comment

SELECT DISTINCT comment_approved  FROM `x_comments`

Results:

0 – Pending

1 – approved

spam – spam

trash – trash

 

Deleting SQL

–delete spam & trash:

DELETE FROM `dbName`.`x_comments` WHERE comment_approved = 'spam' or comment_approved = 'trash'
--delete pending:
DELETE FROM `dbName`.`x_comments` WHERE comment_approved = '0'

SSIS: Trim the Join Fields for Merge Join

Categories: Database; Tagged with: ; @ March 13th, 2013 19:37

Kindly remind you:

Do trim the join fields for your ‘MergeJoin’:

Otherwise it may take hours to debug!!

image

 

 

SELECT LTRIM(RTRIM(seccode)) AS seccode….

TSQL: Converting/Casting String between Unicode and Non-Unicode

Categories: Database; Tagged with: ; @ January 16th, 2013 12:04

I got an Error in a SSIS package:

 cannot convert between unicode and non-unicode string data types.

We can use one component called ‘DataConversion’, but I think directly convert the type in SQL will be better.

SQL:

DECLARE @str_unicode NVARCHAR(32);
SET @str_unicode = NCHAR(9734);
SELECT @str_unicode

— Using CAST
SELECT CAST(@str_unicode AS VARCHAR(32))

— Using Convert
SELECT CONVERT(VARCHAR(32), @str_unicode)

Result:

image

Older Posts



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.