Re: [sqlite] how to put the database in cache without waiting the system do it by himself ?

[Pavel Ivanov]

> so i guess it's because the windows cache in memory the database file ?

If you launch your application again then yes it's OS disk cache. If
you execute same query later in your application without stopping it
and maybe even without closing connection to SQLite database then it
could be SQLite's page cache. You can't fill it in any other way than
executing queries.


Pavel

On Thu, Jan 13, 2011 at 4:55 PM, Vander Clock Stephane
 wrote:
> hello,
>
> when i just launch th application, at the beginning the query can take
> around fews seconds... but after some time (10 - 20 minutes), it's take
> only few ms !
>
> so i guess it's because the windows cache in memory the database file ?
>
> so how to speed up this time to make windows cache more fastly in memory
> the database file ?
>
> thanks you by advance
> stephane
> ___
> 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] Committing to a savepoint

[Pavel Ivanov]

> Is there a way to do this today? How hard would it be to add "commit to
> savepoint" if not?

It's impossible to do the thing you want in SQLite. And it won't be
feasible to add that.
What you really want is for database engine to allow to have two
parallel writing transactions and for it to not lock the whole
database in those transactions but do some fine-grained locking
instead. In this case you will be able to do A in one transaction
(over one connection) and C in another transaction (over another
connection). Then when you are ready you commit first transaction
leaving second uncommitted. You should use some other RDBMS for that,
SQLite is not suitable for such task. If you insist on using SQLite
you should cache yourself all changes in C somewhere outside the
database. Then when you commit everything done in A you can apply all
cached changes...


Pavel

On Thu, Jan 13, 2011 at 6:16 PM, Charles Samuels  wrote:
>
> Hi,
>
> I have a program that uses sqlite to do bookkeeping for another set of data. I
> do a commit on sqlite once I'm certain the other set of data is done. While
> I'm waiting for the other set of data to process, I might make other changes
> to my Sqlite database. However, I don't want to commit the stuff made after I
> started to process the other set of data.
>
> In short, I want a "commit to savepoint savepoint-name" which commits up until
> a savepoint begins, but not anything after.
>
> Here's more or less what I need:
>
> A * sqlite gets some inserts
> B * we're at a checkpoint, so everything after this point shouldn't get
> committed now. So "savepoint SP"
> C * insert some more into sqlite
> D * The checkpoint is ready to go, so we do "commit to savepoint SP"
> E * now, on-disk, the sqlite db contains everything in step A, but nothing in
> step C
>
> In this example, doing the commit at D is the same as doing in between A and
> B.
>
> Is there a way to do this today? How hard would it be to add "commit to
> savepoint" if not?
>
> Thanks,
>
> Charles
>
> ps: I think the documentation on savepoint is a little bit unclear on if
> "rollback to savepoint" and "release savepoint" keep the savepoint in question
> as the active savepoint.
> ___
> 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] View with values in 1st table replaced by values in second table

[Jay A. Kreibich]

On Thu, Jan 13, 2011 at 01:44:12PM -0600, Josh Marell scratched on the wall:

> Schedule {
> date TEXT UNIQUE NOT NULL
> problem_set INTEGER
> literature INTEGER
> research INTEGER}
> 
> Presenters {
> p_id INTEGER PRIMARY KEY
> short_name TEXT UNIQUE NOT NULL}

> I am trying to create a view such that the output is the 4 columns in the
> schedule table, except instead of the p_id being displayed, I want to
> replace those values with the short_name.
> 
> For any given date, 2 of the 3 categories is set to a p_id and the 3rd is
> null.


CREATE VIEW Schedule_names AS
  SELECT s.date, p.name, l.name, r.name
FROM  Schedule   AS s
LEFT JOIN Presenters AS p ON ( s.problem_set = p.p_id )
LEFT JOIN Presenters AS l ON ( s.literature  = l.p_id )
LEFT JOIN Presenters AS r ON ( s.research= r.p_id );



   -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] Committing to a savepoint

[Charles Samuels]

On Thursday, January 13, 2011 3:23:55 pm Simon Slavin wrote:
> Look at savepoints:
> 
> http://www.sqlite.org/lang_savepoint.html

Yes, I have been, and I use them quite a bit. However, they don't appear to 
meet my needs, which is why I asked my question.

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

Re: [sqlite] Committing to a savepoint

[Simon Slavin]

On 13 Jan 2011, at 11:16pm, Charles Samuels wrote:

> In short, I want a "commit to savepoint savepoint-name" which commits up 
> until 
> a savepoint begins, but not anything after.

Look at savepoints:

http://www.sqlite.org/lang_savepoint.html

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

[sqlite] Committing to a savepoint

[Charles Samuels]

Hi,

I have a program that uses sqlite to do bookkeeping for another set of data. I 
do a commit on sqlite once I'm certain the other set of data is done. While 
I'm waiting for the other set of data to process, I might make other changes 
to my Sqlite database. However, I don't want to commit the stuff made after I 
started to process the other set of data.

In short, I want a "commit to savepoint savepoint-name" which commits up until 
a savepoint begins, but not anything after.

Here's more or less what I need:

A * sqlite gets some inserts
B * we're at a checkpoint, so everything after this point shouldn't get 
committed now. So "savepoint SP"
C * insert some more into sqlite
D * The checkpoint is ready to go, so we do "commit to savepoint SP"
E * now, on-disk, the sqlite db contains everything in step A, but nothing in 
step C

In this example, doing the commit at D is the same as doing in between A and 
B.

Is there a way to do this today? How hard would it be to add "commit to 
savepoint" if not?

Thanks,

Charles

ps: I think the documentation on savepoint is a little bit unclear on if 
"rollback to savepoint" and "release savepoint" keep the savepoint in question 
as the active savepoint.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to put the database in cache without waiting the system do it by himself ?

[Jean-Christophe Deschamps]

>when i just launch th application, at the beginning the query can take
>around fews seconds... but after some time (10 - 20 minutes), it's take
>only few ms !
>
>so i guess it's because the windows cache in memory the database file ?
>
>so how to speed up this time to make windows cache more fastly in memory
>the database file ?

Read the file!

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

Re: [sqlite] View with values in 1st table replaced by values in second table

[Igor Tandetnik]

