Hello all... please read
I've struggled with this for YEARS
-------------------------------------------------
THE PROBLEM:
-------------------------------------------------
MySQL alone cannot represent a complex data-structure.
Re-modeling of MySQL data is required in Perl, PHP, Java,
etc.
* MySQL needs to have the ability to add properties to
columns and tables.
-------------------------------------------------
Explanation:
-------------------------------------------------
I have numerous MySQL front-end and back-end clients
written in Perl, PHP, and Java.
In each language - I have had to re-model the entire
MySQL data stucture in that programming language.
A PAIN IN THE BUTT.
If I change one column, add one field, play with one
table... I have to go into all of that code in all of
those languages and mess with either how the front-ends
and/or how the back-ends are interpreting the
data-schema.
-------------------------------------------------
OBJECTIVE
-------------------------------------------------
To get MySQL to be the one, central data-source for
nearly everything used in your applications and
data-interfaces.
Not just data...
->Dynamic data.
No more manual SQL statements in your applications.
->Dynamic SQL.
-------------------------------------------------
PICTURE THIS
-------------------------------------------------
bear with me...
-----------------------------------------
EXAMPLE # 1
-----------------------------------------
Removing a single column from one of your MySQL reports
would be as simple as doing this:
* mysql> UPDATE myTable.myColumn SET isVisible=FALSE;
It doesn't matter what programming language the report is
programmed in - that column would no longer show up in
the report.
-----------------------------------------
EXAMPLE # 2
-----------------------------------------
Or change what name is displayed for your column name in
all your MySQL reports?
* mysql> UPDATE myTable.myColumn SET Alias='My Display Name';
That's right! Your column display names can have spaces!
Programming your column names by hand is now optional.
You can add and customize any column or table to have any
property you wish.
-----------------------------------------
OK, BUT WHY ?
-----------------------------------------
You could do the same by just editing the SQL statements
of your Perl, PHP, Java, etc. application, easy...
RIGHT?
* WRONG. *
1.) You've got to go into (for example) your Perl scripts
and change the name of the fields that are being
shown there, the SQL statement itself, and the
variables in Perl that the SQL statement is
returning.
2.) Then go into your Visual Basic application (you know,
the one that all your accounting people use) and edit
that code and SQL. Then you have to re-deploy the
Visual Basic application on all those desktops.
3.) Or how about that PHP script that the sales people
on-the-road use - it has the same SQL report! Gotta
change that, too.
4.) What about that other Java program? 5.) the C++ one?
-----------------------------------------
YOUR POINT ?
-----------------------------------------
* --> MySQL needs to have the ability to add properties to
columns and tables.
The more flexible MySQL becomes, the more flexible your
applications become... and the easier they are to develop
and use.
Instead of SQL code in your applications, automatically
generated SQL.
Instead of editing programming language code... a simple
SQL statement from the MySQL shell could do a lot of
what's needed to alter how your applications interpret
the data schema.
One SQL statement... one minute of time.
-------------------------------------------------
POSSIBLE SOLUTION #1
-------------------------------------------------
--> Add properties/attributes to MySQL columns & tables
For columns...
Currently we have:
+----------------
| Field
| Type
| Null
| Key
| Default
| Extra
+----------------
Are these properties flexible enough?
I do not believe so.
With the new file formats in MySQL 4.x and more complex
data tables such as InnoDB, is it possible to ADD
properties to columns? even add properties to tables?
We could have:
+----------------
| Field
| Type
| Null
| Key
| Default
| Extra
(added)
| Alias
| isVisible
| [more custom]
+----------------
Alias and isVisible are simply custom properties. Any
properties could be added to a column... or to a table.
Properties of columns and tables will use the same
data-types that MySQL currently uses.
-------------------------------------------------
POSSIBLE SOLUTION #2
-------------------------------------------------
--> Two new tables: TABLES and COLUMNS
TABLES
+----------------
| Name
(added)
| Alias
| Description
| [custom fields]
+----------------
COLUMNS
+----------------
| Name
| TableName
(added)
| Alias
| isVisible
| [custom fields]
+----------------
This would enable us nearly all the flexibility we need.
Allowing the properties of both the tables and the
columns to be foreign keys of other tables we use in the
database, and already implement the current MySQL
data-types. It would allow for all the speed we need
with no almost no further coding - using already existing
MySQL indexes and the high speeds of the query parser.
'Name' is the only required field for the TABLES table.
'Name' and 'TableName' are the only required fields for
the COLUMNS table. All other fields are customizable.
I can add any field I want to the COLUMNS table:
COLUMNS
+----------------
| Name
| TableName
(added)
| HTMLFormType
| [custom fields]
+----------------
But is it practical?
Forcing for the existance of every database to contain
two tables named TABLES and COLUMNS ?
This solution is already being used by some.
Some Transparent Persistance implementations in Java,
such as POEM, use this method. But POEM relies on the
programming language of the MySQL client to keep an
up-to-date listing of the TABLES and COLUMNS tables.
If, for example, a table was added manually through the
MySQL shell, those two tables (TABLES and COLUMNS) would
contain old data. Or even worse, if one of the tables
were removed manually, again through the MySQL shell,
there would be an invalid reference to a non-existant
table in TABLES. Perhaps a trigger could be used when
they are available.
The only possible downfall to this approach is that for
any properties we wish to assign to a column - we must
add to all columns. So, even though a property may only
apply to a couple columns in a particular table, it would
be a property for every column in every table, although
it might not need to actually be defined (could be left
NULL).
-------------------------------------------------
Conclusion
-------------------------------------------------
I believe we've all worked our way around this problem
thus far. Using the MySQL client's programming language
to overcome dynamic MySQL schema barriers. However, if
we're all trying to do the same thing... in different
ways... in awkward ways - we're doing it in the wrong
ways.
* There needs to be a standard.
I'm talking about every interface ever devised for MySQL,
not just HTML formatting. After all, it's all about
interfaces, is it not? MySQL holds the data - but the
data means nothing unless it can be viewed, added, and
edited -- hence an interface.
* If we can make MySQL more flexible - we can make our
data interfaces more flexible.
If we can program our interfaces and backends to look at
column properties instead of just our columns - we'll
truly reach a Dynamic database environment! But without
this - it's SO much more work for everyone! A headache
for the programmer, a nightmare to just add one column,
then add it into all that code, keep the new code all in
a versioning system, and then redistribute standalone
client applications. When with dynamic column
properties... all one would have to do is make a column
and (for example) mark it visible.
One SQL statement... One minute of time.
Am I missing something here that all other programmers
are using? Am I embarassing myself as some intermediate
programmer? Perhaps... but in my humble opinion and in
light of what I've been exposed to, I feel there is a
significant need for dynamic schema properties or
something very similar.
Please advise.
I appreciate everyone's time and help... much thanks in advance.
Regards,
-Ryan Hatch
Database Administrator
HiTech Enterprises, Inc.
825 Ontario Road
Green Bay, WI 54311
(920) 465-4600
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
