Re: [sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

2008-06-16 Thread Bharath Booshan L
> Well, if I epxlicitly run DELETE and then INSERT, would you also expect
> an UPDATE trigger to run? After all, "on the whole" the operation is an
> update.

Oh!! May be I was wrong in thinking that INSERT OR REPLACE would keep the
PRIMARY KEY as it is..

Am a stupid guy..

Thanks Igor for your valuable time,

Bharath


On 6/16/08 6:39 PM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:

> "Bharath Booshan L"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> IF INSERT OR REPLACE statement performs REPLACE operation, then again
>> insert_trigger is being invoked, which as per the documentation ( i.e
>> it
>> actually performs DELETE and INSERT) is fine.
>> 
>> Am I wrong in expecting that INSERT OR REPLACE should trigger
>> update_trigger
> 
> Yes. Didn't you just explain why it doesn't happen?
> 
>> when on the whole the operation performed is actually an UPDATE?
> 
> Well, if I epxlicitly run DELETE and then INSERT, would you also expect
> an UPDATE trigger to run? After all, "on the whole" the operation is an
> update.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
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.


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


[sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

2008-06-16 Thread Bharath Booshan L
Hello Sqlite users, experts,

 I am in a state of confusion and I request you to help me out please.

 
 Can "INSERT OR REPLACE" trigger if actual operation performed is REPLACE?

Say , For example, if I have 2 triggers on table T1, one trigger, say
insert_trigger, is set to trigger after INSERT, and another, say
update_trigger, set to trigger after UPDATE.

If INSERT OR REPLACE statement performs INSERT operation, the insert_trigger
is being invoked, which is fine.

IF INSERT OR REPLACE statement performs REPLACE operation, then again
insert_trigger is being invoked, which as per the documentation ( i.e it
actually performs DELETE and INSERT) is fine.

Am I wrong in expecting that INSERT OR REPLACE should trigger update_trigger
when on the whole the operation performed is actually an UPDATE?

I apologize, if my question is too silly.

Regards,

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.


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


Re: [sqlite] Binding Date value in Prepare/bind

2008-06-06 Thread Bharath Booshan L
Thanks Igor,

 >> I am asking this because julianday(date('1984-03-03')) =
>> julianday('1984-03-03'). Right?
> 
> Right. In fact, date('1984-03-03') is a no-op: the result of
> date('1984-03-03') is simply '1984-03-03'. Though I fail to see how this
> fact is relevant to your original question.

I had a doubt that date('1984-03-03') might take some reasonable amount of
time, where I am using this date funtion just for the sake of date('now').

I did not know julianday('now') is supported.

One more question,

 We have developed an App that was written for Version 3.1.3 available in
Mac OS 10.4.
 Now I have to move all the sqlite_exec to prepare/step/finalize methods,
but as per the documents, it says 'use of sqlite_prepare is not recommended'

Is sqlite_prepare supported in 3.1.3? If not, I seems there is no other
option than to move to 3.3.9 & above.
 


Thanks for you valuable time,

Bharath


On 6/6/08 5:33 PM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:

> "Bharath Booshan L"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> 1. Can I use sqlite3_prepare_v2 in Version 3.1.3?
> 
> No. It was introduced in v3.3.9
> 
>> 2. How do I bind date values using prepare/bind methods?
> 
> SQLite doesn't have dedicated date or time types. You may choose to
> store timestamps as strings, as julian dates (floating point numbers) or
> as Unix epoch timestamps (integers). See also
> 
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> 
>> Eg: INSERT INTO TABLE Info(Name,DOB)
>> values('XYZ',julianday('1984-03-03'));
>> 
>> For above example I can write a prepared statement as
>> 
>> INSERT INTO TABLE Info(Name,DOB) values(?,julianday(?))
> 
> Here, you are replacing two string literals with parameter placeholders.
> So you bind them as strings.
> 
>> But how do I write prepared statement if I want to insert date('now')
>> value into the table, like below query
>> 
>> INSERT INTO TABLE Info(Name,DOB) values('XYZ',julianday(date('now'))
> 
> julianday('now') would work just as well. So you can use your first
> statement, and bind 'now' for the parameter.
> 
>> I am asking this because julianday(date('1984-03-03')) =
>> julianday('1984-03-03'). Right?
> 
> Right. In fact, date('1984-03-03') is a no-op: the result of
> date('1984-03-03') is simply '1984-03-03'. Though I fail to see how this
> fact is relevant to your original question.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
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.


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


[sqlite] Binding Date value in Prepare/bind

2008-06-06 Thread Bharath Booshan L
Hello SQLite users,

 I have two questions, could anyone please help me out.

 1. Can I use sqlite3_prepare_v2 in Version 3.1.3?
 2. How do I bind date values using prepare/bind methods?

 Eg: INSERT INTO TABLE Info(Name,DOB)
values('XYZ',julianday('1984-03-03'));

For above example I can write a prepared statement as

 INSERT INTO TABLE Info(Name,DOB) values(?,julianday(?))
 
And bind 1 & 2 parameters to text values.

But how do I write prepared statement if I want to insert date('now') value
into the table, like below query
 
INSERT INTO TABLE Info(Name,DOB) values('XYZ',julianday(date('now'))



Will adding julianday(date(?)) cause any performance impact when I supply
the date itself as a value rather than date('now').


I am asking this because julianday(date('1984-03-03')) =
julianday('1984-03-03'). Right?


I am inserting some thousands of rows.

Thanks in advance for your valuable time,

--
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.


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


[sqlite] Attempt to read a write-only database error from BEGIN TRANSACTION

2008-03-27 Thread Bharath Booshan L
Hello list,

 I am using SQLite version 3.4.0 and facing some strange problem

 My database file is located in a Read-Write folder and I can perform
insert/update/select from SQLite Shell. But I encountered error number 8
"attempt to write a read-only database file".

The strange thing is, I get this error when "BEGIN EXCLUSIVE TRANSACTION"
statement is executed, where I expect a SQLITE_BUSY or SQLITE_OK values, and
hence all my inserts within this transaction are failing with the same
error.


Are there any other situations that causes this error to appear?

Waiting for your valuable inputs.

Thanks & Regards,

Bharath

PS: There is only one single process accessing the database at a given time.



---
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.


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


Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

2008-03-18 Thread Bharath Booshan L

> Do you test for SQLITE_BUSY, when you perform BEGIN IMMEDIATE ?
> 

Yes, I do.

> Yes a process that is reading will continue to read. Once it completes if
> anothre process is waiting to write then the additional read locks will not be
> granted. This is to prevent writer starvation.
> 
> How do you know that the data for which you are querying is actually loaded? I
> don't think you can since you invoke a load process after the query.
> 

I query for updated data only after the  writer process is terminated.



---
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.


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


Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

2008-03-18 Thread Bharath Booshan L

> Where do the  and  come from?
> Where do the  and  come from?
> Where does all the data to create the new records come from? You say
> Process A only has a FilePath as input.



> Where do the  and  come from?
> 

All the Information written into the database is extracted from the file
itself. The Segments data is also available within the file itself. We
manually append these information when we save the file.

> What do you mean by the line that says "Info Values)"?
> 
It is just the information retrieved from file.



> 
> It seems strange to change the creation date for an update. I would
> think this should only happen when the record is created in step d.

Yes. Can't imagine how did I do this silly thing.





> Where is this "set of files" coming from? The query in step a returns
> results for a single file only. Process A takes a FilePath parameter,
> the same as used in the where clause in query above I would assume.




>> "SELECT * FROM Segments,File WHERE File.FileID =  Segments.FileID AND
>> File.FullPath = ;
>> 
> 
> This will return the primary key for each segment associated with the
> file. With that you can get any other info about the segment that may be
> needed.
Yes, that is what I need. I require all the information matching the given
file.




>> 
>> C) Repeat Step a)
>> 
> 
> This is where you say you are having problems, correct? Are you saying
> that you are not getting the same segments for the file FileA after
> running process A with a parameter value of FileA?
> 
> This is unexpected since you say Process A only updates existing rows if
> the file FileA exists. It only adds new rows if FileA doesn't exist. But
> if FileA doesn't exist when you run Process A then there would have been
> nothing to return from the query in step a above, so any new rows would
> be expected. If the file exists, then you update some column values, but
> not the values that are used to select the results in the query in step
> a above, so the results should be the same.
> 
> Is there more stuff happening elsewhere that you haven't described? Are
> the file and/or segment rows ever deleted?
> 
Yes, the segments rows which are not updated are also deleted. This happens
only when I do some large inserts/updates continuously i.e add/update file
info and request for the updated as well as old data. I think there is some
concurrency issues at my side. Now I have to re-visit to in what order are
these inserts & selects are performed actually.

> Process A knows it will write later, so the reserved lock at step a lets
> others continue to read until it gets to step c or step d. The advantage
> of this is that it prevents some other process form executing a delete
> between your steps a and c for example. If the file existed when your
> process A did its existence check as step a, but didn't exist when it
> got to step c, there would be problems (not the problems you are seeing
> though). It is probably a good idea to acquire the lock (i.e. start the
> immediate transaction) before you start reading the database though.

Just I tried to have an EXCLUSIVE lock in Process A just to ensure that
writer process doesn't have to wait for reader process, and from that time
onwards I am not getting this issue, atleast till now. But I cannot sign it
off, treating this as the required change. Will look exactly what is
happening.

Since I am seeing this problem after upgrading it to 3.4.0, I thought its
better to ask the list whether there were any known issues regarding
database corrupt.


Thanks for all your inputs,

Will feedback what exactly is the case after I fix it, so that another
person will get to know if he encounter with similar issue.




--

Bharath

On 3/18/08 12:59 AM, "Dennis Cote" <[EMAIL PROTECTED]> wrote:






---
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.


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


Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

2008-03-17 Thread Bharath Booshan L
Ken,
 
Thanks for reply,

> I would start with APP A to determine after processing that the data that you
> asked to be loaded is actually loaded. If it does not, then look into app A.
> 
I think I need to add some more information here.

App B will be in running state, and whenever a write operation needs to be
performed, it invokes App B.


> If app A data load succeeds (and is correct in that all data loaded).
> Run app B. If this errors out you more than likely have a query related
> problem.

Query is absolutely fine. A simple select on the table after the database
table corrupt does not show some rows, which is expected to be there.

I will post the simple schema and the actual SQL statements I am using so
that might be helpful.
--

Bharath


On 3/17/08 8:16 PM, "Ken" <[EMAIL PROTECTED]> wrote:



> 
> HTH,
> Ken



---
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.


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


Re: [sqlite] mail forwarding loop?

2008-03-17 Thread Bharath Booshan L

Same here

On 3/14/08 7:17 PM, "P Kishor" <[EMAIL PROTECTED]> wrote:

> I have now twice gotten the following message. What gives?
> 
> 
> This is the mail system at host sqlite.org.
> 
> I'm sorry to have to inform you that your message could not
> be delivered to one or more recipients. It's attached below.
> 
> For further assistance, please send mail to postmaster.
> 
> If you do so, please include this problem report. You can
> delete your own text from the attached returned message.
> 
>   The mail system
> 
> : mail forwarding loop for sqlite-users@sqlite.org
> 
> Final-Recipient: rfc822; sqlite-users@sqlite.org
> Original-Recipient: rfc822;sqlite-users@sqlite.org
> Action: failed
> Status: 5.4.6
> Diagnostic-Code: X-Postfix; mail forwarding loop for sqlite-users@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
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.


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


[sqlite] Database Table corrupt in SQLite v 3.4.0

2008-03-17 Thread Bharath Booshan L


Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the step D, in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.



---
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.


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


[sqlite] Database Table corrupt in SQLite v 3.4.0

2008-03-17 Thread Bharath Booshan L

Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the step D, in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.



---
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.


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


[sqlite] Database Table corrupt in SQLite v 3.4.0

2008-03-17 Thread Bharath Booshan L
Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the last step in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.





---
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.


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


Re: [sqlite] Rowid After Sorting

2008-03-14 Thread Bharath Booshan L

> But I need my rowid to be chaged as follows.
> 
>  
> 
> Rowid   Id  Name
> 
>  
> 
> 1 4  aaa
> 
> 2 3  bbb
> 
> 3 2  xxx
> 
> 4   1  zzz

If you are looking Rowid to contain serial numbers every time then you can
query for only Id and Name and then while extracting the query result you
can append your own serial numbers as part of post processing the query
result.

HTH

Bharath



On 3/14/08 11:32 AM, "Mahalakshmi.m" <[EMAIL PROTECTED]>
wrote:

> 
> 
> Rowid   Id  Name
> 
>  
> 
> 4 4  aaa
> 
> 3 3  bbb
> 
> 2 2  xxx
> 
> 1   1  zzz
> 
>  
> 
> But I need my rowid to be chaged as follows.
> 
>  
> 
> Rowid   Id  Name
> 
>  
> 
> 1 4  aaa
> 
> 2 3  bbb
> 
> 3 2  xxx
> 
> 4   1  zzz
> 
>  
> 
> I tried with Views but its rowid is not changed.
> 
>  
> 
> But by creating one new table like
> 
>  "create table Temp as select Name from Mytable order by Name;"
> 
> gives the desired result as above.
> 
>  
> 
> Its taking more time for this.
> 
> So I there any other way I can do the same without creating table because in
> My table I am having many
> 
>  fields and each time I will create and drop the table for each fields.
> 
>  
> 
> Can anyone please help to solve this.
> 
>  
> 
> Thanks & Regards,
> 
> Mahalakshmi.M



---
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.


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


Re: [sqlite] FW: Query to Find number of distinct records

2008-02-27 Thread Bharath Booshan L
> support for count(distinct) has been added in 3.2.6
> your version is certainly < 3.2.6 (sqlite -version)
Your are right. My version is 3.1.3


Thanks a lot for all your inputs folks :-)

--
Bharath

On 2/27/08 4:31 PM, "Cyril SCETBON" <[EMAIL PROTECTED]> wrote:

> support for count(distinct) has been added in 3.2.6
> your version is certainly < 3.2.6 (sqlite -version)
> 
> Bharath Booshan L wrote:
>> ------ Forwarded Message
>> From: Bharath Booshan L <[EMAIL PROTECTED]>
>> Date: Wed, 27 Feb 2008 14:32:14 +0530
>> To: Eugene Wee <[EMAIL PROTECTED]>
>> Conversation: [sqlite] Query to Find number of distinct records
>> Subject: Re: [sqlite] Query to Find number of distinct records
>> 
>> I was off for lunch
>> 
>>> The thing is, it should work. What is the error message?
>> 
>> sqlite> select count(DISTINCT Name) as nameCount from TableA;
>> SQL error: near "DISTINCT": syntax error
>> 
>> 
>> On 2/27/08 1:24 PM, "Eugene Wee" <[EMAIL PROTECTED]> wrote:
>> 
>>> Hi,
>>> 
>>> Bharath Booshan L wrote:
>>>> Yeah!! I got it right this time.
>>>> 
>>>> Select count(*) from ( select DISTINCT Name from TableA);
>>>> 
>>>> 
>>>> But what's not getting into my mind is the difference b/w the following two
>>>> queries:
>>>> 
>>>> Select count( Name) from TableA  -- works fine
>>>> 
>>>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
>>> The thing is, it should work. What is the error message?
>>> 
>>> Regards,
>>> Eugene Wee
>>> 
>> 
>> -- End of Forwarded Message
>> 
>> 
>> 
>> ---
>> 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.
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 



---
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.


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


[sqlite] FW: Query to Find number of distinct records

2008-02-27 Thread Bharath Booshan L

-- Forwarded Message
From: Bharath Booshan L <[EMAIL PROTECTED]>
Date: Wed, 27 Feb 2008 14:32:14 +0530
To: Eugene Wee <[EMAIL PROTECTED]>
Conversation: [sqlite] Query to Find number of distinct records
Subject: Re: [sqlite] Query to Find number of distinct records

I was off for lunch

> The thing is, it should work. What is the error message?

sqlite> select count(DISTINCT Name) as nameCount from TableA;
SQL error: near "DISTINCT": syntax error


On 2/27/08 1:24 PM, "Eugene Wee" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> Bharath Booshan L wrote:
>> Yeah!! I got it right this time.
>> 
>> Select count(*) from ( select DISTINCT Name from TableA);
>> 
>> 
>> But what's not getting into my mind is the difference b/w the following two
>> queries:
>> 
>> Select count( Name) from TableA  -- works fine
>> 
>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
> 
> The thing is, it should work. What is the error message?
> 
> Regards,
> Eugene Wee
> 

-- End of Forwarded Message



---
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.


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


Re: [sqlite] Query to Find number of distinct records

2008-02-26 Thread Bharath Booshan L
Yeah!! I got it right this time.

Select count(*) from ( select DISTINCT Name from TableA);


But what's not getting into my mind is the difference b/w the following two
queries:

Select count( Name) from TableA  -- works fine

Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?


On 2/27/08 12:34 PM, "Bharath Booshan L" <[EMAIL PROTECTED]>
wrote:

> Hello All,
> 
>  This might be simple question, but am not getting the SQL query right for
> my problem.
> 
> 
> For eg, consider following table
> 
> NonUniqueNo  Name
> -
> 23 A
> 23 B
> 24 C
> 25 A
> 23 E
> 
> 
> How can I find the number of people for which an entry has been recorded in
> my table?
> 
> The answer I am expecting is 4 ( i.e. A,B,C,E)
> 
> Using query "select count(DISTINCT Name) from TableA" results in an error.
> 
> Could anyone please post a SQL query for my problem?
> 
> Thanks in advance,
> 
> 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.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
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.


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


Re: [sqlite] Query to Find number of distinct records

2008-02-26 Thread Bharath Booshan L
Thanks for your quick response,

> select count(DISTINCT Name) nameCount from TableA

This is not working :(. I am getting the same error.

--
Bharath



On 2/27/08 12:38 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:

> Try
> select count(DISTINCT Name) nameCount from TableA
> 
> Shibu Narayanan 
> Consultant, PrimeSourcing Division, Investment Banking Group
> Tel.Office: 91-80-2208-6270 or 91-80-6659-6270
> e-mail: [EMAIL PROTECTED]
> The answer is 42.
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Bharath Booshan L
> Sent: Wednesday, February 27, 2008 12:35 PM
> To: Discussion of SQLite Database
> Subject: [sqlite] Query to Find number of distinct records
> 
> Hello All,
> 
>  This might be simple question, but am not getting the SQL query right
> for
> my problem.
> 
> 
> For eg, consider following table
> 
> NonUniqueNo  Name
> -
> 23 A
> 23 B
> 24 C
> 25 A
> 23 E
> 
> 
> How can I find the number of people for which an entry has been recorded
> in
> my table?
> 
> The answer I am expecting is 4 ( i.e. A,B,C,E)
> 
> Using query "select count(DISTINCT Name) from TableA" results in an
> error.
> 
> Could anyone please post a SQL query for my problem?
> 
> Thanks in advance,
> 
> 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.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> DISCLAIMER:
> This message contains privileged and confidential information and is intended
> only for an individual named. If you are not the intended recipient, you
> should not disseminate, distribute, store, print, copy or deliver this
> message. Please notify the sender immediately by e-mail if you have received
> this e-mail by mistake and delete this e-mail from your system. E-mail
> transmission cannot be guaranteed to be secure or error-free as information
> could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or
> contain viruses. The sender, therefore,  does not accept liability for any
> errors or omissions in the contents of this message which arise as a result of
> e-mail transmission. If verification is required, please request a hard-copy
> version.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
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.


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


[sqlite] Query to Find number of distinct records

2008-02-26 Thread Bharath Booshan L
Hello All,

 This might be simple question, but am not getting the SQL query right for
my problem.


For eg, consider following table

NonUniqueNo  Name
-
23 A
23 B
24 C
25 A
23 E


How can I find the number of people for which an entry has been recorded in
my table?

The answer I am expecting is 4 ( i.e. A,B,C,E)

Using query "select count(DISTINCT Name) from TableA" results in an error.

Could anyone please post a SQL query for my problem?

Thanks in advance,

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.


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


Re: [sqlite] Getting the no of rows using count(*)

2008-01-30 Thread Bharath Booshan L
Hello Kirrthana,

> should i use prepare and step in this case and where the result of the query
> will be stored on executing my c code.

  retValue = sqlite3_prepare( dataBaseConnection ,sqlQuery,-1, ,0);

if( retValue != SQLITE_BUSY && (retValue = sqlite3_step( ppStmt ) ==
SQLITE_ROW )
{
   numOfRows  =sqlite3_column_int64(ppStmt ,0);

}
  
sqlite3_finalize( ppStmt );


This should do it

--
Bharath

On 1/31/08 11:00 AM, "kirrthana M" <[EMAIL PROTECTED]> wrote:

> Hi all,
> 
> Im using the query Select count(*) from table to get the no of rows in the
> table in my c code.
> In the command line it will print the no of rows ,but in my c code how can i
> get the result,
> should i use prepare and step in this case and where the result of the query
> will be stored on executing my c code.
> 
> Regards
> Kirrthana
> 
> The information contained in this electronic message and any attachments to
> this message are intended for the exclusive use of the addressee(s) and may
> contain proprietary, confidential or privileged information. If you are not
> the intended recipient, you should not disseminate, distribute or copy this
> e-mail. Please notify the sender immediately and destroy all copies of this
> message and any attachments contained in it.
> 
> Contact your Administrator for further information.
> 



---
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? ( Indexes usage issue)

2008-01-29 Thread 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?

2008-01-28 Thread Bharath Booshan L
Hello experts,

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

--
Bharath


On 1/30/08 10:30 AM, "Bharath Booshan L" <[EMAIL PROTECTED]>
wrote:

> Thanks for the inputs experts,
> 
> I am using SQLite 3.4.0 on Mac OS X Leapord,  and using regexp in my query
> reports an error
> 
> SQL error: no such function: regexp
> 
> I tried to use the query using GLOB and another query which separates
> FilePath into Dirpath and FileName and uses LIKE comparison.
> 
> Both seems to be working fine.
> 
> Is this the optimized query one could achieve for this problem? Is there any
> other way which I could retrieve results much faster than the two queries I
> have mentioned above?
> 
> Thanks & Regards,
> 
> Bharath
> 
> 
> On 1/29/08 12:11 AM, "James Dennett" <[EMAIL PROTECTED]>
> wrote:
> 
>>> -Original Message-
>>> From: Nicolas Williams [mailto:[EMAIL PROTECTED]
>>> Sent: Monday, January 28, 2008 10:35 AM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] How to specify regular expression in a query?
>>> 
>>> On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote:
>>>> I believe that this API would also ease implementations of Unicode
>>>> LIKE and GLOB.
>>> 
>>> That's what I was thinking of.  The Unicode extensions work by
>>> redefining the like, glob and regexp functions, and by adding
>>> collations.  But surely the existing user-defined functions interface
>>> does not allow for this sort of optimization.
>> 
>> Right, which is why this conversation is about extending that interface
>> :)
>> 
>>> Or did I miss something?
>> 
>> No, I think you're in "violent agreement".
>> 
>> -- James
>> 
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>> 
>> 
> 
> 
> 
> ---
> 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]
> -
> 
> 



---
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?

2008-01-28 Thread Bharath Booshan L
Thanks for the inputs experts,

I am using SQLite 3.4.0 on Mac OS X Leapord,  and using regexp in my query
reports an error

SQL error: no such function: regexp

I tried to use the query using GLOB and another query which separates
FilePath into Dirpath and FileName and uses LIKE comparison.

Both seems to be working fine.

Is this the optimized query one could achieve for this problem? Is there any
other way which I could retrieve results much faster than the two queries I
have mentioned above?

Thanks & Regards,

Bharath


On 1/29/08 12:11 AM, "James Dennett" <[EMAIL PROTECTED]>
wrote:

>> -Original Message-
>> From: Nicolas Williams [mailto:[EMAIL PROTECTED]
>> Sent: Monday, January 28, 2008 10:35 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] How to specify regular expression in a query?
>> 
>> On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote:
>>> I believe that this API would also ease implementations of Unicode
>>> LIKE and GLOB.
>> 
>> That's what I was thinking of.  The Unicode extensions work by
>> redefining the like, glob and regexp functions, and by adding
>> collations.  But surely the existing user-defined functions interface
>> does not allow for this sort of optimization.
> 
> Right, which is why this conversation is about extending that interface
> :)
> 
>> Or did I miss something?
> 
> No, I think you're in "violent agreement".
> 
> -- James
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 



