[sqlite] anyone know how to use ta-lib api to the sqlite database?

[YAN HONG YE]

the ta-lib.org, the ta-lib library could use in EXCEL, I wanna use it in the 
sqlite database, but I don't know how to write the c or c++ code to load the 
ta-lib api in sqlite database, anyone know it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Connection between SQLite - Other Databases

[Reddy C. balaji]

Hi All,
Please let me know the options available to connect to other 
databases ( Oracle, MSSQL , MySQL etc ) from SQLite. For example, to connect 
from Oracle to other databases DBLink can be used. In the same way, from MSSQL 
, Linked Server can be used to connect to other databases.

In the same way are there any features available in SQLite ??

Thank you
Reddy Balaji C.

DISCLAIMER: This email message and all attachments are confidential and may 
contain information that is Privileged, Confidential or exempt from disclosure 
under applicable law. If you are not the intended recipient, you are notified 
that any dissemination, distribution or copying of this email is strictly 
prohibited.  If you have received this email in error, please notify us 
immediately by return email to mailad...@spanservices.com and destroy the 
original message.  Opinions, conclusions and other information in this message 
that do not relate to the official of SPAN, shall be understood to be nether 
given nor endorsed by SPAN.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] use sqlite to count macd value

[Simon Slavin]

On 20 Jun 2013, at 2:04am, YAN HONG YE  wrote:

> DIFF : EMA(CLOSE,12) - EMA(CLOSE,26);
> DEA  : EMA(DIFF,9);
> MACD : 2*(DIFF-DEA);
> 
> I wanna use sqlite to count macd value, have any idea?

You cannot calculate moving average inside SQLite, because table rows do not 
appear to aggregate functions in any set order.

You will have to calculate these values in your programming language.

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

[sqlite] use sqlite to count macd value

[YAN HONG YE]

DIFF : EMA(CLOSE,12) - EMA(CLOSE,26);
DEA  : EMA(DIFF,9);
MACD : 2*(DIFF-DEA);

I wanna use sqlite to count macd value, have any idea?
thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error with new HTTPS configuration on sqlite.org (missing certificate chain up to CA)

[Oliver Schneider]

Hi,

using Opera I receive the following error:

> Could not connect to remote server
> 
> You tried to access the address https://sqlite.org/lang_attach.html, which is 
> currently unavailable. Please make sure that the web address (URL) is 
> correctly spelled and punctuated, then try reloading the page.
> 
> Secure connection: fatal error (40)
> 
> https://sqlite.org/lang_attach.html
> 
> Failed to connect to server. The reason may be that the encryption methods 
> supported by the server are not enabled in the security preferences.
> 
> Please note that some encryption methods are no longer supported, and that 
> access will not be possible until the website has been upgraded to use strong 
> encryption.
> Make sure your internet connection is active and check whether other 
> applications that rely on the same connection are working.
> Check that the setup of any internet security software is correct and does 
> not interfere with ordinary web browsing.
> If you are behind a firewall on a Local Area Network and think this may be 
> causing problems, talk to your systems administrator.
> Try pressing the F12 key on your keyboard and disabling proxy servers, unless 
> you know that you are required to use a proxy to connect to the internet. 
> Reload the page.

I don't with Firefox. So I decided to investigate a bit more with
Firefox. Turns out the latter uses a built-in version of the CA cert.
This suggests that your server doesn't hand out the complete certificate
chain.

Since your web server doesn't provide information about what's running.
I'm unable to suggest a solution other than reviewing the configuration.

However, you can find the GoDaddy CA bundles here:

  

// Oliver

PS: re-sending from the subscribed email address.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] escape quote for csv import

[Steve Martin]

Hi

> (There is no official CSV standard, and there is no widely supported escaping 
> mechanism.)

Refer to RFC4180 for CSV standard.



Steve Martin
Technical Lead
NEC New Zealand Limited
NEC House, Level 6, 40 Taranaki Street, PO Box 1936, Wellington 6140, New 
Zealand
T: 043816291  M: 0293816291  F: +644380
steve.mar...@nec.co.nz
nz.nec.com



Please consider the environment before printing
this email

