[sqlite] DB access privilege problem...

[forforum]

Hi,
I am using sqlite DB and am creating my DB in USB drive(this is my
application requirement), My problem is that. when am creating my DB as a
admin in 1 system and taking the same USB in 2nd system who is having
limited rights, then 2nd system user is getting Read only Database
exception, 
So i want to ask is there any access rights for Sqlite DB, so that where
ever i will carry my DB it should get update regardless of access rights..
 

Thanks in advance
 
-- 
View this message in context: 
http://old.nabble.com/DB-access-privilege-problem...-tp30006788p30006788.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] File Locking in WinCE

[Afriza N. Arief]

On Thu, Oct 14, 2010 at 2:41 PM, Afriza N. Arief wrote:

> ... sometimes my WinCE application has a problem where the changes made in
> one instance a of sqlite3 database not reflected in the other instance b of
> the same database file even though they exist in the same process.
>
> Thread A:
> initialize sqlite3 instance a
> initialize sqlite3 instance b
> Thread B:
> modify via instance a
> Thread A:
> read via instance b
> Thread B:
> modify via instance a
> Thread A:
> close sqlite3 instance b
> initialize sqlite3 instance b
> read via instance b // the changes from a is sometimes not reflected
>
> I fixed the above problem by using instance a for both Thread A and Thread
> B since they are in the same process and hence eliminating instance b.
>


After exploring more on SQLite documentations, especially
http://sqlite.org/lang_transaction.html and
http://www.sqlite.org/lockingv3.html#transaction_control , it looks like the
problem was caused by pending queries in the database connection. Since
there were some pending/unfinished queries, the implicit transaction in one
db connections keeps the lock(s) and the transaction becomes long lived.
Other connections to the db may not see the changes since they also have
pending queries and thus have long-lived transactions as well.

Here is roughly how my code was:

bool SomeClass::ProductWithTypeExists(const char* type)
{
sqlite3_reset();
sqlite3_bind();
if (sqlite3_step() == SQLITE_ROW)
return true;
return false;
}

and now it becomes

bool SomeClass::ProductWithTypeExists(const char* type)
{
sqlite3_reset();
sqlite3_bind();
bool result = sqlite3_step() == SQLITE_ROW
sqlite3_reset(); // always reset() statements after use to mark it as
finished and allow implicit transaction to end.
return result;
}

Regards,

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

[sqlite] SQL code vs application code (was: Insert or Update, was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

[BareFeetWare]

On 20/10/2010, at 1:23 AM, Pavel Ivanov wrote:

>> 2. I suggest that you're better off doing the logic entirely in SQL, rather 
>> than application code, for the sake of portability, data integrity and speed.
> 
> I'd say this is a very bad advice for the developer using SQLite.

Thanks for your input. I thought someone might bite ;-)

> First of all "insert or ignore" and "insert or replace" are not portable SQL 
> structures, so we can forget about portability.

I should clarify that by "portable", I meant that the SQLite file itself is 
portable. If all the logic is contained in the SQL itself, then the SQLite file 
can be moved from a dedicated app to a generic GUI app to an iPhone app to a 
PHP driven website. If the logic is in the application code (instead of the 
SQL), then that logic would have to be recreated in Objective-C, PHP and 
whatever other application code is used. In the case of moving the SQLite file 
between SQLite GUI apps, the user has no control over the code, so the logic 
would no longer work. Since SQLite is the one constant of all situations I 
describe, then it seems efficient to me to put as much of the logic into it as 
possible.

Even if you're looking at portability across SQL flavors, the same principle 
holds. We're much better off writing as close to standard SQL as possible and 
adapting only what the new flavor might require/omit, than leaving logic, data 
integrity and data optimization in the hands of an external coding environment. 
You will also pick up for free whatever optimizations the new SQL flavor 
provides, as long as the SQL includes the logic and constraints, otherwise the 
RDMS can't see it.

> Secondly SQL and application are completely separated creatures only in case 
> of some server-side RDBMS. In case of SQLite they are the same
> application and if you add complexity to SQL, you add complexity to your 
> application and it happens very often that complexity on SQL side
> is much worse in performance than some additional application bits on the 
> other side. Just test different approaches and see it for
> yourself.