On 1/13/2011 2:44 PM, Josh Marell wrote:
> I have two tables designed as:
>
> Schedule {
> date TEXT UNIQUE NOT NULL
> problem_set INTEGER
> literature INTEGER
> research INTEGER}
>
> Presenters {
> p_id INTEGER PRIMARY KEY
> short_name TEXT UNIQUE NOT NULL}
>
> problem_set, literature, and research reference a p_id in the presenter
> list.
>
> I am trying to create a view such that the output is the 4 columns in the
> schedule table, except instead of the p_id being displayed, I want to
> replace those values with the short_name.

select date, ps.short_name, l.short_name, r.short_name
from Schedule left join Presenters ps on (problem_set = ps.p_id)
   left join Presenters l on (literature = l.p_id)
   left join Presenters r on (research = r.p_id);

-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3_open_v2 somewhat misses the point

[Marian Cascaval]

Uhm... so you've figured I'm not an English teacher either.

That's why a team is good for: correcting each other's typos :)




Marian Cascaval





From: Puneet Kishor 
To: General Discussion of SQLite Database 
Sent: Thu, January 13, 2011 11:21:11 PM
Subject: Re: [sqlite] sqlite3_open_v2 somewhat misses the point

Since you *are* correcting typos in the documentation (and have good 
humor about you while doing so) ...

Marian Cascaval wrote:
> I'm not a programmer, so just bare with me please..

... that would require Richard to get personal with you. You probably 
want to change the above to

"I'm not a programmer, so just _bear_ with me please."


-- 
Puneet Kishor
___
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] how to put the database in cache without waiting the system do it by himself ?

[Vander Clock Stephane]

hello,

when i just launch th application, at the beginning the query can take 
around fews seconds... but after some time (10 - 20 minutes), it's take 
only few ms !

so i guess it's because the windows cache in memory the database file ?

so how to speed up this time to make windows cache more fastly in memory 
the database file ?

thanks you by advance
stephane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cast(count(1) as integer) result is text?

[Vannus]

(missed your message somehow)

All 3 return integer in sqlite express (which is good), but zeos doesn't
like typeof().

On 6 January 2011 09:12, Simon Slavin  wrote:

>
> On 6 Jan 2011, at 4:00am, Vannus wrote:
>
> > Using both ZeosLib and SQLite Expert the SubTotal (and count if inserted)
> is
> > returned as text
>
> The documentation for ZeosLib appears to be in '.chm' files.  I can't read
> that format.
>
> SQLite Expert is a database admin system, not a programming API.  It
> doesn't need to support typing.  My guess is that it handles everything that
> isn't a direct copy of a column value as text, which would be the simplest
> way to write such a program.
>
> > SELECT
> >  Orders.ID AS OrderID,
> >  Mixes.ID AS MixID,
> >  Mixes.Percentage AS Percentage,
> > /* count(1),*/
> > /* cast(cout(1) as integer), */
> > /* 1 as one,*/
> > /* cast(1 as integer) AS one,*/
> >  (Orders.Total/100)*Mixes.Percentage AS SubTotal
> > FROM
> >  Orders, Mixes
>
> Execute
>
> SELECT
>  typeof(Orders.Total/100),
>  typeof((Orders.Total/100)*Mixes.Percentage),
>  typeof(count(Orders))
> FROM
>  Orders, Mixes
>
> and see what you get.  We assure you that as far as we know, SQLite handles
> such numbers as numbers rather than text.
>
> 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] sqlite3_open_v2 somewhat misses the point

[Puneet Kishor]

Since you *are* correcting typos in the documentation (and have good 
humor about you while doing so) ...

Marian Cascaval wrote:
> I'm not a programmer, so just bare with me please..

... that would require Richard to get personal with you. You probably 
want to change the above to

"I'm not a programmer, so just _bear_ with me please."


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

[sqlite] sqlite3_open_v2 somewhat misses the point

[Marian Cascaval]

Hi again fellows SQLiters and thank you Mr. Hipp for this great C code!


I was under the impression that sqlite3_open_v2 allows you to know for sure if 
the database is or isn't opened successfully when using the 
SQLITE_OPEN_READWRITE flag.

But when the first argument to sqlite3_open_v2 is NULL, the function returns 
SQLITE_OK, counter to my logic. Of course, subsequent error messages appear 
from 
trying to execute SQL statements on a non-existent database, but the point is 
the error comes too late and not at the appropriate time i.e. the opening of 
database.

So, I conclude it's the programmer's job to test, prior to executing 
sqlite3_open_v2, if there's a database to open at all (for example testing the 
value of argc from 'int main(int argc, char **argv)' where argv would be the 
database path).

Is the returning of SQLITE_OK from sqlite3_open_v2(NULL, _, 
SQLITE_OPEN_READWRITE, _) an intended feature?

If so, then I think this info could be useful to have it mentioned in the 
sqlite3_open Documentation under SQLITE_OPEN_READWRITE:
http://www.sqlite.org/c3ref/open.html

I think it should go like this:
"In either case the database must already exist, otherwise an error is returned 
_, except when first parameter is NULL_."


I'm not a programmer, so just bare with me please..




Marian Cascaval


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

[sqlite] View with values in 1st table replaced by values in second table

[Josh Marell]

Hi everyone.  I am new to this mailing list, so hopefully I will be able to
present my idea clearly to you:

I have two tables designed as:

Schedule {
date TEXT UNIQUE NOT NULL
problem_set INTEGER
literature INTEGER
research INTEGER}

Presenters {
p_id INTEGER PRIMARY KEY
short_name TEXT UNIQUE NOT NULL}

problem_set, literature, and research reference a p_id in the presenter
list.

I am trying to create a view such that the output is the 4 columns in the
schedule table, except instead of the p_id being displayed, I want to
replace those values with the short_name.

For any given date, 2 of the 3 categories is set to a p_id and the 3rd is
null.

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

Re: [sqlite] Commit from thread?

[Igor Tandetnik]

On 1/13/2011 11:49 AM, Black, Michael (IS) wrote:
> I have a shared library being called by a transaction-oriented Java
> system.  I'm doing periodic commits (every 5 seconds) to speed up
> processing.  However, I want to commit even when not polled by the
> Java system.  There could be long periods of silence and all the data
> needs to be accessible by others.
>
> I thought of spawning a thread which would do the commitsbut it
> would appear that's not possible (or at least a really bad idea).

With reasonably modern SQLite versions, this shouldn't be a problem. 
SQLite API is thread-safe (there's just a single lock assigned to a 
connection that every API method acquires on entry and releases on exit).
-- 
Igor Tandetnik

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

