Re: [sqlite] Increase the datafile file size to limit the file fragmentation

[Vander Clock Stephane]

I know this trick, but it's a little longer to do than simply manually 
increate the file DB size ?
my test show it's work, i m just currious why we can not do like this ?

thanks again
stéphane

On 12/11/2010 3:44 AM, Max Vlasov wrote:
> On Sat, Dec 11, 2010 at 1:52 AM, Vander Clock Stephane<
> svandercl...@yahoo.fr>  wrote:
>
>> yes, i agree except that the file fragmentation of the file database
>> cause also the file fragmentation of some other files... that is logique
>> when more than 2 file grow a little at a time ! and the other file need
>> absolutely to not be fragmented !
>>
>>
> Ok then, if you feel you need this, there's a trick for db expanding. Create
> something big, like a table filled with random or uniform data and then
> delete it, you will get a database file with plenty of free space.
>
> Max
> ___
> 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] Increase the datafile file size to limit the file fragmentation

[Simon Slavin]

On 11 Dec 2010, at 8:18am, Vander Clock Stephane wrote:

> I know this trick, but it's a little longer to do than simply manually 
> increate the file DB size ?
> my test show it's work, i m just currious why we can not do like this ?

Fragmentation does not make much difference in any operating system except 
Windows.  Even under Windows, the actual speed improvement you get from 
defragmentation only lasts until the file gets a little fragmented again, which 
is usually not long.

Try your file with and without fragments and see what actual time improvement 
you get.

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

Re: [sqlite] Select

[lucavb]

Works great! Thanks!

Kees Nuyt wrote:
> 
> On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb
>  wrote:
> 
>>
>>Hello,
>>i need to to do this:
>>
>>i have this table: "userrates", composed by: id, userid, idtitle, rate.
>>
>>for example:
>>(1, 12, 1, 3)
>>(2, 15, 99, 4)
>>(3, 22, 10, 1)
>>(10, 22, 1, 5)
>>(5, 166, 37, 1)
>>(4, 244, 10, 2)
>>(6, 298, 1, 4)
>>(7, 298, 10, 3)
>>(8, 298, 15, 2)
>>
>> i need to extract only the rows with the userid
>> who had voted both fims (1 and 10):
>>
>> the result will be:
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>>
>>How can i do that?
>>
> 
> Assuming you mean idtitle where you write "fims":
> 
> SELECT a.id, a.userid, a.idtitle, a.rate
>   FROM userrates AS a
>   INNER JOIN (
>   SELECT userid
> FROM userrates
> WHERE idtitle IN (1,10)
> GROUP BY userid
> HAVING count(id) = 2
>   ) AS b ON b.userid = a.userid
>  WHERE a.idtitle IN (1,10)
> ORDER BY a.userid,a.id;
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Select-tp30425149p30432694.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] referential integrity, and necessity of a primary key on the one side

[TP]

Hello,

I have a question about referential integrity when there is no explicitly 
defined primary key in the table on the one side. Look at this example:

--
PRAGMA foreign_keys = ON;

CREATE TABLE foo( bar );
INSERT INTO foo values( "bar1" );

CREATE TABLE fox( dog
   , foo_id
   , FOREIGN KEY ( foo_id ) REFERENCES foo ON UPDATE CASCADE ON DELETE 
CASCADE );
INSERT INTO fox values( "dog1", 1 );
Error: foreign key mismatch

SELECT *, rowid from foo;
bar   rowid   
  
bar1  1   
---

So, we obtain a "foreign key mismatch", whereas there is the suitable value 
of rowid in the table.

If we modify the previous example by including an explicit primary key in 
table foo, it works:

--
PRAGMA foreign_keys = ON;

CREATE TABLE foo(
foo_id INTEGER PRIMARY KEY
, bar );
INSERT INTO foo values( 1, "bar1" );

