Re: [sqlite] Expected behavior for begin exclusive?/database locked?

[Rosemary Alles]

I established re-try logic  to get this to work. :(


On Jun 5, 2009, at 7:08 PM, Rosemary Alles wrote:

>
> I have several (identical processors) accessing a sqlite3 database  
> over NFS. I have a busy handler (see below) and use "begin exclusive"
>
> Periodically I get the following error from from sqlite3:
>
> Function:artd_sql_exec_stmt error in stmt:begin exclusive against  
> database:/wise/fops/ref/artid/latents.db, error:[database is locked]
>
> I was under the impression that sqlite3 should return SQLITE_BUSY  
> under these conditions?
>
> Here is my busy handler:
>
> PASSFAIL
> artid_sql_busy_handler(void* p_arg_one, int num_prior_calls) {
>
>  // Adding a random value here greatly reduces locking?
>  if (*((int *)p_arg_one) < ARTID_ZERO) {
>usleep((rand() % 5) + 4); // Change magic number after test
>  }
>  else {
>usleep(5); // Change magic number after test
>  }
>
>  /*
>   * Have sqlite3_exec immediately return SQLITE_BUSY or
>   * sleep if handler has been called less than
>   * threshold value. 50 secs?
>   */
>  return (num_prior_calls < ARTID_THOUSAND);
> }
>
>
> How would I know if my busy_handler (is even) being called? And how  
> do I avoid the said error?
>
> Thanks,
> -rosemary.
>
>

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

[sqlite] Expected behavior for begin exclusive?/database locked?

[Rosemary Alles]

I have several (identical processors) accessing a sqlite3 database  
over NFS. I have a busy handler (see below) and use "begin exclusive"

Periodically I get the following error from from sqlite3:

Function:artd_sql_exec_stmt error in stmt:begin exclusive against  
database:/wise/fops/ref/artid/latents.db, error:[database is locked]

I was under the impression that sqlite3 should return SQLITE_BUSY  
under these conditions?

Here is my busy handler:

PASSFAIL
artid_sql_busy_handler(void* p_arg_one, int num_prior_calls) {

   // Adding a random value here greatly reduces locking?
   if (*((int *)p_arg_one) < ARTID_ZERO) {
 usleep((rand() % 5) + 4); // Change magic number after test
   }
   else {
 usleep(5); // Change magic number after test
   }

   /*
* Have sqlite3_exec immediately return SQLITE_BUSY or
* sleep if handler has been called less than
* threshold value. 50 secs?
*/
   return (num_prior_calls < ARTID_THOUSAND);
}


How would I know if my busy_handler (is even) being called? And how do  
I avoid the said error?

Thanks,
-rosemary.



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

Re: [sqlite] Getting last inserted rowid?

[Nuno Lucas]

Last try...

On Sat, Jun 6, 2009 at 1:30 AM, Nikolaus Rath wrote:
> John Machin  writes:
>> On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
>>> John Machin  writes:
> Now I'm confused. I want to know if it will be sufficient to wrap my
> last_insert_rowid() call between BEGIN .. and END in order to make it
> return the rowid that was last inserted by the same thread even if
> multiple threads are using the same connection (but different cursors).
>
> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
> saying that it's not sufficient, I need to use additional mechanism.

Forget all I and Igor said...

You have 2 choices to have consistent last_insert_rowid() after an
INSERT using threads sharing the same connection handle:

1) Wrap the "INSERT..." ==> "last_insert_rowid()" inside a BEGIN/END
transaction.

Advantages:
- No need for mutexes or other synchronization primitive
- Other threads that don't use transactions can still read from the
database if they don't require the last_insert_rowid() value (this can
be of no great importance as they will still find the database busy
while doing the insert, which is probably the big majority of the time
of the operation)

Disadvantages:
- You need to check for the nested transaction error and retry
- Implies 2 more automatic mutex locks done by the sqlite library for
the BEGIN and END (probably negligible time on most cases but maybe
important when dealing with a lot of threads)

2) Wrap  a mutex or other synchronization primitive around the
"INSERT" ==> "last_insert_rowid()" calls.

Advantages:
- 1 less mutex held (although this one has a longer duration)
- No need to check for a specific error condition. Other threads just
wait until they can proceed.

Disadvantages:
- Other threads are locked for the full duration of the event (as in
the point 1, probably also not that important, as the insert will make
most of the time)
- You need to share the mutex to all threads involved and removes some
flexibility for the algorithm design.


The pros/cons relating to the mutexes held can very well be inverted
depending on the specific code being run.
If you don't use the same connection handle, then all this is moot, as
all synchronization is done at the file level.
I didn't try to be exhaustive. That is your job.

Now, if you still have doubts after this I don't think I can be of any
help, as that makes it obvious my communication skills are lacking.


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

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

John Machin  writes:
> On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
>> John Machin  writes:
 Now I'm confused. I want to know if it will be sufficient to wrap my
 last_insert_rowid() call between BEGIN .. and END in order to make it
 return the rowid that was last inserted by the same thread even if
 multiple threads are using the same connection (but different cursors).

 As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
 saying that it's not sufficient, I need to use additional mechanism.
