CGI script to edit csv file

Over the last days, this script has become more complex and unreadable but also more powerful.... It is still unlikely that someone ever will understand the code. But for now, it works!

Here are three examples of what it's all about:

Showing a table (note the tooltip)
Editing a row of that table, optionally with column-dependent select-boxes to offer predefined values
Inserting a date via calendar-popup

With this CGI script you are able to line-edit predefined standard csv files over the web, using your web browser. It is not possible to add or delete columns (in other words, the file structure is fixed), but it can be used to add new rows, delete rows, edit cells and sort in ascending or descending order. As an addition feature, two cool javascripts are used to choose a date from a popup calendar (see the noted web references for more details) and to implement tooltips. To declare a field as a date field, simply add @date as the last word in it and then see what happens after clicking edit again... To generate a Link in a cell, use LinkName url @link-notation. To show it bold, add @bold (other gimmicks not mentioned...).

To install the script and make it work:

Download the following script and copy it to some location below your cgi-bin
Optionally download the DatePopup-Javascript-Routines and install them under httproot/jscalendar-1.0 (or if you put them into another location, then you must alter the source ;-)
(again optionally) download and copy the wz_tooltip.js to your webserver-root (or elsewhere, but beware)
create a standard csv file (that is, fields surrounded with Doublequotes and separated by commas), e.g. with Excel or - much better ;-) - with OpenOffice - somewhere below the /cgi-bin, e.g. /cgi-bin/data
point your browser to .../cgi-bin/csvedit.tcl?file=[path/]yourcsvfile (file relative to script path!) (optional args may follow, like sort...)
of course, your webserver must be able to serve CGI scripts coded in tcl... (what about tclhttpd ;-?)
optionally, you can create a csvname.rc-file which holds special configuration options per csv-file. Such a file is shown near the bottom of this document

LV Does the csv file need write permissions for the same user as the web server accessing the file? MHo: I think so. I must admit that I forgot to think about such aspect...

The scripts create a file.bak before each write access and stores deleted and overwritten records to a "transaction"-log named after the csv file.

