[sqlite] How to patch several sql words into one words
I want to insert several pieces of data (include blob) into a table in a transaction by programing in C language , like: char *sql[n+2]; sql[0] = sqlite3_mprintf (BEGIN;); char *sql_f = INSERT OR REPLACE INTO collstate VALUES (%d, %d, ?);; for (i=1; in+1; i++) sql[i] = sqlite3_mprintf (sql_f, st_mydata[i].m1, st_mydata[i].m2); sql[0] = sqlite3_mprintf (COMMIT;); sqlite3_prepare_v2 (db, sql*, -1, p_stmt, NULL); // How to patch several sql words like above into one sql words? for (i=1; in; i++) ret = sqlite3_bind_blob (p_stmt, i, st_mydata[i].data, sizeof (st_mydata[i].data), SQLITE_STATIC); -- View this message in context: http://old.nabble.com/How-to-patch-several-sql-words-into-one-words-tp27942977p27942977.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] All ancestors / descendents of a vertex in sqlite?
Dear list, I have a (small) directed graph which I would be able to fins all ancestors or descendents of a certain vertex (transitive closure?). So, using this graph: CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER, UNIQUE(parent_id, child_id)); INSERT INTO levels_levels VALUES(6,7); INSERT INTO levels_levels VALUES(6,8); INSERT INTO levels_levels VALUES(8,9); INSERT INTO levels_levels VALUES(7,10); INSERT INTO levels_levels VALUES(9,10); which would look like this: 6 7 8 | 9 10 I would like to make it possible to find 10 as a descendent of 8 and 6 as a parent of 9 (for instance). I have found a couple of procedural solutions using procedural calls in sql server or postgresql, but is there a solution that I could get into sqlite? The graph will never be very big, updating efficiency is not an important factor. It will be queried a lot though, so search efficiency is important. /Fredrik -- Life is like a trumpet - if you don't put anything into it, you don't get anything out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database optimization issues.
Here is an example of some tables which I am having very poor
performance querying. I was wondering if someone could give me some
suggestions as to how I can optimize this.
I am using the adobe Air framework (AS3) to access the database.
I make the connection to the file like this:
sqlConnection = new SQLConnection();
sqlConnection.openAsync(dbfile);
In my constructor I setup a parametrized sql statement that I will use
to be running the query:
stmtGetQuestionsNewSystemType = new SQLStatement();
stmtGetQuestionsNewSystemType.sqlConnection = sqlConnection;
stmtGetQuestionsNewSystemType.text = 'SELECT DISTINCT
Question.id
AS id, Question.name AS name, Question.label AS label,
Question.datatypeid AS datatypeid, Question.advanced AS advanced,
Question.multivalue AS multivalue, Question.measurementtypeid AS
measurementtypeid, Question.hotlist AS hotlist FROM main.Question,
main.CategoryQuestions, main.ApplicationQuestions,
main.TechnologyQuestions WHERE (Question.id =
CategoryQuestions.questionid AND CategoryQuestions.categoryid =
@categoryid ) OR (Question.id = ApplicationQuestions.questionid AND
ApplicationQuestions.applicationid = @applicationid ) OR (Question.id
= TechnologyQuestions.questionid AND TechnologyQuestions.technologyid
= @technologyid ) ;';
stmtGetQuestionsNewSystemType.itemClass = QuestionVO;
stmtGetQuestionsNewSystemType.addEventListener(
SQLEvent.RESULT,
function ( event:SQLEvent ):void {
resultHandlerQuestionsNewSystemType.call( this,
new
ArrayCollection( stmtGetQuestionsNewSystemType.getResult().data ),
_rowItem );
});
then whenever I run the query i merely do this:
setParameters( stmtGetQuestionsNewSystemType, [ {name:categoryid,
value:rowItem.systemcategoryid}, {name:applicationid,
value:rowItem.systemapplicationid}, {name:technologyid,
value:rowItem.systemtechnologyid} ] );
stmtGetQuestionsNewSystemType.execute();
Things I did to improve efficiency:
reused parametrized statements without modifying text property
declare explicitly that the table is coming from database main
declare are columns I want to retrieve in my select statement
This query is still taking about 40 seconds to execute on a relatively
fast computer. I am almost at my wits end because I can't figure out
how to make this query execute quickly. I know that you have to be
somewhat crafty to be efficient with sqlite so I was hoping someone
could point me in the right direction.
thanks,
Felipe Aramburu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] All ancestors / descendents of a vertex in sqlite?
On 18 Mar 2010, at 9:05am, Fredrik Karlsson wrote: I would like to make it possible to find 10 as a descendent of 8 and 6 as a parent of 9 (for instance). I have found a couple of procedural solutions using procedural calls in sql server or postgresql, but is there a solution that I could get into sqlite? One that can be executed in one SQL query ? No. Not without adding more data to your database. It will almost definitely be faster and simpler to do this in your chosen programming language, ending with a table like this: CREATE TABLE levels_relatives ( id INTEGER UNIQUE, ancestors TEXT, descendants TEXT); The simplest way to do this is to make any number of changes to levels_levels, then delete and reconstruct the entire levels_relatives table either by programming in your programming language, or by using group_concat(X) to concatenate the values already present. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to patch several sql words into one words
liubin liu wrote:
I want to insert several pieces of data (include blob) into a table in a
transaction by programing in C language , like:
char *sql[n+2];
sql[0] = sqlite3_mprintf (BEGIN;);
char *sql_f = INSERT OR REPLACE INTO collstate VALUES (%d, %d, ?);;
Why don't you use parameters for all three values in the VALUES clause? Then
you would only need one statement.
for (i=1; in; i++)
ret = sqlite3_bind_blob (p_stmt, i, st_mydata[i].data, sizeof
(st_mydata[i].data), SQLITE_STATIC);
The second parameter to sqlite3_bind_blob (in fact, all sqlite3_bind_*
functions) is the index of a parameter within the statement. In your case, it
should always be 1 (since you have only one parameter). Passing 'i' there
doesn't make any sense.
So, in the end, you'll have somethng like this:
sqlite3_exec(db, BEGIN, NULL, NULL, NULL);
sqlite3_stmt* stmt;
const char* sql = INSERT OR REPLACE INTO collstate VALUES (?, ?, ?);;
sqlite3_prepare_v2(db, sql, -1, stmt, NULL);
for (int i = 0; i n; ++i) {
sqlite3_bind_int(stmt, 1, st_mydata[i].m1);
sqlite3_bind_int(stmt, 2, st_mydata[i].m2);
sqlite3_bind_blob(stmt, 3, st_mydata[i].data, sizeof(st_mydata[i].data),
SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, END, NULL, NULL, NULL);
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] All ancestors / descendents of a vertex in sqlite?
On Thu, Mar 18, 2010 at 10:05:20AM +0100, Fredrik Karlsson scratched on the wall: Dear list, I have a (small) directed graph which I would be able to fins all ancestors or descendents of a certain vertex (transitive closure?). So, using this graph: CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER, UNIQUE(parent_id, child_id)); INSERT INTO levels_levels VALUES(6,7); INSERT INTO levels_levels VALUES(6,8); INSERT INTO levels_levels VALUES(8,9); INSERT INTO levels_levels VALUES(7,10); INSERT INTO levels_levels VALUES(9,10); I have found a couple of procedural solutions using procedural calls in sql server or postgresql, but is there a solution that I could get into sqlite? No, not with this table design. Oracle, and a few other RDBMS products, have custom SQL extensions for dealing with this kind of edge tree, but SQLite does not. If you want to use this representation, you will end up writing loops in your code. The graph will never be very big, updating efficiency is not an important factor. It will be queried a lot though, so search efficiency is important. You might want to look into using a nested set. Nested sets can do these types of queries very quickly. The big disadvantage is that they can be extremely expensive to update. If that isn't a problem, it might be a better representation. Nested Sets are usually used for DAGs, so you may still end up looping over multiple node ID (for example, find all the parents of 10 may require two queries, one for each 10 in the tree), but it is still likely to be faster. With enough JOINs you might be able to do this in one pass. It is too early for me to think too hard about that. Just do a websearch for nested set SQL and start reading. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] All ancestors / descendents of a vertex in sqlite?
I have a (small) directed graph which I would be able to fins all ancestors or descendents of a certain vertex (transitive closure?). So, using this graph: CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER, UNIQUE(parent_id, child_id)); INSERT INTO levels_levels VALUES(6,7); INSERT INTO levels_levels VALUES(6,8); INSERT INTO levels_levels VALUES(8,9); INSERT INTO levels_levels VALUES(7,10); INSERT INTO levels_levels VALUES(9,10); which would look like this: 6 7 8 | 9 10 I would like to make it possible to find 10 as a descendent of 8 and 6 as a parent of 9 (for instance). I have found a couple of procedural solutions using procedural calls in sql server or postgresql, but is there a solution that I could get into sqlite? The graph will never be very big, updating efficiency is not an important factor. It will be queried a lot though, so search efficiency is important. Re-implement your levels_levels table as a couple of twin trees (father, mother) using integer intervals (lookup nested tree). All common queries into that can then be made very efficiently using a single SQL statement. Inserts (and updates but are there many in genealogy?) will definitely take longer, but your tree probably doesn't see too many new leaves each second! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] state of wrappers
I am also interested as to which Java JDBC wrappers are good. I have used the zentus wrapper for a very basic application and it worked OK. I could not get it to register as a desktop database application in Netbeans (6.5); but, that wasn't a problem for that simple application. But, I am about to do a much more complicated application and would like to know what the best proven choices are. Vance Jay A. Kreibich wrote: I'm trying to put together some documentation and need some help from SQLite users utilizing non-C languages. The wiki page on SQLite wrappers is a bit of a mess, so I'm hoping some people that actually use these languages can offer some opinions. In specific, I'm trying to understand the current state of drivers and wrappers for: - Java JDBC. Is there a the driver for JDBC? These seem to be popular, with some common code paths: http://www.ch-werner.de/javasqlite/ http://www.zentus.com/sqlitejdbc/index.html http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC - .NET The wiki lists a dozen or so wrappers, but most look like they were weekend projects that never really went anywhere. Is there a supported open-source .NET/ADO.NET driver that has established itself? Or at least one that is well supported and likely to have a future? The two big ones seem to be: http://sqlite.phxsoftware.com/ http://www.mono-project.com/SQLite And they seem to be related (at least the newer SQLite v3 stuff). I must admit I'm a bit lost here. I know nothing of .NET. If you're the developer of one of these packages or want to express a strong personal opinion, please feel free to contact me off-list. Thanks! -j ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Updated FTS3 compression patch
Hello! I did rewrite the subj and now my test database is 4.6x smaller. It's fine, of cource. But there is the problem with speed of count(*) query: sqlite select count(*) from file_text; 8430 CPU Time: user 0.508032 sys 0.012000 sqlite select count(*) from file_text_content; 8430 CPU Time: user 0.004000 sys 0.00 Really, unpacking full database content is slow operation. Is it possible to fix this on virtual-table level? Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updated FTS3 compression patch
On 18 Mar 2010, at 4:35pm, Alexey Pechnikov wrote: sqlite select count(*) from file_text; 8430 CPU Time: user 0.508032 sys 0.012000 Try counting just the primary key. Suppose your primary key is a column called 'id', try SELECT COUNT(id) FROM file_text Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
