Re: [sqlite] open db cx to fork(2)d children
On Fri, Jul 09, 2010 at 02:38:08PM -0700, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/09/2010 02:31 PM, Nicolas Williams wrote: > > The trick to making that go fast is to use pthread_atfork() to get the > > new PID on the child side of fork() and store the PID in a global > > variable so that you don't need to call getpid(). > > That assumes that pthreads is in use, that getpid is poorly implemented/slow > and that enough of the 1% slowdown was due to getpid calls that changing it > would make a difference :-) I'm spoiled by Solaris, where (since Solaris 10) there's a unified process model and every process has pthreads (not necessarily more than one thread, mind you), with pthreads folded into libc. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open db cx to fork(2)d children
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/09/2010 02:31 PM, Nicolas Williams wrote: > The trick to making that go fast is to use pthread_atfork() to get the > new PID on the child side of fork() and store the PID in a global > variable so that you don't need to call getpid(). That assumes that pthreads is in use, that getpid is poorly implemented/slow and that enough of the 1% slowdown was due to getpid calls that changing it would make a difference :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkw3lrsACgkQmOOfHg372QRlvACePV+jbjnvVxC6BsLZq0mvJOiG AHAAn0yjpqdhxd/MaYnk/lRbtIeMcb/K =S41l -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open db cx to fork(2)d children
On Fri, Jul 09, 2010 at 02:22:37PM -0700, Roger Binns wrote: > On 07/09/2010 01:52 PM, Eric Smith wrote: > > What do you mean, "immediately"? As I said, my child comes to life, > > does some work without touching (its copy of) existing SQLite strucures, > > and then calls exit(2). > > I'll bet you are actually getting exit(3) which means anything registered > with atexit will be run. (SQLite does not register with atexit.) Oh, duh. I forgot that distinction. Yes, exit(3), not exit(2). (Any library with atexit handlers should check the process' PID if fork- safety is an issue, and do nothing when called on the child side of a fork().) > In my wrapper I provide functionality that can check SQLite objects are not > being used across processes. The way it does this is by providing an > alternate mutex implementation (almost every SQLite operation acquires and > releases mutexes) and verifies the mutex is used in the same process id it > was allocated in. In a benchmark doing only SQLite operations I found a 1% > performance hit. The trick to making that go fast is to use pthread_atfork() to get the new PID on the child side of fork() and store the PID in a global variable so that you don't need to call getpid(). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open db cx to fork(2)d children
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/09/2010 01:52 PM, Eric Smith wrote: > What do you mean, "immediately"? As I said, my child comes to life, > does some work without touching (its copy of) existing SQLite strucures, > and then calls exit(2). I'll bet you are actually getting exit(3) which means anything registered with atexit will be run. (SQLite does not register with atexit.) In my wrapper I provide functionality that can check SQLite objects are not being used across processes. The way it does this is by providing an alternate mutex implementation (almost every SQLite operation acquires and releases mutexes) and verifies the mutex is used in the same process id it was allocated in. In a benchmark doing only SQLite operations I found a 1% performance hit. A little tricky thing is that you can't change mutex implementations after SQLite has initialised. Consequently the code ensures SQLite is initialised to get the current mutex implementation, then does a shutdown, installs the new implementation and initialises SQLite again. You should not shutdown SQLite unless all items are fully closed and freed. Therefore this code is best run as close to the beginning of main() as possible. I'd recommend doing something like this, even if it is only enabled during testing because other people may write code, or after time has passed you may forget about the whole forking issue. You can see example code at: http://code.google.com/p/apsw/source/browse/src/apsw.c#858 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkw3kxkACgkQmOOfHg372QTomgCg1g8LUOQnJK7z3B0j2tsax6FN 2OsAn1K6eO1zHZx+eR66PVasjaugm9tp =Hm6K -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open db cx to fork(2)d children
On Fri, Jul 09, 2010 at 04:52:35PM -0400, Eric Smith wrote: > > > I strongly recommend that you always make the child side of fork(2) > > either exit(2) or exec(2) immediately. > > Sorry Nico, I never saw this response -- I appreciate it! > > What do you mean, "immediately"? Good question. One answer: "before ever utilizing state inherited from the parent for libraries such as libsqlite3". A much more conservative answer: "the exit(2) or exec(2) call should be lexically close to the fork(2) call, and it should be obvious from lexical context that the child will not ever utilize state inherited from the parent for libraries such as libsqlite3". (What a mouthful. If there's a good English-language word to describe the above without superfluously referring to 'time', as 'immediately' does, I'd love to hear what it is.) > As I said, my child comes to life, > does some work without touching (its copy of) existing SQLite strucures, > and then calls exit(2). The lifetime of the child is small wrt the > lifetime of the parent. That's fine. The child could run forever then. Of course, if you design an architecture like that you end up creating the temptation (for subsequent developers) to do more in the child process later, so I'd not recommend it. > Let's assume for the moment that I don't care about safety wrt > non-sqlite libraries (except of course any libraries on which > sqlite depends). Good luck! :) > > With respect to SQLite3, there are two sets of fork-safety issues: file > > descriptor offsets (use USE_PREAD to avoid this issue), > > I take you to mean that the child and parent's fds point to the same > underlying file description, and if the child changes the file > description then it will have a side effect in the parent. Exactly. > But I have assumed that the child does not make any sqlite api calls > against existing sqlite structures. I believe this assumption allows > me to conclude that sqlite will not touch any existing fd, and hence > will not bear such an impact on the parent (even if the child makes > sqlite api calls against structures the child creates on its own). > Am I right? Correct. > > and POSIX file byte range locks. > > I'm not using POSIX locks, so I'm good to go there. But even if I were, > I believe my above reasoning applies equally well here, since I believe > your reason for being concerned about it is similar. The fds that were > duplicated across the fork refer to the same underlying file > description, so we are technically in a "dangerous" state: the child > *could*, at its whim, release the parent's lock (for example). But if > it guarantees not to do so (by guaranteeing to make no sqlite calls > against existing structures), then no harm will result. No, I meant that if you stepped, in the child, a statement inherited from the parent then the child would go ahead believing it has a lock when in fact it does not (because the parent got the lock). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open db cx to fork(2)d children
> I strongly recommend that you always make the child side of fork(2) > either exit(2) or exec(2) immediately. Sorry Nico, I never saw this response -- I appreciate it! What do you mean, "immediately"? As I said, my child comes to life, does some work without touching (its copy of) existing SQLite strucures, and then calls exit(2). The lifetime of the child is small wrt the lifetime of the parent. Let's assume for the moment that I don't care about safety wrt non-sqlite libraries (except of course any libraries on which sqlite depends). > With respect to SQLite3, there are two sets of fork-safety issues: file > descriptor offsets (use USE_PREAD to avoid this issue), I take you to mean that the child and parent's fds point to the same underlying file description, and if the child changes the file description then it will have a side effect in the parent. But I have assumed that the child does not make any sqlite api calls against existing sqlite structures. I believe this assumption allows me to conclude that sqlite will not touch any existing fd, and hence will not bear such an impact on the parent (even if the child makes sqlite api calls against structures the child creates on its own). Am I right? > and POSIX file byte range locks. I'm not using POSIX locks, so I'm good to go there. But even if I were, I believe my above reasoning applies equally well here, since I believe your reason for being concerned about it is similar. The fds that were duplicated across the fork refer to the same underlying file description, so we are technically in a "dangerous" state: the child *could*, at its whim, release the parent's lock (for example). But if it guarantees not to do so (by guaranteeing to make no sqlite calls against existing structures), then no harm will result. Thanks, Eric -- Eric A. Smith Impartial, adj.: Unable to perceive any promise of personal advantage from espousing either side of a controversy or adopting either of two conflicting opinions. -- Ambrose Bierce, "The Devil's Dictionary" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concat 2 const chars ?
Hi, i think you are asking a C question, and not an sql question. Is your example C code? If this is the case, you should read up on C basics. Feel free to contact me directly (also in German) Martin Am 09.07.2010 21:06, schrieb rollerueckwaerts: > Hello, > I try to get an sql query string from 2 const chars. > > const char *language; > language = "'6'"; > const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = "; > > const char *sql = strcpy(sql2,language); > // or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " + language; > //or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = "& language; > > > nothing works :) > > How can i do this ? > > Hoping for help :) > tobi > > Fax: +49 (0) 3212 / 1001404 engelsch...@codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concat 2 const chars ?
I really don't mean to be a jerk, but this does seem to be really off topic for this mailing list. Isn't the concatination of two string a general C/C++ question rather then a sqlite question? Don't you think you might be better off asking this question on a C/C++ mailing list or forum, maybe somewhere like http://www.codeguru.com? As to stay close to the topic [sqlite], aka that of general sql, I would HIGHLY recommend looking at this link: http://en.wikipedia.org/wiki/SQL_injection And finally to stay on topic [sqlite], don't do what you are trying to do, use a prepared statement and bind your variables to the prepared statement to prevent sql injection. Sam On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts wrote: > > Hello, > I try to get an sql query string from 2 const chars. > > const char *language; > language = "'6'"; > const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = "; > > const char *sql = strcpy(sql2,language); > // or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " + language; > //or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " & language; > > > nothing works :) > > How can i do this ? > > Hoping for help :) > tobi > > -- > View this message in context: > http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to concat column
On Fri, Jul 9, 2010 at 2:35 PM, Peng Yu wrote: > On Sun, Jul 4, 2010 at 7:15 PM, P Kishor wrote: >> On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu wrote: >>> Hi, >>> >>> I only find row-wise concatenation by not column-wise. >>> >>> For example, I have table >>> >>> x1 y1 >>> x1 y2 >>> x2 y3 >>> x4 y4 >>> >>> I want to have the second column concatenated based on the value in >>> the first column to get the new table >>> x1 y1y2 >>> x2 y3y4 >>> >>> Moreover, I want to have a spectator (e.g., ',') in the second column. >>> x1 y1,y2 >>> x2 y3,y4 >>> >>> Could you show me if it is possible to do this in sqlite3? >> >> >> sqlite> CREATE TABLE t (a, b); >> sqlite> INSERT INTO t VALUES ('x1', 'y1'); >> sqlite> INSERT INTO t VALUES ('x1', 'y2'); >> sqlite> INSERT INTO t VALUES ('x2', 'y3'); >> sqlite> INSERT INTO t VALUES ('x4', 'y4'); >> sqlite> SELECT * FROM t; >> a b >> -- -- >> x1 y1 >> x1 y2 >> x2 y3 >> x4 y4 >> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a; >> a Group_concat(b) >> -- --- >> x1 y1,y2 >> x2 y3 >> x4 y4 >> sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4'; >> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a; >> a Group_concat(b) >> -- --- >> x1 y1,y2 >> x2 y3,y4 >> sqlite> > > Is there a way to reverse the operation done by Group_concat. > > x1 y1,y2 > x2 y3,y4 > > Suppose that I start with the above table, how to convert it to the > following table? > > x1 y1 > x1 y2 > x2 y3 > x2 y4 > Use a programming language. > -- > Regards, > Peng > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite query with c++ variable
smengl90 wrote: > Hi, I want to compose a query that will use a c++ variable. For example I > have: > > int i= 5; > char * query = "SELECT * from userInfo WHERE count<'i'". > > The syntax does not work. How do I do that? I think your primary problem is lack of experience in C++. I strongly recommend that you read up on the language itself before attempting to write SQLite applications. Are you required to use C++? You may find it (much, much) easier to use Tcl at first. If I may ask: are you a student, or doing this for fun in your spare time, or is this a part of your job? Eric -- Eric A. Smith Windows is *NOT* a virus - viruses are small and efficient. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite query with c++ variable
On Fri, Jul 9, 2010 at 2:48 PM, smengl90 wrote: > > Hi, I want to compose a query that will use a c++ variable. For example I > have: > > int i= 5; > char * query = "SELECT * from userInfo WHERE count<'i'". > > The syntax does not work. How do I do that? smengl90, You are asking a question that would be answered in an decent tutorial on C/C++ programming. Based on your earlier questions about how to compile/link C/C++ code and now this question, I have to say: You might get a lot farther, a lot faster, if you step back from sqlite for a little bit and dig into basic C/C++ programming. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: concat 2 const chars ?
You must be thinking of Java or such -- C doesn't do concatenation that way. There is a strcat() function but that's an ugly way to do it. Here's how I'd do it: int language=6; int nbytes; char sql[65535]; // big sql buffer to store whatever we need nbytes=snprintf(sql,sizeof(sql),"SELECT key,name,test FROM uebersetzungen WHERE sprach_id='%d'", language); if (nbytes >= sizeof(sql)) { fprintf(stderr,"SQL buffer overflow!!\n"); } How is sprach_id defined in your table? As an integer or character? It looks to me like you're using it as an integer. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of rollerueckwaerts Sent: Fri 7/9/2010 2:06 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] concat 2 const chars ? Hello, I try to get an sql query string from 2 const chars. const char *language; language = "'6'"; const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id = "; const char *sql = strcpy(sql2,language); // or const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id = " + language; //or const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id = " & language; nothing works :) How can i do this ? Hoping for help :) tobi -- View this message in context: http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concat 2 const chars ?
and don't use strcpy here is why https://buildsecurityin.us-cert.gov/bsi-rules/home/g1/848-BSI.html On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts wrote: > > Hello, > I try to get an sql query string from 2 const chars. > > const char *language; > language = "'6'"; > const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE > sprach_id > = "; > > const char *sql = strcpy(sql2,language); > // or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " + language; > //or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " & language; > > > nothing works :) > > How can i do this ? > > Hoping for help :) > tobi > > -- > View this message in context: > http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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 > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concat 2 const chars ?
>From the point of view of a C question, make your array of characters large enough to hold the characters you want (and terminating null) before copying them in. >From the point of view of an SQL: if you want to change the comparison constant in a where clause, look up bind parameters. read through http://www.sqlite.org/c3ref/bind_blob.html There are lots of examples on the list of binding. regards, Adam On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts wrote: > > Hello, > I try to get an sql query string from 2 const chars. > > const char *language; > language = "'6'"; > const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE > sprach_id > = "; > > const char *sql = strcpy(sql2,language); > // or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " + language; > //or > const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id > = " & language; > > > nothing works :) > > How can i do this ? > > Hoping for help :) > tobi > > -- > View this message in context: > http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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 > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
I'm apologise for the reminder, but I think I have become a victim of "thread takeover". Would someone please kindly answer my questions ? Kavita On 7/9/10 11:41 AM, "Kavita Raghunathan" wrote: > Thanks. Sounds like I have to use BLOBs which is not what I'm doing > currently. > > 1) I wonder about backward compatibility when I start using this BLOB > method. There are previous databases out there that don't use BLOBs. Can I > mix and match ? Or does this require the previous database to be deleted on > an upgrade ? > > 2) When you say " Use statement paramters and bind the data directly" Are > you referring to the examples in http://sqlite.org/c3ref/bind_blob.html as > pointed to by Eric Smith? > > Thanks, > Kavita > > > On 7/9/10 11:30 AM, "Jay A. Kreibich" wrote: > >> On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the >> wall: >>> Hello, >>> I?m storing encrypted passwords in the sqlite database. The encryption >>> algorithm generates ?null? character, and therefore the password >>> strings can have nulls in them. >>> >>> 1. Is this an issue for storing in database ? If strcpy is used >>> anywhere, it would be a problem >> >> They can't be stored as text values without some type of encoding >> (like base64), but they can be stored as BLOBs. >> >>> 2. I?m using sprintf to generate the SQL statement as shown below. >>> This causes a problem because sprintf stops printing when it >>> encounters ?null?. >> >> Don't do that. Use statement parameters and bind the data directly. >> >>-j > > ___ > 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] How to concat column
On Sun, Jul 4, 2010 at 7:15 PM, P Kishor wrote: > On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu wrote: >> Hi, >> >> I only find row-wise concatenation by not column-wise. >> >> For example, I have table >> >> x1 y1 >> x1 y2 >> x2 y3 >> x4 y4 >> >> I want to have the second column concatenated based on the value in >> the first column to get the new table >> x1 y1y2 >> x2 y3y4 >> >> Moreover, I want to have a spectator (e.g., ',') in the second column. >> x1 y1,y2 >> x2 y3,y4 >> >> Could you show me if it is possible to do this in sqlite3? > > > sqlite> CREATE TABLE t (a, b); > sqlite> INSERT INTO t VALUES ('x1', 'y1'); > sqlite> INSERT INTO t VALUES ('x1', 'y2'); > sqlite> INSERT INTO t VALUES ('x2', 'y3'); > sqlite> INSERT INTO t VALUES ('x4', 'y4'); > sqlite> SELECT * FROM t; > a b > -- -- > x1 y1 > x1 y2 > x2 y3 > x4 y4 > sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a; > a Group_concat(b) > -- --- > x1 y1,y2 > x2 y3 > x4 y4 > sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4'; > sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a; > a Group_concat(b) > -- --- > x1 y1,y2 > x2 y3,y4 > sqlite> Is there a way to reverse the operation done by Group_concat. x1 y1,y2 x2 y3,y4 Suppose that I start with the above table, how to convert it to the following table? x1 y1 x1 y2 x2 y3 x2 y4 -- Regards, Peng ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted sqlite journal
On Fri, Jul 9, 2010 at 3:21 PM, Jim Wilcoxson wrote: > On Fri, Jul 9, 2010 at 10:38 AM, Ke Tao wrote: > >> >> HI All , >> >> I have sqlite db name "wdb" and "wdb-journal" file was created by power >> failure something , when I do any db operation sqlite always prompt "disk >> I/O error" , but when I delete the "wdb-journal" ,there is no errors >> prompted. I think maybe the wdb-journal file was corrupted , does anyone >> have any idea on this ? >> I used sqlite on linux system. >> >> Best Regards, >> Ke Tao >> > > Permission problems can cause this. The "disk I/O error" actually means "I > can't do a rollback", maybe because root created the journal and owns the > database, then another user is trying to do things with it. Just a guess. > This "disk I/O error" message is very confusing. Been there! > > Jim > -- > HashBackup: easy onsite and offsite Unix backup > http://sites.google.com/site/hashbackup > Also, by deleting the journal, you are likely to corrupt your database. You can't delete the journal! Jim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted sqlite journal
On Fri, Jul 9, 2010 at 10:38 AM, Ke Tao wrote: > > HI All , > > I have sqlite db name "wdb" and "wdb-journal" file was created by power > failure something , when I do any db operation sqlite always prompt "disk > I/O error" , but when I delete the "wdb-journal" ,there is no errors > prompted. I think maybe the wdb-journal file was corrupted , does anyone > have any idea on this ? > I used sqlite on linux system. > > Best Regards, > Ke Tao > Permission problems can cause this. The "disk I/O error" actually means "I can't do a rollback", maybe because root created the journal and owns the database, then another user is trying to do things with it. Just a guess. This "disk I/O error" message is very confusing. Been there! Jim -- HashBackup: easy onsite and offsite Unix backup http://sites.google.com/site/hashbackup ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL: no longer able to use read-only databases?
In testing the latest SQLite snapshot with WAL enabled, it seems that there's no way to use a database in a read-only location. For example, let's say I've created a database as root, then closed it (cleanly): $ ls -l /flash/alarms.db* -rw-r--r--1 root root 36864 Jan 1 00:14 /flash/alarms.db If I try as another user to use that database, I get an error: $ sqlite3 /flash/alarms.db SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA journal_mode; Error: unable to open database file sqlite> However, if I create a symlink to that database in a location that I have write access to, then everything works fine: $ ln -s /flash/alarms.db ./alarms.db $ ls -l alarms.db* lrwxrwxrwx1 adminadmin 16 Jan 1 00:15 alarms.db -> /flash/alarms.db $ sqlite3 alarms.db SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA journal_mode; wal sqlite> .quit $ ls -l alarms.db* lrwxrwxrwx1 adminadmin 16 Jan 1 00:15 alarms.db -> /flash/alarms.db -rw-r--r--1 adminadmin32768 Jan 1 00:15 alarms.db-shm -rw-r--r--1 adminadmin0 Jan 1 00:15 alarms.db-wal So clearly this is a side-effect of WAL creation, which happens in the same directory as the database file. This doesn't seem like it should fundamentally be any different than normal journaling mode, in that opening a database in read-only mode makes the creation of a journal / WAL unnecessary. But I'm not familiar with the WAL internals, so maybe there's more to it. FYI, this works fine with normal journaling mode (we bumped in existing code after changing the journal_mode). Any additional flags or ways of doing this that I'm missing? Or is it a bug? Thanks! -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] concat 2 const chars ?
Hello, I try to get an sql query string from 2 const chars. const char *language; language = "'6'"; const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id = "; const char *sql = strcpy(sql2,language); // or const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id = " + language; //or const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id = " & language; nothing works :) How can i do this ? Hoping for help :) tobi -- View this message in context: http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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] sqlite query with c++ variable
Hi, I want to compose a query that will use a c++ variable. For example I have: int i= 5; char * query = "SELECT * from userInfo WHERE count<'i'". The syntax does not work. How do I do that? Thanks -- View this message in context: http://old.nabble.com/sqlite-query-with-c%2B%2B-variable-tp29121237p29121237.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
Re: [sqlite] Null character problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/09/2010 10:54 AM, Eric Smith wrote: > My question came purely from a mild curiosity. I was wondering about > the behavior of sqlite call sqlite3_bind_text when it is passed a range > of BYTES that includes nulls. bind_text and bind_blob use the same backend function, passing it their parameters (byte pointer and length) as well as an encoding parameter (0 for blob, SQLITE_UTF8 or SQLITE_UTF16NATIVE). If the encoding doesn't match the database encoding then it is changed. The important thing is that the bytes are taken exactly as is. They are not looked at unless the encoding needs to be changed. The routine to change encoding (sqlite3VdbeMemTranslate) does not return an error if the bytes are not actually valid UTF8/16. In other words if you give SQLite something claiming it is a string then SQLite takes you at your word and does not do a round of double checking. It will return that same "string" when retrieving that value (ie same length and byte sequence). This is why you can include embedded nulls. It is also why you can feed it bytes in IS8859-1 and things will seem to be okay. (One of the Python wrappers allows that!) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkw3aWoACgkQmOOfHg372QT3vACgtVRoq/l8HZeDLd/QYwdt50NN qZ0AoM48Qu3ubM3Ld3FfQMjnyxv/WJkA =i6MM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
>> My question came purely from a mild curiosity. I was wondering about >> the behavior of sqlite call sqlite3_bind_text when it is passed a range >> of BYTES that includes nulls. See this snipper for documentation: "The third argument is the value to bind to the parameter. In those routines that have a fourth argument, its value is the number of bytes in the parameter. To be clear: the value is the number of bytes in the value, not the number of characters. If the fourth parameter is negative, the length of the string is the number of bytes up to the first zero terminator. The fifth argument to sqlite3_bind_blob(), sqlite3_bind_text(), and sqlite3_bind_text16() is a destructor used to dispose of the BLOB or string after SQLite has finished with it. If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns." http://sqlite.org/c3ref/bind_blob.html -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Smith Sent: sexta-feira, 9 de julho de 2010 14:54 To: General Discussion of SQLite Database Subject: Re: [sqlite] Null character problem Simo Slavin wrote: > (according to your earlier post) I'm not OP. I'm Eric. OP was someone else. In this context, I don't care about blobs or about the right way of doing anything. > Read the documentation for memset(). I know quite well how memset works. I know character!=byte. These matters are irrelevant to my question. My question came purely from a mild curiosity. I was wondering about the behavior of sqlite call sqlite3_bind_text when it is passed a range of BYTES that includes nulls. -- Eric A. Smith It's up. It sorta works. That's a start. -- BJ Premore ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite query with c
-- View this message in context: http://old.nabble.com/sqlite-query-with-c-tp29120975p29120975.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] More WAL questions and concerns.
A few questions that are my application specific that is an embedded application. I have two databases, the first one is a fixed size configuration database where all writes are UPDATES, and the database does not get that many writes. Does this mean that because of WAL the database itself will not get updated and changes will be residing in WAL for a very long time due to lack of traffic? The second is an archive database that is written to once a second, and once a month (size grows to 335MB), the database is closed, renamed, a template database is copied over, and the process starts again. Does this mean that I have to worry about renaming the WAL file as well now? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
Simo Slavin wrote: > (according to your earlier post) I'm not OP. I'm Eric. OP was someone else. In this context, I don't care about blobs or about the right way of doing anything. > Read the documentation for memset(). I know quite well how memset works. I know character!=byte. These matters are irrelevant to my question. My question came purely from a mild curiosity. I was wondering about the behavior of sqlite call sqlite3_bind_text when it is passed a range of BYTES that includes nulls. -- Eric A. Smith It's up. It sorta works. That's a start. -- BJ Premore ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
On Fri, Jul 9, 2010 at 10:48 AM, Eric Smith wrote: > Will sqlite3_bind_text work properly if the string contains (internal) > nulls? What if I did something like: > > char zText[100]; > memset(zText, 0, sizeof(zText)); > sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT); If you *really* want to work with your password information as a string / using string functions, convert / encode it to regular text first. The trivial case would encode to hex characters, changing your null characters to '00', which any of the string functions would happily handle. That encoding would double the storage space needed for the password, but string handling would be *safe*. Other encodings [that produce standard text characters] would also work. These would still require more storage than the raw 'binary' data you have, but most would be less than the double storage needed by the hex characters. -- Phil ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
On 9 Jul 2010, at 5:48pm, Eric Smith wrote: > Simon Slavin wrote: > >> BLOBs can handle any sequences of bytes without problems, >> including nulls, ETX, and sequences which be illegal if they were used to >> express Unicode characters. You can put anything you like in a BLOB. > > I assume, due to the manifest typing semantics of the library, that > the declared type of the column will make no difference when I bind a > weird datum to a variable (as long as it's not an 'INTEGER PRIMARY KEY' > column). You can use the _bind_blob routine to bind something which will eventually be stored in a column with TEXT affinity. (Sorry, I hate that sentence but I can't figure out how to improve it.) There are no problems doing this. > Will sqlite3_bind_text work properly if the string contains (internal) > nulls? There are many different meanings for 'string' and I can't answer any question about 'properly' until you describe in great detail what you mean by 'string'. On the other hand _bind_blob doesn't refer to strings at all. > What if I did something like: > > char zText[100]; > memset(zText, 0, sizeof(zText)); > sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT); Read the documentation for memset(). It does not take chars as parameters and does not use them internally. You are using a _text routine with an array of 'char's. Those lines will work perfectly together. But they has nothing to do with using BLOBs in SQLite. For that you would not use _bind_text, you'd use _bind_blob with things that probably do not represent characters. Possibly 'unsigned int' would be a more appropriate type than 'char'. > According to a strict reading of the doc, sqlite will blindly copy > sizeof(zText) characters (starting from zText[0]) into the column. > That is, this will store 100 null bytes into the column. Is that > right? Text is not bytes, it's characters. Text involves interpretation. BLOBs, on the other hand, are just sequences of bytes which are treated without any attempt to interpret or convert them at all. Since this is what your data actually is (according to your earlier post) you should be declaring your column as a BLOB column and using _blob routines to handle them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/09/2010 09:30 AM, Jay A. Kreibich wrote: > They can't be stored as text values without some type of encoding > (like base64), but they can be stored as BLOBs. Nulls can be stored in text values. Behind the scenes SQLite treats strings and blobs almost identically and you'll notice most functions operate on both. The major difference is that strings can be transformed into UTF-8 or UTF-16 encoding. Other than that they are both a bucket of bytes. As an example look at the implementation of sqlite3_bind_{blob,text} or trimFunc and replaceFunc. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkw3WrsACgkQmOOfHg372QTzbACfVVfmAsmSBdvv6WwPtd00DzHt /z4AoMORO0XCFsc3g3csCL6QA5meBzmM =g/YJ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/09/2010 09:24 AM, Kavita Raghunathan wrote: > 1. Is this an issue for storing in database ? If strcpy is used anywhere, > it would be a problem SQLite quite happily stores/retrieves null bytes in strings. It is part of my test suite. If you use the SQLite API correctly then it is fine. (ie *you* must not use strcpy etc). Some of the SQL level functions will however stop at the first null such as string concatenation or replace. Note however that strings are to store Unicode strings, encoded as UTF-8 or UTF-16 depending on the API flavour you use. Do not save binary data into a string. > 2. I’m using sprintf to generate the SQL statement as shown below. As others have pointed out this is a really bad idea as it allows for SQL injection bugs and attacks. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkw3WAcACgkQmOOfHg372QSNigCgpxjYX4Rktm7qePeZB/bKrZHs tHkAoOIvWAhNMFjI5P8F5sy7ZuXfZkD2 =T/px -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
Simon Slavin wrote: > BLOBs can handle any sequences of bytes without problems, > including nulls, ETX, and sequences which be illegal if they were used to > express Unicode characters. You can put anything you like in a BLOB. I assume, due to the manifest typing semantics of the library, that the declared type of the column will make no difference when I bind a weird datum to a variable (as long as it's not an 'INTEGER PRIMARY KEY' column). Will sqlite3_bind_text work properly if the string contains (internal) nulls? What if I did something like: char zText[100]; memset(zText, 0, sizeof(zText)); sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT); According to a strict reading of the doc, sqlite will blindly copy sizeof(zText) characters (starting from zText[0]) into the column. That is, this will store 100 null bytes into the column. Is that right? Eric -- Eric A. Smith When you come to a fork in the road, take it. -- Yogi Berra ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
Thanks. Sounds like I have to use BLOBs which is not what I'm doing currently. 1) I wonder about backward compatibility when I start using this BLOB method. There are previous databases out there that don't use BLOBs. Can I mix and match ? Or does this require the previous database to be deleted on an upgrade ? 2) When you say " Use statement paramters and bind the data directly" Are you referring to the examples in http://sqlite.org/c3ref/bind_blob.html as pointed to by Eric Smith? Thanks, Kavita On 7/9/10 11:30 AM, "Jay A. Kreibich" wrote: > On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the > wall: >> Hello, >> I?m storing encrypted passwords in the sqlite database. The encryption >> algorithm generates ?null? character, and therefore the password >> strings can have nulls in them. >> >> 1. Is this an issue for storing in database ? If strcpy is used >> anywhere, it would be a problem > > They can't be stored as text values without some type of encoding > (like base64), but they can be stored as BLOBs. > >> 2. I?m using sprintf to generate the SQL statement as shown below. >> This causes a problem because sprintf stops printing when it >> encounters ?null?. > > Don't do that. Use statement parameters and bind the data directly. > >-j ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
On 9 Jul 2010, at 5:29pm, Eric Smith wrote: > I have no specific knowledge on whether sqlite handles null characters > within the variables' values--but if I were a bettin man, I'd bet that > it handles them quite cleanly. You win. BLOBs can handle any sequences of bytes without problems, including nulls, ETX, and sequences which be illegal if they were used to express Unicode characters. You can put anything you like in a BLOB. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the wall: > Hello, > I?m storing encrypted passwords in the sqlite database. The encryption > algorithm generates ?null? character, and therefore the password > strings can have nulls in them. > > 1. Is this an issue for storing in database ? If strcpy is used > anywhere, it would be a problem They can't be stored as text values without some type of encoding (like base64), but they can be stored as BLOBs. > 2. I?m using sprintf to generate the SQL statement as shown below. > This causes a problem because sprintf stops printing when it > encounters ?null?. Don't do that. Use statement parameters and bind the data directly. -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
Re: [sqlite] Null character problem
Kavita Raghunathan wrote: > sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType, > AttrValue, ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d, > %d);", tbl_name, db[i]->attr_name, db[i]->attr_num, db[i]->attr_type, > db[i]->attr_value, db[i]->attr_src, entity_id); Don't do that. What if attr_name contains a ' character (or, as you say, some other weird character)? Instead, prepare a statement with sqlite variables, and bind values to those variables using the sqlite3_bind* family of interfaces: http://sqlite.org/c3ref/bind_blob.html I have no specific knowledge on whether sqlite handles null characters within the variables' values--but if I were a bettin man, I'd bet that it handles them quite cleanly. -- Eric A. Smith I think there's a world market for about five computers. -- attr. Thomas J. Watson (Chairman of the Board, IBM), 1943 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Null character problem
Hello, I’m storing encrypted passwords in the sqlite database. The encryption algorithm generates “null” character, and therefore the password strings can have nulls in them. 1. Is this an issue for storing in database ? If strcpy is used anywhere, it would be a problem 2. I’m using sprintf to generate the SQL statement as shown below. This causes a problem because sprintf stops printing when it encounters “null”. Please advice. sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType, AttrValue, ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d, %d);", tbl_name, db[i]->attr_name, db[i]->attr_num, db[i]->attr_type, db[i]->attr_value, db[i]->attr_src, entity_id); Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL: Wrong error "database disk image is malformed"
I have long running test in WAL mode (inserting millions of records in autocommit mode). In other connection by sqlite3 shell I see these errors: sqlite> select (select count(*) from role_exist)/1000/1000; 12 sqlite> select (select count(*) from role_exist)/1000/1000; Error: file is encrypted or is not a database sqlite> select (select count(*) from role_exist)/1000/1000; 12 The database is correct: sqlite> pragma integrity_check; ok And test continue works: sqlite> select (select count(*) from role_exist)/1000/1000; Error: database disk image is malformed sqlite> select (select count(*) from role_exist)/1000/1000; Error: database disk image is malformed sqlite> select (select count(*) from role_exist)/1000/1000; Error: database disk image is malformed sqlite> select (select count(*) from role_exist)/1000/1000; 13 -- 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
[sqlite] Corrupted sqlite journal
HI All , I have sqlite db name "wdb" and "wdb-journal" file was created by power failure something , when I do any db operation sqlite always prompt "disk I/O error" , but when I delete the "wdb-journal" ,there is no errors prompted. I think maybe the wdb-journal file was corrupted , does anyone have any idea on this ? I used sqlite on linux system. Best Regards, Ke Tao ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query critique
If you had the following tables Table PEOPLERANKINGS(personid INTEGER PRIMARY KEY, personname TEXT, ranking) Table FRIENDS(personid1, personid2) and an index on PEOPLERANKINGS.ranking and FRIENDS.personid1,FRIENDS.personid2 is a composite unique primary key You could get the top 10 ranked people select * from PEOPLERANKINGS order by ranking desc limit 10 and get your own ranking and the ranking of your friends: select peoplerankings.* from PEOPLERANKINGS where personid IN (select personid2 from FRIENDS where personid1 = ?yourId? ) NOTE: befriend yourself by default in the FRIENDS table. Regards Tim Romano Swarthmore PA On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat wrote: > On Fri, Jul 9, 2010 at 11:08, Ian Hardingham wrote: > > Hey guys. > > > > I have a query which is very slow, and was wondering if there was any > > advice you guys had on it. > > > > Here are two table definitions: > > > > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY > > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, > > record TEXT); > > > > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY > > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT); > > > > And here is my query (written in a script language): > > > > db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE > > upper(name) = upper('?') OR id < ? union all SELECT a.* FROM > > globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') > > AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, > > %client.username, %globId, %client.username); > > > > Create an index either on player column or friend column in your second > table. > CREATE INDEX i_friendTable ON friendTable (player ASC); > > > -- > Benoit Mortgat > 20, avenue Marcel Paul > 69200 Vénissieux, France > +33 6 17 15 41 58 > +33 4 27 11 61 23 > ___ > 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] Retrieve Specific record number in one shot.
On Fri, Jul 09, 2010 at 08:12:20AM -0300, Israel Lins Albuquerque scratched on the wall: > Maybe do you want this! > > http://www.sqlite.org/syntaxdiagrams.html#select-stmt > > Select * From Product order by ProductName LIMIT 210 OFFSET 210; Close, but not quite. This will return rows 211 through 420. If you want row 210, you need "LIMIT 1 OFFSET 209". Or "LIMIT 209, 1" -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
Re: [sqlite] Retrieve Specific record number in one shot.
Piyush Verma wrote: > I want to navigate to specific position in table for example I want > row number 210 inspite of nevigating one by one how can get that row. See if this helps: http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieve Specific record number in one shot.
Maybe do you want this! http://www.sqlite.org/syntaxdiagrams.html#select-stmt Select * From Product order by ProductName LIMIT 210 OFFSET 210; - "Piyush Verma" escreveu: > Hello All, > > I want to navigate to specific position in table for example I want > row number 210 inspite of nevigating one by one how can get that row. > > One way could be create a Index and use where clause to get that But > it's not useful in my case. > > I have table which have primary key, and product name(there is another > index for ProductName). Now I sort by Product name and want to access > row number 210. > > Is that a way to get it directly? > > like > > >>"Select * From Product order by ProductName" > >>move_to_row(210); > >>read row; > > > something like that. > > > > > -- > Thanks & Regards > > Piyush Verma > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query critique
On Fri, Jul 9, 2010 at 11:08, Ian Hardingham wrote: > Hey guys. > > I have a query which is very slow, and was wondering if there was any > advice you guys had on it. > > Here are two table definitions: > > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, > record TEXT); > > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT); > > And here is my query (written in a script language): > > db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE > upper(name) = upper('?') OR id < ? union all SELECT a.* FROM > globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') > AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, > %client.username, %globId, %client.username); > Create an index either on player column or friend column in your second table. CREATE INDEX i_friendTable ON friendTable (player ASC); -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re trieve Specific record number in one shot.
Would Select * From Product order by ProductName LIMIT 1 OFFSET 209 help? -- Igor Piyush Verma-3 wrote: > > Hello All, > > I want to navigate to specific position in table for example I want > row number 210 inspite of nevigating one by one how can get that row. > > One way could be create a Index and use where clause to get that But > it's not useful in my case. > > I have table which have primary key, and product name(there is another > index for ProductName). Now I sort by Product name and want to access > row number 210. > > Is that a way to get it directly? > > like > >>>"Select * From Product order by ProductName" >>>move_to_row(210); >>>read row; > > > something like that. > > > > > -- > Thanks & Regards > > Piyush Verma > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Retrieve-Specific-record-number-in-one-shot.-tp29115356p29115896.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] Query critique
Hey guys. I have a query which is very slow, and was wondering if there was any advice you guys had on it. Here are two table definitions: CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, record TEXT); CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT); And here is my query (written in a script language): db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE upper(name) = upper('?') OR id < ? union all SELECT a.* FROM globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, %client.username, %globId, %client.username); The intention of this query is to, basically: - get the top 10 scores in rankingTable - get "my" score in rankingTable - get all of my friends' scores in rankingTable The query works, but is very slow. This may just be because it's a complex query, but all feedback would be much appreciated. For your extra information: - globalRankingTable has several thousand rows - friendTable has several thousand rows also, but each user will only have 0-10 friends (usually on the low part of that scale) - yes I know I'm an idiot with the upper stuff, a refactor is needed on that - I know all of my "name" fields should really be integers. Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieve Specific record number in one shot.
Hello All, I want to navigate to specific position in table for example I want row number 210 inspite of nevigating one by one how can get that row. One way could be create a Index and use where clause to get that But it's not useful in my case. I have table which have primary key, and product name(there is another index for ProductName). Now I sort by Product name and want to access row number 210. Is that a way to get it directly? like >>"Select * From Product order by ProductName" >>move_to_row(210); >>read row; something like that. -- Thanks & Regards Piyush Verma ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] importing data from file with 3 colums to table with 4 columns
On 9 July 2010 06:18, wrote: > > > Thank you very much Simon. > > That worked very slick. > > > > Say, is there a way to put all of the SQLite3 commands I used into a script > and have SQLite3 execute them in the script sequentially? > Yes, several ways: use .read command (sqlite3 tst.db ".read script.sql") use redirection to script file when invoking sqlite (sqlite3 tst.db < script.sql) scripts can use .read to invoke other scripts > > > -Chris > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users