Clean Up TX_MACHINE_LOCATION

I delete them.

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth Hawryluk
Sent: Wednesday, June 24, 2015 11:41 AM
To: Gloria Ormsby
Subject: [Tessitura Technical Forum] Clean Up TX_MACHINE_LOCATION

Hey there,

I notice that there is no "inactivate" option for TX_MACHINE_LOCATION. Is it ok to delete old rows here or should this be an ever growing list? How are other orgs keeping this table clean?

Thanks!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

On top of that, even though there’s no referential integrity enforced at a database level, the create_loc field in every table should have its corresponding record in TX_MACHINE_LOCATION.

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

It’s not a huge table, and the records do provide last login information, which might be a reason to hang on to them.

BRIAN WILBUR GRUNDSTROM | Database Administrator
SHAKESPEARE THEATRE COMPANY
Recipient of the 2012 Regional Theatre Tony Award®
516 Eighth Street, SE | Washington, DC 20003-2834
p 202.547.3230 ext. 2216 | c 917.952.7957
bwg@shakespearetheatre.org
www.shakespearetheatre.org
www.brianwilbur.com
2014|2015 SEASON •• Shakespeare’s AS YOU LIKE IT • Shakespeare’s  THE TEMPEST • Ives’ THE METROMANIACS • MAN OF LA MANCHA • Greig’s DUNSINANE • Molière’s TARTUFFE

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
Sent: Thursday, June 25, 2015 11:36 AM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] Clean Up TX_MACHINE_LOCATION

Michele Keutsch:

Just wondering if there is a reason to clean up this table?

Just the usual context of general security related cleanup, e.g. old/unused logins being disabled, e-mail accounts closed, etc.

Hi all,

There is a tx_machine_location_hist table that appears to keep a history of all rows that were ever in tx_machine_location, including deleted tx_machine_location rows. There are delete, insert, and update triggers to maintain the history table. For example, when a row is deleted from tx_machine_location, the end_dt column in the corresponding row in tx_machine_location_hist is updated with the current date/time.

Based on what I’m seeing, it would appear tx_machine_location is supposed to contain only “machines” that are currently authorized while tx_machine_location_hist is where you look for history.

I haven’t done thorough testing of this, but it appears to be working based on a quick review of our own data.

On top of that, even though there’s no referential integrity enforced at a database level, the create_loc field in every table should have its corresponding record in TX_MACHINE_LOCATION.

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

It’s not a huge table, and the records do provide last login information, which might be a reason to hang on to them.

BRIAN WILBUR GRUNDSTROM | Database Administrator
SHAKESPEARE THEATRE COMPANY
Recipient of the 2012 Regional Theatre Tony Award®
516 Eighth Street, SE | Washington, DC 20003-2834
p 202.547.3230 ext. 2216 | c 917.952.7957
bwg@shakespearetheatre.org
www.shakespearetheatre.org
www.brianwilbur.com
2014|2015 SEASON •• Shakespeare’s AS YOU LIKE IT • Shakespeare’s  THE TEMPEST • Ives’ THE METROMANIACS • MAN OF LA MANCHA • Greig’s DUNSINANE • Molière’s TARTUFFE

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
Sent: Thursday, June 25, 2015 11:36 AM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] Clean Up TX_MACHINE_LOCATION

Michele Keutsch:

Just wondering if there is a reason to clean up this table?

Just the usual context of general security related cleanup, e.g. old/unused logins being disabled, e-mail accounts closed, etc.

That makes sense.

Thanks David.

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

Hi all,

There is a tx_machine_location_hist table that appears to keep a history of all rows that were ever in tx_machine_location, including deleted tx_machine_location rows. There are delete, insert, and update triggers to maintain the history table. For example, when a row is deleted from tx_machine_location, the end_dt column in the corresponding row in tx_machine_location_hist is updated with the current date/time.

Based on what I’m seeing, it would appear tx_machine_location is supposed to contain only “machines” that are currently authorized while tx_machine_location_hist is where you look for history.

I haven’t done thorough testing of this, but it appears to be working based on a quick review of our own data.

On top of that, even though there’s no referential integrity enforced at a database level, the create_loc field in every table should have its corresponding record in TX_MACHINE_LOCATION.

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

It’s not a huge table, and the records do provide last login information, which might be a reason to hang on to them.

BRIAN WILBUR GRUNDSTROM | Database Administrator
SHAKESPEARE THEATRE COMPANY
Recipient of the 2012 Regional Theatre Tony Award®
516 Eighth Street, SE | Washington, DC 20003-2834
p 202.547.3230 ext. 2216 | c 917.952.7957
bwg@shakespearetheatre.org
www.shakespearetheatre.org
www.brianwilbur.com
2014|2015 SEASON •• Shakespeare’s AS YOU LIKE IT • Shakespeare’s  THE TEMPEST • Ives’ THE METROMANIACS • MAN OF LA MANCHA • Greig’s DUNSINANE • Molière’s TARTUFFE

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
Sent: Thursday, June 25, 2015 11:36 AM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] Clean Up TX_MACHINE_LOCATION

Michele Keutsch:

Just wondering if there is a reason to clean up this table?

Just the usual context of general security related cleanup, e.g. old/unused logins being disabled, e-mail accounts closed, etc.