GUI queen needs database advice

Phishfry

Son of Beastie

Reaction score: 2,027
Messages: 4,993

I want to assemble a freestanding DB + GUI front end for experimentation.
20 years ago I made a Point Of Sale program using Microsoft Access and AccessBasic.
Looking for similar software in FreeBSD. Not wanting LibreOffice's Base but a freestanding DB.

I think I have found a solution but would like opinions.
postgres seems to appeal to me. So for a GUI we have pgadmin3 (somewhat outdated though).
All that seems kosher but I also see a phppgadmin port. What is the difference? Obviously it adds php support.
Do I need that? Will pgadmin suffice for application building?
The world has changed since Access Basic and web applications are the new norm.
Where should I go? For my personal web site server I currently use lighttpd on Linode and love it.
So should I locally serve up my http database application with lightttpd too?
Any advice is welcomed. Even better DB choices are OK. I like the postgres license.
 

Sevendogsbsd

Aspiring Daemon

Reaction score: 534
Messages: 988

Throwing some security stuff out there since that's my swim lane :cool:: is this for internal use only? Will the web app/pages be on the public Internet? If public, make sure whatever DB you choose is not on the same box as the web server. Web server in DMZ of course.

Pretty sure the DB admin apps you referenced are for just that: admin of the DB itself. As for an app, you will have to write code yourself: Java, php, etc. Will also need to design the DB accordingly based on your app requirements.

I am not in the web app dev business any longer - been out for well over a decade. Others can better advise you on the technologies.
 

obsigna

Daemon

Reaction score: 730
Messages: 1,135

I cannot give a conclusive answer since I am also looking for a good PostgreSQL-GUI. The problem is that pgAdmin3 is not only outdated, it does not work anymore reliably with recent PostgreSQL server versions. For example, when I open a database hosted by a PostgreSQL 11.2 server, I need to click away tons of error messages before I come to see the db structure, and when I want to open a table, another bunch of error messages about missing meta data fields are displayed.

Using pgAdmin3 with databases hosted on a PostgreSQL 9.6 server is fine, except that I see one message, that only upto 9.5 is supported

The successor is pgAdmin4, however, this is not in the ports, and while pre-compiled installation packages are provided for Linux, Windows and macOS, we need to try to get around with the sources, in case we want to have it on FreeBSD. I did not try this yet.

That said, pgAdmin4 is a totally different animal than ...3. Actually it provides a web interface to the databases, and the desktop version comes with its own web server in order to achieve this - quote from the documentation:
pgAdmin may be deployed as a desktop application by configuring the application to run in desktop mode and then utilising the desktop runtime to host the program on a supported Windows, Mac OS X or Linux installation.

The desktop runtime is a system-tray application that when launched, runs the pgAdmin server and launches a web browser to render the user interface. If additional instances of pgAdmin are launched, a new browser tab will be opened and be served by the existing instance of the server in order to minimise system resource utilisation. Clicking the icon in the system tray will present a menu offering options to open a new pgAdmin window, configure the runtime, view the server log and shut down the server.
 
OP
Phishfry

Phishfry

Son of Beastie

Reaction score: 2,027
Messages: 4,993

Sometimes I can't find what I need on Google until I ask correctly...
This search worked best: "MS Access for Linux"

obsigna I really appreciate the advice about pgAdmin3.
Seeing how postgresql11 is out and postgresql95 is needed for pgAdmin3 I will drop the GUI requirement for database admin.

For building applications I have might just start at Kexi or LibreOffice Base.
As for an app, you will have to write code yourself: Java, php, etc.
This is where I am now searching around.
AJAX seems to be well supported on lighttpd. Javascript has several IDE's I have found. Geany supports php.
That is my weak spot. Web languages. I want something as simple as my lighttpd webserver.
Perl, modPHP, FastCGI, AJAX.
So many buzz words and so little knowledge.
All I initially need is GUI text input boxes for the DB form and perhaps some dropdown dialog boxes for choices.
 

20-100-2fe

Well-Known Member

Reaction score: 362
Messages: 303

The current standard for web or enterprise application development is to create the user interface with a JavaScript-based framework (such as React or VueJS). The front-end uses REST APIs to communicate with a back-end server (written in PHP, Java, etc), which in turn will access your database.

The tool you use to interact with your database (pgAdmin, phpPgAdmin) is not important because you'll use it only for debugging purposes on your development workstation or test server. Database setup (and further evolutions) is simply done by feeding your DBMS command line client with a SQL script.

However, if you're looking for an equivalent of MS Access + VB, I imagine you plan to create a small application. Moreover, I understand from your initial post that you don't even seem to NEED a web application.

A good starting point would be to tell us what you want to do and in which context it will run. Depending on your requirements, you might realize macros in an OpenOffice spreadsheet would be enough, or that a Python application built on top of SQLite would best suit your needs than a web application.
 
