Месяц: Сентябрь 2020

Простой сервис родительского контроля (simple service parental control)

По просьбе трудящихся наваял небольшой сервис, выполняет выход из сеанса пользователя (log off) или его отключение (disconnect) в определенное время с временной блокировкой учетной записи, если необходимо. Периодичность проверки попадания в график 1 минута.

Действия по блокировке или отключению учетки применяются только если сессия активна. Учетной запись разблокируется при условиях: учетная запись присутствует в графике, она заблокирована, и в этот момент не попадает в график по отключению. (раз в минуту)

Версия 1.3 — добавлена возможность отключения записи графика. У отключенных текст подкрашен серым.

UPD 09.10.2020 Версия 1.4 — добавлена группировка записей графика и множественное выделение и выключение/включение записей.

chado.zip

chado

Необходимо настроить график отключений, установить и запустить сервис. Данные графика сохраняются в SQLite. После изменения графика необходим перезапуск сервиса, так как сервисом данные считываются только один раз, при запуске. Сервис и GUI-приложение один и тот же бинарный файл, при установке сервиса приложение не копируется, сервис запускается с места нахождения chado.exe

Для disconnect и log off используются WTSDisconnectSession и WTSLogoffSession


for (auto& user : theApp.DBUsers)
{
	if (user.userName.Compare(userName) == 0 
		&& user.dayOfWeek == dayOfWeek
		&& curentSeconds >= user.allowedFrom
		&& curentSeconds <= user.allowedTo
		) {
		if (user.disconnect == 0) {
			WTSDisconnectSession(WTS_CURRENT_SERVER_HANDLE, pInfos[i].SessionId, FALSE);
			theApp.log.WriteString((date.Format(L"%Y-%m-%d %T") + L" disconnect: " + user.userName + L"\r\n").GetString());
		}
		else {
			WTSLogoffSession(WTS_CURRENT_SERVER_HANDLE, pInfos[i].SessionId, FALSE);
			theApp.log.WriteString((date.Format(L"%Y-%m-%d %T") + L" logoff: " + user.userName + L"\r\n").GetString());
		}
		if (user.lock == 0) {
			theApp.unlockAccount(user.userName.GetString(), 1);
		}
	}
}

Для блокировки/разблокировки учетной записи: NetUserGetInfo и NetUserSetInfo


	if (!lock && (ui4->usri4_flags & UF_ACCOUNTDISABLE) == UF_ACCOUNTDISABLE) {
		ui.usri1008_flags = ui4->usri4_flags ^ UF_ACCOUNTDISABLE;
		need = 2;
	}
	if (lock && (ui4->usri4_flags & UF_ACCOUNTDISABLE) != UF_ACCOUNTDISABLE) {
		ui.usri1008_flags = ui4->usri4_flags | UF_ACCOUNTDISABLE;
		need = 1;
	}

	// Call the NetUserSetInfo function
	//  to disable the account, specifying level 1008.
	if (need) {
		nStatus = NetUserSetInfo(NULL, username, 1008, (LPBYTE)&ui, NULL);
		// Display the result of the call.
		if (nStatus == NERR_Success) {
#ifdef _DEBUG
			theApp.log.WriteString((date.Format(L"%Y-%m-%d %T") + (need == 1 ? L" lock " : L" unlock ") + L"account: " + username + L"\r\n").GetString());
#endif
		}
		else {
			theApp.log.WriteString((date.Format(L"%Y-%m-%d %T") + L" error lock/unlock account: " + username + L"\r\n").GetString());
		}
	}

Латеральное объединение (LATERAL JOIN)

Ну вот, хоть в чем-то MySQL переплюнул MariaDB и это LATERAL (на дворе осень 2020)


create table t1(id int, val int);
create table t2(id int, val int);

insert into t1(id, val)
with recursive
r1 as (
  select 1 id, (rand()*100 div 1) r 
  union all
  select r1.id+1 id, (rand()*100 div 1) r from r1 
   where r1.id<100
 ) 
  select id, r from r1
  ;
  
insert into t2(id, val)
with recursive
r1 as (
  select 1 id, (rand()*100 div 1) r 
  union all
  select r1.id+1 id, (rand()*100 div 1) r from r1 
   where r1.id<100
 ) 
  select id, r from r1 where r between 40 and 60
 ;  
 
 select t1.*, t22.* from t1
  join lateral (
  select max(t2.id) ma, min(t2.id) mi from t2 where t1.val between t2.val-10 and t2.val+10
 ) t22 on (true) -- по сути своей left join ибо "on (true)"
-- ) t22 on (t22.ma is not null) -- как костыль inner join можно получить так
 ;

SQL суммы или минимумы-максимумы внутри однотипных повторяющихся подгрупп

SQL суммы или минимумы-максимумы внутри однотипных повторяющихся групп разделяемых другими группами

Или в моем практическом случае была задача на «схлопывание» однотипных диапазонов по времени, минимум и максимум в подгруппе.