CREATE TABLE fox( dog
   , foo_id
   , FOREIGN KEY ( foo_id ) REFERENCES foo ON UPDATE CASCADE ON DELETE 
CASCADE );
INSERT INTO fox values( "dog1", 1 );

select * from fox;
dog1|1
--

What is the explanation for this behavior?

Thanks in advance,

Julien


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

Re: [sqlite] referential integrity, and necessity of a primary key on the one side

[Kees Nuyt]

On Sat, 11 Dec 2010 12:39:39 +0100, TP 
wrote:

>Hello,
>
>I have a question about referential integrity when there is no explicitly 
>defined primary key in the table on the one side. Look at this example:
>
>--
>PRAGMA foreign_keys = ON;
>
>CREATE TABLE foo( bar );
>INSERT INTO foo values( "bar1" );
>
>CREATE TABLE fox( dog
>   , foo_id
>   , FOREIGN KEY ( foo_id ) REFERENCES foo ON UPDATE CASCADE ON DELETE 
>CASCADE );
>INSERT INTO fox values( "dog1", 1 );
>Error: foreign key mismatch


Side note: string literals should be quoted with single quotes, so
INSERT INTO foo VALUES (1, "bar1");
should be
INSERT INTO foo VALUES (1, 'bar1');

http://www.sqlite.org/lang_expr.html#litvalue

>SELECT *, rowid from foo;
>bar   rowid   
>  
>bar1  1   
>---
>
>So, we obtain a "foreign key mismatch", whereas there is the suitable value 
>of rowid in the table.
>
>If we modify the previous example by including an explicit primary key in 
>table foo, it works:
>
>--
>PRAGMA foreign_keys = ON;
>
>CREATE TABLE foo(
>foo_id INTEGER PRIMARY KEY
>, bar );
>INSERT INTO foo values( 1, "bar1" );
>
>CREATE TABLE fox( dog
>   , foo_id
>   , FOREIGN KEY ( foo_id ) REFERENCES foo ON UPDATE CASCADE ON DELETE 
>CASCADE );
>INSERT INTO fox values( "dog1", 1 );
>
>select * from fox;
>dog1|1
>--
>
>What is the explanation for this behavior?

It is by design. At the bottom of
http://www.sqlite.org/lang_createtable.html
it says:
The parent key of a foreign key constraint is not allowed to use the
rowid. The parent key must used named columns only.

This means you have to alias the rowid to be able to refer to it in
a foreign key clause.

In your case, the foreign key clause does not explicitly refer to a
specific column in the parent table, foo. SQLite probably tries to
find the primary key of the parent table, but there isn't one.

In general it is a bad idea to depend on the implicit existence of
rowid. Make it a habit to alias rowid to an explicit integer primary
key. It makes your code more portable and more readible.

For readibility, I would also explicitly name the column in the
foreign key clause. So your example would become:

CREATE TABLE foo (
id  INTEGER PRIMARY KEY NOT NULL
,   bar TEXT
);

CREATE TABLE fox (
dogTEXT
,   foo_id INTEGER
,   CONSTRAINT fk_foo_id
FOREIGN KEY ( foo_id ) REFERENCES foo (id)
ON UPDATE CASCADE ON DELETE CASCADE
);
BEGIN;
INSERT INTO foo (id,bar) VALUES (1,'bar1');
INSERT INTO fox (dog,foo_id) VALUES ('dog1',last_insert_rowid() ) 
COMMIT;

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma vs select for introspection

[BareFeetWare]

On 11/12/2010, at 12:29 PM, Simon Slavin wrote:

> The problem with foreign keys (and triggers !) as separate rows of 
> SQLITE_MASTER is that it would all have to be one long string, so you'd have 
> to write a parser.

I'm not sure what you mean here. Triggers are already listed in SQLite_Master. 
Their SQL definition is listed as "one long string". The only element that is 
parsed out for us is the name of the table (or view) upon which the trigger 
operates, in the Tbl_Name column. I want that "one long string" to be parsed 
into smaller chunks, the way that some pragmas do now, but made more usable via 
select queries instead.

