Re: [sqlite] Can I create this view more efficient

[Cecil Westerhof]

That is a fast reply. :-D

2016-08-12 23:48 GMT+02:00 Simon Slavin :

>
> On 12 Aug 2016, at 10:34pm, Cecil Westerhof 
> wrote:
>
> > In the past I worked (I think) with a database (not SQLite) where I could
> > usedAfter in the definition for ratioTotalUsed. It is not a very big
> > problem, but is something like that possible with SQLite?
>
> Sorry, but you cannot do this in SQLite.  You cannot rely on usedAfter
> being already defined when you are defining ratioTotalUsed.  You must
> define ratioTotalUsed in terms of the memUsageLine table.
>

​I was afraid of that, but just wanted to be sure. Thanks.​




> There is a way to do it.  You define a first VIEW which calculates
> usedAfter and includes some other fields from the TABLE, when you define a
> second VIEW which takes its values from the first VIEW.
>
> In your example this is not worth the extra processing since it is easy to
> calculate (totalAfter-freeAfter), but it can be useful when aggregate
> functions are used.
>

​Exactly my thoughts.


​It is very handy to get the information out of a SQLite database instead
of from several​ log files. And with views I get the extra information for
free. It was some work, but that is only once. I am going to use SQLite
more often I think. ;-)

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

Re: [sqlite] 64-bit SQLite3.exe

[Random Coder]

On Fri, Aug 12, 2016 at 10:06 AM, Rousselot, Richard A
 wrote:
> I, and others, have tried to compile this as a 64-bit library but it will not 
> load from the command line (using .load) and gives the "Error: The specified 
> module could not be found."
>
> Anyone have tips on how to resolve this?  Is this library somehow 
> incompatible with 64-bit?

Generally that means the exports haven't been properly setup.  The
easiest way to do this generally is to setup a .def file to get the
proper function exported from the DLL with the correct naming
convention.

I've done just that for a quick test version at
https://new-bucket-2a9cf983.s3.amazonaws.com/sqlite64.zip , which may
or may not work for your needs.  Other than verifying acos() was
present, I've done no testing.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I create this view more efficient

[Simon Slavin]

On 12 Aug 2016, at 10:34pm, Cecil Westerhof  wrote:

> In the past I worked (I think) with a database (not SQLite) where I could
> usedAfter in the definition for ratioTotalUsed. It is not a very big
> problem, but is something like that possible with SQLite?

Sorry, but you cannot do this in SQLite.  You cannot rely on usedAfter being 
already defined when you are defining ratioTotalUsed.  You must define 
ratioTotalUsed in terms of the memUsageLine table.

There is a way to do it.  You define a first VIEW which calculates usedAfter 
and includes some other fields from the TABLE, when you define a second VIEW 
which takes its values from the first VIEW.

In your example this is not worth the extra processing since it is easy to 
calculate (totalAfter-freeAfter), but it can be useful when aggregate functions 
are used.

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

[sqlite] Can I create this view more efficient

[Cecil Westerhof]

At the moment I have the following table:
CREATE TABLE memUsageLine(
className   TEXT NOT NULL,
pid INT  NOT NULL,
timeChecked INT  NOT NULL DEFAULT (strftime('%s')),

freeAfter   INT NOT NULL,
freeBefore  INT NOT NULL,
maxMemory   INT  NOT NULL, -- Is probably not necessary
totalAfter  INT NOT NULL,
totalBefore INT NOT NULL,

PRIMARY KEY (className, PID, timeChecked)
);

​and I create the following view:
CREATE VIEW memUsageLineExtended AS
SELECT   className
,pid
,strftime('%Y-%m-%d %H:%M', timeChecked, 'unixepoch',
'localtime') as timeChecked
,freeAfter
,freeBefore
,maxMemory
,totalAfter
,totalBefore
,totalAfter  -
freeAfter   as usedAfter
,totalBefore -
freeBefore  as usedBefore
,CAST(totalAfter AS REAL) / (totalAfter  -
freeAfter)  as ratioTotalUsed
FROM memUsageLine
;

In the past I worked (I think) with a database (not SQLite) where I could
usedAfter in the definition for ratioTotalUsed. It is not a very big
problem, but is something like that possible with SQLite?

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

Re: [sqlite] 64-bit SQLite3.exe

[Scott Robison]

On Fri, Aug 12, 2016 at 12:02 PM, Warren Young  wrote:

> On Aug 11, 2016, at 7:50 PM, Scott Robison 
> wrote:
> >
> >> It’d be a lot of work just to avoid rebuilding for 64-bit, but maybe it
> >> would be an interesting project for someone.  Like a master’s university
> >> project, maybe.
> >>
> >
> > At first I thought to myself that a custom memory allocator for SQLite
> > could do this, but the real problem would be once a pointer is given to
> > SQLite, it is expected that pointer will be valid until disposed of
>
> Yeah, you’d need something like the handle lock/unlock pattern you see on
> some OSes, where the app generally holds only handles long-term, locking
> them down to yield a pointer only for the duration of a single function
> invocation at most.
>
> > Certainly a valuable tool for heavy processes that need to run on 32-bit
> > PAE hardware with > 4 GiB of addressable ram. Anyone want to start work
> on
> > SQLHeavy? ;)
>
> I was thinking more “valuable for the educational experience” than
> valuable in any practical sense, given the easy availability of 64-bit OSes
> and hardware.
>