>>> As Igor pointed out, if you have multiple threads using the same 
>>> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
>>> If you don't have that, yes you need to "use additional mechanism" BUT 
>>> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
>>> added a rider like "(presuming your existing code is not stuffed)".
>> 
>> Are you saying that I need to use mutexes or whatever in the following
>> program?
>> 
>> def thread1():
>> cur = conn.cursor()
>> for i in range(500):
>> cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)
>> 
>> def thread2():
>> cur = conn.cursor()
>> for i in range(500):
>> cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)
>> 
>> threading.Thread(target=thread1).start()
>> threading.Thread(target=thread2).start()
>
> Somebody needs to use mutexes somewhere. You have obscured the question 
> by introducing two unknowns: (1) Which Python wrapper are you using 
> (sqlite3 module or the apsw module)? (2) What does it do under the 
> covers? Try asking the relevant guru for whatever you are using.

It's made for apsw and it shouldn't do anything under the hood. Consider
the same example in C if it helps.

 Where am I wrong?
>>> In wasting time on semantic confusion instead of implementing it and 
>>> testing the bejaysus out of it.
>> 
>> When you are working with a multithreaded program, you can't even hope
>> to test a fraction of the possible state trajectories. Finding the
>> proper implementation by trial & error is therefore even more hopeless
>> than in a single threaded program.
>
> If you can't test it, then how will you know whether *any* 
> implementation is "proper", let alone *the* "proper" one?

Of course you have to test once you have defined proper behavior. But my
question in this thread is essentially what the "proper" behavior is.

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] Getting last inserted rowid?

[Igor Tandetnik]

Nikolaus Rath  wrote:
>>> Is BEGIN ... COMMIT sufficient for that?
>>
>> No. Transaction is also maintained per connection. Starting a
>> transaction would prevent other connections from making concurrent
>> changes, but wouldn't block other threads using the same connection.
>
> Actually it seems to do exactly that:
>
>>
>> def thread1():
>> cur = conn.cursor()
>> cur.execute("BEGIN")
>> print "Thread 1 in transaction"
>> time.sleep(5)
>> print "Thread 1 finished."
>>
>> def thread2():
>> cur = conn.cursor()
>> time.sleep(2)
>> cur.execute("BEGIN")
>> print "Thread 2 in transaction"
>> time.sleep(5)
>> print "Thread 2 finished."

What you see is the effect that Nuno Lucas pointed out: if you try to 
execute BEGIN while there's an open transaction, you'll get an error. It 
doesn't matter whether the nested BEGIN is executed on the same or 
different thread.

This behavior doesn't mean that starting a transaction blocks out other 
threads - they can happily execute any statements (which would then 
become part of the open transaction), just not BEGIN.

So yes, you can use BEGIN statement as a test to see whether a 
transaction is open on a connection. I must admit I haven't thought of 
that. See also sqlite3_get_autocommit.

Igor Tandetnik 



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

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath wrote:
>> "Igor Tandetnik"  writes:
>>> Nikolaus Rath  wrote:
 How can I determine the rowid of the last insert if I am accessing
 the db from different threads? If I understand correctly,
 last_insert_rowid() won't work reliably in this case.
>>>
>>> Last inserted rowid is maintained per connection. Do your threads use
>>> the same connection, or each create their own?
>>
>> Same connection, just different cursors.
>>
>>> If all threads share the same connection, it is your responsibility
>>> to make "insert then retrieve last rowid" an atomic operation, using
>>> thread synchronization mechanism of your choice. Just as with any
>>> access to shared data.
>>
>> Is BEGIN ... COMMIT sufficient for that?
>
> No. Transaction is also maintained per connection. Starting a 
> transaction would prevent other connections from making concurrent 
> changes, but wouldn't block other threads using the same connection.

Actually it seems to do exactly that:

,
| $ cat test.py 
| import apsw
| import threading
| import time
| 
| conn = apsw.Connection('test.db')
| 
| def thread1():
| cur = conn.cursor()
| cur.execute("BEGIN")
| print "Thread 1 in transaction"
| time.sleep(5)
| print "Thread 1 finished."
| 
| def thread2():
| cur = conn.cursor()
| time.sleep(2)
| cur.execute("BEGIN")
| print "Thread 2 in transaction"
| time.sleep(5)
| print "Thread 2 finished."
| 
| 
| threading.Thread(target=thread1).start()
| threading.Thread(target=thread2).start()
`

,
| $ python test.py 
| Thread 1 in transaction
| Exception in thread Thread-2:
| Traceback (most recent call last):
|   File "/usr/lib/python2.6/threading.py", line 525, in __bootstrap_inner
| self.run()
|   File "/usr/lib/python2.6/threading.py", line 477, in run
| self.__target(*self.__args, **self.__kwargs)
|   File "test.py", line 17, in thread2
| cur.execute("BEGIN")
|   File "apsw.c", line 4238, in resetcursor
| SQLError: SQLError: cannot start a transaction within a transaction
| 
| Thread 1 finished.
`


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] Getting last inserted rowid?

