date:20091202

Re: [sqlite] Doubts about usage of sqlite3.exe and foreign keys (enable this feature)

2009-12-02 Thread Simon Davies

2009/12/2 Ubirajara Marques da Cruz :
> To Support,
>
>
>
> I am trying to develop a data base what need to specify  foreign key to 
> preserve problems with insertion of datas.
>
>
>
> I have four tables like i describe below:
>
.
.
.
>
> When i start sqlite3 and inside sqlite prompt line i create data base with 
> this conditions and enable foreign key feature data base return error when i 
> try to put some datas out of range and return fail because foreign key isn´t 
> respected. But when i create data base right with sqlite3.exe dB.db  "SQL 
> STATMENT" command, foreign key is not respected.
>
>
> Why this happen?
>

http://www.sqlite.org/pragma.html#pragma_foreign_keys indicates that
the setting applies to the connection, not the database.

Each time you issue  command, a new
connection is created.

Try 

>
> Thanks in advance.
>
> Eng° Ubirajara Marques da Cruz
>

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

[sqlite] Doubts about usage of sqlite3.exe and foreign keys (enable this feature)

2009-12-02 Thread Ubirajara Marques da Cruz

To Support,

 

I am trying to develop a data base what need to specify  foreign key to 
preserve problems with insertion of datas. 

 

I have four tables like i describe below:

 

1st) (Projects)   with this structure/fields : 

índex(primary key)  int

ent_project  varchar(50)

2nd)(Systems) with this structure/fields:

índex(primary key) int

ent_system varchar(50)

 

3rd) (equip) with this structure/fields:

índex(primary key) int

ent_equip varchar(50)

 

4rt) (Docs) with this structure/fields:

índex(primary key) int

ent_docs varchar(50)

 

5ft) (datas) with this structure/fields

índex(primary key) int

ent_project (foreign key with projects.index)

ent_system(foreign key with systems.index)

ent_equip(foreign key with equip.index)

ent_docs(foreign key with docs.index)

 

I only described structure, not SQL commands to give some Idea what is it. If 
necessary i will send complete code to analyse.

 

THE PROBLEM IS:

 

I created these tables using right command like this;

 

C:\> sqlite3.exe  teste.db "CREATE TABLE projects(index int NOT NULL 
UNIQUE,project VARCHAR(60) NOT NULL, PRIMARY KEY (index));"

 

For systems, equipment and documents are the same command right into command 
line .

To datas

 

C:\> sqlite3 teste.db "CREATE TABLE datas(index int UNIQUE NOT NULL,project 
VARCHAR(60), 

,

PRIMARY KEY (index),

FOREIGN KEY (project) REFERENCES project(index),

FOREIGN KEY (system) REFERENCES system(index),

FOREIGN KEY (equip) REFERENCES equip(index),

FOREIGN KEY (documents) REFERENCES documents(index));"

 

After this, i put some datas into tables and  i  tested the condition where i 
inserted some datas without  of range  to see if  foreign keys would return 
some error.

For my surprise, the command has sucessfull! 

 

I red the post into sqlite3 home Page about the foreign key condition is 
optional and i need to enable it to make it work.

 

The I downloaded the newest version of sqlite3 (3.6.20) to test this condition 
and a have the same result. My sqlite3 version was 3.6.12.

 

When i start sqlite3 and inside sqlite prompt line i create data base with this 
conditions and enable foreign key feature data base return error when i try to 
put some datas out of range and return fail because foreign key isn´t 
respected. But when i create data base right with sqlite3.exe dB.db  "SQL 
STATMENT" command, foreign key is not respected.

 

Why this happen?

 

Thanks in advance.

 

 

Eng° Ubirajara Marques da Cruz

CEMIG - GERAÇÃO E TRANSMISSÃO

Gerência de expansão da geração - EN/EG

tel. +55(31)3506-4602 

Email: birac...@cemig.com.br 

 


