Re: [sqlite] How to build mutil-primary key for a table?

[Kermit Mei]

On Wed, 2009-07-01 at 22:45 -0400, Igor Tandetnik wrote:
> Kermit Mei wrote:
> > I have three table like this:
> >
> > Device(id,name,icon,type) -id as primary key
> >
> > ZWavePhsicalDevice(id,funcode)  -id as primary key
> >
> > ZWaveGSDevice(Device.id, ZWavePhsicalDevice.id,order)
> > -Device.id and ZWavePhsicalDevice.id as mutil primary keys.
> >
> > Now, I don't know how to build the ZWaveGSDevice(...) in sqlite3 ?
> 
> The same way as in any other SQL database:
> 
> create table ZWaveGSDevice(Device_id, ZWavePhsicalDevice_id, order,
> primary key(Device_id, ZWavePhsicalDevice_id));
> 
> Igor Tandetnik 

I see. Thanks, Igor:p

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

Re: [sqlite] Changing Table Contents

[David Baird]

On Wed, Jul 1, 2009 at 9:50 PM, Rick Ratchford wrote:
> From what I understand, ANYTIME you do a SQL statement, such as "SELECT...",
> you are doing this to a TABLE and returning the result in a sort of
> 'recordset'.
>
> So then, the table is this sqlite_master, the field is tbl_name, and the
> result of the SQL query can be had from the recordset it returns. Right?

That is correct.  I think that, to be very pedantic, "column" might be
a better term than "field" for tbl_name.

> So using VB and my wrapper, with SQLite, it is...
>
> Set Rs = Cnn.OpenRecordset(SQLString)
>
> I did this and it worked! The returned recordset has a field named after the
> function, called count(*).

Here's another trick you can do too if you want to give specific names
to the resulting columns:

SELECT count(*) AS my_count FROM sqlite_master WHERE tbl_name = 'Foo';

> So I guess the answer I was looking for as to where to find the returned
> result is "RECORDSET". It wasn't that obvious to me until a few minutes ago.

Glad you found it! :-)

If you're still new to SQL and want to delve more into theory, you
could search Google for "normal forms" (e.g. database
normalization)... but that may not be too important until you start
working with larger or complex data.

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

Re: [sqlite] Changing Table Contents

[Rick Ratchford]

Although I'm going to use the easier DROP TABLE IF EXISTS... line as this
works wonderfully.

The only reason I asked about the other method you mentioned is that I
wanted to at least understand how it worked. The more one knows, the easier
it gets later on.

Anyway, I think I may know how the value is returned.

I have to assume that you thought I was looking for a VB solution. Actually,
I'm still brand new at this so sometimes the answer is something obvious to
others.

>From what I understand, ANYTIME you do a SQL statement, such as "SELECT...",
you are doing this to a TABLE and returning the result in a sort of
'recordset'.

So then, the table is this sqlite_master, the field is tbl_name, and the
result of the SQL query can be had from the recordset it returns. Right?

So using VB and my wrapper, with SQLite, it is...

Set Rs = Cnn.OpenRecordset(SQLString)

I did this and it worked! The returned recordset has a field named after the
function, called count(*). 

So I guess the answer I was looking for as to where to find the returned
result is "RECORDSET". It wasn't that obvious to me until a few minutes ago.

Thanks David.

(and Simon for your comments as well).

Thank you again.

Rick


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Baird
Sent: Wednesday, July 01, 2009 9:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Changing Table Contents

On Wed, Jul 1, 2009 at 6:05 PM, Rick Ratchford
wrote:
> I'm using a VB wrapper, and so I run this by...
>
>    Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name = 
> 'DeltaGrid'"
>
> Thing is, I don't know where to check for the return value.

I'm afraid I can't help with this - I don't have Visual Basic experience :-(
___
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 build mutil-primary key for a table?

[Igor Tandetnik]

Kermit Mei wrote:
> I have three table like this:
>
> Device(id,name,icon,type) -id as primary key
>
> ZWavePhsicalDevice(id,funcode)  -id as primary key
>
> ZWaveGSDevice(Device.id, ZWavePhsicalDevice.id,order)
> -Device.id and ZWavePhsicalDevice.id as mutil primary keys.
>
> Now, I don't know how to build the ZWaveGSDevice(...) in sqlite3 ?

The same way as in any other SQL database:

create table ZWaveGSDevice(Device_id, ZWavePhsicalDevice_id, order,
primary key(Device_id, ZWavePhsicalDevice_id));

Igor Tandetnik 



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

Re: [sqlite] Help with SQLite Query

[Igor Tandetnik]

JokBoy wrote:
> I have tried your query and I don't get any rows returned.  Any ideas
> why it wouldn't work?

