[pmwiki-users] SelectQuery development: database records as pages?

[Pico]

Ben Stallings <Ben <at> InterdependentWeb.com> writes:
> 
> Thank you all for your feedback about the possibility of treating 
> database records as pages.  I gave it some serious thought today... I 
> want this recipe to be as user-friendly as possible (which the current 
> incarnations of SelectQuery and UpdateForm are definitely not), and I 
> think this is the way to go.  I envision somebody being able to install 
> this recipe, point it at a database, and voila, everything else happens 
> automatically.  Here's my modest proposal:
> 
> * The functionality of SelectQuery and UpdateForm will be merged into a 
> single recipe.  I was thinking of calling it DataFace, but I see there's 
> already an open-source project by that name that does pretty much the 
> same thing I have in mind, but without the wiki integration.  If I 
> weren't so invested in the wiki integration, I might consider using it 
> instead... but as it is I'm open to name suggestions!

PmDataFace
PmDataPage
PmDataIn
PmDatIn
PmDbIn
PmDb

PmDatabaseQuery
PmDbQuery
PDQ (1)

PmQueryWiki
PmQwiki
PmQwik
PmQi (2)

(1) PQD: Short for Pm Database Query, where PDQ is a play on Pretty Darn Quick
(2) PmQi: Short for Pm Query Interface, where Qi is a play on the Chinese word
whose meanings include breathing (in and out).  Since Qi is pronounced "Chi" or
"Chee", this spicy little recipe could be pronounced "PimChi" or "PimChee",
which is, itself, a play on the spicy Korean condiment called KimChi (or KimChee)

> 
> * If the tag for the recipe (whatever it turns out to be) is used 
> without parameters -- for example, by putting it in a GroupFooter as you 
> would (:pmcal:) -- it will apply itself to a database table whose name 
> matches the Group.  The Group/Group page will list all records in the 
> table (much like SelectQuery) with a link on each one so that you can 
> view or edit a single record.
> 

Nice and simple.

> * The Group/Search page will show a search form with fields based on the 
> table.  The searching syntax of the PageList script will be imitated as 
> closely as possible, so that users don't need to learn two different 
> searching syntaxes on the same site.

Great.  Note, you should be able to use the search page as the target search
results page that controls the default results when searches are entered in any
search box anywhere in the group. That is not to say that the search page
shouldn't provide its own customized search forms to provide additional
functionality and ease of use for advanced searches, but simply that the search
page can be used as the default search results page for simple search boxes
located elsewhere in the group.
> 
> * Browsing a Group/Record page will display the matching record, if any. 
>   Editing a Group/Record page will produce a form (much like UpdateForm 
> currently does).  The structure of the tables and forms will be based 
> automatically on the structure of the database table unless specified by 
> a template.  Because the records are displayed as if they were pages, 
> viewing & editing privileges should apply, whether by AuthUser or 
> UserAuth or regular wiki passwords.
> 

Great. I assume that we are still talking about two separate levels of
privileges: (1)PmWiki and (2) mySQL (or other db).  Anything that could be done
to simplify and coordinate these two would be helpful.

> * If the tag for the recipe is used with the 'tables' parameter (and 
> 'where' if necessary), the page group can refer to a query of one or 
> more tables rather than on a table directly.  Searching, browsing, and 
> editing records will work just as if the records came from a single 
> table -- similar functionality to a saved query in Microsoft Access.
> 

Excellent!

> * Templates will be in a format as similar as possible to PageList 
> templates (see question #1 below).  In addition to Site/LocalTemplates, 
> the recipe will look in Group/Templates for templates specific to the 
> current table or query.
> 

Yes, that should be great.

> So having said that, I have some more questions for you fine folks:
> 
> 1) There will need to be some way for templates to allow for the 
> differing numbers and names of fields from one table to the next -- a 
> sort of field-loop within the page-loop that PageList executes.  Can 
> templates be nested??  That is, could a template for a list of records 
> somehow include a template for a list of fields?  Or is there a better 
> way to go about it?
> 
Well, right now, templates work with two levels of looping, by group {$Group}
and page ({$Name}, {$FullName}, {$Title} ...) using = (current), < (previous)
and > (next).  Notice that any page can have multiple page variables ({$Name},
{$FullName}, {$Title}, or, generally {$Var}) and that such a relationship (one
to many) is not significantly different from the new page text variables
({$:var}) that Pm is introducing and incorporating into pagelists.  Hopefully
the same looping (+, < and >) can be applied.

