postgresql – Enforce that every company has at least one CEO

I’ve a requirement to ensure that every company has at least one CEO.

Example database model with requirements db<>fiddle.

Basically, a CEO can’t be removed from a company without naming another CEO. The CEO can’t take another role in the company, when there is no other CEO. But it’s OK to remove all 3 entities: company, staff, role.

I’ve tried following ideas:

  1. Use before update or delete trigger: look at the graph and try to guess what happens after execution. It doesn’t work, because it’s executed immediately.
  2. Use after update or delete constraint DEFERRABLE INITIALLY DEFERRED trigger: How check count of CEOs’ if staff is deleted too? Or how to allow, if company is deleted with staff and role? And it isn’t easy to test with pgtap.

Any further ideas?

I’m asking not about creation, but about the constraint that a CEO can’t be removed from company without naming another CEO. It’s just most simple example of the difficulty with trigger and relations.


Found a workaround

See db<>fiddle. In the core following workaround seems to work:

  1. define after update or delete triggers those checks the state after deletion. But don’t check if related entity deleted to. Because relations are ignored, triggers are needed on whole chain except root. In the example on roles and staff:
CREATE FUNCTION roles_updates() RETURNS trigger AS $$
BEGIN
  IF
    -- check only if staff wasn't deleted too, ignore otherwise
    (select count(1) from staff where name = OLD.staffid) > 0
    -- check if there is at least one ceo left
    and (
        select count(1)
        from roles r
        join staff s on r.staffid = s.name
        where
          s.company in (select company from staff where name = OLD.staffid)
          and r.role = 'ceo') < 1
  THEN
    RAISE EXCEPTION 'roles: company without ceo isn''t allowed';
  END IF;

  RETURN OLD;
END; $$ LANGUAGE plpgsql;
  1. Define DEFERRABLE INITIALLY DEFERRED on relations. This allows check for foreign keys at the end of transaction. Therefore I can use:
begin;
delete from company where name = 'amd';
delete from staff where name = 'lisa';
delete from roles where id = 1;
rollback;

instead to put it into one statement/common table expression like:

with DeletedCompany as (
  delete from company where name = 'amd'
),
DeletedStaff as (
  delete from staff where name = 'lisa'
)
delete from roles where id = 1;

There is still a non working use case:


begin;
delete from roles where id = 1;
delete from staff where name = 'lisa';
delete from company where name = 'amd';
rollback;

I think it can be fixed with usage of constraint trigger, which is executed at the end of transaction. But because it is hard to test, I prefer above workaround.

postgresql – How to enforce that every company has a ceo?

I’ve a requirement to ensure that every company has an owner. Example database model with requirements @db<>fiddle.

I’ve tried following ideas:

  1. use before update or delete trigger: look at the graph and try to guess what happens after execution. It doesn’t work, because it’s executed immediately.
  2. use after update or delete constraint DEFERRABLE INITIALLY DEFERRED trigger: how check count of ceos’ if staff is deleted too? Or how to allow, if company is deleted with staff and role? And it isn’t easy to test with pgtap.

Any further ideas? Thanks!

c++ – How do game engines enforce global, engine-specific coordinate systems?

In Unreal, the 3D coordinate system is defined as a right-handed, X-forward, Y-left, Z-up frame of reference; but, the default 3D coordinate system for, say, DirectX is a left-handed, X-right, Y-up, Z-forward frame of reference.

How does an engine enforce this behavior? Or, how do engines define a global coordinate system that “just works”? When an object is asked to get its “forward direction” it doesn’t do any matrix or quaternion conversions that I can tell, it just returns the I-basis (or X-component) of its transform. Its obvious something is being done to translate local transform matrices’ coordinate systems into the engine-specific transform matrices’ required coordinate systems. I just don’t know where or when these modifications are being applied.

EDIT:

A concrete example:

My confusion is compounded by how 3D cameras work; they ultimately determine how the scene is viewed. In my own engine I don’t change the default behavior that DirectX provides, so my coordinate system is a left-handed, X-right, Y-up, Z-forward. I don’t like this. I want it to be X-forward, Y-right, Z-up globally throughout the engine. Where would I do this? During the view calculation for every camera? Would that affect intuitive translations where if I translate “right” with a vector (+10,0,0) the camera erroneously appears to move forward?

air travel – How do they enforce rules on declaring funds?

One way is by using currency-sniffing dogs who are trained to detect the odor of currency. They could deploy such dogs in international departure areas, and search passengers or their luggage if the dog alerts.

