[sqlite] Encryption

[Richard Andersen]

I'm using the ADO.NET version (System.Data.SQlite).

I've created an RSA encrypted database using SQLite2009 and that is
working fine, but I can't find any tools for editing the table in
SQLite2009 once it's been created. Is it possible at all?

In DB Browser for SQlite I can edit the table but I'm not sure if the
SQLCipher encryption used here can be made to work with
System.Data.SQlite, or how to do if it can. Does anyone know anything
about this?

thanks,
Richard

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

Re: [sqlite] Select older or not selected records

[Simon Slavin]

On 11 Nov 2016, at 12:59pm, Niall O'Reilly  wrote:

>  IIUC, OP wishes to pick at random from the items which haven't yet been
>  used in the current cycle.

That's different.  To do that, you just need a flag 'seen' yes / no.

When you enter a new quote set seen to 'no'.

To search for a random quote:

SELECT id,quotetext FROM quotes WHERE seen='no' ORDER BY RANDOM() LIMIT 1

If the SELECT returns zero rows then do

UPDATE quotes SET seen='no'
SELECT id,quotetext FROM quotes WHERE seen='no' ORDER BY RANDOM() LIMIT 
1

then do

UPDATE quotes SET seen='yes' WHERE id= 

Worth noting that the above method, although it does what was asked, means that 
it is possible to see the same quote twice in a row.

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

Re: [sqlite] Select older or not selected records

[Niall O'Reilly]

On 11 Nov 2016, at 12:42, Simon Slavin wrote:

No.  When you use a quote you update the 'last used on' date for that 
table row.


  I think that's actually a "yes".

  IIUC, OP wishes to pick at random from the items which haven't yet 
been
  used in the current cycle.  By simply using the 'last used on' date, 
the first
  item in each cycle is guaranteed to be the same, and the second, and 
so on.


  To do what I think he wants, he'll need an additional selection 
criterion or two.



  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select older or not selected records

[Simon Slavin]

On 11 Nov 2016, at 9:04am, Cecil Westerhof  wrote:

> Because you get always the same next quote after all quotes have been used.

No.  When you use a quote you update the 'last used on' date for that table row.

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

Re: [sqlite] Strange problem with using random with a table

[Don V Nielsen]

Does the same thing happen if you select from Psalms, or Thessalonians,
instead of Proverbs?  Sorry. Little religion humor. Too early in the
morning and I haven't had my coffee, yet.

On Fri, Nov 11, 2016 at 4:34 AM, Cecil Westerhof 
wrote:

> 2016-11-11 11:22 GMT+01:00 Clemens Ladisch :
> > Cecil Westerhof wrote:
> >> When I execute the following query:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser);
> >>
> >> I get normal results:
> >> 61|61000
> >>
> >> But when I change it to:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser
> >>   FROM   proverbs)
> >> LIMIT 5;
> >>
> >> I get very strange results:
> >> 382|606000
> >> 172|148000
> >> 144|592000
> >> 181|136000
> >> 123|469000
> >>
> >> What could be happening here?
> >
> > In the first query, the subquery is computed with a temporary table
> > (actually, a coroutine) before the values of the outer query are
> > computed.
> >
> > The second query is flattened, so it ends up like this:
> >
> >   SELECT abs(random()) / 1,
> >  abs(random()) / 1 * 1000
> >   FROM proverbs
> >   LIMIT 5;
> >
> > "randomiser" is not a table column, it is just an _alias_ for the
> > expression, so this is an allowed transformation.
> >
> > To prevent this, violate one of the rules that the optimizer currently
> > uses to determine whether flattening is possible:
> > 
> > For example, put a LIMIT clause also into the subquery.
>
> I need all the records, so I use:
> SELECT *
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> FROM   proverbs
> LIMIT  (SELECT COUNT(*) FROM proverbs)
> )
> LIMIT 5;
>
> Not very clean, but it works.
>
> Maybe a good idea to have an option to disable flattening?
>
> --
> Cecil Westerhof
> ___
> 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] Select older or not selected records

[Cecil Westerhof]

2016-11-11 9:49 GMT+01:00 Rowan Worth :
> ORDER BY
> CASE timestamp
> WHEN NULL THEN -9223372036854775808
> ELSE abs(random())*timestamp
> END
>  LIMIT 5?
>
> Completely untested, and the weighting function (ELSE clause) is almost
> certainly terrible :P I think the approach is ok, though I remember some
> recent threads suggesting the interaction between ORDER BY and LIMIT is not
> as intuitive as one might expect.

The following looks likes it works reasonable well:
SELECT *
, CASE WHEN julianday(used) IS NULL THEN
randomiser * -1
  ELSE
(randomiser / 5) + julianday(used)
  END AS ordering
FROM (
SELECT *
,  abs(random()) / 10 + 1 AS randomiser
FROM   proverbs
LIMIT  (SELECT COUNT(*) FROM proverbs)
)
ORDER BY ordering
LIMIT  5

Adding the randomiser is better. In this way a selected is never more
as three week used after a not selected one.

I want the spread in the never selected to be a little bigger as the
selected. That is why I use the '/ 5'.


