[sqlite] Can i store DER encoded Certificate data in sqlite
Dear All, I am trying to store certificate related DER encoded data using sqlite database?? But what kind of datatype should i use in sqlite to store and retrive DER encoded data?? Is there anyway to store the DER encoded certificate related fileds in sqlite?? Thanks in advance.. Samrat - Original Message From: Igor Tandetnik <[EMAIL PROTECTED]> To: SQLite <[EMAIL PROTECTED]> Sent: Thursday, April 12, 2007 5:12:51 PM Subject: [sqlite] Re: Encoding confusion Anders Persson <[EMAIL PROTECTED]> wrote: > What is confusing me is how are the string stored, > if i understand corrent it is UTF-8 is this done automatic > and what coding is a get back when a extra data ? SQLite database may store string data in UTF-8, UTF-16le or UTF-16be encodings. The encoding is determined when the first table in the database is created, and cannot be changed afterwards. See "PRAGMA encoding" at http://sqlite.org/pragma.html When you extract the data, it is automatically converted, if necessary, to UTF-8 or UTF-16 depending on whether you use sqlite3_column_text or sqlite3_column_text16. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/
RE: [sqlite] Help with SQL syntax. Ticket #2296
Still, I think backwards compatibility and consistency with other databases would be most important in this situation. I just checked MSSQL and it's same as current sqlite which uses the first select statement's column names. It doesn't just affect order by too.. based on the standard SQLite should be returning a different column name entirely in the result set. Surely it would break a lot of code out there if SQLite suddenly started returning seemingly random column names from union queries when the query didn't specifically alias the columns. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 11:09 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > Wouldn't implementation dependent mean it's not really standardized? The > way I read it the query could still be considered legal in some dbms and > not > in others (which stinks). Samuel, That's not what the standard says. It says the name assigned to the result columns are implementation dependent, they could be sqlite_column_1 and sqlite_column_2, or perhaps cnnn where nnn is a random number, but they can not be the names of any of the columns in any of the tables in the query. The query should generate a syntax error because it is trying to sort on columns that are not, or at least should not be, present in the result. Besides, the current version of SQLite seems to match on the first tables > names which is consistent with expectations from other databases and not > prohibited by the standard (in the way I read it) and backwards > compatibility seems to be the most important thing here.. > > This behavior is prohibited by the standard. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
--- [EMAIL PROTECTED] wrote: > Consider this query: > >SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; > > Is the query above equalent to: > > (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; > > Or is it the same as: > > (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; Most databases use form (1). SQLite already leans in this direction anyway, using the first select in a union for the headings. May as well be consistant. SQLite version 3.3.15 Enter ".help" for instructions sqlite> .header on sqlite> select 3 a union select 4 b; a 3 4 8:00? 8:25? 8:40? Find a flick in no time with the Yahoo! Search movie showtime shortcut. http://tools.search.yahoo.com/shortcuts/#news - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Wouldn't implementation dependent mean it's not really standardized? The way I read it the query could still be considered legal in some dbms and not in others (which stinks). Samuel, That's not what the standard says. It says the name assigned to the result columns are implementation dependent, they could be sqlite_column_1 and sqlite_column_2, or perhaps cnnn where nnn is a random number, but they can not be the names of any of the columns in any of the tables in the query. The query should generate a syntax error because it is trying to sort on columns that are not, or at least should not be, present in the result. Besides, the current version of SQLite seems to match on the first tables names which is consistent with expectations from other databases and not prohibited by the standard (in the way I read it) and backwards compatibility seems to be the most important thing here.. This behavior is prohibited by the standard. Dennis Cote
RE: [sqlite] Help with SQL syntax. Ticket #2296
Wouldn't implementation dependent mean it's not really standardized? The
way I read it the query could still be considered legal in some dbms and not
in others (which stinks).
Besides, the current version of SQLite seems to match on the first tables
names which is consistent with expectations from other databases and not
prohibited by the standard (in the way I read it) and backwards
compatibility seems to be the most important thing here..
sqlite> create table t(a text, b text);
sqlite> insert into t values('one', 'two');
sqlite> insert into t values('three', 'four');
sqlite> select a, b from t union select b,a from t;
a b
-- --
fourthree
one two
three four
two one
sqlite> select a, b from t union select b,a from t order by a,b;
a b
-- --
fourthree
one two
three four
two one
sqlite> select a, b from t union select b,a from t order by b,a;
a b
-- --
three four
two one
fourthree
one two
sqlite>
So from this example seems like what we really need is to teach SQLite how
to count... four, one, three, two.. sheesh. :-)
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 12, 2007 6:05 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296
...
Otherwise, the of the i-th column of TR is implementation
dependent
and not equivalent to the of any column, other than
itself, of any table referenced by any contained in
the SQL statement.
...
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Data structure
At 17:35 11/04/2007, you wrote: >Lloyd wrote: >> >>Sorry, I am not talking about the limitations of the system in our side, >>but end user who uses our software. I want the tool to be run at its >>best on a low end machine also. >>I don't want the capabilities of a data base here. Just want to store >>data, search for presence, remove it when there is no more use of it. >>Surely I will check out BerkeleyDB. The data set must be in ram, because >>the total size of it is very small. (Few maga bytes) I just want to >>spped up the search, which is done millions of times. >>Thanks, >> LLoyd >You might discover that you can craft a very effective memory resident storage >system using a compression system like Huffman Encoding and an index method >appropriate to the key you are using for retrieval. That could work very well >in an embedded system, have a small footprint in data and code and be very >fast. There is a book about that (2 books) called Managing Gigabytes (1 and 2 editions) which shows how to use compression techniques with data search. The full source code is open source (or i remember it was). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
At 7:22 PM + 4/12/07, [EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. Alternately, attempting to run that could just be made to fail with an error citing ambiguity. Then users can make it work by making sure the columns they are unioning have the same names (and hence, so does the result of the union), such as by using 'AS' in the select-list. Unless you are explicitly trying to accept ambiguous syntax just because other DBMSs do (to aid portability), that is the simplest option. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
At 9:48 AM -0600 4/12/07, Dennis Cote wrote:
Yes I did assume no coupling because you didn't suggest any. If
there is coupling this is just another case of the second example.
While I didn't explicitly suggest coupling before, I was making my
arguments on the general case where actions against a database may
possibly be coupled, and my argument was towards solutions that work
for the general case. Sorry if I didn't communicate before that I
was speaking to the general case.
So put all the sub-steps in a subroutine and call it.
In the general case, I don't control what the sub-steps are, but I am
being a proxy for someone else, and I don't know in advance what they
would ask for. Also, as users may want data returned to them between
the sub-steps, their use for which could include determining what
sub-steps are, I can't just generate a subroutine at runtime to
execute, as then they wouldn't get anything back from their
intermediate queries on time. That said, I recognize that in some
situations it is possible for the stored procedure to embed all the
decision making logic necessary from the application, but this isn't
always true, as eg some user may be involved in intermediate steps.
I think that a SQLite pager-based mechanism for tracking child
transactions is quite a bit less complicated and more reliable than
using your workaround, since no details have to be remembered but
for the pages that changed.
That is not true and you know it.
On the contrary, I believe what I said.
You are just pushing the complexity back to Richard. He will have to
implement the changes to the parser, code generation, pager layers,
and test suite, as well as address the backwards compatibility
issues.
I don't see this as a problem. While it is true that a lot of
complexity can be layered on top of the DBMS rather than being
internal to the DBMS, I see child transactions as something that is
best implemented inside the DBMS.
Speaking in a very loose analogy, I see the complexity as SQLite is
now compared to with child transaction support to be like replacing:
foo();
foo();
With:
for ... {
foo();
}
That is, I see it as the difference between explicitly doing
something twice, and doing it once but inside a loop.
So as one can refactor code to use loops rather than explicit
repeating, I don't see the end result here being much larger or more
difficult to maintain. That is, we aren't just adding code, but also
taking away some that has become redundant, is how I conceptualize it.
So SQLite with child transactions is only trivially less lite than
it is now, which is still lite.
If it is a trivial as you suggest, then you should have already
prepared a patch. :-)
I wasn't saying that the patch itself was trivial (though I'm saying
it should be a simpler than the patches for many other requested
features), but rather that the measures of how "lite" SQLite is would
change a trivially small amount between before and after.
In fact, I propose moving rollbackable child transaction support to
the top of the todo list, rather than it being in the middle, given
that its presence can make a lot of other todo or wishlist items
much easier to implement, I believe.
And if it will make a difference, I will even make a monetary
donation (as I can afford to) in order to sponsor its development
(though I would like to think that the benefits are compelling on
their own).
You will have to discuss this with Richard Hipp.
Yes, of course. And I already did do that a few minutes after the list post.
How will nested transactions make creating a your wrapper easier?
Please be specific.
Well, to help people better understand this, I should start but
outlining my own connected work.
I am writing a free and open source RDBMS of my own, whose main
innovations relative to the general DBMS field are in the query
engine, namely the public face (programmatic API and query language)
that application developers and their users interact with. My RDBMS
has its own query language and feature set which overlaps with but
isn't the same as that of existing SQL DBMSs.
My RDBMS is structured as a framework with separate public interface
and backend implementation layers (called "Interface" and "Engine),
such that the backend is a swappable plugin-style component. The
"interface" or wrappers thereof handle parsing user queries into an
standardized AST format, which is what an "Engine" takes as input and
the engine implements the AST-defined query however it wants. The
native language and AST of my RDBMS define rigorous semantics which
users should be able to expect, and which an Engine is supposed to
comply with.
Note that a single query in my language is a full-blown routine
definition (which in the trivial case just contains a single
statement), so what it does and what format of data it can process
for input or output is arbitrarily complex.
Re: [sqlite] Help with SQL syntax. Ticket #2296
[EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. Richard, If my reading of the SQL:1999 standard is correct (its more than a little convoluted in this area), then from section 7.12 expression> Syntax Rule 16 case b)i)1 we have: Let C be the of the i-th column of T1. If the name> of the i-th column of T2 is C, then the of the i-th column of TR is C. where T1 and T2 are the tables being operated on by the UNION and TR is the result table. This case covers the normal case of matching column names. The next case 16)b)i)2 covers the case of non matching column names: Otherwise, the of the i-th column of TR is implementation dependent and not equivalent to the of any column, other than itself, of any table referenced by any contained in the SQL statement. This says that the output column names are neither a or b, but something else. This would imply that the first query above is in fact a syntax error since the output table does not have columns named a or b, and therefore it can't be ordered by those columns. Either of the second two queries should be legal, since they do not use column names for the order by clause. To generate a legal query the user would have to alias the column names in one or both of the sub queries. SELECT a, b FROM t1 UNION SELECT t1.b as a, t1.a as b from t1 ORDER by a, b SELECT t1.a as b, t1.b as a FROM t1 UNION SELECT b, a from t1 ORDER by a, b SELECT a as c, b as d FROM t1 UNION SELECT b as c, a as d from t1 ORDER by c, d HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Gerry,
I took a look at this and I don't see how it works.
I believe I would have to do the following:
Make TEMP copies of all of the tables that are being modified.
Upon COMMIT:
Delete the old tables
Rename the temp tables to the old tables
COMMIT
I don't see an easy way to do this in SQLite.
Keep in mind I am a newbie to database access.
Ray
Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > Here is an excerpt from my requirements doc:
> >
> >
> > If a transaction is aborted all database state is reset
> ("rolled-back") to its value at the time the transaction was opened.
> > Nested transactions abort or commit relative to their
> parent transaction. In the case of an aborted transaction,
> the database state is rolled back to the point where
> the transaction was started. (This is true whether
> or not the transaction is nested.) In the case of
> a commit, the nested transaction’s changes become
> part of its parent transaction, as if the nested
> transaction boundaries had never been established.
>
> If I had to implement this with the current SQLite,
> I would start a new TEMP table at each BEGIN, and
> destroy the current TEMP table at a ROLLBACK or
> add its contents to its parent table (either
> the previous TEMP table or the "real" table at a
> COMMIT.
>
> I think that does everything you need, and would be
> easy to program.
>
> HTH,
>
> Gerry
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Help with SQL syntax. Ticket #2296
Andy's answer and explanation is consistent with my experience and expectations too.. mostly from MSSQL and Access background. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 3:42 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 My understanding is: select a, b from t1 union select b, a from t1 is equivalent to select a as a, b as b from t1 union select b as a, a as b from t1 And therefore, the first sql statement controls the resulting column names, and the order by applies to the column names (transitively) from the first statement. I'll find a reference in SQL89/SQL92 to support my understanding, but this is how Oracle behaves. :) --andy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Thanks. I'll look into this path and see if it fits.
Anybody here live in the Irvine, Ca area.
After the discussions on this subject I have come to the conclusion that we
need somebody to implement our embedded database.
Ray
Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > Here is an excerpt from my requirements doc:
> >
> >
> > If a transaction is aborted all database state is reset
> ("rolled-back") to its value at the time the transaction was opened.
> > Nested transactions abort or commit relative to their
> parent transaction. In the case of an aborted transaction,
> the database state is rolled back to the point where
> the transaction was started. (This is true whether
> or not the transaction is nested.) In the case of
> a commit, the nested transaction’s changes become
> part of its parent transaction, as if the nested
> transaction boundaries had never been established.
>
> If I had to implement this with the current SQLite,
> I would start a new TEMP table at each BEGIN, and
> destroy the current TEMP table at a ROLLBACK or
> add its contents to its parent table (either
> the previous TEMP table or the "real" table at a
> COMMIT.
>
> I think that does everything you need, and would be
> easy to program.
>
> HTH,
>
> Gerry
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Help with SQL syntax. Ticket #2296
My understanding is: select a, b from t1 union select b, a from t1 is equivalent to select a as a, b as b from t1 union select b as a, a as b from t1 And therefore, the first sql statement controls the resulting column names, and the order by applies to the column names (transitively) from the first statement. I'll find a reference in SQL89/SQL92 to support my understanding, but this is how Oracle behaves. :) --andy On 4/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Help with SQL syntax. Ticket #2296
Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Null row detection when doing sqlite3_step
Thanks. Igor Tandetnik wrote: > > pompomJuice <[EMAIL PROTECTED]> wrote: >> Basically I am looking for somthing simular to oracle's code 1403 >> where a query returned zero rows. > > If a resultset is empty, the very first call to sqlite3_step would > return SQLITE_DONE (normally it would be SQLITE_ROW). That's your cue. > >> How do I know when step resulted in >> zero rows without checking each column value that the query returned. > > You cannot check each column value. It is only legal to call > sqlite3_column_* when the previous sqlite3_step call returned > SQLITE_ROW. In this case, it returns SQLITE_DONE instead. > > Igor Tandetnik > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Null-row-detection-when-doing-sqlite3_step-tf3565781.html#a9965764 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Building Test Fixture under Visual Studio 2005
I am trying to build the test files under VS2005
I've created a new project and added all the test files with additional
libraries for TCL and the sqlite3 libraries.
All compiles fine, with the exception of the following errors
Error 21 error C2491: 'Sqlite3_Init' : definition of dllimport
function not allowed
Error 22 error C2491: 'Tclsqlite3_Init' : definition of dllimport
function not allowed
Error 23 error C2491: 'Sqlite3_SafeInit' : definition of
dllimport function not allowed
Error 24 error C2491: 'Tclsqlite3_SafeInit' : definition of
dllimport function not allowed
The offending lines are
tclsqlite.c:
EXTERN int Sqlite3_Init(Tcl_Interp *interp){
Tcl_InitStubs(interp, "8.4", 0);
Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0,
0);
Tcl_PkgProvide(interp, "sqlite3", PACKAGE_VERSION);
Tcl_CreateObjCommand(interp, "sqlite", (Tcl_ObjCmdProc*)DbMain, 0, 0);
Tcl_PkgProvide(interp, "sqlite", PACKAGE_VERSION);
return TCL_OK;
}
EXTERN int Tclsqlite3_Init(Tcl_Interp *interp){ return
Sqlite3_Init(interp); }
EXTERN int Sqlite3_SafeInit(Tcl_Interp *interp){ return TCL_OK; }
EXTERN int Tclsqlite3_SafeInit(Tcl_Interp *interp){ return TCL_OK; }
What step / configuration param for VS2005 I forgetting?
Regards,
Noah Hart
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
Autonomous transactions: ie begin begin autonomous txn1 commit; commit txn1 This transaction has no impact on the outer txn. I believe it can commit either in or out of the parent transaction as well Quite a bit more complicated and really requires the concept of a transaction ID. Maybe this is specific to the DB vendor (ORA$$) I totally agree, in more than 20 years of commercial db development and DBA work, I've only encounterd the use of savepoints 1 time. Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > Correct me if I'm wrong on this concept: > Adding nested transactions really means adding the ability to demark > internally a transaction ID. So that later that transaction can be rolled > back. > > Consider > begin Main; > step a > savepoint loc1 > step 1 > savepoint loc2 > step 2 > rollback loc2 <- Rolls back step2 > step 2a > savepoint loc3 > step 3 > commit ; > >(result: step a, step 1, step2a and step3 ) > > I think the concept of a savepoint is simpler than a truely nested > transaction. As one doesn't actually need to start a new transaction just > mark a position where a savepoint rollback would stop. Savepoints then are > not really nested transactions but just markers that indicate when to stop > rolling back within the journal file. > Ken, As far as I understand it the two concepts are fundamentally the same. Savepoints can be implemented using simply nested transactions. The savepoint syntax is what is used by the SQ:1999 and later standards. > > But savepoints are usefull in special situations. > Yes they are, but those situations are really quite rare in the real world. > >Instead of Nested Transactions, What about the concept of an autonomous > transaction? > > I don't know what you mean by autonomous transactions as opposed to normal SQL transactions. Can you explain the difference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance analysis of SQLite statements
What do you want to measure? Jonas Sandman wrote: Anyone know a good bench-marking (preferably free or cheap) which can be used to benchmark C/C++ code in Windows? Best regards, Jonas On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Are there any tools to help analyze the performance of components with a particular SQLite statement? I'm aware of the EXPLAIN option which can show what VBDE code was used to execute a statement, but afaik there is no way to tell the time each step took. Basically I want to know how long the different components of a single SQL statement took relative to the whole statement. Provide for more fine-grained analysis than just comparing two SQL statements based on total execution time. Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 11, 2007 8:02 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Select columns & performance ... Do a lot of benchmarking. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite join-mechanisms question
Thank very very much That is the information that i was searching for! ;) On 12/04/07, Dennis Cote <[EMAIL PROTECTED]> wrote: Cesar Rodas wrote: > Thanks for the answer mister Hipp, but I am searching an SQL help. > I'd like > to know how is the SQLite join algorithm or where i could find it (in > what > ".c" file). > > Thank for the help! ;) > Cesar, You should check out the slide show at http://www.sqlite.org/php2004/page-001.html The discussion of indexes starts about slide 40 and joins are on slide 57. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] Data structure
We use a very simple data retrieval method for smallish datasets. The data is just stored in memory or as a memory mapped file and a sequential search used. It sounds crude but when you use a fast search algorithm like Boyer-Moore it outperforms index methods up to a surprisingly large number of records. As you can imagine the code footprint is miniscule and if you add regular expression logic you can realize very intricate search patterns. We use the method in conjunction with a database to achieve an enormous speed increase on "LIKE" type searches. Grep a few files to get a feel for the performance. Another method which works well for memory resident storage is to implement self balancing AVL trees. The code is simple and the performance lightning fast. With a little ingenuity you can use disk storage. Mini Sql (MSql) is a good example of how this can be effective. As Einstein said - "Make it as simple as possible, but not too simple". Applying Occam's Razor can turn bloated solutions into more effective lean ones. Typical solutions come in two sizes just like Army boots - too big and too small. Lloyd wrote: Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: You might discover that you can craft a very effective memory resident storage system using a compression system like Huffman Encoding and an index method appropriate to the key you are using for retrieval. That could work very well in an embedded system, have a small footprint in data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Data structure
valgrind -Original Message- From: Lloyd [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 12:26 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Data structure Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance analysis of SQLite statements
> > You used to be able to compile with -DVDBE_PROFILE=1 to enable some > > special assembly-language instructions that would use > hi-res timers on > > ix586 chips to provide the cycle counts needed to execute each > > instruction in a VDBE program. But I haven't used that feature in > > years so I don't know if it still works or not. > > If you are talking of the "rdtsc" instruction, then to work > with current dual core CPUs the test must be "pinned" to a > single CPU, or you could be reading TSC values from different CPU's. > > Other than that, as long as the code works on a "single-core" > CPU, it should work on newer ones. > > Off course, there is that word: "should" ;-) > I would add that if using windows I recommend using QueryPerformanceCounter() instead of "rdtsc" as this function/call already handles the above mentioned issue which affects multiprocessor/multicore systems. In a previous job I did some performance metrics since I presumed it would be slow but remember being pleasantly surprised that it was as fast (circa 4 clock cycles to retrieve and store in integer) as using assembler code. This was using VS C++ 2003 compiler. I'm not aware of a similar call in Unix Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal files not deleted.
Guilty of extending this silly thread... On 4/12/07, Noah Hart <[EMAIL PROTECTED]> wrote: Not to quibble, but to quote from wipro's website "Only Indian company to be ranked among the top 10 global outsourcing providers in IAOP's 2006 Global Outsourcing 100 listing" you are indeed quibbling by pointing out this absolutely useless piece of trivia. For that matter, nowhere on Wipro's website do they say that they are NOT the largest SW IT companies, and I took Ravi's "NO" to not deny that they are not an outsourcing shop but that they are not _just_ an outsourcing shop. In any case, none of this has anything to do with what the poor bloke was asking in the first place. All he wanted to know was about the journal files that were not getting deleted. Here is the original messge -- The journal files created during the transactions are not getting deleted. These files are created per transaction or only one file per connection? I am seeing hundreds of journal files with a '-' suffix causing an error "Database disk full" (DB file is stored in flash card). Note : I am closing the file after every transaction. Something like this. Sqlite3_open(); Sqlite3_exec(); Sqlite3_close(); Sheesh... answer an email or, if not, at least make a funny observation, not one that makes others defensive. Else, just delete it and go your way. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 10:26 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [sqlite] Journal files not deleted. NO, This is one of the largest IT (SW development) companies. Does this matter in anyways? Regards, Ravi K -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 8:41 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Journal files not deleted. [EMAIL PROTECTED] wrote: > www.wipro.com Would this be Wipro, the outsourcing company? Martin CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal files not deleted.
The original question was something about journal files not being deleted Nobody else has reported seeing this behavior. If you want help, you will have to give us additional information. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Journal files not deleted.
Not to quibble, but to quote from wipro's website "Only Indian company to be ranked among the top 10 global outsourcing providers in IAOP's 2006 Global Outsourcing 100 listing" NH -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 10:26 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [sqlite] Journal files not deleted. NO, This is one of the largest IT (SW development) companies. Does this matter in anyways? Regards, Ravi K -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 10, 2007 8:41 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Journal files not deleted. [EMAIL PROTECTED] wrote: > www.wipro.com Would this be Wipro, the outsourcing company? Martin CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite join-mechanisms question
Cesar Rodas wrote: Thanks for the answer mister Hipp, but I am searching an SQL help. I'd like to know how is the SQLite join algorithm or where i could find it (in what ".c" file). Thank for the help! ;) Cesar, You should check out the slide show at http://www.sqlite.org/php2004/page-001.html The discussion of indexes starts about slide 40 and joins are on slide 57. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance analysis of SQLite statements
Jonas Sandman wrote: Anyone know a good bench-marking (preferably free or cheap) which can be used to benchmark C/C++ code in Windows? It's not free, but AQTime http://www.automatedqa.com/products/aqtime/ from AutomatedQA is quite good and supports most compilers under windows. You can download a free trial. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Darren Duncan wrote: At 3:33 PM -0600 4/11/07, Dennis Cote wrote: You have lost me here. If this transaction is considered successful without executing the middle step (which is the same as executing it and then undoing that execution) then that step does not belong in this transaction. Instead of begin step 1 savepoint after_1 optional step 2 if error rollback to savepoint after_1 step 3 commit You can do begin step 1 step 3 commit begin optional step 2 if error rollback else commit Your example assumes no coupling between the different steps, which is true some times, but in the general case there can be coupling. That is, the effect that step 3 actually has can be different depending on whether step 2 is rolled back or not, either because step 3 is operating on a different database state, or because step 3 contains conditionals that cause different statements to execute depending on database state that could have been changed by step 2. So in the general case, step 2 must always be run after step 1 and before step 3. Darren, Yes I did assume no coupling because you didn't suggest any. If there is coupling this is just another case of the second example. This *is* an example of an application where a nested transaction or a savepoint could be useful. However there is a fairly simple workaround that gets the same result without a nested transaction. Instead of: begin step 1 begin nested temp step 2 var = query current state rollback nested step 3 using var commit You could do: begin step 1 temp step 2 var = query current state rollback begin step 1 step 3 using var commit The cost of this approach is repeating the work done in step 1. While that workaround may be an acceptable solution for some situations, I see that as overly complicated and difficult in the general case. For one thing, it requires the application to keep track of all the details of what step 1 was, and step 1 could be arbitrarily complex. So put all the sub-steps in a subroutine and call it. Moreover, step 1 could have been expensive, involving a large amount of data which may have been input from somewhere and can't be retrieved again nor stored in RAM; the only copy of it is in the database. You can always manufacture an example where nested transactions are useful. My point is that these situations are rare in real world applications. Or even ignoring the last point there is still the complexity, especially if one used bind variables that were since freed up for other tasks, since you aren't just keeping a log of SQL strings to re-run. Again subroutines solve this issue quite handily. I think that a SQLite pager-based mechanism for tracking child transactions is quite a bit less complicated and more reliable than using your workaround, since no details have to be remembered but for the pages that changed. That is not true and you know it. You are just pushing the complexity back to Richard. He will have to implement the changes to the parser, code generation, pager layers, and test suite, as well as address the backwards compatibility issues. How much more complicated is the nested transaction solution if *you* have to implement it? Now going off on a tangent ... To address the oft-raised comment that some people make that any proposed additions or changes to SQLite be opposed in general on the principal that "it is supposed to be lite", I say this: It is perfectly in keeping with SQLite's philosophy for us to add lots of power to it if the cost of adding that power is low. Low cost meaning that the codebase doesn't need to increase much if any, the resource efficiency of running SQLite isn't impacted much, complexity doesn't raise the bug density appreciably, and particularly, it isn't more difficult for users to use. Some features, like proper child transactions as I described, are a situation where users gain a lot of power at very little cost. Having proper child transactions means it is a lot easier for users and developers, particularly SQLite extension or wrapper writers such as myself, to add powerful features to SQLite using programs while SQLite itself is hardly more complex. Users are saved a lot of work, and SQLite developers gain next to none. By contrast, say, supporting named users in the database and concurrent database writes and stuff like that is indeed way too complicated for SQLite to have, and I still support SQLite never adding support for it. So SQLite with child transactions is only trivially less lite than it is now, which is still lite. If it is a trivial as you suggest, then you should have already prepared a patch. :-) How will nested transactions make creating a your wrapper easier? Please be specific. In fact, I propose moving rollbackable child transaction support to the top
Re: [sqlite] Performance analysis of SQLite statements
On 4/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: You used to be able to compile with -DVDBE_PROFILE=1 to enable some special assembly-language instructions that would use hi-res timers on ix586 chips to provide the cycle counts needed to execute each instruction in a VDBE program. But I haven't used that feature in years so I don't know if it still works or not. If you are talking of the "rdtsc" instruction, then to work with current dual core CPUs the test must be "pinned" to a single CPU, or you could be reading TSC values from different CPU's. Other than that, as long as the code works on a "single-core" CPU, it should work on newer ones. Off course, there is that word: "should" ;-) Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Looking for sqlite3_fds.h and fds.c
I have a package which uses functions like sqlite3_get_database_file_fd() and sqlite3_get_journal_file_fd(). Supposedly sqlite3_fds.h has these two functions and fds.c defines them, but I searched both sqlite-3.3.4 and sqlite-3.3.15, and could not find anything. Does anyone know where I can find sqlite3_fds.h and fds.c? Thanks. Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Ken wrote: Correct me if I'm wrong on this concept: Adding nested transactions really means adding the ability to demark internally a transaction ID. So that later that transaction can be rolled back. Consider begin Main; step a savepoint loc1 step 1 savepoint loc2 step 2 rollback loc2 <- Rolls back step2 step 2a savepoint loc3 step 3 commit ; (result: step a, step 1, step2a and step3 ) I think the concept of a savepoint is simpler than a truely nested transaction. As one doesn't actually need to start a new transaction just mark a position where a savepoint rollback would stop. Savepoints then are not really nested transactions but just markers that indicate when to stop rolling back within the journal file. Ken, As far as I understand it the two concepts are fundamentally the same. Savepoints can be implemented using simply nested transactions. The savepoint syntax is what is used by the SQ:1999 and later standards. But savepoints are usefull in special situations. Yes they are, but those situations are really quite rare in the real world. Instead of Nested Transactions, What about the concept of an autonomous transaction? I don't know what you mean by autonomous transactions as opposed to normal SQL transactions. Can you explain the difference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Ramon Ribó wrote: I cannot agree here. Just imagine that the user decision is based on the imported data. Of course, you can read the data of the file, store in temporal structures on memory, ask the user and then, enter the data into the database. But the advantage of using sqlite as storage mechanism, as drh pointed out, is to use it as file format, an avoid creating intermediate data structures. Ramon, In order to insert any data into an sqlite database you have to have it in memory in some intermediate data structure. All I have suggested is querying the user before you do the insert instead of after, so that you don' t need to undo the insert if the user does not want that data inserted. My opinion is that it is completely possible to live without nested transactions, in fact we all do now and survive, but they are very convenient to solve a full range of problems. You are right that a different range of problems can be easily solved by just maintaining a counter of transactions. I fully agree. I just think its important to point out that the range of problems that require nested transactions is not nearly as large as people assume when they first start thinking about the issue. Written in an abstract form, you base your reasoning on the fact that you know, before beginning the first transaction, all the future steps that you are going to perform and the order of performing them. This is not always true, as some decisions can be based on external input, like an interative user, data coming from an external source, other types of events. Also, the decision is often taken based on the data itself, so it is more convenient to put the data in the database, operate with it and decide after operation. That is not what I'm saying. There is no requirement to know the all the steps that will be included in the transaction or the order of performing them before the transaction begins. The point of a transaction is that all the actions are done or none are. The individual actions can be conditional, or done in different orders from one transaction to another. What you don't want to do is include any action that is not necessary to the successful completion of the transaction. This way if an error occurs at some point, the transaction can be rolled back to undo all the changes in the same way the database handles an incomplete transaction due to a catastrophic failure such as a power failure. If the transaction only includes necessary statements, then an error in any statement means the transaction is incomplete, and therefore should be completely undone. If you include unnecessary actions in your transaction, you are not using transactions correctly, and you will run into problems. Finally, I am not advocating to include nested transactions into sqlite or not, as this is a problem to be solved by the people who is developing the library. But it is important to include all the facts in the discussion. Again, I agree fully. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Null row detection when doing sqlite3_step
pompomJuice <[EMAIL PROTECTED]> wrote: Basically I am looking for somthing simular to oracle's code 1403 where a query returned zero rows. If a resultset is empty, the very first call to sqlite3_step would return SQLITE_DONE (normally it would be SQLITE_ROW). That's your cue. How do I know when step resulted in zero rows without checking each column value that the query returned. You cannot check each column value. It is only legal to call sqlite3_column_* when the previous sqlite3_step call returned SQLITE_ROW. In this case, it returns SQLITE_DONE instead. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Null row detection when doing sqlite3_step
Sortoff. Basically I am looking for somthing simular to oracle's code 1403 where a query returned zero rows. How do I know when step resulted in zero rows without checking each column value that the query returned. If it is so that I need to check all columns then I would like to know which of the calls sqlite3_column_text, sqlite3_column_int, sqlite3_column_blob or sqlite3_column_bytes will perform the best in such a loop. Igor Tandetnik wrote: > > pompomJuice <[EMAIL PROTECTED]> wrote: >> What is the best way to determine that sqlite3_step returned a null >> now? > > I'm not sure what you mean by sqlite3_step returning a NULL. I assume > you mean it returns a row one or more columns of which contain NULL > values. You can check that with sqlite3_column_type. > > Igor Tandetnik > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Null-row-detection-when-doing-sqlite3_step-tf3565781.html#a9961008 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Dennis Cote <[EMAIL PROTECTED]> wrote: Ramon Ribó wrote:
>
>
> Imagine one application that can import data from a file. You want
> that, in case of computer crash, either all the data of the file is
> imported or none. At the same time, you want the user to manually
> accept or reject every section of the file.
>
> This example can be modelled in a very natural way with a
> transaction covering the full file import and a nested transaction
> covering every section.
>
>
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested
else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Denis,
Correct me if I'm wrong on this concept:
Adding nested transactions really means adding the ability to demark
internally a transaction ID. So that later that transaction can be rolled back.
Consider
begin Main;
step a
savepoint loc1
step 1
savepoint loc2
step 2
rollback loc2 <- Rolls back step2
step 2a
savepoint loc3
step 3
commit ;
(result: step a, step 1, step2a and step3 )
I think the concept of a savepoint is simpler than a truely nested
transaction. As one doesn't actually need to start a new transaction just mark
a position where a savepoint rollback would stop. Savepoints then are not
really nested transactions but just markers that indicate when to stop rolling
back within the journal file.
The examples given thus far are not very compelling for savepoints. But
savepoints are usefull in special situations.
Instead of Nested Transactions, What about the concept of an autonomous
transaction?
Regards,
Ken
[sqlite] Re: Null row detection when doing sqlite3_step
pompomJuice <[EMAIL PROTECTED]> wrote: What is the best way to determine that sqlite3_step returned a null now? I'm not sure what you mean by sqlite3_step returning a NULL. I assume you mean it returns a row one or more columns of which contain NULL values. You can check that with sqlite3_column_type. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Jef Driesen wrote: I can give you the example of an application using sqlite as the on-disk file format. As mentioned on the sqlite website [1], the traditional File/Open operation does an sqlite3_open() and executes a BEGIN TRANSACTION. File/Save does a COMMIT followed by another BEGIN TRANSACTION. That would be the parent transaction. Imagine now the application needs to execute a group of sql statements that needs to be atomic. Some examples that come to my mind are importing data, re-arranging existing data,... To guarantee the entire operation is atomic, I want to group them in a child transaction. But when this operation fails for some reason (because of invalid data, a violated constraint,...), only the child transaction needs to rollback. Because changes prior to this child transaction should remain intact and the application can still continue because the database remains in a clean state. Without nested transactions, I have to make a compromise by: (a) not using a parent transaction and loosing the File/Save feature. (b) not using a child transaction and running into the risk of leaving inconsistent data in the database after an error or having to throw away all changes after an error. [1] http://www.sqlite.org/whentouse.html Jef, While this is another example of where a savepoint mechanism could be useful, it is not necessary. Another solution to your dilemma is given in the second paragraph of the application file format description on that web page. You use the database to store an undo log. You can then undo the changes made since the beginning of your "pseudo transaction" in the event of a error during a multiple statement change. Since you are probably going to have an undo/redo mechanism anyway, this adds little or no additional work. You simply mark all statements in a "pseudo transaction" with the same transaction number in the undo log, and if an error occurs you undo all the statements already logged with that transaction number. Of course there are also other ways of handling this such as swapping files on open and save rather than using transactions, so that real transactions can be used to update the active file atomically. Dennis Cote . - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Null row detection when doing sqlite3_step
Hello. What is the best way to determine that sqlite3_step returned a null now? At the moment the only way I see is checking each select column with sqlite3_column_bytes and setting the row ato null id all of those calls return 0. Is there maybe a better way? I can't seem to find such an function in the API. Thanks in advance. -- View this message in context: http://www.nabble.com/Null-row-detection-when-doing-sqlite3_step-tf3565781.html#a9960376 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite join-mechanisms question
On 12/04/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Cesar Rodas" <[EMAIL PROTECTED]> wrote: > Hello. > > I have a question about SQLite join-mechanisms. Let me explain with an > example. > > I have the follow table. > CREATE TABLE a( >word_id INTEGER, >doc_id INTEGER > ); > > CREATE INDEX "a_index1" ON "a"( > "doc_id" ASC > ); > > CREATE INDEX "a_index" ON a ( >"word_id" DESC > ); > > And how can SQLite do an optimized join of the follow query > SELECT > a.doc_id > FROM > a, a as a1, a as a2 > WHERE > a.doc_id = a1.doc_id and a1.doc_id = a2.doc_id and > a.word_id = 1 and a1.word_id = 2 and a2.word_id = 4 > LIMIT 0,20 > My (untested) guess it that you will get better performance if you do this: DROP INDEX a_index1; DROP INDEX a_index; CREATE INDEX a_index2 ON a(doc_id, word_id); I will also guess that ANALYZE will help in this case. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - Thanks for the answer mister Hipp, but I am searching an SQL help. I'd like to know how is the SQLite join algorithm or where i could find it (in what ".c" file). Thank for the help! ;) -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] Data structure
You might want to check out kazlib for your data structure lookups. It cantains code to implement Linked List, Hast, and Dictionary access data structures. The hashing code is really quite fast for in memory retrievals plus it is dynamic so that you don't have to preconfigure your hash table size. The linked list code is pretty good, it does have the ability to create Memory Pools (node pools) for the list structures. That way the package is not continually calling malloc and free for every node insert/delete etc.. Lloyd <[EMAIL PROTECTED]> wrote: On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: > I think, looking from Lloyd's email address, (s)he might be limited to > what CDAC, Trivandrum might be providing its users. > > Lloyd, you already know what size your data sets are. Esp. if it > doesn't change, putting the entire dataset in RAM is the best option. > If you don't need SQL capabilities, you probably can just use > something like BerkeleyDB or DBD::Deep (if using Perl), and that will > be plenty fast. Of course, if it can't be done then it can't be done, > and you will have to recommend more RAM for the machines (the CPU > seems fast enough, just the memory may be a bottleneck). Sorry, I am not talking about the limitations of the system in our side, but end user who uses our software. I want the tool to be run at its best on a low end machine also. I don't want the capabilities of a data base here. Just want to store data, search for presence, remove it when there is no more use of it. Surely I will check out BerkeleyDB. The data set must be in ram, because the total size of it is very small. (Few maga bytes) I just want to spped up the search, which is done millions of times. Thanks, LLoyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
I've used callgrind to get a hierachy of calls, it's good to graphically see where your spending time at in the code. Also you might want to check out oprofile. Its more of a system based profiler. And if you want to spend $$$ Rational Rose (I thinkt its an IBM product now) Purify is an excellent tool. Ken Lloyd <[EMAIL PROTECTED]> wrote: Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite join-mechanisms question
"Cesar Rodas" <[EMAIL PROTECTED]> wrote: > Hello. > > I have a question about SQLite join-mechanisms. Let me explain with an > example. > > I have the follow table. > CREATE TABLE a( >word_id INTEGER, >doc_id INTEGER > ); > > CREATE INDEX "a_index1" ON "a"( > "doc_id" ASC > ); > > CREATE INDEX "a_index" ON a ( >"word_id" DESC > ); > > And how can SQLite do an optimized join of the follow query > SELECT > a.doc_id > FROM > a, a as a1, a as a2 > WHERE > a.doc_id = a1.doc_id and a1.doc_id = a2.doc_id and > a.word_id = 1 and a1.word_id = 2 and a2.word_id = 4 > LIMIT 0,20 > My (untested) guess it that you will get better performance if you do this: DROP INDEX a_index1; DROP INDEX a_index; CREATE INDEX a_index2 ON a(doc_id, word_id); I will also guess that ANALYZE will help in this case. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite join-mechanisms question
I want to know SQLite join algorithm On 12/04/07, P Kishor <[EMAIL PROTECTED]> wrote: On 4/12/07, Cesar Rodas <[EMAIL PROTECTED]> wrote: > Hello. > > I have a question about SQLite join-mechanisms. Let me explain with an > example. > > I have the follow table. > CREATE TABLE a( >word_id INTEGER, >doc_id INTEGER > ); > > CREATE INDEX "a_index1" ON "a"( > "doc_id" ASC > ); > > CREATE INDEX "a_index" ON a ( >"word_id" DESC > ); > > And how can SQLite do an optimized join of the follow query > SELECT > a.doc_id > FROM > a, a as a1, a as a2 > WHERE > a.doc_id = a1.doc_id and a1.doc_id = a2.doc_id and > a.word_id = 1 and a1.word_id = 2 and a2.word_id = 4 > LIMIT 0,20 > Cesar, Am I flaking out or are you just making things more complicated for yourself than they need be -- > What the example do is find the doc_id that has word_id 1,2 and 4. > And suppose that there is: This Query is not equal to my query. Would a simple statement like the following work? SELECT doc_id FROM a WHERE word_id IN (1, 2, 4) >- 4 docs with word_id 1 >- 5 docs with word_id 2 >- 353500 docs with word_id 4 > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] - -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] Performance analysis of SQLite statements
Anyone know a good bench-marking (preferably free or cheap) which can be used to benchmark C/C++ code in Windows? Best regards, Jonas On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Are there any tools to help analyze the performance of components with a particular SQLite statement? I'm aware of the EXPLAIN option which can show what VBDE code was used to execute a statement, but afaik there is no way to tell the time each step took. Basically I want to know how long the different components of a single SQL statement took relative to the whole statement. Provide for more fine-grained analysis than just comparing two SQL statements based on total execution time. Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 11, 2007 8:02 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Select columns & performance ... Do a lot of benchmarking. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite join-mechanisms question
On 4/12/07, Cesar Rodas <[EMAIL PROTECTED]> wrote: Hello. I have a question about SQLite join-mechanisms. Let me explain with an example. I have the follow table. CREATE TABLE a( word_id INTEGER, doc_id INTEGER ); CREATE INDEX "a_index1" ON "a"( "doc_id" ASC ); CREATE INDEX "a_index" ON a ( "word_id" DESC ); And how can SQLite do an optimized join of the follow query SELECT a.doc_id FROM a, a as a1, a as a2 WHERE a.doc_id = a1.doc_id and a1.doc_id = a2.doc_id and a.word_id = 1 and a1.word_id = 2 and a2.word_id = 4 LIMIT 0,20 Cesar, Am I flaking out or are you just making things more complicated for yourself than they need be -- What the example do is find the doc_id that has word_id 1,2 and 4. And suppose that there is: Would a simple statement like the following work? SELECT doc_id FROM a WHERE word_id IN (1, 2, 4) - 4 docs with word_id 1 - 5 docs with word_id 2 - 353500 docs with word_id 4 -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite join-mechanisms question
Hello. I have a question about SQLite join-mechanisms. Let me explain with an example. I have the follow table. CREATE TABLE a( word_id INTEGER, doc_id INTEGER ); CREATE INDEX "a_index1" ON "a"( "doc_id" ASC ); CREATE INDEX "a_index" ON a ( "word_id" DESC ); And how can SQLite do an optimized join of the follow query SELECT a.doc_id FROM a, a as a1, a as a2 WHERE a.doc_id = a1.doc_id and a1.doc_id = a2.doc_id and a.word_id = 1 and a1.word_id = 2 and a2.word_id = 4 LIMIT 0,20 What the example do is find the doc_id that has word_id 1,2 and 4. And suppose that there is: - 4 docs with word_id 1 - 5 docs with word_id 2 - 353500 docs with word_id 4 What i am searching is a optimized way to join, i mean, the algorithm. And I think SQLite has a great performance with joins. Thank to all. -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
[sqlite] Performance analysis of SQLite statements
Are there any tools to help analyze the performance of components with a particular SQLite statement? I'm aware of the EXPLAIN option which can show what VBDE code was used to execute a statement, but afaik there is no way to tell the time each step took. Basically I want to know how long the different components of a single SQL statement took relative to the whole statement. Provide for more fine-grained analysis than just comparing two SQL statements based on total execution time. Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 11, 2007 8:02 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Select columns & performance ... Do a lot of benchmarking. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Encoding confusion
Anders Persson <[EMAIL PROTECTED]> wrote: What is confusing me is how are the string stored, if i understand corrent it is UTF-8 is this done automatic and what coding is a get back when a extra data ? SQLite database may store string data in UTF-8, UTF-16le or UTF-16be encodings. The encoding is determined when the first table in the database is created, and cannot be changed afterwards. See "PRAGMA encoding" at http://sqlite.org/pragma.html When you extract the data, it is automatically converted, if necessary, to UTF-8 or UTF-16 depending on whether you use sqlite3_column_text or sqlite3_column_text16. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Dennis,
I cannot agree here. Just imagine that the user decision is based on the
imported data.
Of course, you can read the data of the file, store in temporal structures
on memory, ask
the user and then, enter the data into the database. But the advantage of
using sqlite as
storage mechanism, as drh pointed out, is to use it as file format, an
avoid creating
intermediate data structures.
My opinion is that it is completely possible to live without nested
transactions, in
fact we all do now and survive, but they are very convenient to solve a
full range of
problems. You are right that a different range of problems can be easily
solved by just
maintaining a counter of transactions.
Written in an abstract form, you base your reasoning on the fact that
you know, before
beginning the first transaction, all the future steps that you are going
to perform and
the order of performing them. This is not always true, as some decisions
can be based on
external input, like an interative user, data coming from an external
source, other types
of events. Also, the decision is often taken based on the data itself, so
it is more
convenient to put the data in the database, operate with it and decide
after operation.
Finally, I am not advocating to include nested transactions into sqlite
or not, as this
is a problem to be solved by the people who is developing the library. But
it is important
to include all the facts in the discussion.
Best regards,
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com[EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
En Thu, 12 Apr 2007 00:37:21 +0200, Dennis Cote <[EMAIL PROTECTED]>
escribió:
Ramon Ribó wrote:
Imagine one application that can import data from a file. You want
that, in case of computer crash, either all the data of the file is
imported or none. At the same time, you want the user to manually
accept or reject every section of the file.
This example can be modelled in a very natural way with a transaction
covering the full file import and a nested transaction covering every
section.
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com[EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Encoding confusion
Hi! I am using SQLITE to save webbased data, under Unix, linux, osx the program is written i C, and uses the C-interface. What is confusing me is how are the string stored, if i understand corrent it is UTF-8 is this done automatic and what coding is a get back when a extra data ? Stupid question but i canät find any answer anyway just a lot off talks about it (i am running latest version av sqlite) // Anders - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite and nested transactions
Dennis Cote wrote:
[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'd', but not 'b' and 'c'.
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think it
is), then the exact same result can always be achieved with the simpler
sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
I can give you the example of an application using sqlite as the on-disk
file format. As mentioned on the sqlite website [1], the traditional
File/Open operation does an sqlite3_open() and executes a BEGIN
TRANSACTION. File/Save does a COMMIT followed by another BEGIN
TRANSACTION. That would be the parent transaction.
Imagine now the application needs to execute a group of sql statements
that needs to be atomic. Some examples that come to my mind are
importing data, re-arranging existing data,... To guarantee the entire
operation is atomic, I want to group them in a child transaction. But
when this operation fails for some reason (because of invalid data, a
violated constraint,...), only the child transaction needs to rollback.
Because changes prior to this child transaction should remain intact and
the application can still continue because the database remains in a
clean state.
Without nested transactions, I have to make a compromise by:
(a) not using a parent transaction and loosing the File/Save feature.
(b) not using a child transaction and running into the risk of leaving
inconsistent data in the database after an error or having to throw away
all changes after an error.
[1] http://www.sqlite.org/whentouse.html
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