Re: [sqlite] How to track a record if the aliased id is changed

[Nicolas Williams]

On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote:
> His problem is that he doesn't know which rows are aliased to rowid,
> so he can't provide a list of column names.  So the following might be
> closer
> 
> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable
> BEGIN
>  UPDATE UpdatedRowId Set Value = new.rowid WHERE new.rowid != old.rowid;
> END;
> 
> But I don't know whether SQLite can actually do that comparison at that stage.

I just tried it.  SQLite3 does handle this correctly:

sqlite> CREATE TEMP TABLE t(a integer primary key, b text);
sqlite> INSERT INTO t VALUES (1, 'abc');
sqlite> INSERT INTO t VALUES (2, 'def');
sqlite> CREATE TEMP TABLE t1 (a integer, newa integer);
sqlite> CREATE TEMP TRIGGER t2 AFTER UPDATE ON t FOR EACH ROW BEGIN
   ...> INSERT INTO t1 (a, newa) SELECT OLD.rowid, NEW.rowid WHERE OLD.rowid !=
   ...> NEW.rowid; END;
sqlite> UPDATE t SET b = 'xyz' WHERE a = 1;
sqlite> SELECT * FROM t;
1|XYZ
2|def
sqlite> SELECT * FROM t1;
sqlite> UPDATE t SET a = 3 WHERE b = 'def';
sqlite> SELECT * FROM t;
1|xyz
3|def
sqlite> SELECT * FROM t1;
2|3
sqlite> 

Handy!

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

Re: [sqlite] UNSUBSCRIBE

[Simon Slavin]

Go to the address at the bottom line of every post, including this one.

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

[sqlite] UNSUBSCRIBE

[Babar Baig]

> From: d...@sqlite.org
> Date: Thu, 13 Jan 2011 07:59:11 -0500
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How SQLite manages result of a SELECT query?
> 
> On Thu, Jan 13, 2011 at 5:42 AM, Sunil Bhardwaj <
> sbhard...@ipolicynetworks.com> wrote:
> 
> > Hi
> >
> >
> > Let me rephrase my question.
> >
> > We are using in-memory db.
> >
> > When we do "execQuery":
> > 1. Does sqlite fetches complete result of query and keep it in separate
> > memory
> > 2. OR Does sqlite fetches result of query in chunks, when we use
> > "sqlite3_step" and keep it in separate memory
> > 3. OR Does sqlite fetches records one by one, when we use "sqlite3_step"
> >
> 
> SQLite computes one row at a time, as you call sqlite3_step().
> 
> 
> >
> > Please help us to understand, how can we use it efficiently.
> >
> > Thanks
> > Sunil Bhardwaj
> > Ext. 1125 (0120-2567001)
> > 9818868910
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:
> > sqlite-users-boun...@sqlite.org] On Behalf Of Ian Hardingham
> > Sent: Thursday, January 13, 2011 4:07 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] How SQLite manages result of a SELECT query?
> >
> > That seems like kind of a broad question.
> >
> > On 13/01/2011 10:33, Sunil Bhardwaj wrote:
> > > Hi
> > >
> > > Please help us to understand, how SQLite manages result of a SELECT
> > query:
> > >   - We are using in-memory db,
> > >   -
> > >   - When we do "execQuery", what operations are internally done in
> > SQLite
> > >
> > > Thanks
> > > Sunil Bhardwaj
> > > Ext. 1125 (0120-2567001)
> > > 9818868910
> > >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:
> > sqlite-users-boun...@sqlite.org] On Behalf Of Madhurima .
> > > Sent: Thursday, January 13, 2011 3:30 PM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] partitioning SQLite database
> > >
> > > I am working on embedded system and the device has linux kernel with
> > sqlite database. Wanted to know if the sqlite database can be partitioned
> > with secure and normal partitions.
> > >
> > > How can the encryption be achieved for sqlite database file in linux.
> > >
> > >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:
> > sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> > > Sent: Thursday, January 13, 2011 3:18 PM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] partitioning SQLite database
> > >
> > >
> > > On 13 Jan 2011, at 6:08am, Madhurima . wrote:
> > >
> > >> Is it possible to have partition in SQLite database?
> > > The word 'partition' is used for more than one meaning.  You might be
> > talking about spreading the data about, or grouping results from a SELECT
> > command.  Could you give an example or a reference to documentation ?
> > >
> > > Simon.
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > > "DISCLAIMER: This message is proprietary to Aricent and is intended
> > solely for the use of the individual to whom it is addressed. It may contain
> > privileged or confidential information and should not be circulated or used
> > for any purpose other than for what it is intended. If you have received
> > this message in error, please notify the originator immediately. If you are
> > not the intended recipient, you are notified that you are strictly
> > prohibited from using, copying, altering, or disclosing the contents of this
> > message. Aricent accepts no responsibility for loss or damage arising from
> > the use of the information transmitted by this email including damage from
> > virus."
> > > ___
> > > 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
> > ___
> > 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
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Commit from thread?

[Black, Michael (IS)]

I think the answer to this is "no" based on http://www.sqlite.org/faq.html#q6
But a confirmation or other solution would be nice...
 
I have a shared library being called by a transaction-oriented Java system.  
I'm doing periodic commits (every 5 seconds) to speed up processing.  However, 
I want to commit even when not polled by the Java system.  There could be long 
periods of silence and all the data needs to be accessible by others.
 
I thought of spawning a thread which would do the commitsbut it would 
appear that's not possible (or at least a really bad idea).
 
Since there is currently no continuous execution path it would seem I'm stuck.  
Would have to have the Java system do a heartbeat or such to ensure the commit 
gets done.
 
Is this the correct interpretation of the the thread-safe ability of SQLite?
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation typos - part no.2

[Puneet Kishor]

Marian Cascaval wrote:
> Hi!
>
> Some more Documentation typos:
>
> 1.
> http://www.sqlite.org/pragma.html#pragma_synchronous
> "... will not corrupt the _dartbase_."
>   It should be either _dartboard_ or _database_ but I'd stick with the 
> latter
> ;).
>
> 2.
> http://www.sqlite.org/c3ref/open.html
> "... and _is creates it_ if it does not already exist."
>   It should be either _is created_ or _creates it_.
>  It's clear the author had these two ideas in mind and amalgamated them 
> ;).
>
>
>
> I can drop the jokes next time if solemnity is required here.
>


