Re: [sqlite] How to design this table?

[Simon Slavin]

On 9 Oct 2011, at 3:57am, 张一帆 wrote:

> i have some data like "a and b or c ...",there will be a word 'and' or
> 'or' which means the Logical relations between each item.

If you have "a and b or c" does that mean

(a and b) or cOR
a and (b or c)?

How does your software know ?

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

[sqlite] How to design this table?

[张一帆]

i have some data like "a and b or c ...",there will be a word 'and' or
'or' which means the Logical relations between each item.So how to design a
table to store the data in best way?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error Message near ".": syntax error

[Simon Slavin]

On 9 Oct 2011, at 2:28am, James Brison wrote:

> I am receiving the following error message when running a query against a 
> sqlite db:
> 
> Error Message near ".": syntax error
> 
> What does this mean? and how do I debug it?  I'm new to sqlite and don't 
> understand the 'near'.  Is it saying that I have syntax error somewhere 
> involving '.'?

In your SQL command you have a '.'.  Either that dot, or very soon after it, is 
a character that SQLite doesn't know what to do with.  If you can't figure it 
out, post the command and we'll tell you.

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

Re: [sqlite] Error Message near ".": syntax error

[Mr. Puneet Kishor]

On Oct 8, 2011, at 8:28 PM, James Brison wrote:

> I am receiving the following error message when running a query against a 
> sqlite db:
> 
> Error Message near ".": syntax error
> 
> What does this mean? and how do I debug it?  I'm new to sqlite and don't 
> understand the 'near'.  Is it saying that I have syntax error somewhere 
> involving '.'?
> 


Wouldn't it have been super-easy to actually show us your query? How can anyone 
divine what is wrong when no knows what your query is. You are likely missing a 
comma, or have an extra space... please resend with the actual query that 
causes the above error.

--
Puneet Kishor

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

[sqlite] Error Message near ".": syntax error

[James Brison]

I am receiving the following error message when running a query against a 
sqlite db:

Error Message near ".": syntax error

What does this mean? and how do I debug it?  I'm new to sqlite and don't 
understand the 'near'.  Is it saying that I have syntax error somewhere 
involving '.'?

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

Re: [sqlite] Statement failing

[Simon Slavin]

On 9 Oct 2011, at 1:39am, Tim Streater wrote:

> On 08 Oct 2011 at 23:32, Simon Slavin  wrote: 
> 
>> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.
> 
> By the way, is one to be preferred over the other?

That is a great question, and I'd love to see answers from PHP programmers.

My contribution: the thing the PDO API has going for it is that it makes 
changing from one SQL engine to another simple.  If you develop on a 
stand-alone computer for a big multi-server network, this is an advantage.  On 
the other hand, the biggest advantage of using the SQLite3 API is that it's 
such a thin wrapper around the SQLite C API.  For someone who already knows 
SQLite it's very easy to pick up.  But that's only an advantage for experienced 
SQLite users, or those who want to be.

I have had trouble using the PDO API in situations that demand proper 
error-handling.  If you have a situation where something either works or 
doesn't you're fine.  If you have to understand exactly what error you got, in 
order to handle several different situations, you pretty-much have to simulate 
all your error conditions and see what happens to write your program.  The 
advantage of the SQLite3 interface here is that it perfectly reflects the 
documentation for the SQLite3 C API, so you can probably figure out what to do 
just by reading the SQLite C documentation.

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

Re: [sqlite] Statement failing

[Tim Streater]

On 08 Oct 2011 at 23:32, Simon Slavin  wrote: 

> On 8 Oct 2011, at 10:37pm, Tim Streater wrote:
>
>> I have this string:
>>
>>   attach database ':memory:' as mem; create table mem.messages ( absid
>> integer, ..., replyto text );
>>
>> (where the ellipsis represents a number of other column declarations). In
>> PHP, I do this:
>>
>>   $dbh->query ($str);
>>
>> where $str contains the string from above.
>
> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.

By the way, is one to be preferred over the other? I did notice that the 
command line tool had no problem with being given multiple statements on a line 
and executing them sequentially (which was in fact what prompted me to try it 
in my app).

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

Re: [sqlite] Statement failing

[Tim Streater]

On 08 Oct 2011 at 23:32, Simon Slavin  wrote: 

> On 8 Oct 2011, at 10:37pm, Tim Streater wrote:
>
>> I have this string:
>>
>>   attach database ':memory:' as mem; create table mem.messages ( absid
>> integer, ..., replyto text );
>>
>> (where the ellipsis represents a number of other column declarations). In
>> PHP, I do this:
>>
>>   $dbh->query ($str);
>>
>> where $str contains the string from above.
>
> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.