Attention:
The information contained in this message and or attachments is intended only 
for the person or entity to which it is addressed and may contain confidential 
and/or privileged material.  Any review, retransmission, dissemination, copying 
or other use of, or taking of any action in reliance upon, this information by 
persons or entities other than the intended recipient is prohibited. If you 
received this in error, please contact the sender and delete the material from 
any system and destroy any copies. NEC has no liability for any act or omission 
in reliance on the email or any attachment.  Before opening this email or any 
attachment(s), please check them for viruses. NEC is not responsible for any 
viruses in this email or any attachment(s); any changes made to this  email or 
any attachment(s) after they are sent; or any effects this email or any 
attachment(s) have on your network or computer system.
-

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

Re: [sqlite] escape quote for csv import

[j . merrill]

The fact that there's no "official CSV standard" doesn't mean that there isn't 
"common practice" that SQLite should support (but I can't say that I know that 
it does; it should be easy to change if it doesn't).
 
The "common practice" is to double each embedded double-quote.
 
"abc","the char in parens ("") is doublequote","def"
 
-Original Message-
Date: Tue, 18 Jun 2013 22:02:31 +0200

From: Clemens Ladisch 
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] escape quote for csv import
Message-ID: <51c0bcd7.4050...@ladisch.de>
Content-Type: text/plain; charset=ISO-8859-1

Roland Hughes wrote:
> How does one escape a in a CSV file so it will correctly import?

The sqlite3 tool allows to configure the separator, but the quote
character for delimiting fields is hardcoded.

(There is no official CSV standard, and there is no widely supported
escaping mechanism.)

> I can only massage the CSV

Convert it into properly formatted SQL INSERT statements.


Regards,
Clemens

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

Re: [sqlite] General question on 'style' Simon/Stephen.

[Rob Willett]

Hi,

I wasn't overaly bothered about performance, since my backend jobs take hours 
to run and processing TB's of data, the overhead of opening and closing a few 
hundred database connections in the grand scheme of things is pretty low. It 
was a general style and usage question. I'm a C hacker by preference and have 
my own style of how I use memory, how I break things down into functions and 
use pointers and structures. I'm not a SQLite expert (pretty much a novice in 
fact) and wanted to get practises in from the beginning rather than trying to 
embed them later.

My backend process might run for an hour or two and only write out a few 
hundred bits of data, so I'll carry on opening and closing it as needed unless 
someone can say there's good practise not to do it. I like the idea of 
localised database transactions within the code, it's a bit like malloc and 
free. Keep it short and sweet if you can.

Thanks again for the sensible and constructive replies.

Rob.

On 19 Jun 2013, at 20:15, RSmith  wrote:

> Actually, having benched this specific thing before, I can tell you that 
> there is a performance penalty opeining and closing DBs (as opposed to just 
> keeping a connection open) - but this is not news, what might be news is that 
> the performance penalty is much much smaller than I anticipated and in fact 
> probably negligible for any sort of web application - so my advice would be 
> to go with what suits your feelings best and the rest of your app best.
> One comment: I see you wirte "...keep the database connection open as other 
> users would/could/might want to add stuff to the queue..." - AFAIK there is 
> zero impact on who else wants to write to your database, any other connection 
> can still write to it (just not at the exact same time, but even then SQLite 
> will handle the timing/locking/writing very well). Only worry about this if 
> your connection locks up a table for a long time - but my guess is that 
> either it doesn't, or there's nothing you can do about it, the data MUST be 
> updated etc.
> 
> Btw, to all, I concur very much with Rob as to the quality of replies on this 
> forum - many thanks to all!
> 
> 
> On 2013/06/19 16:45, Rob Willett wrote:
>> Stephen, Simon,
>> 
>> I'm not worried about the CPU load at all, it was purely a question of 
>> style. Stephan has talked about keeping the connections open for the 
>> lifetime of a desktop connection, Simon has talked about closing and opening 
>> connections many, many times during the course of a transaction. My model is 
>> more towards Simon's.
>> 
>> Part of this model is due to the backend process taking hours to run. I 
>> wanted the web user to kick this off and come back when it's finished 
>> (possible tomorrow). It didn't feel write to keep the database connection 
>> open as other users would/could/might want to add stuff to the queue to be 
>> run.
>> 
>> The fact I have had two replies, and thank you both for writing back, but 
>> have very different viewpoints and experiences makes me think that my model 
>> isn't bad for my particular use case. Clearly other people will have 
>> different experiences and in different situations I would do it differently.
>> 
>> On another note, I have been impressed with the quality of replies on this 
>> list. I listen into other lists on other technologies and the quality and 
>> standard of 'stuff' here is very high. Not seen very many "My database don't 
>> work, fix it for me" type mails here which is great.  Some of the detail 
>> people go into is wonderful and I've learnt an awful lot just reading 
>> replies.
>> 
>> Best wishes,
>> 
>> Rob.
>> 
> 
> ___
> 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] General question on 'style' Simon/Stephen.

