gmane.comp.db.sqlite.general

Re: How do you access a specific row of a resultset?

Simon Slavin — 2012-05-16T22:27:23

On 16 May 2012, at 11:14pm, Goatjugsoup <goatjugsoup-PkbjNfxxIARBDgjK7y7TUQ< at >public.gmane.org> wrote:


SQLite is free to return rows in any order which satisfies your ORDER BY clause.  It is possible for the same SELECT statement, run twice in a row, with the same data in the table, to return its rows in a different order.  Two ways I can think of to make this happen are to create a new index, and to do an ANALYZE, but there are probably others which I haven't thought of.

If you want your rows to be returned in the same order every time, use an ORDER BY clause for your SELECT statement, and make sure the order you've asked for the rows gives every row a unique value.

Simon.

Re: How do you access a specific row of a resultset?

Pavel Ivanov — 2012-05-16T22:25:58

Only if you use ORDER BY clause, and the set of columns in this clause
have unique values for each row, and all these values don't change
between query executions.


Pavel


On Wed, May 16, 2012 at 6:14 PM, Goatjugsoup <goatjugsoup-PkbjNfxxIARBDgjK7y7TUQ< at >public.gmane.org> wrote:

Re: How do you access a specific row of a resultset?

Goatjugsoup — 2012-05-16T22:14:33

Well, a new thought just came to me, if I use the same sql statement will the
resultset be in the same order each time because if that is the case I
imagine I could use a for statement and an index to move through it the way
I want to with next inside of that as many times as it takes to get to where
I need.

Re: Please review this email to sqlite's mailing list

Charles Samuels — 2012-05-16T20:52:29

Sorry, I misspoke here a little bit and I think it's confusing you to the 
actual problem I'm having. To clarify: After I do a commit to "otherdb" - I'm 
*still* able to atomically roll it back, simply because I keep history of its 
previous versions in the sqlite db. I have it such that if the sqlite database 
itself rolls back, then the data in otherdb automatically does as well (it 
involves lots of COW pages and such). This is a solved problem.

The real problem is that while I'm waiting for "otherdb" to synchronize, more 
data is still coming in to this entire solution. Otherdb is able to take that 
data and put it in a "future version", but sqlite can't. After otherdb 
synchronizes (i.e., with fsync), I can commit the sqlite db and even if 
otherdb has more stuff of a "future version" added to it, the sqlite database 
causes that "future data" to be safely ignored.

However, while I can safely add to otherdb's "future data", there's no place 
to add it into SQLite, as I still haven't committed sqlite'

Re: Please review this email to sqlite's mailing list

Simon Slavin — 2012-05-16T19:20:26


You will find that in almost all situations where either of them failed, whatever method you're using to protect the integrity of your data will fail too, because it will depend on the same thing.  Not only that, but that your method of assuring synchrony is likely to add lots of complication which will make the whole system fail more than a simple one-SQL-engine implementation would.  For data integrity of the type you describe, there's nothing much you can do short of using duplicate servers talking to RAIDs with redundancy.

On 16 May 2012, at 7:40pm, Charles Samuels <charles-zqt5JYEDRmtBDgjK7y7TUQ< at >public.gmane.org> wrote:


You are inventing a versioning system for database rows.  This has been discussed a few times on this list, but never with any useful outcome in the form of code.  There's no reason it couldn't be done, but it can turn complicated fast.  Especially if you are going to deal correctly with multi-thread, multi-process or multi-user access.  Two standard approaches are savepoints and an

Re: Please review this email to sqlite's mailing list

Charles Samuels — 2012-05-16T18:40:31

Well, yes, but it already does; you can rollback a savepoint.

If I could rollback a savepoint and then unrollback it after doing an commit, 
that'd be the equivalent, if I could trick the paging module.


I'm trying that now, it involves temporary tables and views; but the 
bookkeeping for it is looking to be increasingly difficult and it is also going 
to make my own code unmaintainable.

