Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[Tony Papadimitriou]

One obvious shortcut would be to factor out the common part of the select 
using WITH


(Hopefully accurate) example:

with t as (
 select GroupName, JobName, Start, End, Status, (strftime('%s', End) - 
strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13'

 )
select * from (select * from t where GroupName like 'GRP01%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP04%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP12%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP15%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP20%' ORDER BY Length 
DESC LIMIT 10);


-Original Message- 
From: pihu...@free.fr

Sent: Tuesday, October 14, 2014 12:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:


select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP04%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP12%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP15%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP20%' ORDER BY Length 
DESC LIMIT 10);


I want to select the ten longest jobs for five different groups (GRP01%, 
GRP04%, GRP12%, GRP15% and GRP20%). I can't find a solution without using 
this "UNION ALL" trick.


Contents in 'Start' and 'End' columns are ISO 8601 formatted.
I use "(strftime('%s', End) - strftime('%s', Start)) as Length" to calculate 
the length of each job, then "ORDER BY Length DESC LIMIT 10" for each group.


Do you know a simplest/better way to perform this query?
Thanks


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


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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[Igor Tandetnik]

On 10/13/2014 5:21 PM, pihu...@free.fr wrote:

Do you know a simplest/better way to perform this query?


Something along these lines:

select * from ReportJobs r1
where rowid in (
  select r2.rowid from ReportJobs r2
  where substr(r2.GroupName, 1, 5) = substr(r1.GroupName, 1, 5)
and r2.PlanDate = '2014-02-13'
  order by (strftime('%s', r2.End) - strftime('%s', r2.Start)) desc 
limit 10

);

It'll probably be noticeably slower than your unrolled query, though.
--
Igor Tandetnik

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

[sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[pihug12]

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:

select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP01%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP04%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP12%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP15%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP20%' ORDER BY Length DESC LIMIT 10);

I want to select the ten longest jobs for five different groups (GRP01%, 
GRP04%, GRP12%, GRP15% and GRP20%). I can't find a solution without using this 
"UNION ALL" trick.

Contents in 'Start' and 'End' columns are ISO 8601 formatted.
I use "(strftime('%s', End) - strftime('%s', Start)) as Length" to calculate 
the length of each job, then "ORDER BY Length DESC LIMIT 10" for each group.

Do you know a simplest/better way to perform this query?
Thanks


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

Re: [sqlite] Will someone be able to explain this weird outcome...

[jose isaias cabrera]

"Roger Binns" wrote...



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/2014 01:18 PM, jose isaias cabrera wrote:

I was able to figure out that comma's are more important than just
a 1000 number delemeter, so I received the right answer by taking
the commas out:


To help avoid this in the future, be aware that how developers deal
with numbers and how users experience them are very different.

People using your apps expect to see numbers in the normal way for
them.  For example thousands separators are useful, but note that some
locales group differently (eg around ten thousands).  Some use dots
not commas, and others the other way around.  Some use dots for the
decimal point and others use a comma.  Some don't use Arabic numerals
(0, 1, 2, 3 etc)

 https://en.wikipedia.org/wiki/Decimal_mark#Digit_grouping

Fortunately the operating system and programming environment provide
ways to output numbers (and dates, currency etc) in the most
appropriate way for the user.

Widespread programming languages wouldn't work very well if numbers
weren't consistently formatted (eg what happens if a developer in a
different locale runs the code).  Reflecting their origins, they
almost always only accept the anglo-centric integer notation of no
grouping and a dot as the decimal point.  SQLite uses SQL which does
the same.

For you that means separating out text that you are using with SQLite,
versus text that is shown/accepted from the user.  If you mix them
together you'll end up with unexpected behaviour, crashes, wrong
results etc.


thanks, Roger.

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

