date:20140717

how to set a password to open a sqlite  db in sqlite browser


On Thu, Jul 17, 2014 at 7:18 AM, RSmith  wrote:

>
> On 2014/07/16 14:55, Rob Willett wrote:
>
>  I’ll second what Simon says, I use the very same technique for a table
>> with 4M+ records in and its so fast I thought I had an error and looked for
>> bugs in my code.  I >assumed (incorrectly) that it would be very slow, It
>> isn’t.
>>
>
> In a similar fashion I had made this system for basically loading CSV
> files into an SQLite DB, then running all kinds of rules on it (which I
> could make with a designer) such as search-replace, substitute column
> values, do checks, delete rows with empty values in a certain column, etc.
> etc. and then finally export it to a CSV again, all basically streamlining
> a datafeed alteration process into a one-click thing. The viewer I used was
> based on a method discussed in another thread where the virtual view would
> get actual data only for items in the visible field by primary key. I
> tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and
> here was my surprise, whether I looked at the top of the list, or the
> bottom, or anywhere else, the data retrieval was instantaneous - retrieving
> a page worth of records at whatever speed I can scroll the vertical
> scrollbar - not a single slow-down as I got further down or indeed any
> other slowness. I have come to expect great performance from SQLite as a
> standard, but I am still often surprised at just how quick it can be.
>
> (btw: This app is freely shared if anyone needs something of the kind or
> fancy testing the above, just mail me)
>
>  ...// I have come to realise that the people who answer here are real
>> experts, I will not embarrass them by naming names//...
>>
>
> Oh don't worry, we know exactly who you mean... ;)
>
> It's Igor right?
>
> We all want to be like Igor when we grow up... *sighs dreamily*
>
>
>
>
>
> ___
> 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] sqlite & multi process usage

2014-07-17 Thread Micka

Well,

All of my process are using Mutex to protect sqlite from concurrent access.

That why I don't understand why I get SQLITE_BUSY error .

Micka,


On Tue, Jul 8, 2014 at 11:44 PM, Simon Slavin  wrote:

>
> On 8 Jul 2014, at 8:39pm, Micka  wrote:
>
> > i'm using sqlite in different process. Sometimes I got the error Busy
> ... =>
> >
> > When SQLite tries to access a file that is locked by another process, the
> > default behavior is to return SQLITE_BUSY.
> >
> >
> > So how can I prevent that ?
>
> Have you set a timeout, so that SQLite can back off for a little time then
> try again ?
>
> Use either
>
> 
>
> or
>
> 
>
> 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] sqlite db transfer

i sent a  sqlite db from server to client  using c program .. in this how i
set a password for the db to open in sqlite browser...


On Fri, May 9, 2014 at 6:05 PM, Simon Slavin  wrote:

>
> On 9 May 2014, at 1:23pm, Sky Meena  wrote:
>
> > i working in server client... c program. i need to transfer db from
> server
> > to client. in udp socket .. i to send a db.
>
> SQLite does not involve a server or a client.  All processing and access
> of the database is done inside your application.  It does not communicate
> over IP, or use a socket, or anything like that.
>
> You can write your own server if you want, and many people have.  Or if
> all you want is to send an entire database, you can use any method which
> would send a text file from one computer to another.  For SQLite, if no
> program is accessing a database then the database is just one file.
>
> 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] sqlite & multi process usage

On 17 Jul 2014, at 10:10am, Micka  wrote:

> Well,
> 
> All of my process are using Mutex to protect sqlite from concurrent access.
> 
> That why I don't understand why I get SQLITE_BUSY error .
> 
> Micka,
> 
> 
> On Tue, Jul 8, 2014 at 11:44 PM, Simon Slavin  wrote:
> 
>> Have you set a timeout

Did you set a timeout period for that connection ?

If you didn't set a timeout, then the database API call will try once to open 
the file, find that it has a MUTEX lock, and fail, giving you SQLITE_BUSY.

If you do set a timeout, then the database API call will try once to open the 
file, find that it has a MUTEX lock, and keep checking the lock until your 
timeout period has passed.

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

Re: [sqlite] sqlite db transfer

On 17 Jul 2014, at 10:47am, Sky Meena  wrote:

> i sent a  sqlite db from server to client  using c program 

SQLite does not involve a server.  It runs on one computer and takes 
instructions only from that computer.

> .. in this how i
> set a password for the db to open in sqlite browser...

SQLite Database Browser cannot handle password-protected databases, as far as I 
know.  Use a different tool.

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

Re: [sqlite] sqlite db transfer

2014-07-17 Thread Kees Nuyt

On Thu, 17 Jul 2014 15:17:07 +0530, Sky Meena 
wrote:

> i sent a  sqlite db from server to client  using c program .. in this how i
> set a password for the db to open in sqlite browser...

