roundup-users

[Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[<iva...@ri...>]

Hello!

I'm interested in Roundup Tracker and am currently exploring its
capabilities. Now I have a question regarding its use with PostgreSQL.

I'm using containerized application servers behind a reverse proxy. 

My current setup has the PGSERVICE environment variable set for each
application server, so that psycopg2 can connect to the PostgreSQL
database using connection information from the pg_service.conf file. It
allows me to avoid storing database credentials in application-related
configuration files. 

Is there a way that Roundup Tracker can avoid using the default database
settings and use the values ​​from pg_service.conf to connect to the
database?

Thank you.

-- 
With appreciation,
Ivanov

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[John P. R. <ro...@cs...>]

Hello Ivanov:

In message <82a...@ri...>,
iva...@ri... writes:
>I'm interested in Roundup Tracker and am currently exploring its
>capabilities.

Thanks for your interest.

>Now I have a question regarding its use with PostgreSQL.
>
>I'm using containerized application servers behind a reverse proxy. 

BTW we have docker images at hub.docker.com look for Roundup.

>My current setup has the PGSERVICE environment variable set for each
>application server, so that psycopg2 can connect to the PostgreSQL
>database using connection information from the pg_service.conf file. It
>allows me to avoid storing database credentials in application-related
>configuration files. 
>
>Is there a way that Roundup Tracker can avoid using the default database
>settings and use the values ​​from pg_service.conf to connect to the
>database?

There is no explicit support in Roundup for this.

However, libpq (which psycopg2 uses) should just use the environment
variable provided Roundup doesn't override it.
(https://www.postgresql.org/docs/current/libpq-envars.html)

Try this. Set the environment variable:

  PGSERVICE=your_service_name

Then remove any values for the settings:

  host, port, user, password, and name

in the rdbms section of the tracker's config.ini. (If you are not
setting some of those values in your pg_service.conf, set them in
config.ini.) Make sure the rdbms backend setting is set to postgresql.

If your pg_service.conf file looks like:

[roundup_roundup]
host=127.0.0.1
port=5432
user=rounduptest
password=rounduptest
dbname=rounduptest

(the defaults for demo mode), run:

  roundup-admin -i tracker_home list status

and see if you get a connection error.

I set thing up as above and it worked for me. It displayed the
statuses for the demo tracker. roundup-admin picked up the settings
from the environment variable and the config file. I am using the
current development tip of the default branch. But nothing done since
release 2.3.0 should affect this AFAICT.

I suspect the same method will work for starting the web frontend, or
other roundup-* commands. If this works, it gives you what you want as
long as you are ok with supporting only one tracker per container.

psycopg2 looks like it can use a "service=service_name" in its connect
method. (Working from https://github.com/psycopg/psycopg2/issues/926.)

It might be a useful enhancement to support 'service' directly in
config.ini. It would be useful when running one roundup server serving
multiple trackers.

If you want to move forward with Roundup and run multiple trackers
with one Roundup server instance, we can work on getting support for
specifying the 'service' in config.ini. You would need to test using
the current development code.

Let me know how this works for you.

Have a great week.
--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[<iva...@ri...>]

Hello John!

On 2023-11-06 23:13, John P. Rouillard wrote: 
> 
> BTW we have docker images at hub.docker.com look for Roundup.

Thank you. I prefer CI tools and Git for delivering and updating web
applications.

> Try this. Set the environment variable:
> 
>   PGSERVICE=your_service_name
> 
> Then remove any values for the settings:
> 
>   host, port, user, password, and name
> 
> in the rdbms section of the tracker's config.ini. (If you are not
> setting some of those values in your pg_service.conf, set them in
> config.ini.) Make sure the rdbms backend setting is set to postgresql.
> 
> If your pg_service.conf file looks like:
> 
> [roundup_roundup]
> host=127.0.0.1
> port=5432
> user=rounduptest
> password=rounduptest
> dbname=rounduptest
> 
> (the defaults for demo mode), run:
> 
>   roundup-admin -i tracker_home list status
> 
> and see if you get a connection error.

Excellent! Thank you very much. I will try it.

> psycopg2 looks like it can use a "service=service_name" in its connect
> method. (Working from https://github.com/psycopg/psycopg2/issues/926.)

Django can use psycopg2/psycopg3 in a similar way. Since version 4.0,
you can simply use the service parameter to connect to PostgreSQL
without even specifying the database name. If you are interested,
details can be found here: https://code.djangoproject.com/ticket/32292

> It might be a useful enhancement to support 'service' directly in
> config.ini. It would be useful when running one roundup server serving
> multiple trackers.
> 
> If you want to move forward with Roundup and run multiple trackers
> with one Roundup server instance, we can work on getting support for
> specifying the 'service' in config.ini. You would need to test using
> the current development code.

Thank you. At the moment I plan to install only one tracker, but I think
it would be nice to have such an option. When you implement this, I will
be happy to test it.
 
> Let me know how this works for you.

Well, of course.

-- 
With appreciation,
Ivanov

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[John P. R. <ro...@cs...>]

Hello Ivanov:

In message <bf0...@ri...>,
iva...@ri... writes:
>On 2023-11-06 23:13, John P. Rouillard wrote: 
>> It might be a useful enhancement to support 'service' directly in
>> config.ini. It would be useful when running one roundup server serving
>> multiple trackers.
>> 
>> If you want to move forward with Roundup and run multiple trackers
>> with one Roundup server instance, we can work on getting support for
>> specifying the 'service' in config.ini. You would need to test using
>> the current development code.
>
>Thank you. At the moment I plan to install only one tracker, but I think
>it would be nice to have such an option. When you implement this, I will
>be happy to test it.

Since it only took a couple of hours, I implemented it in: 

    changeset:   7696:4af0d235b570
    tag:         tip
    user:        John Rouillard <ro...@ie...>
    date:        Tue Nov 07 12:11:37 2023 -0500
    files:       CHANGES.txt doc/reference.txt
                 roundup/backends/back_postgresql.py
                 roundup/backends/rdbms_common.py roundup/configuration.py

    description:
    feat(db): support using postgresql service connection file

    Add new service rdbms config option to set the service name to be used
    with a postgresql service connection file.

    This can be done using the PGSERVICE environment variable for a single
    instance tracker server. For a multi-instance server this per-tracker
    config option is needed.

    Note that settings (host, user, (db)name...) in config.ini file will
    override the service connection file setting. Also setting PGSERVICE
    and service will use the service setting.

You can pull the code from the repo using mercurial:

  hg clone http://hg.code.sf.net/p/roundup/code roundup

The issue is tracked at: https://issues.roundup-tracker.org/issue2551299

You can send email to the is...@ro... with the subject:

  [issue2551299] Connecting to PostgreSQL database using pg_service.conf

to followup with any issues or just reply here.

Have a great week.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

[Roundup-users] Feature Request : Copy Issue id and title separated by '; ' to the clipboard

[SCHLEMMER N. <Nor...@pd...>]

Hello all



How can we add a button to the "Editing issue" template to copy the issue id and the title separated by '; ' to the clipboard.



I need this very often to add references to other issues.



[cid:image001.png@01DA1229.1B76A390]



Thanks

Norbert

Re: [Roundup-users] Feature Request : Copy Issue id and title separated by '; ' to the clipboard

[Tom E. <te...@uw...>]

Norbert,

I put this in the first <td> element of the comment field.

Time tracking example: <button type="button" onclick="set_comment()"><span>:spend 1.5h 2023-11-01</span></button><br>

This JS function does the work.


/* Insert a :spend string for yesterday into the comment field. */

function set_comment() {

    var ele = document.getElementById('comment_text');

    var now = new Date();

    var then = new Date(now.getTime() - 24*60*60*1000);

    var month = then.getMonth() + 1; // Month is 0 based.

    var date= then.getDate();

    var s;

    if (month < 10) {

        month = '0' + month;

    }

    if (date < 10) {

        date = '0' + date;

    }

    s = ':spend 1.5h ' + then.getFullYear() + '-' + month + '-' + date;

    ele.value = ele.value + '\n' + s;

}

Not quite what you want. You could pass the issue id and title to the function to compute the "issue,title" string. Google "javascript writing to clipboard" to find various ways to write to the clipboard with JS.


Tom Ekberg
Senior Computer Specialist
Department of Laboratory Medicine and Pathology
4th Floor, Pat Steel Building, currently WFH
Home: (253) 561-2509
Email: te...@uw...<mailto:te...@uw...>

From: SCHLEMMER Norbert <Nor...@pd...>
Sent: Wednesday, November 8, 2023 12:51 AM
To: rou...@li...
Subject: [Roundup-users] Feature Request : Copy Issue id and title separated by '; ' to the clipboard


Hello all



How can we add a button to the "Editing issue" template to copy the issue id and the title separated by '; ' to the clipboard.



I need this very often to add references to other issues.



[cid:image001.png@01DA121A.5D9B9D20]



Thanks

Norbert

Re: [Roundup-users] Feature Request : Copy Issue id and title separated by '; ' to the clipboard

[John P. R. <ro...@cs...>]

Hi Norbert:

In message <DU2PR01MB8557DF8C05C168671C6D7844E8A8A@DU2PR01MB8557.eurprd01.prod.
exchangelabs.com>,
SCHLEMMER Norbert writes:
>How can we add a button to the "Editing issue" template to copy
>the issue id and the title separated by '; ' to the clipboard.
>
>I need this very often to add references to other issues.

Try this:

      <button id="copyreference" type="button"
	      tal:condition="context/is_edit_ok">
	 Copy Reference
      </button>

      <script tal:attributes="nonce request/client/client_nonce">
      (function () {
	 "use strict";

	 let crb = document.querySelector("#copyreference");
	 if ( ! crb ) return
	 crb.addEventListener("click", async (e) => {
	     e.preventDefault()
	     if ( ! navigator.clipboard ) {
		 alert("Clipboard is not available")
		 return
	     }

	     let issueDesignator = new URL(document.URL)
		 .pathname
		 .split("/")
		 .pop()
	     let issueTitleText = document.querySelector("#title").value;

	     await navigator.clipboard.writeText(`${issueDesignator}: ${issueTitleText}`)

	     let originalCrbInnerText =  crb.innerText
	     crb.innerText = "Reference copied"
	     setTimeout(() => {crb.innerText = originalCrbInnerText}, 2000)

	 }
			     );
      })();
    </script>

Place the button where you want in you issue.item.html. Place the
script anywhere in the issue.item.html file after the button element.
Usually I put it as close to the bottom of the generated page as I
can.

Note that the button element only shows up if the user can edit the
issue.  When the user is in view only (not edit) mode, there are no
id's or CSS selectors that can be used to find the title. I made my
best guess as to the CSS selectors I needed to use. See below if they
don't work out.

When clicking on the button (or hitting space or return when focused
on the button) you will get something like:

  issue1: nosier test change scrummage but longer than title mung.

copied to the clipboard.  The text of the button will change to
"Reference copied" and then reset to the original message after 2
seconds.

This code depends on the browser having the clipboard API. If it
doesn't an alert will pop up.

There is nothing with an id or CSS selector that contains the
designator.  I am using the final element of the path of the URL to
get the designator.

The code structure is called an IIFE. I am using it to allow me to
exit/return if the button is missing from the document (e.g. the user
only has view permission).

There is some other error checking that probably needs to happen:

   if the user is not permitted to edit the title, this will fail
     as the input element with the id will not exist.

but this should provide you with the 80% solution.

If you do need to have this work in view only mode:

 * change the TAL to output an id="title" for the element enclosing the title
 * remove the condition from the button.

Then try replacing:

	     let issueTitleText = document.querySelector("#title").value;

with:

	     let issueTitle = document.querySelector("input#title");
	     if ( ! issueTitle ) {
	       issueTitle = document.querySelector("#title");
	       issueTitleText = issueTitle.innerText
	     } else {
	       issueTitleText = issueTitle.value
	     }

I think that may work if you need support for view only mode.

If your HTML doesn't have a title id on the title element you can try
replacing "#title" with "input[name=title]".

Hope this works. Have a great day.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

Re: [Roundup-users] Feature Request : Copy Issue id and title separated by '; ' to the clipboard

[SCHLEMMER N. <Nor...@pd...>]

Hi John

Thanks, git it working, great.
Have to replace "#title" with "input[name=title]"

Br
Norbert

[Roundup-users] Feature Request : Additional Search Buttons

[SCHLEMMER N. <Nor...@pd...>]

Hello All



How can the "Your queries" or "Issues" frame be extended with these buttons / queries:



Show issues with activity today

Show issues with activity yesterday

Show issues with activity this week

Show issues with activity last week



Thanks

Norbert

Re: [Roundup-users] Feature Request : Additional Search Buttons

[John P. R. <ro...@cs...>]

Hi Norbert:

In message <DU2PR01MB8557064CB1116A4A3C9ED17CE8AFA@DU2PR01MB8557.eurprd01.prod.
exchangelabs.com>,
SCHLEMMER Norbert writes:

>How can the "Your queries" or "Issues" frame be extended with these
>buttons / queries:
>
>Show issues with activity today
>
>Show issues with activity yesterday
>
>Show issues with activity this week
>
>Show issues with activity last week

You should be able to make all these queries mostly. See:

   https://www.roundup-tracker.org/docs/user_guide.html#searching-page

for how to make queries. You can make queries public using the edit
link near your queries. This way others can add your query to their
"Your Queries" list.

For the queries above, you would set up a query for each interval
using the activity property.

  https://www.roundup-tracker.org/docs/user_guide.html#date-properties

may be of use.

However, you may not be able to get exact the time intervals.

It looks like yesterday would be: "from 00:00 -1d to 00:00". At least
that seems to indicate that it should work.

Similarly for the other intervals. E.G. "last week" might not be
alignable on week boundaries, but would something like:

  from 00:00 -14d to 00:00 -7d

for the last 7 days each day starting at midnight.

Ralf, thoughs about supporting something like

      "sun - 7d"

which would be the sunday prior to today minus 7 days (so a week ago
sunday?). I am not sure if we want to handle day names or use numbers
like cron (with some letter indicating it's a fixed day reference).

Norbert, if you want to add it to the issues frame, you will have to
create "a" links manually by editing page.html (IIRC) using the search
url as the href.

Have a great day.
--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

Re: [Roundup-users] Feature Request : Additional Search Buttons

[SCHLEMMER N. <Nor...@pd...>]

Hi John



Thanks again for your help, got it working...



Br

Norbert





[cid:image001.png@01DA1623.2CF0DCA0]

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[John P. R. <ro...@cs...>]

Hello Ivanov:

In message <202...@pe...>,
"John P. Rouillard" writes:
>In message <bf0...@ri...>,
>iva...@ri... writes:
>>On 2023-11-06 23:13, John P. Rouillard wrote: 
>>> If you want to move forward with Roundup and run multiple trackers
>>> with one Roundup server instance, we can work on getting support for
>>> specifying the 'service' in config.ini. You would need to test using
>>> the current development code.
>>
>>Thank you. At the moment I plan to install only one tracker, but I think
>>it would be nice to have such an option. When you implement this, I will
>>be happy to test it.
>
>Since it only took a couple of hours, I implemented it in: 
>
>    changeset:   7696:4af0d235b570
>    tag:         tip
>    user:        John Rouillard <ro...@ie...>
>    date:        Tue Nov 07 12:11:37 2023 -0500
>    files:       CHANGES.txt doc/reference.txt
>                 roundup/backends/back_postgresql.py
>                 roundup/backends/rdbms_common.py roundup/configuration.py
>
>    description:
>    feat(db): support using postgresql service connection file
>
>    Add new service rdbms config option to set the service name to be used
>    with a postgresql service connection file.
>
>    This can be done using the PGSERVICE environment variable for a single
>    instance tracker server. For a multi-instance server this per-tracker
>    config option is needed.
>
>    Note that settings (host, user, (db)name...) in config.ini file will
>    override the service connection file setting. Also setting PGSERVICE
>    and service will use the service setting.
>
>You can pull the code from the repo using mercurial:
>
>  hg clone http://hg.code.sf.net/p/roundup/code roundup
>
>The issue is tracked at: https://issues.roundup-tracker.org/issue2551299
>
>You can send email to the is...@ro... with the subject:
>
>  [issue2551299] Connecting to PostgreSQL database using pg_service.conf
>
>to followup with any issues or just reply here.

Have you had a chance to test this?

Have a great week.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[<iva...@ri...>]

On 2023-11-21 03:43, John P. Rouillard wrote:
> 
> Have you had a chance to test this?
> 

Hello. Sorry for the long silence. 

I have tested this and found the following two problems.

First, the 'roundup-admin initialise' command doesn't work properly in
this case. I got the same results using first the PGSERVICE environment
variable and then the rdbms_service configuration parameter. 


$ roundup-admin initialise
Enter tracker home: core
Admin Password: 
       Confirm: 
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-18 03:15:58,166 INFO DROP DATABASE ""
Traceback (most recent call last):
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 106, in pg_command
    cursor.execute(command)
psycopg2.errors.SyntaxError: zero-length delimited identifier at or near
""""
LINE 1: DROP DATABASE ""
                      ^


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
    sys.exit(run())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
    sys.exit(tool.main())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
    ret = self.run_command(args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
    return self.do_initialise(self.tracker_home, args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
    tracker.nuke()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
    self.backend.db_nuke(self.config)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
    db_command(config, command)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 92, in db_command
    if pg_command(cursor, command):
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 118, in pg_command
    raise RuntimeError(response)
RuntimeError: zero-length delimited identifier at or near """"


But what's even stranger is that I can't initialize the database when I
specify the database name, host, port, username and password in the
configuration file.


$ roundup-admin initialise
Enter tracker home: core
Admin Password:
       Confirm:
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-18 19:31:01,239 INFO DROP DATABASE "roundup_dev"
Traceback (most recent call last):
  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
    sys.exit(run())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
    sys.exit(tool.main())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
    ret = self.run_command(args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
    return self.do_initialise(self.tracker_home, args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
    tracker.nuke()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
    self.backend.db_nuke(self.config)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
    db_command(config, command)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 96, in db_command
    raise RuntimeError('10 attempts to create database failed')
RuntimeError: 10 attempts to create database failed


My postgresql user is the owner of the selected database, but does not
have permission to create databases. 

I think the problem is here, but I don't want to give them additional
permissions just to initially populate the database.

Maybe some kind of non-destructive initialization might be possible,
without recreating the entire database?

By the way, why a database and not a schema?


At the same time, when I directly create Roundup users, it works for me.


$ roundup-admin create user username="admin" roles="Admin"
Enter tracker home: core
2023-12-18 20:03:10,205 INFO open database via service 'roundup_dev'
2023-12-18 20:03:10,241 INFO commit
2023-12-18 20:03:10,241 DEBUG SQL "select nextval('_user_ids') from
dual" None
2023-12-18 20:03:10,242 DEBUG SQL 'select id from _user where
_username=%s and __retired__=%s' ('admin', 0)
2023-12-18 20:03:10,245 DEBUG addnode user1 {'username': 'admin',
'roles': 'Admin', 'password': None, 'address': None, 'realname': None,
'phone': None, 'organisation': None, 'alternate_addresses': None,
'queries': [], 'timezone': None}
2023-12-18 20:03:10,245 DEBUG SQL 'insert into _user
(_activity,_actor,_address,_alternate_addresses,_creation,_creator,_organisation,_password,_phone,_realname,_roles,_timezone,_username,id)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' ('2023-12-18
16:03:10.245', 1, None, None, '2023-12-18 16:03:10.245', 1, None, None,
None, None, 'Admin', None, 'admin', '1')
2023-12-18 20:03:10,246 DEBUG addjournal user1 <Date
2023-12-18.16:03:10.246> 1 create {}
2023-12-18 20:03:10,246 DEBUG SQL 'insert into user__journal
(nodeid,date,tag,action,params) values (%s,%s,%s,%s,%s)' ('1',
'2023-12-18 16:03:10.246', '1', 'create', '{}')
1
2023-12-18 20:03:10,247 INFO commit
2023-12-18 20:03:10,248 INFO close

$ roundup-admin create user username="anonymous" roles="Anonymous"
Enter tracker home: core
2023-12-18 20:03:55,461 INFO open database via service 'roundup_dev'
2023-12-18 20:03:55,493 INFO commit
2023-12-18 20:03:55,494 DEBUG SQL "select nextval('_user_ids') from
dual" None
2023-12-18 20:03:55,495 DEBUG SQL 'select id from _user where
_username=%s and __retired__=%s' ('anonymous', 0)
2023-12-18 20:03:55,496 DEBUG addnode user2 {'username': 'anonymous',
'roles': 'Anonymous', 'password': None, 'address': None, 'realname':
None, 'phone': None, 'organisation': None, 'alternate_addresses': None,
'queries': [], 'timezone': None}
2023-12-18 20:03:55,496 DEBUG SQL 'insert into _user
(_activity,_actor,_address,_alternate_addresses,_creation,_creator,_organisation,_password,_phone,_realname,_roles,_timezone,_username,id)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' ('2023-12-18
16:03:55.497', 1, None, None, '2023-12-18 16:03:55.497', 1, None, None,
None, None, 'Anonymous', None, 'anonymous', '2')
2023-12-18 20:03:55,497 DEBUG addjournal user2 <Date
2023-12-18.16:03:55.497> 1 create {}
2023-12-18 20:03:55,497 DEBUG SQL 'insert into user__journal
(nodeid,date,tag,action,params) values (%s,%s,%s,%s,%s)' ('2',
'2023-12-18 16:03:55.497', '1', 'create', '{}')
2
2023-12-18 20:03:55,497 INFO commit
2023-12-18 20:03:55,505 INFO close


roundup_dev=> select * from public._user;
        _activity        | _actor | _address | _alternate_addresses |   
    _creation        | _creator | _organisation | _password | _phone |
_realname |  _roles   | _timezone | _username | id | __retired__ 
-------------------------+--------+----------+----------------------+-------------------------+----------+---------------+-----------+--------+-----------+-----------+-----------+-----------+----+-------------
 2023-12-18 16:03:10.245 |      1 |          |                      |
2023-12-18 16:03:10.245 |        1 |               |           |       
|           | Admin     |           | admin     |  1 |           0
 2023-12-18 16:03:55.497 |      1 |          |                      |
2023-12-18 16:03:55.497 |        1 |               |           |       
|           | Anonymous |           | anonymous |  2 |           0
(2 rows)



Second, there is a problem with using a schema other than 'public'. I
can't perform database migration when I create scheme named by my
postgresql username and use it in search_path. I see that Roundup
expected me to use the 'public' schema.


$ roundup-admin migrate
Enter tracker home: core
2023-12-18 03:13:02,011 INFO open database via service 'roundup_dev'
2023-12-18 03:13:02,033 INFO rollback
2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
None
2023-12-18 03:13:02,044 DEBUG SQL 'CREATE TABLE dual (dummy integer)'
None
2023-12-18 03:13:02,046 DEBUG SQL 'insert into dual values (1)' None
2023-12-18 03:13:02,047 DEBUG SQL 'CREATE TABLE otks (otk_key
VARCHAR(255),\n            otk_value TEXT, otk_time float)' None
2023-12-18 03:13:02,055 DEBUG SQL 'CREATE INDEX otks_key_idx ON
otks(otk_key)' None
2023-12-18 03:13:02,060 DEBUG SQL 'CREATE TABLE sessions (\n           
session_key VARCHAR(255), session_time float,\n            session_value
TEXT)' None
2023-12-18 03:13:02,065 DEBUG SQL 'CREATE INDEX sessions_key_idx ON\n   
        sessions(session_key)' None
