[Example] Logging outgoing and incoming items.
Posted: Sat Jun 16, 2012 7:14 pm
2023-02-19: Removed an orphaned script event.
Here we go again for another database example. Based on the request in topic: http://user.services.openoffice.org/en/ ... 5&p=238345 (logging outgoing and incoming keys of a prison ward)
Disclaimer:This database is distributed for educational puposes as a "native Base database" in a single .odb file. This type of database has a bad reputation because it lacks many features, because it is slow and most importantly, it will destroy your data beyond repair if something weird happens. It is possible to convert this embedded HSQL database to a fully functional and more reliable external HSQLDB or use some other well known relational database engine such as MySQL, PostgreSQL etc.
Greetings to all US inmates who work for Chinese wages in privately run gulags, sheltered by attendants who collect their utilities on the internet. This database does not include any macros nor any other executable code. In particular, it does not include my "Folsom" virus to open all gates on the eve of 4th of July.
The attached database document contains an embedded HSQLDB with 2 event tables and 3 simple inventories. A simple inventory list has an integer auto-ID and some name field. All names are enforced to be unique. All names are of type VARCHAR_IGNORECASE where “Abc”equals ”abc” and ”ABC” etc.
The 3 inventory lists:
1) List of locations with an additional unique index on the names.
2) List of keys with a location-ID and the the unique index on the names.
3) List of personell with 3 letter short names (initials) and unique index on names. Plus 2 boolean fields “Auth” and "Ret" marking a person as authorized to give and take keys or retired (no longer active).
Two "working tables" to record outgoing and incoming events
Table "OUTGOING" records all events where one authorized person (PID) hands out a key (KID) to some other person (PUSER). Each event gets an auto-ID and a time stamp.
Table "INCOMING" records all events where an OUTGOING event is “closed” by an authorized person (the key is back again). The incoming event records the outgoing ID, a time stamp and the receiving autority.
This is the whole database as shown in the relations window.
The simple lists had been created in the Base GUI (table designer, index designer, relation designer). The two main tables have been written down in plain SQL for 3 reasons:
1) The 1-to-1 relation between OUTGOING and INCOMING is asymmetric. The incoming keys inherit their ID from the outgoing ones.
2) There is an additional CHECK constraint which takes care that the autority which hands out the key is different from the key user (PID != PUSER).
3) The table designer can not set up any time stamps
This is the whole script to create all the tables, indices and relations from scratch:
Executing this SQL code from menu:Tools>SQL will create all tables, indices and relations from scratch.
You may want to run the following SQL in order to permanently remove the constraint which does not allow that an authorized person hands out a key to himself:
######################################################
Then we have some simple queries:
qListBoxStaff is a list of all persons to fill the “key user” list boxes.
qListBoxAuth is a list of all persons to fill the “authority” list boxes.
qListBoxKeys is a list of all keys to fill list boxes with the key names and their respective location name:
Please notice: The above list box of keys shows all the keys including the currently used ones. Someone could virtually hand out the same key twice. Instead of a macro based solution I added the following query “qDuplicateKeys” to indicate such errors in the form:
Any dupes are shown in bold red font on a separate list attached to the main form.
Finally, there is query "qOutgoing_All" to show all the outgoing and incoming events including the ones that are "out" but not yet "in":
This is the script to delete all test data from the database in the right order of dependency (menu:Tools>SQL):
Now close and reload the database document.
In the oppsite order you can fill out the inventory lists with fresh user data:
1. Enter all unique location names into the locations table.
2. Enter all unique key names and their associated locations into form keys@locations
3. Enter all unique initials into table "Personell" and mark the authorities.
###############################################################################
This is how to use the main form with the prepared lists of persons, keys and locations:
- The authority who hands out a key types his initials into the first list box.
- Hit Tab, type the key name into the key box.
- Hit Tab, type the initials of the key user.
- Hit enter to store the new OUTGOING record with the PID, KID, PUSER plus a new auto-ID and time stamp. The 3 boxes are cleared for the next new record. The undo-button clears unsaved form content. It does not withdraw any record that has been saved to the database.
If the 3 list boxes are not cleared for new entry, some error occured and nothing has been stored. Unfortunately, the form does nothing without any error message when the giving authority is the same person as the receiving user of the key. Try out to see what I mean.
- Hit the refresh button on top of the key list. The new record appears on top of the table. Hit the refesh button whenever you want to see the current state of the database. A macro could do that automatically on any changes.
If the given key has been used twice, the key name will be shown in bold red font in the extra list right of the table. This is only a quick work-around instead of a macro driven solution to prevent duplicates. The duplicate error can be fixed by deleting the last record directly out of the OUTGOING table.
When a key comes back into the ward the receiving authority selects the respective outgoing event in the table grid and confirms the reception by typing his initials into the editable red list box in the bottom-right area. Hit Enter to store a new incoming event with the same ID as the outgoing event, a time stamp and the PID of the receiving authority. Again, the undo-button clears unsaved form content. It does not withdraw any record that has been saved to the database.
At the bottom left corner of the table there is a group of buttons to sort and filter the table. The second last button toggles the preset filter on and off. By default the form is loaded with a filter that shows only the currently used keys without incoming data (outgoing ID without any incoming ID). When you turn off the filter you see all records including the ones with incoming time and receipient.
This is the whole tree of logical forms in the main form document as displayed in the form navigation tool:
"NewOut" is a form which appends new records only. You can not scroll back, edit or delete any outgoing event. It is directly bound to table OUTGOING. (Well, you may "scroll" back to records entered in this session by tabbing backwards (Shift+Tab))
"Outgoing" is a second independent form to fill the main overview table. It is bound to query "qOutgoing_All"
"SelectedOut" is a subform of "Outgoing". It only repeats the respective key and key user for the outgoing event selected in the parent form (white list boxes in the bottom-right corner).
"Incoming" is another subform of "Outgoing" directly bound to table INCOMING. It inherits the outgoing ID number from its parent form and the red list box writes the receiving authority ID.
"DuplicateKeys" is another subform of "Outgoing". There is no other relation between this subform and its parent. It shows any record of "qDuplicateKeys" regardless of what is selected in the parent form. I made it a subform so it refreshes automatically together with its parent form.
What the navigator window does not show: Columns #2 to #5 of the main grid hold numeric IDs. The numeric IDs are masked by list boxes showing the respective names of persons and keys. While in form design mode right-click a column header and get the column properties in order to see what I mean.
The two time stamps are displayed by formatted fields in ISO format YYYY-MM-DD HH:SS.
Here we go again for another database example. Based on the request in topic: http://user.services.openoffice.org/en/ ... 5&p=238345 (logging outgoing and incoming keys of a prison ward)
Disclaimer:This database is distributed for educational puposes as a "native Base database" in a single .odb file. This type of database has a bad reputation because it lacks many features, because it is slow and most importantly, it will destroy your data beyond repair if something weird happens. It is possible to convert this embedded HSQL database to a fully functional and more reliable external HSQLDB or use some other well known relational database engine such as MySQL, PostgreSQL etc.
Greetings to all US inmates who work for Chinese wages in privately run gulags, sheltered by attendants who collect their utilities on the internet. This database does not include any macros nor any other executable code. In particular, it does not include my "Folsom" virus to open all gates on the eve of 4th of July.
The attached database document contains an embedded HSQLDB with 2 event tables and 3 simple inventories. A simple inventory list has an integer auto-ID and some name field. All names are enforced to be unique. All names are of type VARCHAR_IGNORECASE where “Abc”equals ”abc” and ”ABC” etc.
The 3 inventory lists:
1) List of locations with an additional unique index on the names.
2) List of keys with a location-ID and the the unique index on the names.
3) List of personell with 3 letter short names (initials) and unique index on names. Plus 2 boolean fields “Auth” and "Ret" marking a person as authorized to give and take keys or retired (no longer active).
Two "working tables" to record outgoing and incoming events
Table "OUTGOING" records all events where one authorized person (PID) hands out a key (KID) to some other person (PUSER). Each event gets an auto-ID and a time stamp.
Table "INCOMING" records all events where an OUTGOING event is “closed” by an authorized person (the key is back again). The incoming event records the outgoing ID, a time stamp and the receiving autority.
This is the whole database as shown in the relations window.
- Relation manager of prison key inventory.
1) The 1-to-1 relation between OUTGOING and INCOMING is asymmetric. The incoming keys inherit their ID from the outgoing ones.
2) There is an additional CHECK constraint which takes care that the autority which hands out the key is different from the key user (PID != PUSER).
3) The table designer can not set up any time stamps
This is the whole script to create all the tables, indices and relations from scratch:
Code: Select all
CREATE CACHED TABLE "Personell"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Initials" VARCHAR_IGNORECASE(3) NOT NULL,
"Auth" BOOLEAN DEFAULT FALSE NOT NULL,
"Ret" BOOLEAN DEFAULT FALSE NOT NULL
)
CREATE UNIQUE INDEX "uniqInitials" ON "Personell"("Initials")
CREATE CACHED TABLE "OUTGOING"(
ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PID INTEGER NOT NULL,
PUSER INTEGER NOT NULL,
"KID" INTEGER NOT NULL,
CONSTRAINT FK_AUTH1 FOREIGN KEY(PID) REFERENCES "Personell"("ID"),
CONSTRAINT AUTH_NE_USER CHECK("OUTGOING".PID!="OUTGOING".PUSER),
CONSTRAINT FK_USER_GET FOREIGN KEY(PUSER) REFERENCES "Personell"("ID")
)
CREATE CACHED TABLE "INCOMING"(
ID INTEGER NOT NULL PRIMARY KEY,
TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PID INTEGER NOT NULL,
CONSTRAINT FK_AUTH2 FOREIGN KEY(PID) REFERENCES "Personell"("ID"),
CONSTRAINT OUTGOING_INCOMING FOREIGN KEY(ID) REFERENCES "OUTGOING"(ID)
)
CREATE CACHED TABLE "Locations"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"N" VARCHAR_IGNORECASE(50) NOT NULL
)
CREATE UNIQUE INDEX "uniqLocationName" ON "Locations"("N")
CREATE CACHED TABLE "Keys"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"N" VARCHAR(3) NOT NULL,
"LID" INTEGER,CONSTRAINT SYS_FK_82 FOREIGN KEY("LID") REFERENCES "Locations"("ID")
)
CREATE UNIQUE INDEX "uniqKeyName" ON "Keys"("N")
ALTER TABLE "OUTGOING" ADD CONSTRAINT SYS_FK_79 FOREIGN KEY("KID") REFERENCES "Keys"("ID")
You may want to run the following SQL in order to permanently remove the constraint which does not allow that an authorized person hands out a key to himself:
Code: Select all
ALTER TABLE "OUTGOING" DROP CONSTRAINT "AUTH_NE_USER CHECK"Then we have some simple queries:
qListBoxStaff is a list of all persons to fill the “key user” list boxes.
Code: Select all
SELECT "Initials", "ID"
FROM "Personell"
WHERE "Ret" = FALSE
ORDER BY "Initials" ASCCode: Select all
SELECT "Initials", "ID"
FROM "Personell"
WHERE "Auth" = TRUE AND "Ret" = FALSE
ORDER BY "Initials"Code: Select all
SELECT "Keys"."N" || ' ' || "Locations"."N" AS "Visible List", "Keys"."ID"
FROM "Keys", "Locations"
WHERE "Keys"."LID" = "Locations"."ID"
ORDER BY "Visible List" ASCCode: Select all
SELECT "OUTGOING"."KID", COUNT( "OUTGOING"."ID" ) FROM { OJ "INCOMING" RIGHT OUTER JOIN "OUTGOING" ON "INCOMING"."ID" = "OUTGOING"."ID" } WHERE "INCOMING"."ID" IS NULL GROUP BY "OUTGOING"."KID" HAVING ( ( COUNT( "OUTGOING"."ID" ) > 1 ) )Finally, there is query "qOutgoing_All" to show all the outgoing and incoming events including the ones that are "out" but not yet "in":
Code: Select all
SELECT "OUTGOING"."PID" AS "PID_OUT", "OUTGOING"."TS" AS "TS_OUT", "OUTGOING"."KID", "OUTGOING"."PUSER", "INCOMING"."PID" AS "PID_IN", "INCOMING"."TS" AS "TS_IN", "OUTGOING"."ID" FROM { OJ "OUTGOING" LEFT OUTER JOIN "INCOMING" ON "OUTGOING"."ID" = "INCOMING"."ID" }, "Keys" WHERE "OUTGOING"."KID" = "Keys"."ID"Code: Select all
DELETE FROM INCOMING;
DELETE FROM OUTGOING;
DELETE FROM "Personell";
DELETE FROM "Keys";
DELETE FROM "Locations";
SHUTDOWN COMPACT;In the oppsite order you can fill out the inventory lists with fresh user data:
1. Enter all unique location names into the locations table.
2. Enter all unique key names and their associated locations into form keys@locations
3. Enter all unique initials into table "Personell" and mark the authorities.
###############################################################################
This is how to use the main form with the prepared lists of persons, keys and locations:
- The authority who hands out a key types his initials into the first list box.
- Hit Tab, type the key name into the key box.
- Hit Tab, type the initials of the key user.
- Hit enter to store the new OUTGOING record with the PID, KID, PUSER plus a new auto-ID and time stamp. The 3 boxes are cleared for the next new record. The undo-button clears unsaved form content. It does not withdraw any record that has been saved to the database.
If the 3 list boxes are not cleared for new entry, some error occured and nothing has been stored. Unfortunately, the form does nothing without any error message when the giving authority is the same person as the receiving user of the key. Try out to see what I mean.
- Hit the refresh button on top of the key list. The new record appears on top of the table. Hit the refesh button whenever you want to see the current state of the database. A macro could do that automatically on any changes.
If the given key has been used twice, the key name will be shown in bold red font in the extra list right of the table. This is only a quick work-around instead of a macro driven solution to prevent duplicates. The duplicate error can be fixed by deleting the last record directly out of the OUTGOING table.
When a key comes back into the ward the receiving authority selects the respective outgoing event in the table grid and confirms the reception by typing his initials into the editable red list box in the bottom-right area. Hit Enter to store a new incoming event with the same ID as the outgoing event, a time stamp and the PID of the receiving authority. Again, the undo-button clears unsaved form content. It does not withdraw any record that has been saved to the database.
At the bottom left corner of the table there is a group of buttons to sort and filter the table. The second last button toggles the preset filter on and off. By default the form is loaded with a filter that shows only the currently used keys without incoming data (outgoing ID without any incoming ID). When you turn off the filter you see all records including the ones with incoming time and receipient.
This is the whole tree of logical forms in the main form document as displayed in the form navigation tool:
- Form navigator of prison key inventory
"Outgoing" is a second independent form to fill the main overview table. It is bound to query "qOutgoing_All"
"SelectedOut" is a subform of "Outgoing". It only repeats the respective key and key user for the outgoing event selected in the parent form (white list boxes in the bottom-right corner).
"Incoming" is another subform of "Outgoing" directly bound to table INCOMING. It inherits the outgoing ID number from its parent form and the red list box writes the receiving authority ID.
"DuplicateKeys" is another subform of "Outgoing". There is no other relation between this subform and its parent. It shows any record of "qDuplicateKeys" regardless of what is selected in the parent form. I made it a subform so it refreshes automatically together with its parent form.
What the navigator window does not show: Columns #2 to #5 of the main grid hold numeric IDs. The numeric IDs are masked by list boxes showing the respective names of persons and keys. While in form design mode right-click a column header and get the column properties in order to see what I mean.
The two time stamps are displayed by formatted fields in ISO format YYYY-MM-DD HH:SS.