In short: you can't. 

SQLite does not implement SQL access control (GRANT/REVOKE). The only
protection you can rely on are the access control features of the
filesystem the database resides in.

You could buy a licence for the SQLite encryption extension (named SEE),
 , which enable you to encrypt
the database, but I doubt sqlite browser would be able to open an
encrypted SQLite database.

Hope this helps

-- 
Regards,

Kees Nuyt

>On Fri, May 9, 2014 at 6:05 PM, Simon Slavin  wrote:
>
>>
>> On 9 May 2014, at 1:23pm, Sky Meena  wrote:
>>
>> > i working in server client... c program. i need to transfer db from
>> server
>> > to client. in udp socket .. i to send a db.
>>
>> SQLite does not involve a server or a client.  All processing and access
>> of the database is done inside your application.  It does not communicate
>> over IP, or use a socket, or anything like that.
>>
>> You can write your own server if you want, and many people have.  Or if
>> all you want is to send an entire database, you can use any method which
>> would send a text file from one computer to another.  For SQLite, if no
>> program is accessing a database then the database is just one file.
>>
>> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Setting boundaries in a search

2014-07-17 Thread Kees Nuyt

On Thu, 17 Jul 2014 13:06:36 +0530, Sky Meena 
wrote:

> how to set a password to open a sqlite  db in sqlite browser

Answered in a different thread 2 minutes ago.


-- 
Regards,

Kees Nuyt



>On Thu, Jul 17, 2014 at 7:18 AM, RSmith  wrote:
>
>>
>> On 2014/07/16 14:55, Rob Willett wrote:
>>
>>  I’ll second what Simon says, I use the very same technique for a table
>>> with 4M+ records in and its so fast I thought I had an error and looked for
>>> bugs in my code.  I >assumed (incorrectly) that it would be very slow, It
>>> isn’t.
>>>
>>
>> In a similar fashion I had made this system for basically loading CSV
>> files into an SQLite DB, then running all kinds of rules on it (which I
>> could make with a designer) such as search-replace, substitute column
>> values, do checks, delete rows with empty values in a certain column, etc.
>> etc. and then finally export it to a CSV again, all basically streamlining
>> a datafeed alteration process into a one-click thing. The viewer I used was
>> based on a method discussed in another thread where the virtual view would
>> get actual data only for items in the visible field by primary key. I
>> tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and
>> here was my surprise, whether I looked at the top of the list, or the
>> bottom, or anywhere else, the data retrieval was instantaneous - retrieving
>> a page worth of records at whatever speed I can scroll the vertical
>> scrollbar - not a single slow-down as I got further down or indeed any
>> other slowness. I have come to expect great performance from SQLite as a
>> standard, but I am still often surprised at just how quick it can be.
>>
>> (btw: This app is freely shared if anyone needs something of the kind or
>> fancy testing the above, just mail me)
>>
>>  ...// I have come to realise that the people who answer here are real
>>> experts, I will not embarrass them by naming names//...
>>>
>>
>> Oh don't worry, we know exactly who you mean... ;)
>>
>> It's Igor right?
>>
>> We all want to be like Igor when we grow up... *sighs dreamily*
>>
>>
>>
>>
>>
>> ___
>> 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] sqlite db transfer

i will explain.. i created a sqlite db.. now i should tranfer it also
transfered.. while opening a db it should ask password..  for that while
creating db i should enable some password or read only mode..


On Thu, Jul 17, 2014 at 4:27 PM, Kees Nuyt  wrote:

> On Thu, 17 Jul 2014 15:17:07 +0530, Sky Meena 
> wrote:
>
> > i sent a  sqlite db from server to client  using c program .. in this
> how i
> > set a password for the db to open in sqlite browser...
>
> In short: you can't.
>
> SQLite does not implement SQL access control (GRANT/REVOKE). The only
> protection you can rely on are the access control features of the
> filesystem the database resides in.
>
> You could buy a licence for the SQLite encryption extension (named SEE),
>  , which enable you to encrypt
> the database, but I doubt sqlite browser would be able to open an
> encrypted SQLite database.
>
> Hope this helps
>
> --
> Regards,
>
> Kees Nuyt
>
> >On Fri, May 9, 2014 at 6:05 PM, Simon Slavin 
> wrote:
> >
> >>
> >> On 9 May 2014, at 1:23pm, Sky Meena  wrote:
> >>
> >> > i working in server client... c program. i need to transfer db from
> >> server
> >> > to client. in udp socket .. i to send a db.
> >>
> >> SQLite does not involve a server or a client.  All processing and access
> >> of the database is done inside your application.  It does not
> communicate
> >> over IP, or use a socket, or anything like that.
> >>
> >> You can write your own server if you want, and many people have.  Or if
> >> all you want is to send an entire database, you can use any method which
> >> would send a text file from one computer to another.  For SQLite, if no
> >> program is accessing a database then the database is just one file.
> >>
> >> 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] sqlite db transfer

