Re: [sqlite] disk image malformed

[Durga D]

Does disk io error is related to hardware issue? i mean, fsync() dint get
success on unix. so, this error may be thrown by sqlite. Is it?


On Fri, Dec 7, 2012 at 12:00 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 6 Dec 2012, at 6:13pm, Durga D <durga.d...@gmail.com> wrote:
>
> > I suspect, linking among pages will  be broken in sqlite database when
> disk
> > io error thrown by sqlite. Is it?
>
> This is one form of database corruption.  But there are others.  Sometimes
> it's just a few strange characters inside a string.  Other times the
> corruption is in the first page of the database and is so bad that the
> database file cannot even be opened successfully.
>
> 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] disk image malformed

[Durga D]

I will run integrity check.

>>I would like to know, why sqlite throws disk io error while inserting
 records...

I suspect, linking among pages will  be broken in sqlite database when disk
io error thrown by sqlite. Is it?


On Thu, Dec 6, 2012 at 11:30 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 6 Dec 2012, at 5:23pm, Durga D <durga.d...@gmail.com> wrote:
>
> > Is it (corruption) related error code 10? (disk io)
> >
> > I would like to know, why sqlite throws disk io error while inserting
> > records...
>
> It doesn't matter.  Run an in integrity check:
>
> <http://www.sqlite.org/pragma.html#pragma_integrity_check>
>
> If the integrity check reports a problem, rescue the data you can.  Throw
> away the corrupt database and make a new one.
>
> If the integrity check doesn't report a problem, your error is very
> unlikely to be related to a corrupt database.  Check your hardware and your
> software.
>
> 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] disk image malformed

[Durga D]

Is it (corruption) related error code 10? (disk io)

I would like to know, why sqlite throws disk io error while inserting
records...


On Thu, Dec 6, 2012 at 10:43 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 6 Dec 2012, at 5:04pm, Durga D <durga.d...@gmail.com> wrote:
>
> > i mean in second case. some records corrupted but not all. How to recover
> > this?
>
> There's no fixed answer.  It depends on which parts of the file are
> corrupt, and what they had written to them.  If you used the shell tool on
> the database and dumped all the data using .dump you might get perfectly
> usable file with all data you could just .read back in.  On the other hand
> the corruption might be in a pointer that caused the .dump routine to
> crash, or go around in circles listing the same rows again and again.
>
> 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] disk image malformed

[Durga D]

i mean in second case. some records corrupted but not all. How to recover
this?


On Thu, Dec 6, 2012 at 10:27 PM, Durga D <durga.d...@gmail.com> wrote:

> Is there anyway to recover?
>
>
> On Thu, Dec 6, 2012 at 10:23 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Thu, Dec 6, 2012 at 11:52 AM, Durga D <durga.d...@gmail.com> wrote:
>>
>> > Hi,
>> >
>> >   Is it possible to corrupt a single table among 10 tables in a
>> database?
>> >
>> >   Is it possible to corrupt some records among millions of records in a
>> > table?
>> >
>>
>> Yes.  Yes.
>>
>>
>>
>> >
>> > Best Regards,
>> > ___
>> > 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

Re: [sqlite] disk image malformed

[Durga D]

Is there anyway to recover?


On Thu, Dec 6, 2012 at 10:23 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Dec 6, 2012 at 11:52 AM, Durga D <durga.d...@gmail.com> wrote:
>
> > Hi,
> >
> >   Is it possible to corrupt a single table among 10 tables in a database?
> >
> >   Is it possible to corrupt some records among millions of records in a
> > table?
> >
>
> Yes.  Yes.
>
>
>
> >
> > Best Regards,
> > ___
> > 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] disk image malformed

[Durga D]

Hi,

  Is it possible to corrupt a single table among 10 tables in a database?

  Is it possible to corrupt some records among millions of records in a
table?

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

Re: [sqlite] to find table exists or not

[Durga D]

I have situation in which I want to read particular record if table exists.
Based on that record information, I have to execute some logic on other
tables of the same database. If record doesn't exist I need to create new
table.

thanks for sharing your ideas.


On Thu, Dec 6, 2012 at 5:26 PM, Dave McKee <dra...@scraperwiki.com> wrote:

> I can see situations in which you might want to create a new table with a
> name that wasn't already taken: at which point simple knowledge would let
> you reconsider your choice of table name without affecting the db in any
> way, success or fail.
>
>
> On Thu, Dec 6, 2012 at 10:57 AM, Hick Gunter <h...@scigames.at> wrote:
>
> > Both applications can be done without external logic using
> >
> > CREATE TABLE IF NOT EXISTS ...
> > (detect that the table is missing AND create it)
> >
> > and (if required)
> >
> > INSERT OR IGNORE INTO ...
> > (set missing options to default while keeping pre-set values)
> >
> > -Ursprüngliche Nachricht-
> > Von: Stephen Chrzanowski [mailto:pontia...@gmail.com]
> > Gesendet: Donnerstag, 06. Dezember 2012 11:21
> > An: General Discussion of SQLite Database
> > Betreff: Re: [sqlite] to find table exists or not
> >
> > I can think of two reasons why you wouldn't want to blindly delete the
> > table, but verify that it exists.
> >
> > - First run of the program that creates the database from square one.  If
> > the table exists, skip over the create routine and continue.  I routinely
> > do this for creating an Options database (Program preferences, etc)
> instead
> > of relying on ini files (In Windows) since they're limited in volume of
> > data, and the kind of data that can be stored within.
> >
> > - If using a temporary table validating that the table exists to begin
> with
> > before you start throwing data at it.
> >
> > The wrapper I use contains a routine that returns a Boolean if the table
> > exists or not, so I don't have to rely on constructing SQL statements.
> >
> > On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter <h...@scigames.at> wrote:
> >
> > > Why do you only want to see if the table is there?
> > >
> > > You can always do
> > >
> > > DROP TABLE IF EXISTS ...
> > > CREATE TABLE ...
> > >
> > > to replace the definition or
> > >
> > > CREATE TABLE IF NOT EXISTS ...
> > >
> > > to keep the old definition.
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: Durga D [mailto:durga.d...@gmail.com]
> > > Gesendet: Mittwoch, 05. Dezember 2012 15:33
> > > An: General Discussion of SQLite Database
> > > Betreff: [sqlite] to find table exists or not
> > >
> > > Hi all,
> > >
> > >
> > >  I just want to find whether table exists or not in a database.
> > >
> > >
> > > Is it correct query?
> > >
> > >
> > > select distinct tbl_name from sqlite_master where tbl_name = 'abc';
> > >
> > >
> > > Is there any other better way to find whether table exists or not.
> > >
> > >
> > > Thanks in advance.
> > >
> > >
> > > Regards,
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > >
> > >
> >
> --
> > >  Gunter Hick
> > > Software Engineer
> > > Scientific Games International GmbH
> > > Klitschgasse 2 - 4, A - 1130 Vienna, Austria
> > > FN 157284 a, HG Wien
> > > Tel: +43 1 80100 0
> > > E-Mail: h...@scigames.at
> > >
> > > This e-mail is confidential and may well also be legally privileged. If
> > > you have received it in error, you are on notice as to its status and
> > > accordingly please notify us immediately by reply e-mail and then
> delete
> > > this message from your system. Please do not copy it or use it for any
> > > purposes, or disclose its contents to any person as to do so could be a
> > > breach of confidence. Thank you for your cooperation.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > __

[sqlite] to find table exists or not

[Durga D]

Hi all,


 I just want to find whether table exists or not in a database.


Is it correct query?


select distinct tbl_name from sqlite_master where tbl_name = 'abc';


Is there any other better way to find whether table exists or not.


Thanks in advance.


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

Re: [sqlite] sqlite3 db open/close

[Durga D]

Thank you.


On Sun, Nov 18, 2012 at 8:18 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote:

> Durga D wrote:
> >What happens if sqlite3_close() called multiple times but
> > sqlite3_open_v2() called only once.
> >
> > Practically I dint see any malfunction/corruption here. I would like
> to
> > know the behavior of sqlite in this scenario.
>
> About same as
>
>   char *foo = malloc(10);
>   free(foo);
>   free(foo);
>
> or
>
>FILE *f = fopen("file", "r");
>fclose(f);
>fclose(f);
>
> Sometimes it does nothing, but display error message (e.g. with some "debug
> malloc" modes/libraries). Sometimes second sqlite3_close/free/fclose kills
> your
> program on assertion or sigsegv. Sometimes it corrupts heap internal
> structures,
> and your program may die *at random time* after that.
>
> ___
> 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] sqlite3 db open/close

[Durga D]

Hi All,


   What happens if sqlite3_close() called multiple times but
sqlite3_open_v2() called only once.

Practically I dint see any malfunction/corruption here. I would like to
know the behavior of sqlite in this scenario.

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

Re: [sqlite] added new db with other journal mode. Any issue?

[Durga D]

