Re: [sqlite] built in function hex
On Mon, 8 Oct 2007 10:03:40 -0700 (PDT), Ken wrote > is there a built in function to display numeric values as > hexidecimal strings? to_hexstr (40) would display 0x28 hex() isn't quite what you want. hex(40) gives 3430, since '4' is 0x34 and '0' is 0x30. Moving on... This is the sort of work I prefer to leave up to the application using SQLite. SQLite is a talented, efficient, obedient, and tireless reference librarian, but asking SQLite to write your thesis is going too far! But you can sure ask it for all the facts, arguments, and bibliographic references you need. It's your job (or, ditching the school metaphor, the application's job) to present the data to the consumer in a format palatable to same. Of course, that's just my take on things. If you still think you have a need for to_hexstr(), you can add it very easily. See http://www.sqlite.org/tclsqlite.html#function . (By the way, Author, you might want to rename the example function to not collide with the built-in hex() function.) -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] 'dump' from the C API
Does anyone know how to do a database dump from the C API? I'm linking to sqlite3.o (the amalgamated file) and was hoping to open a database and dump the contents to an sql file. I know this can be done from the shell, but as of yet, I can't get the shell running on AIX. What version of AIX are you running? Are you using xlC or gcc? On some versions of AIX you need to compile Sqlite without debug (no -g) when using gcc. You need to have readline installed to make a nice to use sqlite3 shell program. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A Question: Use of the sqlite3_exe( ) function?
Lee Crain wrote: Igor, I have a question. Why is it "highly recommended" to use the function call sequence you iterate in preference to the sqlite3_exe call, since it is implemented using that sequence? Lee Crain The callback method did not turn out to be such a good idea in practice and was superseded by a more flexible and powerful approach. Write a few programs and the benefit becomes very obvious. Using the prepare, step API's also encourages you to write more efficient and robust programs by pre-compileing the SQL. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
A brute force method would be to store the rowid of the owner row in each of the detail rows. chetana bhargav wrote: Is this a garbage collection situation, where you want a row in B to go away when all referring rows in A are deleted? Yes exactly this is what I wanted :) Sorry for the confusing message earlier -x- Chetana - Original Message From: Trevor Talbot <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, October 8, 2007 9:10:22 PM Subject: Re: [sqlite] many-one relation On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: We got two tables, tableA & tableB. tableB is turning out to be a many-one relation where in we have many rows of tableA mapping to one row of tableB, would like to know what is the best way to delete a row in tableB 1. Keep a reference count of the number of rows that are referring to this ( to be honest I dont think this is good) 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] - Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] 3.5.1 build issues
Thanks Joe > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Monday, October 08, 2007 1:45 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] 3.5.1 build issues > > "make test" already fixed in CVS. > > http://www.sqlite.org/cvstrac/tktview?tn=2689 > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] 3.5.1 build issues
"make test" already fixed in CVS. http://www.sqlite.org/cvstrac/tktview?tn=2689 --- "Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote: > A glitch or two got introduced into the 3.5.1 build for cygwin and > possibly other platforms. Please advise if I did something wrong: > > There are undefined symbols when building testfixture: > > /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build > /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2511: undefined > reference to `_Sqlitetest7_Init' > /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build > /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2520: undefined > reference to `_SqlitetestThread_Init' > /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build > /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2521: undefined > reference to `_SqlitetestOnefile_Init' Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. http://mobile.yahoo.com/go?refer=1GNXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
FW: [sqlite] 3.5.1 build issues
Addendum: There is a new file, src/test_onefile.c, that needs to be added to Makefile.in. -Original Message- From: Evans, Mark (Tandem) Sent: Monday, October 08, 2007 12:23 PM To: sqlite-users@sqlite.org Subject: [sqlite] 3.5.1 build issues A glitch or two got introduced into the 3.5.1 build for cygwin and possibly other platforms. Please advise if I did something wrong: There are undefined symbols when building testfixture: /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2511: undefined reference to `_Sqlitetest7_Init' /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2520: undefined reference to `_SqlitetestThread_Init' /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2521: undefined reference to `_SqlitetestOnefile_Init' The first function, Sqlitetest7_Init, turns up undefined due to change in #if-s in test7.c: from: #if OS_UNIX && SQLITE_THREADSAFE && \ defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE) to: #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE) #if defined(OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE The latter two functions were moved to test_thread.c which does not appear in Makefile.in but they are referenced by tclsqlite.c. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
I wrote: > On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > > >> Is this a garbage collection situation, where you want a row in B to go > > >> away when all referring rows in A are deleted? > > > > Yes exactly this is what I wanted :) > Maintaining a reference count in table B may be more efficient from an > I/O standpoint, but you'll probably have to do checking within app > code (instead of using SQL triggers directly), and it would likely > only pay off if table A has both a vey large number of total rows, as > well as many references to the same key in table B. It's also not as > clean from an SQL standpoint, since it's an additional item that must > be tracked, yet has nothing to do with your actual data dependencies. After thinking a bit more about implementing this, a pair of triggers can accomplish it without doing anything special in the app: CREATE TRIGGER DeRef AFTER DELETE ON A BEGIN UPDATE B SET refcount = refcount-1 WHERE key = OLD.refkey; END; CREATE TRIGGER DelOrphans AFTER UPDATE ON B WHEN NEW.refcount = 0 BEGIN DELETE FROM B WHERE rowid = NEW.rowid; END; I still favor the simpler approach, but at least this one is workable. I also choose lousy trigger names :) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] 3.5.1 build issues
A glitch or two got introduced into the 3.5.1 build for cygwin and possibly other platforms. Please advise if I did something wrong: There are undefined symbols when building testfixture: /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2511: undefined reference to `_Sqlitetest7_Init' /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2520: undefined reference to `_SqlitetestThread_Init' /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2521: undefined reference to `_SqlitetestOnefile_Init' The first function, Sqlitetest7_Init, turns up undefined due to change in #if-s in test7.c: from: #if OS_UNIX && SQLITE_THREADSAFE && \ defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE) to: #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE) #if defined(OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE The latter two functions were moved to test_thread.c which does not appear in Makefile.in but they are referenced by tclsqlite.c. Thanks, Mark
Re: [sqlite] many-one relation
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > >> Is this a garbage collection situation, where you want a row in B to go > >> away when all referring rows in A are deleted? > > Yes exactly this is what I wanted :) Okay. A trigger on table A can accomplish it without any extra tracking: CREATE TRIGGER CleanUpOrphans AFTER DELETE ON A WHEN NOT EXISTS (SELECT 1 FROM A WHERE refkey = OLD.refkey) BEGIN DELETE FROM B WHERE key = OLD.refkey; END; If table A has an index on the refkey column, it should be reasonably efficient. Maintaining a reference count in table B may be more efficient from an I/O standpoint, but you'll probably have to do checking within app code (instead of using SQL triggers directly), and it would likely only pay off if table A has both a vey large number of total rows, as well as many references to the same key in table B. It's also not as clean from an SQL standpoint, since it's an additional item that must be tracked, yet has nothing to do with your actual data dependencies. Keeping a list of referring rowids within a table B row would be hard to maintain; you'd probably instead end up with a relationship table that maps keys in table A to keys in table B -- the same thing you'd use for a classic many-to-many relationship. I don't see this being a win in any form, since it's essentially just a manual version of an index on table A's refkey column. I'd start with the no-extra-tracking approach, and only test other methods if it turns out to be too slow for your data. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
On Mon, 8 Oct 2007 09:04:53 -0700 (PDT), you wrote: >>> Is this a garbage collection situation, where >>> you want a row in B to go away when all >>> referring rows in A are deleted? > >Yes exactly this is what I wanted :) > >Sorry for the confusing message earlier > >-x- >Chetana This might be of help: http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] built in function hex
is there a built in function to display numeric values as hexidecibmal strings? to_hexstr (40)would display 0x28 Thanks, Ken
[sqlite] Re: A Question: Use of the sqlite3_exe( ) function?
Lee Crain <[EMAIL PROTECTED]> wrote: Why is it "highly recommended" to use the function call sequence you iterate in preference to the sqlite3_exe call, since it is implemented using that sequence? 1) It's usually easier to write a loop, than to break out the body of the loop into a separate callback function that can only communicate with the mainline by way of side effects. 2) It avoids unnecessary type conversions. The callback always gets the data as strings: sqlite3_column_* can get data in its original format (e.g. as integers). 3) sqlite3_exec doesn't support parameters (see sqlite3_param_*) which leads to excessive use of sprintf or similar, which in turn creates opportunities for SQL injection attacks. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: user defined function
Aviad Harell <[EMAIL PROTECTED]> wrote: I tried to create user defined function called Rank. which gets partition columns and order by column. this function should create rank according to its parameters. i.e.: select customer, product, sales, Rank(customer, sales) from table should return: customerA productA 100 0 customerA productB 300 2 customerA productC 200 1 customerA productD 400 3 customerB productB 300 1 customerB productA 400 2 customerB productC 100 0 how can i do it? should i use FunctionType scalar or aggregate? Scalar. Aggregate function looks at multiple rows and produces a single value (see SUM, MAX). A scalar function produces one value per row, which is what you want here. Note also that you don't really need a custom function here. This query should produce the same result: select customer, product, sales, (select count(*) from tableName t2 where t2.customer = t1.customer and t2.sales < t1.sales) from tableName t1; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] A Question: Use of the sqlite3_exe( ) function?
Igor, I have a question. Why is it "highly recommended" to use the function call sequence you iterate in preference to the sqlite3_exe call, since it is implemented using that sequence? Lee Crain _ -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Sunday, October 07, 2007 9:44 AM To: SQLite Subject: [sqlite] Re: Callback fonction really not flexible to use [EMAIL PROTECTED] wrote: >Here a sample (in c) of the use i would like to do with sqlite >fucntion1() call fonction2() where is sqlite3_exec() >Callback function is the function3() and i would like to add data >in an array, which is retuned to function1() after the call of > function(2). >How i can do that ? does the Callback function can return > something else than an int ? A callback function must return 0. Any non-zero return value is an error indicator. However, the callback can, and usually does, have side effects. The void* parameter you pass to sqlite3_exec is passed through to the callback. Normally, this points to some kind of a data structure that the callback modifies. Having said that, be aware that sqlite3_exec is retained for backward compatibility only. It is highly recommended for new code to use API like sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_* to iterate over the resultset. In fact, sqlite3_exec itself is implemented entirely in terms of these public API functions. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
>> Is this a garbage collection situation, where you want a row in B to go away >> when all referring rows in A are deleted? Yes exactly this is what I wanted :) Sorry for the confusing message earlier -x- Chetana - Original Message From: Trevor Talbot <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, October 8, 2007 9:10:22 PM Subject: Re: [sqlite] many-one relation On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > We got two tables, tableA & tableB. > > tableB is turning out to be a many-one relation where in we have many rows of > tableA mapping to one row of tableB, would like to know what is the best way > to delete a row in tableB > > 1. Keep a reference count of the number of rows that are referring to this ( > to be honest I dont think this is good) > 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] - Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV. http://tv.yahoo.com/
Re: [sqlite] many-one relation
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > We got two tables, tableA & tableB. > > tableB is turning out to be a many-one relation where in we have many rows of > tableA mapping to one row of tableB, would like to know what is the best way > to delete a row in tableB > > 1. Keep a reference count of the number of rows that are referring to this ( > to be honest I dont think this is good) > 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Is this a garbage collection situation, where you want a row in B to go away when all referring rows in A are deleted? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems with SQLite and PHP
On 10/8/07, Markus Wolff - NorthClick <[EMAIL PROTECTED]> wrote: > I find this to be a bit irritating - shouldn't it be enough that the > server can dive into that subdir and find a file it can actually write > to within the dir? How does it make sense that the entire directory > containing the directory must be writeable? Is this an SQLite > requirement or a PHP quirk? SQLite needs to be able to create and delete a journal file during database modification. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] user defined function
Hey, I tried to create user defined function called Rank. which gets partition columns and order by column. this function should create rank according to its parameters. i.e.: select customer, product, sales, Rank(customer, sales) from table should return: customerA productA 100 0 customerA productB 300 2 customerA productC 200 1 customerA productD 400 3 customerB productB 300 1 customerB productA 400 2 customerB productC 100 0 how can i do it? should i use FunctionType scalar or aggregate?
Re: [sqlite] Problems with SQLite and PHP
On Mon, 08 Oct 2007 13:12:08 +0200, Markus Wolff - NorthClick wrote > How does it make sense that the entire directory containing the > directory must be writeable? Sounds like a temporary file or lock file is being created. Somebody who knows more about PHP or SQLite can tell you which is taking place. Me, I'd find out what's really happening by running SQLite in strace and looking for open() calls. -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] many-one relation
Hi All, We are designing a data base landed in some problems would like to know what approach is the best. We got two tables, tableA & tableB. tableB is turning out to be a many-one relation where in we have many rows of tableA mapping to one row of tableB, would like to know what is the best way to delete a row in tableB 1. Keep a reference count of the number of rows that are referring to this ( to be honest I dont think this is good) 2. More on similar lines instead of count have row-ids and attach a trigger -x- Chetana Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get listings, and more! http://tv.yahoo.com/collections/3658
[sqlite] Re: Null-ifying the columns
chetana bhargav wrote: Want to know is there any easy way to make all the columns in a particular row to NULL other than specifying column name and NULL value, no delete as that would change the row-id Well, you could remember the ROWID, then delete the row and insert the new blank one with the old ROWID. Realize that you can explicitly specify a ROWID in the INSERT statement: insert into tableName(rowid) values(123) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems with SQLite and PHP
I have found the problem. PHP was happily reporting that the frontend.db file itself is writeable (which was true all the time), but the directory containing the database was not writeable by the webserver. I find this to be a bit irritating - shouldn't it be enough that the server can dive into that subdir and find a file it can actually write to within the dir? How does it make sense that the entire directory containing the directory must be writeable? Is this an SQLite requirement or a PHP quirk? This way I will have to make a new directory exclusively for the database, because I do not want all scripts to have write access to the whole main dir... CU Markus Am Donnerstag, den 04.10.2007, 23:37 +0200 schrieb Markus Wolff: > Hello everone, > > I'm having a really weird problem with SQLite when used with PHP - I'm > pretty sure it's not SQLite that's at fault here, but since in the > PHP-DB mailingliste nobody seemed to be able to help me, I'm hoping to > get some insights here :-) > > I'm trying to open an SQLite3 database from a PHP very simple PHP > script: > > $db = dirname(__FILE__).'/frontend.db'; > $pdo = new PDO('sqlite:'.$db); > $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > $pdo->query("SELECT * FROM page LIMIT 1"); > echo "Deleting pages\n"; > $pdo->query("DELETE FROM page"); > echo "Deleting websites\n"; > $pdo->query("DELETE FROM website"); > > The database file contains no data whatsoever, just the table > definitions (in case you were wondering, this is a stripped-down version > of a larger script for debugging purposes, hence the seemingly idiotic > DELETE statements that won't do any good in an empty database anyway, > but I digress...). > > When executed on the command line, this works perfectly. When I execute > the same script via Apache and mod_php, I'm getting this exception: > > PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or > missing database in /home/mwolff/webs/markus/cms/test.php on line 8 > > Getting experimental, I've tried to change the calls for the DELETE > statements from $pdo->query() to $pdo->exec(), just to see what happens. > Well, what happens is that I'm getting a different error: > > PDOException: SQLSTATE[HY000]: General error: 14 unable to open database > file in /home/mwolff/webs/markus/cms/test.php on line 6 > > Argh... what can possibly be wrong here? The script works from the > commandline, with the exact same PHP version (Debian package, PHP > 5.2.0-8+etch7, and we also tried upgrading to the latest Debian package > of 5.2.4, to no avail). > > It can't be file permissions, I've even tried to set the database file > to 777... no change at all. > > > For the previous discussion in the PHP-DB mailinglist, see here > (starting point, same content as above): > http://marc.info/?l=php-db&m=119134768316086&w=2 > > A detailed step-by-step list for reproducing the problem, including the > used SQL schema, can be found here: > http://marc.info/?l=php-db&m=119143000125909&w=2 > > I hope someone here can point me in the right direction, I'm getting > somewhat desperate :-) > > Thanks, > Markus > > -- Mit freundlichen Grüßen Markus Wolff Development NorthClick GmbH Gasstr. 10 - 22761 Hamburg Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998 Internet: http://www.northclick.de/ Geschäftsführer: F. Detzner | M. Henze | C. Springub Amtsgericht Hamburg, HRB 94459 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Merge two rows/records
Hi Daan, You can make the columns (a, b) unique across (a, b), but not separately unique; by that whenever you are trying to insert a row with same (a, b) combination it will give an error and at that time you can update the column values c and d. I hope this will solve your problem. Regards, Phani -Original Message- From: Daan van der Sanden [mailto:[EMAIL PROTECTED] Sent: Monday, October 08, 2007 3:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] Re: Merge two rows/records Thanks for the quick reply! Simon Davies writes: > > sqlite> select a,b,sum(c),sum(d) from foo group by a,b; > > > > gives you the data you are after. This could be used to populate > > another table via > > > > sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by > > a,b; > > > > Of course, if you can get the data you want from your existing table > > using a simple query, you may not actually need a new table. At the moment I've got a database with values gathered from multiple inputs that generated "duplicate entries" for the "what should be unique" a,b combination. So I was wondering if they could be "easily" merged without creating a new table. Now I'm going to first copy all unique samples to a new database and then insert the summed values using the given query. But this solution seems a bit awkward, since I'm copying 6 million unique records to a new database and adding a small 22.000 records that are summed. So that's why I was wondering if it could be done in the same table. I hope my problem is a bit clearer now. Kind regards Daan - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Merge two rows/records
Hi Daan, You could try this: sqlite> CREATE TABLE foo(a integer, b integer, c integer, d integer, ...> unique (a, b) on conflict ignore); sqlite> CREATE TRIGGER fooUnique before insert on foo ...> when exists (select a from foo where a=new.a and b=new.b) ...> begin update foo set c=c+new.c, d=d+new.d where a=new.a and b=new.b; ...> end; sqlite> sqlite> sqlite> INSERT INTO foo VALUES (1,1,1,2); sqlite> INSERT INTO foo VALUES (1,2,3,4); sqlite> INSERT INTO foo VALUES (1,2,5,6); sqlite> INSERT INTO foo VALUES (2,1,7,8); sqlite> INSERT INTO foo VALUES (3,1,9,10); sqlite> INSERT INTO foo VALUES (3,2,11,12); sqlite> INSERT INTO foo VALUES (3,2,13,14); sqlite> INSERT INTO foo VALUES (3,3,15,16); sqlite> sqlite> select * from foo; 1|1|1|2 1|2|8|10 2|1|7|8 3|1|9|10 3|2|24|26 3|3|15|16 sqlite> Rgds, Simon On 08/10/2007, Daan van der Sanden <[EMAIL PROTECTED]> wrote: > Thanks for the quick reply! > . . . > At the moment I've got a database with values gathered from multiple > inputs that generated "duplicate entries" for the "what should be unique" > a,b combination. So I was wondering if they could be "easily" merged > without creating a new table. > > Now I'm going to first copy all unique samples to a new database and then > insert the summed values using the given query. But this solution seems a > bit awkward, since I'm copying 6 million unique records to a new database > and adding a small 22.000 records that are summed. So that's why I was > wondering if it could be done in the same table. > > I hope my problem is a bit clearer now. > > Kind regards > Daan > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] CONFIG_FPE_FASTFPE and the text representation of a float
L.S. Here's the patch for those interested: --- sqlite-3.4.2-orig/src/printf.c 2007-06-28 14:46:19.0 +0200 +++ sqlite-3.4.2/src/printf.c 2007-10-08 11:56:49.0 +0200 @@ -158,7 +158,11 @@ static int et_getdigit(LONGDOUBLE_TYPE *val, int *cnt){ int digit; LONGDOUBLE_TYPE d; +#ifdef SQLITE_FPE_FASTFPE + if( (*cnt)++ >= 10 ) return '0'; +#else if( (*cnt)++ >= 16 ) return '0'; +#endif /* SQLITE_FPE_FASTFPE */ digit = (int)*val; d = digit; digit += '0'; and diff -u -r sqlite-3.4.2-orig/src/vdbemem.c sqlite-3.4.2/src/vdbemem.c --- sqlite-3.4.2-orig/src/vdbemem.c 2007-06-28 14:46:19.0 +0200 +++ sqlite-3.4.2/src/vdbemem.c 2007-10-08 11:58:07.0 +0200 @@ -217,7 +217,11 @@ sqlite3_snprintf(NBFS, z, "%lld", pMem->u.i); }else{ assert( fg & MEM_Real ); +#ifdef SQLITE_FPE_FASTFPE +sqlite3_snprintf(NBFS, z, "%!.9g", pMem->r); +#else sqlite3_snprintf(NBFS, z, "%!.15g", pMem->r); +#endif /* SQLITE_FPE_FASTFPE */ } pMem->n = strlen(z); pMem->z = z; FYI, the (latest?) 2.6 kernels seem to have ditched FASTFPE completely, so the above is probably a 2.4 - issue only. -- Best, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Merge two rows/records
Thanks for the quick reply! Simon Davies writes: > sqlite> select a,b,sum(c),sum(d) from foo group by a,b; > > gives you the data you are after. This could be used to populate > another table via > > sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by > a,b; > > Of course, if you can get the data you want from your existing table > using a simple query, you may not actually need a new table. At the moment I've got a database with values gathered from multiple inputs that generated "duplicate entries" for the "what should be unique" a,b combination. So I was wondering if they could be "easily" merged without creating a new table. Now I'm going to first copy all unique samples to a new database and then insert the summed values using the given query. But this solution seems a bit awkward, since I'm copying 6 million unique records to a new database and adding a small 22.000 records that are summed. So that's why I was wondering if it could be done in the same table. I hope my problem is a bit clearer now. Kind regards Daan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems with SQLite and PHP
Am Freitag, den 05.10.2007, 01:03 +0200 schrieb Kees Nuyt: > I can't reproduce the exception (the PDO->query > version) on an environment I happened to have > available. > > - MS Windows XP Professional (5.2 build 2600) > - Apache 2.2.4 > - PHP 5.2.2 > - PDO SQLite 1.0.1 2007/03/23 > - SQLite library 3.3.16 > > Perhaps I'll try again tomorrow with the > same installation, but using > - SQLite library 3.4.2 > > The only thing i can think of (wild guess) is your > php-cli uses another php.ini than the Apache module does. Hi Kees, it's true that the php.ini is different for the CLI and mod_php variants - that's actually quite a common setup. The library version used by PHP seems to be consistent with the SQLite commandline tool, though: $ php -i | grep -i sqlite /etc/php5/cli/conf.d/02_pdo_sqlite.ini, /etc/php5/cli/conf.d/03_sqlite3.ini, /etc/php5/cli/conf.d/04_sqlite.ini, PDO drivers => sqlite, mysql pdo_sqlite PDO Driver for SQLite 3.x => enabled PECL Module version => 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6 2006/01/01 12:50:12 sniper Exp $ SQLite Library => 3.4.2 SQLITE3 SQLite3 support => enabled sqlite3 library version => 3.4.2 The phpinfo() output from mod_php states exactly the same version. CU Markus - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems with SQLite and PHP
Hi Joe, standard temp directory in my setup is /tmp, which is both readable and writeable by Apache. Available space is 21GB, which should be more than sufficient for the task at hand :-) CU Markus Am Donnerstag, den 04.10.2007, 16:13 -0700 schrieb Joe Wilson: > See if the apache/mod_php process' unix account can read/write to at > least one of the following directories and there is sufficient disk > space in the first such directory that is r/w. > > pragma temp_store_directory; -- if present. As run in apache. > /var/tmp > /usr/tmp > /tmp > . > > --- Markus Wolff <[EMAIL PROTECTED]> wrote: > > I'm trying to open an SQLite3 database from a PHP very simple PHP > > script: > > > > $db = dirname(__FILE__).'/frontend.db'; > > $pdo = new PDO('sqlite:'.$db); > > $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > > $pdo->query("SELECT * FROM page LIMIT 1"); > > echo "Deleting pages\n"; > > $pdo->query("DELETE FROM page"); > > echo "Deleting websites\n"; > > $pdo->query("DELETE FROM website"); > > > > The database file contains no data whatsoever, just the table > > definitions (in case you were wondering, this is a stripped-down version > > of a larger script for debugging purposes, hence the seemingly idiotic > > DELETE statements that won't do any good in an empty database anyway, > > but I digress...). > > > > When executed on the command line, this works perfectly. When I execute > > the same script via Apache and mod_php, I'm getting this exception: > > > > PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or > > missing database in /home/mwolff/webs/markus/cms/test.php on line 8 > > > > > Shape Yahoo! in your own image. Join our Network Research Panel today! > http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- Mit freundlichen Grüßen Markus Wolff Development NorthClick GmbH Gasstr. 10 - 22761 Hamburg Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998 Internet: http://www.northclick.de/ Geschäftsführer: F. Detzner | M. Henze | C. Springub Amtsgericht Hamburg, HRB 94459 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Merge two rows/records
On 08/10/2007, Daan van der Sanden <[EMAIL PROTECTED]> wrote: > Hi, > > Is it possible to merge two rows with SQLite? Say I have the following table": > CREATE TABLE foo(a integer, b integer, c integer, d integer); > CREATE INDEX idx ON foo(a,b); > > With the following data: > > INSERT INTO foo VALUES (1,1,1,2); > INSERT INTO foo VALUES (1,2,3,4); > INSERT INTO foo VALUES (1,2,5,6); > INSERT INTO foo VALUES (2,1,7,8); > INSERT INTO foo VALUES (3,1,9,10); > INSERT INTO foo VALUES (3,2,11,12); > INSERT INTO foo VALUES (3,2,13,14); > INSERT INTO foo VALUES (3,3,15,16); > > What I would like is that when the combenation of a and b are not unique to > merge the two rows to one and sum the values b and c. So the resulting table > should become: > > a | b | c | d > ---+---+---+---+ > 1 | 1 | 1 | 2 > 1 | 2 | 8 | 10 > 2 | 1 | 7 | 8 > 3 | 1 | 9 | 10 > 3 | 2 | 24| 26 > 3 | 3 | 15| 16 > > I know how to select the two duplicate rows (ensum them, but. I use the query: > SELECT * FROM foo GROUP BY a,b HAVING count(*)>1; to select them. > > But how do I merge them? Do I need a self written program to itterate throuw > the result of the previous query to merge them or can this be done > on the sqlite3 program with a few "simple" querys? > > Thanks in advance! > Kind Regards > Daan Hi Daan, sqlite> select a,b,sum(c),sum(d) from foo group by a,b; gives you the data you are after. This could be used to populate another table via sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by a,b; Of course, if you can get the data you want from your existing table using a simple query, you may not actually need a new table. Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Null-ifying the columns
SQL Guru's, Want to know is there any easy way to make all the columns in a particular row to NULL other than specifying column name and NULL value, no delete as that would change the row-id -x- Chetana Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545433
[sqlite] Merge two rows/records
Hi, Is it possible to merge two rows with SQLite? Say I have the following table": CREATE TABLE foo(a integer, b integer, c integer, d integer); CREATE INDEX idx ON foo(a,b); With the following data: INSERT INTO foo VALUES (1,1,1,2); INSERT INTO foo VALUES (1,2,3,4); INSERT INTO foo VALUES (1,2,5,6); INSERT INTO foo VALUES (2,1,7,8); INSERT INTO foo VALUES (3,1,9,10); INSERT INTO foo VALUES (3,2,11,12); INSERT INTO foo VALUES (3,2,13,14); INSERT INTO foo VALUES (3,3,15,16); What I would like is that when the combenation of a and b are not unique to merge the two rows to one and sum the values b and c. So the resulting table should become: a | b | c | d ---+---+---+---+ 1 | 1 | 1 | 2 1 | 2 | 8 | 10 2 | 1 | 7 | 8 3 | 1 | 9 | 10 3 | 2 | 24| 26 3 | 3 | 15| 16 I know how to select the two duplicate rows (ensum them, but. I use the query: SELECT * FROM foo GROUP BY a,b HAVING count(*)>1; to select them. But how do I merge them? Do I need a self written program to itterate throuw the result of the previous query to merge them or can this be done on the sqlite3 program with a few "simple" querys? Thanks in advance! Kind Regards Daan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] why sqlite3_prepare could return SQLITE_BUSY in single thread program?
Why sqlite3_prepare (... , "select name from sqlite_master where type = 'table' order by name", ...) could return SQLITE_BUSY or SQLITE_OK dependent on database size? I'm using Sqlite 3.3.7 and single thread execution. I have database with about 20 tables and some tables hold about 50'000 records and I get stable crash trying above statement when one table exceeds some magic limit (about 46'000 records). All I have to execute is sqlite3_open and sqlite3_prepare. And what's even more strange sqlite3.exe executes this query on the same database with no trouble. - To unsubscribe, send email to [EMAIL PROTECTED] -