If you tried it against the database file you showed in your original 
post, note that you have MOEData.Date in different format from 
Criteria.StartTime_crit and EndTime_crit. Recall that SQLite doesn't 
have a dedicated date/time type: those fields are just strings, and are 
compared as strings, lexicographically.

Igor Tandetnik 



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

Re: [sqlite] Changing Table Contents

[David Baird]

On Wed, Jul 1, 2009 at 6:05 PM, Rick Ratchford wrote:
> I'm using a VB wrapper, and so I run this by...
>
>    Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name =
> 'DeltaGrid'"
>
> Thing is, I don't know where to check for the return value.

I'm afraid I can't help with this - I don't have Visual Basic experience :-(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting offending constraint

[Simon Slavin]

On 2 Jul 2009, at 1:57am, James Gregurich wrote:

> I tried that, but I still got back "constraint failed" rather than my
> RAISE message. Since you say it should work, I probably did something
> wrong. I'll look at it again.

If you left the constraint definition in in your table definition then  
you're getting an error from that rather than from the trigger.

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

Re: [sqlite] 3 million rows, query speeds, and returning zero for rows that don't exist

[Simon Slavin]

On 2 Jul 2009, at 2:00am, yaconsult wrote:

> For testing purposes, I created indexes
> for all the columns used in the selects below.

This doesn't do what you want.  For instance, suppose I had a huge  
amount of data in this table:

   description   |  date   |  time
+-+-

and suppose I wanted to list all the events in date/time order.  I do  
something like

SELECT description FROM events ORDER BY date,time

Creating one index on the date column and one on the time column does  
not help much: it lets the SELECT sort by date quickly, but then it  
has to sort all items on each day manually.  So if you have a hundred  
events one day and a hundred on the next it has to perform two sorts  
of 100 items each time I execute the SELECT command.  With 3 million  
lines a day that's a huge amount of sorting when you do the SELECT  
command.

What this actually needs is one index on both columns:

CREATE INDEX timestamp ON events (date,time)

Then the SELECT command just uses that index and magically gets every  
single line in the order it wants.  Even for something like

select
date,
hour,
min,
sec,
count(port)
from log
where
uid != "-"
and (response = 207
 or response = 200)
and port = 8444
group by
timestamp
order by
timestamp;

indexes on all the separate fields would help only in selecting on one  
field.  A good index for this might be something like

CREATE INDEX i_207_and_200 ON log (uid,port,response,timestamp)

or

CREATE INDEX i_207_and_200 ON log (uid,port,timestamp,response)

Imagine you were doing the SELECT yourself, by hand.  Work out what  
kind of index would help /you/ most.

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

[sqlite] How to build mutil-primary key for a table?

[Kermit Mei]

Hello community!

I have three table like this:

Device(id,name,icon,type) -id as primary key

ZWavePhsicalDevice(id,funcode)  -id as primary key

ZWaveGSDevice(Device.id, ZWavePhsicalDevice.id,order)
 -Device.id and ZWavePhsicalDevice.id as mutil primary keys. 

Now, I don't know how to build the ZWaveGSDevice(...) in sqlite3 ?

Hope any help.
Thank you, very much!

Kermit Mei

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

Re: [sqlite] Changing Table Contents

[Simon Slavin]

On 2 Jul 2009, at 1:22am, Rick Ratchford wrote:

> Okay, I found what needed to be added to DROP TABLE to make it not  
> produce
> the error.
>
> DROP TABLE IF EXISTS Foo
>
> That did the trick.
>
> Still haven't figured out how to get the test result from...
>
> SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo';
>
> If this returns 1 or 0 based on whether there is a table called  
> 'Foo' in the
> sqlite_master, where does one check for that 1 or 0?

You no longer care.  Each time you want to start again do these two  
commands:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo ... (whatever your requirements are)

Neither will give an error.  The second command will always result in  
an empty table.

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

[sqlite] 3 million rows, query speeds, and returning zero for rows that don't exist

[yaconsult]

I'm using sqlite to do some analysis on very large web application log
files - approaching 3 million lines per day.  And what a wonderful tool
it is!  It has saved me from writing lots of custom scripts.

I have a perl script that parses an rsynced copy of this huge log file,
munges, converts, and adds new entries to my database.

The schema currently looks something like this - some of the columns
are there as a aid to development/debugging so I can see what's
going on:

CREATE TABLE 'log' (
 ip VARCHAR,
 datetimestring VARCHAR COLLATE NOCASE,
 uid VARCHAR COLLATE NOCASE,
 timestamp INTEGER,
 operation VARCHAR COLLATE NOCASE,
 target VARCHAR,
 response INTEGER,
 client VARCHAR COLLATE NOCASE,
 completion VARCHAR COLLATE NOCASE,
 port INTEGER,
 connections INTEGER,
 date INTEGER,
 hour INTEGER,
 min INTEGER,
 sec INTEGER
)