this the sequence of sqlite statements:
--

sqlite3_open
sqlite3_key
sqlite3_prepare_v2
sqlite3_step
sqlite3_finalize
sqlite3_prepare_v2 --- //my application crashed here




On Wed, Nov 14, 2012 at 5:14 PM, Durga D <durga.d...@gmail.com> wrote:

> sqlite_source_id(): 2012-03-20 11:35:50
> 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
>
> I will send the sequence of sql stmts.
>
>
> On Wed, Nov 14, 2012 at 4:51 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Wed, Nov 14, 2012 at 3:41 AM, Durga D <durga.d...@gmail.com> wrote:
>>
>> > I got the below error:
>> > ---
>> > The database disk image is malformed.
>> >
>>
>> Please send:
>>
>> (1) The version of SQLite you are running - the output of "SELECT
>> sqlite_source_id();".
>> (2) The exact sequence of SQL statements and API calls you used to create
>> the error above.  Be specific, please.
>>
>> Thanks.
>>
>>
>> >
>> > I am able to open. It's throwing above error when I try to set journal
>> mode
>> > as truncate after db open before tables creation. How to solve this?
>> >
>> >
>> > On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin <slav...@bigfraud.org>
>> > wrote:
>> >
>> > >
>> > > On 14 Nov 2012, at 7:35am, Durga D <durga.d...@gmail.com> wrote:
>> > >
>> > > > I have 8 sqlite databases with TRUNCATE journal mode. I have added
>> new
>> > > > database with WAL journal mode. Now, 9 databases in my application.
>> > Will
>> > > it
>> > > > give any database corruption?
>> > >
>> > > You can mix different modes for different databases without problems.
>> > >
>> > > 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
>> >
>>
>>
>>
>> --
>> 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

Re: [sqlite] added new db with other journal mode. Any issue?

[Durga D]

sqlite_source_id(): 2012-03-20 11:35:50
00bb9c9ce4f465e6ac321ced2a9d0062dc364669

I will send the sequence of sql stmts.


On Wed, Nov 14, 2012 at 4:51 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Nov 14, 2012 at 3:41 AM, Durga D <durga.d...@gmail.com> wrote:
>
> > I got the below error:
> > ---
> > The database disk image is malformed.
> >
>
> Please send:
>
> (1) The version of SQLite you are running - the output of "SELECT
> sqlite_source_id();".
> (2) The exact sequence of SQL statements and API calls you used to create
> the error above.  Be specific, please.
>
> Thanks.
>
>
> >
> > I am able to open. It's throwing above error when I try to set journal
> mode
> > as truncate after db open before tables creation. How to solve this?
> >
> >
> > On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin <slav...@bigfraud.org>
> > wrote:
> >
> > >
> > > On 14 Nov 2012, at 7:35am, Durga D <durga.d...@gmail.com> wrote:
> > >
> > > > I have 8 sqlite databases with TRUNCATE journal mode. I have added
> new
> > > > database with WAL journal mode. Now, 9 databases in my application.
> > Will
> > > it
> > > > give any database corruption?
> > >
> > > You can mix different modes for different databases without problems.
> > >
> > > 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
> >
>
>
>
> --
> 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

Re: [sqlite] added new db with other journal mode. Any issue?

[Durga D]

I got the below error:
---
The database disk image is malformed.

I am able to open. It's throwing above error when I try to set journal mode
as truncate after db open before tables creation. How to solve this?


On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 14 Nov 2012, at 7:35am, Durga D <durga.d...@gmail.com> wrote:
>
> > I have 8 sqlite databases with TRUNCATE journal mode. I have added new
> > database with WAL journal mode. Now, 9 databases in my application. Will
> it
> > give any database corruption?
>
> You can mix different modes for different databases without problems.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] added new db with other journal mode. Any issue?

[Durga D]

Hi All,

 I have 8 sqlite databases with TRUNCATE journal mode. I have added new
database with WAL journal mode. Now, 9 databases in my application. Will it
give any database corruption?

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

Re: [sqlite] indexing for integer column

[Durga D]

Hi,

   Can I create INDEX for the particular column when database journal mode
is WAL?

   Any impact If I INDEXed database with some triggers?

   Thanks in advance.

Best Regards,

On Thu, Sep 6, 2012 at 4:04 PM, Durga D <durga.d...@gmail.com> wrote:

> Thank you Kees.
>
>
> On Thu, Sep 6, 2012 at 3:34 PM, Kees Nuyt <k.n...@zonnet.nl> wrote:
>
>> On Thu, 6 Sep 2012 12:02:03 +0400, Durga D <durga.d...@gmail.com> wrote:
>>
>> >Hi All,
>> >
>> >Somehow  "original author" missed one INTETER column as searchable
>> >field ( like unique or primary key) in the table. When execute queries
>> >based on this integer field in where clause/joins, huge performance hit.
>> >So, I am planning to add INDEXING for this integer column.
>> >
>> >Is there any alternative/best solution?
>>
>> Make it a UNIQUE INDEX if possible.
>>
>> Yet, the best thing to do is to refactor the schema.
>>
>> sqlite3 olddbfile .dump >dumpfile
>> edit dumpfile to adjust schema
>> sqlite3 newdbfile > rename olddbfile to olddbfile.ORIGINAL
>> rename newdbfile to olddbfile
>> start the application, test thoroughly
>>
>> Inform the original author, so a new release is correct from the
>> beginning.
>>
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>>
>> ___
>> 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] indexing for integer column

[Durga D]

Thank you Kees.

On Thu, Sep 6, 2012 at 3:34 PM, Kees Nuyt <k.n...@zonnet.nl> wrote:

> On Thu, 6 Sep 2012 12:02:03 +0400, Durga D <durga.d...@gmail.com> wrote:
>
> >Hi All,
> >
> >Somehow  "original author" missed one INTETER column as searchable
> >field ( like unique or primary key) in the table. When execute queries
> >based on this integer field in where clause/joins, huge performance hit.
> >So, I am planning to add INDEXING for this integer column.
> >
> >Is there any alternative/best solution?
>
> Make it a UNIQUE INDEX if possible.
>
> Yet, the best thing to do is to refactor the schema.
>
> sqlite3 olddbfile .dump >dumpfile
> edit dumpfile to adjust schema
> sqlite3 newdbfile  rename olddbfile to olddbfile.ORIGINAL
> rename newdbfile to olddbfile
> start the application, test thoroughly
>
> Inform the original author, so a new release is correct from the
> beginning.
>
>
> --
> Regards,
>
> Kees Nuyt
>
> ___
> 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] indexing for integer column

[Durga D]

Hi All,

Somehow  "original author" missed one INTETER column as searchable
field ( like unique or primary key) in the table. When execute queries
based on this integer field in where clause/joins, huge performance hit.
So, I am planning to add INDEXING for this integer column.

Is there any alternative/best solution?

   Thanks in advance.

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

[sqlite] open database on Linux. Already db created on Mac.

[Durga D]

Hi All,

What is the procedure to open the sqlite3 database file in Linux
Terminal which is already created on Mac.

   Thanks in advance.

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

Re: [sqlite] single gdbconn object with multiple threads.

[Durga D]

Thank you Simon/Igor.

I got the answer.

>>But since all the work is serialized, a single thread that does all the
reading and writing would be just as fast, if not slightly faster.

How can I achieve this with single thread?

My Req. is: I have to write a wrapper for sqlite3. This wrapper will be
called by different clients from different threads within the process.
Requests might be read or write. Wrapper will have static sqlite3*. So all
clients shares the same sqlite3*. To simulate this, I created this sample
application.

doubt: How can I achieve this single thread when multiple clients are
called.

Am I right?

Regards,

On Mon, Jul 23, 2012 at 8:19 PM, Igor Tandetnik <itandet...@mvps.org> wrote:

> On 7/23/2012 10:30 AM, Durga D wrote:
>
>> Unless your threads do something else in parallel, you could just as well
>>>>
>>> do all SQLite work on a single thread
>>
>> doubt: all are parallel threads. started at same time. one thread is
>> writing and others are reading at the same time by using same sqlite3*.
>>
>
> Not really. What really happens is, one of the threads does some work,
> while the other three are sitting waiting on a mutex.
>
>
>  In this scenario, all are parallel.
>>
>
> Again - every SQLite API call acquires a mutex associated with the
> connection. If that mutex is already taken, then the thread sits there
> waiting for it to be released. In other words, two SQLite calls on the same
> connection never execute at the same time - they are serialized on the
> mutex. Threads effectively take turns to make these calls.
>
>
>  my main doubt is: same sqlite3* is passing to 4 threads from the primary
>> thread.
>>
>> Is it correct way to implement multiple readers and single writer?
>>
>
> There are no technical problems with this, if that's what you are asking.
> It would work. But since all the work is serialized, a single thread that
> does all the reading and writing would be just as fast, if not slightly
> faster. You are adding complexity but are not gaining any performance out
> of it.
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] single gdbconn object with multiple threads.

