Re: [sqlite] C Extensions to SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/04/2010 03:35 PM, Barton Torbert wrote:
> We are thinkng about writing C Extensions to SQLite to do this.  But I was 
> wondering if there are a set already written that could be used?

Not to my knowledge as every programming language already includes
formatting.  SQLite is a library within your process so there is no need
to use SQLite C extension functions - just use whatever language the
process is written in.

The only time you'd need to use C extensions is if SQLite itself needs
to be aware of your formatting such as for filtering or sorting which
mainly means a collation.  However it is usually better to store an
additional column with the formatted data rather than have functions
repeatedly called whenever SQLite needs to access the row.

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

iEYEARECAAYFAkzTWWMACgkQmOOfHg372QSOiQCgnMeSSObhijsTP3Ra5IXw1QyW
YZAAn1Qs9QqueHG5k6b4g/+Mf8Fv9Ho+
=cEtz
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C Extensions to SQLite

[Simon Slavin]

On 4 Nov 2010, at 10:35pm, Barton Torbert wrote:

> My company is using SQLite.  We need to do some formatting to the data before 
> posting it to a webpage. What we want to do are simplier tasks such as zero 
> padding, limiting placess to the right of the decimal etc.

Can you tell us why you feel these functions should be part of SQLite instead 
of just using the functions already in the language you're using to call SQLite 
?  Presumably you're using PHP or Python to retrieve your data from the 
database and both those languages have excellent padding functions.

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

Re: [sqlite] Formatting (left aligned) Integers

[BareFeetWare]

Hi Joshua,

In reply to:

> The real issue I'm trying to solve is a sorting one. I run some SQL which 
> takes some integers and creates a column for sorting with data like 
> following:
> 1.1.2.3
> 1.2
> 1.12.4

Why not do something like:

select Column1 || '.' || Column2 || '.' || Column3 || '.' || Column4
from MyTable
order by Column1, Column2, Column3, Column4
;

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

[sqlite] C Extensions to SQLite

[Barton Torbert]

My company is using SQLite.  We need to do some formatting to the data before 
posting it to a webpage.  What we want to do are simplier tasks such as zero 
padding, limiting placess to the right of the decimal etc.
 
We are thinkng about writing C Extensions to SQLite to do this.  But I was 
wondering if there are a set already written that could be used?
 
Bart
btorb...@fbdover.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Formatting (left aligned) Integers

[Joshua Grauman]

After more Googling, I found this tidbit which simulates lpad for sqlite:

substr('00' || mycolumn, -10, 10)

Thanks!

Josh

> Hello all,
>
> The simple question is, is there any way in SQLite to format (left align)
> integers? So for example I want to print 1 as 1, 23 as 00023, 102 as
> 00102, etc.
>
> The real issue I'm trying to solve is a sorting one. I run some SQL which
> takes some integers and creates a column for sorting with data like
> following:
> 1.1.2.3
> 1.2
> 1.12.4
>
> It gets sorted as follows, (whereas I want it sorted as above):
> 1.1.2.3
> 1.12.4
> 1.2
>
> Hence if I could left align the printing of the integers it would sort
> correctly, but if there is another way to get the sorting I want, I could
> use something else...
>
> Thanks!
>
> Josh
> ___
> 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] Formatting (left aligned) Integers

[Joshua Grauman]

Hello all,

The simple question is, is there any way in SQLite to format (left align) 
integers? So for example I want to print 1 as 1, 23 as 00023, 102 as 
00102, etc.

The real issue I'm trying to solve is a sorting one. I run some SQL which 
takes some integers and creates a column for sorting with data like 
following:
1.1.2.3
1.2
1.12.4

It gets sorted as follows, (whereas I want it sorted as above):
1.1.2.3
1.12.4
1.2

Hence if I could left align the printing of the integers it would sort 
correctly, but if there is another way to get the sorting I want, I could 
use something else...

Thanks!

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

Re: [sqlite] EXTERNAL:Re: R*Tree module and double precision?

[Black, Michael (IS)]

I had an application which had 5 cm meter accuracy and needed to get points 
with 1 meter for example.
 
I ended up writing my own search method.  I'm sure others are trying to do 
similar.
 
The patch I submitted doesn't change the original code at all unless you enable 
the RTREE_DOUBLE.  So it should be perfectly safe to apply.  Could probably be 
done better but it's isolated right now.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Thu 11/4/2010 10:49 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?



