Re: [sqlite] Load a Sqlite Database on Disk as an In-memory Database

[Simon Slavin]

On 5 Feb 2013, at 7:33am, Alex Cheng  wrote:

> I want to write a program to load a sqlite database on disk as an in-memory
> database, for sake of reducing number of disk I/O operations. Do you know
> how to achieve it?

Probably to use the SQLite backup API:

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

of course, a program which did nothing more than what you described above would 
be useless: immediately the program quits, the in-memory database will 
disappear.

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

Re: [sqlite] update to limits infomation

[Yongil Jang]

Suggestion for new development item of sqlite?
I just mentioned this idea because of I thought that it is helpful for the
others if it can be implemented.

Your answer is correct.
But, n x n times of comparation would be occurred to count bigger number to
set rank of each record in mytable can make this query to be slow.
And, in my experience, there was some requirement of sequence number in my
work area.

Regards,
Yongil Jang.


2013/2/5 Igor Tandetnik 

> On 2/4/2013 9:37 PM, Yongil Jang wrote:
>
>> For example, if sqlite supports sequence() function that returns current
>> sequence number of result set then it can be used for this case, doesn't
>> it?
>>
>
> If SQLite supported such a function, then it could be used. But it
> doesn't, so it can't. I'm not sure what point you are trying to make here.
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Load a Sqlite Database on Disk as an In-memory Database

[Alex Cheng]

Hi guys,

I want to write a program to load a sqlite database on disk as an in-memory
database, for sake of reducing number of disk I/O operations. Do you know
how to achieve it?

Thank you in advance.

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

Re: [sqlite] update to limits infomation

[Igor Tandetnik]

On 2/4/2013 9:37 PM, Yongil Jang wrote:

For example, if sqlite supports sequence() function that returns current
sequence number of result set then it can be used for this case, doesn't it?


If SQLite supported such a function, then it could be used. But it 
doesn't, so it can't. I'm not sure what point you are trying to make here.

--
Igor Tandetnik

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

Re: [sqlite] update to limits infomation

[Yongil Jang]

I have a question.

For example, if sqlite supports sequence() function that returns current
sequence number of result set then it can be used for this case, doesn't it?

ex)
select sequence(), * from mytable order by score desc;
1, 10,rosa,95,0
2, 6,kero,90,0
3, 1,anna,80,0
4, 2,qera,65,0

This runtime values on result set may help to solve some query scenarios.


2013/2/5 Igor Tandetnik 

> On 2/4/2013 8:34 PM, Igor Tandetnik wrote:
>
>> On 2/4/2013 8:22 PM, YAN HONG YE wrote:
>>
>>> I hava a table like this:
>>> id,name,score,rank
>>> 1,anna,80,0
>>> 2,qera,65,0
>>> 6,kero,90,0
>>> 10,rosa,95,0
>>>
>>> what I would like to do is to update the rank position.
>>>
>>
>> update mytable set rank = (select count(*) from mytable t2 where
>> t2.score <= mytable.score);
>>
>
> Sorry, that should bet2.score >= mytable.score
>
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update to limits infomation

[Igor Tandetnik]

On 2/4/2013 8:22 PM, YAN HONG YE wrote:

I hava a table like this:
id,name,score,rank
1,anna,80,0
2,qera,65,0
6,kero,90,0
10,rosa,95,0

what I would like to do is to update the rank position.


update mytable set rank = (select count(*) from mytable t2 where 
t2.score <= mytable.score);


--
Igor Tandetnik

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

[sqlite] update to limits infomation

[YAN HONG YE]

I hava a table like this:
id,name,score,rank
1,anna,80,0
2,qera,65,0
6,kero,90,0
10,rosa,95,0

what I would like to do is to update the rank position.  I have this,

update mytable set rank= 1 where max(score);
update mytable set rank= 2 where max(score-1) ;


the result should be like this:
id,name,score,rank
1,anna,80,3
2,qera,65,4
6,kero,90,2
10,rosa,95,1

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

[sqlite] 1st Call For Papers, 19th Annual Tcl/Tk Conference 2012

[Andreas Kupries]

20'th Annual Tcl/Tk Conference (Tcl'2013)
http://www.tcl.tk/community/tcl2013/