Re: [sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

[Keith Medcalf]

And 2.7.8:

Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on 
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn=sqlite3.connect(":memory:")
>>> conn.execute("""
... CREATE TABLE `UpdateFrom` (
... `VersionName` TEXT NOT NULL UNIQUE,
... `VersionNumber` INTEGER NOT NULL,
... `UpdateFrom` INTEGER NOT NULL,
... `MetafileURL` TEXT NOT NULL,
... `PatchURL` TEXT NOT NULL,
... `SHA1` TEXT NOT NULL
... );""")

>>> versionName=0
>>> versionNumber=0
>>> updateFrom=0
>>> metafileURL=0
>>> patchURL=0
>>> sha1=0
>>> params = versionName, versionNumber, updateFrom, metafileURL, patchURL, sha1
>>> conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params)

>>>
>>> for row in conn.execute('select * from updatefrom'):
...  print row
...
(u'0', 0, 0, u'0', u'0', u'0')
>>>

On Monday, 13 October, 2014 11:14, Clemens Ladisch  said:

>Jeffrey Parker wrote:

>> I am working with sqlite3 in python 2.7.8 and I am running into a
>> strange error where I get the below exception when running an insert 
>> into statement on an empty table.
>
>The following code executes fine in Python 2.7.5:
>
>import sqlite3
>conn=sqlite3.connect(":memory:")
>conn.execute("""
>CREATE TABLE `UpdateFrom` (
>`VersionName` TEXT NOT NULL UNIQUE,
>`VersionNumber` INTEGER NOT NULL,
>`UpdateFrom` INTEGER NOT NULL,
>`MetafileURL` TEXT NOT NULL,
>`PatchURL` TEXT NOT NULL,
>`SHA1` TEXT NOT NULL
>);""")
>versionName=0
>versionNumber=0
>updateFrom=0
>metafileURL=0
>patchURL=0
>sha1=0
>params = versionName, versionNumber, updateFrom, metafileURL, patchURL,
>sha1
>conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params)



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

Re: [sqlite] group_concat query performance

[Joe Mistachkin]

Kraijenbrink - FixHet - Systeembeheer wrote:
>
> 1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9
seconds;
>
> 2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8
seconds.
>(Without the GROUP_CONCAT function that is;)
> 
> 3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6
minutes;  
> 

Are you able to share the query and the schema of the database involved?

If you have sample code, that might reveal important details as well.

--
Joe Mistachkin

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

Re: [sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

[Dan Kennedy]

On 10/13/2014 11:44 PM, Jeffrey Parker wrote:

Hello,

I am working with sqlite3 in python 2.7.8 and I am running into a strange
error where I get the below exception when running an insert into statement
on an empty table. I know this is probably more to do with the python
libraries but I thought that I would ask the question here in case someone
has any recommendations.

sqlite3.IntegrityError: column VersionName is not unique

The insert still works and when I do the next insert into that table there
is no exception.


It sounds like you are attempting to insert a duplicate row.

You could temporarily add a SELECT statement before the INSERT to check 
if a record with the specified "versionName" is already present in the 
table.


Dan.





There are multiple insert statements and multiple tables. The insert
statements look like below.

params = versionName, versionNumber, updateFrom, metafileURL, patchURL, sha1
conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params)

and the table looks like below.