On Thu, Nov 4, 2010 at 11:27 AM, Ben Harper  wrote:

> Are you sure that double precision in the RTree is necessary for your
> purposes?
>

To put this another way, single-precision RTree is sufficient to locate any
point on the surface of the earth to within less than 2.5 meters.  I'm
curious to know what real-world application needs more precision than that?

Remember that R-Tree is intended as a approximation filter to limit the
search space for a query.  It does not normally yield the definitive
answer.  Instead, R-Tree returns a superset of the answer which is further
refined by lower-level constraints.  Within 2.5 meters over the entire
surface of the earth is not a close enough approximation?


>
> Here is a table of floating point precision at various values:
>
> The 'flip' values are the 'org' values with the lowest bit flipped.
>
> org flip (delta)
>0.01 0.01 ( 0.00) (lowest bit is 10 ^ -9.030900)
>0.10 0.10 ( 0.00) (lowest bit is 10 ^ -8.127810)
>1.00 1.00 ( 0.00) (lowest bit is 10 ^ -7.224720)
>   10.0010.00 ( 0.01) (lowest bit is 10 ^ -6.020600)
>  100.00   100.00 ( 0.08) (lowest bit is 10 ^ -5.117510)
> 1000.00  1000.00 ( 0.61) (lowest bit is 10 ^ -4.214420)
>1.00 1.00 ( 0.000977) (lowest bit is 10 ^ -3.010300)
>9.99 9.98 ( 0.007813) (lowest bit is 10 ^ -2.107210)
>   99.9499.88 ( 0.062500) (lowest bit is 10 ^ -1.204120)
>  999.00   998.00 ( 1.00) (lowest bit is 10 ^ 0.00)
> 9992.00  9984.00 ( 8.00) (lowest bit is 10 ^ 0.903090)
>99936.00 99872.00 (64.00) (lowest bit is 10 ^ 1.806180)
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: 04 November 2010 02:51 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] R*Tree module and double precision?
>
> I'll send the patched C file to your email address.  I think the 1M patched
> version is a bit much for the email list.
>
> Normally you would just use the "patch" utility to apply the patch to your
> file.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of
> jsarc...@nanotronicsimaging.com
> Sent: Wed 11/3/2010 10:22 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?
>
>
>
> >From: "Black, Michael (IS)"  I made a patch
> >which seems to do this for you...
> >Use this switch to enable double precision on RTREE /DRTREE_DOUBLE
>
> I am interested in this as well.  Will this patch be included in a future
> version of SQLite?  Or, if not, how best to make use of this patch if I
> normally use the amalgamation from C++.
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615
> ___
> 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


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

Re: [sqlite] R*Tree module and double precision?

[Richard Hipp]

On Thu, Nov 4, 2010 at 11:27 AM, Ben Harper  wrote:

> Are you sure that double precision in the RTree is necessary for your
> purposes?
>

To put this another way, single-precision RTree is sufficient to locate any
point on the surface of the earth to within less than 2.5 meters.  I'm
curious to know what real-world application needs more precision than that?

Remember that R-Tree is intended as a approximation filter to limit the
search space for a query.  It does not normally yield the definitive
answer.  Instead, R-Tree returns a superset of the answer which is further
refined by lower-level constraints.  Within 2.5 meters over the entire
surface of the earth is not a close enough approximation?


>
> Here is a table of floating point precision at various values:
>
> The 'flip' values are the 'org' values with the lowest bit flipped.
>
> org flip (delta)
>0.01 0.01 ( 0.00) (lowest bit is 10 ^ -9.030900)
>0.10 0.10 ( 0.00) (lowest bit is 10 ^ -8.127810)
>1.00 1.00 ( 0.00) (lowest bit is 10 ^ -7.224720)
>   10.0010.00 ( 0.01) (lowest bit is 10 ^ -6.020600)
>  100.00   100.00 ( 0.08) (lowest bit is 10 ^ -5.117510)
> 1000.00  1000.00 ( 0.61) (lowest bit is 10 ^ -4.214420)
>1.00 1.00 ( 0.000977) (lowest bit is 10 ^ -3.010300)
>9.99 9.98 ( 0.007813) (lowest bit is 10 ^ -2.107210)
>   99.9499.88 ( 0.062500) (lowest bit is 10 ^ -1.204120)
>  999.00   998.00 ( 1.00) (lowest bit is 10 ^ 0.00)
> 9992.00  9984.00 ( 8.00) (lowest bit is 10 ^ 0.903090)
>99936.00 99872.00 (64.00) (lowest bit is 10 ^ 1.806180)
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: 04 November 2010 02:51 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] R*Tree module and double precision?
>
> I'll send the patched C file to your email address.  I think the 1M patched
> version is a bit much for the email list.
>
> Normally you would just use the "patch" utility to apply the patch to your
> file.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of
> jsarc...@nanotronicsimaging.com
> Sent: Wed 11/3/2010 10:22 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?
>
>
>
> >From: "Black, Michael (IS)"  I made a patch
> >which seems to do this for you...
> >Use this switch to enable double precision on RTREE /DRTREE_DOUBLE
>
> I am interested in this as well.  Will this patch be included in a future
> version of SQLite?  Or, if not, how best to make use of this patch if I
> normally use the amalgamation from C++.
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615
> ___
> 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] EXTERNAL:Re: R*Tree module and double precision?

