Re: [sqlite] Parsing create statements

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

BareFeet wrote:
> At the moment I am resorting to developing regular expressions to do the 
> parsing. They work, but it seems to be re-inventing the wheel.

You won't be able to do parsing completely with regular expressions.  Create
statements let you specify default values for a column and that can be any
arbitrary SQL expression.

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

iEYEARECAAYFAktX+dQACgkQmOOfHg372QTTGgCdFyuP0MqeCntd/i1QpxvXD+O9
BTUAoNngur+HgxyToFjbr3ErYUMNdQQm
=mWRi
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Dan Kennedy]

On Jan 21, 2010, at 1:54 AM, Pavel Ivanov wrote:

>> Why the difference in search time between searching individually and
>> searching together?
>
> Apparently SQLite is not smart enough to optimize the search for both
> min and max to make double entrance to the index - first from the
> beginning, then from the end. It does search through the full index
> instead which is not much better than search through the full table
> (as it does without the index). But when searched separately SQLite
> understands that it can pick up just first or just last entry from the
> index.

Exactly right.

   http://www.sqlite.org/optoverview.html#minmax

>
>
> Pavel
>
> On Wed, Jan 20, 2010 at 1:47 PM, Robert Citek  
>  wrote:
>> On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov   
>> wrote:
>>> I thought at the first moment that Pavel's suggestion on using  
>>> extra index
>>> is very "expensive" in terms of megabytes on the disk, but despite  
>>> this
>>> drawback it's actually more robust.
>>
>> For my own curiosity I created a table with random text data and
>> discovered there was only a negligible difference between using
>> indexes and not when search for min and max in the same query.
>> However, when searching for min or max in separate queries the answer
>> was almost instantaneous. (details below).
>>
>> Why the difference in search time between searching individually and
>> searching together?
>>
>> Regards,
>> - Robert
>>
>> -
>>
>> $ sqlite3 sample.db 'drop table if exists sample ;
>> create table sample (foo text) ; '
>>
>> $ jot -r -c 8000 a z |
>> tr -d '\n' |
>> fold -w 8 |
>> fmt -w 8 |
>> sqlite3 sample.db '.imp "/dev/stdin" "sample"'
>>
>> $ sqlite3 sample.db 'select count(*) from sample ; '
>> 1000
>>
>> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from  
>> sample ; '
>> aaaq|ytyd
>> real 3.24
>> user 3.10
>> sys 0.11
>>
>> $ time -p sqlite3 sample.db 'create index sample_foo on sample  
>> (foo) ; '
>> real 838.92
>> user 53.68
>> sys 38.46
>>
>> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from  
>> sample ; '
>> aaaq|ytyd
>> real 3.19
>> user 3.13
>> sys 0.06
>>
>> $ time -p sqlite3 sample.db 'select min(foo) from sample ; '
>> aaaq
>> real 0.00
>> user 0.00
>> sys 0.00
>>
>> $ time -p sqlite3 sample.db 'select max(foo) from sample ; '
>> ytyd
>> real 0.00
>> user 0.00
>> sys 0.01
>>
>> $ sqlite3 --version
>> 3.6.10
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Parsing create statements

[BareFeet]

Hi All,

Following up my own post:

> Is there any way to parse a create statement (eg create table, create view, 
> create trigger) into its components?

At the moment I am resorting to developing regular expressions to do the 
parsing. They work, but it seems to be re-inventing the wheel.

For instance, I can parse a create table statement into two strings: table 
name, and combined column definitions and table constraints using:

(?xis)  # Parse create table 
statement into:
^\s*
create\s+table\s+
(\w+)\s*# 1 table name
\([\r\n]*
(.*)\s* # 2 columns and 
constraints
\)
\s*
;?\s*
$   # end of text

Then I can separate the column definitions and table constraints using:

(?xis)  # Parse columns and 
constraints into:
^
(.+?)   # 1 column definitions
(?: ,
(   # 2 table constraint 
definitions
(?<=,)  # preceding comma, not 
captured
\s*
(?: constraint\s+\w+\s+)?
(?: primary\s+key
|   unique
|   check
|   foreign\s+key
)\b
.*
)*
)?
$

