Jump to content
Sign in to follow this  
dennis-fedco

How acceptable is it to use DB metadata in your queries?

Recommended Posts

I have a feature in my Web Application where I make a copy of an business object.  Business object is stored in the database.  The way I make a copy is a little generic and I wanted to check if I am doing it right. 

 

Namely, I use Database metadata to help me instead of using actual column names from a table.  I have functionality in my DB where I use SQL statements like so:

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name' AND
TABLE_NAME = 'table_name' AND
COLUMN_NAME NOT IN ('id', 'db_table_foreign_key');

it essentially selects column names from a particular table belonging to the business object, minus the keys.  All I need to do to copy another table is to change the DB and table names in the above SQL.

 

Later I use another SQL to do the actual new record creation:

INSERT INTO db_name.table_name SET ... -- uses PHP code to essentially copy over things, like:
   {'column_name' = $value, ...} -- $value is from another SQL (not shown) that loads up values to copy
   foreign_key = $new_foreign_key;

Where I use PHP code that uses variables from the first SQL (and actual values from another SQL which is not shown) to essentially create a new table with values copied over from old table to new table, creates new keys, and foreign keys.

 

My question is about "am I doing this right"?  I have many tables and in a way, taking a cheap way out, doing this same generic code basically to copy over several tables, instead of copying every table by using actual SQL with actual table names for each separate table. 

 

I am concerned that going to information_schema.COLUMNS is problematic, even though it saves me lots of work when tables change colum names/ new names are added, etc.

 

I'll appreciate any insight and experience on if my approach is reasonably industry-practied...

 

 

Share this post


Link to post
Share on other sites

My first reaction is "Why are you creating so many tables?", followed by "Why doesn't your application know the table structures already?"

Share this post


Link to post
Share on other sites

my answer .... 6 tables.  Not that many.  Why - to store data related to my application.

 

I am not sure what you mean by my application already knowing the table structure.

It is using the tables, so it is aware of them.

Share this post


Link to post
Share on other sites

I was saying that because of:

 

Namely, I use Database metadata to help me instead of using actual column names from a table.

(your application doesn't know the columns in the table so it has to query the metadata for them)

 

All I need to do to copy another table is to change the DB and table names in the above SQL.

(you say you're copying tables)

 

Where I use PHP code that uses variables from the first SQL (and actual values from another SQL which is not shown) to essentially create a new table with values copied over from old table to new table, creates new keys, and foreign keys.

(creating tables)

 

I have many tables and in a way, taking a cheap way out, doing this same generic code basically to copy over several tables, instead of copying every table by using actual SQL with actual table names for each separate table.

("many tables" and you're copying them around)

 

I am concerned that going to information_schema.COLUMNS is problematic, even though it saves me lots of work when tables change colum names/ new names are added, etc.

(tables are changing schema on a regular basis)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.