Re: [sqlite] Nested transactions

[Dan]

On Jul 25, 2009, at 2:26 AM, Pavel Ivanov wrote:

> First of all in case you've mentioned it will be not nested
> transaction but one transaction including all deletes and it will be
> committed when select statement will be finished.

This was true for a long time. But as of version 3.6.5, behaviour has
changed so that transactions may be committed even if the database
connection has ongoing read queries.

Assuming no BEGIN has been issued (sqlite3_get_autocommit() returns 1),
each DELETE statement in the pseudo-code below will cause SQLite to
upgrade from a read to a write lock, delete records from the database
file, then downgrade back to a read lock.

Dan.


>
> As a second note: it's pretty bad idea to change table which is used
> in not yet finished select statement. In SQLite it can lead to
> unpredictable behavior. E.g. rows that you've already deleted can
> still be selected in subsequent fetches or new rows that you've
> inserted would be returned by issued earlier select statement. And
> this can lead effectively to infinite loop.
> So indeed you better use some memory structures for storing results of
> your select first, before changing the table.
>
> Pavel
>
> On Fri, Jul 24, 2009 at 3:12 PM, Guido  
> Ostkamp wrote:
>> Hello,
>>
>> just a short question:
>>
>> With the current sqlite version, is it possible to have nested
>> transactions, e.g.
>>
>> exec select ... from table
>> while (fetch row) {
>> if (criteria)
>> exec delete from t where ...
>> }
>>
>> which means execute some 'select', fetching the results in a loop and
>> inside the loop executing e.g. 'delete' statements on the same  
>> table based
>> on the data returned by the fetch?
>>
>> The 'definitive guide to sqlite' book that I have (which is based  
>> on an
>> now outdated version of sqlite) says it isn't and I have to put the
>> results of the select into another temporary table or local storage  
>> to
>> work around the problem, but I thought I had heard about new  
>> support for
>> nested transactions.
>>
>> Thanks for any insight.
>>
>> Regards
>>
>> Guido
>> ___
>> 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] a system for arbitrarily tagging rows in a table

[Jay A. Kreibich]

On Fri, Jul 24, 2009 at 09:20:29PM -0500, P Kishor scratched on the wall:
> I am trying to develop a "tagging" system, whereby each row in a table
> can be tagged with arbitrary number of tags.

  This smells of a Relational division problem.  If you're dealing with
  tags you might want to have a look at that (Celko has a few good
  articles on it).  Since SQL lacks a native Relational division
  operator, chances are a solution in that direction is going to be
  more complex -- at least for this problem.  But any time I've done
  tags or attributes, sooner or later I find myself needing to do a
  division.  They come in handy any time you say "my data is vertical
  but I need it horizontal."  You might want to read up on them just to
  have that knowledge available.


> TABLE foo (f_id INTEGER PRIMARY KEY, f_name TEXT);
> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
> TABLE foo_tag (f_id INTEGER, t_id INTEGER);


> I have the following solution. Could I do better or differently?

  I'm not sure about "better", but here's different:

sqlite> SELECT foo.f_name, tag.t_name 
   ...> FROM tag AS target 
   ...>   NATURAL JOIN foo_tag AS target_ft
   ...>   NATURAL JOIN foo
   ...>   NATURAL JOIN foo_tag
   ...>   NATURAL JOIN tag
   ...> WHERE target.t_name = 'bar' 
   ...>   AND tag.t_id != target.t_id
   ...> ORDER BY foo.f_name, tag.t_name;

  This basically folds your IN sub-select back into the main query.
  We join "foo" to the tag table in two directions... one to find
  the search target tag id and the other to produce the output.

  "tag AS target" with the first WHERE clause should return one row.
  We join that through "foo_tag AS target_ft" to get a list of foo ids
  that have the search target tag.  We then build the normal output
  list by joining that back through the foo_tag bridge table to the
  tags, and throw out any rows with an output tag id that matches the
  search target tag id.

  Simple!

  Best of all, the target only appears in the query once, and your name
  convention means we can use NATURAL JOINs to keep things clean.  It
  also totally falls apart if you need to search on more than one tag.
  That's where Relational division comes in.

   -j (who had to draw a picture to get it right)

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

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

Re: [sqlite] a system for arbitrarily tagging rows in a table

[Simon Slavin]

On 25 Jul 2009, at 3:20am, P Kishor wrote:

> I am trying to develop a "tagging" system, whereby each row in a table
> can be tagged with arbitrary number of tags.

As an alternative for using a table for tags, consider using a long  
string instead.  The default value for this column would be the  
character (picking one at random) ','.  To add a tag you append the  
tag plus another ',' to it.  So a record tagged as both 'hot' and  
'current bug' would have this value in the 'tags' column:

,hot,current bug,

To search for all rows containing a specific tag, use the 'LIKE'  
operator to find all strings including ','||thisTag||','.  To show all  
the tags apart from that one, use a 'replace' expression to remove it  
from the string or use whatever your programming languages uses for  
'replace'.  To forget all uses of the tag use an UPDATE command with a  
'replace' expression.

This solution does not suit every application, since it's slower for  
searching big tables, but this way of implementing tags allows  
operations like the one you describe to be done as a single simple  
SELECT statement with no JOIN or SELECT arguments.  It does not  
require you to carefully modify a 'tags' table when you delete a  
record.  It removes the requirement for one table and one index, and  
thus makes the overall database smaller.

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

Re: [sqlite] Reporting on summary data

[John Machin]

On 25/07/2009 11:59 AM, David Bicking wrote:
> On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote:

>> An accounting system where the sign of the amount is detached and has to 
>> be obtained from another column is tedious and error-prone; obtaining it 
>> from TWO columns is "interesting"; 

> You must have never dealt with SAP.

What makes you say that?

> The values are all positive, and in
> another column they have H or S to say if it is positive or negative.

Yes, SAP is an example of the type of system to which I referred.