Now, the fact that each Table/Group will have a different set of Field/(Page
Text) Variables is a departure from the current system where the universe of
possible Page Variables {$Var} is defined and known, but it will not be a
departure from the new system where the universe of possible Page Text Variables
{$:var}.  

Still, if you want to be able to create boilerplate templates that can be shared
among different Table/Groups, my initial thought (which may be admittedly ugly)
is that you could grab a standardized set of (multiple) Field/PageTextVariables.
    Thus, for example, if you have a contacts table with fields called:

stFirstName
stLastName
stTelephone
...

instead of creating PageTextVariables called:

FirstName:
LastName:
Telephone:

You could create multiple PageTextVariables:

Field1Name:
Field1Value:

Field2Name:
Field2Value:

Field2Name:
Field2Value:

And, optionally, you could also create a Field1Type: to differentiate amount
string, date, number and other types of data.  Better yet, if you append the
number to the end (FieldName1: FieldName2: etc) then you could specify
FieldName* and iterate over the appended integer values.

The purpose of all of that would be to allow generic templates that use the
value of FieldName1:, FieldName2:, FieldName* to populate the header of tables
and FieldValue1:, FieldValue2:, FieldValue*: to populate the subsequent rows
(and FieldType: in a footer, with a conditional, to test for numberic fields and
perform a sum, for example)

If all of this is so obvious, I apologize: it is only a reflection of the fact
that I had not thought this through when I first read your e-mail and imagined
that pages would be populated with simple pairings of:

FirstName: Ben
LastName: Stallings

In fact, that may be what should appear on pages, but the underlying markup used
to display that might use the the intermediary generic fields:

{$:FieldName1}: {$:FieldValue1}
{$:FieldName2}: {$:FieldValue2}

> 2) A single query may need to have as many as four templates associated 
> with it: one for the list of records, one for viewing a single record, 
> one for the editing form, and one for the search form.  Should I break 
> up the 'fmt' parameter into 'listfmt', 'editfmt', etc., or is there a 
> better way?  Perhaps look in Group/Templates for #list, #edit, etc.?
> 

Would you need to use a pagelist type of (looping) template to view, or edit, a
single record?  The edit template could be a modified version of the
Site.EditForm and a similar page template approach could be used for viewing a
single record page.

As with the comments to item 1, it would be nice to be able to reuse the same
generic format on different Table/Groups.  Presumably, the recipe could have an
initialization process that is triggered when a Table/Group is browsed, the
table is read to populate an array of FieldNames (FieldName1, FieldName2...)and
use the results of that array to determine how much of the generic edit form
will be used (if table contains 5 fields, then the recipe generates view/edit
templates that stop after the 5th field).

> 3) If it works as described above, the recipe will pretend that certain 
> pages exist that do not actually exist.  Is there a way to suppress -- 
> or intercept -- the "page does not exist" message?  Or is there a better 
> way to do this, too?  Maybe this ties in with the recent talk of virtual 
> pages?
> 
> 4) If it works as described above, the recipe will produce a 
> record-editing form when the (nonexistent) page corresponding to a 
> record is edited.  What is the best way to intercept action=edit?  Or 
> would I be better off creating a new action for database edits?

If you can use a custom EditForm to accomplish what you need, then you might not
need to intercept action=edit.  If....
> 
> 5) Where should I look to learn more about creating a PageStore object? 
>   I'm having trouble finding the documentation.
> 

In general, you might monitor some of the (many) threads dealing with FastData,
but hopefully Pm will just reply with more information.  If not, just post
another email asking for an explanation of PageStore.

> Thanks again for your insight!!  --Ben
> 


Thank you!

Pico