DB DEFAULT VALUES

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
fdscadmin
Posts: 59
Joined: Thu Aug 09, 2007 2:33 pm

DB DEFAULT VALUES

Post by fdscadmin » Mon Jun 14, 2021 9:41 am

Is there any particular reason that many of the tables do not have default values set for all the columns? I'm just wondering if this is by design or mostly because they weren't setup when the DB was designed. I'm working on some integration stuff and having to address every column is cumbersome. I'd like to set default values for tables I'm interacting with, but I thought I'd get some feedback here first.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: DB DEFAULT VALUES

Post by jordansparks » Mon Jun 14, 2021 9:57 am

We have a CRUD layer that we generated that automatically handles all columns for every INSERT and UPDATE, so we don't have to worry about it. But nobody has to worry about it for SELECT queries in any case. That makes me suspect that you are considering doing INSERTS and/or UPDATES. That's not allowed. It's hugely dangerous, with default values being just one of the many many things that could go wrong and cause corruption. We will also be tracking changes that our code didn't make and we will be gradually turning on countermeasures to prevent changes. So beware!
Have you seen our API? It is under very active development, with lots of methods being regularly added. I don't know what kind of integration you are working on, but we will soon be adding methods for payments.
Jordan Sparks, DMD
http://www.opendental.com

fdscadmin
Posts: 59
Joined: Thu Aug 09, 2007 2:33 pm

Re: DB DEFAULT VALUES

Post by fdscadmin » Mon Jun 14, 2021 10:42 am

Ok that makes more sense if you have your own logic on the application side. Yes I've browsed the API but it doesn't have the features we need yet. We are dealing with some unique scenarios of importing 835 files. I didn't know INSERTS and UPDATES were considered dangerous to the application....I'll look at other options.

fdscadmin
Posts: 59
Joined: Thu Aug 09, 2007 2:33 pm

Re: DB DEFAULT VALUES

Post by fdscadmin » Mon Jun 14, 2021 12:05 pm

Sooo was looking at the Plugin option thinking we could use the DB interface already built by OpenDent, but it also looks like that is frowned up also? So if I'm understanding this correctly the only supported way to update the DB is through the API being developed? It also looks like there will be a pop-up warning that will bug users if the DB has been altered...will we be able to disable this? I've been rolling versions of OpenDent with custom features for over 10 years depending on features that are available (or not) depending on the situation. It's actually a strength of OpenDent. This new "feature" seems like a hindrance. Interacting with the database decoupled from the main application is a huge plus, and this would make that difficult.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: DB DEFAULT VALUES

Post by jordansparks » Mon Jun 14, 2021 12:54 pm

Our goal is to make the API so easy and so loaded with features that nobody will ever want to bother again with plugins. But I also don't see us removing plugins in the short term. We are very rapidly adding API methods. We will add any methods that you need. I'll add 835 imports to our todo list, but I might return here with questions about tables and fields that you need.
Jordan Sparks, DMD
http://www.opendental.com

fdscadmin
Posts: 59
Joined: Thu Aug 09, 2007 2:33 pm

Re: DB DEFAULT VALUES

Post by fdscadmin » Mon Jun 14, 2021 3:39 pm

Sounds good to me...in the interim (and to avoid direct altering of data in tables) is it recommended to add custom tables to existing db or add another db entirely?

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: DB DEFAULT VALUES

Post by jordansparks » Mon Jun 14, 2021 11:17 pm

Adding custom tables will always be totally fine. Just name them with a prefix that we would never accidentally use. I would throw in an underscore for good luck.
Jordan Sparks, DMD
http://www.opendental.com

Post Reply