[Black, Michael (IS)]

If you're trying to get more than 6 or 7 significant digits, yes
And if you're dealing with lat/lon 
1 degree = 69.04 miles or 364531.2 feet
 
So if youi're trying to get sub-foot accuracy/precision you need the digits.
 
Not quite so important for the RTree I would think as it is for the actual 
points you retrieve.
 
 
Without double prec:
sqlite> CREATE VIRTUAL TABLE demo_index USING rtree(
   ...>id,  -- Integer primary key
   ...>minX, maxX,  -- Minimum and maximum X coordinate
   ...>minY, maxY   -- Minimum and maximum Y coordinate
   ...> );
sqlite> insert into demo_index 
values(1,-80.98754321,-80.1234576789,30.43219756,30.567891234);
sqlite> select * from demo_index;
1|-80.9875411987305|-80.1234588623047|30.4321975708008|30.567892074585
 
With double precision.
 
sqlite> CREATE VIRTUAL TABLE demo_index USING rtree(
   ...>id,  -- Integer primary key
   ...>minX, maxX,  -- Minimum and maximum X coordinate
   ...>minY, maxY   -- Minimum and maximum Y coordinate
   ...> );
sqlite> insert into demo_index 
values(1,-80.98754321,-80.1234576789,30.43219756,30.567891234);
sqlite> select * from demo_index;
1|-80.98754321|-80.1234576789|30.43219756|30.567891234
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Ben Harper
Sent: Thu 11/4/2010 10:27 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?



Are you sure that double precision in the RTree is necessary for your purposes?

Here is a table of floating point precision at various values:

The 'flip' values are the 'org' values with the lowest bit flipped.

 org flip (delta)
0.01 0.01 ( 0.00) (lowest bit is 10 ^ -9.030900)
0.10 0.10 ( 0.00) (lowest bit is 10 ^ -8.127810)
1.00 1.00 ( 0.00) (lowest bit is 10 ^ -7.224720)
   10.0010.00 ( 0.01) (lowest bit is 10 ^ -6.020600)
  100.00   100.00 ( 0.08) (lowest bit is 10 ^ -5.117510)
 1000.00  1000.00 ( 0.61) (lowest bit is 10 ^ -4.214420)
1.00 1.00 ( 0.000977) (lowest bit is 10 ^ -3.010300)
9.99 9.98 ( 0.007813) (lowest bit is 10 ^ -2.107210)
   99.9499.88 ( 0.062500) (lowest bit is 10 ^ -1.204120)
  999.00   998.00 ( 1.00) (lowest bit is 10 ^ 0.00)
 9992.00  9984.00 ( 8.00) (lowest bit is 10 ^ 0.903090)
99936.00 99872.00 (64.00) (lowest bit is 10 ^ 1.806180)


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Black, Michael (IS)
Sent: 04 November 2010 02:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] R*Tree module and double precision?

I'll send the patched C file to your email address.  I think the 1M patched 
version is a bit much for the email list.

Normally you would just use the "patch" utility to apply the patch to your file.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems




From: sqlite-users-boun...@sqlite.org on behalf of 
jsarc...@nanotronicsimaging.com
Sent: Wed 11/3/2010 10:22 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?



>From: "Black, Michael (IS)"  I made a patch
>which seems to do this for you...
>Use this switch to enable double precision on RTREE /DRTREE_DOUBLE

