Any user is allowed to insert, update, and delete the string_contents table.
This means that a user can alter another user's strings. This may be impossible
in practice, because the web application will not allow it, but it would be
better if the database, not the application code, enforced this restriction.

Tables that have this problem are:
  * string_contents (already mentioned above)
  * all history tables (should be modifiable by triggers only, not directly by
    users)
  * imodels (has no permission checking, maybe needs something like
    imodels_users)
  * lentities_users and gentities_users don't check insert operations (this
    mustn't be very hard; could allow inserts only when [lg]entity id does
    not already exist in [lg]entities [with deferred constraint])

In addition:
  * Anyone can update sequences.

Reassigning to nobody.

INSERT INTO vpeople(id, first_names_en, last_name_en) VALUES
(nextval('lentities_id'), 'Antonios', 'Christofides');

This query results in rule ri_vpeople being executed, which first inserts to
people, then to lentities. However, before inserting to lentities, it
re-evaluates nextval('lentities_id') (and the insertion is denied by trigger
ti_lentities because of integrity violation). Could this be a PostgreSQL bug?
The same happens if the query is formed as

INSERT INTO vpeople(id, first_names_en, last_name_en)
    SELECT nextval('lentities_id'), 'Antonios', 'Christofides';

The workaround is, of course, to first get the id, then do the insert. Beware of
a race condition if you do it like this:

SELECT nextval('lentities_id');
INSERT INTO vpeople(id, first_names_en, last_name_en)
    SELECT last_value, 'Antonios', 'Christofides' FROM lentities_id;

You don't know that someone else hasn't updated lentities_id again between the
two queries. Better get the result of the first query into a variable.

Reassigning to nobody.

Field users.emailaddr, which was added by bug 11, is not checked for correct
email syntax.

Reassigning to nobody.

Station data web form:

For user convenience it is better for coordinates to be entered in a form like
deg - min - secs instead of deg with decimal digits form. In addition, some 
radio boxes are desirable so as to enter hemisphers (North/South or 
East/West). Proposed default values of North/East.

In addition, when station data are showed (e.g. 
http://openmeteo.itia.ntua.gr/wk/dbmn/station?id=2 ) the coordinates must 
appear with the "ddd mm ss" form

Reassigning to nobody, improving title, moving to correct component.

Except for the fact that there are no installation instructions, there isn't an
installation checklist either. For example, it is easy for the person installing
to forget to create directory /var/www/downloads (which caused bug 22).

Another thing to note is that either PostgreSQL 7.3 or later must be used, or
7.2 must be patched so that the definition of REWRITE_INVOKE_MAX in
src/backend/rewrite/rewriteHandler.c is 100 rather than 10.

The procedure for installing the database is roughly:   a) Install PostgreSQL
8.0      At some point it must be made to work with Unicode. At this time     
I don't know when exactly and how we ensure that (mine worked in      Unicode
by default and I haven't researched it further).   b) Make sure PL/PgSQL is
installed:      createlang plpgsql template1   c) Run the omdb.sql script:     
psql template1      \i omdb.sql      If it fails claiming that template1 is in
use, attempting to run      it a second or third time sometimes works. 

I'm writing down several random notes (ignore previous comment, which is
unreadable and anyway I have no recollection of writing it).

The procedure is roughly the following:

1. Extract from CVS:
   cd /usr/local
   export CVSROOT=:pserver:anonymous@openmeteo.itia.ntua.gr:/var/lib/cvs
   cvs login (use empty password)
   cvs checkout openmeteo
2. Install PostgreSQL 8.0.
3. Run the database creation script.
   cd /usr/local/openmeteo/database
   psql template1
   \i omdb.sql
   quit
4. Compile and install "timeseries":
   cd /usr/local/openmeteo/timeseries
   make
   make test
   make install
5. Correct /usr/local/openmeteo/dbmn/config.py and create directory
   /var/www/downloads (need to explain in more detail what these are).
6. Install apache and webkit and configure them so that the software in
   /usr/local/openmeteo/dbmn works properly.
...
18. Configure your automatic temporary directory cleanup program (such as
    tmpreaper) to clean /var/www/downloads as well.

Additional notes:
  * Also need to install Cheetah templates, pyPgSQL, Python eginix mx base.
  * The user who connects to the database must be a database super user
    such as postgres.
  * User guest must exist in the database.
  * Pl/PgSQL may need to be installed. Chapter 34 of PgSQL manual, but usually a
       createlang plpgsql template1
    is enough.

Also check that various programs are installed (head, tail, unix2dos, and
others, need to check source code to find them all).

Also create a symlink, /usr/local/bin/python, to the python actually being used
(which, I believe, must be at least 2.3).

If I forget to log in and try to do an operation such as "upload data", nothing
reminds me that I have not logged in, until the denial at the very end of the
data insert/update/upload procedure. The best way to do it would be to request a
login whenever a permissions problem is encountered, and then continue the
operation (more or less as bugzilla does it).

