On Thu, Dec 20, 2018 at 9:05 AM Roger Schlueter <se...@cox.net> wrote:
On 12/19/2018 23:01, Chris Locke wrote:
What application are you using to build your application? You mentioned
Visual Studio, so .NET? If so, are you using the SQLite library from
system.data.sqlite.org? Are you using c# or vb?
Yes. .NET via vb using wpf.
I plan to use the library. Right now I'm in the contemplation
stage. :-) Once I
settle on my approach to this issue, I'll start the coding.
While other methods have merit - It's always a good idea to keep to 1NF
when designing data structures. Inside an application file format
special care must be taken to ensure future proofing and an easy way to
preserve backward compatibility lest your app becomes a famous one with
millions of uses and evolves over time to add more and more functionality.
Sticking to specifically the "settings" bit - I'll try point out some
advantages, but first, how /might/ such a table look:
CREATE TABLE settings (
Kind TEXT COLLATE NOCASE NOT NULL,
Name TEXT COLLATE NOCASE NOT NULL,
Property TEXT COLLATE NOCASE NOT NULL,
Value
PRIMARY KEY (Kind, Name, Property)
);
Now a typical entry (taking from what you have given so far) might be:
Kind = 'Window'
Name = 'MyMainWindow'
Property = 'Left'
Value = 150
or to make it look table-ish (with some added things for clarity):
| Kind | Name | Property | Value |
--------+--------------+-------------+------------+
Window | MyMainWIndow | Left | 150 |
Window | MyMainWIndow | Top | 50 |
Window | MyMainWIndow | Width | 640 |
Window | MyMainWIndow | Height | 480 |
Window | MyMainWIndow | IsMaximized | 0 |
Document| MetaData | Author | Joe Smith |
Document| MetaData | CreateDate | 2018-12-22 |
etc.
Note first the absence of "User" - this is actually a good idea in a
central RDBMS, but as an application file format, you have to ask
yourself, do you wish to carry around all settings for all users that
ever touch the app file? I'm hoping not. The file format should ONLY
contain information specifically relevant to THAT application
document/entity.
Also note, following this method, how easy it would be to edit/debug the
DB values using any DB manager. If I may suggest, for this very reason -
Do not use codes, use names. The days when we needed to save every byte
is long gone unless your application intends to store millions of lines
or is embedded with a specifically small footprint.
Reading properties are as simple as "SELECT Value FROM settings WHERE
Kind='Window' AND Name = :name AND Property = 'Left';
- OR, if you use any modern tools like VB/.Net/Python/etc. that can read
key-value pair lists, you can directly populate a "property-list", ".inf
list" or key-value list like so: -
SELECT Property||'='||Value FROM settings WHERE Kind = 'Window' AND Name
= :name;
which will make a whole group of properties handy to your object in one go.
Next, your properties reader object should be resilient to missing data
- this makes it immediately backwards compatible, and future proof.
If you next year decide to add a custom frame colour property for your
windows, now you can simply add "Frame_Color" as another Property. This
simply inserts another line in the table. You don't have to change the
table layout, no new columns that might not exist in previous versions,
no missing columns causing errors when read by newer versions of your
app, just a line that will show up in a query and can be ignored if not
needed, and can be defaulted if needed but not present. The DB structure
remains the same.
This also cuts down on any DB upgrade/maintenance between versions.
SQLite is just specifically brilliant for this purpose - bringing all
these database abilities directly to your application without the need
of connecting to a server. (I almost wonder how anyone could use
anything else as an application file format.)
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