I guess this is where our experience or approach differs. I've heard others 
vouch for the same perspective that you suggest. In my case, however, I have 
moved SQLite databases from fledgling creation using the command line tool and 
a rudimentary GUI built in AppleScript, some PERL, to PHP driven web sites, a 
desktop application using an SQLite wrapper written in C and most recently 
iPad/iPhone apps written in Objective C. Within any one of those particular 
environments, I've used various wrappers of frameworks to talk to the SQLite 
databases. There are probably a dozen combinations of application environments 
used. Thankfully, since I designed the logic of the databases in SQLite itself, 
I was able to move the SQLite database file from one environment to the next 
with no change to the SQL itself and without any customisation of the 
application code to cater for a particular database. I can to this day open my 
iPad SQLite database using a desktop command line tool, for example, and know 
that any inserts, updates etc I perform will be internally checked and 
consistent since the logic is within the SQL. This is very handy for debugging 
as well.

In the case of the OP (original poster), he was looking through "several GUI 
editors for SQLite". He is not writing his own application code. So in his case 
also, there is a clear line between application code and SQLite code.

I would further argue that even if you have full control of the application 
code and somehow magically know that you'll never need to move it over to a new 
platform (ie portability is not a concern), that you're still better off 
keeping the logic in the SQLite code itself. Queries and aggregates and tests 
are generally much faster when executed as a single transaction, than when 
handed back and forward or in a loop between application code and SQL, 
reinjecting the results of one query back into some other SQL. I know that from 
a procedural mindset (including modern OO languages), I was initially tempted 
to stick to if-then and loop type logic, making several SQL calls throughout. 
But I obtained better performance by thinking in sets and moving the logic to 
SQL. This approach also forced me to look at optimising queries which often 
simplified greatly in a purely SQL context.

And, just touching on the "data integrity" aspect I mentioned: It seems 
intuitively and is practically less error prone to let the SQL take care of the 
data structure that it knows best, rather than create the data integrity rules 
in an application wrapper. Constraints, foreign keys, triggers, views are all 
tools that are designed for this task and since they are closer to the data, 
are more accurate and efficient than us re-inventing the wheel in another layer 
of code.

I'll leave it to someone smarter than I to have the last say:

Quoting from "The Art of SQL":

>> 

Re: [sqlite] Query help

[Simon Davies]

On 19 October 2010 16:26, jeff archer  wrote:
> I have a table containing width and height of images with columns wPixels,
> hPixels.  I would like to select all rows that have either a unique wPixels 
> or a
> unique hPixels value.
>
> for this data:
> 10, 20
> 10, 20
> 10, 30
> 10, 3015, 10
> 15, 30
> 15, 30
> 15, 30
>
> I would like to select:
> 10, 20
> 10, 30
> 15, 10
> 15, 30

select distinct wPixels, hPixels from table;

>
>  Jeff Archer

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

[sqlite] Query help

[jeff archer]

I have a table containing width and height of images with columns wPixels, 
hPixels.  I would like to select all rows that have either a unique wPixels or 
a 
unique hPixels value.

for this data:
10, 20
10, 20
10, 30
10, 3015, 10
15, 30
15, 30
15, 30

I would like to select:
10, 20
10, 30
15, 10
15, 30

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3

[Richard Hipp]

On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote:

> I have a query which takes 17 minutes to run with 3.7.3 against 800ms
> with 3.7.2
>
>
Thank you for the report.

Can you please send your complete schema.  The query is useful in
combination with the schema but is pretty much useless without it.

Have you tried running ANALYZE?  Does it help?  Can you also send (in
addition to the schema) the content of the sqlite_stat1 table after you have
run ANALYZE.

Your easiest work-around for the time being is to do

PRAGMA automatic_index=OFF;





> The query is:
>
> SELECT x.sheep_no, x.registering_flock, x.date_of_registration
> FROM sheep x LEFT JOIN
> (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
> s.date_of_registration, prev.owner_change_date
> FROM sheep s JOIN flock_owner prev ON s.registering_flock =
> prev.flock_no
> AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
> WHERE NOT EXISTS
> (SELECT 'x' FROM flock_owner later
> WHERE prev.flock_no = later.flock_no
> AND later.owner_change_date > prev.owner_change_date
> AND later.owner_change_date <= s.date_of_registration || '
> 00:00:00')
> ) y ON x.sheep_no = y.sheep_no
> WHERE y.sheep_no IS NULL
> ORDER BY x.registering_flock
>
> explain query plan with 3.7.3:
> 0   0   TABLE sheep AS s
> 1   1   TABLE flock_owner AS prev WITH INDEX
> sqlite_autoindex_flock_owner_1
> 0   0   TABLE flock_owner AS later WITH INDEX
> sqlite_autoindex_flock_owner_1
> 0   0   TABLE sheep AS x
> 1   1   TABLE  AS y
>
> explain query plan with 3.7.2:
> 0   0   TABLE sheep AS s
> 1   1   TABLE flock_owner AS prev WITH INDEX
> sqlite_autoindex_flock_owner_1
> 0   0   TABLE flock_owner AS later WITH INDEX
> sqlite_autoindex_flock_owner_1
> 0   0   TABLE sheep AS x
> 1   1   TABLE  AS y WITH AUTOMATIC INDEX
>
>
> Seems the planner has missed creating an index for the second nested
> SELECT.
>
> The flock_owner table has an index on each of flock_no, owner_person_id
> and owner_change_date.
>
> Pete
> --
> Peter Hardman
> Breeder of Shetland sheep and cattle
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Jay A. Kreibich]

