by Dr. Peter Wayne
| Bill has a network with 48 workstations running Alpha Five. He needs to be able to knock users off his database to perform administrative tasks. Can it be done from within Alpha Five? |
At the June, 1999 course on Alpha Five in Maryland, Bill presented his problem. Bill is the information systems manager for a department with 48 workstations connected to a network. On any given day, 30 or more users are running his Alpha Five application. He needs to perform database administration periodically, and of course it almost always happens that someone has failed to log off his database, preventing him from restructuring tables, modifying indexes, and otherwise maintaining and improving his application.
If Bill is still in the building, he can go from room to room turning off workstations that were left on. But Bill often does database administration remotely during evenings and weekends. Is there any way he can force his users out of his database?
Ideally, Bill would be able to set a system-level command that would send an immediate message to all his users warning them to finish their work and then shutting them down after an appropriate interval. Indeed, many multiuser operating systems will have such functionality built in to the OS. But our network doesn't have it, and even if it did, we don't insist that users log off the network, only that they log off the A5 application.
Steve Workings had already dealt with this problem. I'll present a slightly different solution here and will outline Steve's near the end. Our solution will be to:
The structure of the sys_control table is very simple:
Figure 1. The sys_control table has 1 numeric field, shutdown_status.
In Field Rules I limit shutdown_status to the values of -1, 1, 5, and 30:
Figure 2. A lookup rule is used to limit shutdown_status to 4 predefined values.
The shutdown_status field is evaluated as follows:
| Shutdown_status | Interpretation |
| -1 | Run |
| +1 | Shutdown in 1 minute |
| +5 | Shutdown in 5 minutes |
| +30 | Shutdown in 30 minutes |
Table 1. Values of shutdown_statusand their meanings.
We now have the sys_control table that Bill can use to set the shutdown_status for all his users. Now we have to make sure his users check the table. We can make our users check in periodically by scheduling a background script that will run every 5 minutes and will read the sys_control table.
Alpha Five provides a method, script_schedule(), that lets us schedule a script to run in the future. Our approach will be to check the sys_control table, and if the shutdown_status is -1 or "Run", to schedule another check-in for 5 minutes in the future. If the shutdown_status is anything other than -1, we'll schedule a shutdown for the appropriate time.
Here is the first attempt at check_status:
sys_control=table.open("sys_control",file_ro_shared)
shutdown_status=sys_control.shutdown_status
sys_control.close()
select
case shutdown_status = -1
script_schedule("check_status",\
totime(toseconds(time())+300,2,0),2)
case shutdown_status = 1
script_schedule("shutdown",\
totime(toseconds(time())+61,2,0),2)
case shutdown_status = 5
script_schedule("shutdown",\
totime(toseconds(time())+300,2,0),2)
case shutdown_status = 30
script_schedule("shutdown",\
totime(toseconds(time())+1800,2,0),2)
end select
end
Script 1. The check_status script.
This script will run every 5 minutes as long as the shutdown_status field is set to "run" or -1. If shutdown_status is set to a planned shutdown, then this script will schedule another script, shutdown, to run after the appropriate interval.
Here is the shutdown script:
dim sys_control as p
sys_control=table.open("sys_control",file_ro_shared)
shutdown_status=sys_control.shutdown_status
sys_control.close()
if shutdown_status>0 then
' shutdown in effect
a5.close()
else
script_schedule("check_status",totime(toseconds(time())+300,2,0),2)
end if
Script 2. The shutdown script
The shutdown script will first check to make sure that the shutdown is still in effect. If shutdown has been canceled, then a new instance of the check_status script will be scheduled for 5 minutes in the future. If shutdown has not been canceled, then A5 will exit.
We have seen that as long as check_status is started by each user, it will continue to reschedule itself every 5 minutes until either the user exits A5 or a shutdown alert has been issued. We now have to make sure that each user will start up check_status. We can do that in either of 2 ways:
If you define an autoexec script for a database, that script will run when the user enters the database. Alternatively, in Database Properties you can select a form which will run when a database is started.
There's one user I don't want to kick out of the system, and that's Bill. The scripts we've outlined will only work properly if we have some way to separate Bill from the "ordinary" users. We'll have to make each user log in to our system, and if the user is "Bill" then we will recognize that he has special privileges and should not be kicked off when a shutdown takes place. Since we want to make sure every user logs in, then our "Run on Load" form will be a "User Logon" form:
Figure 3. The User Logon form.
The User Logon form will do double-duty. We will use its OnInit event to launch the first instance of check_status, and then we will use the OnPush script for the OK button to see whether the user is Bill or an "ordinary" user. Here is the OnInit script for User Logon:
Figure 4. The OnInit script for the Logon form launches the first instance of check_status.
The OK button has the job of checking to see whether a supervisor has logged in. It checks the user name and password against a table of names with passwords and privileges. If a supervisor logs in, then the program will immediately display the form that manages the sys_control table, otherwise it will display the first application form:
dim global user as c
dim passtable as p
dim pass as c
dim global privilege as c
dim rec as n
pass=ui_get_password("Your password","Enter password")
passtable=table.open("password",file_ro_shared)
passtable.index_primary_put("user")
rec=passtable.fetch_find(user)
select
case rec>0 .and. passtable.password=pass
privilege=passtable.privilege
if passtable.privilege="supervisor" then
form.view("sys_control")
else
form.view("sales form")
' or whatever is the main application form
end if
parent.close()
case else
a5.close()
end select
Script 3. The OnPush script for the OK button checks user status against a password table.
The structure of the password table is simply:
Figure 5. Structure of the password table.
If Bill logs in, the Logon form will guide him to the sys_control form. The sys_control form simply lets Bill set the shutdown_status field of the single-record sys_control table:
Figure 6. The sys_control form.
The sys_control form has a multistate button which is linked to the shutdown field of sys_control.dbf:
The multistate button sets the value of shutdown.
The Choices for the multistate button reflect the different possible values for shutdown:
Figure 8. The Choices tab for the multistate button
In the Properties for the form, I checked the Enter record restriction so that the sys_control table remains a single-record table:
Figure 9. This form cannot be used to inadvertently create new records.
The check_status script presented in Script 1 does not take any special action if Bill or another supervisor logs in - it will schedule a shutdown for everyone. We promised that our scripts would make an exemption for Bill: after all, the whole point is to allow Bill to work on the database, so we have to make sure he stays logged in! We'll amend the check_status script to check user privileges and also add a little error checking to handle the occasional instance in which 2 users are simultaneously trying to read the sys_control table. We'll also give the user warning of an impending shutdown:
dim global privilege as c
if privilege="supervisor" then
end
end if
on error goto reschedule
sys_control=table.open("sys_control",file_ro_shared)
on error goto 0
shutdown_status=sys_control.shutdown_status
sys_control.close()
constant crlf=chr(13)+chr(10)
select
case shutdown_status = -1
script_schedule("check_status",\
totime(toseconds(time())+300,2,0),2)
case shutdown_status = 1
script_schedule("shutdown",\
totime(toseconds(time())+61,2,0),2)
ui_msg_box("Warning","\
System shutting down in 1 minute."+crlf+\
"Please finish your work.",UI_STOP_SYMBOL)
case shutdown_status = 5
script_schedule("shutdown",\
totime(toseconds(time())+300,2,0),2)
ui_msg_box("Warning","\
System will shut down in 5 minutes."+crlf+\
"Please finish your work.",UI_ATTENTION_SYMBOL)
case shutdown_status = 30
script_schedule("shutdown",\
totime(toseconds(time())+1800,2,0),2)
ui_msg_box("Warning","\
System will shut down in 30 minutes.",UI_INFORMATION_SYMBOL)
end select
end
reschedule:
' sys_control was locked, try again in 1 minute
script_schedule("schedule",\
totime(toseconds(time())+61,2,0),2)
end
Script 4. Revised check_status script.
Remember I began this article by telling you that Steve Workings illustrated a similar scheme at a recent meeting in Maryland? Steve did not use script_schedule(). Rather, Steve used an OnTimer event for a "Main Menu" form. In Steve's application, all users always have the "Main Menu" form open. Steve sets the form's Timer to 60 seconds, and he has an OnTimer script that checks a table to see whether a shutdown has been scheduled.
Figure 10. The Timer property of the form can be set in form design mode. If Timer is set to 0 seconds, then the OnTimer event never fires.
A simple OnTimer script that checks the shutdown_status is:
sys_control=table.open("sys_control",file_ro_shared)
shutdown_status=sys_control.shutdown_status
sys_control.close()
if shutdown_status>0 then
ui_msg_box("Shutdown will occur in",\
alltrim(str(shutdown_status))+ " minutes."+chr(13)+chr(10)+\
"Finish your work.",UI_ATTENTION_SYMBOL)
script_schedule("shutdown",totime(toseconds(time())+(shutdown_status*60),2,0),2)
end if
this.code.OnTimer=""
Script 5. This OnTimer script will schedule a shutdown if the shutdown_status has been set. The line, this.code.OnTimer="", temporarily erases the OnTimer script so that it will not execute again after 1 minute.
Steve says that he has verified that the extra network traffic of checking a table every 60 seconds is negligible. In my example, I chose to check for a shutdown every 5 minutes instead of every 60 seconds, and since I don't keep a "Main Menu" form open at all times, I could not use an OnTimer event. Steve's approach and mine are essentially equivalent, and the choice of method depends on your preference and the structure of your application.
6/28/99
Don't forget, we need your feedback to make this site better!