Gists
On gists
JavaScript
Catching async/await
how-to-catching.js #
function getUserInfo() {
return new Promise((resolve, reject) => {
setTimeout(() => {
reject('request exception')
}, 1000)
})
}
// 1) Try-catch
async function loggedIn() {
try {
let userInfo = await getUserInfo()
// Execution interrupt
let pageInfo = await getPageInfo(userInfo?.userId)
} catch(e) {
console.warn(e)
}
}
// 2 direct catch
async function loggedIn() {
let userInfo = await getUserInfo().catch(e => console.warn(e))
// Execution continues, userInfo might be undefined
if (!userInfo) return
let pageInfo = await getPageInfo(userInfo?.userId)
}
// 3 direct catch with handle
async function loggedIn() {
let userInfo = await getUserInfo().catch(e => {
console.warn(e)
return Promise.reject(e)
})
// Execution interrupt
let pageInfo = await getPageInfo(userInfo?.userId)
}
On gists
JavaScript
Vue.js
Importing JSON file
foo.js #
// ES 6, JS without bundlers
// 1) fetch('data.json').then().then() ...
// 2) import data from './data.json' assert { type: 'json' };
// Vue
async function fetchData() {
try {
const response = await import('@/data.json');
const data = await response.default; // Získání dat z Promise
console.log(data);
} catch (error) {
console.error('Chyba při načítání dat:', error);
}
}
// Or
const o = ref([]);
const data = import('@/data.json').then(x => {
o.value = x.default;
});
On gists
Vue.js
Groupping computed & methods (very limited)
example.vue #
<template>
<p>{{ counterInfo.currentValue }} / {{ counterInfo.nextValue }}</p>
<button @click="pokus().a(50)">Pokus</button>
</template>
<script>
methods: {
pokus() {
return {
a: n => {
this.counter = n;
},
};
},
},
computed: {
counterInfo() {
return {
currentValue: this.counter,
nextValue: this.counter + 1,
// tohle nejde :D
beforeValue: () => {
return this.counter - 1;
},
};
},
},
</script>
On gists
Protecting
Heidi pwd decoding
heidi.js #
function heidiDecode(hex) {
var str = '';
var shift = parseInt(hex.substr(-1));
hex = hex.substr(0, hex.length - 1);
for (var i = 0; i < hex.length; i += 2)
str += String.fromCharCode(parseInt(hex.substr(i, 2), 16) - shift);
return str;
}
heidiDecode('pwd ...')
On gists
Decodes a password from HeidiSQL. HeidiSQL passwords can be found in the registry. Use File -> Export Settings to dump all settings. Great f
•
DOC
HeidiDecode.js #
function heidiDecode(hex) {
var str = '';
var shift = parseInt(hex.substr(-1));
hex = hex.substr(0, hex.length - 1);
for (var i = 0; i < hex.length; i += 2)
str += String.fromCharCode(parseInt(hex.substr(i, 2), 16) - shift);
return str;
}
document.write(heidiDecode('755A5A585C3D8141786B3C385E3A393'));
On gists
MySql
MySql - advanced
LEFT JOIN -> JOIN ON, WHERE
join.sql #
/*
https://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh
*/
+--------+---------------+
| DeptId | DeptName |
+--------+---------------+
| 1 | HR |
| 2 | Payroll |
| 3 | Admin |
| 4 | Marketing |
| 5 | HR & Accounts |
+--------+---------------+
+-------+----------+--------+-----------+
| EmpId | EmpName | DeptId | EmpSalary |
+-------+----------+--------+-----------+
| 1 | John | 1 | 5000.00 |
| 2 | Albert | 1 | 4500.00 |
| 3 | Crain | 2 | 6000.00 |
| 4 | Micheal | 2 | 5000.00 |
| 5 | David | NULL | 34.00 |
| 6 | Kelly | NULL | 457.00 |
| 7 | Rudy | 1 | 879.00 |
| 8 | Smith | 2 | 7878.00 |
| 9 | Karsen | 5 | 878.00 |
| 10 | Stringer | 5 | 345.00 |
| 11 | Cheryl | NULL | 0.00 |
+-------+----------+--------+-----------+
-- 1
select * from employees e
left join departments d on e.deptid = d.deptid;
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName | DeptId | EmpSalary | DeptId | DeptName |
+-------+----------+--------+-----------+--------+---------------+
| 1 | John | 1 | 5000.00 | 1 | HR |
| 2 | Albert | 1 | 4500.00 | 1 | HR |
| 3 | Crain | 2 | 6000.00 | 2 | Payroll |
| 4 | Micheal | 2 | 5000.00 | 2 | Payroll |
| 5 | David | NULL | 34.00 | NULL | NULL |
| 6 | Kelly | NULL | 457.00 | NULL | NULL |
| 7 | Rudy | 1 | 879.00 | 1 | HR |
| 8 | Smith | 2 | 7878.00 | 2 | Payroll |
| 9 | Karsen | 5 | 878.00 | 5 | HR & Accounts |
| 10 | Stringer | 5 | 345.00 | 5 | HR & Accounts |
| 11 | Cheryl | NULL | 0.00 | NULL | NULL |
+-------+----------+--------+-----------+--------+---------------+
-- 2
select * from employees e
left join departments d on e.deptid = d.deptid
and ( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName | DeptId | EmpSalary | DeptId | DeptName |
+-------+----------+--------+-----------+--------+---------------+
| 1 | John | 1 | 5000.00 | 1 | HR |
| 2 | Albert | 1 | 4500.00 | 1 | HR |
| 3 | Crain | 2 | 6000.00 | NULL | NULL |
| 4 | Micheal | 2 | 5000.00 | NULL | NULL |
| 5 | David | NULL | 34.00 | NULL | NULL |
| 6 | Kelly | NULL | 457.00 | NULL | NULL |
| 7 | Rudy | 1 | 879.00 | 1 | HR |
| 8 | Smith | 2 | 7878.00 | NULL | NULL |
| 9 | Karsen | 5 | 878.00 | 5 | HR & Accounts |
| 10 | Stringer | 5 | 345.00 | 5 | HR & Accounts |
| 11 | Cheryl | NULL | 0.00 | NULL | NULL |
+-------+----------+--------+-----------+--------+---------------+
-- 3
select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName | DeptId | EmpSalary | DeptId | DeptName |
+-------+----------+--------+-----------+--------+---------------+
| 1 | John | 1 | 5000.00 | 1 | HR |
| 2 | Albert | 1 | 4500.00 | 1 | HR |
| 7 | Rudy | 1 | 879.00 | 1 | HR |
| 9 | Karsen | 5 | 878.00 | 5 | HR & Accounts |
| 10 | Stringer | 5 | 345.00 | 5 | HR & Accounts |
+-------+----------+--------+-----------+--------+---------------+
-- to same ale z druheho pohledu, slo by resit pres RIGHT JOIN
-- 1
select * from departments d
left join employees e on e.deptId = d.deptId;
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName | EmpId | EmpName | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
| 1 | HR | 1 | John | 1 | 5000.00 |
| 1 | HR | 2 | Albert | 1 | 4500.00 |
| 1 | HR | 7 | Rudy | 1 | 879.00 |
| 2 | Payroll | 3 | Crain | 2 | 6000.00 |
| 2 | Payroll | 4 | Micheal | 2 | 5000.00 |
| 2 | Payroll | 8 | Smith | 2 | 7878.00 |
| 3 | Admin | NULL | NULL | NULL | NULL |
| 4 | Marketing | NULL | NULL | NULL | NULL |
| 5 | HR & Accounts | 9 | Karsen | 5 | 878.00 |
| 5 | HR & Accounts | 10 | Stringer | 5 | 345.00 |
+--------+---------------+-------+----------+--------+-----------+
-- 2
select * from departments d
left join employees e on e.deptId = d.deptId and
( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName | EmpId | EmpName | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
| 1 | HR | 1 | John | 1 | 5000.00 |
| 1 | HR | 2 | Albert | 1 | 4500.00 |
| 1 | HR | 7 | Rudy | 1 | 879.00 |
| 2 | Payroll | NULL | NULL | NULL | NULL |
| 3 | Admin | NULL | NULL | NULL | NULL |
| 4 | Marketing | NULL | NULL | NULL | NULL |
| 5 | HR & Accounts | 9 | Karsen | 5 | 878.00 |
| 5 | HR & Accounts | 10 | Stringer | 5 | 345.00 |
+--------+---------------+-------+----------+--------+-----------+
-- 3
SELECT * from departments d
left join employees e on e.deptId = d.deptId
where ( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName | EmpId | EmpName | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
| 1 | HR | 1 | John | 1 | 5000.00 |
| 1 | HR | 2 | Albert | 1 | 4500.00 |
| 1 | HR | 7 | Rudy | 1 | 879.00 |
| 5 | HR & Accounts | 9 | Karsen | 5 | 878.00 |
| 5 | HR & Accounts | 10 | Stringer | 5 | 345.00 |
+--------+---------------+-------+----------+--------+-----------+
On gists
Pivot table
•
MySql - advanced
pivot.sql #
-- static
SELECT
product_id,
SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
SUM(CASE WHEN month = 'March' THEN sales_amount ELSE 0 END) AS March
FROM sales
GROUP BY product_id;
-- dynamic
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN month = ''',
month,
''' THEN sales_amount ELSE 0 END) AS ',
CONCAT('`', month, '`')
)
) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- another table
SELECT
`Sales Date`,
MAX(CASE WHEN `Product Name` = 'Product AthanEN `Sales Amount` END) AS `Product A`,
MAX(CASE WHEN `Product Name` = 'Product B' then `Sales Amount` END) AS `Product B`,
MAX(CASE WHEN `Product Name` = 'Product C' than `Sales Amount` END) AS `Product C`
FROM
`sales_data`
GROUP BY
`Sales Date`;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN `Product Name` = ''', `Product Name`, ''' THEN `Sales Amount` END) AS `', `Product Name`, '`')
) INTO @sql
FROM
`sales_data`;
SET @sql = CONCAT('SELECT `Sales Date`, ', @sql, ' FROM `sales_data` GROUP BY `Sales Date`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/* month-year invoices */
select rok,
sum(case when mesic=1 then castka else 0 end) as 'leden',
sum(case when mesic=2 then castka else 0 end) as 'únor',
sum(case when mesic=3 then castka else 0 end) as 'březen',
sum(case when mesic=4 then castka else 0 end) as 'duben',
sum(case when mesic=5 then castka else 0 end) as 'květen',
sum(case when mesic=6 then castka else 0 end) as 'červen',
sum(case when mesic=7 then castka else 0 end) as 'červenec',
sum(case when mesic=8 then castka else 0 end) as 'srpen',
sum(case when mesic=9 then castka else 0 end) as 'září',
sum(case when mesic=10 then castka else 0 end) as 'říjen',
sum(case when mesic=11 then castka else 0 end) as 'listopad',
sum(case when mesic=12 then castka else 0 end) as 'prosinec'
from
(
select year(datum) as rok, month(datum) as mesic, sum(castka) as castka
from faktury group by year(datum), month(datum)
) as soucty
group by rok
On gists
MySql - advanced
window fn - standings rank with previous rank
rank.sql #
CREATE TABLE `standing` (
`team` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
`pts` INT(11) NULL DEFAULT NULL,
`season` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=MyISAM
;
SELECT
team,
season,
pts,
RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rnk,
LAG(rank_prev_season) OVER (PARTITION BY team ORDER BY season) AS prev_rnk
FROM
(
SELECT
team,
season,
pts,
RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rank_prev_season
FROM
standing
) AS subquery
ORDER BY
season, rnk;
+--------+--------+------+-----+----------+
| team | season | pts | rnk | prev_rnk |
+--------+--------+------+-----+----------+
| Plzen | 2021 | 60 | 1 | NULL |
| Sparta | 2021 | 55 | 2 | NULL |
| Slavia | 2021 | 40 | 3 | NULL |
| Sparta | 2022 | 54 | 1 | 2 |
| Slavia | 2022 | 50 | 2 | 3 |
| Plzen | 2022 | 48 | 3 | 1 |
| Slavia | 2023 | 55 | 1 | 2 |
| Plzen | 2023 | 49 | 2 | 3 |
| Sparta | 2023 | 38 | 3 | 1 |
+--------+--------+------+-----+----------+
On gists
MySql
MySql tricks
MySql - advanced
sql table from select
howto.sql #
-- 1
SELECT 'Mon'
UNION
SELECT 'Tue'
UNION
SELECT 'Web'
UNION
SELECT 'Thu'
UNION
SELECT 'Fri'
UNION
SELECT 'Sat'
UNION
SELECT 'Sun';
-- 2
WITH RECURSIVE days_cte AS (
SELECT 'Mon' AS day_name
UNION ALL
SELECT
CASE day_name
WHEN 'Mon' THEN 'Tue'
WHEN 'Tue' THEN 'Wed'
WHEN 'Wed' THEN 'Thu'
WHEN 'Thu' THEN 'Fri'
WHEN 'Fri' THEN 'Sat'
WHEN 'Sat' THEN 'Sun'
ELSE 'Mon' -- Pro zajištění cyklického opakování
END
FROM days_cte
WHERE day_name != 'Sun'
)
SELECT day_name FROM days_cte;