What is best practice for referencing data rows within stored procedures – via PK, code column or data value?

Suppose you have a table for colours with columns:

  • id = automatically incrementing integer, primary key
  • code = short code reference for the colour, unique
  • colour = human-readable name of colour, unique

Example values might be:

Now imagine you have a stored procedure that, at some point, needs to reference this table. Let’s say the business logic says to obtain the colour “Green”. To achieve this, you could have any of the following three WHERE clauses:

  • WHERE id = 2
  • WHERE code = GR
  • WHERE colour = Green

Now, if the system is designed such that it is agreed that a code value, once created, never changes, then, in my view, that is the best column to reference because:

  • It is an alternate key
  • It is human-readable for people who maintain the code
  • It will not be impacted when the business decides to change the colour value to ‘Sea Green’

However, if a legacy table lacks such code values, what, in your opinion, is best practice? To reference the id column, or the colour column?

If you reference the id column, the code is not readable unless you then also add comments – you shouldn’t have to comment simple things like this. It sucks figuring out what statements like WHERE id not in (1, 7, 17, 24, 56) mean.

I’m not sure how often, in reality, the id value might change – but consider if you run a script during development to insert new colours but then delete those and insert some more. If your stored procedure references the id values from that last set of colours inserted but when you create your new colours in your next environment you skip the step that inserted the colours which ended up deleted, then the id values won’t match in that next environment. Bad practice, but it can happen – a developer develops their script on a dev instance not thinking that the id values will conflict with production (which, for example, may have had additional colours created manually by the business before your colour creation script runs).

If you reference the colour column, you run the risk that if the business does ask to update the description from ‘Green’ to ‘Sea Green’, that your procedure will begin to fail.

I suppose a further solution is to implement the code column when you need it, if it isn’t there already – probably the best solution?

popup – Best practice for Web Push Notifications Prompt

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

best practice for PUT requests in REST API

What’s the best practice around updating information received from the request?

  1. Update all the columns with the values received in the request object? What if there are columns that should be updated when posted by admin and not by the regular user? We have a lot of such situations.

  2. Should the backend check what attributes are getting a different value from what is saved in the DB?

P.S: We are using Dynamo and AWS API gateway and this is a serverless application

postgresql – Best practice for organizing DDL SQL files

I am developing a postgres database with the following approximate number of entities:

  • 60 tables spread across 7 schemas
  • 20 functions

What’s the best practice for organizing all the DDL SQL?

I currently have a single SQL for the table definitions, another for the views and yet another for the functions. But two of these files have grown to over 1,000 lines each and become unwieldy. That said, there are relationships between tables in different schemas and one file makes these easy to manage.

Would it be better to organize the DDL by schema? Or finer grain still, at the entity level?

I am using JetBrains DataGrip and would appreciate that the solution still enable Intellisense and error checking. The SQL is stored in git.

lvm – Best Practice: Add an Additional Disk to Expand Logical Volume or Expand Existing Disk

We host a client’s Oracle VMs on our Nutanix platform. To date, whenever their VMs require more space they have us add an additional vDisk which they then add to the VG in order to expand the required LV. The reason they’re doing it this way is because they don’t know how to expand a disk and its partitions inside Linux without rebooting the OS.

Of course, it is completely possible to add a disk in Linux and expand the parition, LV and filesystem while the OS is running, and in my opinion, this is the preferred method in terms of keeping things simple and linear. However, I don’t know enough about LVM on a pooled storage backend to justify this from a performance perspective.

So my question is:

How would multiple vDisks for a single LV impact I/O performance for an Oracle DB compared to using a single large vDisk on a virtualisation platform where storage from multiple physical disks are pooled together?

object oriented – 16yo’s Python PacMan – Being more Pythonic, Good Coding Practice, Extension tasks

I have been coding ‘PacMan’ with my 16yo, a hopefully not to boring project to help improve his Python coding. We have just moved the ‘Ghosts’ into a Class which was a good first introduction to objects for him.