OP
Phishfry

Phishfry

Son of Beastie

Reaction score: 2,027
Messages: 4,993

The desktop runtime is a system-tray application that when launched
This was where TCL/TK gave me fits.
I created a simple application using uname but how to make it a run time executable.
There is a method they call starkit/starpack which converts it into a freestanding application so tcl/tk is not required to run the app.
I could not find any "kits" in our ports system for tcl/tk.
 

obsigna

Daemon

Reaction score: 730
Messages: 1,135

When it comes to web application development you need to do client side programming, and this you would do using HTML5, CSS3, JavaScript and perhaps SVG (the latter for interactive graphics) -- and be assured, you want HTML, you want CSS, and you want JavaScript for the client code.

On the server side you need the web server, and I assume, lighttpd would be fine. AND, you need some programs which respond to requests from the client in a sensible way. For the time being, let's call these programs responders. The responders run on the server, and for this you may use any programming language which you are comfortable with and which can be plugged somehow into lighttpd - usually by CGI or FastCGI. While the requesters run in the client's web browser (e.g. JavaScript, and here you would do yourself a big favour to forget Java, Flash and similar stuff for the client).

So the client sends a request and the server shall respond. And here comes AJAX into play. Without AJAX, the web browser would simply refresh the whole web page, which is fine, if you got no interactive visualisation on the page. With AJAX your JavaScript code may send a single value together with a sort of an ID to the server, and the server could for example update only the respective field of your database and in addition respond with some other action, and that without needing to reload the page.
 

twllnbrck

Active Member

Reaction score: 171
Messages: 246

I cannot give a conclusive answer since I am also looking for a good PostgreSQL-GUI.
Im not an expert for databases and GUI admin tools but for PostgreSQL administration there is also databases/phppgadmin. And you can use Oracle's SQL Developer with the appropriate JDBC driver to connect with your Postgres DB. Oracle provides it as Freeware.
 

gpw928

Well-Known Member

Reaction score: 126
Messages: 370

I used to do this sort of stuff with Apache (HTML and CSS), perl CGI, and Oracle. But times have moved on, and what obsigna said is pretty much where it is today.

But the basic security stuff is still the same:
  • Understand what SQL injection and Cross Site Scripting (XSS) are, and how to defend against them. Compose and test attacks yourself, so you really do understand. Investigate why M$ SQL autocomplete is an XSS hacker's delight.
  • Take advice on the network design. Leave room for a load balancer. Design the HTTPS termination point with fore-thought. Locate your database server as far as possible from the Internet.
  • Never leave financial or personal data in the DMZ for a microsecond longer than they need to be there. Encrypt and ship to a safe place immediately. Manage the keys actively. Obliterate passwords, keys, and personal data as soon as they no longer required.
 
OP
Phishfry

Phishfry

Son of Beastie

Reaction score: 2,027
Messages: 4,993

A good starting point would be to tell us what you want to do and in which context it will run.
I am going to start with a 'contacts' database for my "Hello World".
First Name
Last Name
Street Address
City
State
Zip Code

Using the 'State' field for my dropdown dialog box test.

I setup postgresql11_5 last night and now comes the hard part. GUI design.
This database will only be available on my LAN, no outside worries.
I did setup pg_hba.conf to allow 192.168.0.0/24 clients.
 

olli@

Aspiring Daemon
Developer

Reaction score: 848
Messages: 802

I just wanted to mention that I think that Postgres is an excellent choice. I also use Postgres wherever possible, for about 20 years. It's very reliable and robust (nearly impossible to destroy or lose a database by accident), yet has very good performance and one of the best query optimizers on the market.

As for the GUI admin interface, I'm afraid I can't help you there. I prefer to administrate my DBs via CLI.
For the actual DB applications (whether web or not), my language of choice is Python. But that's a different story and wasn't the question here.
 

gpw928

Well-Known Member

Reaction score: 126
Messages: 370

That sounds… nonsensical. Where can I learn more about this?
Sorry I chose my words sloppily. It's not "auto-complete", it's the '--' in-line comment operator that permits SQL syntax errors to be ignored by the SQL compiler.
 

unitrunker

Aspiring Daemon

Reaction score: 204
Messages: 500

Here's what I'm currently playing with ...

1. pgsql as the database (unix socket - no TCP).
2. stored procedures for everything.
3. pgsql access model prevents adhoc queries from the application.
4. light fastcgi wrapper in C++ to call above stored procedures (on "lo" interface).
5. #4 runs as an user permitted to call above stored procs.
6. nginx as the external facing "attack surface" - nginx is well understood and easy to configure.

I end up with a bunch of REST calls that I can easily test using a tool like Postman. That's when the fun starts with html/css/javascript - all served up by nginx.
 

kpedersen

Daemon

Reaction score: 1,110
Messages: 2,106

You mentioned Kexi or LibreOffice Base so you aren't tied to a web page for the GUI? If it is a POS system then that makes sense.

