[sqlite] how to show the equivalent hex value of integer data while select in sqlite3 shell?

[liubin liu]

how to show the equivalent hex value of integer data while select in sqlite3
shell?


sqlite> 
sqlite> CREATE TABLE test (id INTEGER, data INTEGER);
sqlite> INSERT INTO test VALUES (1, 32);
sqlite> SELECT * FROM test;
1|32
sqlite> 
sqlite> 
sqlite> SELECT hex(data) FROM test;
3332   ### hope to get 0x20
sqlite> 

-- 
View this message in context: 
http://old.nabble.com/how-to-show-the-equivalent-hex-value-of-integer-data-while-select-in-sqlite3-shell--tp27009563p27009563.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] SQLite 3.6.21 - slow query

[Igor Tandetnik]

Jeremy Zeiber wrote:
> This query runs in ~ 17 ms:
> SELECT COUNT(data) FROM detail AS outerdetail WHERE headerid=4 AND data
> NOT IN (SELECT DISTINCT data FROM detail WHERE headerid<4)

Here the subquery is not coordinated. It is run once, the results are stored in 
an ephemeral table, then the check in the main query is performed against that 
table.

> This query runs in ~ 15s:
> SELECT COUNT(data) FROM detail AS outerdetail WHERE headerid=4 AND data
> NOT IN (SELECT DISTINCT data FROM detail WHERE
> headerid Why is there such a huge difference in the query time for two very
> similar queries?  Is there anything I can do with the second query to
> make it perform more like the first?

The second query is equivalent to the first, it's just written in the form that 
makes it difficult for SQLite to optimize. Why don't you just use the first 
query?

> This query is part of a larger
> query that aggregates detail data such as the following, so I can't hard
> code the headerid in the query.
> 
> SELECT headerid,
> (SELECT COUNT(data) FROM detail AS outerdetail WHERE
> headerid=header.headerid AND data NOT IN (SELECT DISTINCT data FROM
> detail WHERE headerid FROM header GROUP BY headerid;

Try replacing outerdetail.headerid with header.headerid.

Igor Tandetnik

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

Re: [sqlite] bind for sub-queries

[Mathieu SCHROETER]

Jay A. Kreibich a écrit :
> On Sun, Jan 03, 2010 at 10:47:01AM +0100, Mathieu SCHROETER scratched on the 
> wall:
>> Hello,
>>
>> I've a simple question about the bind functions. If it can
>> be realistic to have in the future, a way to bind a sub-query
>> in a query?
> 
>   Doubtful.  Sub-queries are a syntactical thing and exist because of
>   limitations in the SQL language.  From the perspective of the
>   database engine, sub-queries are merged into the main query as one big
>   operation.
> 
>   Depending on your situation, you might be able to use views, temporary
>   views, or temporary tables.

Well.. thanks for the answer!


Mathieu

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

Re: [sqlite] SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Darren Duncan]

 From now on, would anyone responding to this thread on sqlite-users@sqlite.org 
please cross-post your on-topic responses to dbd-sql...@lists.scsys.co.uk as I 
am?  I moderate the dbd-sqlite list and will let your postings through even if 
you aren't subscribed to it.  This is to help save me some manual forwarding 
work.

Thank you.

Adam Kennedy had yesterday posted replies to Roger's first reply which I 
effectively forwarded to the dbd-sql...@lists.scsys.co.uk mailing list.  Adam's 
replies were also sent to sqlite-users@sqlite.org but the list moderators 
didn't 
yet let them through to the list so I'm forwarding a compilation of them for 
the 
  latter's benefit.

-- Darren Duncan

1 of 2:

 Original Message 
Subject: Re: [DBD-SQLite] Re: [sqlite] SQLite bug ticket - build fails on 
sun4-solaris-64int 2.10
Date: Sun, 3 Jan 2010 14:55:39 +1100
From: Adam Kennedy 
Reply-To: a...@ali.as
To: Darren Duncan 
CC: DBD::SQLite Mailing List ,General 
Discussion of SQLite Database 
References: <4b3efa2a.3090...@darrenduncan.net> 
<4b3f0480.8080...@rogerbinns.com> <4b3f0786.50...@darrenduncan.net>

Thanks for the comments on the build flags, we will investigate.

The threadsafe disabling may be a result of DBD::SQLite being itself
compiled on a Perl that has threading compiled out (which adds about
10-20% overall speed and is used in certain situations).

Adam K
DBD::SQLite Release Manager


 > Roger Binns wrote [to sqlite-us...@sqlite.org]:
 >>
 >> Darren Duncan wrote:
 >>>
 >>> I would like to bring an apparent SQLite bug to the attention of the
 >>> SQLite core developers as a ticket, where build fails on sun4-solaris-64int
 >>> 2.10
 >>
 >> You'll find this is not a bug in SQLite.
 >>
 >>> cc: Fatal error in /opt/sunstudio12.1/prod/bin/cg
 >>> cc: Status 139
 >>> *** Error code 139
 >>
 >> That is the compiler crashing (signal 11, SIGSEGV).  This sort of thing
 >> usually turns out to be an optimiser bug and likely won't happen if you
 >> disable optimisation, or compile the files individually rather than
 >> using the amalgamation.  Alternatively use a working compiler like gcc.
 >>
 >> Incidentally three of your defines are dodgy:


2 of 2:

 Original Message 
Subject: Re: [DBD-SQLite] Re: [sqlite] SQLite bug ticket - build fails on 
sun4-solaris-64int 2.10
Date: Sun, 3 Jan 2010 15:47:22 +1100
From: Adam Kennedy 
Reply-To: a...@ali.as
To: Darren Duncan 
CC: DBD::SQLite Mailing List ,General 
Discussion of SQLite Database 
References: <4b3efa2a.3090...@darrenduncan.net> 
<4b3f0480.8080...@rogerbinns.com> <4b3f0786.50...@darrenduncan.net>

The build code we're using is run across all operating systems.

You can see the actual build script here.

[ http://svn.ali.as/cpan/trunk/DBD-SQLite/Makefile.PL ]

Unfortunately, we neither have the ability to run configure (as we
don't have reliable access to /bin/sh or any of the other stuff it
needs) or the ability to use a pregenerated static configuration
across all platforms.

But your other comments are welcome.

I've removed the useless pointer size flag, and commented out the core
flag until we can determine why it was there in the first place
(there's a new team looking after DBD::SQLite since about March last
year, and some stuff is definitely grandfathered in from long ago).

Adam K

 >> The other flags seem to be guessed.  There is no need to tell a 64 bit
 >> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
 >> the system likely has LOCALTIME_R and GMTIME_R too as well as several
 >> other header files.
 >>
 >> If you do not want to build SQLite using its build system then the
 >> approach I take is to run SQLite's configure, grab the DEFS = line out
 >> of the resulting Makefile and generate a .h file with the relevant -D
 >> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
 >> will #include any config.h so you can dump your #defines in there.
 >>
 >> Roger

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

Re: [sqlite] Foreign key support in Sqlite

[Luciano de Souza]

Oh! Certainly, I didn't see it. I am using Outlook and probably I forgot to 
update the messages before answering.
Well, the important is everything works.
What was lacking in my script test.sql was:

pragma foreign_keys = on;

After this, I am happy!
Thank you very much!

- Original Message - 
From: "Roger Andersson" 
To: "'General Discussion of SQLite Database'" 
Sent: Sunday, January 03, 2010 4:10 PM
Subject: Re: [sqlite] Foreign key support in Sqlite


> -Ursprungligt meddelande-
> Från: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] För Luciano de Souza
> Skickat: den 3 januari 2010 19:05
> Till: General Discussion of SQLite Database
> Ämne: Re: [sqlite] Foreign key support in Sqlite
>
> I can't comprehend! I downloaded the two packs in c:\test.
> Three files were
> unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def.
>

Maybe you need to check what Igor Tandetnik did say?

http://www.sqlite.org/foreignkeys.html#fk_enable
http://www.sqlite.org/pragma.html#pragma_foreign_keys


/Roger

___
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] Foreign key support in Sqlite

[Luciano de Souza]

Oh! Sorry! I didn't read Igor's message. I will read the article and 
certainly I will get the result! Sorry again!
Jean and Igor,  thank you!

- Original Message - 
From: "Luciano de Souza" 
To: "General Discussion of SQLite Database" 
Sent: Sunday, January 03, 2010 4:04 PM
Subject: Re: [sqlite] Foreign key support in Sqlite


>I can't comprehend! I downloaded the two packs in c:\test. Three files were
> unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def.
>
> I created the database:
>
> c:> sqlite3 test.db
>
> I create the structure:
>
> sqlite> .read test.sql
>
> The test.sql contains the statements mentioned before.
>
> I tried to insert a further illegal record:
>
> sqlite> insert into people(name, cities_id) values('Pedro', 14);
>
> The record is added without problems!
>
> I tried:
>
> select sqlite_version();
>
> The answer is:
>
> 3.6.21
>
> I had placed the three files in the current folder, but to make sure old
> versions in the environment paths don't be enabled by accident, I removed
> another version in c:\windows. I don't have reasons to believe the version 
> I
> downloaded is currently working. In the folder, I have:
>
> test.sql
>
> test.db
>
> sqlite3.dll
>
> sqlite3.exe
>
> sqlite3.def
>
> Unfortunately, the answer didn't change and the record follows being added
> contrarily the foreign key constraint.
>
>
>
> - Original Message - 
> From: "Jean-Christophe Deschamps" 
> To: "General Discussion of SQLite Database" 
> Sent: Sunday, January 03, 2010 2:51 PM
> Subject: Re: [sqlite] Foreign key support in Sqlite
>
>
>>
>>>create table cities
>>>(
>>>id integer primary key not null,
>>>name text not null
>>>);
>>>
>>>create table people
>>>(
>>>id integer primary key not null,
>>>name text not null,
>>>cities_id integer not null,
>>>foreign key(cities_id) references cities(id)
>>>);
>>>
>>>insert into cities(name) values('Campos');
>>>insert into cities(name) values('Araraquara');
>>>insert into cities(name) values('Porto');
>>>insert into cities(name) values('Curitiba');
>>>insert into people(name, cities_id) values('John', 3);
>>>insert into people(name, cities_id) values('Mary', 2);
>>>
>>>Regarding cities don't have the Id = 8, this statement should fail:
>>>insert into people(name, cities_id) values('Pedro', 8);
>>
>> This last insert fails here (3.6.21) with constraint violation.
>>
>> Can you check which version you're actually running:
>>
>> select sqlite_version();
>> 3.6.21
>>
>>
>> ___
>> 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] Foreign key support in Sqlite

