#Need help with model design to show latest set of data

24 messages · Page 1 of 1 (latest)

potent mirage
#

I have a set of servers where I get current list of packages installed a few times daily. What is a good way of modelling this into ORM so that one can fetch the latest set of packages for any given server or to be able identify if a package is current on any server or old information.

The basic model I have looks like this:

class Server(models.Model):
    hostname = models.CharField(max_length=200)

class Package(models.Model):
    name = models.CharField(max_length=255)
    version = models.CharField(max_length=255)
    class Meta:
        unique_together = ("name", "version")

class HostDetails(models.Model):
    time = models.DateTimeField()
    server = models.ForeignKey(Server, on_delete=models.CASCADE)
    packages = models.ManyToManyField(Package)

I am finding the lastest set of packages for a given server using the following:

host_details = HostDetails.objects.filter(host=host).order_by("-time")[0]
host_details.packages.all()
raw breach
#

The models look fine to me. I assume HostDetails is the model that gets several instances created on a day as a sort of snapshot of installed packages on the host.

potent mirage
vernal tartan
#

That last requirement will be the trickiest—finding out if a given package is the latest on any host

raw breach
#

for a non optimized loop maybe like so?

host_details = HostDetail.objects.get(name="my-host").latest("time")
for host_package in host_details.packages.all():
    latest_package = Package.objects.filter(name=host_package.name).order_by("version").last()
    if host.package.version < latest_package.version:
        print(f"Package {host_package.name} on host {host_details.server.hostname} is outdated")
#

There's probably a clever way to annotate outdated packages within a single query instead ...

potent mirage
#

I don't think we can compare versions like that easily (knowing linux package versions are strange).

vernal tartan
#

I think I would look to annotate the Host with its latest query date and then comparing with that's something…

raw breach
#

maybe comparing package versions requires a modelling of Version itself? or some more complex lookup from some repository that keeps organized tracking of releases?

potent mirage
#

The question is less about version but more about for a given package-version combination (a row in the Package table), which all HostDetails records have that as latest entry for the servers.

vernal tartan
#

i.e. "what's still running postgres 11.7"

potent mirage
#

Exactly that 🙂

sudden warren
#

So would HostDetails.objects.filter(packages=the_package) not give you that? 🤔

potent mirage
#

That would provide all HostDetails entries I think, then how to identify which ones are the current data?

sudden warren
#

Yes, so that filter give you the list of HostDetails with the matching package. “A row in the Package table” — what do you mean by the “current data” ?

#

How’s that represented?

potent mirage
#

I mean say a package postgres 11.7 and a 100 HostDetails entries have that, but except two servers, in all other servers that package is not there anymore.

sudden warren
#

Oh, I see… You want to fetch each HostDetails annotating with a Boolean if it has package.name but not package.version == latest_one

potent mirage
#

Now I need to identify which all hostdetails entries have postgres 11.7 in the latest entry for those servers?

potent mirage
sudden warren
#

OK… so filter to the subset that have the package at all… to make it simpler. Annotate with T/F “Does the latest entry match what I’m looking for?” Filter on that annotation. No?

If that’s not right, you might be better off posting to the forum where you can type it out better, so it’s easier to follow.

potent mirage
#

I will ask in forum with more detailed example.

#

Thank you 🙂