---
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?

2008-01-27 Thread Bharath Booshan L
Thanks kjh,

Sorry, there are some missing details which I did not explain.

1. Search should be something like MyMovie*.*
2. I have to search for filename under specified volume. i.e. Search
MyMovie*.* in volume '/Volumes/Backup'
3. I already have a Movie table with AutoIncrement primary key.

> 
> create table MovieFiles
> (
>ID  integer,
>FilePath  varchar(255), -- contains `dirname  MovieFile`
>FileName  varchar(255)  -- contains `basename MovieFile`
> ) ;


> create index MovieFilesFileName on MovieFiles( FileName ) ;

With above details in consideration, if I have to split filepath into
dirname and basename and index FileName, then would the following query uses
the indexes properly?

Select * from MyMovies where FilePath LIKE '/Volumes/Backup/%' AND FileName
LIKE 'MyMovie%';
 
Or something like this using regular expression and FilePath column contains
both dirname and basename

Select * from MyMovies where FilePath regexp
'/Volumes/Backup/*/MyMovie*(\.[^\./]+)*$';

Using regexp in query prevents use of index I suppose, Isn't it?



--Bharath



On 1/25/08 6:15 PM, "Konrad J Hambrick" <[EMAIL PROTECTED]> wrote:

> 
> 
> On 01/26/2008 02:40 AM, Bharath Booshan L wrote:
>> 
>> Hello list,
>> 
>>  I have to perform a search something similar to this
>> 
>>   ID FilePath
>>1  /Volumes/Backup/MyMovies/MyMovie.mp4
>>2  /Volumes/Backup/MyMovies/Hello.mp4
>>3  /Volumes/Tiger/MyMovie.mov
>> 
>> 
>> Search for file name MyMovie should retrieve
>> 
>>ID FilePath
>>1  /Volumes/Backup/MyMovies/MyMovie.mp4
>>3  /Volumes/Tiger/MyMovie.mov
>> 
>> 
>> To simplify, I am searching for a file name from a collection of absolute
>> file paths.
>> 
>> How can I achieve this in SQLite?
>> 
>> Is there anyways I can use regular expression in a query to perform string
>> matching.
> 
> Bharath --
> 
> I suppose if say, your Movie Table is called 'MyMovies'
> then, I you could:
> 
>  -- the following assumes all files have a 3-char extent
> 
> select ID,
>FilePath
>   from MyMovies
>  where FilePath glob '*/MyMovie.???'
> 
> Or (more portably but less precisely) ...
> 
> select ID,
>FilePath
>   from MyMovies
>  where FilePath like '%/MyMovie.%'
> 
> This could be slow on a large Table.
> 
> Have you thought about splitting the Path (man dirname)
> from the FileName (man basename) ?
> 
> Something like:
> 
> create table MovieFiles
> (
>ID  integer,
>FilePath  varchar(255), -- contains `dirname  MovieFile`
>FileName  varchar(255)  -- contains `basename MovieFile`
> ) ;
> 
> Might make for simpler queries and faster too if you
> add an index on the FileName Column.
> 
> create index MovieFilesFileName on MovieFiles( FileName ) ;
> 
> HTH
> 
> -- kjh
> 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 