[Durga D]

Hi Igor,

Sorry.

>>Unless your threads do something else in parallel, you could just as well
do all SQLite work on a single thread

doubt: all are parallel threads. started at same time. one thread is
writing and others are reading at the same time by using same sqlite3*.

I passed same sqlite3* as an argument to 4 posix threads. Among 4, three
threads are reading (selct name from table where id < 10*index) from
database. Fourth thread is writer thread, which is inserting into database.

In this scenario, all are parallel. three threads are accessing database
for read and another one for write.

It's working fine in my sample application with WAL mode only.

my main doubt is: same sqlite3* is passing to 4 threads from the primary
thread.

Is it correct way to implement multiple readers and single writer?

please ignore intel tbb.

Regards,

On Mon, Jul 23, 2012 at 5:17 PM, Igor Tandetnik <itandet...@mvps.org> wrote:

> Durga D <durga.d...@gmail.com> wrote:
> >   I have used same gdbconn for all the threads. In my sample application,
> > it working perfectly in wal mode.
> >
> >   Is it correct approach?
>
> Approach to what? You've never stated the problem you are trying to solve.
>
> With this setup, you don't get any concurrency out of your four threads.
> All SQLite API calls are serialized on one mutex associated with the
> connection. Unless your threads do something else in parallel, you could
> just as well do all SQLite work on a single thread.
>
> >   If yes, will tbb improves the performance for reader_threads?
>
> What's "tbb"?
> --
> Igor Tandetnik
>
> ___
> 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] single gdbconn object with multiple threads.

[Durga D]

Hi all,

I developed sample application with below logic:

   1. in main(), got the valide gdbconn (type sqlite3*) after sqlite3_open
and sqlite3_prepare_v2.

   2. wal mode.

   3. created 4 threads and passed gdbconn as a argument to threads.

   4. Three are reader_threads. each reader thread executed different
queries. It's given correct results.

   5. Fourth thread is writer_thread. ||y updating the with same database
by using gdbconn. This is also working fine.


   I have used same gdbconn for all the threads. In my sample application,
it working perfectly in wal mode.

   Is it correct approach?


   If yes, will tbb improves the performance for reader_threads?


  Thanks in advance.

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

Re: [sqlite] set of db connections

[Durga D]

Hi Simon, what you suggest now? sigle object based or set of connections?

Thanks,


On Mon, Jul 16, 2012 at 6:11 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 16 Jul 2012, at 2:59pm, "Igor Tandetnik" <itandet...@mvps.org> wrote:
>
> > Durga D <durga.d...@gmail.com> wrote:
> >>   So, I can establish x connections in app initialize time in WAL mode.
> >> Based on request (read or write), I can pick the connection and serve.
> >> Allows only one write at a time.
> >
> > I'm not sure what you mean by picking a connection based on a request.
> It's not connections that are readers or writers - transactions are. A
> connection may execute a read-only transaction, then later a write
> transaction, then a read transaction again, and so on.
> >
> > You cannot have two write transactions (on two separate connections)
> active at the same point in time - one of them will block the other.
>
> Igor forgot to explain that WAL mode affects the database file.  You don't
> put a connection into WAL mode, something is stored with the database file
> which means that everything that talks to it will automatically use WAL
> mode.
>
> So you'll probably do something like this:
>
> Create a blank database file
> Put it into WAL mode using "PRAGMA journal_mode=WAL" (you can do this at
> any time, even after data is written)
> Make some tables (and put some data in if you want)
> Close the file
>
> Then you can write your apps to do whatever accessing you want.  Each app
> would open one connection to the database, and use that connection for
> whatever requests you want, in any order you want.  Each request gets its
> own 'handle' and stores its own command and its own results.
>
> 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] set of db connections

[Durga D]

Hi Michael/Igor,

So, I can establish x connections in app initialize time in WAL mode.
Based on request (read or write), I can pick the connection and serve.
Allows only one write at a time.

Is it correct?

Thanks in advance.

Regards,

On Mon, Jul 16, 2012 at 4:43 PM, Igor Tandetnik  wrote:

> Black, Michael (IS)  wrote:
> > Ummmare we forgetting about WAL mode?
> >
> > http://www.sqlite.org/draft/wal.html
> >
> > "Reading and writing can proceed concurrently."
> >
> > Not that you can have multiples of each...just one of each.
>
> To be precise, with WAL you can have one writer and multiple readers at
> any point in time. Without WAL, it's one writer OR multiple readers.
> --
> Igor Tandetnik
>
> ___
> 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] set of db connections

[Durga D]

Got it. Thank you so much.

On Mon, Jul 16, 2012 at 3:24 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 16 Jul 2012, at 12:09pm, Durga D <durga.d...@gmail.com> wrote:
>
> >scenario: while write request is in progress with 100K records
> > insertion, new request with reading of 1 records already existing
> > records. Here, read request should wait till write request completes. Is
> it
> > correct?
>
> You are correct.  In order to make the insertion of 100K records a single
> write request you will, of course, make it one transaction by enclosing it
> in BEGIN ... COMMIT.
>
> When one request (even one which reads and does not write) is in progress
> SQLite locks the entire database.  The read request will automatically know
> that it cannot execute until the write request is finished.  This will
> prevent any other requests from executing.  You should set a timeout value
> of a few seconds using
>
> <http://www.sqlite.org/c3ref/busy_timeout.html>
>
> to prevent the second request from simply returning a SQLITE_BUSY or
> SQLITE_LOCKED result.  With a timeout set, the read request will wait until
> the write request is finished (or the timeout has expired), and then
> execute.
>
> 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] set of db connections

[Durga D]

Hi Simon,

I agree with one connection logic.

scenario: while write request is in progress with 100K records
insertion, new request with reading of 1 records already existing
records. Here, read request should wait till write request completes. Is it
correct?

Thanks in advance.

Regards,

On Mon, Jul 16, 2012 at 2:29 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 16 Jul 2012, at 7:50am, Durga D <durga.d...@gmail.com> wrote:
>
> >I am developing readers (> 1) and writer(1) application for sqlite3
> db.
> >
> >I would like to maintain set of connections in application
> > initialization time. whenever read request comes, serve the request from
> > existing connection object like pool.
>
> You can keep one database connection open per application, for the entire
> time that your application runs.  This will work correctly.
>
> The API will only service one request at a time.  You can prepare a number
> of statements (for example, a number of "SELECT" commands) but SQLite is
> single-threaded and locks the entire database when working.  So it will
> work only on one command at a time.  So there's no point in doing clever
> things with multiprocessing and background handling of many commands
> because the SQLite API will still be your bottleneck.
>
> >Here, my doubt is: if app. runs for a long time ( one week), will
> > connection maintain latest state of db?
>
> SQLite correctly handles many different apps, users and computers
> accessing the database at once.  A change made by one app on one computer
> will be instantly noticed by a different app on a different computer.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] set of db connections

[Durga D]

Hi all,

I am developing readers (> 1) and writer(1) application for sqlite3 db.

I would like to maintain set of connections in application
initialization time. whenever read request comes, serve the request from
existing connection object like pool.

Here, my doubt is: if app. runs for a long time ( one week), will
connection maintain latest state of db?

Is it correct approach?

Thanks in advance.

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

Re: [sqlite] fetching rows

[Durga D]

Thank you(Pavel) for the prompt response.

Sqlite does auto commit for every 25k insertions. Do I need to change the
number from 25k to x ( for ex: 100)?

On Thu, Jun 21, 2012 at 7:54 AM, Pavel Ivanov <paiva...@gmail.com> wrote:

> On Wed, Jun 20, 2012 at 11:33 PM, Durga D <durga.d...@gmail.com> wrote:
> > Hi All,
> >
> >I have to develop a sqlite application. Within the process, multiple
> > threads are trying to access in write/read mode. Will sqlite supports
> read
> > and write at a time?
> >
> >scenario:  1. x number of records (x related data) are going to insert
> > in a transaction in ThreadA. still, not committed.
> >   2. In ThreadB (parallel thread), trying to read records
> > (x related data), which are there in transaction.
> >
> >By default, sqlite supports this scenario? or Do I need to enable any
> > flags/macros?
>
> Yes, SQLite supports that. You can see some "problems" only if ThreadA
> inserts a lot in one transaction, so that it doesn't fit into memory
> cache.
>
>
> Pavel
> ___
> 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] fetching rows

[Durga D]

Hi All,

I have to develop a sqlite application. Within the process, multiple
threads are trying to access in write/read mode. Will sqlite supports read
and write at a time?

scenario:  1. x number of records (x related data) are going to insert
in a transaction in ThreadA. still, not committed.
   2. In ThreadB (parallel thread), trying to read records
(x related data), which are there in transaction.

By default, sqlite supports this scenario? or Do I need to enable any
flags/macros?

Thanks in advance.

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