thank you.. how can i use sqlite_db_readonly function..


On Thu, Jul 17, 2014 at 5:40 PM, Sky Meena  wrote:

> i will explain.. i created a sqlite db.. now i should tranfer it also
> transfered.. while opening a db it should ask password..  for that while
> creating db i should enable some password or read only mode..
>
>
> On Thu, Jul 17, 2014 at 4:27 PM, Kees Nuyt  wrote:
>
>> On Thu, 17 Jul 2014 15:17:07 +0530, Sky Meena 
>> wrote:
>>
>> > i sent a  sqlite db from server to client  using c program .. in this
>> how i
>> > set a password for the db to open in sqlite browser...
>>
>> In short: you can't.
>>
>> SQLite does not implement SQL access control (GRANT/REVOKE). The only
>> protection you can rely on are the access control features of the
>> filesystem the database resides in.
>>
>> You could buy a licence for the SQLite encryption extension (named SEE),
>>  , which enable you to encrypt
>> the database, but I doubt sqlite browser would be able to open an
>> encrypted SQLite database.
>>
>> Hope this helps
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>>
>> >On Fri, May 9, 2014 at 6:05 PM, Simon Slavin 
>> wrote:
>> >
>> >>
>> >> On 9 May 2014, at 1:23pm, Sky Meena  wrote:
>> >>
>> >> > i working in server client... c program. i need to transfer db from
>> >> server
>> >> > to client. in udp socket .. i to send a db.
>> >>
>> >> SQLite does not involve a server or a client.  All processing and
>> access
>> >> of the database is done inside your application.  It does not
>> communicate
>> >> over IP, or use a socket, or anything like that.
>> >>
>> >> You can write your own server if you want, and many people have.  Or if
>> >> all you want is to send an entire database, you can use any method
>> which
>> >> would send a text file from one computer to another.  For SQLite, if no
>> >> program is accessing a database then the database is just one file.
>> >>
>> >> 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] sqlite db transfer

Hi Sky,

The read-only mode for opening the database is simply you telling the sqlite engine that you do not intend to write to this database
and so it doesn't try to acquire write-locks on it during the life of that connection, and your connection cannot make changes to it
in any way (even if the user maybe invokes a query that tries to change data). It is a safety measure and sometimes a speed increase
or concurrency helping method, but this is not preventing anyone else or any other program or access method from writing to the
database, it is only a restriction for a single connection under your control.

Access to SQLite DBs depend on exactly the access to the file, whomever can access the file can read/alter/delete it. To lock a db
so that nobody else can alter it you have to encrypt the file itself for which one official solution is the SEE extension which
works very well, as Kees mentioned in a previous post.

SQLite does not do Data/User-level access control on top of a normal connection as others stated, nor pretends to. PostGRES/MySQL
etc. are viable alternatives for this, especially since you mention copying the file between server/client, might a client-server
based engine would work better for you?

A brilliant easy-to-install MySQL installation (bundled with an PHP+Apache web service which you can ignore) that almost mimics a
stand-alone DB engine and can be used for all of Linux/WIndows/Mac can be found from the friends of Apache folks, called XAMPP.

https://www.apachefriends.org/index.html

On 2014/07/17 14:17, Sky Meena wrote:

thank you.. how can i use sqlite_db_readonly function..

On Thu, Jul 17, 2014 at 5:40 PM, Sky Meena wrote:

i will explain.. i created a sqlite db.. now i should tranfer it also
transfered.. while opening a db it should ask password.. for that while
creating db i should enable some password or read only mode..

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

[sqlite] Vacuum command fails

I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
with an error "out of memory". I was under the impression that it copies the 
database file and then performs vacuum on that file. Here it looks like its 
using RAM. Appreciate your inputs from the experts.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails


On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory". I was under the impression that it copies the 
> database file and then performs vacuum on that file. Here it looks like its 
> using RAM.

What's your

PRAGMA temp_store

set to ?

Simon.

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

[sqlite] An Explain Query Plan that I cannot explain

2014-07-17 Thread Staffan Tylen

I've got a view VJU covering 8 tables T1-T8. They all share a column C1
which is indexed in each table:

CREATE VIEW VJU AS
SELECT * FROM T1
LEFT JOIN T2 USING (C1)
LEFT JOIN T3 USING (C1)
LEFT JOIN T4 USING (C1)
LEFT JOIN T5 USING (C1)
LEFT JOIN T6 USING (C1)
LEFT JOIN T7 USING (C1)
LEFT JOIN T8 USING (C1);

After experiencing a performance issue I ran:

EXPLAIN QUERY PLAN SELECT * FROM VJU WHERE C1='ABC';

with the following result:

SEARCH TABLE T1 USING INDEX T1_C1_IDX (C1=?)
SCAN TABLE T2
SEARCH TABLE T3 USING INDEX T3_C1_IDX (C1=?)
SEARCH TABLE T4 USING INDEX T4_C1_IDX (C1=?)
SEARCH TABLE T5 USING INDEX T5_C1_IDX (C1=?)
SEARCH TABLE T6 USING INDEX T6_C1_IDX (C1=?)
SEARCH TABLE T7 USING INDEX T7_C1_IDX (C1=?)
SEARCH TABLE T8 USING INDEX T8_C1_IDX (C1=?)

I'm unable to understand why T2 isn't using the index. Is it the fact that
the table T2 only has 2 rows so SQLite has decided that it's quicker with a
scan?

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

Re: [sqlite] An Explain Query Plan that I cannot explain

On 17 Jul 2014, at 6:34pm, Staffan Tylen  wrote:

> I'm unable to understand why T2 isn't using the index. Is it the fact that
> the table T2 only has 2 rows so SQLite has decided that it's quicker with a
> scan?

Does doing an ANALYZE change anything ?

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

Re: [sqlite] Vacuum command fails

When i execute the command PRAGMA temp_store, it returned me 0. What is the 
ideal value that needs to be set?

On Thursday, 17 July 2014 10:30 AM, Simon Slavin  wrote:

On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory". I was under the impression that it copies the 
> database file and then performs vacuum on that file. Here it looks like its 
> using RAM.

What's your

PRAGMA temp_store

set to ?

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

Re: [sqlite] Setting boundaries in a search



On 2014/07/17 03:48, RSmith wrote:


In a similar fashion I had made this system for basically loading CSV files into an SQLite DB, then running all kinds of rules on 
it//..


(btw: This app is freely shared if anyone needs something of the kind or fancy 
testing the above, just mail me)


I have received more requests than anticipated for this, it seems many people use CSV type data transfers. I've responded to some 
and then decided it is probably easier to just upload and link it, if I have not responded to anyone specifically, apologies, but 
herewith the link - thanks.


http://www.rifin.co.za/software/utils/RCSVServ.zip

A Readme is included, but as it is (was) mostly intended for internal use, there is not a lot of other Help included, though after a 
cursory glance at the readme, everything else should be intuitive, please feel free to mail any questions directly as I don't wish 
to occupy this forum/thread with it.


The app is Win-32-bit and you can drop in your own dll if needed.


Thank you and have a great day!
Ryan

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

Re: [sqlite] Vacuum command fails

2014-07-17 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/14 10:42, veeresh kumar wrote:
> When i execute the command PRAGMA temp_store, it returned me 0. What is
> the ideal value that needs to be set?

A quick google search would have found the answer:

  https://sqlite.org/pragma.html#pragma_temp_store

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlPIH8wACgkQmOOfHg372QRtLwCfYUzGhB4UKejmTT0qcVRRHNQy
bRgAn2MdaOspER3bgVcwlgKjLq3G8akr
=B7gd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Keeping a history of structured data

2014-07-17 Thread Tristan Van Berkom


Hi all,

We've been pondering how to keep history of structured records without
storing the entirety of the structured record for each new revision, we
have some ideas on how to achieve this but I wanted to consult this list
in case we have overlooked some other strategies, and also just to see
if our proposed solution to the problem is sound.

First, here is a brief description of the nature of the data and the
requirements of it's usage, it revolves around the notion of 'Events'
which can have 'Participants'.

One record:


 E
/|\
   / | \
  P P P...

One can assume that 'E' is a table of 'Events' where each Event has
a uid, and that 'P' is a table of participants which refer to the
'Event' uid they belong to (each participant consequently has an 'id'
which is only unique per 'Event' uid).

The objective is to keep a revisioned history of 'E' whenever 'E' has
changed, or any of it's 'P' counterparts have changed, ideally without
storing a duplicate row for the one 'E' and every one of its 'P's every
time anything changes.

The requirements are that:

 a.) The current revision of a given 'E' (Event) and it's
 'P' (Participant) counterparts be quickly searchable.

 b.) Every change to a given 'E', or any of it's 'P' counterparts
 must be recorded (may be implemented with triggers, may not
 be, the method of this is not a requirement).

 c.) The entirety of 'E' and every 'P' should not be duplicated for
 a modification of only the 'E' or any individual 'P' which belongs
 to the given 'E'.

 d.) Any 'E' can be queried for it's history (i.e. the full structured
 record of 'E' and all 'P' counterparts can be reconstructed for
 every version), the history of 'E' can be listed.