Then parse the column definitions into column name, type, column constraints 
using:

(?xis)  # Parse table column 
definitions into:
(?:^|(?<=,))# start or leading comma
\s*
(\w+?)  # 1 column name
\b\s*
([^,]*?)?   # 2 column type
\b\s*
(   \s*
(?:constraint\s+\w+\s+)?# 3 column constraints
\b
(?: primary\s+key
|   not\s+null
|   unique
|   check
|   default
|   collate
|   references
)
\b[^,]*
)*
(?:,|$) # trailing comma or end of text

So, something like this:

create table People
(   ID integer primary key
,   Name text collate nocase
,   Family_ID integer
references Family(ID)
)

Becomes:

table name: People
table columns & constraints:

ID integer primary key
,   Name text collate nocase
,   Family_ID integer
references Family(ID)

My second parser would separate the column definitions and table constraints, 
but since there are no table constraints in this sample, I'll skip it.

My third parser divides up the column definitions to give:

Column 1:
  name:ID
  type:integer
  constraints: primary key

Column 2:
  name:Name
  type:text
  constraints: collate nocase

Column 3:
  name:Family_ID
  type:integer
  constraints: references Family(ID)

For more complex column constraints I could then write another regex parser to 
separate each constraint.

I have to allow for quoted identifiers (eg "Family ID"), comments and nested 
brackets by tokenizing the string and substituting quoted/bracketed/commented 
sections with word placeholders before applying the regex.

But, as I said, I'm thinking I'm re-inventing the wheel here. Isn't there a 
better way? Or anyone care to comment on my regex, such as contingencies I may 
have missed?

Thanks,
Tom
BareFeet

 --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml



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

Re: [sqlite] Parsing create statements

[BareFeet]

Hi Stephan,

> Does this help?
> 
>  pragma table_info([table-name])
> 
> The output looks easier to parse than the create-table statement.

Thanks for the link. Yes, I am aware of the pragma table_info command. I use it 
to generate each column's name and type, but it doesn't give table constraints, 
column constraints etc.

Thanks,
Tom
BareFeet

 --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] Parsing create statements

[BareFeet]

Hi Simon,

>> Is there any way to parse a create statement (eg create table, create view, 
>> create trigger) into its components?

> Have you seen
> 
> 
> 
> ?  You can do this with many kinds of statements, not just SELECT.  It 
> doesn't do just what you want, but its output could be useful for whatever 
> you want it for.

Thanks for the link but, no, it doesn't seem to provide anything I could, for 
example, use to parse a create table statement into an array of column 
definitions, each showing name, type, array of constraints (as illustrated in 
previous email).

Thanks,
Tom
BareFeet

 --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 1:54 PM, Pavel Ivanov  wrote:
>> Why the difference in search time between searching individually and
>> searching together?
>
> Apparently SQLite is not smart enough to optimize the search for both
> min and max to make double entrance to the index - first from the
> beginning, then from the end. It does search through the full index
> instead which is not much better than search through the full table
> (as it does without the index). But when searched separately SQLite
> understands that it can pick up just first or just last entry from the
> index.

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

Re: [sqlite] fastest way to get the min/max

[Pavel Ivanov]

> Why the difference in search time between searching individually and
> searching together?

Apparently SQLite is not smart enough to optimize the search for both
min and max to make double entrance to the index - first from the
beginning, then from the end. It does search through the full index
instead which is not much better than search through the full table
(as it does without the index). But when searched separately SQLite
understands that it can pick up just first or just last entry from the
index.


Pavel

