Skip to content

Instantly share code, notes, and snippets.

View 1
SELECT COUNT(*) AS customers
DATE(time_of_visit)
FROM traffic
GROUP BY DATE(time_of_visit)
ORDER BY DATE(time_of_visit)
@OllieJones
OllieJones / fullquery
Last active Jul 28, 2021
Fast nearest-location finder for SQL (MySQL, PostgreSQL, SQL Server)
View fullquery
SELECT zip, primary_city,
latitude, longitude, distance
FROM (
SELECT z.zip,
z.primary_city,
z.latitude, z.longitude,
p.radius,
p.distance_unit
* DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
* COS(RADIANS(z.latitude))
@OllieJones
OllieJones / stored_procs
Last active Apr 16, 2020
SQL Reporting By Time Intervals
View stored_procs
DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_HOUR$$
CREATE
FUNCTION TRUNC_HOUR(datestamp DATETIME)
RETURNS DATETIME DETERMINISTIC NO SQL
COMMENT 'returns current hour'
RETURN DATE_FORMAT(datestamp, '%Y-%m-%d %H:00')$$
DROP FUNCTION IF EXISTS TRUNC_DAY$$
@OllieJones
OllieJones / cache.js
Last active Dec 3, 2016 — forked from bshamric/cache.js
I like phantomjs, but it doesn't directly support getting images from webpages without requesting them separately like in casperjs. I went through QTNetworking code in the phantomjs repo until I figured out where the cache was. To use this, have all three files in the same directory. Then modify test.js for whatever you need.Call phantom js with…
View cache.js
var pFs = require('fs');
//this is the path that QTNetwork classes uses for caching files for it's http client
//the path should be the one that has 16 folders labeled 0,1,2,3,...,F
exports.cachePath = '/path/to/phantomjs/cache/data/folder';
//object path
exports.objectPath = '/path/to/phantomjs/cache/objects';
//call this when you first encounter a resource//this is the extension used for files in the cache path
@OllieJones
OllieJones / RandomString.sql
Last active Feb 25, 2016
Cryptographic quality random text string generation in SQL Server
View RandomString.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Generate a 20-character cryptographically secure random string of uppercase letters and digits.
--- The entropy of such a string is about 100 bits: plenty.
-- Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4b5a6d1-1068-41e1-abd7-b3ec51d3f1c3/create-function-error?forum=transactsql
-- =============================================
CREATE VIEW RandomSeqView AS
@OllieJones
OllieJones / AddSignatureAlgs.cs
Created Mar 4, 2016
Add this code to your dotnet app to enable rsa-sha256, rsa-384, and rsa-512 signatures.
View AddSignatureAlgs.cs
/// <summary>Declare the signature type for rsa-sha512</summary>
public class RsaPkCs1Sha512SignatureDescription : SignatureDescription
{
public RsaPkCs1Sha512SignatureDescription()
{
KeyAlgorithm = typeof(RSACryptoServiceProvider).FullName;
DigestAlgorithm = typeof(SHA512CryptoServiceProvider).FullName;
FormatterAlgorithm = typeof(RSAPKCS1SignatureFormatter).FullName;
DeformatterAlgorithm = typeof(RSAPKCS1SignatureDeformatter).FullName;
}
View ActivateSigAlgoritms.cs
CryptoConfig.AddAlgorithm(typeof(RsaPkCs1Sha512SignatureDescription),
"http://www.w3.org/2001/04/xmldsig-more#rsa-sha512");
CryptoConfig.AddAlgorithm(typeof(RsaPkCs1Sha384SignatureDescription),
"http://www.w3.org/2001/04/xmldsig-more#rsa-sha384");
CryptoConfig.AddAlgorithm(typeof(RsaPkCs1Sha256SignatureDescription),
"http://www.w3.org/2001/04/xmldsig-more#rsa-sha256");
@OllieJones
OllieJones / DataTableResultSet.cs
Last active Mar 30, 2022
C# code for handling Ajax calls for the DataTables.net client table-rendering plugin.
View DataTableResultSet.cs
/// <summary>
/// Resultset to be JSON stringified and set back to client.
/// </summary>
[Serializable]
[SuppressMessage("ReSharper", "InconsistentNaming")]
public class DataTableResultSet
{
/// <summary>Array of records. Each element of the array is itself an array of columns</summary>
public List<List<string>> data = new List<List<string>>();
@OllieJones
OllieJones / explanation.md
Last active Jul 20, 2016
Detecting a disconnected RDP session in a unix / linux / bsd server.
View explanation.md

Here is a way to obtain a list of disconnected xrdp sessions. It relies on the fact that the xrdp server is, in normal X session manager usage, the only client that establishes a TCP connection to the Xvnc X Window System display server. (The other client programs -- the ones doing things for the user -- use UNIX-domain sockets for their display connections).

When an xrdp session is active, the associated Xvnc display server has two TCP connections, one in the ESTABLISHED state, and the other in the LISTEN state. That looks something like this using the lsof(1) program.

$ sudo lsof  -b -w -n -c /^Xvnc$/b -a -iTCP:5900-5999 
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
Xvnc    1625 guest    1u  IPv4 252910      0t0  TCP 127.0.0.1:5910 (LISTEN)
Xvnc    1625 guest    9u  IPv4 261226      0t0  TCP 127.0.0.1:5910->127.0.0.1:35242 (ESTABLISHED)

If the user of the remote session abandons it by closing the RDP connection (or, in the case of an Apache Guacamole RDP session

@OllieJones
OllieJones / nonce.js
Created Oct 3, 2017
Snippets of Javascript.
View nonce.js
'use strict';
/* node.js nonce: crypto-quality random session key (filename safe base64, rfc4648) */
const btoa = require( 'btoa' );
const crypto = require( 'crypto' );
function nonce( length ) {
const rndLen = 1+(( (length)+(length<<1) +3)>>2);
const randomArray = crypto.randomBytes( rndLen );