[RSmith]

Actually, having benched this specific thing before, I can tell you that there is a performance penalty opeining and closing DBs (as 
opposed to just keeping a connection open) - but this is not news, what might be news is that the performance penalty is much much 
smaller than I anticipated and in fact probably negligible for any sort of web application - so my advice would be to go with what 
suits your feelings best and the rest of your app best.
One comment: I see you wirte "...keep the database connection open as other users would/could/might want to add stuff to the 
queue..." - AFAIK there is zero impact on who else wants to write to your database, any other connection can still write to it (just 
not at the exact same time, but even then SQLite will handle the timing/locking/writing very well). Only worry about this if your 
connection locks up a table for a long time - but my guess is that either it doesn't, or there's nothing you can do about it, the 
data MUST be updated etc.


Btw, to all, I concur very much with Rob as to the quality of replies on this 
forum - many thanks to all!


On 2013/06/19 16:45, Rob Willett wrote:

Stephen, Simon,

I'm not worried about the CPU load at all, it was purely a question of style. 
Stephan has talked about keeping the connections open for the lifetime of a 
desktop connection, Simon has talked about closing and opening connections 
many, many times during the course of a transaction. My model is more towards 
Simon's.

Part of this model is due to the backend process taking hours to run. I wanted 
the web user to kick this off and come back when it's finished (possible 
tomorrow). It didn't feel write to keep the database connection open as other 
users would/could/might want to add stuff to the queue to be run.

The fact I have had two replies, and thank you both for writing back, but have 
very different viewpoints and experiences makes me think that my model isn't 
bad for my particular use case. Clearly other people will have different 
experiences and in different situations I would do it differently.

On another note, I have been impressed with the quality of replies on this list. I listen 
into other lists on other technologies and the quality and standard of 'stuff' here is 
very high. Not seen very many "My database don't work, fix it for me" type 
mails here which is great.  Some of the detail people go into is wonderful and I've 
learnt an awful lot just reading replies.

Best wishes,

Rob.



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

Re: [sqlite] Use more than one database in one application

[Gianni Sassanelli]

If you open SQLite db by SQLLite plugin in Android,
you must look in plugin source to found your problem.

The bug was there because there is always 1 db opened for time end db
opened is always the last opened

if you try
sqlite3_open16 (“db1.db”, db_1);  // open first
sqlite3_open16 (“db2.db”, db_2);  // open second
when I INSERT some data into db_1, data is actually inserted into db_2  //
this query is executed on db2.db

if you redo:
sqlite3_open16 (“db1.db”, db_1);  // open first
and you run query:  "Select * from Table_on_db1"   // this query is always
executed on db2.db because the plugin do not reopen a database that was
just open

i haved maked a simply workaround into plugin that close the opened db then
re-open it

after workaround into plugin, my query are similar to:

 sqlite3_open16 (“db1.db”, db_1);  // open first
tx.executesql( ...query1 on db1...)  //
tx.executesql( ...query2 on db1..)  //
tx.executesql( ...query3 on db1..)  //

sqlite3_open16 (“db2.db”, db_2);  // open second
tx.executesql( ...query1 on db2...)  //
 tx.executesql( ...query2 on db2..)  //

 sqlite3_open16 (“db1.db”, db_1);  // re-open first
