Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
Hi Smith, Is there any way where I can free the old pages and without using rollback feature. Because I'm much concerned about memory usage. As soon as I delete some records, It should free up the memory. Thanks On 8/1/07, Christian Smith [EMAIL PROTECTED] wrote: Lokesh Babu uttered: Hello Folks, When I perform the DELETE operation on a Table using In-Memory Database (:memory:), the memory usage increases. I tried using PRAGMA auto_vacuum=1; /* result - nothing works */ Even I tried using VACUUM table_name; /* this too isn't work */ if I perform DROP the table operation, memory usage works. Deleting the rows doesn't work. Anybody please help me in resolving this. Memory usage goes up because SQLite must temporarily store copies of the old pages that store the deleted rows, in order to allow a future rollback if required. Once a commit is done, the old pages are free'd, but by that time the memory footprint has already increased. Not many libc implementations release heap memory back to the OS once it's allocated. -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] extension-functions.tgz for sqlite3 3.4.1 ?
Hi, I wanted to get a LOG10() function in sqlite3, and I found the extension-functions.tgz file in http://sqlite.org/contrib I am using the all-in-one sqlite3.h/c version of sqlite3, and the extension-functions files don't seem to fit at all. For example, it wants to call a function called sqlite3CreateFunc(), which seems to have been renamed to sqlite3_create_function() Should I even bother trying to hack these files into shape, or has sqlite3 changed so much that these files will only serve to introduce bugs? Does anyone use this contribution? Does anyone use it with a recent sqlite3 release? thanks Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
are you saying this is a memory leak? sqlite never gives back the unused memory? Christian Smith wrote: Lokesh Babu uttered: Hello Folks, When I perform the DELETE operation on a Table using In-Memory Database (:memory:), the memory usage increases. I tried using PRAGMA auto_vacuum=1; /* result - nothing works */ Even I tried using VACUUM table_name; /* this too isn't work */ if I perform DROP the table operation, memory usage works. Deleting the rows doesn't work. Anybody please help me in resolving this. Memory usage goes up because SQLite must temporarily store copies of the old pages that store the deleted rows, in order to allow a future rollback if required. Once a commit is done, the old pages are free'd, but by that time the memory footprint has already increased. Not many libc implementations release heap memory back to the OS once it's allocated. Thanks in advance, Lokee -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - -- - As nightfall does not come at once, neither does oppression. In both instances, there is a twilight when everything remains seemingly unchanged. And it is in such twilight that we all must be most aware of change in the air however slight lest we become unwitting victims of the darkness. William O. Douglas, Justice of the U.S. Supreme Court - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
Lokesh Babu uttered: Hi Smith, Is there any way where I can free the old pages and without using rollback feature. Because I'm much concerned about memory usage. As soon as I delete some records, It should free up the memory. Use a libc that has a malloc implementation that releases excess memory back to the operating system. I don't know of specific instances of libc that do this, so I can't help further, sorry. Thanks On 8/1/07, Christian Smith [EMAIL PROTECTED] wrote: Lokesh Babu uttered: Hello Folks, When I perform the DELETE operation on a Table using In-Memory Database (:memory:), the memory usage increases. I tried using PRAGMA auto_vacuum=1; /* result - nothing works */ Even I tried using VACUUM table_name; /* this too isn't work */ if I perform DROP the table operation, memory usage works. Deleting the rows doesn't work. Anybody please help me in resolving this. Memory usage goes up because SQLite must temporarily store copies of the old pages that store the deleted rows, in order to allow a future rollback if required. Once a commit is done, the old pages are free'd, but by that time the memory footprint has already increased. Not many libc implementations release heap memory back to the OS once it's allocated. -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
just enter a ; (semi-colon) to indicate that you have ended your command. SQLite will complain, and then will let you pick up and continue. On 8/2/07, Scott Derrick [EMAIL PROTECTED] wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott -- - As nightfall does not come at once, neither does oppression. In both instances, there is a twilight when everything remains seemingly unchanged. And it is in such twilight that we all must be most aware of change in the air however slight lest we become unwitting victims of the darkness. William O. Douglas, Justice of the U.S. Supreme Court - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ ST Policy Fellow, National Academy of Sciences http://www.nas.edu/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UI question
This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott -- - As nightfall does not come at once, neither does oppression. In both instances, there is a twilight when everything remains seemingly unchanged. And it is in such twilight that we all must be most aware of change in the air however slight lest we become unwitting victims of the darkness. William O. Douglas, Justice of the U.S. Supreme Court - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UI question
-Original Message- From: Scott Derrick [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 10:22 AM To: sqlite-users@sqlite.org Subject: [sqlite] UI question This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott = Hi Scott, The command line utility thinks you are continuing a long SQL command. A semicolon (;) will terminate it, put you back to command mode, and allow you to .quit - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
Scott Derrick [EMAIL PROTECTED] wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Type a semicolon on a line by itself. You'll then get a syntax error and you will be back at the command prompt. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
On Thu, 2007-08-02 at 08:21 -0600, Scott Derrick wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? It means keep typing, the SQL statement isn't finished yet. Add a semi-colon and press enter. The shell will figure the SQL statement is complete and hand it off to the sqlite libary for execution. The SQLite libary will tell you it's a syntax error. Dan. Scott - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: UI question
Scott Derrick [EMAIL PROTECTED] wrote: When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? ... is a continuation prompt. It allows you to type a long query on multiple lines. The shell does a simple parsing to see whether the text so far forms a complete statement - essentially, it looks for the final semicolon. To get out of this mode, simply type a semicolon and hit Enter. Whatever you typed so far will be interpreted as a statement, most likely resulting in a syntax error. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? It's a continuation line, which allows your SQL statements to span multiple lines. Just hit ;enter and you'll receive an error that says invalid sql or something similar, and you'll be able to enter another command. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
On Thu, 02 Aug 2007 08:21:30 -0600, Scott Derrick wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott Try typing the semi-colon to terminate the sql statement. You should get a syntax error report and no further action. The command prompt should appear on the next line. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE_BUSY database is locked when db is on network drive...
running sqlite 3.4.1 on mac os x 10.4 i've set up a file share on another mac running 10.4 and placed a small db file on the share and chmod'ed it to 777 (full access). i go to the development mac and log into the other mac as the user who owns that db file. the path on my dev mac to that shared db is now: /Volumes/SharedFolder/smalldb.db the test app (which works perfectly with the same exact small db sitting on its local drive) is launched and a connection is made to the db sitting on the other mac. it connects fine. no errors. but then i try to create a temp table (which, like i said, works if the db is local) it fails immediately with SQLITE_BUSY database is locked. NO ONE else is accessing this database file, so how is that possible? what am i doing wrong? - chase - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
also if the semicolon does not get you back to the prompt you may have an open quoted string so try a ; to close it and get you back. Quoting Dan Kennedy [EMAIL PROTECTED]: On Thu, 2007-08-02 at 08:21 -0600, Scott Derrick wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? It means keep typing, the SQL statement isn't finished yet. Add a semi-colon and press enter. The shell will figure the SQL statement is complete and hand it off to the sqlite libary for execution. The SQLite libary will tell you it's a syntax error. Dan. Scott - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] extension-functions.tgz for sqlite3 3.4.1 ?
--- Paul Harris [EMAIL PROTECTED] wrote:
I wanted to get a LOG10() function in sqlite3, and I found the
extension-functions.tgz file in http://sqlite.org/contrib
I am using the all-in-one sqlite3.h/c version of sqlite3, and the
extension-functions files don't seem to fit at all.
For example, it wants to call a function called sqlite3CreateFunc(),
which seems to have been renamed to sqlite3_create_function()
Should I even bother trying to hack these files into shape, or has
sqlite3 changed so much that these files will only serve to introduce
bugs?
The extension is somewhat out of date, but still usable.
sqlite3CreateFunc is an internal sqlite function to register
the extension functions, making it incompatible with a seperately
compiled sqlite3.c. sqlite3utf8CharLen is another internal
function used by the extension.
You could convert all the registration functions to use the external
API, or drop this file into sqlite/src and update the standard makefile.
If you want to live on the edge, put map.h, map.c, func_ext.c,
sqlite3.c and sqlite3.h in the same directory and run this:
sed 's/sqlite3RegisterBuiltinFunctions(db);/ {extern void
sqlite3RegisterExtraFunctions(sqlite3
*db); sqlite3RegisterExtraFunctions(db);};/' sqlite3.c sqlite3f.c
echo sqlite3f.c
echo #include errno.h sqlite3f.c
echo sqlite3f.c
sed 's/#include.*//' map.h map.c func_ext.c | \
sed 's/sqlite3utf8CharLen/sqlite3Utf8CharLen/' sqlite3f.c
Then use sqlite3f.c instead of sqlite3.c to build your program.
If you're on Windows, download and install Cygwin or MSYS to get these
UNIX commands to build sqlite3f.c.
# optional: build sqlite3 command-line shell.
# shell.c must be in current directory.
gcc sqlite3f.c shell.c -o sqlite3f
Pinpoint customers who are looking for what you sell.
http://searchmarketing.yahoo.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] SQLITE_BUSY database is locked when db is on network drive...
running sqlite 3.4.1 on mac os x 10.4 i've set up a file share on another mac running 10.4 and placed a small db file on the share and chmod'ed it to 777 (full access). i go to the development mac and log into the other mac as the user who owns that db file. the path on my dev mac to that shared db is now: /Volumes/SharedFolder/smalldb.db the test app (which works perfectly with the same exact small db sitting on its local drive) is launched and a connection is made to the db sitting on the other mac. it connects fine. no errors. but then i try to create a temp table (which, like i said, works if the db is local) it fails immediately with SQLITE_BUSY database is locked. NO ONE else is accessing this database file, so how is that possible? what am i doing wrong? - chase - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
It is a bug in sqlite3. You can kill the process from another channel or supend it with a CTL Z and then kill it locally. Scott Derrick wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? Drink a glass of water and have someone scare you. ...Umm, nevermind, that's for hiccups. Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] database is locked SQLITE_BUSY when db is on network drive...
running sqlite 3.4.1 on mac os x 10.4 i've set up a file share on another mac running 10.4 and placed a small db file on the share and chmod'ed it to 777 (full access). i go to the development mac and log into the other mac as the user who owns that db file. the path on my dev mac to that shared db is now: /Volumes/SharedFolder/smalldb.db the test app (which works perfectly with the same exact small db sitting on its local drive) is launched and a connection is made to the db sitting on the other mac. it connects fine. no errors. but then i try to create a temp table (which, like i said, works if the db is local) it fails immediately with SQLITE_BUSY database is locked. NO ONE else is accessing this database file, so how is that possible? what am i doing wrong? - chase - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Custom GLOBing function
I have a query of the following form using SQLite's built in GLOB function: SELECT * FROM foo WHERE bar GLOB '*' I implemented a custom glob(x,y) function to replace the built-in GLOB, registering it with: sqlite3_create_function(db, glob, 2, SQLITE_ANY, NULL, my_glob, NULL, NULL); This all worked fine. My custom function was called for the above query and worked as I expected. I then decided it would be best to not universally replace the built-in GLOB, but only use my custom function on the queries that needed it. I changed my registration to: sqlite3_create_function(db, my_glob, 2, SQLITE_ANY, NULL, my_glob, NULL, NULL); and then tried modifying my query to use the following forms: SELECT * FROM foo WHERE bar MY_GLOB '*' SELECT * FROM foo WHERE MY_GLOB(bar, '*') SELECT * FROM foo WHERE bar my_glob '*' SELECT * FROM foo WHERE my_glob(bar, '*') They all gave SQL errors when I tried to pass them to prepare... what am I doing wrong? I feel like I'm missing something simple. Thanks. -Shane
[sqlite] how do i declare and use variables in sqlite?
select 542 as x; that part works, but then when i try to access it, i get no column x... select x; set x = 542; var x = 542; @set x = 542; @var x = 542; set @x = 542; var @x = 542; $set x = 542; $var x = 542; set $x = 542; var $x = 542; none of these seem to work and i can't find the documentation on this subject. can someone shed some light on this for me? thanks. - chase - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Parser information
Hi Ken, Do you have an answer to what external api we can use to get the Select parse tree? Rohit On 7/31/07, Joe Wilson [EMAIL PROTECTED] wrote: --- Ken [EMAIL PROTECTED] wrote: You should be using the external API calls not the internal sqlite calls and types. Please point us to where you can get the Select parse tree from the external API. See: http://www.sqlite.org/capi3ref.html 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] -
[sqlite] Implementation of ANSI SQL-92 FOREIGN KEY and referential integrity
Hi, Does anyone know if there is a plan to implement the enforcement of the SQL-92 FOREIGN KEY constraints? Seems to me the No.1 missing feature. After all, data integrity, even in an embedded DB is very important and bugs in client code can easily mess up the referential integrity. Does appear odd that SQLite implements some of the less useful SQL-92 features and even goes as far as to parse FOREIGN KEY constraints but not raise an error when it encounters one? Thanks in advance. PS. I know you can sort of implement this with a load of triggers, but that seems a bit daft. -- View this message in context: http://www.nabble.com/Implementation-of-ANSI-SQL-92-FOREIGN-KEY-and-referential-integrity-tf4208807.html#a11972903 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query Indexes
Is there any way to determine if a query is using an index or not? In
PostgreSQL the explain works to tell whether a table is being
sequentially scanned or not..
I have a query :
SELECT *,(total - balance_due) as total_paid FROM invoice_master WHERE
lower(invoice_number) LIKE lower('%%') AND status != 'Void' AND
status != 'Recur' AND status != 'Paid' AND status != 'Forwarded'
ORDER BY created ASC LIMIT 25
The lower('%%') gets used with whatever field the user is searching on.
I have indexes on created, status and invoice_number - but apparently
I can't make an index on lower(invoice_number) -- can I?
--
- Mitchell Vincent
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] database is locked SQLITE_BUSY when db is on network drive...
Can you do any other write queries (INSERT or UPDATE for example)? On 8/2/07, Chase [EMAIL PROTECTED] wrote: running sqlite 3.4.1 on mac os x 10.4 i've set up a file share on another mac running 10.4 and placed a small db file on the share and chmod'ed it to 777 (full access). i go to the development mac and log into the other mac as the user who owns that db file. the path on my dev mac to that shared db is now: /Volumes/SharedFolder/smalldb.db the test app (which works perfectly with the same exact small db sitting on its local drive) is launched and a connection is made to the db sitting on the other mac. it connects fine. no errors. but then i try to create a temp table (which, like i said, works if the db is local) it fails immediately with SQLITE_BUSY database is locked. NO ONE else is accessing this database file, so how is that possible? what am i doing wrong? - chase - To unsubscribe, send email to [EMAIL PROTECTED] - -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
Sqlite3 will get into a tangle with certain sequences where it does not accept a semicolon as a terminator or obey a CTL c. Chris Peachment wrote: On Thu, 02 Aug 2007 08:21:30 -0600, Scott Derrick wrote: This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott Try typing the semi-colon to terminate the sql statement. You should get a syntax error report and no further action. The command prompt should appear on the next line. Chris - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Custom GLOBing function
Shane Harrelson [EMAIL PROTECTED] wrote: I changed my registration to: sqlite3_create_function(db, my_glob, 2, SQLITE_ANY, NULL, my_glob, NULL, NULL); and then tried modifying my query to use the following forms: SELECT * FROM foo WHERE bar MY_GLOB '*' SELECT * FROM foo WHERE MY_GLOB(bar, '*') SELECT * FROM foo WHERE bar my_glob '*' SELECT * FROM foo WHERE my_glob(bar, '*') They all gave SQL errors when I tried to pass them to prepare... The last statement should work. What error do you get? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
--- John Stanton [EMAIL PROTECTED] wrote: Sqlite3 will get into a tangle with certain sequences where it does not accept a semicolon as a terminator or obey a CTL c. To reproduce: 1. build sqlite3 without readline support. 2. run sqlite3 in an xterm 3. at the prompt, press cursor up 4. hit return Nothing you do at this point will work except for Ctrl-\ to kill the process. SQLite version 3.4.1 Enter .help for instructions sqlite ^[[A ... .q ... ; ... select 1; ... .q ... .h ... ; ... ; Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: how do i declare and use variables in sqlite?
Chase [EMAIL PROTECTED] wrote: how do i declare and use variables in sqlite? You don't. You declare and use variables in whatever program you write that runs SQLite statements. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how do i declare and use variables in sqlite?
-Original Message- From: Chase [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 12:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] how do i declare and use variables in sqlite? select 542 as x; that part works, but then when i try to access it, i get no column x... select x; set x = 542; var x = 542; @set x = 542; @var x = 542; set @x = 542; var @x = 542; $set x = 542; $var x = 542; set $x = 542; var $x = 542; none of these seem to work and i can't find the documentation on this subject. can someone shed some light on this for me? AFAIK, this isn't something you do with SQLite. You can retrieve values into variables in the host language (C, C++, or some wrapping language), and you can bind their values into SQL statements. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
On Thu, 2 Aug 2007 14:16:28 -0700 (PDT), Joe Wilson wrote: --- John Stanton [EMAIL PROTECTED] wrote: Sqlite3 will get into a tangle with certain sequences where it does not accept a semicolon as a terminator or obey a CTL c. To reproduce: 1. build sqlite3 without readline support. 2. run sqlite3 in an xterm 3. at the prompt, press cursor up 4. hit return Nothing you do at this point will work except for Ctrl-\ to kill the process. SQLite version 3.4.1 Enter .help for instructions sqlite ^[[A ... .q ... ; ... select 1; ... .q ... .h ... ; ... ; Is this a weakness in the scanner or its state table? Is readline() providing the command history feature that is so valuable during repetitive debugging? And for which the up arrow is an essential keystroke? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Index Creation Questions
Trey Mack [EMAIL PROTECTED] wrote: Enough words.. concrete example: CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT, b TEXT, c TEXT); CREATE INDEX i1 ON t (a); -- is this sufficient? CREATE INDEX i2 ON t (a, id); -- or is this necessary to avoid going back to the original table? Every index automatically includes the ROWID of the table - that's how SQLite links from the index entry to the corresponding table row. You don't need to explicitly include the id in the index. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: how do i declare and use variables in sqlite?
[In the message [sqlite] Re: how do i declare and use variables in sqlite? on Aug 2, 18:22, Igor Tandetnik writes:] Chase [EMAIL PROTECTED] wrote: how do i declare and use variables in sqlite? You don't. You declare and use variables in whatever program you write that runs SQLite statements. Igor Tandetnik This is certainly true, but there might be cases where one wants to keep certain results between sql statements and reuse them. While this conceptual solution is certainly not the Variable it sort of gets the job done. create temporary table vars (name text, value something); -- insert into vars set name=x, value=0; -- ... where something = (select value from vars where name=x)... I real life you might want to create better named temporary table directly with select and use its values without having to search through names. If this is slow you can attach in memory database which would then be used to store variables. Nikola - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how do i declare and use variables in sqlite?
On 03/08/07, James Dennett [EMAIL PROTECTED] wrote: -Original Message- From: Chase [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 12:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] how do i declare and use variables in sqlite? select 542 as x; that part works, but then when i try to access it, i get no column x... select x; set x = 542; var x = 542; @set x = 542; @var x = 542; set @x = 542; var @x = 542; $set x = 542; $var x = 542; set $x = 542; var $x = 542; none of these seem to work and i can't find the documentation on this subject. can someone shed some light on this for me? AFAIK, this isn't something you do with SQLite. You can retrieve values into variables in the host language (C, C++, or some wrapping language), and you can bind their values into SQL statements. in MySQL, you can do something like: select @xyz := column from table where id=1; can this be done in Sqlite? is there an equivalent in standard sql ? cheers Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Index Creation Questions
I'm creating some indices, and want to know if it's going to help or hurt me (or be totally irrelevant because of optimization) to include the primary key at the end of the index. All of my tables (in question) have an INTEGER PRIMARY KEY AUTOINCREMENT field, and I have several textual descriptors for the tables in question. I want the fastest lookups (of course) of the PRIMARY KEY value given the text descriptor(s). It is my understanding that each index is a btree ordered starting with whatever columns you're indexing, and the rowid for subsequent lookups of fields not included in the index. The rowid in my case is, of course, the same as the PRIMARY KEY that I'm after, so I believe this will skip a lookup in the actual table. Enough words.. concrete example: CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT, b TEXT, c TEXT); CREATE INDEX i1 ON t (a); -- is this sufficient? CREATE INDEX i2 ON t (a, id); -- or is this necessary to avoid going back to the original table? CREATE INDEX i3 ON t (b); CREATE INDEX i4 ON t (b, id); CREATE INDEX i5 ON t (c); CREATE INDEX i6 ON t (c, id); Also, a, b, and c may be constrained independently or together. So, should I also create a composite index including them all? In every possible ordering? (a,b,c) (a,c,b) (b,a,c) (b,c,a) (c,a,b) (c,b,a) I guess that would cover all bases, but seems like overkill. I think the answer to this question is just let a,b and c ride independently as in the earlier indices, and I'll have constrained alot, though not as fully as possible. Thanks in advance for any insight, Trey
Re: [sqlite] Problem with SQLite FastCGI module malformed database schema
On Wed, Aug 01, 2007 at 03:04:35PM -0700, Joe Wilson wrote: Maybe the php wrapper is not closing sqlite connections correctly (or at all). See if you can get the stack traces of all threads in the php process 162 via pstack, lsstack or by attaching gdb to it at runtime. Before I'll make it: does there exist any possibility, that I made some mistake in PHP-scripts? If I correctly recall, even ev. omitting of sqlite_close function can't make any problem, because the connection is always closed even without this, just when end of script has been reached. Are any script-related (not bugs in PHP-module) reasons possible? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
It seems to be connected with xterm. Joe Wilson wrote: --- John Stanton [EMAIL PROTECTED] wrote: Sqlite3 will get into a tangle with certain sequences where it does not accept a semicolon as a terminator or obey a CTL c. To reproduce: 1. build sqlite3 without readline support. 2. run sqlite3 in an xterm 3. at the prompt, press cursor up 4. hit return Nothing you do at this point will work except for Ctrl-\ to kill the process. SQLite version 3.4.1 Enter .help for instructions sqlite ^[[A ... .q ... ; ... select 1; ... .q ... .h ... ; ... ; Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Implementation of ANSI SQL-92 FOREIGN KEY and referential integrity
At 2:11 PM -0700 8/2/07, Mikey C wrote: Does anyone know if there is a plan to implement the enforcement of the SQL-92 FOREIGN KEY constraints? Seems to me the No.1 missing feature. After all, data integrity, even in an embedded DB is very important and bugs in client code can easily mess up the referential integrity. I believe that this is planned; however, some other planned upates have higher priority and are being done first. Note also that traditional foreign key constraints can only implement some kinds of business rules, and there are lots of others whose violation could cause problems, that foreign keys wouldn't help with; in theory, the more generic format of triggers is a more complete solution, or more specifically, free-form check constraints that can be comprised of any query are a more complete solution. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_update_hook does not get called when other app updates shared db
When i call sqlite3_update_hook() from App A and point it to my callback, i am only notified when app A updates the database. When app B (another instance of the same app as A) updates the database, app A's callback is never called, and vice versa. I thought the whole point of this function was to get callbacks on any **outside** tinkering with a shared database file. Can someone please shed some light on this. Thank you. - Chase - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: how do i declare and use variables in sqlite?
Paul Harris [EMAIL PROTECTED] wrote: On 03/08/07, Nikola Miljkovic [EMAIL PROTECTED] wrote: This is certainly true, but there might be cases where one wants to keep certain results between sql statements and reuse them. While this conceptual solution is certainly not the Variable it sort of gets the job done. create temporary table vars (name text, value something); -- insert into vars set name=x, value=0; -- ... where something = (select value from vars where name=x)... I tried doing this, but there doesn't seem to be a way to do the same thing with an UPDATE command ? No way to do what with UPDATE command? What exactly are you trying to do, and failing? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: how do i declare and use variables in sqlite?
Nikola Miljkovic
[EMAIL PROTECTED] wrote:
create temporary table vars (name text, value something);
insert into vars set name=x, value=0;
Surely you mean
insert into vars(name, value) values('x', 0);
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Parser information
You already have the answer to your question. There's no need for badgering. --- Rohit Mordani [EMAIL PROTECTED] wrote: Hi Ken, Do you have an answer to what external api we can use to get the Select parse tree? Rohit On 7/31/07, Joe Wilson [EMAIL PROTECTED] wrote: --- Ken [EMAIL PROTECTED] wrote: You should be using the external API calls not the internal sqlite calls and types. Please point us to where you can get the Select parse tree from the external API. See: http://www.sqlite.org/capi3ref.html Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: how do i declare and use variables in sqlite?
Paul Harris [EMAIL PROTECTED] wrote: in MySQL, you can do something like: select @xyz := column from table where id=1; can this be done in Sqlite? No. is there an equivalent in standard sql? No. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: how do i declare and use variables in sqlite?
On 03/08/07, Nikola Miljkovic [EMAIL PROTECTED] wrote: [In the message [sqlite] Re: how do i declare and use variables in sqlite? on Aug 2, 18:22, Igor Tandetnik writes:] Chase [EMAIL PROTECTED] wrote: how do i declare and use variables in sqlite? You don't. You declare and use variables in whatever program you write that runs SQLite statements. Igor Tandetnik This is certainly true, but there might be cases where one wants to keep certain results between sql statements and reuse them. While this conceptual solution is certainly not the Variable it sort of gets the job done. create temporary table vars (name text, value something); -- insert into vars set name=x, value=0; -- ... where something = (select value from vars where name=x)... I real life you might want to create better named temporary table directly with select and use its values without having to search through names. If this is slow you can attach in memory database which would then be used to store variables. I tried doing this, but there doesn't seem to be a way to do the same thing with an UPDATE command ? Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Query Indexes
Mitchell Vincent [EMAIL PROTECTED]
wrote:
Is there any way to determine if a query is using an index or not? In
PostgreSQL the explain works to tell whether a table is being
sequentially scanned or not..
Prepend the query with EXPLAIN QUERY PLAN
I have a query :
SELECT *,(total - balance_due) as total_paid FROM invoice_master WHERE
lower(invoice_number) LIKE lower('%%') AND status != 'Void' AND
status != 'Recur' AND status != 'Paid' AND status != 'Forwarded'
ORDER BY created ASC LIMIT 25
The lower('%%') gets used with whatever field the user is searching
on.
I have indexes on created, status and invoice_number - but apparently
I can't make an index on lower(invoice_number) -- can I?
You can't. You can, however, create an index on invoice_number with
COLLATE NOCASE clause.
Note that condition lower(invoice_number) LIKE lower('%%') is always
true - any string matches this pattern. What precisely is this supposed
to achieve, and how an index on lower(invoice_number) is expected to
help here?
A condition on status can be more compactly written as
status NOT IN ('Void', 'Recur', 'Paid', 'Forwarded')
The query as written should use an index on created to implement ORDER
BY clause.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Re: how do i declare and use variables in sqlite?
create temporary table vars (name text, value something); -- insert into vars set name=x, value=0; -- ... where something = (select value from vars where name=x)... I tried doing this, but there doesn't seem to be a way to do the same thing with an UPDATE command ? No way to do what with UPDATE command? What exactly are you trying to do, and failing? i've just realised the last statement (...where etc etc) is probably supposed to be part of a select statement. anyway, this is what i'm trying to do: eg 1 select @somevar := column1 from table1; update table2 set column2 = @somevar; eg2 update table1,table2 set column2=column1; eg3 (using temp table - how to do this?) create temporary table vars (name text, value something); insert into vars set name=x, value=0; update vars,table2 set column2=value where name = 'x'; something like that. thanks, Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Replacing Clipper DB lookup application
I have an old Clipper DBF type application that has worked well but times changed and changed long ago. The feature of this DBF, non-GUI, text oriented, application is that it is mostly a list lookup application with few DB changes. A separate Admin program does list management. The list is about 10,000 people long. New requirements justify a rewrite of the programs. Rewrite environments include Mono's C#, JAVA, Python, C++, et. al. , all of which appear supported by SQLite bindings. The Clipper application is very fast at these lookups which are at a customer service desk where there is a queue of people impatiently waiting in line. Using an incremental lookup text entry box the list quickly narrows to just a few entries and then a quick cursor pick chooses the correct record. Clipper DBF file indexes are a simple key-value and a pointer into a DBF file's records. That is why these lookups are so fast. See this xBase file format descriptionhttp://www.clicketyclick.dk/databases/xbase/format/. It is not a RDBMS as some stated in the past. I am of the opinion that full SQL engine's set based extractions are too slow and may not be suited to an incremental list lookup. However, SQLite appears on the surface to be potentially faster than the its full SQL engine peers. I also like the idea of linking a library into the application, that also looks faster a first blush. I am wondering if SQLite has features that could be used to make these lookups just as quick? Can you point me , so to speak, in the correct direction? -- John S. Wolter President Wolter Works Mailto:[EMAIL PROTECTED] Desk 1-734-665-1263 Cell: 1-734-904-8433
[sqlite] Re: sqlite3_update_hook does not get called when other app updates shared db
Chase [EMAIL PROTECTED] wrote: When i call sqlite3_update_hook() from App A and point it to my callback, i am only notified when app A updates the database. When app B (another instance of the same app as A) updates the database, app A's callback is never called, and vice versa. I thought the whole point of this function was to get callbacks on any **outside** tinkering with a shared database file. You thought incorrectly. Callbacks are installed per connection and only react to operations performed on the same connection. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Parser information
On Thu, 2007-08-02 at 13:19 -0700, Rohit Mordani wrote: Hi Ken, Do you have an answer to what external api we can use to get the Select parse tree? There is no such API. You are in hacking territory on this one. Dan. Rohit On 7/31/07, Joe Wilson [EMAIL PROTECTED] wrote: --- Ken [EMAIL PROTECTED] wrote: You should be using the external API calls not the internal sqlite calls and types. Please point us to where you can get the Select parse tree from the external API. See: http://www.sqlite.org/capi3ref.html 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Indexes usage on Foreign Key
I am using sqlite v3.1.3 on Mac OS 10.4 Consider the following schema Create table Library(LibraryID INTEGER PRIMARY KEY AUTOINCRMENT, LibraryName TEXT); Create table Book(LibraryID INTEGER REFERENCES Library, BookID TEXT PRIMARY KEY, BookName TEXT); Now I believe Library(LibraryID) is automatically indexed. But when I use Book(LibrayID) field in one of my Query as below I believe it is not indexed as the query execution takes more amount of time. eg: SELECT * FROM Book WHERE LibraryID IN ( ... ); But when I index Book(LibraryID) it is quick enough. But is it meaningful to index Foreign Key ? Will the Foreign key use the same index as of its counterpart in original table or should it be separately indexed ? I am not able to see the Query plan through Explain Query plan .explain command. Explain Query plan returns a syntax error and .explain does nothing :( Thanks in advance, Bharath Booshan L. --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: how do i declare and use variables in sqlite?
[In the message Re: [sqlite] Re: Re: how do i declare and use variables in sqlite? on Aug 3, 11:47, Paul Harris writes:] create temporary table vars (name text, value something); -- insert into vars set name=x, value=0; -- ... where something = (select value from vars where name=x)... I tried doing this, but there doesn't seem to be a way to do the same thing with an UPDATE command ? No way to do what with UPDATE command? What exactly are you trying to do, and failing? i've just realised the last statement (...where etc etc) is probably supposed to be part of a select statement. Yeap, sorry for being too conceptual, dislexic (insert/update, thanks Igor) and obviously too confusing :-). anyway, this is what i'm trying to do: eg 1 select @somevar := column1 from table1; update table2 set column2 = @somevar; Try: create temporary table var1 select column1 from table1; update table2 set column2 = (select column1 from var1); As written the second comand will likely not do what was intended since var1 might have more than 1 row and there is no constraint so every row in table2 will be affected. I assume that real implementation will deal with this. eg2 update table1,table2 set column2=column1; I believe that SQLite only updates one table at the time, so this will have to be rewriten, but where is the variable here? Perhaps: update table2 set column2=(select column1 from table1); with some where clauses? eg3 (using temp table - how to do this?) create temporary table vars (name text, value something); insert into vars set name=x, value=0; update vars,table2 set column2=value where name = 'x'; Is the code above (eg1) what you are lookin after? Nikola - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UI question
Simply put a ; there..it will finish the command and says invalid sqlite command and comes back to the commad mode again. -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 8:04 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] UI question -Original Message- From: Scott Derrick [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 10:22 AM To: sqlite-users@sqlite.org Subject: [sqlite] UI question This is probably a stupid question but has frustrated me a couple of times. When using the command line interface sqlite3, a couple of times I have forgotten to use the . before a command. After that I get a ... prompt that I can't seem to escape from and accepts no commands? My only choice is to shut down that terminal and start a new one.. There must be an easy way to get back to the command mode? And what is the ... mode? Scott = Hi Scott, The command line utility thinks you are continuing a long SQL command. A semicolon (;) will terminate it, put you back to command mode, and allow you to .quit - To unsubscribe, send email to [EMAIL PROTECTED] - ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify [EMAIL PROTECTED] ** - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite.org needs online forms
i haven't been around long, but here's my 2c anyway, i find the sourceforge forums hopeless, any time I need to look for info on one of their projects, i end up looking through their mailing list - where forum posts are automatically sent to anyway. mailing lists are good for on-going issues. wikis are good when you need to persist that knowledge. bugzilla and bug DBs are ok, but not as useful as the previous 2 points. sqlite has the tools, now it just needs people to put that info into the wiki and improve the documentation to cover questions that are asked on the mailing list. On 03/08/07, john s wolter [EMAIL PROTECTED] wrote: SQLite.org in my opinion, needs to have online community forms. I first used majordomo ten years ago which is like the list manager being used for sqlite-users@sqlite.org but in today's Internet it can be mistaken for SPAM. I do not know if SQLite would qualify because of the Public Domain license but maybe sourceforge or the likes of freshmeat or other FOSS have forms for all their hosted projects. That would allow browsing of prior support issues and make it easier for users to benefit from that collected knowledege. Let us all know how you react to this idea. -- John S. Wolter President Wolter Works Mailto:[EMAIL PROTECTED] Desk 1-734-665-1263 Cell: 1-734-904-8433 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: how do i declare and use variables in sqlite?
On 03/08/07, Nikola Miljkovic [EMAIL PROTECTED] wrote: [In the message Re: [sqlite] Re: Re: how do i declare and use variables in sqlite? on Aug 3, 11:47, Paul Harris writes:] create temporary table vars (name text, value something); -- insert into vars set name=x, value=0; -- ... where something = (select value from vars where name=x)... I tried doing this, but there doesn't seem to be a way to do the same thing with an UPDATE command ? No way to do what with UPDATE command? What exactly are you trying to do, and failing? i've just realised the last statement (...where etc etc) is probably supposed to be part of a select statement. Yeap, sorry for being too conceptual, dislexic (insert/update, thanks Igor) and obviously too confusing :-). anyway, this is what i'm trying to do: eg 1 select @somevar := column1 from table1; update table2 set column2 = @somevar; Try: create temporary table var1 select column1 from table1; update table2 set column2 = (select column1 from var1); As written the second comand will likely not do what was intended since var1 might have more than 1 row and there is no constraint so every row in table2 will be affected. I assume that real implementation will deal with this. ok, so a subselect can be used. not bad, but not as powerful as the mysql @ variables, which can then be used in all sorts of scenarios later, without inducing the same query over and over to get the value. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite.org needs online forms
Wiki's. I have not used them myself. What's the general idea? Is there a form type that would be easy to use? On 8/3/07, Paul Harris [EMAIL PROTECTED] wrote: i haven't been around long, but here's my 2c anyway, i find the sourceforge forums hopeless, any time I need to look for info on one of their projects, i end up looking through their mailing list - where forum posts are automatically sent to anyway. mailing lists are good for on-going issues. wikis are good when you need to persist that knowledge. bugzilla and bug DBs are ok, but not as useful as the previous 2 points. sqlite has the tools, now it just needs people to put that info into the wiki and improve the documentation to cover questions that are asked on the mailing list. On 03/08/07, john s wolter [EMAIL PROTECTED] wrote: SQLite.org in my opinion, needs to have online community forms. I first used majordomo ten years ago which is like the list manager being used for sqlite-users@sqlite.org but in today's Internet it can be mistaken for SPAM. I do not know if SQLite would qualify because of the Public Domain license but maybe sourceforge or the likes of freshmeat or other FOSS have forms for all their hosted projects. That would allow browsing of prior support issues and make it easier for users to benefit from that collected knowledege. Let us all know how you react to this idea. -- John S. Wolter President Wolter Works Mailto:[EMAIL PROTECTED] Desk 1-734-665-1263 Cell: 1-734-904-8433 -- John S. Wolter President Wolter Works Mailto:[EMAIL PROTECTED] Desk 1-734-665-1263 Cell: 1-734-904-8433
[sqlite] SQLite.org needs online forms
SQLite.org in my opinion, needs to have online community forms. I first used majordomo ten years ago which is like the list manager being used for sqlite-users@sqlite.org but in today's Internet it can be mistaken for SPAM. I do not know if SQLite would qualify because of the Public Domain license but maybe sourceforge or the likes of freshmeat or other FOSS have forms for all their hosted projects. That would allow browsing of prior support issues and make it easier for users to benefit from that collected knowledege. Let us all know how you react to this idea. -- John S. Wolter President Wolter Works Mailto:[EMAIL PROTECTED] Desk 1-734-665-1263 Cell: 1-734-904-8433