**Schema (PostgreSQL v11)**
    
    create table transactions (id int, type int, sum decimal);
    
    insert into transactions
    (id, type, sum)
    VALUES
    (1, 1, 100),(2, 1, 101),
    (3, 2, 200),(4, 2, 201),
    (5, 3, 300),(6, 3, 301),
    (7, 3, 302),(8, 2, 500),
    (9, 2, 510),(10, 1, 20);
        
---
**Query #1**

    with 
    t1 as (
    	select *, lead(type) over (order by id) rn1, lag(type) over (order by id) rn2 from transactions
    ) 
    ,t2 as (
      select t1.*, 
        sum(case when type=rn1 and (rn1!=rn2 or rn2 is null) or (rn1 is null and type!=rn2) then 1 else 0 end) over(order by id) sg
        from t1
     )
    select min(id) first_id_in_group, type, sum(t2.sum) from t2
     group by sg, type
     order by 1;

| first_id_in_group | type | sum  |
| ----------------- | ---- | ---- |
| 1                 | 1    | 201  |
| 3                 | 2    | 401  |
| 5                 | 3    | 903  |
| 8                 | 2    | 1010 |
| 10                | 1    | 20   |
---

С помощью условий находим начала интервалов, обозначаем их 1, последующие записи в группе 0

case when type=rn1 and (rn1!=rn2 or rn2 is null) or (rn1 is null and type!=rn2) then 1 else 0 end

Кумулятивно суммируем и получаем номера групп.

sum() over(order by id)

Ну а дальше всё как обычно.

Recursive CTE Postgres vs MySQL(MariaDB)

Простая задача построение дерева с динамическим количеством уровней вложенности и возможностью настраиваемой сортировки ветвей и листьев 

**Schema (PostgreSQL v12)**

create table catalog
    (id int, parent int, name text, ordered int);
    
insert into catalog (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', 10),
    (2, null, 'root2', 8),
    (3, null, 'root1', 1),
    (4, 3, 'child1-root1', 1),
    (5, 3, 'child3-root1', 3),
    (6, 3, 'child2-root1', 2),
    (7, 1, 'child3-root10', 30),
    (8, 1, 'child2-root10', 20),
    (9, 1, 'child1-root10', 10),
    (7, 2, 'child3-root2', 30),
    (8, 2, 'child1-root2', 10),
    (9, 2, 'child2-root2', 20)
    ;
---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, (row_number() over (partition by parent order by ordered))::text path from catalog c1 where c1.parent is null
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, row_number() over (partition by c2.parent order by c2.ordered)) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name          | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3   |        | root1         | 1       | 1   | 1    |
| 4   | 3      | child1-root1  | 1       | 2   | 11   |
| 6   | 3      | child2-root1  | 2       | 2   | 12   |
| 5   | 3      | child3-root1  | 3       | 2   | 13   |
| 2   |        | root2         | 8       | 1   | 2    |
| 8   | 2      | child1-root2  | 10      | 2   | 21   |
| 9   | 2      | child2-root2  | 20      | 2   | 22   |
| 7   | 2      | child3-root2  | 30      | 2   | 23   |
| 1   |        | root10        | 10      | 1   | 3    |
| 9   | 1      | child1-root10 | 10      | 2   | 31   |
| 8   | 1      | child2-root10 | 20      | 2   | 32   |
| 7   | 1      | child3-root10 | 30      | 2   | 33   |

С Postgres все ОК, в Oracle и MS SQL Server тоже все работает.

MySQL и MariaDB заявляют о поддержке рекурсивных обобщенных табличных выражений, но похоже не полностью.


with recursive
r as (
select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
  union all
select c2.*, r.lvl + 1 lvl, concat(r.path, convert(row_number() over (partition by c2.parent order by c2.ordered), char)) path  from catalog c2, r where c2.parent = r.id
)
select * from r order by path

MySQL 8.0.12
Query Error: Error: UNKNOWN_CODE_PLEASE_REPORT: Recursive Common Table Expression 'r' can contain neither aggregation nor window functions in recursive query block
---
MariaDB 10.4.12-MariaDB
ER_NOT_STANDARD_COMPLIANT_RECURSIVE (conn=2466, no: 4008, SQLState: HY000) Restrictions imposed on recursive definitions are violated for table 'r' sql: with recursive r as ( select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null union all select c2.*, r.lvl + 1 lvl, concat(r.path, convert(row_number() over (partition by c2.parent order by c2.ordered), char)) path from catalog c2, r where c2.parent = r.id ) select * from r order by path - parameters:[]

А вот если из второго запроса убрать оконную функцию row_number, и немного изменив логику формирования пути сортировки, то получим нужный результат.


