Re: [sqlite] What this function returns?

[Harold Wood]

Lol so call you igor?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John McKown
Sent: Friday, November 29, 2013 8:10 PM
To: rsm...@rsweb.co.za; General Discussion of SQLite Database
Subject: Re: [sqlite] What this function returns?

Ah, found it.
http://msdn.microsoft.com/en-us/library/s3f49ktz.aspx

__int64, unsigned __int64, long long, unsigned long long,

-- 
> This is clearly another case of too many mad scientists, and not 
> enough hunchbacks.
>
> Maranatha! <><
> John McKown
>



--
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
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] Eficiency : 1 table vs several tables

[Harold Wood]

Old school database Mapper worked along that line, you had a ton of small
tables and a lookup table that told your queries what table to look in...all
in all it was a mess.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Friday, November 29, 2013 7:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Eficiency : 1 table vs several tables


On 29 Nov 2013, at 8:27pm, Carlos Ferreira  wrote:

> Let's assume that TABLE now designates my real tables that can be 
> either SQLITE tables or sub groups of records inside one big real 
> SQLite table
> 
> 
> 
> I have to load to memory and save to DB groups of these TABLE at the 
> same time ( by saving I refer to update or save the blobs inside each
table ).
> 
> 
> 
> What if  faster? 

SQLite is optimized for searching a table for specific values.  Assuming, of
course, that you have an index ideally suited to your search.  It is not
optimized for searching a huge list of tables for one with a specific name.

My assumption is that putting 1000 tables in a database is going to slow
down every operation since every operation has to search for the correct
table and /then/ search that table for the correct row(s).  But I have no
figures to prove that.

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] controlling changes

[Harold Wood & Meyuni Gani]

You can do it as a constraint.

-Original Message-
From: David Bicking 
Sent: Saturday, October 10, 2009 9:08 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] controlling changes

I have a table:
CREATE TABLE Assets
(   ControlDate Date
,   Amt
)

Now, the business rules are you can INCREASE the Amt if the Current Date
is the ControlDate in the record. You can DECREASE the amount if the
Current Date is greater than or equal to the ControlDate.

Can this be enforced via a trigger, or must I enforce that business rule
at the application layer?

Thanks,
David


___
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 there an easy way to concatenate 2 views ?

[Harold Wood & Meyuni Gani]

Yw, anytime.

-Original Message-
From: Stef Mientki <s.mien...@ru.nl>
Sent: Saturday, July 25, 2009 4:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there an easy way to concatenate 2 views ?

thanks Woody ( Harold ?),

that works perfect.

cheers,
Stef

Harold Wood & Meyuni Gani wrote:
> Concantenate or join?
>
> To concantenate use the
> Select * from a
> Union all
> Select * from b
>
> Only use * if columns all columns in a are identical to all columns in b, if 
> not then build a column list and use that indtead of *
>
> Woody
>
> -Original Message-
> From: Stef Mientki <s.mien...@ru.nl>
> Sent: Saturday, July 25, 2009 3:19 PM
> To: SQLite List <sqlite-users@sqlite.org>
> Subject: [sqlite] Is there an easy way to concatenate 2 views ?
>
> hello,
>
> I've 2 views ( or maybe tables) with same columns,
> is there an easy way to concatenate those 2 views to 1 new view ?
>
> thanks,
> Stef Mientki
> ___
> 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 there an easy way to concatenate 2 views ?

[Harold Wood & Meyuni Gani]

Concantenate or join?

To concantenate use the
Select * from a
Union all
Select * from b

Only use * if columns all columns in a are identical to all columns in b, if 
not then build a column list and use that indtead of *

Woody

-Original Message-
From: Stef Mientki 
Sent: Saturday, July 25, 2009 3:19 PM
To: SQLite List 
Subject: [sqlite] Is there an easy way to concatenate 2 views ?

hello,

I've 2 views ( or maybe tables) with same columns,
is there an easy way to concatenate those 2 views to 1 new view ?

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

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

Re: [sqlite] Time format

[Harold Wood]

try DATE('now','localtime')

--- On Tue, 6/30/09, Goldstein, Ian  wrote:


From: Goldstein, Ian 
Subject: [sqlite] Time format
To: sqlite-users@sqlite.org
Date: Tuesday, June 30, 2009, 12:07 AM




Hello, I am one day into sqlite and have probably a very simple problem
involving datetime function.
It seems, there is a 4 hour difference between what is in the db and my
real time.
For example:
date;sqlite3 ian.db "select (datetime('now'))"
Tue Jun 30 00:05:35 EDT 2009
2009-06-30 04:05:35

Thanks 
Ian


  


___
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] Database logic in TRIGGER and CONFLICT, or in software ?

[Harold Wood & Meyuni Gani]

Very well said.

Harold Wood & Meyuni Gani

-Original Message-
From: Jay A. Kreibich <j...@kreibi.ch>
Sent: Sunday, June 14, 2009 8:44 PM
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Database logic in TRIGGER and CONFLICT,   or in   
software ?

On Sat, Jun 13, 2009 at 11:42:21PM +0100, Simon Slavin scratched on the wall:

> Do any of you have experience with doing this ?  Are there some  
> conclusive points which will make me decide immediately that I should  
> do it one way or the other ?  I accept reasoned argument, URLs,  
> anecdotes, or anything else relevant.

  Every system that uses a database must deal with this problem, and
  there are no real easy answers.  But here are a few deeper points to
  consider.

  From the view point of Relational Model, the formal mathematical
  model that most RDBMS engines are based off (in theory, anyways), you
  should push as many constraints, rules, and general enforcement into
  the database as possible.  The database is meant to represent "truth"
  and under the Model it should be impossible (or at least as difficult
  as possible) to make the database become inconsistent-- that is, be
  in a state that doesn't represent truth.

  There are two main issues with this.  First, an modern SQL database
  is not a Relational Model database.  SQL only offers a number of
  different constraints, but it is not as flexible or complete as the
  theoretical model presented by the Relational Model.

  Second, the Relational Model doesn't account for access methods or
  the concept of middle-ware or tiered applications.  It is assumed
  that anyone and everything from applications to command-line systems
  will be accessing the database.  If you have a user typing in raw SQL
  commands and you want your database to remain consistent, you have no
  choice but to push as much as possible into the database and do lots
  of audits.

  Thankfully, most real-world applications do not work this way.  You
  provide some piece of software that accesses the database, and (in
  general) the only people that have command line access are DBAs and
  other admin types.  In that case, you can relax some of the constraints
  or rules that are difficult to express in the database and move those
  out to the application.

  This can be good or bad.  In the case of an old-school mainframe
  application, where you logged into a central system via your VT100 or
  TN3270 terminal and ran the application on the mainframe, the
  software was centralized and easy to maintain.  A system-wide
  application upgrade consists of replacing one binary.  This made it
  relatively safe to push rules and constraints into the application, as
  it was easy to keep the end-user application and the database --
  including schema changes and format updates -- in sync.  After all,
  both the DB and the application were sitting on one machine.

  Then came the era of desktop systems.  Now it was common to have a
  GUI desktop application that was used to access and manipulate the
  database.  The problem is, it is nearly impossible to keep every
  desktop system in perfect sync.  If a schema change required an
  update to a query, that required a new version of the desktop
  application, which required a network-wide upgrade.  Really fast, you
  learned to either make your upgrades backwards compatible OR you had
  an extremely simply automatic update system.  Regardless, there were
  dangers associated with pushing too many high-level rules into the
  application.  If someone managed to access the database with an older
  client that had a different set of rules, bad things could happen.
  To get around these problems people learned to push more and more
  logic into the database, including complex stored functions and
  procedures to do just about any update or adjustment and views for
  all but the most basic filtering of a query.  This would allow for
  schema changes without client updates.

  As the web gained popularity, along with middle-ware and multi-tier
  architectures, we returned more or less to the model of "everything
  on one machine" or, at least, under one control.  If the application
  is completely web driven, the problem of upgrading the SQL access
  application (e.g. the web server code) and the database are greatly
  reduced compared to the desktop model, allowing more and more logic
  to return to the code.

  So much of the answer to your question depends on the environment
  you're trying to support and how much control you have over all the
  bits of code that have direct access to the database.  If you control
  the database AND the next tier down, pushing logic, rules, and
  constraints into that layer has fewer issues and ramifications than
  keeping them in the database.

  From a pure software engineering standpoint, the best solution is to
  keep the rules and constrain

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