Any specific languages you are most proficient with? If it is C++ or Python, then wxWidgets is nice. If it is Java then the classic javax.swing is OK.

Postgres is good but I have only used it from the CLI; if you want a UI for modifying it, then you could consider using MariaDB *just* because of phpmyadmin being quite good.

A company I worked for used this: https://www.navicat.com/en/products/navicat-for-postgresql
But unfortunately it is proprietary. However their Linux port was just a wine wrapper. I actually made a FreeBSD port for them but I don't know if they maintained it or not.

If the application itself is web based; I am not great in that area. Drag in a bunch of Javascript frameworks seems to be a common strategy XD.
 
OP
Phishfry

Phishfry

Son of Beastie

Reaction score: 2,027
Messages: 4,993

Why web GUI and not Qt/GTK/etc.?
No particular reason. I thought web 2.0 would be simpler. Obviously I was wrong.
I might try tcl/tk with postgres. I have been buffing up on that.
so you aren't tied to a web page for the GUI? If it is a POS system then that makes sense.
No this is just an excuse for learning. My POS system was for a now defunct dive shop.
Its not like I could re-use any of that code.
 

20-100-2fe

Well-Known Member

Reaction score: 362
Messages: 303

No this is just an excuse for learning.
You don't need an excuse to learn, only a purpose. ;)
Depending on what you want to do next, you'll easily find out what to learn first.
If your purpose is to work as a developer, web technologies are appropriate.
If your purpose is to contribute to an open-source project, the project you'll choose will dictate what you'll need to learn.
 

Sevendogsbsd

Aspiring Daemon

Reaction score: 534
Messages: 988

Sorry I chose my words sloppily. It's not "auto-complete", it's the '--' in-line comment operator that permits SQL syntax errors to be ignored by the SQL compiler.
And it's not XSS, it's SQL injection.
 

shkhln

Daemon

Reaction score: 688
Messages: 1,678

Indeed, XSS usually refers to the improperly escaped/encoded HTML output. Assuming it is potentially exploitable, of course. (By the way, where do I report one? I don't want to talk to XenForo myself.)
 

Sevendogsbsd

Aspiring Daemon

Reaction score: 534
Messages: 988

Exactly - normally involves Javascript, thus the term Cross-site-"scripting"...SQL Injection is entirely different.
 

roccobaroccoSC

Aspiring Daemon

Reaction score: 165
Messages: 651

I want to assemble a freestanding DB + GUI front end for experimentation.
20 years ago I made a Point Of Sale program using Microsoft Access and AccessBasic.
Looking for similar software in FreeBSD. Not wanting LibreOffice's Base but a freestanding DB.

I think I have found a solution but would like opinions.
postgres seems to appeal to me. So for a GUI we have pgadmin3 (somewhat outdated though).
All that seems kosher but I also see a phppgadmin port. What is the difference? Obviously it adds php support.
Do I need that? Will pgadmin suffice for application building?
The world has changed since Access Basic and web applications are the new norm.
Where should I go? For my personal web site server I currently use lighttpd on Linode and love it.
So should I locally serve up my http database application with lightttpd too?
Any advice is welcomed. Even better DB choices are OK. I like the postgres license.
I personally use MySQL. It's quite easy to setup on FreeBSD.
If you need a GUI for management, you can install an Apache web server with PHPMyAdmin app. All of them are available as packages.

It goes something like this:
Bash:
pgk install mariadb104-server apache24 phpMyAdmin-php74
sysrc mysql_enable="YES"
sysrc apache24_enable="YES"
service start mysql
service start apache24
mysql_secure_installation

firefox 'http://localhost/phpmyadmin'
For sure, you could also go SSL with the Apache, you need to create certificates and keys with openssl if you want to, but if your firewall stops traffic from outside, your webserver should be secure.
 

obsigna

Daemon

Reaction score: 730
Messages: 1,135

Inspired by this thread, I found yesterday in the afternoon databases/kexi.
Kexi is an open source visual database applications creator,
a long-awaited competitor for programs like MS Access
or Filemaker.

WWW: http://www.kexi-project.org/
Somewhere I read, that it can deal with MySQL, PostgreSQL and SQLITE3 backends, so I installed it on a system, in order to see whether this can be used as a GUI client for the said database backends. It cannot, it uses the backends for its own storage necessities. So, Kexi won’t serve my purposes. However, in case I understood the objectives of Phishfry correctly, then Kexi might be exactly what he’s looking for, namely a Visual Database Applications Creator. I’m not quite impressed about MS Access, however, if Kexi can really do about 75 % of what FileMaker can do, then this is something.

Building from the ports took it’s time, though, 3 h on a Quad-i7@4,2 GHz. If there were a competition among open source projects which drag-in the most dependencies, then Kexi would for sure win a trophy.
 
Last edited:
Top