Re: [sqlite] Variable values in Views

[Jonathan Moules]

Hi List,
Thanks for the responses.
I don't think TCL will work for me - I want to use less languages, not more.

As to the structure - I am considering using ATTACH as a method, but
haven't gotten to the point where I need to decide which of the three
options (keys in tables, table sets, or separate ATTACHed databases) to
use. I think personally I prefer the logical separation of  the ATTACH
method but haven't fully looked into it yet.

Cheers,
Jonathan

On 30 July 2014 07:00, Noel Frankinet  wrote:

> There a tcl binding to sqlite, maybe it could help you ?
>
> Noël
>
>
> On 30 July 2014 08:44, Sylvain Pointeau 
> wrote:
>
> > It is called parameterized view in sqlserver.
> > Actually it is extremely useful in order to have a good reusability in
> the
> > code.
> > I was actually missing it in Oracle, although I found a workaround of
> using
> > the pipelined functions.
> >
> > Unfortunately, it is missing in sqlite, as well as the merge statement,
> > also very useful (insert or replace has just to be avoided)
> >
> > What I used to make is to use a shell script, and to use sed to replace
> my
> > variable before executing the script... Far from ideal but it worked ok,
> it
> > is just annoying because we have to prepare the file first.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable values in Views

[Noel Frankinet]

There a tcl binding to sqlite, maybe it could help you ?

Noël


On 30 July 2014 08:44, Sylvain Pointeau  wrote:

> It is called parameterized view in sqlserver.
> Actually it is extremely useful in order to have a good reusability in the
> code.
> I was actually missing it in Oracle, although I found a workaround of using
> the pipelined functions.
>
> Unfortunately, it is missing in sqlite, as well as the merge statement,
> also very useful (insert or replace has just to be avoided)
>
> What I used to make is to use a shell script, and to use sed to replace my
> variable before executing the script... Far from ideal but it worked ok, it
> is just annoying because we have to prepare the file first.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable values in Views

[Sylvain Pointeau]

It is called parameterized view in sqlserver.
Actually it is extremely useful in order to have a good reusability in the
code.
I was actually missing it in Oracle, although I found a workaround of using
the pipelined functions.

Unfortunately, it is missing in sqlite, as well as the merge statement,
also very useful (insert or replace has just to be avoided)

What I used to make is to use a shell script, and to use sed to replace my
variable before executing the script... Far from ideal but it worked ok, it
is just annoying because we have to prepare the file first.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable values in Views

[James K. Lowden]

On Mon, 28 Jul 2014 14:53:34 +0100
Jonathan Moules  wrote:

> Fair question, but I'm doing log analysis. Each set of tables will be
> for a given server that's being analysed. The application that uses
> the data is a read-only web-app, so my database needs to be optimised
> for SELECT queries.

When I do work like this, I make the logfile name the first column in
the table.  That name becomes part of the primary key.  Depending on
the situation, it may be the first or last component of the key.  

You have to do something to put the logfile data into tabular form.
Might as well grab the filename and the file's timestamp while you're
there.  Then it doesn't matter how many systems are producing the logs,
or over how much time.  If you're really worried about keyspace, number
the logfile names in a separate table, and use that number in your
key.  

I wouldn't assume more tables is faster than fewer.  A binary search
locates one row in a billion in 29 comparisons; in a 100 million rows it
it needs 26.  

Meaning: if you split your one-big-table into 10 smaller ones, each
lookup will be 10% faster *assuming* nothing else interferes.  While
the improvement is marginal and speculative, this is certain: your SQL
and your application will be more complex.  

HTH.  

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

Re: [sqlite] Variable values in Views

[Petite Abeille]

On Jul 28, 2014, at 3:53 PM, Jonathan Moules 
 wrote:

> Fair question, but I'm doing log analysis. Each set of tables will be for a
> given server that's being analysed.

Alternatively, you could setup your tables as a set of distinct databases, one 
per server, and attach/detach the relevant one while keeping the same set of 
table/view names. Just a thought.

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

Re: [sqlite] Variable values in Views

[Jonathan Moules]

Hi Simon,
Thanks for your response. Good point about the preparation, didn't know
that.

> On the other hand, the fact that you're numbering tables suggests a badly
formed schema.  Are you sure you shouldn't merge the tables and move that
number to a column inside the table ?

Fair question, but I'm doing log analysis. Each set of tables will be for a
given server that's being analysed. The application that uses the data is a
read-only web-app, so my database needs to be optimised for SELECT queries.
I don't anticipate there being many servers in an install so the number of
table sets should be small. However the number of rows can be fairly large;
I figure this method should offer a small speed up on the potentially
larger datasets even if it's not strictly best-practice.