Charts of time series would be very nice.

The "timeseries" program can now produce charts, but the web interface does not
yet make any use of the new feature.

Charts were temporarily deactivated (but you can tweak the Makefile to get them
back) because the openmeteo server has trouble compiling them; libgdcharts
library is not included in that version of the operating system. Will compile
with Debian Sarge.

Our wiki pages are really ugly. We need a new template. Some research on
existing twiki templates and skins must be done before
choosing/customizing/creating the one we want.

We need a logo. I believe I have some ideas by Alkisti somewhere; when I find
them  I'll post them. In the meantime, other ideas are welcome.

Right now, the data downloaded from openmeteo.org are licensed for personal
use.
We need to find or create a suitable license. After determining the license, we
must

(a) find out how to maintain it. For example, when licensing software under the
GPL, we normally license with version 2 or any later GPL version published by
the FSF. This is very convenient, provided we trust the FSF for GPL's
maintenance, otherwise, if we needed to change the license, we'd have to find
_all_ copyright holders and get them to agree (there may be tens of copyright
holders in a piece of free software).

(b) Get NTUA to agree to license its data under the license.

Licenses that must be considered are the GPL, the FDL, the Design Science
License (http://www.gnu.org/licenses/dsl.html) and the Creative Common licenses
(http://creativecommons.org/licenses/).

After some research, I have the following results:

1) The FDL is out. It is too text-oriented, complex, and does not contain
disclaimer of warranty and limitation of liability.

2) I'd rather not use the Design Science License, because it is copyrighted by
an individual rather than an organisation, and it is not supported, maintained,
or popular. It is better to stick with either the FSF or the Creative Commons.

3) The GPL is not absolutely unsuitable, but it is very
computer-program-oriented and it is not clear whether data measurements (or a
derivative work, such as a weather movie) have a source form and an object form.
They most probably don't, which results in many reduntant clauses.

4) The Creative Commons licenses seem to be the best candidate. They are clear,
complete, easy to read (considering they are legal documents) and cover our
needs. There are two CC candidates: the simple, "Attribution" CC license, and
the Attribution-ShareAlike license. The latter contains the additional
restriction that derivative works must be distributed with the same license. It
is not yet clear to me whether we want this or not.

An additional problem is that copyright laws have not been written with such
data in mind. It is not even clear that data measurements can be copyrighted,
let alone what constitutes a derivate work. However, even if data measurements
(or, more exactly, collections of data measurements) cannot be copyrighted at
present, copyright laws are likely to change in order to also make provision for
that (because this might give the incentive for private companies to build
meteorological stations in order to sell the data). We will thus pretend that
the data are copyrighted and license them under a CC license; if then some
lawyer decides they are not copyrighted, that's fine, the license doesn't apply,
but the data are free anyway, which is what we care about.

So: Let's choose a Creative Commons license. I'll think a bit more about what
might be a derivative work, and the consequences of requiring or not requiring
that it also be free, and we choose between Attribution and Attribution-Sharealike.

I thought a little bit more and I've reached what I consider a final
decision, and this is the Creative Commons Attribution-ShareAlike
license. Here's the rationale:

I give my data for free. Some other guy has a measuring station but
does not give his data for free; he's selling them. And let's also
suppose that he mixes his data with my data, thus creating a
"derivative work" (say, for a simplistic example, surface rainfall).
He then sells his derivative work, reserving all rights. I think this
is undesirable, for the same reason many people prefer the GPL to the
BSD. I thus prefer the "ShareAlike" licenses, which would force him to
either make his derivative work free, or to ask me, the copyright
holder, to license the data to him under a different license.

(In any case, I repeat that all this is high speculative, because of
uncertainty of how to interpret copyright laws).

In addition, things are not so complex as for software, where there
can be tens or hundreds of people and organisations collectively
owning the copyright for a piece of software. In meteorological data,
normally only a few entities (usually one) will one the copyright, so
it is easy to ask the owner for another license. Finally, if anyone is
disatisfied with the chosen CC attribution-sharealike license, we
might enhance the database/software so that the copyright holder of
each timeseries can choose from a number of licenses.

The timeseries program can compile with or without chart functionality; this is
useful because chart functionality can be difficult to compile. In order to
leave charts out, the following must be changed in the Makefile:

  * -D NO_CHARTS   must be added to CXXFLAGS
  * LIBS = -lgdc   must be removed or commented out
  * gdcpp.o        must e removed from "objects"

and I may be forgetting something. Obviously this is inconvenient. We need to
use autoconf/automake, or, until we get there, to have a simple true/false
variable in the Makefile that will take care of the rest.

If the idea of being hosted by NTUA doesn't go very well, we need to find
something else. We need to know the costs and features of commercial server
hosting, as well as any idea for an organisation that could offer to host our
server.

