Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

This time we expose…let’s call it…a “Phenomenon”! I’m also amazed about this “feature” since I heard from it. I wasn’t aware this happens on a subtyping level, but in a large scaling project it can be a dramatic impact. So, be aware what you’re doing as things don’t look always as bright as they might be!

Trust me…For this one you want to have your database administrator as a friend! What I always say…Don’t forget him to treat him (or her) on a beer (or some wine!) 😉

Let get right into it…

A teasing introduction again, but what am I talking about this time? Well, that’s the principles of creating a subtype entity out of a parent (current) entity. Before we dive into any specifics, it’s first a good plan to see how the database looks like after the publication of one simple entity. So, dive into your project, create that first (project) entity with just an easy text property prj_name. Generate the default building blocks, and publish it into runtime. The first question would be…How does this change our database?

I make sure the database (in my case Postgres) is spitting out some more logging:

• Editing a config file: sudo vi /var/lib/pgsql/data/postgresql.conf
• Update this setting: log_statement = 'all' (in “Vim” you can search with / and use n|N for next/previous entry!)
• Restart the service: systemctl restart postgresql
• Check the logging with: sudo tail -999f /var/lib/pgsql/data/log/postgresql-{day}.log | grep -i "create table"

After my publication, I clearly see my create query passing by:

1
2
3
4
5
6
7
8

CREATE TABLE o2aw_tipsaw_genericproject
  (
     id                    BIGINT NOT NULL,
     prj_name              VARCHAR(64) NULL,
     s_item_status         INTEGER NULL,
     s_is_temporary_copy   BOOLEAN NULL,
     s_temporary_copy_data BYTEA NULL
  );

“o2aw_tipsaw_genericproject” in small pieces:

• o2 = is the ‘o’ for organization and your organization id (read more here)
• aw_tipsaw_generic = is the concatenation of the “package owner” and the “product name” of your package; Have a look in the package properties of the project (right-click context menu)!
• project = dûh!?

With this verified information, it’s time to create a new subtype of the ‘Project’ entity. I create a subtype ‘Project Secret’…You can do this by right-clicking the entity and create a subtype out of it:

Follow the steps for creating a new entity project_secret and add one additional Boolean property prj_is_secret. When done, do a publication and watch the database queries again (HINT: do a grep on alter) from the logging:

1
2
3
4

ALTER TABLE o2aw_tipsaw_genericproject
  ADD COLUMN s_discriminatorid VARCHAR(32) NULL;
ALTER TABLE o2aw_tipsaw_genericproject
  ADD COLUMN prj_is_secret BOOLEAN NULL;

Do you see what I see!?!? WHAT? Our subtype information is simply (?) stored in the same table! Is this wise/smart to do? Will is benefit the future of inheritance? I also wonder who made this decision!? For me this raises one valuable question; How many columns can a database table have?

Well, isn’t that’s interesting! You can ask yourself the question; Will I ever hit those numbers? Well, never say never; it’s possible…At least in a large project with lots of entities (that use subtyping), you should have a note of this specific detail!

…

So far, so good…Only, this raises (at least from my side) more questions:

What is the s_discriminatorid column hiding?

Discriminator = The partial key of the weak entity set. It is just a part of the key as only a subset of the attributes can be identified using it. It is partially unique and can be combined with other strong entity set to uniquely identify the tuples.

Well, that matches with what we see is happening! Now, for a practical example. Let’s add some instances for both entities in runtime and do this query after it: SELECT id, s_discriminatorid, prj_name FROM o2aw_tipsaw_genericproject;. I query the database with the tool HeidiSQL. The outcome? Have a look:

What is the id-value referring to?

That’s the type-id of the sub-entity (the value in front of the dot in the URL when viewing the entity instance.)

What if I add a third level of subtyping?

So, the project_secret gets a subtype to project_secret_extra 😊 with an Is Extra Boolean property flag…Publish it, instance it in runtime, and watch the database output:

Interesting…!!

Why is the parent entity not having a discriminator id filled in?

I have no idea!? Well, the column was introduced after the creation of the first subtype. So, if you never have a discriminator it’s also clueless to save it on a single (non-subtype) entity!

What is the value of the column when not used in the entity, so what is the value of prj_is_active for my parent entity instance?

Now that we have some data, it’s interesting to see what is saved overall in the other columns:

You see that they get a ‘NULL’ value!

For your information; On the first subtype, I added also the ‘Assignee’ BB and the ‘Tracking’ BB to see the outcome…Just because we can!

Finally, is it bad to have NULL values in a database?

Well, I’m not the only one asking myself that same question…Have a look here, here, and here

Make yourself a final conclusion if it’s good or bad…We can’t change the platform (and it will never be changed as well…I guess), so we just have to deal with it. I give it an insiders “DONE” on how data is saved in the database when we work with subtypes in our beloved platform. Be aware of it and make valid choices as subtyping is not always required; For sure not with the examples in this post…We could add those 2 properties just to the initial entity! Always have a second thought and a collaboration with your team-mates. Let’s have a great weekend and I see you in another topic on AppWorks Tips.

Don’t forget to subscribe to get updates on the activities happening on this site. Have you noticed the quiz where you find out if you are also “The AppWorks guy”?