As informacoes contidas nesta mensagem e nos arquivos anexados sao para uso
exclusivo do destinatario aqui indicado e podem conter assuntos comerciais, de 
propriedade intelectual ou outras informacoes confidenciais, protegidas pelas 
leis 
aplicaveis.
Caso nao seja o destinatario correto, por favor, notifique o remetente 
imediatamente e elimine esta mensagem, uma vez que qualquer revisao, leitura, 
copia e, ou divulgacao do conteudo desta mensagem sao estritamente proibidas 
e nao autorizadas.
Obrigado por sua cooperacao.
The information contained in this message and the attached files are restricted 
to 
the addressee, and may contain commercial information, copyright, or other 
confidential information protected by law.
If you are not the recipient, please notify the sender immediately and delete 
it 
from you system, since any change, reading, copy and, or dissemination of this 
e-mail is strictly prohibited by and not authorized.
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?

2009-12-02 Thread Dan Kennedy


On Dec 2, 2009, at 3:07 PM, Ralf Junker wrote:

> With the latest FTS3 changes, fts3b-4.9 no longer passes. This short  
> SQL
> emulates the test:
>
>   DROP TABLE IF EXISTS t4;
>   CREATE VIRTUAL TABLE t4 USING fts3(c);
>   INSERT INTO t4 (docid, c) VALUES (12, 'still testing');
>   UPDATE t4 SET docid = 14 WHERE docid = 12;
>
> Note that SQLite 3.6.20 chokes with "SQL logic error or missing
> database" on the last line.
>
> Everything runs fine with the latest FTS3. I can even query
>
>   SELECT docid, * FROM t4 WHERE t4 MATCH 'still';
>
> and it happily returns that the updated docid equals 14.
>
> Questions:
>
> * Is it now intentionally possible to update FTS3 DOCIDs?

It is. The failing sqlite test case has been replaced.

Dan.

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

Re: [sqlite] Add better support to 64-bit Windows

2009-12-02 Thread Fiacca . m

In x86 the specification of "=A" is EDX:EAX, however in x86-64 is RAX.
And, rdtsc always store EDX:EAX.
In a word, the code of sqlite3Hwtime is opposite.



diff -u sqlite-source-3_6_20/hwtime.h sqlite-source-3_6_20.1/hwtime.h
--- sqlite-source-3_6_20/hwtime.h Wed Nov 4 08:54:16 2009
+++ sqlite-source-3_6_20.1/hwtime.h Thu Dec 3 00:52:48 2009
@@ -30,9 +30,9 @@
#if defined(__GNUC__)

__inline__ sqlite_uint64 sqlite3Hwtime(void){
- unsigned int lo, hi;
- __asm__ __volatile__ ("rdtsc" : "=a" (lo), "=d" (hi));
- return (sqlite_uint64)hi << 32 | lo;
+ unsigned long long val;
+ __asm__ __volatile__ ("rdtsc" : "=A" (val));
+ return val;
}

#elif defined(_MSC_VER)
@@ -46,14 +46,25 @@

#endif

-#elif (defined(__GNUC__) && defined(__x86_64__))
+#elif (defined(__GNUC__) || (defined(_MSC_VER) && _MSC_VER >= 1400)) && \
+ (defined(__x86_64__) || defined(_M_X64))
+
+ #if defined(__GNUC__)

__inline__ sqlite_uint64 sqlite3Hwtime(void){
- unsigned long val;
- __asm__ __volatile__ ("rdtsc" : "=A" (val));
- return val;
+ unsigned int lo, hi;
+ __asm__ __volatile__ ("rdtsc" : "=a" (lo), "=d" (hi));
+ return (sqlite_uint64)hi << 32 | lo;
}
-
+
+ #elif defined(_MSC_VER)
+
+ __inline sqlite_uint64 __cdecl sqlite3Hwtime(void){
+ return __rdtsc();
+ }
+
+ #endif
+
#elif (defined(__GNUC__) && defined(__ppc__))