[John Machin]

On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
> John Machin  writes:
>>> Now I'm confused. I want to know if it will be sufficient to wrap my
>>> last_insert_rowid() call between BEGIN .. and END in order to make it
>>> return the rowid that was last inserted by the same thread even if
>>> multiple threads are using the same connection (but different cursors).
>>>
>>> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
>>> saying that it's not sufficient, I need to use additional mechanism.
>> As Igor pointed out, if you have multiple threads using the same 
>> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
>> If you don't have that, yes you need to "use additional mechanism" BUT 
>> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
>> added a rider like "(presuming your existing code is not stuffed)".
> 
> Are you saying that I need to use mutexes or whatever in the following
> program?
> 
> def thread1():
> cur = conn.cursor()
> for i in range(500):
> cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)
> 
> def thread2():
> cur = conn.cursor()
> for i in range(500):
> cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)
> 
> threading.Thread(target=thread1).start()
> threading.Thread(target=thread2).start()

Somebody needs to use mutexes somewhere. You have obscured the question 
by introducing two unknowns: (1) Which Python wrapper are you using 
(sqlite3 module or the apsw module)? (2) What does it do under the 
covers? Try asking the relevant guru for whatever you are using.

>>> Where am I wrong?
>> In wasting time on semantic confusion instead of implementing it and 
>> testing the bejaysus out of it.
> 
> When you are working with a multithreaded program, you can't even hope
> to test a fraction of the possible state trajectories. Finding the
> proper implementation by trial & error is therefore even more hopeless
> than in a single threaded program.

If you can't test it, then how will you know whether *any* 
implementation is "proper", let alone *the* "proper" one?

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

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

John Machin  writes:
>> Now I'm confused. I want to know if it will be sufficient to wrap my
>> last_insert_rowid() call between BEGIN .. and END in order to make it
>> return the rowid that was last inserted by the same thread even if
>> multiple threads are using the same connection (but different cursors).
>> 
>> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
>> saying that it's not sufficient, I need to use additional mechanism.
>
> As Igor pointed out, if you have multiple threads using the same 
> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
> If you don't have that, yes you need to "use additional mechanism" BUT 
> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
> added a rider like "(presuming your existing code is not stuffed)".

Are you saying that I need to use mutexes or whatever in the following
program?

def thread1():
cur = conn.cursor()
for i in range(500):
cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)

def thread2():
cur = conn.cursor()
for i in range(500):
cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)

threading.Thread(target=thread1).start()
threading.Thread(target=thread2).start()

   
>> Where am I wrong?
>
> In wasting time on semantic confusion instead of implementing it and 
> testing the bejaysus out of it.

When you are working with a multithreaded program, you can't even hope
to test a fraction of the possible state trajectories. Finding the
proper implementation by trial & error is therefore even more hopeless
than in a single threaded program.

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

[sqlite] Caching model and aging timeout

[Mike Borland]

Hi!

I have a few questions/observations about the caching model.  Any help
clarifying these issues would be greatly appreciated. (I'm working on a
Windows Server class OS).
 

1) Setting the SQLITE_CONFIG_PAGECACHE keyword within sqlite3_config()
seems to define a single cache for the instance of the database.
However, from what I've observed/read, each connection to the database
has its own cache (Unless the shared cache mode is enabled).  So how
does the single buffer defined by SQLITE_CONFIG_PAGECACHE relate to the
cache for each connection instance?

2) I had stupidly defined the SQLITE_DEFAULT_PAGE_SIZE to be 4096, and
the SQLITE_DEFAULT_CACHE_SIZE to 200,000 within a program that has 15+
database connections.  When I started hammering my program, I saw the
memory shoot up to well over 1.5GB and continue climbing.  I came to
realize that each connection had a maximum cache size of 750MB which
combined would easily exceed the 2GB program limit.  What's interesting
is that after I let the app sit with no work for about an hour and then
executed a single write and commit, the app size shrunk by about 1GB
which implies the cache was cleaned out.  Does anybody have details on
when and how the cache is flushed?


Thanks,
Mike Borland

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

Re: [sqlite] Search in multiple fields.

[Jay A. Kreibich]

On Fri, Jun 05, 2009 at 10:53:58AM -0400, German Escallon scratched on the wall:

> Say I have the following record in my DB:
> 
> Path: /home/media/mymusic
> filename: rock_my_world
> extension: mp3
> 
> I want to be able to find this file by typing any of the following in
> the user interface that I'll provide.

> ?> rock
> ?> rock_my_world
> ?> mp3
> ?> mymusic
> ?> rock_my_world.mp3
> ?> /home/media/mymusic/rock_my_world.mp3