Our tentative solution for this flows as follows:

 1.) All history of 'E' and 'P' records are to be stored in
 separate tables - this is to satisfy the requirement 'a'
 by reducing the complexity of joining tables 'E' and 'P'
 (i.e. when querying, we are only interested in the latest
 revision).

 Let us refer to these tables as 'E(h)' and 'P(h)', i.e.
 the history of E and the history of P.


 2.) Every 'E' and every 'P' has it's own local revision

 Every 'E' revision must be unique for a given 'E' and every
 'P' revision must be unique for a given 'P's participation
 in a given 'E' (there is no other constraint on this revision
 except for it's uniqueness).


 3.) Another revision exists which is the master revision of the
 conceptually structured record 'E'.

 The nature of this revision is very specific, it is an integer
 which starts at 0 whenever any new 'E' is introduced, and it is
 incremented whenever the structured record 'E' changes in any
 way (I.e. the master revision is incremented if 'E' changes or
 if one or more of it's related 'P's change, or both).

 One row per 'E' will be stored in a separate table indicating
 the "latest" revision of any given 'E'. Let's refer to this
 table as 'E(r)', the "revision of E".


 4.) We will require 2 more tables to reconstruct the records for
 a given revision of the structured record:

   o The Event History Index contains a local revision / master
 revision tuple for every possible master revision of 'E', we
 shall refer to this as: E(hi)

   o The Participant History Index contains a local / master
 revision tuple for every possible master revision of 'E',
 it differs only from the above as it holds one revision per
 'P' of a given 'E'. Let's refer to this table as: P(hi)

 This table will obviously grow in proportion to the history,
 however as it is constructed mostly of master revisions (integers)
 and foreign keys, it should be an order of magnitude less than
 simply duplicating everything for every modification.


 5.) When a modification is made to 'E' or any of it's 'P's, the
 following will occur:

  o If the 'E' has changed:
 o Create a new copy of 'E' in 'E(h)'
 o Set a new local revision of 'E'
  o If any 'P' has changed:
 o Create a new copy of 'P' in 'P(h)'
 o Set a new local revision of 'P'

  o Create one entry in 'E(hi)' mapping the global revision to
it's local revision
  o Create one entry for every related 'P' in 'P(hi)' mapping
their local revisions to the current master revision

  o Increment the master revision for 'E' in 'E(r)', the current
data in tables 'E' and 'P' are considered to be at the revision
indicated by 'E(r)'


 6.) When listing the history of a given 'E' the following will occur:

  o Obtain the current master revision of 'E' from 'E(r)',
let's call it $current

  o If $current is 0, there is no history yet, abort

  o Otherwise, let $rev be each revision from ($current - 1)
down to 0

  o Construct a complete structured record of 'E' for every

Re: [sqlite] Vacuum command fails

2014-07-17 Thread Luuk


On 17-7-2014 21:11, Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/14 10:42, veeresh kumar wrote:

When i execute the command PRAGMA temp_store, it returned me 0. What is
the ideal value that needs to be set?


A quick google search would have found the answer:

   https://sqlite.org/pragma.html#pragma_temp_store



so, 0 means temp is written to disk.

Back to the the question.

How much free disk space is there?

I think you need more than 14Gb of free space if your database is 14Gb 
in size.


http://lmgtfy.com/?q=sqlite+vacuum+how+much+disk+space+is+needed&l=1

"This means that when VACUUMing a database, as much as twice the size of 
the original database file is required in free disk space. "

(source: http://sqlite.org/lang_vacuum.html)


So, you will need 28Gb of free disk space..
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

2014-07-17 Thread Nelson, Erik - 2


Luuk wrote:

>so, 0 means temp is written to disk.

>Back to the the question.

>How much free disk space is there?

>I think you need more than 14Gb of free space if your database is 14Gb in size.

>http://lmgtfy.com/?q=sqlite+vacuum+how+much+disk+space+is+needed&l=1

>"This means that when VACUUMing a database, as much as twice the size of the 
>original database file is required in free disk space. "

>So, you will need 28Gb of free disk space..


Sometimes *where* the free space is makes a difference... for example, if the 
temporary file is generated in /tmp, then /tmp needs to have sufficient free 
space.  In my experience, /tmp is frequently too small to vacuum large 
databases.  The tmp location can be controlled by a (deprecated, IIRC, and 
hopefully never removed) pragma.

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory".

What kind of error is this ?  Are you getting SQLITE_NOMEM returned from a 
SQLite call or is this being returned from some part of your operating system ?

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

Re: [sqlite] Keeping a history of structured data

On 17 Jul 2014, at 8:43pm, Tristan Van Berkom  wrote:

> The objective is to keep a revisioned history of 'E' whenever 'E' has
> changed, or any of it's 'P' counterparts have changed, ideally without
> storing a duplicate row for the one 'E' and every one of its 'P's every
> time anything changes.

I assume that an event is created knowing what participants it has, and that 
these participants never change over the lifetime of the events.

Most of your design criteria depend on knowing these two things:

(A) Are you making few changes and doing lots of SELECTing, or making lots and 
lots of changes and only occasionally needing to look things up ?

(B) When you do SELECTing, do you mostly need to know about the data as it is 
right now, or are most SELECTs about previous versions and not the current 
version ?

Once you know those two things you can decide whether most of the work should 
be done when a change occurs, or whether you should make noting a change as 
easy as possibly, and do all the hard work when you need to look things up.

You will also know whether you need just a copy of each row with the 
change-timestamp, or whether it's useful to keep another copy with just 'data 
as it is now' in it for fast reference when you need current data.

> SELECT * FROM 'E(h)'

Just a warning that you can't use single quotes as identifiers in SQLite.  Nor 
can you have the name of a table as a variable.

Apart from that, you seem to have figured out all the usable strategies, you 
just need to know how the data will be used to pick between them.

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

Re: [sqlite] Vacuum command fails

I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store = 
FILE). But it did not help. I get SQLITE_NOMEM error message.