>> a system where the nature of the 
>> transaction or event is not recorded and has to be reverse-engineered 
>> from circumstantial evidence is also "interesting" (especially where the 
>> evidence spans more than one row) -- is this an existing system, or is 
>> it a prototype that you could reconsider?
>>
> 
> The values are always what they are in the amt column, but this report
> is tracking changes for a given date.
> 
> d1 and d2 are starting and ending dates. If you have an id that starts
> on a given date, but has no prior record ending on that date, then it is
> brand new. If one ends on a given date but there is no newer record
> starting, then the item was dropped, and represents a decrease in the
> total. If an item end son one date, and picks up with a new value, then
> it changed. 

Thanks for the explanation; I presumed without any evidence that the 
amounts were transactional rather than current totals or balances ... a 
bit of prejudice carrying over from some systems that I've had to 
struggle with :-)

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

[sqlite] a system for arbitrarily tagging rows in a table

[P Kishor]

I am trying to develop a "tagging" system, whereby each row in a table
can be tagged with arbitrary number of tags.

TABLE foo (f_id INTEGER PRIMARY KEY, f_name TEXT);
TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
TABLE foo_tag (f_id INTEGER, t_id INTEGER);

foo

1, one
2, two
3, three
4, four
5, five

tag

1, good
2, bad
3, ugly
4, bar

foo_tag
--
1, 1 ('one' is tagged with 'good')
1, 2 ('one' is tagged with 'bad')
1, 4 ('one' is tagged with 'bar')
2, 2 ('two' is tagged with 'bad')
2, 4 ('two' is tagged with 'bar')
3, 1 ('three' is tagged with 'good')
3, 3 ('three' is tagged with 'ugly')
3, 4 ('three' is tagged with 'bar')
and so on

I want to find all rows in ‘foo’ that have a tag ‘bar’ and display all
their tags that are NOT ‘bar’, that is, I want to display the
following result

f_name t_name
-- ---
onegood
onebad
twobad
three  good
three  ugly

So, one, two and three are tagged with the tag ‘bar’, but I am showing
all the other tags. Ultimately, I want to display the above results as
shown below, but that is an exercise to be solved in the application
--

good
- one
- three

bad
- one
- two

ugly
- three

I have the following solution. Could I do better or differently?
SELECT
  f.f_name, t.t_name
FROM
  foo f JOIN
  foo_tag ft ON f.f_id = ft.f_id JOIN
  tag t ON ft.t_id = t.t_id
WHERE
  ft.t_id != ‘bar’ AND ft.f_id IN (
SELECT f_id FROM foo_tag WHERE t_id = ‘bar’
  )
ORDER BY t.t_id

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

Re: [sqlite] Reporting on summary data

[David Bicking]

On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote:
> On 25/07/2009 6:17 AM, David Bicking wrote:
> > That works. Thanks!
> 
> It struck me that Pavel's revised query didn't mention the d2 column at 
> all, only d1:
> 

Thanks for the additional info. In real life, I added WHERE d1='X' OR
D2='X' to the inner subqeury. Yes, there are records where neither are
'X' and they are to be ignored.

> >> sum(case when d1='X' then 1 else -1 end) as act_sum,
> >> sum(case when d1='X' then amt else -amt end) as net
> 
> ... backtracking, it seems that you haven't stated anything about the 
> possibility of having 'X' in neither d1 nor d2. You may like to program 
> this along more defensive lines:
> 
> sqlite> create table source (id integer, d1 text, d2 text, amt int);
> sqlite> insert into source values(1, 'X', 'Y', 15);
> sqlite> insert into source values(2, 'X', 'Z',  6);
> sqlite> insert into source values(2, 'A', 'X',  7);
> sqlite> insert into source values(3, 'B', 'X', 12);
> sqlite> insert into source values(4, 'C', 'W', 99);
> sqlite> .headers on
> sqlite> select id,
> ...> case kind
> ...> when 1 then 'NEW'
> ...> when 2 then 'DROP'
> ...> when 3 then 'CHANGE'
> ...> else'WHOOPS'
> ...> end as Action,
> ...> net
> ...> from (
> ...> select id,
> ...> sum((d1='X') + (d2='X') * 2) as kind,
> ...> sum(((d1 = 'X') - (d2 = 'X')) * amt) as net
> ...> from source
> ...> group by id
> ...> )
> ...> order by 2 desc
> ...> ;
> id|Action|net
> 4|WHOOPS|0
> 1|NEW|15
> 3|DROP|-12
> 2|CHANGE|-1
> sqlite> select id,
> ...> case when act_sum = 1 then 'NEW'
> ...> when act_sum = 0 then 'CHANGE'
> ...> else 'DROP'
> ...> end as Action,
> ...> net
> ...> from
> ...> (
> ...> select id,
> ...> sum(case when d1='X' then 1 else -1 end) as act_sum,
> ...> sum(case when d1='X' then amt else -amt end) as net
> ...> from Source
> ...> group by id
> ...> )
> ...> order by 2 desc;
> id|Action|net
> 1|NEW|15
> 3|DROP|-12
> 4|DROP|-99
> 2|CHANGE|-1
> sqlite>
> 
> An accounting system where the sign of the amount is detached and has to 
> be obtained from another column is tedious and error-prone; obtaining it 
> from TWO columns is "interesting"; 

You must have never dealt with SAP. The values are all positive, and in
another column they have H or S to say if it is positive or negative. (H
and S being the initials of Credit and Debit in German. Or maybe those
initials are used because I work for the US division of a German
company. Not entirely sure.)

> a system where the nature of the 
> transaction or event is not recorded and has to be reverse-engineered 
> from circumstantial evidence is also "interesting" (especially where the 
> evidence spans more than one row) -- is this an existing system, or is 
> it a prototype that you could reconsider?
> 

The values are always what they are in the amt column, but this report
is tracking changes for a given date.

d1 and d2 are starting and ending dates. If you have an id that starts
on a given date, but has no prior record ending on that date, then it is
brand new. If one ends on a given date but there is no newer record
starting, then the item was dropped, and represents a decrease in the
total. If an item end son one date, and picks up with a new value, then
it changed. 

