Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi Andrew,

Yup, I think my mind is made up...I will use SQLite with WAL with "fairly
frequent" commits to force the update of the database from the WAL file.

How often do you run the commits between the two files? Or do you just use
the default?

Chat later

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrew Davison
Sent: 01 October 2010 12:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

I've used SQLite for exactly this. I have no gripes with it at all. I 
ended up using just one table in one DB as indexing makes things simple 
and fast and I support arbitrary queues with multiple readers. WAL i've 
been trying these last few days and seems trouble-free.

On 30/09/2010 6:11 PM, Lynton Grice wrote:
> Hi there,
>
>
>
> I am a HUGE SQLite fan and have an interesting question I have been
> scratching my head about for a couple days now.
>
>
>
> First off my daily job is very much around "messaging" and I am very
> familiar with message queue products like Websphere MQ and Fiorano MQ.
>
>
>
> When you install Websphere MQ or Fiorano MQ they have a FILE based queue
> underneath by default (typically one file per queue with the messages etc
> stored at different offsets).
>
>
>
> There messaging systems will have "file writer locks" that in essence will
> only allow ONE WRITER per queue at any one time. So any clients sending
> messages will have "to wait in line" until the message can be physically
> written to file.
>
>
>
> Bottomline: We have one writer and multiple readers per queuefine.
>
>
>
> Then I scratch my head and wonder why SQLite is not the PERFECT
persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
>
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea? Am I missing
> something? I personally think it is a wonderful idea and would be hugely
> useful to my daily job
>
>
>
> Any help or advise in this regard would be hugely appreciated ;-)
>
>
>
> Thanks
>
>
>
> Lynton
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

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

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Andrew Davison]

I've used SQLite for exactly this. I have no gripes with it at all. I 
ended up using just one table in one DB as indexing makes things simple 
and fast and I support arbitrary queues with multiple readers. WAL i've 
been trying these last few days and seems trouble-free.

On 30/09/2010 6:11 PM, Lynton Grice wrote:
> Hi there,
>
>
>
> I am a HUGE SQLite fan and have an interesting question I have been
> scratching my head about for a couple days now.
>
>
>
> First off my daily job is very much around "messaging" and I am very
> familiar with message queue products like Websphere MQ and Fiorano MQ.
>
>
>
> When you install Websphere MQ or Fiorano MQ they have a FILE based queue
> underneath by default (typically one file per queue with the messages etc
> stored at different offsets).
>
>
>
> There messaging systems will have "file writer locks" that in essence will
> only allow ONE WRITER per queue at any one time. So any clients sending
> messages will have "to wait in line" until the message can be physically
> written to file.
>
>
>
> Bottomline: We have one writer and multiple readers per queuefine.
>
>
>
> Then I scratch my head and wonder why SQLite is not the PERFECT persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
>
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea? Am I missing
> something? I personally think it is a wonderful idea and would be hugely
> useful to my daily job
>
>
>
> Any help or advise in this regard would be hugely appreciated ;-)
>
>
>
> Thanks
>
>
>
> Lynton
>
>
>
> ___
> 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] Pivot Sequential Data into Dynamic Groups

[Petite Abeille]

On Sep 30, 2010, at 11:44 PM, Jordan Dahlke wrote:

> Is there a good way to do this with Select and Group By statement?

For a given definition of "good" :P

create temporary table range as
select  0 as start, 24 as end union all
select  25 as start, 49 as end union all
select  50 as start, 74 as end union all
select  75 as start, max( value ) as end from measurement;



select  range.start || ' - ' || range.end as time,
sum( coalesce( speed.value, 0 ) ) as speed,
sum( coalesce( direction.value, 0 ) ) as direction,
sum( coalesce( temp.value, 0 ) ) as temp
fromrange

left join   measurement speed
on  speed.variable = 'Speed' 
and speed.time between range.start and range.end

left join   measurement direction
on  direction.variable = 'Direction' 
and direction.time between range.start and range.end

left join   measurement temp
on  temp.variable = 'Temp' 
and temp.time between range.start and range.end

group byrange.start,
range.end

order byrange.start


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

Re: [sqlite] appropriate Uses For SQLite

[Visnik]

