[fpc-pascal] The new book: "WEB and database programming with fpc and Lazarus for newbies and professionals"

[Burkhard Carstens]

.. anybody knows where I can buy it? ;-)

I just started my first web application and .. well .. the basics work, 
but I have the feeling, I'm doing it the wrong way.

[Warning: Looong post]

What it's about:
It's a time-punch machine that registers when I'm working on which 
project. Data is stored in postgres tables and shall be reported 
preferably as nice pdf files per customer listing the projects and the 
times spent on them (and off course the money I'd like to have for 
that..)

Unfortunately, I'm new to all related topics.:
The only experience in DB programming is about 10 or 12 years ago 
(writing this time-punch machine as desktop app in Delphi <= 6). Played 
with fpc/laz and postgres a few times since then, but never got 
anything working.
There is some knowledge about XML and the concept of DOM but only basic 
html stuff and absolutely nothing about css and JavaScript, ajax, 
extjs, jQuery, JSON... well, there was nothing when I started a week 
ago.

This is my journey so far: 
(all done with fpc and lazarus from trunk)

chapter 1 - Database programming

First, I created a desktop app to play with the database. All the DB 
components on a DataModule and some DBGrid, DBNavigator on a form.
* First problem: 
Data changes in the grid don't get to the database.
* Solution: 
in SQLQueryTimes.OnAfterPost add:
   SQLQueryTimes.ApplyUpdates;
   SQLTransactionTimes.CommitRetaining;

* Next problem:
When doing "insert, post, edit (the inserted row), post" the data 
doesn't get to DB.
* Found reason: 
The primary key is an auto-increment. It is added by postgres when 
inserting the record, but not reported back. Therefore, the following 
Update statement uses (probably?) "UPDATE .. WHERE times_id = NULL .." 
while times_id actually got a non-NULL value in the DB. So the update 
statement does nothing, not even reporting an error.
* Solution:
none. According to http://bugs.freepascal.org/view.php?id=16076 this is 
not yet implemented.
* Workaround:
Do a "refresh" after the "insert, post". Downside: The cursor is lost, 
i.e. you have to navigate back to the just inserted record before 
further editing.

* Next Problem:
The "projects" are stored in a second table with id, name and some other 
info. Now I'd like to see (and deal with) the projects name instead of 
its ID in the times_grid. Some keywords came to memory: "Master/
Detail", "Lookup" and "JOIN" .. at this point, I decided to order some 
books:
"SQL von Kopf bis Fuss" ("Head first SQL"), a funny book, though a 
matter of taste whether one likes fun when searching technical 
information (I do). Sadly, the examples all refer to mySQL and pretty 
often there is a note like "not all RDBMS understand this syntax".. but 
still, very helpfull book.
"PostgreSQL", pretty dry admin doku ..
"Lazarus", very nice book, covering loads of topics. While it shed quite 
some light into my understanding of the database components, it 
unfortunately ends right there where my quest begins .. at least 
regarding WEB and DB programming .. hence the topic of this post :-)

* Solution:
none, at least not the way I intended. Seems like DBGrid is not (yet) 
able to offer a LookupCombo in a cell like Delphi does. (After I found 
out, I also saw the related bug report .. ) I got something working 
with a separate DBLookupList next to the grid ..

At this point, I stopped dealing with the desktop GUI and went over to 
fpWeb stuff. I just liked the idea of "open dataset, select/insert/
update, close dataset". SELECTing the data using a INNER JOIN statement 
did what I need for displaying ... and editing .. well that's still far 
away, it seems.

chapter 2 - web programming

Here is what I did and learn so far:
* New/Project/CGI Application, save as "cgitimes"
* Add action "main" (code and template mainly taken from the 
"listrecords" cgi example and adapted).
* Add action "switch" 
* "main" produces a page with a status like this:

either
| working on {project name}
| click to pause or call it a day (href=switch?NewProjectId=0)
or
| Inactive
| click to continue with {last_active_project_name}
|  (href=switch?NewProjectId=last_active_project_id)