The format seems nice and compact. You can get the detail for any given
date by selecting WHERE target>=d1 and targethttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest way to insert lots of data

[Pavel Ivanov]

Try to add to your SQLite test these steps:
3.5) Execute statement BEGIN (you can do it via prepared statement).
5.5) Say every 10 000 records execute COMMIT and then BEGIN again. Or
if you indeed want to measure maximum write speed then skip this step
at all.

And your words that pragma synchronous didn't have any effect at all
show that either you did something wrong or you are working with some
weird OS settings preventing respect of data flushing request...

Pavel

On Fri, Jul 24, 2009 at 5:29 PM, Zachary Turner wrote:
> What kind of performance can I expect to get from sqlite inserting
> many many rows of blob data into a database?   I know this depends on
> many factors, but I'm a little puzzled because I have written some
> tests that allow me to test various ways of inserting data and as far
> as I can tell I've tested almost every combination of pragmas and
> sqlite3_config options that might have an effect on performance, and I
> cannot find a way to insert any faster than about 20MB / second.  On
> this same disk, I wrote a similar program to test the speed of writing
> sequentially to a file and in this case I was able to get around 75 MB
> / second.  Here was what I've done in the two scenarios:
>
> Raw Disk Write Test (75 MB / second)
> ---
> 1) Open a very large input file (a few GB) from physical disk 1
> 2) Open an output file on physical disk 2
> 2) Read from the input file sequentially in 4KB chunks
> 3) For each 4KB chunk, write the chunk to the output file
>
>
> Sqlite Test (20 MB / second)
> --
> 1) (Same as above)
> 2) Create a sqlite database on physical disk 2 with one table that has
> 1 column of type BLOB
> 3) Created a prepared statement "INSERT INTO MyTable (ChunkData) values (?1)"
> 4) Read from the input file sequentially in 4KB chunks
> 5) For each 4KB chunk,use sqlite3_bind_blob with the SQLITE_TRANSIENT
> flag, execute the prepared statement, then reset the prepared
> statement.
>
>
> In addition, I've tried changing pretty much every sqlite option I can
> find, including replacing the sqlite_mem_methods with an extremely
> fast allocator using sqlite3_config(SQLITE_CONFIG_MALLOC, ...),
> disabling memory stat collection with
> sqlite3_config(SQLITE_CONFIG_MEMSTATUS, ...), pragma
> default_cache_size, pragma page_size, and pragma synchronous.
>
> Even pragma synchronous, which I thought would surely increase
> performance if I set it to 0, has no effect at all.  I also tried
> creating the database with SQLITE_OPEN_NOMUTEX and this also had no
> effect on performance.  20MB / second seems awfully slow given that I
> write almost 4x that fast using regular disk i/o.
>
> Am I doing something wrong?  I was hoping to be able to achieve at
> least 40MB / second of throughput.
>
> Regards,
> Zach
> ___
> 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] Reporting on summary data

[John Machin]

On 25/07/2009 6:17 AM, David Bicking wrote:
> That works. Thanks!

It struck me that Pavel's revised query didn't mention the d2 column at 
all, only d1:

>> sum(case when d1='X' then 1 else -1 end) as act_sum,
>> sum(case when d1='X' then amt else -amt end) as net

... backtracking, it seems that you haven't stated anything about the 
possibility of having 'X' in neither d1 nor d2. You may like to program 
this along more defensive lines:

sqlite> create table source (id integer, d1 text, d2 text, amt int);
sqlite> insert into source values(1, 'X', 'Y', 15);
sqlite> insert into source values(2, 'X', 'Z',  6);
sqlite> insert into source values(2, 'A', 'X',  7);
sqlite> insert into source values(3, 'B', 'X', 12);
sqlite> insert into source values(4, 'C', 'W', 99);
sqlite> .headers on
sqlite> select id,
...> case kind
...> when 1 then 'NEW'
...> when 2 then 'DROP'
...> when 3 then 'CHANGE'
...> else'WHOOPS'
...> end as Action,
...> net
...> from (
...> select id,
...> sum((d1='X') + (d2='X') * 2) as kind,
...> sum(((d1 = 'X') - (d2 = 'X')) * amt) as net
...> from source
...> group by id
...> )
...> order by 2 desc
...> ;
id|Action|net
4|WHOOPS|0
1|NEW|15
3|DROP|-12
2|CHANGE|-1
sqlite> select id,
...> case when act_sum = 1 then 'NEW'
...> when act_sum = 0 then 'CHANGE'
...> else 'DROP'
...> end as Action,
...> net
...> from
...> (
...> select id,
...> sum(case when d1='X' then 1 else -1 end) as act_sum,
...> sum(case when d1='X' then amt else -amt end) as net
...> from Source
...> group by id
...> )
...> order by 2 desc;
id|Action|net
1|NEW|15
3|DROP|-12
4|DROP|-99
2|CHANGE|-1
sqlite>

An accounting system where the sign of the amount is detached and has to 
be obtained from another column is tedious and error-prone; obtaining it 
from TWO columns is "interesting"; a system where the nature of the 
transaction or event is not recorded and has to be reverse-engineered 
from circumstantial evidence is also "interesting" (especially where the 
evidence spans more than one row) -- is this an existing system, or is 
it a prototype that you could reconsider?

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

[sqlite] Fastest way to insert lots of data

[Zachary Turner]

What kind of performance can I expect to get from sqlite inserting
many many rows of blob data into a database?   I know this depends on
many factors, but I'm a little puzzled because I have written some
tests that allow me to test various ways of inserting data and as far
as I can tell I've tested almost every combination of pragmas and
sqlite3_config options that might have an effect on performance, and I
cannot find a way to insert any faster than about 20MB / second.  On
this same disk, I wrote a similar program to test the speed of writing
sequentially to a file and in this case I was able to get around 75 MB
/ second.  Here was what I've done in the two scenarios:

Raw Disk Write Test (75 MB / second)
---
1) Open a very large input file (a few GB) from physical disk 1
2) Open an output file on physical disk 2
2) Read from the input file sequentially in 4KB chunks
3) For each 4KB chunk, write the chunk to the output file