> The above returns the right results for the first 4 cases, but not the
> last two. Any suggestions?? Thank you in advance.

  Build the full path and search against that:

  WHERE (path||filename||'.'||extension LIKE '%%');

  In fact, that's the only WHERE clause you'd need.


  BTW, string constants in SQL are single quotes ('), not double (").

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Db design question (so. like a tree)

[Francis GAYREL]

I am not familiar with breeding business.
My suggestion comes from a very different problem that i solved recently.
The challenge was to describe a tree and  to find the path to the root 
starting from any leaf or intermediate node.
How to do?
1) The entity supported by  the node shall support at least one relative 
precedence criterion  which depends on the nature of the entity.
Then we can specify a Node_Table(Node Primary 
Key,,PrecedenceCriterion)
2) Populate the Node_Table (at least 2 linkable nodes at starting time)
3) Complete the links using a selection filter mainly based on 
precedence (we assume that the user has the expertise to pick the right 
choice)
4) Repeat 2) and 3) as far as necessary
If only one-to-one links are required they can be included in the 
Node_Table model.
If one-to-several links are necessary a separate 
Link_Table(LinkType,OwnerNode,LinkedNode) is recommended. (for example 
Link  types  may be Father,Mother,Clone)

John Machin a écrit :
> On 5/06/2009 5:27 PM, Francis GAYREL wrote:
>   
>> To build a consistent oriented tree we need to associate to the nodes a 
>> ranking property such as the birthdate (or any precedence criterion).
>> Therefore the ancestor of  someone  is to be selected among  older ones.
>> 
>
> "Ancestor" is a *derived* relationship, not something you'd wish to 
> store in the database, and is quite irrelevant at data-entry time. Don't 
> you mean "parent"?
>
> Let me get this straight: the user is entering the details of animal X 
> who was born yesterday and has to input somehow the identity of the 
> mother and of the father.
>
>   
>> To make the ancestor allocation more easy  the ancestor's list may be 
>> filtered on birthdate credibility.
>> 
>
> So your plot is, (e.g. for the father) to show a list of all male 
> animals who are in some credible-parenthood age range on (say) a 
> drop-down list, and the user selects one, hopefully not at random. Is 
> that right?
>
> I would imagine in a planned targeted organised animal breeding program 
> that the mother-to-be and father-to-be are recorded at the time of 
> impregnation, and the identities are established from ear-tags, embedded 
> chips, photographs, etc and the credible-parenthood test is applied then 
> [note: test, NOT input selection method] and all of the above is 
> confirmed at birth.
>
>   
>> The ranking property eliminates the circular link concern.
>> 
>
> Indeed, but you have to use it properly to eliminate other data 
> integrity concerns :-)
>
> HTH,
>
> John
> ___
> 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 in multiple fields.

[Alexandre Courbot]

Hi,

Not sure if that answers your question, but I think you seriously want
to use FTS3. It will be both a trillion times faster than your current
query - you may have trouble with your last example though. Maybe you
can still go through by escaping the search terms.

http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.syntax=1.2

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

[sqlite] Search in multiple fields.

[German Escallon]

Hello all,

I have a table with file information (path, filename, extension, size,
etc), and I would like to enable search on this table based on different
fields with a single input, and/or concatenation of the same. For example..

Say I have the following record in my DB:

Path: /home/media/mymusic
filename: rock_my_world
extension: mp3

I want to be able to find this file by typing any of the following in
the user interface that I'll provide.


?> rock
?> rock_my_world
?> mp3
?> mymusic
?> rock_my_world.mp3
?> /home/media/mymusic/rock_my_world.mp3

I am doing something along the lines of:

SELECT basename, extension FROM tb_file WHERE (basename LIKE
"%%") OR (extension LIKE "%%") OR (path LIKE
"%%");

The above returns the right results for the first 4 cases, but not the
last two. Any suggestions?? Thank you in advance.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "not an error" error inserting data trough a view on fts3 table

[vkharitonov]

I tried the scripts with the command line sqlite3 application. The row
inserted in the data_view. But I received “SQL error: unknown error.”


Pavel Ivanov-2 wrote:
> 
> "Not an error" is SQLITE_OK. Are you sure that you don't do anything
> between getting error code from SQLite and obtaining error message?
> Maybe in another thread?
> 
> Pavel

-- 
View this message in context: 
http://www.nabble.com/%22not-an-error%22-error-inserting-data-trough-a-view-on-fts3-table-tp23883745p23889421.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] Db design question (so. like a tree)

[Stephen Woodbridge]

Here is a relational model that I use for my genealogy. It is in 
postgresql, but it should work fine in SQLite:

All people are stored in the indi table:

CREATE TABLE woodbridge.indi
(
   indi character varying(10) NOT NULL,
   lname character varying(30),
   fname character varying(60),
   title character varying(20),
   lname_sndx character varying(4),
   famc character varying(10),
   sex character(1),
   birt_date character varying(20),
   birt_plac character varying(60),
   chr_date character varying(20),
   chr_plac character varying(60),
   deat_date character varying(20),
   deat_plac character varying(60),
   buri_date character varying(20),
   buri_plac character varying(60),
   refn character varying(20),
   note character varying(10),
   sour character varying(10),
   CONSTRAINT indi_pkey PRIMARY KEY (indi)
)
WITHOUT OIDS;

