SQLite Application Question

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Question

Jens Alfke-2


> On Dec 18, 2018, at 7:46 PM, Roger Schlueter <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Question

Simon Slavin-3
On 19 Dec 2018, at 6:02pm, Jens Alfke <[hidden email]> 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:

<https://www.sqlite.org/json1.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Question

Roger Schlueter
In reply to this post by Jens Alfke-2
On 12/19/2018 10:02, Jens Alfke wrote:
>> On Dec 18, 2018, at 7:46 PM, Roger Schlueter <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Question

Chris Locke-3
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 <[hidden email]> wrote:

> On 12/19/2018 10:02, Jens Alfke wrote:
> >> On Dec 18, 2018, at 7:46 PM, Roger Schlueter <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
>
>> On 12/19/2018 10:02, Jens Alfke wrote:
>>>> On Dec 18, 2018, at 7:46 PM, Roger Schlueter <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Question

Chris Locke-3
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 <[hidden email]> 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 <[hidden email]> wrote:
> >
> >> On 12/19/2018 10:02, Jens Alfke wrote:
> >>>> On Dec 18, 2018, at 7:46 PM, Roger Schlueter <[hidden email]> 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
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Question

R Smith-2
On Thu, Dec 20, 2018 at 9:05 AM Roger Schlueter <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users