[sqlite] encrypt/decrypt sqlite3 database file or content

[Durga D]

Hi All,


I am developing sqlite3 based application. I want to create/maintain
sqlite3 database in encrypted. Others should not have rights to open this
database without key. Is there any sqlite3 api's to do this?

Thanks in advance.

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

Re: [sqlite] simple like query optimization

[Durga D]

Here, a,b,d,e,f,u and v are variable length strings.

delimiter '/ ' is constant.

On Fri, Jan 13, 2012 at 12:22 PM, Durga D <durga.d...@gmail.com> wrote:

> Hi All,
>
> I have a table like this:
>
> h   t0info( h is primary key, t0info is unique)
> ---
>
> 1/a/d/u
> 2/a/e/u
> 3/a/f/u
> 4/a/g/v
> 5/a/b/c/d/e/f
>
> input is : /a
> output should be: d, e f, g and b
>
> I am doing this way: select t0info from t0 where t0info like '/a%';
>
> processing on string operations on result set to get d, e, f, g and b.
>
> any alternative solution for this?
>
> Thanks in advance,
>  Durga.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] simple like query optimization

[Durga D]

Hi All,

I have a table like this:

h   t0info( h is primary key, t0info is unique)
---

1/a/d/u
2/a/e/u
3/a/f/u
4/a/g/v
5/a/b/c/d/e/f

input is : /a
output should be: d, e f, g and b

I am doing this way: select t0info from t0 where t0info like '/a%';

processing on string operations on result set to get d, e, f, g and b.

any alternative solution for this?

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key autoincrement updation problemincaserefereced

[Durga D]

Fixed it.

It allows duplicates when stmt reset(sqlite3_reset) missed after
sqlite3_step(..) in transaction.

Thank you.

On Thu, Jan 12, 2012 at 4:27 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 12 Jan 2012, at 10:52am, Durga D wrote:
>
> >   I have attached sample db. I tried to simulate with test application.
> > But, I could not.
> >
> >Please find the attached db, which has only one table.
>
> You can't attach things to messages to this list, because we don't want
> the list flooded with everyone attaching their problem files rather than
> doing their own investigations.  However, you could try something like
>
> SELECT * FROM sqlite_master WHERE name='t0'
>
> and see which CREATE command you get back.
>
> 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] primary key autoincrement updation problemincaserefereced

[Durga D]

Thank you.

create table if not exists t0 (
   h integer primary key autoincrement,
   t0info text, unique(t0info));

t0 table allows duplicate t0info column, when in transaction.

how to avoid duplicates for t0info column.

Thanks in advance.
Durga.


On Thu, Jan 12, 2012 at 3:37 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 12 Jan 2012, at 6:05am, Durga D wrote:
>
> >Insertion time, I am able to get the rowid from
> > sqlite3_last_insert_rowid();
> >
> >   It's working fine.
> >
> >  Now,  t0info record already exists, that time , I should not update
> > with latest rowid. I should fetch the corresponding h value, update in t1
> > table. Here, fetching is the problem:
>
> Yes, this is a problem.  If the way your program works may cause it to try
> to insert a t0 row which already exists, you do need at least two SQLite
> commands.  The two common structures are ...
>
> create table if not exists t0 (
>h integer primary key autoincrement,
>t0info text);
>
> First option:
>
> INSERT OR IGNORE INTO t0 ...
> SELECT h FROM t0 WHERE ...
>
> Second option:
>
> SELECT h FROM t0 WHERE ...
>
> Then see whether you got 0 or 1 row back.  If you got 1 for back, the row
> already exists and you can use that 'h' value.  If not, you do the INSERT,
> then use sqlite3_last_insert_rowid().
>
>
>
>
> One option is good if your application structure makes 'IF' commands
> difficult.  The other is good if executing SQLite calls slows it down
> unacceptably.
>
> 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] primary key autoincrement updation problemincaserefereced

[Durga D]

Corrected it. Thank you.

insert into t1( hh, t1info) values ((select h from t0 where t0info = ?), ?)


On Thu, Jan 12, 2012 at 11:35 AM, Durga D <durga.d...@gmail.com> wrote:

> Dear Igor,
>
> Insertion time, I am able to get the rowid from
> sqlite3_last_insert_rowid();
>
>It's working fine.
>
>   Now,  t0info record already exists, that time , I should not update
> with latest rowid. I should fetch the corresponding h value, update in t1
> table. Here, fetching is the problem:
>
>  I tried with below code: not getting succ.
>
> sqlite3_stmt* stmtt1_hhfrom_t0;
>
> nres =  sqlite3_prepare( dbconn, "insert into t1 values (?, ?, ?)
> where hh = (select h from t0 where t0info = ?)" , -1, _hhfrom_t0, 0);
>
> if ( nres != SQLITE_OK ) return 0;
>
> Thanks in advance,
> Durga.
>
>
> On Thu, Jan 12, 2012 at 11:03 AM, Igor Tandetnik <itandet...@mvps.org>wrote:
>
>> Durga D <durga.d...@gmail.com> wrote:
>> > Dear Igor,
>> >
>> >If already toinfo record exists, that time how to get the h
>> > (primary key) (instead of rowid)?
>>
>> An INTEGER PRIMARY KEY column is in fact an alias for rowid. They are one
>> and the same.
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] primary key autoincrement updation problemincaserefereced

[Durga D]

Dear Igor,

Insertion time, I am able to get the rowid from
sqlite3_last_insert_rowid();

   It's working fine.

  Now,  t0info record already exists, that time , I should not update
with latest rowid. I should fetch the corresponding h value, update in t1
table. Here, fetching is the problem:

 I tried with below code: not getting succ.

sqlite3_stmt* stmtt1_hhfrom_t0;

nres =  sqlite3_prepare( dbconn, "insert into t1 values (?, ?, ?)
where hh = (select h from t0 where t0info = ?)" , -1, _hhfrom_t0, 0);

if ( nres != SQLITE_OK ) return 0;

Thanks in advance,
Durga.


On Thu, Jan 12, 2012 at 11:03 AM, Igor Tandetnik <itandet...@mvps.org>wrote:

> Durga D <durga.d...@gmail.com> wrote:
> > Dear Igor,
> >
> >If already toinfo record exists, that time how to get the h
> > (primary key) (instead of rowid)?
>
> An INTEGER PRIMARY KEY column is in fact an alias for rowid. They are one
> and the same.
> --
> Igor Tandetnik
>
> ___
> 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] primary key autoincrement updation problem incaserefereced

[Durga D]

Dear Igor,

If already toinfo record exists, that time how to get the h
(primary key) (instead of rowid)?

   Any shortcut for this?

Thanks in advance,
Durga.

On Wed, Jan 11, 2012 at 7:19 PM, Durga D <durga.d...@gmail.com> wrote:

> Thanks alot !!!
>
> Have a good day. Let me try.
>
>
> On Wed, Jan 11, 2012 at 7:15 PM, Igor Tandetnik <itandet...@mvps.org>wrote:
>
>> Durga D <durga.d...@gmail.com> wrote:
>> > so, first I need to insert in t0 table, then query for value from t0,
>> then
>> > update t1 with this value. Needs  to avoid reading from t0 table.
>>
>> You don't need to query t0 after insert. You can use
>> sqlite3_last_insert_rowid API function, or last_insert_rowid() SQL function.
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] primary key autoincrement updation problem incaserefereced

[Durga D]

Thanks alot !!!

Have a good day. Let me try.

On Wed, Jan 11, 2012 at 7:15 PM, Igor Tandetnik <itandet...@mvps.org> wrote:

> Durga D <durga.d...@gmail.com> wrote:
> > so, first I need to insert in t0 table, then query for value from t0,
> then
> > update t1 with this value. Needs  to avoid reading from t0 table.
>
> You don't need to query t0 after insert. You can use
> sqlite3_last_insert_rowid API function, or last_insert_rowid() SQL function.
> --
> Igor Tandetnik
>
> ___
> 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] primary key autoincrement updation problem in caserefereced

[Durga D]

Thanks Igor.

>>You seem to be under impression that a FOREIGN KEY >>constraint somehow
magically pulls a value from >>referenced table.

  correct.

so, first I need to insert in t0 table, then query for value from t0, then
update t1 with this value. Needs  to avoid reading from t0 table.

Is there any alternative way to insert?

Thanks in advance.

On Wed, Jan 11, 2012 at 6:41 PM, Igor Tandetnik <itandet...@mvps.org> wrote:

> Durga D <durga.d...@gmail.com> wrote:
> > create table if not exists t0 ( h integer primary key autoincrement,
> t0info
> > text);
> > create table if not exists t1 ( i integer primary key autoincrement,
> t1info
> > text, hh integer not null, unique(i, hh), foreign key (hh) references t0
> > (h) );
>
> The UNIQUE constraint is pointless. Since i is a PRIMARY KEY, it's already
> guaranteed to be unique by itself.
>
> > create table if not exists t2 (ii integer primary key, t2info text,
> foreign
> > key (ii) references t1 (i) );
> >
> > I created three prepared statement for t0, t1 and t2.
> > Updated the records (t0, t1 and t2 table) by using bind and step apis in
> > trasaction.
> > Binding t0info for t0, t1info for t1 table and t2info for t2 table only.
> >
> > But, sqlite3_step(stmtoft1); is failing always. What might be the wrong
> > with this approach.
>
> When you insert a record into t1, what value are you providing for hh
> column, if any? You can't just leave it off, as it's declared NOT NULL. It
> needs to be assigned a value, and that value must match t0.h in some t0 row.
>
> You seem to be under impression that a FOREIGN KEY constraint somehow
> magically pulls a value from referenced table. This is not the case. You
> are still responsible for providing the value, the constraint only checks
> that the value actually exists in the referenced table/column.
> --
> Igor Tandetnik
>
> ___
> 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] primary key autoincrement updation problem in case refereced

[Durga D]

Simon/Jr

  Please find the attached main.c files.

Thanks,
Durga


On Wed, Jan 11, 2012 at 12:58 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 11 Jan 2012, at 6:05am, Durga D wrote:
>
> > But, sqlite3_step(stmtoft1); is failing always. What might be the wrong
> > with this approach.
>
> What code is it reporting when it fails ?
>
> 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] primary key autoincrement updation problem in case refereced

[Durga D]

then, how to insert the record with binding and step apis in C language
with second approach?

hh field updation in t1 table is the issue.

If I do in sqlite shell, it's working fine. In C language only, I have this
issue.

Any idea?


On Wed, Jan 11, 2012 at 12:17 PM, jr <creature.eter...@gmail.com> wrote:

> On 11/01/12 06:29, Durga D wrote:
>
>> Deleted sqlite data base before executing first approach.
>> Again deleted sqlite database before executing second approach.
>>
>
> ok, misread this and thought 'first approach' relevant.
>
>
>
>> Issue is related to database design or insertion procedure.
>>
>>
> from what I can see there's no problem with the table design (created them
> here).
>
>
> jr.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] primary key autoincrement updation problem in case refereced

[Durga D]

Deleted sqlite data base before executing first approach.
and again delete sqlite database before executing second approach.

Issue is related to database design or insertion procedure.

There is no syntax problems in queries also.

On Wed, Jan 11, 2012 at 11:54 AM, jr <creature.eter...@gmail.com> wrote:

> On 11/01/12 06:05, Durga D wrote:
>
> if I understand correctly: first you created tables t1 and t2, then you
> say 'create if not exist' -- but they already do.  instead of (re-)creating
> tables t1 and t2, ALTER TABLE statements can be used to modify the existing.
>
> jr.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] primary key autoincrement updation problem in case refereced

[Durga D]

First approach:

I have created database with below schema:

create table if not exists t1 ( i integer primary key autoincrement, t1info
text);
create table if not exists t2 (ii integer primary key, t2info text, foreign
key (ii) references t1 (i) );

I created two prepared statement for t1 and t2.
Updated the records (t1 and t2 table) by using bind and step apis in
trasaction.
Always binding t1info for t1 table and t2info for t2 table only. It's
working fine.
primary key is auto increment, so it's increasing and updating in t1 and t2
tables. It' working fine.

-

Second approach:

Now, I need to change the schema this way:


create table if not exists t0 ( h integer primary key autoincrement, t0info
text);
create table if not exists t1 ( i integer primary key autoincrement, t1info
text, hh integer not null, unique(i, hh), foreign key (hh) references t0
(h) );
create table if not exists t2 (ii integer primary key, t2info text, foreign
key (ii) references t1 (i) );

I created three prepared statement for t0, t1 and t2.
Updated the records (t0, t1 and t2 table) by using bind and step apis in
trasaction.
Binding t0info for t0, t1info for t1 table and t2info for t2 table only.

But, sqlite3_step(stmtoft1); is failing always. What might be the wrong
with this approach.

realtions among tables are not proper with second approach? if yes, please
suggest.

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert if record not exists without primary key

[Durga D]

Yes. I agree.

What about second approach.

create table if not exists emp (id integer primary key autoincrement, col1
text, col2 text); //without unique.

I tried with insert or ignore into emp (col1, col2) values ('a', 'b');

I noticed, this is fast.

just to know, what might be the reason?


On Tue, Jan 10, 2012 at 2:36 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 10 Jan 2012, at 8:37am, Durga D wrote:
>
> > I noticed, with first approach, huge performance hit when database grows.
> > for ex: database has 500,000 records. now insert 25,000 within a
> > transaction. It takes lot of time when compared to insert 25,000 records
> > with empty database.
> >
> > approximately 15 times slower. because of unique(col1, col2).
>
> This makes some sense.  Every INSERT command involves SQLite looking
> through the records which are already in the table.  So a new insert when
> there are only 100 rows in the table is going to be pretty quick, but an
> insert when there are 500,000 rows in the table will involve lots of work.
>
> SQLite has to look up the data of the new record in the index it makes of
> (col1, col2) so it can find out whether the new row is a duplicate of an
> existing row.  So it has to do 25,000 searches of a balanced tree.  And if
> the tree has 500,000 records in it that means it's doing around ... hmm,
> it's over 3/4 of 1020, call it 765 ... tests for each search of the tree,
> 765 test for each INSERT command.
>
> So inserting 25,000 new rows when you already have 500,000 rows involves
> more than 25,000 * 765 string comparisons.  Call it 19 million string
> comparisons.  Plus, of course, all the other work involved in messing with
> the database.  Since you know how long your strings are you can get an idea
> of how much data SQLite is having to handle to do this.
>
> > I did with transactions for every 25,000 records.
>
> That is a reasonable way to do it.  People may be able to recommend
> PRAGMAs which will speed this up a bit but what you're doing really does
> involve a lot of work for the computer to do.
>
> 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] insert if record not exists without primary key

[Durga D]

Simon,

Thank you.

>>create table if not exists emp(id integer primary key
>>autoincrement, col1 text, col2 text, unique (col1, col2));

I noticed, with first approach, huge performance hit when database grows.
for ex: database has 500,000 records. now insert 25,000 within a
transaction. It takes lot of time when compared to insert 25,000 records
with empty database.

approximately 15 times slower. because of unique(col1, col2).

I did with transactions for every 25,000 records.

Any suggestions?

Durga.

On Tue, Jan 10, 2012 at 1:00 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 10 Jan 2012, at 7:23am, Durga D wrote:
>
> >   create table if not exists emp(id integer primary key autoincrement,
> > col1 text, col2 text, unique (col1, col2));
> >
> > here, col1 and col2 should be unique. I tried to insert 1000 records
> > with unique(col1, col2). It's very slow. So, I choosed id as primary key
> > (surrogate key) and without unique key.
> >
> > create table if not exists emp(id integer primary key autoincrement, col1
> > text, col2 text);
> >
> > I want to insert a record if not exists. How?
>
> You did it correctly the first time.  That is the correct way to prevent
> duplicates.
>
> Your test is unrealistic and will not reflect how fast the database will
> be in real life (are you really going to have your application insert
> 10,000,000 rows often ?) but if you really do want to do this, put all your
> insert commands into a transaction:
>
> BEGIN TRANSACTION;
> insert ...;
> insert ...;
> insert ...;
> ...;
> END TRANSACTION;
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert if record not exists without primary key

[Durga D]

Hi all,

I have emp sqlite table:

   create table if not exists emp(id integer primary key autoincrement,
col1 text, col2 text, unique (col1, col2));

here, col1 and col2 should be unique. I tried to insert 1000 records
with unique(col1, col2). It's very slow. So, I choosed id as primary key
(surrogate key) and without unique key.

create table if not exists emp(id integer primary key autoincrement, col1
text, col2 text);

I want to insert a record if not exists. How?

I tried with insert or replace. Duplicates are occuring. While inserting, I
dont have primary key in my hand.

Note: id is foreign key in other 4 tables.

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Petite,

   Thank you for your material.

   I will go through it.

Thanks,
Durga.

On Tue, Jan 3, 2012 at 1:02 AM, Petite Abeille wrote:

>
> On Jan 2, 2012, at 5:25 PM, Jay A. Kreibich wrote:
>
> >  Oracle has some syntax short-cuts to deal with this, but they're
> non-standard.
>
> Recursive 'with' clause anyone?
>
> "goodbye Connect By or: the end of hierarchical querying as we know it"
>
> http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
>
>
> http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#i2077142
>
>
> Regarding hierarchical data, in addition to Joe Celkos nice book, here are
> a couple of links summarizing different structures:
>
> Models for hierarchical data
> http://www.slideshare.net/billkarwin/models-for-hierarchical-data
>
> What are the Options for Storing Hierarchical Data in a Relational
> Database?
>
> http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database
>
> Aside from the materialized path approach, I personally like so-called
> "closure tables":
>
> http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html
>
> And  if you feel ambitious, matrix encoding might be for you:
>
> http://vadimtropashko.files.wordpress.com/2011/07/ch5.pdf
>
> As always, YMMV as to which one of these structures work best for a given
> scenario.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Simon,

