Re: [sqlite] Is there a way to return the row number? (NOT the rowid)
Simon, Of course the rowid is THE field to use to uniquely identify a row (for edit, delete, etc...). I am talking about some ranking a result set. The ranking can be used for displaying (in HTML or a desktop GUI) or as source for insert/update sql. Of course in code you can have your own counter; what about writing a simple sql in the shell tool or any sql editor (I use SqliteSpy). There were lots of other questions about "re-assigning" the rowid to "natural" numbers (1..n); a solution would be to assign the rank to a field (another field, not the rowid). See the reply from James for the problem and a sql solution: http://www.schemamania.org/sql/#rank.rows How can this be efficient?? I don't use mysql, but Tony writes it can apparently do something like: SELECT @ROW:=@ROW+1 as ROW,* FROM table Regards, Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to return the row number? (NOT the rowid)
James, Indeed, that's the case. However, I can't imagine this to be efficient. It's just a pure sql workaround to a counter. The only advantage is, it is standard sql and should work with any engine. I wonder if sqlite would make some optimizations out of it, otherwise it's O(n^2) WHEN having an index for the sort field. Update: with no index, sqlite uses only one temp b-tree which would be needed anyway for sorting. Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] assertion in sqlite 3.7.17 for rather simple SELECT query
Am 01.07.2013 um 20:16 schrieb Richard Hipp: > On Mon, Jul 1, 2013 at 9:20 AM, Jens Miltner wrote: > >> I recently updated our SQLite source to SQLite source distribution 3.7.17. >> Since then, we get assertions when running one of our larger queries. I >> have stripped down the query to a bare minimum query that produces the same >> assertion: >> > > > Thanks for an providing such a clean and easy to reproduce bug report. > > A patch to fix this problem has been checked into trunk. ( > http://www.sqlite.org/src/info/72919ec34f). The patch should apply cleanly > to whatever version of SQLite you are using. > > At http://www.sqlite.org/draft/download.html you can find snapshots of the > version 3.8.0 alpha version that includes this patch. Thanks for the quick turnaround. I just verified that this patch fixes our problems. -jens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
The SQL standard has always been a moving feast, chasing the field implementations, perfectly capable of going back on it's earlier mistakes, the main purpose of which, on a good day, is to promote standardisation of SQL implementations and try and keep to the Relational Theory model where practical considerations allow. So, if the SQL standard has drifted toward requiring "… in the order in which they are defined in the table definition…" to be meaningful, then this is an oversight that would likely be corrected when somebody has an in the field SQL database which, correctly, enforces no such concept. People should not be encouraged to become more dependent on the use of such temporary misfeatures. In context, the particular focus of your objection to the relational approach, seems irrelevant. >> "sort by *" would imply that the order of the columns returned by '*' is >> meaningful, which it is not. "sort by the arbitrary order produced by >> 'select *'" isn't even deterministic. > > In SQL column order *is* deterministic, so the sort order would also > be deterministic. Likely meaningless, but still deterministic. Sort order isn't necessarily deterministic even if we know the column order. So the possibility that we may not know it, makes life no worse. On 2 Jul 2013, at 05:30, Jay A. Kreibich wrote: > On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the > wall: > >> "select *" is shorthand for "all columns". You'll note that what's >> returned isn't some kind of special '*' column, but all columns. The >> order in which the columns are returned isn't meaningful because the >> colums have labels -- names -- to tell you which is which. Rearranging >> the column order doesn't change the answer. > > That's not quite true. > > What you say is more or less true in pure Relational Theory. Under > Relational Theory, relational attributes (columns) are a proper set. > The columns have no defined order (just as rows have no defined > order), and can only be definitively reference by name. > > In SQL, columns are *not* a set. The order of the columns in any SQL > query or operation is strictly defined. Columns cannot be referenced > by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and > multiple columns with the same name (SELECT 1 A, 1 A, 1 A;). SQL > doesn't even strictly define the column name for a calculated column > (SELECT avg( 1 )) and allows the DB to make up its own names. SQLite > used to have several PRAGMAs to control short and long column names. > > Rather, in SQL, a column is definitively defined by its positional > index in the table or result set. This is also why so many SQL APIs > allow you to fetch column values by index, rather than by name (which > would be a totally broken and dangerous API if columns could move > around). It gets pretty messy... The SQL standard goes to some > length to define a specific column order for stuff like JOIN operations, > including edge-case details like NATURAL JOINs where the number of > columns is reduced and somewhat ambiguously named. > > While rearranging the column order may not functionally change the > answer, a database is not given that flexibility in SQL. For > example, "SELECT *" *must* return the columns in the order they are > defined in the table definition. It isn't that most databases just > happen to do this-- the column order is actually predicated by the > standard. > >> "sort by *" would imply that the order of the columns returned by '*' is >> meaningful, which it is not. "sort by the arbitrary order produced by >> 'select *'" isn't even deterministic. > > In SQL column order *is* deterministic, so the sort order would also > be deterministic. Likely meaningless, but still deterministic. > > > -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
Re: [sqlite] Another 2 questions about SQLite
On 2 Jul 2013, at 3:33am, James K. Lowden wrote: > Igor Tandetnik wrote: > >> On 6/30/2013 11:13 PM, Igor Korot wrote: >>> Well I will use another statement variable as in the sample code. >>> My questions was: if I call delete on the record that was just >>> retrieved in another query will this delete affects it? >> >> If you change data that a live SELECT statement is iterating over, >> the outcome is unpredictable. It may appear to work, it may skip >> rows, it may return some rows more than once > > Really? That's not what SERIALIZABLE means. > > If I have 10-row table T and issue > > select * from T; > > and from a different process issue > > delete * from T; > > you're saying the SELECT statement may fetch any number of rows > between 0 and 10? No. The SELECT statement is fine and consistent. But the SELECT statement is all of _prepare(), _step(), and _finalize(). Igor is pointing out that that if you stop before _step() has returned SQLITE_DONE then you haven’t /done/ a SELECT statement, you’ve just started one and it is still, in his word, "live". On 2 Jul 2013, at 4:30am, James K. Lowden wrote: > So I still don't see how the SELECT could be anything undeterministic. SELECT is deterministic. You want to know what it does, read the source code and understand the file system protocols it uses. But it’s not predictable in any simple way because its behaviour changes depending on lots of things like whether there’s a covering index, and what other threads do things with the database and when. So you can't write a simple set of rules that it’ll always follow: it does different things under different circumstances. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI for SQLite (nobre)
Hi Joe, It works quite good for me. It can be installed even from Mac App Store here: https://itunes.apple.com/us/app/valentina-studio/id604825918?ls=1&mt=12 BTW, you can look how it looks on Mac on this video: http://www.youtube.com/watch?v=1wAd1IrwKAs Although something should be improved yet. For example, it not reacts on horizontal scroll on trackpad. As I have hear they going switch to QT very soon, and this should resolve such mac issues. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Re-GUI-for-SQLite-nobre-tp69784p69799.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
[sqlite] UPDATE of many rows is very slow
Hello sqlite users,
I believe I have used many ways to speed up the UPDATE of many rows,
But nothing helped so far.
We are opening a transaction,
like this:
private SQLiteTransaction BeginTransaction(SQLiteConnection
connection)
{
return connection.BeginTransaction();
}
and close it at the end of course.
We open one connection, one transaction, and then we update many rows on the
database.
This means we are cumulate many different SQL statements over quite a few
tables .
One big part is the update of 12000 records in one table
protected override void UpdateRows(SQLiteConnection connection,
IEnumerable rowsToUpdate)
{
var command = new SQLiteCommand(Queries.SQLUpdateDocument,
connection);
foreach (DataRow documentRow in rowsToUpdate)
{
command.Parameters.AddWithValue("@Filename",
documentRow[Constants.Col_Document_Filename]);
command.Parameters.AddWithValue("@ClassID",
documentRow[Constants.Col_Document_ClassID]);
command.Parameters.AddWithValue("@PageCount",
documentRow[Constants.Col_Document_PageCount]);
command.Parameters.AddWithValue("@DocID",
documentRow[Constants.Col_Document_GlobalDocID]);
command.Parameters.AddWithValue("@ReadOnly",
documentRow[Constants.Col_Document_ReadOnly]);
command.Parameters.AddWithValue("@Confirmed",
documentRow[Constants.Col_Document_Confirmed]);
command.Parameters.AddWithValue("@ParentFolderID",
documentRow[Constants.Col_Document_ParentFolderID]);
command.Parameters.AddWithValue("@SequenceNumber",
documentRow[Constants.Col_Document_SequenceNumber]);
command.Parameters.AddWithValue("@XmlRepr",
documentRow[Constants.Col_Document_XmlRepr]);
command.ExecuteNonQuery();
documentRow.AcceptChanges();
}
}
with the query beeing:
UPDATE T_Doc SET
Filename = @Filename,
ClassID = @ClassID,
PageCount = @PageCount,
ReadOnly = @ReadOnly,
Confirmed = @Confirmed,
ParentFolderID = @ParentFolderID,
SequenceNumber = @SequenceNumber,
XmlRepr = @XmlRepr
WHERE ID = @DocID;
the AcceptChanges() does not take long.
The ExecuteNonQuery() is much slower than expected.
PRAGMA foreign_keys = ON;
has been executed before the update command and ClassID and ParentFolderID
reference other tables.
Anyways - the update is extremely slow, updating 12000 records takes like 15-30
minutes.
Can anybody help me with this?
Hinrich Aue
Sr. SW Engineer
Kofax Development GmbH
Wentzinger Strasse 19
79106 Freiburg
Germany
Tel: +49 761 452 69 57234
Fax: +49 761 452 69 58734
hinrich@kofax.com
NEWS FLASH: Kofax Among "Five Most Intriguing Companies at Finovate 2013 for
Mobile Customer Onboarding"
This communication is only for the use of the intended recipient. It may
contain confidential or proprietary information. If you are not the intended
recipient or have received this communication in error, please notify the
sender via phone and destroy this communication immediately.
Kofax Development GmbH
Sitz der Gesellschaft: Freiburg i. Brg.
Registergericht: Amtsgericht Freiburg i.Br.
Registernummer: HRB 7007
Gesch?ftsf?hrer: Bradford Weller, Christian Hefner, Daniel Geiger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI for SQLite
RSmith wrote > Well you need to "buy" a $0.00 registration key, for which you need to > fill in an inordinate amount of personal and contact > information (just like buying other things online). The sort of event that > promises Inbox spam aplenty and is too much hassle for > most casual onlookers - but it does promise some sort of interoperability > between SQLite and MySQL etc. > (Haven't used it, so just mentioning what it claims). > > > On 2013/06/26 17:34, Paolo Bolzoni wrote: >> So... no? It is gratis, but not open. thanks. >> >> On Wed, Jun 26, 2013 at 5:28 PM, Michael Black < > mdblack98@ > > wrote: >>> Free doesn't necessarily mean open source >>> > > ___ > sqlite-users mailing list > sqlite-users@ > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Actually if to install vstudio and start, it offers dialog register. I was asked only for name and email. In less of minute I have got letters with serials. IF you very worry about own security/spam you always can use nospam service and provide some fake name. -- View this message in context: http://sqlite.1065341.n5.nabble.com/GUI-for-SQLite-tp11673p69800.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
[sqlite] Sqlite3 Affinity
Few Query : 1) Is their any function or Method to get Affinity type associated with colum. 2) I understand Integer,Text & Float could satisfy all data type requirement.In that case where i can use blob Or Numeric type. Cheers - Techi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE of many rows is very slow
Hinrich Aue wrote:
> I believe I have used many ways to speed up the UPDATE of many rows,
> But nothing helped so far.
>
> We open one connection, one transaction, and then we update many rows on the
> database.
>
> var command = new SQLiteCommand(Queries.SQLUpdateDocument, connection);
> foreach (DataRow documentRow in rowsToUpdate)
> {
> command.ExecuteNonQuery();
Already answered on StackOverflow ("command.Transaction = transaction;" was
missing):
> This communication is only for the use of the intended recipient. [...]
This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public. If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails. Additionally, confidentiality notices in those e-mails
will incur legal processing fees of €42 per line; you have agreed to
this by reading this confidentiality notice.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 Affinity
There are sqlite3_column_decltype() and sqlite3_column_type() functions to determine the declared and actual types. You can determine affinity via the documentation: 2.1 Determination Of Column Affinity The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown: If the declared type contains the string "INT" then it is assigned INTEGER affinity. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity. Otherwise, the affinity is NUMERIC. Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER. -Ursprüngliche Nachricht- Von: techi eth [mailto:techi...@gmail.com] Gesendet: Dienstag, 02. Juli 2013 13:16 An: sqlite-users@sqlite.org Betreff: [sqlite] Sqlite3 Affinity Few Query : 1) Is their any function or Method to get Affinity type associated with colum. 2) I understand Integer,Text & Float could satisfy all data type requirement.In that case where i can use blob Or Numeric type. Cheers - Techi ___ 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
Re: [sqlite] Sqlite3 Affinity
1 - Using SQL there exists an SQLIte function typeof(x) where x can be a column name and it will return the type which is specific to the affinity and not to the original specified type when creating the table. If on the other hand you simply wish to know which type supports which affinity, this is well-documented in the SQLite site. http://www.sqlite.org/datatype3.html#affname 2 - The affinity does not really control how the data is stored, so it does not matter - it does however control (or shall I use the words "suggests strongly") how the output will be formatted. Numeric types are most notably used with Date/Time values which have proper text representation and formats but gets added to, subtracted from and generally modified in mathematical ways. (That's the short version, there are more to be said about it you can see the online docs). BLOB values on the other hand has no type, and no affinity - the SQL processor makes no attempt to work with them, modify or format them for any output (unless specifically requested via functions). The bytes get written/read straight into / out of the data files. Quite useful and quite quick - it DOES however require some bit of extra processing from your software, most notably having to Set an initial blob of n length into the db (fo which there are ver nice zeroblob() etc. functions) and then writing to that blob only the exact bytelengths specifed, etc. It's very well documented and very worth the effort. On 2013/07/02 13:15, techi eth wrote: Few Query : 1) Is their any function or Method to get Affinity type associated with colum. 2) I understand Integer,Text & Float could satisfy all data type requirement.In that case where i can use blob Or Numeric type. Cheers - Techi ___ 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] Sqlite3 Affinity
Please forgive my atrocious typo's - I never use a spellchecker for reasons of personal linguistic pride, which tend to bite me when replying in a hurry. Also, howcome no matter how much I force plaintext format on all mails, it returns as HTML? Does the mailing list have a format specifier for HTML or is my email client (Moz Thunderbird) up to some skullduggery on the downstream end? Thank you kindly On 2013/07/02 15:30, RSmith wrote: 1 - Using SQL there exists an SQLIte function typeof(x) where x can be a column name and it will return the type which is specific to the affinity and not to the original specified type when creating the table. If on the other hand you simply wish to know which type supports which affinity, this is well-documented in the SQLite site. http://www.sqlite.org/datatype3.html#affname 2 - The affinity does not really control how the data is stored, so it does not matter - it does however control (or shall I use the words "suggests strongly") how the output will be formatted. Numeric types are most notably used with Date/Time values which have proper text representation and formats but gets added to, subtracted from and generally modified in mathematical ways. (That's the short version, there are more to be said about it you can see the online docs). BLOB values on the other hand has no type, and no affinity - the SQL processor makes no attempt to work with them, modify or format them for any output (unless specifically requested via functions). The bytes get written/read straight into / out of the data files. Quite useful and quite quick - it DOES however require some bit of extra processing from your software, most notably having to Set an initial blob of n length into the db (fo which there are ver nice zeroblob() etc. functions) and then writing to that blob only the exact bytelengths specifed, etc. It's very well documented and very worth the effort. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting Constraints Details
For some reason I'm using a more complicated version of that same query for constraints. Perhaps its based on some older information that may have been in the table. SELECT type, sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' AND sql NOT NULL AND name='MyTable' danap. > Vijay Khurdiya wrote: >> How to get details of constraints associated with Data in SQLite3. > > There is no API to get individual properties; you have to look up the > original SQL statement: > > SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'MyTable' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
On Mon, 1 Jul 2013 23:30:10 -0500 "Jay A. Kreibich" wrote: > While rearranging the column order may not functionally change the > answer, a database is not given that flexibility in SQL. For > example, "SELECT *" *must* return the columns in the order they are > defined in the table definition. It isn't that most databases just > happen to do this-- the column order is actually predicated by the > standard. Thank you for the clarification; I didn't know the standard addressed that. I find the standard hard to read, even by the standard of standards. And, after all, it's pretty safe to ignore: that what most products do when it suits them! > > "sort by *" would imply that the order of the columns returned by > > '*' is meaningful, which it is not. "sort by the arbitrary order > > produced by 'select *'" isn't even deterministic. > > In SQL column order *is* deterministic, so the sort order would also > be deterministic. Likely meaningless, but still deterministic. Yes, I was musing about that today. "sort by *" could certainly be deterministic, depending on how it's defined. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to return the row number? (NOT the rowid)
On Tue, 02 Jul 2013 10:01:23 +0200 Gabriel Corneanu wrote: > > Ranking the rows requires nothing more than joining the table to > > itself. > Indeed, that's the case. However, I can't imagine this to be > efficient. It's just a pure sql workaround to a counter. I wouldn't call it a "workaround"; to me that means some bug or odd behavior than can be awkwardly dealt with. On the contrary, joining the table to itself to produce a rank is simply *using* SQL. The technique has other advantages. The rank can be within a group, not only for the whole set. And the rank can be referenced within the query, permitting joins and further grouping e.g. histograms. All to say: it's an algebra, use it! Codd was right to warn against magical columns. The strange thing is that the warning is still needed. > I wonder if sqlite would make some optimizations out of it, otherwise > it's O(n^2) WHEN having an index for the sort field. Hmm? Just as any join, it's O(n^2) *without* an index: every element has to be compared to every other. A binary index reduces that to O(N log N). Assuming the implementation doesn't take further shortcuts, which it's free to do. Now, there are people out there with truly impressive SQLite databases, but they know who they are and they know how to count the rows as received. For lots of applications, though, O(N log N) is indistinguishable from O(N), and the programming gets done sooner because it's in SQL. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
