Express Path Variables Number type

Source: https://expressjs.com/en/guide/routing.html

Route parameters

Route parameters are named URL segments that are used to capture the values specified at their position in the URL. The captured values are populated in the req.params object, with the name of the route parameter specified in the path as their respective keys.

Route path: /users/:userId/books/:bookId
Request URL: http://localhost:3000/users/34/books/8989
req.params: { "userId": "34", "bookId": "8989" }

To define routes with route parameters, simply specify the route parameters in the path of the route as shown below.

app.get('/users/:userId/books/:bookId', function (req, res) {
  res.send(req.params)
})

The name of route parameters must be made up of “word characters” ([A-Za-z0-9_]).

Since the hyphen (-) and the dot (.) are interpreted literally, they can be used along with route parameters for useful purposes.

Route path: /flights/:from-:to
Request URL: http://localhost:3000/flights/LAX-SFO
req.params: { "from": "LAX", "to": "SFO" }

Route path: /plantae/:genus.:species
Request URL: http://localhost:3000/plantae/Prunus.persica
req.params: { "genus": "Prunus", "species": "persica" }

To have more control over the exact string that can be matched by a route parameter, you can append a regular expression in parentheses (()):

Route path: /user/:userId(\d+)
Request URL: http://localhost:3000/user/42
req.params: {"userId": "42"}

Because the regular expression is usually part of a literal string, be sure to escape any \ characters with an additional backslash, for example \\d+.

Checking Whether a String Contains a Substring

Source: https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch04s06.html

Problem

You want to know whether a given string occurs within another string.

Solution

Use LOCATE( ).

Discussion

The LOCATE( ) function takes two arguments representing the substring that you’re looking for and the string in which to look for it. The return value is the position at which the substring occurs, or 0 if it’s not present. An optional third argument may be given to indicate the position within the string at which to start looking.

mysql> SELECT name, LOCATE('in',name), LOCATE('in',name,3) FROM metal;
+----------+-------------------+---------------------+
| name     | LOCATE('in',name) | LOCATE('in',name,3) |
+----------+-------------------+---------------------+
| copper   |                 0 |                   0 |
| gold     |                 0 |                   0 |
| iron     |                 0 |                   0 |
| lead     |                 0 |                   0 |
| mercury  |                 0 |                   0 |
| platinum |                 5 |                   5 |
| silver   |                 0 |                   0 |
| tin      |                 2 |                   0 |
+----------+-------------------+---------------------+

LOCATE( ) is not case sensitive as of MySQL 4.0.0, and is case sensitive before that.

Remove last item from array

Source: https://stackoverflow.com/questions/19544452/remove-last-item-from-array

Use splice(index,howmany)

arr.splice(-1,1)

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Source: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join

The power of MySQL GROUP_CONCAT

Source: https://www.percona.com/blog/2013/10/22/the-power-of-mysql-group_concat/

When is MySQL GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work.

The following are some simple examples.

This is a test table:

CREATE TABLE `group_c` (

`parent_id` int(11) DEFAULT NULL,

`child_id` int(11) DEFAULT NULL

) ENGINE=InnoDB;

INSERT INTO group_c(parent_id, child_id)

VALUES (1,1),(1,2),(1,3),(2,1),(2,4),(1,4),(2,6),(3,1),(3,2),(4,1),(4,1),(1,1),(5,0);

Without grouping info the only way you can check things is:

ysql> SELECT DISTINCT

    -> parent_id, child_id

    -> FROM group_c

    -> ORDER BY parent_id;

+-----------+----------+

| parent_id | child_id |

+-----------+----------+

|         1 |        1 |

|         1 |        2 |

|         1 |        3 |

|         1 |        4 |

|         2 |        1 |

|         2 |        3 |

|         2 |        4 |

|         2 |        6 |

|         3 |        1 |

|         3 |        2 |

|         4 |        1 |

|         5 |        0 |

+-----------+----------+

12 rows in set (0.00 sec)

But it looks much better and easier to read with GROUP_CONCAT:

mysql> SELECT DISTINCT

    -> parent_id, GROUP_CONCAT(DISTINCT child_id ORDER BY child_id) AS child_id_list

    -> FROM group_c

    -> group by parent_id

    -> ORDER BY parent_id;

+-----------+---------------+

| parent_id | child_id_list |

+-----------+---------------+