tx.executesql( ...query4 on db1...)  //
tx.executesql( ...query5 on db1..)  //
but if if I could perform to do "ATTACH DATABASE " command everything
would be much simpler by id don't find nothing in this way
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] First Day of Week Inconsistency

[Michael Black]

Oops...forgot to add what you want.

   %U The week number of the current year as a decimal number,
range  00  to  53,
  starting  with the first Sunday as the first day of week 01.
See also %V and
  %W.

And this one:
   %V The ISO 8601:1988 week number of the current year as a decimal
number, range
  01  to  53,  where  week 1 is the first week that has at least
4 days in the
  current year, and with Monday as the first day of the week.
See also %U  and
  %W. (SU)

But SQLite doesn't implement those.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Denis Burke
Sent: Wednesday, June 19, 2013 11:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] First Day of Week Inconsistency

The built-in function strftime properly (imho) responds to the '%w' command
for day of week with Sunday as day 0.

e.g. -  strftime ('%w','2013-06-19')
->  3

But the built-in function for returning week of the year treats weeks as
though they begin on Monday, not Sunday:
e.g.
strftime ('%W','2013-06-19')
-> 24
strftime ('%W','2013-06-17')
-> 24
strftime ('%W','2013-06-16')
-> 23


It seems to me these useful functions are inconsistent.  Is it possible to
modify %W to treat Sunday as the first day of the week?

Thanks,
Denis Burke
___
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] First Day of Week Inconsistency

[Simon Slavin]

On 19 Jun 2013, at 5:20pm, Denis Burke  wrote:

> It seems to me these useful functions are inconsistent.

They are inconsistent with one-another.  But both functions are implemented to 
the standards, correctly as far as lots of POSIX-based computing is done.  It’s 
annoying but it’s correct.  Sorry.

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

Re: [sqlite] First Day of Week Inconsistency

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19/06/13 09:20, Denis Burke wrote:
> Is it possible to modify %W to treat Sunday as the first day of the
> week?

Traditionally strftime %W uses Monday to start each week while %U uses
Sunday.  It looks like SQLite doesn't implement the latter.

Roger

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

iEYEARECAAYFAlHB29kACgkQmOOfHg372QTxwQCfc3qGW5PtGljBm/rvgEOGVtA/
DzwAoI6cIiZ+SzMmX7NE2gcwEfDR0w2W
=y1z6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] First Day of Week Inconsistency

[Michael Black]

Those are unrelated questions.day of week has nothing to with the start
of a week.

>From the standard strftime man page which has been around for decades.


   %w The  day  of  the week as a decimal, range 0 to 6, Sunday
being 0.  See also
  %u.
   %W The week number of the current year as a decimal number,
range  00  to  53,
  starting with the first Monday as the first day of week 01.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Denis Burke
Sent: Wednesday, June 19, 2013 11:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] First Day of Week Inconsistency

The built-in function strftime properly (imho) responds to the '%w' command
for day of week with Sunday as day 0.

e.g. -  strftime ('%w','2013-06-19')
->  3

But the built-in function for returning week of the year treats weeks as
though they begin on Monday, not Sunday:
e.g.
strftime ('%W','2013-06-19')
-> 24
strftime ('%W','2013-06-17')
-> 24
strftime ('%W','2013-06-16')
-> 23


It seems to me these useful functions are inconsistent.  Is it possible to
modify %W to treat Sunday as the first day of the week?

Thanks,
Denis Burke
___
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] First Day of Week Inconsistency

[Denis Burke]

The built-in function strftime properly (imho) responds to the '%w' command
for day of week with Sunday as day 0.

e.g. -  strftime ('%w','2013-06-19')
->  3

But the built-in function for returning week of the year treats weeks as
though they begin on Monday, not Sunday:
e.g.
strftime ('%W','2013-06-19')
-> 24
strftime ('%W','2013-06-17')
-> 24
strftime ('%W','2013-06-16')
-> 23


It seems to me these useful functions are inconsistent.  Is it possible to
modify %W to treat Sunday as the first day of the week?

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

Re: [sqlite] General question on 'style' Simon/Stephen.

[Rob Willett]

Stephen, Simon,

