DEV Community: Samuel Nitsche

Deeply Nested XML and JSON with SQL

Samuel Nitsche — Thu, 16 Feb 2023 20:46:57 +0000

I recently wrote about how much I like the XML generating capabilities of modern SQL, but I still struggle once in a while with creating XML (and JSON) that is nested into more than just one or two levels.

Therefore I want to share another code example about how to create XML that is deeply nested.

We’re building on the same example than last time, but our characters now also have a faction attribute and we want to group by that faction:

Full setup

create table characters (
  id integer not null primary key,
  name varchar2(100) unique,
  faction varchar2(100)
);

create table favorite_food (
  character_id integer not null references characters( id ),
  food varchar2(4000)
);

insert into characters values (1, 'Chewbacca', 'Rebels');
insert into characters values (2, 'Darth Vader', 'Empire');
insert into characters values (3, 'Jar Jar Binks', 'Rebels');

insert into favorite_food values ( 1, 'Grilled Pork');
insert into favorite_food values ( 1, 'Cooked Pork');
insert into favorite_food values ( 1, 'Raw Pork');
insert into favorite_food values ( 2, 'Cheesecake');

commit;

The result we want to get is the following XML:

<Factions>
  <Faction name="Empire">
    <Characters>
      <Character>
        <name>Darth Vader</name>
        <favouriteFoods>
          <food>Cheesecake</food>
        </favouriteFoods>
      </Character>
    </Characters>
  </Faction>
  <Faction name="Rebels">
    <Characters>
      <Character>
        <name>Jar Jar Binks</name>
        <favouriteFoods/>
      </Character>
      <Character>
        <name>Chewbacca</name>
        <favouriteFoods>
          <food>Grilled Pork</food>
          <food>Cooked Pork</food>
          <food>Raw Pork</food>
        </favouriteFoods>
      </Character>
    </Characters>
  </Faction>
</Factions>

But if we just throw in another xmlagg element, we will get an ORA-00935: Group nested too deeply:

select
  xmlserialize(
    document
    xmlelement(
      "Factions",
      xmlagg(
        xmlelement(
          "Faction",
          xmlattributes(c.faction as "name"),
          xmlelement("Characters",
            xmlagg(
              xmlelement("Character",
                xmlforest(
                  c.name as "name"
                ),
                xmlelement("favouriteFoods",
                  xmlagg(
                    xmlforest(
                      ff.food as "food"
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
    as clob indent size = 2
  )
from characters c
  left outer join favorite_food ff on c.id = ff.character_id
group by faction

This makes sense, since how should the database know what you want to group at which level?

It’s all fine as long as you group into two different levels: You have one level that goes with the group by values and one level that can be filled with the grouped values. But as soon as you want to nest deeper, you will need to use a different approach: subqueries:

select
  xmlserialize(
    document
    xmlelement(
      "Factions",
      xmlagg(
        xmlelement(
          "Faction",
          xmlattributes(c.faction as "name"),
          xmlelement("Characters",
            xmlagg(
              xmlelement("Character",
                xmlforest(
                  c.name as "name"
                ),
                xmlelement("favouriteFoods",
                  (
                    select
                      xmlagg(
                        xmlforest(
                          ff.food as "food"
                        )
                      )
                    from favorite_food ff
                    where c.id = ff.character_id
                  )
                )
              )
            )
          )
        )
      )
    )
    as clob indent size = 2
  )
from characters c
group by faction

If the subqueries get too complex, we can also use a WITH clause to structure our query a bit better into different chunks:

with character_foods as (
  select
    character_id,
    xmlagg(
      xmlforest(
        food as "food"
      )
    ) xml
  from favorite_food
  group by character_id
)
select
  xmlserialize(
    document
    xmlelement(
      "Factions",
      xmlagg(
        xmlelement(
          "Faction",
          xmlattributes(c.faction as "name"),
          xmlelement("Characters",
            xmlagg(
              xmlelement("Character",
                xmlforest(
                  c.name as "name"
                ),
                xmlelement("favouriteFoods",
                  (
                    select xml
                    from character_foods cf
                    where cf.character_id = c.id
                  )
                )
              )
            )
          )
        )
      )
    )
    as clob indent size = 2
  )
from characters c
group by faction

It’s still a bit hard to read and I encourage you to be very strict with indentation and formatting, because otherwise you might get lost quickly. But it is still an awesome and relatively easy way to get deeply nested XML structures out of your database.

But you mentioned JSON in the Title! I want JSON!

Here you go – the absolute same mechanics can be used to create a deeply nested JSON structure. As usual, it is less verbose than XML:

select
  json_serialize(
    json_arrayagg(
      json_object(
        faction,
        'characters' value json_arrayagg(
          json_object(
            name,
            'favoriteFoods' value (
              select
                json_arrayagg(
                  ff.food
                )
              from favorite_food ff
              where c.id = ff.character_id
            )
          )
        )
      )
    )
    pretty
  )
from characters c
group by faction;

This will create the following result:

[
  {
    "faction" : "Empire",
    "characters" :
    [
      {
        "name" : "Darth Vader",
        "favoriteFoods" :
        [
          "Cheesecake"
        ]
      }
    ]
  },
  {
    "faction" : "Rebels",
    "characters" :
    [
      {
        "name" : "Jar Jar Binks",
        "favoriteFoods" : null
      },
      {
        "name" : "Chewbacca",
        "favoriteFoods" :
        [
          "Grilled Pork",
          "Cooked Pork",
          "Raw Pork"
        ]
      }
    ]
  }
]

Have a lot of fun generating deepest depths JSON and XML structures!

The post Deeply Nested XML and JSON with SQL appeared first on Developer Sam.

Get a list of Quarters by Year with Recursive CTEs (WITH-clause)

Samuel Nitsche — Fri, 10 Feb 2023 16:22:23 +0000

The introduction of Recursive CTEs (recursive WITH-clauses) turned the SQL:1999 standard into a turning complete programming language. That means (simplified) that a language can solve every computational problem, given infinite resources.

As you can see in the name of the standard and the picture, it’s been around for quite a while and is implemented in most relational databases.

It is a compelling addition and can – besides other things – be used to create rows “from thin air”.

For example, we can generate a list of quarters by year for whatever range we want:

with year_quarter(quarter, year) as (
  -- Recursive CTEs always start with a base row
  select 1 quarter, 2023 year from dual
  union all
  -- and are then followed by sql that describes
  -- how each following row is created
  select
    -- For quarter we count backwards until 1 is reached
    case
      when prev.quarter <= 1 then 4
      else prev.quarter-1
    end as quarter,
    -- For year we only count backwards if quarter is 1
    case
      when prev.quarter <= 1 then prev.year-1
      else prev.year
    end as year
  from year_quarter prev
  -- it's very important to have a limit for the recursion
  where prev.year >= 2019
    and prev.quarter between 1 and 4
)
select * from year_quarter
-- We will get one 2018 row by the recursive CTE
-- so let's remove it
where year >= 2019;

This will give us a nice list of quarters and years:

You might wonder why we have the and prev.quarter between 1 and 4 in the CTE, because we wouldn’t really need it.

If you remove it, though, you will get an ORA-32044 error that cycles were detected in your recursion. This is because by default Oracle uses the column aliases that are used in the where clause of the second select to determine which columns will form a cycle.

In this case, a row forms a cycle if one of its ancestor rows has the same values for all the columns in the column alias list for query_name that are referenced in the WHERE clause of the recursive member.

You can circumvent this by adding a cycle clause in which you define the columns that are used for cycle detection:

with year_quarter(quarter, year) as (
...

  from year_quarter prev
  where prev.year >= 2019
)
cycle quarter, year set is_loop to 'Y' default 'N'
select quarter, year 
from year_quarter
where year >= 2019;

Getting the start and end date of each quarter

With this baseline data, we can now also select the start and end dates of each quarter, using the last_day function that is built-in in Oracle:

with year_quarter(quarter, year) as (
  select 1 quarter, 2023 year from dual
  union all
  select
    case
      when prev.quarter <= 1 then 4
      else prev.quarter-1
    end as quarter,
    case
      when prev.quarter <= 1 then prev.year-1
      else prev.year
    end as year
  from year_quarter prev
  where prev.year >= 2019
    and prev.quarter between 1 and 4
),
dates as (
    select
      year,
      quarter,
      to_date(year||'-'||(quarter*3-2), 'YYYY-MM') start_date,
      -- we're using the very handy last_day function here
      last_day(to_date(year||'-'||(quarter*3), 'YYYY-MM')) end_date
    from year_quarter
  )
select *
from dates
where year >= 2019;

This gives us some very nice base data to be combined with other queries (e.g. getting the number of arrivals and departures of spaceships for each quarter, even if there are quarters with no arrivals at all)

Have fun and never forget: SQL is your friend!

The post Get a list of Quarters by Year with Recursive CTEs (WITH-clause) appeared first on Developer Sam.

XML-Generation in SQL with Namespaces

Samuel Nitsche — Fri, 03 Feb 2023 16:45:25 +0000

One of my favorite features of (Oracle-)SQL is how easy it is to turn relational data into other data formats like JSON or XML.

Let’s assume we have some data in the tables character and favorite_food :

create table characters (
  id integer not null primary key,
  name varchar2(100) unique
);

create table favorite_food (
  character_id integer not null references characters( id ),
  food varchar2(4000)
);

insert into characters values (1, 'Chewbacca');
insert into characters values (2, 'Darth Vader');

insert into favorite_food values ( 1, 'Grilled Pork');
insert into favorite_food values ( 1, 'Cooked Pork');
insert into favorite_food values ( 1, 'Raw Pork');
insert into favorite_food values ( 2, 'Cheesecake');

Selecting will lead to a table like this:

select *
from characters c
  inner join favorite_food ff on c.id = ff.character_id;

ID	NAME	CHARACTER_ID	FOOD
1	Chewbacca	1	Grilled Porg
1	Chewbacca	1	Cooked Porg
1	Chewbacca	1	Raw Porg
2	Darth Vader	2	Cheesecake

With SQL, it is very easy to turn this into a nested XML structure:

select
    xmlelement("Characters",
      xmlagg(
        xmlelement("Character",
          xmlforest(
            c.name as "name"
          ),
          xmlelement("favouriteFoods",
            xmlagg(
              xmlforest(
                ff.food as "food"
              )
            )
          )
        )
      )
    )
from characters c
  inner join favorite_food ff on c.id = ff.character_id
group by name

<Characters>
    <Character>
        <name>Chewbacca</name>
        <favouriteFoods>
            <food>Grilled Pork</food>
            <food>Raw Pork</food>
            <food>Cooked Pork</food>
        </favouriteFoods>
    </Character>
    <Character>
        <name>Darth Vader</name>
        <favouriteFoods>
            <food>Cheesecake</food>
        </favouriteFoods>
    </Character>
</Characters>

But what if we need to add XML Namespaces?

XML is a very powerful and therefore sometimes a bit complex format. For example, it comes with namespaces to allow different structures to build on each other without name conflicts.

What if we require all the nodes to start with our very own http://developer-sam.de/codeexamples namespace?

It’s relatively easy to get a prefix into a node name, since xmlelement allows anything in the name element:

select
    xmlelement("sam:Characters",
...

But how do we get that xmlns:sam="http://developer-sam.de/codeexamples" into our main node?

Maybe that’s easy to answer for you, but I needed a while of thinking and trying out today before it hit me:

The xmlns in an XML is just an ordinary XML attribute.

And therefore we can easily add it:

select
    xmlelement("sam:Characters",
      xmlattributes(
        'http://developer-sam.de/codeexamples' as "xmlns:sam"
      ),
      xmlagg(
        xmlelement("sam:Character",
          xmlforest(
            c.name as "name"
          ),
          xmlelement("favouriteFoods",
            xmlagg(
              xmlforest(
                ff.food as "food"
              )
            )
          )
        )
      )
    )
from characters c
  inner join favorite_food ff on c.id = ff.character_id
group by name

And Oracle even puts in the xmlns wherever it is used later in the XML:

<sam:Characters xmlns:sam="http://developer-sam.de/codeexamples">
    <sam:Character xmlns:sam="http://developer-sam.de/codeexamples">
        <name>Chewbacca</name>
        <favouriteFoods>
            <food>Grilled Pork</food>
            <food>Raw Pork</food>
            <food>Cooked Pork</food>
        </favouriteFoods>
    </sam:Character>
    <sam:Character xmlns:sam="http://developer-sam.de/codeexamples">
        <name>Darth Vader</name>
        <favouriteFoods>
            <food>Cheesecake</food>
        </favouriteFoods>
    </sam:Character>
</sam:Characters>

I find this very neat!

The post XML-Generation in SQL with Namespaces appeared first on Developer Sam.

Dead easy NULL-aware comparison in Oracle with DECODE

Samuel Nitsche — Mon, 16 Jan 2023 21:56:41 +0000

One of the probably most unnerving things in databases is dealing with NULLs, especially when comparing data (e.g. looking for some data with the use of other data where you can’t be sure if either of them will be NULL).

Consider the following data:

NAME	AGE
Chewbacca	86
NULL	NULL

If we would join that data with itself and compare the names, we would expect the following result:

NAME1	NAME2	NAMES_MATCH
Chewbacca	NULL	not equal
Chewbacca	Chewbacca	equal
NULL	NULL	equal
NULL	Chewbacca	not equal

This is achievable by the following SQL statement, using CASE…WHEN:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null , null from dual
)
select
  td1.wookie_name wookie_name1,
  td2.wookie_name wookie_name2,
  case when (
          td1.wookie_name is not null
      and td2.wookie_name is not null
      and td1.wookie_name = td2.wookie_name)
    or (
          td1.wookie_name is null
      and td2.wookie_name is null)
    then 'equal'
    else 'not equal'
  end names_match
from test_data td1
  cross join test_data td2

Pretty verbose and not exactly pretty. We can, however, remove the and td2.wookie_name is not null part, because if NAME1 is not NULL, a comparison with NULL will always fail.

But still, the comparison is very verbose and it’s a lot to type.

If we are on Oracle however, we can remove that whole CASE statement by one simple DECODE call:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null , null from dual
)
select
  td1.wookie_name wookie_name1,
  td2.wookie_name wookie_name2,
  decode(td1.wookie_name, td2.wookie_name, 'equal', 'not equal') names_match
from test_data td1
  cross join test_data td2

And yes, it works for all data types:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null , null from dual
)
select
  td1.age age1,
  td2.age age2,
  decode(td1.age, td2.age, 'equal', 'not equal') age_match
from test_data td1
  cross join test_data td2

And yes, it can be easily used in the where clause:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null , null from dual
)
select
  td1.wookie_name wookie_name1,
  td2.wookie_name wookie_name2
from test_data td1
  cross join test_data td2
where decode(td1.wookie_name, td2.wookie_name, 1, 0) = 1

So the next time you are comparing values that could be NULL and want to write a complicated CASE…WHEN statement – think about your friend DECODE and let it do its magic!

(Thank you very much Jacek Gebal for showing me this little trick)

The post Dead easy NULL-aware comparison in Oracle with DECODE appeared first on Developer Sam.

“Stop Hiding From Your Planet” – Interview with Steven Feuerstein about Databases, PL/SQL, and Direct Positive Action

Samuel Nitsche — Tue, 12 Jul 2022 20:04:08 +0000

Some weeks ago, I did an interview with Steven Feuerstein for the DOAG RedStack magazine.

Steven has been a major inspiration for me during the past couple of years, both professionally and personally. He was one of the people who really encouraged me on my speaker journey and provided a lot of thoughtful and honest advice whenever I reached out to him.

Since the RedStack article is in German, only, I will provide the English version (with friendly permission of DOAG e.V.) here. I think what Steven has to say is worth being heard by a lot of people.

Samuel: Thank you Steven for taking the time to chat with us. Do you want to introduce yourself?

Steven: No, it’s an interview, you have to ask the questions (laughing)

Samuel: Okay, then I will make a short introduction: Steven is one of the most knowledgeable public figures and famous advocates when it comes to Oracle’s PL/SQL language.

Steven: I like to call myself a “popularizer”. Though many might find it hard to believe, I am not very technical, not a computer scientist. Sometimes I am referred to as “the father of PL/SQL” and I have to tell you if I created PL/SQL it would not be very good. But I seem to be good at communicating ideas and making developers laugh.

Samuel: “Popularizer” – I love that term! You also have written ten books on Oracle and PL/SQL, given hundreds of talks and keynotes around the globe, and continue to constantly share amazing content for database developers, for example with your biweekly “Feuertips” sessions (Insum Feuertips: PL/SQL tips with Steven Feuerstein).

So, how did you get started into software development?

Steven: I’m an old guy, I’m 63, and that means when I went to college computers were just seeping into the minds and lives of humans. It also means that you could fall into a decent job in programming without much formal training in computer science, which is what I did.

So I ended up taking a couple of classes in programming when I was in college. I think I started with ALGOL and then continued with LISP.

I just dabbled a bit with it and then had the opportunity to start working as a programmer in a research project on campus, which was in FORTRAN, so I basically stayed another year after graduation, working as a programmer.

I kind of fell into it and never had what you call a career plan, I never said to myself: first, I’ll get this job, then after a couple of years, I’ll be ready to do the next job. I moved from job to job, learned things, and eventually fell into a job at Oracle, again without any real plan.

Samuel: How did you pick up PL/SQL then?

Steven: By getting a job at Oracle Corporation in 1987! I’d been working as a consultant for a few years, mostly writing Fortran, and drifting into databases via System 1022 for DEC-10 computers.

I was super bored with where I was, so I started looking for a new job and came across something at Oracle Corporation (never heard of it) involving relational databases (never heard of them). I had no idea about relational databases, but I found an article on the 12 rules for relational databases, memorized them, went to interviews, and was eventually hired (I was never asked, throughout the entire process, what I knew about relational technology). That was even before PL/SQL, though I think they were already working on it.

I was a pre-sales person, which meant in 1987 that my job was to stand in front of an audience, run a couple of SQL statements, and make them excited about how you could easily order by a different column. The good old days!

It was kind of a harbinger of my career to come, which I suppose could be marked by creativity, willingness to try new things, and not being very disciplined or careful.

So I was handed a SQL script by my manager John Cordell to present, and he a=camne to watch me of course, because it was my first time ever doing a public seminar. And of course, I wasn’t satisfied with the script so I had to change it and make it more interesting. So I did that and made it more interesting, but also didn’t test it thoroughly. So in the middle of my first public seminar, running a canned script, it happened: press enter, run something, get an error. (laughs)

I fixed it on the fly, and I’ve never stopped doing that in the hundreds of presentations I’ve done since then. As I like to say: “Act now, perfect later.”

Except maybe you never get around to perfecting, you just keep acting/doing. And thereby avoid falling flat on your face.

Samuel: Amazing! Let’s focus a bit on PL/SQL: What is your favorite feature of PL/SQL?

Steven: First of all, generally, my favorite thing about PL/SQL is that Oracle really got it right in terms of integrating a procedural language with the database. They were very smart that they didn’t say “We can design programming languages better than anybody”, which is a really bad tendency among software companies. They think that because they can do software A, they also can do software B and C and D. And Oracle made that mistake several times and lost a lot of money because of it, but when it came to PL/SQL they looked at existing programming languages instead, to see if there was a model they wanted to follow. They eventually chose ADA, so as a result, we got a really well-structured language as a foundation, which they then let merge with the database. For example: You can take a for loop, go from one to ten and do X. But they extended that conceptually and syntactically, so for example you can do this loop for every row returned by a query. That is my broad favorite aspect of PL/SQL.

As a specific feature, I have to say: String-indexed collections are just the best! I guess they’re called maps in other languages – or associative arrays, which is their official name in PL/SQL as well. You can use them in so many creative ways, you can use them to reduce code redundancy – it’s just super cool!

Samuel: What is something you would like more people to know about database development in general or PL/SQL specifically?

Steven: In general, to just not be afraid of it. There has been a big push in modern software development away from relational databases, which is kind of understandable because all human beings strive for change and these relational database topics are seen as old school, not fancy. So there has been this general movement away, or let’s say, further up the stack. Which you can understand, because things get commodified at the lower level or hidden away, and then people don’t pay attention to them anymore. Usually with pretty bad consequences.

Like: One of the worst things Amazon ever did to the world, to our planet, is convincing us all that we should not have to pay for shipping. You’re a loser if you don’t go for free shipping. And yet, to make the entire transportation infrastructure of the global economy invisible and “free” turns out to be very bad news – for our planet, and therefore also for humans.

We have a similar situation here: If you disappear databases and you say they’re just commodities and that it doesn’t really matter and that you shouldn’t have to pay for them and shouldn’t have to pay attention to them – it turns out they are a pretty fundamental part of the stack and you have to pay attention to it if you want to optimize your applications and get the most out of your whole stack. I wish more people were aware of that.

Any successful application needs a solid database foundation. And you need a really great UI. But you need both of those, and the focus right now is nearly fully on great UIs, which is basically all driven by the smartphones.

So the way I would think about it is to convince developers that sure, you shouldn’t have to deal with databases, but you should have someone on your team who is a database expert. Every team should have a balance between UI and database people.

Samuel: You mentioned that smartphones had a big influence on how we see software development today. Can you elaborate on that?

Steven: To me, the most fundamental transformation that happened with computers is that the user of a computer used to be an employee of a company. So we built business software, the company bought the software, they would pay for training, people would learn how to use the UI and they might hate it, but they have to use it.

Then you have smartphones and suddenly everything is really your choice. And everybody can use it. And you can’t rely on training because people just need to be able to use it and so you have this powerful (and sometimes troubling) simplification of interfaces.

Since the creation of smartphones, there have been so many fundamental shifts in what drives software development and I think we are really just coming to grips with the impact of that.

One impact is that UI is now THE most important thing – and the database is so far away from user experience.

With smartphones, our software is also much more directly tied to culture. Business software was never so tightly attached to public culture, so it changed a lot slower, was a lot less individualized. Now everything is driven around that personal experience.

Samuel: Coming back to features of PL/SQL: If you could wish for one change in PL/SQL or one new feature: What would it be?

Steven: I think that would be full orthogonality. For example: I declare a variable to be a number, and instead of assigning a static number, I should be able to write a single-row select right there that fills the variable. So everywhere I have single value usage, I should be able to substitute that with a single value query. Things like that.

And they might even be working on that for version 23, I don’t know. They already took a big step in this direction in 21c, when they changed the iterator logic, and expanded it dramatically – that’s another example where they are kind of completing the language. More things like that would be very nice.

Samuel: That includes a boolean datatype in SQL, right?

Steven: Yes, that would be nice (laughs). Unfortunately, from my days inside Oracle, I can now see that (a) adding a brand new datatype to SQL is a lot of work and (b) the way they prioritize is to ask “Is there a way users can work around this missing feature?” If not, then higher priority. If so, then, well, there you go: work around!

But one very specific feature that I would love to have them add in is the ability to select bulk-collect into string indexed collections. Something like “select bulk collect into index by” and then give a column name (or several) to do the indexing.

Samuel: What would you say is the hardest part of database development? What is a major struggle?

Steven: I would say the struggles are not so much about the technology, but around the process. How do you build disciplined developers so they do the right thing and don’t just take a lot of shortcuts and – write code like me essentially (laughs)

As I mentioned before, I’m a popularizer, I wrote a couple of books, and people like what I write, they like the jokes that I put in the books, and they conclude that I must be some amazing PL/SQL developer.

Now, just because you write a good book doesn’t mean that you are a good developer. I think I’m okay at writing individual PL/SQL program units, and I can probably do it faster than anybody on the planet, but in terms of having the discipline to build an application correctly and carefully – nah, I’m terrible at that. I keep making the same mistakes over and over again for the last couple of years and probably will until I retire.

So to me, there’s not much of a technical barrier to the PL/SQL programming language – it’s a very straightforward language. It’s mostly around the process and the discipline that’s necessary to build applications in which we have a challenge. And that might be partly because we, the database developers, are not as much following the practices that are more common in other development environments, like using git for version control. But I don’t know – JavaScript developers might have the same struggles. That said: The simplicity and readability of database code on the other hand might make it harder to screw up than in JavaScript, but it’s still possible.

Samuel: Yeah I agree – also the strictness, especially of PL/SQL.

Steven: Oracle has always been pretty paternalistic about what you can do in its database. Sometimes we might not like it, but there are usually very good reasons.

Samuel: It wouldn’t be me interviewing you if I didn’t ask a question about utPLSQL. Two decades ago, you brought automated tests into the Oracle database. You even wrote a framework – utPLSQL version 1 and 2 – to help with that.

Why do you think automated tests on the database level still have so little acceptance?

Steven: Probably for the same reason they’re so little accepted across any other programming environment. I mean, do you really think that a higher percentage of JavaScript developers use automated regression tests?

Samuel: I think it depends a lot, but there are certainly programming language communities that embrace automated tests a lot more. Take python with its dedicated “test” keyword for example, or Java with a strong acceptance of JUnit automated tests.

From my perspective, the acceptance among the database development community is exceptionally low.

Steven: I will just say for the record, that I believe, that if we can find out the real percentage among the gazillions of Java developers who use and embrace automated tests, chances are this percentage will not differ so much from PL/SQL developers. That would be my guess.

But putting that aside for a moment, there are some specific challenges around database code testing. Famously, the idea of unit-testing is that you take an individual unit of code which is independent and just test it, because it is deterministic. These are the easiest things to test. But as soon as you have database dependency, all sorts of additional challenges come into play. So probably the biggest reason is, that the barrier to do automated tests in the database is usually a lot higher. And the barrier to do automated tests, in general, is high already because it takes time, it takes knowledge, and it takes management enlightenment to some extent to give developers the time and tools to do it. And this is even compounded by the additional challenges of databases.

Another reason might be that the database community is one that’s been around longer than … maybe not longer than automated testing, but certainly longer than things like Test-Driven-Development, Extreme Programming and other trends of the “agile movement”.

Samuel: So it also might be a cultural problem?

Steven: Yes, I think so. I mean – I noticed JUnit when it came out and thought it would be cool to have that for PL/SQL. But I never practiced it consistently and really used it in my development process and will probably not do it in the future.

I still don’t believe that our communities are all that different.

You got the additional challenges of databases. And you got the additional challenges of the database side of the stack being not respected as much, not getting as much attention, which means you lack the tools that exist for other parts of the stack. It also means that you don’t get as much money and resources to do good testing.

_ Samuel: Let’s close with a couple of questions that are not about PL/SQL. What is one of the best pieces of advice that you’ve gotten in your career? _

Steven: Normalize! I think that one of the reasons I like relational databases so much is that the idea of normalizing data to avoid redundancy (and more) synchs very powerfully with some internal drivers I have, namely around not wasting time or resources.

Don’t just normalize data, normalize code! Avoid redundancy in code, make the structure of your code clean and easy to manage. So for me one of the most powerful pieces of advice that I follow is to modularize: keep program units small, don’t get lost in a huge bunch of spaghetti code. That’s one of the principles I try to live by as I write my code.

Samuel: What would be one thing you’d say to someone who just started their journey into software development?

Steven: Put down your computer and go outside. More than that, put down your computer and BE outside.

Samuel: That’s a great one!

Steven: I’d say the most important piece of advice I’d give to any human being alive today, especially to anybody that lives in the technological, “modern”, “civilized” world, is: Stop hiding from your planet.

And I say that not only because the only way we can possibly destroy so much of our planet is by not being witness to it, by being disassociated from it as much as we are, but also because when we’re going out to our planet, we’ll be able to think more clearly, we’ll be able to solve problems more effectively, we’ll be healthier, we’ll be more comfortable in our own body. And all those things will make you a better developer.

It is really striking, Sam, to think about how many humans, and we’re talking about mainly middle-class people in a country like the United States, have almost no direct contact with planet earth. We live in our boxes. We wake up inside our box (home). We walk on our sidewalks to our car-box. We drive on streets that take us to our office box. And there is one very common feature of all these boxes: they have almost nothing alive in them. Except us and some plants.

You walk down a street in your city and if you’re lucky there’s a tree you walk by, which is really just a tiny gap in the sidewalk concrete that allows a trunk to come up.

For me, we are so far off any sort of rational and positive way to live on this planet, that the very first thing we need to do is reconnect to and with it.

Samuel: Time for one last question: If you weren’t a software developer – what would you be, work as, or focus your energy on full-time?

Steven: I think the number one thing any human being alive should be doing today is work on healing our planet. So I would do the same as I do now with all the time I’m not spending in front of a computer. What I do is I spend as much time as possible outdoors, fighting invasive species and helping our native ecosystems to thrive.

To me, the most important principle to follow is direct and positive action.

You can vote, you can donate, you can sign petitions, but what really is important is that you are going out into the world and making a difference in healing our planet and saving the lives of non-humans. And bring your children along!

Samuel: That’s a wonderful closing answer. Thank you so much for this conversation.

The post “Stop Hiding From Your Planet” – Interview with Steven Feuerstein about Databases, PL/SQL, and Direct Positive Action appeared first on Developer Sam.

Why did my favorite conference reject my talk?

Samuel Nitsche — Fri, 27 May 2022 07:34:05 +0000

This blog post is part of a “blog ring” about the ATD selection process. Other posts about this topic:

What’s You’re Missing in Your Conference Abstract: Spoilers by Elizabeth Zagroba
Reviewing Submissions for the Agile Testing Days – Seaside Testing by Stephan Kämper

Having a talk rejected is hard,

especially when you really cared about the conference you wanted to speak at. It’s even harder for people that can only go to that conference if they get the benefits (e.g.free ticket) of a speaker – I feel that because I’m also in this group.

I have been on a couple of conference review committees, reviewed and judged hundreds of abstracts and I would like to share a couple of reasons why a talk might be rejected.

The main reason for this blog post was my participation in the review committee for Agile Testing Days 2022 and the many disappointed reactions to having their abstract rejected in the community. While this post is not exclusively about Agile Testing Days, it’s heavily influenced by it.

Quality and Structure of your abstract – the only reason in your area of influence

Let’s get over this one quickly: of course, the quality and structure of your abstract has some influence on getting accepted or not. Others have already written a lot about that, what you can do and how you can create a great abstract.

Here are some of my most important tips:

Write for a specific conference and audience. Yes, it’s more efficient to write one abstract you can send without change to different conferences, but it will never be as good as several different versions of your abstract that are tailored to the specific topics, preferences, and audience of a specific conference.
Check previous talk descriptions of the conference you want to apply for. I always do this if I don’t know about a conference yet. It gives so much information about what they value and prefer.
Use the advantages of written text to structure your abstract. Of course, you can write prose without any highlighting, just as if you’d speak. But that will probably be less appealing because you cannot carry emotion, timing, change of voice pitch, etc. in written text. The advantages you have with text, though, are headlines, formatting, paragraphs, and bullet-point lists. It will provide a lot of additional structure and makes it a lot easier for the reader (reviewer and attendee) to grasp the message.
Reach out for help. Nobody is perfect, and even if you have a lot of experience with writing abstracts, it can always be improved. There are a lot of people in the community who love to give feedback and do reviews of your abstracts before you send them in. Yes, it is additional work. The thing is: if you decide to not put in that work, someone else with a similar topic might, and then they have an advantage.

Having said all of this, it is very important to notice that this is the only thing you can influence. As you will see, there are a lot of other reasons that you cannot influence, or that just make it a lot harder to get accepted.

An awful lot of abstracts for a very small number of slots

Conferences have a limited number of slots they can fill with talks. This is obvious, but I think it is often not well-known how limited these slots really are compared to the number of abstracts handed in.

I always do a small calculation of the “abstract vs. slot ratio” when I’m on a review committee, and even for small events that are not that popular, this ratio is usually around 2:1 or 3:1. That means for every possible talk slot at this conference, there are 2 or 3 abstracts competing (totally ignoring the different topics and if they fit the conference or not).

For Agile Testing Days, I went a step further and asked for specific numbers per presentation type and came up with the following abstract vs. slot ratios:

Workshop: 4:1
Combo-Session: 8:1
Talks: 6:1
Social Activities: 4:1

This is brutal. Even for the social activities, only one out of four abstracts could possibly be selected, even worse with the talks and combo sessions. Nearly 90% of all combo-session proposals had to be rejected just because of the limited slot capacity.

These numbers also show, that the challenge of being accepted at Agile Testing Days is harder in general compared to other events, because they get a lot of abstracts.

Overall high quality of abstracts

Some events do a lot to help people writing good abstracts, and Agile Testing Days is definitely among them. There are blog posts, preparation sessions, tips, a friendly and helpful community, a lot of helping and sharing.

This results in an overall higher quality of abstracts. Yes, I can confirm that from experience: I had very, very few abstracts that were of low quality (technically).

I think on top of that, there is also a relation between the popularity of a conference and the amount of effort people put into their abstract, further increasing the quality of the proposals.

From the conference organizer’s perspective, this is an awesome situation, because it makes it a lot easier to create a high-quality program. From the speaker’s perspective, this means a lot more and tougher competition, and means it becomes less likely to have their abstract be accepted.

(By the way: from the reviewer’s perspective it makes judging and reviewing also harder, just saying, not complaining ;))

Having a line-up that meets the conference values

Tech has a huge overrepresentation of white middle-aged men. We are in general more encouraged, have more freedom, more backing and support, more assumed expertise and less limiting factors when it comes to public speaking.

Some conferences care about that and work hard to create a line-up that reflects their values more than the current status quo. I applaud every conference which cares, I appreciate it deeply, and I know this is the case for Agile Testing Days.

Oh my, Samuel, does that mean that, as a white dude in tech, I might have a harder competition to get selected?

Hell yes, I hope so! Because that little bit of extra competition is nothing compared to the extra effort members of underrepresented groups have to put in to even get the chance (and then to make it a success and have it perceived as success). Guys, deal with it and check the privileges you have.

One word to all the conference organizers who claim to care: If you don’t offer travel and accommodation for your speakers (ATD does, just to make that clear), you already make it a lot harder for people of underrepresented groups. So please don’t tell me about “equal chances”.

Other reasons

Depending on the conference, there are even a lot more other reasons why an abstract is not chosen. I won’t go into detail, but here are some ideas of more possible reasons that in the end can all affect the individual “abstract vs. slot” ratio of your talk:

Cost efficiency (Maybe have more talks of the same speaker, have a limited number of international speakers, etc)
Specific slots for new voices
Language (some conferences prefer talks in the national language)
Employer/Company (maybe the conference only wants X talks from one company)
Popularity (maybe some well-known speaker is preferred in case of similar topics, which makes total sense for the organizer)

You are not a failure.

Being rejected is hard, but it does not mean that you are a failure.

I hope I could explain that there are so many things that can affect your chances of getting accepted and that a lot of them are not even in your area of influence.

Keep going. Keep handing in. Reach out for feedback. Keep improving. You are not a failure. Nobody has ever told your story from your perspective.

The post Why did my favorite conference reject my talk? appeared first on Developer Sam.

List Comprehension – What Is It And Does It Only Exist In Python?

Samuel Nitsche — Tue, 09 Feb 2021 23:07:45 +0000

Since summer, I have two trainees. One very nice thing about that situation is that I am forced to think about basic concepts in a way I never did before – because now I have to explain and teach them instead of just using them.

And sometimes, I learn new terms – like “list comprehension”.

The term might be known by most Python developers, but I never encountered it so far and when my trainee brought it up I was curious.

Here’s what my current understanding of the concept is:

What is List Comprehension?

In short, list comprehension is a way in which a programming language supports working with lists and collections in a comfortable way.

In most programming languages, you are able to iterate through lists of objects and do all sorts of manipulations inside that iteration loop, for example creating a new list with just a part of the original content based on some conditions.

If a programming language supports list comprehension, it provides an easier way to do that.

Let’s look at an easy Python example where we have a list of dictionaries, holding some famous Star Wars Characters:

starWarsCharacters = (
    {"name":"Darth Vader", "side":"dark"},
    {"name":"Mace Windu", "side":"light"},
    {"name":"Leia Organa", "side":"light"},
    {"name":"Darth Maul", "side":"dark"},
)

newList = []
for i in range(0, len(starWarsCharacters)) :
    if ( starWarsCharacters[i]["side"] == "dark") :
        newList.append(starWarsCharacters[i]["name"])

print(newList)

I can use a for-loop to go through all the elements of a list and if they belong to the dark side, put their name into a new list.

Thanks to list comprehension in Python, I can do the same with one single line of code, which is also a lot easier to read:

newList = [character["name"] for character in starWarsCharacters if character["side"] == "dark"]
print(newList)

Isn’t that nice? It’s nearly self-explanatory, but let’s go through it step by step.

Anatomy of List Comprehension

List comprehension usually consists of 3 parts: A source from which to read, a filter (optional) and a mapping that goes into the new list.

In our Python example, the parts are as follows, put into square brackets:

Part	Example
Source	`for character in starWarsCharacters`
Filter	`if character["side"] == "dark"`
Mapping	`character["name"]`

The order in which the different parts are arranged in Python is

<Mapping> for <SourceListElement> in <SourceList> if <Filter>

List Comprehension in Other Languages

I’m not sure if the term “list comprehension” is really used in other languages, but when I understood what the term means in Python I recognized that the concept exists in a lot of programming languages:

Python

starWarsCharacters = (
    {"name":"Darth Vader", "side":"dark"},
    {"name":"Mace Windu", "side":"light"},
    {"name":"Leia Organa", "side":"light"},
    {"name":"Darth Maul", "side":"dark"},
)

newList = [character["name"] for character in starWarsCharacters if character["side"] == "dark"]
print(newList)

# Run: py characters_of_the_dark_side.py

JavaScript

let starWarsCharacters = [
    {name:"Darth Vader", side:"dark"},
    {name:"Mace Windu", side:"light"},
    {name:"Leia Organa", side:"light"},
    {name:"Darth Maul", side:"dark"},
];

let newList = 
    starWarsCharacters
        .filter(character => character.side == "dark")
        .map(character => character.name);
console.log(newList);

// Run: node characters_of_the_dark_side.js

Note that the order in which the different list comprehension parts are arranged are different, but the way it works is pretty similar.

C-Sharp

To run C# code as script, install the dotnet-scripting tool via

dotnet tool install -g dotnet-script

or read about the alternatives.

var starWarsCharacters = new[] {
    new {name = "Darth Vader", side = "dark"},
    new {name = "Mace Windu", side = "light"},
    new {name = "Leia Organa", side = "light"},
    new {name = "Darth Maul", side = "dark"}
};

var newList = 
    from character in starWarsCharacters
    where character.side == "dark"
    select character.name;

foreach ( var item in newList ) {
    Console.WriteLine(item);
}

// Run: dotnet script characters_of_the_dark_side.csx

The list comprehension feature of C# is called LINQ – and it’s quite awesome.

SQL

People often forget this, but SQL is really the champion of list comprehension.

To run this example you have to install sqlite, e.g. on Windows via chocolatey:

choco install sqlite

with starWarsCharacters as (
    select "Darth Vader" name, "dark" side union all
    select "Mace Windu" name, "light" side union all
    select "Leia Organa" name, "light" side union all
    select "Darth Maul" name, "dark" side
)
select character.name
    from starWarsCharacters character
    where side = "dark";

-- Run: sqlite3 test.db ".read characters_of_the_dark_side.sql"

Was this helpful and/or interesting? Let me know

The post List Comprehension – What Is It And Does It Only Exist In Python? appeared first on Developer Sam.

Advent Of Code 2020 in SQL – Day 10: “Adapter Array”

Samuel Nitsche — Thu, 10 Dec 2020 23:21:12 +0000

Day 10 is a beast. Really.

We are supposed to order a bunch of adapters in a way that we get from 0 jolt to the jolt needed by our device (which is the max number in the input + 3) in a step-size between 1 and 3.

Part 1 feels nearly like cheating, because we can just use a little bit of window-function magic:

with
  base_data as (
    select
      to_number(column_value) num
    from table (
        aoc_file_loader.file_as_stringlist(
          aoc_file_loader.local_url('day_10_input.txt')
       )
    )
  ),
  jolt_diffs as (
    select
      num,
      num - nvl(lag(num) over (order by num),0) jolt_diff
    from base_data
  ),
  sums as (
    select
      jolt_diff,
      count(*) count,
      sum(jolt_diff) sum
    from jolt_diffs
    group by (jolt_diff)
  )
select
  (select count from sums where jolt_diff = 1)
  * (select count+1 -- +1 for the device-adapter
    from sums where jolt_diff = 3) result
from dual;

But then comes part 2, in which we need to calculate all the possible ways that we can use to get our adapter connected.

This was incredibly hard for me to solve and I’m not sure I got the best solution – but it’s very, very fast so I think I found a pretty efficient one.

For all possibilities need to end with the maximum number in the list, I decided to start with that number. But of course, iterating through all the possibilities would be terribly unefficient, so I dabbled around with multiplying and stuff – which didn’t work.

As a constant learner, I looked for algorithms other folks came up with (NOT in the context of Advent Of Code, of course) and found this explanation of an efficient algorithm to count possible ways to cover a distance.

It was not the solution to my problem, but it gave me the right idea to count the possibilities to each reachable next point in a recursive subquery.

This would look like the following for the larger example-data:

NUM	MULTIPLIER	DIST_1	DIST_2	DIST_3
	prev.DIST_1 (or 1 to start with)	prev.DIST_2 + (MULTIPLIER Exists)*	previous.DIST_3 + (MULTIPLIER Exists)*	MULTIPLIER Exists*
49	1	0+(1*Exists(48))=1	0+(1*Exists(47))=1	1*Exists(46)=1
48	1	1+(1*Exists(47))=2	1+(1*Exists(46))=2	1*Exists(45)=1
47	2	2+(2*Exists(46))=4	1+(2*Exists(45))=3	2*Exists(44)=0
46	4	3+(4*Exists(45))=7	0+(4*Exists(44))=0	4*Exists(43)=0
45	7	0+(7*Exists(44))=0	0+(7*Exists(43))=0	7*Exists(42)=7
44	0	0+(0*Exists(43))=0	7+(0*Exists(42))=7	0*Exists(41)=0

with
  base_data as (
    select
      to_number(column_value) num
    from table (
        aoc_file_loader.file_as_stringlist(
          aoc_file_loader.local_url('day_10_input.txt')
       )
    )
    union all select 0 from dual
  ),
  walk (num, multiplier, dist1, dist2, dist3) as (
    select
      num,
      1,
      (select count(*) from base_data where num = cur.num-1),
      (select count(*) from base_data where num = cur.num-2),
      (select count(*) from base_data where num = cur.num-3)
    from base_data cur
    where num = (select max(num) from base_data)
    union all
    select
      prev.num-1,
      prev.dist1,
      prev.dist2 + (
        prev.dist1 * (select count(*) from base_data where num = cur.num-1)),
      prev.dist3 + (
        prev.dist1 * (select count(*) from base_data where num = cur.num-2)),
      prev.dist1 * (select count(*) from base_data where num = cur.num-3)
    from walk prev
      left outer join base_data cur on prev.num-1 = cur.num
    where prev.num > 0
  )
select multiplier as result
from walk
order by num fetch first row only

Enjoy. I never did such a thing before and I’m pretty proud right now I’ve figured it out.

You can find the solution on my github-repository.

Advent Of Code 2020 in SQL – Day 9: “Encoding Error”

Samuel Nitsche — Wed, 09 Dec 2020 16:50:41 +0000

The Day 9 puzzle wants us to find certain anomalies in a stream of numbers: Each number must be the sum of two previous numbers in a range of 25 (between line-1 and line-25). We should find the first number that doesn’t meet this requirement.

For databases are great at adding numbers, I have no problem doing a lot of additional work (well, let the machine do a lot of additional work) and calculate the sum of each of these possibilities.

I also added a config-subquery so I can easily switch my actual data to the data provided in the example. The puzzles really get so challenging that I usually start with the example-data.

with
  config as (
    select
      25 as preamble_length,
      25 as search_range
    from dual
  ),
  base_data as (
    select
      rownum line,
      to_number(column_value) num
    from table (
        aoc_file_loader.file_as_stringlist(
          aoc_file_loader.local_url('day_9_input.txt')
       )
    )
  ),
  calc_cube as (
    select
      d1.line line_1,
      d1.num num_1,
      d2.line line_2,
      d2.num num_2,
      d1.num+d2.num sum
    from base_data d1
      cross join base_data d2
      cross join config
    where
      d1.line-d2.line between 1 and config.search_range
      or d1.line-d2.line between 1 and config.search_range
  )
select * from calc_cube

From there I can check whether there is such a sum in the requested range line by line, starting with the first line after the preamble.

with
  config as (
    select
      25 as preamble_length,
      25 as search_range
    from dual
  ),
  base_data as (
    select
      rownum line,
      to_number(column_value) num
    from table (
        aoc_file_loader.file_as_stringlist(
          aoc_file_loader.local_url('day_9_input.txt')
       )
    )
  ),
  calc_cube as (
    select
      d1.line line_1,
      d1.num num_1,
      d2.line line_2,
      d2.num num_2,
      d1.num+d2.num sum
    from base_data d1
    cross join base_data d2
    cross join config
    where
      d1.line-d2.line between 1 and config.search_range
      or d1.line-d2.line between 1 and config.search_range
  ),
  valid_sequence( line, num, parents_found) as (
    select
      line,
      num,
      (select count(*) from calc_cube cube
        where
          cube.sum = cur.num
          and cube.line_2 between cur.line-config.search_range and cur.line-1
          and cube.line_1 between cur.line-config.search_range and cur.line-1
      )
    from base_data cur
      cross join config
    where line = config.preamble_length+1 -- First line after preamble
    union all
    select
      cur.line,
      cur.num,
      (select count(*) from calc_cube cube
        where
          cube.sum = cur.num
          and cube.line_2 between cur.line-config.search_range and cur.line-1
          and cube.line_1 between cur.line-config.search_range and cur.line-1
      )
    from base_data cur
      inner join valid_sequence prev on cur.line = prev.line+1
      cross join config
    where prev.parents_found > 0
  )
select
  line,
  num
from valid_sequence
where parents_found = 0

So far, so good – my database does this in like 1,5 seconds, which is okay.

Now Part 2. Phew. I really have no idea how to solve this in a set-based way, so I decided to take a “walk” approach with recursive subqueries.

After limiting the possible input data, I decide in each step what the next line to walk through will be. The logic is basically:

add to run_total as long as it’s not greater than invalid_number
reduce run_total by the amount of min_line until it’s lower than invalid_number again
- Also increase min_line in that case
stop when run_total of the previous walk is equal to invalid_number

with
  config as (
    select
      41682220 as invalid_num
    from dual
  ),
  base_data as (
    select
      rownum line,
      to_number(column_value) num
    from table (
        aoc_file_loader.file_as_stringlist(
          aoc_file_loader.local_url('day_9_input.txt')
       )
    )
  ),
  possible_input as (
    select
      line,
      num
    from base_data
      cross join config
    where
      line < (select line from base_data where num = config.invalid_num)
  ),
  walk (line, num, run_total, min_line, max_line, action, next_line) as (
    select
      line,
      num,
      num,
      line,
      line,
      'add',
      line+1
    from possible_input
    where line = 1
    union all
    select
      cur.line,
      cur.num,
      case when prev.run_total+cur.num > config.invalid_num then
        prev.run_total - (select num from possible_input where line = prev.min_line)
      else
        prev.run_total + cur.num
      end run_total,
      case when prev.run_total+cur.num > config.invalid_num then
        prev.min_line+1
      else
        prev.min_line
      end min_line,
      cur.line max_line,
      case when prev.run_total+cur.num > config.invalid_num then
        'rem'
      else
        'add'
      end action,
      case when prev.run_total+cur.num > config.invalid_num then
        cur.line
      else
        cur.line+1
      end next_line
    from possible_input cur, walk prev, config
    where cur.line = prev.next_line
      and prev.run_total != config.invalid_num
  ) --cycle line, run_total set is_loop to 'Y' default 'N'
select
  (select min(num) from possible_input where line between walk.min_line and walk.max_line) min_num,
  (select max(num) from possible_input where line between walk.min_line and walk.max_line) max_num,
  (select min(num)+max(num) from possible_input where line between walk.min_line and walk.max_line) sum_num
from walk, config
where run_total = config.invalid_num

The commented-out line was pretty helpful to check at which point I messed up and produced an infinite loop.

While the solution is unusual for a SQL-query, it’s very, very fast: ~250ms on my crappy dockerized database.

As always, you can find the code on my github-repository.

Advent Of Code 2020 in SQL – Day 8: “Handheld Halting”

Samuel Nitsche — Tue, 08 Dec 2020 10:34:54 +0000

I never ran and debugged an infinite loop of program consisting of the instructions nop, jmp and acc in SQL before – but it’s possible and the Day 8 puzzle gives me the possibility to do so.

Each of the commands has a possible value, and while we ignore it for nop, acc manipulates a global variable and jmp redirects the program to a different line.

To solve this in SQL we need recursive subqueries again. With a bit of case we can easily determine the next line number to be exectued, but how to we prevent the infinite loop from running?

My solution was to add a stack-column which holds all the previously called lines. With that I can check whether a line has already been called.

I use a “:” suffix and prefix, because when I check for “42” it will find that number in “142”, too, but “:42:” will not be found.

with
  base_data as (
    select
      rownum line_number,
      column_value line
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_8_input.txt')
      )
    )
  ),
  command_value as (
    select
      line_number,
      substr(line, 1, 3) command,
      to_number(substr(line, 4)) value
    from base_data
  ),
  run_program (line_number, command, next, accumulator, stack) as (
    select
      line_number,
      command,
      line_number+1,
      case when cmd.command = 'acc' then
        cmd.value
      else
        0
      end,
      ':'||line_number||':'
    from command_value cmd where line_number = 1
    union all
    select
      cmd.line_number,
      cmd.command,
      case when cmd.command = 'jmp' then
        cmd.line_number+cmd.value
      else
        cmd.line_number+1
      end,
      case when cmd.command = 'acc' then
        call.accumulator+cmd.value
      else
        call.accumulator
      end,
      call.stack||':'||cmd.line_number||':'
    from command_value cmd, run_program call
    where cmd.line_number = call.next
      and instr(call.stack, ':'||cmd.line_number||':') <= 0
  )
select
  accumulator
from run_program
order by stack desc
fetch first row only;

Part 2 gets a bit more complicated now, because we need to find the single nop or jmp command that leads to the infinite loop – and replace it.

Because we have the power of computers (and modern computers are immensely powerful calcualation machines), we brute-force the solution by running all the variants in which we replace one command.

We can achieve that by cross-joining a variant_definition table which contains all the nop or jmp commands and the replacement commands.

To find the correct one, we look for the one variant that has NumberOfLines+1 as the maximum next value, because that means the program reached its end.

with
  base_data as (
    select
      rownum line_number,
      column_value line
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_8_input.txt')
      )
    )
  ),
  command_value as (
    select
      line_number,
      substr(line, 1, 3) command,
      to_number(substr(line, 4)) value
    from base_data
  ),
  variant_definition as (
    select
      rownum variant_id,
      line_number,
      command,
      case command
        when 'jmp' then
          'nop'
        when 'nop' then
          'jmp'
      end replacement_command,
      value
    from command_value
    where command in ('jmp','nop')
  ),
  program_variants as (
    select
      variant.variant_id,
      cmd.line_number,
      case when cmd.line_number = variant.line_number then
        variant.replacement_command
      else
        cmd.command
      end command,
      cmd.value
    from variant_definition variant
    cross join command_value cmd
  ),
  run_program (variant_id, line_number, command, next, accumulator, stack) as (
    select
      variant_id,
      line_number,
      command,
      line_number+1,
      case when cmd.command = 'acc' then
        cmd.value
      else
        0
      end,
      ':'||line_number||':'
    from program_variants cmd where line_number = 1
    union all
    select
      cmd.variant_id,
      cmd.line_number,
      cmd.command,
      case when cmd.command = 'jmp' then
        cmd.line_number+cmd.value
      else
        cmd.line_number+1
      end,
      case when cmd.command = 'acc' then
        call.accumulator+cmd.value
      else
        call.accumulator
      end,
      call.stack||':'||cmd.line_number||':'
    from program_variants cmd, run_program call
    where cmd.variant_id = call.variant_id
      and cmd.line_number = call.next
      and instr(call.stack, ':'||cmd.line_number||':') <= 0
  ),
  last_values as (
    select
      variant_id,
      max(next) last_value
    from run_program
    group by variant_id
  )
select
  accumulator,
  variant_definition.line_number,
  variant_definition.command,
  variant_definition.replacement_command
from run_program
  inner join variant_definition using (variant_id)
where variant_id = (
  select variant_id from last_values where last_value-1 = (
    select count(*) from base_data
  )
)
order by stack desc
fetch first row only;

That was challenging and very fun!

You can find the complete solution in my github-repository.

Advent Of Code 2020 in SQL – Day 7: “Handy Haversacks”

Samuel Nitsche — Tue, 08 Dec 2020 07:54:13 +0000

Day 7’s puzzle comes with a bunch of rules about bags that contain other bags – and a lot of different colors (very cool puzzle setting btw!)

As always, the first thing we want to do is to normalize the input in a way that gives us a workable result set. In this case I aim to have a row for each child-rule, groupable by the bag’s name.

If we want to split a column into several rows, one of best approaches (if we cannot easily unpivot) is to use recursive subqueries. They seemed complicated to me at first, but once I used them several times I really get to like them:

with
  base_data as (
    select
      column_value line
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_7_input.txt')
      )
    )
  ),
  bag_children as (
    select
      replace(
        regexp_replace(line, '^(.+) contain (.+)$', '\1'),
        'bags',
        'bag'
        ) bag,
      regexp_replace(line, '^(.+) contain (.+)$', '\2') children
    from base_data
  ),
  children_in_rows (bag, child, children) as (
    select
      bag,
      nvl(substr(children, 1, instr(children, ', ')-1), children),
      case when instr(children, ', ') > 0 then
        substr(children, instr(children, ', ')+2)
      else null end
    from bag_children
    union all
    select
      bag,
      nvl(substr(children, 1, instr(children, ', ')-1), children),
      case when instr(children, ', ') > 0 then
        substr(children, instr(children, ', ')+2)
      else null end
    from children_in_rows where children is not null
  )