Well, it certainly was practical at one point in time. I'm sure there are
still apps running on old servers that depend on its existence. As for
writing new code today that utilizes it, the utility is minimal if it even
exists.

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

[sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

Consider:

1. Create a new database, set the pragma page_size=512

2. Create a new database on the connection with ATTACH DATABASE 
'/tmp/number_two.db' AS second;

3. Issue pragma second.page_size=4096  to try and set the page size on the 
attached DB to 4096.

4. Read back with pragma second.page_size and get the default page size of 
1024. (We are still on 3.10.1)

Is it expected that the 4096 did not "stick?" Is there some relationship 
between page sizes in a main and attached DB?

Thanks

-- Ward

(In real life, we also set journal_mode=WAL on both databases AFTER issuing the 
page_size pragma, in case it makes any difference.)





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

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 11, 2016, at 7:50 PM, Scott Robison  wrote:
> 
>> It’d be a lot of work just to avoid rebuilding for 64-bit, but maybe it
>> would be an interesting project for someone.  Like a master’s university
>> project, maybe.
>> 
> 
> At first I thought to myself that a custom memory allocator for SQLite
> could do this, but the real problem would be once a pointer is given to
> SQLite, it is expected that pointer will be valid until disposed of

Yeah, you’d need something like the handle lock/unlock pattern you see on some 
OSes, where the app generally holds only handles long-term, locking them down 
to yield a pointer only for the duration of a single function invocation at 
most.

> Certainly a valuable tool for heavy processes that need to run on 32-bit
> PAE hardware with > 4 GiB of addressable ram. Anyone want to start work on
> SQLHeavy? ;)

I was thinking more “valuable for the educational experience” than valuable in 
any practical sense, given the easy availability of 64-bit OSes and hardware.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Rousselot, Richard A]

Update to my 64-bit saga.

I was able to work with some helpful mailing list members to create a 64-bit 
SQLite3.exe.  Far as I can tell it works fine but unfortunately I also use the 
math extension library (extensions-functions.c) in my CTE queries which is also 
32-bit.

I, and others, have tried to compile this as a 64-bit library but it will not 
load from the command line (using .load) and gives the "Error: The specified 
module could not be found."

Anyone have tips on how to resolve this?  Is this library somehow incompatible 
with 64-bit?

Any insight would be appreciated.

Richard
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a better way to perform this query?

[Dominique Devienne]

On Fri, Aug 12, 2016 at 6:01 PM, Chris Depetris 
wrote:

>Select * FROM TABLE where ((M1 IN (0,
> 1,2,7,15,150)) AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL )
>
> AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL)  AND (M3 IN (0, 1,2,7,15,150)
> Or
> M3 IS NULL)
>
> AND (M4 IN (0, 1,2,7,15,150) Or M4 IS NULL)  AND (M5 IN (0, 1,2,7,15,150)
> Or
> M5 IS NULL)
>
> AND (M6 IN (0, 1,2,7,15,150) Or M6 IS NULL)  AND (M7 IN (0, 1,2,7,15,150)
> Or
> M7 IS NULL)
>
> AND (M8 IN (0, 1,2,7,15,150) Or M8 IS NULL) AND (M9 IN (0, 1,2,7,15,150) Or
> M9 IS NULL)
>
> AND (M10 IN (0, 1,2,7,15,150) Or M10 IS NULL))
>
>   This query works and has reasonable performance right now for
> us, but I feel like there should be a more efficient way to do this.
>

Start by showing the query plan for this query, and tell us if you have
indexes, if any.
If you have none, try with an index on M1 only, and compare the plans and
performance.
See if making it a compound index of (M1, M2) helps. But given your data, I
think that
the more cols (or indexes) you add on those Mx columns, won't translate
into any gains
(diminishing returns on investment in a way). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there a better way to perform this query?

[Chris Depetris]

We have a table that has 10 fields that are used as in query as a key.  The
fields in question are M1-M10. 
M1-M10 will contain an integer of 0-5000 (may be higher). Also the values in
fields M1-M10 are always unique within the record and are usually ascending
(we can mandate this if it will help). An exception to this is the value of
0 or NULL which means the field is not used and if a field is 0  all higher
fields will be 0 ex: M4 = 0 then M5-10 will also be 0 (sometimes this can be
null, but we can eliminate the nulls) and of course M1 will never be 0.
There is no guarantee that 2 or more records will not have the same values
for fields M1-M10.  So an example of fields from those records would be. 

1,7,11,15,0,0, 0, 0, 0, 0

3,11,22,100,0,0,0,0,0,0,0

2,150,0,0,0,0,0,0,0

7,15,0,0,0,0,0,0,0,0

 

  We now need to query these to find all records that have only
