Re: [sqlite] SQLite Application Question

[R Smith]

On Thu, Dec 20, 2018 at 9:05 AM Roger Schlueter  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

Re: [sqlite] SQLite Application Question

[Chris Locke]

I tend to have a class at the table layer, so essentially have a 'settings'
class (matching the 'settings' table).  My form then (hard coded) grabs the
settings it needs.  I'd store the location as x,y (so 300,900 for example)
and size (so 1000,800 for example).  The class handles grabbing records,
editing as required, and saving back.  I don't tend to 'bind' as such.  No
pun, but I'm more of a basic VB programmer.  So in the form_load, create a
class which points to the settings table.  Either explicitly grab the 'main
form/size' setting (eg, "1000,800") break that down and apply as required,
or grab a bunch of 'main form/*' settings.  I can then pull out the size,
height, state, etc, records from that recordset (ie, keep it to one
database query).
Things get 'messy' when saving back.  Clean, but messy.  So to save the
position, I have to find the explicit 'main form/position' record again
(one query), edit it, and save it back (another query).  Due to the class,
there isn't much code to achieve that.
I use winForms, not wpf, but I assume (ignorantly) that the process is the
same.
Just to take this back on topic, SQLite is fabulous for this type of
usage.  A query takes milliseconds and a database can hold a whole manner
of settings, configurations, etc.  File size is small and efficient too.

Thanks,
Chris


On Thu, Dec 20, 2018 at 9:05 AM Roger Schlueter  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.
>
> >
> > My settings table is a lot simpler.  id, setting and value.  3 columns.
> > Possibly 4, adding a 'code' column. The 'setting' column holds the full
> > setting you want to store, eg, 'main form height', or 'main form
> > windowstate'.  I can have user settings in this via 'chris/main form
> > height'.  I can then store that setting name as a constant in my
> > application, so its accessible via Intellisense.  Doing a series of quick
> > database lookups is relatively cheap.  You can also group the settings if
> > need be, so 'main form/height' and 'main form/windowstate' so you could
> > pull out a group of settings with one database query.
> > Happy to link you to a sample if needed.  A simple (although bloaty!)
> > database class can be used for the mundane database work - reading,
> > creating, editing and deleting records.  I tend to ensure my databases
> have
> > unique rowIds, and use these for the glue for relationships.
>
> Hey, you're ahead of me so let me ask for a few more details.  I
> contemplate bringing
> the data from the db into a class for each window that I can bind
> the values to.  That way
> the only other code necessary is to push altered any values back to
> the db when the window
> closes.  In your setup, then, how do you tell the "main form" that
> the binding is on the
> height property.  Or do you just assign the value in the loaded
> event and keep track of
> changes in your own code?
>
> Thanks a lot for your time; I'm stretching to put this together ...
> which is part of the fun.
>
> > Thanks,
> > Chris
> >
> > On Thu, Dec 20, 2018 at 3:37 AM Roger Schlueter  wrote:
> >
> >> On 12/19/2018 10:02, Jens Alfke wrote:
>  On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> 
>  I am starting work on a prototype application so this might be an
> >> excellent opportunity to use SQLite for my application file format.
> Part
> >> of this would be the saving and restoring of GUI elements such as window
> >> positions and sizes, control states, themes, etc.
> >>> IMHO something like JSON is a good format for such config/preference
> >> data, instead of having a table with a column for every pref. During
> >> development you’ll often be adding new prefs, and it’s a pain to have to
> >> update a CREATE TABLE statement every time you add one. It’s even more
> of a
> >> pain to have to handle a schema change with ALTER TABLE in an app
> upgrade
> >> that adds a new pref. If you use JSON you just have to come up with a
> new
> >> string to use as the key for each pref. It’s also easy to have
> structured
> >> values like arrays or nested objects. (FWIW, his is essentially the way
> >> that Apple OS’s manage app prefs via the NSUserDefaults class.)
> >>
> >>  JSON or XML: Two sides of the same coin.  If I wanted to go the
> >>  separate file approach, I'd just use the settings class of Visual
> >>  Studio since all the required plumbing is already in place.
> >>
> >>  More importantly, as I noted this is a prototype (read: test)
> >>  application so it is a good opportunity for me to get my feet wet

Re: [sqlite] SQLite Application Question

[Roger Schlueter]

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.



