Обсуждение: 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

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:

http://www.postgresql.org/message-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

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
casenone could be identified in the database (such as when updating the result of a select based on a view or
retrievingdata 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

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

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] - 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

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
"formsapplication". 

Regards
Thomas

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.

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...?

-- Stefan

[1] "Postmodern PostgreSQL Application Development" by David Sankel at
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

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

> 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

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

> 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