On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp scratched on the wall:

> I was going to change this at one point, so that it raised an error, but
> that suggestion raised such an outcry that I decided to leave it.
> Apparently, there are many applications out there that depend on this
> behavior.

  I get extremely annoyed at databases that consider this an "error".
  I've had it get in the way many times, and never once has it saved me
  from an unintended error.
 
  I realize that it sets up the potential for the database to return
  somewhat nonsensical values, but getting SQL to do something dumb
  isn't exactly hard.  At the end of the day, I know my database much
  better than the RDBMS does, and the "we need to protect you from
  yourself" error is not really appreciated.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

[Pavel Ivanov]

> 2. I suggest that you're better off doing the logic entirely in SQL, rather 
> than application code, for the sake of portability, data integrity and speed.

I'd say this is a very bad advice for the developer using SQLite.
First of all "insert or ignore" and "insert or replace" are not
portable SQL structures, so we can forget about portability. Secondly
SQL and application are completely separated creatures only in case of
some server-side RDBMS. In case of SQLite they are the same
application and if you add complexity to SQL, you add complexity to
your application and it happens very often that complexity on SQL side
is much worse in performance than some additional application bits on
the other side. Just test different approaches and see it for
yourself.


Pavel

On Mon, Oct 18, 2010 at 7:54 PM, BareFeetWare  wrote:
> On 19/10/2010, at 8:10 AM, NSRT Mail account. wrote:
>
>> I would use the update if I knew the entry already existed. In my 
>> application however, it doesn't know if the entry already exists. I was 
>> looking for something to replace MySQL's ON DUPLICATE KEY UPDATE.
>>
>> I modified my application to use two SQL statements instead.
>>
>>     if (!db.execute("INSERT INTO users VALUES(?, ?, ?);", user.id, 
>> user.type, user.name))
>>     {
>>       db.execute("UPDATE users SET name=? WHERE id=? AND type=?;", 
>> user.name, user.id, user.type);
>>     }
>
> A few points:
>
> 1. I know the frustration with "replace". Unfortunately, as Igor pointed out, 
> it deletes the old row and inserts a new one. It would be great to a have a 
> "insert or update" command, but we don't, so you have to code it explicitly 
> as "insert or ignore" and an "update".
>
> 2. I suggest that you're better off doing the logic entirely in SQL, rather 
> than application code, for the sake of portability, data integrity and speed.
>
> 3. The identifiers (table and column names) should be wrapped in double 
> quotes not single quotes (which is for string literals). Most of the time 
> single quotes will work by default, but not always.
>
> -- Setting up your initial data:
>
> insert or ignore into users ("id", "type", "name") values (1, 4, 'Joe');
>
> -- now your new data, by inserting a new id if doesn't already exist, then 
> updating the columns:
>
> insert or ignore into users ("id") values (1);
> update users set "name" = 'Joe C', "type" = 4, where "id" = 1;
>
> -- alternatively you could do this, which will update the existing row, if 
> exists, or insert a new one if it doesn't:
>
> update users set "name" = 'Joe C', "type" = 4, where "id" = 1;
> insert or ignore into users ("id", "type", "name") values (1, 4, 'Joe C');
>
> Furthermore, I suspect that you want the userId to be dynamically looked up, 
> rather than specifically entered. So you may want something like this:
>
> insert into "meetings" ("userId", "type", "password")
> select
>        (select id from users where name = 'Joe C' and type = 4)
> ,       4
> ,       'blah'
> ;
>
> HTH,
> Tom
> BareFeetWare
>
>  --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Richard Hipp]

