Singleton Table

Posts   
 
    
greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 12-Jun-2012 19:42:49   

Hi,

I need to create a single row table that will hold some configuration parameters for the system. What is the best way to make a constraint (on the database) so that LLBLGen will recognize it as a singleton?

LLBLGen v3.1 Final Sept 30th, 2011

Thanks!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Jun-2012 22:28:59   

There is no constrain that can enforce this on the database level. You can have this built in your application logic.

Or better have configurations in a config. file. Database is not the ideal place for it.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Jun-2012 09:52:41   

The only way I can think of is by using a known PK value in your application, and store a row in a table with that PK value as PK so when retrieving the entity, you always use the known PK value, so you're always working with the same entity instance. But as walaa said, you can't force the DB to not insert more rows by accident.

Frans Bouma | Lead developer LLBLGen Pro
greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 13-Jun-2012 21:12:59   

Thanks for the response.

The "configuration" data I have in this singleton table is a few things: 1.) major/minor/build/release version of my database. We use this for our database update utility to step-by-step, walk the database admin to the current version. We really need to keep this in the database, as it's the key to running a structural update on tables of the database. 2.) Some global settings that are configurable via a customer accessible web-interface (a person who's not a system admin...so they don't have access to the web.config file). These are also one-per-system.

I could have gone the other direction: Make Name/Value columns in the table, then fish out the values by name. But that feels looser than a singleton table. To tighten that (in the database), i might would have a unique key on "name", and a trigger to check that it's a valid name (putting all valid names in the trigger).

But either way, I'd have to modify the database for any new config value...so I want to go with adding a new column (when add a new config value)...and making it a singleton row.

I believe I can do as you say and make a "lock" column with a known value (like an "x" in the lock column), making a unique constraint on "lock", and a "must be value 'x'" constraint.

But I'm gathering in LLBLGen, there's no recognition of a database table singleton. No big deal...just an extra line or couple of code to make it solid using the GREAT llblgen generated code. (llblgen is such a great tool!!!)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Jun-2012 09:14:24   

smile

But, I wouldn't go for row locking. I'd go for the approach to offer the data to an application in such a way that it can only see 1 row, as it only knows the id of 1 row: it can only work with that one row. Even if there are 10 rows in that table, that instance only knows e.g. ID==3, so it will only load row with ID 3.

Frans Bouma | Lead developer LLBLGen Pro