Skip to content

Instantly share code, notes, and snippets.

@blzzua
blzzua / merge.sql
Created July 3, 2024 16:22
ms sql merge
MERGE targettable AS target
USING
(
select id, sub_id, somevalue from sometable_or_query
)
AS source
ON (target.id = source.id AND target.sub_id = source.sub_id)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN MATCHED THEN
@blzzua
blzzua / ShannonIndex.sql
Created August 10, 2023 13:16
Diversity index MS SQL
WITH SpeciesCounts AS (
SELECT
SpecieType,
COUNT(*) AS CountPerSpecies
FROM
Table
GROUP BY
SpecieType
),
@blzzua
blzzua / compact.js
Last active May 17, 2023 08:05 — forked from daniel-sc/compact.js
Compact/reorg/defragment all collections inside of a MongoDB database
// This script loops though all collections of all db in a MongoDB and runs the compact operation on them
// Simply paste this into the Mongo shell
rs.secondaryOk(); // db.slaveOk(); for early versions
db.getMongo().getDBNames().forEach(function(dbName) {
if ("local" != dbName && "admin" != dbName && "system" != dbName && "config" != dbName /* use this to (re-)start: && dbName > "am"*/) {
var subject = db.getSiblingDB(dbName);
subject.getCollectionNames().forEach(function (collectionName) {
print(new Date() + ': Compacting: ' + dbName + " - " + collectionName);
sleep(1000); // assure a cancel (CTRL-C) after "done" is executed before compact command
@blzzua
blzzua / count_size_by_index_pattern.py
Created April 18, 2023 20:16
elasticsearch count_size_by_index_pattern.py
#!/bin/python3
import re
import requests
SIZE_SUFFIX = {'tb': 1024*1024*1024*1024,'gb': 1024*1024*1024, 'mb': 1024*1024, 'kb': 1024, 'b': 1}
def read_size_data(url='http://127.0.0.1:9200/_cat/indices?h=index,pri.store.size'):
response = requests.get(url)
if response.status_code == 200:
return response.text
@blzzua
blzzua / cpu-adaptive.py
Created March 21, 2023 17:16
adaptive cpu on/off
from time import sleep
import os
import json
def uncollapse(inp):
""" '0-2,6-8,9,10,23' -> 0,1,2,6,7,8,9,10,23 """
res = []
for items in inp.split(','):
if '-' in items:
@blzzua
blzzua / split_combination.py
Last active December 24, 2022 18:32
Split combinations: iterative algorithm O(N) = 2**N. based on binary represenation split
def split_combination(s):
"iterative algorythm O(N) = 2**N. based on binary represenation split"
l = len(s)-1
all_comb = []
for i in range(2**l):
splitmap = bin(i)[2:].rjust(l,'0')
combination = []
substr = s[0]
for splitflag, char in zip(splitmap, s[1:]):
@blzzua
blzzua / active_directory_ldap_auth_example.py
Created December 15, 2022 22:02
active directory ldap auth python
import ldap
login, password = 'login', 'password'
ctx = {}
ctx['action'] = 'initializing LDAP connection'
ldap_obj = ldap.initialize('ldap://active.directory.local')
ldap_obj.protocol_version=ldap.VERSION3
ldap_obj.set_option(ldap.OPT_REFERRALS, 0) # ????
ldap_obj.bind_s('ldap_bind_user', 'ldap_bind_password', ldap.AUTH_SIMPLE)
searchfilter = '(sAMAccountName=%(username)s)' % {'username': login}
@blzzua
blzzua / authors.json
Created November 27, 2022 17:54
example lib data
[{"patronymic": " ", "name": "Nikolai", "surname": "Amosov"}, {"patronymic": " ", "name": "Emma", "surname": "Andijewska"}, {"patronymic": " ", "name": "Sofia", "surname": "Andrukhovych"}, {"patronymic": " ", "name": "Yuri", "surname": "Andrukhovych"}, {"patronymic": " ", "name": "Ivan", "surname": "Bahrianyi"}, {"patronymic": " ", "name": "Mykola", "surname": "Bakay"}, {"patronymic": " ", "name": "Vasyl", "surname": "Barka"}, {"patronymic": " ", "name": "Mykola", "surname": "Bazhan"}, {"patronymic": " ", "name": "Natalia", "surname": "Belchenko"}, {"patronymic": " ", "name": "Nina", "surname": "Bichuya"}, {"patronymic": " ", "name": "Natalka", "surname": "Bilotserkivets"}, {"patronymic": " ", "name": "Dmytro", "surname": "Blazheyovskyi"}, {"patronymic": " ", "name": "Osip", "surname": "Bodyansky"}, {"patronymic": " ", "name": "Dniprova", "surname": "Chayka"}, {"patronymic": " ", "name": "Olena", "surname": "Chekan"}, {"patronymic": " ", "name": "Marko", "surname": "Cheremshyna"}, {"patronymic
@blzzua
blzzua / load_average.sh
Created September 28, 2022 13:20
Generates a cpu load average and maintains it in the form of a sinusoid, with a period in time.
MAXJOBS=20
for i in `seq 1 $MAXJOBS` ; do nice -n 19 gzip -c /dev/zero > /dev/null & done
while true
do
LA=`</proc/loadavg` && LA=${LA// */}
H=$(date +%M) && H=${H/0/}
G=`echo "(${MAXJOBS}/2)*(s(2*3.14159*${H}/60) +1 ) <= ${LA}" | bc -l`
test ${G} -lt 1 && for i in `seq 1 $MAXJOBS` ; do kill -SIGCONT %$i ; done || for i in `seq 1 $MAXJOBS` ; do kill -SIGSTOP %$i; done
sleep 1
def day_of_week(date):
""" get weekday from date (as instance 2022-12-31 iso format) """
#
year = int(date[0:4])
month = int(date[5:7])
day = int(date[8:10])
offset = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334]
week = ['Sunday',
'Monday',
'Tuesday',