select * from children_in_rows;

It’s important we normalize the “bags” in the bagcolumn to “bag”, because we have rules that say “contain 1 other bag.” and we won’t be able to match that with the “bags”.

The first task now is to wander upwards and count how many different (parent) bag-colors we have.

This might be possible with connect by prior, too, but I noticed I have way more control about how exactly my recursion behaves if I use recursive subquery again:

with
  base_data as (
    select
      column_value line
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_7_input.txt')
      )
    )
  ),
  bag_children as (
    select
      replace(
        regexp_replace(line, '^(.+) contain (.+)$', '\1'),
        'bags',
        'bag'
        ) bag,
      regexp_replace(line, '^(.+) contain (.+)$', '\2') children
    from base_data
  ),
  children_in_rows (bag, child, children) as (
    select
      bag,
      nvl(substr(children, 1, instr(children, ', ')-1), children),
      case when instr(children, ', ') > 0 then
        substr(children, instr(children, ', ')+2)
      else null end
    from bag_children
    union all
    select
      bag,
      nvl(substr(children, 1, instr(children, ', ')-1), children),
      case when instr(children, ', ') > 0 then
        substr(children, instr(children, ', ')+2)
      else null end
    from children_in_rows where children is not null
  ),
  bag_hierarchy( bag, child, hierarchy_level, path ) as (
    select
      bag,
      child,
      1,
      bag
    from children_in_rows
    where child like '%shiny gold bag%'
    union all
    select
      cur_bag.bag,
      cur_bag.child,
      parent_bag.hierarchy_level+1,
      cur_bag.bag || '/' || parent_bag.path
    from children_in_rows cur_bag
      inner join bag_hierarchy parent_bag on cur_bag.child like '%'||parent_bag.bag||'%'
  )