[Harold Wood]

Well you have a column named data and a table named data, but the biggest issue 
is you only supplied 3 column names but in the values list you have 6 values.
 


--- On Thu, 6/4/09, liubin liu <7101...@sina.com> wrote:


From: liubin liu <7101...@sina.com>
Subject: Re: [sqlite] how to compose the sql sentence?
To: sqlite-users@sqlite.org
Date: Thursday, June 4, 2009, 2:39 AM



Thank you a lot!


I created a table:
CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER,
time2 INTEGER, format CHAR(1) );

and create a index:
CREATE INDEX i_data ON data (num, di, time1);

I want to do:
first tell whether there is a record in the table "data" according to the
index "i_data".
to update the record if there is a record;
to insert the record if there isn't any record.



when I run the sql:
INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
'732e4a39', 8323000, 8323255, 22);

the sqlite3 report a error:
SQL error: 6 values for 3 columns

Does It mean the method isn't the right way?




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...-tp23845882p23864558.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Harold Wood & Meyuni Gani]

Hmm, I have a view, its  strictly a bunch of bit columns.  Default value is 
0=false, this view has a huge trigger on it.

I use the different columns to activate particular sections of the trigger 
code, within those I do inserts, deletes, updates etc. 

It was a design around not having stored procedures.



Harold Wood & Meyuni Gani

-Original Message-
From: BareFeet <list@tandb.com.au>
Sent: Wednesday, June 03, 2009 6:21 PM
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite

Hi Harold,

> SQL does have branching logic.
>
> (SELECT CASE
> WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT  
> 1) IS NULL)
> THEN
>  0
> ELSE
>  (SELECT StoreId FROM History WHERE ItemId = NEW.ID AND  
> UnitPrice = (SELECT MIN(UnitPrice) FROM HISTORY WHERE ItemId =  
> NEW.ID))
> END);
>
> i use it in my  current project.

Let me clarify. By "branching logic" I mean branching (eg if/then or  
loop) to perform an action such as update, insert, delete, create etc.

The case/when/then construct is a function, not procedural branching  
(at least by my definition above). It will return different results  
depending on the test, but it can't be used to perform different  
actions based on the test.

> you could modify this to meet the goal of insert x or update y.

No, that won't work. You can't put an action (such as an update or an  
insert) inside a case statement. You can only put expressions  
(including select statements) within a case statement.

Tom
BareFeet

   --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

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

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

Re: [sqlite] Getting last inserted rowid?

[Harold Wood & Meyuni Gani]

If you are inserting in order then selecting the max value from  an indexed 
column should work.

Harold Wood 

-Original Message-
From: Nikolaus Rath <nikol...@rath.org>
Sent: Wednesday, June 03, 2009 3:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Getting last inserted rowid?

Nuno Lucas <ntlu...@gmail.com> writes:
> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.org> wrote:
>> Nuno Lucas <ntlu...@gmail.com> writes:
>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath <nikol...@rath.org> wrote:
>>>> Hello,
>>>>
>>>> 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.
>>>
>>> It should work if you do:
>>>
>>> BEGIN
>>> INSERT ...
>>> last_insert_rowid()
>>> END
>>
>> That would be very nice. But does "it should work" mean that you know
>> that it works (and it is documented and guaranteed)? The above sounds a
>> bit uncertain to me...
>
> It just means I'm too old to assume anything is certain. The Universe
> is always conspiring against you ;-)
>
> What I mean is that if it doesn't work, then you found a bug, most
> probably in your own code.

Well, now you are in direct contradiction to Igor who says that it does
not work:

,
| >> 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.
`


Any third opinions or references to documentation? 


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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how can we solve IF EXIST in SQLite

[Harold Wood]

SQL does have branching logic.
 

(SELECT CASE
WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT 1) IS NULL)
THEN
 0
ELSE
 (SELECT StoreId FROM History WHERE ItemId = NEW.ID AND UnitPrice = (SELECT 
MIN(UnitPrice) FROM HISTORY WHERE ItemId = NEW.ID))
END);

i use it in my  current project.
 
you could modify this to meet the goal of insert x or update y.
 
Woody
--- On Wed, 6/3/09, BareFeet  wrote:


From: BareFeet 
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
To: "General Discussion of SQLite Database" 
Date: Wednesday, June 3, 2009, 8:29 PM


Hi Mathew,

> hi am new to SQLite can anybody please tell me how this query can be  
> solved
> in SQLite?
>
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE  
> prod_batch_code=1000)
>        UPDATE stock_tab
>        SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab  
> WHERE
> oduct_batch_code=1000 )
>        WHERE prod_batch_code=1000
> ELSE
>        INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,
>        DATETIME('NOW') );

Your if/then/else structure is a branching procedure. SQL is a  
language for manipulating sets and so does not facilitate procedural  
branching such as if/then/else or loops.

SQL, being a language dealing with sets, is designed to perform  
actions on entire sets or subsets of data.

So, instead of saying "test this, branch here if true, there if  
false", you need to instead say "do this to the subset that tests  
true, and do that to the subset that tests false".

So, something like this:

begin immediate
;
update Stock_Tab
set Stock_Qty = Stock_Qty +
    (select Purchase_Qty from Purchase_Tab where Product_batch_code = 1000)
where Prod_batch_code = 1000
;
insert into Stock_Tab (Stock_ID, Prod_Batch_Code, Stock_Qty, Stock_Date)
values (20009, 1003, 200, datetime('now'))
where not exists (select 1 from Stock_Tab where Prod_Batch_Code = 1000)
;
commit
;

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml



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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Harold Wood]

Select case
   when ((Select stock_id from Table where Stock_Id = ?)  IS NULL)
  then
    insert into Table
  else
   update Table
 end;

--- On Wed, 6/3/09, Kees Nuyt <k.n...@zonnet.nl> wrote:


From: Kees Nuyt <k.n...@zonnet.nl>
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
To: sqlite-users@sqlite.org
Date: Wednesday, June 3, 2009, 2:15 PM


On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood
<hwoody2w...@yahoo.com> wrote:

>you should use the insert or replace statement, 
>it inserts if the row doesnt exist, if the row
>does exists then it updates the row.

No, that doesn't fulfil the requirement,
because quantity isn't incremented.

>--- On Wed, 6/3/09, robinsmathew <robinsmat...@hotmail.com> wrote:
>
>
>From: robinsmathew <robinsmat...@hotmail.com>
>Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>To: sqlite-users@sqlite.org
>Date: Wednesday, June 3, 2009, 3:15 AM
>
>
>
>its showing an error near "if": syntax error
>
>
>Kees Nuyt wrote:
>> 
>> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew
>> <robinsmat...@hotmail.com> wrote:
>> 
>>>
>>>hey thanx for the reply... u leave the things happening inside.. wat i jus
>>>wanna do is i wanna insert a new row to a table
>>>the table will be like this
>>>stock_id PK        product_id FK       quantity    stock_date
>>>1                 1000                    10            28-05-2009
>>>10001                 1001                      5            27-05-2009
>>>
>>>and wen i insert a new row with values  NULL,   1000,   15,    30-05-2009    
>>>       
>>>i dont want want it as a new recorde i jus want to update the first row
>coz
>>>its also having the same product id i jus want set the quantity = 10+15
>and
>>>the date new date that is 30-05-2009
>>>and suppose if i insert row with different product_id it should be
>inserted
>>>as it is..
>> 
>> Pseudocode:
>> BEGIN;
>> UPDATE stock_tab SET . WHERE stock_id = 1;
>> if sqlite_error()
>>     INSERT INTO stock_tab SET (...) VALUES (...);
>> endif
>> COMMIT;
>> -- 
>>   (  Kees Nuyt
-- 
  (  Kees Nuyt
  )
c[_]
___
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] Db design question (so. like a tree)

[Harold Wood]

just curious. why not a table for stricty for the linkages
 
structure similar to 
 
create table Linkages 
(
    Parent_Id int,
    Child_Id  int
    PRIMARY KEY (Parent_Id, Child_Id))
 
This would allow a lot of flexability in the parenting, you could have (A:B), 
(A:C), (B:D), (B:E), (B:F), (E:G) and if you wanted to get a little insane, 
(A:D), etc.


--- On Wed, 6/3/09, Jay A. Kreibich  wrote:


From: Jay A. Kreibich 
Subject: Re: [sqlite] Db design question (so. like a tree)
To: "General Discussion of SQLite Database" 
Date: Wednesday, June 3, 2009, 12:36 PM


On Wed, Jun 03, 2009 at 12:25:14AM +0200, Jan scratched on the wall:
> Hi,
> > If you don't want to update, but you do want to query for entire
> > subtrees, do give nested sets more consideration.
> 
> But as Jay pointed out: Nested sets only work with one parent. Do they?

  You can think of nested sets as basically sets of parenthesis.

  So the tree:

       A
      / \
     B   C
    /   /|\
   D   E F G

  Turns into:

    (A:(B:(D:))(C:(E:)(F:)(G:)))

  As you can see, quite literally "nested sets" (or "sets of sets").

  Each node can have exactly one parent (the containing set) and zero
  or more (with "more" being > 2) children.



  In the case of a family tree, you can get around the "one parent"
  by extracting the table structure out to a detail table, so that the
  tree table only has "person_id" values that point back to some
  master "person" table.  You can then just build two nested sets: one
  that represents all fathers and one that represents all mothers.  The
  "father" table will still have daughters, but daughters will always
  lack any children (in the "father" table).
  
  [I think that will work.  My morning coffee has just about worn off.]

  Of course, this cancels out many of the query optimizations that
  nested sets are good at, since you'll frequently need to combine data
  from the two trees to get what you want.  But it would be possible.

  The bigger issue is that nested sets assume a perfect tree structure.
  It has to lead back to a "point."  You could, in theory, do a family
  tree for a single person by turning the table up-side down, but if
  you're trying to track breeding over a group of animals you need not
  so much a tree as a scattered mesh that generally trends in one
  direction.  Unless you started out with exactly one male and one
  female, a nested set isn't going to cut it.

  I'm also unsure about cross-generational links (Like a son being a
  half-brother kind of thing) that might happen in lab animals.
  adjacency lists can deal with all of these quite easily.  You can
  have multiple NULL parents for the "tops" of different sub-trees, and
  the tree structure is localized to a node and it's parents, meaning
  the links can go all over the place.





  As for AVL trees, I'm just confused by that suggestion.  AVL trees,
  like B-trees, Red/Black trees, or just about any kind of balanced
  tree are designed to hold sorted lists.  The whole idea of balanced
  trees is that the tree structure can rearrange itself at will, just
  as long as the leaf nodes keep their order and are fast to find.  You
  can't hold a tree structure in a AVL tree since the tree structure is
  prone to changing if you add or remove leaf nodes.

  Or am I missing something?

   -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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how can we solve IF EXIST in SQLite

[Harold Wood]

you should use the insert or replace statement, it inserts if the row doesnt 
exist, if teh row does exists then it updates the row.

--- On Wed, 6/3/09, robinsmathew  wrote:


From: robinsmathew 
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
To: sqlite-users@sqlite.org
Date: Wednesday, June 3, 2009, 3:15 AM



its showing an error near "if": syntax error


Kees Nuyt wrote:
> 
> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew
>  wrote:
> 
>>
>>hey thanx for the reply... u leave the things happening inside.. wat i jus
>>wanna do is i wanna insert a new row to a table
>>the table will be like this
>>stock_id PK        product_id FK       quantity    stock_date
>>1                 1000                    10            28-05-2009
>>10001                 1001                      5            27-05-2009
>>
>>and wen i insert a new row with values  NULL,   1000,   15,    30-05-2009     
>>      
>>i dont want want it as a new recorde i jus want to update the first row
coz
>>its also having the same product id i jus want set the quantity = 10+15
and
>>the date new date that is 30-05-2009
>>and suppose if i insert row with different product_id it should be
inserted
>>as it is..
> 
> Pseudocode:
> BEGIN;
> UPDATE stock_tab SET . WHERE stock_id = 1;
> if sqlite_error()
>     INSERT INTO stock_tab SET (...) VALUES (...);
> endif
> COMMIT;
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> 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-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23846618.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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

[Harold Wood]

look up the insert or replace statement, http://www.sqlite.org/lang_insert.html

--- On Wed, 6/3/09, liubin liu <7101...@sina.com> wrote:


From: liubin liu <7101...@sina.com>
Subject: [sqlite] how to compose the sql sentence?
To: sqlite-users@sqlite.org
Date: Wednesday, June 3, 2009, 2:05 AM



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.

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sql-sentence--tp23845882p23845882.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] 2 columns as primary key?

[Harold Wood]

create table tablename 
(
    colname1 coltype,
    colname2 coltype,
    colname3 coltype,
    PRIMARY KEY(colname1 asc, colname2 asc)
)

--- On Fri, 5/29/09, Pavel Ivanov  wrote:


From: Pavel Ivanov 
Subject: Re: [sqlite] 2 columns as primary key?
To: "General Discussion of SQLite Database" 
Date: Friday, May 29, 2009, 10:54 PM


What create table statement did you use?

Pavel

2009/5/29 "Andrés G. Aragoneses" :
> I just tried to create a primary key with 2 columns and got this error:
>
> "sqlite error" "table X has more than one primary key"
>
>
> Doesn't SQLite support this?? :o
>
>        Andres
>
> --
>
> ___
> 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] Multi column ORDER BY across table peformance problem....

[Harold Wood]

how about
 

select Starttime
 from channel C
 inner join (select showing.startTime AS startTime,
    showing.stationId AS stationId
    FROM showing
   WHERE showing.startTime >= 123923
     ORDER BY showing.startTime
     LIMIT 8 ) AS s
   on S.StationId = C.stationId
 ORDER BY s.startTime, c.ChannelMajorNumber
 limit 8
 


--- On Wed, 4/22/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Wednesday, April 22, 2009, 1:11 AM

Um, well, except that I'd have huge gaping holes. i.e. say there are 100
shows at or after the specified time all on different channels. The above
query would limit to the first 8 showings on or after that time *before*
sorting by channel number. 



Jeremy Hinegardner wrote:
> 
> How about trying:
> 
>   SELECT s.startTime
> FROM ( SELECT  showing.startTime AS startTime
>   ,showing.stationId AS stationId
>   FROM showing
>  WHERE showing.startTime >= 123923
>   ORDER BY showing.startTime
>LIMIT 8 ) AS s
> JOIN channel AS c
>   ON s.stationId = c.stationId
> ORDER BY s.startTime, c.ChannelMajorNumber
> 
> I believe this should limit your table of 100,000 rows down to just the 8
> you
> want on the inner query, and then join that against the 100 rows in the
> channel
> table.
> 
> Although, to tell you the truth, I don't see the purpose in your
original
> query:
> 
> SELECT showing.startTime 
>   FROM showing 
>   JOIN channel 
> ON showing.startTime >= 123923 
>AND showing.stationId = channel.stationId
>   ORDER BY showing.startTime, channel.ChannelMajorNumber 
>  LIMIT 8;
> 
> You are not doing anything with the channel table data other than joining
> it
> agains the showing tables.  To me that means you are joining against the
> channel
> table in order to filter out rows in the showing table that have
> stationId's
> that do not exist in the channel table.  
> 
> Is that correct?  If that is correct, then the query I gave will not do
> what you
> want.  
> 
> Can the goal you are attempting be stated as?
> 
> Find next or earliest showings from the showings table for channels in
> the
> channel table?
> 
> If this is the case, maybe this query will work:
> 
>   SELECT showing.startTime
> FROM showing
>WHERE showing.startTime >= 123923000
>  AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM
> channel )
>LIMIT 8;
> 
> enjoy,
> 
> -jeremy
> 
>  
> On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote:
>> 
>> Writing the query as you said you would returns results in nearly
>> instantly,
>> within a few ms. 
>> 
>> The problem is when you add a secondary ordering field. The intended
>> indices
>> are being used.
>> 
>> The problem, as I've explained several times already is that there
is no
>> way
>> to create a multicolumn index across tables. Sqlite is using the
>> stationId
>> index on the channel table to join showings via the stationId. The
query
>> plan shows it clearly. When ordering by startTime or even startTime
and
>> stationId, the results are returned in a few ms. But when adding
channel
>> number(i.e startTime and then channel number), it jumps to two
minutes.
>> This
>> is because sqlite is bringing in nearly 100K records from the showing
>> table,
>> sorted by time, because of the startTime index, before it starts
sorting
>> by
>> channel number. It would sure be nice to be able to get sqlite to sort
on
>> the channel number for each time group. i.e. once all the 10:00 PM
>> showings
>> have been returned and the first 10:30 PM showing is about to be
>> returned,
>> sort all of the 10:00 PM showings by channel number. Sqlite is NOT
doing
>> this but should. 
>> 
>> At this point, I'm going to assume that this is just a limitation
in the
>> sqlite engine and I'll have to proceed in other ways to solve this
issue.
>> 
>> 
>> 
>> Ian Walters wrote:
>> > 
>> >> SELECT showing.startTime FROM showing JOIN channel ON  
>> >> showing.startTime >=
>> >> 123923 AND showing.stationId = channel.stationId ORDER BY
>> >> showing.startTime LIMIT 8;
>> > 
>> > I don't know if it would be faster... but I would have
written that.
>> > 
>> > SELECT showing.startTime FROM showing JOIN channel USING
(stationId)  
>> > WHERE startTime < 123923 ORDER BY startTime LIMIT 8.
>> > 
>> > Also I know in the latest version of SQLite its possible to
'hint'  
>> > what indexes should be used, which might be helpful.  There is
also  
>> > something on the contrib page that lets you check a query to see
what  
>> > indexes it does use.
>> > 
>> > Sorry if the above lacks detail, its kinda a busy day.
>> > 
>> > --
>> > Ian
>> > 

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

sorry.  Ive had good results with subqueries in resolving similar performance 
issues.

--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 10:22 PM

I can't think of any reason why this would result in a faster query. But I
went ahead and tried it on the off chance that maybe specifying the
channel.ChannelMajorNumber inside an inner select might somehow trick sqlite
into doing the right thing. Unfortunately, the result was the same. A few ms
without the channel number and over 2 minutes with it. Again, sqlite is
bringing in all results, not just the limit before it sorts on the channel
number. 

I tried your query but had to alias the inner columns so they could be
selected in the outer query.

SELECT startTime FROM (SELECT showing.startTime as startTime,
channel.ChannelMajorNumber as ChannelMajorNumber FROM showing JOIN channel
ON showing.startTime >= 1240362000 AND showing.stationId = channel.stationId
) ORDER BY startTime, ChannelMajorNumber LIMIT 8;



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168567.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

try this
 

select showing.startTime, from (
 SELECT showing.startTime,
   channel.ChannelMajorNumber
  FROM showing 
  JOIN channel 
   on showing.stationId = channel.stationId
  Where showing.startTime >=  123923) 
ORDER BY showing.startTime, channel.ChannelMajorNumber 
LIMIT 8;
 
 
 
--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 9:46 PM

It's in my original post above.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168281.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

send me your original query please.
 
thanks
 
Woody

--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 9:29 PM

No. This can't be broken down into a query within a query. Perhaps maybe if
you spell out an example of what you're thinking?

Thanks.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168105.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

did you try the subquery method i suggested?
 
Woody

--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 8:48 PM

Wow. Anybody? I figured this would be a simple question for the gurus on this
board. Seriously, nobody knows a better way to do this?




sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23166621.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

just curious; have you tried doing the select in a subquery, and then the order 
by in the outer query?

 
woody
--- On Fri, 4/17/09, sorka  wrote:

From: sorka 
Subject: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Friday, April 17, 2009, 11:12 PM

This should be simple but apparently it isn't.

I have two tables:
"CREATE TABLE showing ( "
"showingIdINTEGER PRIMARY KEY, "
"stationId  INTEGER, "
"startTime  INTEGER, "
") "
CREATE INDEX showing_startTime on showing(startTime);


AND
"CREATE TABLE channel ( "
"  channelIdINTEGER PRIMARY KEY, "
"  ChannelNumber   INTEGER, "
"  stationIdINTEGER, "
"  ) "
CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
CREATE INDEX channel_stationId on channel(stationId);

When I do this select:
SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId ORDER BY
showing.startTime LIMIT 8;

I get back the correct 8 results in about 3 milliseconds. 

If I throw a secondary order term in there say ChannelNumber:

SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId ORDER BY
showing.startTime, channel.ChannelMajorNumber LIMIT 8;

It now takes over 120 seconds!!!

I've tried various multi-column indices including one on channel(stationId,
ChannelNumber).

No difference.

As far as I can tell, when ordering on columns that cross tables, sqlite
will bring in all the records that match the equality or inequality search
term on the first column before it does a secondary sort.

I have over 100,000 records in the showing table and about 100 records in
the channel table.

Sqlite should be smart enough to do the secondary sort on ChannelNumber as
soon as it sees that the records coming back have a later startTime than the
previous one. i.e. Say the first 5 records have the same startTime and
different channel numbers. Then the next 5 records have a later start time
than the first. Sqlite should be smart enough to see this as the results
come back and do a secondary sort on ChannelNumber on the first 5 results
and then rinse and repeat.

What appears to be happening is that even though startTime is indexed,
sqlite is bringing all 100K records into memory sorted by time before it
starts to sort by channel number. 

Is there a way to get sqlite to do the right thing? If there was only a way
to have a multi-column index that included columns from different tables. Oh
wait, there is, it's called an intermediate table. However the cost of
doing
this is pretty high for reasons I can't go into here. 

Any ideas? Maybe I'm just doing something wrong and this should be simple.

Thanks.



-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23109024.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Visual Basic Programming to SQlite

[Harold Wood]

Dim Records
Dim Database

dim Recs

AddObject "newObjects.sqlite3.dbutf8", "Db"

set Database = Db

'Open the database
On Error resume next
If DataBase.Open(Get_Database_Name) Then 
   DataBase.AutoType = True
   DataBase.TypeInfoLevel = 4
Else
  MsgBox "ERROR ON OPEN OF DATABASE: " & DataBase.lastError
  Bye
End If
On Error Goto 0

On Error resume next
Set Records= Database.Execute("Select * from tablea")
On Error Goto 0

Recs = 1
While Recs <= Records.Count
  DoEvents
  blah balh
  Recs = Recs + 1
wend

Set Records = Nothing

DataBase.Close

Set DataBase = Nothing

--- On Fri, 10/3/08, Steven Charest <[EMAIL PROTECTED]> wrote:

> From: Steven Charest <[EMAIL PROTECTED]>
> Subject: [sqlite] Visual Basic Programming to SQlite
> To: sqlite-users@sqlite.org
> Date: Friday, October 3, 2008, 7:53 PM
> I am a Visual Basic programmer who would like to port the
> Access Database
> over to SQlite.  Is there any documentation on how to use
> this database with
> Visual Basic?  I am difficulty finding any documentation on
> it.
> 
>  
> 
> Thanks
> 
>  
> 
>  
> 
>  
> 
> ___
> 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] Precompiled SQLite Binaries for WinCE

[Harold Wood & Meyuni Gani]

newobjects

Woody
from his pda

-Original Message-
From: Roger Binns <[EMAIL PROTECTED]>
Sent: Sunday, August 17, 2008 2:00 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Precompiled SQLite Binaries  for WinCE

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:
> I was looking for precompiled SQLite binaries for WinCE on the download page,

Why do you need the binary?  If you use the amalgamation (a single
source file which includes the WinCE support) then you can just add that
source file and header to your project.  That way you'll get the right
CPU and WinCE version for your binaries.

I actually #include the amalgamation into my source file that interfaces
with SQLite so it doesn't even mean I have to worry about linking!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIp+iXmOOfHg372QQRAuxHAKCiQ7ImTNxcSsHcLhRePmvkYpvhIwCfeavB
ZcS9eECrcuted0i/XpKAmwM=
=uu57
-END PGP SIGNATURE-
___
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] Does sqlite support stored procedure?

[Harold Wood & Meyuni Gani]

Can you send examples?
Thanks


Woody
from his pda

-Original Message-
From: John Stanton <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2008 11:39 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Does sqlite support stored procedure?

Adding Javascript to Sqlite as a stored procedure language was a fairly 
simple operation.  Try it if you need stored procedures.

BareFeet wrote:
> Hi John,
> 
> 
>>I would like to know if SQLite supports stored procedures.
> 
> 
> Technically, no it doesn't.
> 
> For what purpose do you want to store procedures?
> 
> You can store some procedures in triggers, if you want to have SQLite  
> trigger a task when some data is changed.
> 
> You can simply create a "Procedures" table like this:
> 
> create table "Procedures" (Name, SQL);
> 
> and populate it with SQL procedures. You can call those procedures  
> later from within your program and sqlite3 command line and execute  
> them.
> 
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI applications:
> http://www.tandb.com.au/sqlite/compare/
> 
> ___
> 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] text datatype matching functions, binding vs explicit insert

[Harold Wood]

that i kind of counted on. if i have a column that i need to index i fix the 
case as i maintain the data, ill still have an unfixed column for the users 
view. that way i kind of compromise.
 
Woody

--- On Fri, 7/25/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

From: D. Richard Hipp <[EMAIL PROTECTED]>
Subject: Re: [sqlite] text datatype matching functions, binding vs explicit 
insert
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, July 25, 2008, 7:50 PM

On Jul 25, 2008, at 7:44 PM, Harold Wood & Meyuni Gani wrote:

> But you can use where 'AA' = upper(fld1) = to get around case
issues.


Yes you can.  But you need to be aware that doing so will prevent an  
index from being used to speed the search.  If your table is small it  
shouldn't make any difference.  But for a table with millions of  
entries, you could run into performance problems.

D. Richard Hipp
[EMAIL PROTECTED]



___
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] text datatype matching functions, binding vs explicit insert

[Harold Wood & Meyuni Gani]

But you can use where 'AA' = upper(fld1) = to get around case issues.

Woody
from his pda

-Original Message-
From: D. Richard Hipp <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2008 3:11 PM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database 

Subject: Re: [sqlite] text datatype matching functions, binding vs explicit 
insert


On Jul 25, 2008, at 8:50 AM, Chris Holbrook wrote:

> I created a table with four text columns and a blob column, and  
> populated it using sqlite3 functions ~prepare, ~bind_text,  
> ~bind_blob. Now I can't query the data using, for example, "where  
> app = 'AA'", though "where app like 'AA'" works! The LENGTH()  
> function returns the number of characters which one would expect.
>
> If I insert data in the same table using explicit SQL, I can use "="  
> with success.
>

The = operater is case sensitive.  LIKE is not.  'aa' LIKE 'AA' is  
true but 'aa'='AA' is false.

The previous paragraph is true by default.  There are ways of changing  
the default.  For example, if you declare a column to be COLLATE  
NOCASE then it will not be case sensitive (for US-ASCII characters).   
And there is a PRAGMA that will make LIKE case sensitive.


D. Richard Hipp
[EMAIL PROTECTED]



___
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] Convert the CURRENT_TIMESTAMP

[Harold Wood & Meyuni Gani]

Look at the wiki for date/time functions, specifically strft.

Woody
from his pda

-Original Message-
From: Joanne Pham <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2008 4:08 PM
To: General Discussion of SQLite Database ; [EMAIL 
PROTECTED]
Subject: [sqlite] Convert the CURRENT_TIMESTAMP

Hi All,
I ran the following sql statement
select CURRENT_TIMESTAMP;
and the output is :
2008-07-25 23:11:13
Is there any easy way or buildin function to convert this format to :
TUE JULY 25 23:11:13 2008
Thanks,
JP



___
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] Return a rowset from function?

[Harold Wood]

yes there is.
 
embed the sql in a table then execute it as needed passing the appropriate 
parms.

Woody

--- On Fri, 7/25/08, Robert Simpson <[EMAIL PROTECTED]> wrote:

From: Robert Simpson <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Return a rowset from function?
To: "'General Discussion of SQLite Database'" 
Date: Friday, July 25, 2008, 2:10 PM

No, but that would be wicked cool and bring stored procs a lot closer to
reality.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alexey Pechnikov
Sent: Friday, July 25, 2008 11:08 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Return a rowset from function?

Hello!

There is any way to create  'table functions', which are functions that
return 
a rowset and are used in place of a table to generate rows?


Best regards, Alexey.
___
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] Ultimate noob question: What do I do to reference another table?

[Harold Wood]

you need several tables,
then you can refernce them thru a view to show what is related to what.

--- On Fri, 7/18/08, LMcLain <[EMAIL PROTECTED]> wrote:

From: LMcLain <[EMAIL PROTECTED]>
Subject: [sqlite] Ultimate noob question: What do I do to reference another 
table?
To: sqlite-users@sqlite.org
Date: Friday, July 18, 2008, 1:33 AM

Hi everyone,

   Yes, this is quite the noob question: I want to have one fields reference
another table, how do I do that?
   Here's what I am trying to do.  I have a bunch of fields(or columns)
Item, UPC, Manufacturer, MSRP for example.  I want to be able to choose the
Manufacturer from a different table's list of manufacturers and then have
that field address that record.  
   I hope that I am making sense, but I wouldn't be surprised if I'm
not! :/ 
I can't figure out how to do it or even what the terminology for this
process is so that I can look it up myself!  Please help!

Thanks,
Lee
-- 
View this message in context:
http://www.nabble.com/Ultimate-noob-question%3A-What-do-I-do-to-reference-another-table--tp18522905p18522905.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] ANN: SQLite .NET provider updated

[Harold Wood]

wow, can i nominate you for sainthood?  i mean really!  i was trying to use 
esql and its a mess...ug!
 
thanks!
 
Woody


--- On Wed, 7/16/08, Robert Simpson <[EMAIL PROTECTED]> wrote:

From: Robert Simpson <[EMAIL PROTECTED]>
Subject: Re: [sqlite] ANN: SQLite .NET provider updated
To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org>
Date: Wednesday, July 16, 2008, 10:27 PM

Sure does! 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Harold Wood
Sent: Wednesday, July 16, 2008 7:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] ANN: SQLite .NET provider updated

very very cool!  now does it work with teh compact framework?

--- On Wed, 7/16/08, Robert Simpson <[EMAIL PROTECTED]> wrote:

From: Robert Simpson <[EMAIL PROTECTED]>
Subject: [sqlite] ANN: SQLite .NET provider updated
To: "'General Discussion of SQLite Database'"
<sqlite-users@sqlite.org>
Date: Wednesday, July 16, 2008, 8:22 PM

I don't normally announce releases here, but this one's got some great
stuff
in it.  Those of you using the SQLite ADO.NET provider will want to check
out.

Some highlights in the 52 release:
3.6.0 code merge
Table and View designers - you can now create and design tables and views,
indexes and foreign keys from the Visual Studio Server Explorer with a nice
interactive GUI.  This is still in beta, but it's looking really good.
Trigger designer is coming up soon.
Entity Framework support much improved from the 51 release.  Still in beta
while Visual Studio 2008 SP1 is in beta.

It's public domain, it's open source, and 3.5 years stable.  Why buy a
SQLite ADO.NET provider from someone else?

http://sqlite.phxsoftware.com

Robert



___
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] ANN: SQLite .NET provider updated

[Harold Wood]

very very cool!  now does it work with teh compact framework?

--- On Wed, 7/16/08, Robert Simpson <[EMAIL PROTECTED]> wrote:

From: Robert Simpson <[EMAIL PROTECTED]>
Subject: [sqlite] ANN: SQLite .NET provider updated
To: "'General Discussion of SQLite Database'" 
Date: Wednesday, July 16, 2008, 8:22 PM

I don't normally announce releases here, but this one's got some great
stuff
in it.  Those of you using the SQLite ADO.NET provider will want to check
out.

Some highlights in the 52 release:
3.6.0 code merge
Table and View designers - you can now create and design tables and views,
indexes and foreign keys from the Visual Studio Server Explorer with a nice
interactive GUI.  This is still in beta, but it's looking really good.
Trigger designer is coming up soon.
Entity Framework support much improved from the 51 release.  Still in beta
while Visual Studio 2008 SP1 is in beta.

It's public domain, it's open source, and 3.5 years stable.  Why buy a
SQLite ADO.NET provider from someone else?

http://sqlite.phxsoftware.com

Robert



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

Re: [sqlite] View with Dynamic Fields ?

[Harold Wood]

if sqlite supported the pivot command
 
Woody

--- On Tue, 7/8/08, Andrea Connell <[EMAIL PROTECTED]> wrote:

From: Andrea Connell <[EMAIL PROTECTED]>
Subject: Re: [sqlite] View with Dynamic Fields ?
To: "General Discussion of SQLite Database" 
Date: Tuesday, July 8, 2008, 3:27 PM

Thanks Chris & Dennis for the group_concat tip... It is an interesting
idea but I don't think it makes sense to use this time. If I have to
parse the results in my code, I might as well just get the answers in
separate rows and group them together in code without having to worry
about returning values for the non-answered questions. 

I knew this wouldn't be an easy thing to solve, but appreciate the
input. I am still holding a shred of hope for a trigger that can
recreate the view whenever the questions table is modified but I haven't
put much thought into it yet and I'm sure it's just as challenging. 

I'm going to keep working on this and see if I can come up with
anything. If anybody thinks of something, let me know.

I know that I could do this in code, and at this point it would probably
take less time, but I'd really like to see if this is possible more than
anything.

Thanks

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 08, 2008 1:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] View with Dynamic Fields ?


This gives the same result set you got and does not need to be edited:

select
   applicantid,
   group_concat(answer, '|')
   from
 (select applicantid, answer from tblanswers order by questionid)
   group by applicantid;

The group_concat() function is part of recent versions of SQLite. It is
returning a single string rather than columns, but it can be parsed. The
subquery ordering by questionid is needed to ensure that the columns
(answers) from all respondents are in the same order.

However, this will only work if tblanswers will always have one record
for every question. That is, if your applicants skip one or more
questions, you will still need to insert a record for skipped
questionids, perhaps with a default answer like 'NOT ANSWERED'.
Similarly, if you add new questions to tblquestions later, you will need
to insert 'NO ANSWER' 
values into tblanswers for existing applicantids, othewise the columns
(answers) will not align.

Chris

On Tue, 8 Jul 2008, Andrea Connell wrote:

> I'm not sure if what I want is possible to do in SQL, but I -am- sure 
> that one of you will know.
> Given two tables - one with questions to ask applicants and one with 
> an applicant's answer to a particular question - I want to make a 
> flattened view with all of an applicant's answers in one row. This is 
> easy enough to do when I know ahead of time which questions are in the

> first table, but I don't like the hard-coded approach.
>
> My schema is as follows:
>
> CREATE TABLE tblquestions (questionid int, question varchar(100)); 
> INSERT INTO "tblquestions" VALUES(1,'whats up'); INSERT
INTO 
> "tblquestions" VALUES(2,'how are you'); INSERT INTO
"tblquestions" 
> VALUES(3,'whats your name'); CREATE TABLE tblanswers (questionid
int, 
> applicantid int, answer varchar(2500)); INSERT INTO "tblanswers"

> VALUES(1,100,'stuff for answer one'); INSERT INTO
"tblanswers" 
> VALUES(2,100,'stuff for answer two'); INSERT INTO
"tblanswers" 
> VALUES(3,100,'stuff for answer three'); INSERT INTO
"tblanswers" 
> VALUES(1,200,'random text one'); INSERT INTO
"tblanswers" 
> VALUES(2,200,'random text two'); INSERT INTO
"tblanswers" 
> VALUES(3,200,'random text three');
>
>
> Here is the view I have come up with so far, which would require 
> editing whenever an insert or delete is done on tblQuestions.
>
> CREATE VIEW allanswers as
> SELECT applicantid,
>  (select answer from tblanswers Z where questionid = 1 and

> Z.applicantid = A.applicantid) As Answer1,
>  (select answer from tblanswers Z where questionid = 2 and

> Z.applicantid = A.applicantid) As Answer2,
>  (select answer from tblanswers Z where questionid = 3 and

> Z.applicantid = A.applicantid) As Answer3 FROM tblanswers A group by 
> applicantid;
>
> sqlite> select * from allanswers;
> 100|stuff for answer one|stuff for answer two|stuff for answer three
> 200|random text one|random text two|random text three
>
>
> Has anybody come across a problem like this and found a reasonable 
> dynamic solution? Even something like a trigger on tblQuestions to 
> change the View would be great, I'm just not sure about the SQL 
> involved. If it helps, I don't really care what the columns end up 
> being named. Also I can't guarantee that the questionids will be 
> consecutive or in any order.
>
> Thanks,
> Andrea
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] View with Dynamic Fields ?

[Harold Wood]

my approah would be a table for applicants, then the table for answers would 
have the applicantid and the questionid as well as the answer.
 
CREATE TABLE tblApplicants (applicantid int, applicantname varchar(100));
 
now just do a select joining the tables
 
select ap.applicantname, qu.question, an.answer
from tblanswers an
inner join tblApplicants ap
on ap.applicantid = an.applicantid 
inner join tblquestions qu
on qu.questionid = an.questionid 
-- optional where clause to select just 1 applicant
where ap.applicantid = 1
-- optional order by clause to make it neat
order by applicantname asc, qu.questionid asc

 


--- On Tue, 7/8/08, Andrea Connell <[EMAIL PROTECTED]> wrote:

From: Andrea Connell <[EMAIL PROTECTED]>
Subject: [sqlite] View with Dynamic Fields ?
To: sqlite-users@sqlite.org
Date: Tuesday, July 8, 2008, 12:09 PM

I'm not sure if what I want is possible to do in SQL, but I -am- sure
that one of you will know. 
Given two tables - one with questions to ask applicants and one with an
applicant's answer to a particular question - I want to make a flattened
view with all of an applicant's answers in one row. This is easy enough
to do when I know ahead of time which questions are in the first table,
but I don't like the hard-coded approach. 
 
My schema is as follows:
 
CREATE TABLE tblquestions (questionid int, question varchar(100));
INSERT INTO "tblquestions" VALUES(1,'whats up');
INSERT INTO "tblquestions" VALUES(2,'how are you');
INSERT INTO "tblquestions" VALUES(3,'whats your name');
CREATE TABLE tblanswers (questionid int, applicantid int, answer
varchar(2500));
INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer
one');
INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer
two');
INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer
three');
INSERT INTO "tblanswers" VALUES(1,200,'random text one');
INSERT INTO "tblanswers" VALUES(2,200,'random text two');
INSERT INTO "tblanswers" VALUES(3,200,'random text three');

 
Here is the view I have come up with so far, which would require editing
whenever an insert or delete is done on tblQuestions.

CREATE VIEW allanswers as
SELECT applicantid, 
  (select answer from tblanswers Z where questionid = 1 and
Z.applicantid = A.applicantid) As Answer1,
  (select answer from tblanswers Z where questionid = 2 and
Z.applicantid = A.applicantid) As Answer2,
  (select answer from tblanswers Z where questionid = 3 and
Z.applicantid = A.applicantid) As Answer3
FROM tblanswers A
group by applicantid;

sqlite> select * from allanswers;
100|stuff for answer one|stuff for answer two|stuff for answer three
200|random text one|random text two|random text three
 
 
Has anybody come across a problem like this and found a reasonable
dynamic solution? Even something like a trigger on tblQuestions to
change the View would be great, I'm just not sure about the SQL
involved. If it helps, I don't really care what the columns end up being
named. Also I can't guarantee that the questionids will be consecutive
or in any order.
 
Thanks,
Andrea
___
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] Bad UPDATE Problems in Mobile6 device

[Harold Wood]

similar statements work fine on my pda, ipaq 210 with min mobile6.  can you 
paste your code?

--- On Tue, 7/8/08, Bob Dennis <[EMAIL PROTECTED]> wrote:

From: Bob Dennis <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Bad UPDATE Problems in Mobile6 device
To: sqlite-users@sqlite.org
Date: Tuesday, July 8, 2008, 4:50 AM

Bob Dennis wrote:
> 
> I have tried with and without the single quotes(Saw them in an example
> somewhere),
> makes no difference(Why do I not get an error if it is wrong?)
> Yes I want to set all flags in this VERY SIMPLE test , just to get
> something to happen.
> 
> Can things be left in a locked state somehow? 
> 
> Bob
> 
>  
> 
> 
> 
>  
> ___
> 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/Bad-UPDATE-Problems-in-Mobile6-device-tp18314650p18334687.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Bad UPDATE Problems in Mobile6 device

[Harold Wood]

also that query will update all rows in the commentlist table.  is that what 
you want to do?
 
Woody

--- On Mon, 7/7/08, Stephen Oberholtzer <[EMAIL PROTECTED]> wrote:

From: Stephen Oberholtzer <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Bad UPDATE Problems in Mobile6 device
To: "General Discussion of SQLite Database" 
Date: Monday, July 7, 2008, 11:50 PM

On Mon, Jul 7, 2008 at 7:26 AM, Bob Dennis <[EMAIL PROTECTED]>
wrote:

> Hi
> I am getting odd results with a simple update query in a Mobile 6 device.
> It only works sometimes , but never reports an error.
> The same code works fine in PocketPC and Mobile5 devices.
>
>  UPDATE 'CommentList' SET xFlag = 3
>
> I wondered if anyone else has seen this, and why would a query not work
> without giving an error.
> This leaves me very worried.
>
> Any ideas greatfully received.


Well, you shouldn't be putting your table names in single-quotes.
Double-quotes are okay, although even they're not necessary in this case


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
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] Fuzzy Matching

[Harold Wood]

I cant go into too much detail because of my current job, but for fuzzy 
matching levenstien isnt very good, you need to try looking into ngram matching 
techniques, it is absolutely awesome in reducing over/under matches.
 
Woody

--- On Sat, 7/5/08, Stephen Woodbridge <[EMAIL PROTECTED]> wrote:

From: Stephen Woodbridge <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Fuzzy Matching
To: "General Discussion of SQLite Database" 
Date: Saturday, July 5, 2008, 11:24 PM

Stephen Woodbridge wrote:
> I would be interested in having something like this also.
> 
> What I don't understand in your approach is how you compute the 
> (Levenstein) distance during a search. It seems like you have a fixed 
> set of tokens from your document text and these are indexed. Then you 
> have a query token the you want to compare to the index based on some 
> fuzzy distance. Since every query can be different I think you have to 
> compute the distance for every key in the index? that would require 
> doing a full index scan.
> 
> If there ware a function that you could run a token through that would 
> given you that tokens "location" in some space then you could
generate a 
> similar "location" for the query token and then use the rtree
and 
> distance. I'm not aware of any such functions, but my expertise is
more 
> in GIS the search searching.

Hmmm, that was supposed to say text searching.

> Thoughts?
> 
> Best,
>-Steve
> 
> Martin Pfeifle wrote:
>> Hi, I think there is nothing available except FTS. Doing a full table
>> scan and computing for each string the (Levenstein) distance to the
>> query object is too time consuming. So what I would like to see is
>> the implementation of a generic metric index which needs as one
>> parameter a metric distance function. Based on such a distance
>> function you could then do similarity search on any objects , e.g.
>> images, strings, etc. One possible index would be the M-tree (which
>> you can also organize relational as it was done with the R*-tree).
>> The idea is that you have a hierarchical index and each node is
>> represented by a database  object o and a covering radius r
>> reflecting the maximal distance of all objects in that subtree to the
>> object o. If you do a range query now, you compute the distance of
>> your query object to the object o. If this distance minus the
>> coverage radius r is bigger than your query range you can prune that
>> subtree. You can either implement such a similarity module as an own
>> extension similar toFTS or the Spatial module, or integrate it into
>> FTS and use it only for strings. Personally, I need the second
>> solution because I'd like to do full and fuzzy text search. Are
there
>> any plans to implement something like this, if yes, I would like to
>> take part in such a development. . Best Martin
>>
>>
>>
>>
>> - Ursprüngliche Mail  Von: Alberto Simões
>> <[EMAIL PROTECTED]> An: General Discussion of SQLite Database
>>  Gesendet: Donnerstag, den 3. Juli
2008,
>> 21:52:05 Uhr Betreff: [sqlite] Fuzzy Matching
>>
>> Hello
>>
>> Although I am quite certain that the answer is that SQLite does not 
>> provide any mechanism to help me on this, it doesn't hurt to ask.
Who
>>  know if anybody have any suggestion.
>>
>> Basically, I am using SQLite for a dictionary, and I want to let the 
>> user do fuzzy searches. OK, some simple Levenshtein distance of one
>> or two would do the trick, probably.
>>
>> I imagine that SQLite (given the lite), does not provide any kind of 
>> nearmisses search. But probably, somebody here did anything similar
>> in any language?
>>
>> Cheers Alberto
> 
> ___
> 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] Multiple constraints per table?

[Harold Wood & Meyuni Gani]

Lol. Thanks. If you want a schema I can attach and send to you.

Woody
from his pda

-Original Message-
From: flakpit <[EMAIL PROTECTED]>
Sent: Tuesday, July 01, 2008 11:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multiple constraints per table?



Harold Wood  Meyuni Gani wrote:
> 
> U, hmm. The tips I gave you were from my pda based shopping program
> that will be selling for 9.99 soon.
> 

Good on you Woody, hope you sell a lot.! If my eyes were up to the challenge
of reading my PDA's small screen, i'd buy a copy and save myself work (LOL!) 

With the amount of junk I've churned out of the years, i've yet to get
anyone to buy anything yet (ROFL).  Actually, not quite true. One
registration out of 6,500 downloads of my address book.
-- 
View this message in context: 
http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18231100.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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

Re: [sqlite] Multiple constraints per table?

[Harold Wood & Meyuni Gani]

U, hmm. The tips I gave you were from my pda based shopping program that 
will be selling for 9.99 soon.  Its 6 for one, half dozen for the other. You 
can design the db so it does the work for you or you code the program to do the 
work for you.

Either way, you will get things to work, it just depends upon how you want to 
partition your code.

Woody
from his pda

-Original Message-
From: flakpit <[EMAIL PROTECTED]>
Sent: Tuesday, July 01, 2008 11:00 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multiple constraints per table?


I understand your solution Woody, but it isn't practical for me, not for my
home shopping list program. A full on relational database like that is an
awful lot of work and it's only for home use (and any other family I can con
into using it and testing it).

I'd go for the relational route if I were designing an enterprise wide
product, it's only sensible (as you intimated) but Igor's solution (that I
also found independantly) will work fine for a small system for now.

Thanks for the ideas, will keep proper design in mind if I ever get talked
into doing something for a company (something I try mightily to avoid,
believe me!!!)
-- 
View this message in context: 
http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230807.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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

Re: [sqlite] Multiple constraints per table?

[Harold Wood]

your main issue seems to be that you really need to normalize your db.  using 
your example of pepper 220 gr, when you examine that string it consists of 3 
parts, pepper = product description or product name, 220 = weight or volume or 
measurement of product, followed by the measurement type. All 3 of those 
properties really dont make a unique item but instead are properties of the 
item.
 
I think that multiple tables would give you a better db design and fix some of 
your isses with the constraints.  looking at your current create table 
statement you have columns that belong in a store table, an item table, a 
purchase history table as well as store_carried table.  
 
just imho.

Woody

--- On Tue, 7/1/08, flakpit <[EMAIL PROTECTED]> wrote:

From: flakpit <[EMAIL PROTECTED]>
Subject: [sqlite] Multiple constraints per table?
To: sqlite-users@sqlite.org
Date: Tuesday, July 1, 2008, 1:10 AM

This is the way I normally create my shopping database, but this leads to
thousands of duplicates.

CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop
TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER
PRIMARY KEY AUTOINCREMENT)


'item' is the full retailer's description for this shopping item.
If I make
this unique, then i eliminate all duplicates and any further entries of this
item, so that's no good.

item TEXT CONSTRAINT item UNIQUE

is it legal sql syntax to allow more than one constraint field in table
creation? I need at least these four below to guarantee that duplicate items
do make it into the database but not on the same day.

item TEXT CONSTRAINT item UNIQUE
units TEXT CONSTRAINT units UNIQUE
shop TEXT CONSTRAINT shop UNIQUE
date TEXT CONSTRAINT date UNIQUE


So the below three records would be allowed as the unit weight is different
and also there are two different dates and as everyone knows, peppers come
in all shapes and sizes (grin) (This is okay)

pepper, 120gm, coles, 02/02/2006
pepper, 50gm, coles, 02/02/2006
pepper, 50gm, coles, 04/11/2007


Or would the multiple constraints work globally (if legal syntax) Would it
end up with only the single record below because the all constraints
operated globally?

pepper, 120gm, coles, 02/02/2006
-- 
View this message in context:
http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18209309.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] help with Dates please

[Harold Wood]

I have several tables that i need to datestamp as transactions occur and then 
retrive with a select where between X and Y.

What is the best way to do this in SQLite?

Thanks

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

Re: [sqlite] Date Selection

[Harold Wood]

thanks.

--- On Thu, 6/12/08, Igor Tandetnik [EMAIL PROTECTED] wrote:

From: Igor Tandetnik [EMAIL PROTECTED]
Subject: Re: [sqlite] Date Selection
To: sqlite-users@sqlite.org
Date: Thursday, June 12, 2008, 7:51 AM

"Harold Wood" [EMAIL PROTECTED]
wrote in message news:[EMAIL PROTECTED]
 the create table statement:

 LastPurchaseDate DATETIME,

You seem to be under impression there's a dedicated DATETIME type in 
SQLite. This is not the case: it's just the nature of SQLite's manifest

typing (http://sqlite.org/datatype3.html) that allows one to specify any 
odd identifier as a column type.

You have a choice of storing dates and times as strings (e.g. 
'2008-06-10'), as integer number of seconds since Unix epoch, or as 
floating point Julian day number. You manipulate these representations 
using built-in date/time functions:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

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] Date Selection

[Harold Wood]

Hello Igor
the create table statement:

CREATE TABLE Items
 (
 
ID
 INT NOT NULL PRIMARY KEY ASC,
 SubCatIdINT NOT NULL,
 DescriptionVARCHAR(60) NOT NULL, 
 LastUnitPriceNUMERIC(6,2) DEFAULT 0.0,
 AvgUnitPriceNUMERIC(6,2) DEFAULT 0.0,
 MinUnitPriceNUMERIC(6,2) DEFAULT 0.0,
 LastPurchaseDateDATETIME,
 LastQtyPurchase NUMERIC(6,2) DEFAULT 0.0,
 PurchaseUnitVARCHAR(20),
 NumTimesPurchasedINT DEFAULT 0,
 NeedItBIT DEFAULT 0,
 FOREIGN KEY (SubCatId) REFERENCES Subcategory(ID)
 );

--- On Wed, 6/11/08, Igor Tandetnik [EMAIL PROTECTED] wrote:

From: Igor Tandetnik [EMAIL PROTECTED]
Subject: Re: [sqlite] Date Selection
To: sqlite-users@sqlite.org
Date: Wednesday, June 11, 2008, 10:45 PM

"Harold Wood" [EMAIL PROTECTED]
wrote in message news:[EMAIL PROTECTED]
 I have a table with a date column.nbsp; I want to select * from
 TableA where DateCol Between '2008-06-10' and
'2008-06-11';nbsp;
 when i execute that query i get 0 records. when i remove the date
 selection i get all teh records. nbsp;
 what is the best way to query on date?

How do you store your dates? Show the output of this statement:

select DateCol, typeof(DateCol) from TableA limit 1;

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] Date Selection

[Harold Wood]

I have a table with a date column. I want to select * from TableA where 
DateCol Between '2008-06-10' and '2008-06-11'; when i execute that query 
i get 0 records. when i remove the date selection i get all teh records.

what is the best way to query on date?

thanks

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

Re: [sqlite] Interruption

[Harold Wood]

look at teh trigger functions.

--- On Fri, 6/6/08, Hildemaro Carrasquel [EMAIL PROTECTED] wrote:

From: Hildemaro Carrasquel [EMAIL PROTECTED]
Subject: [sqlite] Interruption
To: sqlite-users@sqlite.org
Date: Friday, June 6, 2008, 2:17 AM

Hello.-

Is there any function that i can make event when a data change?

-- 
Ing. Hildemaro Carrasquel
Ingeniero de Proyectos
Cel.: 04164388917/04121832139
___
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] design question / discussion

[Harold Wood & Meyuni Gani]

I've done an app like that before with a different db foundation. Basically 2 
different databases, same structure. The logging app hits an ini file before 
each write, if the current db is different than the name in the ini file then 
close the current db, open the new db and write the row to the new db, 
otherwise write the row to the current db.

I had background app that ran as a service and would switch the fb name in the 
ini file when one hour had passed or the db was full.

It worked great .

Woody
from his pda

-Original Message-
From: A.J.Millan <[EMAIL PROTECTED]>
Sent: Wednesday, May 21, 2008 2:29 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] design question / discussion

Rich Rattanni wrote:>Hi I have a general design question.  I have the
following senario...

>In an embedded system running linux 2.6.2x I have a sqlite database
>constantly being updated with data acquired by the system.  I cant
>lose data (hence why I am using sqlite in the first place).  However
>periodically I have download the data contain within the database to a
>central server.  The system cannot stall during the download and must
>continue to record data.  Also, after the download I need to shrink
>the database size, simply because if the database is allowed to grow
>to its max size (~50MB) then every download thereafter would be 50MB,
>which is unacceptable.

After thinking in your's problem, according to yours first exposition, it 
seems that you are using the SQLite dbase as a mere tampon or 
temporarybuffer to the acquired data.  In that condition, with no further 
process of those data in the embedded system, perhaps you can consider 
simply write a flat file appending to it the incoming data (may be 
alternating between two or more files) and then compress and send the data 
to the host where they can be further processed or appended to a dbase.

>From the security point of view, the data in the embedded device are not 
necesarily  safer in a SQLite dbase that in a flat file.  Perhaps that 
layer(SQLite) are not necessary at all in the embedded device.

Just thinking out loud :-)

Adolfo.

___
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] deleting 100,000 entries

[Harold Wood & Meyuni Gani]

Doesn't sqlite support the truncate table command

Woody
from his pda

-Original Message-
From: Carlo S. Marcelo <[EMAIL PROTECTED]>
Sent: Tuesday, May 20, 2008 8:49 PM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] deleting 100,000 entries

@Barefoot and Keith,

Awesome! It took forever for the web interface to delete, and I had to restart 
httpd for the database to unlock.. the command you provided took less than a 
second to clear everything out, thanks!

Carlo

- Original Message 
From: BareFeet <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, May 21, 2008 11:45:06 AM
Subject: Re: [sqlite] deleting 100,000 entries

Hi Carlo,

> I want to clear the whole thing out(list)!

Do you mean that you want to delete all rows from the "list" table? If  
so, do this:

delete from list;

See the syntax pages at:
http://www.sqlite.org/lang.html
http://www.sqlite.org/lang_delete.html

Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/?ml

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



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

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

Re: [sqlite] CASE WHEN sample ?

[Harold Wood & Meyuni Gani]

Better would be
Insert into tablea(ida, value1a)
Select idb, value1b
from tableb
where idb not in(select ida from tablea);

Woody
from his pda

-Original Message-
From: Lothar Behrens <[EMAIL PROTECTED]>
Sent: Saturday, May 10, 2008 2:23 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] CASE WHEN sample ?

Hi,

I am searching for a sample that uses the case when expression.
I like to insert values into a table when these values are not in that 
table before.

My tries to read, understand and try the documentation of expressions 
failed.

Executing this statemen twice creates two rows:

replace into anwendungen (name) values ('lbDMF Manager')

This is pseudo code:

if not exsists (select id from mytable where name = 'some name')
insert into mytable (name) values ('some name')

Is this possible ?

Thanks

Lothar

___
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