.. 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