CREATE TABLE `UpdateFrom` (

`VersionName` TEXT NOT NULL UNIQUE,

`VersionNumber` INTEGER NOT NULL,

`UpdateFrom` INTEGER NOT NULL,

`MetafileURL` TEXT NOT NULL,

`PatchURL` TEXT NOT NULL,

`SHA1` TEXT NOT NULL

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


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

Re: [sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

[Clemens Ladisch]

Jeffrey Parker wrote:
> I am working with sqlite3 in python 2.7.8 and I am running into a strange
> error where I get the below exception when running an insert into statement
> on an empty table.

The following code executes fine in Python 2.7.5:

import sqlite3
conn=sqlite3.connect(":memory:")
conn.execute("""
CREATE TABLE `UpdateFrom` (
`VersionName` TEXT NOT NULL UNIQUE,
`VersionNumber` INTEGER NOT NULL,
`UpdateFrom` INTEGER NOT NULL,
`MetafileURL` TEXT NOT NULL,
`PatchURL` TEXT NOT NULL,
`SHA1` TEXT NOT NULL
);""")
versionName=0
versionNumber=0
updateFrom=0
metafileURL=0
patchURL=0
sha1=0
params = versionName, versionNumber, updateFrom, metafileURL, patchURL, sha1
conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params)


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

Re: [sqlite] decoding a bitmask

[Paul Sanderson]

Thanks all - agree rookie mistake with xor, had this been a C++ exercise I
would have have been OK - SQL seems to make my mind go blank...

Thanks for the case explanation Mark - v helpful.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 13 October 2014 16:30, Mark Lawrence  wrote:

> My apologies for the previous completely wrong mesage. I got mixed up
> with operator meaning & precedence...
>
> On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> >
> > My actual code is as folows
> >
> >   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
> > ELSE '' END ||
>
> But I can at least put some better light on your issue. SQLite returns
> different results for your style of case/when construct:
>
> CASE expr WHEN val THEN...
>
> and the alternative which I find reads easier:
>
> CASE WHEN truth_expr THEN...
>
> Here's a demonstration:
>
> WITH x
> AS (
> SELECT
> 0x01 AS a,
> '0x01' AS txt
> UNION ALL SELECT
> 0x10,
> '0x10'
> UNION ALL SELECT
> 0x01 | 0x10,
> '0x01 | 0x10'
> )
> SELECT
> x.txt,
>
> CASE
> WHEN x.a & 0x01
> THEN 'A'
> ELSE ''
> END
> || ' ' ||
> CASE
> WHEN x.a & 0x10
> THEN 'B'
> ELSE ''
> END AS result1,
>
> CASE x.a & 0xff
> WHEN 0x01
> THEN 'A'
> ELSE ''
> END
> || ' ' ||
> CASE x.a & 0xff
> WHEN 0x10
> THEN 'B'
> ELSE ''
> END AS result2
> FROM
> x
> ;
>
> And the results:
>
> txt result1 result2
> --  --  --
> 0x01A   A
> 0x10 B   B
> 0x01 | 0x1  A B
>
> --
> MarkeLawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

[Jeffrey Parker]

Hello,

I am working with sqlite3 in python 2.7.8 and I am running into a strange
error where I get the below exception when running an insert into statement
on an empty table. I know this is probably more to do with the python
libraries but I thought that I would ask the question here in case someone
has any recommendations.

sqlite3.IntegrityError: column VersionName is not unique

The insert still works and when I do the next insert into that table there
is no exception.

There are multiple insert statements and multiple tables. The insert
statements look like below.

params = versionName, versionNumber, updateFrom, metafileURL, patchURL, sha1
conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params)

and the table looks like below.

