sqlite query

[Jochem]

I have severall tables with the usage of software in our company with some foreign keys
Now I want to get a list of the 5 most common users of a software package.
I cant get a list of users. Maybe it is easier to do with auto-it code, but I would like to do it with a single sqlite query.

I tried a lot of joins, but can`t get it working, I only get the following query working with the id of users.

query:

SELECT LUuser, COUNT (LUuser) AS cnt 
from license_usage
where LUtime > (SELECT LDid FROM license_date ORDER BY ABS((SELECT strftime('%s','now','-2 month')) - LDdate) LIMIT 1)
and LUpackage = (select LPid from license_package where LPname = "package")
GROUP BY LUuser
ORDER BY cnt 
DESCLIMIT 5;

db:

CREATE TABLE [Users] (
  [USid] INTEGER NOT NULL ON CONFLICT IGNORE PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
  [USname] CHAR(50) NOT NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT IGNORE COLLATE NOCASE,
  [USfirstname] CHAR(25) COLLATE NOCASE,
  [USfamilyname] CHAR(50) COLLATE NOCASE,
  [USstatus] CHAR(25) COLLATE NOCASE,
  [USfullname] CHAR(50) COLLATE NOCASE);
 
CREATE TABLE [license_Usage] (
  [LUid] INTEGER NOT NULL ON CONFLICT IGNORE PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
  [LUpackage] INTEGER(10) NOT NULL ON CONFLICT IGNORE CONSTRAINT [FKLU_package] REFERENCES [license_package]([LPid]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE,
  [LUuser] INTEGER(10) NOT NULL ON CONFLICT IGNORE CONSTRAINT [FKLU_user] REFERENCES [Users]([USid]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE,
  [LUtime] INTEGER(10) NOT NULL ON CONFLICT IGNORE CONSTRAINT [FKLU_time] REFERENCES [license_date]([LDid]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE,
  UNIQUE([LUpackage], [LUuser], [LUtime]) ON CONFLICT IGNORE);
 
CREATE TABLE [license_package] (
  [LPid] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
  [LPname] CHAR(50) NOT NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT IGNORE COLLATE NOCASE,
  [LPdesciption] CHAR(50) COLLATE NOCASE);
 
CREATE TABLE [license_date] (
  [LDid] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT,
  [LDdate] INTEGER(15) NOT NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT IGNORE COLLATE NOCASE);

[jchd]

I have some hard time with your schema and the way you use it.

First, a couple of general remarks: your schema uses several no-op constraints which you should get rid of. Indeed, NOT NULL ON CONFLICT IGNORE literally means "the column must not be null but if it is, then ignore the error".

Similarly, UNIQUE ON CONFLICT IGNORE is a no-op constraint, yet it forces SQLite to maintain a unique index where entries are allowed to be non-unique.

Foreign keys should never be allowed to hold null.

Also why license usage date is made a foreign key to a separate table is unclear.

Finally you use the NOT DEFERRABLE clause which might not always be what you want in practice, depending on your actual needs.

Almost forgot: it is pointless to specify a size and/or precision in column description. For SQLite, char(50) is the same as char or text.

Now the beef: your query uses conditions that exceed the initial wording "I want to get a list of the 5 most common users of a software package".

Can you post some sample data (with emasculated personal details) to test? A join should work at any rate.

Edited September 22, 2014 by jchd

[jchd]

AFAICT, the following schema is enough to hold data and support the queries you could have done with the previous one. Yet it is simpler and more robust.

Again it's partly off the top of my head since I've no idea why you made your initial choices.

CREATE TABLE "license_package" (
  "LPid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  "LPname" CHAR UNIQUE COLLATE NOCASE, 
  "LPdescription" CHAR COLLATE NOCASE);

CREATE TABLE "Users" (
  "USid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  "USname" CHAR UNIQUE COLLATE NOCASE, 
  "USfirstname" CHAR COLLATE NOCASE, 
  "USfamilyname" CHAR COLLATE NOCASE, 
  "USstatus" CHAR COLLATE NOCASE, 
  "USfullname" CHAR COLLATE NOCASE);

CREATE TABLE "license_Usage" (
  "LUpackage" INTEGER NOT NULL CONSTRAINT "FKLU_package" REFERENCES "license_package"("LPid") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE, 
  "LUuser" INTEGER NOT NULL CONSTRAINT "FKLU_user" REFERENCES "Users"("USid") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE, 
  "LUtime" INTEGER NOT NULL COLLATE NOCASE, 
  CONSTRAINT "sqlite_autoindex_license_Usage_1" PRIMARY KEY ("LUpackage", "LUuser", "LUtime")) WITHOUT ROWID;

Edited September 22, 2014 by jchd

[Jochem]

Thanks for the answers yet, I will first work on the no-op constrains.

I thought the the "on conflict ignore" option ignores the transaction.

Data is a separate table because severall users can use the software at the same time.

the sizes I used to get a smaller screen in the record editor in sqlite expert. Sometimes I need to change something in sqlite directly.

but first i will remove all the no-op constrains

[jchd]

 

Data is a separate table because severall users can use the software at the same time.

Precisely why I removed the license_date table and invite you to store usage date directly in license_Usage.LUtime.

A row in this table uniquely records usage of a given software package by a given user at a given datetime.

Back to your query, if I understand what you want, it is something like: "get the set of the top K users who used package X the most in the last N months, in decreasing order of usage count". It can be days or hours or months or ... In your query, K=5, X = 'package' and N=2 .

Notice that this part of your query is plain wrong (no offence meant): "... where LUtime > (SELECT LDid FROM ...". You're comparing a timestamp with a timestamp ID, that is orange and onions.

With the schema I propose your query could be:

SELECT
      LUuser,
      COUNT(LUuser) AS cnt 
from
    license_usage
    join license_package on lupackage = lpid and lpname = "package" and lutime > cast(strftime('%s', 'now', '-2 months') as integer)
    join users on luuser = usid
GROUP BY LUuser
ORDER BY cnt DESC
LIMIT 5;

Note that you can place conditions along with ON clauses like above, or put them in a WHERE clause or in a HAVING clause. The query optimizer should produce the same result in all three cases.

I currently have no time to enter dummy data into Expert to check I didn't make an error, but the query itself is valid and should produce the result you want efficiently in the simplified schema.

[Jochem]

your query workes like a charm.

for the license_date I will try to update the date into license_Usage.LUtime without destroying the data.

but do you know why i get an error in autoit on "without rowid"

(malformed database schema (license_Usage) - near "WITHOUT": syntax error)

thanks a lot!

[jchd]

You're using an outdated DLL. Download it from our repository by selecting sqlite3.dll or sqlite3_x64.dll. Both are latest release version, namely v3.8.6. Version 3.8.7 is in beta test stage and should be release pretty soon, but it doesn't impact your use at any rate.

[jchd]

I forgot to mention: to update your table to the new schema without losing anything, I'd use Expert. First make a clean backup copy of the DB while it is not in use (important, else some things will also live in journal file(s)) then delete the foreign key to license_date.

Now use a simple update to transfer actual dates from LDdate to LUtime:

update license_usage set lutime = (select lddate from license_date where lutim = ldid);
drop table license_date

Untested, of course.

[Jochem]

You're using an outdated DLL. Download it from our repository by selecting sqlite3.dll or sqlite3_x64.dll. Both are latest release version, namely v3.8.6. Version 3.8.7 is in beta test stage and should be release pretty soon, but it doesn't impact your use at any rate.

 thanks

[Jochem]

I forgot to mention: to update your table to the new schema without losing anything, I'd use Expert. First make a clean backup copy of the DB while it is not in use (important, else some things will also live in journal file(s)) then delete the foreign key to license_date.

Now use a simple update to transfer actual dates from LDdate to LUtime:

update license_usage set lutime = (select lddate from license_date where lutim = ldid);
drop table license_date

Untested, of course.

 thanks again

[Jochem]

just one question again:

I am strugling with the:

CONSTRAINT "sqlite_autoindex_license_Usage_1" PRIMARY KEY ("LUpackage", "LUuser", "LUtime")) WITHOUT ROWID;

because now i see that one software package has a very bad license model, and if somebody opens twice the program it uses two licenses. so the above unique constraint isn`t correct (there needs to be a duplicate values), but i can`t remove it because "without rowid" needs a primary key, And there isn`t any correct unique constraint, with those three collumns. So I think I should recreate the rowid collumn.