My settings table is a lot simpler.  id, setting and value.  3 columns.
Possibly 4, adding a 'code' column. The 'setting' column holds the full
setting you want to store, eg, 'main form height', or 'main form
windowstate'.  I can have user settings in this via 'chris/main form
height'.  I can then store that setting name as a constant in my
application, so its accessible via Intellisense.  Doing a series of quick
database lookups is relatively cheap.  You can also group the settings if
need be, so 'main form/height' and 'main form/windowstate' so you could
pull out a group of settings with one database query.
Happy to link you to a sample if needed.  A simple (although bloaty!)
database class can be used for the mundane database work - reading,
creating, editing and deleting records.  I tend to ensure my databases have
unique rowIds, and use these for the glue for relationships.


   Hey, you're ahead of me so let me ask for a few more details.  I
   contemplate bringing
   the data from the db into a class for each window that I can bind
   the values to.  That way
   the only other code necessary is to push altered any values back to
   the db when the window
   closes.  In your setup, then, how do you tell the "main form" that
   the binding is on the
   height property.  Or do you just assign the value in the loaded
   event and keep track of
   changes in your own code?

   Thanks a lot for your time; I'm stretching to put this together ...
   which is part of the fun.


Thanks,
Chris

On Thu, Dec 20, 2018 at 3:37 AM Roger Schlueter  wrote:


On 12/19/2018 10:02, Jens Alfke wrote:

On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:

I am starting work on a prototype application so this might be an

excellent opportunity to use SQLite for my application file format.  Part
of this would be the saving and restoring of GUI elements such as window
positions and sizes, control states, themes, etc.

IMHO something like JSON is a good format for such config/preference

data, instead of having a table with a column for every pref. During
development you’ll often be adding new prefs, and it’s a pain to have to
update a CREATE TABLE statement every time you add one. It’s even more of a
pain to have to handle a schema change with ALTER TABLE in an app upgrade
that adds a new pref. If you use JSON you just have to come up with a new
string to use as the key for each pref. It’s also easy to have structured
values like arrays or nested objects. (FWIW, his is essentially the way
that Apple OS’s manage app prefs via the NSUserDefaults class.)

 JSON or XML: Two sides of the same coin.  If I wanted to go the
 separate file approach, I'd just use the settings class of Visual
 Studio since all the required plumbing is already in place.

 More importantly, as I noted this is a prototype (read: test)
 application so it is a good opportunity for me to get my feet wet
 with SQLite since I'm a n00b with it.


Of course you can save the JSON in the database file. Just create a

‘prefs’ table with one blob column for the JSON.

A related solution is to store each named pref as a row in the ‘prefs’

table, identified by a ‘key’ column.

 In fact, this statement makes the concerns you raised in the first
 paragraph moot.  A simple table with four columns:

  1. Window name
  2. Control name
  3. Control property
  4. Property value

 covers all the possibilities, no ALTER table necessary.  If I want
 to enable per user values, I'd just add a User column.

In short, the design part is easy IMO.  I'm still hoping to see some
examples since, surely, I'm not the first person to go this route.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Chris Locke]

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?

My settings table is a lot simpler.  id, setting and value.  3 columns.
Possibly 4, adding a 'code' column. The 'setting' column holds the full
setting you want to store, eg, 'main form height', or 'main form
windowstate'.  I can have user settings in this via 'chris/main form
height'.  I can then store that setting name as a constant in my
application, so its accessible via Intellisense.  Doing a series of quick
database lookups is relatively cheap.  You can also group the settings if
need be, so 'main form/height' and 'main form/windowstate' so you could
pull out a group of settings with one database query.
Happy to link you to a sample if needed.  A simple (although bloaty!)
database class can be used for the mundane database work - reading,
creating, editing and deleting records.  I tend to ensure my databases have
unique rowIds, and use these for the glue for relationships.


Thanks,
Chris

On Thu, Dec 20, 2018 at 3:37 AM Roger Schlueter  wrote:

> On 12/19/2018 10:02, Jens Alfke wrote:
> >> On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> >>
> >> I am starting work on a prototype application so this might be an
> excellent opportunity to use SQLite for my application file format.  Part
> of this would be the saving and restoring of GUI elements such as window
> positions and sizes, control states, themes, etc.
> > IMHO something like JSON is a good format for such config/preference
> data, instead of having a table with a column for every pref. During
> development you’ll often be adding new prefs, and it’s a pain to have to
> update a CREATE TABLE statement every time you add one. It’s even more of a
> pain to have to handle a schema change with ALTER TABLE in an app upgrade
> that adds a new pref. If you use JSON you just have to come up with a new
> string to use as the key for each pref. It’s also easy to have structured
> values like arrays or nested objects. (FWIW, his is essentially the way
> that Apple OS’s manage app prefs via the NSUserDefaults class.)
>
> JSON or XML: Two sides of the same coin.  If I wanted to go the
> separate file approach, I'd just use the settings class of Visual
> Studio since all the required plumbing is already in place.
>
> More importantly, as I noted this is a prototype (read: test)
> application so it is a good opportunity for me to get my feet wet
> with SQLite since I'm a n00b with it.
>
> > Of course you can save the JSON in the database file. Just create a
> ‘prefs’ table with one blob column for the JSON.
> >
> > A related solution is to store each named pref as a row in the ‘prefs’
> table, identified by a ‘key’ column.
>
> In fact, this statement makes the concerns you raised in the first
> paragraph moot.  A simple table with four columns:
>
>  1. Window name
>  2. Control name
>  3. Control property
>  4. Property value
>
> covers all the possibilities, no ALTER table necessary.  If I want
> to enable per user values, I'd just add a User column.
>
> In short, the design part is easy IMO.  I'm still hoping to see some
> examples since, surely, I'm not the first person to go this route.
> >
> > —Jens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Roger Schlueter]