Thanks for the info. it has helped clarify some things for me. I think I will
stick with MySQL with this application as it is going to be a networked app. 
If I do one that does not need to be on a network i will dive into SQLlite.

thanks again.



Pavel Ivanov-2 wrote:
> 
> There's no contradiction in those citations. First talks about website
> with some 100K hits/day. Website means application running on some
> dedicated server, clients send HTTP requests to your application and
> application processes it working with locally stored database. Second
> citation is talking about some file-server where you store your
> database, and clients run your application locally which then access
> database via some file sharing network protocol.
> 
>> I have no experience with SQLite but some MySQL experience. Is SQLite a
>> good
>> fit for this type of application/situation?
> 
> Generally speaking - no, because as citation says using SQLite
> database over the network is a bad idea. But depending on your usage
> patterns it might work, although without strong guarantees that
> database will never be corrupted.
> 
> 
> Pavel
> 
> On Thu, Sep 30, 2010 at 4:17 PM, Visnik 
> wrote:
>>
>> I am looking into the best method for implementing a database for a small
>> application that I will most likely be  building it in Adobe Air.  I
>> looked
>> at "Appropriate Uses For SQLite" on the the SQLite.org website and I got
>> some conflicting info.
>>
>> for Example I saw the following:
>> "SQLite usually will work great as the database engine for low to medium
>> traffic websites (which is to say, 99.9% of all websites). The amount of
>> web
>> traffic that SQLite can handle depends, of course, on how heavily the
>> website uses its database. Generally speaking, any site that gets fewer
>> than
>> 100K hits/day should work fine with SQLite."
>>
>> this seemed to conflict a little with this:
>> "If you have many client programs accessing a common database over a
>> network, you should consider using a client/server database engine
>> instead
>> of SQLite."
>>
>> My application will be on a local network and only accessed by 10- 20
>> people
>> at most. I would like to have a non-client/server based solution if
>> possible.  I was looking at MySQL originally, but the "embedded into
>> application part of SQLite got my attention as I would not have to have a
>> Apache, PHP an MySQL solution ready at all times.
>>
>> I have no experience with SQLite but some MySQL experience. Is SQLite a
>> good
>> fit for this type of application/situation?
>>
>> thanks
>> --
>> View this message in context:
>> http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852017.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852925.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] Pivot Sequential Data into Dynamic Groups

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/30/2010 02:44 PM, Jordan Dahlke wrote:
> Is there a good way to do this with Select and Group By statement?

Look in the archives for this mailing list.  There was exactly that
discussion yesterday with a subject of "Vertical -> Horizontal transformation".

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkylBlQACgkQmOOfHg372QQbRgCgka+9KrobMczbj+HutwfrooHg
e6wAoK5QgEVgKc5tSfDYUvi2l+UvlciL
=Pgun
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pivot Sequential Data into Dynamic Groups

[Jordan Dahlke]

I am recording data received over a serial bus into a sqlite database. The
data is received at roughly 40hz, though some variables are in less frequent
multiples of that, 10hz and 4hz.

Variable, Value, Time
Speed, 60, 0.1ms
Direction, 90, 0.4ms
Temp, 22, 0.7ms
Speed, 60, 25ms
Speed, 60.21, 50ms
Speed, 60.55, 75ms
Speed, 60, 100.1ms
Direction, 90, 100.4ms
Speed, 60, 125ms
Speed, 60.21, 150ms
Speed, 60.55, 175ms
Speed, 60, 200.1ms
Direction, 90, 200.4ms
Speed, 60, 225ms
Speed, 60.21, 250ms
Direction, 90, 250.4ms
Temp, 22, 250.7ms

I would like to pivot the table into groups of 40hz data and get

Time, Speed, Direction, Temp
0-25, 60, 90, 22
25-50, 60, null, null
50-75, 60.21, null, null
75-100, 60.55, null, null
100-125, 60, 90, null
125-150, 60, null, null
150-175, 60.21, null, null
175-200, 60.55, null, null
200-225, 60, 90, null
225-250, 60, null, null
250-275, 60.21, 90, 22

Is there a good way to do this with Select and Group By statement?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tips on connecting to remote SQLite servers?

[Petite Abeille]

On Sep 30, 2010, at 11:40 PM, Mike wrote:

> I am new to SQLite and I want to connect to a remote older SQLite server 
> from a modern iPhone app.
> 
> I am using the C API to connect. Can someone tell me the syntax for 
> connecting to a remote SQLite server using the C API?

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

So not out-of-the-box, no

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

[sqlite] Tips on connecting to remote SQLite servers?

[Mike]

I am new to SQLite and I want to connect to a remote older SQLite server 
from a modern iPhone app.

I am using the C API to connect. Can someone tell me the syntax for 
connecting to a remote SQLite server using the C API?

Thanks,

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

Re: [sqlite] appropriate Uses For SQLite

[Pavel Ivanov]

There's no contradiction in those citations. First talks about website
with some 100K hits/day. Website means application running on some
dedicated server, clients send HTTP requests to your application and
application processes it working with locally stored database. Second
citation is talking about some file-server where you store your
database, and clients run your application locally which then access
database via some file sharing network protocol.

> I have no experience with SQLite but some MySQL experience. Is SQLite a good
> fit for this type of application/situation?

Generally speaking - no, because as citation says using SQLite
database over the network is a bad idea. But depending on your usage
patterns it might work, although without strong guarantees that
database will never be corrupted.


Pavel

On Thu, Sep 30, 2010 at 4:17 PM, Visnik  wrote:
>
> I am looking into the best method for implementing a database for a small
> application that I will most likely be  building it in Adobe Air.  I looked
> at "Appropriate Uses For SQLite" on the the SQLite.org website and I got
> some conflicting info.
>
> for Example I saw the following:
> "SQLite usually will work great as the database engine for low to medium
> traffic websites (which is to say, 99.9% of all websites). The amount of web
> traffic that SQLite can handle depends, of course, on how heavily the
> website uses its database. Generally speaking, any site that gets fewer than
> 100K hits/day should work fine with SQLite."
>
> this seemed to conflict a little with this:
> "If you have many client programs accessing a common database over a
> network, you should consider using a client/server database engine instead
> of SQLite."
>
> My application will be on a local network and only accessed by 10- 20 people
> at most. I would like to have a non-client/server based solution if
> possible.  I was looking at MySQL originally, but the "embedded into
> application part of SQLite got my attention as I would not have to have a
> Apache, PHP an MySQL solution ready at all times.
>
> I have no experience with SQLite but some MySQL experience. Is SQLite a good
> fit for this type of application/situation?
>
> thanks
> --
> View this message in context: 
> http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852017.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] appropriate Uses For SQLite

[Visnik]

I am looking into the best method for implementing a database for a small
application that I will most likely be  building it in Adobe Air.  I looked
at "Appropriate Uses For SQLite" on the the SQLite.org website and I got
some conflicting info.

for Example I saw the following:
"SQLite usually will work great as the database engine for low to medium
traffic websites (which is to say, 99.9% of all websites). The amount of web
traffic that SQLite can handle depends, of course, on how heavily the
website uses its database. Generally speaking, any site that gets fewer than
100K hits/day should work fine with SQLite."

this seemed to conflict a little with this:
"If you have many client programs accessing a common database over a
network, you should consider using a client/server database engine instead
of SQLite."

My application will be on a local network and only accessed by 10- 20 people
at most. I would like to have a non-client/server based solution if
possible.  I was looking at MySQL originally, but the "embedded into
application part of SQLite got my attention as I would not have to have a
Apache, PHP an MySQL solution ready at all times. 

I have no experience with SQLite but some MySQL experience. Is SQLite a good
fit for this type of application/situation?

thanks
-- 
View this message in context: 
http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852017.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] data consistency issues in WAL

[Richard Hipp]

On Thu, Sep 30, 2010 at 3:02 PM, Erik Fears  wrote:

> Each thread has its own handle (implemented using boost's thread local
> storage).
>
> I had a bug a few days ago I fixed where I was leaking sqlite3_stmt objects
> in the reader thread. If I don't finalize a sqlite3_stmt, would it leave a
> transaction uncommitted?
>

It could.  The answer depends on the details of the SQL statement.  But if
you were leaving statements unfinalized and you were getting behavior
consistent with holding a transaction open, I'm guessing that was your
problem.