I'm willing to pay the sqlite team to add this feature to a future of sqlite.

Charles

p.s. please excuse my Subject line.

Re: Please review this email to sqlite's mailing list

Igor Tandetnik — 2012-05-16T17:37:48

Well, SQLite doesn't. Basically, you want SQLite to maintain multiple 
not-yet-committed versions of the same record. I don't believe there's 
any way to get it to do that automatically - you'll have to come up with 
an explicit versioning scheme (like your idea of creating a temporary 
shadow database).


"Oh yes, I thought of something... but unfortunately, it rather involved 
being on the other side of this airtight hatchway." -- The Hitchhiker's 
Guide to the Galaxy.

Re: Query by License -Consulta por Licencia

2012-05-16T17:14:37

Guillermo:

Due to generous copyright conditions of its author DRH, you can use SQLite in any application you want and for any purpose.

HTH

Re: Please review this email to sqlite's mailing list

Charles Samuels — 2012-05-16T17:03:09

Yes, but this is incredibly hard: I have to maintain the schema twice, I can't 
allow the user to enter their own SQL statements, I still have to copy all the 
accumulated stuff over, and schema changes would be very difficult to handle.

Charles

Re: How do you access a specific row of a resultset?

Rob Richardson — 2012-05-16T15:24:48

The other thing you are not thinking about is that you never know the order of data in a set.  You may think that you want the 5th record that was ever inserted into the table, but you have no guarantee that a select statement will return records in the order in which they were inserted.  The 5th oldest record could come up first in the result set, or last, or anywhere else.

So, you either have to walk through the set from the beginning, checking every record to see if it's the one you want, or you're going to have to build a more precise select statement.

RobR

Re: Please review this email to sqlite's mailing list

Adam DeVita — 2012-05-16T14:10:57

Did you check out
http://www.sqlite.org/inmemorydb.html

Could you use an in-memory db to act as a db for a save point?

When you are ready to commit, do so from 1 in memory db, while accumulating
into another in preparation for that save point.

Adam

On Tue, May 15, 2012 at 1:55 PM, Charles Samuels <charles-zqt5JYEDRmtBDgjK7y7TUQ< at >public.gmane.org>wrote:

Re: How do you access a specific row of a resultset?

Igor Tandetnik — 2012-05-16T13:51:19

That rather depends on what "it" is. What's the point of the exercise? Are you looking to implement something like this, by any chance:

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

Re: import problem

Spora — 2012-05-16T13:14:47


Donald Griggs ha scritto quanto segue il 16/05/2012 13.24>

1744:
i have the last line as the previous.
I tryed with and without the carriage return but i have this error.

82886:
i have many " unclosed.
do it is possible to bypass the congruity check and import as in 
previous version in special cases?


 
 
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f
 
 Sponsor:
 Speciale ponte del 2 Giugno a Cesenatico all'Hotel Gallia: Pacchetto 2 giorni: Euro 154; Pacchetto 3 giorni: Euro 231. Pensione completa, servizio spiaggia, bevande ai pasti
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12343&d=16-5

Re: NULL sqlite3_stmt in function calls

Richard Hipp — 2012-05-16T12:48:00


No.  sqlite3_finalize() is, but most others are not.

Re: Query by License -Consulta por Licencia

Simon Davies — 2012-05-16T12:46:15

2012/5/16 Guillermo Soland <ggsoland-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org>:

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


Regards,
Simon

Re: Escape table and column names

Jean-Christophe Deschamps — 2012-05-16T12:38:58