man pg_dumpall warns that it does not dump large objects. I think it
means the old large objects of PostgreSQL and not the TEXT and BYTEA
fields. But this must be checked.

In any case, a test restore from the tape has to be done.

1. I try to add a new timeseries, but make an error, such as insert a
non-numeric value for the time step.
2. I click 'submit query'.

Result: The system complains about the error, but what I had inserted in
variable, unit of measurement, instrument, time step unit, strict, and timestamp
interpretation has been lost.

Expected result: All these fields should be remembered.

User-Agent:       Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET
CLR 1.1.4322)
Build Identifier: 

thelw W/m2

Reproducible: Always
Steps to Reproduce:
1.
2.
3.

The timeseries functions should be a shared library rather than a
self-contained
program.

A Python library interface should be created rather than using popen2 to run
the
timeseries program.

dbmnutils.py contains reusable code and is used by both dbmn and autoupdate
(using a symlink). It must become a module with documentation, and probably with
exhaustive unit testing (it contains two critical functions: timeseries_file2db
and timeseries_dbupdate).

When running the database creation script on my system, I frequently
have what appear to be timing issues when, at the beginning, the
script drops and creates the openmeteo database.

If the database does not already exist, usually I get the following:
psql:omdb.sql:19: ERROR:  database "openmeteo" does not exist
psql:omdb.sql:21: ERROR:  source database "template1" is being accessed by other users

If it exists, sometimes when the script attempts to drop it I get the
message that "openmeteo" is in use.

In both cases, trying to run the script a second or third time may
work.

Line 2214 of the current version of the database creation script
contains the string 'micromol/m2'. I wanted to write this with a Greek
mu instead of the word 'micro', but vim said this caused a conversion
error on save. I don't know why, I've been using vim in UTF-8 for
ages. There is also the degree symbol above said line, which is also
non-ascii and must be checked.

vzorbas@dblab.ntua.gr reports:

-  Created a db user "vzorbas" from the web page
http://meteonet.chi.civil.ntua.gr/wk/dbmn/edituser
-  Created a test station "Test" from the web page
- Tried to delete the test station "Test":
  1. Connected to db as superuser "postgres"
  2. Run  " delete from vstations; "
      UNSUCCESSFULL: "ERROR:  User <NULL> is not permitted to DELETE on
stations for id=3"
  3. Changed the session user:   SET SESSION AUTHORIZATION "vzorbas"
  4. Rerun " delete from vstations; "  SUCCESSFULL
  5. I supposed that all the tables: stations, gpoints, gentities sould
now be empty BUT NO (# IS THIS A BUG? #)
     Checked:  stations table=Empty, gpoints table: 1row - deleted it,
                     gentities table: 1 row.

vzorbas@dblab.ntua.gr reports:

When I tried to run "delete from gentities" got the following error:
     " ERROR:  column "name" of relation "lentities_history" does not exist
CONTEXT:  SQL statement "INSERT INTO lentities_history(_operation, id,
name, remarks, country, state, address) VALUES ('D',  $1 , NULL, NULL,
NULL, NULL, NULL)"
PL/pgSQL function "fd_gentities" line 2 at SQL statement"

Run the following statement

 Create or replace FUNCTION fd_gentities() RETURNS trigger AS '
 BEGIN
  INSERT INTO lentities_history(_operation, id, remarks)
      VALUES (''D'', OLD.id, NULL);
  DELETE FROM strings WHERE id=OLD.remarks OR id=OLD.name OR
id=OLD.address;
  RETURN OLD;
  END;
 ' LANGUAGE 'plpgsql';

Checked table  lentities_history seems OK.

This bug appears to be because of a typo in function fd_gentities: it should
say
"INSERT INTO gentities_history" instead of "INSERT INTO lentities_history". The
above fix is incorrect, it would create a big mess in the history tables.

I attempt to delete a station and get the following message:

ERROR: update or delete on "instruments" violates foreign key constraint
"fk_timeseries_instruments_instr" on "timeseries_instruments"
DETAIL: Key (id)=(2) is still referenced from table "timeseries_instruments".
CONTEXT: SQL statement "DELETE FROM ONLY "public"."instruments" WHERE "station"
= $1"

Reportedly a "#end def" is missing from the last line of data.tmpl, which
prevents it from compiling.

Autoupdate needs a Makefile to:
  * Compile the manual.
  * Install manual and program in needed directories.
  * Possibly also check for dependencies, such as needed python modules
    and needed programs.

"todos" is used, but some distributions, such as SuSE 9.2, don't have it, they
have unix2dos instead. Debian sarge has both, unix2dos being a symlink to todos.

Sometimes it so happens that the raw file is not in chronological order, for
example due to a logger clock error. Autoupdate does not understand that, and it
may insert the data to the database, which will cause problems later.

Sometimes it is needed to just check that a file is OK (in chronological
order).
"timeseries" already does that, but it lacks an option to do only that.