[Roger Andersson]

> -Ursprungligt meddelande-
> Från: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] För Luciano de Souza
> Skickat: den 3 januari 2010 19:05
> Till: General Discussion of SQLite Database
> Ämne: Re: [sqlite] Foreign key support in Sqlite
> 
> I can't comprehend! I downloaded the two packs in c:\test. 
> Three files were
> unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def.
> 

Maybe you need to check what Igor Tandetnik did say?

http://www.sqlite.org/foreignkeys.html#fk_enable
http://www.sqlite.org/pragma.html#pragma_foreign_keys


/Roger

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

Re: [sqlite] Foreign key support in Sqlite

[Luciano de Souza]

I can't comprehend! I downloaded the two packs in c:\test. Three files were 
unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def.

I created the database:

c:> sqlite3 test.db

I create the structure:

sqlite> .read test.sql

The test.sql contains the statements mentioned before.

I tried to insert a further illegal record:

sqlite> insert into people(name, cities_id) values('Pedro', 14);

The record is added without problems!

I tried:

select sqlite_version();

The answer is:

3.6.21

I had placed the three files in the current folder, but to make sure old 
versions in the environment paths don't be enabled by accident, I removed 
another version in c:\windows. I don't have reasons to believe the version I 
downloaded is currently working. In the folder, I have:

test.sql

test.db

sqlite3.dll

sqlite3.exe

sqlite3.def

Unfortunately, the answer didn't change and the record follows being added 
contrarily the foreign key constraint.



- Original Message - 
From: "Jean-Christophe Deschamps" 
To: "General Discussion of SQLite Database" 
Sent: Sunday, January 03, 2010 2:51 PM
Subject: Re: [sqlite] Foreign key support in Sqlite


>
>>create table cities
>>(
>>id integer primary key not null,
>>name text not null
>>);
>>
>>create table people
>>(
>>id integer primary key not null,
>>name text not null,
>>cities_id integer not null,
>>foreign key(cities_id) references cities(id)
>>);
>>
>>insert into cities(name) values('Campos');
>>insert into cities(name) values('Araraquara');
>>insert into cities(name) values('Porto');
>>insert into cities(name) values('Curitiba');
>>insert into people(name, cities_id) values('John', 3);
>>insert into people(name, cities_id) values('Mary', 2);
>>
>>Regarding cities don't have the Id = 8, this statement should fail:
>>insert into people(name, cities_id) values('Pedro', 8);
>
> This last insert fails here (3.6.21) with constraint violation.
>
> Can you check which version you're actually running:
>
> select sqlite_version();
> 3.6.21
>
>
> ___
> 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] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Adam Kennedy wrote:
> Unfortunately, we neither have the ability to run configure (as we
> don't have reliable access to /bin/sh or any of the other stuff it
> needs) or the ability to use a pregenerated static configuration
> across all platforms.