CBP acknowledges that they employ currency-sniffing dogs (near the bottom of the page), and reports on a case at a land border crossing where a dog detected undeclared currency, which was seized and the driver arrested.

c++ – How can I enforce that decorator pattern is complete at compile time?

I have a C++ class (Class D) that is a decorator of another class (Class B). Class D inherits from B and also requires an instance of B to construct that it keeps track of. Class D overrides all methods in B and instead calls the same functions on it’s own instance of B. My worry is in maintaining this. If someone edits class B and adds a new function, they need to ensure that they add that function to class D as well, or it will break the pattern. Is there a way I can check at compile time that class D overrides all methods from class B? I’m working in C++.

Code sample:

class B
{
 public:
  B();
  void methodA() {std::cout << "A" << std::endl;};
  void methodB() {std::cout << "B" << std::endl;};
};

class D : public B
{
 public:
  D(B* b)
  {
   bptr_ = b;
  }
  void methodA() override 
  {
   bptr_->methodA();
  }

  void methodB() override
  {
   bptr_->methodB();
  }

  void specialDFunction() {/*do something*/};
 private:
  B* bptr_;
}


Does snapd enforce cryptographic authentication and integrity validation by default for all packages? (debian, ubuntu)

Does the snapd package manager in Debian-based systems require successful cryptographic authentication and integrity validation for all packages?

I know that software downloaded with apt-get packages must be cryptographically verified because the repo’s manifest files (synced with apt-get update) are cryptographically signed.

But what about snapd?

Do Operating Systems with snapd require valid signatures from a pinned set of keys on all packages by default?

Does apt-get enforce cryptographic authentication and integrity validation by default for all packages? (debian, ubuntu)

Does the built-in apt package manager in Debian-based systems require successful cryptographic authentication and integrity validation for all packages?

My understanding was that software downloaded with apt-get packages would be cryptographically verified because the repo would sync pgp-signed manifest files on apt-get update.

However, I stumbled on an article today that suggested that unsigned packages are accepted by default on all major Debian-based Operating Systems by the contents of /etc/dpkg/dpkg.cfg

root@disp2019:~# cat /etc/dpkg/dpkg.cfg
# dpkg configuration file
#
# This file can contain default options for dpkg.  All command-line
# options are allowed.  Values can be specified by putting them after
# the option, separated by whitespace and/or an `=' sign.
#

# Do not enable debsig-verify by default; since the distribution is not using
# embedded signatures, debsig-verify would reject all packages.
no-debsig

# Log status changes and actions to a file.
log /var/log/dpkg.log
root@disp2019:~# 

Indeed, this does not look good.

Do Operating Systems with apt require valid signatures from a pinned set of keys stored to the apt-key keyring on all packages by default? Or are these signatures just treated as optional by default?

csom – Enforce File Naming Convention in a Folder inside a Document Library SharePoint Online

SharePoint Online specific.

I wanted to know if it is possible to enforce file naming conventions at the folder or sub-folder level in a Document Library.

For example, I have a Document library called “Documents” with many sub-folders. I want to enforce a specific file naming convention for all Word (docx) files uploaded to a “Legal” folder, e.g. “Document_Name_Department_MMYYYY”.

Then similarly a different file naming convention in the folder “IT”, etc.

I have found a site that explains how to do it using the CSOM (https://www.tatvasoft.com.au/blog/how-to-implement-remote-event-receiver-in-sharepoint-online/).

Is anyone aware if this is possible using JS, no-code, Azure or Flow? basically an alternative way rather then the CSOM example listed above.

Thanks!

How to enforce SQL Server policies in order to avoid users enabling bad practices

I have some rogue Windows Administrators who give themselves SA and then grant end-users SA.
These users then end up enabling auto-shrink, auto-growth percentage and all kinds of other poor practices – no matter how many times I tell them not to do it, they are still doing it.

Is there a way for me to use SQL Policies to forcefully stop these settings from being enabled? Currently I can only evaluate them and get a notification when the policies are failing.

amazon – Enforce user to set Mfa before using services he’s allowed to use

There is an answer to this question to this link.

Is there a better way, a more automated way, instead of making the user go in the console and look for how to manage the authenticator?

What happens if after that he removes the authenticator?

Expected:

  1. User tries to log in

  2. Console asks for password change

  3. Colsole asks for scanning the code and introducing the codes

  4. User logs in with new password and the code from authenticator