values from within a specific set. The current query we use which does work
is similar to the following.

 

   Select * FROM TABLE where ((M1 IN (0,
1,2,7,15,150)) AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL )

AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL)  AND (M3 IN (0, 1,2,7,15,150) Or
M3 IS NULL) 

AND (M4 IN (0, 1,2,7,15,150) Or M4 IS NULL)  AND (M5 IN (0, 1,2,7,15,150) Or
M5 IS NULL) 

AND (M6 IN (0, 1,2,7,15,150) Or M6 IS NULL)  AND (M7 IN (0, 1,2,7,15,150) Or
M7 IS NULL) 

AND (M8 IN (0, 1,2,7,15,150) Or M8 IS NULL) AND (M9 IN (0, 1,2,7,15,150) Or
M9 IS NULL)

AND (M10 IN (0, 1,2,7,15,150) Or M10 IS NULL))

 

  This query works and has reasonable performance right now for
us, but I feel like there should be a more efficient way to do this. 

 

Thanks

Chris

 

 

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

Re: [sqlite] AS being optional

[Igor Korot]

Dominick,

On Fri, Aug 12, 2016 at 9:24 AM, Dominique Devienne  wrote:
> On Fri, Aug 12, 2016 at 2:42 PM, Keith Medcalf  wrote:
>
>> [...] The main problem with the JOIN/ON syntax is that to a casual reading
>> order is implied
>
>
> No idea what you mean here :)
>
>
>> ([...] ream of brackets [...]).
>>
>
> Nor what this has to do with JOIN/ON. Get off the anti-MS soapbox Keith ;)

For the "average Joe" the JOIN/ON" syntax is weird. So I have no idea why MS
does it this way, since that's their target audience. ;-)

>
>
>> TO me it is much simpler to understand:
>>
>
> I guess we agree to disagree on this one. To each his own. And both forms
> are valid of course. --DD

And if you need to filter results, you don't have a choice, but use WHERE...

Thank you.

> ___
> 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] AS being optional

[Dominique Devienne]

On Fri, Aug 12, 2016 at 2:42 PM, Keith Medcalf  wrote:

> [...] The main problem with the JOIN/ON syntax is that to a casual reading
> order is implied


No idea what you mean here :)


> ([...] ream of brackets [...]).
>

Nor what this has to do with JOIN/ON. Get off the anti-MS soapbox Keith ;)


> TO me it is much simpler to understand:
>

I guess we agree to disagree on this one. To each his own. And both forms
are valid of course. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AS being optional

[Keith Medcalf]

> I'm not a manager, and I do have a few "computer skills" (I think), and I
> still find JOIN ON much more
> readable than the FROM-comma + WHERE alternative. Helps me "thread" the
> table join in my head much better.
> Definitely helps me "grok" a statement faster, so not syntax sugar to me.
> My $0.02. --DD

The main problem with the JOIN/ON syntax is that to a casual reading order is 
implied (you can see this, for example, in the Microsoft products that litter 
generated SQL statements with ream upon ream of brackets perhaps in the hope of 
influencing a query optimizer, but more likely to ensure the result is as 
unreadable as possible).

TO me it is much simpler to understand:

SELECT 
  FROM 
 WHERE 

considering that JOIN which is mere sugar is (and ought) to be treated by 
replacing it with a "," and moving the conditions in the ON clause into the 
WHERE clause.




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

Re: [sqlite] AS being optional

[Keith Medcalf]

> AFAIK, AS is necessary in UNION, at least on some RDBMS, to have the same
> columns for all UNION'ed queries.
> So in that sense, not strictly syntax sugar. And it's of course valuable
> to
> give good names to complex expressions.
> A good name goes a long way to make "code" (of any sort) more readable and
> obvious.

In all cases where AS is used it can be replaced with nothing (removed).  There 
is no distinct purpose for the string as that cannot be served by not having 
the string as (that is, search and replace all " as " with " ") and you will 
find that the only meaningful change is that the number of bytes in the 
statement is less ...

JOIN however does have one (and only one) non-sugary use:  it is about the only 
way to specify a join type other than a simple cross (or, where there is a 
where clause, an equijoin).  Most vendors have removed all the various "not 
included in the spec" methods that used to be used to specify these types of 
operations.





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

Re: [sqlite] AS being optional

[Dominique Devienne]

On Fri, Aug 12, 2016 at 2:55 AM, Keith Medcalf  wrote:

>
> AS is optional as it was "syntactic sugar" added to SQL [...]
>

AFAIK, AS is necessary in UNION, at least on some RDBMS, to have the same
columns for all UNION'ed queries.
So in that sense, not strictly syntax sugar. And it's of course valuable to
give good names to complex expressions.
A good name goes a long way to make "code" (of any sort) more readable and
obvious.

Other syntactic sugar include the JOIN and ON syntax and keywords.


I'm not a manager, and I do have a few "computer skills" (I think), and I
still find JOIN ON much more
readable than the FROM-comma + WHERE alternative. Helps me "thread" the
table join in my head much better.
Definitely helps me "grok" a statement faster, so not syntax sugar to me.
My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users