[sqlite] How to make correct transaction use only SQL?

[Alexander Batyrshin]

For example i have this tabe:

CREATE TABLE t1 (
  id int unique ON CONFLICT ROLLBACK,
  val char
);

And I have to execute this sql file:

BEGIN TRANSACTION;
INSERT INTO t1 (id, val) VALUES(1, 'val1');
INSERT INTO t1 (id, val) VALUES(2, 'val2');
INSERT INTO t1 (id, val) VALUES(3, 'val3');
INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT
INSERT INTO t1 (id, val) VALUES(4, 'val5');
COMMIT;

If we execute this sql file, only INSERT before CONFLICT case will be
rollback-ed, but last one still will be executed and remains in
database...

cat test.sql | sqlite3 test.db
SQL error near line 11: column id is not unique
SQL error near line 13: cannot commit - no transaction is active
$ sqlite3 test.db
SQLite version 3.4.0
sqlite> select * from t1;
4|val5


I what that on conflict _whole_ transaction will ROLLBACK and state of
database will be exactly like at moment of execution "BEGIN
TRANSACTION". How it is possible using only SQL?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Default ON CONFLICT clause

[Alexander Batyrshin]

Yep. It is last line in this document. Somehow i missed it :)

On Jan 30, 2008 3:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 1/29/08, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
> >  Hello all,
> > What is default ON CONFLICT clause?
>
> "The algorithm specified in the OR clause of a INSERT or UPDATE
> overrides any algorithm specified in a CREATE TABLE. If no algorithm
> is specified anywhere, the ABORT algorithm is used."
>
> 
>
> > --
> > Alexander Batyrshin aka bash
> > bash = Biomechanica Artificial Sabotage Humanoid
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Default ON CONFLICT clause

[Igor Tandetnik]

Alexander Batyrshin <[EMAIL PROTECTED]>
wrote: 

What is default ON CONFLICT clause?


ABORT

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Default ON CONFLICT clause

[P Kishor]

On 1/29/08, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>  Hello all,
> What is default ON CONFLICT clause?

"The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE. If no algorithm
is specified anywhere, the ABORT algorithm is used."



> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Default ON CONFLICT clause

[Alexander Batyrshin]

 Hello all,
What is default ON CONFLICT clause?
-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Callback issue - using pointer as argument

[Igor Tandetnik]

David Hautbois <[EMAIL PROTECTED]> wrote:


