Re: [sqlite] How to realize the ROWID in a view?
Thanks for you reply.
I Just want to get a sequence number in a view, that is not the ID field in
the table foo.
Table foo is a sample of mine. In fact, my table is not only the id and x
field.
And the x field could be duplicate in the table foo.
Could you help me to finish that?
Thanks.
On 6/12/06, Christian Smith <[EMAIL PROTECTED]> wrote:
PY uttered:
> Hi All,
>
> I have a problem about the ROWID in a view. I want to simulate a ROWID
in a
> view just like the same purpose in a table.
>
> For Example:
>
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> insert into foo(x) values('X');
> insert into foo(x) values('Y');
> insert into foo(x) values('X');
> insert into foo(x) values('Z');
>
> Create View v_foo AS
> SELECT distinct(x) FROM foo
> ORDER BY x desc;
>
>
> SELECT * from foo;
>
> id x
> -- --
> 1 X
> 2 Y
> 3 X
> 4 Z
>
>
> My expect result of "select * from v_foo;" is
>
> id x
> -- --
> 1 Z
> 2 Y
> 3 X
>
>
>
> Would you please help to tell me how to finish that?
> Thanks for your grest help.
Why is this your expected result? Distinct applies to the whole row,
which includes the id. The view to get id and x is:
Create View v_foo AS
select distinct id,x FROM foo
ORDER BY x desc;
This will give you:
sqlite> select * from v_foo;
4|Z
2|Y
3|X
1|X
Each row is indeed distinct.
As you view is defined, you don't get the id at all, and distinct does
indeed return a single instance of X, Y and Z. If you want the x to be
unique, make it the primary key or create a unique index on it:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE );
The insert of the second x=X row will now fail:
sqlite> insert into foo(x) values('X');
sqlite> insert into foo(x) values('Y');
sqlite> insert into foo(x) values('X');
SQL error: column x is not unique
sqlite> insert into foo(x) values('Z');
sqlite>
>
> Thanks,
> VK
>
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
[sqlite] SQLite_Exec16 - suggestion
Full UTF-16 support functions are present, except SQLite_Exec16. As I havely using UTF-16 and scripts (UPDATE and INSERT), I created such addon to SQLiteAPI as Delphi function). However, it may be worted to be natively supported from SQLiteAPI, instead to be simulated. Sasa -- www.szutils.net
Re: [sqlite] lemon segfault
Michael Somos wrote: I found the following for "lemon" in the sqlite-3.3.6 distribution : === lemon -x Lemon version 1.0 lemon /dev/null Segmentation fault gdb ./lemon GNU gdb 6.4 Copyright 2005 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i686-pc-linux-gnu"...Using host libthread_db library "/lib/libthread_db.so.1". (gdb) run /dev/null Starting program: /home/tmp/lemon /dev/null Program received signal SIGSEGV, Segmentation fault. 0x08053b5a in confighash (a=0xb740) at lemon.c:4607 4607 h = h*571 + a->rp->index*37 + a->dot; (gdb) bt #0 0x08053b5a in confighash (a=0xb740) at lemon.c:4607 #1 0x08053fab in Configtable_find (key=0xb740) at lemon.c:4720 #2 0x0804a83b in Configlist_addbasis (rp=0x41, dot=0) at lemon.c:1165 #3 0x08049935 in FindStates (lemp=0xb7e0) at lemon.c:723 #4 0x0804b37e in main (argc=2, argv=0xb904) at lemon.c:1482 (gdb) quit === Michael, You should report this bug at http://www.sqlite.org/cvstrac/tktnew so it will be tracked and fixed. Dennis Cote
Re: [sqlite] How to realize the ROWID in a view?
PY uttered:
Hi All,
I have a problem about the ROWID in a view. I want to simulate a ROWID in a
view just like the same purpose in a table.
For Example:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
insert into foo(x) values('X');
insert into foo(x) values('Y');
insert into foo(x) values('X');
insert into foo(x) values('Z');
Create View v_foo AS
SELECT distinct(x) FROM foo
ORDER BY x desc;
SELECT * from foo;
id x
-- --
1 X
2 Y
3 X
4 Z
My expect result of "select * from v_foo;" is
id x
-- --
1 Z
2 Y
3 X
Would you please help to tell me how to finish that?
Thanks for your grest help.
Why is this your expected result? Distinct applies to the whole row,
which includes the id. The view to get id and x is:
Create View v_foo AS
select distinct id,x FROM foo
ORDER BY x desc;
This will give you:
sqlite> select * from v_foo;
4|Z
2|Y
3|X
1|X
Each row is indeed distinct.
As you view is defined, you don't get the id at all, and distinct does
indeed return a single instance of X, Y and Z. If you want the x to be
unique, make it the primary key or create a unique index on it:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE );
The insert of the second x=X row will now fail:
sqlite> insert into foo(x) values('X');
sqlite> insert into foo(x) values('Y');
sqlite> insert into foo(x) values('X');
SQL error: column x is not unique
sqlite> insert into foo(x) values('Z');
sqlite>
Thanks,
VK
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] How to realize the ROWID in a view?
It's been a while since I did it, but I think I just listed the fields to
display and included that field name
PY ([EMAIL PROTECTED]) wrote:
>
> Hi All,
>
> I have a problem about the ROWID in a view. I want to simulate a ROWID in a
> view just like the same purpose in a table.
>
> For Example:
>
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> insert into foo(x) values('X');
> insert into foo(x) values('Y');
> insert into foo(x) values('X');
> insert into foo(x) values('Z');
>
> Create View v_foo AS
> SELECT distinct(x) FROM foo
> ORDER BY x desc;
>
>
> SELECT * from foo;
>
> id x
> -- --
> 1 X
> 2 Y
> 3 X
> 4 Z
>
>
> My expect result of "select * from v_foo;" is
>
> id x
> -- --
> 1 Z
> 2 Y
> 3 X
>
>
>
> Would you please help to tell me how to finish that?
> Thanks for your grest help.
>
>
>
>
> Thanks,
> VK
>
[sqlite] How to realize the ROWID in a view?
Hi All,
I have a problem about the ROWID in a view. I want to simulate a ROWID in a
view just like the same purpose in a table.
For Example:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
insert into foo(x) values('X');
insert into foo(x) values('Y');
insert into foo(x) values('X');
insert into foo(x) values('Z');
Create View v_foo AS
SELECT distinct(x) FROM foo
ORDER BY x desc;
SELECT * from foo;
id x
-- --
1 X
2 Y
3 X
4 Z
My expect result of "select * from v_foo;" is
id x
-- --
1 Z
2 Y
3 X
Would you please help to tell me how to finish that?
Thanks for your grest help.
Thanks,
VK
Re: [sqlite] SQLite 3.3.6 - possibly memory leak
I've seen a few issues myself, but they all appeared to be related to
the memory management thing. If you're using VC, try putting this into
your code:
#define CRTDBG_MAP_ALLOC
#include
#include
// and then in your main function:
_CrtSetDbgFlag ( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF |
_CRTDBG_CHECK_ALWAYS_DF);
Put a copy of the above line in sqlite3_open command as well. I think that it
would be good to run the standard tests with that enabled. It provides a lot of
useful information and checks for out of bounds or memory leaks on every
allocation. It is similar to Borland's Codeguard. I have no idea how to enable
malloc_dbg automatically in GCC, though.
Here's a few links:
http://www.amanjit-gill.de/CPP_VCtips.html
http://www.cprogramming.com/debugging/valgrind.html
http://msdn2.microsoft.com/en-us/library/sc65sadd(VS.80).aspx
Sasa Zeman wrote:
I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.
File variant works fine:
SQLite3_Open('Test.sqb',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
However memory variant rise a memory leak report:
SQLite3_Open(':memory:',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.
Sasa
--
www.szutils.net
Re: [sqlite] MacOS X build failure
Kon Lovett uttered: Hi, The following occurs building the CVS head w/ gcc 4.0.1 on MacOS 10.4.6: ./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1 -I/usr/local/include -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_CURSOR -c ../sqlite/src/loadext.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1 -I/usr/local/include -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_CURSOR -c ../sqlite/src/loadext.c -fno-common -DPIC -o .libs/loadext.o ../sqlite/src/loadext.c: In function 'sqlite3CloseExtensions': ../sqlite/src/loadext.c:285: error: 'SQLITE_LIBRARY_TYPE' undeclared (first use in this function) ../sqlite/src/loadext.c:285: error: (Each undeclared identifier is reported only once ../sqlite/src/loadext.c:285: error: for each function it appears in.) ../sqlite/src/loadext.c:285: error: parse error before ')' token Probably 'HAVE_DLOPEN' not defined but don't know why. I have the same issue on Linux. I think it's because HAVE_DLOPEN is not defined, and DRH doesn't use or maintain the autoconf based build, but uses his own Makefile (possibly Makefile.linux-gcc based). configure.ac will need to be updated to check for dlopen and set the defines accordingly. As autoconf is horrid, bagsy not me to do it:) Use you own Makefile until the build system is fixed or remove loadext.c from your build. Thank you in advance, Kon -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] SQLite 3.3.6 - possibly memory leak
Sasa Zeman uttered:
I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.
File variant works fine:
SQLite3_Open('Test.sqb',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
However memory variant rise a memory leak report:
SQLite3_Open(':memory:',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.
Speculating, as I'm not familiar with your Delphi wrapper, but are you
sqlite3_free()'ing the error message string when reporting any errors?
Sasa
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
[sqlite] SQLite 3.3.6 - possibly memory leak
I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.
File variant works fine:
SQLite3_Open('Test.sqb',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
However memory variant rise a memory leak report:
SQLite3_Open(':memory:',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.
Sasa
--
www.szutils.net
