Category: Technology

Oracle – Select Top

I discovered the “rownum” trick early in my usage of Oracle databases — especially useful for sampling data to see what’s in there, something like “select * from dataTable where rownum < 6” gets you the first five records. But that’s not suitable if you want to sort the records. In this particular case, I have a series of names. I want to find the highest number value in the series so I can name my object with the next sequential name.

Enter “fetch first”  … this appears to be available since 12c (so older database installations may still require a more convoluted solution):

SELECT set_name from set_data
WHERE set_name LIKE 'Something-With-A-Series-%'
ORDER BY set_name DESC
fetch first 1 row only;

Which returns the last name in the series.

PHP Sub-Second Sleep

I needed to add a sleep to a PHP process, but I didn’t want to waste a whole second on each cycle. That’s usleep:

<?php
        date_default_timezone_set('America/New_York');

        $t = microtime(true);
        $micro = sprintf("%06d",($t - floor($t)) * 1000000);
        $d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

        print $d->format("Y-m-d H:i:s.u") . "\n";                                                                       
        usleep(100000);

        $t = microtime(true);
        $micro = sprintf("%06d",($t - floor($t)) * 1000000);
        $d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

        print $d->format("Y-m-d H:i:s.u")  . "\n";                                                                      
        sleep(1);
        $t = microtime(true);
        $micro = sprintf("%06d",($t - floor($t)) * 1000000);
        $d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

        print $d->format("Y-m-d H:i:s.u")  . "\n";                                                                      
?>

Run the script, and you’ll see sub-second sleeps.

[tempuser@564240601ac2 /]# php testSleep.php
2020-07-09 14:06:20.641449
2020-07-09 14:06:20.741952
2020-07-09 14:06:21.742347

Oracle – Group By Having

I needed a query to find records where duplicate name values exist. I know how to group by and count, but the table has millions of records. I don’t want the 99% of the data where no duplication occurs. By using “having” in conjunction with “group by”, I am able to restrict the output to the groups that match my criterion.

select display_name, count(display_name) from circuit
group by display_name
having count(display_name) > 1;

My result set is the display name & occurrence count for that display name without all of the ‘good’ records where there’s a unique display name. (Yes, I know uniqueness could be enforced. The real scenario isn’t this straight-forward. There are times where the display name should be the same and I’ve got additional filters that drop out those cases).

 

Apache HTTPD: SSL Virtual Hosts

For quite some time, you couldn’t bind multiple SSL web sites to a single IP:Port combination — this had to do with the mechanics of negotiating an SSL session — the client and server negotiated encryption based on a specific certificate before the server really knew what the client was trying to retrieve. The quick/easy solution was to just add a virtual IP to the box and bind each individual web site to a unique IP address. While this was quite effective in a corporate environment or purely internal network, it was a terrible solution for a set of home-hosted personal web servers — I don’t want to buy four public IP addresses to host four differently named websites. My workaround was to off-port sites no one else would be using (the MQTT WebSockets reverse proxy) and use a reverse proxy to map paths within the family website to the remaining web servers. This page, for instance, is rushworth.us/lisa … which the reverse proxy re-maps to https://lisa.rushworth.us behind the scenes.

With Apache HTTPD 2.2.12 or later built against OpenSSL v0.9.8g or later, you can use Server Name Indication (SNI) to serve multiple SSL websites from a single IP:Port just like you have been able to do with non-SSL sites. Using SNI, the client includes “what they’re looking for” in first message of the SSN negotiation process so the server knows which cert to serve.

In your httpd.conf, indicate that you want to use SNI on an IP:Port combo

# Listen for virtual host requests on all IP addresses
NameVirtualHost *:443

And, optionally, configure one of the named virtual hosts as the default for non-SNI browsers:

SSLStrictSNIVHostCheck off

Now the configuration for your SSL sites can include a ServerName directive. Restart Apache HTTPD, and you’ll be able to access the proper SSL-enabled website without adding virtual IP addresses.

HTML – Multiple Values on Select Option

I needed to pass multiple values with a select option. It’s easily accomplished by setting the value to a JSON string

while ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
	echo "<option value= " . json_encode($row) . ">" . $row['STRTYPENAME'] . "</option>\n";
}

And using JSON.parse to pull out the key of the value you need:

jQuery("#selectDivSetType").change(function () {     
    var strTemplateObject = $('#selectDivSetType').val();
    var jsonTemplateObject = JSON.parse( strTemplateObject );
    var strTemplateURI = './templates/' + jsonTemplateObject.STRTEMPLATENAME;
    $('#templateURI').attr("href", strTemplateURI); 
});

jQuery – Changing href When Drop-down Selection Changes

I needed to provide a different template depending on the type of activity selected in a drop-down menu. The following jQuery code gets the template name from the drop-down value and updates the href target. 

jQuery("#selectDivSetType").change(function () {     
    var strTemplateName = $('#selectDivSetType').val();
    var strTemplateURI = './templates/' + strTemplateName;
    $('#templateURI').attr("href", strTemplateURI); 
});

Exporting Microsoft Stream Transcript — Prettier Output and Error Handling

Updated script available at https://www.rushworth.us/lisa/?p=6854 — and, since copy/paste doesn’t seem to work for everyone, the script is also available as a text file.

I had posted a one-liner to grab the text content of the Microsoft Stream transcript — there’s a good bit of cleanup required to make something professional looking, but I’ve been lazy about it & leave formatting up to the recipient. The one-liner approach fails when it doesn’t encounter a text element where it expects to find one. A more robust export approach creates a Node List containing all of the transcript-line classed elements, then iterates through that list and when the node has a textContent attribute appends that content to a running string value. Printing the running string value produces output that needs minimal reformatting.

Code:

var objTranscriptLines = window.document.querySelectorAll('.transcript-line');
var strRunningText = null;
for(var i = 0; i < objTranscriptLines.length; i++){
    if( objTranscriptLines[i].textContent ){
        var strLineText = objTranscriptLines[i].textContent;
        strRunningText = strRunningText + "\n" + strLineText.replace("Discard   Save","");
    }
}
console.log(strRunningText);

Results:

You *could* strip off the timestamps as well — instead of strLineText.replace(“Discard Save”,””) use (strLineText.replace(“Discard    Save”,””)).substr(8)

Scratch: Changing Variable In Operator

When I needed to change a variable within an operator block, I’d been removing the variable block and adding the right one. Unlike the “change <variable> …” and “set <variable>…” blocks, the little variable name bubbles do not have a drop-down selector. Today, Anya and I were working on our Chicken Keepers games and she right-clicked the little bubble to select a different variable. Totally didn’t realize you could do that.