MySQL Views DFantastic!

[Aoirthoir]

Ok I finally tested it. Due to the fact that I have all my ID fields named ID in all of my tables...I would have had to do a ton individual field listings. So instead I just added an ID field to my R_ table and made it the primary key. Previously I was using all the relationship fields Pname, Cname, PID, CID and OID as the primary key. Not sure if that was a good idea anyhow. So then I just set

[ID]
Key=PRI

in my view table and it works. It has an add button and I clicked it..it does give me an edit form. But since I only had 3 fields chosen, I opted not to continue with the test. I will do more tomorrow. MySQL views claim that they wont save on joins. I dont know if that is still pertinent. So I thought I would ask it here....

I can say this is going to save a TON of time...I can see the possibility of creating dozens or hundreds of views or even more...for all my reports. ESPECIALLY since it will do all the totalling within MySQL. Now that we have this feature in DF...we have a lot more options.

Mr. Steve, I do not think my company and I are the only ones who appreciate what you have done. MANY many thanks....

So now when is the DF convention?

[Aoirthoir]

Dangit...it gave me an error saying I have to have a primary key...when I tried to click the record. (not an add, just a view.)...

Anyhow I will experiment more tomorrow....i am getting tired now.

[shannah]

What was the error that you got? Did it give a line / file number?

Thanks

Steve

[Aoirthoir]

Just about to start testing...I will let you know as soon as I get it done.

[Aoirthoir]

Well first I wanted to make sure it wasn't the view having an error. So I removed all evidence of the view, out of MySQL and my app dirs. Now I can "Add new record to the relationship" without any problems. If I try to "Add existing Record to the Relationship..." I get the parent view when I click Save. No record is saved. This is in v0.6.8 so I reverted back to v0.6.3r2. I thought I had been able to add existing in that. But it is giving me the same trouble. Instead of bringing up the view however it brings up the related list....

Ok so what is different? Here it is, I added the ID field to my R_ table. So I removed it and everything works fine again. It seems that on "Add a related Record" DF brings up the view instead of the related record list. But the record is added. So the caveat is having that primary key right. When I have an ID field in R_ then the R_.CID is always set to zero. Thus the reason it does not show up in the related list...

Ok here is the setup that works..the only difference is that with ID involved..it doesnt work. This is only about the actual table relationships...going to check out the view in a minute...But I will have to concat or just use all the ID fields and specify them as a Key=PRI in order for them to work. The only reason I did not do so last night, is that since all my Primary keys are named ID, except in R_, it was going to be a lot of AS statements. Also in R_ I have 4 components to the primary key. Not sure how that will hit. Anyhow here are my setups:

SHOW CREATE TABLES:

Code: Select all