>
> --erik
>
> On Wed, Sep 29, 2010 at 2:35 PM, Simon Slavin 
> wrote:
>
> >
> > On 29 Sep 2010, at 8:20pm, Erik Fears wrote:
> >
> > > -C++ with 3 threads. One of them a write thread, one a main reader
> > thread,
> > > and one a diagnostic reader thread
> >
> > Does each thread open the database independently, or are you passing the
> > same handle from one to another ?
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] data consistency issues in WAL

[Erik Fears]

Each thread has its own handle (implemented using boost's thread local
storage).

I had a bug a few days ago I fixed where I was leaking sqlite3_stmt objects
in the reader thread. If I don't finalize a sqlite3_stmt, would it leave a
transaction uncommitted?

--erik

On Wed, Sep 29, 2010 at 2:35 PM, Simon Slavin  wrote:

>
> On 29 Sep 2010, at 8:20pm, Erik Fears wrote:
>
> > -C++ with 3 threads. One of them a write thread, one a main reader
> thread,
> > and one a diagnostic reader thread
>
> Does each thread open the database independently, or are you passing the
> same handle from one to another ?
>
> 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] how to know the primary key of a table?

[TP]

Jay A. Kreibich wrote:

>   The sixth column of the output of "PRAGMA table_info(  )".
>   Each column of  will return one row.  The last column of the
>   output will have a value of 1 (true) if that row's column is part of
>   the PK, or 0 (false) if that row's column is not part of the PK.
> 
>   Remember that a PK might consist of more than one column.

Thanks a lot.
I didn't see it in table_info(  )!

Cheers,

Julien

-- 
python -c "print ''.join([chr(154 - ord(c)) for c in '*9(9&(18%.\
9&1+,\'Z4(55l4('])"

"When a distinguished but elderly scientist states that something is
possible, he is almost certainly right. When he states that something is
impossible, he is very probably wrong." (first law of AC Clarke)

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

Re: [sqlite] how to know the primary key of a table?

[Jay A. Kreibich]

On Thu, Sep 30, 2010 at 05:47:49PM +0200, TP scratched on the wall:
> Hi everybody,
> 
> Apart from parsing the SQL definition of a table obtained with 
> sqlite_master, is there any means to obtain directly the primary key of a 
> table? I have not found any PRAGMA allowing that.

  The sixth column of the output of "PRAGMA table_info(  )".
  Each column of  will return one row.  The last column of the
  output will have a value of 1 (true) if that row's column is part of
  the PK, or 0 (false) if that row's column is not part of the PK.

  Remember that a PK might consist of more than one column.


  Yes, I know, the docs 
  fail to mention the PK column.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Disambiguation of Latin accent characters for FTS3

[Sam Roberts]

On Thu, Sep 30, 2010 at 8:36 AM, Travis Orr  wrote:
> I know it is possible but can't figure out what needs to be done to be able 
> to make FTS3 see E as being equal to É. And other similar cases.

Despite the orthographic similarity, those sounds are as different to
French speakers as "a" and "e" are to English speakers.

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

Re: [sqlite] Disambiguation of Latin accent characters for FTS3

[Dami Laurent (PJ)]

Hi Travis,

You need to define a "tokenizer" to be used by FTS3; something somehow similar 
to user-defined collating sequences.
See  http://www.sqlite.org/fts3.html#section_5_1 

The ICU library has language-specific library functions for  ignoring accents 
while tokenizing.
 
The Perl binding for SQLite has a general-purpose "unaccent" tokenizer, see
http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#FULLTEXT_SEARCH 
and
http://search.cpan.org/~dami/Search-Tokenizer-1.00/lib/Search/Tokenizer.pm 

Or you can write your own tokenizer in C ...

Best regards,
Laurent Dami


>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] De la part de Travis Orr
>Envoyé : jeudi, 30. septembre 2010 17:36
>À : sqlite-users@sqlite.org
>Objet : [sqlite] Disambiguation of Latin accent characters for FTS3
>
>I know it is possible but can't figure out what needs to be done to be
>able to make FTS3 see E as being equal to É. And other similar cases.
>
>
>
>I have a custom collation sequence that does this disambiguation for
>sorting query results, but it doesn't appear to be functioning when
>performing FTS3 queries.
>
>
>
>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] how to know the primary key of a table?

[Simon Slavin]