That said, I don't suppose there's any option for the other variable
($date_string)?

Thanks,
Jonathan


On 28 July 2014 14:37, Simon Slavin  wrote:

>
> > On 28 Jul 2014, at 12:41pm, Jonathan Moules <
> jonathanmou...@warwickshire.gov.uk> wrote:
> >
> > *$table_prefix* which will be a number indicating which table set to look
>
> You can't have variable table names in a prepared statement.  The process
> which does preparation has to know which table it will be accessing.  If
> you don't know which table you're be using, you can't use a system like
> that.
>
> On the other hand, the fact that you're numbering tables suggests a badly
> formed schema.  Are you sure you shouldn't merge the tables and move that
> number to a column inside the table ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable values in Views

[Simon Slavin]

On 28 Jul 2014, at 2:53pm, Jonathan Moules  
wrote:

> That said, I don't suppose there's any option for the other variable
> ($date_string)?

No.  I suppose you could store the calculated date in another table, then use 
another JOIN to retrieve it.  I have no idea how well this would work in real 
life.

Otherwise I think you're going to have to have your software prepare the query 
as a big string using whatever string facilities your programming language has. 
 It can then execute the query using _prepare()/_step (without needing to do 
any binding) or _exec().  You could do that with your CREATE VIEW command.  Or 
you could do it with a SELECT.

For those who don't know, CREATE VIEW is just a clever way of storing a SELECT 
statement.  It does not store data from inside a table anywhere.

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

Re: [sqlite] Variable values in Views

[RSmith]

On 2014/07/28 15:53, Jonathan Moules wrote:

Fair question, but I'm doing log analysis. Each set of tables will be for a
given server that's being analysed. The application that uses the data is a
read-only web-app, so my database needs to be optimised for SELECT queries.
I don't anticipate there being many servers in an install so the number of
table sets should be small. However the number of rows can be fairly large;
I figure this method should offer a small speed up on the potentially
larger datasets even if it's not strictly best-practice.

That said, I don't suppose there's any option for the other variable
($date_string)?


Fair application, isn't it possible to create the view without the where exclusion of dates (making sure to add the date as a 
column) and then simply select form the view (for which you can really use variable binds) like so:


Select * from myView WHERE time_date > ?1

Obviously in any programming language there is a hundred solutions to the above, with all different efficacies, but from a purely 
SQL functionality point of view this is not really a supported feature. There is no point to having variables in SQLite because 
there are no Stored Procedures you can call with variable parameters. There are only Binds which is a programmatic solution, and can 
always be used in any query (as above).





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

Re: [sqlite] Variable values in Views

[Simon Slavin]

> On 28 Jul 2014, at 12:41pm, Jonathan Moules 
>  wrote:
> 
> *$table_prefix* which will be a number indicating which table set to look

You can't have variable table names in a prepared statement.  The process which 
does preparation has to know which table it will be accessing.  If you don't 
know which table you're be using, you can't use a system like that.

On the other hand, the fact that you're numbering tables suggests a badly 
formed schema.  Are you sure you shouldn't merge the tables and move that 
number to a column inside the table ?

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

[sqlite] Variable values in Views

[Jonathan Moules]

Hi List,
I have a view that works fine as-is, but I want to make it dynamic,
replacing a few values with variables.


SELECT
> service,
> sum( num ) AS num,
> round( ( sum( num ) * 100.0 ) /(
> SELECT sum( num )
> FROM* [$table_prefix]*_wms__getmap
> WHERE time_date > (SELECT datetime(max(time_date),
> *[$date_string]* ) FROM *[$table_prefix]*_all__request_type)
> ), 2 ) AS percent
> FROM *[$table_prefix]*_all_wms__getmap
> WHERE time_date > (SELECT datetime(max(time_date), *[$date_string]* )
> FROM *[$table_prefix]*_all__request_type)
> GROUP BY service
> ORDER BY sum( num ) DESC
>

There are two variables:
*$date_string* which will contain a string such as "-7 days" or "-1 month".

and
*$table_prefix* which will be a number indicating which table set to look
in.

I know SQLite can't do variables (for example
http://sqlite.1065341.n5.nabble.com/Using-local-variables-through-sqlite-td21270.html)
but does anyone have any suggestions for how this can be done? I don't want
to have to create tons of extra views if I don't have to.

Thanks,
Jonathan

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users