---
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?

2008-01-27 Thread Bharath Booshan L
Thanks Ralf,

>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$';

Will this query use index, if we had one, on filepath?

Regards,

Bharath

On 1/25/08 5:22 PM, "Ralf Junker" <[EMAIL PROTECTED]> wrote:

> Hello Bharath Booshan L,
> 
> yes, with SQLiteSpy you can do this:
> 
> drop table if exists t;
> create table t (id integer primary key, filepath text);
> insert into t values (1, '/Volumes/Backup/MyMovies/MyMovie.mp4');
> insert into t values (2, '/Volumes/Backup/MyMovies/Hello.mp4');
> insert into t values (3, '/Volumes/Tiger/MyMovie.mov');
> select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$';
> 
> Ralf
> 
>> I have to perform a search something similar to this
>> 
>>  ID FilePath
>>   1  /Volumes/Backup/MyMovies/MyMovie.mp4
>>   2  /Volumes/Backup/MyMovies/Hello.mp4
>>   3  /Volumes/Tiger/MyMovie.mov
>> 
>> 
>> Search for file name MyMovie should retrieve
>>
>>   ID FilePath
>>   1  /Volumes/Backup/MyMovies/MyMovie.mp4
>>   3  /Volumes/Tiger/MyMovie.mov
>> 
>> 
>> To simplify, I am searching for a file name from a collection of absolute
>> file paths.
>> 
>> How can I achieve this in SQLite?
>> 
>> Is there anyways I can use regular expression in a query to perform string
>> matching.
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 