Am I wrong?

Or should I make a collumn with the number of used licences by one person, which is in 99,999999% of the cases just 1. and makes my autoit scipt harder to make.

There is only a performance reason to use withoud rowid as I understood

Edited October 14, 2014 by Jochem

[jchd]

There are multiple ways to overcome the issue.

Without changing the schema, you can store a more precise datetime (down to milli- or even microseconds) if I'm right thinking that it's the process launch timestamp.

You can remove the "without rowid" clause from the table definition; in this case, define an ID column explicitely this way: "ID integer primary key". That means copying the data to the new table.

[Jochem]

Without changing the schema, you can store a more precise datetime (down to milli- or even microseconds) if I'm right thinking that it's the process launch timestamp.

 

Well I check once every 5 minutes the license servers output, and the time datetime i use is every five hole minutes, so they are grouped every 5 minutes. Easier to check what software is used on a specific time (or at least, that was the idea) instead of searching a value.

but anyway I will introduce the rowid collumn.

thanks

[jchd]

Ah, I see that a more precise timestamp won't help you here. Then yes, make you own ID integer primary key.

[Jochem]

Ah, I see that a more precise timestamp won't help you here. Then yes, make you own ID integer primary key.

Yes, that was also the reason that I created the date table.

[jchd]

Sorry if I misled you but it wasn't obvious from the start.  It isn't doomday either!

[Jochem]

no problem at all, I added a collumn (LUTimeEpoche) instead of change the values in the LUtime, so till now, I have both values.

I preffered keeping to much data, instead of making changes while a wasn`t shure if I did it the wright way

I forgot to explain this in my first post actually

Edited October 14, 2014 by Jochem

[jchd]

SQLite will let you store really large amounts of data efficiently (provided a non-crazy design) and you can always simplify things after experience invites you to do so.