Dataface and information_schema

[Aoirthoir]

information_schema is a built in database following the rules of relational databases that metadata must be available in the same format as regular tables and relationships. Thus you can use regular SQL commands with information_schema to gather information about your entire MySQL setup, such as which databases you have, tables, columns and so forth. In phpMyAdmin I can view the records in the various tables of this metadata. (the table named "tables" gives a list of all tables with one field specifying which database the table belongs to..same for columns with a field specifying what table it belongs to).

Now in phpMyAdmin, I can view these records as I stated but not edit. (This kinda makes sense, because we would edit them by using SQL commands...CREATE TABLE to add a record to the tables table for instance...) But I got to thinking maybe we could if not create and edit those records in Dataface, at least read them. So I set up a dataface test with the following in the conf.ini:

[_database]
host = "blabla"
user = "blabla"
password = "blabla"
name = "information_schema"

[_tables]
TABLES = "TABLES"

And it does kinda work. I get the tabs no problem. But I only get one record displayed. It seems to be a kind of random record as well.

Anyhow this isn't one of those major things or something I'm trying to get support on. Just postin some experimenting I have been doing and the results of it.

[shannah]

This is an interesting idea. I haven't played around with the information_schema at all yet as my main test machines are still running MySQL 4.1 which (AFAIK) doesn't have the information_schema database. It is strange that it would only show 1 table, and at that, a random table. Not sure why this would be happening. I will definitely make a note of this to tinker with, when I upgrade to MySQL 5.

Best regards

Steve

[Aoirthoir]

I think it is strange. Just to clarify it is showing one record of the table entitled TABLES..WHen I tested it with other tables (Columns for instance) i got the same result.

Just something I wanted to tinker with and see the results.

[Aoirthoir]

Ok I think I figured out what the problem may be.

REC_ID...there is no primary key in the meta data tables. I don't know what the deal is there or if there is any way around it...but I think that explains why only one record is being displayed....

Now the brings up a question, currently, if I recall right, dataface requires a primary key (usually auto-numbered but this is not a requirement) in order to display a table. I don't really see any reason for this to change. Except perhaps in the case of information_schema.

My hosting provider is running version 5.0.18

As I said this aint really an issue. I dont even think mysql would allow writes to this thing anyhow. Reads would be nice but they are not critical. Perhaps something for version 1.9 of dataface