On Mon, Oct 18, 2010 at 8:19 AM,  wrote:

> I made an error in my SQL when I did not include one of my non-aggregate
> columns in my group.  I was surprised that Sqlite did not catch this, and
> even more surprised when the docs spelled out this behavior.
>
> Is everyone ok with this?
> Do any other SQL engines allow this?
> (DB2 does not)
>

I was going to change this at one point, so that it raised an error, but
that suggestion raised such an outcry that I decided to leave it.
Apparently, there are many applications out there that depend on this
behavior.

I think the big use case is as a substitute for DISTINCT.



>
> Sent from my Verizon Wireless BlackBerry
>
> -Original Message-
> From: Stephen Chrzanowski 
> Sender: sqlite-users-boun...@sqlite.org
> Date: Mon, 18 Oct 2010 06:30:28
> To: General Discussion of SQLite Database
> Reply-To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Time calculation bug?
>
> Interesting.  I get the same results as you when I use sqlite3.exe, but, in
> a database manager, the result comes back as I reported.  I'll contact the
> developer of the utility and see if he can come up with something.
>
> On Mon, Oct 18, 2010 at 5:31 AM, Simon Davies
> wrote:
>
> > On 18 October 2010 09:28, Stephen Chrzanowski 
> wrote:
> > > I seem to be having an odd behavioral problem with calculating time
> > stamps.
> > >
> > .
> > .
> > .
> > > For instance:
> > >
> > > select strftime('%s','now') RealUTC,strftime('%s','now','localtime')
> > > LocalTime,
> > >   strftime('%s','now') -   strftime('%s','now','localtime')
> > >
> > > Yeilds results of:
> > > RealUTCLocalTime  strftime('%s','now') -
> > > strftime('%s','now','localtime')
> > > -- --
> > > -
> > > 1287389442 1290053442 -2664000
> >
> > On my windoze7 m/c I set the time zone to Atlantic Time (Canada) (UTC
> > -04:00), and executed your query in sqlite3 shell:
> >
> > SQLite version 3.6.11
> > Enter ".help" for instructions
> > sqlite> select strftime('%s','now')
> > RealUTC,strftime('%s','now','localtime')
> >...> LocalTime,
> >   ...>   strftime('%s','now') -
> strftime('%s','now','localtime')
> >...> ;
> > 1287394030|1287379630|14400
> > sqlite>
> >
> > I do not see the problem that you report
> >
> > >
> > > I'm currently sitting in -0400 (EDT) and there should only be a maximum
> > of
> > > 14,400 seconds.  2664000 seems to add up to just under 31 days.
> > >
> > > Now, I'm writing the code that does the database management, and I've
> > > modified it so that when inserting/updating the time, its done with the
> > > date('2010-10-18 04:08:04','utc') to do the conversion, and the math
> > works
> > > without using UTC or LOCALTIME in the strftime functions but I'd still
> > like
> > > to know why the above SQL statement bombs?
> >
> > Regards,
> > Simon
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Spread the word...that's what these lists are for...
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Tue 10/19/2010 8:03 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls




Hi,

Well i have to say i am like a kid in a sweet shop right now, you all may
have just saved me 6 or so hours work. Thanks again for your input.

I was wondering if anyone had any issues with me posting this up on the
Boxee forum as there lots of people with this issue? I will of course tell
people that it came from here.

Thanks again,

Rich
--
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.html
Sent from the SQLite mailing list archive at Nabble.com.

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


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

Re: [sqlite] Time calculation bug?

[Stephen Chrzanowski]

Just as a follow up, there was a bug in the developers code, and he's
corrected the problem.  He forgot to carry a month. {smirk}

On Mon, Oct 18, 2010 at 6:30 AM, Stephen Chrzanowski wrote:

> Interesting.  I get the same results as you when I use sqlite3.exe, but, in
> a database manager, the result comes back as I reported.  I'll contact the
> developer of the utility and see if he can come up with something.
>
>
> On Mon, Oct 18, 2010 at 5:31 AM, Simon Davies <
> simon.james.dav...@gmail.com> wrote:
>
>> On 18 October 2010 09:28, Stephen Chrzanowski 
>> wrote:
>> > I seem to be having an odd behavioral problem with calculating time
>> stamps.
>> >
>> .
>> .
>> .
>> > For instance:
>> >
>> > select strftime('%s','now') RealUTC,strftime('%s','now','localtime')
>> > LocalTime,
>> >   strftime('%s','now') -   strftime('%s','now','localtime')
>> >
>> > Yeilds results of:
>> > RealUTCLocalTime  strftime('%s','now') -
>> > strftime('%s','now','localtime')
>> > -- --
>> > -
>> > 1287389442 1290053442 -2664000
>>
>> On my windoze7 m/c I set the time zone to Atlantic Time (Canada) (UTC
>> -04:00), and executed your query in sqlite3 shell:
>>
>> SQLite version 3.6.11
>> Enter ".help" for instructions
>> sqlite> select strftime('%s','now')
>> RealUTC,strftime('%s','now','localtime')
>>...> LocalTime,
>>   ...>   strftime('%s','now') -   strftime('%s','now','localtime')
>>...> ;
>> 1287394030|1287379630|14400
>> sqlite>
>>
>> I do not see the problem that you report
>>
>> >
>> > I'm currently sitting in -0400 (EDT) and there should only be a maximum
>> of
>> > 14,400 seconds.  2664000 seems to add up to just under 31 days.
>> >
>> > Now, I'm writing the code that does the database management, and I've
>> > modified it so that when inserting/updating the time, its done with the
>> > date('2010-10-18 04:08:04','utc') to do the conversion, and the math
>> works
>> > without using UTC or LOCALTIME in the strftime functions but I'd still
>> like
>> > to know why the above SQL statement bombs?
>>
>> Regards,
>> Simon
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

[Dickie.wild]

Hi,

Well i have to say i am like a kid in a sweet shop right now, you all may
have just saved me 6 or so hours work. Thanks again for your input.

I was wondering if anyone had any issues with me posting this up on the
Boxee forum as there lots of people with this issue? I will of course tell
people that it came from here.

Thanks again,

Rich
-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Igor Tandetnik]