---
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]
-



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

2008-01-25 Thread Bharath Booshan L
Hello list,

 I have to perform a search something similar to this

  ID FilePath
   1  /Volumes/Backup/MyMovies/MyMovie.mp4
   2  /Volumes/Backup/MyMovies/Hello.mp4
   3  /Volumes/Tiger/MyMovie.mov


Search for file name MyMovie should retrieve

   ID FilePath
   1  /Volumes/Backup/MyMovies/MyMovie.mp4
   3  /Volumes/Tiger/MyMovie.mov


To simplify, I am searching for a file name from a collection of absolute
file paths.

How can I achieve this in SQLite?

Is there anyways I can use regular expression in a query to perform string
matching.


Thanks in advance,

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]
-



[sqlite] SQLite 3.5.* source code location?

2007-10-21 Thread Bharath Booshan L
Hello,

 Could anybody tell where can I get the latest sqlite3 source code for Mac
OS 10.4?

Regards,

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] sqlite3_exec function error:database is locked

2007-10-14 Thread Bharath Booshan L
Are you using any BLOBs in your table?. More often than not it will be your
query which will have direct impact on the performance. I suggest you to
check that appropriate columns have been indexed and are used in right
manner as specified in the SQLite documentation or mailing list with subject
" How does SQLite choose the index?"