On 30 Sep 2010, at 4:47pm, TP wrote:

> Apart from parsing the SQL definition of a table obtained with 
> sqlite_master, is there any means to obtain directly the primary key of a 
> table? I have not found any PRAGMA allowing that.

If your primary key is a unique autoincrement integer, then (bit of handwaving) 
SQLite will hand it to you when you ask for the column called '_rowid_'.  So 
even if you don't know what it's called, you can always get the values used.

If you're willing to do some analysis work you can work out what the primary 
key is by looking at the table definition.  And you can find that by doing a 
SELECT on SQLITE_MASTER:



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

[sqlite] how to know the primary key of a table?

[TP]

Hi everybody,

Apart from parsing the SQL definition of a table obtained with 
sqlite_master, is there any means to obtain directly the primary key of a 
table? I have not found any PRAGMA allowing that.

Thanks in advance,

Julien
-- 
python -c "print ''.join([chr(154 - ord(c)) for c in '*9(9&(18%.\
9&1+,\'Z4(55l4('])"

"When a distinguished but elderly scientist states that something is
possible, he is almost certainly right. When he states that something is
impossible, he is very probably wrong." (first law of AC Clarke)

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

[sqlite] Disambiguation of Latin accent characters for FTS3

[Travis Orr]

I know it is possible but can't figure out what needs to be done to be able to 
make FTS3 see E as being equal to É. And other similar cases.

 

I have a custom collation sequence that does this disambiguation for sorting 
query results, but it doesn't appear to be functioning when performing FTS3 
queries.

 

Thanks

 

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

Re: [sqlite] Referential integrity based on a view.

[Dan Kennedy]

On Sep 30, 2010, at 3:30 PM, Muthuveerappan Alagappan wrote:

> Hi
>
>   I am a newbie to sqlite, I am having difficulty in trying to 
> build  
> referential integrity based on a view.
>   
>   sqlite allows me to create referential integrity based on a 
> view  
> ( vu_cars ), however it doesn't allow me to insert any records into  
> the table car_properties.
>
>   I am using the sqlite version 3.7.2
>
>   I have explained below with an example:
>
>   Desired Result:
>   Green colored insert statements should be allowed
>   Red colored insert statement should throw an error "Foreign Key 
>  
> mismatch"

Any inserts on table "car_properties" should throw the
"foreign key mismatch" error. "foreign key mismatch" indicates
a problem with the schema - in this case that the parent table
of an FK constraint is actually a view, not a table.

SQLite sometimes allows you to create invalid FK constraints
like this one. Then throws the error when you try to execute
a statement that requires testing the FK constraint. More
details here:

   http://www.sqlite.org/foreignkeys.html#fk_indexes

Dan.

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

Re: [sqlite] [C-API] Query returns only 0

[Martin.Engelschalk]

  Hello Gerald,

i think you should tell us more about what you are trying to do and add 
some C code.
what do you mead by "return value"? What functions do you call? Do you 
know that you have to get the selected value by calling a function like 
sqlite3_column_text ?

Martin

Am 30.09.2010 11:41, schrieb Legen Där:
> Hello Gyus,
>
>
>
> ive got problems with my qeuries. After loading a table, i create a statement 
> for every column to prepare the querys. A sample for such a prepared query 
> is..
>
>
>
> SELECT "F_SIZE_M0200_m0_VALUE" FROM fileInfos WHERE FILEINFO_FULLNAME = ?1
>
>
>
>   ...for that query i bind TEXT values like... "C:\Dokumente und 
> Einstellungen\gkaeding\Eigene Dateien\Bilder\Color1000\0.jpg". Unfortunately 
> the returned values are only 0. If i execute this query with the SQLite 
> Database Browser it works. :|
>
>
>
> Any suggestions?
>
>
>
> Thanks in advance!
>
>
>
> Gerald
>   
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi Drake,

Thanks for your comments below, I really appreciate your insight on this ;-)

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Drake Wilson
Sent: 30 September 2010 12:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

Quoth Lynton Grice , on 2010-09-30 12:05:18
+0200:
> BTW: What is WAL 

WAL mode uses a write-ahead log instead of a rollback journal, which
can help reduce write activity but requires the use of shared memory
to keep things consistent between database handles.  If I were doing
queues in SQLite I would use WAL mode if possible, based on what I
read at http://sqlite.org/wal.html.

