Reservationssystem

deutsch english français Print

9.3 RESERVATION SYSTEM

INTRODUCTION

Nowadays, reserving seats on planes and at concert halls is done almost exclusively through online booking systems. The information is managed by online databases. Database specialists with great programming skills are responsible for the development of these systems.

PROGRAMMING CONCEPTS: Multi-user system, access conflict

RESERVATION SYSTEM PART 1: USER INTERFACE

An attractive user interface is used to present the reservation system to the user. A user interface can be generated with a standalone program or as a web site. It is typical that the outline of the room or the aircraft is shown where the seat layout is displayed. Seats that are already reserved will be marked with a special color, for example the free seats could be green and the already reserved seats could be red.

The user can select a seating option by clicking on an empty seat. The seat then changes color, let's say to yellow. This selection is not yet transferred to the database server because often the user wants to reserve multiple seats at one time. The booking process is carried out only once they click the confirm button.

The game library JGameGrid is ideal for implementing the graphical user interface because the seats are usually arranged in a grid-like structure. The sprite identifier (0, 1, 2) can be used to save the current states (available, option, reserved).

In your example, you model a small concert or theater room with only 30 seats, numbered from 1 to 30. You embed them into the grid shown adjacently.
Use the class GGButton for the two buttons. In order to receive a notification of the button clicks, you need to define a separate button class MyButton that is derived from GGButton as from GGButtonListener (multiple inheritance). The method buttonPressed() is called when a button is pressed. You can find out which button was used using the parameter button.

You have already implemented a fully functional user interface with this short program. It is of course still missing a connecting to the database [more... If you do not understand something, look at the examples in the chapter on OOP & Games] .

from gamegrid import *