|         1 | 1,2,3,4       |

|         2 | 1,3,4,6       |

|         3 | 1,2           |

|         4 | 1             |

|         5 | 0             |

+-----------+---------------+

5 rows in set (0.00 sec)

Easy? Let’s go to production usage and some “real” examples 

Assume you have 4 Support Engineers who were working with 6 Customers this week on 15 issues.

As it usually happens: everyone (sure, except those who are on vacation :)) worked on everything with everybody.

How you would represent it?

Here is my way:

Create test tables:

• engineers (id, name, surname, URL) – list of engineers
• customers (id, company name, URL) – list of customers
• issues (id, customer_id, description) – list of issues assigned to customers
• workflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them

-- Engineers

CREATE TABLE engineers (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

e_name VARCHAR(30) NOT NULL,

e_surname VARCHAR(30) NOT NULL,

url VARCHAR(255) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

-- Customers

CREATE TABLE customers (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

company_name VARCHAR(30) NOT NULL,

url VARCHAR(255) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

-- Issues (Issue-Customer)

CREATE TABLE issues (

id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

customer_id VARCHAR(30) NOT NULL,

description TEXT,

PRIMARY KEY (id)

) ENGINE=InnoDB;

-- Workflow (Action: Engineer-Issue(Customer))

CREATE TABLE workflow (

action_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

engineer_id SMALLINT UNSIGNED NOT NULL,

issue_id SMALLINT UNSIGNED NOT NULL,

PRIMARY KEY (action_id)

) ENGINE=InnoDB;

INSERT INTO engineers (e_name, e_surname, url)

VALUES

('Miguel', 'Nieto', 'https://www.percona.com/about-us/our-team/miguel-angel-nieto'),

('Marcos', 'Albe', 'https://www.percona.com/about-us/our-team/marcos-albe'),

('Valerii', 'Kravchuk', 'https://www.percona.com/about-us/our-team/valerii-kravchuk'),

('Michael', 'Rikmas', 'https://www.percona.com/about-us/our-team/michael-rikmas');

INSERT INTO customers (company_name, url)

VALUES

('OT','http://www.ovaistariq.net/'),

('PZ','http://www.peterzaitsev.com/'),

('VK','http://mysqlentomologist.blogspot.com/'),

('FD','http://www.lefred.be/'),

('AS','http://mysqlunlimited.blogspot.com/'),

('SS','https://www.flamingspork.com/blog/');

INSERT INTO issues(customer_id, description)

VALUES

(1,'Fix replication'),

(2,'Help with installation of Percona Cluster'),

(3,'Hardware suggestions'),

(4,'Error: no space left'),

(5,'Help with setup daily backup by Xtrabackup'),

(6,'Poke sales about Support agreement renewal'),

(4,'Add more accounts for customer'),

(2,'Create Hot Fix of Bug 1040735'),

(1,'Query optimisation'),

(1,'Prepare custom build for Solaris'),

(2,'explain about Percona Monitoring plugins'),

(6,'Prepare access for customer servers for future work'),

(5,'Decribe load balancing for pt-online-schema-change'),

(4,'Managing deadlocks'),

(1,'Suggestions about buffer pool size');

INSERT INTO workflow (engineer_id, issue_id)

VALUES (1,1),(4,2),(2,3),(1,4),(3,5),(2,6),(3,7),(2,8),(2,9),(1,10),(3,11),(2,12),(2,13),(3,14),(1,15),(1,9),(4,14),(2,9),(1,15),(3,10),(4,2),(2,15),(4,8),(4,4),(3,11),(1,7),(3,7),(1,1),(1,9),(3,4),(4,3),(1,5),(1,7),(1,4),(2,4),(2,5);

Examples:

List of issues for each engineer (GROUP_CONCAT):

mysql> SELECT

    ->  CONCAT (e_name, ' ', e_surname) AS engineer,

    ->  GROUP_CONCAT(DISTINCT issue_id, ' (', c.company_name,')' ORDER BY issue_id SEPARATOR ', ' ) AS 'issue (customer)'

    -> FROM

    ->  workflow w,

    ->  engineers e,

    ->  customers c,

    ->  issues i

    -> WHERE

    ->  w.engineer_id = e.id

    ->  AND w.issue_id = i.id

    ->  AND i.customer_id = c.id

    -> GROUP BY

    ->  e.id

    -> ORDER BY

    ->  e_name, e_surname;

+------------------+---------------------------------------------------------------------------+

| engineer         | issue (customer)                                                          |

+------------------+---------------------------------------------------------------------------+

| Marcos Albe      | 3 (VK), 4 (FD), 5 (AS), 6 (SS), 8 (PZ), 9 (OT), 12 (SS), 13 (AS), 15 (OT) |

| Michael Rikmas   | 2 (PZ), 3 (VK), 4 (FD), 8 (PZ), 14 (FD)                                   |

| Miguel Nieto     | 1 (OT), 4 (FD), 5 (AS), 7 (FD), 9 (OT), 10 (OT), 15 (OT)                  |

| Valerii Kravchuk | 4 (FD), 5 (AS), 7 (FD), 10 (OT), 11 (PZ), 14 (FD)                         |

+------------------+---------------------------------------------------------------------------+

4 rows in set (0.00 sec)

List of engineers for each customer (GROUP_CONCAT inside of GROUP_CONCAT):

mysql> SELECT

    ->  c.company_name AS company,

    ->  GROUP_CONCAT(DISTINCT issue_id, ' (', engineer_list, ')' ORDER BY issue_id SEPARATOR ', ' ) AS issue

    -> FROM

    ->  workflow w,

    ->  engineers e,

    ->  customers c,

    ->  issues i,

    ->  (SELECT

    ->    i.id AS i_id,

    ->    GROUP_CONCAT(DISTINCT CONCAT(e_name, ' ', e_surname) ORDER BY e_name SEPARATOR ', ') AS engineer_list

    ->   FROM

    ->    workflow w,

    ->    engineers e,

    ->    issues i

    ->   WHERE

    ->    w.engineer_id = e.id

    ->    AND w.issue_id = i.id

    ->   GROUP BY

    ->    i.id) AS e_list

    -> WHERE

    ->  w.engineer_id = e.id

    ->  AND w.issue_id = i.id

    ->  AND i.customer_id = c.id

    ->  AND w.issue_id = e_list.i_id

    -> GROUP BY

    ->  c.id

    -> ORDER BY

    ->  c.company_name;

+---------+--------------------------------------------------------------------------------------------------------------------------------------------+

| company | issue (engineer)                                                                                                                           |

+---------+--------------------------------------------------------------------------------------------------------------------------------------------+

| AS      | 5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe)                                                                          |

| FD      | 4 (Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk), 7 (Miguel Nieto, Valerii Kravchuk), 14 (Michael Rikmas, Valerii Kravchuk) |

| OT      | 1 (Miguel Nieto), 9 (Marcos Albe, Miguel Nieto), 10 (Miguel Nieto, Valerii Kravchuk), 15 (Marcos Albe, Miguel Nieto)                       |

| PZ      | 2 (Michael Rikmas), 8 (Marcos Albe, Michael Rikmas), 11 (Valerii Kravchuk)                                                                 |

| SS      | 6 (Marcos Albe), 12 (Marcos Albe)                                                                                                          |

| VK      | 3 (Marcos Albe, Michael Rikmas)                                                                                                            |

+---------+--------------------------------------------------------------------------------------------------------------------------------------------+

6 rows in set (0.00 sec)

Update a column value, replacing part of a string

Source: https://stackoverflow.com/questions/10177208/update-a-column-value-replacing-part-of-a-string

UPDATE urls
SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/')

Very useful, worked on 2000 queries with out fail, very quickly. – Andy Dec 14 '16 at 18:05
• 4
  REPLACE also works on SELECTs, quite useful :) – Arda Apr 19 '17 at 10:20