This is the relationship and indi is placed in a family unit as a child 
of the unit:

CREATE TABLE woodbridge.child
(
   fami character varying(10) NOT NULL,
   seq integer NOT NULL DEFAULT 0,
   indi character varying(10) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY (fami, seq)
)
WITHOUT OIDS;

fams is the relationship where two indi's have a spousal relationship

CREATE TABLE woodbridge.fams
(
   indi character varying(10) NOT NULL,
   seq integer NOT NULL DEFAULT 0,
   fami character varying(10) NOT NULL,
   CONSTRAINT fams_pkey PRIMARY KEY (indi, seq)
)
WITHOUT OIDS;

fami is the description of a family unit, ie a set of parents and 
resulting children:

CREATE TABLE woodbridge.fami
(
   fami character varying(10) NOT NULL,
   husb character varying(10),
   wife character varying(10),
   marr_date character varying(20),
   marr_plac character varying(60),
   div character(1),
   div_date character varying(20),
   div_plac character varying(60),
   sour character varying(10),
   CONSTRAINT fami_pkey PRIMARY KEY (fami)
)
WITHOUT OIDS;

There are additional tables for things like notes and photos.

You can see it running here:

http://swoodbridge.com/family/Woodbridge/

It should be pretty easy to convert this to tables for livestock breeding.

-Steve


Francis GAYREL wrote:
> To build a consistent oriented tree we need to associate to the nodes a 
> ranking property such as the birthdate (or any precedence criterion).
> Therefore the ancestor of  someone  is to be selected among  older ones.
> To make the ancestor allocation more easy  the ancestor's list may be 
> filtered on birthdate credibility.
> The ranking property eliminates the circular link concern.
> 
> 
> Jan a écrit :
>> Hi Mark,
>>
>> I think that wont work:
>>
>> Scenario: A calf is born from a mother within your flock but from a 
>> father outside. The father appears for the first time and you are not 
>> able to gather information on his father (or grand-grand father). 
>> Therefore his father is NULL. But later you get the information on his 
>> father and add it to the animal list: The id of the fathers father is 
>> then greater then the id of his grandchild (the calf).
>> I could start the id initially with 10 to allocate <10 ids in 
>> theses cases, but I am unsure if this is a good way to start.
>>
>> Mark Hamburg schrieb:
>>   
>>> One of the questions that I believe was raised but not answered on  
>>> this thread was how to make sure that you don't have circular  
>>> relationships particularly given that SQLite isn't good at scanning  
>>> the tree. If you can control the id's then simply require that the id  
>>> of the child be greater than the id's of the parents.
>>>
>>> Mark
>>>
>>> ___
>>> 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] "not an error" error inserting data trough a view on fts3 table

[Jay A. Kreibich]

On Fri, Jun 05, 2009 at 11:50:02AM +0400, Vladimir Kharitonov scratched on the 
wall:
> I have a problem inserting data through a view.

> INSERT INTO data_view (id, created, content)
> VALUES (randomblob(16), datetime('now'), 'data');
> 
> I receive strange SQLite Error: ???not an error???.
> I use version 3.6.13

  SQLite VIEWs are read-only.

  From < http://sqlite.org/omitted.html >:
 
  VIEWs in SQLite are read-only. You may not execute a
  DELETE, INSERT, or UPDATE statement on a view. But 
  you can create a trigger that fires on an attempt to
  DELETE, INSERT, or UPDATE a view and do what you need 
  in the body of the trigger. 



   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "not an error" error inserting data trough a view on fts3 table

[Pavel Ivanov]

"Not an error" is SQLITE_OK. Are you sure that you don't do anything
between getting error code from SQLite and obtaining error message?
Maybe in another thread?

Pavel

On Fri, Jun 5, 2009 at 3:50 AM, Vladimir Kharitonov  
wrote:
> I have a problem inserting data through a view.
>
> This is my DB:
>
> CREATE TABLE data (
>        id BLOB PRIMARY KEY,
>        created REAL NOT NULL
> );
>
> CREATE VIRTUAL TABLE text USING FTS3();
>
> CREATE VIEW data_view AS
>  SELECT d.id, d.rowid, d.created, t.content
>  FROM data d INNER JOIN text t ON d.rowid = t.rowid;
>
> CREATE TRIGGER data_view_insert INSTEAD OF INSERT ON data_view
> BEGIN
>  INSERT INTO data (id , created) VALUES (new.id, new.created);
>  INSERT INTO text (docid, content) VALUES (last_insert_rowid(), new.content);
> END;
>
> When I insert rows in the data_view:
>
> PRAGMA count_changes = 1;
> INSERT INTO data_view (id, created, content)
> VALUES (randomblob(16), datetime('now'), 'data');
>
> I receive strange SQLite Error: “not an error”.
> I use version 3.6.13
>
> Any help?
> ___
> 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] foreign key problems

[Igor Tandetnik]