select count(distinct bag)
from bag_hierarchy

Part 2 now wants us to walk down the tree and count all the bags inside of the shiny gold bag – and the bags inside the bags etc.

Therefore we need to extract the number from the text – easy with regular expressions. We also replace “no other bags.” with 0 so we can calculate properly.

I found it easier to calculate the cumulated children of each bag (on parent-level) rather than the cumulated number on child-level.

with
  base_data as (
    select
      column_value line
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_7_input.txt')
      )
    )
  ),
  bag_children as (
    select
      replace(
        regexp_replace(line, '^(.+) contain (.+)$', '\1'),
        'bags',
        'bag'
        ) bag,
      regexp_replace(line, '^(.+) contain (.+)$', '\2') children
    from base_data
  ),
  children_in_rows (bag, child, children) as (
    select
      bag,
      nvl(substr(children, 1, instr(children, ', ')-1), children),
      case when instr(children, ', ') > 0 then
        substr(children, instr(children, ', ')+2)
      else null end
    from bag_children
    union all
    select
      bag,
      nvl(substr(children, 1, instr(children, ', ')-1), children),
      case when instr(children, ', ') > 0 then
        substr(children, instr(children, ', ')+2)
      else null end
    from children_in_rows where children is not null
  ),
  children_with_count as (
    select
      bag,
      to_number(
        replace(
          regexp_replace(child, '^([0-9]+) (.*)$', '\1'),
          'no other bags.',
          '0'
          )
        )child_count,
      regexp_replace(child, '^([0-9]+) (.*)$', '\2') child_name
    from children_in_rows
  ),
  bag_hierarchy( bag, child_name, child_count, cumulated_child_count, hierarchy_level, path ) as (
    select
      bag,
      child_name,
      child_count,
      child_count,
      1,
      bag
    from children_with_count
    where bag like '%shiny gold bag%'
    union all
    select
      cur_bag.bag,
      cur_bag.child_name,
      cur_bag.child_count,
      parent_bag.cumulated_child_count*cur_bag.child_count,
      parent_bag.hierarchy_level+1,
      parent_bag.path || '/' || cur_bag.bag
    from children_with_count cur_bag
      inner join bag_hierarchy parent_bag on parent_bag.child_name like '%'||cur_bag.bag||'%'
  )