I am interested in this as well.  Will this patch be included in a future 
version of SQLite?  Or, if not, how best to make use of this patch if I 
normally use the amalgamation from C++.
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
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] R*Tree module and double precision?

[Ben Harper]

Are you sure that double precision in the RTree is necessary for your purposes?

Here is a table of floating point precision at various values:

The 'flip' values are the 'org' values with the lowest bit flipped.

 org flip (delta)
0.01 0.01 ( 0.00) (lowest bit is 10 ^ -9.030900)
0.10 0.10 ( 0.00) (lowest bit is 10 ^ -8.127810)
1.00 1.00 ( 0.00) (lowest bit is 10 ^ -7.224720)
   10.0010.00 ( 0.01) (lowest bit is 10 ^ -6.020600)
  100.00   100.00 ( 0.08) (lowest bit is 10 ^ -5.117510)
 1000.00  1000.00 ( 0.61) (lowest bit is 10 ^ -4.214420)
1.00 1.00 ( 0.000977) (lowest bit is 10 ^ -3.010300)
9.99 9.98 ( 0.007813) (lowest bit is 10 ^ -2.107210)
   99.9499.88 ( 0.062500) (lowest bit is 10 ^ -1.204120)
  999.00   998.00 ( 1.00) (lowest bit is 10 ^ 0.00)
 9992.00  9984.00 ( 8.00) (lowest bit is 10 ^ 0.903090)
99936.00 99872.00 (64.00) (lowest bit is 10 ^ 1.806180)


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Black, Michael (IS)
Sent: 04 November 2010 02:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] R*Tree module and double precision?

I'll send the patched C file to your email address.  I think the 1M patched 
version is a bit much for the email list.
 
Normally you would just use the "patch" utility to apply the patch to your file.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of 
jsarc...@nanotronicsimaging.com
Sent: Wed 11/3/2010 10:22 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?



>From: "Black, Michael (IS)"  I made a patch 
>which seems to do this for you...
>Use this switch to enable double precision on RTREE /DRTREE_DOUBLE

I am interested in this as well.  Will this patch be included in a future 
version of SQLite?  Or, if not, how best to make use of this patch if I 
normally use the amalgamation from C++.
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
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 from fossil

[Richard Hipp]

On Thu, Nov 4, 2010 at 9:19 AM, Hakki Dogusan  wrote:

> > In newer versions of Fossil, the "fossil ui" command binds to the
> loopback
> > IP only (127.0.0.1) meaning that it will only see TCP/IP traffic coming
> from
> > the local machine.  This is a security feature.  The "fossil server"
> command
> > continues to bind to all IP addresses as it always has.
> >
>
> >
> (Possibly a stupid question/suggestion, but)
> Could "fossil ui" be relaxed to allow local addresses?
>
>
I don't think the internet protocol supports such a concept.

If you want to try to patch something, search for HTTP_SERVER_LOCALHOST in
the Fossil source tree to find the relevant places in the code.


-- 
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] sqlite from fossil

[Hakki Dogusan]

Hi,

04/11/2010 14:09, Richard Hipp wrote:
> On Thu, Nov 4, 2010 at 3:52 AM, Hakki Dogusan  wrote:
>
>> Hi,
>>
>> 03/11/2010 23:51, Benjamin Peterson wrote:
>>> Richard Hipp   writes:
 Try setting:

fossil setting autosync off

 before you do the

fossil update
>>>
>>> I actually get this from "fossil clone http://sqlite.org/src;, too.
>>>
>>
>>
>> I don't know it is same problem as this but I'm getting something like
>> "couldn't login" error too.
>>
>> In my case
>> Server (Ubuntu) with: fossil-linux-x86-20101101142335.zip
>> Client (WinXP) with : fossil-w32-20101101142335.zip
>>
>> If I use
>> fossil server reponame.fossil
>> instead of
>> fossil ui reponame.fossil
>> then client successfully sync.
>>
>
> In newer versions of Fossil, the "fossil ui" command binds to the loopback
> IP only (127.0.0.1) meaning that it will only see TCP/IP traffic coming from
> the local machine.  This is a security feature.  The "fossil server" command
> continues to bind to all IP addresses as it always has.
>

(Possibly a stupid question/suggestion, but)
Could "fossil ui" be relaxed to allow local addresses?


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

Re: [sqlite] R*Tree module and double precision?

[Black, Michael (IS)]