robinsmathew wrote:
> hey i hve created a  table
> CREATE TABLE video_tab (video_id INTEGER, project_id INTEGER,
> video_path VARCHAR(100), video_length INTEGER, video_type
> VARCHAR(10), video_size VARCHAR(10), CONSTRAINT video_pk PRIMARY
> KEY(video_id, project_id), CONSTRAINT fk_project_id FOREIGN KEY
> (project_id) REFERENCES project_tab(project_id));
>
> and wrote a trigger for the foreign key constraint...
> anyways i have to write a trigger for the foreign key constraint  does
> CONSTRAINT fk_project_id FOREIGN KEY (project_id) REFERENCES
> project_tab(project_id) make any difference in the CREATE TABLE
> command?

SQLite engine ignores FOREIGN KEY constraints. However, SQLite comes 
with a tool that can parse such constraints and automatically generate 
triggers necessary to simulate them:

http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README

Igor Tandetnik 



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

[sqlite] foreign key problems

[robinsmathew]

hey i hve created a  table 
CREATE TABLE video_tab (video_id INTEGER, project_id INTEGER, video_path
VARCHAR(100), video_length INTEGER, video_type VARCHAR(10), video_size
VARCHAR(10), CONSTRAINT video_pk PRIMARY KEY(video_id, project_id),
CONSTRAINT fk_project_id FOREIGN KEY (project_id) REFERENCES
project_tab(project_id));

and wrote a trigger for the foreign key constraint... 
anyways i have to write a trigger for the foreign key constraint  does
CONSTRAINT fk_project_id FOREIGN KEY (project_id) REFERENCES
project_tab(project_id) make any difference in the CREATE TABLE command?
-- 
View this message in context: 
http://www.nabble.com/foreign-key-problems-tp23886053p23886053.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] Db design question (so. like a tree)

[John Machin]

On 5/06/2009 5:27 PM, Francis GAYREL wrote:
> To build a consistent oriented tree we need to associate to the nodes a 
> ranking property such as the birthdate (or any precedence criterion).
> Therefore the ancestor of  someone  is to be selected among  older ones.

"Ancestor" is a *derived* relationship, not something you'd wish to 
store in the database, and is quite irrelevant at data-entry time. Don't 
you mean "parent"?

Let me get this straight: the user is entering the details of animal X 
who was born yesterday and has to input somehow the identity of the 
mother and of the father.

> To make the ancestor allocation more easy  the ancestor's list may be 
> filtered on birthdate credibility.

So your plot is, (e.g. for the father) to show a list of all male 
animals who are in some credible-parenthood age range on (say) a 
drop-down list, and the user selects one, hopefully not at random. Is 
that right?

I would imagine in a planned targeted organised animal breeding program 
that the mother-to-be and father-to-be are recorded at the time of 
impregnation, and the identities are established from ear-tags, embedded 
chips, photographs, etc and the credible-parenthood test is applied then 
[note: test, NOT input selection method] and all of the above is 
confirmed at birth.

> The ranking property eliminates the circular link concern.

Indeed, but you have to use it properly to eliminate other data 
integrity concerns :-)

HTH,

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

Re: [sqlite] Why row is not found?

[Marco Bambini]

Hello Martin,

it's a db than an user sent me... so I really don't know...

--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






On Jun 5, 2009, at 11:31 AM, Martin Engelschalk wrote:

> Hi Marco,
>
> How do you insert this data into your database?
> I opened your database with an old version of SQLiteSpy, which uses an
> even older version of sqlite. It showed the value as a blob.
>
> Martin
> Marco Bambini wrote:
>> I understand that the issue could be caused by the wrong datatype...
>> but what is strange is that the same db and the same query worked  
>> fine
>> with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x
>>
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> http://www.creolabs.com/payshield/
>>
>>
>>
>>
>>
>>
>> On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote:
>>
>>
>>> Your field value is a blob, so you have to use a cast like you did  
>>> to
>>> find the row, or use a blob literal:
>>> SELECT * FROM lo_user WHERE lo_name=X'61646d696e';
>>>
>>>
>>>
>>> Marco Bambini wrote:
>>>
 I just posted the db on my website... there is one row and there
 aren't invisible characters.

 Please note that the following query returns the exact row:
 SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';
 but I really don't have an explanation...

 --
 Marco Bambini
 http://www.sqlabs.com
 http://www.creolabs.com/payshield/






 On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:



> Hi,
>
> attachments do not make it through the list.
> There is no row with the value 'admin' in the field 'lo_name' in
> your
> table. Did you check that there are no blank spaces or other
> invisible
> characters?
>
> Martin
>
> Marco Bambini wrote:
>
>
>> Anyone can please explain me why this query:
>> SELECT * FROM lo_user WHERE lo_name='admin';
>> returns 0 rows in this db?
>>
>>
>>
>> Thanks.
>> -- 
>> Marco Bambini
>> http://www.sqlabs.com
>> http://www.creolabs.com/payshield/
>>
>>
>>
>>
>>
>>
>> 
>>
>> ___
>> 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

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