2023-12-18 03:13:02,069 DEBUG SQL 'CREATE SEQUENCE ___textids_ids' None
2023-12-18 03:13:02,070 DEBUG SQL 'CREATE TABLE __textids (\n           
_textid integer primary key, _class VARCHAR(255),\n            _itemid
VARCHAR(255), _prop VARCHAR(255))' None
2023-12-18 03:13:02,079 DEBUG SQL 'CREATE TABLE __words (_word
VARCHAR(55),\n            _textid integer)' None
2023-12-18 03:13:02,080 DEBUG SQL 'CREATE INDEX words_word_idx ON
__words(_word)' None
2023-12-18 03:13:02,087 DEBUG SQL 'CREATE INDEX words_by_id ON __words
(_textid)' None
2023-12-18 03:13:02,092 DEBUG SQL 'CREATE UNIQUE INDEX
__textids_by_props ON __textids (_class, _itemid, _prop)' None
2023-12-18 03:13:02,097 DEBUG SQL 'DELETE FROM otks' None
2023-12-18 03:13:02,097 DEBUG SQL 'ALTER TABLE otks DROP otk_value' None
2023-12-18 03:13:02,098 DEBUG SQL 'ALTER TABLE otks ADD otk_value TEXT'
None
2023-12-18 03:13:02,099 DEBUG SQL 'DELETE FROM sessions' None
2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions DROP
session_value' None
2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions ADD
session_value TEXT' None
2023-12-18 03:13:02,100 DEBUG SQL 'CREATE INDEX words_both_idx ON
public.__words\n            USING btree (_word, _textid)' None
Traceback (most recent call last):
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 217, in open_connection
    self.load_dbschema()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 305, in load_dbschema
    self.cursor.execute('select schema from schema')