and a table:
| .. or switch to a different project:
| project_name (href=switch?NewProjectId=1)
| project_name (href=switch?NewProjectId=2)
...

Now when clicking one of the links, I get to the "switch" page. The 
action does to the DB what it should do but I didn't intend to see a 
separate page. I only wanted the action to act on DB and refresh the 
main page.
So I tried to add a http-equiv="Refresh" content="3,URL=main" to 
AResponse.CustomHeaders and went crazy because it didn't work. 
Actually, setting any other header stuff (ContentType, ContentEncoding) 
also didn't work .. at least it didn't show up on "View document 
source" in the browser. ( hey, don't laugh so loud! )
It took me some time to realize that my web module descends from 
TSessionHTTPModule and not from any TxxHTMLModule .. so the headers I 
tried to change were actally HT*TP* headers and not HT*ML* headers..
This confusion was partly due to the fact that I thought, a HTML 
document needs a <head> .. </head> section but none of the templates 
got one. So I assumed, this is added automatically... 
After this enlightenment, I got further. Just adding a "<head> .. 
Refresh .. URL=main ..</head>" via "AResponse.contents.add" did the 
job. Still, this didn't look or feel right. 
Back to the cgi chapter in Lazarus book, I saw the "Location" field in 
the HTTP header and tried setting 
"AResponse.Location:=ARequest.Referer;" in the switch action handler.
Heureka! That did the job! Still I don't know, wheter this is the 
correct way or not and whether I should return a specific HTTP status 
code (201, 205, 302, 303 or 307) ?

Now I want more functionality: edit field to add a note to the current 
time_record on main page, a way to add new projects (probably a 
separate page?), another page to manipulate the times table in a grid  
and of course: reports, reports, reports (Is it possible to use the 
LazReport in a cgi)?

So how to proceed? Should I change my FPWebModule to a THTMLModule?
What happens, when I have multiple T[FPWeb,HTML]Modules in my cgi? Now I 
call it with http://myhost/cgi-bin/cgitimes/main (or even without the 
"main"). With multiple modules, I'd need to select the module like 
http://myhost/cgi-bin/cgitime/MODULE_1/main ?


After installing the lazwebextra package, there are lots of components 
(THTMLxxxProducers, TxxWebDataYY) .. well, precisely, there are 21 
components on the fpWeb page + 5 different DataModules in 
file/new/module ... how are those supposed to gear into each other? 
which components must be placed on what kind of module?


Looking at the fpWeb examples as well as the webdesign package from 
Joost (which installs another DataModule + countless components), I 
realized that both use some JavaScript lib, namely "ExtJS" and 
"jQuery". So it seems, a decent interface can only be done using 
JavaScript. Ok .. I usually have this turned off in my browser but I'm 
willing to change my mind on this.
On a first glance, ExtJS looks somewhat cooler, but it's GPL licensed. 
Does that mean, I need to release the source of my cgi program, if I 
decided to distribute it? While it is irrelevant for this (in-house, 
toy) project, I'd prefer to learn the "right" one, i.e. the one I can 
also use in comercial closed source projects. 

After spending hours on www.w3c.org and www.w3schools.com, extjs.com 
(now sencha.com) and jquery.com reading a lot about html, dhtml, 
javascript and checking out some docs and examples about extjs and 
jquery, I got the feeling that all these pieces could form a nice 
picture .. just I still couldn't see it.
Then I found this artice and now all of the sudden, everything makes a 
lot more sense:
http://www.ibm.com/developerworks/web/library/wa-ajaxintro1.html

Guess, it might be good to start from ground up (now that I found 
ground):
Make some nice html files with some JavaScript functions, which just 
"call" my cgi via XmlHTTPRequest to insert something into the DB or 
return some results or tiny html snippets.


Any hints and suggestions are welcome!


Writing this mail helped me to clean up my mind. I could have deleted it 
after writing, but I thought, it probably helps some other newbie 
and/or maybe motivates someone (Michael, Joost ?) to start writing "the 
new book" ;-)

However, thanks for another great toolset: fpWeb ! It looks really 
promising.

Best regards
 Burkhard