def toLoc(seat):
    i = ((seat - 1) % 6) + 1
    k = ((seat - 1) // 6) + 2
    return Location(i, k)

def toSeatNb(loc):
    if loc.x < 1 or loc.x > 6 or loc.y < 2 or loc.y > 6:
       return None
    i = loc.x - 1
    k = loc.y - 2
    seatNb = k * 6 + i + 1
    return seatNb

class MyButton(GGButton, GGButtonListener):
    def __init__(self, imagePath):
        GGButton.__init__(self, imagePath)
        self.addButtonListener(self)
        
    def buttonClicked(self, button):
        if button == confirmBtn:
            confirm()
        if button == renewBtn:
            renew()
            
    def buttonPressed(self, button):
        pass

    def buttonReleased(self, button):
        pass
           
def renew():
    setStatusText("View refreshed")

def confirm():
    for seatNb in range(1, 31):
       if seats[seatNb - 1].getIdVisible() ==  1:
           seats[seatNb - 1].show(2)
           refresh()
    setStatusText("Reservation successful")

def pressCallback(e):
    loc = toLocation(e.getX(), e.getY())
    seatNb = toSeatNb(loc)
    if seatNb == None:
        return
    seatActor = seats[seatNb - 1]
    if seatActor.getIdVisible() == 0:  # free
        seatActor.show(1) # option
        refresh()
    elif seatActor.getIdVisible() == 1:  # option
       seatActor.show(0) # free
       refresh()

makeGameGrid(8, 8, 40, None, "sprites/stage.gif", False, 
             mousePressed = pressCallback)
addStatusBar(30)
setTitle("Seat Reservation")
setStatusText("Please select free seats and press 'Confirm'")
confirmBtn = MyButton("sprites/btn_confirm.gif")
renewBtn = MyButton("sprites/btn_renew.gif")
addActor(confirmBtn, Location(1, 7))
addActor(renewBtn, Location(6, 7))
seats = []
for seatNb in range(1, 31):
    seatLoc = toLoc(seatNb)
    seatActor = Actor("sprites/seat.gif", 3)
    seats.append(seatActor)
    addActor(seatActor, seatLoc)
    addActor(TextActor(str(seatNb)), seatLoc)
show()

Highlight program code (Ctrl+C copy, Ctrl+V paste)

MEMO

The conversion of seat numbers to locations and vice versa is done in two transformation functions. The names of such mapping functions are often prefixed with to.

RESERVATION SYSTEM PART 2: CONNECTING TO THE DATABASE

Online databases are multi-user systems. Because many clients manipulate the data at the same time, serious access conflicts can occur depending on the situation. It is in the nature of such problems to occur only sporadically, and they are therefore difficult to master. The following scenario describes a typical conflict case:

Customer A and Customer B make a reservation at the same time on a reservation system. At the beginning, A and B inquire about the current availability of the database just shortly one after the other. Both end up receiving the same information about the availability of seats (green = available, red = already reserved seats).

A and B can select the chosen seats as an option with a mouse click. The opted seats are then colored yellow. However, these options are not sent to the database since the customer might still want to change something or add more options. Only after some time, the faster determined customer A clicks on the confirm button to make their choice final. The database puts the seats in the state reserved (booked = 'Y'). However, customer B does not realize these changes, since the database cannot directly give feedback to customer B, and customer A is of course not in direct contact with B [more... In a client-server environment, the server can not send messages to the client by itself]. After a certain amount of time, customer B also decides and presses the confirm button.

Just like Murphy said, "If anything can go wrong, it goes wrong" customers A and B have now chosen the same seats. What happens now? Are the seats assigned to customer B or will B's program even crash?

There is a simple solution to this access conflict: When a customer provides their options to the database, the database needs to be asked once again (just shortly before) if the seats are still actually free. If they are not, the database has no other choice than to politely notify the customer that the seats have already been assigned in the meantime [more... Thus, the problem is only transferred from the of minutes to milliseconds,
because it could still within the query and update the database two users get in the way.
In order to avoid this, the tables must be blocked during the two operations
for the exclusive use by a single client (table lock)]

Note: The execution of this program requires you to restart the database server, create the table reservation, and the table must be filled with initialization data (see previous chapter).

from dbapi import *
from gamegrid import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

def toLoc(seat):
    i = ((seat - 1) % 6) + 1
    k = ((seat - 1) // 6) + 2
    return Location(i, k)

def toSeatNb(loc):
    if loc.x < 1 or loc.x > 6 or loc.y < 2 or loc.y > 6:
       return None
    i = loc.x - 1
    k = loc.y - 2
    seatNb = k * 6 + i + 1
    return seatNb

def pressCallback(e):
    loc = toLocation(e.getX(), e.getY())
    seatNb = toSeatNb(loc)
    if seatNb == None:
        return
    seatActor = seats[seatNb - 1]
    if seatActor.getIdVisible() == 0:  # free
        seatActor.show(1) # option
        refresh()
    elif seatActor.getIdVisible() == 1:  # option
       seatActor.show(0) # free
       refresh()

class MyButton(GGButton, GGButtonListener):
    def __init__(self, imagePath):
        GGButton.__init__(self, imagePath)
        self.addButtonListener(self)
        
    def buttonClicked(self, button):
        if button == confirmBtn:
            confirm()
        if button == renewBtn:
            renew()
 
    def buttonPressed(self, button):
        pass

    def buttonReleased(self, button):
        pass
 
def renew():
    with connect(serverURL, dbname, username, password) as con:    
        cursor = con.cursor()
        sql = "SELECT * FROM " + tbl
        cursor.execute(sql)
        con.commit()
        result = cursor.fetchall()
        for record in result:
            seatNb = record[0]
            isBooked = (record[1] != 'N')
            if isBooked:
                seats[seatNb - 1].show(2)
            else:
                seats[seatNb - 1].show(0)
        refresh()
        setStatusText("View refreshed")

def confirm():
    # check if seats is still available
    with connect(serverURL, dbname, username, password) as con:    
        cursor = con.cursor()
        for seatNb in range(1, 31):
           if seats[seatNb - 1].getIdVisible() == 1:
               sql = "SELECT * FROM "  + tbl + "  WHERE seat=" + str(seatNb)
               cursor.execute(sql)
               result = cursor.fetchall()
               for record in result:
                   if record[1] == 'Y':
                        setStatusText("One of the seats are already taken.")
                        return

        isReserved = False                    
        for seatNb in range(1, 31):
           if seats[seatNb - 1].getIdVisible() == 1:
               sql = "UPDATE "  + tbl + " SET booked='Y' WHERE seat=" + \
                     str(seatNb)
               cursor.execute(sql)
               isReserved = True
           con.commit()
        renew()
        if isReserved:
            setStatusText("Reservation successful")
        else:
            setStatusText("Nothing to do")
        
makeGameGrid(8, 8, 40, None, "sprites/stage.gif", False, 
             mousePressed = pressCallback)
addStatusBar(30)
setTitle("Seat Reservation - Loading...")
confirmBtn = MyButton("sprites/btn_confirm.gif")
renewBtn = MyButton("sprites/btn_renew.gif")
addActor(confirmBtn, Location(1, 7))
addActor(renewBtn, Location(6, 7))
seats = []
for seatNb in range(1, 31):
    seatLoc = toLoc(seatNb)
    seatActor = Actor("sprites/seat.gif", 3)
    seats.append(seatActor)
    addActor(seatActor, seatLoc)
    addActor(TextActor(str(seatNb)), seatLoc)
show()
renew()
setTitle("Seat Reservation - Ready")
setStatusText("Select free seats and press 'Confirm'")
while not isDisposed():
    delay(100)

Highlight program code (Ctrl+C copy, Ctrl+V paste)

MEMO

You can test the reservation system with multiple user windows by repeatedly starting the TigerJython IDE on your computer and executing the same program. If all seats are reserved, you drop the table as shown in the last chapter and create/initialize it again.

EXERCISES

1.	Expand the reservation system so that when you click on Confirm, the customer number is prompted (as an integer) and saved in the database. You can use the function inputInt(prompt, False) to read in, which does not end the program when you press the Cancel button, but rather returns None.
2.	Write an administrator tool that displays the current assignment and a list of the reserved seats, as well as the customer number. In addition, it should be possible to undo a reservation by clicking on the red seats again.