__inline__ sqlite_uint64 sqlite3Hwtime(void){
diff -u sqlite-source-3_6_20/mutex_w32.c sqlite-source-3_6_20.1/mutex_w32.c
--- sqlite-source-3_6_20/mutex_w32.c Wed Nov 4 08:54:16 2009
+++ sqlite-source-3_6_20.1/mutex_w32.c Thu Dec 3 00:34:38 2009
@@ -49,7 +49,7 @@
** this out as well.
*/
#if 0
-#if SQLITE_OS_WINCE
+#if SQLITE_OS_WINCE || SQLITE_OS_WIN64
# define mutexIsNT() (1)
#else
static int mutexIsNT(void){
diff -u sqlite-source-3_6_20/os.h sqlite-source-3_6_20.1/os.h
--- sqlite-source-3_6_20/os.h Wed Nov 4 08:54:16 2009
+++ sqlite-source-3_6_20.1/os.h Thu Dec 3 00:07:44 2009
@@ -77,6 +77,14 @@
# define SQLITE_OS_WINCE 0
#endif

+/*
+** Determine if we are dealing with 64-bit Windows
+*/
+#if defined(_WIN64)
+# define SQLITE_OS_WIN64 1
+#else
+# define SQLITE_OS_WIN64 0
+#endif

/*
** Define the maximum size of a temporary filename
diff -u sqlite-source-3_6_20/os_win.c sqlite-source-3_6_20.1/os_win.c
--- sqlite-source-3_6_20/os_win.c Wed Nov 4 08:54:16 2009
+++ sqlite-source-3_6_20.1/os_win.c Thu Dec 3 00:07:44 2009
@@ -147,7 +147,7 @@
** WinNT/2K/XP so that we will know whether or not we can safely call
** the LockFileEx() API.
*/
-#if SQLITE_OS_WINCE
+#if SQLITE_OS_WINCE || SQLITE_OS_WIN64
# define isNT() (1)
#else
static int isNT(void){
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possibly a bug in SQLite?

2009-12-02 Thread P Kishor

On Wed, Dec 2, 2009 at 9:42 AM, Nick Shaw  wrote:
> Ok, my mistake.  But would AUTOINCREMENT imply NOT NULL?


No.

> Could you have
> an AUTOINCREMENT field with post-updated null values?
>

Yes, unless you also specify NOT NULL


> Nick.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
> Sent: 02 December 2009 15:02
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Possibly a bug in SQLite?
>
> On Wed, Dec 02, 2009 at 09:38:54AM -, Nick Shaw scratched on the
> wall:
>> You don't need to define the PRIMARY KEY as NOT NULL - it's implied.
>
>  Yes, you do.  You shouldn't, but you do:
>
>    http://sqlite.org/lang_createtable.html
>
>    According to the SQL standard, PRIMARY KEY should imply NOT NULL.
>    Unfortunately, due to a long-standing coding oversight, this is not
>    the case in SQLite. SQLite allows NULL values in a PRIMARY KEY
>    column.
>
>> The column constraint flow diagram in the documentation in fact
> doesn't
>> allow it:
>> http://www.sqlite.org/syntaxdiagrams.html#column-constraint
>
>  The diagrams are for clear human readability, not to define the
>  accepted language.
>
>    -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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possibly a bug in SQLite?

2009-12-02 Thread Nick Shaw

Ok, my mistake.  But would AUTOINCREMENT imply NOT NULL?  Could you have
an AUTOINCREMENT field with post-updated null values?

Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: 02 December 2009 15:02
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possibly a bug in SQLite?

On Wed, Dec 02, 2009 at 09:38:54AM -, Nick Shaw scratched on the
wall:
> You don't need to define the PRIMARY KEY as NOT NULL - it's implied.

  Yes, you do.  You shouldn't, but you do:

http://sqlite.org/lang_createtable.html

According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not
the case in SQLite. SQLite allows NULL values in a PRIMARY KEY
column.

> The column constraint flow diagram in the documentation in fact
doesn't
> allow it:
> http://www.sqlite.org/syntaxdiagrams.html#column-constraint

  The diagrams are for clear human readability, not to define the
  accepted language.

-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Problem with left-outer join and virtual tables. Version 3.6.18 and trunk

2009-12-02 Thread Schrum, Allan

Bump. One bump limit met :-)

Didn't know if this got lost in the holiday rush, but the bug remains. I have a 
work-around (see below) that does work, but didn't know if there was a better 
solution.

Regards,

-Allan

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Schrum, Allan
> Sent: Monday, November 23, 2009 11:29 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Bug: Problem with left-outer join and virtual tables.
> Version 3.6.18 and trunk
> 
> Hi Folks,
> 
> I have a virtual table implementation currently based upon 3.6.18
> sources. I have found a bug in the way left outer joins are implemented
> in SQLITE3 that causes bad results to occur. The short description of
> the problem is that the temporary registers setup to fetch data from
> the virtual tables overwrite the register used to store a value as part
> of the loop. By that I mean an inner loop overwrites a value that is
> expected to be there when used by the outer loop. As such, the result
> set is not as expected.
> 
> 
> The virtual table setup can be emulated with the following SQL:
> 
> create table atab(id int, mask text) ;
> create table btab(mask text, bid int) ;
> create table ctab(bid int, desc text) ;
> 
> insert into atab values (1, '1234') ;
> insert into atab values (2, '2345') ;
> insert into btab values ('1234', 1) ;
> insert into btab values ('1234', 2) ;
> insert into btab values ('1234', 3) ;
> insert into btab values ('1234', 7) ;
> insert into ctab values ( 1, 'Text for 1') ;
> insert into ctab values ( 2, 'Text for 2') ;
> insert into ctab values ( 3, 'Text for 3') ;
> 
> select a.id, a.mask, b.mask, b.bid, c.bid, c.desc
> from atab as a
> left outer join btab as b
> on a.mask = b.mask
> left outer join ctab as c
> on b.bid = c.bid
> where
> a.id = 1 ;
> 
> The following is the "explain" of the query as a virtual table
> implementation. The names have been "corrected" to emulate the SQL
> above, and the column numbers are obviously a bit different than those
> found above, but the problem is demonstrated:
> 
> addr  opcode p1p2p3p4 p5
> comment
>   -        -  --  -
> 
> 0 Trace  0 0 000
> (null)
> 1 Integer3 1 000
> (null)
> 2 Goto   0 50000
> (null)
> 3 VOpen  0 0 0 vtab:8224638:8101800   00  atab
> 4 VOpen  1 0 0 vtab:81F90E0:8101800   00  btab
> 5 VOpen  2 0 0 vtab:81E71C0:8101800   00  ctab
> 6 SCopy  1 4 000
> (null)
> 7 Integer1 2 000
> (null)
> 8 Integer1 3 000
> (null)
> 9 VFilter0 46200
> (null)
> 10VColumn0 0 600
> atab.id
> 11Ne 1 456 collseq(BINARY)6c
> (null)
> 12Integer0 8 000  init
> LEFT JOIN no-match flag
> 13VColumn0 3 400
> atab.mask
> 14Integer1 2 000
> (null)
> 15Integer1 3 000
> (null)
> 16VFilter1 42200
> (null)
> 17VColumn1 0 700
> btab.mask
> 18Ne 7 414 collseq(BINARY)6a
> (null)
> 19Integer1 8 000
> record LEFT JOIN hit
> 20Integer0 9 000  init
> LEFT JOIN no-match flag
> 21Integer174 000
> (null)
> 22VColumn1 1 500
> btab.bid
> 23Integer2 2 000
> (null)
> 24Integer2 3 000
> (null)
> 25VFilter2 38200
> (null)
> 26VColumn2 0 700
> ctab.bid
> 27Integer176 000
> (null)
> 28Ne 6 377 collseq(BINARY)6c
> (null)
> 29VColumn2 1 600
> ctab.bid
> 30Ne 6 375 collseq(BINARY)6b
> (null)
> 31Integer1 9 000
> record LEFT JOIN hit
> 32VColumn0 3 10   00
> atab.mask
> 33VColumn1 1 11   00
> btab.bid
> 34VColumn2 1 12

Re: [sqlite] Possibly a bug in SQLite?

2009-12-02 Thread D. Richard Hipp

On Dec 2, 2009, at 4:38 AM, Nick Shaw wrote:

> You don't need to define the PRIMARY KEY as NOT NULL - it's implied.
> The column constraint flow diagram in the documentation in fact  
> doesn't
> allow it:
> http://www.sqlite.org/syntaxdiagrams.html#column-constraint
>

That diagram is the syntax for a single constraint.  You can have  
multiple constraints per column.  (See 
http://www.sqlite.org/syntaxdiagrams.html#column-def) 
  The PRIMARY KEY is one constraint and NOT NULL is another  
constraint.  The two can occur together and in any order.

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

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

Re: [sqlite] Possibly a bug in SQLite?

2009-12-02 Thread Jay A. Kreibich

On Wed, Dec 02, 2009 at 09:38:54AM -, Nick Shaw scratched on the wall:
> You don't need to define the PRIMARY KEY as NOT NULL - it's implied.

  Yes, you do.  You shouldn't, but you do:

http://sqlite.org/lang_createtable.html

According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not
the case in SQLite. SQLite allows NULL values in a PRIMARY KEY
column.

> The column constraint flow diagram in the documentation in fact doesn't
> allow it:
> http://www.sqlite.org/syntaxdiagrams.html#column-constraint

  The diagrams are for clear human readability, not to define the
  accepted language.

-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

Re: [sqlite] Possibly a bug in SQLite?

2009-12-02 Thread P Kishor

On Wed, Dec 2, 2009 at 3:38 AM, Nick Shaw  wrote:
> You don't need to define the PRIMARY KEY as NOT NULL - it's implied.
> The column constraint flow diagram in the documentation in fact doesn't
> allow it:
> http://www.sqlite.org/syntaxdiagrams.html#column-constraint
>
> Maybe that's the problem?  Try recreating the table without the NOT NULL
> constraint on the "id" column and see if it works after that.


No, that is not the problem. I used the provided code and confirmed
that the table and the inserts work just fine. The problem is, as Igor
correctly conjectured, most likely with the code that the OP didn't
show, the TRIGGERs that might be causing additional INSERTs, and
violating the PK constraint.

To the original poster, please show the rest of the code that you have
omitted from your email.

>
> Thanks,
> Nick.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brandon Wang
> Sent: 01 December 2009 17:06
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Possibly a bug in SQLite?
>
> Hello,
>
> I've come upon a interesting scenerio.
>
> .sqlite> .schema rg_configuration
> CREATE TABLE 'rg_configuration' (
>    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
>    "configurationName" TEXT NOT NULL,
>    "host" TEXT,
>    "user" TEXT,
>    "parentArch" INTEGER NOT NULL,
>    "parentJob" INTEGER NOT NULL,
>    "parentSubblock" INTEGER NOT NULL,
>    "parentBlock" INTEGER NOT NULL,
>    "canBeRun" INTEGER DEFAULT (1)
> );
> [Addititonal indices, triggers, etc. here]
>
> One of my scripts attempts to execute the following:
>
> INSERT INTO main.rg_configuration (configurationName, parentArch,
> parentJob, parentSubblock, parentBlock, canBeRun) VALUES
> ('full_chip.nofeedthru', 9565, 3014, 33, 8, 1);
>
> Upon which I get the error:
>
> SQL error: PRIMARY KEY must be unique
>
> I'm not specifying the primary key, id. Is there some error on my part?
>
> Thanks!
>
> -Brandon
> ___
> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert Access sql to SQLite sql

2009-12-02 Thread Pavel Ivanov

Your Access query doesn't have good equivalent in SQLite. Your options are:
1. Execute SELECT separately and then for each row in the result issue
an UPDATE with necessary values (UPDATE can be prepared and you can
just bind all values).
2. Insert results of SELECT into some temporary table and then issue
one UPDATE statement like this:

UPDATE EvAtemp SET
EvAtemp.EstimateDate = (select Date from TempTable tt where tt.Code =
EvAtemp.Code and tt.Function = EvAtemp.Function),
EvAtemp.CodeEst = (select Code from TempTable tt where tt.Code =
EvAtemp.Code and tt.Function = EvAtemp.Function),
...

This will work much-much slower than first approach.

3. Modify your SELECT so that it joins with EvAtemp too to get from
there rowid of the row which should be updated, insert results into
temporary table and issue UPDATE like this:

UPDATE EvAtemp SET
EvAtemp.EstimateDate = (select Date from TempTable tt where
tt.EvAtempROWID = EvAtemp.rowid),
EvAtemp.CodeEst = (select Code from TempTable tt where tt.
EvAtempROWID = EvAtemp.rowid),
...

This will work faster than 2nd approach but I believe it's still
slower than the 1st.


Pavel

On Tue, Dec 1, 2009 at 7:53 PM, P.McFarlane  wrote:
> I am currently converting a project that uses Access as a database to using 
> SQLite. Part of this involves rewriting sql queries.
> So far this has been OK, however I have struck a problem with an UPDATE query.
> The following access sql query updates some fields in a table depending on 
> existing values in other fields in the table and uses a sub query
>
>  UPDATE EvAtemp INNER JOIN
> [SELECT EstimateIndex.Date, QAK1Data.JobNo, CodesIndex.Code, 
> FunctionsIndex.Function,
> EstimateDetail.Hours, EstimateDetail.Rate, EstimateDetail.EmpTime, 
> EstimateIndex.Contingent,
> EstimateDetail.Hours*EstimateDetail.Rate AS Charge, 
> (EstimateDetail.Hours*EstimateDetail.Rate)*(1+(EstimateIndex.Contingent/100)) 
> AS TotalCharge
> FROM (((EstimateDetail INNER JOIN EstimateIndex ON EstimateDetail.EstID = 
> EstimateIndex.EstID)
> INNER JOIN QAK1Data ON EstimateIndex.EstProjNo = QAK1Data.ProjRecID)
> INNER JOIN CodesIndex ON EstimateDetail.EstCodeRec = 
> CodesIndex.CodesIndexRecID)
> INNER JOIN FunctionsIndex ON EstimateDetail.EstFuncRec = 
> FunctionsIndex.FunctionsIndexRecID]. AS Q ON EvAtemp.JobNo = Q.JobNo
> SET EvAtemp.EstimateDate = [Q].[Date], EvAtemp.CodeEst = [Q].[Code], 
> EvAtemp.FunctionEst = [Q].[Function],
> EvAtemp.HoursEst = [Q].[Hours], EvAtemp.RateEst = [Q].[Rate], 
> EvAtemp.EmpTimeE = [Q].[EmpTime],
> EvAtemp.Contingent = [Q].[Contingent], EvAtemp.ChargeEst = [Q].[Charge], 
> EvAtemp.TotalCharge = [Q].[TotalCharge]
> WHERE (((EvAtemp.Code)=[Q].[Code]) AND ((EvAtemp.Function)=[Q].[Function]));
>
> My attempt in SQLite is
>
> UPDATE EvAtemp
> SET [EstDate] = [Q].[Date],
> [CodeEst] = [Q].[Code],
> [FunctionEst] = [Q].[Function],
> [HoursEst] = [Q].[Hours],
> [RateEst] = [Q].[Rate],
> [EmpTimeE] = [Q].[EmpTime],
> [Contingent] = [Q].[Contingent],
> [ChargeEst] = [Q].[Charge],
> [TotalCharge] = [Q].[TotalCharge ]
> (SELECT [EstimateIndex].[Date], [QAK1Data].[JobNo], [CodesIndex].[Code],
>      [FunctionsIndex].[Function], [EstimateDetail].[Hours],
>      [EstimateDetail].[Rate], [EstimateDetail].[EmpTime],
>      [EstimateIndex].[Contingent], [EstimateDetail].[Hours] *
>      [EstimateDetail].[Rate] AS [Charge], ([EstimateDetail].[Hours] *
>      [EstimateDetail].[Rate]) * (1 + ([EstimateIndex].[Contingent] /
>      100)) AS [TotalCharge]
>    FROM [EstimateIndex] INNER JOIN
>      [EstimateDetail] ON [EstimateIndex].[EstID] = [EstimateDetail].[EstID]
>      INNER JOIN
>      [QAK1Data] ON [EstimateIndex].[EstProjNo] = [QAK1Data].[ProjRecID]
>      INNER JOIN
>      [CodesIndex] ON [EstimateDetail].[EstCodeRec] =
>        [CodesIndex].[CodesIndexRecID] INNER JOIN
>      [FunctionsIndex] ON [EstimateDetail].[EstFuncRec] =
>        [FunctionsIndex].[FunctionsIndexRecID]) [Q]
> WHERE [Code]=[Q].[Code]
> AND [Function]=[Q].[Function]
> AND [ProjNo] = [Q].[JobNo]
>
> This does not work. I have tried many variations with no success.
> I hope that some one can point me in the right direction with an example of 
> correct useage.
>
> thanks
> Phil
> ___
> 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] Sampling Data

2009-12-02 Thread Rod Dav4is

Could you provide some example data? I'm particularly interested in the 
values of the Minute field.
-R.

npearson99 wrote:
> I had another question before about moving averages via sql statement and the
> response I got was great.
>
> Now I'm trying to "sample" the data.  I guess it would be a form of
> smoothing but I'm not sure what to call it.
>
> I want to do something like this:
>
> row1 = (item[0] + item[1] + item[2] + item[3] )/4)
> row2 = (item[4] + item[5] + item[6] + item[7] )/4)
> row3 = (item[8] + item[9] + item[10] + item[11] )/4)
>
> I'm using some pseudo code here, I hope this make sense.
>
> Example Table:
> tableID
> Minute
> Watt
>
> I'm trying to sum average the watt column by minute.
>
> Thanks in advance.
>
>
>   