I'm not worried about the CPU load at all, it was purely a question of style. 
Stephan has talked about keeping the connections open for the lifetime of a 
desktop connection, Simon has talked about closing and opening connections 
many, many times during the course of a transaction. My model is more towards 
Simon's. 

Part of this model is due to the backend process taking hours to run. I wanted 
the web user to kick this off and come back when it's finished (possible 
tomorrow). It didn't feel write to keep the database connection open as other 
users would/could/might want to add stuff to the queue to be run. 

The fact I have had two replies, and thank you both for writing back, but have 
very different viewpoints and experiences makes me think that my model isn't 
bad for my particular use case. Clearly other people will have different 
experiences and in different situations I would do it differently.

On another note, I have been impressed with the quality of replies on this 
list. I listen into other lists on other technologies and the quality and 
standard of 'stuff' here is very high. Not seen very many "My database don't 
work, fix it for me" type mails here which is great.  Some of the detail people 
go into is wonderful and I've learnt an awful lot just reading replies.

Best wishes,

Rob.

On 19 Jun 2013, at 15:35, Simon Slavin  wrote:

> 
> On 19 Jun 2013, at 3:15pm, Rob Willett  wrote:
> 
>> Now to the hub (excuse the really bad pun) of my questions, within my 
>> service side application, I'm finding that I'm constantly opening and 
>> closing the same SQLite database, inserting data, reading data, updating 
>> date over many hours. The total number of interactions is quite small, 
>> perhaps a few hundred over the course of the application run which could 
>> take hours to run. 
>> 
>> I'm not bothered over the file i/o per se. nor about the amount of data 
>> written to the database as its very small but wanted to validate that this 
>> approach of opening and closing the database as close to the necessary 
>> transaction as possible is an appropriate 'style'. As I do this I'll open 
>> and close the SQLite database probably a few hundred times in the course of 
>> a run.
>> 
>> My feeling is that opening and keeping open the SQLite database for any time 
>> longer than necessary at all is bad, since my PHP might want to update 
>> things.
> 
> Nice description.
> 
> In some of my uses I have a setup similar to yours in many ways.  The back 
> end to my web-facing services is a small PHP file on my web server which acts 
> as a SQLite3 shim.  Requests are passed to it in JSON and it answers in JSON. 
>  Some web-facing systems (in JavaScript) and other applications (which 
> generate their own HTTP POST requests) pass it individual SQLite commands and 
> it opens the database, does the command, then closes the database again.  
> Because ... well, that’s all it can really do given the structure of what PHP 
> on a browser can do.
> 
> So yes, my logs can show my PHP shim being called hundreds of times a minute. 
>  But it does the job fine: I’ve never traced down any fault down to it being 
> called so often.  And, of course, the server has Apache, PHP, the shim file 
> and many of my SQLite databases permanently in cache, so the whole thing runs 
> pretty quickly.
> 
> As you write, it does seem to be terribly inefficient.  But we’re bound by 
> the nature of how HTTP requests work.  You can’t really improve on it without 
> implementing something that serves SQLite over TCP/IP.  Or maybe implement it 
> as an Apache module.  And it’s so much more convenient to be able to write my 
> code in PHP and know it will run on any Apache platform and can be maintained 
> (should I die) by anyone familiar with PHP programming and HTTP requests.
> 
> I’ll worry about it more when my server CPU starts heading towards 100%.
> 
> 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] General question on 'style'

[Simon Slavin]

On 19 Jun 2013, at 3:15pm, Rob Willett  wrote:

> Now to the hub (excuse the really bad pun) of my questions, within my service 
> side application, I'm finding that I'm constantly opening and closing the 
> same SQLite database, inserting data, reading data, updating date over many 
> hours. The total number of interactions is quite small, perhaps a few hundred 
> over the course of the application run which could take hours to run. 
> 
> I'm not bothered over the file i/o per se. nor about the amount of data 
> written to the database as its very small but wanted to validate that this 
> approach of opening and closing the database as close to the necessary 
> transaction as possible is an appropriate 'style'. As I do this I'll open and 
> close the SQLite database probably a few hundred times in the course of a run.
> 
> My feeling is that opening and keeping open the SQLite database for any time 
> longer than necessary at all is bad, since my PHP might want to update things.

