Re: [sqlite] Use with Visual Studio
I use system.data.sqlite.dll (taken from here: https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki) with no problems in both VS 2017 Professional and VS 2017 Community. Thanks, Chris On Mon, Jul 9, 2018 at 2:47 AM Roger Schlueter wrote: > I am considering using the .net version of SQLite but have two questions: > > 1. The documentation lists the versions of Visual Studio that are > supported. VS2017 is NOT listed. Is VS2017 supported. > 2. The documentation states "Due to Visual Studio licensing > restrictions, the Express Editions can no longer be supported." > (Yes, in red). However, Microsoft no longer uses the phrase > "Express Edition" but rather calls the freebie version "Community". > Is this just semantics or does the red warning still apply to > Community? Also, I am unaware of any "licensing restrictions" on > the Community editions that would preclude the use of SQLite. Are > there such restrictions and, if so, what are they? > > Roger > > ___ > 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] Segfault when authorizer denies some steps of creating a WITHOUT ROWID table
Thanks for providing a succinct test case! A fix is now check-in on
trunk. https://www.sqlite.org/src/info/164b1641e346994f
Just FYI, the regression test case was added to TH3
(https://www.sqlite.org/th3.html) rather than in the main source tree
as it is much easier to incorporate there.
On 7/8/18, sql...@zzo38computer.org wrote:
> #if 0
> gcc -s -O2 -o ./sqltest1 sqltest1.c sqlite3.o -ldl -lpthread
> exit
> #endif
>
> /*
> Test with the command:
> ./sqltest1 2 'create table vt(a integer primary key,b,c) without rowid;'
> It segfaults. If the first argument is 3 or 4 it also segfaults.
> */
>
> #include
> #include
> #include
> #include "sqlite3.h"
>
> static int count=-1;
> static sqlite3*db;
>
> static int xAuth(void*aux,int act,const char*p3,const char*p4,const
> char*p5,const char*p6) {
> fprintf(stderr,"%d: %d %s %s %s %s\n",count,act,p3,p4,p5,p6);
> return count--?SQLITE_OK:SQLITE_DENY;
> }
>
> int main(int argc,char**argv) {
> if(argc!=3) return 1;
> if(sqlite3_open(":memory:",)) return 1;
> count=strtol(argv[1],0,0);
> sqlite3_set_authorizer(db,xAuth,0);
> printf("%d\n",sqlite3_exec(db,argv[2],0,0,0));
> return 0;
> }
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use with Visual Studio
I am considering using the .net version of SQLite but have two questions: 1. The documentation lists the versions of Visual Studio that are supported. VS2017 is NOT listed. Is VS2017 supported. 2. The documentation states "Due to Visual Studio licensing restrictions, the Express Editions can no longer be supported." (Yes, in red). However, Microsoft no longer uses the phrase "Express Edition" but rather calls the freebie version "Community". Is this just semantics or does the red warning still apply to Community? Also, I am unaware of any "licensing restrictions" on the Community editions that would preclude the use of SQLite. Are there such restrictions and, if so, what are they? Roger ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segfault when authorizer denies some steps of creating a WITHOUT ROWID table
#if 0
gcc -s -O2 -o ./sqltest1 sqltest1.c sqlite3.o -ldl -lpthread
exit
#endif
/*
Test with the command:
./sqltest1 2 'create table vt(a integer primary key,b,c) without rowid;'
It segfaults. If the first argument is 3 or 4 it also segfaults.
*/
#include
#include
#include
#include "sqlite3.h"
static int count=-1;
static sqlite3*db;
static int xAuth(void*aux,int act,const char*p3,const char*p4,const
char*p5,const char*p6) {
fprintf(stderr,"%d: %d %s %s %s %s\n",count,act,p3,p4,p5,p6);
return count--?SQLITE_OK:SQLITE_DENY;
}
int main(int argc,char**argv) {
if(argc!=3) return 1;
if(sqlite3_open(":memory:",)) return 1;
count=strtol(argv[1],0,0);
sqlite3_set_authorizer(db,xAuth,0);
printf("%d\n",sqlite3_exec(db,argv[2],0,0,0));
return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] To JSON or not to JSON
Cecil Westerhof wrote: > my SQLite database contains for example: > > "5BF19111-9FD5-48CA-B919-A09411346A87""[ > ""The journey of a thousand miles > must begin with a single step. > > - Lao Tzu"", > ""Welke stap kun je vandaag zetten, > om dat verre doel te bereiken?"" > ]""2018-07-07" > > Respectively quoteID, quote (with extra line(s)) and lastUsed. > > Is this an acceptable way to implement it, or would it be better to unravel > the elements in different records? Putting the quotes into a separate table, one per row, would allow you to access them individually. If you never need to access individual quotes, and if you know that this will never happen in the future, you could just as well keep storing the quotes as an array. (And then please tell us how you manage to predict the future. ;-) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
I found a missing sqlite_reset call in my wrapper library, and the solution proposed by Richard works great. So does the solution of guarding all the sql execute calls with a mutex. I have to perform the speed tests for the expected dataset and thread count yet. Thank you for your help! czw., 5 lip 2018 o 02:42 Richard Hipp napisał(a): > > On 7/4/18, Wojtek Mamrak wrote: > > Creating a separate connection for the SELECTs does not seem to solve > > the problem. What is the preferred way of tackling such a scenario? I > > guarded all the calls with a mutex, but it did not help. > > The change was a bug fix. Any write to an r-tree might cause the > parts of the r-tree to be reorganized. If that where to happen while > another thread where reading from the part being reorganized, > incorrect answers might result. > > You can probably work around the problem by adding something like > "ORDER BY +rowid" to each query against the r-tree. The "ORDER BY > +rowid" will force the query against the rtree to run to completion on > the first call too sqlite3_step(), storing the results in temporary > storage (for sorting). Then result rows will be handed out via > subsequent sqlite3_step() calls from temporary storage, rather than > from cursors on the rtree. This approach ensures that there are no > read cursors on rtree tables when they are written. > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Kind of pivot table
2018-07-08 11:00 GMT+02:00 Cecil Westerhof : > 2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > >> I thought there was a problem with RANDOM. I used: >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser >> >> And it seemed I got a lot of threes. >> >> To check this I used: >> SELECT Randomiser >> , COUNT(*) AS Count >> FROM ( >> SELECT date >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser >> FROM CPUUsage >> ORDER BY date >> ) >> GROUP BY Randomiser >> ORDER BY Randomiser >> >> And this gave results like: >> "0""165491" >> "1""166270" >> "2""166207" >> "3""165727" >> "4""165619" >> "5""165749" >> "6""98042" >> >> So 6 is created less often as 0 - 5, but that is in my use case not a >> problem. >> >> This worked for me because I have a big table CPUUsage. But if I would >> not have, is there another way to to do this? >> > > Solved it. Do not need a table any-more and generate Randomiser in the > correct way: > > SELECT Randomiser > , COUNT(*) AS Count > FROM ( > WITH RECURSIVE > cnt(x) AS ( > SELECT 1 > UNION ALL > SELECT x + 1 FROM cnt > LIMIT 1.1E6 > ) > SELECT x > ,ABS(RANDOM()) % 7 AS Randomiser > FROM cnt > ORDER BY x > ) > GROUP BY Randomiser > ORDER BY Randomiser > > And this gives: > "0""157139" > "1""157865" > "2""156849" > "3""157226" > "4""156916" > "5""157230" > "6""156775" > > By the way: it is only slightly faster as the version where I used the > table. > I can shave off about 30% by rewriting it to: WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt LIMIT 1.1E6 ) SELECT ABS(RANDOM()) % 7 AS Randomiser ,COUNT(*) FROM cnt GROUP BY Randomiser ORDER BY Randomiser -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > I thought there was a problem with RANDOM. I used: > ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > > And it seemed I got a lot of threes. > > To check this I used: > SELECT Randomiser > , COUNT(*) AS Count > FROM ( > SELECT date > ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > FROM CPUUsage > ORDER BY date > ) > GROUP BY Randomiser > ORDER BY Randomiser > > And this gave results like: > "0""165491" > "1""166270" > "2""166207" > "3""165727" > "4""165619" > "5""165749" > "6""98042" > > So 6 is created less often as 0 - 5, but that is in my use case not a > problem. > > This worked for me because I have a big table CPUUsage. But if I would not > have, is there another way to to do this? > Solved it. Do not need a table any-more and generate Randomiser in the correct way: SELECT Randomiser , COUNT(*) AS Count FROM ( WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt LIMIT 1.1E6 ) SELECT x ,ABS(RANDOM()) % 7 AS Randomiser FROM cnt ORDER BY x ) GROUP BY Randomiser ORDER BY Randomiser And this gives: "0""157139" "1""157865" "2""156849" "3""157226" "4""156916" "5""157230" "6""156775" By the way: it is only slightly faster as the version where I used the table. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
On 8 Jul 2018, at 9:40am, Simon Slavin wrote: > Use the conventional > >CAST(RANDOM() * 7) AS INTEGER > > or whatever spread you want. Apologies. In SQLite this should be (RANDOM() % 7) instead. The percent sign is a 'modulo' operator. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
On 8 Jul 2018, at 7:19am, Cecil Westerhof wrote: > ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) Don't divide by a number like 1.4e18 in an 18-digit INTEGER calculation and expect to get sensible answers. Use the conventional CAST(RANDOM() * 7) AS INTEGER or whatever spread you want. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
2018-07-08 10:20 GMT+02:00 Keith Medcalf : > > You probably do not have the kurtosis or skew aggregate functions either. > > generate_series is the series.c extension. > OK, thanks. Something to look into at a later moment. > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > >Sent: Sunday, 8 July, 2018 02:16 > >To: SQLite mailing list > >Subject: Re: [sqlite] Kind of pivot table > > > >2018-07-08 9:10 GMT+02:00 Keith Medcalf : > > > >> > >> sqlite> > >> > >> select kurt(abs(random() % 7)) from generate_series where start=1 > >and > >> stop=1e6; > >> -1.25154453962449 > >> > >> sqlite> select skew(abs(random() % 7)) from generate_series where > >start=1 > >> and stop=1e6; > >> 0.00104535938599554 > >> > >> The PRNG is pretty random. > >> > >> It is slightly concave (that is, anti-normal) (a "flat" > >distribution would > >> have a kurtosis of -1.2) and the curve is slightly skewed above the > >average. > >> > > > >At the moment it does not say much to me. But when I try the first > >statement I get: > >Error: no such table: generate_series > > > >Am I overlooking something? > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
You probably do not have the kurtosis or skew aggregate functions either. generate_series is the series.c extension. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Sunday, 8 July, 2018 02:16 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > >2018-07-08 9:10 GMT+02:00 Keith Medcalf : > >> >> sqlite> >> >> select kurt(abs(random() % 7)) from generate_series where start=1 >and >> stop=1e6; >> -1.25154453962449 >> >> sqlite> select skew(abs(random() % 7)) from generate_series where >start=1 >> and stop=1e6; >> 0.00104535938599554 >> >> The PRNG is pretty random. >> >> It is slightly concave (that is, anti-normal) (a "flat" >distribution would >> have a kurtosis of -1.2) and the curve is slightly skewed above the >average. >> > >At the moment it does not say much to me. But when I try the first >statement I get: >Error: no such table: generate_series > >Am I overlooking something? > >-- >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] Kind of pivot table
2018-07-08 9:10 GMT+02:00 Keith Medcalf : > > sqlite> > > select kurt(abs(random() % 7)) from generate_series where start=1 and > stop=1e6; > -1.25154453962449 > > sqlite> select skew(abs(random() % 7)) from generate_series where start=1 > and stop=1e6; > 0.00104535938599554 > > The PRNG is pretty random. > > It is slightly concave (that is, anti-normal) (a "flat" distribution would > have a kurtosis of -1.2) and the curve is slightly skewed above the average. > At the moment it does not say much to me. But when I try the first statement I get: Error: no such table: generate_series Am I overlooking something? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] To JSON or not to JSON
A long time ago I changed a line base application to a SQLite application. Every line was a record and this record could have several elements. Every element was send to Twitter with a minute between them. When converting I kept it like this. So my SQLite database contains for example: "5BF19111-9FD5-48CA-B919-A09411346A87""[ ""The journey of a thousand miles must begin with a single step. - Lao Tzu"", ""Welke stap kun je vandaag zetten, om dat verre doel te bereiken?"" ]""2018-07-07" Respectively quoteID, quote (with extra line(s)) and lastUsed. Is this an acceptable way to implement it, or would it be better to unravel the elements in different records? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
sqlite> select kurt(abs(random() % 7)) from generate_series where start=1 and stop=1e6; -1.25154453962449 sqlite> select skew(abs(random() % 7)) from generate_series where start=1 and stop=1e6; 0.00104535938599554 The PRNG is pretty random. It is slightly concave (that is, anti-normal) (a "flat" distribution would have a kurtosis of -1.2) and the curve is slightly skewed above the average. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Sunday, 8 July, 2018 00:59 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > >2018-07-08 8:49 GMT+02:00 Keith Medcalf : > >> >> Why not use MOD (%) as in >> >> ABS(RANDOM() % 6) >> > >You are completely right. How stupid of me. :'-( > >It only has to be: >ABS(RANDOM() % 7) > > > >> >-Original Message- >> >From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >> >Sent: Sunday, 8 July, 2018 00:44 >> >To: SQLite mailing list >> >Subject: Re: [sqlite] Kind of pivot table >> > >> >2018-07-08 8:19 GMT+02:00 Cecil Westerhof >: >> > >> >> I thought there was a problem with RANDOM. I used: >> >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS >Randomiser >> >> >> >> And it seemed I got a lot of threes. >> >> >> >> To check this I used: >> >> SELECT Randomiser >> >> , COUNT(*) AS Count >> >> FROM ( >> >> SELECT date >> >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS >> >Randomiser >> >> FROM CPUUsage >> >> ORDER BY date >> >> ) >> >> GROUP BY Randomiser >> >> ORDER BY Randomiser >> >> >> >> And this gave results like: >> >> "0""165491" >> >> "1""166270" >> >> "2""166207" >> >> "3""165727" >> >> "4""165619" >> >> "5""165749" >> >> "6""98042" >> >> >> >> So 6 is created less often as 0 - 5, but that is in my use case >not >> >a >> >> problem. >> >> >> >> This worked for me because I have a big table CPUUsage. But if I >> >would not >> >> have, is there another way to to do this? >> >> >> > >> >By the way better select is: >> >SELECT Randomiser >> >, COUNT(*) AS Count >> >FROM ( >> >SELECT date >> >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) >AS >> >Randomiser >> >FROM CPUUsage >> >ORDER BY date >> >) >> >GROUP BY Randomiser >> >ORDER BY Randomiser >> > >> >This gives: >> >"0""156204" >> >"1""157032" >> >"2""155636" >> >"3""156399" >> >"4""156256" >> >"5""155480" >> >"6""156073" >> >"7""52" >> > >> >This is much better. Only very rarely you get a seven you do not >> >want. >> >(Again in my case not really a problem.) >> > >> >Because in my case I use Randomiser to get a small subset of the >> >records, >> >this can be solved with: >> >SELECT Randomiser >> >, COUNT(*) AS Count >> >FROM ( >> >SELECT date >> >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) >AS >> >Randomiser >> >FROM CPUUsage >> >ORDER BY date >> >) >> >WHERERandomiser <> 7 >> >GROUP BY Randomiser >> >ORDER BY Randomiser >> > >> > >> >Then I get something like: >> >"0""155806" >> >"1""156270" >> >"2""156473" >> >"3""155748" >> >"4""155828" >> >"5""156196" >> >"6""156733" >> > >-- >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] Kind of pivot table
2018-07-08 8:49 GMT+02:00 Keith Medcalf : > > Why not use MOD (%) as in > > ABS(RANDOM() % 6) > You are completely right. How stupid of me. :'-( It only has to be: ABS(RANDOM() % 7) > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > >Sent: Sunday, 8 July, 2018 00:44 > >To: SQLite mailing list > >Subject: Re: [sqlite] Kind of pivot table > > > >2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > > > >> I thought there was a problem with RANDOM. I used: > >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > >> > >> And it seemed I got a lot of threes. > >> > >> To check this I used: > >> SELECT Randomiser > >> , COUNT(*) AS Count > >> FROM ( > >> SELECT date > >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS > >Randomiser > >> FROM CPUUsage > >> ORDER BY date > >> ) > >> GROUP BY Randomiser > >> ORDER BY Randomiser > >> > >> And this gave results like: > >> "0""165491" > >> "1""166270" > >> "2""166207" > >> "3""165727" > >> "4""165619" > >> "5""165749" > >> "6""98042" > >> > >> So 6 is created less often as 0 - 5, but that is in my use case not > >a > >> problem. > >> > >> This worked for me because I have a big table CPUUsage. But if I > >would not > >> have, is there another way to to do this? > >> > > > >By the way better select is: > >SELECT Randomiser > >, COUNT(*) AS Count > >FROM ( > >SELECT date > >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS > >Randomiser > >FROM CPUUsage > >ORDER BY date > >) > >GROUP BY Randomiser > >ORDER BY Randomiser > > > >This gives: > >"0""156204" > >"1""157032" > >"2""155636" > >"3""156399" > >"4""156256" > >"5""155480" > >"6""156073" > >"7""52" > > > >This is much better. Only very rarely you get a seven you do not > >want. > >(Again in my case not really a problem.) > > > >Because in my case I use Randomiser to get a small subset of the > >records, > >this can be solved with: > >SELECT Randomiser > >, COUNT(*) AS Count > >FROM ( > >SELECT date > >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS > >Randomiser > >FROM CPUUsage > >ORDER BY date > >) > >WHERERandomiser <> 7 > >GROUP BY Randomiser > >ORDER BY Randomiser > > > > > >Then I get something like: > >"0""155806" > >"1""156270" > >"2""156473" > >"3""155748" > >"4""155828" > >"5""156196" > >"6""156733" > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Kind of pivot table
Why not use MOD (%) as in ABS(RANDOM() % 6) --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Sunday, 8 July, 2018 00:44 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > >2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > >> I thought there was a problem with RANDOM. I used: >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser >> >> And it seemed I got a lot of threes. >> >> To check this I used: >> SELECT Randomiser >> , COUNT(*) AS Count >> FROM ( >> SELECT date >> ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS >Randomiser >> FROM CPUUsage >> ORDER BY date >> ) >> GROUP BY Randomiser >> ORDER BY Randomiser >> >> And this gave results like: >> "0""165491" >> "1""166270" >> "2""166207" >> "3""165727" >> "4""165619" >> "5""165749" >> "6""98042" >> >> So 6 is created less often as 0 - 5, but that is in my use case not >a >> problem. >> >> This worked for me because I have a big table CPUUsage. But if I >would not >> have, is there another way to to do this? >> > >By the way better select is: >SELECT Randomiser >, COUNT(*) AS Count >FROM ( >SELECT date >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS >Randomiser >FROM CPUUsage >ORDER BY date >) >GROUP BY Randomiser >ORDER BY Randomiser > >This gives: >"0""156204" >"1""157032" >"2""155636" >"3""156399" >"4""156256" >"5""155480" >"6""156073" >"7""52" > >This is much better. Only very rarely you get a seven you do not >want. >(Again in my case not really a problem.) > >Because in my case I use Randomiser to get a small subset of the >records, >this can be solved with: >SELECT Randomiser >, COUNT(*) AS Count >FROM ( >SELECT date >,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS >Randomiser >FROM CPUUsage >ORDER BY date >) >WHERERandomiser <> 7 >GROUP BY Randomiser >ORDER BY Randomiser > > >Then I get something like: >"0""155806" >"1""156270" >"2""156473" >"3""155748" >"4""155828" >"5""156196" >"6""156733" > >-- >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] Kind of pivot table
2018-07-08 8:19 GMT+02:00 Cecil Westerhof : > I thought there was a problem with RANDOM. I used: > ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > > And it seemed I got a lot of threes. > > To check this I used: > SELECT Randomiser > , COUNT(*) AS Count > FROM ( > SELECT date > ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser > FROM CPUUsage > ORDER BY date > ) > GROUP BY Randomiser > ORDER BY Randomiser > > And this gave results like: > "0""165491" > "1""166270" > "2""166207" > "3""165727" > "4""165619" > "5""165749" > "6""98042" > > So 6 is created less often as 0 - 5, but that is in my use case not a > problem. > > This worked for me because I have a big table CPUUsage. But if I would not > have, is there another way to to do this? > By the way better select is: SELECT Randomiser , COUNT(*) AS Count FROM ( SELECT date ,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS Randomiser FROM CPUUsage ORDER BY date ) GROUP BY Randomiser ORDER BY Randomiser This gives: "0""156204" "1""157032" "2""155636" "3""156399" "4""156256" "5""155480" "6""156073" "7""52" This is much better. Only very rarely you get a seven you do not want. (Again in my case not really a problem.) Because in my case I use Randomiser to get a small subset of the records, this can be solved with: SELECT Randomiser , COUNT(*) AS Count FROM ( SELECT date ,CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) AS Randomiser FROM CPUUsage ORDER BY date ) WHERERandomiser <> 7 GROUP BY Randomiser ORDER BY Randomiser Then I get something like: "0""155806" "1""156270" "2""156473" "3""155748" "4""155828" "5""156196" "6""156733" -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Kind of pivot table
I thought there was a problem with RANDOM. I used: ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser And it seemed I got a lot of threes. To check this I used: SELECT Randomiser , COUNT(*) AS Count FROM ( SELECT date ,ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS Randomiser FROM CPUUsage ORDER BY date ) GROUP BY Randomiser ORDER BY Randomiser And this gave results like: "0""165491" "1""166270" "2""166207" "3""165727" "4""165619" "5""165749" "6""98042" So 6 is created less often as 0 - 5, but that is in my use case not a problem. This worked for me because I have a big table CPUUsage. But if I would not have, is there another way to to do this? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