**Schema (MySQL v8.0)**

    create table catalog
    (id int, parent int, name text, ordered int);
    
    insert into catalog
    (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', 10),
    (2, null, 'root2', 8),
    (3, null, 'root1', 1),
    (4, 3, 'child1-root1', 1),
    (5, 3, 'child3-root1', 3),
    (6, 3, 'child2-root1', 2),
    (7, 1, 'child3-root10', 30),
    (8, 1, 'child2-root10', 20),
    (9, 1, 'child1-root10', 10),
    (10, 2, 'child3-root2', 30),
    (11, 2, 'child1-root2', 10),
    (12, 2, 'child2-root2', 20),
    (13, 8, 'child1-child2-root10', 2),
    (14, 8, 'child2-child2-root10', 3)
    ;    

---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, convert(c2.ordered, char)) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name                 | ordered | lvl | path |
| --- | ------ | -------------------- | ------- | --- | ---- |
| 3   |        | root1                | 1       | 1   | 1    |
| 4   | 3      | child1-root1         | 1       | 2   | 11   |
| 6   | 3      | child2-root1         | 2       | 2   | 12   |
| 5   | 3      | child3-root1         | 3       | 2   | 13   |
| 2   |        | root2                | 8       | 1   | 2    |
| 11  | 2      | child1-root2         | 10      | 2   | 210  |
| 12  | 2      | child2-root2         | 20      | 2   | 220  |
| 10  | 2      | child3-root2         | 30      | 2   | 230  |
| 1   |        | root10               | 10      | 1   | 3    |
| 9   | 1      | child1-root10        | 10      | 2   | 310  |
| 8   | 1      | child2-root10        | 20      | 2   | 320  |
| 13  | 8      | child1-child2-root10 | 2       | 3   | 3202 |
| 14  | 8      | child2-child2-root10 | 3       | 3   | 3203 |
| 7   | 1      | child3-root10        | 30      | 2   | 330  |

UPDATE

Ан, нет, проглядел я, без использования оконной функции во втором подзапросе правильный «путь» не получить. В примере все хорошо, так как данные удачно легли.

А если, так, то беда


**Schema (MySQL v8.0)**

    create table catalog
    (id int, parent int, name text, ordered int);
    
    insert into catalog
    (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', 10),
    (2, null, 'root2', 8),
    (3, null, 'root1', 1),
    (4, 3, 'child1-root1', 1),
    (5, 3, 'child3-root1', 10),
    (6, 3, 'child2-root1', 2),
    (7, 1, 'child3-root10', 30),
    (8, 1, 'child2-root10', 20),
    (9, 1, 'child1-root10', 10),
    (7, 2, 'child3-root2', 30),
    (8, 2, 'child1-root2', 10),
    (9, 2, 'child2-root2', 20)
    ;
    
    

---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null  
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, convert(c2.ordered, char)) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name          | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3   |        | root1         | 1       | 1   | 1    |
| 4   | 3      | child1-root1  | 1       | 2   | 11   |
| 5   | 3      | child3-root1  | 10      | 2   | 110  |
| 6   | 3      | child2-root1  | 2       | 2   | 12   |
| 2   |        | root2         | 8       | 1   | 2    |
| 8   | 2      | child1-root2  | 10      | 2   | 210  |
| 9   | 2      | child2-root2  | 20      | 2   | 220  |
| 7   | 2      | child3-root2  | 30      | 2   | 230  |
| 1   |        | root10        | 10      | 1   | 3    |
| 9   | 1      | child1-root10 | 10      | 2   | 310  |
| 8   | 1      | child2-root10 | 20      | 2   | 320  |
| 7   | 1      | child3-root10 | 30      | 2   | 330  |

---

UPDATE 2

Есть выход, но нужно будет немного усложнить механизм сохранения значений сортировки в разрезе ветви одного уровня. Например, добиваем «0»-ми до максимального кол-ва знаков в числе по ветви одного уровня


**Schema (MySQL v8.0)**

    create table catalog
    (id int, parent int, name text, ordered varchar(10));
    
    insert into catalog
    (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', '10'),
    (2, null, 'root2', '08'),
    (3, null, 'root1', '01'),
    (4, 3, 'child1-root1', '01'),
    (5, 3, 'child3-root1', '10'),
    (6, 3, 'child2-root1', '02'),
    (7, 1, 'child3-root10', '30'),
    (8, 1, 'child2-root10', '20'),
    (9, 1, 'child1-root10', '10'),
    (7, 2, 'child3-root2', '30'),
    (8, 2, 'child1-root2', '10'),
    (9, 2, 'child2-root2', '20')
    ;
    
    

---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, c1.ordered path from catalog c1 where c1.parent is null  
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, c2.ordered) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name          | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3   |        | root1         | 01      | 1   | 01   |
| 4   | 3      | child1-root1  | 01      | 2   | 0101 |
| 6   | 3      | child2-root1  | 02      | 2   | 0102 |
| 5   | 3      | child3-root1  | 10      | 2   | 0110 |
| 2   |        | root2         | 08      | 1   | 08   |
| 8   | 2      | child1-root2  | 10      | 2   | 0810 |
| 9   | 2      | child2-root2  | 20      | 2   | 0820 |
| 7   | 2      | child3-root2  | 30      | 2   | 0830 |
| 1   |        | root10        | 10      | 1   | 10   |
| 9   | 1      | child1-root10 | 10      | 2   | 1010 |
| 8   | 1      | child2-root10 | 20      | 2   | 1020 |
| 7   | 1      | child3-root10 | 30      | 2   | 1030 |