> Also what is the "worst that can happen" with regard
> to:
> 
> "...rollback journal activity multiplying the number of synchronized
writes.
> "

The rollback journal essentially implies that each transaction gets
synced twice: once to keep the old data in the rollback journal, and
once to write the new data.  o/` Double the fsyncs, double your dues;
it's a statement per transaction with SQLite queues. o/`

Problems I can imagine with a plainer ring-buffer-like approach would
be along the lines of unclear semantics on the platform side regarding
which writes are stable with regard to others, such as whether a crash
in the middle of an append is able to corrupt data from beforehand.
ISTR SQLite doing fairly extensive work to overcome these kinds of
limitations in some cases.

> Thanks again
> 
> Lynton

   ---> Drake Wilson
___
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] Referential integrity based on a view.

[Muthuveerappan Alagappan]

Thanks a lot!!

Is there any proposal to build this feature (referential integrity on a view)?



On Sep 30, 2010, at 19:58, "Igor Tandetnik"  wrote:

> Muthuveerappan Alagappan  wrote:
>> sqlite allows me to create referential integrity based on a view ( vu_cars )
> 
> This looks like a bug. I'm pretty sure referencing a view in a foreigh key is 
> not supposed to work.
> -- 
> 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] Referential integrity based on a view.

[Igor Tandetnik]

Muthuveerappan Alagappan  wrote:
> sqlite allows me to create referential integrity based on a view ( vu_cars )

This looks like a bug. I'm pretty sure referencing a view in a foreigh key is 
not supposed to work.
-- 
Igor Tandetnik

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

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Drake Wilson]

Quoth Lynton Grice , on 2010-09-30 12:05:18 +0200:
> BTW: What is WAL 

WAL mode uses a write-ahead log instead of a rollback journal, which
can help reduce write activity but requires the use of shared memory
to keep things consistent between database handles.  If I were doing
queues in SQLite I would use WAL mode if possible, based on what I
read at http://sqlite.org/wal.html.

> Also what is the "worst that can happen" with regard
> to:
> 
> "...rollback journal activity multiplying the number of synchronized writes.
> "

The rollback journal essentially implies that each transaction gets
synced twice: once to keep the old data in the rollback journal, and
once to write the new data.  o/` Double the fsyncs, double your dues;
it's a statement per transaction with SQLite queues. o/`

Problems I can imagine with a plainer ring-buffer-like approach would
be along the lines of unclear semantics on the platform side regarding
which writes are stable with regard to others, such as whether a crash
in the middle of an append is able to corrupt data from beforehand.
ISTR SQLite doing fairly extensive work to overcome these kinds of
limitations in some cases.

> Thanks again
> 
> Lynton

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

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi Drake,

Thanks for your comments, much appreciated.

I will look into your concerns below, thanks. To me it seems like a perfect
match, one could even break it up into "one .db file per message
queue"...very nice and easy to use.

I already have a message queue implementation in Python that uses SQLite but
now I am wanting to re-write the "python API" in pure C. 

BTW: What is WAL mode? Also what is the "worst that can happen" with regard
to:

"...rollback journal activity multiplying the number of synchronized writes.
"

Thanks again

Lynton


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Drake Wilson
Sent: 30 September 2010 11:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

Quoth Lynton Grice , on 2010-09-30 10:11:54
+0200:
> Bottomline: We have one writer and multiple readers per queuefine.
> 
> Then I scratch my head and wonder why SQLite is not the PERFECT
persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea?

I don't think it would be a bad idea per se, but I'm not sure it would
gain you much over other approaches.  It would certainly be simple!

If you're going for high performance, I would be a little wary of
rollback journal activity multiplying the number of synchronized
writes.  WAL mode would help with that, but it would increase the
underlying complexity WRT filesystem and shared memory accesses.

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

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

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Drake Wilson]

Quoth Lynton Grice , on 2010-09-30 10:11:54 +0200:
> Bottomline: We have one writer and multiple readers per queuefine.
> 
> Then I scratch my head and wonder why SQLite is not the PERFECT persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea?

I don't think it would be a bad idea per se, but I'm not sure it would
gain you much over other approaches.  It would certainly be simple!