I've been experimenting with indices as far as when the tradeoff in
size and speed of adding data is offset sufficiently by how fast I
need to get the results.  For testing purposes, I created indexes
for all the columns used in the selects below.

Most of the queries I've done so far have been pretty straightforward
and it's worked very well.  But, now I need to do one that's taking
too long.  There's probably a better way than the one I'm using.

The problem is that I need to produce information for the timeslots
that are missing as well as those that are there.

For example, I need to be able to evaluate the effect of different
load balancers, so I want to compare the numbers of transactions on
all the ports.   I need to do so on a second by second basis so I can
see the most detail and not an average.

I thought one way do do this would be with a self-join because
there are so many transactions that I'm pretty sure that all seconds
will be present in the table.  I also considered creating a second
table derived from the first whenever it's updated that would have a
unix epoch entry for each second within the log file. 

Here's what I tried for the first approach:

select
distinct t.timestamp,
t.port,
count(u.timestamp)
from
log t
left join
log u
on
t.timestamp = u.timestamp
and
t.timestamp >= 1246266000
and u.uid != "-"
and (u.response = 207
 or u.response = 200)
and u.port = 8444;

The purpose of the join is to give me all of the timestamps - one for
each second - even those seconds that had no activity on that port -
and then use those for the query.  But this query is taking a very,
very, very long time.  I'm probably making some newbie mistake,
because that's what I am!

Is this a valid approach?  Is there a better solution?

I am able to get results extremely quickly by using something like
this:

select
date,
hour,
min,
sec,
count(port)
from log
where
uid != "-"
and (response = 207
 or response = 200)
and port = 8444
group by
timestamp
order by
timestamp;

but the problem is that I also need to know when the ports are not
busy and there are no log entries.  Here are the last few lines of the
result: 

2009-06-29|17|42|0|2
2009-06-29|17|42|7|1
2009-06-29|17|42|8|4

What I need to have in the results are entries for seconds 1-6 with a
value of 0, like this:

2009-06-29|17|42|0|2
2009-06-29|17|42|1|0
2009-06-29|17|42|2|0
2009-06-29|17|42|3|0
2009-06-29|17|42|4|0
2009-06-29|17|42|5|0
2009-06-29|17|42|6|0
2009-06-29|17|42|7|1
2009-06-29|17|42|8|4

Am I on the right path with the self-join?  I also considered adding a
new table to the database that contained all the possible time values
in epoch seconds - I could easily do so with the script that creates
the database and adds the data.  I could then left join this table
with the data table.  But would doing so be substantially faster than
the self-join?

The examples above are for only one port - I need to do the same for
16 ports.  I wrote a perl script to do queries for each second, but it was
taking about 1 minute to do queries for the 16 ports for a 1 second
interval - way too slow to be used to produce results for 24 hour periods.

Thanks for any advice!  I learn a lot from this list.
Leo
-- 
View this message in context: 
http://www.nabble.com/3-million-rows%2C-query-speeds%2C-and-returning-zero-for-rows-that-don%27t-exist-tp24299431p24299431.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] getting offending constraint

[James Gregurich]

thanks.

I tried that, but I still got back "constraint failed" rather than my  
RAISE message. Since you say it should work, I probably did something  
wrong. I'll look at it again.

On Jul 1, 2009, at 3:59 PM, Simon Slavin wrote:

>
> On 1 Jul 2009, at 8:19pm, James Gregurich wrote:
>
>> Would there be a way to identify the offending constraint if
>> "SQLITE_CONSTRAINT" is returned?
>>
>>
>> sqlite3_errmsg is just telling me "constraint failed"...which is of
>> limited usefulness.
>
> Instead of the constraint, you could define a trigger, and use the
> 'RAISE' form to supply your own error message.  Here's an example:
>
> CREATE TRIGGER authors_books_insert BEFORE INSERT ON books
>   FOR EACH ROW BEGIN
>   SELECT RAISE(ROLLBACK, 'Attempt to add a book with an author
> number which is not valid.')
>   WHERE (SELECT id FROM authors WHERE id = new.author) IS NULL;
>   END
>
> You get back exactly the error message you put in.
>
> 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] Changing Table Contents

[Rick Ratchford]

 
Okay, I found what needed to be added to DROP TABLE to make it not produce
the error.

DROP TABLE IF EXISTS Foo

That did the trick.

Still haven't figured out how to get the test result from...

SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo';