psycopg2.errors.UndefinedTable: relation "schema" does not exist
LINE 1: select schema from schema
                           ^


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
    sys.exit(run())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
    sys.exit(tool.main())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
    ret = self.run_command(args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2114, in run_command
    self.db = tracker.open(self.name)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 111, in open
    'db': backend.Database(self.config, name)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 227, in __init__
    self.open_connection()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 227, in open_connection
    self.fix_version_3_tables()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 335, in fix_version_3_tables
    self.sql('''CREATE INDEX words_both_idx ON public.__words
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 266, in sql
    cursor.execute(sql)
psycopg2.errors.UndefinedTable: relation "public.__words" does not exist


If I only use the 'public' schema for my database, the migration command
works well.

Thank you for your help.

-- 
With appreciation,
Ivanov

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

[John P. R. <ro...@cs...>]

Hi Ivanov:

We should probably take this to the issue:

   https://issues.roundup-tracker.org/issue2551299

Can you sign up for an account and add yourself to the issue above.

In message <725...@ri...>,
iva...@ri... writes:
>I have tested this and found the following two problems.
>
>First, the 'roundup-admin initialise' command doesn't work properly in
>this case. I got the same results using first the PGSERVICE environment
>variable and then the rdbms_service configuration parameter. 

Good it's consistent.

>$ roundup-admin initialise
>Enter tracker home: core
>Admin Password: 
>       Confirm: 
>WARNING: The database is already initialised!
>If you re-initialise it, you will lose all the data!
>Erase it? Y/N: Y
>2023-12-18 03:15:58,166 INFO DROP DATABASE ""
>Traceback (most recent call last):
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 106, in pg_command
>    cursor.execute(command)
>psycopg2.errors.SyntaxError: zero-length delimited identifier at or near
>""""
>LINE 1: DROP DATABASE ""

Ick.

If I have psycopg > 2.8 I can use
psycopg2.extensions.ConnectionInfo(connection) to get the dbname. I think.

>But what's even stranger is that I can't initialize the database when I
>specify the database name, host, port, username and password in the
>configuration file.

That is wierd. Do you have all the settings plus the service
defined?

>$ roundup-admin initialise
>Enter tracker home: core
>Admin Password:
>       Confirm:
>WARNING: The database is already initialised!
>If you re-initialise it, you will lose all the data!
>Erase it? Y/N: Y
>2023-12-18 19:31:01,239 INFO DROP DATABASE "roundup_dev"
>Traceback (most recent call last):
>  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
>8, in <module>
>    sys.exit(run())
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
>line 50, in run
>    sys.exit(tool.main())
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
>line 2230, in main
>    ret = self.run_command(args)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
>line 2079, in run_command
>    return self.do_initialise(self.tracker_home, args)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
>line 1168, in do_initialise
>    tracker.nuke()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
>line 213, in nuke
>    self.backend.db_nuke(self.config)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 64, in db_nuke
>    db_command(config, command)
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 96, in db_command
>    raise RuntimeError('10 attempts to create database failed')
>RuntimeError: 10 attempts to create database failed

Hmm, db_command should say what command it's having an issue with ans
what the error is.

Can you change:

-    raise RuntimeError('10 attempts to create database failed')

to

+    raise RuntimeError('10 attempts to create database failed when running: %s' % command)

around line 96 of roundup/backends/back_postgresql.py. That will get
some of the info hopefully.

Also can you connect to the db with psql and run \du so I can see what
permissions the user has. I assume none are listed since you don't
have CREATEDB.

>My postgresql user is the owner of the selected database, but does not
>have permission to create databases. 
>
>I think the problem is here, but I don't want to give them additional
>permissions just to initially populate the database.
>
>Maybe some kind of non-destructive initialization might be possible,
>without recreating the entire database?

It's supposed IIRC. It checks to see if the db exists. If the db
exists it tries to load the roundup schema. If that fails it
initializes the db. But it doesn't look like you are getting that far.

>By the way, why a database and not a schema?

Because thats how it was originally designed and nobody has tried to
integrate https://issues.roundup-tracker.org/issue2550852 and finish
up tests/docs etc. I have a vague recollection of testing the patch a
few years ago and it worked.

>At the same time, when I directly create Roundup users, it works for me.

Is the sequence here:

  set all db params in config.ini (host, database ...)

  roundup-admin init

  get the error: RuntimeError: 10 attempts to create database failed

  roundup-admin create user ....

  works.

>Second, there is a problem with using a schema other than 'public'. I
>can't perform database migration when I create scheme named by my
>postgresql username and use it in search_path. I see that Roundup
>expected me to use the 'public' schema.

That is either fixed in issue2550852, or a 'to be fixed' if support
for schemas comes on board.

>$ roundup-admin migrate
>Enter tracker home: core
>2023-12-18 03:13:02,011 INFO open database via service 'roundup_dev'
>2023-12-18 03:13:02,033 INFO rollback
>2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
>None
>2023-12-18 03:13:02,044 DEBUG SQL 'CREATE TABLE dual (dummy integer)'
>None
>2023-12-18 03:13:02,046 DEBUG SQL 'insert into dual values (1)' None
>2023-12-18 03:13:02,047 DEBUG SQL 'CREATE TABLE otks (otk_key
>VARCHAR(255),\n            otk_value TEXT, otk_time float)' None
>2023-12-18 03:13:02,055 DEBUG SQL 'CREATE INDEX otks_key_idx ON
>otks(otk_key)' None
>2023-12-18 03:13:02,060 DEBUG SQL 'CREATE TABLE sessions (\n           
>session_key VARCHAR(255), session_time float,\n            session_value
>TEXT)' None
>2023-12-18 03:13:02,065 DEBUG SQL 'CREATE INDEX sessions_key_idx ON\n   
>        sessions(session_key)' None
>2023-12-18 03:13:02,069 DEBUG SQL 'CREATE SEQUENCE ___textids_ids' None
>2023-12-18 03:13:02,070 DEBUG SQL 'CREATE TABLE __textids (\n           
>_textid integer primary key, _class VARCHAR(255),\n            _itemid
>VARCHAR(255), _prop VARCHAR(255))' None
>2023-12-18 03:13:02,079 DEBUG SQL 'CREATE TABLE __words (_word
>VARCHAR(55),\n            _textid integer)' None
>2023-12-18 03:13:02,080 DEBUG SQL 'CREATE INDEX words_word_idx ON
>__words(_word)' None
>2023-12-18 03:13:02,087 DEBUG SQL 'CREATE INDEX words_by_id ON __words
>(_textid)' None
>2023-12-18 03:13:02,092 DEBUG SQL 'CREATE UNIQUE INDEX
>__textids_by_props ON __textids (_class, _itemid, _prop)' None
>2023-12-18 03:13:02,097 DEBUG SQL 'DELETE FROM otks' None
>2023-12-18 03:13:02,097 DEBUG SQL 'ALTER TABLE otks DROP otk_value' None
>2023-12-18 03:13:02,098 DEBUG SQL 'ALTER TABLE otks ADD otk_value TEXT'
>None
>2023-12-18 03:13:02,099 DEBUG SQL 'DELETE FROM sessions' None
>2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions DROP
>session_value' None
>2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions ADD
>session_value TEXT' None
>2023-12-18 03:13:02,100 DEBUG SQL 'CREATE INDEX words_both_idx ON
>public.__words\n            USING btree (_word, _textid)' None
>Traceback (most recent call last):
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 217, in open_connection
>    self.load_dbschema()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
>line 305, in load_dbschema
>    self.cursor.execute('select schema from schema')
>psycopg2.errors.UndefinedTable: relation "schema" does not exist
>LINE 1: select schema from schema
>                           ^

Now this is wierd because:

  2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'

the schema table and column were created, but probably in your
alternate schema. ("schema" is a confusing term for Roundup to use
there....)

How are you creating/directing roundup to use an alternate schema?  Is
there a schema= in pg_service.conf? Is the path associated with the
logged in user?

My guess is that the database layout is being put into your alternate
(non-public) schema and 'select schema from schema' uses the public
schema by default and fails.

The schema support patch does an explicit set search path to include
the schema.

>line 227, in __init__
>    self.open_connection()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 227, in open_connection

This is the code that runs to initialize the db if it already exists.
fix_version_3_tables runs after initializing the tables.

>    self.fix_version_3_tables()
>  File
>"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
>line 335, in fix_version_3_tables
>    self.sql('''CREATE INDEX words_both_idx ON public.__words
>[...]
>psycopg2.errors.UndefinedTable: relation "public.__words" does not exist
>
>If I only use the 'public' schema for my database, the migration command
>works well.

I wonder why this has an explicit schema on it? Can you check to see
if your alternate schema (assuming it got committed to the db) has a
__words table in it?

Can you change the reference to public.__words to just __words around
line 335 in back_postgresql.py. Then see if the migrate works. I don't
see any other explicit schema references in that file.

> Thank you for your help.

No, thank you for helping to make Roundup better.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.