I'll send the patched C file to your email address.  I think the 1M patched 
version is a bit much for the email list.
 
Normally you would just use the "patch" utility to apply the patch to your file.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of 
jsarc...@nanotronicsimaging.com
Sent: Wed 11/3/2010 10:22 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision?



>From: "Black, Michael (IS)" 
>I made a patch which seems to do this for you...
>Use this switch to enable double precision on RTREE /DRTREE_DOUBLE

I am interested in this as well.  Will this patch be included in a future
version of SQLite?  Or, if not, how best to make use of this patch if I normally
use the amalgamation from C++.
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
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] Using sqlite's WAL with a hash table store

[Russell Leighton]

Check out:


http://jaksprats.wordpress.com/2010/09/28/introducing-redisql-the-lightning-fast-polyglot/


On Nov 3, 2010, at 10:51 AM, Jay A. Kreibich wrote:

> On Wed, Nov 03, 2010 at 05:10:22PM +0300, Alexey Pechnikov scratched  
> on the wall:
>> 2010/11/3 Jay A. Kreibich 
>>>
>>> Why not just use an off-the-self hash cache, like memcached, or an
>>> off-the-self hash database, like Redis?  Redis even supports an
>>> Append-Only write mode (e.g. WAL-ish journal mode).
>>
>> How about power fail or OS crash? As I know, Redis does not  
>> garantees the
>> data safety.
>
>  This is getting a bit off topic, but quickly...
>
>  No, by default Redis does not provide the level of protection a
>  default file-backed SQLite database provides.  Redis's append-only
>  mode does a pretty good job, however.  Redis will update journal
>  with each command, and a sync is performed every second.  In theory,
>  in the case of a power or OS crash, maximum data loss is right around
>  one second worth of transactions.  Application crash will not result
>  in data loss, since the OS still has valid file buffers that will
>  eventually be flushed.  You can also configure things so that the
>  journal syncs after each command, providing similar protection to
>  SQLite.  That is, as you might expect, somewhat slow, however.
>
>> And I think SQLite in-memory database is faster.
>
>  Without testing, I'd guess Redis is faster for basic read/write
>  operations.  Redis is also a proper server and allows multiple client
>  connections, even for a fully memory based data set.
>
>  By default Redis will hold all data in RAM, and is highly optimized
>  for one-- and only one-- basic operation, while SQLite is supporting
>  a much richer and more expressive data processing environment.
>
>  Each tool has its place, and they're not really trying to solve
>  the same problem.
>
>  The whole reason I'm looking to merge the two has to do with SQLite's
>  expressive environment.  In designing a very high-performance app,
>  the highly-optimized common-case queries can talk to Redis directly.
>  This is fast, but requires jumping through a lot of hoops in the
>  application code.  Conversely, the less frequent queries (including
>  many of the management tasks) can talk to SQLite, which can then talk
>  to Redis.  Use of the SQL language makes development MUCH faster for
>  those operations that are not as performance-critical.
>
>   -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

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

[sqlite] sqlite.exe db_name .dump : It missed : table name is un quoted.(INSERT statement)

[geefwfge201011]

sqlite.exe db_name .dump
It missed : table name is un quoted.(INSERT statement)
database conversion fails (version 2 to 3)

sqlite -version
2.8.17

- dump_test.sql
create table "where" (id INTEGER);
   INSERT INTO "where" VALUES(1);
---

@rem create database
sqlite.exe dump_test.db < dump_test.sql

sqlite.exe dump_test.db .dump > test1.sql
@rem test1.sql : missed : table name is un quoted.

sqlite.exe dump_test.db .dump | sqlite3.exe test_db3.db
@rem sqlite3.exe test_db3.db < test1.sql
@rem  error occures.
@rem  : syntax error
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3: col name bug on select statemnt

[geefwfge201011]

[critical bug] sqlite3: col name bug on select statemnt

sqlite3 --version
3.7.3

this bug occures with count or sum .

 
c:\tmp> sqlite3 test3.db

.header ON

-- bug
drop table t1;
drop table t2;
drop table t3;
CREATE  TABLE t1 (col1 INTEGER);
INSERT INTO t1 VALUES(1);
CREATE  TABLE t2 as select "col1" , count(*) from t1;
CREATE  TABLE t3 as select "col1" , sum("col1") from t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT [col1] FROM t2;
SELECT [col1] FROM t3;
.schema t2
.schema t3
--- Error: no such column: col1
--- Result : CREATE TABLE t2("""col1""" INT,"count(*)");
  ^^^^
 workaround : CREATE  TABLE t2 as select "col1"  as 'col1' , count(*) from 