Cheers,

Bharath

On 10/13/07 9:34 PM, "varunkumar" <[EMAIL PROTECTED]> wrote:

> 
>i have 40 columns table in my database. now my database size is 23MB .
> untill now my database has 78752 rows(records). when i am query database  it
> is taking 10 seconds of  time . my database performance is degrading.
>  i want to improve my database performance what should i do to improve
> performance. this report generating time depends on what parameters . is it
> depend on number of rows and size of the database
> 
>
> 
> 
> Richard Klein-3 wrote:
>> 
>>> varunkumar <[EMAIL PROTECTED]> wrote:
 so two different processes cannot  access the database at a time
 
>>> 
>>> One process cannot access the database at the same instant
>>> in time that another process is modifying the database.
>>> 
>>> --
>>> D. Richard Hipp <[EMAIL PROTECTED]>
>> 
>> To clarify further:  Process A and process B can both have
>> the same database *open* at the same time.
>> 
>> However, if process A tries to access (read or write) the
>> database while process B is modifying (writing) it, then
>> process A will get a SQLITE_BUSY error code returned to it.
>> 
>> Process A should be prepared to handle this SQLITE_BUSY
>> error.  Typically he will want to sleep for a little while,
>> and then try again.
>> 
>> - Richard Klein
>> 
>> 
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>> 



---
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] select COUNT (DISTINCT column1, column2) from table?

2007-09-25 Thread Bharath Booshan L
Hi Phani,

Hope this answers your Query.