CREATE TABLE `UpdateFrom` (

`VersionName` TEXT NOT NULL UNIQUE,

`VersionNumber` INTEGER NOT NULL,

`UpdateFrom` INTEGER NOT NULL,

`MetafileURL` TEXT NOT NULL,

`PatchURL` TEXT NOT NULL,

`SHA1` TEXT NOT NULL

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

Re: [sqlite] decoding a bitmask

[Mark Lawrence]

My apologies for the previous completely wrong mesage. I got mixed up
with operator meaning & precedence...

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> 
> My actual code is as folows
> 
>   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
> ELSE '' END ||

But I can at least put some better light on your issue. SQLite returns
different results for your style of case/when construct:

CASE expr WHEN val THEN... 

and the alternative which I find reads easier:

CASE WHEN truth_expr THEN...

Here's a demonstration:

WITH x
AS (
SELECT
0x01 AS a,
'0x01' AS txt
UNION ALL SELECT
0x10,
'0x10'
UNION ALL SELECT
0x01 | 0x10,
'0x01 | 0x10'
)
SELECT
x.txt,

CASE
WHEN x.a & 0x01
THEN 'A'
ELSE ''
END
|| ' ' ||
CASE
WHEN x.a & 0x10
THEN 'B'
ELSE ''
END AS result1,

CASE x.a & 0xff
WHEN 0x01
THEN 'A'
ELSE ''
END
|| ' ' ||
CASE x.a & 0xff
WHEN 0x10
THEN 'B'
ELSE ''
END AS result2
FROM
x
;

And the results:

txt result1 result2   
--  --  --
0x01A   A 
0x10 B   B
0x01 | 0x1  A B   

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

Re: [sqlite] decoding a bitmask

[Doug Currie]

>
> The query is on a visits table from a google chrome history database. The
> query seems to work OK if a single bit is set, but fails (a blank string is
> returned) when multiple bits are set. Any ideas why?
>

It's because none of the WHEN 0x... cases, except 0xC0...,  have multiple
bits set. The approach you've chosen requires enumerating all the possible
combinations (all 2^5 of them in this case). You are better off with one of
the other suggested approaches by Richard Hipp or RSmith.

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

Re: [sqlite] decoding a bitmask

[Clemens Herschel]

Hi,
I used to bitwise stuff in Assembler  in the 60's when disk space was 
dear. Do not know your environment. But maybe my observations will help.
CASE stops after one conditions is met and does not test other cases. 
The next line executed is after the CASEs
 When I worked with bits, I would look at the sum of the bits. So a 1st 
bit and a 2nd bit set has a sum  3. A 1st and a 3rd has a sum of 5.

Generally do not answer to list but hope this helps.
Clemens Herschel ( a different Clemens)
On 10/13/14, 9:39 AM, Paul Sanderson wrote:

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
   AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-SQLite Forensic Toolkit
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 13 October 2014 13:17, Clemens Ladisch  wrote:


Paul Sanderson wrote:

I have a table with an integer value which is a bitmask.

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

I'd like to create a query which would take an attribute, say 0x07 and

spit

out "system, hidden, readonly"

SELECT substr(CASE WHEN attr & 32 THEN ', archive'   ELSE '' END ||
   CASE WHEN attr & 16 THEN ', directory' ELSE '' END ||
   CASE WHEN attr &  4 THEN ', system'ELSE '' END ||
   CASE WHEN attr &  2 THEN ', hidden'ELSE '' END ||
   CASE WHEN attr &  1 THEN ', readonly'  ELSE '' END, 3)
FROM MyTable;


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


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


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

Re: [sqlite] decoding a bitmask

[Igor Tandetnik]

On 10/13/2014 9:39 AM, Paul Sanderson wrote:

The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?


CASE x WHEN y ... checks that x = y, not that x & y != 0. You are 
checking for equality with a one-bit value - so of course it only checks 
out when the other side of the comparison is also a one-bit value.


You want

CASE WHEN visits.transition & 0x0080 THEN 'Blocked' ELSE '' END

--
Igor Tandetnik

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

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 15:39, Paul Sanderson wrote:

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
   AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?


Rookie mistake :)

You are basically bitwise AND-ing 2 values together, such as (visits.transition & 0xFF00) and then checking the result against a 
single bit... this will never work unless the transition value has only a single bit set, I think you meant to AND the mask with the 
transition value and then check if the result is non-zero?