Well, you already pre-generate -DHAVE_USLEEP which doesn't exist on Windows
or older Unixen!  Your only workaround is to read/run the real configure to
see what kind of stuff it generates and then write your own tests to
generate the same flags.

GMTIME_R/LOCALTIME_R will affect performance if doing date/time code - not
having them means SQLite internally uses a mutex around calls to
gmtime/localtime which still leaves you vulnerable to bogus data if any
other non-SQLite thread in the program calls those functions.

The other flags mainly cover header file presence and you'll generally get
away without defines for them (unistd.h likely has everything anyway).  The
only likely gotcha is if you have extension loading enabled in which case
SQLite needs to know which header contains dlopen and friends.

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

iEYEARECAAYFAktA2l4ACgkQmOOfHg372QTpYACfYyh1PPCLcFN58xesZw1JknL9
AxkAoKTvDHoniKKJe1EoYeeruvbH+5Sr
=GEli
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Adam Kennedy]

Apologies, that should be...

http://svn.ali.as/cpan/trunk/DBD-SQLite/Makefile.PL

2010/1/3 Adam Kennedy :
> The build code we're using is run across all operating systems.
>
> You can see the actual build script here.
>
> http://ali.as/cpan/trunk/DBD-SQLite/Makefile.PL
>
> Unfortunately, we neither have the ability to run configure (as we
> don't have reliable access to /bin/sh or any of the other stuff it
> needs) or the ability to use a pregenerated static configuration
> across all platforms.
>
> But your other comments are welcome.
>
> I've removed the useless pointer size flag, and commented out the core
> flag until we can determine why it was there in the first place
> (there's a new team looking after DBD::SQLite since about March last
> year, and some stuff is definitely grandfathered in from long ago).
>
> Adam K
>
>>> The other flags seem to be guessed.  There is no need to tell a 64 bit
>>> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
>>> the system likely has LOCALTIME_R and GMTIME_R too as well as several
>>> other header files.
>>>
>>> If you do not want to build SQLite using its build system then the
>>> approach I take is to run SQLite's configure, grab the DEFS = line out
>>> of the resulting Makefile and generate a .h file with the relevant -D
>>> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
>>> will #include any config.h so you can dump your #defines in there.
>>>
>>> Roger
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Adam Kennedy]

The build code we're using is run across all operating systems.

You can see the actual build script here.

http://ali.as/cpan/trunk/DBD-SQLite/Makefile.PL

Unfortunately, we neither have the ability to run configure (as we
don't have reliable access to /bin/sh or any of the other stuff it
needs) or the ability to use a pregenerated static configuration
across all platforms.

But your other comments are welcome.

I've removed the useless pointer size flag, and commented out the core
flag until we can determine why it was there in the first place
(there's a new team looking after DBD::SQLite since about March last
year, and some stuff is definitely grandfathered in from long ago).

Adam K

>> The other flags seem to be guessed.  There is no need to tell a 64 bit
>> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
>> the system likely has LOCALTIME_R and GMTIME_R too as well as several
>> other header files.
>>
>> If you do not want to build SQLite using its build system then the
>> approach I take is to run SQLite's configure, grab the DEFS = line out
>> of the resulting Makefile and generate a .h file with the relevant -D
>> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
>> will #include any config.h so you can dump your #defines in there.
>>
>> Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Adam Kennedy]

Thanks for the comments on the build flags, we will investigate.

The threadsafe disabling may be a result of DBD::SQLite being itself
compiled on a Perl that has threading compiled out (which adds about
10-20% overall speed and is used in certain situations).

Adam K
DBD::SQLite Release Manager