> I think a better idea would be to expand table_info with a second parameter 
> so it could list all tables, and report on columns, indexes, triggers and 
> foreign keys all in one PRAGMA.  Then all the other PRAGMAs that do this 
> could be removed.

No, that's horrible. Columns, indexes, triggers and foreign keys all have 
different elements so need different tables to show them. We'd want a properly 
normalized schema.

And, as per my post before this, I would like to see them accessed via select 
query, rather than pragmas, so we can properly filter the result.

In short, what we need is to be able to access the schema components as tables 
and perform standard sort, filtering etc on them. You know, kinda like how a 
database works. Hang on, SQLite is a database, so why not use its own built in 
features to do the job? (Meant for humour and to highlight the obvious, not 
condescension ;-) )

We already have:

create table SQLite_Master
(   Type text
,   Name text
,   Tbl_Name text
,   Rootpage int
,   SQL text
)

In a similar fashion, I'm proposing/requesting that we morph pragma 
foreign_key_list into:

create table SQLite_Foreign_Keys
(   ID integer primary key
,   Sequence integer
,   Name text
,   From_Table text
,   From_Column text
,   To_Table text
,   To_Column text
,   On_Update text
,   On_Delete text
,   Match text
,   Deferrable boolean
,   Initially text
)

Similarly, I propose that pragma table_info() would be better as selectable 
tables:

create table SQLite_Table_Columns
(   ID integer primary key
,   Table_Name text
,   Sequence integer
,   Name text
,   Type text
,   Constraints text
)

and:

create table SQLite_View_Columns
(   ID integer primary key
,   View_Name text
,   Sequence integer
,   Name text
,   Type text
,   Expression text
,   Origin_Column_ID
references SQLite_Table_Columns(ID)
)

Triggers are a different animal again, so belong in their own table:

create table SQLite_Triggers
(   ID integer primary key
,   Name text
,   Table_Name text
,   Event text  -- delete, insert, update, update of
,   Occur text  -- before, after, instead of
)

create table SQLite_Trigger_Update_Columns
(   ID integer primary key
,   Column_Name
,   Trigger_ID integer
references SQLite_Triggers(ID)
)

create table SQLite_Trigger_Steps
(   ID integer primary key
,   Trigger_ID integer
references SQLite_Triggers(ID)
,   Sequence integer
,   SQL text
)

Then we could do all manner of introspection in a single select statement, such 
as (rewording some examples I gave before):

1. In an "Invoices Entry" view, in a "Customer" column, show the list of 
allowed values (from the origin and foreign key column). To the user it may 
look something like this:
http://www.databare.com/data_choices.html

2. For a particular column in a view, get the expression, name (alias) and 
result type of the column, along with the trigger that is activated by an 
update to that column in the view. See the first four snapshots here for a 
visual example:
http://www.databare.com/trace.html

3. Navigate through the hierarchy of any object, such as 
View->Column->Trigger->Steps->Insert->Select. For example:
http://www.databare.com/column_trigger.html

I currently do this via a pile of parsing code, but I feel like I'm reinventing 
the wheel, since SQLite obviously already has this information internally, but 
won't share. And I'm concerned that if SQLite's internals change, my external 
mimicking will fail.

I hope this clarifies what I'm talking about.

Thanks,
Tom
BareFeetWare

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

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

[sqlite] pragma vs select for introspection

[BareFeetWare]

On 11/12/2010, at 12:58 PM, Petite Abeille wrote:

> On Dec 11, 2010, at 2:29 AM, Simon Slavin wrote:
> 
>> Then all the other PRAGMAs that do this could be removed
> 
> While a consistent, comprehensive API would be nice, the problem with pragmas 
> is that, even though they return what looks like a result set, they are 
> neither selectable, nor queryable in plain SQL.

Yes, exactly. It is odd and frustrating to get a result from SQLite that can't 
be treated like a normal SQLite result. We need to be able to select... from.. 
where... order by... etc.