My coding is far for perfect, and especially not in Python which was only just being invented when I was learning this stuff, so I am looking for feedback on how to improve the professionalism of his code, how to be more ‘pythonic’, how to enable him to add more features to this project. Clearly he has a long way to go, so looking for minor steps forwards that we can understand and work with rather then a whole sale rewrite please 🙂

Ultimately I would like to get him to implement some search algorithms for the ghosts – (BFS, A* etc), so ensuring the current structure is fit to do that within would be good.

Comments, thoughts, suggestions welcomed.

Code is below, or a zip with the textures etc is here: https://filebin.net/nxe82o408swslmyf

It currently runs, but we have not got lives, ghosts killing pacman, levels, etc coded yet.

import pygame
import os
import time
import math as maths

# Text Positioning

#Pacman Orientation
UP = 10
RIGHT = 11
LEFT = 12
DOWN = 13


PIXEL = 20

# DX and DY for each direction
WEST = (-PIXEL, 0)

YELLOW = (255, 255, 102)
PALEYELLOW = (128, 128, 51)
WHITE = (255, 255, 255)
BLACK = (0, 0, 0)
BLUE = (0, 0, 255)
RED = (255,0,0)


#Global Variables
gameOver = False
win = False
score = 0

#Dictionary mapping between board chars and gif's to display.
char_to_image = {'.' : pygame.transform.scale(pygame.image.load('pellet.gif'), (PIXEL, PIXEL)),
                 '=' : pygame.transform.scale(pygame.image.load('wall-nub.gif'), (PIXEL, PIXEL)), 
                 '=T' : pygame.transform.scale(pygame.image.load('wall-end-b.gif'), (PIXEL, PIXEL)),
                 '=R' : pygame.transform.scale(pygame.image.load('wall-end-l.gif'), (PIXEL, PIXEL)),
                 '=L' : pygame.transform.scale(pygame.image.load('wall-end-r.gif'), (PIXEL, PIXEL)),
                 '=B' : pygame.transform.scale(pygame.image.load('wall-end-t.gif'), (PIXEL, PIXEL)) ,
                 '=TR' : pygame.transform.scale(pygame.image.load('wall-corner-ll.gif'), (PIXEL, PIXEL)),
                 '=TL' : pygame.transform.scale(pygame.image.load('wall-corner-lr.gif'), (PIXEL, PIXEL)),
                 '=BR' : pygame.transform.scale(pygame.image.load('wall-corner-ul.gif'), (PIXEL, PIXEL)),
                 '=BL' : pygame.transform.scale(pygame.image.load('wall-corner-ur.gif'), (PIXEL, PIXEL)),
                 '=TB' : pygame.transform.scale(pygame.image.load('wall-straight-vert.gif'), (PIXEL, PIXEL)),
                 '=RL' : pygame.transform.scale(pygame.image.load('wall-straight-horiz.gif'), (PIXEL, PIXEL)),
                 '=LTR' : pygame.transform.scale(pygame.image.load('wall-t-bottom.gif'), (PIXEL, PIXEL)),
                 '=TRB' : pygame.transform.scale(pygame.image.load('wall-t-left.gif'), (PIXEL, PIXEL)),
                 '=BLT' : pygame.transform.scale(pygame.image.load('wall-t-right.gif'), (PIXEL, PIXEL)),
                 '=RBL' : pygame.transform.scale(pygame.image.load('wall-t-top.gif'), (PIXEL, PIXEL)),
                 '=TRLB' : pygame.transform.scale(pygame.image.load('wall-x.gif'), (PIXEL, PIXEL)),
                 'U' : pygame.transform.scale(pygame.image.load('pacman-u 4.gif'), (PIXEL, PIXEL)),
                 'R' : pygame.transform.scale(pygame.image.load('pacman-r 4.gif'), (PIXEL, PIXEL)),
                 'L' : pygame.transform.scale(pygame.image.load('pacman-l 4.gif'), (PIXEL, PIXEL)),
                 'D' : pygame.transform.scale(pygame.image.load('pacman-d 4.gif'), (PIXEL, PIXEL)),
                 '!P' : pygame.transform.scale(pygame.image.load('Pinky.gif'), (PIXEL, PIXEL)),
                 '!P.' : pygame.transform.scale(pygame.image.load('Pinky.gif'), (PIXEL, PIXEL)),
                 '!B' : pygame.transform.scale(pygame.image.load('Blinky.gif'), (PIXEL, PIXEL)),
                 '!B.' : pygame.transform.scale(pygame.image.load('Blinky.gif'), (PIXEL, PIXEL)),
                 '!I' : pygame.transform.scale(pygame.image.load('Inky.gif'), (PIXEL, PIXEL)),
                 '!I.' : pygame.transform.scale(pygame.image.load('Inky.gif'), (PIXEL, PIXEL)),
                 '!C' : pygame.transform.scale(pygame.image.load('Clyde.gif'), (PIXEL, PIXEL)),
                 '!C.' : pygame.transform.scale(pygame.image.load('Clyde.gif'), (PIXEL, PIXEL)),
#Class stuff
class Ghost:
    def __init__(self, ghostPixelX, ghostPixelY, sprite):
        print("Init " + sprite)
        self.ghostPixelX = ghostPixelX
        self.ghostPixelY = ghostPixelY
        self.sprite = sprite

    def draw(self):
        #print("draw " + self.sprite)
        dis.blit(char_to_image(self.sprite), (self.ghostPixelX, self.ghostPixelY))

    def erase(self):
        #print("erase " + self.sprite)
        # Erase Ghost by drawing black rectangle over it
        pygame.draw.rect(dis, BLACK, (self.ghostPixelX, self.ghostPixelY, PIXEL, PIXEL))
        boardX = int(self.ghostPixelX/PIXEL)
        boardY = int(self.ghostPixelY/PIXEL)
        # If the space contains food, redraw the food
        if "." in board(boardY)(boardX):
            dis.blit(char_to_image("."), (self.ghostPixelX, self.ghostPixelY))

    def move(self, pacManPixelX, pacManPixelY):

        #print("PreMove: " + str(self.sprite) + " " + str(self.ghostPixelX) + " " + str(self.ghostPixelY))

        #if score moves, so does directions
        #Sorts directions to which direction is best to take
        directions = (NORTH, EAST, SOUTH, WEST)
        score = ("","","","") #Which move is best

        #Calculate distance between Ghost and PacMan
        pixelDistanceX = pacManPixelX - self.ghostPixelX
        pixelDistanceY = pacManPixelY - self.ghostPixelY
        pixelDistance = maths.sqrt(pixelDistanceX**2 + pixelDistanceY**2)

        #Calculate distance between Ghost and PacMan after a move in each direction
        for i, direction in enumerate(directions):
            ghostDX, ghostDY = direction
            newGhostPixelX = self.ghostPixelX + ghostDX
            newGhostPixelY = self.ghostPixelY + ghostDY
            newPixelDistanceX = pacManPixelX - newGhostPixelX
            newPixelDistanceY = pacManPixelY - newGhostPixelY
            newPixelDistance = maths.sqrt(newPixelDistanceX**2 + newPixelDistanceY**2)

            #Store how much better (closer) or worse (further away) the move would take the ghost from PacMan
            score(i) = pixelDistance - newPixelDistance

        #Insertion sort O(n)
        #Iterates through the list for the next number to sort (start at pos 1)
        for index in range(1, len(score)):
            currentEntry = score(index)
            currentEntryDir = directions(index)
            position = index

            #Iterates through the list for the number to swap
            while position > 0 and score(position-1) > currentEntry:
                #Copies the lower position into the original position, overwriting it
                score(position) = score(position-1)
                directions(position) = directions(position-1)
                position = position - 1
            #puts the stored value from position, into the final lower position
            score(position) = currentEntry
            directions(position) = currentEntryDir

        # Take the now sorted list of moves, trying each one in turn and take the best move possible
        for direction in reversed(directions):
            ghostDX, ghostDY = direction
            newGhostPixelX = self.ghostPixelX + ghostDX
            newGhostPixelY = self.ghostPixelY + ghostDY

            # Ghosts cant hyperjump
            if newGhostPixelX >= 0 and newGhostPixelX < BOARDPIXELWIDTH and newGhostPixelY >= 0 and newGhostPixelX < BOARDPIXELHEIGHT:
                # Ghosts can't go through walls
                if TestMove(newGhostPixelX, newGhostPixelY, HYPERJUMPNOTALLOWED):
                    self.ghostPixelX = newGhostPixelX
                    self.ghostPixelY = newGhostPixelY

                    #print("PostMove: " + str(self.sprite) + " " + str(self.ghostPixelX) + " " + str(self.ghostPixelY))



# Load Board from a file in current directory
# Boards are text files called "board-X.txt"
def LoadBoard():   
    #ToDo load board from file
    #10 x 10 Board
    board = (('=BR', '=RL', '=RL', '=L', 'O', '.', '=R', '=RL', '=RL', '=BL'),
             ('=TB', '!B.', '.', '.', '.', '.', '.', '.', '!I.', '=TB'),
             ('=TB', '.', '=BR', '=L', '.', '.', '=R', '=BL', '.', '=TB'),
             ('=T', '.', '=T', '.', '.', '.', '.', '=T', '.', '=T'),
             ('.', '.', '.', '.', '.', 'U', '.', '.', '.', 'O'),
             ('O', '.', '.', '.', '.', '.', '.', '.', '.', '.'),
             ('=B', '.', '=B', '.', '.', '.', '.', '=B', '.', '=B'),
             ('=TB', '.', '=TR', '=L', '.', '.', '=R', '=TL', '.', '=TB'),
             ('=TB', '!C.', '.', '.', '.', '.', '.', '.', '!P.', '=TB'),
             ('=TR', '=RL', '=RL', '=L', '.', 'O', '=R', '=RL', '=RL', '=TL'))

    global foodTotal
    global pacManPixelX, pacManPixelY, pacManFacing, pacManDX, pacManDY
    global Pinky, Blinky, Inky, Clyde
    foodTotal = 0
    pacManPixelX = pacManPixelY = pacManDX = pacManDY = 0
    pacManFacing = UP
    #ToDo Load Board Pixel Width and Height here and delete from top of this file
    for boardY, line in enumerate(board):
        for boardX, symbol in enumerate(line):
            if symbol == ".":
                foodTotal +=1 # Count how much food we start with
            elif symbol == "!P." or symbol == "!P": #Which Ghost is it?
                Pinky = Ghost(boardX * PIXEL, boardY * PIXEL, "!P") #Create the ghost!
            elif symbol == "!B." or symbol == "!B": 
                Blinky = Ghost(boardX * PIXEL, boardY * PIXEL, "!B")
            elif symbol == "!I." or symbol == "!I": 
                Inky = Ghost(boardX * PIXEL, boardY * PIXEL, "!I")
            elif symbol == "!C." or symbol == "!C": 
                Clyde = Ghost(boardX * PIXEL, boardY * PIXEL, "!C") 
            elif symbol == "U":
                pacManPixelX = boardX * PIXEL # Get PacMan starting position
                pacManPixelY = boardY * PIXEL
    return board

#Draw Board
def DrawBoard():
    for y, line in enumerate(board):
        # Convert from board PIXEL to real PIXEL
        y *= PIXEL
        for x, symbol in enumerate(line):
            # Convert from board PIXEL to real PIXEL
            x *= PIXEL
            # Convert board chars to gif filename using dictionary
            if symbol != "O":
                dis.blit(char_to_image(symbol), (x, y))

#Test if Character can move to new location
def TestMove(newPixelX, newPixelY, hyperJumpAllowed):

    #TODO This is used for Ghosts and PacMan, Ghosts are not allowed to move in to a square already occupied by a Ghost
    # Pacman is, but then will die
    if newPixelX >= BOARDPIXELWIDTH or newPixelY >= BOARDPIXELHEIGHT or newPixelX < 0 or newPixelY < 0:
        if (hyperJumpAllowed):
            #If move would be a HyperJump, and HypeJumps are allowed then move must be ok
            return True
            #If move would be a HyperJump, and HypeJumps are not allowed then move must not be ok
            return False
    newBoardX = int(newPixelX/PIXEL)
    newBoardY = int(newPixelY/PIXEL)
    #Test if move would end up in a wall    
    if "=" in board(newBoardY)(newBoardX):
        return False
        return True

#Move PacMan to new location, but dont draw the update
def MovePacMan(pixelX, pixelY, dPixelX, dPixelY, facing):

    # Move PacMan
    newPixelX = pixelX + dPixelX
    newPixelY = pixelY + dPixelY

    # Check if move needs to be a HyperJump and if so HyperJump
    if (newPixelX >= BOARDPIXELWIDTH):
        newPixelX = 0
    elif (newPixelX < 0):

    if (newPixelY >= BOARDPIXELHEIGHT):
        newPixelY = 0
    elif (newPixelY < 0):
        newPixelY = BOARDPIXELHEIGHT - PIXEL      
    return newPixelX, newPixelY

def moveGhosts(pacManPixelX, pacManPixelY):
    Pinky.move(pacManPixelX, pacManPixelY)
    Blinky.move(pacManPixelX, pacManPixelY)
    Inky.move(pacManPixelX, pacManPixelY)
    Clyde.move(pacManPixelX, pacManPixelY)

def eraseGhosts():

def ErasePacMan(pixelX, pixelY):
    # Erase PacMan from old position by drawing black rectangle over it
    pygame.draw.rect(dis, BLACK, (pixelX, pixelY, PIXEL, PIXEL))

def drawGhosts():

#Draw PacMan at a new position
def DrawPacMan(pixelX, pixelY, facing):
    # Draw PacMan at new position
    if facing == UP:
        dis.blit(char_to_image('U'), (pixelX, pixelY))
    elif facing == DOWN:
        dis.blit(char_to_image('D'), (pixelX, pixelY))
    elif facing == LEFT:
        dis.blit(char_to_image('L'), (pixelX, pixelY))
    elif facing == RIGHT:
        dis.blit(char_to_image('R'), (pixelX, pixelY))
    # Remove food at new board position
    board(int(pixelY / PIXEL))(int(pixelX / PIXEL)) = "O"

#Play sounds as PacMan eats
def PlaySound(pixelX, pixelY):
    boardX = int(pixelX / PIXEL)
    boardY = int(pixelY / PIXEL)
    #Play sound if new position has food
    if board(boardY)(boardX) == ".":
        # Alternate between two different sounds
        if (boardX + boardY) % 2 == 0:

def message(msg, color, pixelX, pixelY, fontSize, align):
    #Setup font
    font_style = pygame.font.SysFont("bahnschrift", fontSize)
    # Render text ont a surface
    msgRendered = font_style.render(msg, True, color)
    # Get size of surface
    msgPixelWidth, msgPixelHeight = msgRendered.get_size()
    # Change position to draw in relation to align 
    if align == CENTRE_MID:
        pixelX = pixelX - (msgPixelWidth / 2)
        pixelY = pixelY - (msgPixelHeight / 2)
    elif align == CENTRE_TOP:
        pixelX = pixelX - (msgPixelWidth / 2)
    dis.blit(msgRendered, (pixelX, pixelY))

#Main Code

#Setup display and pygame clock
dis = pygame.display.set_mode((BOARDPIXELWIDTH, BOARDPIXELHEIGHT + ( 2 * PIXEL)))
pygame.display.set_caption('Pac-man by ME')
clock = pygame.time.Clock()

#Setup Sounds
if os.path.isfile("1-pellet1.wav") and os.path.isfile("1-pellet2.wav") and os.path.isfile("1-default.wav"):
    sound = True
    food1Sound = pygame.mixer.Sound("1-pellet1.wav")
    food2Sound = pygame.mixer.Sound("1-pellet2.wav")
    defaultSound = pygame.mixer.Sound("1-default.wav")
    print("Warning: Sound files not found, not playing sounds.")
    sound = False
#Load board from file
#ToDo Load random board or different board each level
board = LoadBoard()

#Draw Board
#Game Loop
while not gameOver:
    for event in pygame.event.get():
        #Allows quitting
        if event.type == pygame.QUIT:
            gameOver = True
        if event.type == pygame.KEYDOWN:
                if event.key == pygame.K_LEFT:
                    pacManFacing = LEFT
                    pacManDX, pacManDY = WEST
                    #pacManDX = -PIXEL
                    #pacManDY = 0
                elif event.key == pygame.K_RIGHT:
                    pacManFacing = RIGHT
                    pacManDX, pacManDY = EAST
                    #pacManDX = PIXEL
                    #pacManDY = 0
                elif event.key == pygame.K_UP:
                    pacManFacing = UP
                    pacManDX, pacManDY = NORTH
                    #pacManDY = -PIXEL
                    #pacManDX = 0
                elif event.key == pygame.K_DOWN:
                    pacManFacing = DOWN
                    pacManDX, pacManDY = SOUTH
                    #pacManDY = PIXEL
                    #pacManDX = 0
    #Can we move to new position?
    if TestMove(pacManPixelX + pacManDX, pacManPixelY + pacManDY, HYPERJUMPALLOWED):
        #Erase PacMan
        ErasePacMan(pacManPixelX, pacManPixelY)
        #Calculate new position
        pacManPixelX, pacManPixelY = MovePacMan(pacManPixelX, pacManPixelY, pacManDX, pacManDY, pacManFacing)

        #print("pacManPixelX " + str(pacManPixelX) + " pacManPixelY " + str(pacManPixelY))
        if board(int(pacManPixelY / PIXEL))(int(pacManPixelX / PIXEL)) == ".":
        if sound:
            PlaySound(pacManPixelX, pacManPixelY)
        #Draw the turn and remove food
        DrawPacMan(pacManPixelX, pacManPixelY, pacManFacing)

        #Update the score
        pygame.draw.rect(dis, BLACK, (0, BOARDPIXELHEIGHT, BOARDPIXELWIDTH, PIXEL))
        message(("You're score is " +str(score)), RED, 0, (BOARDPIXELHEIGHT), 15, LEFT_TOP)

    # Ghosts

    #Calculate new Ghost position  
    moveGhosts(pacManPixelX, pacManPixelY)

    #Draw new Ghost positions on the screen   

    #TODO Has the ghost caughtPacMan, if so pacman looses 1 of 3 lives.
    # So need lives system - 3 pacmen bottom right of screen that get 'used up' each time one dies
    # What happens when Pacman dies?  Ghosts get reset, pacman gets reset, score -10 and then carry on?
    # Hint, pac man moves first, so when each ghost moves you can test if it has hit pacman
    #if ghostPixelX == pacManPixelX and ghostPixelY == pacManPixelY:
    #    gameOver = True
    if foodTotal == 0:
        gameOver = True
        win = True
    #Tick the clock

if win == True:
    pygame.draw.rect(dis, YELLOW, (0, 0, BOARDPIXELWIDTH, BOARDPIXELHEIGHT))
    message(("This message will dissapear in 5 seconds"), RED, (BOARDPIXELWIDTH / 2), (BOARDPIXELHEIGHT / 2 + PIXEL), 10, CENTRE_TOP)
    pygame.draw.rect(dis, RED, (0, 0, BOARDPIXELWIDTH, BOARDPIXELHEIGHT))
    message(("This message will dissapear in 5 seconds"), YELLOW, (BOARDPIXELWIDTH / 2), (BOARDPIXELHEIGHT / 2 + PIXEL), 10, CENTRE_TOP)

Thanks very much

technique – Is there a known practice of post-processing to make a finished photo while viewing a subject?

Photographers often aim to create a work that accurately depicts a subject, and/or is informed or inspired by the experience they had looking at it.

A significant part of the work of making a photo is often in post-processing on a computer, rather than in preparing and taking the photo with the a camera.

So I wonder whether there’s any known practice of taking a computer to the subject (or vice versa) and creating the finished photograph while viewing both together. I’m sure that people have done this but my question is whether it’s a practice that has a name and perhaps prominent photographers have talked about doing, or prominent photography commentators have discussed.

Of course for small product photography this is likely to happen incidentally, and for street photography it’s usually impossible, so I’m thinking more about things like landscape, cityscape, and portraiture.

What Sport Do You Currently Practice?