Thank you for your response.

*>>CREATE TABLE IF NOT EXISTS durTreeLevels (
   treeNumber INTEGER,
   levelNumber INTEGER,
   nodes TEXT,
   UNIQUE (treeNumber, levelNumber))*

  As per your suggestion, I am planning to create two tables.
1. TreeInfo:  id (primarykey autoincrement), node, parent node
2.  LevelsInfo: TreeNum(primary key), levlelnum int, node, foreignkey
(TreeNum) references TreeInfo(id)

Here, for insert/update/search, we need to execute more than  two on two
tables.

If I understood wrongly, please correct me.

Thanks,
Durga.

On Mon, Jan 2, 2012 at 12:31 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 2 Jan 2012, at 5:25am, Durga D wrote:
>
> > "create table if not exists durtree  (id integer primary key
> autoincrement,
> > c1 varchar[260], c2 varchar[260], c3 varchar[260], c4 varchar[260], c5
> > varchar[260], c6 varchar[260], c7 varchar[260], c8 varchar[260], c9
> > varchar[260], c10 varchar[260], c11 varchar[260], c12 varchar[260], c13
> > varchar[260], c14 varchar[260], c15 varchar[260], c16 varchar[260], c17
> > varchar[260], c18 varchar[260], c19 varchar[260], c20 varchar[260], c21
> > varchar[260], c22 varchar[260], c23 varchar[260], c24 varchar[260], c25
> > varchar[260], c26 varchar[260], c27 varchar[260], c28 varchar[260], c29
> > varchar[260], c30 varchar[260], c31 varchar[260], c32 varchar[260], c33
> > varchar[260], c34 varchar[260], c35 varchar[260], c36 varchar[260], c37
> > varchar[260], c38 varchar[260], c39 varchar[260], c40 varchar[260], c41
> > varchar[260], c42 varchar[260] default null, c43 varchar[260] default
> null,
> > c44 varchar[260] default null, c45 varchar[260] default null, c46
> > varchar[260] default null, c47 varchar[260] default null, c48
> varchar[260]
> > default null, c49 varchar[260] default null, c50 varchar[260] default
> null,
> > c51 varchar[260] default null, c52 varchar[260] defaul null, c53
> > varchar[260] default null, c54 varchar[260] defaul null, c55 varchar[260]
> > default null, c56 varchar[260] default null, c57 varchar[260] default
> null,
> > c58 varchar[260] default null, c59 varchar[260] default null, c60
> > varchar[260] default null, c61 varchar[260] default null, c62
> varchar[260]
> > default null, c63 varchar[260] default null, c64 varchar[260] default
> null,
> > c65 varchar[260] default null, c66 varchar[260] default null, c67
> > varchar[260] default null, c68 varchar[260] default null, c69
> varchar[260]
> > default null, c70 varchar[260] default null, unique (c1, c2, c3, c4, c5,
> > c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20,
> c21,
> > c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35,
> c36,
> > c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50,
> c51,
> > c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65,
> c66,
> > c67, c68, c69, c70));"
>
> There is no such datatype as 'varchar' in SQLite.  The ones you've
> specified will be treated as TEXT and SQLite will not do anything about the
> length.
> You don't need to specify DEFAULT NULL in SQLite.  That's always the
> default unless you specify otherwise.
>
> Besides which, almost any schema where you have to number your columns is
> a bad schema.  You should be able to hold the entire schema in your head at
> one time.
>
> > I want to make c1 to c70 as unique with default null. But, I could not
> with
> > above query. I can make c42 to c70 as default null  and c1 to c70 as
> > unique. If I add default null to c41 and c40, it gets failed.
>
> The above does not make each of the c nodes unique.  It makes each
> combination of c nodes unique: every single c would have to be identical in
> two rows for SQLite to reject it as violating your UNIQUE constraint.
>  There's no way to make each node unique in the above schema because a node
> could be in c65 in one row and c66 in another row.
>
> A data structure more used for storing trees would look something like
> this:
>
> CREATE TABLE IF NOT EXISTS durTreeNodes (
>treeNumber INTEGER,
>parent TEXT,
>nodes TEXT NOT NULL,
>UNIQUE (treeNumber, node))
>
> This allows for trees of any height to be held efficiently.  The parent
> value of the root node could be null, or 'root' or something.
>
> > objective: I am trying to store tree in a sqlite3 db depth of 70.  I need
> > high performance when accessing any level of the tree.
>
> The above schema, with its very large number of columns, is not going to
> be very fast.  To retrieve c70 from a row SQLite will need to count through
> 70 columns before it can ge

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Kreibich,

You understood the problem perfectly. Thank you.

*>>I  would just suggest using a single composite path value, >>rather
than trying to break the path value down into >>individual components
and store those each in their own >>column.*

I already with single column (/root/local/bin/create_db), but search
and listing is very difficult with single query. for ex: under root, lising
distinct folders and files.  So, I am planning to  redesign the db for
filepaths. I will go through Joe books and let you know.

Thanks in advance.

Thanks,
Durga.

On Mon, Jan 2, 2012 at 9:55 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:

> On Mon, Jan 02, 2012 at 07:01:29AM +, Simon Slavin scratched on the
> wall:
> >
> > On 2 Jan 2012, at 5:25am, Durga D wrote:
> >
> > > "create table if not exists durtree  (id integer primary key
> autoincrement,
>
> [...]
>
> > Besides which, almost any schema where you have to number your columns is
> > a bad schema.  You should be able to hold the entire schema in your head
> > at one time.
>
>   In the sense of being a properly normalized and theoretically ideal
>  schema, I would agree, but the real world has a tendency of getting
>  in the way of theoretically ideals.
>
>  "Normalized until it hurts, denormalize until it works."
>
> > > I want to make c1 to c70 as unique with default null. But, I could not
> with
> > > above query. I can make c42 to c70 as default null  and c1 to c70 as
> > > unique. If I add default null to c41 and c40, it gets failed.
> >
> > The above does not make each of the c nodes unique.  It makes each
> > combination of c nodes unique: every single c would have to be identical
> > in two rows for SQLite to reject it as violating your UNIQUE constraint.
>
>   Exactly?  If he's looking to store a tree that allows direct access
>  to specific nodes, the numbered columns are essentially storing a path
>  through the tree.  Any single node can have multiple children (or
>  itself be a node), so the individual columns don't need to be unique.
>  The path as a whole, however, must be unique to represent a single node.
>
> > There's no way to make each node unique in the above schema because a
> > node could be in c65 in one row and c66 in another row.
>
>   I'm not following you.  Many types of trees allow leaf nodes at
>  different levels.
>
> > A data structure more used for storing trees would look something like
> this:
> >
> > CREATE TABLE IF NOT EXISTS durTreeNodes (
> >   treeNumber INTEGER,
> >   parent TEXT,
> >   nodes TEXT NOT NULL,
> >   UNIQUE (treeNumber, node))
> >
> > This allows for trees of any height to be held efficiently.  The parent
> > value of the root node could be null, or 'root' or something.
>
>   *Held* efficiently, perhaps-- but not accessed.  What you're
>  describing is the "Adjacency Model", which is the most basic way to
>  store tree-like structures in SQL.  It has some serious drawbacks,
>  however.  Standard SQL has no type of recursive or looping features,
>  meaning "walking" a tree to find a specific node, even when the full
>  path is known, takes multiple queries.  In specific, it takes as many
>  queries as the node is deep in the tree.  This makes it extremely
>  slow.  Oracle has some syntax short-cuts to deal with this, but they're
>  non-standard.
>
>  Your unique constraint is also radically different.  You're forcing
>  nodes to be unique across whole trees, while the original schema only
>  forces them to be unique if they have the same parent (which is
>  usually what most people want).
>
>  These compromises are are part of the reason there are so many different
>  models to store tree-based structures in an SQL database (or even in
>  many "NoSQL" systems, for that matter).  Each method has specific
>  advantages and disadvantages.  Some are compact to store, but slow to
>  access, others (like the "nested set") are quick to access, but very
>  expensive to modify.
>
>  Using a "path" based approach is definitely a valid approach.  Most of
>  the time it is done with a single column, however, using some type of
>  delimiter for the tree levels.  For example, have one column with the
>  value "/usr/local/bin" rather than {c1="usr", c2="local", c3="bin"}.
>  You can then use a UNIQUE constraint on just that one column.
>  Wild-card LIKE matches allow access to sub-trees.  Using a single
>  composite path value also eliminates a specific depth limit.
>
> > > objective: I am trying to store tree in a sqlite3 db

Re: [sqlite] search

[Durga D]

Dear Aris,

 Wish you a happy new year.

 I agree.