Good heavens no. Lord knows we need some levity. SQL is not a very 
scintillating topic by itself. That said, making jokes does open you up 
for being judged on your sensayuma, so better make them top-notch.

Anyway, thanks for the contribs. This is the kind of stuff that benefits 
everyone.

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

[sqlite] Documentation typos - part no.2

[Marian Cascaval]

Hi!

Some more Documentation typos:

1.
http://www.sqlite.org/pragma.html#pragma_synchronous
"... will not corrupt the _dartbase_."
 It should be either _dartboard_ or _database_ but I'd stick with the 
latter 
;).

2.
http://www.sqlite.org/c3ref/open.html
"... and _is creates it_ if it does not already exist."
 It should be either _is created_ or _creates it_.
It's clear the author had these two ideas in mind and amalgamated them ;).



I can drop the jokes next time if solemnity is required here.


Marian Cascaval


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

Re: [sqlite] Using stored Regular Expressions to match given String

[Bruno Augusto]

Sorry for the mistake about the images.

The current version is: http://img15.imageshack.us/img15/7747/44037591.png

About the problem, many thanks for all the explanations about the way SQLITE
deals with Regular Expressions.

It worked perfectly. If interested to anyone (as part of PHP routine):

>> private function extend() {
>>
>>if( ! method_exists( $this -> dbh, 'sqliteCreateFunction' ) ) {
>>throw new RouterException( 'PDO::sqliteCreateFunction() doesn\'t
exist in current >> Database Handler so you are not able to use Standard
Router' );
>>}
>>
>>$this -> dbh
>>  -> sqliteCreateFunction(
>>
>>  'REGEXP',
>>
>>  function( $regex, $string ) {
>>
>>  if( preg_match( sprintf( '@%s@', $regex ), $string ) !=
0 ) {
>>  return TRUE;
>>  }
>>
>>  return FALSE;
>>  },
>>
>>  2
>>  );
>> }

Using:

>> 'SELECT `RouteID`, `Application`, `Class`, `Method`,
>>   `RequiredParams`, `OptionalParams`
>> FROM `Routes` WHERE `RequestMethod` = "GET"
>>  AND "STRING_HERE" REGEXP `URI`'

I hope this helps someone else.

-- 
Best Regards

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

Re: [sqlite] How SQLite manages result of a SELECT query?

[Pavel Ivanov]

> When we do "execQuery":
> 1. Does sqlite fetches complete result of query and keep it in separate memory
> 2. OR Does sqlite fetches result of query in chunks, when we use 
> "sqlite3_step" and keep it in separate memory
> 3. OR Does sqlite fetches records one by one, when we use "sqlite3_step"

Number 3 is how SQLite works, no matter if you have in-memory or
on-disk database. And probably even as Marian pointed out it doesn't
really "fetch", it just remembers location in the database page (from
database page cache) where necessary row is located and actually
"fetches" data when you call sqlite3_column().


Pavel

On Thu, Jan 13, 2011 at 5:42 AM, Sunil Bhardwaj
 wrote:
> Hi
>
>
> Let me rephrase my question.
>
> We are using in-memory db.
>
> When we do "execQuery":
> 1. Does sqlite fetches complete result of query and keep it in separate memory
> 2. OR Does sqlite fetches result of query in chunks, when we use 
> "sqlite3_step" and keep it in separate memory
> 3. OR Does sqlite fetches records one by one, when we use "sqlite3_step"
>
> Please help us to understand, how can we use it efficiently.
>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ian Hardingham
> Sent: Thursday, January 13, 2011 4:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How SQLite manages result of a SELECT query?
>
> That seems like kind of a broad question.
>
> On 13/01/2011 10:33, Sunil Bhardwaj wrote:
>> Hi
>>
>> Please help us to understand, how SQLite manages result of a SELECT query:
>>       - We are using in-memory db,
>>       -
>>       - When we do "execQuery", what operations are internally done in SQLite
>>
>> Thanks
>> Sunil Bhardwaj
>> Ext. 1125 (0120-2567001)
>> 9818868910
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhurima .
>> Sent: Thursday, January 13, 2011 3:30 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] partitioning SQLite database
>>
>> I am working on embedded system and the device has linux kernel with sqlite 
>> database. Wanted to know if the sqlite database can be partitioned with 
>> secure and normal partitions.
>>
>> How can the encryption be achieved for sqlite database file in linux.
>>
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
>> Sent: Thursday, January 13, 2011 3:18 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] partitioning SQLite database
>>
>>
>> On 13 Jan 2011, at 6:08am, Madhurima . wrote:
>>
>>> Is it possible to have partition in SQLite database?
>> The word 'partition' is used for more than one meaning.  You might be 
>> talking about spreading the data about, or grouping results from a SELECT 
>> command.  Could you give an example or a reference to documentation ?
>>
>> Simon.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> "DISCLAIMER: This message is proprietary to Aricent and is intended solely 
>> for the use of the individual to whom it is addressed. It may contain 
>> privileged or confidential information and should not be circulated or used 
>> for any purpose other than for what it is intended. If you have received 
>> this message in error, please notify the originator immediately. If you are 
>> not the intended recipient, you are notified that you are strictly 
>> prohibited from using, copying, altering, or disclosing the contents of this 
>> message. Aricent accepts no responsibility for loss or damage arising from 
>> the use of the information transmitted by this email including damage from 
>> virus."
>> ___
>> 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
> ___
> 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] How SQLite manages result of a SELECT query?

[Richard Hipp]

On Thu, Jan 13, 2011 at 5:42 AM, Sunil Bhardwaj <
sbhard...@ipolicynetworks.com> wrote:

> Hi
>
>
> Let me rephrase my question.
>
> We are using in-memory db.
>
> When we do "execQuery":
> 1. Does sqlite fetches complete result of query and keep it in separate
> memory
> 2. OR Does sqlite fetches result of query in chunks, when we use
> "sqlite3_step" and keep it in separate memory
> 3. OR Does sqlite fetches records one by one, when we use "sqlite3_step"
>

SQLite computes one row at a time, as you call sqlite3_step().


