[sqlite] Re: Table with 2 primary keys... what's the problem?
Darren Duncan <[EMAIL PROTECTED]> wrote: At 11:37 PM +0100 5/12/07, Paulo J. Matos wrote: SQLite does not have a boolean data type, though I think it really should; the boolean type is fundamental to the relational model of data. For example, what is the data type of the expression in a WHERE clause if not a boolean? It's an int, with 1 meaning true and 0 meaning false. C language managed without boolean quite happily for some 35 years now. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: List of columns of a table
jose isaias cabrera <[EMAIL PROTECTED]> wrote: Is there any other way that I could get a list of the columns of a table? PRAGMA table_info(tableName); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] List of columns of a table
Greetings.
I have been playing with
PRAGMA index_list(tablename)
but I am not getting it to work. I am using D, a new language comparable to
c. Anyway, the libraries available are lacking and so I must use queries to
get the values that I want. But I am trying to get the list of the columns
of a table, but I am failing. The call I am doing is,
Rows = db.query("PRAGMA index_list(LSOpenJobs);");
But Rows has nothing. Is there any other way that I could get a list of the
columns of a table?
thanks,
josé
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Table with 2 primary keys... what's the problem?
At 11:37 PM +0100 5/12/07, Paulo J. Matos wrote: Moreover, active in table product is a boolean but I'm using an int since I don't know if there's a boolean type, is there? SQLite has just these types: Int, Real, Text, Blob, Null. SQLite does not have a boolean data type, though I think it really should; the boolean type is fundamental to the relational model of data. For example, what is the data type of the expression in a WHERE clause if not a boolean? sqlite> CREATE TABLE productinfo (productasin INTEGER PRIMARY KEY, infoid INTEGER PRIMARY KEY); SQL error: table "productinfo" has more than one primary key Why is this? How can I solve the problem? If what you intended here was to have a single primary key which comprises both the asin and id columns (most likely), such that only the combination of the 2 must be unique, then you have to specify it using different syntax, such as this: CREATE TABLE productinfo ( productasin INTEGER, infoid INTEGER, PRIMARY KEY (productasin, infoid) ) If you intended that each column is individually unique (unlikely), then each one is simply a "key", which in SQLite syntax involves saying one or both are "UNIQUE" rather than "PRIMARY KEY". By definition, there can only be one "primary". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Table with 2 primary keys... what's the problem?
Hello, I have something like this: Table Product asin : text PK active : integer title : text Table Info -- id : int PK AUTOINC price : int date : date Now, since each product can have 1 .. n infos, I have yet this table: Table ProductInfo - asin : text PK (should be a product PK) id : int PK (should be a info PK) In sqlite I do: $ sqlite3 buh SQLite version 3.3.12 Enter ".help" for instructions sqlite> CREATE TABLE product (asin TEXT PRIMARY KEY NOT NULL, active INTEGER, title TEXT); sqlite> CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT, price INTEGER, date DATE); sqlite> CREATE TABLE productinfo (productasin INTEGER PRIMARY KEY, infoid INTEGER PRIMARY KEY); SQL error: table "productinfo" has more than one primary key Why is this? How can I solve the problem? Moreover, active in table product is a boolean but I'm using an int since I don't know if there's a boolean type, is there? Cheers, -- Paulo Jorge Matos - pocm at soton.ac.uk http://www.personal.soton.ac.uk/pocm PhD Student @ ECS University of Southampton, UK - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Building a newer SQlite3 dylib/framework for XCode ?
Hi Folks I am using SQLite3 in a Mac XCode project (all C, not cocoa). It's working wonderfully well. However - I find that I need one or 2 functions which are not included with the built in SQlite3 library which comes with the Mac OSX. In particular I want to add columns to existing tables, which doesn't seem to work with the Mac standard build. I need to be able to update older databases by adding columns. So, I need to rebuild a newer version of SQlite and link it into my XCode project as a dylib. Unfortunately, I cannot find any information about how to do this, and everything I try seems to lead to a dead end. I have spent the best part of week compiling binaries, .a files and so on, but with no luck linking my XCode successfully to a newer build of SQlite3. Could anyone assist me ? Does anyone have a newer dylib or XCode Framework already pre-built ? Ideally I want a drop in replacement for the Apple library which can be EMBEDDED in my app bundle, and used instead of the built in OS version of the library (I definitely do not want users to have to install a newer version of SQlite in their systems - needs to be delivered with the app itself). An app like SQLiteManager is doing exactly what I need - it seems to have a newer version embedded, and the SQL I need to run in my own app (which fails with the OS library) is already working perfectly in SQLiteManager. Any assistance would be most welcome. Thanks Mark Gilbert. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
Yves Goergen wrote: > I guess that doesn't work when I'm accessing the database through the > System.Data.SQLite interface in .NET? Fortunately your guess is wrong. ;-) System.Data.SQLite supports user defined collation sequences. See TestCases.cs of the source distribution for samples how to implement them. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 5/12/07, Yves Goergen <[EMAIL PROTECTED]> wrote: On 12.05.2007 17:33 CE(S)T, Igor Tandetnik wrote: > Yves Goergen <[EMAIL PROTECTED]> > wrote: >> I'm trying to get my table sorted the way how for example Windows >> Explorer or other file managers are sorting names. Most of all, >> accented characters should not be listed at the end of the list but >> near their non-accented character instead. I can only see the >> built-in collations BINARY and NOCASE. Is there something else? > > You need to install a custom collation. See sqlite3_create_collation[16] I guess that doesn't work when I'm accessing the database through the System.Data.SQLite interface in .NET? I did the same question about ordering, in my case, with Perl DBD::SQLite. As the answer was the same (and I understand why), i did the.. erm... stupid approach: . a column with the word/string or whatever . a column with the string where accented characters are not (accented). Use the first for presentation and query, the second for ordering. Hope this can help Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem with Unicode surrogates
"Jiri Hajek" <[EMAIL PROTECTED]> wrote: > > What I would propose is to be more robust in handling such incorrect Unicode > strings, so that application can e.g. insert really any string, not only > those that comply Unicode standards. > Doing this can potentially lead to security exploits in programs that use SQLite. If you want to handle ill-formed UTF8 strings, use a BLOB. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem with Unicode surrogates
Hello, what I describe below probably can't be called a SQLite bug, but in my opinion it could be fixed in SQLite anyway. The problem happens on Windows platform when using Sqlite3_Prepare16(), i.e. function accepting Unicode strings in UTF-16 encoding. When I for example perform a query: SELECT * FROM Table1 Where Field1='XY' where instead of X is 0xD800 Unicode (UTF-16) character, the query fails. It's apparently because 0xD800 is one of UTF-16 surrogates, i.e. the next character is expected to be something like 0xDD00, which isn't our case. What I would propose is to be more robust in handling such incorrect Unicode strings, so that application can e.g. insert really any string, not only those that comply Unicode standards. Thanks, Jiri
Re: [sqlite] Re: How to sort not binary?
On 12.05.2007 17:33 CE(S)T, Igor Tandetnik wrote: > Yves Goergen <[EMAIL PROTECTED]> > wrote: >> I'm trying to get my table sorted the way how for example Windows >> Explorer or other file managers are sorting names. Most of all, >> accented characters should not be listed at the end of the list but >> near their non-accented character instead. I can only see the >> built-in collations BINARY and NOCASE. Is there something else? > > You need to install a custom collation. See sqlite3_create_collation[16] I guess that doesn't work when I'm accessing the database through the System.Data.SQLite interface in .NET? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Column order in resultset
On Sat, 2007-05-12 at 08:02 -0700, Mike Johnston wrote: > When doing a "select * from foo" and using sqlite3_step() with > the associated sqlite3_column_xx funcs, is the order of the > columns in the resultset deterministic? > > What is the rule that defines what the order? > it seems to be the order of the create table statement... Right on both counts. It is deterministic, and they are in the same order as in the CREATE TABLE statement. (Zero is the leftmost column in the CREATE TABLE). A lot of people think it's bad practice to write "SELECT *" in application programs. If a column is added to the table later on, the extra column might surprise the application code. On the other hand, it could be just what you want. Dan. > > but I can't seem to find where it is written a "select *"must follow that > order. > > TIA > > > - > No need to miss a message. Get email on-the-go > with Yahoo! Mail for Mobile. Get started. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] default values at CREATE TABLE
"Frank Pool" <[EMAIL PROTECTED]> writes:
> I want to create a table with two colums:
>
> One ist the primary key (test_num)
> and the second column sholud contain the value of the primary key (maybe as
> a string) by default.
> How can I define this table in sql ?
>
> CREATE TABLE test_table ("test_num integer primary key AUTOINCREMENT NOT
> NULL, test_name varchar(256) DEFAULT ??? NOT NULL,")
>
> Any ideas ?
With the current version of sqlite3, you can do this.
CREATE TABLE test_table
(
test_num INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
test_name VARCHAR(256)
);
CREATE TRIGGER test_table_tr
AFTER INSERT ON test_table
BEGIN
UPDATE test_table
SET test_name = test_num
WHERE test_num = new.test_num;
END;
INSERT INTO test_table (test_num) VALUES (23);
INSERT INTO test_table (test_num) VALUES (42);
.mode line
SELECT * FROM test_table;
This yields:
test_num = 23
test_name = 23
test_num = 42
test_name = 42
Derrell
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] How to sort not binary?
Hi, I'm trying to get my table sorted the way how for example Windows Explorer or other file managers are sorting names. Most of all, accented characters should not be listed at the end of the list but near their non-accented character instead. I can only see the built-in collations BINARY and NOCASE. Is there something else? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Column order in resultset
When doing a "select * from foo" and using sqlite3_step() with the associated sqlite3_column_xx funcs, is the order of the columns in the resultset deterministic? What is the rule that defines what the order? it seems to be the order of the create table statement but I can't seem to find where it is written a "select *"must follow that order. TIA - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
Re: [sqlite] Check-ins [3987,3988] : Fix an obscure b-tree bug that applied to transient trees used for IN(...) expressions.
Joe Wilson <[EMAIL PROTECTED]> wrote: > Does anyone know if this bug was the result of a recent btree optimization, > or was it a longstanding issue? > I think it has been in the tree since version 3.0.0. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Check-ins [3987,3988] : Fix an obscure b-tree bug that applied to transient trees used for IN(...) expressions.
Does anyone know if this bug was the result of a recent btree optimization, or was it a longstanding issue? Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] chickens and egg problem: how to set page size before creating
On 5/12/07, Jens Miltner <[EMAIL PROTECTED]> wrote: Am 12.5.07 um 04:26 schrieb Andrew Finkenstadt: > It would appear that I need one "sqlite3* handle" in order to execute > statements such as "pragma page_size=32768;", but the act of calling > sqlite3_open(filename, ) creates the file, which prevents the > changing of the page size, as the sqlite master tables are created, > thereby > rubbing up against the proviso, " The page-size may only be set if the > database has not yet been created. ". > > Or am I missing something really obvious? > > Plainly I can compile sqlite3.c with SQLITE_DEFAULT_PAGE_SIZE equal > to my > desired page size, but surely that was not the way its use was > intended. Did you actually try it? I believe I tried it once and it would actually change the page size if I run the pragma command as the first thing after opening the sqlite connection... Actually I did... but I forgot that my .open() call checked for the existence of my necessary tables and created them, automatically. Problem fixed. --a
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/12/07, Alberto Simões <[EMAIL PROTECTED]> wrote: I am compiling *manually* sqlite3 in my Mac OS. Configured without tcl, and with prefix /usr/local. (--disable-tcl --prefix=/usr/local) I get this while compiling: ... gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Solved Removed those -L and -I for fink libraries. Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
Ok, I didn't explain correctly. This time I am not using fink :) Argh. Not being native english speaker sometimes sucks. Let start again. I am compiling *manually* sqlite3 in my Mac OS. Configured without tcl, and with prefix /usr/local. (--disable-tcl --prefix=/usr/local) I get this while compiling: ... gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Hints, please? Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/12/07, P Kishor <[EMAIL PROTECTED]> wrote: On 5/12/07, Alberto Simões <[EMAIL PROTECTED]> wrote: > On 5/11/07, P Kishor <[EMAIL PROTECTED]> wrote: > > > Just download the latest source code and compile a new sqlite3 with > > it. All will be well. > > I am trying to compile SQLite under Mac OS. Normally I use fink but it > includes an old version of SQLite. Don't use fink. I used fink once about a few years ago, and promptly deleted anything to do with it because it (as well as darwinports) was more trouble than worth it. SQLite (and many other software packages) compiles on my Mac (10.4.x) with nary a hiccup. Ok, I didn't explain correctly. This time I am not using fink :) > Configured without tcl, and with prefix /usr/local. > I get this while compiling: > > gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG > -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 > -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o > .libs/alter.o > In file included from ./src/alter.c:17: > ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' > ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union > ./src/sqliteInt.h:528: error: parse error before '}' token > ... > > > Hints, please? > Thanks > Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/12/07, Alberto Simões <[EMAIL PROTECTED]> wrote: On 5/11/07, P Kishor <[EMAIL PROTECTED]> wrote: > Just download the latest source code and compile a new sqlite3 with > it. All will be well. I am trying to compile SQLite under Mac OS. Normally I use fink but it includes an old version of SQLite. Don't use fink. I used fink once about a few years ago, and promptly deleted anything to do with it because it (as well as darwinports) was more trouble than worth it. SQLite (and many other software packages) compiles on my Mac (10.4.x) with nary a hiccup. Do as I suggested above and all will be well. Configured without tcl, and with prefix /usr/local. I get this while compiling: gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Hints, please? Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/11/07, P Kishor <[EMAIL PROTECTED]> wrote: Just download the latest source code and compile a new sqlite3 with it. All will be well. I am trying to compile SQLite under Mac OS. Normally I use fink but it includes an old version of SQLite. Configured without tcl, and with prefix /usr/local. I get this while compiling: gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Hints, please? Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] default values at CREATE TABLE
On Fri, May 11, 2007 at 20:43:46 +0200, Frank Pool wrote:
> I want to create a table with two colums:
>
> One ist the primary key (test_num)
> and the second column sholud contain the value of the primary key (maybe as
> a string) by default.
> How can I define this table in sql ?
>
> CREATE TABLE test_table ("test_num integer primary key AUTOINCREMENT NOT
> NULL, test_name varchar(256) DEFAULT ??? NOT NULL,")
You can't do this with SQL. DEFAULT value is a constant, it is not
re-evaluated on every insert. You may consider inserting the same
value into both test_num and test_name columns explicitly, since the
next value of AUTOINCREMENT column is predictable (and the largest
used value may be learned from sqlite_sequence table).
--
Tomash Brechko
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] chickens and egg problem: how to set page size before creating
Am 12.5.07 um 04:26 schrieb Andrew Finkenstadt: It would appear that I need one "sqlite3* handle" in order to execute statements such as "pragma page_size=32768;", but the act of calling sqlite3_open(filename, ) creates the file, which prevents the changing of the page size, as the sqlite master tables are created, thereby rubbing up against the proviso, " The page-size may only be set if the database has not yet been created. ". Or am I missing something really obvious? Plainly I can compile sqlite3.c with SQLITE_DEFAULT_PAGE_SIZE equal to my desired page size, but surely that was not the way its use was intended. Did you actually try it? I believe I tried it once and it would actually change the page size if I run the pragma command as the first thing after opening the sqlite connection... - To unsubscribe, send email to [EMAIL PROTECTED] -