Sqlite Test (20 MB / second)
--
1) (Same as above)
2) Create a sqlite database on physical disk 2 with one table that has
1 column of type BLOB
3) Created a prepared statement "INSERT INTO MyTable (ChunkData) values (?1)"
4) Read from the input file sequentially in 4KB chunks
5) For each 4KB chunk,use sqlite3_bind_blob with the SQLITE_TRANSIENT
flag, execute the prepared statement, then reset the prepared
statement.


In addition, I've tried changing pretty much every sqlite option I can
find, including replacing the sqlite_mem_methods with an extremely
fast allocator using sqlite3_config(SQLITE_CONFIG_MALLOC, ...),
disabling memory stat collection with
sqlite3_config(SQLITE_CONFIG_MEMSTATUS, ...), pragma
default_cache_size, pragma page_size, and pragma synchronous.

Even pragma synchronous, which I thought would surely increase
performance if I set it to 0, has no effect at all.  I also tried
creating the database with SQLITE_OPEN_NOMUTEX and this also had no
effect on performance.  20MB / second seems awfully slow given that I
write almost 4x that fast using regular disk i/o.

Am I doing something wrong?  I was hoping to be able to achieve at
least 40MB / second of throughput.

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

Re: [sqlite] Reporting on summary data

[David Bicking]

That works. Thanks!

> From: Pavel Ivanov 

> Oops, sorry! I misunderstood what you
> need. Try this:
> 
> select id,
> case when act_sum = 1 then 'NEW'
> when act_sum = 0 then 'CHANGE'
> else 'DROP'
> end as Action,
> net
> from
> (
> select id,
> sum(case when d1='X' then 1 else -1 end) as act_sum,
> sum(case when d1='X' then amt else -amt end) as net
> from Source
> group by id
> )
> order by 2 desc;
> 
> 
> Pavel
> 

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

Re: [sqlite] Reporting on summary data

[Pavel Ivanov]

Oops, sorry! I misunderstood what you need. Try this:

select id,
case when act_sum = 1 then 'NEW'
when act_sum = 0 then 'CHANGE'
else 'DROP'
end as Action,
net
from
(
select id,
sum(case when d1='X' then 1 else -1 end) as act_sum,
sum(case when d1='X' then amt else -amt end) as net
from Source
group by id
)
order by 2 desc;


Pavel

On Fri, Jul 24, 2009 at 3:44 PM, David Bicking wrote:
>
>
>> From: Pavel Ivanov 
>>
>> Just do the same approach with CASE you've already used:
>>
>> Select ID,
>> CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
>>     WHEN d1 IS NULL AND d2='X' THEN 'DROP'
>>     ELSE 'CHANGE' END AS Action,
>> Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN
>> d2='X' THEN
>> AMT ELSE 0 END) AS Net
>> FROM Source GROUP BY ID ORDER BY 2 DESC;
>>
>> Pavel
>>
>
> Thanks, unfortunately, the ELSE 'CHANGE' never fires as all records match 
> either the first or second WHEN clauses.
>
> Using your query, I got
> 1 | NEW | 15
> 2 | DROP | -1
> 3 | DROP | -12
>
> And it occurs to me that I misstated the problem slightly. The source is 
> actually;
>
> 1 | X | Y | 15
> 2 | X | Z |  6
> 2 | A | X |  7
> 3 | B | X | 12
>
> Where A,B,Y and Z are arbitrary values that aren't = 'X'. Probably Y and Z 
> would be greater than X, and A and B would be less than X, but it isn't 
> guaranteed.
>
> 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] Reporting on summary data

[David Bicking]

> From: Pavel Ivanov 
> 
> Just do the same approach with CASE you've already used:
> 
> Select ID,
> CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
>     WHEN d1 IS NULL AND d2='X' THEN 'DROP'
>     ELSE 'CHANGE' END AS Action,
> Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN
> d2='X' THEN
> AMT ELSE 0 END) AS Net
> FROM Source GROUP BY ID ORDER BY 2 DESC;
> 
> Pavel
> 

Thanks, unfortunately, the ELSE 'CHANGE' never fires as all records match 
either the first or second WHEN clauses.

Using your query, I got
1 | NEW | 15
2 | DROP | -1
3 | DROP | -12

And it occurs to me that I misstated the problem slightly. The source is 
actually;

1 | X | Y | 15
2 | X | Z |  6
2 | A | X |  7
3 | B | X | 12

Where A,B,Y and Z are arbitrary values that aren't = 'X'. Probably Y and Z 
would be greater than X, and A and B would be less than X, but it isn't 
guaranteed.

David

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

Re: [sqlite] Nested transactions

[Pavel Ivanov]

First of all in case you've mentioned it will be not nested
transaction but one transaction including all deletes and it will be
committed when select statement will be finished.

As a second note: it's pretty bad idea to change table which is used
in not yet finished select statement. In SQLite it can lead to
unpredictable behavior. E.g. rows that you've already deleted can
still be selected in subsequent fetches or new rows that you've
inserted would be returned by issued earlier select statement. And
this can lead effectively to infinite loop.
So indeed you better use some memory structures for storing results of
your select first, before changing the table.

Pavel

On Fri, Jul 24, 2009 at 3:12 PM, Guido Ostkamp wrote:
> Hello,
>
> just a short question:
>
> With the current sqlite version, is it possible to have nested
> transactions, e.g.
>
>     exec select ... from table
>     while (fetch row) {
>         if (criteria)
>             exec delete from t where ...
>     }
>
> which means execute some 'select', fetching the results in a loop and
> inside the loop executing e.g. 'delete' statements on the same table based
> on the data returned by the fetch?
>
> The 'definitive guide to sqlite' book that I have (which is based on an
> now outdated version of sqlite) says it isn't and I have to put the
> results of the select into another temporary table or local storage to
> work around the problem, but I thought I had heard about new support for
> nested transactions.
>
> Thanks for any insight.
>
> Regards
>
> Guido
> ___
> 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] Nested transactions