> On 11 November 2016 at 16:26, Cecil Westerhof 
> wrote:
>
>> I have an application that I want to migrate from file-based to
>> sqlite. It displays random quotes. After selecting a quote I update
>> the record with a timestamp.
>>
>> I want to select several quotes, with the following constraints:
>> - As long there are quotes that are not selected, no record that was
>> already selected should be selected.
>> - How longer ago a record was selected, the bigger the chance should
>> be that it is selected.
>>
>> What would the best way to do this? Can this be done in one query? For
>> example: I select 5 records, two records where never selected. So
>> these are the first two selected records. The other three records are
>> selected at random from the already selected ones, where the older
>> ones have a higher chance of getting selected.
>> Is this possible, or should it be done in two queries?
>>
>> I hope I am clear enough.

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

Re: [sqlite] Strange problem with using random with a table

[Cecil Westerhof]

2016-11-11 11:22 GMT+01:00 Clemens Ladisch :
> Cecil Westerhof wrote:
>> When I execute the following query:
>> SELECT *, randomiser * 1000
>> FROM (SELECT abs(random()) / 1 AS randomiser);
>>
>> I get normal results:
>> 61|61000
>>
>> But when I change it to:
>> SELECT *, randomiser * 1000
>> FROM (SELECT abs(random()) / 1 AS randomiser
>>   FROM   proverbs)
>> LIMIT 5;
>>
>> I get very strange results:
>> 382|606000
>> 172|148000
>> 144|592000
>> 181|136000
>> 123|469000
>>
>> What could be happening here?
>
> In the first query, the subquery is computed with a temporary table
> (actually, a coroutine) before the values of the outer query are
> computed.
>
> The second query is flattened, so it ends up like this:
>
>   SELECT abs(random()) / 1,
>  abs(random()) / 1 * 1000
>   FROM proverbs
>   LIMIT 5;
>
> "randomiser" is not a table column, it is just an _alias_ for the
> expression, so this is an allowed transformation.
>
> To prevent this, violate one of the rules that the optimizer currently
> uses to determine whether flattening is possible:
> 
> For example, put a LIMIT clause also into the subquery.

I need all the records, so I use:
SELECT *
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
FROM   proverbs
LIMIT  (SELECT COUNT(*) FROM proverbs)
)
LIMIT 5;

Not very clean, but it works.

Maybe a good idea to have an option to disable flattening?

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

Re: [sqlite] Strange problem with using random with a table

[Cecil Westerhof]

2016-11-11 11:01 GMT+01:00 Cecil Westerhof :
> When I execute the following query:
> SELECT *
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> );
>
> I get normal results:
> 61|61000
>
>
> But when I change it to:
> SELECT *
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> FROM   proverbs
> )
> LIMIT 5;
>
> I get very strange results:
> 382|606000
> 172|148000
> 144|592000
> 181|136000
> 123|469000
>
> What could be happening here?
>
> I am using SQLite version 3.8.10.2 on openSUSE Leap 42.1.

It even gets stranger. When I use:
SELECT *
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
FROM   proverbs
)
LIMIT 5;

I get:
453|75000|90|772000|823000|51000
93|572000|825000|881000|339000|681000
223|213000|726000|456000|688000|772000
565|359000|771000|674000|547000|26000
114|822000|188000|576000|635000|229000

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

Re: [sqlite] Strange problem with using random with a table

[Clemens Ladisch]

Cecil Westerhof wrote:
> When I execute the following query:
> SELECT *, randomiser * 1000
> FROM (SELECT abs(random()) / 1 AS randomiser);
>
> I get normal results:
> 61|61000
>
> But when I change it to:
> SELECT *, randomiser * 1000
> FROM (SELECT abs(random()) / 1 AS randomiser
>   FROM   proverbs)
> LIMIT 5;
>
> I get very strange results:
> 382|606000
> 172|148000
> 144|592000
> 181|136000
> 123|469000
>
> What could be happening here?

In the first query, the subquery is computed with a temporary table
(actually, a coroutine) before the values of the outer query are
computed.

The second query is flattened, so it ends up like this:

  SELECT abs(random()) / 1,
 abs(random()) / 1 * 1000
  FROM proverbs
  LIMIT 5;

"randomiser" is not a table column, it is just an _alias_ for the
expression, so this is an allowed transformation.

To prevent this, violate one of the rules that the optimizer currently
uses to determine whether flattening is possible:

For example, put a LIMIT clause also into the subquery.


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

[sqlite] Strange problem with using random with a table

[Cecil Westerhof]

When I execute the following query:
SELECT *
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
);

I get normal results:
61|61000


But when I change it to:
SELECT *
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
FROM   proverbs
)
LIMIT 5;

I get very strange results:
382|606000
172|148000
144|592000
181|136000
123|469000

What could be happening here?

I am using SQLite version 3.8.10.2 on openSUSE Leap 42.1.

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

Re: [sqlite] Select older or not selected records

[Cecil Westerhof]