SQLite version 3.1.3
Enter ".help" for instructions
sqlite> create table m( mNo integer, year integer, month integer );
sqlite> insert into m values (1, 2007, 9);
sqlite> insert into m values (2, 2006, 5);
sqlite> insert into m values (3, 2006, 5);
sqlite> insert into m values (4, 2004, 4);
sqlite> SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM m group by year,month);
3
sqlite> 

Regards,

Bharath


On 9/25/07 3:59 PM, "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:

> Hi Simon,
> 
> Assume you have a following data:
> matchNo, year, month
> 34 2007 9
> 
> 27 2006 5
> 
> 26 2006 5
> 
> 24  2005 4
> 
> For the above data my answer should be 3, since there are three unique
> combination of year, month {(2007, 9), (2006, 5), (2005, 4)}. Here I
> need to find the number of distinct combinations of year, month not the
> count for a particular year, month.
> 
> Regards,
> Phani
> 
> 
> -Original Message-
> From: Simon Davies [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 25, 2007 3:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] select COUNT (DISTINCT column1, column2) from
> table?
> 
> On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> .
> .
>> Assume you have a following data:
>> 
>> matchNo, year, month
>> 
>> 34 2007 9
>> 
>> 27 2006 5
>> 
>> 26 2006 5
>> 
>> Now distinct year, month will return
>> 
>> 2007, 9
>> 
>> 2006, 5
>> 
>> Is there a way by which I can count (distinct year, month)
> combinations?
>> For this example answer should be 2.
>> 
>> Regards,
>> 
>> Phani
> 
> Hi Phani,
> 
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table m( mNo integer, year integer, month integer );
> sqlite>
> sqlite> insert into m values (1, 2006, 11 );
> sqlite> insert into m values (2, 2007, 5 );
> sqlite> insert into m values (3, 2007, 5 );
> sqlite>
> sqlite> select count(*), year, month from m group by year,month;
> 1|2006|11
> 2|2007|5
> sqlite>
> 
> Rgds,
> Simon
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 



---
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] An example for "progress" method?

2007-09-17 Thread Bharath Booshan L
I have used the prepare, step, finalize methods in order to implement the
progress callback and it works fine.

However, I would like to know couple of things.

a. Whether the sqlite3_exec function is better in terms of performance to
receive the callback?

b. Will sqlite3_interrupt function stop the query execution immediately upon
request or does it continue the current instruction and then stop or any
thing as such?

Please advise,

Thanks in advance,

Bharath


On 9/18/07 5:13 AM, "John Stanton" <[EMAIL PROTECTED]> wrote:

> Zbigniew Baniewski wrote:
>> An interesting method is "progress":
>> 
>>   "The progress callback can be used to display the status of a lengthy query
>>or to process GUI events during a lengthy query."
>> 
>> But I'm not quite sure presently, how it could look like in practice? To make
>> a "progress bar" I've got to know a maximal value of the records (table
>> rows) involved in a query BEFORE it'll be caused to run.
>> 
>> Perhaps again I've missed some simple thing(?) - but currently I don't know,
>> how can it be done in a simple way. The methods "changes" and "total_changes"
>> are giving the number or rows involved AFTER the query is done. So, how can
>> I count it all before, to make a nice looking progress bar? Just by making
>> additional, earlier query like "SELECT COUNT(*) FROM xyz WHERE "?
>> 
>> Perhaps someone could show me some example?
> You cannot know the result of a query until it is finished, and when it
> is finished there is no need for a progress bar.
> 
> The obvious solution is to not use a bar but to have another form of
> progress indicator.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 



---
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] An example for "progress" method?

2007-09-17 Thread Bharath Booshan L



On 9/18/07 5:00 AM, "Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote:

> An interesting method is "progress":
> 
>   "The progress callback can be used to display the status of a lengthy query
>or to process GUI events during a lengthy query."
> 
> But I'm not quite sure presently, how it could look like in practice? To make
> a "progress bar" I've got to know a maximal value of the records (table
> rows) involved in a query BEFORE it'll be caused to run.
> 
> Perhaps again I've missed some simple thing(?) - but currently I don't know,
> how can it be done in a simple way. The methods "changes" and "total_changes"
> are giving the number or rows involved AFTER the query is done. So, how can
> I count it all before, to make a nice looking progress bar? Just by making
> additional, earlier query like "SELECT COUNT(*) FROM xyz WHERE "?


Yes, this could be one way to find the max value, but is feasible as long as
we can afford the time taken to execute the query.

One more way I would suggest is that, you could predict average number
records your query would retrieve in all circumstances. Just a suggestion
:-). It works well for me.




---
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]
-



[sqlite] _sqlite3StrCmp not found

2007-08-30 Thread Bharath Booshan L
Hello All,

 I am using sqlite 3.1.3 version which is provided in Mac OS 10.4 OS for my
application. We are upgrading our application to support Leapord. Leapord
includes sqlite 3.4.0 version and while loading dynamic library
"libsqlite3.dylib" it returns me following error

Symbol not found : _sqlite3StrCmp
Referenced from: /usr/lib/sqlite/libtclsqlite3.dylib
Expected in: dynamic lookup

I am using the following API's in my App.

sqlite3_get_table()
sqlite3_prepare()
sqlite3_mprintf()
sqlite3_step()
sqlite3_free()

What am I supposed to do to upgrade to 3.4.0 from 3.1.3.

Regards,

Bharath Booshan L

PS: It works fine in sqlite 3.3.1.




---
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] Threading issues in SQLite

2007-08-10 Thread Bharath Booshan L
Hi Dan,

 Thanks for your suggestion.

> If you have any choice in the matter, don't use threads. Run all 5
> queries from the same thread. You can either run them sequentially,

Running sequentially might trouble me as I have to update more sets of
information at a cause of single event.
 
> or sqlite3_prepare() all 5 and then round-robin calls to sqlite3_step().
I have look into this. However, could you please let me know the overhead in
managing multiple connections? Does it create any impact on my application's
performance? 

On 8/10/07 11:41 AM, "Dan Kennedy" <[EMAIL PROTECTED]> wrote:

> On Fri, 2007-08-10 at 11:25 +0530, Bharath Booshan L wrote:
>> Hello All,
>> 
>>  I am using SQLite in one my application and I require to retrieve around
>> 4-5 sets of information simultaneously. I have opted to run the queries in
>> separate threads which seems obvious choice here.
>> 
>>  However, I came to know from one of the documents in sqlite.org that single
>> connection cannot be used simultaneously across threads.
>> 
>>  So I would like to hear any suggestions regarding the best wary to open &
>> manage multiple connections i.e.
>> 
>> a) Is it efficient to create new connections in separate thread each time
>> when the query needs to be executed? ( Here I am worried about the time
>> taken to open connection )
>> 
>> b) Is it efficient to create the number of connections required, in a single
>> thread and use it in different threads? ( although a connection will not be
>> used simultaneously and this will eliminate the effort to create different
>> connections but we need to ensure that a connection is used simultaneously
>> in different threads)
>> 
>> Looking forward to your suggestions,

 



---
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]
-



[sqlite] Threading issues in SQLite

2007-08-09 Thread Bharath Booshan L
Hello All,

 I am using SQLite in one my application and I require to retrieve around
4-5 sets of information simultaneously. I have opted to run the queries in
separate threads which seems obvious choice here.

 However, I came to know from one of the documents in sqlite.org that single
connection cannot be used simultaneously across threads.

 So I would like to hear any suggestions regarding the best wary to open &
manage multiple connections i.e.

a) Is it efficient to create new connections in separate thread each time
when the query needs to be executed? ( Here I am worried about the time
taken to open connection )

b) Is it efficient to create the number of connections required, in a single
thread and use it in different threads? ( although a connection will not be
used simultaneously and this will eliminate the effort to create different
connections but we need to ensure that a connection is used simultaneously
in different threads)

Looking forward to your suggestions,

Bharath Booshan L   



---
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] a c++ newbie question

2007-08-06 Thread Bharath Booshan L
Hi Stev,

 Prepared statements are best option, however try sqlite3_mprintf() with
'%q' as format specifier which escapes every '\' character.

Find more info in http://sqlite.org/capi3ref.html.

Bharath Booshan L.


On 8/6/07 11:50 AM, "Stephen Sutherland" <[EMAIL PROTECTED]>
wrote:

> Hi ;
>
>   I am trying to treat a string before passing it through my SQL statement
> into the database.
>
>   I know that a  single apostrophe will break the SQL statement.
>   So I have to replace them all to double apostrophes.
>   Question #1: 
>   What may I ask is the c or C++ code to accomplish that ?
>   Initially I was using this simple scheme.
>
>   string str2("stephen's test . Bob's test");
>
>   if (  str2.find("'"!= string::npos)
> {
>  str2.replace(str2.find('"), 1, "''");
>   } 
>   I know this doens't test for multiple single apostrophes.
>
>   But are there any other characters that will break the SQL statement ?
>
>   Has anyone  created a nice algorithm?
> I actually have a situation where the user creates an XML file and the
> contents of the XML file gets dumped in the database. So there is opportunity
> for a hacker to create an XML file which has some SQL statements in it like '
> DELETE TABLE X ;
>
>   So any thoughts or existing code would be great.
>
>   Thanks 
>
>   Stev 
> 
>
> -
> Luggage? GPS? Comic books?
> Check out fitting  gifts for grads at Yahoo! Search.



---
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]
-



[sqlite] Indexes usage on Foreign Key

2007-08-02 Thread Bharath Booshan L
I am using sqlite v3.1.3 on Mac OS 10.4

Consider the following schema

Create table Library(LibraryID INTEGER PRIMARY KEY AUTOINCRMENT, LibraryName
TEXT);
Create table Book(LibraryID INTEGER REFERENCES Library, BookID TEXT PRIMARY
KEY, BookName TEXT);

Now I believe Library(LibraryID) is automatically indexed.

But when I use Book(LibrayID) field in one of my Query as below I believe it
is not indexed as the query execution takes more amount of time.

eg: SELECT * FROM Book WHERE LibraryID IN ( ... );

But when I index Book(LibraryID) it is quick enough. But is it meaningful to
index Foreign Key ?

Will the Foreign key use the same index as of its counterpart in original
table or should it be separately indexed ?

I am not able to see the Query plan through "Explain Query plan" &
".explain" command. " Explain Query plan" returns a syntax error and
.explain does nothing :(

Thanks in advance,

Bharath Booshan L.
   



---
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] strategy adding indexes

2007-07-30 Thread Bharath Booshan L
Hi Tom,

I have one more query regarding usage of indexes.



> 2. From left to right in the same order as your index. So if you
> create index MyIndex on MyTable ( Column1, Column2, Column3 ), then
> you must test them in the same order, eg: where Column1 = Value1 and
> Column2 = Value2 or Column3 = Value3. If you miss a column in the
> sequence or place one out of order, the index won't be used from that
> point in the test onwards.


If I use only one of the Column i.e. Column1, Column2 or Column3 in a query
Would it still use the index MyIndex or that we need to have the 3 Columns
(in sequence) in the test in order to use the MyIndex?

Regards,

Bharath Booshan L. 



---
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]
-



[sqlite] Is SQLite Scalable to handle large data?

2007-07-27 Thread Bharath Booshan L
Hello,

 I have been using SQLIte database for one of my application (stand -alone).
It contains  12 Tables and, on average, there can be around 60 - 100
thousand of rows in one table and rest contains around 2000 rows of
information which is linked to former one.

 It is providing acceptable performance. Now we need to make this
application as Client-Server type and hence the Scalability, Concurrency
issues arises which were not at-most importance till now.

Is SQLite feasible enough to handle huge data assuring the concurrency?

Note: The application performs more SELECT operations, with limited INSERT &
UPDATE operations. So the Query results retrieval time should be minimal.

Please provide some suggestions. I will provide more information if needed.

Regards,

Bharath Booshan L.  



---
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]
-



[sqlite] How to create in-memory database?

2007-07-24 Thread Bharath Booshan L
Hi ,


 How do I create an in-memory database? Is there any API related to it?

 Please advise.

Regards,

Bharath Booshan L.



---
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] Prepared Statement (select * from x where y in ());

2007-07-18 Thread Bharath Booshan L



> 1)How can we prepare a SQliteStatement for the following type of select,
>   select * from tablename where column in (?);
>   ?: we don;t know the length of this string.


Pass any value less than zero as 3rd parameter to sqlite3_prepare function,
in which case, characters up to the first null terminator will be
considered.

Cheers,

Bharath Booshan L.



---
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] Finding record position

2007-07-17 Thread Bharath Booshan L



On 7/17/07 5:13 PM, "Colin Manning" <[EMAIL PROTECTED]> wrote:


> 
> Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as
> my user pages up and down the list, or drags a scrollbar.
> 
> Next, assume the user wants to jump to a specific record in the list, or to
> (say) the first entry for a specific name.
> 
> How do you do this with sqlite, without forcing the the app to fetch every
> record and then do a manual comparison in the app?