[Guido Ostkamp]

Hello,

just a short question:

With the current sqlite version, is it possible to have nested 
transactions, e.g.

 exec select ... from table
 while (fetch row) {
 if (criteria)
 exec delete from t where ...
 }

which means execute some 'select', fetching the results in a loop and 
inside the loop executing e.g. 'delete' statements on the same table based 
on the data returned by the fetch?

The 'definitive guide to sqlite' book that I have (which is based on an 
now outdated version of sqlite) says it isn't and I have to put the 
results of the select into another temporary table or local storage to 
work around the problem, but I thought I had heard about new support for 
nested transactions.

Thanks for any insight.

Regards

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

Re: [sqlite] Reporting on summary data

[Pavel Ivanov]

> And how can I calculate the value for Action?

Just do the same approach with CASE you've already used:

Select ID,
CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
WHEN d1 IS NULL AND d2='X' THEN 'DROP'
ELSE 'CHANGE' END AS Action,
Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN d2='X' THEN
AMT ELSE 0 END) AS Net
FROM Source GROUP BY ID ORDER BY 2 DESC;

Pavel

On Fri, Jul 24, 2009 at 2:17 PM, David Bicking wrote:
> I am trying to create a report. The source table can have one or two records 
> for each given ID. If there is a record with D1='X' but no record with 
> D2='X', then it is a "NEW" action. If there are both D1 and D2 record, it is 
> a "CHANGE" and the Net amount is the D1 amount less the D2 amount. If there 
> is no D1, then it is a "DROP" and is the negative of the D2 amount.
>
> I can get close, but I have no idea how to calculate the Action words.
>
> Select * from Source
>
> ID | D1 | D2 | AMT
> 1  | X  |    | 15
> 2  | X  |    |  6
> 2  |    |  X |  7
> 3  |    |  X | 12
>
> Select ID, ??? AS Action, Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - 
> Sum(CASE WHEN d2='X' THEN AMT ELSE 0 END) AS Net FROM Source GROUP BY ID 
> ORDER BY 2 DESC;
>
> ID | Action | Net
> 1  | NEW    |  15
> 3  | DROP   | -12
> 2  | CHANGE |  -1
>
> Is the approach I am taking a "good" approach?  And how can I calculate the 
> value for Action?
>
> 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

[sqlite] Reporting on summary data

[David Bicking]

I am trying to create a report. The source table can have one or two records 
for each given ID. If there is a record with D1='X' but no record with D2='X', 
then it is a "NEW" action. If there are both D1 and D2 record, it is a "CHANGE" 
and the Net amount is the D1 amount less the D2 amount. If there is no D1, then 
it is a "DROP" and is the negative of the D2 amount. 

I can get close, but I have no idea how to calculate the Action words.

Select * from Source

ID | D1 | D2 | AMT
1  | X  || 15
2  | X  ||  6
2  ||  X |  7
3  ||  X | 12

Select ID, ??? AS Action, Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE 
WHEN d2='X' THEN AMT ELSE 0 END) AS Net FROM Source GROUP BY ID ORDER BY 2 DESC;

ID | Action | Net
1  | NEW|  15
3  | DROP   | -12
2  | CHANGE |  -1

Is the approach I am taking a "good" approach?  And how can I calculate the 
value for Action?

Thanks,
David

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

Re: [sqlite] SQLite: Porting to another Operating system.

[Doug Currie]

On Jul 24, 2009, at 8:44 AM, D. Richard Hipp wrote:

> SQLite database files are cross-platform.  All you have to do is copy
> the file to the new machine.  There is no separate "external format".
> The same database file format work on all platforms.

Just make sure that if you are moving to a new platform, that the data  
formats match those expected by SQLite. This is especially important  
for platforms with weird floating point formats. For example, on ARM  
platforms there are a couple floating point formats, and the  
SQLITE_MIXED_ENDIAN_64BIT_FLOAT compile switch helps accommodate them.

SQLite provides support to get this right:

** Developers using SQLite on an ARM7 should compile and run their
** application using -DSQLITE_DEBUG=1 at least once.  With DEBUG
** enabled, some asserts below will ensure that the byte order of
** floating point values is correct.

e

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

Re: [sqlite] SQLite: Porting to another Operating system.

[Griggs, Donald]

 Re: "I can't see how you get to that page."

I found the link from page:
http://www.sqlite.org/features.html
And perhaps it's linked from others as well.


Re: "I would expect to reorganise the physical database on a regular
basis..."

Once again, I'd look at the VACUUM command, though you may find you
don't really need to run it very often.
http://www.sqlite.org/lang_vacuum.html

You may also be interested in the ANALYZE command, which can provide
hints to sqlite's query optimizer.
http://www.sqlite.org/lang_analyze.html
Once you're up and running with your database app, running analyze just
once may be enough.

You might want to do some experiments to see if running either of these
frequently has a measurable benefit to your application.


Hope this helps,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: Porting to another Operating system.

[Rich Shepard]

On Fri, 24 Jul 2009, CityDev wrote:

> I'm only familiar with DB2, Access Jet and Focus. In each case I would
> expect to reorganise the physical database on a regular basis - maybe
> daily or weekly. What's the best way of doing that with SQLite?

   Vacuum.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Rich Shepard]

On Fri, 24 Jul 2009, Simon Slavin wrote:

> SQLite has enough fans that people are happy to build binaries for
> major platforms.

   I have a SlackBuild script that I've used since sqlite-3.3.1 on June 15,
2006. It builds a Slackware package (*.tgz) on my standard system (currently
-12.2). In the past I've sent the SQLite-*.tgz package to folks who've asked
for it, and I'd be happy to provide a copy to the application's Web site if
that would help. It uses the amalgamation packages with standard options and
standard Slackware filesystem usage (e.g., the executable is in
/usr/bin/).

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: Porting to another Operating system.

[CityDev]

Thanks Donald. I can't see how you get to that page off the documentation
menu but now I can go direct.

