Using pgAdmin III (was Re: [Gnumed-devel] How gnumed deals with death)

[Jim Busser]

On 16-Jul-09, at 5:49 PM, Rogerio Luz Coelho wrote:

How do I do it Karsten ? pgAdmin III wil do 

This was learning also for me. Just taking an (incomplete) stab at it, others will need to help out. The following web resource got me started:

http://www.1keydata.com/sql/sqlupdate.html

Before we go into pgAdmin III we need to know some information about what we will put in the UPDATE command:

1) in GNUmed, bring the patient into focus i.e. "search" them

2) at the far right of the GNUmed client window title bar (i.e. above the Cave) note the primary ID "#" of the patient

e.g. in the demo database, Kirk is #12

You will need to know the table and column name whose value you want to replace. While I do supply it below, if you wished to look it up, click GNUmed's "Reports" plugin, and click the obscured right-most button ("Schema") whose tooltip "Show the database schema...) will confirm it to be the one you want... you can unobscure it by dragging downward the splitter that runs just below the buttons.

From the wiki page, click V11. At this point, it helps to be familiar that the gnumed schema is divided into subschemas like "audit", "clin", "dem" ... the table that we want is within "dem"... it is the "identity" table... and within that table can be identified the column "deceased"

3) pgAdmin III will need Karsten or Sebastian's help (?)

From the Gnome desktop menu Applications > System Tools > pgAdmin III

- it appears we need to create a "connection" to "register our server" in pgAdmin III

--> below the File menu, click the "AC plug" icon

(see screenshot)

Picture 26.png
Description: application/applefile

- complete the "new server registration"

- name can be anything you want (?)

- host is presumably your laptop as localhost (?)

- password may be a problem if it was forgotten or never configured, because apparently in Ubuntu/Debian one may not have been set as explained (with a solution, below) at

https://issues.openbravo.com/view.php?id=1154

Assuming your laptop is encrypted anyway, you might substitute an easier to remember password than "123" (maybe postgres). Open a terminal and as sudo (or if not sudo, as root):

$ sudo su - postgres

$ psql -U postgres

# alter role postgres with password '123';

# \q

$ exit

- back into the connection profile, insert the new postgres password "postgres"

- click "OK"

- thereafter we should be able to see something in the left pane that can be popped open and selected... I am expecting somewhere we might be able to see gnumed_v11... if so, maybe just click on it?

- can a suitable UPDATE command now just be pasted into the bottom right "SQL pane"?

5) issue the UPDATE command (below), substituting a suitable timestamp for [new value] (without "[" "]") and the # for the patient

As far as their timestamp of death, I am not sure whether just YYMMDD will be enough. If a timezone is required and if postgres may not simply assume your current time zone,  Curitiba is UTC -3 except during your daylight savings time you are UTC -2 ... maybe if in a terminal you run the "date" command you will see something regional that may also be acceptable. Allowable date format alternates to YYYYMMDD are described at

http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html

The command will be like

UPDATE "identity"

SET "deceased" = [new value]
WHERE pk IS #