t1;

-- bug
drop table t1;
drop table t2;
CREATE  TABLE t1 ([col-1] INTEGER);
CREATE  TABLE t2 as select [col-1] , count(*) from t1;
.schema t2
--- Result : CREATE TABLE t2("[col-1]" INT,"count(*)");

-- bug
drop table t1;
drop table t2;
CREATE  TABLE t1 ("col-1" INTEGER);
CREATE  TABLE t2 as select "col-1" , count(*) from t1;
.schema t2
--- Result : CREATE TABLE t2("""col-1""" INT,"count(*)");

-- bug
drop table t1;
drop table t2;
CREATE  TABLE t1 ('col1' INTEGER);
CREATE  TABLE t2 as select "col1" , count(*) from t1;
.schema t2
--- Result : CREATE TABLE t2("""col1""" INT,"count(*)");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R*Tree module and double precision?

[jsarcher]

>From: "Black, Michael (IS)" 
>I made a patch which seems to do this for you...
>Use this switch to enable double precision on RTREE /DRTREE_DOUBLE

I am interested in this as well.  Will this patch be included in a future 
version of SQLite?  Or, if not, how best to make use of this patch if I 
normally 
use the amalgamation from C++.
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite from fossil

[Richard Hipp]

On Thu, Nov 4, 2010 at 3:52 AM, Hakki Dogusan  wrote:

> Hi,
>
> 03/11/2010 23:51, Benjamin Peterson wrote:
> > Richard Hipp  writes:
> >> Try setting:
> >>
> >>   fossil setting autosync off
> >>
> >> before you do the
> >>
> >>   fossil update
> >
> > I actually get this from "fossil clone http://sqlite.org/src;, too.
> >
>
>
> I don't know it is same problem as this but I'm getting something like
> "couldn't login" error too.
>
> In my case
> Server (Ubuntu) with: fossil-linux-x86-20101101142335.zip
> Client (WinXP) with : fossil-w32-20101101142335.zip
>
> If I use
> fossil server reponame.fossil
> instead of
> fossil ui reponame.fossil
> then client successfully sync.
>

In newer versions of Fossil, the "fossil ui" command binds to the loopback
IP only (127.0.0.1) meaning that it will only see TCP/IP traffic coming from
the local machine.  This is a security feature.  The "fossil server" command
continues to bind to all IP addresses as it always has.



>
>
> --
> Regards,
> Hakki Dogusan
>
> ___
> 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] A question about transactions

[Dan Kennedy]

On Nov 4, 2010, at 1:30 AM, Igor Tandetnik wrote:

> Pavel Ivanov  wrote:
>>> Yes. That's precisely the intended use case. Remember though that  
>>> the transaction is not really committed until COMMIT statement
>>> runs: if your application crashes or machine loses power, all  
>>> changes to the beginning of the transaction are rolled back, not
>>> just those since last "committed" checkpoint.
>>
>> Does my memory fool me or there are some error conditions in
>> SQLite when it automatically (without explicit user request)  
>> rollbacks
>> the whole transaction disregarding any savepoints?
>
> I believe ON CONFLICT ROLLBACK (and its equivalents, like  
> RAISE(ROLLBACK) ) would roll back the whole transaction.

That's the usual cause.

Also, if an OOM or IO error occurs the transaction may be rolled
back automatically. Or sometimes if the file-system is full.

Dan.

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

Re: [sqlite] sqlite from fossil

[Hakki Dogusan]

Hi,

03/11/2010 23:51, Benjamin Peterson wrote:
> Richard Hipp  writes:
>> Try setting:
>>
>>   fossil setting autosync off
>>
>> before you do the
>>
>>   fossil update
>
> I actually get this from "fossil clone http://sqlite.org/src;, too.
>


I don't know it is same problem as this but I'm getting something like 
"couldn't login" error too.

In my case
Server (Ubuntu) with: fossil-linux-x86-20101101142335.zip
Client (WinXP) with : fossil-w32-20101101142335.zip

If I use
fossil server reponame.fossil
instead of
fossil ui reponame.fossil
then client successfully sync.


--
Regards,
Hakki Dogusan

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