> To me, pragmas look more like part of the problem than the solution due to 
> their lack of integration with SQL.

Yes. By contrast, SQLite_Master doesn't give much, but since it's integrated 
with SQL, it's very useful. The few pragmas we have would be far more useful if 
they also integrated.

> I'm in the opinion that a comprehensive data dictionary, accessible directly 
> from SQL, is the way to go.  

Yes, yes, yes :-)

I think there's an SQL standard for introspective queries, isn't there? Is it 
something like MySQL's "INFORMATION_SCHEMA Tables", as per?:
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Thanks,
Tom
BareFeetWare

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

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

Re: [sqlite] pragma vs select for introspection

[Simon Slavin]

On 11 Dec 2010, at 2:28pm, BareFeetWare wrote:

> On 11/12/2010, at 12:58 PM, Petite Abeille wrote:
> 
>> I'm in the opinion that a comprehensive data dictionary, accessible directly 
>> from SQL, is the way to go. 
> 
> Yes, yes, yes :-)
> 
> I think there's an SQL standard for introspective queries, isn't there? Is it 
> something like MySQL's "INFORMATION_SCHEMA Tables", as per?:
> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Section 21 of the (SQL92) standard.
It's absolutely horrible.
Let's try to avoid that if we can.

Fortunately SQLite has no user model,
so most of it would be
pointless anyway.

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

Re: [sqlite] pragma vs select for introspection

[BareFeetWare]

On 12/12/2010, at 1:48 AM, Simon Slavin wrote:

> On 11 Dec 2010, at 2:28pm, BareFeetWare wrote:
> 
>> I think there's an SQL standard for introspective queries, isn't there? Is 
>> it something like MySQL's "INFORMATION_SCHEMA Tables", as per?:
>> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
> 
> Section 21 of the (SQL92) standard.
> It's absolutely horrible.
> Let's try to avoid that if we can.
> 
> Fortunately SQLite has no user model, so most of it would be pointless anyway.

Yes, I expect that at least half of what MySQL uses wouldn't be relevant to 
SQLite. But don't dismiss the concept due to what may be a flawed or wider 
implementation.

I suspect part of the SQL standard for introspection (or "metadata") is 
relevant and worth using. But even if none is, please still move away from 
pragmas to some form of selectable query.

Thanks,
Tom
BareFeetWare

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

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

Re: [sqlite] referential integrity, and necessity of a primary key on the one side

[TP]

Kees Nuyt wrote:

> It is by design. At the bottom of
> http://www.sqlite.org/lang_createtable.html
> it says:
> The parent key of a foreign key constraint is not allowed to use the
> rowid. The parent key must used named columns only.
> 
> This means you have to alias the rowid to be able to refer to it in
> a foreign key clause.
> 
> In your case, the foreign key clause does not explicitly refer to a
> specific column in the parent table, foo. SQLite probably tries to
> find the primary key of the parent table, but there isn't one.
> 
> In general it is a bad idea to depend on the implicit existence of
> rowid. Make it a habit to alias rowid to an explicit integer primary
> key. It makes your code more portable and more readible.
> 
> For readibility, I would also explicitly name the column in the
> foreign key clause. So your example would become:

Thanks a lot Kees.
Yes, I think it is better too; I just tried to see what type of tables are 
tolerated for a foreign relation.

Julien

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

Re: [sqlite] pragma vs select for introspection

[Petite Abeille]

On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote:

> Section 21 of the (SQL92) standard.

Yes, the notorious information schema:

http://en.wikipedia.org/wiki/Information_schema

> It's absolutely horrible.

Des goûts et des couleurs on ne discute point.

> Let's try to avoid that if we can.

Well, it has the merit of existing and being in use across various databases... 
so the cost/benefit of designing a slightly better, but different, information 
schema seem not worthwhile the trouble.

In the same way as one could question some of the design choices of SQL itself, 
one is usually better off sticking to SQL nonetheless. Ditto for that 
information schema. No point in re-inventing a slightly squared wheel.