Dickie.wild  wrote:
> I thought that looked like it would get the same results, but i seem to be
> getting the following error, are you able to try it and let me know if your
> getting the same error?
> 
> SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
> (rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg')

Make it

UPDATE video_files SET strCover = rtrim(strPath,replace(strPath,'\','')) || 
'folder.jpg';

-- 
Igor Tandetnik

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

Re: [sqlite] EXTERNAL:Re: EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Sorry...I didn't test before I submitted...
 
sqlite> create table video_files(strPath varchar,strCover varchar);
sqlite> insert into video_files values('c:\dir1\dir2\file.txt','');
sqlite> update video_files set strCover=(rtrim(strPath,replace(strPath,'\','')) 
|| 'folder.jpg');
sqlite> select * from video_files;
c:\dir1\dir2\file.txt|c:\dir1\dir2\folder.jpg
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Tue 10/19/2010 7:35 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls




Hi,

I thought that looked like it would get the same results, but i seem to be
getting the following error, are you able to try it and let me know if your
getting the same error?

SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
(rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg')  [ near "TO":
syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
--
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2755.html
Sent from the SQLite mailing list archive at Nabble.com.

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


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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Dickie.wild]

Hi,

I thought that looked like it would get the same results, but i seem to be
getting the following error, are you able to try it and let me know if your
getting the same error?

SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
(rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg')  [ near "TO":
syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2755.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Actually marbex came up with the best solutionwould work with ANY allowable 
path characters.
 
UPDATE video_files SET strCover TO (rtrim(strPath,replace(strPath,'\','')) || 
'folder.jpg')

 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Tue 10/19/2010 7:17 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls




On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:

> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also attached the DB just encase anyone can do it for me?
>
> Update video_files
>
> Set strCover =
> Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
> 'Folder.jpg'

We told you how to do it in SQLite.  The answer involved using rtrim().  Two of 
us even posted example code for you.

If you want an equivalent to that specific command then, copying from my post 
of a few days ago, it would be something like

UPDATE video_files SET strCover TO (rtrim(strPath, 'abcd... ABCD... 123... ._') 
|| 'folder.jpg')

(expanded to include alphabets and digits).  If you have tried that and it 
didn't work please tell us what it did instead of working.

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


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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Simon Slavin]

On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:

> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also attached the DB just encase anyone can do it for me?
> 
> Update video_files
> 
> Set strCover =
> Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
> 'Folder.jpg'

We told you how to do it in SQLite.  The answer involved using rtrim().  Two of 
us even posted example code for you.

If you want an equivalent to that specific command then, copying from my post 
of a few days ago, it would be something like

UPDATE video_files SET strCover TO (rtrim(strPath, 'abcd... ABCD... 123... ._') 
|| 'folder.jpg')

(expanded to include alphabets and digits).  If you have tried that and it 
didn't work please tell us what it did instead of working.

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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Dickie.wild]

Hi, 

Thanks for the reply's i have tried the various ways described that they do
not seem to be working. I have a way in which it works but this is in SQL
could anyone convert it to SQLite for me? i am not to sure it is even
possible. I have also attached the DB just encase anyone can do it for me?

Update video_files

Set strCover =
Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
'Folder.jpg'

http://old.nabble.com/file/p2497/Boxee_catalog.db Boxee_catalog.db 
-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2497.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

[Raj, Praveen]

Hello Dan,

To answer your question, the 2nd ftruncate() is not zeroing the 1st mmapped 
region. I had checked it using debugger, and it happens only during
2nd mmap call.
But from my investigation, i found that the 2nd ftruncate()(and further calls) 
is the one from where the problem is arising.

The ftruncate() call is setting some flag (it could be time field of file) 
which will make the 1st region to remap with disk file contents, when 2nd 
mmap() is called.
I avoided the second ftruncate call (hardcoded size to high value) and 2nd 
mmap() call worked as desired - mapped only the 2nd 32k region retaining the 
1st 32k region data intact.

---
int ftruncate( int fildes,
   off_t length );

int ftruncate64( int fildes,
 off64_t length );

Arguments:
fildes
The descriptor for the file that you want to truncate.
length
The length that you want the file to be, in bytes.
Library:
libc

Use the -l c option to qcc to link against this library. This library is 
usually included automatically.

Description:
These functions cause the file referenced by fildes to have a size of length 
bytes. If the size of the file previously exceeded length, the extra data is 
discarded (this is similar to using the F_FREESP option with fcntl()). If the 
size of the file was previously shorter than length, the file size is extended 
with NUL characters (similar to the F_ALLOCSP option to fcntl()).

The value of the seek pointer isn't modified by a call to ftruncate().

Upon successful completion, the ftruncate() function marks the st_ctime and 
st_mtime fields of the file for update. If the ftruncate() function is 
unsuccessful, the file is unaffected.


The API tells that the time fields of file are updated. This could be the 
triggering point for our issue, not sure though.

Michael/Dan,
Any inputs from your side?

Thanks for all your valuable inputs till now.

Thanks,
Praveen


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan Kennedy
Sent: Friday, October 15, 2010 9:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS


On Oct 15, 2010, at 10:24 PM, Black, Michael (IS) wrote:

> I'm not sure but I suspect sqlite is not calling unmap before
> extending the area.
> That would explain why it still gets zeroed out even with the flags.
>
> Put a break point in the unixShmUnmap call and see if it gets called
> before mmap.


We're doing this:

   ftruncate(fd, 32*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
   
   ftruncate(fd, 64*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 32*1024);

No unmap calls.

Praveen's investigations suggest that the second mmap() call is
zeroing the memory mapped by the first mmap() call. Which is,
as you might expect, confusing SQLite.

I guess it could also be the second ftruncate() call that is
zeroing our mapped memory. That would be even odder though...

Dan.



> May just need some QNX logic that says "if we're extending an area
> unmap it first with flags".
>
> I think the mmap should honor the NOINIT flag when extending an area
> but apparently it doesn't -- only unmap can set the flag to make it
> honor it.  QNX must be about the only one that does this.
>
> QNX mmap claims to be POSIX 1003.1 compliant but I don't see this
> behavior defined in there.
>
> The only zero-fill reference I see in POSIX is
> "The system shall always zero-fill any partial page at the end of an
> object"
> which doesn't fit this behavior at all.
>
> Perhaps you should report this as a bug or non-desirable/non-
> compliant behavior compared to every other OS in the world (and the
> POSIX standard which doesn't call for this behavior).
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Raj, Praveen
> Sent: Fri 10/15/2010 9:55 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] TestFixture 3.7.2 - Some WAL tests
> fail on QNX OS
>
>
>
> Hi Michael,
>
> Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL"
> flag to munmap_flags() call. Don't know where i might be going wrong
> in SQLite.
>
> As you suggested, I wrote a small application to check if this
> works. Fortunately it worked as desired (as given below).
>
> MAP_NOINIT
> When specified, the POSIX requirement that the memory be zeroed is
> relaxed. The physical memory being used for this allocation must
> have been previously freed with UNMAP_INIT_OPTIONAL for this flag to
> have any effect.
>
> _
> int main(int argc, char