JavaScript Demo: Promise.all()

Source: 
The Promise.all() method returns a single Promise that resolves when all of the promises passed as an iterable have resolved or when the iterable contains no promises. It rejects with the reason of the first promise that rejects.


var promise1 = Promise.resolve(3);
var promise2 = 42;
var promise3 = new Promise(function(resolve, reject) {
  setTimeout(resolve, 100, 'foo');
});

Promise.all([promise1, promise2, promise3]).then(function(values) {
  console.log(values);
});

// expected output: Array [3, 42, "foo"]

MySQL Timezone tables

https://dev.mysql.com/downloads/timezones.html
https://dba.stackexchange.com/questions/149492/i-cant-find-time-zone-tables-in-sys-database-of-mysql

I used to face the same issue as you and I resolved it by following these quick steps.

Please follow below:

Environment:

• MySQL Version: 5.7
• OS: Windows 10 (64 bit)

Steps:

• Download the package that contains the data files of pre-built time zone tables. You can download here https://dev.mysql.com/downloads/timezones.html
  • timezone_2018e_posix_sql.zip - POSIX standard for MySQL version 5.7
• Extract the downloaded package, you will then have a file named timezone_posix.sql
• Open a command line in the root directory of MySQL. Ex: On my computer this is "C:\Program Files\MySQL\MySQL Server 5.7\bin"
• Run the command mysql -u root -p to login to MySQL with root access and then enter password.
• Run the command show databases; to view all database. You will see a database named mysql. You need to insert data into the mysql database
• Finally, you can insert data by issuing the command source /path/to/timezone_posix.sql.

