RE: [sqlite] basic table level stuff
Hi- that is a great example and I will definitely use it. However, the constraint you listed goes at the end. I am also wondering about the constraint in the column-def like column-def ::= name [type] [[CONSTRAINT name] column-constraint]* CREATE [TEMP | TEMPORARY] TABLE table-name ( column-def [, column-def]* [, constraint]* ) I am trying to get this last piece because I want to be able to delete,modify, and add tables and columns in my gui program in addition to being able to execute sql statements (which I already have). There are a dozen of these programs out there now but I wish to make my own. thanks for helping me dave, marvin -Original Message- From: David M. Cook [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 8:11 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] basic table level stuff On Tue, Jan 20, 2004 at 05:15:15PM -0500, jim wrote: > if they already have these constraints at the column level, why do > they need them a second time as in ... name [type] [[CONSTRAINT name] > column-constraint]* Because you may want a composite primary key or set of unique columns. A simple example is CREATE TABLE person ( person_id INTEGER PRIMARY KEY lastname TEXT, firstname TEXT, UNIQUE (lastname, firstname) ); Dave Cook - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Transaction -> OE_Ignore
How do I set a transaction (inserting items) to ignore doubles in keys? As the default behaviour now is to abort/rollback... I've also been looking where the sqlite_vm - structure is defined, but I did not found anything... Is that normal? Or is it just a pointer to a struct with nothing inside? G00fy, (aka KaReL, aka Steven) Main Webpage : http://komma.cjb.net CQ Database : http://lid.fragland.net/ ICQ #: 35217584 MSN : [EMAIL PROTECTED]
Re: [sqlite] basic table level stuff
On Tue, Jan 20, 2004 at 05:15:15PM -0500, jim wrote: > if they already have these constraints at the column level, > why do they need them a second time as in ... > name [type] [[CONSTRAINT name] column-constraint]* Because you may want a composite primary key or set of unique columns. A simple example is CREATE TABLE person ( person_id INTEGER PRIMARY KEY lastname TEXT, firstname TEXT, UNIQUE (lastname, firstname) ); Dave Cook - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] basic table level stuff
Hi- One thing that is puzzling me is the contstraint below that isnt a column constraint. constraint ::=PRIMARY KEY ( name [, name]* ) [ conflict-clause ]| UNIQUE ( name [, name]* ) [ conflict-clause ] | CHECK ( expr ) [ conflict-clause ] if they already have these constraints at the column level, why do they need them a second time as in ... name [type] [[CONSTRAINT name] column-constraint]* the column constraint seems more straight forward column-constraint ::=NOT NULL [ conflict-clause ] | PRIMARY KEY [sort-order] [ conflict-clause ] | UNIQUE [ conflict-clause ] | CHECK ( expr ) [ conflict-clause ] | DEFAULT value please let me know. thanks, marvin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Apostrophe's and Quotes
Grant Robinson <[EMAIL PROTECTED]> writes: > I've been googling for a solution to this and haven't been able to find one. > Here is my problem: > > I have a table named questions that looks like this: > id|question|answer > 1|Cat''s name|fluffy > 2|Dog''s name|buffy > > When I execute the following query: > > select * from questions where question = 'Cat''s name'; > > I get no results. However, when I execute the query > with double quotes: > > select * from questions where question = "Cat''s name"; > > I get 1 row returned. Can someone explain this discrepancy to me? I'll first define my Terminology: ' = single quote (which is also known as an apostrophe) " = double quote Your data actually contains TWO single quotes. If you use signal quotes for surrounding the string in your query, then two single quotes is the way of escaping one single quote within the string. If you use double quotes for surrounding the string in your query, then single quotes need not be escaped. Similarly, if you use double quotes for surrounding the string in your query, then a double quote may be escaped by inserting two double quotes. You've done one of these to get the data into the first row of your table, thus actually inserting two single quotes in the question field: INSERT INTO questions (id, question, answer) VALUES (1, "Cat''s name", "fluffy"); INSERT INTO questions (id, question, answer) VALUES (1, 'Cats name', 'fluffy'); You can query your table using single quotes to surround the string, by doing: SELECT * from questions where question = 'Cats name'; Derrell - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Apostrophe's and Quotes
I've been googling for a solution to this and haven't been able to find one. Here is my problem: I have a table named questions that looks like this: id|question|answer 1|Cat''s name|fluffy 2|Dog''s name|buffy When I execute the following query: select * from questions where question = 'Cat''s name'; I get no results. However, when I execute the query with double quotes: select * from questions where question = "Cat''s name"; I get 1 row returned. Can someone explain this discrepancy to me? Thanks, Grant Robinson - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Setting sqlite-shell preferences
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 20, 2004 11:48 AM > To: SQLite-Users (E-mail) > Subject: Re: [sqlite] Setting sqlite-shell preferences > > It is suppose to execute commands in ~/.sqliterc prior to giving you > a prompt. This works, mostly. But I think there are some bugs > outstanding against this. The code there definately needs to be > cleaned up. Cool, that seems to work. Thanks. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Setting sqlite-shell preferences
Williams, Ken wrote: Hi, Every time I use the sqlite shell, I'd like to be in column mode, and with headers turned on. Is there a way to autoexecute ".header on; .mode column" every time I start up? I think "sqlite -header -column" will do it, but is there any way to put this in a stored preferences file or something? It is suppose to execute commands in ~/.sqliterc prior to giving you a prompt. This works, mostly. But I think there are some bugs outstanding against this. The code there definately needs to be cleaned up. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Setting sqlite-shell preferences
Hi, Every time I use the sqlite shell, I'd like to be in column mode, and with headers turned on. Is there a way to autoexecute ".header on; .mode column" every time I start up? I think "sqlite -header -column" will do it, but is there any way to put this in a stored preferences file or something? -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Tables involved in queries
Hello, It would be very useful to be able to determine which tables are being used in a given query. The background is that we are performing a local caching of queries by caching the underlying tables from an Oracle database in a local SQLite database. Given this query: SELECTA.col_1, B.col_2 FROM A , ( SELECT C.col_1 col_1, C.col_2 col_2 FROM C, D WHERE C.col_2 = D.col_2) B WHEREA.col_1 = B.col_1 [ Note that I have to alias the column C.col_1 in the subquery to get this to work! ] ...I would like to be able to determine that the tables A, C, D are used as the underlying tables. I had thought of using the EXPLAIN command to examine the vm and check all the OpenRead, OpenTemp, etc... opcodes. Is there an easier way? Regards, Steve
Re: [sqlite] [Repost] Implementing Full Text Search
On 20 Jan 2004, at 7:25, George Ionescu wrote: The question is this: since most of RDBMS implement full text search, shouldn't this be a feature sqlite could support ? SQLite is "lite" on purpose. Most RDBMS also support data types, as a counter example. Matt. This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Changing default_temp_store
Hello sqlite users, Hello dr. Hipp, I'm trying to speed up sqlite and I would like to change the default_temp_store so that all temporary tables are created in memory, instead of file. I was wondering how can I do that. I've tried changing the TEMP_STORE macro to 3 - Always use memory, with no success: when doing PRAGMA default_temp_store, I always get 0. I would also like to know if there are any other things involved with changing this? Is the data integrity affected in any way? ( This might sound like a silly question, but I have to ask it ) Best regards, George Ionescu
[sqlite] [Repost] Implementing Full Text Search
Hello dear SQLite users, Hello dr. Hipp, I'm reposting this because I didn't actually got an answer, just oppinions, which are fine as long as they are constructive... The question is this: since most of RDBMS implement full text search, shouldn't this be a feature sqlite could support ? Brad wrote: > My regex patch should do that > SELECT * FROM Categories WHERE CategoryDescription RLIKE 'Beverages" and > CategoryDescription NOT RLIKE 'Whiskey'; > You can find a working test version here > http://www.wasp.net.au/~brad/sqlite-110104-snapshot-bkc1.tgzCould I have a 'Windows' > version?I would like to test your regex, however I need to now if what you're doing > is a full scan of the column...D. Morel wrote: > Regarding Full Text Search syntax, I think CONTAINS statement would be ok > (as implemented in other database engines). > So: > SELECT * FROM Categories > WHERE CONTAINS(CategoryDescription, 'Beverages') > would return all the rows where column CategoryDescription contains the > word 'Beverages' :-) >> doesn't SELECT * FROM Categories WHERE CategoryDescription LIKE '%Beverages%'; >> do just that ? It will, but it won't be a true 'Full Text Search', since the LIKE operator will not benefit from indexes, so a full column scan will be necessary to perform such a query. I'm quite familiar with the anayomy of a full text search engine (parsers / tokenizers / stop words etc.), but since this was a suggestion from Dr. Hipp, I was really wondering what did he have in mind: just a 'smarter' LIKE operator (which returns ranking), or something more... Best regards, George Ionescu