At 13:55 16/05/2012, you wrote:
´¯¯¯
`---

Oops, that's what happens when you try doing too many things at the 
same time.

Nonetheless,

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table [foo [[bar]] ?] (a);
Error: unrecognized token: "]"

It would be logical to have ]] represent a single ] (standard escape by 
doubling), while [[ would be left as [[ since there's ne need to escape 
the opening bracket [.

But yes, this one works:

sqlite> create table `foo ``bar`` ?` (a);
sqlite>

Re: Accented chars in Android embeded Sqlite.

Richard Hipp — 2012-05-16T12:27:40


There is a file in the latest source tree named "test_spellfix1.c" which
includes an application-defined SQL function named
"spellfix1_translit(X)".  See
http://www.sqlite.org/src/artifact/495535f3eb57a?ln=1152-1171 for the
implementation.  The spellfix1_translit(X) function attempts to convert its
input string X into a pure ASCII transliteration.  In other words, it
removes the accented characters, replacing them with ASCII equivalents.
Example:

      SELECT spellfix1_translit('água');     -- returns agua

You could, perhaps, add such a function to your application, then use it to
accomplish what you describe above:

      SELECT * FROM table WHERE spellfix1_tanslit(column) LIKE 'a%';

Note that spellfix1 is not currently a part of any build and is
unsupported.  But you can use it as a guide to your own implementation.

Re: sqlite max query parameters differs on Snow Leopard?

Richard Hipp — 2012-05-16T12:19:51


The upper limit on the maximum number of query parameters is a compile-time
parameter that defaults to 999.  Apple raises the limit to 500,000 in their
builds of SQLite.

SQLite allocates an array to hold query parameters.  The size of the array
is the size of the largest parameter.  So you really don't want to be using
?500000 as that requires a large allocation, even if it is the only query
parameter used. The engineers at Apple are aware of this issue but still
choose to increase the limit to 500000 since apparently there exist legacy
applications on their platform that require this large limit.




To find the upper bound use:

    sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, 0x7fffffff);
    int upperBound = sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, -1);

import problem

Spora — 2012-05-16T10:19:02

I have problem when import with versione superior to 3071000.
No problem with 3071000.

..\sqlite3 -echo aa_VUOTO.db   0<aa.script
.separator "\t"
.import aa.txt aa
Error: aa line 1744: expected 5 columns of data but found 2
.import aa aa
Error: aa.txt line 82886: expected 9 columns of data but found 5
.exit
 
 
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f
 
 Sponsor:
 Fino al 2 Giugno una settimana Euro 310 nel Family Hotel King di Milano Marittima. Speciale settimana corta con arrivo la domenica e partenza al sabato Euro 270. Pensione completa, bevande ai pasti, terzo letto gratis, quarto scontato 50%, servizio spiaggia
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12340&d=16-5

Accented chars in Android embeded Sqlite.

Yahoo — 2012-05-16T01:23:25

Hi everyone!

Im Brazilian, also, Android Dev.

Im in a struggle with Sqlite and accented chars such as á or é.

What Im trying to do is a select with where accent insensitive.

For months I have having research over and over the internet to accomplish
that, but, unfortunately unsuccessful.

 

What I need is something like this:

select * from table where column like a%

and result be:

agora

água

através

and so on

 

Its possible? Or better to commit suicide ;-)

Thanks in advance

Youre all aces in my book.

Query by License -Consulta por Licencia

Guillermo Soland — 2012-05-16T00:35:49

Mr. Representative of SQLite:


Hello, my name is Guillermo G. Soland, I live in the city of Villa
Constitución, Santa Fe, Argentina, I graduated as a Computer Systems Analyst,
but now I do not practice my profession for profit because my job is totally
alien to what I studied , he wanted to see if I am allowed to include a file
SQLite database in an application that I developed, and if so if I'm allowed
to include with the application to apply for registration under any license,
either GPL or Copyright (preferably the latter).
Motivates this reason that I like to develop applications (as amateur) to
users or public organizations, usually non profit with the sale of software
(do not sell, are free), but I would ever write a history that I have I
performed.
From already thank you very much.



Signed:  Guillermo G. Soland







(Texto Original en Español)


Sr. Representante de SQLite:


Hola, me llamo Guillermo G. Soland,  vivo en la ciudad de Villa
Constitución, provincia de Santa Fe, Argentina, soy gra