If you're going for high performance, I would be a little wary of
rollback journal activity multiplying the number of synchronized
writes.  WAL mode would help with that, but it would increase the
underlying complexity WRT filesystem and shared memory accesses.

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

[sqlite] [C-API] Query returns only 0

[Legen Där]

Hello Gyus,

 

ive got problems with my qeuries. After loading a table, i create a statement 
for every column to prepare the querys. A sample for such a prepared query is..

 

SELECT "F_SIZE_M0200_m0_VALUE" FROM fileInfos WHERE FILEINFO_FULLNAME = ?1

 

 ...for that query i bind TEXT values like... "C:\Dokumente und 
Einstellungen\gkaeding\Eigene Dateien\Bilder\Color1000\0.jpg". Unfortunately 
the returned values are only 0. If i execute this query with the SQLite 
Database Browser it works. :|

 

Any suggestions?

 

Thanks in advance!

 

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

[sqlite] Referential integrity based on a view.

[Muthuveerappan Alagappan]

Hi 

I am a newbie to sqlite, I am having difficulty in trying to 
build referential integrity based on a view.

sqlite allows me to create referential integrity based on a 
view ( vu_cars ), however it doesn't allow me to insert any records into the 
table car_properties.

I am using the sqlite version 3.7.2

I have explained below with an example:

Desired Result:
Green colored insert statements should be allowed
Red colored insert statement should throw an error "Foreign Key 
mismatch"

Actual result:
Green colored and red colored statements are both not allowed.

Question:
Let me know if I am missing something or making a blunder. 
Thanks.


I have a table called "vehicles" which is the master table.

CREATE TABLE vehicles(
   type  TEXT,
   model_number  INTEGER,
   price DOUBLE,
   PRIMARY KEY(type, model_number)
);

INSERT INTO vehicles(type, model_number, price) VALUES ("CAR", 1, 100);
INSERT INTO vehicles(type, model_number, price) VALUES ("BIKE", 1, 50);
INSERT INTO vehicles(type, model_number, price) VALUES ("CAR", 5, 200);
INSERT INTO vehicles(type, model_number, price) VALUES ("BIKE", 7, 50);


I have a view called "vu_cars" which lists only the CAR from the table 
"vehicles".


CREATE VIEW vu_cars
AS
  SELECT *
  FROM vehicles
  WHERE 
  type = "CAR";

I have a table called "car_properties" which contains the car 
properties and has referential integrity based on the view "vu_cars"


CREATE TABLE car_properties(
 model_number  INTEGER,
 number_of_doors   INTEGER,
 PRIMARY KEY(model_number),
 FOREIGN KEY (model_number) REFERENCES 
vu_cars(model_number)
   );

--Below should be inserted successfully:
INSERT INTO car_properties(model_number, number_of_doors) VALUES (1, 4);
INSERT INTO car_properties(model_number, number_of_doors) VALUES (5, 2);

--This should throw an error:
INSERT INTO car_properties(model_number, number_of_doors) VALUES (7, 2);

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

[sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi there,

 

I am a HUGE SQLite fan and have an interesting question I have been
scratching my head about for a couple days now.

 

First off my daily job is very much around "messaging" and I am very
familiar with message queue products like Websphere MQ and Fiorano MQ.

 

When you install Websphere MQ or Fiorano MQ they have a FILE based queue
underneath by default (typically one file per queue with the messages etc
stored at different offsets).

 

There messaging systems will have "file writer locks" that in essence will
only allow ONE WRITER per queue at any one time. So any clients sending
messages will have "to wait in line" until the message can be physically
written to file.

 

Bottomline: We have one writer and multiple readers per queuefine.

 

Then I scratch my head and wonder why SQLite is not the PERFECT persistence
layer for building an awesome "file based queue" on? It is lightening fast
by default, and also has the same "locking issues" described above. We are
talking milliseconds anywaybetween writes

 

Bottomline: Is there any reason why anyone would think someone like me
coding a solid message queue using SQLite is a bad idea? Am I missing
something? I personally think it is a wonderful idea and would be hugely
useful to my daily job

 

Any help or advise in this regard would be hugely appreciated ;-)

 

Thanks

 

Lynton

 

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