select sum(cumulated_child_count)
from bag_hierarchy

This was quite a challenge and I had several errors (mainly from the missing “one some-colored bag.” connection) – hierarchical queries are something I don’t do that often. But it was very funny and I learned and refreshed a lot!

You can find the whole code on my github-repository.

Advent Of Code 2020 in SQL – Day 6: “Custom Customs”

Samuel Nitsche — Mon, 07 Dec 2020 13:38:42 +0000

The puzzle of Day 6 requests us to group answers to a customs declaration form and analyse them.

Challenge one is the format we need to normalize into something we can work with in SQL. Each answer is a line, but several answers are grouped together and the groups are separated by an empty line.

So as a first step, we introduce a new column that indicates whether the row is a break or not and then use window functions to summarize all breaks from beginning to the current position, which will be our group-ID

base_data as (
    select
      rownum answer_id,
      column_value answers,
      case when column_value is null then 1 else 0 end is_break
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_6_input.txt')
      )
    )
  ),
  grouped_data as (
    select
      answer_id,
      group_id,
      answers
    from (
      select
        answer_id,
        answers,
        is_break,
        sum(is_break) over (
          order by answer_id
          range between unbounded preceding
            and current row)
          group_id
      from base_data
    )
    where is_break = 0
  )
select * from grouped_data