I'm using PDO. And I've now knocked up a small test program allowing me to use 
either.

> But first I notice you're using ::query() and you should be using ::exec() .

OK I'll study these to see why :-)

> And second, the documentation says that both functions execute only one
> statement.  My guess is that they ignore everything after the ';' used as a
> statement separator.  But you should try it with ::exec() and find out for
> yourself.

Well, with my test program it looks like PDO does ignore anything after the 
semi-colon. The sqlite3 extension, by contrast, does appear to process after it.

> Refusing to process multiple SQL statements is probably going to be the best
> thing to do in today's security-sensitive times.  It acts against attempts to
> break your database using techniques like this:

Hmmm. There are places where I have a sequence of statements. I was trying to 
reduce overhead by combining them. But perhaps that's not possible, or, in the 
case of the sqlite3 extension, unreliable at best.

> 

I get a 404.

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

Re: [sqlite] Statement failing

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/10/11 15:32, Simon Slavin wrote:
> And second, the documentation says that both functions execute only one
> statement.  My guess is that they ignore everything after the ';' used
> as a statement separator.

Just to be clear it won't be that they are parsing the SQL.  The SQLite C
API for preparing a statement does the parsing of the complete text parsed
in, stopping at the end of the first statement.  It tells the developer
where the next statement starts in that text.

Most likely they are discarding that information.  It is consequently easy
to alter the code to only accept one statement, or to make it keep
executing all the statements.  The good news is that the existing code
can't be fooled into thinking the statement ends somewhere different than
where it actually does since it is the SQLite core making that
determination and it isn't foolish.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6Q1rwACgkQmOOfHg372QSiFwCgiVxPxNvlJcJVgVw4gPESfPog
ZbsAnRvZi9P3p6R8qabxA7wDsvm+E3Td
=x/kH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Statement failing

[Simon Slavin]

On 8 Oct 2011, at 10:37pm, Tim Streater wrote:

> I have this string:
> 
>   attach database ':memory:' as mem; create table mem.messages ( absid 
> integer, ..., replyto text );
> 
> (where the ellipsis represents a number of other column declarations). In 
> PHP, I do this:
> 
>   $dbh->query ($str);
> 
> where $str contains the string from above.

I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.

But first I notice you're using ::query() and you should be using ::exec() .

And second, the documentation says that both functions execute only one 
statement.  My guess is that they ignore everything after the ';' used as a 
statement separator.  But you should try it with ::exec() and find out for 
yourself.

Refusing to process multiple SQL statements is probably going to be the best 
thing to do in today's security-sensitive times.  It acts against attempts to 
break your database using techniques like this:



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

Re: [sqlite] Statement failing

[Stephan Beal]

On Sat, Oct 8, 2011 at 11:37 PM, Tim Streater  wrote:

> If I take the string above and split it into two calls to $dbh->query - one
> to attach the memory database, the second to create the mem.messages table,
> then everything works as expected. Is there something I'm obviously doing
> wrong?
>

http://www.php.net/manual/en/pdo.query.php

says:

 Executes an SQL statement, returning a result set as a PDOStatement object


"an" is singular, and result set implies a single statement.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Statement failing

[Tim Streater]

I have this string:

   attach database ':memory:' as mem; create table mem.messages ( absid 
integer, ..., replyto text );

(where the ellipsis represents a number of other column declarations). In PHP, 
I do this:

   $dbh->query ($str);

where $str contains the string from above. It completes without apparent error, 
but any attempt to immediately access the table mem.messages (such as via 
insert into mem.messages ...) fails with: No such table mem.messages.

But:

If I take the string above and split it into two calls to $dbh->query - one to 
attach the memory database, the second to create the mem.messages table, then 
everything works as expected. Is there something I'm obviously doing wrong?

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

Re: [sqlite] Can pre-sorted data help?

[Richard Hipp]

On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani  wrote:

> Hi All,
>
> I have been trying to see how we can make one of our databases more space
> efficient.


Have you tried increasing the page size?  You seem to currently be using
1024-byte pages.  Try increasing that to 4096 or 8192.  You might get both a
performance increase and a database size reduction.

PRAGMA page_size=8192; VACUUM;

The VACUUM might take a little while on your database,.



>  I am now looking at seeing if we have the right indexes and if there is a
> way to save space by removing indexes.
>
> Is there some way to make use of the fact that the data can be sorted by a
> specific column in the desired order before it is imported into the table,
> or do we need to create an index on that column anyway?   Eliminating the
> index could save us space.  One of the things in our case is that the data
> is in a read only database (and further compressed/ encrypted by CEROD).
>
> Any ideas?
>
> Best Regards,
> Mohit.
> 8/10/2011 | 12:29 AM.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] Can pre-sorted data help?