If this returns 1 or 0 based on whether there is a table called 'Foo' in the
sqlite_master, where does one check for that 1 or 0?

Thanks!

Rick

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: Wednesday, July 01, 2009 7:06 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Changing Table Contents


DROP TABLE Foo;
-- It's okay to execute this command, even if Foo does not exist
already.

Hello David. Thanks for your reply.

The above "Drop Table" created an error when I tried to run it when no table
existed.


CREATE TABLE Foo ( ... );

Forgive my novice ignorance. Although I have no trouble executing this SQL
without an error, where do I look for the return value?

I'm using a VB wrapper, and so I run this by...

Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name =
'DeltaGrid'"

Thing is, I don't know where to check for the return value. 



Thank you.

Rick


___
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] Changing Table Contents

[Rick Ratchford]

DROP TABLE Foo;
-- It's okay to execute this command, even if Foo does not exist
already.

Hello David. Thanks for your reply.

The above "Drop Table" created an error when I tried to run it when no table
existed.


CREATE TABLE Foo ( ... );

Forgive my novice ignorance. Although I have no trouble executing this SQL
without an error, where do I look for the return value?

I'm using a VB wrapper, and so I run this by...

Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name =
'DeltaGrid'"

Thing is, I don't know where to check for the return value. 



Thank you.

Rick


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

Re: [sqlite] Changing Table Contents

[David Baird]

On Wed, Jul 1, 2009 at 5:29 PM, Rick Ratchford wrote:
> 1. Determine if the table has already been created due to a prior run.
>
> 2. If so, to remove the information currently in that table and replace it
> with new information.
>
> I'm not sure how to determine whether the table already exists.
>
> If it does exist, I suppose I can then use the SQL Delete to delete all the
> records and then write the new stuff. If this is not the way to do it, maybe
> someone can suggest the proper way.

Well, here are a few commands that may give you some ideas, and you
can see what works best for you

Here is how you can use sqlite_master to determine if a table exists:

SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo';
-- returns 1 if table exists, 0 otherwise

If you want to drop your table and then create it, you could do this:

DROP TABLE Foo;
-- It's okay to execute this command, even if Foo does not exist already.

CREATE TABLE Foo ( ... );

Here you can see whether or not a table contains any rows at all:

SELECT count(*) FROM Foo;
-- returns 0 if table is empty

If you want to clean out your table:

DELETE FROM Foo;
-- This will delete all rows from Foo

If you just want to update some already existing data:

UPDATE Foo SET column2 = 33 WHERE column1 = 1;
-- This lets you update (modify) information that already exists
in the table


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

[sqlite] Changing Table Contents

[Rick Ratchford]

Language: VB6
 
 
In my project, I create a Table that holds specific information based on a
User's selection.
 
When the user runs a new selection, my procedure that creates this table is
run again to recreate the table but with new information.
 
However, the problem I have is that since the table name is constant
(DataTable), running the procedure again will simply cause a "Table already
exists" error.
 
What I need to do is to:
 
1. Determine if the table has already been created due to a prior run.
 
2. If so, to remove the information currently in that table and replace it
with new information.
 
I'm not sure how to determine whether the table already exists.
 
If it does exist, I suppose I can then use the SQL Delete to delete all the
records and then write the new stuff. If this is not the way to do it, maybe
someone can suggest the proper way.
 
I've seen some routines where you try to create a RS from the Table in order
to test if it exists. Is this how it is done for SQLite?
 
Thanks.
Rick
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with SQLite Query

[JokBoy]

Igor,

I have tried your query and I don't get any rows returned.  Any ideas why it
wouldn't work?

Regards

Andrew
-- 
View this message in context: 
http://www.nabble.com/Help-with-SQLite-Query-tp24297858p24298529.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] getting offending constraint

[Simon Slavin]

On 1 Jul 2009, at 8:19pm, James Gregurich wrote:

> Would there be a way to identify the offending constraint if
> "SQLITE_CONSTRAINT" is returned?
>
>
>  sqlite3_errmsg is just telling me "constraint failed"...which is of
> limited usefulness.

Instead of the constraint, you could define a trigger, and use the  
'RAISE' form to supply your own error message.  Here's an example:

CREATE TRIGGER authors_books_insert BEFORE INSERT ON books
   FOR EACH ROW BEGIN
   SELECT RAISE(ROLLBACK, 'Attempt to add a book with an author  
number which is not valid.')
   WHERE (SELECT id FROM authors WHERE id = new.author) IS NULL;
   END

You get back exactly the error message you put in.

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

Re: [sqlite] Help with SQLite Query

[Igor Tandetnik]

JokBoy  wrote:
> Basically, I would like to be able to query one table based on
> criteria entered into another table.
>
> I have attached an excel file with a couple of hours of monitoring
> data, the 10 minute data has been interpolated to give minute answers
> (as MOEData Tab in the excel sheet).
>
> This Table of Data would be called MOEData in the database (original I
> know...)
>
> I then would like to have another table (See Criteria Table Tab in
> excel sheet).  This table would provide the criteria to be used.  A
> verbose description of the actual query would be as follows (when
> looking at the first line of the table).
>
> Count all the rows between 0:00 and 1:20 on the 16/06/2009, where the
> MOEData.Hs is greater than 1.3, OR the  (MOEData.Vwind > 20 AND
> (Twind is between 45 and 135, or Twind is between 225 and 315))

select Start_Time, End_Time, count(*) as "Above Criteria"
from Criteria c join MOEData d on (
  d.Date between c.Start_Time and c.End_Time and
  (d.HsSea > c.Hs_crit or
   (d.Vwind > c.Vwind_crit and
(d.Twind between 45 and 135 or d.Twind between 225 and 315)
   )
  )
)
group by c.rowid;

Igor Tandetnik 



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

[sqlite] Help with SQLite Query

[JokBoy]

Basically, I would like to be able to query one table based on criteria
entered into another table.

I have attached an excel file with a couple of hours of monitoring data, the
10 minute data has been interpolated to give minute answers (as MOEData Tab
in the excel sheet).

This Table of Data would be called MOEData in the database (original I
know...)

I then would like to have another table (See Criteria Table Tab in excel
sheet).  This table would provide the criteria to be used.  A verbose
description of the actual query would be as follows (when looking at the
first line of the table).

Count all the rows between 0:00 and 1:20 on the 16/06/2009, where the
MOEData.Hs is greater than 1.3, OR the  (MOEData.Vwind > 20 AND (Twind is
between 45 and 135, or Twind is between 225 and 315))

Based on the criteria in the table, the results of the query would be as per
the results in the Results tab in the excel sheet.

I have also included the above tables as an SQLite database.

Regards

Andrew http://www.nabble.com/file/p24297858/SQL%2BQuery%2BInformation.xls
SQL+Query+Information.xls 
http://www.nabble.com/file/p24297858/TestDatabase.db3 TestDatabase.db3 
-- 
View this message in context: 
http://www.nabble.com/Help-with-SQLite-Query-tp24297858p24297858.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO and Hexadecimal Literals

[Igor Tandetnik]

Ben Atkinson  wrote:
> Sorry for the newbie SQL question.  I'm trying to use the INSERT INTO
> statement with a hexadecimal literal.  I want to accomplish something
> like this:
>
> INSERT INTO TruckDefaultsTable VALUES ( 'AirPressureTime', 0,
> 0xB4);
>
> Does SQL have a hex literal sequence that serves the same role as
> "0x" in C?

No, there's no syntax for integral hexadecimal literals. There is a blob 
literal x'B4', but it doesn't behave like a number (e.g. you can't 
do arithmetic on blobs). Bottom line, the only option is to use plain 
vanilla decimal numbers.

> I could express the value in decimal as 11796480, but that's pretty
> awkward since the actual value I'm putting into the table is a Linux
> timeval structure. It just makes more sense as hex.

How come you need to type these timestamps in by hand? When you work 
with SQLite programmatically, you just use int variables and such - 
there's almost never a need to represent the number as string, whether 
decimal or hex.

Igor Tandetnik



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

Re: [sqlite] getting offending constraint

[James Gregurich]

ah. I have no knowledge of how mailing list programs work. no "poor  
etiquette" was intended.



On Jul 1, 2009, at 1:41 PM, P Kishor wrote:

> On Wed, Jul 1, 2009 at 3:39 PM, James Gregurich  
> wrote:
>>
>> How would I have "hijacked" a thread?  I changed the subject and
>> removed the original text.
>>
> ...
>
> that is exactly how a thread is hijacked... changing the subject is
> not enough. Every message has a unique id that is used by the mail
> programs to keep track of threading.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] INSERT INTO and Hexadecimal Literals

[Clay Baenziger]

Erg, I can almost count. Sorry, the correct way to specify a hex literal 
is in that thread too. If you use x'' you can enter the bits.

Thank you,
Clay

On Wed, 1 Jul 2009, Clay Baenziger wrote:

> Hi Ben,
>   I hit this a few months ago. Hex literals have to contain an even
> number of hex digits. For that discussion, see:
> http://www.nabble.com/Hexadecimal-Inequalities-Failing--td20216982.html
>
>   Thank you,
>   Clay
>
> On Wed, 1 Jul 2009, Ben Atkinson wrote:
>
>>
>> Sorry for the newbie SQL question.  I'm trying to use the INSERT INTO
>> statement with a hexadecimal literal.  I want to accomplish something
>> like this:
>>
>> INSERT INTO TruckDefaultsTable VALUES ( 'AirPressureTime', 0, 0xB4);
>>
>> sqlite chokes on the 0xB4 expression with:
>>   unrecognized token: "0xB4"
>>
>> I could express the value in decimal as 11796480, but that's pretty awkward
>> since the actual value I'm putting into the table is a Linux timeval 
>> structure.
>> It just makes more sense as hex.
>>
>> Does SQL have a hex literal sequence that serves the same role as "0x" in C?
>>
>> Thanks for any help.
>>
>> Ben
>>
>>
>>
>> ___
>> 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] getting offending constraint

[P Kishor]

On Wed, Jul 1, 2009 at 3:39 PM, James Gregurich wrote:
>
> How would I have "hijacked" a thread?  I changed the subject and
> removed the original text.
>
...

that is exactly how a thread is hijacked... changing the subject is
not enough. Every message has a unique id that is used by the mail
programs to keep track of threading.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting offending constraint

[James Gregurich]

How would I have "hijacked" a thread?  I changed the subject and  
removed the original text.





On Jul 1, 2009, at 12:32 PM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> James Gregurich wrote:
>>
>> howdy!
>
> You hijacked someone else's thread by hitting reply, rather than
> starting a new one.  That is very poor netiquette.
>
>> Would there be a way to identify the offending constraint if
>> "SQLITE_CONSTRAINT" is returned?
>>
>>  sqlite3_errmsg is just telling me "constraint failed"...which is of
>> limited usefulness.
>
> http://www.sqlite.org/cvstrac/tktview?tn=1648
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkpLub0ACgkQmOOfHg372QTM5wCeO38HYFTMGi77aHcgtl1Y1xyK
> H3EAoJav+Q+pAq3LzpWnoMugx87ZnmrF
> =JN3m
> -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] INSERT INTO and Hexadecimal Literals

[Clay Baenziger]

Hi Ben,
I hit this a few months ago. Hex literals have to contain an even 
number of hex digits. For that discussion, see: 
http://www.nabble.com/Hexadecimal-Inequalities-Failing--td20216982.html

Thank you,
Clay

On Wed, 1 Jul 2009, Ben Atkinson wrote:

>
> Sorry for the newbie SQL question.  I'm trying to use the INSERT INTO
> statement with a hexadecimal literal.  I want to accomplish something
> like this:
>
> INSERT INTO TruckDefaultsTable VALUES ( 'AirPressureTime', 0, 0xB4);
>
> sqlite chokes on the 0xB4 expression with:
>   unrecognized token: "0xB4"
>
> I could express the value in decimal as 11796480, but that's pretty awkward
> since the actual value I'm putting into the table is a Linux timeval 
> structure.
> It just makes more sense as hex.
>
> Does SQL have a hex literal sequence that serves the same role as "0x" in C?
>
> Thanks for any help.
>
> Ben
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT INTO and Hexadecimal Literals

[Ben Atkinson]

Sorry for the newbie SQL question.  I'm trying to use the INSERT INTO
statement with a hexadecimal literal.  I want to accomplish something
like this:

INSERT INTO TruckDefaultsTable VALUES ( 'AirPressureTime', 0, 0xB4);

sqlite chokes on the 0xB4 expression with:
   unrecognized token: "0xB4"

I could express the value in decimal as 11796480, but that's pretty awkward
since the actual value I'm putting into the table is a Linux timeval structure.
It just makes more sense as hex.

Does SQL have a hex literal sequence that serves the same role as "0x" in C?

Thanks for any help.

Ben


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

Re: [sqlite] getting offending constraint

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

James Gregurich wrote:
> 
> howdy!

You hijacked someone else's thread by hitting reply, rather than
starting a new one.  That is very poor netiquette.

> Would there be a way to identify the offending constraint if  
> "SQLITE_CONSTRAINT" is returned?
> 
>   sqlite3_errmsg is just telling me "constraint failed"...which is of  
> limited usefulness.

http://www.sqlite.org/cvstrac/tktview?tn=1648

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

iEYEARECAAYFAkpLub0ACgkQmOOfHg372QTM5wCeO38HYFTMGi77aHcgtl1Y1xyK
H3EAoJav+Q+pAq3LzpWnoMugx87ZnmrF
=JN3m
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] getting offending constraint

[James Gregurich]

howdy!

Would there be a way to identify the offending constraint if  
"SQLITE_CONSTRAINT" is returned?


  sqlite3_errmsg is just telling me "constraint failed"...which is of  