-- 
Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA
Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/
538 ancestral & collateral families, mostly 17°-19° century 
New England & European roots. Total population: 136,000+
Annex: http://www.gencircles.com/users/dav4is/
email: dav...@yahoo.com
A Democrat, a Republican and a giraffe walk into a bar. The 
bartender looks up from his want ads and says, "What is this, a joke?"
-unknown

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

Re: [sqlite] Possibly a bug in SQLite?

2009-12-02 Thread Nick Shaw

You don't need to define the PRIMARY KEY as NOT NULL - it's implied.
The column constraint flow diagram in the documentation in fact doesn't
allow it:
http://www.sqlite.org/syntaxdiagrams.html#column-constraint

Maybe that's the problem?  Try recreating the table without the NOT NULL
constraint on the "id" column and see if it works after that.

Thanks,
Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brandon Wang
Sent: 01 December 2009 17:06
To: sqlite-users@sqlite.org
Subject: [sqlite] Possibly a bug in SQLite?

Hello,

I've come upon a interesting scenerio.

.sqlite> .schema rg_configuration
CREATE TABLE 'rg_configuration' (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"configurationName" TEXT NOT NULL,
"host" TEXT,
"user" TEXT,
"parentArch" INTEGER NOT NULL,
"parentJob" INTEGER NOT NULL,
"parentSubblock" INTEGER NOT NULL,
"parentBlock" INTEGER NOT NULL,
"canBeRun" INTEGER DEFAULT (1)
);
[Addititonal indices, triggers, etc. here]