After inserting the timezone data, you can test by issuing the following query:

 SELECT CONVERT_TZ('2018-07-02 14:46:46','utc','America/Los_Angeles')

If the result is not null, you're successful!!!

Bootstrap Contextual Colors

<div class="container">
  <h2>Contextual Colors</h2>
  <p>Use the contextual classes to provide "meaning through colors":</p>
  <p class="text-muted">This text is muted.</p>
  <p class="text-primary">This text is important.</p>
  <p class="text-success">This text indicates success.</p>
  <p class="text-info">This text represents some information.</p>
  <p class="text-warning">This text represents a warning.</p>
  <p class="text-danger">This text represents danger.</p>
  <p class="text-secondary">Secondary text.</p>
  <p class="text-dark">This text is dark grey.</p>
  <p class="text-body">Default body color (often black).</p>
  <p class="text-light">This text is light grey (on white background).</p>
  <p class="text-white">This text is white (on white background).</p>
</div>

Contextual Colors

Use the contextual classes to provide "meaning through colors":

This text is muted.

This text is important.

This text indicates success.

This text represents some information.

This text represents a warning.

This text represents danger.

Secondary text.

This text is dark grey.

Default body color (often black).

This text is light grey (on white background).

This text is white (on white background).

MySQL Date and Time functions

https://www.w3resource.com/mysql/date-and-time-functions/date-and-time-functions.php

Date and Time functions