limited usefulness.

-James

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

[sqlite] Accessing a DB while copying it causes Windows to eat virtual memory

[Stan Bielski]

Hello,

In the course of copying a largish (20 GB) database file while
accessing it via sqlite3, the machine became very unresponsive. I
opened task manager and found that the system was using a huge amount
of virtual memory, causing it to thrash. Per-process memory usage
looked normal and did not add up to anywhere near system-wide VM
usage.

I ran into this issue at a customer site and was able to reproduce it
using a local Windows 2008 installation. I have not installed any
backup software or a virus scanner. Storage is local disk, SQLite
version is 3.3.17.

At first I thought that this was a general Windows problem involving a
process accessing a file that is being copied, but other binaries I
tested do not cause the same behavior that sqlite3 does. I performed
the following experiments to try to diagnose the issue.

Case 1:

* I copy a 20 GB sqlite DB using Windows' own copy utility (e.g. via explorer).
* At any point during the copy, I open the file being copied in sqlite3
* I exit sqlite3.
* During the rest of the copy the OS will consume virtual memory
linear (seemingly identical) to the amount of data copied since the
process opened the file.

I repeated this experiment using a similarly-sized file created from
/dev/zero (i.e. an invalid DB) and the results were the same.

Case 2:

* I copy the sqlite DB using Windows' own copy utility (e.g. via explorer).
* At any point during the copy, I run 'strings' with the file as an argument.
* I exit strings.
* The copy does not result in the OS consuming additional virtual memory.

Case 3:

* I open the DB in sqlite3
* I let sqlite3 idle and do not input any commands.
* I copy a sqlite DB using Windows' own copy utility (e.g. via explorer).
* I continue to let sqlite3 idle and do not input any commands.
* During the rest of the copy the OS will consume virtual memory
linear (seemingly identical) to the amount of data copied since the
process opened the file.

Is there a workaround for this issue? Any assistance or info is appreciated.

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

Re: [sqlite] How can I specify that a column is equal to another?

[Dennis Cote]

Yuzem wrote:
> Is there any way to specify that movies.id  is equal to user.id so I can use
> just id in my query?
> Thanks in advance!
>   
Not with a left join, but with an inner join you can use the USING 
clause or a NATURAL join. See 
http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join for more details.

Note that SQLite does not report an error when you use a table name 
qualifier for a column named in a using clause or paired off in a 
natural join as it should. This may cause compatibility issues with 
other database programs. Using your example, you could use either of the 
following queries.

select title,my_rating
from movies join user using(id)
where id = 'tt0426459';

select title,my_rating
from movies natural join user
where id = 'tt0426459';


According to the SQL standard, these should all produce an error since 
the qualified column doesn't exist in the join's result table.

select title,my_rating
from movies join user using(id)
where movies.id = 'tt0426459';

select title,my_rating
from movies join user using(id)
where user.id = 'tt0426459';

select title,my_rating
from movies natural join user
where movies.id = 'tt0426459';

select title,my_rating
from movies natural join user
where user.id = 'tt0426459';


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

Re: [sqlite] How can I specify that a column is equal to another?

[Pavel Ivanov]

You can do only
where movies.id = 'tt0426459'
or
where user.id = 'tt0426459'

What to choose depends on your needs. And you're wrong that these
variants are identical and movies.id is always equal to user.id
because you're making left join. They will be identical if you will
make inner join. But even in this case I don't understand why you
consider bad or inconvenient explicitly mentioning table name.

Pavel

On Wed, Jul 1, 2009 at 12:24 PM, Yuzem wrote:
>
> I have this:
> select title,my_rating
>        from movies left join  user on movies.id = user.id
>        where id = 'tt0426459'
> The result:
> ambiguous column name: id
>
> I could use:
> select movies.id ids,title,my_rating
>        from movies left join  user on movies.id = user.id
>        where ids = 'tt0426459'
>
> but I don't want to select the id
>
> Another solution:
>        where movies.id = 'tt0426459'
>
> Is there any way to specify that movies.id  is equal to user.id so I can use
> just id in my query?
> Thanks in advance!
> --
> View this message in context: 
> http://www.nabble.com/How-can-I-specify-that-a-column-is-equal-to-another--tp24292794p24292794.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 I specify that a column is equal to another?

[Simon Slavin]

On 1 Jul 2009, at 5:24pm, Yuzem wrote:

> Is there any way to specify that movies.id  is equal to user.id so I  
> can use
> just id in my query?

I think you have already come up with the two best solutions.  You  
could also create a VIEW

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

and specify which 'id' you wanted when defining the view.

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

[sqlite] How can I specify that a column is equal to another?