Its not a space issue as I do have enough space in the hard drive. Whenver 
Vacuum command is executed, its using the RAM and thats were it goes out of 
memory. 

On Thursday, 17 July 2014 12:11 PM, Roger Binns  wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/14 10:42, veeresh kumar wrote:
> When i execute the command PRAGMA temp_store, it returned me 0. What is
> the ideal value that needs to be set?

A quick google search would have found the answer:

  https://sqlite.org/pragma.html#pragma_temp_store

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlPIH8wACgkQmOOfHg372QRtLwCfYUzGhB4UKejmTT0qcVRRHNQy
bRgAn2MdaOspER3bgVcwlgKjLq3G8akr
=B7gd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

On 17 Jul 2014, at 10:03pm, veeresh kumar  wrote:

> I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store 
> = FILE). But it did not help. I get SQLITE_NOMEM error message.

What version of SQLite are you using ?  The simplest way is probably to tell us 
the result of "sqlite_version()" give by your own application.

Are you using any PRAGMAs at all in your code ?  In other words, can we assume 
that all PRAGMAs are set to their defaults ?

If you open your database in the SQLite shell tool and execute "PRAGMA 
integrity_check;" on it, do you get an error ?

If you open your database in the SQLite shell tool and execute "VACUUM;" on it, 
do you still get an error ?

> Its not a space issue as I do have enough space in the hard drive.

Just to make this clear, do you have at least three times as much free space on 
your hard drive as the size of the database ?

I am sorry for these detailed questions which may seem to be attacking you, but 
we have run out of obvious causes for this problem and are trying to check all 
the unlikely things.

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

Re: [sqlite] Vacuum command fails



On 2014/07/17 23:03, veeresh kumar wrote:

I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store = 
FILE). But it did not help. I get SQLITE_NOMEM error message.


Might I offer one more possible (although probably not very likely) problem - I have just recently posted an experiment in memory 
usage where I had the SQLITE_NOMEM error returned after trying to execute a query for which the cache size limit exceeded available 
system resources in 32-bit mode of the test software. Could it be your page size multiplied by the cache pages (both selectable or 
viewable via their respective pragmas) exceeds about 2GB? If so a 14GB Vacuum would probably try to use a lot of memory and if the 
cache limit exceeds the resource limit that error might happen.


Not very likely, but possible. Good luck!




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

[sqlite] Copy rows in same table

2014-07-17 Thread Prashanth Anil Mascarenhas

Hi All,

I want to copy a row in same table. How can I achieve this?

Eg : Table(ID,name,place,comments)

1 ABD 121 None
2 ASD 141 None

Now I want to copy the table ID 2 to new table ID 3using sqlite3 commands.



-- 
Regards,
Prashanth Anil Mascarenhas,
+918904231492,
Sankalp Semiconductor Pvt Ltd.

-- 
Disclaimer : Privileged and Confidential information is contained in this e 
mail and/or attachments and is subject to legal privilege. This e mail is 
intended solely for the recipient/s mentioned above. If you are not the 
intended recipient or responsible for delivery of the message to the 
intended recipient you may not use, copy, distribute or deliver to anyone 
this e mail and/or any attachments or any part of its contents or take any 
action in reliance on it. In such case, you should destroy this message, 
and notify us immediately. All reasonable precautions have been taken to 
ensure no viruses are present in this e-mail. Our company is not liable for 
any loss arising from unauthorized access to our company's email account by 
any third party and/or any loss caused due to the transmission of any 
viruses. The views, opinions, conclusions and other information expressed 
in this electronic mail that do not relate to the company's business shall 
be deemed to be the personal opinion of the sender and not endorsed by the 
company.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [bug] Optimizer chooses suboptimal plan for correlated subqueries containing tables with zero rows

