Postgresql support

[jeffk]

I'm casually referring some dental practices to the open dental product, because I fell that this approach is likely to result in a superior product to proprietary packages.

This may be a long-settled issue, but since the term doesn't show up in the forum search, I'll raise it here.

The choice of MySQL as the backend database is something of an impediment to recommending it to my clients. I have much greater trust in PostgreSQL, and for my part, I would prefer to use that database if deploying major systems such as practice management.

Postgreql is well supported on .Net. What are the prospects of Open Dental using an database abstraction layer that would allow PostgreSQL or MySQL as chosen by the system integrator?

Thanks.

[jordansparks]

Well, we added support for Oracle last year, and it was painful to say the least. Many inconsistencies between the way the two databases handle things. What we've ended up with is an annoying situation where every query we use must be thought about in terms of Oracle. So we frequently have:
if(Oracle){
...
}else{
...
}
To support PostgreSql, we'd be increasing this complexity. Honestly, if I was going to pick another database to support, I would pick MS-Sql before PostgreSql. Is it worth it? I don't think so. We have one group of clinics using Oracle, and they run into all kinds of bugs every time they upgrade. How are we supposed to thoroughly test on 3 different databases? I just don't see the point.

[jeffk]

Hi Jordan, and thanks for responding.

Yes, I can agree wholeheartedly agree that supporting additional database backends with handwritten SQL would be tedious and error-prone. I wouldn't ever suggest going down that road, even to support Oracle alongside MySQL.

I don't have much information on the Open Dental program architecture at this stage.

My question was more along the lines of a database-independent data access and abstraction layer. Last time I programmed in C#/.Net, the conventional choice was ADO.net, it probably is still the most popular library. On Python these days, I use an outstanding library called SQLAlchemy.

Except for special case queries, these libraries generally work transparently to emit backend-specific SQL for everything from sqlite up through MySQL, PostgreSQL, and proprietary systems like MSSQL and Oracle.

I wouldn't personally have any interest in support for additional *proprietary* databases, although some users might have their reasons. I'm interested in zeroing out the infrastructure and server maintenance costs for my clients. For that reason, I would be very interested in PostgreSQL with Open Dental. I won't get into comparisons, leave that to the PostgreSQL and MySQL communities. I'll just say that I don't mind supporting PostgreSQL application deployments

Just to cut to the core issue: I would like to recommend a package like Open Dental to the practices I encounter, with the expectation that they pay/contribute something for the supported program, somewhat less than their current proprietary practice management packages.

As their systems integrator and local system and network support contact, I'm most interested in free and best-option infrastructure. For me, that would mean a Linux server, the PostgreSQL database, and a (*) GUI that runs on Microsoft's .Net and Mono (the open-source .Net implementation that runs on Windows and Linux servers and desktops).

It looks like Open Dental could get there in a future revision, and I'd love to see that happen.

A system that meets most of those criteria, and saves my clients some money over proprietary systems gets a big enthusiastic recommendation from me. The timing is good, because the proprietary systems were installed about six years ago, and the vendor's version upgrade is going to cost a lot of money.

(* : "Firefox-compatible AJAX WebUI and/or " , as long as we're listing wished-for Open Dental ponies)

[jordansparks]

Bottom line is that it's cheaper for us to target and support one database. Much cheaper.

[mnpbds]

FWIW I had a reverse desire some years ago to convert Odontolinux from using PostgreSQL to MySQL. This was PHP based and the code to do this ended up something like

Code: Select all

<?php
/* Set variables to enable database independence */
$dbtype = "mysql";
$connfunc = "${dbtype}_connect";
$selectfunc = "${dbtype}_select_db";
$queryfunc = "${dbtype}_query";
  $fetchfunc = "${dbtype}_fetch_array";
  $numrowsfunc = "${dbtype}_num_rows";
  $freefunc = "${dbtype}_free_result";
  $closefunc = "${dbtype}_close";
  $errnofunc = "${dbtype}_errno";
  $errorfunc = "${dbtype}_error";

  $dbname = "mpreston";
  $dbusername = "mpreston";
  $dbpassword = "401hjkl";
  $dbhost = "mysql";
  
  $dbh = $connfunc ($dbhost, $dbusername, $dbpassword);
  if (!$dbh) {
    die(sprintf("Cannot connect to $dbtype, %s: %s", 
        $errnofunc(), $errorfunc()));
  }

#Select the database
$db = @$selectfunc($dbname, $dbh)
      or die("Could not select database");

echo "Hi, it worked! ";
?>

This should be easier to manage than if... else... type changes.
Regards,
Mark