Nice description.

In some of my uses I have a setup similar to yours in many ways.  The back end 
to my web-facing services is a small PHP file on my web server which acts as a 
SQLite3 shim.  Requests are passed to it in JSON and it answers in JSON.  Some 
web-facing systems (in JavaScript) and other applications (which generate their 
own HTTP POST requests) pass it individual SQLite commands and it opens the 
database, does the command, then closes the database again.  Because ... well, 
that’s all it can really do given the structure of what PHP on a browser can do.

So yes, my logs can show my PHP shim being called hundreds of times a minute.  
But it does the job fine: I’ve never traced down any fault down to it being 
called so often.  And, of course, the server has Apache, PHP, the shim file and 
many of my SQLite databases permanently in cache, so the whole thing runs 
pretty quickly.

As you write, it does seem to be terribly inefficient.  But we’re bound by the 
nature of how HTTP requests work.  You can’t really improve on it without 
implementing something that serves SQLite over TCP/IP.  Or maybe implement it 
as an Apache module.  And it’s so much more convenient to be able to write my 
code in PHP and know it will run on any Apache platform and can be maintained 
(should I die) by anyone familiar with PHP programming and HTTP requests.

I’ll worry about it more when my server CPU starts heading towards 100%.

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

Re: [sqlite] General question on 'style'

[Stephen Chrzanowski]

IMO, depends on the type of process wanting to make a connection.

Anything web service wise is typically stateless, which means any time a
new request is made, you have to re-open a new connection.  You can
probably get away with using server side variables to handle the
connections, as I did in ASP days in IIS under Win2k, but I've not done so
with PHP/Apache.  With IIS, I used to use pooling connections, however, 99%
of the time, it was just in-office stuff with a staff of 4, and typically
only 5 computers accessing the server.  I'd guestimate that during a
regular 8 hour shift, server work load time was in the minutes, so not
exactly what you'd call heavy use. ;)

If you're using a desktop application, I leave the connection open to the
database for the life of the application.  Right at the application start,
I connect to the database, and when the application is to die, I close it.
During the life span, I create tables objects within the
procedures/functions I need to do the prepare, or direct SQL queries.


On Wed, Jun 19, 2013 at 10:15 AM, Rob Willett
wrote:

> Hi,
>
> My first question as a lurker and read of these groups. Hopefully it's not
> too stupid :)
>
> I've been working through using SQLite on my Mac and have a question on
> style and the way to use SQLite databases.
>
> My application has two parts, a client/PHP side to collect requests from a
> user and a C server side program that manipulates large quantities of data,
> anything from 10's bytes to 10's of Terabytes. When its manipulating TB's
> of data (none of which is in a SQL database) it will run for hours which is
> absolutely fine and expected. Just to be clear I do not have a performance
> issue with SQLite at all.
>
> Since the application has to work across numerous operating systems, I'm
> treating SQLite as the main way to handle information, I'm not using lock
> files, semaphores or any other files as these are all too OS specific. I
> hold requests to do work in SQLite in a queue table, I pass information
> back to the client side via a SQL database. None of this is too difficult
> and I take my inspiration from the manual which says that you can use
> SQLite almost as a replacement for a file in some cases. I like the idea of
> a simple interface like this and to be honest it seems to work well.
>
> Now to the hub (excuse the really bad pun) of my questions, within my
> service side application, I'm finding that I'm constantly opening and
> closing the same SQLite database, inserting data, reading data, updating
> date over many hours. The total number of interactions is quite small,
> perhaps a few hundred over the course of the application run which could
> take hours to run.
>
> I'm not bothered over the file i/o per se. nor about the amount of data
> written to the database as its very small but wanted to validate that this
> approach of opening and closing the database as close to the necessary
> transaction as possible is an appropriate 'style'. As I do this I'll open
> and close the SQLite database probably a few hundred times in the course of
> a run.
>
> My feeling is that opening and keeping open the SQLite database for any
> time longer than necessary at all is bad, since my PHP might want to update
> things.
>
> Comments welcomed.
>
> Thanks,
>
> Rob
>
>
>
> ___
> 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] General question on 'style'

[Rob Willett]

