client that supports editing views

Willy-Bas Loos, 09.12.2013 14:36:

I have a database with views that have rules on them, so that users
can do insert/update/delete on their part of the data. The rules ore
"do instead" rules that redirect the edits to the table that actually
holds the data, on which the users have no rights.

This works fine in MS Access (at least versions 2000 and 2010).

Now i've added some functionality, i've added a set of views that
show records that a user is entitled to insert, that she hasn't
inserted. The records in the view show the default values, they could
be viewed as "templates". To create the new record, all the user has
to do is edit the template record. It is then inserted into the
corresponding table and consequently disappears from the template
view.

But Access 2010 detects the removal from this "template" view as a
conflict: the user did an update, but "another user" deleted the
record :( .

Is there a more lightweight windows GUI client that does updates and
deletes on views?

What kind of "GUI" are you thinking of?

A GUI focused on running SQL queries or a really something like MS Access where you have custom input forms.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/09/2013 05:36 AM, Willy-Bas Loos wrote:

I have a database with views that have rules on them, so that users can
do insert/update/delete on their part of the data.
The rules ore "do instead" rules that redirect the edits to the table
that actually holds the data, on which the users have no rights.

This works fine in MS Access (at least versions 2000 and 2010).

Now i've added some functionality, i've added a set of views that show
records that a user is entitled to insert, that she hasn't inserted. The
records in the view show the default values, they could be viewed as
"templates". To create the new record, all the user has to do is edit
the template record. It is then inserted into the corresponding table
and consequently disappears from the template view.

But Access 2010 detects the removal from this "template" view as a
conflict: the user did an update, but "another user" deleted the record :( .

Is there a timestamp field in the view? This sounds like an issue Access
has with timestamp precision, where if you supply a timestamp that is
too precise it has problems. See here for more detail:

/messages/by-id/A434C531E37AD442815608A769550D8059425A3E00@EGEXCMB01.oww.root.lcl

Short version, make your timestamp field timestamp(0).

Is there a more lightweight windows GUI client that does updates and
deletes on views?

afaik, pgAdmin doesn't allow it.

Cheers,

WBL

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No, there's no timestamp.
Here's a screenshot of the error in access 2010.
I'll have tot translate it, it's in dutch (and you can't change the
language like you can in ubuntu.. )
It says:
==write conflict==
As you were editing the record, it was changed by another user.
If you save the record, the changes by the other user will be overwritten.

If you want to see the changes made by the other user first, copy the
changes to the Clipboard. After that you can still paste your changes to
the record if you like.

Buttons:
Save record (disabled), Copy to Clipboard, Ignore Changes
--end of error message--

But the option to save the record is disabled, the other 2 buttons do not
enable me to save the record.

Anyway, i don't care much for MS Access, any client will do (that is, if it
costs money, it would be nice if most users own it already or it isn't very
expensive)
I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

Does anyone know of a client that is either smart enough to understand
about rules on views, or transparent enough to let the server handle
everything?

Cheers,

WBL

On Mon, Dec 9, 2013 at 4:16 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On 12/09/2013 05:36 AM, Willy-Bas Loos wrote:

I have a database with views that have rules on them, so that users can
do insert/update/delete on their part of the data.
The rules ore "do instead" rules that redirect the edits to the table
that actually holds the data, on which the users have no rights.

This works fine in MS Access (at least versions 2000 and 2010).

Now i've added some functionality, i've added a set of views that show
records that a user is entitled to insert, that she hasn't inserted. The
records in the view show the default values, they could be viewed as
"templates". To create the new record, all the user has to do is edit
the template record. It is then inserted into the corresponding table
and consequently disappears from the template view.

But Access 2010 detects the removal from this "template" view as a
conflict: the user did an update, but "another user" deleted the record
:( .

Is there a timestamp field in the view? This sounds like an issue Access
has with timestamp precision, where if you supply a timestamp that is too
precise it has problems. See here for more detail:

/messages/by-id/A434C531E37AD442815608A769550D
8059425A3E00@EGEXCMB01.oww.root.lcl

Short version, make your timestamp field timestamp(0).

Is there a more lightweight windows GUI client that does updates and
deletes on views?

afaik, pgAdmin doesn't allow it.

Cheers,

WBL

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

--
Adrian Klaver
adrian.klaver@gmail.com

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Willy-Bas Loos wrote on 09.12.2013 21:44:

But the option to save the record is disabled, the other 2 buttons do not enable me to save the record.
Anyway, i don't care much for MS Access, any client will do (that is, if it costs money,
it would be nice if most users own it already or it isn't very expensive)

I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

Does anyone know of a client that is either smart enough to understand about rules on views,
or transparent enough to let the server handle everything?

If pgAdmin is acceptable, you might want to try SQL Workbench/J: http://www.sql-workbench.net

It will allow to run inserts on anything that you can select from and it will prompt the user for the PK columns in case none could be identified in the database (such as when updating the result of a select based on a view or retrieving data from a table without a PK). The selection of the PK columns can be saved (locally) to avoid further prompting

Disclosure: I am the author of that tool.

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

cool, SQL Workbench/J: does the job.
It's too bad that it doesn't list the lables/views, so that you can't just
open them with a click (i use select * from table to get the data), but it
works well.

thanks Thomas.

On Mon, Dec 9, 2013 at 11:51 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:

Willy-Bas Loos wrote on 09.12.2013 21:44:

But the option to save the record is disabled, the other 2 buttons do not

enable me to save the record.
Anyway, i don't care much for MS Access, any client will do (that is, if
it costs money,
it would be nice if most users own it already or it isn't very expensive)

I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

Does anyone know of a client that is either smart enough to understand
about rules on views,
or transparent enough to let the server handle everything?

If pgAdmin is acceptable, you might want to try SQL Workbench/J:
http://www.sql-workbench.net

It will allow to run inserts on anything that you can select from and it
will prompt the user for the PK columns in case none could be identified in
the database (such as when updating the result of a select based on a view
or retrieving data from a table without a PK). The selection of the PK
columns can be saved (locally) to avoid further prompting

Disclosure: I am the author of that tool.

Regards

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Willy-Bas Loos, 10.12.2013 13:30:

cool, SQL Workbench/J: does the job.
It's too bad that it doesn't list the lables/views, so that you can't just open them with a click
(i use select * from table to get the data), but it works well.

Did you check the "Database Explorer"?

http://www.sql-workbench.net/table_definition_png.html

(Available in the Tools menu)

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Dec 9, 2013 at 7:16 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

Is there a timestamp field in the view? This sounds like an issue Access
has with timestamp precision, where if you supply a timestamp that is too
precise it has problems. See here for more detail:

Updateable view can be a challenge due to MS-Access Optimistic Locking
checks. First, for each row updated by Access, MS-Access checks that each
field is the same returning as what it issued - Any changes with throw a
roll-back. Next if the count of record changes does not match the count
that that Access expects, it will roll-back the changes.

--
Regards,
Richard Broersma Jr.

Hi,

2013-12-09 Thomas Kellerer <spam_eater@gmx.net>:

Willy-Bas Loos wrote on 09.12.2013 21:44:

I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

...

If pgAdmin is acceptable, you might want to try SQL Workbench/J:
http://www.sql-workbench.net

I'd like to revive that discussion and like to know:
Q1 => Can anybody recommend a >> desktop GUI tool (preferrably open
source) << for PG with customizable forms like Access or FileMaker?

I know this list [1]http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools - but either it's an admin tool (scary for users)
or it's an abandoned project/product.

My current short list is either pgAdmin, SQL Workbench/J or Postgres
Forms (altough abandoned?).
My question here is:
Q2 => Does it make sense to write a plugin for pgAdmin (in C/C++) or
SQL Workbench/J (Java) or Forms (Tkl)??

-- Stefan

[1]: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
[2]: http://pfm.projects.pgfoundry.org/

2013-12-09 Thomas Kellerer <spam_eater@gmx.net>:

Willy-Bas Loos wrote on 09.12.2013 21:44:

But the option to save the record is disabled, the other 2 buttons do not
enable me to save the record.
Anyway, i don't care much for MS Access, any client will do (that is, if
it costs money,
it would be nice if most users own it already or it isn't very expensive)

I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

Does anyone know of a client that is either smart enough to understand
about rules on views,
or transparent enough to let the server handle everything?

If pgAdmin is acceptable, you might want to try SQL Workbench/J:
http://www.sql-workbench.net

It will allow to run inserts on anything that you can select from and it
will prompt the user for the PK columns in case none could be identified in
the database (such as when updating the result of a select based on a view
or retrieving data from a table without a PK). The selection of the PK
columns can be saved (locally) to avoid further prompting

Disclosure: I am the author of that tool.

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Stefan Keller wrote on 08.02.2014 12:31:

If pgAdmin is acceptable, you might want to try SQL Workbench/J:
http://www.sql-workbench.net

I'd like to revive that discussion and like to know:
Q1 => Can anybody recommend a >> desktop GUI tool (preferrably open
source) << for PG with customizable forms like Access or FileMaker?

I know this list [1] - but either it's an admin tool (scary for users)
or it's an abandoned project/product.

My current short list is either pgAdmin, SQL Workbench/J or Postgres
Forms (altough abandoned?).
My question here is:
Q2 => Does it make sense to write a plugin for pgAdmin (in C/C++) or
SQL Workbench/J (Java) or Forms (Tkl)??

I can (obviously) only answer for SQL Workbench/J

SQL Workbench does not support plugins and it's primary target audience are developers or users comfortable using SQL.

I'm sorry to disappoint you, but I have no intention to make it more "end-user friendly" or turn it into a full blown "forms application".

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Why not use Access directly? You can connect to PostgreSQL though ODBC
(inside Access) and do the forms there. I am sure 95% that I had done that
in the past, although I cannot be sure it works with the most recent
versions.

Something like that:
http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php

--
View this message in context: http://postgresql.1045698.n5.nabble.com/client-that-supports-editing-views-tp5782418p5791070.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Thomas and Alexandros

Thanks for your quick replies!
If possible I'd prefer an open source framework.
I've actually found another possible solution candidate: Using Qt Designer [1]"Postmodern PostgreSQL Application Development" by David Sankel at PostgreSQL Conference East 08 http://netsuperbrain.com/postmodern-postgres.html.
I'm still evaluation and now keen if anybody stands up to vote for a
pgAdmin plugin...?

-- Stefan

[1]: "Postmodern PostgreSQL Application Development" by David Sankel at PostgreSQL Conference East 08 http://netsuperbrain.com/postmodern-postgres.html
PostgreSQL Conference East 08
http://netsuperbrain.com/postmodern-postgres.html

2014-02-08 alexandros_e <alexandros.ef@gmail.com>:

Why not use Access directly? You can connect to PostgreSQL though ODBC
(inside Access) and do the forms there. I am sure 95% that I had done that
in the past, although I cannot be sure it works with the most recent
versions.

Something like that:
http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php

--
View this message in context: http://postgresql.1045698.n5.nabble.com/client-that-supports-editing-views-tp5782418p5791070.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/08/2014 05:24 AM, Stefan Keller wrote:

Hi Thomas and Alexandros

Thanks for your quick replies!
If possible I'd prefer an open source framework.
I've actually found another possible solution candidate: Using Qt Designer [1].
I'm still evaluation and now keen if anybody stands up to vote for a
pgAdmin plugin...?

Not sure if this what you are looking for, but two suggestions:

1) Dabo (http://dabodev.com/) I have used this one.

2) Sqlkit (http://sqlkit.argolinux.org/) I have just experimented with this.

-- Stefan

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Q1 => Can anybody recommend a >> desktop GUI tool (preferrably open
source) << for PG with customizable forms like Access or FileMaker?

Without programming:

- LO/OO Base: www.libreoffice.org, www.openoffice.org
- Kexi: www.kexi-project.org
- Rekall is apparently dead, unfortunately.

Using Python:

With PyQt:
- Qtalchemy: www.qtalchemy.org
- Camelot: www.python-camelot.com
- Pypapi: pypi.python.org/pypi/PyPaPi/0.8
- Thyme: clocksoft.co.uk/downloads/

With PyGTK:
- Sqlkit: sqlkit.argolinux.org
- Kiwi: www.async.com.br/projects/kiwi
- Glom: www.glom.org
- Gemello: abu.sourceforge.net

With wxPython:
- Gui2Py: code.google.com/p/gui2py/
- Dabo: www.dabodev.com
- Defis: sourceforge.net/projects/defis (Russian only)
- GNUe: www.gnuenterprise.org

Sincerely,

Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Wolfgang

Thanks!

This is now my ranked shortlist which I will evaluate further:
1. Camelot: http://www.python-camelot.com - PyQt
2. Dabo: http://www.dabodev.com - wxPython
3. Gui2Py: http://code.google.com/p/gui2py/ - wxPython
4. Kiwi: http://www.async.com.br/projects/kiwi - PyGTK
5. Sqlkit: http://sqlkit.argolinux.org - PyGTK

All other projects seem to be abandoned or don't support Windows
(besides Linux and Mac).
It's obviously a question of PyQt vs. wxPython (or PyGTK).
And it's also a question of tool support meaning if its WYSIWIG (like
Access/FileMaker/Foxpro), script-based (e.g. XForms) or pure
programming based.

--S.

2014-02-08 Wolfgang Keller <feliphil@gmx.net>:

Q1 => Can anybody recommend a >> desktop GUI tool (preferrably open
source) << for PG with customizable forms like Access or FileMaker?

Without programming:

- LO/OO Base: www.libreoffice.org, www.openoffice.org
- Kexi: www.kexi-project.org
- Rekall is apparently dead, unfortunately.

Using Python:

With PyQt:
- Qtalchemy: www.qtalchemy.org
- Camelot: www.python-camelot.com
- Pypapi: pypi.python.org/pypi/PyPaPi/0.8
- Thyme: clocksoft.co.uk/downloads/

With PyGTK:
- Sqlkit: sqlkit.argolinux.org
- Kiwi: www.async.com.br/projects/kiwi
- Glom: www.glom.org
- Gemello: abu.sourceforge.net

With wxPython:
- Gui2Py: code.google.com/p/gui2py/
- Dabo: www.dabodev.com
- Defis: sourceforge.net/projects/defis (Russian only)
- GNUe: www.gnuenterprise.org

Sincerely,

Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is now my ranked shortlist which I will evaluate further:
1. Camelot: http://www.python-camelot.com - PyQt
2. Dabo: http://www.dabodev.com - wxPython
3. Gui2Py: http://code.google.com/p/gui2py/ - wxPython
4. Kiwi: http://www.async.com.br/projects/kiwi - PyGTK
5. Sqlkit: http://sqlkit.argolinux.org - PyGTK

All other projects seem to be abandoned or don't support Windows
(besides Linux and Mac).
It's obviously a question of PyQt vs. wxPython (or PyGTK).
And it's also a question of tool support meaning if its WYSIWIG (like
Access/FileMaker/Foxpro), script-based (e.g. XForms) or pure
programming based.

If I had the skills, I'd pick up Pypapi. It is in production use with
several italian municipalities, but the developer has switched to Java
for whatever reason.

Sincerely,

Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general