CREATE TABLE `D_TASK` (
  `ID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
  `TYPE` enum('Task','Project') NOT NULL default 'Task',
  `DESCRIPTION` varchar(80) default NULL,
  `PRIORITY` enum('URGENT','High','Medium','Low','Whenever','Just a Brain Storm','Nevermind') default NULL,
  `STATUS` enum('Assigned','Under Consideration','Started','Halfway','Almost Done','Completed','On Going','Repeating','Delayed','Requires Outside Help','Postponed','Cancelled') default NULL,
  `ID_USER_CREATED` mediumint(8) unsigned zerofill default NULL,
  `ID_USER_ASSIGNED` mediumint(8) unsigned zerofill default NULL,
  `DATE_PROJECTED_START` date default NULL,
  `DATE_PROJECTED_FINISH` date default NULL,
  `DATE_ACTUAL_START` date default NULL,
  `DATE_ACTUAL_FINISH` date default NULL,
  `COMMENT` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `R_` (
  `PNAME` varchar(10) NOT NULL,
  `CNAME` varchar(10) NOT NULL,
  `PID` mediumint(8) unsigned zerofill NOT NULL,
  `CID` mediumint(8) unsigned zerofill NOT NULL,
  `OID` mediumint(8) unsigned zerofill NOT NULL default '00000001',
  PRIMARY KEY  (`PNAME`,`CNAME`,`PID`,`CID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


And now my app/tables/D_TASK/relationships.ini

Code: Select all

[D_TASK]
action: label = Children
action: description = Other tasks which must be completed before this 
task can be marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND   R_.CNAME='D_TASK'
AND   D_TASK.ID = R_.CID
AND   R_.PID='$ID'"
action: order = 1
[DUMMY_PARENT]
action: label = Parents
action: description = Tasks which cannot be completed before this task is marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND   R_.CNAME='D_TASK'
AND   D_TASK.ID = R_.PID
AND   R_.CID='$ID'"
action: order = 2
actions: addnew = 0
actions: addexisting = 0
[D_NOTE]
action: label = Task Notes
action: description = Notes about this particular task.
__sql__ = "
SELECT * FROM R_, D_NOTE
WHERE R_.PNAME='D_TASK'
AND   R_.CNAME='D_NOTE'
AND   D_NOTE.ID = R_.CID
AND   R_.PID='$ID'"
action: order = 3
[D_SUGGEST]
action: label = Suggestions
action: description = Task specific suggestions.
__sql__ = "
SELECT * FROM R_, D_SUGGEST
WHERE R_.PNAME='D_TASK'
AND   R_.CNAME='D_SUGGEST'
AND   D_SUGGEST.ID = R_.CID
AND   R_.PID='$ID'"
action: order = 4


[shannah]

What is the view definition?
What line/file was the error on in 'view' that you mentioned last night?

[Aoirthoir]

Ok I changed everything back to what I had...so here we go all listed.

SHOW CREATE TABLES:

Code: Select all

CREATE TABLE `D_TASK` (
  `ID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
  `TYPE` enum('Task','Project') NOT NULL default 'Task',
  `DESCRIPTION` varchar(80) default NULL,
  `PRIORITY` enum('URGENT','High','Medium','Low','Whenever','Just a Brain Storm','Nevermind') default NULL,
  `STATUS` enum('Assigned','Under Consideration','Started','Halfway','Almost Done','Completed','On Going','Repeating','Delayed','Requires Outside Help','Postponed','Cancelled') default NULL,
  `ID_USER_CREATED` mediumint(8) unsigned zerofill default NULL,
  `ID_USER_ASSIGNED` mediumint(8) unsigned zerofill default NULL,
  `DATE_PROJECTED_START` date default NULL,
  `DATE_PROJECTED_FINISH` date default NULL,
  `DATE_ACTUAL_START` date default NULL,
  `DATE_ACTUAL_FINISH` date default NULL,
  `COMMENT` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `D_NOTE` (
  `ID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
  `TYPE` enum('Note','Feature Request','Bug Report','Program Code','File Information','Website Bookmark','How To','Complaint','Praise') default NULL,
  `DESCRIPTION` varchar(80) default NULL,
  `COMMENT` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `R_` (
  `ID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
  `PNAME` varchar(10) NOT NULL,
  `CNAME` varchar(10) NOT NULL,
  `PID` mediumint(8) unsigned zerofill NOT NULL,
  `CID` mediumint(8) unsigned zerofill NOT NULL,
  `OID` mediumint(8) unsigned zerofill NOT NULL default '00000001',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin


Then my view....

Code: Select all

CREATE ALGORITHM=UNDEFINED
DEFINER=`someone@someIPaddress`
SQL SECURITY DEFINER VIEW `DV_TASKNOTES` AS
select `R_`.`ID` AS `ID`,
`D_TASK`.`DESCRIPTION` AS `DESCRIPTION`,
`D_NOTE`.`COMMENT` AS `COMMENT`
from
((`D_TASK` join `R_`) join `D_NOTE`)
where ((`R_`.`PNAME` = _latin1'D_TASK')
and (`R_`.`CNAME` = _latin1'D_NOTE')
and (`R_`.`PID` = `D_TASK`.`ID`)
and (`D_NOTE`.`ID` = `R_`.`CID`))


But what I actually typed to store it was:

Code: Select all

CREATE VIEW DV_TASKNOTES (ID, DESCRIPTION, COMMENT) AS
SELECT R_.ID, D_TASK.DESCRIPTION, D_NOTE.COMMENT
FROM D_TASK, R_, D_NOTE
WHERE R_.PNAME = 'D_TASK'
AND R_.CNAME = 'D_NOTE'
AND R_.PID = D_TASK.ID
AND D_NOTE.ID = R_.CID;


Now my conf.ini

Code: Select all

[_database]
host = "stuff"
user = "stuff"
password = "stuff"
name = "stuff"
[_tables]
D_TASK = "Tasks"
D_NOTE = "Notes"
D_SUGGEST = "Suggestions"
DV_TASKNOTES = "V Notes"
S_USER = "Users"
[_auth]
users_table = S_USER
username_column = USERNAME
password_column = PASSWORD


Next my app/tables/DV_TASKNOTES/fields.ini :

Code: Select all

[ID]
Key=PRI


The error I got in v0.6.3r2:

Code: Select all

Found 4 of 4 records in table DV_TASKNOTES
Now Showing 1 of 4
Fatal error: There is no primary key defined on table "DV_TASKNOTES". Please define a primary key.On line 122 of file /myhomedir/mydomaindir/libraries/dataface/dataface-0.6.3r2/Dataface/QueryTool.php in function printStackTrace()
On line 257 of file /myhomedir/mydomaindir/libraries/dataface/dataface-0.6.3r2/Dataface/ResultController.php in function getTitles()
On line 309 of file /myhomedir/mydomaindir/libraries/dataface/dataface-0.6.3r2/Dataface/ResultController.php in function getContentsList()
On line 645 of file /myhomedir/mydomaindir/libraries/dataface/dataface-0.6.3r2/Dataface/SkinTool.php in function jumpMenu()
On line 20 of file /myhomedir/mydomaindir/planning/templates_c/dataface/%%2A^2AB^2AB5C15C%%Dataface_Details_Controller.html.p in /myhomedir/mydomaindir/libraries/dataface/dataface-0.6.3r2/Dataface/QueryTool.php on line 126



Finally the error I got in v0.6.8:

Code: Select all

Found 4 of 4 records in table DV_TASKNOTES
Now Showing 1 of 4
Fatal error: There is no primary key defined on table "DV_TASKNOTES". Please define a primary key.On line 123 of file /myhomedirectory/mydomaindirectory/libraries/dataface/dataface-0.6.8/Dataface/QueryTool.php in function printStackTrace()
On line 257 of file /myhomedirectory/mydomaindirectory/libraries/dataface/dataface-0.6.8/Dataface/ResultController.php in function getTitles()
On line 309 of file /myhomedirectory/mydomaindirectory/libraries/dataface/dataface-0.6.8/Dataface/ResultController.php in function getContentsList()
On line 645 of file /myhomedirectory/mydomaindirectory/libraries/dataface/dataface-0.6.8/Dataface/SkinTool.php in function jumpMenu()
On line 20 of file /myhomedirectory/mydomaindirectory/planning/templates_c/dataface/%%2A^2AB^2AB5C15C%%Dataface_Details_Controller.html.php in fu in /myhomedirectory/mydomaindirectory/libraries/dataface/dataface-0.6.8/Dataface/QueryTool.php on line 127



There you go. I am going to try to experiment by just keeping the PRI set to all the fields...will do that later..going to get on the server soon...

[shannah]

Thanks for the output.

I think I have corrected this issue in the attached version of Dataface_Table. Please download this and replace your old Dataface/Table.php file with this one.

I have a feeling that the edit forms and new record forms won't work properly with views. To aid me,

Can you run the query:

show columns from `DV_TASKNOTES`

And give me the output.

Best regards

Steve

[shannah]

OK.. attachments don't seem to be working in the forum... i'll post it in the issue tracker...

[Aoirthoir]

Ok I will load that..here is the info you requested..I just ran the query in a console:

Code: Select all

+-------------+--------------------------------+------+-----+----------+-------+
| Field       | Type                           | Null | Key | Default  | Extra |
+-------------+--------------------------------+------+-----+----------+-------+
| ID          | mediumint(8) unsigned zerofill | NO   |     | 00000000 |       |
| DESCRIPTION | varchar(80)                    | YES  |     | NULL     |       |
| COMMENT     | text                           | YES  |     | NULL     |       |
+-------------+--------------------------------+------+-----+----------+-------+


As to adds and edits not working...I read documentation that said MySQL doesnt support modifying in JOINed views. But a view from a single table can be added or modified....

[shannah]

Download patch from issue tracker here: http://framework.weblite.ca/development/issue-tracker/34

[shannah]

Dataface may still have issues with editing views from a single table if some of the columns are calculated columns because it may try to set values in these columns. You can probably manually work around this problem by setting widget:type to static or hidden.

Let me know how it goes... I'm hoping to have MySQL 5 installed on my laptop soon so I can actually test some of this stuff out.

-Steve

[Aoirthoir]

Ok well I just tested it..the fix you gave worked fine on both v0.6.3r2 and v0.6.8. Not only that, check this bro, even though the documentationc claimed you can't write to a joined view...DF wrote to it fine... Now it might have issues adding...since some fields are required but wont be in the view..if you dont include them...I will check that out later..also I will experiement with the calculated and set them to static and hidden..but otherwise it worked great now.

Thanks for the help.

[Aoirthoir]

The following msg appears on a multi table view:

Fatal error: Error inserting record: Can not modify more than one base table through a join view


So as the documentation says, Multiple Views cannot be saved. Thus some mechanism needs to be in place to prevent the save/add buttons from appearing in these.