September 23 - 27, 2013
Bourbon Orleans Hotel
New Orleans, Louisiana, USA

Important Dates:

Abstracts and proposals due   June  22, 2013
Notification to authors   August 5, 2013
Author materials due  September  2, 2013
Tutorials Start   September 23, 2013
Conference starts September 25, 2013

Email Contact:tclconfere...@googlegroups.com

Submission of Summaries

Tcl/Tk 2013 will be held in New Orleans, Louisiana, USA from September
23 - 27, 2013. The program committee is asking for papers and
presentation proposals from anyone using or developing with Tcl/Tk
(and extensions). Past conferences have seen submissions covering a
wide variety of topics including:

* Scientific and engineering applications
* Industrial controls
* Distributed applications and Network Managment
* Object oriented extensions to Tcl/Tk
* New widgets for Tk
* Simulation and application steering with Tcl/Tk
* Tcl/Tk-centric operating environments
* Tcl/Tk on small and embedded devices
* Medical applications and visualization
* Use of different programming paradigms in Tcl/Tk and proposals for new
  directions.
* New areas of exploration for the Tcl/Tk language

Submissions should consist of an abstract of about 100 words and a
summary of not more than two pages, and should be sent as plain text
to  no later than August 5,
2013. Authors of accepted abstracts will have until September 2, 2013
to submit their final paper for the inclusion in the conference
proceedings. The proceedings will be made available on digital media,
so extra materials such as presentation slides, code examples, code
for extensions etc. are encouraged.

Printed proceedings will be produced as an on-demand book at lulu.com

The authors will have 25 minutes to present their paper at the
conference.

The program committee will review and evaluate papers according to the
following criteria:

* Quantity and quality of novel content
* Relevance and interest to the Tcl/Tk community
* Suitability of content for presentation at the conference

Proposals may report on commercial or non-commercial systems, but
those with only blatant marketing content will not be accepted.

Application and experience papers need to strike a balance between
background on the application domain and the relevance of Tcl/Tk to
the application. Application and experience papers should clearly
explain how the application or experience illustrates a novel use of
Tcl/Tk, and what lessons the Tcl/Tk community can derive from the
application or experience to apply to their own development efforts.

Papers accompanied by non-disclosure agreements will be returned to
the author(s) unread. All submissions are held in the highest
confidentiality prior to publication in the Proceedings, both as a
matter of policy and in accord with the U. S. Copyright Act of 1976.

The primary author for each accepted paper will receive registration
to the Technical Sessions portion of the conference at a reduced rate.

Other Forms of Participation

The program committee also welcomes proposals for panel discussions of
up to 90 minutes. Proposals should include a list of confirmed
panelists, a title and format, and a panel description with position
statements from each panelist. Panels should have no more than four
speakers, including the panel moderator, and should allow time for
substantial interaction with attendees. Panels are not presentations
of related research papers.

Slots for Works-in-Progress (WIP) presentations and Birds-of-a-Feather
sessions (BOFs) are available on a first-come, first-served basis
starting in August 5, 2013. Specific instructions for reserving WIP
and BOF time slots will be provided in the registration information
available in June 3, 2013. Some WIP and BOF time slots will be held open
for on-site reservation. All attendees with an interesting work in
progress should consider reserving a WIP slot.

Registration Information