Functions	Description
ADDDATE()	MySQL ADDDATE() adds a time value with a date.
ADDTIME()	In MySQL the ADDTIME() returns a time or datetime after adding a time value with a time or datetime.
CONVERT_TZ()	In MySQL the CONVERT_TZ() returns a resulting value after converting a datetime value from a time zone specified as the second argument to the time zone specified as the third argument.
CURDATE()	In MySQL the CURDATE() returns the current date in 'YYYY-MM-DD' format or 'YYYYMMDD' format depending on whether numeric or string is used in the function.
CURRENT_DATE()	In MySQL the CURRENT_DATE returns the current date in ‘YYYY-MM-DD’ format or YYYYMMDD format depending on whether numeric or string is used in the function.
CURRENT_TIME()	In MySQL the CURRENT_TIME() returns the current time in ‘HH:MM:SS’ format or HHMMSS.uuuuuu format depending on whether numeric or string is used in the function.
CURRENT_TIMESTAMP()	In MySQL the CURRENT_TIEMSTAMP returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending on whether numeric or string is used in the function.
CURTIME()	In MySQL the CURTIME() returns the value of current time in ‘HH:MM:SS’ format or HHMMSS.uuuuuu format depending on whether numeric or string is used in the function.
DATE_ADD()	MySQL DATE_ADD() adds time values (as intervals) to a date value. The ADDDATE() is the synonym of DATE_ADD().
DATE_FORMAT()	MySQL DATE_FORMAT() formats a date as specified in the argument. A list of format specifiers given bellow may be used to format a date.
DATE_SUB()	MySql date_sub() function subtract a time value (as interval) from a date.
DATE()	MySQL DATE() takes the date part out from a datetime expression.
DATEDIFF()	MySQL DATEDIFF() returns the number of days between two dates or datetimes.
DAY()	MySQL DAY() returns the day of the month for a specified date.
DAYNAME()	MySQL DAYNAME() returns the name of the week day of a date specified in the argument.
DAY OF MONTH()	MySQL DAYOFMONTH() returns the day of the month for a given date.
DAY OF WEEK()	MySQL DAYOFWEEK() returns the week day number (1 for Sunday,2 for Monday …… 7 for Saturday ) for a date specified as an argument.
DAY OF YEAR()	MySQL DAYOFYEAR() returns day of the year for a date. The return value is within the range of 1 to 366.
EXTRACT()	MySQL EXTRACT() extracts a part of a given date.
FROM_DAYS()	MySQL FROM_DAYS() returns a date against a datevalue.
FROM_UNIXTIME()	MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.
GET_FORMAT()	MySQL GET_FORMAT() converts a date or time or datetime in a formatted manner as specified in the argument.
HOUR()	MySQL HOUR() returns the hour of a time.
LAST_DAY()	MySQL LAST_DAY() returns the last day of the corresponding month for a date or datetime value.
LOCALTIME()	MySQL LOCALTIME returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function.
LOCALTIMESTAMP()	MySQL LOCALTIMESTAMP returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function.
MAKEDATE()	MySQL MAKEDATE() returns a date by taking a value of a year and a number of days. The number of days must be greater than 0 otherwise a NULL will be returned.
MAKETIME()	MySQL MAKETIME() makes and returns a time value from a given hour, minute and seconds.
MICROSECOND()	MySQL MICROSECOND() returns microseconds from the time or datetime expression.
MINUTE()	MySQL MINUTE() returns a minute from a time or datetime value.
MONTH()	MySQL MONTH() returns the month for the date within a range of 1 to 12 ( January to December).
MONTHNAME()	MySQL MONTHNAME() returns the full name of the month for a given date.
NOW()	MySQL NOW() returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function.
PERIOD_ADD()	MySQL PERIOD_ADD() adds a number of months with a period and returns the value in the format YYYYMM OR YYMM. Remember that the format YYYYMM and YYMM are not date values.
PERIOD_DIFF()	MySQL PERIOD_DIFF() returns the difference between two periods.
QUARTER()	MySQL QUARTER() returns the quarter of the year for a date.
SEC_TO_TIME()	MySQL SEC_TO_TIME() returns a time value by converting the seconds specified in the argument.
SECOND()	MySQL SECOND() returns the second for a time.
STR_TO_DATE()	MySQL STR_TO_DATE() returns a datetime value by taking a string and a specific format string as arguments.
SUBDATE()	MySQL SUBDATAE() subtracts a time value (as interval) from a given date.
SUBTIME()	MySQL SUBTIME() subtracts one datetime value from another.
SYSDATE()	MySQL SYSDATE() returns the current date and time in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format depending on the context of the function.
TIME_FORMAT()	MySQL TIME_FORMAT() converts a time in a formatted string using the format specifiers.
TIME_TO_SEC()	MySQL TIME_TO_SEC() converts a time value in to seconds.
TIME()	MySQL TIME() extracts the time part of a time or datetime expression as string format.
TIMEDIFF()	MySQL TIMEDIFF() returns the differences between two time or datetime expressions.
TIMESTAMP()	MySQL TIMESTAMP() returns a datetime value against a date or datetime expression.
TIMESTAMPADD()	MySQL TIMESTAMPADD() adds time value with a date or datetime value.
TIMESTAMPDIFF()	MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another.
TO_DAYS()	MySQL TO_DAYS() returns number of days between a given date and year 0.
UNIX_TIMESTAMP()	MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIT_TIMESTAMP().
UTC_DATE()	MySQL UTC_DATE returns the current UTC (Coordinated Universal Time) date as a value in 'YYYY-MM-DD' or YYYYMMDD format depending on the context of the function i.e. in a string or numeric context.
UTC_TIME()	MySQL UTC_TIME returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format depending on the context of the function i.e. in a string or numeric context.
UTC_TIMESTAMP()	In MySQL the UTC_TIMESTAMP returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format depending on the usage of the function i.e. in a string or numeric context.
WEEK()	MySQL WEEK() returns the week number for a given date.
WEEKDAY()	MySQL WEEKDAY() returns the index of the day in a week for a given date (0 for Monday, 1 for Tuesday and ......6 for Sunday).
WEEK OF YEAR()	MySQL WEEKOFYEAR() returns the calender week (as a number) of a given date.
YEAR()	MySQL YEAR() returns the year for a given date.
YEARWEEK()	MySQL YEARWEEK() returns year and week number for a given date.