>
> Please help us to understand, how can we use it efficiently.
>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Ian Hardingham
> Sent: Thursday, January 13, 2011 4:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How SQLite manages result of a SELECT query?
>
> That seems like kind of a broad question.
>
> On 13/01/2011 10:33, Sunil Bhardwaj wrote:
> > Hi
> >
> > Please help us to understand, how SQLite manages result of a SELECT
> query:
> >   - We are using in-memory db,
> >   -
> >   - When we do "execQuery", what operations are internally done in
> SQLite
> >
> > Thanks
> > Sunil Bhardwaj
> > Ext. 1125 (0120-2567001)
> > 9818868910
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Madhurima .
> > Sent: Thursday, January 13, 2011 3:30 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] partitioning SQLite database
> >
> > I am working on embedded system and the device has linux kernel with
> sqlite database. Wanted to know if the sqlite database can be partitioned
> with secure and normal partitions.
> >
> > How can the encryption be achieved for sqlite database file in linux.
> >
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> > Sent: Thursday, January 13, 2011 3:18 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] partitioning SQLite database
> >
> >
> > On 13 Jan 2011, at 6:08am, Madhurima . wrote:
> >
> >> Is it possible to have partition in SQLite database?
> > The word 'partition' is used for more than one meaning.  You might be
> talking about spreading the data about, or grouping results from a SELECT
> command.  Could you give an example or a reference to documentation ?
> >
> > Simon.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > "DISCLAIMER: This message is proprietary to Aricent and is intended
> solely for the use of the individual to whom it is addressed. It may contain
> privileged or confidential information and should not be circulated or used
> for any purpose other than for what it is intended. If you have received
> this message in error, please notify the originator immediately. If you are
> not the intended recipient, you are notified that you are strictly
> prohibited from using, copying, altering, or disclosing the contents of this
> message. Aricent accepts no responsibility for loss or damage arising from
> the use of the information transmitted by this email including damage from
> virus."
> > ___
> > 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
> ___
> 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] How SQLite manages result of a SELECT query?

[Marian Cascaval]

Hi!

As far as I understandd, sqlite3_step() does not fetch any data but returns 
SQLITE_ROW. If this is the case, then use sqlite3_column() to fetch the actual 
data.



Marian Cascaval





From: Sunil Bhardwaj 
To: General Discussion of SQLite Database 
Sent: Thu, January 13, 2011 12:42:46 PM
Subject: Re: [sqlite] How SQLite manages result of a SELECT query?

Hi


Let me rephrase my question.

We are using in-memory db.

When we do "execQuery":
1. Does sqlite fetches complete result of query and keep it in separate memory
2. OR Does sqlite fetches result of query in chunks, when we use "sqlite3_step" 
and keep it in separate memory
3. OR Does sqlite fetches records one by one, when we use "sqlite3_step"

Please help us to understand, how can we use it efficiently. 

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ian Hardingham
Sent: Thursday, January 13, 2011 4:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How SQLite manages result of a SELECT query?

That seems like kind of a broad question.

On 13/01/2011 10:33, Sunil Bhardwaj wrote:
> Hi
>
> Please help us to understand, how SQLite manages result of a SELECT query:
> - We are using in-memory db,
> -
> - When we do "execQuery", what operations are internally done in SQLite
>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Madhurima .
> Sent: Thursday, January 13, 2011 3:30 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
> I am working on embedded system and the device has linux kernel with sqlite 
>database. Wanted to know if the sqlite database can be partitioned with secure 
>and normal partitions.
>
> How can the encryption be achieved for sqlite database file in linux.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Simon Slavin
> Sent: Thursday, January 13, 2011 3:18 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
>
> On 13 Jan 2011, at 6:08am, Madhurima . wrote:
>
>> Is it possible to have partition in SQLite database?
> The word 'partition' is used for more than one meaning.  You might be talking 
>about spreading the data about, or grouping results from a SELECT command.  
>Could you give an example or a reference to documentation ?
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> "DISCLAIMER: This message is proprietary to Aricent and is intended solely 
> for 
>the use of the individual to whom it is addressed. It may contain privileged 
>or 
>confidential information and should not be circulated or used for any purpose 
>other than for what it is intended. If you have received this message in 
>error, 
>please notify the originator immediately. If you are not the intended 
>recipient, 
>you are notified that you are strictly prohibited from using, copying, 
>altering, 
>or disclosing the contents of this message. Aricent accepts no responsibility 
>for loss or damage arising from the use of the information transmitted by this 
>email including damage from virus."
> ___
> 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
___
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] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 2:32 PM, Simon Davies
wrote:

> On 13 January 2011 10:56, Max Vlasov  wrote:
> > On Thu, Jan 13, 2011 at 1:48 PM, Simon Davies
> > wrote:
> >
> >> On 13 January 2011 10:28, Max Vlasov  wrote:
> >>
> >> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON
> >> TestTable
> >> BEGIN
> >>  UPDATE UpdatedRowId Set Value = new.rowid;
> >> END;
> >>
> >>
> > Simon, this particular change won't help globally since if you remember I
> > don't know what exact column is aliased, but I probably will end up
> without
> > "OF" at all
>
> pragma table_info gives information allowing rowid alias to be determined
>
>

There's at least one exception (see this post
http://www.mail-archive.com/sqlite-users@sqlite.org/msg52462.html)

Ok, thanks to Dan's suggestion and the following discussion everything now
works like a charm, aliased fields can be changed and tracked.

Thanks to all!

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Davies]

On 13 January 2011 10:56, Max Vlasov  wrote:
> On Thu, Jan 13, 2011 at 1:48 PM, Simon Davies
> wrote:
>
>> On 13 January 2011 10:28, Max Vlasov  wrote:
>>
>> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON
>> TestTable
>> BEGIN
>>  UPDATE UpdatedRowId Set Value = new.rowid;
>> END;
>>
>>
> Simon, this particular change won't help globally since if you remember I
> don't know what exact column is aliased, but I probably will end up without
> "OF" at all

pragma table_info gives information allowing rowid alias to be determined

>
> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable...
>
> so any update will pass changed (or not changed) rowid. I think it should ok
> in terms of performance if both trigger and table are temporary.
>
> Max

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