2010/1/2 Darren Duncan :
> Roger, thank you for this quick response, and for your tips.  So from the
> looks of what you're saying, the problem is actually related to something
> that the DBD::SQLite build process is doing inadequately, so now I suspect
> the ball will go back there to be dealt with.  Thank you. -- Darren Duncan
>
> Roger Binns wrote [to sqlite-us...@sqlite.org]:
>>
>> Darren Duncan wrote:
>>>
>>> I would like to bring an apparent SQLite bug to the attention of the
>>> SQLite core developers as a ticket, where build fails on sun4-solaris-64int
>>> 2.10
>>
>> You'll find this is not a bug in SQLite.
>>
>>> cc: Fatal error in /opt/sunstudio12.1/prod/bin/cg
>>> cc: Status 139
>>> *** Error code 139
>>
>> That is the compiler crashing (signal 11, SIGSEGV).  This sort of thing
>> usually turns out to be an optimiser bug and likely won't happen if you
>> disable optimisation, or compile the files individually rather than
>> using the amalgamation.  Alternatively use a working compiler like gcc.
>>
>> Incidentally three of your defines are dodgy:
>>
>> -DSQLITE_CORE
>>
>> There is never any need to specify this - all that stuff is handled
>> internally.
>>
>> -DSQLITE_PTR_SZ=4
>>
>> That name is not used anywhere in the SQLite source I could find.  Even
>> if it was, implying 4 byte pointers on a 64 bit machine seems dangerous.
>>
>> -DTHREADSAFE=0
>>
>> Really?  What is wrong (and less likely to cause the unwary grief) than
>> the default of 1?
>>
>> The other flags seem to be guessed.  There is no need to tell a 64 bit
>> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
>> the system likely has LOCALTIME_R and GMTIME_R too as well as several
>> other header files.
>>
>> If you do not want to build SQLite using its build system then the
>> approach I take is to run SQLite's configure, grab the DEFS = line out
>> of the resulting Makefile and generate a .h file with the relevant -D
>> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
>> will #include any config.h so you can dump your #defines in there.
>>
>> Roger
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> DBD-SQLite mailing list
> dbd-sql...@lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key support in Sqlite

[Jean-Christophe Deschamps]

>create table cities
>(
>id integer primary key not null,
>name text not null
>);
>
>create table people
>(
>id integer primary key not null,
>name text not null,
>cities_id integer not null,
>foreign key(cities_id) references cities(id)
>);
>
>insert into cities(name) values('Campos');
>insert into cities(name) values('Araraquara');
>insert into cities(name) values('Porto');
>insert into cities(name) values('Curitiba');
>insert into people(name, cities_id) values('John', 3);
>insert into people(name, cities_id) values('Mary', 2);
>
>Regarding cities don't have the Id = 8, this statement should fail:
>insert into people(name, cities_id) values('Pedro', 8);

This last insert fails here (3.6.21) with constraint violation.

Can you check which version you're actually running:

select sqlite_version();
3.6.21


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

Re: [sqlite] Foreign key support in Sqlite

[Igor Tandetnik]

Luciano de Souza wrote:
> Everything downloaded correctly, but there is still something wrong.

http://www.sqlite.org/foreignkeys.html#fk_enable
http://www.sqlite.org/pragma.html#pragma_foreign_keys

Igor Tandetnik

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

Re: [sqlite] Foreign key support in Sqlite

[Luciano de Souza]

Everything downloaded correctly, but there is still something wrong. Perhaps 
my very short experience in SQL resulted in any statement mistake.
Let me show again how the tables are created and the data inserted:

create table cities
(
id integer primary key not null,
name text not null
);

create table people
(
id integer primary key not null,
name text not null,
cities_id integer not null,
foreign key(cities_id) references cities(id)
);

insert into cities(name) values('Campos');
insert into cities(name) values('Araraquara');
insert into cities(name) values('Porto');
insert into cities(name) values('Curitiba');
insert into people(name, cities_id) values('John', 3);
insert into people(name, cities_id) values('Mary', 2);

Regarding cities don't have the Id = 8, this statement should fail:
insert into people(name, cities_id) values('Pedro', 8);
Unexplainably, the record is added despite the constraint.
What am I doing wrong?

- Original Message - 
From: "Jean-Christophe Deschamps" 
To: "General Discussion of SQLite Database" 
Sent: Sunday, January 03, 2010 2:09 PM
Subject: Re: [sqlite] Foreign key support in Sqlite


