Re: Field naming was ( SQL is amazing)

Reeve I have seen many conventions on Field Naming standards.
What you have shared is IMO a reasonable and good convention.
I had a naming standard but unless you define a standard from the conception of a project the standard is rather pointless.
Many of the ERP systems we used had sort of a  naming convention but nothing rigorous.
One can define standards and rigorously enforce them and coders continue to interpret the standard differently.
Eventually the code evolves into something where people do the least amount of work possible to meet the convention.
So when doing a mass change, although standards help, one cannot trust that the standards were rigorously followed and I resorted to cross references to do the changes.

PS
After doing a massive change to system expanding all currency fields from whatever to 13,3 I adopted a new approach.
NEVER EVER change a column attribute,  I added new columns, maintenance programs and suchlike updated the data in two columns.
That way programs that referenced the old columns but did nothing with the data did not need to change. Only programs needing the changed column were affected.

Frank

On 30/06/2022 9:06 am, Reeve wrote:

Who am I to argue with Rob on anything? 😆

Rob, IMO you're dead wrong WRT both field naming conventions and field
reference files.

Having a prefix *greatly* simplifies debugging and managing data within the
code. When I see a variable "x_". I know it's a program-defined variable
(I'd use the term "working storage" but that would merely evoke another
COBOL-versus-RPG battle). When I see CMCUST, I know it's from table
ARP001, my name and address master; GPDCUST is the account code in my
discounts file; and GPNCUST is the account code from my FAK file. Knowing
what data came from what table is critical when deep into a nasty debugging
session; having data overlaid from other database reads turns a task into a
challenge. Yes, there are times when I have to do a bunch of EVAL's to
move data between tables, but (1) I can use EVAL-CORR and (2) I have a
utility that generates skeleton code (EVAL's, CLEAR's, SQL CRUD operations)
for every column in a file; those tools simplify the task. The utility
includes the column text description; this makes it easy for others to know
what data is being moved and programmers, myself included, don't do that
manually.

I note that prefixing is a capability of RPG and this suggests there's an
acknowledged need to separate and control data with shared column names
across multiple tables. I use PREFIX() in trigger programs to manage
before- and after-update logic and in some funky data conversion programs
but I don't have the need otherwise. My transportation management
application began on the System/38 and the *ab initio* use of a
two-character (now three) prefix appears to have been a decision that's
made green- and brown-field work substantially easier. IMO, it's easier to
know where data is and to get it rather than to have CSTNAM in multiple
files (I'm calling them "files" on purpose). These comments are centered
on program logic and database management; bringing device and
communications files into the discussion provides *more* justification for
a carefully-named and prefixed database.

Field reference files are a must-have for shops with multiple developers.
While I'm not a believer in DBA's, it is important for one person to take
oversight and resolve conflicts in the FRF and across the database
in general. The database is a resource shared across developers and you
don't want one (or more) to foul the nest. Well-defined column definitions
(including mixed-case text and column headings, edit codes/words, and
INZ when necessary) simplify database development and add a great deal of
value to ad-hoc reporting tools.

Is it smart or practical to put every field in an FRF? No...but my own
experience suggests more than 80% of columns across a database will be
referenced in multiple tables and therefore candidates for inclusion in an
FRF. Name, address, city, state/province, country, telephone numbers,
email addresses, units of measure, commodity codes--the list of "standard"
data elements in most businesses is long. An FRF can be a source of
documentation (comments)--but I don't include VALUES in any table
definition. Some businesses are reflecting changing societal values and
VALUES('M' 'F') is no longer adequate for capturing a person's gender
identification.

Changing a column attribute? Change it once in the FRF and use PDM/RDi to
scan for FRF references that trigger CHGPF. Easy.

\reeve

On Wed, Jun 29, 2022 at 2:08 PM Frank Kolmann <Frank.Kolmann@xxxxxxxxx>
wrote:

Hi Rob

I never worked on S34.
Also I never did come up with a good solution for field naming. The
biggest restriction being the 6 byte field size.
Program described field names were a proper dogs breakfast.

To get a comprehensive Field Where Used we used Pathfinder , dont know
if it still exists.
Prior to Pathfinder I had a Field cross reference utility that used
File/Field End position as the sort criteria.
Dug that info from Compile Cross reference listings, wrote a utility to
compile programs into Qtemp then parsed the listing.

Frank


On 29/06/2022 9:49 pm, Rob Berendt wrote:

Do you still do that S/34 RPG stuff where every field name has a 2

character prefix which indicate which table it came from?

Because if you don't and ITEMNUMBER is called the same name in every

table it appears it's extremely simple to get a cross reference of every
table it's used in by simply doing the following

select SYSTEM_COLUMN_NAME, SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
DATA_TYPE, LENGTH, NUMERIC_SCALE, COLUMN_HEADING, COLUMN_TEXT
from qsys2.syscolumns
where system_column_name = 'ITEMNUMBER'
;

Also by avoiding that prefix method it's extremely simple to

scan/replace all source when you change a column attribute.

I find field reference files rather obsolete.

Rob Berendt

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com