Skip to main content

mysql case when then end

Use case

I use a Dovecot IMAP & POP server, against a MySQL user database. In database, the users are stored this way:

id pwd
mihamina@gulfsat.mg $1$HSBxVt/clgdj0L5Fgzm61
mihamina@freedsl.mg $1$7TCTJJMG9cUvAlWuAbvE.
mihamina@rktmb.org $1$H23xVt.bbgdj0L5Fgzm61

On my server I have mutiple IP adresses:

  • A legacy IP (1.2.3.4) where "@rktmb.org" users sometimes log in only with the localpart, sometimes with the full email address
  • A legacy IP (5.6.7.8) where "@freedsl.mg" users sometimes log in only with the localpart, sometimes with the full email address
  • A last IP (7.8.9.0) where users log in with the full email address

In usual condition, on a mail system, all users either all login with the full email address or all with the localpart. But on my alien system, they mix their habits, for legacy reasons.

Therefore:

  • The "mihamina@rktmb.org" user always logs in on the 1.2.3.4 IP but sometimes with "mihamina", sometimes with "mihamina@rktmb.org"
  • The "mihamina@freedsl.mg" user always logs in on the 5.6.7.8 IP but sometimes with "mihamina", sometimes with "mihamina@freedsl.mg"
  • The other users always log in on 7.8.9.0 with their full email address

The SQL query using CASE WHEN THEN END

Dovecot allows to use "%u" as username and "%l" as local IP in SQL queries. This is how I did it:

 SELECT id AS user, pwd AS password  FROM passwdc  WHERE (((id = '%u') OR (id = (                               CASE                                 WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org')                                 ELSE ('%u@freedsl.mg')                               END                              ))) AND en = 1)      
or
 SELECT id AS user, pwd AS password  FROM passwdc  WHERE (((id = '%u') OR (id = (                               CASE                                 WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org')                                 WHEN ("%l" = "5.6.7.8") THEN ('%u@freedsl.mg')                               END                              ))) AND en = 1)      

Comments

Popular posts from this blog

vmware net_device trans_start

VMWare Workstation 12 and Kernel 4.7 When recompiling vmware kernel modules on a kernel 4.7, I get this error:

/tmp/modconfig-xrrZGZ/vmnet-only/netif.c:468:7: error: ‘struct net_device’ has no member named ‘trans_start’; did you mean ‘mem_start’?     dev->trans_start = jiffies;
This seems to be an already encountered problem: http://rglinuxtech.com/?p=1746http://ferenc.homelinux.com/?p=356 I choosed to replace the line, instead of deleting it.

- dev->trans_start = jiffies; + netif_trans_update(dev); I also noted that I had to re-tar the modified sources instead of leaving them untared, because the compilation process only takes the archives. 
On precedent editions of these files, I just left the modified folders "vmnet-only/" and "vmmon-only/" expanded without the need to re-tar them.


Jira workflow for new projects

Associated workflow creation I'm a Jira Cloud user and begining from some version 6, I noticed that when I create a project, it automatically creates a Workflow and Issue Scheme that is prepended by the project key and which is a copy of the default scheme.
I always had to make a cleanup after creating a project. Default workflow for new projects I also miss a feature that would allow me to make a custom workflow (and globally custom project setting) the default for new projects I create.
Solution: Create with shared configuration While searching, I noticed that with Jira Cloud which is version 7.1.0 at the time I write, there is a link at the bottom of the "Create project" wizard:
"Create with shared configuration" will allow me to select the project I want the new one to share configuration with.

The new created project will use the same configuration as the project I selectThere will be no creation of Workflow and Issue Scheme that I need to cleanup

This fea…

vmware workstation 12 unable to load libvmwareui.so

Using VMWare Workstation on ArchLinux, it suddenly refused to launch.
when inspecting the logs, which BTW are in /tmp/vmware-<id>, I see:

2015-12-11T17:41:54.442+03:00| appLoader| I125: Log for appLoader pid=1727 version=12.0.1 build=build-3160714 option=Release
2015-12-11T17:41:54.442+03:00| appLoader| I125: The process is 64-bit.
2015-12-11T17:41:54.442+03:00| appLoader| I125: Host codepage=UTF-8 encoding=UTF-8
2015-12-11T17:41:54.442+03:00| appLoader| I125: Host is unknown
2015-12-11T17:41:54.448+03:00| appLoader| W115: HostinfoReadDistroFile: Cannot work with empty file.
2015-12-11T17:41:54.448+03:00| appLoader| W115: HostinfoOSData: Error: no distro file found
2015-12-11T17:41:54.448+03:00| appLoader| I125: Invocation: "/usr/lib/vmware/bin/vmware-modconfig --launcher=/usr/bin/vmware-modconfig --appname=VMware Workstation --icon=vmware-workstation"
2015-12-11T17:41:54.448+03:00| appLoader| I125: Calling: "/usr/lib/vmware/bin/vmware-modconfig --launcher=/usr/bin/vmware…