> Hi,
>
>>I am trying to migrate to Sqlite3.6.21. I visited the Sqlite site and
>>downloaded the compiled file. I'm surprised in verifying that the zip
>>only had the executable. I hoped to find also the DLL.
>
> I bet you downloaded this: http://www.sqlite.org/sqlite-3_6_21.zip
>
> This is the CLI, he command-line interface.
>
>
> You need to donload this as well:
> http://www.sqlite.org/sqlitedll-3_6_21.zip
>
> This is the windows 32 dll file.
>
>
> Happy New SQLite
>
> ___
> 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] Foreign key support in Sqlite

[Jean-Christophe Deschamps]

Hi,

>I am trying to migrate to Sqlite3.6.21. I visited the Sqlite site and 
>downloaded the compiled file. I'm surprised in verifying that the zip 
>only had the executable. I hoped to find also the DLL.

I bet you downloaded this: http://www.sqlite.org/sqlite-3_6_21.zip

This is the CLI, he command-line interface.


You need to donload this as well: 
http://www.sqlite.org/sqlitedll-3_6_21.zip

This is the windows 32 dll file.


Happy New SQLite

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

[sqlite] Foreign key support in Sqlite

[Luciano de Souza]

Hello listers,

I am trying to migrate to Sqlite3.6.21. I visited the Sqlite site and 
downloaded the compiled file. I'm surprised in verifying that the zip only had 
the executable. I hoped to find also the DLL.

Well, since I could not find the DLL file concerning this version, I presumed 
DLL does not change and for this reason, an update is not necessary for it.

In order to do my first test, I created the following tables and inserted some 
values. 



create table cities

(

id integer primary key not null,

name text not null

);

create table people

(

id integer primary key not null,

name text not null,

cities_id integer not null,

foreign key(cities_id) references people(id)

);

insert into cities(name) values('Campos');

insert into cities(name) values('Araraquara');

insert into cities(name) values('Recife');

insert into cities(name) values('Curitiba');

insert into people(name, cities_id) values('João', 3);

insert into people(name, cities_id) values('Maria', 2);

The objective is to test the new support for foreign keys. I hoped that this 
command failed:

insert into people(name, cities_id) values('John', 8);

Unfortunately, the value was added successfully.

Is there something wrong? I need to do something to enable the foreign key 
support?

This is my first message to the list. It's a pleasure for me to join the Sqlite 
community.



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

Re: [sqlite] bind for sub-queries

[Jay A. Kreibich]

On Sun, Jan 03, 2010 at 10:47:01AM +0100, Mathieu SCHROETER scratched on the 
wall:
> Hello,
> 
> I've a simple question about the bind functions. If it can
> be realistic to have in the future, a way to bind a sub-query
> in a query?

  Doubtful.  Sub-queries are a syntactical thing and exist because of
  limitations in the SQL language.  From the perspective of the
  database engine, sub-queries are merged into the main query as one big
  operation.

  Depending on your situation, you might be able to use views, temporary
  views, or temporary tables.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bind for sub-queries

[Mathieu SCHROETER]

Hello,

I've a simple question about the bind functions. If it can
be realistic to have in the future, a way to bind a sub-query
in a query?


I create queries with a number of sub-queries which depends
of the user. Then, if all sub-queries can be prepared
independently of the main query, it suggests that a significant
time would be saved.


Regards,

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

Re: [sqlite] uninstalling sqlite

[Simon Slavin]

On 3 Jan 2010, at 3:34am, Ervin Sebag wrote:

> No need for google-fu antics. I was able to uninstall using fnpkg -r with
> the following results:
> 
> Removing file /mnt/HD_a2/ffp/lib/pkgconfig/sqlite3.pc
> Removing file /mnt/HD_a2/ffp/lib/libsqlite3.la
> Removing file /mnt/HD_a2/ffp/lib/libsqlite3.a
> Removing file /mnt/HD_a2/ffp/include/sqlite3ext.h
> Removing file /mnt/HD_a2/ffp/include/sqlite3.h
> 
> However i am still able to access the sqlite prompt by typing 'sqlite3'
> 
> I think all other sqlite files should be deleted as well.

Your problem is with the package manager, not with sqlite3.  You may have 
installed another package with a dependency on the files that weren't 
uninstalled.  Or perhaps those files come with the OS rather than having been 
installed with the package.

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