More information on the conference is available the conference Web
site (http://www.tcl.tk/community/tcl2013/) and will be published on
various Tcl/Tk-related information channels.

To keep in touch with news regarding the conference and Tcl events in
general, subscribe to the tcl-announce list. See:
http://code.activestate.com/lists/tcl-announce to subscribe to the
tcl-announce mailing list.


Conference Committee

Clif Flynt  Noumena CorpGeneral Chair, Website 
Admin
Andreas Kupries ActiveState Software Inc.   Program Chair
Gerald Lester   KnG Consulting, LLC Site/Facilities Chair
Arjen MarkusDeltares
Brian Griffin   Mentor Graphics
Cyndy Lilagan   Nat. Museum of Health & Medicine, Chicago
Donal Fellows   University of Manchester
Jeffrey Hobbs   ActiveState Software Inc.
Kevin Kenny GE Global Research Center
Larry 

Re: [sqlite] select max(x), y from table

[Petite Abeille]

On Feb 4, 2013, at 12:47 PM, Ryan Johnson  wrote:

> Otherwise, it looks like you want some variant of the "windowing" and "rank" 
> functions, which are non-standard features of postgres:
> http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Window functions (aka analytics) are standard as per SQL:2003.

Many databases implement them.

Sadly, SQLite is not one of them. Oh, well...



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

Re: [sqlite] populating a table as quickly as possible

[Dominique Pellé]

Paul Sanderson wrote:

> I want to populate a large table (millions of rows) as quickly as possible,
>
> The data set will not be operated on until the table is fully populated and
> if the operation fails I will be starting again. the operation is a one off
> and the table will not be added to at a future date.
>
> What are optimisations will work best for me?


Do you have only 1 table to populate or several tables?

If you have several tables, you could consider this:

* put your tables in different databases;

* perform the INSERT in different processes (1 per
  database) so tables can be populated in parallel;

* wait for all processes to finish;

* ATTACH all databases, so it it behaves as
  a single database.

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

Re: [sqlite] populating a table as quickly as possible

[Nigel Verity]

Hi
I had a similar issue a few months ago, only it was several hundred thousand 
records rather than millions. I found that dividing the job into  smaller 
transactions did definitely speed things up considerably. In my case I think I 
limited each transaction to 20,000 records.
I would definitely agree that you don't want to spend too much time 
experimenting with lots of different methods; diminishing returns and all that. 
Do test runs of, say, 50,000 records and then just go with the first that seems 
to provide "reasonable" performance.
Regards
Nige


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

Re: [sqlite] I suspect not - but is the database separator configurable?

[Ryan Johnson]

On 03/02/2013 10:16 AM, Stephen Chrzanowski wrote:

Just had a thought;

You could do a few things, unfortunately all at the code base level;

1> I don't know if Python will handle it, but I know most other languages
have a string-replace function, or, more specifically, in Delphi, there is
a "format" command in which you would create a string, fill it in with
parameters (Like %s, %d, etc) and then you have an array of variables the
system would fill in.  Create a variable that contains the SQL you need,
but replace what needs to be quoted with a format specifier.  Then,
regenerate the SQL with either quoted or non-quoted variables.  This would
become a PITA if you had 30 field names or so, however.  Not to mention bug
prone.

2> In line with a true string replace, replace all the field names in the
string to a format like '%db..table%' then run a string replace against all
the possible field names and replace them with either a quoted string, or a
non-quoted string.

3> Another option is to manually mangle the string based on what Sybase is
being fed.  So, if you're going to toss it into SQLite:
- scan the string for double-periods, and remember the position
- backup, character by character, until the first non-valid character, and
put a single/double quote
- go forward+1 from the remembered position until the first non-valid
character, and drop the same quote.

4> The last thing, off the top of my head, would be to locate each and
every SQL string, and move it into a file of its own, then duplicate for
SQLite and mangle as needed.  Along with option 1, nightmare to manage.

My thought, #3 would probably be the easiest to implement, as it'd require
one function to be created and an appropriate result returned then going
through the code and applying the function where needed, but it'd probably
the slowest since you'll be practically touching each and every character
in the string before its fed to the DBMS.  #2 would be next in line as a
search and replace based on field names would PROBABLY be more quicker
instead of mangling strings character by character, but it'd involve going
in and modifying the SQL calls themselves, as well as adding the
appropriate string replace commands.
Another option: wrap the appropriate functions from the various classes 
in the python sqlite3 module, and have them convert '..' to '.' in 
queries before passing on. You'd just have to make sure not to replace 
'..' appearing inside string constants.


Assuming you're lazy like me, and only issue sql using methods of the 
Connection object, that means interposing on Connection.execute and 
Connection.executemany; the Connection object has read-only attributes, 
so you'd probably need a wrapper class.


Ryan

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

Re: [sqlite] sorting two distinct groups

[Igor Tandetnik]

On 2/4/2013 2:40 AM, e-mail mgbg25171 wrote:

Your query is extremely close
but for the repetition of the c.ids at the end


I'm not sure I understand. What is the relationship of "firms" and 
"calls" tables? Is it one-to-many (multiple calls for each firm)? If so, 
what is the meaning of calls.last? Are you updating this field for all 
calls for a firm whenever a new call is inserted?


Perhaps you are looking for something like this:

select f.id from firms f left join calls c on (f.id = c.firm_id)
group by f.id order by min(c.last);

--
Igor Tandetnik

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

Re: [sqlite] select max(x), y from table

[Ryan Johnson]

On 03/02/2013 10:31 AM, Gabor Grothendieck wrote:

On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma  wrote:

Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven:



On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:


http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html


The query in that thread is of the form "select * from MyTable group by
Name having max(Timestamp)", and the expectation, somehow, is that the
HAVING clause would cause each group to be represented by a row for which
max(Timestamp) is reached. I'm not sure where this expectation comes from.
This is a valid SQL statement whose HAVING clause means "only include a
group in the resultset if max(Timestamp) for this group is logically true"
(that is, not NULL, 0, empty string or empty blob).

A semantic change of the nature you envision is not backward compatible -
it modifies the meaning of existing valid statements. Also, I'm pretty sure
it's not supported by any SQL standard; and I'm not aware of any DBMS that
would interpret the statement the way you want (which doesn't mean none such
exists, of course).

All in all, It seems unlikely that such a proposal would be entertained.
--
Igor Tandetnik


Hi,

Is it then not a perfect solution? it works, if in the example the timestamp
is always logically true (i.e nit 0 or NULL). Otherwise you might write
something like HAVING MAX(timestamp) OR MAX(timestamp) IS NULL.

This construction comes in useful to deal with the issue as was recently
observed with this featurre (see

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The question is still
if it will work also in future versions of SQLite.

What was wanted was to pick out the row with the largest timestamp in
each group (and not to pick out those groups with a non-null maximum
timestamp) so the fact that the desired result was returned in the
example would seem to be a  coincidence assuming no specific feature
along these lines has been implemented in SQLite.
select mytable.*, mt from mytable join (select id,name, max(timestamp) 
mt from mytable group by id,name) using(id)


Slightly cumbersome, but it works just fine... probably even fairly 
efficient thanks to use of a primary key index.


Otherwise, it looks like you want some variant of the "windowing" and 
"rank" functions, which are non-standard features of postgres:

http://www.postgresql.org/docs/9.1/static/tutorial-window.html

That said, I'm not sure how to express your query more efficiently even 
using the pgsql syntax, as I understand the latter.


Ryan

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

Re: [sqlite] sorting two distinct groups

[e-mail mgbg25171]

This seems to have answered part of my problem

SELECT id FROM firms f
left outer join
(select firm_id from calls group by firm_id) c
on f.id = c.firm_id
order by firm_id

On 4 February 2013 07:40, e-mail mgbg25171 wrote:

> Igor
> Thank you!
> Your query is extremely close
> but for the repetition of the c.ids at the end
>
> I've tried putting group by (c,id) but got an error
> Indeed my weakness seems to be having little idea of how to insert the
> stuff for a single query into compound queries.
> e.g.
> If I want to introduce an "order by clause" for the firms stuff I can't
> quite see how to insert it without again causing an error.
>
> Any advice greatfully received.
> Dean
>
>
>
> On 4 February 2013 03:40, Igor Tandetnik  wrote:
>
>> On 2/3/2013 3:43 PM, e-mail mgbg25171 wrote:
>>
>>> SELECT f.id FROM firms AS f
>>> WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL
>>> union
>>> SELECT f2.id FROM firms AS f2
>>> WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2)
>>> order by (c1.last is null, c2.last is not null)
>>>
>>> I have two tables firms and calls.
>>> I'd like to list all the firms for which no calls records exist
>>> then
>>> I'd like to list all firms for which calls records DO exist
>>> in order of earliest last call first
>>>
>>
>> select f.id from firms f left join calls c on (f.id = c.firm_id)
>> order by c.last;
>>
>> NULL compares less than any other value, so rows for which there are no
>> calls will sort at the top.
>> --
>> Igor Tandetnik
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users