sql parser problem

[raz3k]

Hello,

I recently updated librariandb from version 0.2 309 to 0.3.2 2530, after i added readded conf.ini and files under tables/books to fit my needs. The reason was php 4 -> php 5.3.6 and mysql 4.x -> mysql 5 .

./tables/books/valuelists.ini contains:

Code: Select all

[book_categories]
__sql__ = "select category_id, category_name from books_categories order by category_name"

[book_media]
__sql__ = "select medium_id, medium_name from books_media order by medium_name"

[users]
__sql__ = "select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username from postfix.mailbox, condica.Personal where (LOWER(CONCAT(Personal.prenume,'.',Personal.nume))= LOWER(SUBSTRING_INDEX(mailbox.username,'@',1)) and mailbox.username != '\.') order by username"



./tables/books/fields.ini contains:

Code: Select all

[borrower_id]
widget:type=select
vocabulary=users
---


Problem:

I don't have any problem logging in(the sql parses ok), but clicking edit on a book i get the error below.

It seems like there's some kind of problem with the dataface mysql connector that the old version did not have regarding Otherdb.othertable.
..btw virtual_lib is the db name of librariandb.

Code: Select all

Fatal error: Error performing mysql query to get column information from table 'Personal'. The mysql error returned was : 'Table 'virtual_lib.Personal' doesn't exist'.
On line 547 of file /arhive/www/internal/library/dataface/Dataface/Table.php in function printStackTrace()
On line 472 of file /arhive/www/internal/library/dataface/Dataface/Table.php in function Dataface_Table(Personal,Resource id #13,)
On line 470 of file /arhive/www/internal/library/dataface/Dataface/Relationship.php in function loadTable(Personal,Resource id #13)
On line 377 of file /arhive/www/internal/library/dataface/Dataface/Relationship.php in function _normalizeColumns()
On line 109 of file /arhive/www/internal/library/dataface/Dataface/Relationship.php in function _init(array(select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username fro)
On line 3425 of file /arhive/www/internal/library/dataface/Dataface/Table.php in function Dataface_Relationship(books,users__valuelist,array(select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username fro)
On line 208 of file /arhive/www/internal/library/dataface/Dataface/ValuelistTool.php in function addRelationship(users__valuelist,array(select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username fro)
On line 45 of file /arhive/www/internal/library/dataface/Dataface/FormTool/select.php in function asRelationship(Dataface_Table Object,users)
On line 426 of file /arhive/www/internal/library/dataface/Dataface/FormTool.php in function buildWidget(Dataface_Record Object,array(borrower_id,int(11),YES,,,,books,array(Borrower id,,books.borrower_id.label,books.borrower_id.,Dataface_QuickForm Object,borrower_id,)
On line 347 of file /arhive/www/internal/library/dataface/Dataface/QuickForm.php in function buildWidget(Dataface_Record Object,array(borrower_id,int(11),YES,,,,books,array(Borrower id,,books.borrower_id.label,books.borrower_id.,Dataface_QuickForm Object,borrower_id,,array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,)
On line 488 of file /arhive/www/internal/library/dataface/Dataface/QuickForm.php in function _buildWidget(array(borrower_id,int(11),YES,,,,books,array(Borrower id,,books.borrower_id.label,books.borrower_id.,array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,)
On line 68 of file /arhive/www/internal/library/dataface/actions/edit.php in function _build()
On line 1309 of file /arhive/www/internal/library/dataface/Dataface/Application.php in function handle(array(array(Edit,some-url)
On line 1756 of file /arhive/www/internal/library/dataface/Dataface/Application.php in function handleRequest()
On line 22 of file /arhive/www/internal/library/index.php in function display()
in /arhive/www/internal/library/dataface/Dataface/Table.php on line 547


Can you help?

Thanks.

[shannah]

Looks like you don't have a table called "Personal" in your database.

[raz3k]

[users]
__sql__ = "select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username from postfix.mailbox, condica.Personal where (LOWER(CONCAT(Personal.prenume,'.',Personal.nume))= LOWER(SUBSTRING_INDEX(mailbox.username,'@',1)) and mailbox.username != '\.') order by username"

I have a database called Personal and a table called Personal_ID, but instead of Personal.Personal_ID it searches for virtual_lib.Personal .

[shannah]

Code: Select all

select Personal.Personal_ID from ...


This SQL statement says:
"Select the 'Personal_ID' column from the 'Personal' table in the current database".

You would need to do something like:

Code: Select all

select Personal.Personal_ID.username from ...


to specify a column in another database.

That said I'm not certain if the parse will handle this sort of multi-database query.

What I generally do with an application that needs to access information from other databases is create a view in the main database that includes data from the other databases. That way, at the application level, everything is accessible as a single database.

-Steve

[raz3k]

Very odd, this sql query worked like a charm before the update, i will try to rewrite it and post back.

Thanks.

[shannah]

Personal_ID is a very strange name for a table. Are you sure that this is not actually a column, and Personal is not actually a table? The SQL query suggests this... otherwise there's no way it ever ran correctly.