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

npm run build base-href

Using NPM to specify base-href
When building an Angular application, people usually use "ng" and pass arguments to that invocation.
Typically, when wanting to hard code "base-href" in "index.html", one will issue:

ng build --base-href='https://ngx.rktmb.org/foo'


I used to build my angular apps through Bamboo or Jenkins and they have a "npm" plugin.
I got the habit to build the application with "npm run build" before deploying it.

But the development team once asked me to set the "--base-href='https://ngx.rktmb.org/foo'" parameter.

npm run build --base-href='https://ngx.rktmb.org/foo


did not set the base href in indext.html

After looking for a while, I found https://github.com/angular/angular-cli/issues/13560 where it says:
You need to use −− to pass arguments to npm scripts. This did the job!
The command to issue is then:

npm run build -- --base-href='https://ngx.rktmb.org/foo'

dockerfile multiline to file

Outputing a multiline string from Dockerfile
I motsly use a Dockerfile by sourcing from a base ditribution: CentOS or Debian.
But I also have a local mirror and would like to use it for packages installation.

Espacially on CentOS it is about many lines to write to the /etc/yum.repos.d/CentOS-Base.repo file.

Easiest way: one RUN per line The first method that comes in mind is to issue one RUN per line to write.
Here you are:

RUN echo "[base] " > /etc/yum.repos.d/CentOS-Base.repo RUN echo "name=CentOS-$releasever - Base " >> /etc/yum.repos.d/CentOS-Base.repo RUN echo "baseurl=ftp://packages-infra.mg.rktmb.org/pub/centos/7/base-reposync-7 " >> /etc/yum.repos.d/CentOS-Base.repo RUN echo "gpgcheck=0 " >>…

Project Lead NextGen Project

Jira, change project Lead in a Next-Gent project Atlassian has launched a new feature on Jira Cloud instances: Next-Gent project
Roles, Workflow and settings are not the same as in usual.
When one creates a Next-Gent project, the creator is the "Project Lead", and changin this is not straight:
How-to-change-project-lead-in-a-Next-Gen-ProjectHow-do-i-set-default-assignee-and-workflow-in-next-gen-projects Here is the mergeGet the ID of your project: say 10002Go to https://[account name].atlassian.net/secure/project/EditProject!default.jspa?pid=10002Set the project Lead