char * get_config_value (sqlite3 * db, char * config_name) {

   TabResult res;

   rc= sqlite3_exec( db, query, exec_get_config_value_cb , ,
);


Strings passed to the callback are valid only within the callback. As 
soon as the callback returns, the memory may be deallocated or reused 
for other purposes. If the callback wants to keep some strings around 
beyond a single call, it should allocate its own memory and copy the 
value over.


Better still, stop using sqlite3_exec and switch over to sqlite3_prepare 
/ sqlite3_step / sqlite3_finalize interface.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Callback issue - using pointer as argument

[David Hautbois]

Hi
I spent 6 hours on this issue and I don't understand why I get a wrong 
value.


My database content :
sqlite> select * from config;
1|version|1
2|ftpserver|A
3|ftp_remotedir|
4|ftp_login|
5|ftp_password|

A simple query :
sqlite> SELECT config_value FROM config WHERE config_name="version";
1

Ok, all right.

Now I try to execute the same query with this code :

*
typedef struct TabResult {
 char *value;
} TabResult;

/***/
/* Get a config 
value  
*/

/***/

char * get_config_value (sqlite3 * db, char * config_name) {

   gchar * query;
   int rc;
   char *zErrMsg = 0;
   char *config_value=0;
   TabResult res;
   
   query = g_strdup_printf("SELECT config_value FROM config WHERE 
config_name='version'\n");

   printf (query);
   rc= sqlite3_exec( db, query, exec_get_config_value_cb , , );
 
   if (rc!=SQLITE_OK) {

   fprintf(stderr, "SQL error: %s\n", zErrMsg);
   sqlite3_free(zErrMsg);
   return NULL;
   }
   printf ("gpstracer-cfg.c - get_config_value : %s : %s\n", 
config_name, (char *)res.value);

   return config_value;
}


/***/
/* Callback of the get_config_value 
query  */

/***/

static int exec_get_config_value_cb(void *result, int argc, char **argv, 
char **azColName) {


   int i;
   TabResult *p = (TabResult*)result;

   for(i=0; ivalue = argv[0];
   printf ("gpstracer-cfg.c - exec_get_config_value_cb : returned value 
: %s\n", argv[0]);

   printf ("%s,%s\n", p->value, argv[0]);
   return 0;
}
*

When I call the function :
get_config_value (sqlite3 * db, "version")

I get :
1: SELECT config_value FROM config WHERE config_name="version"
2: 0 : config_value = 1
3:
4: gpstracer-cfg.c - exec_get_config_value_cb : returned value : 1
5: 
1,1   
-> ok
6: gpstracer-cfg.c - get_config_value : version : 
ftp_password   ->nok


Line #4 : all right : version = 1
Line #5 : The returned value change to "ftp-password" !

I don't know why !

I use libsqlite3-0 3.4.1

Can some help me to understand this ?

Thanks.

David.

--
Web site : http://david.hautbois.free.fr
Tablet users map : http://david.hautbois.free.fr/maps/index.php



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How Does NOT NULL produce NULLs?

[Lee Crain]

No, I'm not performing Outer Joins. 

This problem occurs on an INSERT statement.

A QString object's pointer to memory is ZERO unless an assignment is made.


Performing: -> QString object = "";

solves the problem.

Lee





-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 29, 2008 12:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How Does NOT NULL produce NULLs?

"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I did expect SQLite to enforce the NOT NULL portion of the SQL
> creation statements, no matter what.

SQLite *does* enforce NOT NULL no matter what.  I think your
pointers are getting turned into NULLs someplace else, perhaps
somewhere in the QT layer.

A NULL can get inserted for NOT NULL columns for non-matching
rows of an OUTER JOIN.  Are you doing OUTER JOINs?  The NOT NULL
applies to the table, not to query results from the table.

Also, if you request a invalid column (the column number is too
large or too small, or the last call to sqlite3_step() did not
return SQLITe_ROW), then the SQLite interfaces will return a 
NULL pointer.

--
D. Richard Hipp <[EMAIL PROTECTED]>


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How Does NOT NULL produce NULLs?

[drh]

"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I did expect SQLite to enforce the NOT NULL portion of the SQL
> creation statements, no matter what.

SQLite *does* enforce NOT NULL no matter what.  I think your
pointers are getting turned into NULLs someplace else, perhaps
somewhere in the QT layer.

A NULL can get inserted for NOT NULL columns for non-matching
rows of an OUTER JOIN.  Are you doing OUTER JOINs?  The NOT NULL
applies to the table, not to query results from the table.

Also, if you request a invalid column (the column number is too
large or too small, or the last call to sqlite3_step() did not
return SQLITe_ROW), then the SQLite interfaces will return a 
NULL pointer.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How Does NOT NULL produce NULLs?

[Lee Crain]

A "bug" in "my" code is possible. 

We are using the QT suite and QString objects do not distinguish between
an uninitialized QString object (pointer == zero) and an empty string ("")
which I think is a flawed lack of distinction. In Lee Crain's Rules Of
Software Development Practices, NULL means nothing is known; empty means
empty. The 2 conditions are distinctly and unambiguously different.

However, I did expect SQLite to enforce the NOT NULL portion of the SQL
creation statements, no matter what. If it cannot for whatever reason,
then I will enforce it in my source code by:

if( 0 == QString.Length( ) )
{
// QString object's state is ambiguous; can be NULL or empty,
//  according to QT documentation.
QString = ""; // This line solves the problem.
}

Thanks for your responses,

Lee Crain

__


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 29, 2008 12:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How Does NOT NULL produce NULLs?

"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
> these SQL statements:
> 
> [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> 
> [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> 
> [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
> 
> __
> 
> Subsequent data insertions of empty strings produce the following data:
> 
> (null)|(null)|(null)  
> 

I am unable to replicate the problem.  Are you sure you don't
have a bug in *your* code?

--
D. Richard Hipp <[EMAIL PROTECTED]>


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How Does NOT NULL produce NULLs?

[Cory Nelson]

On Jan 29, 2008 11:01 AM,  <[EMAIL PROTECTED]> wrote:
> "Lee Crain" <[EMAIL PROTECTED]> wrote:
> > I've created a table with several fields, 3 of which are created using
> > these SQL statements:
> >
> > [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> >
> > [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> >
> > [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
> >
> > __
> >
> > Subsequent data insertions of empty strings produce the following data:
> >
> > (null)|(null)|(null)
> >
>
> I am unable to replicate the problem.  Are you sure you don't
> have a bug in *your* code?

It looks to me like he is passing a null pointer to printf.

-- 
Cory Nelson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How Does NOT NULL produce NULLs?

[Lee Crain]

Scott,

I'm not ignoring your post. I'm going to respond to DRH's post.

Thanks,

Lee

_

-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 29, 2008 11:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How Does NOT NULL produce NULLs?

That seems unlikely, since NULL wouldn't come out as the string (null)
in any case.  Most likely some higher layer is putting the literal
'(null)' in for you when you insert.  Please post a set of literal
input to sqlite3 which demonstrates the problem.

.nullvalue '[null]'
create table x (
  [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
  [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
  [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE
);
insert into x values ('', '', '');
insert into x (description) values ('x');

Outputs:
||
x||

Just what I'd expect.

create table y (
  [description] [varchar](255) COLLATE NOCASE,
  [keywords][varchar](255) COLLATE NOCASE,
  [metadata][varchar](255) COLLATE NOCASE
);
insert into y values ('', '', '');
insert into y (description) values ('x');
select * from y;

Outputs:
||
x|[null]|[null]

Again, just what I'd expect.

This under SQLite version 3.5.4.

-scott


On Tue, Jan 29, 2008 at 10:12 AM, Lee Crain <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
>  these SQL statements:
>
>  [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
>  [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
>  [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
>
>  __
>
>  Subsequent data insertions of empty strings produce the following data:
>
>  (null)|(null)|(null)
>
>  __
>
>  It is important in the application this data is associated with to NOT
>  have any NULL fields. To me, an empty string is not a NULL, only an
empty
>  string, an important distinction.
>
>  How can I prevent the insertion of NULLs into these fields and instead
>  replace them with empty strings?
>
>  Lee Crain
>
>
--
---
>  To unsubscribe, send email to [EMAIL PROTECTED]
>
--
---
>
>

--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How Does NOT NULL produce NULLs?

[drh]

"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
> these SQL statements:
> 
> [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> 
> [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> 
> [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
> 
> __
> 
> Subsequent data insertions of empty strings produce the following data:
> 
> (null)|(null)|(null)  
> 

I am unable to replicate the problem.  Are you sure you don't
have a bug in *your* code?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How Does NOT NULL produce NULLs?

[Scott Hess]

That seems unlikely, since NULL wouldn't come out as the string (null)
in any case.  Most likely some higher layer is putting the literal
'(null)' in for you when you insert.  Please post a set of literal
input to sqlite3 which demonstrates the problem.

.nullvalue '[null]'
create table x (
  [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
  [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
  [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE
);
insert into x values ('', '', '');
insert into x (description) values ('x');

Outputs:
||
x||

Just what I'd expect.

create table y (
  [description] [varchar](255) COLLATE NOCASE,
  [keywords][varchar](255) COLLATE NOCASE,
  [metadata][varchar](255) COLLATE NOCASE
);
insert into y values ('', '', '');
insert into y (description) values ('x');
select * from y;

Outputs:
||
x|[null]|[null]

Again, just what I'd expect.

This under SQLite version 3.5.4.

-scott


On Tue, Jan 29, 2008 at 10:12 AM, Lee Crain <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
>  these SQL statements:
>
>  [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
>  [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
>
>  [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
>
>  __
>
>  Subsequent data insertions of empty strings produce the following data:
>
>  (null)|(null)|(null)
>
>  __
>
>  It is important in the application this data is associated with to NOT
>  have any NULL fields. To me, an empty string is not a NULL, only an empty
>  string, an important distinction.
>
>  How can I prevent the insertion of NULLs into these fields and instead
>  replace them with empty strings?
>
>  Lee Crain
>
>  -
>  To unsubscribe, send email to [EMAIL PROTECTED]
>  -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How Does NOT NULL produce NULLs?

[Lee Crain]

I've created a table with several fields, 3 of which are created using
these SQL statements:

[description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,  

[keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,  

[metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);

__

Subsequent data insertions of empty strings produce the following data:

(null)|(null)|(null)

__

It is important in the application this data is associated with to NOT
have any NULL fields. To me, an empty string is not a NULL, only an empty
string, an important distinction. 

How can I prevent the insertion of NULLs into these fields and instead
replace them with empty strings?

Lee Crain

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] CREATE VIEW or CREATE TEMP TABLE

[Alexander Batyrshin]

Offtop: You are trying to make something like statistic for game?
-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify regular expression in a query? ( Indexes usage issue)

[Dennis Cote]

Bharath Booshan L wrote:

There is also a discussion of the REGEXP Function on that page and why your
app threw an error when you tried to invoke a REGEXP filter in your query.


I have tested that in sqlite3 command-line tool(v3.4.0), but no yield.



REGEXP syntax is supported by SQLite, but you have to supply an suitable 
regexp function for SQLite to call when needed. That function is not 
included in the standard distribution.




I have tried the following example to test the usage of index

Create table MyTable(FilePath TEXT PRIMARY KEY NULL);
Insert some appropriate values

SELECT MovieURL FROM  MyTable WHERE MovieURL = 'Some File Path';

Initially, I thought the Primary key in the table is automatically indexed,
but that doesn't seem to be the case;


Yes, the primary key field is automatically indexed.



So I created an index on FilePath

Create index indexFilePath on MyTable(FilePath);



You are just duplicating the automatic primary key index.



sqlite> explain query plan
   ...> SELECT MovieURL FROM MyTable WHERE FilePath =
'/Volumes/Users/Shared/';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1

Now what is this sqlite_autoindex_MyTable_1? Is it the index of implicit
rowid of MyTable?



It is the automatically generated index on the primary key.



In simple way, shouldn't the above query use the index indexFilePath as it
is FilePath is being compred with a constant?



SQLite finds the automatic primary key index first and your duplicate 
index does not provide a better means of accessing the data so it is 
never used.





Similarly,
sqlite> explain query plan
   ...> SELECT MovieURL FROM MyTable WHERE MovieURL =
'/Volumes/Users/Shared/%';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1

Again, the wild character is at the end, and therefore it should have used
index indexFilePath. Isn't it?



This is an equality comparison,not a LIKE call so the % is a normal 
character, not a wildcard. In either case, the automatic primary keyu 
index is being used for the same reason as the previous test case.


And one more 


sqlite> explain query plan
   ...> SELECT MovieURL FROM MyTable WHERE FilePath GLOB
'/Volumes/Users/Shared/%';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1



GLOB uses * and ? as wildcard characters, not %.

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] database disk image is malformed

[Salles, Joaquim Campos]

Hello,

 

Which condition can cause the error: database disk image is malformed.

 

Thanks for the help,

 

Joaquim

[sqlite] CREATE VIEW or CREATE TEMP TABLE

[P Kishor]

So, I have a series of SQL selects to do, making for a pretty
complicated process. The end result is to be inserted into a new
table.

I can create a VIEW of each step, SELECTing each subsequent result
from the preceding VIEW. Or, I can CREATE TEMP TABLE for each step.
Any pros and cons of one or the other?

-- 
Puneet Kishor

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify regular expression in a query? ( Indexes usage issue)

[Bharath Booshan L]

Thanks kjh for your valuable inputs,

> If you use US ASCII, there is a collation (COLLATE NOCASE)
> that could handle this for you.

I am using Unicode characters.


> There is also a discussion of the REGEXP Function on that page and why your
> app threw an error when you tried to invoke a REGEXP filter in your query.

I have tested that in sqlite3 command-line tool(v3.4.0), but no yield.


> CREATE TABLE t1
> (
>IDINTEGER,
>PathName  VARCHAR(255) COLLATE NOCASE, -- contains `dirname  MovieFile`
>FileName  VARCHAR(255) COLLATE NOCASE  -- contains `basename MovieFile`
> ) ;
> 
> In this case, COLLATE NOCASE makes both PathName and FileName filters case
> insensitive for the US ASCII character set.
No, The PathName and FileName has to be case sensitive.

I have tried the following example to test the usage of index

Create table MyTable(FilePath TEXT PRIMARY KEY NULL);
Insert some appropriate values
..
..
..

SELECT MovieURL FROM  MyTable WHERE MovieURL = 'Some File Path';

Initially, I thought the Primary key in the table is automatically indexed,
but that doesn't seem to be the case;

So I created an index on FilePath

Create index indexFilePath on MyTable(FilePath);



sqlite> explain query plan
   ...> SELECT MovieURL FROM MyTable WHERE FilePath =
'/Volumes/Users/Shared/';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1

Now what is this sqlite_autoindex_MyTable_1? Is it the index of implicit
rowid of MyTable?


I have gone through the sqlite arechive of Indexes and its usage, but it
made my knowledge on indexes even more complex.


In simple way, shouldn't the above query use the index indexFilePath as it
is FilePath is being compred with a constant?


Similarly,
sqlite> explain query plan
   ...> SELECT MovieURL FROM MyTable WHERE MovieURL =
'/Volumes/Users/Shared/%';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1

Again, the wild character is at the end, and therefore it should have used
index indexFilePath. Isn't it?

And one more 

sqlite> explain query plan
   ...> SELECT MovieURL FROM MyTable WHERE FilePath GLOB
'/Volumes/Users/Shared/%';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1



> I am not sure what your application is ultimately going to do.

My App indexes certain movie files and custom annotations related to that
movie file and stores that in a database. And at some point in time, App
queries for Movie files under specific search directory along with
constraints on these custom annotation. It is similar to that of a Spotlight
Search in Mac OS X.


> You'll have to decide for yourself -- a lot depends on the number of records
> in
> the table -- tens of records won't need an index, hundreds of records might
> work
> better with INDEXes, thousands probably will most likely run better with
> INDEXes).

More number of SELECT s are performed by the application and hence the right
columns has to be indexed for better performance.


Did I explained well?


Any inputs will be greatly appreciated


--
Bharath



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify regular expression in a query?

[Konrad J Hambrick]

On 01/29/2008 11:16 PM, Bharath Booshan L wrote:


 How can I instruct GLOB function to perform case-insensitive search similar
to LIKE. Can I?


Bharath --

A lot depends on the character set you choose to use.

If you use US ASCII, there is a collation (COLLATE NOCASE)
that could handle this for you.

See:  http://www.sqlite.org/lang_expr.html

There is also a discussion of the REGEXP Function on that page and why your
app threw an error when you tried to invoke a REGEXP filter in your query.

Back to your original table (call it t1), one way to do case insensitive filters
would be to add COLLATE NOCASE:

   CREATE TABLE t1
   (
  IDINTEGER,
  PathName  VARCHAR(255) COLLATE NOCASE, -- contains `dirname  MovieFile`
  FileName  VARCHAR(255) COLLATE NOCASE  -- contains `basename MovieFile`
   ) ;

In this case, COLLATE NOCASE makes both PathName and FileName filters case
insensitive for the US ASCII character set.

I am not sure what your application is ultimately going to do.

Adding INDEXes to a table is always a balancing act between performance on 
INSERTs
versus SELECTs.

You'll have to decide for yourself -- a lot depends on the number of records in
the table -- tens of records won't need an index, hundreds of records might work
better with INDEXes, thousands probably will most likely run better with 
INDEXes).

To answer your question from yesterday about using indexes on that table, if you
add the following two INDEXes (note that the table name is t1), you could query
via INDEX on either PathName or FileName:

   create index t1PathName on t1( PathName ) ;
   create index t1FileName on t1( FileName ) ;

The COLLATE NOCASE expressions in the CREATE TABLE statement will allow case
insensitive searches.

For example, to find all the movies in a PathName (directory):

   SELECT ID   as "ID",
  PathName
   || '/'
   || FileName as "FilePath"
 FROM t1
WHERE PathName LIKE '/volumes/backup/mymov%'
ORDER BY FileName ;

The query should use the t1PathName INDEX because the % wildcard is at the
end of the constant '/volumes/backup/mymov%'

To find all the movies starting with 'mymov' (case insensitive):

   SELECT ID   as "ID",
  PathName
   || '/'
   || FileName as "FilePath"
 FROM t1
WHERE FileName GLOB 'mymov*.???'
ORDER BY "FilePath" ;

That query should use the t1FileName INDEX because the '*.???' wildcard is at
the end of the constant 'mymov*.???'

HTH -- have fun !

-- kjh




-
To unsubscribe, send email to [EMAIL PROTECTED]
-