Re: [sqlite] Date and age calculations

[D. Richard Hipp]

On Sep 17, 2009, at 12:53 AM, Igor Tandetnik wrote:
>
>> Is there
>> any way I can make the SELECT more precise?
>
> Try this:
>
> WHERE ( date('now') - birth - (substr(date('now'), 6) < substr(birth,
> 6)) )
>BETWEEN 12 and 24
>


I didn't think about the BETWEEN operator.  How about this:

 WHERE birth BETWEEN date('now','-24 years') AND date('now','-12  
years')

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Date and age calculations

[D. Richard Hipp]

On Sep 16, 2009, at 11:49 PM, Craig Smith wrote:

> Hello:
>
> I am having difficulties getting precise returns using a SELECT that
> calculates the age of persons in my table.  All dates are -MM-DD,
> here is my SELECT statement:
>
> SELECT db_id, Full_name, round((SELECT julianday('now') - julianday
> (birth))/365,1) FROM members WHERE date ('now') - birth > 12 AND date
> ('now') - birth < 24 AND married = 'Single' AND moved = 0;

OK, let's see.  "date('now')" is a string in the format '-MM-DD'  
and "birth" is another string in the same format.  When you try to  
subtract one string from the other, SQLite tries to convert the  
strings into numbers as best it can.  In this case, it just extracts  
the  part as an integer.

So '2009-09-17' - '1997-12-12' is going to equal 12,  not 11.77 as you  
seemed to be expecting.  And '2009-09-17' - '1997-01-01' will also  
give 12, not 12.71.

Have you tried this instead:

 ... WHERE date('now') > date(birth, '+12 years') AND date('now')  
< date('birth',+24 years') ...



>
> The problem is that anyone over the age of 12 does not show up in the
> return until their age is actually nearer to 12.5.   However, when I
> change the SELECT to
>
> birth >= 12
>
> I pick up a few persons whose ages are between 11 and 12.  Is there
> any way I can make the SELECT more precise?
>
> Thank you very much,
>
> Craig Smith
> cr...@macscripter.net
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Date and age calculations

[Igor Tandetnik]

Craig Smith wrote:
> I am having difficulties getting precise returns using a SELECT that
> calculates the age of persons in my table.  All dates are -MM-DD,
> here is my SELECT statement:
>
> SELECT db_id, Full_name, round((SELECT julianday('now') - julianday
> (birth))/365,1) FROM members WHERE date ('now') - birth > 12 AND date
> ('now') - birth < 24 AND married = 'Single' AND moved = 0;

Why the nested select? Why not just

round((julianday('now') - julianday(birth))/365,1)

> The problem is that anyone over the age of 12 does not show up in the
> return until their age is actually nearer to 12.5.

That would depend on when in the year the person was born. Your 
condition "date ('now') - birth > 12" subtracts years while ignoring 
months and days (the minus operator implicitly converts strings to 
integers, and the conversion stops at the first non-digit character). So 
the condition would be almost correct for a person born in December, but 
will be one year off for someone born in January.

> However, when I
> change the SELECT to
>
> birth >= 12
>
> I pick up a few persons whose ages are between 11 and 12.

This condition is simply always true (strings compare greater than 
integers, regardless of actual values). I predict if you drop it 
completely you'll get the same results.

> Is there
> any way I can make the SELECT more precise?

Try this:

WHERE ( date('now') - birth - (substr(date('now'), 6) < substr(birth, 
6)) )
BETWEEN 12 and 24

This expression calculates full years of age (in other words, it 
increases by one each year on the person's birthday).

Igor Tandetnik 



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

Re: [sqlite] Date and age calculations

[Bart Smissaert]

This is a VB function I use that will give you the idea.
I think I got this with help from Dennis Cote.


Public Function AgeAtISO8601Date(ByVal strDOB As String, _
 ByVal strDate As String, _
 Optional ByVal strAlias As String) As String

  'will make a SQLite SQL that produces the age given a:
  'date of birth in ISO8601 format
  'date in ISO8601 format
  '-
  Dim strAS As String

  If Len(strAlias) > 0 Then
strAS = " AS "
  End If

  AgeAtISO8601Date = "case when date(" & strDOB & ", '+' || " & _
 "(strftime('%Y', " & strDate & ")" & _
 " - strftime('%Y', " & strDOB & ")) || " & _
 "' years') <= " & strDate & " then " & _
 "strftime('%Y', " & strDate & ")" & _
 " - strftime('%Y', " & strDOB & ") " & _
 "else " & _
 "strftime('%Y', " & strDate & ")" & _
 " - strftime('%Y', " & strDOB & ") -1 " & _
 "End" & _
 strAS & strAlias

End Function


RBS


On Thu, Sep 17, 2009 at 4:49 AM, Craig Smith  wrote:
> Hello:
>
> I am having difficulties getting precise returns using a SELECT that
> calculates the age of persons in my table.  All dates are -MM-DD,
> here is my SELECT statement:
>
> SELECT db_id, Full_name, round((SELECT julianday('now') - julianday
> (birth))/365,1) FROM members WHERE date ('now') - birth > 12 AND date
> ('now') - birth < 24 AND married = 'Single' AND moved = 0;
>
> The problem is that anyone over the age of 12 does not show up in the
> return until their age is actually nearer to 12.5.   However, when I
> change the SELECT to
>
> birth >= 12
>
> I pick up a few persons whose ages are between 11 and 12.  Is there
> any way I can make the SELECT more precise?
>
> Thank you very much,
>
> Craig Smith
> cr...@macscripter.net
>
>
>
> ___
> 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] Date and age calculations

[Craig Smith]

Hello:

I am having difficulties getting precise returns using a SELECT that  
calculates the age of persons in my table.  All dates are -MM-DD,  
here is my SELECT statement:

SELECT db_id, Full_name, round((SELECT julianday('now') - julianday 
(birth))/365,1) FROM members WHERE date ('now') - birth > 12 AND date  
('now') - birth < 24 AND married = 'Single' AND moved = 0;

The problem is that anyone over the age of 12 does not show up in the  
return until their age is actually nearer to 12.5.   However, when I  
change the SELECT to

birth >= 12

I pick up a few persons whose ages are between 11 and 12.  Is there  
any way I can make the SELECT more precise?

Thank you very much,

Craig Smith
cr...@macscripter.net



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

[sqlite] building permanently loaded extensions

[P Kishor]

pursuant to a recent email, I am wondering if I can build
extension-functions.c

so that it is permanently available within sqlite library, and not
just when loaded manually via a command. If I can, could someone
please guide me to a tutorial for doing so?




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

[sqlite] Convention for column type names?

[Dan Bishop]

I understand that SQLite lets you use arbitrary names for column types, 
and all that's significant is a few substrings like "INT", "CHAR", and 
"BLOB".  But what's the common practice?  Do you declare everything as 
INTEGER, TEXT, BLOB, REAL, or NUMERIC for a one-to-one match with the 
actual type affinities?  Or do you declare types like DATE, BOOLEAN, 
VARCHAR(20), SMALLINT, etc.?


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

[sqlite] Force the use of a specified index?

[Bart Smissaert]

Is it possible to tell SQLite to use a specified index?
I know you can use the + to excludes fields being used in an index,
but this doesn't help me in this particular case. I remember a
discussion about this and that this option might be added to SQLite,
but couldn't find it anywhere.

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

Re: [sqlite] Two feature requests

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tim Romano wrote:
> Requesting these here, since I'm not quite sure how to go about it via 
> the WIKI (do you simply edit the request list there and prepend|append 
> your request to the list?)

Generally you should enter them as tickets setting the type as enhancement
request.  You can see the open feature requests using the new Fossil source
control system at:

  http://www.sqlite.org/src/info/084941461f

The old cvstrac system has its requests at:

  http://www.sqlite.org/cvstrac/rptview?rn=8

(Yes it would be nice if they were merged :)

> 1. An IFEMPTY(a,b) operator would be a convenience, analogous to 
[...]
> 2. I would like to have a function that does a 
> standard LIKE comparison against a list of values:

First remember the 'Lite' part of SQLite.  Nothing is going to be added to
the core unless it is substantially useful and would be used by the vast
majority of users.  So far SQLite users have survived just fine without
these items :-)

There are separate contributed extensions where your requests are generally
more appropriate.  See the contrib page at http://www.sqlite.org/contrib and
in particular the last item - you'd find that author more receptive to your
requests.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqxVW4ACgkQmOOfHg372QSEFwCeLbJ9de0gOszqUgivMgWWdBRY
g04An2mY7/YDMjVa9KKbnh7uvFx4NYQo
=Ecf8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
> from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid
>
> Works also thanks.
> So you do not believe the following has a performance penalty ?
>
> SELECT pid, txt, price, qty-coalesce((SELECT sum(qty) FROM orders
> WHERE orders.pid = products.pid),0) FROM products

I don't see why it should, but when in doubt, test and measure.

Igor Tandetnik 



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

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

On Wed, Sep 16, 2009 at 9:50 PM, Igor Tandetnik  wrote:
> Gert Cuykens 
> wrote:
>> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik
>>  wrote:
>>> Gert Cuykens 
>>> wrote:
 On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik
  wrote:
>>>
> Perhaps your query could be a bit clearer when written this way:
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid

 This does not show me the new products that are not ordered yet
>>>
>>> Are you sure? I don't see why it wouldn't.
>>
>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
>> from PRODUCTS t left join ORDERS o on t.pid = o.pid
>>
>> shows 1 product because I only have 1 order with that product
>
> This query is different from the one I posted. Mine had "where t.pid =
> ?;", yours doesn't. If you want a report for all products, add the
> following clause:
>
> group by t.pid;
>
>> SELECT t.pid,
>>         t.txt,
>>         t.price,
>>         t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty"
>>    FROM PRODUCTS t
>> LEFT JOIN (SELECT o.pid,
>>                 SUM(o.qty) "qty_sold"
>>            FROM ORDERS o) qs ON qs."o.pid" = t.pid
>>
>> shows all products
>
> Compare this statement with the one you mentioned in your original post.
> Lacking mind-reading abilities, I was trying to help you with the
> statement you actually asked about, not the one you were thinking about.

select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid

Works also thanks.
So you do not believe the following has a performance penalty ?

SELECT pid, txt, price, qty-coalesce((SELECT sum(qty) FROM orders
WHERE orders.pid = products.pid),0) FROM products
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik
>  wrote:
>> Gert Cuykens 
>> wrote:
>>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik
>>>  wrote:
>>
 Perhaps your query could be a bit clearer when written this way:

 select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
 onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid
>>>
>>> This does not show me the new products that are not ordered yet
>>
>> Are you sure? I don't see why it wouldn't.
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
> from PRODUCTS t left join ORDERS o on t.pid = o.pid
>
> shows 1 product because I only have 1 order with that product

This query is different from the one I posted. Mine had "where t.pid = 
?;", yours doesn't. If you want a report for all products, add the 
following clause:

group by t.pid;

> SELECT t.pid,
> t.txt,
> t.price,
> t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty"
>FROM PRODUCTS t
> LEFT JOIN (SELECT o.pid,
> SUM(o.qty) "qty_sold"
>FROM ORDERS o) qs ON qs."o.pid" = t.pid
>
> shows all products

Compare this statement with the one you mentioned in your original post. 
Lacking mind-reading abilities, I was trying to help you with the 
statement you actually asked about, not the one you were thinking about.

Igor Tandetnik 



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

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik  wrote:
> Gert Cuykens 
> wrote:
>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik 
>> wrote:
>
>>> Perhaps your query could be a bit clearer when written this way:
>>>
>>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
>>> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid
>>
>> This does not show me the new products that are not ordered yet
>
> Are you sure? I don't see why it wouldn't.

select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
from PRODUCTS t left join ORDERS o on t.pid = o.pid

shows 1 product because I only have 1 order with that product

SELECT t.pid,
 t.txt,
 t.price,
 t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty"
FROM PRODUCTS t
LEFT JOIN (SELECT o.pid,
 SUM(o.qty) "qty_sold"
FROM ORDERS o) qs ON qs."o.pid" = t.pid

shows all products

>>> or this way
>>>
>>> select t.pid, t.txt, t.price, t.qty - IFNULL(
>>> (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
>>> from PRODUCTS t where t.pid = ?;
>>>
>>
>> I learned that this would be a performance issue doing it like that.
>>
>> http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity
>
> You "learned"? Do you mean, you measured and discovered this to be the
> case? The answer in that thread you are basing this claim on is largely
> nonsense, in my humble opinion. For one thing, your query only returns
> one row, so running a subselect "for every row returned" means running
> it once. For another, how does the poster believe joins are calculated -
> black magic?
>

The where t.pid=? should have been removed from my original question,
so it show the complete list of products.
I did not measured it, it sounded logic.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite uses a lot of memory during a delete operation

[shankar m]

Maybe LIMIT clause can be used. Please check the below link.

http://www.sqlite.org/lang_delete.html

Regards
Shankar

On Wed, Sep 9, 2009 at 5:16 PM, P Kishor  wrote:

> On Tue, Sep 8, 2009 at 10:48 PM, Benjamin Rutt  wrote:
> > On Mon, Sep 7, 2009 at 12:28 PM, P Kishor  wrote:
> >
> >> Find out if the DELETEion is chewing up the memory or the SELECTion. Try
> >>
> >> SELECT * FROM old_conns WHERE "end" >= strftime('%s', 'now', '-7 days');
> >>
> >> If the above is quick, you can simply create a new table with that,
> >> and then drop the old table.
> >>
> >> CREATE TABLE new_conns AS SELECT * FROM old_conns WHERE "end" >=
> >> strftime('%s', 'now', '-7 days');
> >>
> >> DROP TABLE old_conns;
> >>
> >
> > When I do the select as you suggested, the process remains contained to a
> > small amount of memory, which is good, but the result set from the select
> is
> > huge.  As I wrote originally, about 50% of the table would be deleted by
> my
> > delete, so about 50% of the table would be selected were I to use your
> > select.  So yes, I could create a tmp table and insert into it, add the
> > missing index, drop the old table, and rename the old to the new.
>
>
> Well, I would recreate indexes as the last step in the process, after
> I have dropped the old table. Indexes are probably significant in
> size.
>
> > This
> > would work, but seems to me is quite an expensive hack.  It would also
> > require 200% of the original table space on disk in the worst case, and
> may
> > necessitate an extra vacuum operation after the fact to conserve disk
> space
> > (the original db file is 8GB so I suspect it would double in size to 16GB
> in
> > the worst case).  So it is a workaround, but not a cheap one.
>
> Yes, but I am not sure if you actually tried the entire process and
> measured the space and time tradeoffs. From your statements above, it
> seems you haven't yet tried it since you write, "I could create a tmp
> table..."
>
> I would be curious to see if you get a decent space time tradeoff or
> not. It may or may not be worth it.
>
>
> >
> > Thanks.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor
> ___
> 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] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik 
> wrote:

>> Perhaps your query could be a bit clearer when written this way:
>>
>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
>> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid
>
> This does not show me the new products that are not ordered yet

Are you sure? I don't see why it wouldn't.

>> or this way
>>
>> select t.pid, t.txt, t.price, t.qty - IFNULL(
>> (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
>> from PRODUCTS t where t.pid = ?;
>>
>
> I learned that this would be a performance issue doing it like that.
>
> http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity

You "learned"? Do you mean, you measured and discovered this to be the 
case? The answer in that thread you are basing this claim on is largely 
nonsense, in my humble opinion. For one thing, your query only returns 
one row, so running a subselect "for every row returned" means running 
it once. For another, how does the poster believe joins are calculated - 
black magic?

Igor Tandetnik 



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

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik  wrote:
> Gert Cuykens 
> wrote:
>>   SELECT t.pid,
>>          t.txt,
>>          t.price,
>>          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
>>     FROM PRODUCTS t
>> LEFT JOIN (SELECT o.pid,
>>                  SUM(o.qty) 'qty_sold'
>>             FROM ORDERS o) qs ON qs.pid = t.pid
>>    WHERE t.pid = ?
>>
>> i have trouble running this statement on sqlite3
>> It tels me qs.pid does not exist
>
> And indeed it doesn't. But there is a column named qs."o.pid". You may
> want to assign an alias to this column, just as you did with qs.qty_sold
>
>> when i rename it to pid it subtracts
>> the wrong values
>
> Because now it refers to t.pid, and the condition (t.pid = t.pid) is
> always true.

qs."o.pid" = t.pid works thanks

> Perhaps your query could be a bit clearer when written this way:
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
> from PRODUCTS t left join ORDERS o on t.pid = o.pid

This does not show me the new products that are not ordered yet

> or this way
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(
>    (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
> from PRODUCTS t where t.pid = ?;
>

I learned that this would be a performance issue doing it like that.

http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select records by specific YEAR

[Simon Slavin]

On 16 Sep 2009, at 6:05pm, vbshar wrote:

> I think the problem is in field format ou data format, because if i  
> insert
> data from my app SQLite does not return any value, but if i insert  
> data with
> the manager "SQLite developer"; the query works...


Your definition

>> BIRTH à DATETIME

just creates a NUMERIC field, not a field with a type of DATETIME.  Or  
at least that's what I understand from section 2.1 of

http://www.sqlite.org/datatype3.html


SQLite does not use string typing for its table contents.  You might  
define a column as INTEGER but you can still insert a text value into  
it.  Make sure that you are distinguishing between the integer value  
and the text value when you INSERT the row, as well as when you try to  
SELECT the row.  Many management tools get this wrong: some assume  
that all values are text values, and some assume that any value which  
contains just digits can never be a text value.  It might help if you  
use the sqlite3 command-line tool to insert a few records and perform  
a SELECT, and see if you get the same results as your management tool  
gets.

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

Re: [sqlite] Select records by specific YEAR

[Pavel Ivanov]

Maybe. Also there's a question when I wanted to ask in the first
letter too: where did you get function year() from? I don't see it in
http://www.sqlite.org/lang_datefunc.html and my sqlite3 doesn't see it
too:

sqlite> select year(date('now'));
SQL error: no such function: year

Maybe it's SQLite developer who registers it as user-defined function?
So you better to look into its documentation and learn what date
format it understands.

Pavel

On Wed, Sep 16, 2009 at 1:05 PM, vbshar  wrote:
>
> I think the problem is in field format ou data format, because if i insert
> data from my app SQLite does not return any value, but if i insert data with
> the manager "SQLite developer"; the query works...
>
> Pavel Ivanov-2 wrote:
>>
>> Did you try to put 1946 without quotes?
>>
>> Pavel
>>
>> On Wed, Sep 16, 2009 at 12:21 PM, vbshar  wrote:
>>>
>>> Hi,
>>>
>>> I have a table called PEOPLE with 2 fiels:
>>>
>>> NAME à TEXT 40
>>> BIRTH à DATETIME
>>>
>>> With some records
>>>
>>> I want to filter all people with BIRTH = 1946, I’m trying this:
>>>
>>> SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)=’1946’;
>>>
>>> There are several people in this year, but recordset all time returns 0
>>> records… what's the problem?
>>>
>>>
>>> Best Regards
>>> Paulo
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Select-records-by-specific-YEAR-tp25475857p25475857.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>>
>>> ___
>>> 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
>>
>>
>
> --
> View this message in context: 
> http://www.nabble.com/Select-records-by-specific-YEAR-tp25475857p25476648.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] Select records by specific YEAR

[vbshar]

I think the problem is in field format ou data format, because if i insert
data from my app SQLite does not return any value, but if i insert data with
the manager "SQLite developer"; the query works...

Pavel Ivanov-2 wrote:
> 
> Did you try to put 1946 without quotes?
> 
> Pavel
> 
> On Wed, Sep 16, 2009 at 12:21 PM, vbshar  wrote:
>>
>> Hi,
>>
>> I have a table called PEOPLE with 2 fiels:
>>
>> NAME à TEXT 40
>> BIRTH à DATETIME
>>
>> With some records
>>
>> I want to filter all people with BIRTH = 1946, I’m trying this:
>>
>> SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)=’1946’;
>>
>> There are several people in this year, but recordset all time returns 0
>> records… what's the problem?
>>
>>
>> Best Regards
>> Paulo
>> --
>> View this message in context:
>> http://www.nabble.com/Select-records-by-specific-YEAR-tp25475857p25475857.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> 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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Select-records-by-specific-YEAR-tp25475857p25476648.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Select records by specific YEAR

[Pavel Ivanov]

Did you try to put 1946 without quotes?

Pavel

On Wed, Sep 16, 2009 at 12:21 PM, vbshar  wrote:
>
> Hi,
>
> I have a table called PEOPLE with 2 fiels:
>
> NAME à TEXT 40
> BIRTH à DATETIME
>
> With some records
>
> I want to filter all people with BIRTH = 1946, I’m trying this:
>
> SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)=’1946’;
>
> There are several people in this year, but recordset all time returns 0
> records… what's the problem?
>
>
> Best Regards
> Paulo
> --
> View this message in context: 
> http://www.nabble.com/Select-records-by-specific-YEAR-tp25475857p25475857.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] Select records by specific YEAR

[vbshar]

Hi,

I have a table called PEOPLE with 2 fiels:

NAME à TEXT 40
BIRTH à DATETIME

With some records

I want to filter all people with BIRTH = 1946, I’m trying this:

SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)=’1946’;

There are several people in this year, but recordset all time returns 0
records… what's the problem?


Best Regards
Paulo
-- 
View this message in context: 
http://www.nabble.com/Select-records-by-specific-YEAR-tp25475857p25475857.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Creating custom function for recursive queries

[Igor Tandetnik]

Marcel Strittmatter 
wrote:
> I implemented a custom function that returns a comma separated list of
> primary keys as a string by making recursive queries. This works well
> if I don't use subqueries. But I like to use subqueries like this
>
> SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));
>
> Is it possible to implement a custom function where the result can be
> used in a subquery like above?

