Generating Random Number In Each Row

Случайное число в каждой строке выборки.
Проблемка с SQL Server, в каждой строке выборки одно и то же значение, у PostgreSQL, MySQL, Oracle, SQLite всё нормально.
Выкручиваемся создав представление и функцию.
create view m_vw_randView
as
select rand() as random_number
go

create function getRand()
returns float
as
begin
    declare @returnValue float
	select @returnValue = random_number from m_vw_randView
    return @returnValue
end
go

select rand() [rand], dbo.getRand() [getRand], * from (values(1),(2),(3))t(c1)

rand			getRand			c1
0,63453611784667	0,96496883196861	1
0,63453611784667	0,0616408952549143	2
0,63453611784667	0,280773494594934	3
PostgreSQL v12
select *, random() from generate_series(1,3) t1(c1);
| c1  | random              |
| --- | ------------------- |
| 1   | 0.28302426362094124 |
| 2   | 0.38171190130398003 |
| 3   | 0.35524341401745474 |
MySQL v8.0
with recursive t1(c1) AS
(
  select 1
  union all
  select c1 + 1 from t1 where c1 + 1 <= 3
)
select *, rand() from t1;
| c1  | rand()             |
| --- | ------------------ |
| 1   | 0.8239167740791261 |
| 2   | 0.6890084265628909 |
| 3   | 0.9732918413107208 |
Oracle Database 11g
select level c1, dbms_random.value(0,1) random from dual connect by level <= 3
C1	RANDOM
1	0,457075791676919
2	0,885762460182671
3	0,555152770728942

C++ include SQLite


1. Navigate to https://www.sqlite.org/download.html and download latest `amalgamation` source version of SQLite.
2. Extract all the files into your project directory, or your include path, or separate path that you will add/added as include path in your project properties.
3. Run `Developer Command Prompt for VS ****` which is usually available at `Start -> Programs -> Visual Studio **** -> Visual Studio Tools`.
4. Navigate with command prompt to that directory where we extracted our SQLite.
5. Run next command to compile: `cl /c /EHsc sqlite3.c`
6. Run next command to create static library: `lib sqlite3.obj`
7. Open properties of your project and add `sqlite3.lib` to `Linker -> Input -> Additional Dependencies`.
Now you ready to include and use `sqlite3.h` in your project.

Простой сервис родительского контроля (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 |

Работа с JSON со стороны QT

QNetworkReply *reply = qobject_cast<QNetworkReply *>(sender()); if (reply->error() == QNetworkReply::NoError) { // Ниже кусок кода, выводящий в textEdit содержимое полученное с сайта. // QByteArray content= reply->readAll(); // Получаем содержимое ответа // Реализуем преобразование кодировки (зависит от кодировки сайта) // QTextCodec *codec = QTextCodec::codecForName(«windows-1251»); // ui->textEdit->setPlainText(codec->toUnicode(content.data()) ); // Выводим результат // А вот тут уже работа с JSON QJsonDocument document = QJsonDocument::fromJson(reply->readAll()); QJsonObject root = document.object(); ui->textEdit->append(QString::number(root.value(«type»).toInt())); ui->textEdit->append(QString::number(root.value(«numOfPins»).toInt())); QJsonValue jv = root.value(«pin»); QJsonArray ja = jv.toArray(); for(int i = 0; i < ja.count(); i++){ QJsonObject subtree = ja.at(i).toObject(); //Берем подмассив и распарсим его по названиям значений //ui->textEdit->append(QString::number(i)+» = «+QString::number(ja[i].toInt())); // Вот так можно взять строчку из массива ui->textEdit->append(subtree.value(«type»).toString() + » » + subtree.value(«name»).toString()+ » » + subtree.value(«value»).toString()); } } else { ui->textEdit->setPlainText(reply->errorString());// Выводим описание ошибки, если она возникает. } reply->deleteLater(); // разрешаем объекту-ответа «удалится» }

Работа с JSON со стороны Arduino

  #include <ArduinoJson.h> StaticJsonDocument<500> doc; // Количество элементов в дереве json doc[«type»]=1; //Добавляем значение поле type и значение «1» doc[«numOfPins»]=6; // То же самое // А вот тут интереснее. Создаем массив векторов «pin». В каждом векторе может // быть сколько угодно пар JsonArray pinTypes = doc.createNestedArray(«pin»); Прописываем значения для каждого элемента массива. pinTypes[0][«type»]=»INPUT»; pinTypes[0][«name»]=»D3″; pinTypes[0][«value»]=String(digitalRead(0)); // А здесь зачем конвертить в String ? Не знаю. Но когда парсится массив, то //QT не может по-человечески вытащить это значение. А из String легко. pinTypes[1][«type»]=»RESERVED»; pinTypes[1][«name»]=»TX»; pinTypes[1][«value»]=0; // И так далее String jsonDoc; // Библиотека может серилизовать прямо в поток, например //  serializeJson(doc, Serial); Но мне нужно писать по-хитрому, serializeJson(doc, jsonDoc); // Поэтому создаем временную переменную и… server.send(200, «text/html», jsonDoc); // отправляем ее клиенту.

Потек кран (очередной раз)

Потекла керамическая кран букса, подсмотрел вариант из итета и проверил.
Похоже в кран буксе предусмотрено слабое звено, некая прокладка и какого-то мягкого полиэтилена, за год она расплющивается почти в 0.
Заменил ее на то, что нашлось под рукой, а нашлось немного, проводок бы медный, пришлось накрутить запчасть от старой прищепки для штор.
Менять пришлось сразу две, обе перестали течь, время покажет как надолго.

IMG_20200314_172622 IMG_20200314_172802 IMG_20200314_172903 IMG_20200314_173434

ssh по ключам

генерируем приватный и публичный ключи
ssh-keygen -t rsa
на сервере добавляем публичный ключ в файл authorized_keys находящийся в домашнем каталоге пользователя которого хотим пускать по ключам в подкаталоге .ssh
cat id_rsa.pub >> authorized_keys
/etc/ssh/sshd_config
PubkeyAuthentication yes
для far manager и winscp используем ключ в формате putty, с помощью puttygen импортируем приватный ключ id_rsa и сохраняем его (save private key)

для использования приватного ключа id_rsa при работе с git добавляем в «C:\Program Files\Git\etc\ssh\ssh_config»
Host 192.168.1.205
   Identityfile "F:\JOB\FSK\CDS\.ssh\id_rsa"