I think, it may not support for search with wild chars. like vil*

Can I get wild char search in leaf node or parent node with XML?

Thanks,
Durga.

On Fri, Dec 30, 2011 at 8:05 PM, Aris Setyawan <aris.s...@gmail.com> wrote:

> Hi Durga,
>
> Another alternative, you can use an xml database. It will fix your
> problem easily using xquery, like this:
>doc('region')//country/title/text() -> it will show all region you have
>doc('region')//village/title/text() -> it will show all village you have
>
> You also can use selection too (where condition).
> http://en.wikibooks.org/wiki/XQuery/XPath_examples
> http://sedna.org
>
> SQLite with fts is my favorite, but for tree like data structure I
> will use xml database.
>
> -aris
>
> On 12/28/11, Durga D <durga.d...@gmail.com> wrote:
> > Dear Michael.Black.
> >
> > It's correct.
> >
> >I need to design database to store file paths and their info like
> > size. I have an idea
> >   item(file or folder), level0(imm. parent), level1(grand parent)
> > to level160(ancestor), type(file type or folder type).
> >
> >  primary key: (item, level0 to level160)
> >
> >  Is it correct approach? This is from server side. Need to store
> > millions of records.
> >
> > Need optimum relationship between folders and files uniquely.
> >
> > for ex: c:/mydocs/home/a.doc
> > c:/mydocs/office/agreement.doc
> >
> >   insertion of filepaths,deltion of file paths are enough. should be able
> > to search by folder wise also.
> >
> >any ideas?
> >
> > Thanks in advance.
> >
> > On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) <
> michael.bla...@ngc.com
> >> wrote:
> >
> >> I don't know if FTS or a normal table will matter here but just
> normalize
> >> the whole thing.
> >>
> >>
> >>
> >> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
> >>
> >> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
> >> faster.
> >>
> >>
> >>
> >> INSERT INTO virfts4 VALUES(1,'CO','country1');
> >>
> >> INSERT INTO virfts4 VALUES(1,'ST','state1');
> >>
> >> INSERT INTO virfts4 VALUES(1,'CI','city1');
> >>
> >> INSERT INTO virfts4 VALUES(1,'VI','village1');
> >>
> >> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
> >>
> >>
> >>
> >> You can store as many levels as you want.
> >>
> >>
> >>
> >> Michael D. Black
> >>
> >> Senior Scientist
> >>
> >> Advanced Analytics Directorate
> >>
> >> Advanced GEOINT Solutions Operating Unit
> >>
> >> Northrop Grumman Information Systems
> >>
> >> 
> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> >> on behalf of Durga D [durga.d...@gmail.com]
> >> Sent: Tuesday, December 27, 2011 4:27 AM
> >> To: General Discussion of SQLite Database
> >> Subject: EXT :Re: [sqlite] search
> >>
> >> Thank you. I agree. It's correct.
> >>
> >> I already have data base with /country/state/city/village format. Is it
> >> possible to do that while virtual table creation time?
> >>
> >> if yes, how?
> >>
> >> in case, if I have 250 levels like this ex: file system. how to do this.
> >> any idea?
> >>
> >> Thanks in advance.
> >>
> >> On Tue, Dec 27, 2011 at 3:38 PM, Kit <kit.sa...@gmail.com> wrote:
> >>
> >> > 2011/12/27 Durga D <durga.d...@gmail.com>:
> >> > > select * from virfts4 where residence match '/*'; -- dint work
> >> > > how to get counties names from this db by using query?
> >> >
> >> > Normalize database to 1NF, e.g.
> >> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> >> > arrivtime, duration, imagelocation);
> >> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> >> > 0730, 1500,'C');
> >> >
> >> > then use select:
> >> > SELECT DISTINCT country FROM virfts4;
> >> > --
> >> > Kit
> >> > ___
> >> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Thanks for your responses.

Wish you a Happy new year.

I tested with below query to create a table:

"create table if not exists durtree  (id integer primary key autoincrement,
c1 varchar[260], c2 varchar[260], c3 varchar[260], c4 varchar[260], c5
varchar[260], c6 varchar[260], c7 varchar[260], c8 varchar[260], c9
varchar[260], c10 varchar[260], c11 varchar[260], c12 varchar[260], c13
varchar[260], c14 varchar[260], c15 varchar[260], c16 varchar[260], c17
varchar[260], c18 varchar[260], c19 varchar[260], c20 varchar[260], c21
varchar[260], c22 varchar[260], c23 varchar[260], c24 varchar[260], c25
varchar[260], c26 varchar[260], c27 varchar[260], c28 varchar[260], c29
varchar[260], c30 varchar[260], c31 varchar[260], c32 varchar[260], c33
varchar[260], c34 varchar[260], c35 varchar[260], c36 varchar[260], c37
varchar[260], c38 varchar[260], c39 varchar[260], c40 varchar[260], c41
varchar[260], c42 varchar[260] default null, c43 varchar[260] default null,
c44 varchar[260] default null, c45 varchar[260] default null, c46
varchar[260] default null, c47 varchar[260] default null, c48 varchar[260]
default null, c49 varchar[260] default null, c50 varchar[260] default null,
c51 varchar[260] default null, c52 varchar[260] defaul null, c53
varchar[260] default null, c54 varchar[260] defaul null, c55 varchar[260]
default null, c56 varchar[260] default null, c57 varchar[260] default null,
c58 varchar[260] default null, c59 varchar[260] default null, c60
varchar[260] default null, c61 varchar[260] default null, c62 varchar[260]
default null, c63 varchar[260] default null, c64 varchar[260] default null,
c65 varchar[260] default null, c66 varchar[260] default null, c67
varchar[260] default null, c68 varchar[260] default null, c69 varchar[260]
default null, c70 varchar[260] default null, unique (c1, c2, c3, c4, c5,
c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21,
c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36,
c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51,
c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66,
c67, c68, c69, c70));"


I want to make c1 to c70 as unique with default null. But, I could not with
above query. I can make c42 to c70 as default null  and c1 to c70 as
unique. If I add default null to c41 and c40, it gets failed.

objective: I am trying to store tree in a sqlite3 db depth of 70.  I need
high performance when accessing any level of the tree.

Thanks  in advance,
Durga.

On Fri, Dec 30, 2011 at 11:17 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 30 Dec 2011, at 4:40pm, Jay A. Kreibich wrote:
>
> > On Fri, Dec 30, 2011 at 04:10:55PM +0530, Durga D scratched on the wall:
> >> Hi all,
> >>
> >>I have sqlite table with 71 columns. not null for 70 and one is
> >> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for
> 70
> >> columns and unique(70 columns). It's worked fine upto 30 columns not
> null
> >> and unique(70 columns).
> >>
> >>  I need 70 columns with unique and not null. How to enable it?
> >
> >  Do you mean 70 columns that each have a single-column unique
> >  constraint, or a single unique constraint that crosses 70 columns?
>
> I think he means he has 70 columns, each of which have to be unique.
>  Which, of course, means he'll have 70 implicit indexes on that table.
>  It'll make inserting things slow, but I don't why any reason why SQLite
> shouldn't do it.  On the other hand, I wouldn't want to make SQLite update
> 70 columns each time I INSERTed a row, and I'd probably rely on my
> application to do it instead of the DBMS.
>
> Durga, here are the limits to the number of things you can have in SQLite:
>
> <http://www.sqlite.org/limits.html>
>
> No mention of a maximum number of indexes.  But frankly I'm suspicious of
> any table which has 70 columns.  You should be able to hold all the columns
> of a table in your head at once, and I lose my place long before 70 columns.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Hi all,

I have sqlite table with 71 columns. not null for 70 and one is
surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
columns and unique(70 columns). It's worked fine upto 30 columns not null
and unique(70 columns).

  I need 70 columns with unique and not null. How to enable it?

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Durga D]

Dear Michael.Black.

It's correct.

   I need to design database to store file paths and their info like
size. I have an idea
  item(file or folder), level0(imm. parent), level1(grand parent)
to level160(ancestor), type(file type or folder type).

 primary key: (item, level0 to level160)

 Is it correct approach? This is from server side. Need to store
millions of records.

Need optimum relationship between folders and files uniquely.

for ex: c:/mydocs/home/a.doc
c:/mydocs/office/agreement.doc

  insertion of filepaths,deltion of file paths are enough. should be able
to search by folder wise also.

   any ideas?

Thanks in advance.