[Yuzem]

I have this:
select title,my_rating
from movies left join  user on movies.id = user.id
where id = 'tt0426459'
The result:
ambiguous column name: id

I could use:
select movies.id ids,title,my_rating
from movies left join  user on movies.id = user.id
where ids = 'tt0426459'

but I don't want to select the id

Another solution:
where movies.id = 'tt0426459'

Is there any way to specify that movies.id  is equal to user.id so I can use
just id in my query?
Thanks in advance!
-- 
View this message in context: 
http://www.nabble.com/How-can-I-specify-that-a-column-is-equal-to-another--tp24292794p24292794.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Near misses

[Alberto Simões]

Hello

2009/6/26 Alberto Simões :
> I am trying to find words in a dictionary stored in sqlite, and trying
> a near miss approach.
> For that I tried an algorithm to create patterns corresponding to
> Levenshtein distance of 1 (edit distance of 1).
> That means, one adition, one remotion or one substitution.
>
> For that, my script receives a word (say, 'car') and generated all
> possible additions and remotions, and substitutions:
>
> Additions: _car c_ar ca_r car_
> Substitutions: _ar c_r ca_
> remotions: ar cr ca
>
> Then, the script constructs an SQL query:
>
> SELECT DISTINCT(word) FROM dict WHERE word = "ar" OR word = "ca" OR
> word LIKE "_car" OR word LIKE "c_r" OR word = "cr" OR word LIKE "_ar"
> OR word LIKE "ca_r" OR word LIKE "c_ar" OR word LIKE "ca_" OR word
> LIKE "car_";
>
> And this SQL quer works... but not as quickly as I need (specially
> because the speed is proportional to the word size).

My current solution is to make all combinations of words: having a
list of letters, cycle them and substitute the underscore by that
letter.
The resulting list is being searched with   SELECT word FROM dict
WHERE word IN ('worda','wordb',wordc') and so on.
While for big words this list can be big (it gets above 1000 easily)
the query executes very fast.

Hope this might be helpful for somebody.
Cheers
Alberto
-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite3 on On-Time RTOS ...

[Kent Dahl]

ma., 27.04.2009 kl. 14.58 +0200, skrev Kent Dahl:
> We've been giving the SQLite 3.6.10 amalgamation source code for Windows
> a try against RTOS 5.14 and it compiled out of the box. However, we ran
> into some linker and run-time errors. After a fair amount of
> experimenting, we got it up and running, but only towards an in-memory
> database. We still have problems opening existing or creating database
> files.
[...]
> Finally we had something that linked and ran, but only towards in-memory
> database. When we tried to open an existing database (or create a new
> one) using the sqlite3_open_v2 API, it kept returning SQLITE_NOMEM(7).
> When I tried debugging this, I got as far as the sqlite3BtreeFactory
> call, but because of the breakpointing problems I didn't get much
> further.

Unfortunately, work priorities shifted so I didn't have much time to dig
futher into the disk problems. 

In the off chance that someone else might find this useful, I'm
including the patches I had so far when I stopped investigating. These
are towards the 3.6.10 amalagamation source and are not too clean, but
might save someone else trying SQLite3 on On-Time RTOS a little time
getting started...

Please let me (and the list) know if anyone has any further progress.

-- 
Mvh/Regards,

Kent Dahl
Software Developer

Industrial Control Design AS



Phone: +47 93 07 32 30

Breivika Industriveg 63
N-6018 Ålesund
Norway

k...@icd.no

www.icd.no


The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon this information by persons or
entities other than the intended recipient is prohibited. If you
received this in error, please contact the System Manager i...@icd.no and
delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and MinGWSys

[Dennis Cote]

ArbolOne wrote:
> I have downloaded the latest version of SQLite as well as the make file 
> in the ticket #931  
> However, after typing 'make' I get a message that saying ' No rule to 
> make target 'src/sqlite.h.in', how do I solve this problem?
>
>   
Read the recommendations at http://www.sqlite.org/download.html and 
don't use a 4 year old makefile for code that was released last week.

Download this http://www.sqlite.org/sqlite-amalgamation-3_6_16.zip and 
extract the files. You will have a single c file that you can add to 
your sqlite based project, or compile as a static library and link to 
your project.

You can download a precompiled dll library or a precompiled copy of the 
command line sqlite3 program if you need those.

If you are trying to build the command line program yourself, you can 
get the source file shell.c from 
http://www.sqlite.org/sqlite-source-3_6_16.zip. Compile and link shell.c 
and sqlite3.c from the amalgamation together like this (untested):

gcc sqlite3.c shell.c -o sqlite3.exe

HTH
Dennis Cote

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