I'm only familiar with DB2, Access Jet and Focus. In each case I would
expect to reorganise the physical database on a regular basis - maybe daily
or weekly. What's the best way of doing that with SQLite?
-- 
View this message in context: 
http://www.nabble.com/SQLite%3A-Porting-to-another-Operating-system.-tp24640206p24648140.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] SQLite: Porting to another Operating system.

[Simon Slavin]

On 24 Jul 2009, at 2:09pm, CityDev wrote:

>  All you have to do is copy
>
> That's handy - I didn't realise that. However I suggest it's good  
> practice
> to dump and reload in these kinds of situations. I don't yet know  
> how SQLite
> works but I suspect a reload will get the physical data into a  
> better shape
> and clear out deleted items etc. Do you know where's there's  
> documentation
> on this?

There's really only one good candidate for something like this:  
dumping a database as a set of SQL commands.  If you want to convert  
'database --> commands' or 'commands --> database', and you don't want  
to write your own software to do it, you can use the sqlite3 command- 
line tool, either in interactive mode or batch mode.  Take a look at  
the '.dump' and '.read' commands in



Of course, an entire database as SQL commands can be a really big file  
but gzip/ZIPping it works wonders because there's a great deal of  
repetition in SQL commands.

But as for your need for such things ... probably not.  Unless you  
routinely create 1000 records then delete 900 of them, you're not  
saving anything by a big export/import process.  What you are doing is  
giving yourself a backup of your database which can be read by a human  
and processed by computers which are not running SQLite.  If you  
suffer a total failure of your technology this can be extremely useful  
and give you many options for restoration.  It's how I keep my SQL  
backups.  But if all goes to plan you'll never use it.

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

Re: [sqlite] Installing SQLite

[Simon Slavin]

On 24 Jul 2009, at 2:51pm, Jay A. Kreibich wrote:

>  As an experienced user of SQLite, I guess I'd rather have the
>  developers working on great new database features, rather than build
>  scripts.

SQLite has enough fans that people are happy to build binaries for  
major platforms.  If the SQLite web site will host those binaries, or  
even just a page with URLs where you can find those binaries, that  
makes 99.9% of users happy.  That includes me: for what I do all I  
want is a '.h' header file and a '.a' library file.

It's the 1% that we're discussing here.  They want to customise their  
copies or build with unusual settings, so they can't use standard  
binaries.  Fortunately, half (?) the people who want to do this know  
how to do it themselves: use 'make', or create a project in their  
favourite IDE, or whatever.

So now we're down to one person in 2000 who needs a special build but  
doesn't know enough to write makefiles, or set all the settings for  
their favourite compiler, or whatever.  And they're really hard to  
cater for because they all have different platforms, compilers, and  
IDEs.  And popular as SQLite is it's not popular enough to have  
experts on each one of these things.

So I think this mailing list does pretty well in dealing with such  
people, considering that we have nobody paid to do it.  It's gonna  
have to be like that for a while.  SQLite is trendy right now: lots of  
cool new portable platforms need a tiny light SQL system.  People will  
get by.

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

Re: [sqlite] SQLite: Porting to another Operating system.

[Griggs, Donald]

Hi, CityDev,

Regarding:
  All you have to do is copy  

That's handy - I didn't realise that. However I suggest it's good
practice to dump and reload in these kinds of situations. I don't yet
know how SQLite works but I suspect a reload will get the physical data
into a better shape and clear out deleted items etc. Do you know where's
there's documentation on this?

--

Dr. Hipp is the originator of sqlite, so it's hard to find advice more
authoritative than his.   For removing deleted items (which would be
reused as needed anyway), you may want to look at the VACUUM command --
it also rebuilds the db structures. 

If you still need an external ascii dump, there is a
command-line-interface utility documented at:
   http://www.sqlite.org/sqlite.html
The ".dump" command gives you sql which can rebuild a database (via the
".read" command). 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An index question

[John Machin]

On 25/07/2009 2:14 AM, Jon Dixon wrote:
> In the description of the "Create Index" statement, it says:
> 
> "Every time the database is opened,
> all CREATE INDEX statements
> are read from the sqlite_master table and used to regenerate
> SQLite's internal representation of the index layout."
> 
> Does this mean that all of the indices are regenerated when the database is 
> opened
[snip]

It doesn't say "regenerate the index". It says "regenerate
SQLite's internal representation of the index layout". It does what it 
says. Consider that building an index can take some considerable time 
... it's not the sort of thing that the average customer wants to wait 
for at the start of the working day or when they turn the phone on to 
dial 112 or whatever -- especially when it's a rather pointless activity.

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

Re: [sqlite] An index question

[D. Richard Hipp]

On Jul 24, 2009, at 12:14 PM, Jon Dixon wrote:

> In the description of the "Create Index" statement, it says:
>
> "Every time the database is opened,
> all CREATE INDEX statements
> are read from the sqlite_master table and used to regenerate
> SQLite's internal representation of the index layout."
>
> Does this mean that all of the indices are regenerated when the  
> database is opened, or is the index stored and this is just  
> confirming the structure?

The latter.  The index content is stored and the layout is confirmed  
each time the database is opened.

D. Richard Hipp
d...@hwaci.com



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

[sqlite] An index question

[Jon Dixon]

In the description of the "Create Index" statement, it says:

"Every time the database is opened,
all CREATE INDEX statements
are read from the sqlite_master table and used to regenerate
SQLite's internal representation of the index layout."

Does this mean that all of the indices are regenerated when the database is 
opened, or is the index stored and this is just confirming the structure? I am 
reading the database from a web application, so generally each query reopens 
the database. If all the indices are being recalculated each time, I would 
probably be better off only creating them for those complex queries where they 
are likely to make a difference. If the indices are calculated once and then 
stored, I am better off adding more of them (especially on more static 
elements) since at this point file size is of lesser importance.

Thanks for the clarification,

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

[sqlite] Ability to convert Access to SQLite

[Scott A Tovey]