On 12/19/2018 10:02, Jens Alfke wrote:

On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:

I am starting work on a prototype application so this might be an excellent 
opportunity to use SQLite for my application file format.  Part of this would 
be the saving and restoring of GUI elements such as window positions and sizes, 
control states, themes, etc.

IMHO something like JSON is a good format for such config/preference data, 
instead of having a table with a column for every pref. During development 
you’ll often be adding new prefs, and it’s a pain to have to update a CREATE 
TABLE statement every time you add one. It’s even more of a pain to have to 
handle a schema change with ALTER TABLE in an app upgrade that adds a new pref. 
If you use JSON you just have to come up with a new string to use as the key 
for each pref. It’s also easy to have structured values like arrays or nested 
objects. (FWIW, his is essentially the way that Apple OS’s manage app prefs via 
the NSUserDefaults class.)


   JSON or XML: Two sides of the same coin.  If I wanted to go the
   separate file approach, I'd just use the settings class of Visual
   Studio since all the required plumbing is already in place.

   More importantly, as I noted this is a prototype (read: test)
   application so it is a good opportunity for me to get my feet wet
   with SQLite since I'm a n00b with it.


Of course you can save the JSON in the database file. Just create a ‘prefs’ 
table with one blob column for the JSON.

A related solution is to store each named pref as a row in the ‘prefs’ table, 
identified by a ‘key’ column.


   In fact, this statement makes the concerns you raised in the first
   paragraph moot.  A simple table with four columns:

1. Window name
2. Control name
3. Control property
4. Property value

   covers all the possibilities, no ALTER table necessary.  If I want
   to enable per user values, I'd just add a User column.

In short, the design part is easy IMO.  I'm still hoping to see some 
examples since, surely, I'm not the first person to go this route.


—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Simon Slavin]

On 19 Dec 2018, at 6:02pm, Jens Alfke  wrote:

> Of course you can save the JSON in the database file. Just create a ‘prefs’ 
> table with one blob column for the JSON.
> 
> A related solution is to store each named pref as a row in the ‘prefs’ table, 
> identified by a ‘key’ column.

Or you could use SQLite's JSON Extension:



Settings and preferences do tend to lend themselves to a nesting JSON 
dictionary.  I've never tried using SQLite for that, through I stopped writing 
that sort of software years ago.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Jens Alfke]

> On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> 
> I am starting work on a prototype application so this might be an excellent 
> opportunity to use SQLite for my application file format.  Part of this would 
> be the saving and restoring of GUI elements such as window positions and 
> sizes, control states, themes, etc.

IMHO something like JSON is a good format for such config/preference data, 
instead of having a table with a column for every pref. During development 
you’ll often be adding new prefs, and it’s a pain to have to update a CREATE 
TABLE statement every time you add one. It’s even more of a pain to have to 
handle a schema change with ALTER TABLE in an app upgrade that adds a new pref. 
If you use JSON you just have to come up with a new string to use as the key 
for each pref. It’s also easy to have structured values like arrays or nested 
objects. (FWIW, his is essentially the way that Apple OS’s manage app prefs via 
the NSUserDefaults class.)

Of course you can save the JSON in the database file. Just create a ‘prefs’ 
table with one blob column for the JSON.

A related solution is to store each named pref as a row in the ‘prefs’ table, 
identified by a ‘key’ column.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Application Question

[Roger Schlueter]

I am starting work on a prototype application so this might be an 
excellent opportunity to use SQLite for my application file format.  
Part of this would be the saving and restoring of GUI elements such as 
window positions and sizes, control states, themes, etc.  I can conceive 
of a few different approaches to this but instead of reinventing the 
wheel, I decided to ask this list if you know of examples of SQLite 
usage for this specific purpose.  Of course, it would be nice if the db 
and associated application code were available for inspection.


Thanks in advance.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users