One of my scripts attempts to execute the following:

INSERT INTO main.rg_configuration (configurationName, parentArch,
parentJob, parentSubblock, parentBlock, canBeRun) VALUES
('full_chip.nofeedthru', 9565, 3014, 33, 8, 1);

Upon which I get the error:

SQL error: PRIMARY KEY must be unique

I'm not specifying the primary key, id. Is there some error on my part?

Thanks!

-Brandon
___
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] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?

2009-12-02 Thread Ralf Junker

With the latest FTS3 changes, fts3b-4.9 no longer passes. This short SQL
emulates the test:

   DROP TABLE IF EXISTS t4;
   CREATE VIRTUAL TABLE t4 USING fts3(c);
   INSERT INTO t4 (docid, c) VALUES (12, 'still testing');
   UPDATE t4 SET docid = 14 WHERE docid = 12;

Note that SQLite 3.6.20 chokes with "SQL logic error or missing 
database" on the last line.

Everything runs fine with the latest FTS3. I can even query

   SELECT docid, * FROM t4 WHERE t4 MATCH 'still';

and it happily returns that the updated docid equals 14.

Questions:

* Is it now intentionally possible to update FTS3 DOCIDs?
* Should I modify my tests accordingly?

Thanks,

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

Re: [sqlite] Doubts about usage of sqlite3.exe and foreign keys (enable this feature)

[sqlite] Doubts about usage of sqlite3.exe and foreign keys (enable this feature)

Re: [sqlite] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?

Re: [sqlite] Add better support to 64-bit Windows

Re: [sqlite] Possibly a bug in SQLite?

Re: [sqlite] Possibly a bug in SQLite?

Re: [sqlite] Bug: Problem with left-outer join and virtual tables. Version 3.6.18 and trunk

Re: [sqlite] Possibly a bug in SQLite?

Re: [sqlite] Possibly a bug in SQLite?

Re: [sqlite] Possibly a bug in SQLite?

Re: [sqlite] Convert Access sql to SQLite sql

Re: [sqlite] Sampling Data

Re: [sqlite] Possibly a bug in SQLite?

[sqlite] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?

14 matches

Site Navigation

Mail list logo

Footer information