Re: [sqlite] Why row is not found?

[Martin Engelschalk]

Hi Marco,

How do you insert this data into your database?
I opened your database with an old version of SQLiteSpy, which uses an 
even older version of sqlite. It showed the value as a blob.

Martin
Marco Bambini wrote:
> I understand that the issue could be caused by the wrong datatype...  
> but what is strange is that the same db and the same query worked fine  
> with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x
>
> --
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote:
>
>   
>> Your field value is a blob, so you have to use a cast like you did to
>> find the row, or use a blob literal:
>> SELECT * FROM lo_user WHERE lo_name=X'61646d696e';
>>
>>
>>
>> Marco Bambini wrote:
>> 
>>> I just posted the db on my website... there is one row and there
>>> aren't invisible characters.
>>>
>>> Please note that the following query returns the exact row:
>>> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';
>>> but I really don't have an explanation...
>>>
>>> --
>>> Marco Bambini
>>> http://www.sqlabs.com
>>> http://www.creolabs.com/payshield/
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:
>>>
>>>
>>>   
 Hi,

 attachments do not make it through the list.
 There is no row with the value 'admin' in the field 'lo_name' in  
 your
 table. Did you check that there are no blank spaces or other  
 invisible
 characters?

 Martin

 Marco Bambini wrote:

 
> Anyone can please explain me why this query:
> SELECT * FROM lo_user WHERE lo_name='admin';
> returns 0 rows in this db?
>
>
>
> Thanks.
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> 
>
> ___
> 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] Why row is not found?

[Marco Bambini]

I understand that the issue could be caused by the wrong datatype...  
but what is strange is that the same db and the same query worked fine  
with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x

--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote:

> Your field value is a blob, so you have to use a cast like you did to
> find the row, or use a blob literal:
> SELECT * FROM lo_user WHERE lo_name=X'61646d696e';
>
>
>
> Marco Bambini wrote:
>> I just posted the db on my website... there is one row and there
>> aren't invisible characters.
>>
>> Please note that the following query returns the exact row:
>> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';
>> but I really don't have an explanation...
>>
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> http://www.creolabs.com/payshield/
>>
>>
>>
>>
>>
>>
>> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:
>>
>>
>>> Hi,
>>>
>>> attachments do not make it through the list.
>>> There is no row with the value 'admin' in the field 'lo_name' in  
>>> your
>>> table. Did you check that there are no blank spaces or other  
>>> invisible
>>> characters?
>>>
>>> Martin
>>>
>>> Marco Bambini wrote:
>>>
 Anyone can please explain me why this query:
 SELECT * FROM lo_user WHERE lo_name='admin';
 returns 0 rows in this db?



 Thanks.
 -- 
 Marco Bambini
 http://www.sqlabs.com
 http://www.creolabs.com/payshield/






 

 ___
 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] Why row is not found?

[Martin Engelschalk]

Your field value is a blob, so you have to use a cast like you did to 
find the row, or use a blob literal:
SELECT * FROM lo_user WHERE lo_name=X'61646d696e';



Marco Bambini wrote:
> I just posted the db on my website... there is one row and there  
> aren't invisible characters.
>
> Please note that the following query returns the exact row:
> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';
> but I really don't have an explanation...
>
> --
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:
>
>   
>> Hi,
>>
>> attachments do not make it through the list.
>> There is no row with the value 'admin' in the field 'lo_name' in your
>> table. Did you check that there are no blank spaces or other invisible
>> characters?
>>
>> Martin
>>
>> Marco Bambini wrote:
>> 
>>> Anyone can please explain me why this query:
>>> SELECT * FROM lo_user WHERE lo_name='admin';
>>> returns 0 rows in this db?
>>>
>>>
>>>
>>> Thanks.
>>> -- 
>>> Marco Bambini
>>> http://www.sqlabs.com
>>> http://www.creolabs.com/payshield/
>>>
>>>
>>>
>>>
>>>
>>>
>>> 
>>>
>>> ___
>>> 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] Why row is not found?

[bartsmissaert]

Maybe don't use varchar, but text instead when creating the table.

RBS



> I just posted the db on my website... there is one row and there
> aren't invisible characters.
>
> Please note that the following query returns the exact row:
> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';
> but I really don't have an explanation...
>
> --
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:
>
>> Hi,
>>
>> attachments do not make it through the list.
>> There is no row with the value 'admin' in the field 'lo_name' in your
>> table. Did you check that there are no blank spaces or other invisible
>> characters?
>>
>> Martin
>>
>> Marco Bambini wrote:
>>> Anyone can please explain me why this query:
>>> SELECT * FROM lo_user WHERE lo_name='admin';
>>> returns 0 rows in this db?
>>>
>>>
>>>
>>> Thanks.
>>> --
>>> Marco Bambini
>>> http://www.sqlabs.com
>>> http://www.creolabs.com/payshield/
>>>
>>>
>>>
>>>
>>>
>>>
>>> 
>>>
>>> ___
>>> 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] Why row is not found?

[Marco Bambini]