I implemented a simple todo-list with this script... (that's the original reason I started to wrote it).

 #===============================================================================
 # CSVEdit 0.99 (c) M.Hoffmann, DAK, BitMarck 2006-2009
 # 26.11.2009 - https://wiki.tcl-lang.org/15676
 # ToDo/Bugs:
 # - es fehlt: ein weniger gesprächiger Modus (für Einbindung in IFRAMEs);
 #    Ein weniger Raum-beanspruchender EditModus; dazu direkt auf Klick in
 #    Tabellenzeile reagieren und (nur!) dort ein Editfeld erscheinen lassen!
 # - Prüfen html::getFormInfo
 # - Beim Abschneiden von Text wird nach <numChars> vorgegangen, eigentlich
 #   müsste je nach Font und Pixelbreite vorgegangen werden...
 # - ToolTips für Abgeschnittenes momentan nicht bei ReqDel sichtbar...
 # - Filter für jede Spalte ( ,Zeile)?
 # - CSS für EditElemente (Input, Button) werden vom IE ignoriert...
 # - evtl. Bubble-Help für Vollanzeige benutzen, oder Klick auf Details ganz
 #   rechts zeigt VollText unten etc.
 # - Statt input type=text immer textarea verwenden; Anzeige wahlweise EXPANDen
 #   = nowrap entfernen o.ä.; NOWRAP konfigurierbar machen + WIDTH
 # - Mehrere Sortierfelder (Datum, PRIO) - aber nicht per Interface (geht nicht
 #   von Haus aus mit LSORT, daher komplex)
 # - Bei EDIT POSITIONIEREN; IST DAS MÖGLICH?! # geht nicht in CGI-URLs!? evtl.
 #   über JS:focus (aber: welches Ziel? Ein Anker!)
 # - NOSCRIPT-Auswertung!
 # - LfdNr als eigene Spalte mitführen/nur anzeigen?
 # - Wahlweise später: Insert_Before, _After, MoveUp/Dn etc. - ein ganzes Muster
 #   an Befehlen ist in der rechten Spalte denkbar! (aber nur mit Sequenz-Vor-
 #   haltung)
 # - Reset()-Button?
 # - ALLE Farben konfigurierbar machen
 # - Auch deforder über .rc vorgebbar (geht wg. Parsingreihenfolge nicht...)
 # - Alternatives Editieren im 'FullScreen/Dialog'-Modus
 # - Bug: Zurück-Link in History
 # - Evtl. UNDO/Redo/Restore/RollBack etc. (evtl. über Buttons in History...)
 # - History: nicht alles schreiben, Datei wird eh zu gross zum Editieren
 # - Bei REFRESH gelegentlicher Höhensprung in HeadLine??
 # - DEL über CheckBoxes, ebenso INSBEFORE, INSAFTER
 # - Während des Editierens müssen keine sortierbaren Tabellenköpfe da sein!
 # ------------------------------------------------------------------------------
 # - Löschen MUSS indirekt erfolgen, damit POS gesetzt ist!
 # ------------------------------------------------------------------------------
 # 290806 0.91 - CSS um @page ergänzt;
 # 301006 0.92 - stdout buffering full
 # 161106 0.93 - Dateiname in den Titel!
 # 161106 0.94 - CACHING
 # 161106 0.95 - CACHING komplett entfernt!
 # 201106 0.96 - Fehlermeldung bei falschen Eingabeparametern; kein Default für
 #               file=. Bugfix.
 # 020208 0.97 - Korrekturen aus dem Wiki übernommen, Überarbeitung.
 #             - ToolTipps mit title='...' (siehe pkgState) statt mit ext. JS.
 # 280109 0.98 - TranLog und Confirmations abschaltbar; compact und noReturn
 #                (nur über .rc); ncgi::reset für lokales Testen.
 # 261109 0.99 - Steuerung DateButton über fieldFlags nicht @date (inkomp.)!
 #             - Bugfixes, Typos, Optimierungen, etc.
 #===============================================================================

 package require ncgi;     # tcllib
 package require html;     # tcllib
 package require csv;      # tcllib
 package require http;     # tcl
 package require lock;     # https://wiki.tcl-lang.org/15173 M.Hoffmann
 package require readprof; # https://wiki.tcl-lang.org/12647 M.Hoffmann
 fconfigure stdout -buffering full

 #-------------------------------------------------------------------------------
 # Wo befinden sich die JavaScripts?

 if {[file isdirectory [file join $::env(DOCUMENT_ROOT) js]]} {
    set jsRoot /js
 } else {
    set jsRoot ""
 }

 #-------------------------------------------------------------------------------

 # Einträge für <head>
 proc addToHead {} {
    puts {
       <style type="text/css">
          <!--
          @media screen {
             body   { font-family:Tahoma,"Trebuchet Ms",Verdana,"Lucida Sans Unicode","MS Sans Serif",Arial;
                      font-size:8pt; line-height:10pt;
                      color: #000; margin-left: 1%; margin-right: 1%; margin-top: 1%; }
          }
          @media print  {
             body   { font-family:"Trebuchet Ms",Verdana,Tahoma,"Lucida Sans Unicode","MS Sans Serif",Arial;
                      font-size:7pt; line-height:9pt;
                      color: #000; margin-left: 1%; margin-right: 1%; margin-top: 1%; }
          }
          a:link    { text-decoration:none; font-weight:bold; color:blue; }
          a:visited { text-decoration:none; font-weight:bold; color:purple; }
          a:hover   { text-decoration:none; font-weight:bold; color:white; background-color:blue; }
          a:active  { text-decoration:none; font-weight:bold; color:purple; }
          a:focus   { text-decoration:none; font-weight:bold; color:purple; }
          td        { font-family:Tahoma,"Trebuchet Ms",Verdana,"Lucida Sans Unicode","MS Sans Serif",Arial;
                      font-size:8pt; line-height:10pt;
                      color: #000; margin-left: 1%; margin-right: 1%; margin-top: 1%; }
          th        { font-family:Tahoma,"Trebuchet Ms",Verdana,"Lucida Sans Unicode","MS Sans Serif",Arial;
                      font-size:8pt;  line-height:10pt; font-weight: bold;
                      color: #000; margin-left: 1%; margin-right: 1%; margin-top: 1%; }
          input     { font-size:8pt; background-color:lightpink; font-family:Courier; }
                    /* IE 6 erkennt die Attribut-Selektoren nicht! */
                    /* daher werden button-styles weiter unten INLINE überschrieben! */
          input[type=submit], input[type=reset], input[type=button]
                       { font-size:7pt; font-weight:bold; background-color:#DCDCDC; font-family:Verdana,Tahoma; }
          input[type=text] { font-family:Courier; font-size:8pt; background-color:lightpink; }
          select    { font-family:Courier; font-size:8pt; background-color:antiquewhite; }
          @page     { size:landscape; }
          -->
       </style>
    }
    set ::bStyle {font-size:7pt; font-weight:bold; background-color:#DCDCDC; font-family:Verdana,Tahoma;}
    # NoScript-Handling?
    # Siehe http://dynarch.com/mishoo/
    if {$::cmd == "edit"} {
       puts "
         <link rel=\"stylesheet\" type=\"text/css\" media=\"all\" href=\"$::jsRoot/jscalendar-1.0/calendar-tas.css\" title=\"tas\" />
         <script type=\"text/javascript\" src=\"$::jsRoot/jscalendar-1.0/calendar.js\"></script>
         <script type=\"text/javascript\" src=\"$::jsRoot/jscalendar-1.0/lang/calendar-de.js\"></script>
         <script type=\"text/javascript\" src=\"$::jsRoot/jscalendar-1.0/calendar-setup.js\"></script>
       "
    }
 }
 # Prüft, ob ein bestimmter fieldFlag gesetzt ist
 proc checkFlag {ix flag} {
    return [expr {[lsearch $::opts(fieldFlags_$ix) $flag] >= 0}]
 }
 # Baut Argumentliste für Links auf: link?var1=value1&var2=value2...
 proc makeLink {title args} {
    lappend args file $::file sort $::sort order $::order back $::back readonly $::readonly mtime $::mtime filter $::filter
    return "<a href=\"[ncgi::urlStub]?[eval http::formatQuery $args]\">$title</a>"
 }
 # Baut Argumentliste für Links auf: link?var1=value1&var2=value2..., aber OHNE ORDER UND SORT
 proc makeLink2 {title args} {
    lappend args file $::file back $::back readonly $::readonly mtime $::mtime filter $::filter
    return "<a href=\"[ncgi::urlStub]?[eval http::formatQuery $args]\">$title</a>"
 }
 # Setzt globale Variablen aus gleichnamigen CGI-Vars, falls fehlend mit Vorgaben
 # es fehlt: Range-Check!
 proc readVars args {
    foreach {varname default} $args {
       ncgi::setDefaultValue $varname $default
       set ::$varname [ncgi::value $varname]
    }
 }
 # Hidden-Inputfelder erzeugen
 proc makeHidden args {
    foreach {varname value} $args {
       puts "<input type=\"hidden\" name=\"$varname\" value=\"[quote $value]\">"
    }
 }
 # (einige) HTML-Sonderzeichen umsetzen, siehe auch https://wiki.tcl-lang.org/13008
 proc quote in {
    if {[string length [info commands html::html_entities]]} {
       # prüfen, ab wann dieses Kommando zV steht! Auch in pkgTool, csvedit einsetzen!
       return [html::html_entities $in]
    } else {
      return [string map \
         {ä &auml; Ä &Auml; ö &ouml; Ö &Ouml; ü &uuml; Ü &Uuml; ß &szlig;} \
         [html::quoteFormValue $in]]
    }
 }
 # Auf spezielle Schlüsselworte am Ende prüfen, ggF. diese entfernen
 proc checkKeyWord {in keyW} {
    return [expr {[string range $in end-[string length $keyW] end] == " $keyW"}]
 }
 proc checkKeyWordAndRemove {in keyW} {
    if {[checkKeyWord $in $keyW]} {
       return [string range $in 0 end-[string length " $keyW"]]
    } else {
       return $in
    }
 }
 # Special-quoting for tooltips (see http://www.walterzorn.de)
 proc quotett in {
    # Wiki-Tipp: regexp {(.*) @bold$} $in _match in ; # 'in' only updated if the regexp matches
    return [string map \
       {ä &auml; Ä &Auml; ö &ouml; Ö &Ouml; ü &uuml; Ü &Uuml; ß &szlig;
        \\ \\\\ ' \\' &amp; "& amp;" &lt; "& lt;" &gt; "& gt;"} \
        [html::quoteFormValue [checkKeyWordAndRemove $in @bold]]]; # hm...
 }
 # "überlange" Anzeigen b.a.W. abkürzen, später optional expandable! I.d.Z.: kann CSV \n speichern?
 proc shortenCell {ix cellIn} {
    if {$::debug == 1} {
       puts "shortenCell "
    }
    set mW $::opts(maxDisplayWidth_$ix)
    if {$::debug == 1} {
       puts "mw := $mW | actLen := [string length $cellIn]<br>"
    }
    if {[string length $cellIn] > $mW} {
       incr mW -4; # Achtung: bedingt mindestwert > 4!
       set ::truncated 1
       return "[string range $cellIn 0 $mW]..."
    } else {
       return $cellIn
    }
 }
 # ggF. Spezialcodes in Zellen umsetzen, Primitiv-Lösung
 proc formatCell {ix cellIn} {
    set ::truncated 0
    if {[checkKeyWord $cellIn @link]} {
       # name ziel [name ziel [...]] @link
       set temp {}
       foreach {name ref} [lrange $cellIn 0 end-1] {
          append temp "<a href=\"$ref\">[quote [shortenCell $ix $name]]</a> "
       }
       return $temp
    } elseif {[checkFlag $ix isDate]} {
       # ACHTUNG: dieser Code ist abgestimmt auf das 'ifFormat'!!!
       foreach {Y m d} [split [lindex $cellIn 0] .] {}
       set temp "$d.$m.$Y"
       if {[string range $temp 0 1] == ".."} {
          return $cellIn; # Feld ist noch leer
       } elseif {$::opts(isTermin_$ix)} {
          set tmp [clock format [clock seconds] -format {%Y%m%d}]
          if {"$Y$m$d" == "$tmp"} {
             return "<span style=\"color: red;\">$temp</span>"
          } elseif {"$Y$m$d" < "$tmp"} {
             return "<span style=\"color: white; background: red\">$temp</span>"
          } else {
             return $temp; # Feld enthält ein Datum < dem aktuellen
          }
       } else {
         return $temp; # Feld enthält schon ein Datum und soll nicht der TerminMarkierung unterliegen
       }
    } elseif {[checkKeyWord $cellIn @bold]} {
       # double check heer needs to be eleminated
       return "<b>[quote [shortenCell $ix [checkKeyWordAndRemove $cellIn @bold]]]</b>"
    } else {
       return [quote [shortenCell $ix $cellIn]]
    }
 }
 # Javascriptcode für Datumsauswahlbutton erzeugen/stacken, siehe http://dynarch.com/mishoo/
 proc pushJSDate id {
    # durch eigene onSelect-Funktion könnte Feld zeitgleich aktualisiert werden!
    append ::jsCode "
       <script type=\"text/javascript\">
          Calendar.setup({
             inputField : '$id',
             ifFormat   : '%Y.%m.%d',
             showsTime  : false,
             button     : '${id}_b',
             singleClick: true,
             cache      : true,
             showOthers : true,
             step       : 1
          });
       </script>
    \n"
 }
 # Farbe zeilenweise umschalten, Test
 proc toggleColor {} {
    global lineColor
    return [set lineColor [expr {$lineColor == "#fffacd" ? "white" : "#fffacd"}]]
 }
 # Some important security feature: don't allow editing arbitrary files!
 # Prevent escaping from http-root! Paths are always relative to script's
 # [pwd], but may contain dir(s). Don't note error if spec is absolute.
 proc relFile file {
    if {[string range $file 1 1] == ":"} {set file [string replace $file 0 1]}; # MS-Win
    set file [string trimleft $file {./\\}]; # remove problematic chars
    return $file
 }
 # primitive AbortHandler (don't care about open html-tags...)
 proc abort {args} {
    puts "<html><head><title>FEHLER - CSVEdit 0.99</title>\n
     <p><table align=\"center\" bgcolor=\"silver\" border=\"3\" width=\"50%\" \
     cellpadding=\"5\" frame=\"box\" height=\"30%\">\n
     <tr><th align=\"left\" height=\"10%\">Fehler:</th></tr>\n
     <tr><td align=\"left\" valign=\"top\">[join $args]</td></tr>\n
     <tr><td align=\"right\" height=\"10%\">(Programm-Abbruch)\n
     </th></tr></table><p></body></html>"
    exit 1;
 }
 # transactionLog (save overwritten or deleted records and opcode in csv-format)
 # needs to be reimplemented 'cause this ever-growing log couldn't really be edited later
 proc tranLog {file op hdr rec} {
    if {$::opts(tranLog) == 0} {
       return 1
    }
    if {[catch {
       set f [open [file rootname $file].log a]
       if {[tell $f] == 0} {
          puts $f [csv::join [lappend hdr op Zeit]]
       }
       puts $f [csv::join [lappend rec $op [clock format [clock seconds] -format {%d.%m.%Y %H:%M:%S}]]]
       close $f
    } rc]} {
       puts "<font color=\"red\">Fehler beim Schreiben des Transaktions-Logs: $rc"
       puts "<b>Die Operation <b>$op</b> wird verworfen!</b></font>"
       ncgi::setValue cmd ""; set ::cmd ""; # ausnahmsweise hier...
       ncgi::setValue pos ""; set ::pos ""; # GLOBALE Vars setzen!
       return 0
    } else {
       return 1
    }
 }

 #-------------------------------------------------------------------------------

 set lineColor "#fffacd"
 if {![info exists ::env(REQUEST_URI)]} {
    ncgi::reset $::argv; # local call from cmdline for testing purposes, e.g.:
 } else {
    ncgi::reset
    ncgi::header text/html charset utf-8
 }
 ncgi::parse
 # pos zuvor 0
 array set vars {file "" sort -1 cmd "" pos "" order increasing back "" debug 0 readonly 0 mtime 0 filter ""}
 eval readVars [array get vars]
 foreach {v d} [ncgi::nvlist] {
    if {[string match -nocase f* $v] == 0 && [string match -nocase s* $v] == 0} {
       if {[lsearch [array names vars] $v] < 0} {
          abort Ungueltiges Argument `$v`! [ncgi::nvlist]
       }
    }
 }
 if {[string length $file] == 0} {
    abort Argument `file=` fehlt!
 }
 set file [relFile $file]
 if {![file exists $file]} {
    abort Datei `$file` existiert nicht!
 }
 if {[string length $back] == 0} {
    set uri {}; catch {set uri $::env(REQUEST_URI)}
    set ref {}; catch {set ref $::env(HTTP_REFERER)}
    if {$uri != $ref} {
       set back $ref
    }
 }
 set jsCode {}
 set hdr  {}
 set rows {}
 if {[catch {
    set f [open $file r]
    set hdr ""
    # Satz könnte durch \n unterbrochen sein -> Modif. aus Wiki integriert
    set record ""
    while {[gets $f line] > -1} {
       if {[csv::iscomplete [append record $line]]} {
          if {[string length $hdr] == 0} {
             set hdr [csv::split $record]
             set cols [llength $hdr]
          } else {
             lappend rows [csv::split $record]
          }
          set record ""
       }
    }
    close $f
 } rc]} then {
    # ok, hier sind nun doppelte HTML-Header im Abbruch... egal!
    abort $rc
 }
 # Profile-Handling:
 set prf [file rootname $file].rc
 set defopts {}
 for {set i 0} {$i < [llength $hdr]} {incr i} {
    lappend defopts default_$i {}
    lappend defopts select_$i {}
    lappend defopts fieldFlags_$i {}
    lappend defopts maxDisplayWidth_$i 10; # mindestens erforderlich für Datum!
    # isTermin und isHidden kann später auch in die fieldFlags! (inkompatibel!)
    lappend defopts isTermin_$i 0; # wenn true, wird dieses Datum bei Überschreiten rot markiert
    lappend defopts isHidden_$i 0; # wenn true, wird diese Spalte übersprungen
 }
 lappend defopts defsort $sort readonly $readonly tranLog 1 confirmation1 1 compact 0 noReturn 0
 array set opts [readprof::readprof1 $prf $defopts]; unset defopts
 if {$sort == -1} {
    set sort $opts(defsort)
 }
 set rows [lsort -dictionary -$order -index $sort $rows]; # vor Edit/Repl, damit 'pos' stimmt!
 for {set i 0} {$i < [llength $hdr]} {incr i} {
    if {$opts(default_$i) == "@date"} {
       set opts(default_$i) [clock format [clock seconds] -format {%Y.%m.%d}]; # Achtung: Format muss passen!
    }
    if {$opts(maxDisplayWidth_$i) < 10} {
       set opts(maxDisplayWidth_$i) 10
    }
 }
 if {$debug == 1} {
    foreach x [array names opts] {puts "$x := $opts($x)<br>"}; # DEBUG <<<<<<<<<<
 }
 puts "<html><head><title>[file tail $file] - CSVEdit 0.99</title>"
 addToHead
 puts {</head><body>}
 if {$opts(compact) == 0} {
    puts "Tabelle: <b>$file</b> "
    if {[string length $cmd]} {puts "Modus: <b>$cmd</b>"}
    puts {<div align="right">}
    puts [makeLink Refresh]
    if {$opts(noReturn) == 0} {
       if {[string length $back]} {puts " <a href=\"$back\">Zur&uuml;ck</a>"}
    }
    puts {</div>}
    puts {<hr><p>}
 }
 set saveFile 0
 if {$readonly == 0} {
    if {($cmd == "save" || $cmd == "add" || $cmd == "del!" || $cmd == "edit" || $cmd == "reqdel") && $mtime != [file mtime $file]} {
       puts "<font color=\"red\">Operation abgebrochen, Datei wurde zwischenzeitlich ge&auml;ndert!"
       puts "Bitte die Operation <b>wiederholen!</b></font>"
       ncgi::setValue cmd ""; set cmd ""
       ncgi::setValue pos ""; set pos ""
    } elseif {$cmd == "save"} {
       # wird gerufen über SUBMIT-Button nach Feld-Änderungen
       for {set i 0} {$i < $cols} {incr i} {lappend newRow [ncgi::value f$i]}
       if {[tranLog $file $cmd $hdr [lindex $rows $pos]]} {
          lset rows $pos $newRow
          set rows [lsort -dictionary -$order -index $sort $rows]
          set saveFile 1
       }
    } elseif {$cmd == "add"} {
       for {set i 0} {$i < [llength $hdr]} {incr i} {
          lappend newRow [join $opts(default_$i)]
       }
       lappend rows $newRow
       set rows [lsort -dictionary -$order -index $sort $rows]
       set saveFile 1
    } elseif {$cmd == "del!"} {
       if {[tranLog $file $cmd $hdr [lindex $rows $pos]]} {
          set rows [lreplace $rows $pos $pos]
          set saveFile 1
       }
    }
    # Fehler hier noch auswerten!
    if {$saveFile} {
       ######## schlecht: CSV wird SORTIERT abgespeichert, ist das ein Problem?
       ## ggF. Satznummer mitführen, und immer vor Speichern nach Satz# sortieren
       if {[catch {lock::withLock {
          file copy -force $file $file.bak
          set f [open $file w]
          puts $f [csv::join $hdr]
          foreach row $rows {
             puts $f [csv::join [string map {\n \\n} $row]]
          }
          close $f
          set mtime [file mtime $file]
       } 5000 [file join $::env(temp) _gentable.lock]} rc]} {
          puts "<font color=\"red\">Timeout beim Schreibzugriff: $rc<br>Editkonflikt!?</font>"
       }
       # Kommando zurücksetzen!! nützt nichts für Browser URL, Back!!!
       ncgi::setValue cmd ""; set cmd ""
       ncgi::setValue pos ""; set pos ""
    }
 }
 set mtime [file mtime $file]
 puts "<form name=\"frm1\" action=\"[ncgi::urlStub]\" method=\"post\""
 # puts { onreset="return confirm('Wirklich alle Eingaben zurücksetzen?');"}
 if {$opts(confirmation1)} {
    puts { onsubmit="return confirm('Operation durchführen?');">}
 }
 puts {<table border="1"><colgroup>}
 foreach h $hdr {puts -nonewline {<col>}}
 puts {<col>}
 puts {</colgroup><thead><tr bgcolor="silver" align="left" nowrap>}
 set i 0
 foreach h $hdr {
    if {!$opts(isHidden_$i)} {
       if {$i == $sort} {puts {<th nowrap bgcolor="#DCDCDC">}} else {puts {<th nowrap>}}
       # IE kann die dicken Pfeile &dArr; und &uArr; nicht!
       puts "[makeLink2 &darr\; sort $i order increasing] $h [makeLink2 &uarr\; sort $i order decreasing]</th>"
    }
    incr i
 }
 if {$readonly == 0} {
    puts "<th align=\"center\" nowrap><input type=\"submit\" style=\"$bStyle\" value=\"add\" name=\"cmd\" /></th></tr>"
 } else {
    puts {</tr>}
 }
 puts {</thead><tbody>}
 set rowIx 0
 set focus ""
 foreach row $rows {
    if {([string length $::filter] == 0) || ([string match -nocase *$::filter* $row] == 1)} {
       puts "<tr bgcolor=\"[toggleColor]\" id=\"z$rowIx\">"
       set cellIx 0
       if {$cmd == "edit" && $pos == $rowIx && $readonly == 0} {
          foreach cell $row {
             if {!$opts(isHidden_$cellIx)} {
                puts "<td nowrap><input type=\"text\" name=\"f$cellIx\" id=\"f$cellIx\" value=\"[quote $cell]\"
                      size=\"$opts(maxDisplayWidth_$cellIx)\" />"
                if {[checkFlag $cellIx isDate] || $opts(isTermin_$cellIx)} {
                   puts "<button type=\"reset\" style=\"$bStyle\" id=\"f${cellIx}_b\">...</button>"
                   pushJSDate f$cellIx
                }
                if {[llength $opts(select_$cellIx)]} {
                   puts "<select size=\"1\" name=\"s$cellIx\"
                      onCHange=\"this.form.f$cellIx.value = this.form.s$cellIx.options\[this.form.s$cellIx.selectedIndex\].value;\">"
                   puts "<option>[quote $cell]</option>"
                   # vor 0.99 stand hier ein join; warum?
                   foreach o $opts(select_$cellIx) {
                      puts "<option>$o</option>"
                   }
                   puts {</select>}
                }
                puts </td>
                if {$focus == ""} {
                   append ::jsCode "
                      <script type=\"text/javascript\">
                         document.frm1.f$cellIx.focus();
                      </script>
                   \n"
                   set focus done
                }
             }
             incr cellIx
          }
          puts "<td nowrap>[makeLink cancel] <input type=\"submit\" style=\"$bStyle\" value=\"save\" name=\"cmd\" /></td>"
       } elseif {$cmd == "reqdel" && $pos == $rowIx && $readonly == 0} {
          foreach cell $row {
             if {!$opts(isHidden_$cellIx)} {
                if {![string length $cell]} {
                   puts {<td>&nbsp;</td>} ; # dafür sorgen, daß der Rahmen bei leeren Felder erscheint
                } else {
                   puts "<td nowrap><strike>[formatCell $cellIx $cell]</strike></td>"
                }
             }
             incr cellIx
          }
          puts "<td nowrap>[makeLink cancel] <input type=\"submit\" value=\"del!\" name=\"cmd\" /></td>"
       } else {
          foreach cell $row {
             if {!$opts(isHidden_$cellIx)} {
                if {![string length $cell]} {
                   puts {<td>&nbsp;</td>} ; # dafür sorgen, daß der Rahmen bei leeren Felder erscheint
                } else {
                   set buffer [formatCell $cellIx $cell]
                   puts [expr {$::truncated == 1 ? "<td nowrap title='[quotett $cell]'>" : "<td nowrap>"}]
                   puts "$buffer</td>"
                }
             }
             incr cellIx
          }
          if {$readonly == 0} {
             puts "<td nowrap>[makeLink edit cmd edit pos $rowIx] [makeLink del cmd reqdel pos $rowIx]</td>"
          }
       }
       puts "</tr>"
    }
    incr rowIx
 }
 makeHidden sort $sort file $file pos $pos order $order back $back readonly $readonly mtime $mtime
 puts {</tbody></table>}
 puts "<p>Filter: <input type=\"text\" size=\"40\" name=\"filter\" id=\"filter\" value=\"[quote $filter]\" style=\"background-color:white;\"/>"
 puts "<input type=\"submit\" style=\"$bStyle\" name=\"cmd\" value=\"Setzen\" />"
 puts {</form>}
 puts "<p>Letzte Dateiänderung: <b>[clock format $mtime -format {%d.%m.%Y %H:%M:%S}]</b>,"
 puts " <b>[llength $rows]</b> Zeile(n)"
 if {$::opts(tranLog) == 1} {
    puts "<a href=\"[ncgi::urlStub]?file=[file rootname $::file].log\">History/Journal</a>"
 }
 if {$opts(compact) == 0} {
    puts {<p><hr><div align="right">}
 } else {
    puts "Tabelle: <b>$file</b> "
    if {[string length $cmd]} {puts "Modus: <b>$cmd</b>"}
 }
 puts [makeLink Refresh]
 if {$opts(noReturn) == 0} {
    # macht kein Sinn bei IFRAMEs (vielleicht automatisch unterscheidbar???)
    if {[string length $back]} {
       puts " <a href=\"$back\">Zur&uuml;ck</a>"
    }
 }
 puts {</div>}
 puts $jsCode
 puts {</body></html>}
 exit 0

Here's an example .rc-file; with such file it is possible to control the layout and behaviour of the table beyond the possibilities to include some control-codes directly in the fields of the CSV-file:

 defsort 6
 default_2 @date
 default_4 HMK
 default_5 3
 default_6 offen @date
 default_8 neu
 select_1 FEHLER Klärung Betrieb NetInstall Kommunizieren Testen Projekt Doku Idee Entwicklung Kunde Orga Meeting Persönlich Überwachen Prüfen/Sichten
 select_4 DAK HMK HEK
 select_5 1 2 3
 select_8 neu inArbeit wartet ausgesetzt QS Abnahme AV-Transfer Produktion erledigt
 maxDisplayWidth_0 55
 isTermin_6 1
 # Soll/IST-Zeiterfassung ausgeblendet, da eh nicht gepflegt
 isHidden_10 1
 isHidden_11 1
 tranLog 1
 confirmation1 1
 noReturn 0
 compact 0

What can be specified is a thing that needs to be documented in detail....

Bugs:

The table does not print very well with Mozilla, the reason is unknown; perhaps I forgot to generate some <tag>s...
M$-IE 6 does not (yet) respect certain CSS styles for buttons and entry fields (at least the 5 or so Windows systems I've tested on...)
Sometimes klicking the browser's refresh-buttons leads to a warning regarding Post-data.... There must be something with my chaotical parameter-handling (due to the mix and match of post and get-operations)

ToDo's and Ideas:

code consolidation (rewrite everything based on the given functionality) (puuuuhh.......!), e.g.: make use of html::-functions, comment everything, structure the code, removing duplicate code sequences etc. etc...
reduce excessive use of global variables, modularize the code (but keeping the ideas behind the chaos...)
Implement alternate input dialogs and tableformats (big input fields, wrapping etc.)
it would be nice to have a sqlite version of this script MHo: or a metakit version... yes, but the primary goal was to use CSV files to keep them editable by Excel, OpenOffice etc. I know the performance is slow ;-) Sometimes I will design a new stand alone/CGI ToDo-List-Application from scratch, if I ever find the time...

History:

31.03.2006: Added code to handle simultaneous access from multiple users
31.03.2006: Mapping \n to \\n before writing to avoid destroying the file structure
31.03.2006: Added tooltips
01.04.2006: Extended the logging to store records before beeing overwritten and to store timestamps and optypes in the log; the log gets a header and is itself a csv-file
25.04.2006: Many updates, impossible to remember them all. Corrections due to lack of IE 6 CSS-Handling.
18.05.2006: v0.8: Possibility to hide columns from beeing displayed and others...
19.05.2006: v0.9: 1) JS Code for DateInput only included in EditMode (speed things up); 2) ToolTips where values are truncated (trailing '...'), not statically in column 0; 3) Color for column defined as isTermin red if date == actual date, red background if date exceeded; 4) output én block in preparing a cache mechanism (done half the way) by overloading puts
29.08.2006: v0.91: Added @page to CSS
30.10.2006: v0.92: stdout -buffering full
16.11.2006: v0.93: filename apperas in browser title
16.11.2006: v0.94: modifications with regard to caching
16.11.2006: v0.95: remove caching modifications; they are confusing and caching is not of much sense here?
20.11.2006: v0.96: Errorchecking for args; no default entry for file=. Internal bugfix.
02.02.2008: v0.97: Tipps discussed below incorporated. External Javascript for tooltips entirely removed and replaced by title='...'-Tag. Some other improvements. Script now handles reading newlines in input fields, but for now those newlines where removed when the file is written out...
28.01.2009: v0.98: Some tweaking, see below.

JMN 2007-10-25 This code is a nice starting point that has saved me some time - thanks. There are a few problems related to the fact that the code attempts to treat arbitrary field data as a Tcl list. I'd suggest using regexp, or wrapping your 'lindex $cell end' calls in a catch when testing for @date @bold etc. e.g - here is an updated quotett:

 proc quotett in {
    regexp {(.*) @bold$} $in _match in ;#'in' only updated if the regexp matches
    return [string map \
       {ä &auml; Ä &Auml; ö &ouml; Ö &Ouml; ü &uuml; Ü &Uuml; ß &szlig;
        \\ \\\\ ' \\' &amp; "& amp;" &lt; "& lt;" &gt; "& gt;"} \
        [html::quoteFormValue $in]]
 }

Also - csvedit crashed on data where fields had embedded newlines

MHo: I don't think it's legal to have newlines in CSVs...?

JMN Line breaks are perfectly reasonable (and I believe not uncommon in CSV fields) when enclosed in double-quotes.

Try replacing the csv::split while loop with something like this:

    set partial ""
    while {[gets $f rec] > -1} {
            if {[csv::iscomplete $partial$rec]} {
                lappend rows [csv::split $partial$rec]
                set partial ""
            } else {
                    set partial $partial$rec
            }
    }

jmn 2007-11-03 - or.. perhaps clearer/slightly more efficient:

 set record ""
 while {[gets $f line] > -1} {
     if {[csv::iscomplete [append record $line]]} {
         lappend rows [csv::split $record]
         set record ""
     }
 }

MHo 2007-10-25: Many thanks for your detailed enhancements. I'll merge them in as soon as possible! Additionally I have to say that the code presented here is not the latest code available. On my harddisk I have a 0.96 available... So I better first refresh everything and then add your code. Thanks again!

MHo 2007-11-02: Unfortunally, somtimes after editing this page (and modifiying a lot), I got a gateway 502 error.... The code is now updated to the latest available. The suggestions from above are not incorporated, yet...

MHo 2008-02-02: Incorporation of the tips from above and other improvements, see history.

MHo 2009-01-28: Added the following keywords to .rc files:

tranLog (if 1, a csv-log-file with all operations is maintained, this is the default. 0 to turn off)
confirmation1 (if set to 0, suppresses the confirmation questions while deleting or adding rows. Attention! 1 is default = ON)
noReturn (1 suppresses generation of a Link to go to the previous page; makes sens if table is included via IFRAME. Default is 0)
compact (if 1, don't write some lines etc. to save space within IFRAMEs. Default is 0 = write all infos as with previous versions)

Furthermore:

if called from command line, initialize cgi data from argv (for testing purposes)
reorder some codeblocks (not well tested...)

MHo 2009-11-26: Fixed a bug regarding to date fields. Attention: datefields are handled in an incompatible way.

using @date in .rc as a default field value only fills in the actual date; to have the button to call the calendar when editing a record, the new keyword fieldFlags has to be specified with a value containing isDate.
In future releases, more attributes (like the one now specified with it's own keyword isTermin) will be moved to values of fieldFlags.

arjen - 2012-11-15 13:53:48

arjen (15 november 2012) The history function lets you revert a page to a previous version quite painlessly. So it was a matter of checking the correct version and pressing a few buttons to restore it to its previous glory.

Category TclHttpd

Category Application