On Wed, Jan 20, 2010 at 1:47 PM, Robert Citek  wrote:
> On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
>> I thought at the first moment that Pavel's suggestion on using extra index
>> is very "expensive" in terms of megabytes on the disk, but despite this
>> drawback it's actually more robust.
>
> For my own curiosity I created a table with random text data and
> discovered there was only a negligible difference between using
> indexes and not when search for min and max in the same query.
> However, when searching for min or max in separate queries the answer
> was almost instantaneous. (details below).
>
> Why the difference in search time between searching individually and
> searching together?
>
> Regards,
> - Robert
>
> -
>
> $ sqlite3 sample.db 'drop table if exists sample ;
> create table sample (foo text) ; '
>
> $ jot -r -c 8000 a z |
> tr -d '\n' |
> fold -w 8 |
> fmt -w 8 |
> sqlite3 sample.db '.imp "/dev/stdin" "sample"'
>
> $ sqlite3 sample.db 'select count(*) from sample ; '
> 1000
>
> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
> aaaq|ytyd
> real 3.24
> user 3.10
> sys 0.11
>
> $ time -p sqlite3 sample.db 'create index sample_foo on sample (foo) ; '
> real 838.92
> user 53.68
> sys 38.46
>
> $ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
> aaaq|ytyd
> real 3.19
> user 3.13
> sys 0.06
>
> $ time -p sqlite3 sample.db 'select min(foo) from sample ; '
> aaaq
> real 0.00
> user 0.00
> sys 0.00
>
> $ time -p sqlite3 sample.db 'select max(foo) from sample ; '
> ytyd
> real 0.00
> user 0.00
> sys 0.01
>
> $ sqlite3 --version
> 3.6.10
> ___
> 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] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
> I thought at the first moment that Pavel's suggestion on using extra index
> is very "expensive" in terms of megabytes on the disk, but despite this
> drawback it's actually more robust.

For my own curiosity I created a table with random text data and
discovered there was only a negligible difference between using
indexes and not when search for min and max in the same query.
However, when searching for min or max in separate queries the answer
was almost instantaneous. (details below).

Why the difference in search time between searching individually and
searching together?

Regards,
- Robert

-

$ sqlite3 sample.db 'drop table if exists sample ;
create table sample (foo text) ; '

$ jot -r -c 8000 a z |
tr -d '\n' |
fold -w 8 |
fmt -w 8 |
sqlite3 sample.db '.imp "/dev/stdin" "sample"'

$ sqlite3 sample.db 'select count(*) from sample ; '
1000

$ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
aaaq|ytyd
real 3.24
user 3.10
sys 0.11

$ time -p sqlite3 sample.db 'create index sample_foo on sample (foo) ; '
real 838.92
user 53.68
sys 38.46

$ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
aaaq|ytyd
real 3.19
user 3.13
sys 0.06

$ time -p sqlite3 sample.db 'select min(foo) from sample ; '
aaaq
real 0.00
user 0.00
sys 0.00

$ time -p sqlite3 sample.db 'select max(foo) from sample ; '
ytyd
real 0.00
user 0.00
sys 0.01

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

Re: [sqlite] Parsing create statements

[Stephan Wehner]

On Wed, Jan 20, 2010 at 6:13 AM, BareFeet  wrote:
> Hi All,
>
> This question seems to come up every now and then, but I've yet to see any 
> solution. The best phrasing of the question I found was back in 2004:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg02649.html
> Is there any way to parse a create statement (eg create table, create view, 
> create trigger) into its components?



Does this help?

  pragma table_info([table-name])

The output looks easier to parse than the create-table statement.

See http://www.sqlite.org/pragma.html#schema

Stephan


>
> Since SQLite does this internally using the Lemon parser, surely there's a 
> simple way to get the parser results?
>
> For example, given:
>
> create table "People"
> (       ID integer primary key
> ,       Name text collate nocase
> ,       "Family ID" integer
>                references Family(ID)
> )
>
> break it up into something like:
>
> table name: People
>
> table columns:
>  1:
>    name: ID
>    type: integer
>    constraints:
>      1:
>         name:
>         detail: primary key
>  2:
>    name: Name
>    type: text
>    constraints:
>      1:
>         name:
>         detail: collate nocase
>  3:
>    name: Family ID
>    type: integer
>    constraints:
>      1:
>         name:
>         detail: references Family(ID)
>         foreign table: Family
>         foreign column: ID
>
> I'm writing in Objective-C, so anything accessible to it would be helpful.
>
> Thanks,
> Tom
> BareFeet
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parsing create statements

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

BareFeet wrote:
> Is there any way to parse a create statement (eg create table, create view, 
> create trigger) into its components?

One simple way is to use an authoriser while calling sqlite3_prepare.
You'll find every action that is done by the statement.