Hi,

My first question as a lurker and read of these groups. Hopefully it's not too 
stupid :)

I've been working through using SQLite on my Mac and have a question on style 
and the way to use SQLite databases.

My application has two parts, a client/PHP side to collect requests from a user 
and a C server side program that manipulates large quantities of data, anything 
from 10's bytes to 10's of Terabytes. When its manipulating TB's of data (none 
of which is in a SQL database) it will run for hours which is absolutely fine 
and expected. Just to be clear I do not have a performance issue with SQLite at 
all.

Since the application has to work across numerous operating systems, I'm 
treating SQLite as the main way to handle information, I'm not using lock 
files, semaphores or any other files as these are all too OS specific. I hold 
requests to do work in SQLite in a queue table, I pass information back to the 
client side via a SQL database. None of this is too difficult and I take my 
inspiration from the manual which says that you can use SQLite almost as a 
replacement for a file in some cases. I like the idea of a simple interface 
like this and to be honest it seems to work well.

Now to the hub (excuse the really bad pun) of my questions, within my service 
side application, I'm finding that I'm constantly opening and closing the same 
SQLite database, inserting data, reading data, updating date over many hours. 
The total number of interactions is quite small, perhaps a few hundred over the 
course of the application run which could take hours to run. 

I'm not bothered over the file i/o per se. nor about the amount of data written 
to the database as its very small but wanted to validate that this approach of 
opening and closing the database as close to the necessary transaction as 
possible is an appropriate 'style'. As I do this I'll open and close the SQLite 
database probably a few hundred times in the course of a run.

My feeling is that opening and keeping open the SQLite database for any time 
longer than necessary at all is bad, since my PHP might want to update things.

Comments welcomed.

Thanks,

Rob



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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Clemens Ladisch]

Igor Tandetnik wrote:
> On 6/19/2013 8:18 AM, Clemens Ladisch wrote:
>>  says:
>> | If two or more tables in different databases have the same name and
>> | the database-name prefix is not used on a table reference, then the
>> | table chosen is the one in the database that was least recently
>> | attached.
>>
>> As the first (i.e., most recently) attached, main always loses.
>
> You seem to have it backwards. The first would be least recent (that is,
> oldest); the last would be most recent.

Sorry, you're right.

The actual search order is first temp, then main, then any attached
databases in attachment order.


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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Igor Tandetnik]

On 6/19/2013 8:18 AM, Clemens Ladisch wrote:

 says:
| If two or more tables in different databases have the same name and
| the database-name prefix is not used on a table reference, then the
| table chosen is the one in the database that was least recently
| attached.

As the first (i.e., most recently) attached, main always loses.


You seem to have it backwards. The first would be least recent (that is, 
oldest); the last would be most recent.

--
Igor Tandetnik

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

Re: [sqlite] How to interrupt a long running update without roll back?

[Ryan Johnson]

On 19/06/2013 1:41 AM, jhnlmn wrote:

Thank you for your response

Simon Slavin  writes:

UPDATE T SET C1 = calculation(C2) WHERE C1 IS NULL AND rowid < 1

This is the best solution when the table is freshly created
and max(rowid) == number of rows.
But after many deletes and inserts many rowid will be unused,
max(rowid) may grow indefinitely and the number of required updates will be
also indefinite.

So, no perfect solution so far.
Why not use a trigger on T to update C1 whenever it gets set to NULL? Is 
it actually important for the NULL to persist until your next batch 
update comes along?


If a trigger would work, that's by far the cleanest solution, and will 
have almost no impact on concurrency. Otherwise...


Assuming you want to process between 10k and 20k rows at a time, you 
might try something like this (writing in python, you should be able to 
translate it easily to the language you actually use) :