It’s important that we filter away the is_break after we applied the window function!

The next step took me a while – my first attempt was to split the answer-rows with connect by level according to the length of the string, but this solution was terribly slow and took several minutes for only ~20 lines of input-data.

So my second approach was to provide a baseline of all characters a-z as result set we could cross-join against – and this worked pretty well:

with
  answer_pos as (
    select column_value c
    from table(sys.odcivarchar2list(
      'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'
      )
    )
  ),
  base_data as (
    select
      rownum answer_id,
      column_value answers,
      case when column_value is null then 1 else 0 end is_break
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_6_input.txt')
      )
    )
  ),
  grouped_data as (
    select
      answer_id,
      group_id,
      answers
    from (
      select
        answer_id,
        answers,
        is_break,
        sum(is_break) over (
          order by answer_id
          range between unbounded preceding
            and current row)
          group_id
      from base_data
    )
    where is_break = 0
  ),
  distinct_one_answer_per_row as (
    select distinct
      group_id,
      answer_pos.c answer
    from grouped_data
      cross join answer_pos
    where instr(answers, answer_pos.c) > 0
  ),
  distinct_answers as (
    select
      group_id,
      listagg(answer, '') within group (order by answer) distinct_answers
    from distinct_one_answer_per_row
    group by group_id
  ),
  answer_length as (
    select
      group_id,
      length(distinct_answers) answer_length
    from distinct_answers
  )