I just posted the db on my website... there is one row and there  
aren't invisible characters.

Please note that the following query returns the exact row:
SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';
but I really don't have an explanation...

--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:

> Hi,
>
> attachments do not make it through the list.
> There is no row with the value 'admin' in the field 'lo_name' in your
> table. Did you check that there are no blank spaces or other invisible
> characters?
>
> Martin
>
> Marco Bambini wrote:
>> Anyone can please explain me why this query:
>> SELECT * FROM lo_user WHERE lo_name='admin';
>> returns 0 rows in this db?
>>
>>
>>
>> Thanks.
>> -- 
>> Marco Bambini
>> http://www.sqlabs.com
>> http://www.creolabs.com/payshield/
>>
>>
>>
>>
>>
>>
>> 
>>
>> ___
>> 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] Why row is not found?

[Martin Engelschalk]

Hi,

attachments do not make it through the list.
There is no row with the value 'admin' in the field 'lo_name' in your 
table. Did you check that there are no blank spaces or other invisible 
characters?

Martin

Marco Bambini wrote:
> Anyone can please explain me why this query:
> SELECT * FROM lo_user WHERE lo_name='admin';
> returns 0 rows in this db?
>
>
>
> Thanks.
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> 
>
> ___
> 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] Why row is not found?

[Marco Bambini]

You can download the db from:
http://www.sqlabs.com/download/test.sqlite
It's only 4KB.

--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






On Jun 5, 2009, at 11:04 AM, Marco Bambini wrote:

> Anyone can please explain me why this query:
> SELECT * FROM lo_user WHERE lo_name='admin';
> returns 0 rows in this db?
>
>
>
> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> ___
> 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] Why row is not found?

[Marco Bambini]

Anyone can please explain me why this query:
SELECT * FROM lo_user WHERE lo_name='admin';
returns 0 rows in this db?




Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






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

Re: [sqlite] how to compose the sql sentence?

[liubin liu]

Thank you very much!

you told me how to do. but I missed the "... defined as  UNIQUE ...".

Actually my question is just solved by two step:

First - CREATE UNIQUE INDEX i_recdata ON rec_data (num, di, time1);

Second - INSERT OR REPLACE INTO rec_data (num, di, data, time1, time2,
format) VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22); 




Simon Slavin-2 wrote:
> 
> 
> On 3 Jun 2009, at 7:05am, liubin liu wrote:
> 
>> the first step is to tell if there is the data in the table.
>> if the answer is not, I want to insert a row of data into the table
>> if the answer is yes, I need to update the row of data acccording to  
>> the
>> data inputting from me.
> 
> INSERT OR REPLACE INTO table (columns) VALUES (values)
> 
> This will use the columns and indices you have already defined as  
> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
> index that does what you want.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23883819.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] "not an error" error inserting data trough a view on fts3 table

[Vladimir Kharitonov]

I have a problem inserting data through a view.

This is my DB:

CREATE TABLE data (
id BLOB PRIMARY KEY,
created REAL NOT NULL
);

CREATE VIRTUAL TABLE text USING FTS3();

CREATE VIEW data_view AS
  SELECT d.id, d.rowid, d.created, t.content
  FROM data d INNER JOIN text t ON d.rowid = t.rowid;

CREATE TRIGGER data_view_insert INSTEAD OF INSERT ON data_view
BEGIN
  INSERT INTO data (id , created) VALUES (new.id, new.created);
  INSERT INTO text (docid, content) VALUES (last_insert_rowid(), new.content);
END;

When I insert rows in the data_view:

PRAGMA count_changes = 1;
INSERT INTO data_view (id, created, content)
VALUES (randomblob(16), datetime('now'), 'data');

I receive strange SQLite Error: “not an error”.
I use version 3.6.13

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

Re: [sqlite] Db design question (so. like a tree)

[Francis GAYREL]

To build a consistent oriented tree we need to associate to the nodes a 
ranking property such as the birthdate (or any precedence criterion).
Therefore the ancestor of  someone  is to be selected among  older ones.
To make the ancestor allocation more easy  the ancestor's list may be 
filtered on birthdate credibility.
The ranking property eliminates the circular link concern.


Jan a écrit :
> Hi Mark,
>
> I think that wont work:
>
> Scenario: A calf is born from a mother within your flock but from a 
> father outside. The father appears for the first time and you are not 
> able to gather information on his father (or grand-grand father). 
> Therefore his father is NULL. But later you get the information on his 
> father and add it to the animal list: The id of the fathers father is 
> then greater then the id of his grandchild (the calf).
> I could start the id initially with 10 to allocate <10 ids in 
> theses cases, but I am unsure if this is a good way to start.
>
> Mark Hamburg schrieb:
>   
>> One of the questions that I believe was raised but not answered on  
>> this thread was how to make sure that you don't have circular  
>> relationships particularly given that SQLite isn't good at scanning  
>> the tree. If you can control the id's then simply require that the id  
>> of the child be greater than the id's of the parents.
>>
>> Mark
>>
>> ___
>> 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