Re: [sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 1:59 PM, Simon Slavin  wrote:

>
> On 13 Jan 2011, at 10:48am, Simon Davies wrote:
>
> > CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON
> TestTable
> > BEGIN
> >  UPDATE UpdatedRowId Set Value = new.rowid;
> > END;
>
> His problem is that he doesn't know which rows are aliased to rowid, so he
> can't provide a list of column names.  So the following might be closer
>
> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable
> BEGIN
>  UPDATE UpdatedRowId Set Value = new.rowid WHERE new.rowid != old.rowid;
> END;
>
> But I don't know whether SQLite can actually do that comparison at that
> stage.
>
>

Funny thing we talk so fast I didn't notice that was different Simon ;)

It's ok without comparison, I willl wrap update in before/after sections.
Before-section will table and trigger, after-section willl get the the
actual rowid from temp table and delete both table and trigger. Everything
else can be finished in the code.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Slavin]

On 13 Jan 2011, at 10:48am, Simon Davies wrote:

> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON 
> TestTable
> BEGIN
>  UPDATE UpdatedRowId Set Value = new.rowid;
> END;

His problem is that he doesn't know which rows are aliased to rowid, so he 
can't provide a list of column names.  So the following might be closer

CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable
BEGIN
 UPDATE UpdatedRowId Set Value = new.rowid WHERE new.rowid != old.rowid;
END;

But I don't know whether SQLite can actually do that comparison at that stage.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 1:48 PM, Simon Davies
wrote:

> On 13 January 2011 10:28, Max Vlasov  wrote:
>
> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON
> TestTable
> BEGIN
>  UPDATE UpdatedRowId Set Value = new.rowid;
> END;
>
>
Simon, this particular change won't help globally since if you remember I
don't know what exact column is aliased, but I probably will end up without
"OF" at all

CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable...

so any update will pass changed (or not changed) rowid. I think it should ok
in terms of performance if both trigger and table are temporary.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Davies]

On 13 January 2011 10:28, Max Vlasov  wrote:
> Simon, I also liked Dan's suggestion, now I'm trying to implement this, but
> it seems that sqlite contains an interesting side effect or I don't know how
> to call it.
>
> if I create the following trigger
>
> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId ON TestTable
> BEGIN
>  UPDATE UpdatedRowId Set Value = new.rowid;
> END;
>
> ...this update if fired only if I change rowid explicitly:
> ... UPDATE TestTable SET RowId=1236 WHERE RowId=1235
>  and not if I change the aliased field
> ... UPDATE TestTable SET Id=1236 WHERE Id=1235
>
> (checked in 3.7.4).

CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON TestTable
BEGIN
 UPDATE UpdatedRowId Set Value = new.rowid;
END;

>
> Maybe this functionality is rarely needed (googling "AFTER UPDATE OF RowId"
> leads to a single page in the whole web) so probably nobody noticed this yet
> or it does not work for temp triggers.
>
> I hope I just did something wrong...
>
> Max

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

Re: [sqlite] How SQLite manages result of a SELECT query?

[Sunil Bhardwaj]

Hi


Let me rephrase my question.

We are using in-memory db.

When we do "execQuery":
1. Does sqlite fetches complete result of query and keep it in separate memory
2. OR Does sqlite fetches result of query in chunks, when we use "sqlite3_step" 
and keep it in separate memory
3. OR Does sqlite fetches records one by one, when we use "sqlite3_step"

Please help us to understand, how can we use it efficiently. 

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ian Hardingham
Sent: Thursday, January 13, 2011 4:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How SQLite manages result of a SELECT query?

That seems like kind of a broad question.

On 13/01/2011 10:33, Sunil Bhardwaj wrote:
> Hi
>
> Please help us to understand, how SQLite manages result of a SELECT query:
>   - We are using in-memory db,
>   -
>   - When we do "execQuery", what operations are internally done in SQLite
>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhurima .
> Sent: Thursday, January 13, 2011 3:30 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
> I am working on embedded system and the device has linux kernel with sqlite 
> database. Wanted to know if the sqlite database can be partitioned with 
> secure and normal partitions.
>
> How can the encryption be achieved for sqlite database file in linux.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Thursday, January 13, 2011 3:18 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
>
> On 13 Jan 2011, at 6:08am, Madhurima . wrote:
>
>> Is it possible to have partition in SQLite database?
> The word 'partition' is used for more than one meaning.  You might be talking 
> about spreading the data about, or grouping results from a SELECT command.  
> Could you give an example or a reference to documentation ?
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> "DISCLAIMER: This message is proprietary to Aricent and is intended solely 
> for the use of the individual to whom it is addressed. It may contain 
> privileged or confidential information and should not be circulated or used 
> for any purpose other than for what it is intended. If you have received this 
> message in error, please notify the originator immediately. If you are not 
> the intended recipient, you are notified that you are strictly prohibited 
> from using, copying, altering, or disclosing the contents of this message. 
> Aricent accepts no responsibility for loss or damage arising from the use of 
> the information transmitted by this email including damage from virus."
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How SQLite manages result of a SELECT query?

[Ian Hardingham]

That seems like kind of a broad question.

On 13/01/2011 10:33, Sunil Bhardwaj wrote:
> Hi
>
> Please help us to understand, how SQLite manages result of a SELECT query:
>   - We are using in-memory db,
>   -
>   - When we do "execQuery", what operations are internally done in SQLite
>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhurima .
> Sent: Thursday, January 13, 2011 3:30 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
> I am working on embedded system and the device has linux kernel with sqlite 
> database. Wanted to know if the sqlite database can be partitioned with 
> secure and normal partitions.
>
> How can the encryption be achieved for sqlite database file in linux.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Thursday, January 13, 2011 3:18 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
>
> On 13 Jan 2011, at 6:08am, Madhurima . wrote:
>
>> Is it possible to have partition in SQLite database?
> The word 'partition' is used for more than one meaning.  You might be talking 
> about spreading the data about, or grouping results from a SELECT command.  
> Could you give an example or a reference to documentation ?
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> "DISCLAIMER: This message is proprietary to Aricent and is intended solely 
> for the use of the individual to whom it is addressed. It may contain 
> privileged or confidential information and should not be circulated or used 
> for any purpose other than for what it is intended. If you have received this 
> message in error, please notify the originator immediately. If you are not 
> the intended recipient, you are notified that you are strictly prohibited 
> from using, copying, altering, or disclosing the contents of this message. 
> Aricent accepts no responsibility for loss or damage arising from the use of 
> the information transmitted by this email including damage from virus."
> ___
> 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

[sqlite] How SQLite manages result of a SELECT query?

[Sunil Bhardwaj]

Hi