select
  sum(answer_length)
from answer_length

Part two changes the rules a bit in a way that we should only count the answers that appear on all answered forms per group.

To get to this point we need to compare the number of total answers per group (no matter the answer) and the number of answers per group to a specific answer-position (character). If both match, this specific answer-position appears in all the answers per group.

with
  answer_pos as (
    select column_value c
    from table(sys.odcivarchar2list(
      'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'
      )
    )
  ),
  base_data as (
    select
      rownum answer_id,
      column_value answers,
      case when column_value is null then 1 else 0 end is_break
    from table(
      aoc_file_loader.file_as_stringlist(
        aoc_file_loader.local_url('day_6_input.txt')
      )
    )
  ),
  grouped_data as (
    select
      answer_id,
      group_id,
      answers
    from (
      select
        answer_id,
        answers,
        is_break,
        sum(is_break) over (
          order by answer_id
          range between unbounded preceding
            and current row)
          group_id
      from base_data
    )
    where is_break = 0
  ),
  distinct_one_answer_per_row as (
    select
      group_id,
      answer_pos.c answer,
      count(distinct answer_id) 
        over (partition by group_id) group_answers,
      count(*) 
        over (partition by group_id, answer_pos.c) this_pos_answered
    from grouped_data
      cross join answer_pos
    where instr(answers, answer_pos.c) > 0
  ),
  all_agreed_upon_answers as (
    select distinct
      group_id,
      answer
    from distinct_one_answer_per_row
    where group_answers = this_pos_answered
  ),
  distinct_answers as (
    select
      group_id,
      listagg(answer, '') within group (order by answer) distinct_answers
    from all_agreed_upon_answers
    group by group_id
  ),
  answer_length as (
    select
      group_id,
      length(distinct_answers) answer_length
    from distinct_answers
  )
select
  sum(answer_length)
from answer_length

A bit tricky, but fun and rewarding.

You can find the complete code in the github-repository.