Re: [sqlite] AUTO INCREMENT
Hi Kevin, You could also query the table or view but don't return any rows. If you set the option to get the column types in the resultset this should do what you want . AN example of what I am trying to say would help :) PRAGMA show_datatypes = ON; select COl3 from tablename where 0 = 1 Then in the array that is returned you should have the column type as used when the table was created. This may save you the trouble of working through the SQLtext. The where statement should result in no CPU time used for the query regards Greg O - Original Message - From: Kevin Waterson To: [EMAIL PROTECTED] Sent: Monday, October 20, 2003 10:59 AM Subject: Re: [sqlite] AUTO INCREMENT This one time, at band camp, "Ian VanDerPoel" <[EMAIL PROTECTED]> wrote: > You can find the info you want in the sqlite_master > table. There is some doco on it at the sqlite.org the website. I am not sure if the info is held anywhere else but > select * from sqlite_master where name = quotes; will return the sql used to build the table. you can parse the data type from that. yep, guess I will just write up a getFieldType function Thanks for your time. Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Read receipt requested.
Sorry guys I didn't turn off read receipt on my email. Please don't send me one. regards Greg O
[sqlite] Web Database Manager for SQLite
Hi Everyone, Does anyone have a web based database manager for SQLite Databases? I thought I would ask before starting one regards Greg O
RE: [sqlite] Web Database Manager for SQLite
Hi Kevin, Means I will have to wait for my Hosting service to upgrade to PHP 5 (which will happen just after those pig start flying backwards) Anyway thanks for the link. It gives me a good example to work on. Kind regards Greg O -Original Message- From: Kevin Waterson [mailto:[EMAIL PROTECTED] Sent: Tuesday, 21 October 2003 8:32 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Web Database Manager for SQLite This one time, at band camp, Kevin Waterson <[EMAIL PROTECTED]> wrote: > > I thought I would ask before starting one > > http://sourceforge.net/projects/sqlite-admin/ screen shots here... http://www.phpro.org/sqlite/ -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Web Database Manager for SQLite
Hi, Well thanks for all the input . I have started a web based admin in .NET, for two reasons One - I'm more comfortable with .NET than Perl or PHP Two - As a further example for the .NET wrapper I have developed. Good luck to everyone else Kind regards Greg O -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Wednesday, 22 October 2003 4:06 AM To: Greg Obleshchuk Cc: SQLite Subject: Re: [sqlite] Web Database Manager for SQLite At 3:54 PM +1000 10/21/03, Greg Obleshchuk wrote: >Hi Everyone, >Does anyone have a web based database manager for SQLite Databases? > >I thought I would ask before starting one > >regards >Greg O This message is an addendum to my previous response. In light of the fact that at least 2 other web managers for SQLite have been brought up here, I will point out that unlike those, my solution is *not* written in PHP. Rather, my project's first major release is entirely in Perl 5 (a more capable language), and works under but doesn't require a pure mod_perl environment. In a subsequent major release, the shared library portion of the project (which is not web-specific at all) will be available as a C library for the obvious resource-usage and compatability reasons, though the application part will still be in Perl 5, and later in Perl 6. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Little issue with indexes
Hi All, I just noticed a little issue with the indexes nad there name. When you create an index like this create index [Customers-CompanyName] on Customers (CompanyName) The name of the index in sqlite_master is [Customers-CompanyName] yet when you creqate a table like this create table [Test table] (a ,b) The name of the table in sqlite_master is Test Table (no square brackets) Is this by design or just a little oversight? regards Greg O
[sqlite] SQLite in Web application
Hi Everyone , I would like some opinions from anyone with experience in this area. I am thinking of replacing the MS SQL Server backend to my web site with a few SQLite databases. I use the database backend to record purchases and store client information. I also record things like software paths and download times. What my concerns are is SQLite ability to handle web type traffic. If two or more people are using the download form, this form opens a SQLite DB and tries to insert information into the table will all the inserts work? I am planning to use it via ASP.NET and IIS 6.0 So if anyone is using SQLite in a web application that is used by lots of people I would love to here about any problems or just of your success. Kind regards Greg O
[sqlite] New version of SQLite Wrapper for .NET
Hi All, I have updated my SQLite Wrapper for .NET . It is now version 1.1.0 In this update I coded in a loop around the execute statement to handle SQlite_busy situation. After reading the wiki page http://www.sqlite.org/cvstrac/wiki?p=MultiThreading I followed the example. This should make it handle multiple threads updating the same DB. By default it is set to 10 ms delay and 3 retries but you can pass your requirements by the execute method (which there are 4 now) The update can be downloaded from my web site http://www.ag-software.com/SQLite/default.asp Any feed back welcome Kind regards Greg O
[sqlite] .NET Wrapper for SQLlite
Hi, I found an issue (bug) with my .NET wrapper for SQLite. The issue was if you issue this statement PRAGMA empty_result_callbacks = ON;select * from test and the test table has no rows then the wrapper crashes. I have fixed this issue The version is now 1.1.1 regards Greg O
Fw: [sqlite] Checking the busy state
Hi Dennis, I have just done some work on this. Take a look at this wiki page http://www.sqlite.org/cvstrac/wiki?p=MultiThreading The lock is only help while updating. If you follow the instructions in the above page Updates in transactions and you add a loop like the one in the solution then actual SQLITE_BUSY situation should be reduced to a bare minimum. You approach of using a secondary table to write to and see is not the best approach because if the write works there is no guarantee that the write to your primary table will work (someone might get in and still lock it) Yet an update in a transaction if fails will rollback. So the code could look something like begin transaction while not sqlite_busy and retries count not reached update table increase a retries counter if sqlite_busy delay for some time (10ms) loop if not sqlite_busy then commit transaction else rollback transaction regards Greg O - Original Message - From: Dennis Volodomanov To: [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 11:07 AM Subject: [sqlite] Checking the busy state Hello, I would like to check for the state of the database before letting the user change any values, because it's on a LAN and could be in use by another machine, but how can I do that without issuing a sqlite_exec() that would attempt to write something and then checking for the SQLITE_BUSY? Is there any way except that? If I understand correctly, I can set up a one-row table for that purpose and before each modification try to write to it and see what the return is? But will I get a SQLITE_BUSY if another table is being used? Does SQLite lock the entire database when it works with it or just one table? I read the explanation of SQLITE_LOCKED, but it didn't answer my question - as I understood it, it means if I screw up somehow then it's issued? Thank you in advance, Dennis
Re: [sqlite] Checking the busy state
Hi , The example that you have given highlight an the issue well, but that issue is not about locking . That issue is about data retention and how to deal with updated rows. Your user B may have a form with the data displayed on their screen then some time later after use A has delete the row user b might try to modify it. The time between User B retrieving the row then user A deleting the row and finally user B updating the row might be minutes or hours. In any case the issue is the same an update command is issued on a row that doesn't exist. A simple solution and one you should always do in a multi-user system is try and retrieve the PK before issuing the delete. You would also wrap this in a transactions. So in your example The row in question has a PK column called ID and a value of 2 the command from User B would be (lots of b's here) Begin transaction select count(*) from tableName where ID = 2 if count is equal to one then update tablename set 333,. where ID = 2 else return some message back to user say the row has been deleted end the if commit transaction There is also another issue you need to consider in a multi-user system. If User A was updating the row not deleting then User B may over write User A changes. In this situation other DB system have columns designated as Timestamps (I know MS SQL has) . Every time a row is updated the system updates the timestamp column automatically. Then the process is simply to compare the timestamp and if they are the same then no changes have taken place. If the differ then warn the users someone else has changed the row and ask what they want to do. SQLite doesn't have this timestamp concept . (it would be great to have it, hidden like the ROWID column) so you should be comparing every column in the row to the original values the user has and then if they are the same update otherwise ask a question. so an update becomes Begin transaction select count(*) from tableName where ID = 2 and col1 = 'orginalcol1data' and col2='orginalcol2data',... and so on if count is equal to one then update tablename set 333,. where ID = 2 else return some message back to user say the row has been updated by someone else end the if commit transaction The SQL_Busy issue is just one of when the command was issued the database was locked (this needs to be handled as described in my first email) this other issue is more important to you in a multi user system (and it more generic it happens on all DB systems) regards Greg O - Original Message - From: Dennis Volodomanov To: [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:53 PM Subject: Re: [sqlite] Checking the busy state Thank you Greg, But I think that you've implemented a different behavior than what I'm looking for (or please correct me if I'm wrong!). What happens if the user modifies the row that a second user has placed a query for? For example, user A access the db first and deletes one row, at the same time user B tried to modify that row, but had to wait because A is working with the db. After A deleted the row, the db is released and B's query to modify a now-deleted row is sent. I would rather just give a message to the user that the db is locked and ask him to come back later than to wait and process that query. And I wish to update user B's db display after A has finished working with the db (I'm thinking of using mailslots for that). Dennis - Original Message - From: Greg Obleshchuk To: Dennis Volodomanov Sent: Wednesday, October 29, 2003 11:40 AM Subject: Re: [sqlite] Checking the busy state Hi Dennis, I have just done some work on this. Take a look at this wiki page http://www.sqlite.org/cvstrac/wiki?p=MultiThreading The lock is only help while updating. If you follow the instructions in the above page Updates in transactions and you add a loop like the one in the solution then actual SQLITE_BUSY situation should be reduced to a bare minimum. You approach of using a secondary table to write to and see is not the best approach because if the write works there is no guarantee that the write to your primary table will work (someone might get in and still lock it) Yet an update in a transaction if fails will rollback. So the code could look something like begin transaction while not sqlite_busy and retries count not reached update table increase a retries counter if sqlite_busy delay for some time (10ms) loop if not sqlite_busy then commit transaction else rollback transaction regards Greg O - Original Message - From: Dennis Volodomanov To: [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 11:07 AM Subject: [sqlite] Checking the busy state Hello, I would li
[sqlite] Where statements are they case sensitive?
Hi All, I just checked something and noticed that the WHERE statement is case sensitive. I have check this in SQL Server and it is not case sensitive. I am using 2.8.5 and 2.8.6. As an example in the northwind DB I have for SQLite . There is a table called Orders select * from sqlite_master where Name = 'orders' return no rows but select * from sqlite_master where Name = 'Orders' does return rows but create table orders(a) returns an error with the table already exists. Should the where statement be case sensitive , By default I don't think it should. Should I report a bug on this or was it by design?? regards Greg O
Re: [sqlite] Where statements are they case sensitive?
I checked a few systems and there doesn't seem to be a standard. As was said Oracle is case sensitive but all the Microsoft products I check SQL, Access , MS Query where all case insensitive. I think a PRAGMA would be a great idea for this. What do other people think? Using a like (which negates the use of indexes or a lower function which does the same si a solution but not the best ) regards Greg
Re: [sqlite] commercial usage of sqlite
Hello, I am curious about what problems the lawyers have (apart from being a lawyer that is) with SQLite being used in a commercial product. I read the line "No indemnity comes with it" which is a very funny statement. Funny because I don't know of any third party (third party to your project) product (control , DLL, compiler, OS ) that will indemnify you over any damage that they may or may not cause. It is standard practice to include a clause spelling out "it not our fault and you use this product at your own risk" Even Microsoft have a clause saying we will only be held responsible for damages up to a maximum of $1.50 in their products license agreemnt. It strikes me strange that a lawyer would want some type of cover from a product/source code you can change and recompile as you need and source that you can see and interagate as you need. Then again lawyers are a strange set of people. regards Greg O - Original Message - From: D. Richard Hipp To: Tim McDaniel Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 10:10 AM Subject: Re: [sqlite] commercial usage of sqlite Tim McDaniel wrote: > I am trying to use SQLite in a commercial application, but I am getting > resistance from the company lawyer: > > "It is a risk to incorporate SQLite in the [product]. No indemnity > comes with it. We are exposed to third party claims of infringement." > > Has anyone else been in this position? Any advice? > I have provided documentation sufficient to convince corporate lawyers at other large consumer product companies that SQLite was not a risk. I can identify the author of every single line of the core code. (The makefiles and documentation are a different matter, but those files do not carry the same infringement risk since they are not distributed when SQLite is embedded in a product.) Have one of your business managers or lawyers contact me directly and we put to your IP concerns to rest. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Fw: [sqlite] Performance problem
Sorry Richard I meant to send this to the group Hello, Last week I raised an issue about case sensitivity in where clauses. In doing a little research I happened to talk to an Oracle DBA here where I work and asked him the question of how Oracle handled case sensitivity . He explained it is handle in the same way and suggested the same fix drop both to lower case and then compare. The next thing is mentions is really what I want to raise, he said the latest release of Oracle allows you to build indexes which include functions. He also said that these new type of indexes were used when the user issued a select statement with a function in the where clause or a like (i.e select Col1, Col2 from tableName where col1 like 'Gr%' or select * from tablename where lower(col1) ='fred' ). Without knowing all the ins and outs of it, this seems like a great feature to have. Any change of having someone look into the possibility of implementing some thing like that? regards Greg - Original Message - From: D. Richard Hipp To: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:45 PM Subject: Re: [sqlite] Performance problem [EMAIL PROTECTED] wrote: > > DRH: Will the changes to indicies allow us to define arbitrary collation > functions? If so, will those indicies be used when a query is done that > could use the arbitrary collation function? > Likely so. But no promises yet. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQL92
Hello, Does anyone have a web link to a document that details SQL92. regards Greg O
[sqlite] charindex function
Hello, I have finished a charindex function for sqlite (which is attached to this email). When I say finished I have done the Non SQLITE_UTF8 work. charindex give the starting position of a specific _expression_ in a string. charindex (StringtoFind, ReferenceString,startingpos) Can any one supply the SQLITE_UTF8 side to this function and how do I get is placed into the original source? regards Greg O func.zip Description: Zip compressed data - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Left field question about non windows platforms
Hi , This may seem like a question un-related to SQLite but there is a link. On Windows platform to dynamically load a DLL into your program you use LoadLibrary and GetProcAddress to get the reference to an exported function in the DLL. My question is how do non Windows platforms do this? regards Greg O
[sqlite] New function for SQlite
Hi,
I have just finished an SQL function for SQLite which will allow to call
exported function form a Win32 DLL.
This is an example of the calling syntax
update UserPassword set Password =
se_fn("mydll_01","Encrypt",'NewPassword');
or
select se_fn("mydll_01","Encrypt",[Message]) from UserMessages
The function is called se_fn (SQLite Extension)
Parameter One: DLL name
Parameter Two: Function Name
All other parameter are passed to the extension DLL
My thoughts behind this function was to help the many people who are using
wrappers or who use languages that don't support points or c type strings
(like VB ASP PHP,..). These languages can't use the sqlite_create_function.
By using this function they can still extend SQLite for more power.
Some of the uses I can see are compression , encryption, scientific
calculations , basically any thing.
At the moment I have written the WIN32 function and I would not know where
to start for any other platform.
This is the function as it appear in func.c, any comments would be welcome
#if WIN_32
#include "Winbase.h"
#include "windows.h"
/***
This is an extesion Function which allows the loading and execution of
exported
functions from DLL's. The main purpose of this function is to provide
extensions
to SQLite to enviroments that don't support C type pointer or strings.
i.e VB ASP PHP and the like.
The external DLL must be a standard WIN32 DLL which exports two function
This is the first function, which is used to release the memory allocated by
the second function
void FreeMemory(char **argr) {
if (argr[0] == NULL) {
return;
}
free(argr[0]);
}
This is a prototype of the second function (the one you wnat to call) The
first two parameter
are your calling parameter as pssed in the SQL statement. The third
parameter is the return parameter
for results. You must allocate the memory for that and NULL terminate the
value.
void TestFunction_01(int argc, const char **argv, char **argr) {
// Test to see if we have a pointer for argr
// Do some processing
argr[0]=(char *)malloc(100);
sprintf(argr[0],"Done");
return;
}
***/
typedef void (*SE_ExternalFunction)(int , const char **, char **);
typedef void (*SE_FreeMemory)(char **);
static void SE_fnFunc (sqlite_func *context, int argc, const char **argv){
HINSTANCE hinstLib;
BOOL fFreeResult;
SE_ExternalFunction LocalFunction;
SE_FreeMemory FreeMemoryFunction;
char *argr=NULL;
if (argc < 2 ) {
// Return is not enough parameters
return;
}
hinstLib=LoadLibrary(argv[0]);
if (hinstLib != NULL) {
// We have loaded the DLL now lets map the function call
// Load the extenal function
LocalFunction=(SE_ExternalFunction)GetProcAddress(hinstLib, argv[1]);
// Load the Release memory fuction
FreeMemoryFunction=(SE_FreeMemory)GetProcAddress(hinstLib, "FreeMemory");
if (LocalFunction != NULL && FreeMemoryFunction != NULL) {
// We have mapped the Function
// Allocate argr;
LocalFunction(argc, argv, &argr);
if (argr != NULL) {
sqlite_set_result_string(context, argr, -1);
}
else
{
sqlite_set_result_string(context, NULL, -1);
}
// Free the memory allocated to argr on the other heap.
//Must do this by using the FreeMemeory function in the DLL
FreeMemoryFunction(&argr);
}
else
{
// Error Loading Function
sqlite_set_result_string(context, "Error Mapping Functions", -1);
}
// Free library
fFreeResult = FreeLibrary(hinstLib);
}
else
{
// Error loading Library
sqlite_set_result_string(context, "Error Loading Library", -1);
return;
}
}
#endif
Kind regards
Greg
Re: [sqlite] Performance tuning question
Hi Arthur, It's very quite here , for some reason (has been all week). Can you tell me a bit more about you inserts are they in separate batches? or one big batch. I have noticed performance issues with in-memory DB when doing lots on little batches , but I would have thought it would be constant. How much memory does you computer have? This may effect performance with swapping. With MDAC which database type are you going to? regards Greg O - Original Message - From: Arthur Hsu To: [EMAIL PROTECTED] Sent: Friday, November 14, 2003 11:58 AM Subject: [sqlite] Performance tuning question Hello, I'm new to sqlite and I'm using it in my MFC program. I compiled the sqlite lib myself. I'm using in-memory DB. My program has two threads, say thread A and B. Thread A is the producer, which inserts rows inside a table named 'master'. Thread B is a consumer, which selects and updates rows from the table 'master'. The SCUD operations are embedded into a singleton, which is responsible for critical section locks. The master table has a primary key composed of two columns, one is integer type and the other is string type. What confuses me is a strange phoenomenon. The first 30 seconds thread A can insert 6000 rows, and the next 30 seconds another 2000, and next 30 seconds 1900, 1800, ... steadily decaying. Meanwhile thread B keeps the same pace to make 2 updates per 30 seconds. I've tried to tune the MAX_PAGES to 1048576 and MAX_BYTES_PER_ROW to 17646. It helps boosting up some performance but the phoenomenon of decaying is still there. I have a rival program which has same functionality but it's written in MDAC (I can't use MDAC for some reasons). The first 30 seconds 8500 rows are inserted, and the next is 17000 rows, 26500, ..., almost linear. Where should I look into to tune my SQLite compilation in order that I can match the MDAC version performance? Any cent is much appreciated. TIA, Arthur - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Is there a problem with this list?
Hello, Just wondering if there is a problem with this list. regards Greg
[sqlite] Improving concurrency in SQLite
Hello, I read your paper and it was interesting. I am an advocate for improving the speed of SQLite where ever possible and after reading your paper and applying the experiences of using MS SQL server these are my thoughts. 1. All transactions should be Read Only (no locking) with an option to turn them into write transaction 2. Writes are not applied until the COMMIT and then only after a lock is obtained 3. Refine the locking down to the table level 1. If you change all transactions to be READ Only by default this would cut down the number of locks. The process that parses the query text could then decide if a transaction is write or read-only. So this BEGIN; SELECT * FROM t1; SELECT * FROM t2; COMMIT; Would always be a read-only transaction but this BEGIN; SELECT * FROM t1; -- Some processing occurs here UPDATE t1 SET ... WHERE ...; COMMIT; Would be a write transaction and require a lock. It should add much time to the parser process to determine this up front. There could be an option to override the parser and turn the transaction into a write lock like this BEGIN WRITE; SELECT * FROM t1; SELECT * FROM t2; COMMIT; Would be a write transaction and need to obtain a write lock I would see this as something that would cut down the number of write lock required 2. In MS SQL server we have two types of READs . A normal read and a dirty READ. A clean read is data that is not in the process of changing (i.e in someone else's transaction and therefore locked) A dirty READ may contain data in a transaction and may not be valid. If the transaction rolls back then the read may contain data that is not valid or doesn't exist. Please see the attached Concurrency_Problems.PDF document. If you only apply the changes to the base tables at the commit stage then a write lock is only needed at that time. What the process could be is some thing like this Write go to a temp table in the SQLite DB file. So the write happen as they occur but just not in the base table. Then a write lock is obtained. Some sort of priority locking may be required to force a write lock could be used Then the once the lock is obtained the base table is updated. The temp table is removed and the lock releases The benefits of this is that READs can still occur while writes are taking place (in the temp table) The updating of the base table should be very quick because it would use the PK (ROWID) and not contain complex where statements. 3. Refining the locking from file to table could see more locking take place while reads are going on. I would think that option 1 and 2 are pretty simple option to be put in place (simple in comparing them against table locking) . One premise that I have always held on to with databases are that they are read far more than written too. Allowing read to occur more often and without delay give the impression of a faster database. Kind regards Greg O -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, 23 November 2003 1:09 AM To: [EMAIL PROTECTED] Subject: [sqlite] Improving concurrency in SQLite Feedback is requested on the paper found at: http://www.sqlite.org/concurrency.html Your analysis and ideas will help to make SQLite a better database engine. Thanks. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Concurrency_Problem.zip Description: Zip compressed data - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite Administration web application
Hello, for anyone who is interested I have placed an application on sourceforge to manage SQLite database from you web site. This is an ASP.NET application . At the moment you can manage databases and table objects. There is a query form and a table wizard form which allows you to data enter information into a table. Any feedback welcome. regards Greg O
re: [sqlite] .NET sqlite wrapper
Hi Richard, Have you placed it on the wiki yet. I added a .NET section with three other native .NET wrappers for SQlite? You should place an entry there if you haven't yet. http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers regards Greg O - Original Message - From: Richard Heyes To: [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 10:20 AM Subject: [sqlite] .NET sqlite wrapper Would anyone be interested in a mostly C# .net wrapper for sqlite? Mostly because of the null reference bug I had which I got around by using a C++ proxy object for open, close and execute functions. It's not your normal ADO.NET model however, just a query class and result class. Cheers. -- Richard Heyes - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
re: [sqlite] Network Performance
Hi Brian, I few things. The problem is your network speed compared to local disk speed (also distance is an issue as well) Disk transfer is in Mega bytes where as network IO is in mega bits. SO while IDE standard 33.3MB looks slower than 100mb for your network (even switched) it is in fact slower. Also most IDE are inboard to the motherboard which bypasses the IO bus. Not the same for network cards while lots are inboard they tend to hang off the IO bus and therefore behave the same as plug-in cards(obviously depending on you motherboard). So no matter what you do local will always be fast, by a magnitude at least. Some things you can check with your network is to make sure you are using a 100mb card (if it's a plug-in) Make sure that you have the card set to 100mb Full duplex as apposed to Auto. There is a Cisco bug in lots of there switches and hubs. This bug relates to auto detect of speed, basically if you have a network card set to Auto every packet is check to ensure it is at the right speed. This slows down the transmission rate a great deal. Make sure you IT department has the switch set-up to 100mb Full duplex as well You could try paging using the offset and limit clauses but depending on your queries these may not give you the results you require. If you query is structured to return the rows in a certain order which the index will not handle this may require a full table scan . Which will mean the same time delay for less rows. The issue is very piece of data that needs to be check has to be transferred from the network location to your P.C. Then that information is then stored and the next retrieved until the whole table is processed. Indexes make it quicker by being sorted and having less information. Another suggestion would be to look at the query and find out if you really need to return so many rows of data (58,840 is a lot) Also have a look at your data if you DB is 37MB and you have 58,840 rows each row is on average 618K . This is a lot of data in a single row. Some times we get carried away with DB design and just because we can store data we do. A good example of this is images and documents. We can store these in a DB but it much better to store a URL for share path to the file and then store the file on the OS drive. Drives ad directories are much better at servicing these items than databases. Or if this isn't the case you could try splitting the table into a one to one relationship table. Have the items you do lookups on in one table and the retrieval items in another. Then you can do a lookup and just join the results to return all the data. This should improve the processing a bit but not the transfer time. It should also reduce the index sizes depending on what you index. Lastly you could try a local cache of the data. use a splash screen with some sort of information like "Init system please wait". Copy the DB locally and then use the local system to query against. When updating you will need to do two updates , but this then fails when other people update the data and you will not see the changes. regards Greg O - Original Message - From: Brian Pugh To: SQLite Email Sent: Wednesday, November 26, 2003 10:12 AM Subject: [sqlite] Network Performance Hi, Now that I've completed my SQLite application (written in Pyxia's IBasic), using the SQLite DLL, I have some questions My database is about 37mb, and has 114,380 records (rows) If I open and query the database locally (ie, the .db file is in the same folder as the application), things move pretty quick. I can do a query for a certain range of records (59,840) in about 4 seconds Doing the same query with the same .db on a network server is taking 18 seconds. Is this acceptable? I find that pretty slow! Most of my queries are fashioned so that they use indexes - I try not to use the "like" operator We have a certified 100mb network, with Cisco gigabit switches, and I am running the application from a 2.4gHz P4 PC How do other users deal with querying large amounts of information over a network? Are you paging in records, say 500 at a time, and then paging in the next 500 records? Could I set my database up differently? At the moment, when the .db loads up, I am setting: pragma cache_size=8192; pragma synchronous=off; pragma count_changes=off; Are there any other tweaks I could set in this area? If anyone has any ideas or suggestions as to how to improve the query speed over a network, I would love to hear them Thanks a lot, Brian Pugh Halifax, England
RE: [sqlite] Field name
Hi, Des that mater? If it does then just un=quote them by removing the first char and lasts Kind regards Greg O -Original Message- From: Bronislav Klucka [mailto:[EMAIL PROTECTED] Sent: Thursday, 27 November 2003 8:42 PM To: SqLite Konference; Greg Obleshchuk Subject: RE: [sqlite] Field name but the results column name seems to be [brona'-kluc"ka] instead of brona'-kluc"ka > -Original Message- > From: Greg Obleshchuk [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 27, 2003 10:32 AM > To: 'Bronislav Kluèka' > Subject: RE: [sqlite] Field name > > > Hi , > I prefer to using [ ] instead of ' or " it makes life so much easier > > So > select [brona'-kluc"ka] from [brona'-klucka]; > Works perfectly and doesn't make your eyes water > > Kind regards > Greg O > > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Is case sensitivity a problem for me only?
Hi Steve, I agree with you completely. As I understand it though from a speed point of view case sensitive compares are much quicker, this may be one reason they are used? Maybe there could be a PRAGMA setting PARGMA case_sensitive=ON; PARGMA case_sensitive=OFF; That would be a nice option regards Greg O - Original Message - From: Steve O'Hara To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:40 AM Subject: RE: [sqlite] Is case sensitivity a problem for me only? Why should case sensitive data comparisons be maintained ? It seems to me that it's only in the most obtuse examples where it is of any use e.g. algebra, formulae, equations etc. Even then, how many people will search for a formula ? However, I would agree, that there is an absolute need to maintain the case of the actual stored data. But we shouldn't confuse the storage of data with the querying of data. By forcing people to use UPPER/LOWER functions to remove the case sensitivity, you are condemning them to considerably poorer performance. As I understand it, UPPER/LOWER and LIKE operators will cause SQLite NOT to use indexes if there are any defined for the column. As you say, Unix has a case sensitive file system (unlike Windows, DOS or VMS for that matter) which is perhaps why the commercial RDBMS that were developed on Unix (Informix, Oracle?, Ingress) are all case sensitive, and the one that wasn't, SQLServer isn't. Can't we solve this in the index ? Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 01 December 2003 22:05 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] Is case sensitivity a problem for me only? For ease of use and less prone to programming errors, Table/Field names should be case INsensitive. Of course data comparisons should be case sensitive, and use UPPER/LOWER methods where case is not a factor. This is one thing Unix has taught the world with case sensitivity in its file system - its a big mistake. In the Windows world we *never* have bugs due to case mistakes in programs where files are involved. IMHO it we will be *much* better off if Sqlite works like this. > >> Pavel said: >> >Hi, all. >> >Traditionally, SQL databases are case insensitive, or at least have an >> >option to behave this way. Sqlite is case sensitive and this fact is >> >introduced in a lot of places in sources. Having case-insensitive >> >sorting and matching seem to be extremely useful thing to me (in >> >business domain where one have to deal with names, addresses etc). Any >> >opinions? >> >Yours, Pavel >> >> Personally, I prefer that databases *are* case-sensitive by default, as > this matches how most programming languages work. >> >> If I do a normal comparison between two strings in a programming >> language, > they match only if the case is the same. It gives me plenty of headaches > if > the database behaves differently, and I can't be certain if matches > returned > by a database actually are matches. >> >> Also, case-sensitive comparisons (basis for both matching and sorting) >> are > considerably faster and use less memory than non-sensitive ones, since > with > the latter one has to convert the string on each side to either an upper > or > lower case representation, so that the normal numerical comparison of the > strings match, and with the former, no conversion is needed. >> >> Also, in these days of increasing internationalization, it is simpler to > do case-sensitive by default since that's what all the world's other > character sets and our own non-letter symbols do, to my knowledge. >> >> Now I do recognize the value of case-insensitive comparisions, but I >> think > that this should be done as a type of extension functionality rather than > in > core functionality. >> >> What I mean is, have a different syntax to specify case-insensitive > matching, much in the way that "like '%abc%'" is used when we want to > match > just part of a string. >> >> Also or alternately, treat case-insensitive operations as a >> locale-related > thing, much as parts of a database schema can be configured to do things > in > a certain way, such as sorting, based on the user's locale. >> >> So case-sensitive default actions all around are the simplest, fastest, > take the least code, are the most reliable, and technically most standard > way of doing things. So I see SQLite doing sensitive by default as a good > thing. >> >> -- Darren Duncan >> >> - >> To unsubscribe, e-mail: > [EMAIL PROTECTED] >> For additional commands, e-mail: > [EMAIL PROTECTED] >> >> > > > > I'm in the opposite camp I'm afraid - I find case sensitivity irritating > for > b
RE: [sqlite] need faster count(*)
Hi, No the process must count all the rows. The ROWID value is generated when a new row is inserted but if you delete rows the last rowid will not be the same as the count of rows. If you have an index on the primary key and count that then it will run as quickly as possible. Regards Greg -Original Message- From: Buzz Weetman [mailto:[EMAIL PROTECTED] Sent: Wednesday, 7 January 2004 7:43 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] need faster count(*) In my particular situation, count(1) or count(my_column_name) does not seem to make a dent. I understand that count(x) is useful for getting the row count of a subset of results. But since I just want the number of rows in a table, shouldn't the query just be able to get this from a value associated with this table? Is this value maintained in the sqlite code? Buzz Hennie Peters <[EMAIL PROTECTED]> wrote: At 07:41 6-1-2004 -0800, Buzz Weetman wrote: >I'm using sqlite in an embedded system. Disk I/O is relatively slow. I >am doing a: >SELECT count(*) FROM my_table; > >This is taking too much time. I've also tried count(1), as I saw >suggested elsewhere in the mail list... though I'm not entirely sure what >this means. 1st column? I've tried a column that I have an index for... >not faster. > >Doesn't sqlite "know" the number of rows in each table without >explicitly >counting them? > >Thanks for any help >Buzz The count function takes a column or a constant as an argument. I posted the count(1) some days ago. I used this in Oracle sql years ago. The idea was to make the query faster as it did not need to scan the columns in the table. groet, Hennie == Linux is like a wigwam - no gates, no windows and an apache inside. == - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] TCL.h File missing from sqlite_source.zip
Hi, I have just downloaded the sqlite_source.zip file for MS-Windows and there seems to be a file missing TCL.H . Anyone have any ideas where it went? regards Greg O
[sqlite] .NET Assembly and VB Wrapper
Hello, I have updated my .NET Assembly and VB Wrapper for SQLite to support version 2.8.9. Both can be downloaded from http://www.ag-software.com/SQLite.aspx .NET Assembly Changes None VB Wrapper changes Introduced a new function called ags_number_of_rows_from_last_call, which as the name suggests returns the number of rows from the last query run. This is help in the situation where the SQL command executed correctly but didn't return any rows. You should check this value before accessing the variant array. Samples Changes Both updated regards Greg O
RE: [sqlite] .NET Assembly and VB Wrapper
Hi Markus, Can I see your code where the wrapper crashes . I have done some test with read-only directories and I don't get any crashes. Regards Greg O -Original Message- From: Markus Huhn [mailto:[EMAIL PROTECTED] Sent: Wednesday, 7 January 2004 5:17 PM To: Greg Obleshchuk Cc: SQLite Users Subject: Re: [sqlite] .NET Assembly and VB Wrapper Hello Greg, >VB Wrapper changes >Introduced a new function called ags_number_of_rows_from_last_call, >which as the name suggests returns the number of rows from the last query run. This is help in the situation where the SQL command executed correctly but didn't return any rows. You should check this value before accessing the variant array. > > Thank you, this is a great Feature, because VB is not very funny, if i will check the empty return value :-( If have an other feature request. If i try to connect to a database on an network drive which has only Read-Rights, the Wrapper crashes. The command-line utility returns "Unable to open database". It is very useful, if the Wrapper can return also an Error Value. Best Regards Markus - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] AGS_SQLite_Wrapper VB
Hi, The returned result is a variant array. You move through a array as you normally do, by increasing a index counter. You can write a class around any array to emulate an ADO recordset if you want. Regards Greg -Original Message- From: ISA Programmi [mailto:[EMAIL PROTECTED] Sent: Sunday, 11 January 2004 3:43 AM To: [EMAIL PROTECTED] Subject: [sqlite] AGS_SQLite_Wrapper VB Hi all, does someone could tell me how to implement the record (rows) navigation like "MoveNext", "MovePrevious", "EOF" etc... using the AGS_SQLite wrapper and VB6? Thank you a lot in advance, Giuliano Isacchi - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] web-based admin utilities
Hi, I've done one in VB.NET and it's here http://sourceforge.net/projects/dotnetsqliteadm/ Regards Greg -Original Message- From: Wade Preston Shearer [mailto:[EMAIL PROTECTED] Sent: Thursday, 15 January 2004 6:01 AM To: [EMAIL PROTECTED] Subject: [sqlite] web-based admin utilities Is anyone aware of a web-based admin utility for SQLite similar to phpMyAdmin? I found one... .http://sqlitemanager.sourceforge.net/ ...but it is not in English and doesn't look very impressive. wade - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] New wiki page for Performance Tuning
Hi All, I just created a new wiki page off the wiki home called PerformanceTuning. I have started to place clipets from emails in the group which relate to making queries or DB operations run quicker. Please add your bits and pieces http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning regards Greg O - Original Message - From: Mauricio Piacentini To: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 1:30 PM Subject: Re: [sqlite] SQLite Browser (Mac OS 10.3) [EMAIL PROTECTED] wrote: >>> ...on Mac OS 10.3? >> >> >> What error you get? > None. That's what's strange about it. It won't even launch. It starts in > the dock but dies one second (literally) later. I can run it on MacOSX 10.3.2. However the version of Qt used to compile the binaries does not support Panther officially, so the widgets do not look correct, specially buttons. There might be other subtle issues with Panther. I will try to find time to compile a newer version against the latest Qt, but please post a request directly to the sourceforge foruns if you have not done so. If you are not using the binaries and have compiled from source you probably do not have Qt setup correctly for static compilation, or your environment is not setup correctly to use Qt shared libraries from the Finder. Since this list is dedicated to SQLite I would recommend checking the Qt forums, or posting to the sqlitebrowser message boards. Regards, Mauricio Piacentini - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How to link under Windows
Erik, You will need to use one of the .Net Wrappers or the .NET data providers. http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers regards Greg O Erik Jälevik wrote: I'm just about to write some code using SQLite from Visual Studio .NET under Win XP. I am however, not sure how I go about linking the library. The precompiled zip for Windows contains a .dll and a .def file. Most other libraries have a .lib file for including in the linker options. I will try and link only the .def but it doesn't seem to contain enough info for the linker to know how to treat the .dll. Furthermore, I need an sqlite.h to include in my source file. After downloading the sources, all I could find was a sqlite.h.in. As I'm not familiar with Unix building procedures, I'm not sure if this file is valid or whether it undergoes some changes when make is run. So basically, I'd be very grateful if anyone using SQLite on Windows could point out how to link it and where to get hold of the correct sqlite.h. Many thanks, Erik - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Best method to create a database with a tree structure?
Hi Martin, You are talking about standard Parent/Child relationships here but because you have unlimited number of entries you fold it into one table and have a Parent_ID column. So if you have your required details as being these columns Name SomethingElse Then a standard table might be Name_ID Name Somethingelse adding the parent id column would be Name_ID Name Somethingelse Parent_ID or Create Table TableName ( Name_ID INTERPER PRIMARYKEY , Name , Somethingelse , Parent_ID ) Now all you need to do is fill in the details , PARENT_ID = 0 means it has no parents (or leave it NULL if you want) When you retrieve the rows you always do select * from Tablename where Parent_ID = 2 (or what ever) regards Greg O - Original Message - From: John Scott To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 9:52 AM Subject: [sqlite] Best method to create a database with a tree structure? Hello folks, I am thinking of to create a database with a tree structure. BUt it seems to be a hard work. I would like to have a tree structure embbeded into the database like: Root folder which contains two other folders "books" and "music". If a click on books, only the entries in the database which belong to the folder bools under root should be displayed. Does someone have a good idea?? Thanks, Martin - 每天都 Yahoo!奇摩 海的顏色、風的氣息、愛你的溫度，盡在信紙底圖 信紙底圖
Re: [sqlite] Query optimization help
Hi Richard, try this SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM topics AS t , posts AS p WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,2761, 2745) and t.id=p.topic_id AND p.poster_id=2 There a post from Dr R about how SQLite works out joins. The above should be the final result it I read it right. You might want to also try using group by instead of DISTINCT In other DB it is faster. http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning regards Greg - Original Message - From: Rickard Andersson To: [EMAIL PROTECTED] Sent: Monday, February 02, 2004 2:00 PM Subject: [sqlite] Query optimization help I'm having some performance problems with queries looking like the following: SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM topics AS t LEFT JOIN posts AS p ON t.id=p.topic_id AND p.poster_id=2 WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396, 2761, 2745) The above query takes a full second to complete on my P3-450. The database contains approx. 1200 topics and 8000 posts. All relevant columns have indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to speed it up? -- Rickard Andersson arpen_at_home_dot_se - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] OK to drop support for legacy file formats?
Hello, Why not remove the feature but create a seperate utility project that converts any version of SQLITE DB to the latest version. In this way SQLite can be just what it is small and fast. There would be a tool from the orginal source which you would know would work and simple to use. The current version of SQLite would need to error when openning an older formatted DB file. regards Greg - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 07, 2004 1:05 AM Subject: [sqlite] OK to drop support for legacy file formats? > If you use a modern version of SQLite (version 2.6.0 through 2.8.11) > to open an older database file (version 2.1.0 through 2.5.6) the > library will automatically rebuild all the indices in the database > in order to correct a design flaw in the older database files. > > I am proposing to drop support for this auto-update feature. > Beginning with 2.8.12, if you attempt to open a database file > built using version 2.5.6 or earlier, the open attempt will > fail (with an appropriate error message). You will have to > update the database file manually. > > Would this proposed change cause anyone unreasonable hardship? > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Primary key and index
Bertrand, This is from the doc on the web Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY. So adding the Primary Key does create an index. you can verfiy this by doing create table a (b primary key,c); select * from sqlite_master; regards Greg - Original Message - From: Bertrand Mansion To: Kurt Welgehausen ; [EMAIL PROTECTED] Sent: Monday, February 09, 2004 8:52 AM Subject: Re: [sqlite] Primary key and index <[EMAIL PROTECTED]> wrote : > Try > >select * from sqlite_master >where tbl_name='yourTableName' and type='index' > > This is all explained in the Create Table section of > lang.html and in the FAQ. Hi, Did you read my question ? I am afraid your answer is totally out of scope. Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] Re: [sqlite] Primary key and index
Hi, Doing some testing , creating a primary key with the INTEGER defined will not create an index but creating a PRIMARY KEY by it self does create an index I.e Create table z(a PRIMARY KEY, B); creates an index but Create table z(a INTEGER PRIMARY KEY, B); does not So Yes I would be creating an index on the a column if it is an autonumber(identity ) column if I was using this column in joins or where clauses. regards Greg - Original Message - From: Michael Hunley To: Greg Obleshchuk Sent: Tuesday, February 10, 2004 6:49 AM Subject: Re: [inbox] Re: [sqlite] Primary key and index At 09:20 AM 2/9/2004 +1100, you wrote: >This is from the doc on the web > >Specifying a PRIMARY KEY normally just creates a UNIQUE index on the >primary key. However, if primary key is on a single column that has >datatype INTEGER, then that column is used internally as the actual key of >the B-Tree for the table. This means that the column may only hold unique >integer values. (Except for this one case, SQLite ignores the datatype >specification of columns and allows any kind of data to be put in a column >regardless of its declared datatype.) If a table does not have an INTEGER >PRIMARY KEY column, then the B-Tree key will be a automatically generated >integer. The B-Tree key for a row can always be accessed using one of the >special names "ROWID", "OID", or "_ROWID_". This is true regardless of >whether or not there is an INTEGER PRIMARY KEY. > >So adding the Primary Key does create an index. > >you can verfiy this by doing > >create table a (b primary key,c); >select * from sqlite_master; So, then, is he seeing a false performance increase if he creates an index on the primary integer key? If not, and he is seeing a real performance boost, why isn't the index auto created? I.e. should we all be creating an extra index on our integer primary key tables for performance? thanks. michael
Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
Hi Ken, >Every sqlite table has an (hidden) index for its OID. When you create a >column as INTEGER PRIMARY KEY, the index for that column will simply be the >OID index. So creating another one is unnecessary. Not that I disagree with you but where are you getting this information from? I just want to do some reading on it. regards Greg - Original Message - From: Williams, Ken To: 'Greg Obleshchuk' ; Michael Hunley ; [EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 7:47 AM Subject: RE: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index > -Original Message- > From: Greg Obleshchuk [mailto:[EMAIL PROTECTED] > Sent: Monday, February 09, 2004 2:38 PM > To: Michael Hunley; [EMAIL PROTECTED] > Subject: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index > > > Hi, > Doing some testing , creating a primary key with the INTEGER > defined will not create an index but creating a PRIMARY KEY > by it self does create an index I'm not sure why you needed to run a test for that, it's just what the docs say. > So Yes I would be creating an index on the a column if it is > an autonumber(identity ) column if I was using this column in > joins or where clauses. No. Every sqlite table has an (hidden) index for its OID. When you create a column as INTEGER PRIMARY KEY, the index for that column will simply be the OID index. So creating another one is unnecessary. -Ken
Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
O,
So you are pointing to this section
then that column is used internally as the actual key of the B-Tree for the table
and this
The B-Tree key for a row can always be accessed using one of the special names
"ROWID", "OID", or "_ROWID_".
But does this mean that SQLite will use that Key for where statements when not
referenced by column ROWID, OID or _ROWID_
What I mean is if you have a table called Product_Description with a column named
Product_ID which is INTEGER PRIMARY KEY and another table called ORDERS which has a
column Product_ID (with an separate index) That SQLite will use the hidden index when
this query is run
Select * , Product_Description.ProductName from ORDERS , Product_Description where
ORDERS.Product_ID = Product_Description.Product_ID
or would you have to do this
Select * , Product_Description.ProductName from ORDERS , Product_Description where
ORDERS.Product_ID = Product_Description.ROWID
regards
Greg
- Original Message -
From: Williams, Ken
To: 'Greg Obleshchuk' ; [EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 9:06 AM
Subject: RE: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
-----Original Message-
From: Greg Obleshchuk [mailto:[EMAIL PROTECTED]
Sent: Monday, February 09, 2004 4:00 PM
To: Williams, Ken; [EMAIL PROTECTED]
Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
Hi Ken,
>Every sqlite table has an (hidden) index for its OID. When you create a
>column as INTEGER PRIMARY KEY, the index for that column will simply be the
>OID index. So creating another one is unnecessary.
Not that I disagree with you but where are you getting this information from? I
just want to do some reading on it.
It's this paragraph from the documentation that's been cited already in this thread:
--
Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key.
However, if primary key is on a single column that has datatype INTEGER, then that
column is used internally as the actual key of the B-Tree for the table. This means
that the column may only hold unique integer values. (Except for this one case, SQLite
ignores the datatype specification of columns and allows any kind of data to be put in
a column regardless of its declared datatype.) If a table does not have an INTEGER
PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The
B-Tree key for a row can always be accessed using one of the special names "ROWID",
"OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.
--
-Ken
Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
Hi Richard, So in these cases there is no benefit from creating an index on a column that is INTEGER PRIMARY KEY? If so is there a way of exposing the fact that INTEGER PRIMARY KEY are used as the key tot he B-Tree table? By looking in SQLite_Master it isn't obvious at all. regards Greg - Original Message - From: D. Richard Hipp To: Greg Obleshchuk Sent: Tuesday, February 10, 2004 9:29 AM Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index Greg Obleshchuk wrote: > > Select * , Product_Description.ProductName from ORDERS , Product_Description > where ORDERS.Product_ID = Product_Description.Product_ID > > or would you have to do this > > Select * , Product_Description.ProductName from ORDERS , Product_Description > where ORDERS.Product_ID = Product_Description.ROWID > If Product_Description.Product_ID is an INTEGER PRIMARY KEY, then Product_Description.Product_ID is just an alias for Product_Description.ROWID. The two queries are identical. Verify this by using EXPLAIN and seeing that both generate identical byte code. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
Thanks for clearing that up Greg - Original Message - From: D. Richard Hipp To: [EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 11:52 AM Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index Greg Obleshchuk wrote: > > So in these cases there is no benefit from creating an index on a column > that is INTEGER PRIMARY KEY? > Putting an index on an INTEGER PRIMARY KEY will make INSERT, DELETE, and UPDATE slower since the index must be maintained. But no SELECT will ever use the index. So adding an index to an INTEGER PRIMARY KEY is less than no benefit - it hurts. See ticket #292. If you say "UNIQUE PRIMARY KEY" (as some users want to do) SQLite will create two identical indices Only one index will ever be used - the other justs wastes CPU time and disk space. I'll get around to fixing that someday. Probably at the same time I should rig it so that attempts to create named indices on PRIMARY KEY are ignored too. Once that happens, you can create indices on your INTEGER PRIMARY KEY all you want - SQLite will ignore your attempts - and everything will work at maximum efficiency regardless of what you try to do. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Database Full!
Hello, Could you open a memory database and then attach the disk based database. Create the tables in the memory database and copy the rows from the disk based into memory. Once done detach the database , delete it from disk . Then create a disk based database , close it and attach it to the memory database and then do the reverse but only with the rows you want. might be worth a try regards Greg O - Original Message - From: "Jakub Adamek" <[EMAIL PROTECTED]> To: "D. Richard Hipp" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, February 17, 2004 7:04 PM Subject: Re: [sqlite] Database Full! > Hello, > it seems to me the solution would be: Create a special small file just > to protect some disc space. If you lack on disc space, delete the > protecting file (and hope nobody will steal the disc space inbetween) > and delete some records. Than again create the protecting file. > Obviously, in a live system this may fail ... > > Jakub > > D. Richard Hipp wrote: > > > Benoit Gantaume wrote: > > > >> Hi, > >> I am trying to handle a problem that occurs when the disk is full: > >> when i try to insert, that return SQLITE_FULL. > >> > >> Ok. > >> > >> There is not problem to get some elements from the database. > >> > >> Then I try to remove some elements... > >> But that returns SQLITE_BUSY! > >> > >> I have tryed to stop all operation with: sqlite_interrupt(this->cdb); > >> But it seems to have no effect! > >> > >> How can I free the database so that I can remove some elements from it? > >> > > > > > > DELETE requires some temporary disk space for the rollback journal. > > So if your disk is full, you cannot delete. > > > > Furthermore, just doing some DELETEs does not reduce the size of the > > database file. DELETE just adds some 1024-byte blocks of the file to > > an internal freelist where they can be reused later for other purposes. > > To actually reduce the size of the database file, you need to run > > VACUUM after you DELETE. VACUUM requires temporary disk space that > > is a little over 2x larger than the size of the original database. > > So (ironically) if you are low on disk space, VACUUM probably will > > not run. > > > > So, as you can see, it is difficult to get SQLite to run when you are > > low on disk space. Your best solution is to get a bigger disk. > > > > A bigger disk drive is the right answer for your desktop, but for > > an embedded solution (with perhaps a few MB of flash disk) that is > > not practical. That problem has been brought to my attention and > > work is underway to make SQLite behave better in a low diskspace > > environment. Unfortunately, the changes to accomplish this will not > > be available in the public version of SQLite for a least 3 more months > > and probably longer than that. Sorry. > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Changing a Field
Brian, Can you be a bit more specific? You have the case expression which allows you to return values depending on other values http://www.sqlite.org/lang.html#expr something like case Col1 when 1 then 'happy' when 2 then 'sad' so this could be wrapped up like update table1 set col2 = case Col1 when 1 then 'happy' when 2 then 'sad' else 'who knows' end where Col3 = 'What she thinking' hope this help (oh you might want to check the syntax of the case expression ) Greg O - Original Message - From: Brian Pugh To: SQLite Email Sent: Friday, February 20, 2004 7:42 AM Subject: [sqlite] Changing a Field Hi, I need to do a multiple search and replace on the same field in my database. Can anyone suggest a suitable bit of code for this? I have over 100,000 records, so any automation is very welcome! Thanks for any suggestions, Brian Pugh
Re: [sqlite] Select statements returned column names
Hello, This is an important question which needs to be considered well. I personally don't care either way but if a decision is to be made it should be for standardisation Can I ask this question, is it a problem only for the people using a wrapper or is it a problem for people using the API interfaces (including the library in there programs) I would think that the people using SQLITE in the program would be referencing the array the column index or am I off the mark here? regards Greg O - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 12:12 AM Subject: Re: [sqlite] Select statements returned column names > Gerard Samuel wrote: > > > > If I execute an sql select like -> > > SELECT f.id, f.foo FROM table f; > > The returned data is -> > > f.id f.foo > > 1 hello > > 2 world > > > > Instead of the normal (as in other DBs I've used) > > id foo > > 1 hello > > 2 world > > > > Is this the correct/expected behaviour of sqlite? > > You can always specify your own column names using > an AS clause, of source: > > SELECT f.id AS one, f.foo AS two FROM table f; > one two > 1 hello > 2 world > > SQLite does attach "different" names to the columns > than other database engines. This has been a > persistent source of complaint. The problem comes > up on joins more than anyplace else. > > Question to all: If I modified SQLite to use the > same column naming rules as (say) PostgreSQL, how > much existing code would it break? Is this something > that should be done, even though it is a (slightly) > incompatible change? > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A puzzling SQLite slowdown
Hello, Richard, I thought that after a transaction SQLite opened and closed the DB anyway and that was one of the main reasons for using a transaction where many separate updates are taking place it cuts down the opening and closing so only one occurs? This problem might be an index problem? Kinda sounds like it. Can you isolate it down to insert or updates that are taking longer? Or are both taking twice as long? regards Greg O - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: "Dennis Cote" <[EMAIL PROTECTED]> Cc: "sql-users" <[EMAIL PROTECTED]> Sent: Wednesday, March 03, 2004 3:10 AM Subject: Re: [sqlite] A puzzling SQLite slowdown > Dennis Cote wrote: > > > > Are you suggesting that SQLite is not enabling these optimizations, or that > > my code needs to do something different to enable them? > > > > The optimizations should be enabled automatically. You shouldn't have > to do anything. Perhaps the problem is that a bug in SQLite is preventing > the automatic enabling. > > Remember, this is a *wild guess*. > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Bad Database file
Hi, I just checked a database which was giving me problems using PRAGMA integrity_check; and discovered it was corrupt. sqlite> PRAGMA integrity_check; *** in database main *** List of tree roots: invalid page number 24 List of tree roots: invalid page number 23 What would be the reasons for a database to go corrupt? Anyone have any ideas regards Greg O - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] RMemory database from 2.8.9 to 2.8.13
Hi , I have an appllication for MS SQL Server which creates crosstab reports . To generate some extra speed out of the process I use SQLite (funning I think) as an in memory database. Has there been any improvements in speed or stability of the :memory: database since 2.8.9? I just want to know if I should upgrade the program. regards Greg O - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] which files to compile...
Hi Peter, Which OS are you using? I have the latest compiled LIB for Windows on my system I could send you. regards Greg - Original Message - From: "Peter Lau" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 12, 2004 8:16 AM Subject: [sqlite] which files to compile... > Hi there, > > SQLite newbie here. I am trying to compile SQLite as a static library, > then statically compile and link with my application source. > > From the reading the site and browsing the files in sqlite_source, > these are the files I think I need, am I correct? > > Thanks in advance for your help. > > pete > > p.s. is this mailing list archived somewhere so that I can search > instead of asking the already answer question? > > p.p.s. the list of files... always forgot. > > attach.c > build.c > btree.c > hash.c > func.c > where.c > vdbeaux.c > vdbe.c > vacuum.c > util.c > update.c > select.c > random.c > printf.c > pragma.c > opcodes.c > pager.c > date.c > copy.c > delete.c > btree_rb.c > parse.c > auth.c > insert.c > expr.c > tokenize.c > trigger.c > os.c > main.c > table.c > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite quite popular
Hi Guys, I just wanted everyone to know that SQLite is a very popular piece of software. I don't what Dr R stats are on downloads but from my site (I have two SQlite Wrappers), in the past 10 days I have had 290 downloads. The SQlite pages out hit any of my other pages on my web site, 4000 - 5000 per month. I think everyone who contributes to this software should pat themselves on the back, were all doing a great job and I hope it continues. Richard you should be very proud of this piece of software. Greg
[sqlite] Information Schema Views
Hello All, I have created a new wiki page off the main wiki page called Information Schema. Here is the link http://www.sqlite.org/cvstrac/wiki?p=InformationSchema The SQL standard INFORMATION_SCHEMA views are a standard way of retrieving schema information from any SQL standardised database. I thought it would be a great idea to create these views for SQLite and place the creation script on this page. People can then add them to there database as they need. Perhaps Richard could add them one day to the system one day. If anyone has any of these views perhaps you could add them to the page or send them to me. regards Greg O
[sqlite] Search results
Hi Everyone, This is a tiny bit off subject, so sorry. I have a search page on my web site which when used will search the database for results. I want to be able to rank the results in best match first order. In the database I will have three columns where I will be searching for matches , my question is what type of method do people/systems use to rate a match in the database? An example of a typical search would be . They enter the work Backup. I was thinking of selecting all rows which have backup in the three columns and then counting how many times the word appears in each column. This then would be the ranking of the result. I.e. Result rows Row 1 10 times Row 2 33 times Row 3 23 times These results would appear like this Row 2 Row 3 Row 1 Does anyone have any other idea's on ranking results? If you do please email them to me regards Greg O
Re: [sqlite] Re: SQLite Logos?
I love that logo! - Original Message - From: D. Richard Hipp To: Karl Timmermann ; [EMAIL PROTECTED] Sent: Sunday, March 28, 2004 9:36 AM Subject: [sqlite] Re: SQLite Logos? Karl Timmermann wrote: > > Do you know of any "Powered By SQLite" type logos that might exist, like > MySQL has? I would like to help support your project by advertising the > fact. > Rasmus Schultz provided this art: http://www.sqlite.org/cvstrac/getfile/sqlite/art/SQLite.gif But, no, I don't know of any "Powered By SQLite" type logos. Contributions from the artistically inclined will be welcomed. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: SQLite Logos?
No, I disagree. The aspire web site is OK but the colours don't do anything for me. The fading from blue to light blue really is quite old. With the SQlite logo I would have liked the feature to be a bit darker but I think it's great. If you compare it to the MySQL logo you can see what a darker image part does, it highlight the mean (in terms of mySQL the freedom ) In terms of the SQLite the feature is obviously the Lite indicating light footprint ) Anyway I think it's a great. Greg - Original Message - From: Allan Edwards To: 'Greg Obleshchuk' ; 'D. Richard Hipp' ; [EMAIL PROTECTED] Sent: Sunday, March 28, 2004 4:45 PM Subject: RE: [sqlite] Re: SQLite Logos? I think it is ok, but does not meet the look that such a good database technology deserves! My websites, http://www.aspire.ws, and http://store.aspire.ws, show what you can do if you are touchy on aesthetics. You can find lots of good graphical artists out their. I think you guys ought to really push some good graphical people on some creative fresh, and more VIBRANT ideas. A lot of times we get 20 or more ideas before pushing a decision. Graphical appeal is very subjective but you should be able to find something that you know just fits. The image has a nice kick to it, butt he color needs some help. Multiple colors would be optimal. Thanks, Allan -Original Message- From: Greg Obleshchuk [mailto:[EMAIL PROTECTED] Sent: Sunday, March 28, 2004 12:29 AM To: D. Richard Hipp; [EMAIL PROTECTED] Subject: Re: [sqlite] Re: SQLite Logos? I love that logo! - Original Message - From: D. Richard Hipp To: Karl Timmermann ; [EMAIL PROTECTED] Sent: Sunday, March 28, 2004 9:36 AM Subject: [sqlite] Re: SQLite Logos? Karl Timmermann wrote: > > Do you know of any "Powered By SQLite" type logos that might exist, like > MySQL has? I would like to help support your project by advertising the > fact. > Rasmus Schultz provided this art: http://www.sqlite.org/cvstrac/getfile/sqlite/art/SQLite.gif But, no, I don't know of any "Powered By SQLite" type logos. Contributions from the artistically inclined will be welcomed. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Group by behavour
Hi All, Has anyone noticed this? Try this query select tbl_name from sqlite_master group by tbl_name Now try this query select tbl_name from (select * from sqlite_master) group by tbl_name The same results should be displayed but only one row is displayed. This also affects group by when using a view Can any one confim this for me Greg
[sqlite] va_list
Hi , Can anyone tell me if you can manually create a va_list object. I have in my wrapper now the sqlite_vmprintf function but as .NET doesn't support the ... as a parameter I had to use a [ParamArray] . Currently I am testing the number of parameters passed and calling sqlite_vmprintf different ways. If I could create a va_list and just pass the object it would be a one line call. thanks Greg O
Re: [sqlite] va_list
Hi Chris, Thanks for your reply. I do have a different way of handling it in C# but I am passing the call on to the sqlite_vmprintf function (in printf.c and that accepts a va_list) The way around it that I use is having a switch statement and if the use passes one parameter the I calls sqlite_vmprintf with one parame , if two it calls it with two and so on. If I could build a va_list then I would built it and just call sqlite_vmprintf once. anyway thanks Greg - Original Message - From: Christian Smith To: Greg Obleshchuk Cc: [EMAIL PROTECTED] Sent: Thursday, April 01, 2004 8:42 PM Subject: Re: [sqlite] va_list On Thu, 1 Apr 2004, Greg Obleshchuk wrote: >Hi , Can anyone tell me if you can manually create a va_list object. I >have in my wrapper now the sqlite_vmprintf function but as .NET doesn't >support the ... as a parameter I had to use a [ParamArray] . Currently I >am testing the number of parameters passed and calling sqlite_vmprintf >different ways. If I could create a va_list and just pass the object it >would be a one line call. va_list is an opaque type as far as the C standard is concerned. How it is implemented is a platform detail of the compiler, and should not be messed with. Looking at the gcc 3.3 stdarg.h on my Linux box, va_list isn't even a structure, but a typedef for an internal __gnuc_va_list type, which is manipulated by internal gcc functions. In other words, even if you think you know how a va_list is implemented on your platform, don't go there. A world of pain awaits. Instead, look at what you're using the va_list for. C generally needs it because it's string handling is poor and *printf is the easiest way to build strings from templates. By .NET I presume you're using C#, which should have proper strings and not need the *printf hacks used in C. > >thanks >Greg O Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Group by only returning one row when source table is sub-query or view
Hi , I posted a bug ticket a few days ago (ticket 678) regarding group by only returning one row if the source was a subquery or view. I was wonder if anyone could confirm the bug and how long would a fix take? (I'm not in a position to create a fix) http://www.sqlite.org/cvstrac/tktview?tn=678,2 regards Greg
Re: [sqlite] Group by only returning one row when source table is sub-query or view
Hi Richard, It is a bit more than that for me anyway. I have several complex views which are using grouping within the view themselves. At the moment I have no other way of summarising the results of these views apart from inserting the results into a temp table and then processing the temp table, which is slow. Greg. - Original Message - From: D. Richard Hipp Cc: [EMAIL PROTECTED] Sent: Friday, April 02, 2004 8:28 AM Subject: Re: [sqlite] Group by only returning one row when source table is sub-query or view Greg Obleshchuk wrote: > > I posted a bug ticket a few days ago (ticket 678) regarding group by only > returning one row if the source was a subquery or view. I was wonder if > anyone could confirm the bug and how long would a fix take? (I'm not in a > position to create a fix) > > http://www.sqlite.org/cvstrac/tktview?tn=678,2 > It might be a bug, or it might not. What seems clear is that it is not something that can't be worked around easily, so it is not a high priority. I'll look at it when I get a chance. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A proposal for SQLite version 3.0
Hello, I just read the proposed changes and they seem fine. A couple of thing. The data types I think are great but can we have a large and small integer? I noticed some people (embedded systems people) complain about this. I quite happy with the large type but as integers will now be stored as the native type that will double the storage requirement for numbers (64-bit integer taking 8bytes where 32 take 4bytes) So may be something like if the value is a number then check to see if it fits in a 32bit integer if it does use that otherwise use a 64 bit. If this is too much trouble in code then maybe a compile directive to use a certain size integer. When using a INTEGER PRIMARY KEY perhaps we could use INTEGER SMALL PRIMARY KEY and INTEGER LARGE PRIMARY KEY Support for user definable collating. I'm assuming that this will give us the ability to turn SQLite into a non case sensitive system by defining our own collation which is not case sensitive? Which is great and you state that there will be two predefined collations COLLATE TEXT and COLLATE NUMERIC. Can I suggest that you create a third pre-defined collation (to make it easy on us that want it) can you create a COLLATE TEXT_CI . Which would be a case in-sensitive collation. Then the people that really want this can use it off the bad, as it were. I'm sure it will be a lot easier for you to create it that for someone else. API and preferred way of executing queries I'm assuming there will still be the wrapper to execute a SQL in one line . You state that there may or may not be the call-back function wrapper. I would be an advocate for keeping it. This way of handling returned data is most useful. Sometimes when returning thousands or more rows of data you want to cancel the statement without a call-back function you must wait until the statement is finished and then discard the result. Having a call-back allows you (or the user) to terminate the statement. Apart from that great changes , more complete but things change and grow (which is good). If I had to decide which is my most wanted feature from the stuff above I would say COLLATE TEXT_CI as this would enable me to use = instead of LIKE in my lookups (I really don't like case sensitive data in the real world) kind regards Greg O - Original Message - From: D. Richard Hipp To: [EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:22 PM Subject: [sqlite] A proposal for SQLite version 3.0 A design proposal for SQLite version 3.0 can be found at: http://www.sqlite.org/prop2.html Feedback from the user community is strongly encouraged. An executive summary of the proposed changes follows: * Support for UTF-16 * Better BLOB support * User-defined collating sequences (for better internationalization support) * Smaller and faster than 2.8.13. The plan is to continue to support the 2.8.X series indefinately and in parallel to the 3.X series. But the only changes to 2.8.X going forward will be bug fixes. New features will go into 3.X. Beta releases of version 3.X are expected within a few months. I do not have much experience with UTF-16 and am expecially interested in feedback on that area of the design. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A proposal for SQLite version 3.0
Hi Will, Thanks for clearing that up for me, it make more sense now. Greg - Original Message - From: Will Leshner To: Forum SQLite Sent: Monday, April 12, 2004 8:06 AM Subject: Re: [sqlite] A proposal for SQLite version 3.0 On Apr 11, 2004, at 3:01 PM, Greg Obleshchuk wrote: > You state that there may or may not be the call-back function wrapper. > I would be an advocate for keeping it. This way of handling returned > data is most useful. Sometimes when returning thousands or more rows > of data you want to cancel the statement without a call-back function > you must wait until the statement is finished and then discard the > result. Having a call-back allows you (or the user) to terminate the > statement. > With the non-callback-API, "executing" the query simply returns a virtual machine ready to get query results. It doesn't actually return any results. You then have to step through the results yourself. So you can pretty much stop the query any time you'd like. And the callback mechanism is now built on top of the non-callback mechanism anyway, so you wouldn't be able to do anything with callbacks you couldn't do without them. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A proposal for SQLite version 3.0
I think the collate feature will solve this. have a pre-defined collate TEXT_CI is the solution. One thing Microsoft allows you to do is create a database with a defined collation. Maybe this is the idea around the parameters in the open API . You could open a DB with COLLATION TEXT_CI and it would collate using the open parameter by default. If there was a column override then it would use that Greg - Original Message - From: Darren Duncan To: [EMAIL PROTECTED] Sent: Monday, April 12, 2004 8:46 AM Subject: RE: [sqlite] A proposal for SQLite version 3.0 At 11:22 PM +0100 4/11/04, Steve O'Hara wrote: >I agree with Greg, the most irksome feature of SQLite is the case >sensititvity - it's one of the few things MS got right with SQLserver. I >know this is more mainstream/standard SQL behaviour but it's outdated in >modern SQL applications that nearly always do some kind of linguistic >searching. >In fact, I'd go one step further and advocate making SQLite case-insensitive >as a rule. >I'm waiting for the flames... >Steve As far as I'm concerned, the issue of case-sensitive vs insensitive is related to locale or nationality specific matters. It involves treating a pair of different characters as being the same character. Besides our latin characters, does any other written language have such a concept as upper/lowercase? Whichever is available will probably have its fans. Case-insensitive may be more like a "natural human language" whereas the other may be less, or not. I think that both methods should be supported, perhaps with a compile time directive determining the default, and a run-time directive changing it on a case by case basis. One thing to make absolutely certain, though, is that the SQLite API provides a means to programmatically determine at runtime what behaviour is being used. If nothing else, it allows an application which either expects one behaviour or is able to adapt to either, to know how to talk to the database such that its expectations match reality. Personally, I would hate for an application which expects a case-sensitive unique field, and inserts multiple rows that it thinks are distinct, only to have one fail or overwrite the other because some case-insensitive rule says they are actually the same. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] New Web site
Hi Everyone, (I know this is off subject) I have finished a new web site dedicated to SQL Scripts for database system. I am looking for people to lodge there useful (and not) scripts onto the system . If you are looking for scripts try here first. There some useful stuff like the SQL-99 standard to download and news links. The site is http://www.sql-scripts.com I am also looking for people to link to the site. I hope everyone can help regards Greg O
Re: [sqlite] New Web site
I forgot to mention that this web site is build using SQLite as the backend database. I am in the process of writing a case study on it just so others can see some of the ways to use SQLite in a product (and what I hope will be) high hit web site. Greg - Original Message - From: Greg Obleshchuk To: [EMAIL PROTECTED] Sent: Monday, April 12, 2004 8:53 AM Subject: [sqlite] New Web site Hi Everyone, (I know this is off subject) I have finished a new web site dedicated to SQL Scripts for database system. I am looking for people to lodge there useful (and not) scripts onto the system . If you are looking for scripts try here first. There some useful stuff like the SQL-99 standard to download and news links. The site is http://www.sql-scripts.com I am also looking for people to link to the site. I hope everyone can help regards Greg O
Re: [sqlite] Adding SQL commands
Hi Basil, The first use of IF is same syntax as the case statement so I don't think it is required and as IF isn't SQL92 I doubt it will be included so your command is replaced by case when (select count(*) from foo) = 100 then 'good' else 'bad' end I like the idea of exist but then again you can do this (in a query) count where (Select count(*) from foo where col1 ='a') = 1 but what I really think you are talking about is a command language like TSQL for Microsoft or P/SQL for Oracle (is it P/SQL what ever?) At the moment you can't do that type of thing is SQLite. I think the reason here is the it is a Database system that is included in your application and therefore you application can make these decisions far better that a SQL language Greg O Don't for get www.SQL-Scripts.Com - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 8:03 AM Subject: [sqlite] Adding SQL commands I would like to add some commands to SQLite to make my SQL(ite) programming life easier!!! The commands are: IF - e.g IF ((select count(*) from foo) = 100) select "good"; ELSE select "bad"; END EXISTS - e.g IF EXISTS(select * from sqlite_master where name = 'foo') DROP TABLE foo; END local variables - e.g. DECLARE @var TEXT; select @var = name from foo; I am not familiar with how compilers work but would be willing to write the "c" code to make these commands work. Does anyone have a simple way to add a command using the lemon parser??? I find the documentation hard to understand and I would like to add these command s using the c++ compiler from Visual Studio.Net B.Thomas This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.
Re: [sqlite] row size limit
Hi Richard, You know that is the first clear and concise explanation of why not to store large blobs in a database that I have heard anywhere. Greg - Original Message - From: D. Richard Hipp Cc: [EMAIL PROTECTED] Sent: Monday, April 19, 2004 9:50 AM Subject: Re: [sqlite] row size limit [EMAIL PROTECTED] wrote: > According to the FAQ on sqlite.org, the row size is arbitrarily > limited to 1MB, which can be increased to 16MB by changing a > #define in the source code. > > My question is, why even limit the row size? Is there a way the > code can modified so that there is no limit for the row size (other > than the available disk/memory space)? > The current file format allocates 24 bits for storing the number of bytes in a particular row. (See http://www.sqlite.org/fileformat.html for details.) So the currect version of SQLite will never allow more than 16MB in one row. The proposed version 3.0.0 of SQLite uses a variable-length integer to store the row size and can thus accomodate up to 2^64 bytes in a single row. In theory. But a limit of 1MB will probably still be enforced. Why is this? SQLite stores large rows by breaking the data up into 1K chunks and storing each chunk in a separate page of the database. Filesystems do much the same thing in an operating system. But filesystems have an advantage over databases in that they can access the many chunks that make up a file in parallel, whereas SQLite has to access them one at a time. Suppose you have a 1MB row in SQLite and you want to read the whole thing. SQLite must first ask for the 1st 1K page and wait for it to be retrieved. Then it asks for the 2nd 1K page and waits for it. And so forth for all 1000+ pages. If each page retrieval requires 1 rotation of the disk, that's about 8.5 seconds on a 7200RPM disk drive. The filesystem code inside the operating system can do the same task in parallel. If you ask the operating system to read in all of a 1MB file for you, it can request many separate blocks from the disk controller at once. The blocks might arrive out of order, but the OS can reassemble them into the correct order before returning the result up to the user-space process. Using this approach, only a few rotations of the disk platter would be required to retrieve a 1MB file, instead of thousands. The retrival will be 100s of times faster. The moral of the story: If you have large amounts of data you want to store, it is best to store that data in a separate file and then write the name of that file into the database. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] row size limit
>>Indeed. But I wonder if most all databases do it the same way? Or do >>all file-based dbs do it the same way? etc. I guess it would depend on the system. I assume (and may ask) that MS SQL and Oracle use multi-threaded processes to access the information and that is the way they get around it. I know the MS is looking at replacing the file system with the SQL engine in Longhorn so they must have solved the issue. The speed that they can generate from queries would indicate that they have solved the issue. But perhaps the smaller systems PostgreSQL and/or MySQL don't. Greg - Original Message - From: Puneet Kishor To: SQLite Sent: Monday, April 19, 2004 10:41 AM Subject: Re: [sqlite] row size limit On Apr 18, 2004, at 7:31 PM, Greg Obleshchuk wrote: > Hi Richard, > You know that is the first clear and concise explanation of why not to > store large blobs in a database that I have heard anywhere. Indeed. But I wonder if most all databases do it the same way? Or do all file-based dbs do it the same way? etc. Nice explanation though. I am currently working on a web-based image gallery application powered by Oracle and am storing file names in the db while the images themselves are stored on the disk. Some said why don't I just stick the images in the db and I said no... the OS would be better and more flexible at managing the physical files. Now I have a more scientific sounding answer to back my assertion. ;-) > > Greg > - Original Message - > From: D. Richard Hipp > Cc: [EMAIL PROTECTED] > Sent: Monday, April 19, 2004 9:50 AM > Subject: Re: [sqlite] row size limit > > > [EMAIL PROTECTED] wrote: >> According to the FAQ on sqlite.org, the row size is arbitrarily >> limited to 1MB, which can be increased to 16MB by changing a >> #define in the source code. >> >> My question is, why even limit the row size? Is there a way the >> code can modified so that there is no limit for the row size (other >> than the available disk/memory space)? >> > > The current file format allocates 24 bits for storing the number of > bytes in a particular row. (See > http://www.sqlite.org/fileformat.html > for details.) So the currect version of SQLite will never allow more > than 16MB in one row. The proposed version 3.0.0 of SQLite uses a > variable-length integer to store the row size and can thus accomodate > up to 2^64 bytes in a single row. In theory. > > But a limit of 1MB will probably still be enforced. Why is this? > > SQLite stores large rows by breaking the data up into 1K chunks and > storing each chunk in a separate page of the database. Filesystems > do much the same thing in an operating system. But filesystems have > an advantage over databases in that they can access the many chunks > that make up a file in parallel, whereas SQLite has to access them > one at a time. > > Suppose you have a 1MB row in SQLite and you want to read the whole > thing. SQLite must first ask for the 1st 1K page and wait for it to > be retrieved. Then it asks for the 2nd 1K page and waits for it. > And so forth for all 1000+ pages. If each page retrieval requires > 1 rotation of the disk, that's about 8.5 seconds on a 7200RPM disk > drive. > > The filesystem code inside the operating system can do the same task > in parallel. If you ask the operating system to read in all of a > 1MB file for you, it can request many separate blocks from the disk > controller at once. The blocks might arrive out of order, but the > OS can reassemble them into the correct order before returning the > result up to the user-space process. Using this approach, only a few > rotations of the disk platter would be required to retrieve a 1MB > file, instead of thousands. The retrival will be 100s of times > faster. > > The moral of the story: If you have large amounts of data you want > to store, it is best to store that data in a separate file and then > write the name of that file into the database. > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] sqlite with Visual Basic
Carlos, No you can't the call-back API and the API thats returns a C style array aren't supported in VB Regards Greg > -Original Message- > From: Carlos Garces [mailto:[EMAIL PROTECTED] > Sent: Friday, 14 May 2004 6:00 AM > To: [EMAIL PROTECTED] > Subject: [sqlite] sqlite with Visual Basic > > Hi! > I can use SQLLite with Visual Basic without using other > external DLL Any sample of using sqlite.dll API? > > Thanks > Carlos Garcés > > > . > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] sqlite with Visual Basic
Hi Carlos, Sorry mate I didn't point out the wrappers on Sqlite.Org http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers Greg > -Original Message- > From: Jalil Vaidya [mailto:[EMAIL PROTECTED] > Sent: Friday, 14 May 2004 11:13 AM > To: Carlos Garces; [EMAIL PROTECTED] > Subject: Re: [sqlite] sqlite with Visual Basic > > There is a VBWrapper.zip in the old SQLite yahoo group's file > section. The archive contains wrapper over the SQLite API so > that it can be used from VB. > The API declarations for VB are also in the archieve. > Get it from here: > > http://f4.grp.yahoofs.com/v1/EBqkQKgMWwt8clzVakFnZ6GAGVancQ9q- > 4gKNXEFX9QQmKIvHaVaTJ9rp1fz-XHaFxRIq46etnp1v_WDUrQPABeSNps/VB% > 20Wrapper > > If you cannot download from the link above then you will have > to join the group to get it. > > HTH, > > Jalil Vaidya > > Disclaimer: I have never used this wrapper myself. > > --- Carlos Garces <[EMAIL PROTECTED]> wrote: > > Hi! > > I can use SQLLite with Visual Basic without using other > external DLL > > Any sample of using sqlite.dll API? > > > > Thanks > > Carlos Garcis > > > = > 01001010 > 0111 > 01101100 > 01101001 > 01101100 > > > > > __ > Do you Yahoo!? > Yahoo! Movies - Buy advance tickets for 'Shrek 2' > http://movies.yahoo.com/showtimes/movie?mid=1808405861 > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] sqlite with Visual Basic
Hi Murray,
Can I make a suggestion or two. Firstly I wouldn't use Crystal Report. It
sucks and I hate it , have a look at ActiveReports from DataDynamics
Version 2.0 is fantastic and very light on it's feet (small). The price is
great as well.
Also ActiveReport supports ADO , DAO, RDO and XML. You can pragmatically
send it a array and bind to that.
With the ODBC driver, I'm not a big fan of installing drivers (ODBC) during
setup or supplying them with the application. That's why the wrapper I
wrote doesn't use them.
A & G Software
Quality software with Style
Greg Obleshchuk
Partner
A & G Software
Victoria
Australia
[EMAIL PROTECTED]
http://www.ag-software.com <http://www.ag-software.com/>
MSN: [EMAIL PROTECTED]
<http://www.ag-software.com/ags_scribe_index.aspx> Document any Microsoft
database in minutes
<http://www.ag-software.com/xp_ags_crosstab.aspx> Generate Cross tab
results from Microsoft SQL Server 2000
_
From: Murray Moffatt [mailto:[EMAIL PROTECTED]
Sent: Monday, 17 May 2004 2:14 PM
To: [EMAIL PROTECTED]
Subject: RE: [sqlite] sqlite with Visual Basic
I'm interested in using SQLite from within a Visual Basic 6 app that I'm
writing. I need reporting features and so was looking at using Crystal
Reports.
I'm guessing that it would be easiest to use SQLite ODBC for accessing the
SQLite database, as the VB tools and Crystal Reports support ODBC.
When I downloaded SQLite ODBC from <http://www.ch-werner.de/sqliteodbc/>
http://www.ch-werner.de/sqliteodbc/ and looked at the readme file I was a
bit concerned when I read that this was still "experimental". Obviously if I
want to distribute my app to others I want something that isn't going to
bomb out every so often! If anyone else is using SQLite ODBC in an app I'd
like to hear how stable it is?
Also the readme file says that it has to be installed and databases set up
by using the ODBC Control Panel applet. Obviously I don't want to have to
tell people that they have to set all this up themselves by hand after they
install my app, so is there any way I can programmatically set up the ODBC
connection? Either via my installation program or within the main app
itself?
At 20:32 16/05/2004 +0200, you wrote:
Personally, I use my own wrapper (attached) with Greg Obleshchuk's
AGS_SQLite.dll. It is very simple to use (it will generate the SQL code for
you):
Dim db as ADOSQLite
Set db = new ADOSQLite
db.Connect "database.db"
db.Table = "mytable"
db.Action = ActionSelect
db.Sort = "id"
db.Where = "id < 100"
db.Exec
If Not db.Err Then
do while not db.EOF
debug.print db.field("id")
db.MoveNext
Loop
End if
Of course this runs "in-memory" so if this is a concern for you, then try to
narrow down your selects.
Cheers!
Chris
> -Original Message-
> From: Raymond Irving [mailto:[EMAIL PROTECTED]
> Sent: 14 May 2004 17:50
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite with Visual Basic
>
>
> I use SQLite ODBC with ADO to access databases from VB. It
> works like a charm.
>
> http://www.ch-werner.de/sqliteodbc/
>
> __
> Raymond Irving
>
>
> Jérôme_VERITE <[EMAIL PROTECTED]> wrote:
> I use this wrapper and to simplify again the code, I created
> littles classes which are very simple and look like the DAO
> classes. It permits to transform Simply a VB application
> using DAO to SQLITE
>
> Jérôme
>
> -Message d'origine-
> De : Steve O'Hara [mailto:[EMAIL PROTECTED]
> Envoyé : vendredi 14 mai 2004 10:35
> À : Jalil Vaidya; Carlos Garces; [EMAIL PROTECTED]
> Objet : RE: [sqlite] sqlite with Visual Basic
>
>
> Here's the SQLite wrapper for VB from the Yahoo groups
>
> Steve
>
> -Original Message-
> From: Jalil Vaidya [mailto:[EMAIL PROTECTED]
> Sent: 14 May 2004 02:13
> To: Carlos Garces; [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite with Visual Basic
>
>
> There is a VBWrapper.zip in the old SQLite yahoo
> group's file section. The archive contains wrapper
> over the SQLite API so that it can be used from VB.
> The API declarations for VB are also in the archieve.
> Get it from here:
>
> http://f4.grp.yahoofs.com/v1/EBqkQKgMWwt8clzVakFnZ6GAGVancQ9q-
> 4gKNXEFX9QQmKI
> vHaVaTJ9rp1fz-XHaFxRIq46etnp1v_WDUrQPABeSNps/VB%20Wrapper
>
> If you cannot download from the link above then you
> will have to join the group to get it.
>
> HTH,
>
> Jalil Vaidya
>
> Disclaimer: I have never used this wrapper myself.
>
> --- Carlos Garces wrote:
> > Hi!
> > I
RE: [sqlite] Sqlite Secure backup
Hi David, I have been thinking about developing a backup/restore as part of my wrapper. It would be great to have all of these features compatible. I really wanted to backup the database and then have the ability to restore just one table or the entire DB. What do you think? Maybe we should create a wiki page on sqlite.org? Greg -Original Message- From: David Costa [mailto:[EMAIL PROTECTED] Sent: Thursday, 3 June 2004 9:26 AM To: [EMAIL PROTECTED] Subject: [sqlite] Sqlite Secure backup Hello Everyone, I am a contributor on the php project ( my profile is available at http://pear.php.net/user/gurugeek ) where I maintain several extensions. I am in the process of writing a PEAR package to handle Sqlite backups (both local backups and remote backups) and I am looking for some suggestions. Is the right way to proceed the db dump ? I assume that copying the db will result in a corrupted file if the db is used at the time of backup. The aim would be to dump or duplicate a whole database for a local or remote backup with the relevant integrity checks. Thanks in advance for your time and attention I remain, Very much obliged, Regards, David Costa PEAR- PHP Extensions and Application Repository Developer http://pear.php.net/user/gurugeek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] ISO 8859
Hello, I had this guy email me about a problem about using ISO8859 character . He has this in the DB Handhilfsbetätigungssatz but when queried using my wrapper it returns Handhilfsbet..tigungssatz When using SQLITE.EXE the results are displayed fine. When I debug the code the char * returned in the call-back event for this column has this character as value -124 . Which I assume is an overflowed 7 bit value. Can anyone help me understand how to fix this in the source? thanks Greg
