Advent Of Code 2020 in SQL - Day 1: "Report Repair"

The Advent of Code 2020 puzzle challenge has started – and I will try to participate for the first time. Since it’s the language I use most these days, I will try to solve all the puzzles with SQL and – if necessary – PL/SQL.

You can find the first question right here: Day 1

“Find the two entries in your input data that add to 2020 and multiply them”

Using SQL, this is not a tough challenge at all once we get the input into our database. For it requires the least effort, I just copied the contents of my input (the input changes for every user!) and passed it as comma-separated constructor-argument to a sys.odcinumberlist which is a predefined type table of number:

select column_value num
  from table(
    sys.odcinumberlist(
      1411,1802,1773,1775,1442,1471,1048,1403,1881,1930,1710,1717,685,1255,1451,1870,208,1725,1879,143,1372,1726,1357,1624,1378,1993,1721,1712,1867,1355,1743,1942,114,407,1892,1937,2001,1466,1461,1770,1441,1410,1915,1482,1512,1631,1954,1632,1788,1971,1989,1427,1684,1749,1795,1839,1358,1354,1591,1924,1456,2002,1746,1323,1946,1889,296,1908,1959,1944,1655,1602,1768,1666,1465,1782,1739,1472,1576,645,1496,1538,1761,1353,1639,1904,1765,1519,1948,1900,1376,1918,1950,667,1976,1925,1939,1319,1895,1510,1480,735,1674,1997,1868,1728,1893,1500,1363,1840,1905,1361,1894,1558,1369,1922,1367,1463,1365,1504,1898,1343,1436,1700,1911,1811,1829,1984,1444,1806,1455,1778,1835,1817,1668,1907,1748,2007,1534,1269,1473,1572,2006,1651,1853,1943,1968,1969,1437,1692,1955,1964,1821,1805,1999,1614,1754,1888,1832,1623,1723,1678,2008,1819,1595,1972,1229,1703,1762,1818,1062,1599,1996,2000,1960,1927,1407,1414,1923,1685,1998,1497,1687,1416,1757,1470,1810,2010,1553,1379,1495,1565,1796,2004,1899,2009,1395,1388,1902,1741
    )
  )

By moving this input into a WITH-clause, we can easily self-join and select the combination matching our needs.

with input as (
    select column_value num
    from table(
      sys.odcinumberlist( ... )
    )
)
select distinct
    input1.num*input2.num
from input input1
    cross join input input2
where input1.num + input2.num = 2020;

Part 2 is similarly easy: Now we need to find the 3 numbers that sum to 2020 and return their product. You can see the strength of a fourth generation language, because we just need to tell the database WHAT we want, not HOW to get there:

with input as (
    select column_value num
    from table(
      sys.odcinumberlist(...)
    )
)
select distinct
    input1.num*input2.num*input3.num
from input input1
    cross join input input2
    cross join input input3
where input1.num + input2.num + input3.num = 2020;

First puzzle solved – let’s see what’s next 🙂

You can find and try out this solution for both parts on Oracle LiveSQL

Bonus: How to get that input-data from the web?

My initial idea was to make my local dockerized Oracle DB connect to the adventofcode Website and get the results from there.

The obstacles are pretty high, though, because it requires to store the SSL certificate in an Oracle Wallet to even be able to connect via HTTPS and I would also need to authenticate and login, because the website generates a personalized set of input data for each user.

It is possible in PL/SQL, but it’s quite some work (and even more work to explain) – work I don’t wanted to do today (remember: Advent of Code is for fun).

Therefore you’ll get the slim version without HTTPS and authentication on a local webserver.

First, we need a webserver – for I use docker already, I set up a common nginx webserver which listens on port 80 and has a volume to my local file system:

docker container run --name aoc-nginx -v C:/dev/aoc/htdocs:/usr/share/nginx/html -p 80:80 -d nginx

I put the input-file as plain TXT in the htdocs folder, so it’s accessible via http://localhost/day_1_input.txt.

Next, we need to allow our Oracle database to communicate via HTTP and port 80 via ACL.

For “localhost” is different inside our database docker container, I use my current local IP address instead to set up Oracle ACL (as SYSDBA)

begin
    dbms_network_acl_admin.append_host_ace(
        host => '192.168.3.106',
        lower_port => 80,
        upper_port => 80,
        ace => xs$ace_type(
            privilege_list => xs$name_list('http'),
            principal_name => 'sithdb',
            principal_type => xs_acl.ptype_db));
end;
/

We can now use UTL_HTTP to connect to our local webserver and read the file. The contents can then be pushed into a sys.odcinumberlist and returned.

create or replace package advent_of_code as
  function get_day_1_input return sys.odcivarchar2list;
end;
/

create or replace package body advent_of_code as
  function get_day_1_input return sys.odcivarchar2list as
    l_result sys.odcivarchar2list := sys.odcivarchar2list();
    l_request utl_http.req;
    l_response utl_http.resp;
    l_value varchar2(1024);
  begin
    begin
      l_request := utl_http.begin_request(
        'http://192.168.3.106/day_1_input.txt');
      l_response := utl_http.get_response(l_request);
      loop
        utl_http.read_line(l_response, l_value, true);
        l_result.extend;
        l_result(l_result.last) := to_number(l_value);
      end loop;
    exception
      when utl_http.end_of_body then
        utl_http.end_response(l_response);
    end;
    return l_result;
  end;
end;
/

Now we can just replace our input-source in the WITH-clause with that new function.

with input as (
    select column_value num
    from table(advent_of_code.get_day_1_input())
)
select distinct
    input1.num*input2.num*input3.num
from input input1
    cross join input input2
    cross join input input3
where input1.num + input2.num + input3.num = 2020;

Happy SQLing!