2014-07-17 Thread Stephen Broberg

Hi,

We encountered an issue in our system where we had two nearly identical 
subqueries in a statement, which differed only in one table (both of which had 
the same definition).  One table had 4 rows, the other table had zero rows.  
The subquery with the 4-row table ran about 10,000 times faster than the 
subquery with the zero-row table.  ANALYZE had been run on this schema, and the 
problem existed on sqlite version 3.7.12 up through 3.8.5 (running on Mac OS 
10.8), and exists whether compiling with SQLITE_ENABLE_STAT3 or not.

After much analysis, it turns out the problem was "fixed" by inserting one row 
into the empty table, running ANALYZE, then removing the row.  Without this 
step, there was no row in sqlite_stat1, and the optimizer used assumed 100,000 
rows for the empty table.

My naïve understanding of the sqlite engine would make me think that the 
problem lies in not storing a stats row for zero-sized tables, resulting in the 
optimizer making the unnecessary assumption.

I've included a script below that will reproduce the problem.  The poor 
performance isn't as dramatic as our production case, likely because the tables 
are not as wide as ours, so the cost of visiting rows is reduced, so I added 
some nonsensical string operations to the select clause to burn cpu.  In our 
code, the timing for the optimal query was "CPU Time: user 0.000142 sys 
0.72" and the suboptimal one was "CPU Time: user 1.021453 sys 0.036893"

Note that several conditions must exist to produce this perfect storm:

1.  The subquery needs to contain several tables besides the empty one
2.  The stats computed for the schema are as described above
3.  Covering indexes exist for the correlation keys.

Here are query plans & times produced my script:

 Query against the subquery with 4-row table 
selectidorder   fromdetail
--  --  --  
-
0   0   0   SEARCH TABLE child AS mc USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
0   1   1   SEARCH TABLE parent AS mp USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SCAN TABLE somerows AS s (~4 rows)
1   1   1   SEARCH TABLE child AS c USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
1   2   2   SEARCH TABLE parent AS p USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
CPU Time: user 0.000183 sys 0.000106

 Query against the subquery with zero-row table Note that it runs about 150 
times slower than the first case 
selectidorder   fromdetail
--  --  --  
-
0   0   0   SEARCH TABLE child AS mc USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
0   1   1   SEARCH TABLE parent AS mp USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   2   SEARCH TABLE parent AS p USING COVERING 
INDEX ak_p (gp_id=?) (~100 rows)
1   1   1   SEARCH TABLE child AS c USING COVERING 
INDEX ak_c (p_id=?) (~50 rows)
1   2   0   SEARCH TABLE norows AS n USING COVERING 
INDEX ak_norows (c_id=?) (~1 rows)
CPU Time: user 0.026749 sys 0.000396

 Query against the subquery with zero-row table after stats were generated 
with a single row in the table  
 Note that now the query plan is the same as in the "correct" case  
selectidorder   fromdetail
--  --  --  
-
0   0   0   SEARCH TABLE child AS mc USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
0   1   1   SEARCH TABLE parent AS mp USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SCAN TABLE norows AS n (~1 rows)
1   1   1   SEARCH TABLE child AS c USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
1   2   2   SEARCH TABLE parent AS p USING INTEGER 
PRIMARY KEY (rowid=?) (~1 rows)
CPU Time: user 0.000173 sys 0.99



==
=== Steps to Reproduce ===
==

Run the attached bash script and redirect to sqlite, like so:

./testdata.sh | sqlite3 test.db

The script generates a db with a million-row table; 15 rows (by choosing 
100/50/30 values for the i/j/k loops) is enough to illustrate the problem as 
well, but the results are not as dramatic.

=
== testdata.sh ==
=
#!/bin/bash
echo 'cre

Re: [sqlite] comma-separated string data

2014-07-17 Thread supriya1890

Hi,

Say the scenario is 

column value = [1,2,33,45,66]
u want to compare value x with the column and retrieve data then use the
condition,

value = 'x' OR value LIKE 'x,%' OR value LIKE '%,x,%' OR value LIKE '%,x'

it should work in most of the cases (y)

Thanks,
Supriya



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/comma-separated-string-data-tp74926p76757.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] comma-separated string data

2014-07-17 Thread supriya1890

Hi,

Say the scenario is 

column value = [1,2,33,45,66]
u want to compare value x with the column and retrieve data then use the
condition,

value = 'x' OR value LIKE 'x,%' OR value LIKE '%,x,%' OR value LIKE '%,x'

it should work in most of the cases (y)

Thanks,
Supriya



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/comma-separated-string-data-tp74926p76758.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] Keeping a history of structured data

2014-07-17 Thread Pavlos Christoforou