> Since SQLite does this internally using the Lemon parser, surely there's a 
> simple way to get the parser results?

As with many other parsers, a parse tree is not built.  Instead on
recognition of parts various internal functions are called to build up
operation codes for the virtual machine (Google "SQLite VDBE" to find out
more).  Consequently the parser results are a virtual machine program.

In neither case (VDBE program or authoriser) will you be able to tie which
portion of the query string is tied to which actions.  The only way you
could do this is to copy the SQLite grammar and write your own functions on
rule recognition.

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

iEYEARECAAYFAktXO1sACgkQmOOfHg372QRvGwCgt/OxqODNedvNz5LB7NCBntI0
PngAoJ8fNoATaJn/adPTplc6q0IeLs5b
=2d5g
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Pavel Ivanov]

> Is it possible? If I attach database B to database A and database A to
> database B. As these statements are committed into A and B, they stay
> attached even if I close and reopen. So If another process opens A (or B),
> it has B (or A) attached to A (or B). I don't have to repeat the ATTACH
> statement each time I open the database right?

No, that's incorrect. ATTACH commands are per-connection and never get
committed to database. You have to attach databases any time you want
to use them. You can attach them in any order or do not attach them at
all - doesn't make any difference for SQLite, it's just for your
convenience and it can make difference in how you write your queries.


Pavel

On Wed, Jan 20, 2010 at 12:07 PM, Tiberio, Sylvain
 wrote:
> Hi,
>
>> And how precisely do you expect SQLite to pull that off?
> Ignore the DETACH command and set and error... No? As when you insert a row
> that doesn't respect a constraints.
>
>> How it is going to stop you from, say, opening a separate
>> connection to one of these databases (and never attaching
>> the other), possibly from a different process?
> Is it possible? If I attach database B to database A and database A to
> database B. As these statements are committed into A and B, they stay
> attached even if I close and reopen. So If another process opens A (or B),
> it has B (or A) attached to A (or B). I don't have to repeat the ATTACH
> statement each time I open the database right?
>
>
> I agree that if data are split in several files, it is a way to introduce
> constraints violation (for instance if I backup/restore one file and not the
> others...).
>
> My first need was to separate my database into two separate files. In the
> 1st file I store my main data, In the second I store relation between data
> (and I accept to lost it). I would like to be able to backup/restore only
> the 1st file. It works fine with ATTACH but I don't have constraints on
> reference and view!
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Wednesday, January 20, 2010 5:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Attached database
>
> Tiberio, Sylvain  wrote:
>> About "cross-database foreign key constraints": I agree with you that
>> attached database can be dettached or changed. So as it is not
>> possible to create the foreign key constraint with no attached
>> database (because parent table doesn't exist), I can imagine that
>> SQLite doesn't allow to detach database when foreign key constraints
>> exist on it.
>
> And how precisely do you expect SQLite to pull that off? How it is going to
> stop you from, say, opening a separate connection to one of these databases
> (and never attaching the other), possibly from a different process?
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Tiberio, Sylvain]

Hi,

> And how precisely do you expect SQLite to pull that off?
Ignore the DETACH command and set and error... No? As when you insert a row
that doesn't respect a constraints.

> How it is going to stop you from, say, opening a separate
> connection to one of these databases (and never attaching
> the other), possibly from a different process?
Is it possible? If I attach database B to database A and database A to
database B. As these statements are committed into A and B, they stay
attached even if I close and reopen. So If another process opens A (or B),
it has B (or A) attached to A (or B). I don't have to repeat the ATTACH
statement each time I open the database right?


I agree that if data are split in several files, it is a way to introduce
constraints violation (for instance if I backup/restore one file and not the
others...).

My first need was to separate my database into two separate files. In the
1st file I store my main data, In the second I store relation between data
(and I accept to lost it). I would like to be able to backup/restore only
the 1st file. It works fine with ATTACH but I don't have constraints on
reference and view!

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Wednesday, January 20, 2010 5:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Attached database