> Fortunately SQLite has no user model,
> so most of it would be
> pointless anyway.

Not sure what specifically you are referring to, but if it's the concept of 
schemata, then main, temp and attached databases fit nicely with the notion of 
schema.


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

Re: [sqlite] tarball and directory name

[Mark Brand]

On 12/08/2010 09:30 AM, Mark Brand wrote:
> Hi,
>
> Just noticed that the format of the version in the tarball name changed,
> as in "sqlite-autoconf-3070400.tar.gz". However, this unpacks to a
> directory called "sqlite-3.7.4". This makes it difficult for automated
> build systems that want to have a predictable directory name. Would it
> be possible to have the directory agree with tarball filename? Ideally
> the naming would follow the well-established tradition where "x.tar.gz"
> unpacks to "x", but even if there was just agreement on the format of
> the version, that would help.
>
> regards,
>
> Mark
>

Looks like the output directory was recently changed to 
sqlite-autoconf-3070400, matching the archive name. Thanks!

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

[sqlite] SQLite crashes due to invalid pointer

[Wanadoo Hartwig]

Hi,

take the following SQL statement:

UPDATE tableA SET column1=(SELECT column2 FROM tableB WHERE function1(column3) 
< 1 ORDER BY function1(column3) LIMIT 1);

Actually, this statement does not make sense because the ORDER BY expression 
does not fulfill the requirements of an ORDER BY expression. I wrote it by 
mistake.

Interestingly SQLite only crashes if function1 is a user supplied function 
(using sqlite3_create_function). I tried the same with the core abs() function 
but then SQLite works.
I am using SQLite 3.7.2 and the crash occurs here:

case OP_Real: {/* same as TK_FLOAT, out2-prerelease */
  pOut->flags = MEM_Real;
  assert( !sqlite3IsNaN(*pOp->p4.pReal) ); <-- crashes here because of invalid 
pointer to p4
  pOut->r = *pOp->p4.pReal;
  break;
}


Hartwig

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

Re: [sqlite] pragma vs select for introspection

[Darren Duncan]

Petite Abeille wrote:
> On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote:
> 
>> Section 21 of the (SQL92) standard.
> 
> Yes, the notorious information schema:

Nonsense.  An information schema is a *good* thing, and is generally the *best* 
tool for introspecting a database.  It lets you use all the power features you 
have when querying data, anything a SELECT can do, and you can query the 
database structure likewise.  This is the way a relational database is supposed 
to work. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite crashes due to invalid pointer

[Richard Hipp]

On Sat, Dec 11, 2010 at 7:07 PM, Wanadoo Hartwig <
hartwig.wiesm...@wanadoo.nl> wrote:

> Hi,
>
> take the following SQL statement:
>
> UPDATE tableA SET column1=(SELECT column2 FROM tableB WHERE
> function1(column3) < 1 ORDER BY function1(column3) LIMIT 1);
>
> Actually, this statement does not make sense because the ORDER BY
> expression does not fulfill the requirements of an ORDER BY expression. I
> wrote it by mistake.
>
> Interestingly SQLite only crashes if function1 is a user supplied function
> (using sqlite3_create_function). I tried the same with the core abs()
> function but then SQLite works.
>

The statement you supply above never generates an OP_Real instruction.
OP_Real is only generated if your statement contains a floating point
literal, which yours does not.  Are you user that the UPDATE statement you
are giving above is the statement that is crashing?




> I am using SQLite 3.7.2 and the crash occurs here:
>
> case OP_Real: {/* same as TK_FLOAT, out2-prerelease */
>  pOut->flags = MEM_Real;
>  assert( !sqlite3IsNaN(*pOp->p4.pReal) ); <-- crashes here because of
> invalid pointer to p4
>  pOut->r = *pOp->p4.pReal;
>  break;
> }
>
>
> Hartwig
>
> ___
> 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