The other day our office manager brought in an old style puzzle, that I used to do as a child.

After thinking about how I used to solve them, decided to let SQL do the grunt work for me:

I used lower case to mark the tail of the plane, and upper case to front of the plane.

The cards are encoded as these id’s

| 0 | 1 | 2 |
| 3 | 4 | 5 |
| 6 | 7 | 8 |

1
2
3
4
5
6
7
8
9
10
11
12

with data(id,a,b,c,d) as (
    select * from values 
    (0, 'b', 'w', 'B', 'Y' ),
    (1, 'y', 'g', 'B', 'W' ),
    (2, 'w', 'G', 'B', 'y' ),
    (3, 'b', 'y', 'G', 'W' ),
    (4, 'g', 'y', 'G', 'W' ),
    (5, 'b', 'w', 'G', 'Y' ),
    (6, 'B', 'Y', 'g', 'w' ),
    (7, 'g', 'w', 'B', 'Y' ),
    (8, 'w', 'y', 'B', 'G' )
)

then swapped those values to an encoding where “a correct match” sums to 10

1
2
3
4
5
6
7
8

), encode as (
    select id,
        decode(a, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as a,
        decode(b, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as b,
        decode(c, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as c,
        decode(d, 'b', 1, 'B', 9, 'g', 2, 'G', 8, 'y', 3, 'Y', 7, 'w', 4, 'W', 6 ) as d
    from data
)

and from there made the permutations of the 4 rotations of each tile

1
2
3
4
5
6
7
8
9

), tiles_rot as (
   select id
        ,decode((column1+0) % 4, 0 , a, 1, b, 2, c, 3, d) as a
        ,decode((column1+1) % 4, 0 , a, 1, b, 2, c, 3, d) as b
        ,decode((column1+2) % 4, 0 , a, 1, b, 2, c, 3, d) as c
        ,decode((column1+3) % 4, 0 , a, 1, b, 2, c, 3, d) as d
        ,column1 as r
   from encode cross join (values(0),(1),(2),(3)) as v
)

then wove those together with the don’t repeat the same tile rules, and enforce the “must match existing tiles on edges” rules for the 12 joins as the tiles are placed:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

select 
    a.id || ' (0)' as a,
    b.id || ' (' || b.r || ')' as b, 
    c.id || ' (' || c.r || ')' as c, 
    d.id || ' (' || d.r || ')' as d, 
    e.id || ' (' || e.r || ')' as e, 
    f.id || ' (' || f.r || ')' as f, 
    g.id || ' (' || g.r || ')' as g, 
    h.id || ' (' || h.r || ')' as h, 
    i.id || ' (' || i.r || ')' as i
from encode as a
join tiles_rot as b
    on b.id != a.id 
    and a.b + b.d = 10 
join tiles_rot as c
    on c.id != a.id and c.id != b.id 
    and b.c + c.a = 10 
join tiles_rot as d
    on d.id != a.id and d.id != b.id and d.id != c.id 
    and a.c + d.a = 10 
    and c.d + d.b = 10
join tiles_rot as e
    on e.id != a.id and e.id != b.id and e.id != c.id 
    and e.id != d.id 
    and d.d + e.b = 10 
join tiles_rot as f
    on f.id != a.id and f.id != b.id and f.id != c.id 
    and f.id != d.id and f.id != e.id 
    and f.c +e.a = 10 and f.b + a.d = 10
join tiles_rot as g
    on g.id != a.id and g.id != b.id and g.id != c.id 
    and g.id != d.id and g.id != e.id and g.id != f.id 
    and f.a + g.c = 10
join tiles_rot as h
    on h.id != a.id and h.id != b.id and h.id != c.id 
    and h.id != d.id and h.id != e.id and h.id != f.id 
    and h.id != g.id 
    and h.d + g.b = 10 and h.c + a.a = 10
join tiles_rot as i
    on i.id != a.id and i.id != b.id and i.id != c.id 
    and i.id != d.id and i.id != e.id and i.id != f.id 
    and i.id != g.id and i.id != h.id 
    and i.d + h.b = 10 and i.c + b.a = 10

and it takes just ~1 second to run in Snowflake on an extra small instance.

and the layout is in this pattern:

| C | D | E |
| B | A | F |
| I | H | G |

looking at the four solutions, they are two base solutions where two cards can be swapped.. is interesting.

Well I answered a “homework” question today, trying to help someone “learn”, and as the question evolved recommended follow-up question be asked in another question, answer those also, looked latter points lost, question gone?? following my browser history to the prior question to find it gone? Mention this on the new question and it also gets deleted.

https://stackoverflow.com/questions/69875135/sql-sum-of-multiple-records-on-same-date

https://i.stack.imgur.com/qBQR6.png

https://stackoverflow.com/questions/69877332/fill-in-missing-dates-in-table-sql

Ok, so cloud_user / cloudaemon you knew you were meant to not be asking on StackOverflow, then don’t!

So I previously put a Lambda@Edge function in place to fix the \blog\post\ requests to become \blog\post\index.html which is what the files are, and I also put a 403/404 error handler page to catch those 404 and log then to Analytics events, and sure enough, there where a number of pages that had been have errors.

I fixed the internal page links, assuming those where the primary dirvers, and moved on.

So in the weekend I rewrote the lambda@edge function from

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

function handler(event) {
    var request = event.request;
    var uri = request.uri;
    
    // Check whether the URI is missing a file name.
    if (uri.endsWith('/')) {
        request.uri += 'index.html';
    } 
    // Check whether the URI is missing a file extension.
    else if (!uri.includes('.')) {
        request.uri += '/index.html';
    }

    return request;
}

to a new flashy one, that has a list of urls to rewrite. I was originally doing to use a javascript Set but those are not supported, so went for a dictionary.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

'use strict';

var patch_url = 'https://simeonpilgrim.com/nikon-patch/nikon-patch.html';
var patch_response = {
	statusCode: 302,
	statusDescription: 'Found',
	headers: 
		{ "location": { "value": patch_url } }
};

var patch_redirect_set = {'/nikon-patch/Nikon-Patch.html': true,
	'/nikon-patch/nikon-patch-beta.html': true,
	'/nikon-patch-test/index.html': true,
	'/nikon-patch': true,
	'/nikon-patch/': true,
	};

function handler(event) {
    var request = event.request;
    var uri = request.uri;
    
    if( patch_redirect_set[uri] ){
		return patch_response;
	}
	
	if( patch_redirect_set[uri] ){
		return patch_response;
	}
    // Check whether the URI is missing a file name.
    if (uri.endsWith('/')) {
        request.uri += 'index.html';
    } 
    // Check whether the URI is missing a file extension.
    else if (!uri.includes('.')) {
        request.uri += '/index.html';
    }
    
    return request;
}

I rather like the Test page on the lambda pages, so once it was not failing, I published it, and tested. And it worked, so there we are. A framework to fix urls. It might make more sense to put the mapping in the dictionary, and if I end up wanting to fix non-patch pages I might go that route. But it’s more less wrong. So that’s a win.

Download the firmware you are wanting to improve. Steve Goossens has a really nice patching guide that has links to the Nikon download archives, if you are looking for older firmware.

Open the firmware in the Nikon-Patch Local UI tool (It is a C# project, so you will need a windows pc and Visual Studio 2019 Community to run it) and decode/exact the firmware

The main UI firmware is the B firmware, we will open that in Ghidra

So demonstrating with the D3300 1.02 firmware, we get the files a860_010101.bin and b860_102.bin from the decode step.

So create a new Ghidra project, then import the B firmware

Then select the ARM v7 little endian

Finding the firmware offset

For now ignore the Options, but we will come back and load the file at the correct offset later. Also for the next couple of step don’t save anything, as we will be deleting and re-importing files as we work out, where the files should be loaded.

Once we open the file, you will be prompted to “Auto disassemble this file” say No you that, and press d key on the first line

this will look like:

    //
    // ram 
    // ram:00000000-ram:016bff89
    //
         assume spsr = 0x0  (Default)
                         Reset                                           XREF[1]:     Entry Point(*)  
    00000000 18 f0 9f e5     ldr        pc=>LAB_90352fe0,[DAT_00000020]                  = 90352FE0h

This is noting the B firware reset jump address is loaded from 0x20 in the firmware, which is address 0x90352FE0, and Nikon B firmwares follow a pattern of being at 0xNN020000 thus this model camera is loaded at 0x90020000

Now we have the correct offset, close the CodeBrowser and don’t save

Deleted the firmware from the project, and confirm yes to permanently deleting

and reimport, but this time set the memory offset

Now we open the CodeBrower again, and again say no to the Analyze option. Because this time we are wanting to learn the address the itron kernel gets loaded to, and if we have that memory mapped before the auto magic process begins, there is a lot of errors avoided.

So here we are going to decode the first jump, and follow that twice, and then we will be in the reset function (at 0x90352fe8 hit f to mark that as a function) and then scroll there are some sections where a repeating pattern occurs, that is a serries of MemCopy operations.

clicking on the addres 9035329f we see some code that looks like this. It’s loading 48 bytes into all the spare registers via the LDMIA instruction and then writing them to the destination. This is the memcopy we are looking for.

So now we can right click on the function in the Disassembler window and Edit Function Signature

and then the jumping back to the reset (Alt+LeftArrow steps back) function will look like:

FUN_903533a0(PTR_DAT_903531cc,PTR_DAT_903531d0,0xae000000);
FUN_90020098();
memcopy((undefined4 *)PTR_LAB_903531d4,DAT_903531d8,DAT_903531dc + 3U & 0xfffffffc);
memcopy((undefined4 *)PTR_LAB_903531e0,DAT_903531e4,DAT_903531e8 + 3U & 0xfffffffc);
memcopy((undefined4 *)PTR_LAB_903531ec,DAT_903531f0,DAT_903531f4 + 3U & 0xfffffffc);
memcopy((undefined4 *)PTR_DAT_903531f8,DAT_903531fc,DAT_90353200 + 3U & 0xfffffffc);
memcopy((undefined4 *)PTR_DAT_90353204,DAT_90353208,DAT_9035320c + 3U & 0xfffffffc);
memcopy((undefined4 *)PTR_DAT_90353210,DAT_90353214,DAT_90353218 + 3U & 0xfffffffc);
FUN_900200a4();
dst = (undefined4 *)((DAT_9035321c & 0xfff00000) + 0x100000);

which looks strange, because the destination pointers are not followed. But we can alter the memory mapping to drop Write from the ROM area

and the code then becomes:

memcopy((undefined4 *)&LAB_9158a358,(undefined4 *)0x10000000,0x3b5b4);
memcopy((undefined4 *)&LAB_915c590c,(undefined4 *)0x1003b5b4,0x179c);
memcopy((undefined4 *)&LAB_915c590c,(undefined4 *)0x1003b5b4,0);
memcopy((undefined4 *)&DAT_916a73a0,(undefined4 *)0x1011d420,0x28b3c);
memcopy((undefined4 *)&DAT_916a73a0,(undefined4 *)0x1011d420,0);
memcopy((undefined4 *)&DAT_916a739c,(undefined4 *)0x1011d03c,4);

Now we know the offsets we need to add as memory maps, on the very screen we used to fix the ROM value, the “Memory Map” screen. Really only those three blocks are needed to be mapped, these are RAM area’s and there is some general RAM that needs to be identified, but for now this is the start we need.

Which is exactly what I have been noting in the Firmware Notes in the code repo, when I remember.

So now we can delete all this again, and load the code base firmware at the correct location, and add the memory maps, and then press Auto Analyze and walk away for a cup of tea.

Adding the Kernel memory maps

So using the find or Firmware Notes values we want to add the three blocks of memory for the Code, Data, and ? third data segments:

repeat for RAM2 and RAM3

and you will end up with

Now it’s time to Auto Analyze

Going forward from here

There are number of landmarks to look out for, there is a chunk of kernel debugging, that you can find the strings for, and then find the stubbed print finctions, thus find all calls, those give a lot of details, there is a small amount of RTTI information, there are some documented protocols like the TIFF/JPG header writing code, the PTP handlers are driven by tables, so are quite easy to find, the EyeFi code can be found quickly.

And then there is the method of loading other camera models firmware, side-by-side, and looking for the addresses of existing patches and finding the related code.

I intend to write more on most of these things, but it also has taken me 4+ years to write this up, so it’s not going to happen overnight…

So at some point, I came across this picture, I feel it was some quality corporate compliance training. Anyways, it had these great chaps, collaborating, and synergising over stuff on those computers screens..

Only problems, is those devices are very much “not plugged in” now I know, cables are ugly, and staged shots look best when they are not “real” I get it. But it also irks me. Maybe this is just be saying “I still have not grown up enough to not talking about meaningless things that irk me”.. yes that might be the only value. Still not there yet.

Google image search shows it used in a number of places so it seems like it’s from some Stock photo library.

The time has come that the trusty SageTV has been turned off.

This year has not been the best for the SageTV and to be honest, all the problems where between my server (Win10) and the HDHomeRun. Back in September 2020, after a Windows Update, the SageTV process failed to connect to the HDHomeRun. After much fluffing around I installed OpenDCT and got that working, and SageTV could see that.

The a couple of weeks back both Sage/OpenDCT and EPG Collector stopped being able to see my HDHomeRun. Again more fluffing around, installing updates, I got it so the HDHomeRun tools could see the channels, and turn to stuff, and a little while later, so could SageTV see live TV, but EPG Collector never had any luck.

Then Michaela called it and said, the streaming apps where good enough, and it could be shut down. At one level I was super glad, to get to stop thinking about what to try next, or that I really should get back on to that task. And another level, super said to give up on the system. I had been a really neat tool for the last 10 years.

So other neat things, are I have freed up a chunk of disk space. And deleted multiple copies of installations, that were keep to debug changes. And deleted most the code changes I had done to SageTV, and EPG Collector, and tweaks to OpenDCT (I think those were submitted). Anyways. Letting code changes go. Feels really relaxing, permission to just forget those TODO’s.

So now I have no Scheduled tasks, and can allow the PC to turn off at night.. so that all feels better, going forward.

I really value the community efforts of: SageTV, EPG Collector, Comskip, OpenDCT, XMLTV Importer for SageTV

So my friends suggested AWS S3 as a good way to host static blog site, and I had been meaning to do this transition for a few years, but just never put to high on the to-do list.

I decided to use S3 & CloudFront in the REST API endpoint as the origin, with access restricted by an OAI method. I also choose to make CloudFront accept only HTTPS, I was moving to the future..

I then hunted through backups looking for all the files, as I was super lazy and only had sub-folders of the site save on my current PC, and then uploaded all that to s3. I turned on CloudFront via the great temp domain: https://dyk3v11u6y5ii.cloudfront.net/nikon-patch/nikon-patch.html which still works!

Once the domain transferred, had more days while things propagated, before Route53 would let me add a cert, and after adding that is ages ~hour to propagate, so you can have the auto-fill in CloudFront to select the alias domain. All fun stuff, trying to working why it was not working..

Then I setup Lambda@Edge script to auto convert the blog/postname/ requests into /blog/postname/index.html which is how the files actually are in S3, not said so far, but AWS has really good documentation, that major flaw is you find the wrong doc’s when googling, but once you are seeing the current/correct Lambda@Edge doc’s the UI/Code makes sense. The Doc‘s I followed, and the script I more or less used.

I installed a 403 error handler, that loads my 404-page.html page, which has a Google Analytics Event so I can see which pages are getting 404.

I used to have a rather large collection of url rewrites on the Windows server, so once this was done my base page hits was more aligned with how it used to be:

Once I had the 404 page, I found my own blog posts where the major source of bad links, joys of a Window webhost, all files are equal.

I did a number of alteration to Hexo modules so the blog behaves as I wanted, and a large amount of mass editing of old posts, but those can have their own posts, in the future.

But so far, the monthly bills ignoring the domain transfer have all been sub $1 nzd/month (and $0.50 is the fixed Route 53 charge), so it’s a rather pleasing savings.

So on 30th of May 2021 I woke to some helpful emails, and an issue posted on github saying “your domain has expired”. Which was super strange, given I had a couple of years of domain registration to go..

If you went to any page on simeonpilgrim.com sure enough it appeared “like the domain was expired”

I logged into the hosting company “kiwigeeks.net” customer service area, and I could see I had a couple years of domain left, but when I tried log into the HELM interface on the servers I could not log in. Back to the customer service area, and I “rest” my HELM passwords, but those didn’t work.

It was starting to look like someone had hijacked the Window Server 2008 boxes (strange that).

The DNS records in the customer service area still showed correct readings, but using Googles Dig tool showed the where some other box other that the correct hosts, ns1.techservers.net & ns2.techservers.net if you are a previous customer of KiwiHosting and using their Windows hosting $10nzd/mth then you stuff might also be dead.

Anyways I posts a request for help:

But after a couple of hours of not being able to regain control of the host, I decided it was time to leave KiwiGeeks. I should have left WikiGeeks years ago, like when the stuffed up my domain renewal

So I pinged my friends on Discord, and the suggested solution was AWS S3 hosting (which worked well as I had already moved to static content via Hexo)

So I spent some hours reading, how to use S3, CloudFront to host a “static blog”. Notes on setting up new server

I kicked off a domain transfer from KiwiGeeks to AWS Route53 (it was a toss-up between AWS DNS services and Google, the Discord team where split of this, but in the end the costs are about the same but all in one shop, felt like it might work out better for me.). It turned out “lucky” that the domain transfer “code” functionality still worked, and then I got to wait. I was “super” excited to receive the automated email, from KiwiGeeks saying they would sit on the transfer for 5 days, for safety reason. I mean that is sensible, except this was an exception.. or maybe not, not sure I would have wanted the hijackers to have taken my account “super fast”. Anyways at the time it was super slow, and now it’s just done.

Anyways, KiwiGeeks.net support never got back to me, and I updated my request to “close my account”, and they still ignored me. So a large part of the this post is to demonstrate that I tried with them, and that I will not “pay” any outstanding debt if they are so stupid to try action it.

Welcome to Hexo! This is your very first post.

Well first post it is not, but sure first Hexo post. Well my old Wordpress blog was getting rather old. And I made a couple of mistakes in updating the server, which meant I’ve not been able to post for over a year.

Not that I have posted anything in 2016, but things have moved on in many dimensions, and at time I’d like to be able to document stuff (for myself) again.

Given my host cannot “upgrade php on the server” a wtf in of itself, to a version that support the current wordpress I need to get off it. So I almost randomly choose Hexo. And have bene altering the defaults somewhat, imported the old blog (and comment) and done a lot of editing to the formatting (with heaps more to go).

So now this needs some testing to see how it all goes live in production..

Anyways there is not comments - email me at simeon.pilgrim@gmail.com and I mainly add your comment if it’s make sense..

I have been using http://www.snowflake.net for a new data processing at work for a few months, and it’s just amazing, to be able to run large queries over large data-sets, and the ability to increase the cluster size, when doing development work to get faster turnarounds, that are not impacting the production cluster, brilliant.

One of the thing I have noticed is slower than I would like is joins based on tableA.time being inside a time-range of tableB.start and tableB.end when the time period being is in the months not days.

So the pattern mapping a value from TABLE_I onto all rows in the time span (not including the end)

CREATE OR REPLACE TEMPORARY TABLE WORKING_B AS
SELECT tp.u_id, tp.time, i.value
FROM TABLE_P tp
JOIN TABLE_I i ON tp.u_id = i.u_id
AND tp.time >= i.start_time AND tp.time < i.end_time;

For one set of data spanning 3 months the above takes 45 minutes on a small cluster for TABLE_P 65M/TABLE_I 10M rows.
Where-as for a similar set of 4 days, and ~45M rows this takes 30 seconds.

So I add some TO_DATE(time), TO_DATE(start_time) columns to the two tables, and then added AND tp.time_day = i.start_time_day and the first query went to ~60 seconds. But I was missing a few million rows as my time ranges span multiple days…

So I did many things that didn’t work (like trying to use a GENERATOR with dynamic input) and settled on a simple solution

CREATE OR REPLACE TABLE TEN_YEARS_OF_DAYS(date) AS
SELECT DATEADD(day, (rn-1),DateADD(months,4,DATEADD(years,-10,CURRENT_DATE))) FROM (
SELECT row_number() over(order by 1) as rn
FROM TABLE(GENERATOR(rowCount => 365*10)) v);

CREATE OR REPLACE FUNCTION get_dates_for_N_days ( start_date DATE, end_date DATE )
RETURNS TABLE (date DATE)
AS 'SELECT date FROM TEN_YEARS_OF_DAYS WHERE date BETWEEN start_date AND end_date';

so this creates a table with ten years of data (moved 4 months into the future) and a table function that selects the rows from it, so I can do a lateral join on that function

CREATE OR REPLACE TEMPORARY TABLE TABLE_I_B AS
SELECT e.*, t.date as range_day_part
FROM TABLE_I e, LATERAL get_dates_for_N_days(TO_DATE(e.start_time), TO_DATE(e.end_time)) t;

So the above code creates another temp table with a row per table B record with every Day is a duplicate row, now we have more rows in the seconds table, but we can do a date based match to speedup the query.

CREATE OR REPLACE TEMPORARY TABLE WORKING_B_B AS
SELECT tp.u_id, tp.time, i.value
FROM TABLE_P_B tp
JOIN TABLE_I_B i
ON tp.u_id = i.u_id AND tp.time_day = i.range_day_part
AND tp.time >= i.start_time AND tp.time < i.end_time;

This code runs in 60 seconds and gives the same results as the 45 minute code.

Things to note, putting LATERAL table joins on a selects with CTE’s presently breaks the SQL parser, in fact even nested selects and LATERAL don’t mix, thus the extra tables with _B etc. Also CTE’s make life so much easier, but as you start joining to them a lot, performance slips, I have found where I do a complex join is a good time to output to a temporary table, and the performance again is crazy…

Snowflake is just a beast. I have a man crush…