[Petite Abeille]

On Oct 8, 2011, at 4:42 PM, Mohit Sindhwani wrote:

> all will usually have an index on zid and (zid,...) for other queries.

Also such compound indices  (zid,...) are pointless as they already have 
maximum selectivity incorporating the primary key.

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

Re: [sqlite] Can pre-sorted data help?

[Petite Abeille]

On Oct 8, 2011, at 4:42 PM, Mohit Sindhwani wrote:

> We have many table that have zid (unique) and all will usually have an index 
> on zid and (zid,...) for other queries.

If 'zid' is your primary key (INTEGER PRIMARY KEY), then there is no need to 
index it again, as it's an alias for the table internal rowid.

Check 'ROWIDs and the INTEGER PRIMARY KEY' in the fine manual:

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

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Hi Richard,

On 8/10/2011 12:51 AM, Richard Hipp wrote:

Hi All,

I have been trying to see how we can make one of our databases more space
efficient.  I am now looking at seeing if we have the right indexes and if
there is a way to save space by removing indexes.


Have you run the sqlite3_analyzer.exe utility (available from
http://www.sqlite.org/download.html) on your database to see how efficiently
the data is being stored and which tables are taking up the most space?  Can
you share the output of sqlite3_analyzer.exe with us?


I have run sqlite_analyzer (and I use it all the time to figure out what 
to optimize next!).  The specific database in question has about 80 
tables - let me see if I can extract out a small part to compare.  This 
is from one of the analyses.


*** Table NCX and all its indices ***

Percentage of total database..  15.4%
Number of entries. 2128132
Bytes of storage consumed. 173074432
Bytes of payload.. 139889514   80.8%
Average payload per entry. 65.73
Average unused bytes per entry 9.03
Average fanout 89.00
Fragmentation.   3.6%
Maximum payload per entry. 1083
Entries that use overflow. 90.0%
Index pages used.. 1691
Primary pages used 167318
Overflow pages used... 9
Total pages used.. 169018
Unused bytes on index pages... 207792  12.0%
Unused bytes on primary pages. 1899955911.1%
Unused bytes on overflow pages 850  9.2%
Unused bytes on all pages. 1920820111.1%

*** Table NCX w/o any indices ***

Percentage of total database..  13.8%
Number of entries. 1064066
Bytes of storage consumed. 155700224
Bytes of payload.. 128184788   82.3%
Average payload per entry. 120.47
Average unused bytes per entry 15.91
Average fanout 89.00
Fragmentation.   3.3%
Maximum payload per entry. 1083
Entries that use overflow. 90.0%
Index pages used.. 1691
Primary pages used 150351
Overflow pages used... 9
Total pages used.. 152051
Unused bytes on index pages... 207792  12.0%
Unused bytes on primary pages. 1672587510.9%
Unused bytes on overflow pages 850  9.2%
Unused bytes on all pages. 1693451710.9%

*** Indices of table NCX 

Percentage of total database..   1.5%
Number of entries. 1064066
Bytes of storage consumed. 17374208
Bytes of payload.. 1170472667.4%
Average payload per entry. 11.00
Average unused bytes per entry 2.14
Fragmentation.   5.9%
Maximum payload per entry. 11
Entries that use overflow. 00.0%
Primary pages used 16967
Overflow pages used... 0
Total pages used.. 16967
Unused bytes on primary pages. 2273684 13.1%
Unused bytes on overflow pages 0
Unused bytes on all pages. 2273684 13.1%

The table and its indexes are as follows:
CREATE TABLE ncx (zid INTEGER PRIMARY KEY, str1 TEXT, str2 TEXT);
CREATE INDEX nc_qiid on newconns(zid);

We have many table that have zid (unique) and all will usually have an 
index on zid and (zid,...) for other queries.


I'm not sure if there is enough information here?

Best Regards,
Mohit.
8/10/2011 | 10:41 PM.

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

Re: [sqlite] System.Data.SQLite Exception with wrong password

[Marco Cosentino]

On 08/10/2011 01:24, Joe Mistachkin wrote:

Marco Cosentino wrote:

The ErrorCode is set to "NotADatabase".
Wouldn't it more correct if this code is set to something like
SQLiteErrorCode.Auth or the Exception is more specialized?


The exceptions thrown by System.Data.SQLite reflect the underlying
error code returned from the native core SQLite library.  In the
case you mention, the exception is technically correct even though
it may seem counterintuitive at first glance.

--
Joe Mistachkin

Hi Joe,
thank you for the reply.
I understand the point. But the encryption subsystem should be smart 
enough to prevent passing an encrypted stream to the native SQLite 
library (I am assuming that the encryption subsystem operates between 
the SQLite core library and the OS).

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