This works perfectly for me:

  (CASE WHEN (visits.transition & 0x0080)>0 THEN 'Blocked'
ELSE '' END ||... etc. etc.
 



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

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 16:51, Mark Lawrence wrote:

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

I suspect it is a bug with multiple bitwise ORs. Demonstration:

 WITH x
 AS (
 SELECT
 0x0080 | 0x0800 AS a
 )
 SELECT
 a & 0x0080,
 a & 0x0800,
 a & 0x0080 & 0x0800
 FROM
 x
 ;

Result:

 a & 0x0080  a & 0x0800  a & 0x0080 & 0x0800
 --  --  ---
 8388608 134217728   0


Those look like exactly the correct results to me, how did you expect the 
result to be?



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

Re: [sqlite] decoding a bitmask

[Clemens Ladisch]

Paul Sanderson wrote:
>   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'   
> ELSE '' END ||
>CASE visits.transition & 0xFF00  WHEN 0x0100 THEN 'Forward_Back'  
> ELSE '' END ||
>...
>
> The query seems to work OK if a single bit is set, but fails (a blank string
> is returned) when multiple bits are set. Any ideas why?

When multiple bits are set, the value of visits.transition & 0xFF00
is something like 0x0180.

Don't bother with the extra bit mask, just check individual bits:

  (CASE WHEN visits.transition & 0x0080 THEN 'Blocked'   ELSE '' END ||
   CASE WHEN visits.transition & 0x0100 THEN 'Forward_Back'  ELSE '' END ||
   ...


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

Re: [sqlite] decoding a bitmask

[Mark Lawrence]

On Mon Oct 13, 2014 at 04:51:16PM +0200, Mark Lawrence wrote:
> On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> 
> Perl equivalent:
> 
> use feature 'say';
> my $a = 0x0080 | 0x0800;
> 
> say $a & 0x0080;
> say $a & 0x0800;
> say $a & 0x0800 & 0x0800;

Sorry, my comment was invalid. The above is incorrect - I mixed up the
8's and the 0's.

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

Re: [sqlite] decoding a bitmask

[Mark Lawrence]

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> 
> The query is on a visits table from a google chrome history database. The
> query seems to work OK if a single bit is set, but fails (a blank string is
> returned) when multiple bits are set. Any ideas why?

I suspect it is a bug with multiple bitwise ORs. Demonstration:

WITH x
AS (
SELECT
0x0080 | 0x0800 AS a
)
SELECT
a & 0x0080,
a & 0x0800,
a & 0x0080 & 0x0800
FROM
x
;

Result:

a & 0x0080  a & 0x0800  a & 0x0080 & 0x0800
--  --  ---
8388608 134217728   0  

Perl equivalent:

use feature 'say';
my $a = 0x0080 | 0x0800;

say $a & 0x0080;
say $a & 0x0800;
say $a & 0x0800 & 0x0800;

Result:

8388608
134217728
134217728

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

Re: [sqlite] decoding a bitmask

[Paul Sanderson]

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

  (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
  AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-SQLite Forensic Toolkit
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 13 October 2014 13:17, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
> > I have a table with an integer value which is a bitmask.
> >
> > 0c01 readonly
> > 0x02 hidden
> > 0x04 system
> > 0x10 directory
> > 0x20 archive
> >
> > I'd like to create a query which would take an attribute, say 0x07 and
> spit
> > out "system, hidden, readonly"
>
> SELECT substr(CASE WHEN attr & 32 THEN ', archive'   ELSE '' END ||
>   CASE WHEN attr & 16 THEN ', directory' ELSE '' END ||
>   CASE WHEN attr &  4 THEN ', system'ELSE '' END ||
>   CASE WHEN attr &  2 THEN ', hidden'ELSE '' END ||
>   CASE WHEN attr &  1 THEN ', readonly'  ELSE '' END, 3)
> FROM MyTable;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Clemens Ladisch]

Paul Sanderson wrote:
> I have a table with an integer value which is a bitmask.
>
> 0c01 readonly
> 0x02 hidden
> 0x04 system
> 0x10 directory
> 0x20 archive
>
> I'd like to create a query which would take an attribute, say 0x07 and spit
> out "system, hidden, readonly"

SELECT substr(CASE WHEN attr & 32 THEN ', archive'   ELSE '' END ||
  CASE WHEN attr & 16 THEN ', directory' ELSE '' END ||
  CASE WHEN attr &  4 THEN ', system'ELSE '' END ||
  CASE WHEN attr &  2 THEN ', hidden'ELSE '' END ||
  CASE WHEN attr &  1 THEN ', readonly'  ELSE '' END, 3)
FROM MyTable;


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

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 13:52, Paul Sanderson wrote:

I have a table with an integer value which is a bitmask. one or more of the
bits can be set and each bit has a corresponding meaning.

so using the windows file attribute as an example we have

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

none, any or all could be set

I'd like to create a query which would take an attribute, say 0x07 and spit
out "system, hidden, readonly"


Or you could make a table like this:

CREATE TABLE "fileFlags" (
"ID" INTEGER PRIMARY KEY,
 "Descr" TEXT
);

Populate it with the Mask descriptions like so:

INSERT INTO "fileFlags" ("ID","Descr") VALUES
(1,'readonly'),
(2,'hidden'),
(4,'system'),
(8,'unknown'),
(16,'directory'),
(32,'archive');

And then any query of the form:

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & [BitValue])>0;

Will work, as the following tests shown:

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 11)>0
readonly
hidden
unknown


SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 7)>0
readonly
hidden
system

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 4)>0
system

etc.

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

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi,

I've created 3 test samples. A C++ (sqlite-amalgamation-3080600 .lib) and two 
VB.NET variants (sqlite-netFx40-binary-x64-2010-1.0.94.0). 

1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9 seconds;

2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8 seconds. 
(Without the GROUP_CONCAT function that is;)

3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6 minutes; 
 

It looks like the .Net variant handles the GROUP_CONCAT function differently.

With regards,

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

Re: [sqlite] decoding a bitmask

[Richard Hipp]

On Mon, Oct 13, 2014 at 7:52 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I have a table with an integer value which is a bitmask. one or more of the
> bits can be set and each bit has a corresponding meaning.
>
> so using the windows file attribute as an example we have
>
> 0c01 readonly
> 0x02 hidden
> 0x04 system
> 0x10 directory
> 0x20 archive
>
> none, any or all could be set
>
> I'd like to create a query which would take an attribute, say 0x07 and spit
> out "system, hidden, readonly"
>


WITH RECURSIVE
  all_bit_patterns(x) AS (VALUES(0) UNION ALL
  SELECT x+1 FROM all_bit_patterns
  WHERE x<0x1f)
SELECT x,
   (SELECT group_concat(name) FROM
  (SELECT 0x01 AS id, 'readonly' AS name
   UNION ALL SELECT 0x02, 'hidden'
   UNION ALL SELECT 0x04, 'system'
   UNION ALL SELECT 0x08, 'directory'
   UNION ALL SELECT 0x10, 'archive')
WHERE (x&id)!=0)
 FROM all_bit_patterns;

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] decoding a bitmask

[Paul Sanderson]

I have a table with an integer value which is a bitmask. one or more of the
bits can be set and each bit has a corresponding meaning.

so using the windows file attribute as an example we have

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

none, any or all could be set

I'd like to create a query which would take an attribute, say 0x07 and spit
out "system, hidden, readonly"

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

Re: [sqlite] (no subject)

[Simon Slavin]

On 13 Oct 2014, at 12:06pm, Rohit Kaushal  wrote:

> please unregister me

Only you can stop forest fires.  See the link at the bottom of every post to 
this list.

Simon.

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

[sqlite] (no subject)

[Rohit Kaushal]

please unregister me
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] following a trail of references

[Paul Sanderson]

Thanks both - modified the code for my example but it failed :( I'll keep
at it and see if I can get my head around it.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-SQLite Forensic Toolkit
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 12 October 2014 21:18, Igor Tandetnik  wrote:

> On 10/12/2014 4:03 PM, Paul Sanderson wrote:
>
>> Is this possible just using SQL select type statements?
>>
>
> With sufficiently recent SQLite version, you can do something like this
> (untested):
>
> with recursive path as (
>   select id, previousid, location from mytable where id=6
> union all
>   select id, previousid, location
>   from path join mytable on (path.previousid = mytable.id)
> )
> select * from path;
>
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users