Please help us to understand, how SQLite manages result of a SELECT query:
- We are using in-memory db,
- 
- When we do "execQuery", what operations are internally done in SQLite

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Madhurima .
Sent: Thursday, January 13, 2011 3:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] partitioning SQLite database

I am working on embedded system and the device has linux kernel with sqlite 
database. Wanted to know if the sqlite database can be partitioned with secure 
and normal partitions.

How can the encryption be achieved for sqlite database file in linux.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, January 13, 2011 3:18 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] partitioning SQLite database


On 13 Jan 2011, at 6:08am, Madhurima . wrote:

> Is it possible to have partition in SQLite database?

The word 'partition' is used for more than one meaning.  You might be talking 
about spreading the data about, or grouping results from a SELECT command.  
Could you give an example or a reference to documentation ?

Simon.

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

"DISCLAIMER: This message is proprietary to Aricent and is intended solely for 
the use of the individual to whom it is addressed. It may contain privileged or 
confidential information and should not be circulated or used for any purpose 
other than for what it is intended. If you have received this message in error, 
please notify the originator immediately. If you are not the intended 
recipient, you are notified that you are strictly prohibited from using, 
copying, altering, or disclosing the contents of this message. Aricent accepts 
no responsibility for loss or damage arising from the use of the information 
transmitted by this email including damage from virus."
___
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] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 12:46 PM, Simon Slavin  wrote:

>
> On 13 Jan 2011, at 8:53am, Dan Kennedy wrote:
>
> > On 01/13/2011 02:31 AM, Max Vlasov wrote:
> >> Hi,
> >> for queries like UPDATE ... WHERE rowid=... one can in most cases reread
> the
> >> record (based on the rowid) and keep for example the cursor in the grid
> at
> >> the same record. But what if one of changed field is aliased to rowid,
> is
> >> there a way to find/track the changed record? In other words, how to
> find
> >> out the new rowid value for this record
> >
> >   CREATE TEMP TRIGGER...
>
> Genius.
>
> There appears to be no documentation on
>
> 
>
> about what the TEMP(ORARY) keyword does.
>
>

Simon, I also liked Dan's suggestion, now I'm trying to implement this, but
it seems that sqlite contains an interesting side effect or I don't know how
to call it.

if I create the following trigger

CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId ON TestTable
BEGIN
  UPDATE UpdatedRowId Set Value = new.rowid;
END;

...this update if fired only if I change rowid explicitly:
... UPDATE TestTable SET RowId=1236 WHERE RowId=1235
 and not if I change the aliased field
... UPDATE TestTable SET Id=1236 WHERE Id=1235

(checked in 3.7.4).

Maybe this functionality is rarely needed (googling "AFTER UPDATE OF RowId"
leads to a single page in the whole web) so probably nobody noticed this yet
or it does not work for temp triggers.

I hope I just did something wrong...

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

Re: [sqlite] partitioning SQLite database

[Richard Hipp]

On Thu, Jan 13, 2011 at 4:59 AM, Madhurima . wrote:

> I am working on embedded system and the device has linux kernel with sqlite
> database. Wanted to know if the sqlite database can be partitioned with
> secure and normal partitions.
>

You can create separate database files and then ATTACH them both to the same
database connection such that they function as a single database.  One
database can be encrypted, the other cleartext.


>
> How can the encryption be achieved for sqlite database file in linux.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Thursday, January 13, 2011 3:18 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] partitioning SQLite database
>
>
> On 13 Jan 2011, at 6:08am, Madhurima . wrote:
>
> > Is it possible to have partition in SQLite database?
>
> The word 'partition' is used for more than one meaning.  You might be
> talking about spreading the data about, or grouping results from a SELECT
> command.  Could you give an example or a reference to documentation ?
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> "DISCLAIMER: This message is proprietary to Aricent and is intended solely
> for the use of the individual to whom it is addressed. It may contain
> privileged or confidential information and should not be circulated or used
> for any purpose other than for what it is intended. If you have received
> this message in error, please notify the originator immediately. If you are
> not the intended recipient, you are notified that you are strictly
> prohibited from using, copying, altering, or disclosing the contents of this
> message. Aricent accepts no responsibility for loss or damage arising from
> the use of the information transmitted by this email including damage from
> virus."
> ___
> 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] partitioning SQLite database

[Simon Slavin]

On 13 Jan 2011, at 9:59am, Madhurima . wrote:

> I am working on embedded system and the device has linux kernel with sqlite 
> database. Wanted to know if the sqlite database can be partitioned with 
> secure and normal partitions.
> 
> How can the encryption be achieved for sqlite database file in linux.

Okay.  SQLite does not support that kind of partitioning.

You can buy some encryption addons for SQLite.  The best one is here:



This one requires no change to the programming at all, so you can develop your 
software with a plain database then switch to an encrypted one later.

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

Re: [sqlite] partitioning SQLite database

[Madhurima .]

I am working on embedded system and the device has linux kernel with sqlite 
database. Wanted to know if the sqlite database can be partitioned with secure 
and normal partitions.

How can the encryption be achieved for sqlite database file in linux.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, January 13, 2011 3:18 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] partitioning SQLite database


On 13 Jan 2011, at 6:08am, Madhurima . wrote:

> Is it possible to have partition in SQLite database?

The word 'partition' is used for more than one meaning.  You might be talking 
about spreading the data about, or grouping results from a SELECT command.  
Could you give an example or a reference to documentation ?

Simon.

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

"DISCLAIMER: This message is proprietary to Aricent and is intended solely for 
the use of the individual to whom it is addressed. It may contain privileged or 
confidential information and should not be circulated or used for any purpose 
other than for what it is intended. If you have received this message in error, 
please notify the originator immediately. If you are not the intended 
recipient, you are notified that you are strictly prohibited from using, 
copying, altering, or disclosing the contents of this message. Aricent accepts 
no responsibility for loss or damage arising from the use of the information 
transmitted by this email including damage from virus."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partitioning SQLite database

[Simon Slavin]

On 13 Jan 2011, at 6:08am, Madhurima . wrote:

> Is it possible to have partition in SQLite database?

The word 'partition' is used for more than one meaning.  You might be talking 
about spreading the data about, or grouping results from a SELECT command.  
Could you give an example or a reference to documentation ?

Simon.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Slavin]

On 13 Jan 2011, at 8:53am, Dan Kennedy wrote:

> On 01/13/2011 02:31 AM, Max Vlasov wrote:
>> Hi,
>> for queries like UPDATE ... WHERE rowid=... one can in most cases reread the
>> record (based on the rowid) and keep for example the cursor in the grid at
>> the same record. But what if one of changed field is aliased to rowid, is
>> there a way to find/track the changed record? In other words, how to find
>> out the new rowid value for this record
> 
>   CREATE TEMP TRIGGER...

Genius.

There appears to be no documentation on



about what the TEMP(ORARY) keyword does.

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

[sqlite] partitioning SQLite database

[Madhurima .]

Hi,

Is it possible to have partition in SQLite database?
What security measures does the SQLite provide?

Thanks
Madhurima


"DISCLAIMER: This message is proprietary to Aricent and is intended solely for 
the use of the individual to whom it is addressed. It may contain privileged or 
confidential information and should not be circulated or used for any purpose 
other than for what it is intended. If you have received this message in error, 
please notify the originator immediately. If you are not the intended 
recipient, you are notified that you are strictly prohibited from using, 
copying, altering, or disclosing the contents of this message. Aricent accepts 
no responsibility for loss or damage arising from the use of the information 
transmitted by this email including damage from virus."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] concatenate command in sqlite

[Simon Davies]

On 13 January 2011 07:34, amna shahzad  wrote:
> i have a  countries table
> create table countries ( country_name varchar2 (100));
>
> add some records
>
> insert into countries values ('Albania');
> insert into countries values ('Andorra');
> insert into countries values ('Antigua');
>
> query for concatenate records
>
> SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
> FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name
> ) rn,  COUNT (*) OVER () cnt  FROM countries)  WHERE rn = cnt  START
> WITH rn = 1  CONNECT BY rn = PRIOR rn +1;CSV
>
> and result is
>
> Albania,Andorra,Antigua
>
> this query works on oracle database..but i does not work on SQLITE
>
> please explain me
> How can I combine multiple rows into a comma-delimited list in SQLITE

http://www.sqlite.org/lang_aggfunc.html#groupconcat

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

[sqlite] concatenate command in sqlite

[amna shahzad]

i have a  countries table
create table countries ( country_name varchar2 (100));

add some records

insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');

query for concatenate records

SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name
) rn,  COUNT (*) OVER () cnt  FROM countries)  WHERE rn = cnt  START
WITH rn = 1  CONNECT BY rn = PRIOR rn +1;CSV

and result is

Albania,Andorra,Antigua

this query works on oracle database..but i does not work on SQLITE

please explain me
How can I combine multiple rows into a comma-delimited list in SQLITE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] partitioning SQLite database

[Madhurima .]

Hi,

Is it possible to have partition in SQLite database?
What security measures does the SQLite provide?

Thanks
Madhurima


"DISCLAIMER: This message is proprietary to Aricent and is intended solely for 
the use of the individual to whom it is addressed. It may contain privileged or 
confidential information and should not be circulated or used for any purpose 
other than for what it is intended. If you have received this message in error, 
please notify the originator immediately. If you are not the intended 
recipient, you are notified that you are strictly prohibited from using, 
copying, altering, or disclosing the contents of this message. Aricent accepts 
no responsibility for loss or damage arising from the use of the information 
transmitted by this email including damage from virus."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to track a record if the aliased id is changed

[Dan Kennedy]

On 01/13/2011 02:31 AM, Max Vlasov wrote:
> Hi,
> for queries like UPDATE ... WHERE rowid=... one can in most cases reread the
> record (based on the rowid) and keep for example the cursor in the grid at
> the same record. But what if one of changed field is aliased to rowid, is
> there a way to find/track the changed record? In other words, how to find
> out the new rowid value for this record

   CREATE TEMP TRIGGER...

> I thought about a partial solution: to track that the rowid used in the
> query is no longer exists so there was a change to some new value, but what
> the value is unknown.
>
> There's also pragma table_info returning pk info, and I probably can track
> the aliased fields but there's at least one exception in the docs...
>
> Any thoughts?
>
> Thanks,
>
> Max Vlasov
> ___
> 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] How to track a record if the aliased id is changed

[Kees Nuyt]

On Thu, 13 Jan 2011 09:44:06 +0100, Kees Nuyt 
wrote:

>or even 
>SELECT ROWID,
>  FROM atable ... ;

oops, make that

or even 
SELECT ROWID,*
  FROM atable ... ;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to track a record if the aliased id is changed

[Kees Nuyt]

On Thu, 13 Jan 2011 08:30:05 +0300, Max Vlasov
 wrote:

[...snip...]


>Maybe I didn't explain fully my situation. Sorry If I repeat some things
>already clarified, but it's also for Nicolas, who answered here too.
>
>It was about a single row change in an editable grid control (not some
>random user query). So the user is at some row of this grid changing values
>in the cells and making some 'apply" after changing. This apply ends with
>final "Update table set value1=...,value2=... where rowid=..." and  the
>"rowid=" here because it's the best way to identify a row from the table for
>deleting and updating. After that apply I want to allow user to repeat the
>same steps (moving cursor from cell to cell, changing and pressing apply).
>Everything is ok if rowid is unaffected by the cells change (I just have
>full set of rowids for the whole table), but if some of cells represents the
>aliased rowid, this row becomes invalid after the change and I no longer can
>identify it for any next change. If I simply reread all the data (I don't
>want to since sometimes it takes time, but let's assume), I still won't be
>able to place the cursor at the changed row since with rowid changes it will
>no longer point to a valid row.
>
>To Nikolas Williams: I'd be glad to forbid rowid changes, but they're from
>virtually unknown aliased column.
>
>Max

A suggestion (untested):
Include a rowid column in your grid, but don't display it.
The user would be able to edit the rowid alias, if any, but not the
hidden rowid itself.

Your table selects would look like

SELECT ROWID,col1,col2,col3,... 
  FROM atable 
 WHERE ...
 ORDER BY ...;

or even 
SELECT ROWID,
  FROM atable ... ;

and the update would have:
UPDATE atable SET 
 WHERE ROWID=$hiddenvalueingrid;

You would have to check with PRAGMA table_info(atable) whether or
not the special name ROWID is used by a user defined column, and if
so, use one of the other special names, i.e. _rowid_ or oid in your
select and update statements.

See also:
http://www.sqlite.org/lang_createtable.html#rowid

HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users