sqlite-users-requ...@sqlite.org wrote:
> --
>
> Message: 5
> Date: Wed, 22 Jul 2009 09:34:48 -0700 (PDT)
> From: scabral 
> Subject: [sqlite]  Ability to convert Access to SQLite
> To: sqlite-users@sqlite.org
> Message-ID: <24609886.p...@talk.nabble.com>
> Content-Type: text/plain; charset=us-ascii
>
> Basically, i want to get rid of the Access database because the user's have
> to manually update the tables everyday from the text files that get ftp'd
> over.  I was trying to figure out a solution that would automatically load
> the text files withouth any human intervention and then give the user's the
> ability to view the data through some sort of front-end (similar to Access
> form).
>
>   
When importing data into access(at least 2007 allows this), you can save 
the import steps.
This function allows you to semi-automate the import of the data. So 
unless you want a
timed import function that is all you need do.

If you need the import fully automated, then you can utilize a Macro or 
Visual Basic to
automate the import.

Export steps can be saved as well. Hopefully one day, we will be able to 
export directly
to a SqLite database.

Scott T.

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

Re: [sqlite] Installing SQLite

[Jay A. Kreibich]

On Fri, Jul 24, 2009 at 02:19:36AM +0700, Dan scratched on the wall:
> 
> On Jul 23, 2009, at 10:50 PM, Jay A. Kreibich wrote:
> 
> > On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on  
> > the wall:
> >
> >  The amalgimation works well enough if what you want is mostly
> >  defaults.  The issue is that, while you can change a few of the
> >  #defines for numric defaults, most of the more interesting build
> >  options won't work with the amalgimation.  Only that's it.  As the
> >  website clearly states, there is no other supported option.
> >  no suppored "by the file"
> 
> I think it's just the SQLITE_ENABLE_UPDATE_DELETE_LIMIT feature and
> some of the SQLITE_OMIT_XXX symbols that won't work with the  
> amalgamation
> version. I haven't tried though, so could be wrong there.

  These days, that's true.  When the amalgamation first came out, it
  seemed like you needed to rebuild the amalgamation from the
  distribution sources for nearly any #define that wasn't a simple
  numeric default.  This is no longer true and the vast majority
  of the available build options do appear to work directly on the
  amalgamation.  That's great.