2016-11-11 9:56 GMT+01:00 Simon Slavin :
>
>> I want to select several quotes, with the following constraints:
>> - As long there are quotes that are not selected, no record that was
>> already selected should be selected.
>> - How longer ago a record was selected, the bigger the chance should
>> be that it is selected.
>>
>> What would the best way to do this?
>
> Get rid of the first requirement by choosing an appropriate 'startup' 
> last-seen-timestamp for existing quotes.  Then you don't have to include the 
> logic for selecting by it in your software.

The idea of Rowan works. Needs some fine-tuning, but that is not a problem.

> Logically speaking this should be a very early date.  Perhaps 10 years before 
> the day the quote was added.  That would simulate the quote not having been 
> seen for 10 years.
>
> As for selecting a quote, why not just select the quote with the earliest 
> last-seen-timestamp ?  It can be done in one SELECT operation which uses an 
> index, so it'll happen almost instantly.

Because you get always the same next quote after all quotes have been used.

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

Re: [sqlite] Select older or not selected records

[Simon Slavin]

On 11 Nov 2016, at 8:26am, Cecil Westerhof  wrote:

> I want to select several quotes, with the following constraints:
> - As long there are quotes that are not selected, no record that was
> already selected should be selected.
> - How longer ago a record was selected, the bigger the chance should
> be that it is selected.
> 
> What would the best way to do this?

Get rid of the first requirement by choosing an appropriate 'startup' 
last-seen-timestamp for existing quotes.  Then you don't have to include the 
logic for selecting by it in your software.

Logically speaking this should be a very early date.  Perhaps 10 years before 
the day the quote was added.  That would simulate the quote not having been 
seen for 10 years.

As for selecting a quote, why not just select the quote with the earliest 
last-seen-timestamp ?  It can be done in one SELECT operation which uses an 
index, so it'll happen almost instantly.

CREATE INDEX q_ls ON quotes (lastSeen);

SELECT id,quoteText FROM quotes ORDER BY lastSeen LIMIT 1;
UPDATE quotes SET lastSeen= WHERE id=;

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

Re: [sqlite] Select older or not selected records

[Cecil Westerhof]

2016-11-11 9:49 GMT+01:00 Rowan Worth :
> ORDER BY
> CASE timestamp
> WHEN NULL THEN -9223372036854775808
> ELSE abs(random())*timestamp
> END
>  LIMIT 5?
>
> Completely untested, and the weighting function (ELSE clause) is almost
> certainly terrible :P I think the approach is ok, though I remember some
> recent threads suggesting the interaction between ORDER BY and LIMIT is not
> as intuitive as one might expect.

I am going to play with it. Thanks.


> On 11 November 2016 at 16:26, Cecil Westerhof 
> wrote:
>
>> I have an application that I want to migrate from file-based to
>> sqlite. It displays random quotes. After selecting a quote I update
>> the record with a timestamp.
>>
>> I want to select several quotes, with the following constraints:
>> - As long there are quotes that are not selected, no record that was
>> already selected should be selected.
>> - How longer ago a record was selected, the bigger the chance should
>> be that it is selected.
>>
>> What would the best way to do this? Can this be done in one query? For
>> example: I select 5 records, two records where never selected. So
>> these are the first two selected records. The other three records are
>> selected at random from the already selected ones, where the older
>> ones have a higher chance of getting selected.
>> Is this possible, or should it be done in two queries?
>>
>> I hope I am clear enough.

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

Re: [sqlite] Select older or not selected records

[Rowan Worth]

ORDER BY
CASE timestamp
WHEN NULL THEN -9223372036854775808
ELSE abs(random())*timestamp
END
 LIMIT 5?

Completely untested, and the weighting function (ELSE clause) is almost
certainly terrible :P I think the approach is ok, though I remember some
recent threads suggesting the interaction between ORDER BY and LIMIT is not
as intuitive as one might expect.

-Rowan


On 11 November 2016 at 16:26, Cecil Westerhof 
wrote:

> I have an application that I want to migrate from file-based to
> sqlite. It displays random quotes. After selecting a quote I update
> the record with a timestamp.
>
> I want to select several quotes, with the following constraints:
> - As long there are quotes that are not selected, no record that was
> already selected should be selected.
> - How longer ago a record was selected, the bigger the chance should
> be that it is selected.
>
> What would the best way to do this? Can this be done in one query? For
> example: I select 5 records, two records where never selected. So
> these are the first two selected records. The other three records are
> selected at random from the already selected ones, where the older
> ones have a higher chance of getting selected.
> Is this possible, or should it be done in two queries?
>
> I hope I am clear enough.
>
> --
> Cecil Westerhof
> ___
> 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] Select older or not selected records

[Cecil Westerhof]

I have an application that I want to migrate from file-based to
sqlite. It displays random quotes. After selecting a quote I update
the record with a timestamp.

I want to select several quotes, with the following constraints:
- As long there are quotes that are not selected, no record that was
already selected should be selected.
- How longer ago a record was selected, the bigger the chance should
be that it is selected.

What would the best way to do this? Can this be done in one query? For
example: I select 5 records, two records where never selected. So
these are the first two selected records. The other three records are
selected at random from the already selected ones, where the older
ones have a higher chance of getting selected.
Is this possible, or should it be done in two queries?

I hope I am clear enough.

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