import sqlite3
c = sqlite3.open('my-database.db')
histo = list(conn.execute('select min(rowid) lo, max(rowid) hi, count(*) 
n from lineitem group by rowid/1' order by lo))

buckets,i = [(0,0,0),], 0
while i < len(histo):
a,b,n = buckets[-1]
c,d,m = histo[i]
buckets[-1] = (a,d,n+m)
i += 1
if n+m >= 1
buckets.append((0,0,0))

for lo,hi,n in buckets:
conn.execute('update T set C1=calculation(C2) where C1 is NULL and 
rowid between ? and ?', (lo, hi))


Translated into plain English: count the number of rows in each slice of 
10k, being sure to return slices in order. That query will be fast 
because it should read from your rowid index and has small output size 
(even a billion-row input will only produce 100k rows). Merge too-small 
slices so that each contains somewhere between 10k and 20k-1 rows, then 
run your update query, passing the lower and upper bound of each slice 
to limit where it looks. Again, the index on rowid will enforce the 
range limit without a table scan (but double-check the output of 
"explain query plan").


NOTE: by splitting the transaction, you risk the database changing out 
from under you before the last slice is done. Somebody *could* delete a 
whole chunk of the rowid space, for example, and throw off your 
carefully computed slice sizes. Or they could add rows after you make 
the slices, and those rows would be ignored.  Or they could set C1=NULL 
on rows you already looked at. Most likely, you can squint and claim 
that all those kinds of things just happened "after" the batch update, 
but whether that's allowed is application dependent.


Ryan

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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Clemens Ladisch]

dochsm wrote:
> I would have thought having no database prefix would default to the
> main

 says:
| If two or more tables in different databases have the same name and
| the database-name prefix is not used on a table reference, then the
| table chosen is the one in the database that was least recently
| attached.

As the first (i.e., most recently) attached, main always loses.


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

Re: [sqlite] How to select from a temp table with same name as a main table.

[dochsm]

That brilliant. It is as I thought. Odd behaviour though. I would have
thought having no database prefix would default to the main, after all I
don't put the prefix there usually and if you try to create two tables in
the same db you get an error (obviously). To be on the safe side I've
re-written the code to give the temp table a different name and altered all
the sql to use the new name. That seems fine.
I'll have to remember that issue though as it might crop up again if ever I
inadvertantly give a temp table the same name as one alreay in main.
regards
Howard 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470p69475.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] How to interrupt a long running update without roll back?

[Simon Slavin]

On 19 Jun 2013, at 6:41am, jhnlmn  wrote:

> Simon Slavin  writes:
> 
>> Do you have an index on T(C1) ?  
>> That should dramatically reduce the search time.
> 
> I tried adding index.
> It caused slow down of the original insert of records to the table by about
> 25%, which is unacceptable to me.
> I also slows down the update because it has to update index as well.
> Note that C1 is not the only column, on which I would like to make updates.
> So, I will have to add several indexes, which will be even worse.

Nevertheless, this is the way the problem should be solved according to the 
design of SQLite.  What you are doing is searching for NULL entries in a table. 
 The way you speed up a search is to create an index ideally suited to the 
search.  And as you can see, it works: time for your update command is reduced 
from 'seconds or even minutes' to 2 seconds.

Is the increase in input/time really unacceptable to you ?  Updates that don’t 
change the value of the fields in the index should not take any longer.  The 
index is updated only if the values in it change.  The only thing that should 
take longer is inserting the row in the first place.  Is a slowdown of even 
100% on inputting new data really unacceptable ?  It should prove, in the long 
run, less inconvenient than the long locked period you are currently trying to 
solve.

You can, of course, do your inputting without the extra index existing, then 
create the index later, at a time suitable for you.  But the job of creating 
the index will block other processes from accessing the table just like the 
UPDATE command you currently do does.

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

Re: [sqlite] How do I show pragma settings

[Hick Gunter]

There is no documented way to show the value of this pragma (which is only used 
to debug SQLite anyway).

Other (settable) pragmas have query form (PRAGMA xxx;) and an "update" form 
(PRAGM xxx=value;).

-Ursprüngliche Nachricht-
Von: sqlitekyounoii [mailto:sqlitekyoun...@yahoo.co.jp]
Gesendet: Mittwoch, 19. Juni 2013 09:47
An: sqlite-users@sqlite.org
Betreff: [sqlite] How do I show pragma settings

I want to show "pragma parser_trace" settings.
How do I show pragma settings?

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I show pragma settings

[sqlitekyounoii]

I want to show "pragma parser_trace" settings.
How do I show pragma settings?

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