On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> I don't know if FTS or a normal table will matter here but just normalize
> the whole thing.
>
>
>
> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>
> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
> faster.
>
>
>
> INSERT INTO virfts4 VALUES(1,'CO','country1');
>
> INSERT INTO virfts4 VALUES(1,'ST','state1');
>
> INSERT INTO virfts4 VALUES(1,'CI','city1');
>
> INSERT INTO virfts4 VALUES(1,'VI','village1');
>
> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>
>
>
> You can store as many levels as you want.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> ____
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Durga D [durga.d...@gmail.com]
> Sent: Tuesday, December 27, 2011 4:27 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] search
>
> Thank you. I agree. It's correct.
>
> I already have data base with /country/state/city/village format. Is it
> possible to do that while virtual table creation time?
>
> if yes, how?
>
> in case, if I have 250 levels like this ex: file system. how to do this.
> any idea?
>
> Thanks in advance.
>
> On Tue, Dec 27, 2011 at 3:38 PM, Kit <kit.sa...@gmail.com> wrote:
>
> > 2011/12/27 Durga D <durga.d...@gmail.com>:
> > > select * from virfts4 where residence match '/*'; -- dint work
> > > how to get counties names from this db by using query?
> >
> > Normalize database to 1NF, e.g.
> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> > arrivtime, duration, imagelocation);
> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> > 0730, 1500,'C');
> >
> > then use select:
> > SELECT DISTINCT country FROM virfts4;
> > --
> > Kit
> > ___
> > 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] search

[Durga D]

Mohit,

  sqlite> .separator /
  sqlite> create virtual table virfts4 using fts4 (residence);
  sqlite> insert into  virfts4  select * from source
  sqlite> select count(*) from  virfts4  where residence match '/*'; --
result is 0.

  Please correct it. I think, i am doing something wrong.

Note: there is no spelling mistakes. all sql stmts compiled. but dint get
result. result should be greater than 0. it has 4 records.

Thanks in advance.

On Tue, Dec 27, 2011 at 7:40 PM, Mohit Sindhwani  wrote:

> On 27/12/2011 9:25 PM, Eduardo Morras wrote:
>
>> At 11:27 27/12/2011, you wrote:
>>
>>> Thank you. I agree. It's correct.
>>>
>>> I already have data base with /country/state/city/village format. Is it
>>> possible to do that while virtual table creation time?
>>>
>>> if yes, how?
>>>
>>
>> Change '/' to ','. This way you get a csv file which you can import
>> directly. Perhaps you need to add a line with the table column description.
>>
>>
> or at the SQLite3 shell do
> sqlite>separator /
> before doing a .import
>
> no?
>
> Cheers,
> Mohit.
> 27/12/2011 | 10:10 PM.
>
>
> __**_
> 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] search

[Durga D]

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit <kit.sa...@gmail.com> wrote:

> 2011/12/27 Durga D <durga.d...@gmail.com>:
> > select * from virfts4 where residence match '/*'; -- dint work
> > how to get counties names from this db by using query?
>
> Normalize database to 1NF, e.g.
> CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> arrivtime, duration, imagelocation);
> INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> 0730, 1500,'C');
>
> then use select:
> SELECT DISTINCT country FROM virfts4;
> --
> Kit
> ___
> 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] search

[Durga D]

Hi kit,

   Thanks for your response.

CREATE VIRTUAL TABLE virfts4 using fts4(residence, arrivtime, duration,
imagelocation);

insert into virfts4 values('/country1/state1/city1/village1', 0730, 1500,
'C');
insert into virfts4 values('/country1/state1/city1/village2', 0731, 1500,
'C');
insert into virfts4 values('/countr1/state1/village1/', 1800, 1000, 'D');

select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?

select * from virfts4 where residence match '/c*'; -- it's worked and very
fast.

Thanks in advance.

On Tue, Dec 27, 2011 at 2:01 PM, Kit <kit.sa...@gmail.com> wrote:

> 2011/12/27 Durga D <durga.d...@gmail.com>:
> > HI all,
> >   I have sqlite database with more than 1000 records. Here,
> > residencearea is the primary key.
> > /country/state/city/village
> >   I tried with fts3 and fts4 virtual tables. Not much performance.
> >   like query on direct table, dead slow.
>
> Send your SQL query and table structure.
>
> Maybe you used LIKE instead of MATCH.
> --
> Kit
> ___
> 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] search

[Durga D]

HI all,

   I have sqlite database with more than 1000 records. Here,
residencearea is the primary key.

   for ex:

/country/state/city/village
/country/city
/country/state/city
/country/

country: USA, UK, CHINA


   Here I want to search based on country and sometimes search based on
village and sometimes search based on /country/state/.

   I tried with fts3 and fts4 virtual tables. Not much performance.

   like query on direct table, dead slow.

   Any alternate solution for this?

Thanks in advance.

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

Re: [sqlite] String field as primarykey

[Durga D]

journal file is creating between transactions.

On Tue, Oct 12, 2010 at 3:00 PM, Oliver Peters <oliver@web.de> wrote:

> Durga D <durga.d...@...> writes:
>
> [...]
>
> >  But performance issue is there.
> >
> >   Is there anyway to optimize this?
> >
>
>
> maybe you should try
>
> PRAGMA journal_mode = OFF;
>
>
> Oliver
>
> ___
> 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] String field as primarykey

[Durga D]

Hi Oliver,

   Thank you for your prompt response.

   I am using C++ wrappers in VC++ 8.0 applicaiton.

   Where should I add PRAGMA?

   And also  when I insert 100k records by using statement,
"m_oDB.execDML(strQuery);"
virtual memory is keep on increasing. Any idea?

Durga.

On Tue, Oct 12, 2010 at 3:00 PM, Oliver Peters <oliver@web.de> wrote:

> Durga D <durga.d...@...> writes:
>
> [...]
>
> >  But performance issue is there.
> >
> >   Is there anyway to optimize this?
> >
>
>
> maybe you should try
>
> PRAGMA journal_mode = OFF;
>
>
> Oliver
>
> ___
> 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] String field as primarykey

[Durga D]

Hi All,

I have created a sqlite database with "create table EMP (F1 INTEGER ,F2
TEXT(1120) primary key collate nocase,F3 INTEGER ,LMD TEXT(1120) ,F4
TEXT(1120) ,F5 TEXT(1120));".

   Here F2(Field2) is primary key. I used transaction queries for every
25,000 records. But performance issue is there.

  Is there anyway to optimize this?

  Requirement: Insert 100,000,000 records.

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

Re: [sqlite] "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON

[Durga D]

Try to launch your appliztion with "Run As Administrator"(right click on exe
-> Run As..).
Note: Take care about fields information for old DB and new DB while opening
tables(backward compatibility). For ex: old DB might have x number of fields
and new DB might have y number fields where x is not equal to y but x and y
greater than 0.

Regards,
Durga.
On Tue, Jul 20, 2010 at 1:58 PM,  wrote:

> In my case, the DB is created by the older version of application and
> now the new version of application is installed and End user has option
> to see old data with selecting old version of DB so its completely on
> end user wish to choose the location for DB. And also new tables in DB
> are created to new version of application, on select of old DB,
> application has to create those tables in OLD DB also to synch.
>
> Application is successfully executing select query on UAC-ON however
> while executing DDL and DML it is throwing "Unable to open Database".
>
> Please guide us on this issue.
>
> Please let me know if it is not clear to you all.
>
> Thanks,
> Preeti
>
>
>
> 
>
> From: Preeti Yadav (WT01 - Healthcare and Services)
> Sent: Monday, July 19, 2010 1:47 PM
> To: 'sqlite-users@sqlite.org'
> Subject: "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON
>
>
> Hello Team,
>
> I am using SQLite DB for  java/Swings based desktop application. We are
> facing below issue on Win-7 & Vista (64 bit) Operating System only on
> UAC -ON (User access Control).
>
> Current version of application is unable to do any DDL/ DML on DB
> Created by older version of application on  Win-7 & Vista (64 bit)
> Operating Systems only on UAC -ON (User access Control). We found that
> DB is throwing "Unable to Open DB".  Everything is working perfectly if
> we set UAC to Never Notify.
>
> We do not have any problems with other OS like Win-xp,  Mac, etc.
>
> We request you to please guide us on this issue.
>
> Please let me know if you need more information.
>
> Thanks in advance for your kind help!!
>
> Warm Regards,
> Preeti
>
>
>  Please do not print this email unless it is absolutely necessary.
>
> The information contained in this electronic message and any attachments to
> this message are intended for the exclusive use of the addressee(s) and may
> contain proprietary, confidential or privileged information. If you are not
> the intended recipient, you should not disseminate, distribute or copy this
> e-mail. Please notify the sender immediately and destroy all copies of this
> message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of viruses. The
> company accepts no liability for any damage caused by any virus transmitted
> by this email.
>
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bulk Insertions allowing duplicate records even primary key existed

[Durga D]

Hi All,

I am developing an application with Sqlite3 Database in VC++ 6.0. Here,
I tried for bulk insertions with CppSQLite3Statement. But, It's allowing
duplicate records in my database even primary key existed.

   First, I inserted 5 records with insert query and transactions for
every 25000.
   Second, same data inserted by prepared statement CppSQLite3Statement and
transactions for every 25000.

   Surprised. Can anyone clear my doubt?

   How to resolve this issue?

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