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

[The Editor]

On 9/28/06, Pico <pmwiki at ben-amotz.com> wrote:
> 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

Just a thought on this.  FAST Data has MANY data manipulation features
available in it already, as well as nice data retrieval capabilities.
I was wondering if there is interest in using FAST Data as a front end
for a database?  It already has functions for GetPage, SetPage, and
GetData, etc.  I imagine it could optionally be set up for database
use just by modifying a few lines of code.  Haven't studied it out,
but it would be nice to give users the chance to store data info
either way in one recipe.  Add a couple different fields and it works
completely different.  And yet keep all the existing functions.  I
will probably add this feature at some point, but why not work
together?

Feedback welcome...

Cheers,
Caveman