SELECT MIN(rowid) FROM pb ORDER  WHERE name LIKE 'John Smith'

Will return first index of the row matching the given condition.

Hope this answers your question.


Cheers,

Bharath Booshan L.  



---
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]
-



[sqlite] Can ORDER-BY perform Case-Insensitive comparison?

2007-07-16 Thread Bharath Booshan L
Hi All,

 Eg:

   People table  
  
  | EmpID | Name|
 21 Rajesh
 22 raj
 23 Rakesh 
 24 ramesh 

 Consider following query

 SELECT  Name 
 FROM People
 ORDER BY Name ASC;

 will result as 

 Rajesh
 Rakesh
 raj
 ramesh

 But where as I need the output to be sorted alphabetically irrespective of
its CASE as shown below.

ramesh
raj
Rajesh
Rakesh


Please anyone suggest me regarding this.

Thanks in advance,

Bharath Booshan L





---
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] SQLite Query Assistance

2007-07-11 Thread Bharath Booshan L



On 7/11/07 8:55 PM, "Joe Wilson" <[EMAIL PROTECTED]> wrote:

> The problem is schema design.
> Just use a single task table with another key column for the task number.
> Queries on N task tables with UNIONS are killing your performance.
> 

But how do I associate processes to different tasks having some Task_Name
'XYZ' stored under Task with some Task_Number.

Thanks for any assistance,

Bharath Booshan L



---
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]
-



[sqlite] SQLite Query Assistance

2007-07-11 Thread Bharath Booshan L

Hi All,

 Following are the table structures I am using in one of my application

 System_table

 | Name | SystemID PRIMARY KEY |

 Process_table
 | Name | SytemID REFERENCES  System_table | ProcessID  PRIMARY KEY |
ParentProcessID | IsParentProcess |

Each process represented by UNIQUE ProcessID may or may not have parent
process, which 
is inturn a process. Only one level of hierarchy is allowed i.e. A Parent
Process cannot does not have any parent and its ParentProcessID contains
NULL value.

IsParentProcess is either 1 if the associated process is parent process or 0
(Zero) if it is child process.

 
 Task1_table
 | Name | TaskID |

 Task1_info_table
 
 | TaskID REFERENCES Task1_info_table | ProcessID REFERENCES Process_table |
IsParentProcess |


Task2_table
 | Name | TaskID |

 Task2_info_table
 
 | TaskID REFERENCES Task2_info_table | ProcessID REFERENCES Process_table |
IsParentProcess |

.
.
.
.

TaskN_table
 | Name | TaskID |

 TaskN_info_table
 
 | TaskID REFERENCES TaskN_info_table | ProcessID REFERENCES Process_table |
IsParentProcess |



I have a UserInterface in my application where the user can select Tasks
under Task1, Task2,  TaskN tables and following information can be
extracted from the User Interface

³Task1 Name CONTAINS  ŒXYZ¹ AND Taks2 Name CONTAINS ŒABC¹²
³Any Task Name DOES NOT CONTAIN ŒABC Œ²


I need create a query which retrieves all the Child ProcessID, with
following conditions, running under system identified by SystemID

1. Retrieve all ProcessIDs whose parent process is running which is not
currently associated with any Task with Name which contains 'XYZ'

2. Retrieve all the ProcessIDs, who do not have a parent process, and is not
currently associated with any Task with Name which contains ŒXYZ¹

The Result should contain ProcessIDs resulted from 1 & 2 and should not
contain any duplicates.



Query which I have implemented is


SELECT Process.ProcessID
FROM Process_table Process, System_table System
WHERE System.SystemID IN ( 1,2,3,4..,N) AND IsParentProcess = 0 AND
Process.ProcessID IN (

SELECT * FROM (
 SELECT Process.ProcessID
 FROM Process_table ChildProcess, ( SELECT ProcessID FROM Task1_table,
Task1_Info_table WHERE Task1_table.TaskID = Task1_Info_table.TaskID AND
Task1_table.Name NOT LIKE Œ%XYZ%¹ AND Task1_Info_table.IsParentProcess = 1)
AS ParentProcess
WHERE ChildProcess.ParentProcessID = ParentProcess.ProcessID

UNION

SELECT ProcessID FROM Task1_table, Task1_Info_table WHERE Task1_table.TaskID
= Task1_Info_table.TaskID AND Task1_table.Name NOT LIKE Œ%XYZ%¹ AND
Task1_Info_table.IsParentProcess = 0 )

INTERSECT

SELECT * FROM (
 SELECT Process.ProcessID
 FROM Process_table ChildProcess, ( SELECT ProcessID FROM Task2_table,
Task2_Info_table WHERE Task2_table.TaskID = Task2_Info_table.TaskID AND
Task2_table.Name NOT LIKE Œ%XYZ%¹ AND Task2_Info_table.IsParentProcess = 1)
AS ParentProcess
WHERE ChildProcess.ParentProcessID = ParentProcess.ProcessID

UNION

SELECT ProcessID FROM Task2_table, Task2_Info_table WHERE Task2_table.TaskID
= Task2_Info_table.TaskID AND Task2_table.Name NOT LIKE Œ%XYZ%¹ AND
Task2_Info_table.IsParentProcess = 0 )

.
.
.


INTERSECT

SELECT * FROM (
 SELECT Process.ProcessID
 FROM Process_table ChildProcess, ( SELECT ProcessID FROM TaskN_table,
TaskN_Info_table WHERE TaskN_table.TaskID = TaskN_Info_table.TaskID AND
TaskN_table.Name NOT LIKE Œ%XYZ%¹ AND Task1_Info_table.IsParentProcess = 1)
AS ParentProcess
WHERE ChildProcess.ParentProcessID = ParentProcess.ProcessID

UNION

SELECT ProcessID FROM TaskN_table, TaskN_Info_table WHERE TaskN_table.TaskID
= TaskN_Info_table.TaskID AND TaskN_table.Name NOT LIKE Œ%XYZ%¹ AND
TaskN_Info_table.IsParentProcess = 0 )

)


Note: Following Information are known only at run-time depending upon users
input
  *  SystemID of the System under which processes needs to be
searched
  *  Task table to be Queried


The above Query takes around 7 sec approx to retrieve around 8000 ProcessIDs
which is un-acceptable.

Please provide me any inputs on how can I optimize this query.


Thanks for any assistance

Bharath Booshan L 
 



---
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.