> The problem with the configure script in the full source tree
> distribution is that it is complex and nobody really seems to understand
> it. Hence "unsupported".
 
  As an experienced user of SQLite, I guess I'd rather have the
  developers working on great new database features, rather than build
  scripts.

  Still, I think the lack of a simple build system for the working
  sources presents a signification barrier of entry to anyone that
  can't use the amalgamation-- including those that need/want to
  make changes or patches to the development sources, as well as
  those that prefer a working debugger (many debuggers, including
  Visual Studio, can't deal with a source file as big as sqlite3.c).

   -j


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

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

Re: [sqlite] SQLite: Porting to another Operating system.

[chandan]

D. Richard Hipp wrote:
> On Jul 24, 2009, at 8:37 AM, CityDev wrote:
>
>   
>> I'm new to SQLite. I would assume you would dump the tables to an  
>> external
>> format and then load them into the new database. I can't however see  
>> where
>> the documentation is for this kind of database management function.  
>> Anyone
>> know where I should look, or do you have to download the SQLite3  
>> application
>> to see it?
>> 
>
>
> SQLite database files are cross-platform.  All you have to do is copy  
> the file to the new machine.  There is no separate "external format".   
> The same database file format work on all platforms.
>
> I think the OP was asking what changes need to be made to SQLite in  
> order to get it to compile and run on a platform other than the ones  
> that are supported out of the box (unix, win32, os/2).  Here is a  
> quick summary:
>
> (1) Write a VFS implementation for the target platform.  Use the  
> os_unix.c, os_win.c, and os_os2.c files as a guide, if you like.  See  
> also the documentation on the sqlite3_vfs object.
>
> (2) Compile the amalgamation using -DSQLITE_OS_OTHER.  This disables  
> the built-in OS interface layer.
>
> (3) Before using SQLite in your application, call  
> sqlite3_vfs_register() to install your alternative OS driver.
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
Thanks for the reply. :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: Porting to another Operating system.

[CityDev]

  All you have to do is copy  

That's handy - I didn't realise that. However I suggest it's good practice
to dump and reload in these kinds of situations. I don't yet know how SQLite
works but I suspect a reload will get the physical data into a better shape
and clear out deleted items etc. Do you know where's there's documentation
on this?

-- 
View this message in context: 
http://www.nabble.com/SQLite%3A-Porting-to-another-Operating-system.-tp24640206p24644244.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] SQLite: Porting to another Operating system.

[D. Richard Hipp]

On Jul 24, 2009, at 8:37 AM, CityDev wrote:

>
> I'm new to SQLite. I would assume you would dump the tables to an  
> external
> format and then load them into the new database. I can't however see  
> where
> the documentation is for this kind of database management function.  
> Anyone
> know where I should look, or do you have to download the SQLite3  
> application
> to see it?


SQLite database files are cross-platform.  All you have to do is copy  
the file to the new machine.  There is no separate "external format".   
The same database file format work on all platforms.

I think the OP was asking what changes need to be made to SQLite in  
order to get it to compile and run on a platform other than the ones  
that are supported out of the box (unix, win32, os/2).  Here is a  
quick summary:

(1) Write a VFS implementation for the target platform.  Use the  
os_unix.c, os_win.c, and os_os2.c files as a guide, if you like.  See  
also the documentation on the sqlite3_vfs object.

(2) Compile the amalgamation using -DSQLITE_OS_OTHER.  This disables  
the built-in OS interface layer.

(3) Before using SQLite in your application, call  
sqlite3_vfs_register() to install your alternative OS driver.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLite: Porting to another Operating system.

[CityDev]

I'm new to SQLite. I would assume you would dump the tables to an external
format and then load them into the new database. I can't however see where
the documentation is for this kind of database management function. Anyone
know where I should look, or do you have to download the SQLite3 application
to see it?
-- 
View this message in context: 
http://www.nabble.com/SQLite%3A-Porting-to-another-Operating-system.-tp24640206p24643787.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] SQLite OLEDB provider for Linked Server

[Curtis Whitworth]

Thanks for response, but already tried that...  Their activation does
not work either...  Will not activate for SQLite but does for MySQL...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of CityDev
Sent: Friday, July 24, 2009 3:46 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite OLEDB provider for Linked Server


It looks like you get an activation code from their site, which can
either be
temporary or you can pay for a proper one.
-- 
View this message in context:
http://www.nabble.com/SQLite-OLEDB-provider-for-Linked-Server-tp24635046
p24640083.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


**

The information contained in this e-mail (along with any attachments) is 
intended only for the use of the individual(s) to whom it is addressed.  It is 
confidential and may contain privileged information. If the reader of this 
message is not the intended recipient, you are hereby notified that you should 
not read its contents, and any dissemination, distribution, or copying of this 
communication is strictly prohibited. If you have received it in error, please 
immediately (1) delete this transmission and any attachments and (2) notify 
i...@pilgrimspride.com to advise us of the error.  THIS E-MAIL IS NOT AN OFFER 
OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any 
other law of similar import, absent an express statement to the contrary 
contained in this e-mail, neither this e-mail nor any attachments are an offer 
or acceptance to enter into a contract, and are not intended to bind the 
sender, Pilgrim’s Pride Corporation, or any of its subsidiaries, or any other 
person or entity.

**


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

Re: [sqlite] Installing SQLite

[Gary_Gabriel]

Hi All;

I'm a newbie trying to get up to speed on SQL and SQLite and first- 
thanks for the product, and thanks for the group access and the chance 
to extensively learn. I agree with the following and would volunteer to 
document my learning experience. I used this thread as an opportunity to 
look around and found some interesting compilers to use for SQLite. I 
would take the opportunity to use collective experience and 
recommendations to choose and install the compiler under XP and build 
SQLite. Then to document it for Windows users. I would suggest 
submitting the docs for group comment, make any changes; and then making 
it available for use. I found some tutorials that look as if they make 
significant contributions.

If there is interest in working together to bundle the collective 
experience and document it, then there are general questions to discuss 
that would speed, and benefit the installation as well as supporting the 
documentation. This would start the work. I would follow with my 
questions and continue with any that the group complements.

Thanks again for so actively sharing experience to help-

Gary Gabriel

PS. As a short intro: I am not new to databases having developed, built 
and implemented a DOS relational database for sales and distribution 
management with DataPerfect that successfully drove a business for 20 
years. I am also not new to documentation. I applied the database above 
to Sales Cycle Management/ Internet Messaging and wrote a series of 
articles to develop the application:
Internet Messaging >>> 

  
Sales Cycle Management 


My goal is to replace DP with SQLite and more tightly integrate into 
Internet Messaging. I am in the process of re-desiging and testing using 
SQLite/ Perl. I am grateful for any helpful comments.

Vance E. Neff wrote:
> If there was a zip file that included an open source compiler and linker 
> and a well commented makefile along with SQLite's source code so that 
> anyone (at least under Windows) can generate the version SQLite dll and 
> command shell that they want without having to search for tools, I think 
> more people would be more likely to experiment with some of the non 
> standard features.
>
> Vance
>
> Jay A. Kreibich wrote:
>   
>>   Grrr I didn't meant to send this just yet.  But since I did, I
>>   guess I need to finish it.
>>
>> On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched on the 
>> wall:
>> 
>>> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on the 
>>> wall:
>>>
>>>   
 I think the OP just has the wrong expectations.
 
>>>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>>>   download, I have to agree that the current build and distribution
>>>   state of SQLite is... let's just say "less than ideal."  I've been
>>>   writing a lot of documentation on just this issue, and unless you
>>>   want a perfect vanilla install, there are definitely a lot of hoops
>>>   you have to jump through compared to most open-source projects of
>>>   similar design.
>>>   
>>>   A few versions ago we transitioned from a traditional UNIX style
>>>   project, complete with "configure" script, to having the amalgamation
>>>   be the "standard" distribution.  I've always felt like that
>>>   transition is incomplete, and we've never gotten back to where we
>>>   were before.
>>>
>>>   The amalgamation works well enough if what you want is mostly
>>>   defaults.  The issue is that, while you can change a few of the
>>>   #defines for numeric defaults, most of the more interesting build
>>>   options won't work with the amalgamation.  Only that's it.  As the
>>>   website clearly states, there is no other supported option.  
>>>   
>>   The "by the file" distribution is bad enough, but you're totally out
>>   of luck if you need to go to the tree for some of the really complex
>>   build options.  Of course, the "by the file" distribution is there
>>   and available for download because a lot of people still need it, but
>>   apparently not enough to justify keeping it updated.  That's a bit of 
>>   a contradiction... for a piece of software that prides itself on its
>>   testing systems, the end-users sees a whole lot of "there but not
>>   supported; it might work it might not; you're on your own" stuff.
>>   That's normally a big red flag in my book.  Stuff should be there, be
>>   supported, and be documented, or it shouldn't.  The current situation
>>   is only easily understandable if you've been following SQLite for a few
>>   years.
>>
>>   And the OP is right... the build docs suck.  There are no build docs
>>   for most downloads, just a archive file with source.  No Makefiles, no
>>   nothing. 

[sqlite] SQLite: Porting to another Operating system.

[chandan]

Hi,
I am using SQLite amalgamation package and linking the compiled 
sqlite3.o along with my application.  The application runs on x86 and 
MIPS (BIG Endian) processors  having Linux has its Operating system. 

We now have plans to port the application to a different Operating 
system. Is there any doc that offers guidelines for successfully porting 
SQLite to another operating system.

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

Re: [sqlite] SQLite OLEDB provider for Linked Server

[CityDev]

It looks like you get an activation code from their site, which can either be
temporary or you can pay for a proper one.
-- 
View this message in context: 
http://www.nabble.com/SQLite-OLEDB-provider-for-Linked-Server-tp24635046p24640083.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