No, not really. SQLite doesn't have table-valued functions. SQLite does 
have the concept of a virtual table:

http://www.sqlite.org/c3ref/module.html

Maybe you could do something like what you want with those. I don't know 
enough about them to comment further.


With the function as written, you can do something like this:

SELECT * FROM users WHERE
','||parents('relations', 3)||',' LIKE '%,'||id||',%';

I don't really recommend this monstrosity, just mention it for 
completeness.

Igor Tandetnik 



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

Re: [sqlite] Temporal SQLite database

[Jean-Christophe Deschamps]

Doug,

At 19:47 15/09/2009, you wrote:
´¯¯¯
>I'm not sure if you are looking to make a entry unique, or determine 
>the order in which the entries occurred. In either case, be aware - 
>time can go *backwards* on a system, especially if it is being syncd 
>to an outside source such as with NTP.
>
>Normally the 'jitter' is under a second, but exceptions do occur 
>(including the one where the Sysadmin changes the system clock!). Note 
>that a backward "jitter" could (conceivably) result in the same 
>timestamp occurring twice. Also, depending on the resolution of the 
>clock (which may vary depending on installation options) it may be 
>possible for two entries to occur at the same 'time'. As a result, 
>I've sworn off using the time for anything more than a logging label.
`---

That's very true.

Time also sometimes get "weird", i.e. UTC time at leap seconds. 
Depending on the time source, system settings and possibly some untold 
libraries, not all installations cope gracefully with timestamp = 
2008/12/31 23:59:60 xxx.  Some systems, when their are informed well 
ahead of the next occurence of a leap second, shift time gradually over 
weeks so as to avoid a 60th second.

I confess this is a relatively rare event, but it does occur. For 
applications dealing with massive 24/7 input, the chance of recording 
rows during such leap second is 100%.  It seems to be no problem when 
recording an epoch-type timestamp, but when this is displayed or 
selected, such leap second is likely to shift the timestamp to the next 
month or year, which can make a huge difference in particular situations.

There are discussions about getting rid of or maintaining leap seconds 
within UTC.  Nonetheless and independently of future decisions those 
that have already occured... well, they have occured!

Google for leap seconds.  In particular Wikipedia give the schedule and 
also mentions:

Several arguments for the abolition have been presented. Some of these 
have only become relevant with the recent proliferation of computers 
using UTC as their internal time representation. For example, currently 
it is not possible to correctly compute the elapsed interval between 
two instants of UTC without consulting manually updated and maintained 
tables of when leap seconds have occurred. Moreover, it is not possible 
even in theory to compute such time intervals for instants more than 
about six months in the future. This is not a matter of computer 
programmers being "lazy"; rather, the uncertainty of leap seconds 
introduces to those applications needing accurate notions of elapsed 
time intervals either fundamentally new (and often untenable) 
operational burdens for computer systems (the need to do online 
lookups) or insurmountable theoretical concerns (the inability in a 
UTC-based computer to accurately schedule any event more than six 
months in the future to within a few seconds).


Posted outside any leap second to avoid any embarassment.



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

[sqlite] Best approach for storing not-so-small BLOBs per record

[Itamar Syn-Hershko]

Hi all,
 
I'm in the design phase of an application with SQLite backend. The SQLite
file will hold a table of about 20K records initially, and a few several
other small tables. About 75% of the records in the large table will have
binary data associated with it. My main question is which one of the
following options I'm better off with to store those BLOBs, in terms of DB
efficiency, memory usage, media seeks (since this will most likely to reside
on a CD) and file size. The storage options I see relevant are:
 
1. BLOBs in the original table in a per-record basis (records with no BLOBs
NULLified). If separating the BLOBs from this table will help performance in
any way, I see two further options:
2. BLOBs in a separate table, and having the unique ID of the record in the
large table point at this. No indices necessary, and will never use JOINs in
queries since that table will be accessed explicitly on-demand only.
3. Same as #2 above, except in a separated, joint SQLite file (to aid file
seeks).
 
As mentioned, the binaries I'll be storing will only be pulled on demand
(most queries to the large table will return the accompanying meta-data
WITHOUT the binary data); no JOINs or foreign indices necessary. The average
BLOB size is a few 10s of KBs; anyway I do not expect to have a BLOB over
1-2MBs. In the shelf version writes to the DB (particularly the large table)
will very rarely occur; mostly only read operations, so I'm willing to take
any cost to write operations.
 
Also, looking up on compression support with SQLite I found 2 solutions -
CEROD [1] and per-field compression using zlib and extension functions to
compress / decompress. Are there more options I might have missed?
 
Thanks in advance for any advice on this.
 
Itamar.
 
[1] http://www.hwaci.com/sw/sqlite/cerod.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema and database layout

[Kavita Raghunathan]

- Original Message -
From: "P Kishor" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, September 16, 2009 9:00:29 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout



>Google for help with designing relational databases. You will get 36
>million hits.


KR: You lied; I only got 4 million hits :-)
Thanks!

Kavita

>
> CREATE TABLE TypeNumbers (
>    ID INTEGER PRIMARY KEY AUTOINCREMENT,
>    table_type INTEGER,
>    MainID INTEGER,
>    Type INTEGER,
>    CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
>  wrote:
>> Please see clarifications below:
>> - Original Message -
>> From: "Pavel Ivanov" 
>> To: "General Discussion of SQLite Database" 
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>
>> Create 1 table with 1 additional column "schema" which will contain
>> numbers from 1 to 10.
>>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>>
>> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>>       strcat(sqlStr,"MainID INTEGER,");
>>       strcat(sqlStr,"Type INTEGER,");
>>       strcat(sqlStr,"CurrentNumber INTEGER);");
>>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>>
>>> 2)Also, do you reccomend a different .db file for each schema so that 
>>> multiple threads can easily operate at the same time ?
>>
>> It depends on how your threads will operate, how often they will issue
>> sql statements, what type of sql statements and all other details
>> about your application functionality.
>>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>> Maybe I need to think this through, even as I write this:-)
>>
>> Thanks, Pavel!
>>
>>
>> On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
>>  wrote:
>>> All,
>>> I have a couple of questions:
>>>
>>> I've been studying the C/C++ interface, and
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 10 CREATE table commands ? 
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>>
>>> 2)Also, do you reccomend a different .db file for each schema so that 
>>> multiple threads can easily operate at the same time ?
>>>
>>> I apologise in advance if I missed it in the documentation.
>>>
>>> Thanks again to this group for your awesome support. I've got a lot of 
>>> ideas from you all.
>>>
>>> Regards,
>>> Kavita
>>> ___
>>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> 

Re: [sqlite] Temporal SQLite database

[Alberto Simões]

Hello, Doug

Thanks for your message. That helped a lot.
ambs

On Tue, Sep 15, 2009 at 6:47 PM, Douglas E. Fajardo <
dfaja...@beyondtrust.com> wrote:

> I'm not sure if you are looking to make a entry unique, or determine the
> order in which the entries occurred. In either case, be aware - time can go
> *backwards* on a system, especially if it is being syncd to an outside
> source such as with NTP.
>
> Normally the 'jitter' is under a second, but exceptions do occur (including
> the one where the Sysadmin changes the system clock!). Note that a backward
> "jitter" could (conceivably) result in the same timestamp occurring twice.
> Also, depending on the resolution of the clock (which may vary depending on
> installation options) it may be possible for two entries to occur at the
> same 'time'. As a result, I've sworn off using the time for anything more
> than a logging label.
>
> I've had good success with the auto-increment approach.  As for code to
> hide it, just set auto-incrementing up on a field in the schema, and there
> is no extra code involved.
>
> *** Doug F.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Alberto Simões
> Sent: Tuesday, September 15, 2009 4:38 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Temporal SQLite database
>
> Dear SQLite users,
>
> I am preparing a temporal database. Basically, entries will have a compound
> key composed by the real key and some kind of time stamp. This is not
> complicated to do, but I would like to know if anybody did something
> similar
> and if:
>  - used the number of non-leap seconds since whatever time the system
> considers to be the epoch, as time stamp
>  - used an auto-increment value as the time-stamp, and adding the time
> stamp
> as an auxiliary column,
>  - knows of any Perl module that hides this time management.
>
> Thanks for your ideas.
> ambs
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Creating custom function for recursive queries

[Simon Davies]

2009/9/16 Marcel Strittmatter :
> Hi
>
> I implemented a custom function that returns a comma separated list of
> primary keys as a string by making recursive queries. This works well
> if I don't use subqueries. But I like to use subqueries like this
>
> SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));
>
> Below some example data I used to test my custom function...
>
> Is it possible to implement a custom function where the result can be
> used in a subquery like above?

Yes - you seem to have achieved that.

>
> Regards
> Marcel
>
>
> Here a dump of an example database:
>
> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
> INSERT INTO "users" VALUES(1,'Marge');
> INSERT INTO "users" VALUES(2,'Homer');
> INSERT INTO "users" VALUES(3,'Lisa');
> INSERT INTO "users" VALUES(4,'Bart');
> INSERT INTO "users" VALUES(5,'Maggie');
> CREATE TABLE relations (parent INTEGER, child INTEGER);
> INSERT INTO "relations" VALUES(1,3);
> INSERT INTO "relations" VALUES(1,4);
> INSERT INTO "relations" VALUES(1,5);
> INSERT INTO "relations" VALUES(2,3);
> INSERT INTO "relations" VALUES(2,4);
>
> And here the output of my custom function (parents), which is similar
> to the builtin function group_concat():
>
> sqlite> SELECT name,parents('relations', id) FROM users WHERE id = 3;
> name        parents('relations', id)
> --  
> Lisa        1,2
>
> But the following query returns nothing
>
> SELECT name FROM users WHERE id IN (SELECT parents('relations', id)
> FROM users WHERE id = 3);
>
> because the result of the custom function is a string ("1,2")...

Indeed. But your custom function is working just fine.

Why not just use

SELECT name FROM users WHERE id IN (SELECT parent from relations WHERE
child = 3);

??

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

[sqlite] Creating custom function for recursive queries

[Marcel Strittmatter]

Hi

I implemented a custom function that returns a comma separated list of  
primary keys as a string by making recursive queries. This works well  
if I don't use subqueries. But I like to use subqueries like this

SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));

Below some example data I used to test my custom function...

Is it possible to implement a custom function where the result can be  
used in a subquery like above?

Regards
Marcel


Here a dump of an example database:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "users" VALUES(1,'Marge');
INSERT INTO "users" VALUES(2,'Homer');
INSERT INTO "users" VALUES(3,'Lisa');
INSERT INTO "users" VALUES(4,'Bart');
INSERT INTO "users" VALUES(5,'Maggie');
CREATE TABLE relations (parent INTEGER, child INTEGER);
INSERT INTO "relations" VALUES(1,3);
INSERT INTO "relations" VALUES(1,4);
INSERT INTO "relations" VALUES(1,5);
INSERT INTO "relations" VALUES(2,3);
INSERT INTO "relations" VALUES(2,4);

And here the output of my custom function (parents), which is similar  
to the builtin function group_concat():

sqlite> SELECT name,parents('relations', id) FROM users WHERE id = 3;
nameparents('relations', id)
--  
Lisa1,2

But the following query returns nothing

SELECT name FROM users WHERE id IN (SELECT parents('relations', id)  
FROM users WHERE id = 3);

because the result of the custom function is a string ("1,2")...

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

Re: [sqlite] Schema and database layout

[Pavel Ivanov]

>   Would this be a better design ?

That would be definitely a better design at least because you will be
able to keep only one prepared statement where one of bindings will be
your entity id. Also you will be able to add more types of entities
without changing database schema. Just don't forget to add proper
index where first field will be entity id.
But each design has its caveats of course. And if your application
works in 10 threads with shared cache mode turned on and each thread
updates or adds only one type of entities then with 10 different
tables you'll have better concurrent updates than with 1 table. It
doesn't matter though if your application works in 10 different
processes.

>   Can you point me to some database design docs that dictates that tables of
>   the same type should be 1 single table ?

Sorry, can't help with this.

Pavel

On Wed, Sep 16, 2009 at 9:57 AM, Kavita Raghunathan
 wrote:
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>
> You better stop telling puzzles and explain everything in thorough
> details so that we could understand it.
> "entity-id" - what is it? There's no such column in the table, you've
> never spoken about any entity, so we can just guess... "this table
> "type" - what table type? Give the definition please what you're
> calling "table type". "I want to just pass this table "type" - pass
> what, where and doing what? What are you doing that is hard and with
> "passing table type" will be easier?
> And continuing to look into my crystal ball I still don't understand
> why my suggestion doesn't suit you. You have 10 tables with exactly
> the same structure, exactly the same set of columns but with different
> types of data. But type of data doesn't matter when it comes to
> storage of this data in the database. The only thing that matters here
> is the set of columns. So you can create table like this:
>
>>KR:
>   I apologise, I did not intend to be vague; you made me realize how
>   vague I was. Also, I'm new to database design. I surely
>   appreciate your help, Pavel.
>
>  Here it is:
>   I have 10 tables with the exact same schema:
>   int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
>   Each of the 10 tables belongs to 1 entity, uniquely identified by an 
> entity-id.
>   I was planning on having 10 tables named like so: typenumber_.
>   and not include entity_id into the schema.
>   But from reading your email, it sounds like i could just have 1 table, and
>   add entity id to it. So, add a 5th column like this:
>   int, int, varchar(10), varchar(20), int(this last one is the entity id and
>   it would be identical every 10 entries or so)
>   Would this be a better design ?
>   Can you point me to some database design docs that dictates that tables of
>   the same type should be 1 single table ?
>
>
>
> CREATE TABLE TypeNumbers (
>    ID INTEGER PRIMARY KEY AUTOINCREMENT,
>    table_type INTEGER,
>    MainID INTEGER,
>    Type INTEGER,
>    CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
>  wrote:
>> Please see clarifications below:
>> - Original Message -
>> From: "Pavel Ivanov" 
>> To: "General Discussion of SQLite Database" 
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 

Re: [sqlite] Schema and database layout

[P Kishor]

On Wed, Sep 16, 2009 at 8:57 AM, Kavita Raghunathan
 wrote:
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>
> You better stop telling puzzles and explain everything in thorough
> details so that we could understand it.
> "entity-id" - what is it? There's no such column in the table, you've
> never spoken about any entity, so we can just guess... "this table
> "type" - what table type? Give the definition please what you're
> calling "table type". "I want to just pass this table "type" - pass
> what, where and doing what? What are you doing that is hard and with
> "passing table type" will be easier?
> And continuing to look into my crystal ball I still don't understand
> why my suggestion doesn't suit you. You have 10 tables with exactly
> the same structure, exactly the same set of columns but with different
> types of data. But type of data doesn't matter when it comes to
> storage of this data in the database. The only thing that matters here
> is the set of columns. So you can create table like this:
>
>>KR:
>   I apologise, I did not intend to be vague; you made me realize how
>   vague I was. Also, I'm new to database design. I surely
>   appreciate your help, Pavel.
>
>  Here it is:
>   I have 10 tables with the exact same schema:
>   int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
>   Each of the 10 tables belongs to 1 entity, uniquely identified by an 
> entity-id.
>   I was planning on having 10 tables named like so: typenumber_.
>   and not include entity_id into the schema.
>   But from reading your email, it sounds like i could just have 1 table, and
>   add entity id to it. So, add a 5th column like this:
>   int, int, varchar(10), varchar(20), int(this last one is the entity id and
>   it would be identical every 10 entries or so)
>   Would this be a better design ?

Yes, this would be a better design.

>   Can you point me to some database design docs that dictates that tables of
>   the same type should be 1 single table ?
>
>

Google for help with designing relational databases. You will get 36
million hits.


>
> CREATE TABLE TypeNumbers (
>    ID INTEGER PRIMARY KEY AUTOINCREMENT,
>    table_type INTEGER,
>    MainID INTEGER,
>    Type INTEGER,
>    CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
>  wrote:
>> Please see clarifications below:
>> - Original Message -
>> From: "Pavel Ivanov" 
>> To: "General Discussion of SQLite Database" 
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>
>> Create 1 table with 1 additional column "schema" which will contain
>> numbers from 1 to 10.
>>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>>
>> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>>       strcat(sqlStr,"MainID INTEGER,");
>>       strcat(sqlStr,"Type INTEGER,");
>>       strcat(sqlStr,"CurrentNumber INTEGER);");
>>
>>> Would there be a way I can pass the schema as well as data into 

Re: [sqlite] Schema and database layout

[Kavita Raghunathan]

- Original Message -
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout

> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.

You better stop telling puzzles and explain everything in thorough
details so that we could understand it.
"entity-id" - what is it? There's no such column in the table, you've
never spoken about any entity, so we can just guess... "this table
"type" - what table type? Give the definition please what you're
calling "table type". "I want to just pass this table "type" - pass
what, where and doing what? What are you doing that is hard and with
"passing table type" will be easier?
And continuing to look into my crystal ball I still don't understand
why my suggestion doesn't suit you. You have 10 tables with exactly
the same structure, exactly the same set of columns but with different
types of data. But type of data doesn't matter when it comes to
storage of this data in the database. The only thing that matters here
is the set of columns. So you can create table like this:

>KR: 
   I apologise, I did not intend to be vague; you made me realize how
   vague I was. Also, I'm new to database design. I surely 
   appreciate your help, Pavel.

  Here it is:
   I have 10 tables with the exact same schema:
   int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
   Each of the 10 tables belongs to 1 entity, uniquely identified by an 
entity-id.
   I was planning on having 10 tables named like so: typenumber_.
   and not include entity_id into the schema.
   But from reading your email, it sounds like i could just have 1 table, and
   add entity id to it. So, add a 5th column like this:
   int, int, varchar(10), varchar(20), int(this last one is the entity id and 
   it would be identical every 10 entries or so)
   Would this be a better design ?
   Can you point me to some database design docs that dictates that tables of 
   the same type should be 1 single table ?



CREATE TABLE TypeNumbers (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
table_type INTEGER,
MainID INTEGER,
Type INTEGER,
CurrentNumber INTEGER);

And here is you "table_type" that will have numbers 1 to 10 and you
will be able "to pass it to SQLite" (whatever that means).

>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.

It doesn't.

> KR: Different threads are going to access different tables of the same table 
> type.

Even more puzzles here. I thought your table type is just one table,
it seems that you have several tables of the same type but why? And
what's the difference between tables of different types? Does this
mean that you have more than 10 tables? Do you have only 10 "table
types"?

> Maybe I need to think this through, even as I write this:-)

That definitely will help. :-)


Pavel

On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
 wrote:
> Please see clarifications below:
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>
> Create 1 table with 1 additional column "schema" which will contain
> numbers from 1 to 10.
>
> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.
>
> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>       strcat(sqlStr,"MainID INTEGER,");
>       strcat(sqlStr,"Type INTEGER,");
>       strcat(sqlStr,"CurrentNumber INTEGER);");
>
>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.
>
>> 2)Also, do you reccomend a different .db file for each schema so that 
>> multiple threads can easily operate at the same time ?
>
> It depends on how your threads will operate, 

[sqlite] Two feature requests

[Tim Romano]

Requesting these here, since I'm not quite sure how to go about it via 
the WIKI (do you simply edit the request list there and prepend|append 
your request to the list?)

1. An IFEMPTY(a,b) operator would be a convenience, analogous to 
IFNULL(a,b). It would return the first non-null, non-empty-string value 
from the choices a,b.

2. I see that SQLite has already implemented a LIKE(a,b) function., so 
the name for this request can't be the ideal one.
Just as the WHERE COLUMN IN(VAL1, VAL3, VALn, ...) function compares the 
value in COLUMN against the items in the IN(...) list, and returns TRUE 
if any of them are equal to it (a shorthand form of COLUMN=VAL1 OR 
COLUMN=VAL2 OR COLUMN=VAL3), I would like to have a function that does a 
standard LIKE comparison against a list of values:

where COLUMN MATCHES('anthro%','paleo%','%morph%').

This avoids having to repeat the column name over and over with many  OR 
operators.

Thanks





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

Re: [sqlite] Schema and database layout

[Pavel Ivanov]

> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.

You better stop telling puzzles and explain everything in thorough
details so that we could understand it.
"entity-id" - what is it? There's no such column in the table, you've
never spoken about any entity, so we can just guess... "this table
"type" - what table type? Give the definition please what you're
calling "table type". "I want to just pass this table "type" - pass
what, where and doing what? What are you doing that is hard and with
"passing table type" will be easier?
And continuing to look into my crystal ball I still don't understand
why my suggestion doesn't suit you. You have 10 tables with exactly
the same structure, exactly the same set of columns but with different
types of data. But type of data doesn't matter when it comes to
storage of this data in the database. The only thing that matters here
is the set of columns. So you can create table like this:

CREATE TABLE TypeNumbers (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
table_type INTEGER,
MainID INTEGER,
Type INTEGER,
CurrentNumber INTEGER);

And here is you "table_type" that will have numbers 1 to 10 and you
will be able "to pass it to SQLite" (whatever that means).

>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.

It doesn't.

> KR: Different threads are going to access different tables of the same table 
> type.

Even more puzzles here. I thought your table type is just one table,
it seems that you have several tables of the same type but why? And
what's the difference between tables of different types? Does this
mean that you have more than 10 tables? Do you have only 10 "table
types"?

> Maybe I need to think this through, even as I write this:-)

That definitely will help. :-)


Pavel

On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
 wrote:
> Please see clarifications below:
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>
> Create 1 table with 1 additional column "schema" which will contain
> numbers from 1 to 10.
>
> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.
>
> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>       strcat(sqlStr,"MainID INTEGER,");
>       strcat(sqlStr,"Type INTEGER,");
>       strcat(sqlStr,"CurrentNumber INTEGER);");
>
>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.
>
>> 2)Also, do you reccomend a different .db file for each schema so that 
>> multiple threads can easily operate at the same time ?
>
> It depends on how your threads will operate, how often they will issue
> sql statements, what type of sql statements and all other details
> about your application functionality.
>
> KR: Different threads are going to access different tables of the same table 
> type.
> Maybe I need to think this through, even as I write this:-)
>
> Thanks, Pavel!
>
>
> On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
>  wrote:
>> All,
>> I have a couple of questions:
>>
>> I've been studying the C/C++ interface, and
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>> with the same schema. How would I prevent doing 10 CREATE table commands ? 
>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> 2)Also, do you reccomend a different .db file for each schema so that 
>> multiple threads can easily operate at the same time ?
>>
>> I apologise in advance if I missed it in the documentation.
>>
>> Thanks again to this group for your awesome support. I've got a lot of ideas 
>> from you all.
>>
>> Regards,
>> Kavita
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> 

Re: [sqlite] Temporal SQLite database

[Douglas E. Fajardo]

I'm not sure if you are looking to make a entry unique, or determine the order 
in which the entries occurred. In either case, be aware - time can go 
*backwards* on a system, especially if it is being syncd to an outside source 
such as with NTP.

Normally the 'jitter' is under a second, but exceptions do occur (including the 
one where the Sysadmin changes the system clock!). Note that a backward 
"jitter" could (conceivably) result in the same timestamp occurring twice. 
Also, depending on the resolution of the clock (which may vary depending on 
installation options) it may be possible for two entries to occur at the same 
'time'. As a result, I've sworn off using the time for anything more than a 
logging label.

I've had good success with the auto-increment approach.  As for code to hide 
it, just set auto-incrementing up on a field in the schema, and there is no 
extra code involved.

*** Doug F.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Alberto Simões
Sent: Tuesday, September 15, 2009 4:38 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Temporal SQLite database

Dear SQLite users,

I am preparing a temporal database. Basically, entries will have a compound
key composed by the real key and some kind of time stamp. This is not
complicated to do, but I would like to know if anybody did something similar
and if:
 - used the number of non-leap seconds since whatever time the system
considers to be the epoch, as time stamp
 - used an auto-increment value as the time-stamp, and adding the time stamp
as an auxiliary column,
 - knows of any Perl module that hides this time management.

Thanks for your ideas.
ambs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users