Tiberio, Sylvain  wrote:
> About "cross-database foreign key constraints": I agree with you that 
> attached database can be dettached or changed. So as it is not 
> possible to create the foreign key constraint with no attached 
> database (because parent table doesn't exist), I can imagine that 
> SQLite doesn't allow to detach database when foreign key constraints 
> exist on it.

And how precisely do you expect SQLite to pull that off? How it is going to
stop you from, say, opening a separate connection to one of these databases
(and never attaching the other), possibly from a different process?

Igor Tandetnik

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

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
>> One of many ways would be to precompute the min/max into a separate
>> table and then query that table whenever you need the min/max.
>
> I thought at the first moment that Pavel's suggestion on using extra index
> is very "expensive" in terms of megabytes on the disk, but despite this
> drawback it's actually more robust.

Agreed.  While storing the min/max in a separate table may be one of
the fastest ways, it is unlikely to be the optimal way, as we do not
know all the constraints (e.g. robustness, datatype, table size, use
cases)

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

Re: [sqlite] Attached database

[Tiberio, Sylvain]

Ok, thanks!

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, January 20, 2010 5:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Attached database

> Ok, so cross-database view is not supported. Right?

Correct. I remember there was some message on this list about these matters.
And IIRC it even offered some simple patch to SQLite sources to allow
cross-database view support. You might try to search the archives.

> So as it is not possible to
> create the foreign key constraint with no attached database (because 
> parent table doesn't exist), I can imagine that SQLite doesn't allow 
> to detach database when foreign key constraints exist on it.

OK. Now you've done with this connection, close it, maybe even close your
application, restart it, connect to database once more but do not attach
that database foreign key refers to. What should SQLite do here? Or another
scenario: you open connection straight to the other database and drop all
data from your referred table. How should SQLite enforce foreign key here?

Unless database is in one piece and in full control of SQLite there always
will be scenario when foreign key cannot be enforced properly.


Pavel

On Wed, Jan 20, 2010 at 11:07 AM, Tiberio, Sylvain
 wrote:
> Ok, so cross-database view is not supported. Right?
>
> About "cross-database foreign key constraints": I agree with you that 
> attached database can be dettached or changed. So as it is not 
> possible to create the foreign key constraint with no attached 
> database (because parent table doesn't exist), I can imagine that 
> SQLite doesn't allow to detach database when foreign key constraints exist
on it.
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Wednesday, January 20, 2010 4:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Attached database
>
> I wouldn't say anything about views - from my POV there's no much harm 
> in supporting cross-database views other than possible user confusion 
> (personally I don't know why it was decided to not support this). But 
> concerning foreign keys: how do you think SQLite is supposed to 
> enforce foreign key when other database is not attached or when you 
> attach another database with the same alias that have table with the 
> same name but with completely different data?
>
>
> Pavel
>
> On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain 
>  wrote:
>> Hi,
>> I remark some effects if I use a main database with other attached 
>> database (ATTACH command):
>> - I cannot create view in main database that select columns from main 
>> database and attached database.
>> - There is no foreign key constraints control is the foreign key is 
>> on an attached database table.
>> Is there any think to do to be able to do that? Do I forget something?
>>
>> Regards,
>>
>> Sylvain
>>
>>
>> ___
>> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Igor Tandetnik]

Tiberio, Sylvain  wrote:
> About "cross-database foreign key constraints": I agree with you that
> attached database can be dettached or changed. So as it is not
> possible to 
> create the foreign key constraint with no attached database (because
> parent 
> table doesn't exist), I can imagine that SQLite doesn't allow to
> detach 
> database when foreign key constraints exist on it.

And how precisely do you expect SQLite to pull that off? How it is going to 
stop you from, say, opening a separate connection to one of these databases 
(and never attaching the other), possibly from a different process?

Igor Tandetnik

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

Re: [sqlite] Attached database

[Pavel Ivanov]

> Ok, so cross-database view is not supported. Right?

Correct. I remember there was some message on this list about these
matters. And IIRC it even offered some simple patch to SQLite sources
to allow cross-database view support. You might try to search the
archives.

> So as it is not possible to
> create the foreign key constraint with no attached database (because parent
> table doesn't exist), I can imagine that SQLite doesn't allow to detach
> database when foreign key constraints exist on it.

OK. Now you've done with this connection, close it, maybe even close
your application, restart it, connect to database once more but do not
attach that database foreign key refers to. What should SQLite do
here? Or another scenario: you open connection straight to the other
database and drop all data from your referred table. How should SQLite
enforce foreign key here?

Unless database is in one piece and in full control of SQLite there
always will be scenario when foreign key cannot be enforced properly.


Pavel

On Wed, Jan 20, 2010 at 11:07 AM, Tiberio, Sylvain
 wrote:
> Ok, so cross-database view is not supported. Right?
>
> About "cross-database foreign key constraints": I agree with you that
> attached database can be dettached or changed. So as it is not possible to
> create the foreign key constraint with no attached database (because parent
> table doesn't exist), I can imagine that SQLite doesn't allow to detach
> database when foreign key constraints exist on it.
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Wednesday, January 20, 2010 4:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Attached database
>
> I wouldn't say anything about views - from my POV there's no much harm in
> supporting cross-database views other than possible user confusion
> (personally I don't know why it was decided to not support this). But
> concerning foreign keys: how do you think SQLite is supposed to enforce
> foreign key when other database is not attached or when you attach another
> database with the same alias that have table with the same name but with
> completely different data?
>
>
> Pavel
>
> On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain
>  wrote:
>> Hi,
>> I remark some effects if I use a main database with other attached
>> database (ATTACH command):
>> - I cannot create view in main database that select columns from main
>> database and attached database.
>> - There is no foreign key constraints control is the foreign key is on
>> an attached database table.
>> Is there any think to do to be able to do that? Do I forget something?
>>
>> Regards,
>>
>> Sylvain
>>
>>
>> ___
>> 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

Re: [sqlite] Attached database

[Tiberio, Sylvain]

Ok, so cross-database view is not supported. Right?

About "cross-database foreign key constraints": I agree with you that
attached database can be dettached or changed. So as it is not possible to
create the foreign key constraint with no attached database (because parent
table doesn't exist), I can imagine that SQLite doesn't allow to detach
database when foreign key constraints exist on it.

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, January 20, 2010 4:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Attached database

I wouldn't say anything about views - from my POV there's no much harm in
supporting cross-database views other than possible user confusion
(personally I don't know why it was decided to not support this). But
concerning foreign keys: how do you think SQLite is supposed to enforce
foreign key when other database is not attached or when you attach another
database with the same alias that have table with the same name but with
completely different data?


Pavel

On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain
 wrote:
> Hi,
> I remark some effects if I use a main database with other attached 
> database (ATTACH command):
> - I cannot create view in main database that select columns from main 
> database and attached database.
> - There is no foreign key constraints control is the foreign key is on 
> an attached database table.
> Is there any think to do to be able to do that? Do I forget something?
>
> Regards,
>
> Sylvain
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Pavel Ivanov]

I wouldn't say anything about views - from my POV there's no much harm
in supporting cross-database views other than possible user confusion
(personally I don't know why it was decided to not support this). But
concerning foreign keys: how do you think SQLite is supposed to
enforce foreign key when other database is not attached or when you
attach another database with the same alias that have table with the
same name but with completely different data?


Pavel

On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain
 wrote:
> Hi,
> I remark some effects if I use a main database with other attached database
> (ATTACH command):
> - I cannot create view in main database that select columns from main
> database and attached database.
> - There is no foreign key constraints control is the foreign key is on an
> attached database table.
> Is there any think to do to be able to do that? Do I forget something?
>
> Regards,
>
> Sylvain
>
>
> ___
> 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] Attached database

[Tiberio, Sylvain]

Hi,
I remark some effects if I use a main database with other attached database
(ATTACH command):
- I cannot create view in main database that select columns from main
database and attached database.
- There is no foreign key constraints control is the foreign key is on an
attached database table.
Is there any think to do to be able to do that? Do I forget something?

Regards,

Sylvain


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

Re: [sqlite] Parsing create statements

[Simon Slavin]

On 20 Jan 2010, at 2:13pm, BareFeet wrote:

> This question seems to come up every now and then, but I've yet to see any 
> solution. The best phrasing of the question I found was back in 2004:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg02649.html
> 
> Is there any way to parse a create statement (eg create table, create view, 
> create trigger) into its components?
> 
> Since SQLite does this internally using the Lemon parser, surely there's a 
> simple way to get the parser results?

Have you seen



?  You can do this with many kinds of statements, not just SELECT.  It doesn't 
do just what you want, but its output could be useful for whatever you want it 
for.

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

[sqlite] Parsing create statements

[BareFeet]

Hi All,

This question seems to come up every now and then, but I've yet to see any 
solution. The best phrasing of the question I found was back in 2004:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg02649.html

Is there any way to parse a create statement (eg create table, create view, 
create trigger) into its components?

Since SQLite does this internally using the Lemon parser, surely there's a 
simple way to get the parser results?

For example, given:

create table "People"
(   ID integer primary key
,   Name text collate nocase
,   "Family ID" integer
references Family(ID)
)

break it up into something like:

table name: People

table columns:
 1:
name: ID
type: integer
constraints:
  1:
 name:
 detail: primary key
 2:
name: Name
type: text
constraints:
  1:
 name:
 detail: collate nocase
 3:
name: Family ID
type: integer
constraints:
  1:
 name:
 detail: references Family(ID)
 foreign table: Family
 foreign column: ID

I'm writing in Objective-C, so anything accessible to it would be helpful.

Thanks,
Tom
BareFeet

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

Re: [sqlite] fastest way to get the min/max

[Max Vlasov]

>
>
>
> One of many ways would be to precompute the min/max into a separate
> table and then query that table whenever you need the min/max.
>

Only if one have full control about how the data changes what with so
widespread format as sqlite almost always not the case. I mean without
triggers you won't be able to control the moment when someone do mass
deletes or inserts with an external admin and these are the points when
actual min/max may change. Even the author of the subject can sometimes
forget about the fact that his data has such dependency and do something
affecting this.

I thought at the first moment that Pavel's suggestion on using extra index
is very "expensive" in terms of megabytes on the disk, but despite this
drawback it's actually more robust.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

> On Wed, Jan 20, 2010 at 6:24 PM, hi  wrote:
>> For my application I am storing about "1770" rows into sqlite table,
>> and when taking 'min' or 'max' it takes about ~7 to 10 seconds.
>>
>> Can you please suggest effective ways to get min/max values.

How are you currently getting the min/max values?  What have you tried so far?

One of many ways would be to precompute the min/max into a separate
table and then query that table whenever you need the min/max.

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

Re: [sqlite] fasted way to get the min/max

[Pavel Ivanov]

Create an index on the field which you're getting min/max of. Then
getting min/max will take some milliseconds.

Pavel

On Wed, Jan 20, 2010 at 7:54 AM, hi  wrote:
> Hi,
>
> For my application I am storing about "1770" rows into sqlite table, and
> when taking 'min' or 'max' it takes about ~7 to 10 seconds.
>
> Can you please suggest effective ways to get min/max values.
>
> Thank you in advance.
> -Hiral
> ___
> 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] fastest way to get the min/max

[hi]

Sorry for the wrong subject:

On Wed, Jan 20, 2010 at 6:24 PM, hi  wrote:

> Hi,
>
> For my application I am storing about "1770" rows into sqlite table,
> and when taking 'min' or 'max' it takes about ~7 to 10 seconds.
>
> Can you please suggest effective ways to get min/max values.
>
> Thank you in advance.
> -Hiral
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fasted way to get the min/max

[hi]

Hi,

For my application I am storing about "1770" rows into sqlite table, and
when taking 'min' or 'max' it takes about ~7 to 10 seconds.

Can you please suggest effective ways to get min/max values.

Thank you in advance.
-Hiral
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ascii separator character in .import

[Simon Slavin]

On 20 Jan 2010, at 3:42am, RevBayes wrote:

> I was trying to use the .import tool in sqlite. I was wondering if there was
> a way to specify any arbitrary ascii character via the .separator command.
> 
> e.g. if i want to use 0x05 (some non-printable ascii char) as the separator,
> how do i specify it in the .separator command. 

See '.separator' in

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

Either figure out how to type that character or tell sqlite to take its 
commands from a file written to disk.

Alternatively, use your OSes tools to make a global change in your import file 
from 0x05 to some saner type-able character.

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