On 17 July 2014 21:43, Tristan Van Berkom  wrote:
>
> Hi all,
>
> We've been pondering how to keep history of structured records without
> storing the entirety of the structured record for each new revision, we
> have some ideas on how to achieve this but I wanted to consult this list
> in case we have overlooked some other strategies, and also just to see
> if our proposed solution to the problem is sound.


We faced similar requirements when designing a financial application and
after some experimentation we settled on an approach very similar to what
you propose. There are quite a few tricky details, one relates to deletions
(which is not mentioned in your email so perhaps it is not relevant) and an
other relates to the actual workflow; how the main application interacts with
the database. Here is a detailed example that highlights some of the tricky
parts (long -- sorry list):

--
TransactionId is a unique id corresponding to the actual database transaction
which may or may not be useful to you

EntryId corresponds to your revision number and is (strictly)
monotically increasing
to avoid having to rely on timestamps for temporal ordering.

GroupEntryId is the unique id of the event which remains constant throughout
the life time of the event

EntryType is how we deal with the workflow issue; when working with an event
record the main app sets the EntryType to 'New' for new records,
'Amend' when amending an existing record and 'Delete' for deleting an existing
record. The main app interacts with the Event table by issuing only inserts
and issues queries on the EventCurrent table which is automatically maintained
by the triggers below. Manual (ie sql console) maintenance of the
Event table is also
handled by triggers.


create table Event (
_id integer primary key autoincrement,
TimeStamp timestamp not null,
TransactionId text not null COLLATE NOCASE,
EntryId text unique COLLATE NOCASE,
GroupEntryId text not null COLLATE NOCASE,
EntryType text COLLATE NOCASE,

EventProperty1 text,
EventProperty2 text
)

create table EventCurrent (
_id integer primary key autoincrement,
TimeStamp timestamp not null,
TransactionId text not null COLLATE NOCASE,
EntryId text unique COLLATE NOCASE,
GroupEntryId text not null COLLATE NOCASE,
EntryType text COLLATE NOCASE,

EventProperty1 text,
EventProperty2 text
)

create trigger trg_Event_update after update on Event for each row
begin
  update EventCurrent set
_id = NEW._id,
TimeStamp = NEW.TimeStamp,
TransactionId = NEW.TransactionId,
EntryId = NEW.EntryId,
GroupEntryId = NEW.GroupEntryId,
EntryType = NEW.EntryType,
EventProperty1 = NEW.EventProperty1,
EventProperty2 = NEW.EventProperty2
  where EventCurrent._id = NEW._id;
end


create trigger trg_Event_delete after delete on Event for each row
begin
  delete from EventCurrent
  where EventCurrent.EntryId = OLD.EntryId;

  replace into EventCurrent
  select * from Event where GroupEntryId = OLD.GroupEntryId and
 EntryId = (select max(EntryId) from Event where
GroupEntryId = OLD.GroupEntryId);

  delete from EventCurrent
  where EventCurrent.EntryId is null;
end


create trigger trg_Event_insert_amend before insert on Event for each row
when NEW.EntryType is not null and NEW.EntryType = 'Amend'
begin
  delete from EventCurrent
where EventCurrent.GroupEntryId = NEW.GroupEntryId;
end


create trigger trg_Event_insert_delete after insert on Event for each row
when NEW.EntryType is not null and NEW.EntryType = 'Delete'
begin
  delete from EventCurrent
where EventCurrent.GroupEntryId = NEW.GroupEntryId;
end


create trigger trg_Event_insert_new after insert on Event for each row
when NEW.EntryType is not null and (NEW.EntryType = 'New' or
NEW.EntryType = 'Amend' or NEW.EntryType = '')
begin
  insert into EventCurrent values (
NEW._id,
NEW.TimeStamp,
NEW.TransactionId,
NEW.EntryId,
NEW.GroupEntryId,
NEW.EntryType,
NEW.EventProperty1,
NEW.EventProperty2
);
end



Cheers

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

Re: [sqlite] Copy rows in same table



On 2014/07/17 06:56, Prashanth Anil Mascarenhas wrote:

Hi All,

I want to copy a row in same table. How can I achieve this?

Eg : Table(ID,name,place,comments)

1 ABD 121 None
2 ASD 141 None

Now I want to copy the table ID 2 to new table ID 3using sqlite3 commands.



INSERT INTO Table SELECT 3,name,place,comments FROM Table WHERE ID=2;


If the ID field is an autoincrement key or rowid alias this will also work to 
automatically select the next number:

INSERT INTO Table SELECT null,name,place,comments FROM Table WHERE ID=2;

-- or --

INSERT INTO Table (name,place,comments) SELECT name,place,comments FROM Table 
WHERE ID=2;


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

Re: [sqlite] Vacuum command fails