[sqlite] Re: 'cating' sqlite databases
On Thursday 08 April 2004 01:49 pm, Stathy G Touloumis wrote: > Hi, > > We are working on a potential architecture by which many sqlite databases > are created. There may be a need to aggregate data between databases and I > was wondering if it's possible to quickly just 'cat' the databases together > or will they need to be recreated into a single sqlite file. You cannot just 'cat' the files together. However, you can accomplish what you need very easily. Assume the 'main' db is open, and you want to take the contents of table 'a' in database 'second.db' and concatinate it to main table 'a': attach 'second.db' as second; insert into a (select * from second.a); detach second; Repeat this for all tables you want to cat together. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] 'cating' sqlite databases
Hi, We are working on a potential architecture by which many sqlite databases are created. There may be a need to aggregate data between databases and I was wondering if it's possible to quickly just 'cat' the databases together or will they need to be recreated into a single sqlite file. Thanks, - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A proposal for SQLite version 3.0
D. Richard Hipp wrote: > The 1st and 3rd APIs above will work, but not the second. Remember, > SQLite 3.0 will have manifest typing, which means that type of the > data can change from one row to the next. Type is not associated > with a column, as in standard SQL. So there is no way to know the > type in advance. > > Manifest typing is a feature, not a bug. The static typing design > of SQL is the bug. ;-) I wasn't sure how much type information SQLite gathered about the literals it needs to be assigned to parameters. If there is none, then this API doesn't make much sense after the statement is prepared, but it is still needed to determine the parameter's type after it has been bound to a particular value (with a manifest type). That type may be needed to call the correct parameter data readback function. > Someone earlier suggested that the same named parameter could occur > in multiple places in the input SQL, but you should only have to bind > it once. That argument makes sense to me. But allowing multiple > occurrences of the same named parameter means that the name->index map > is not unique so the function above will not work. The name to index mapping must be unique and one-to-one. The idea is to have a single parameter value with a name that may appear multiple times in the SQL statement. Every occurrence of that name in the statement is replaced with the bound value when statement is executed. That is why SQLite must scan the parameter list each time it parses a parameter name in the statement. It must check if it has already assigned an index number to this name, or if it needs to assign a new index number for a new name. The index numbers are used internally in the VDBE code that is generated to execute the statement. The client application then only needs to bind the value of the parameter once, not once for each time it appears in the original SQL statement. > Is that really the desired behavior? If you want to reset parameters > on a statement reset, wouldn't it be better to do so explicitly. That > way, if a statement has 10 parameters, and you want to execute it 10 > times, and only one parameter changes between each run, you do not > have to reinitialize the other 9 every time. No, that's what happens when you add a quick comment without giving it enough though. You are right, it makes much more sense to leave all parameters with there current value after a reset. If the user want to change any value they can use the bind API to change just the parameters that need to change, and they can set them to null if they want. This way only the values that need to change are modified, rather than having SQLite change them to null, and the user change many of them back to their previous value. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] A proposal for SQLite version 3.0
> > > > A statement's parameter values would be reset to null > values when the > > statement is reset. > > > > Is that really the desired behavior? If you want to reset > parameters on a statement reset, wouldn't it be better to do > so explicitly. That way, if a statement has 10 parameters, > and you want to execute it 10 times, and only one parameter > changes between each run, you do not have to reinitialize the > other 9 every time. > You could add an option to the reset function to specify whether the parameters should be reset. Tim McDaniel - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Encryption in 2.8.13
Ulrik Petersen wrote: I see that in 2.8.13, there are stubs for an encryption layer, but the encryption itself seems not to have been made publicly available. Any chance of this becoming public in the next release? Form what I understand of the description on the SQlite site, encryption is available to customers who pay DRH for support. Sorta "value added services". Regards P. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Encryption in 2.8.13
Ulrik Petersen wrote: I see that in 2.8.13, there are stubs for an encryption layer, but the encryption itself seems not to have been made publicly available. Any chance of this becoming public in the next release? If not, is there any chance that the stubs could be documented so that one can write one's own encryption? Encryption is a valued-added extension. It is available in the US for a small fee. Outside the US, there are issues of export license and so forth. I've never worked through those problems before so I'm not sure what all is involved. I have decided not to make the encryption extension available for free at this time. Sorry. -- 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
Dennis Cote wrote: The API should provide functions that allow the application to inspect the number, type, and names of the parameters that were discovered while parsing the SQL. These functions could be called any time after the statement is prepared. int sqlite3_param_count(sqlite3_stmt* stmt); int sqlite3_param_type(sqlite3_stmt* stmt, int iParm); const char* sqlite3_param_name(sqlite3_stmt* stmt, int iParm); The 1st and 3rd APIs above will work, but not the second. Remember, SQLite 3.0 will have manifest typing, which means that type of the data can change from one row to the next. Type is not associated with a column, as in standard SQL. So there is no way to know the type in advance. Manifest typing is a feature, not a bug. The static typing design of SQL is the bug. ;-) You have the value data pointers declared as static char and void pointers. I believe they should be const pointers. You're right. A typo. I would propose adding a second set of parallel API functions that would allow the application to bind the parameters by name for those cases (likely far more common) where the application knows the names of the parameters beforehand This second set of these bind functions could be eliminated by the use of a single new API function that would return the index for a parameter given its name. int sqlite3_param_index(sqlite3_stmt* stmt, const char* name); Someone earlier suggested that the same named parameter could occur in multiple places in the input SQL, but you should only have to bind it once. That argument makes sense to me. But allowing multiple occurrances of the same named parameter means that the name->index map is not unique so the function above will not work. A statement's parameter values would be reset to null values when the statement is reset. Is that really the desired behavior? If you want to reset parameters on a statement reset, wouldn't it be better to do so explicitly. That way, if a statement has 10 parameters, and you want to execute it 10 times, and only one parameter changes between each run, you do not have to reinitialize the other 9 every time. -- 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
D. Richard Hipp wrote: > A design proposal for SQLite version 3.0 can be found at: > > http://www.sqlite.org/prop2.html > Richard, I read your proposal and it all look very promising to me. I would like to propose some additions to the API to support named parameters in the SQL statement. These features will make it easier to use parameters in complex SQL statements, and also allow generalized handling of SQL statements in cases where the statement to be executed is not know before it is executed (this occurs with manually entered SQL in database "explorer" applications for example). It also makes the use of pre-prepared SQL more straight forward. First I would propose that SQLite recognize named parameters in the SQL code. A named parameter is a colon, ':', followed by the parameter name. The name itself must be a valid SQL identifier, like table and column names. Some example names are; :foo, :bar, :part_number, and :zip_code. SQLite would recognize the named parameters when the statement is parsed by the prepare API function. SQLite will maintain a list of parameters found in the statement. As each parameter name recognized, SQLite will scan the list of parameters to see if this name has appeared earlier in the statement. If so it will use the previously assigned index for that parameter. If not, it will add the new parameter name to the end of the list (effectively assigning it the next index value). The API should provide functions that allow the application to inspect the number, type, and names of the parameters that were discovered while parsing the SQL. These functions could be called any time after the statement is prepared. int sqlite3_param_count(sqlite3_stmt* stmt); int sqlite3_param_type(sqlite3_stmt* stmt, int iParm); const char* sqlite3_param_name(sqlite3_stmt* stmt, int iParm); The first function returns the number of parameters in the statement. The second returns the type code of the indexed parameter. This function would use the same type codes as the sqlite3_column_type function. The third would return the name (without the colon prefix character) of the parameter. The parameter names may be used to build a menu of parameters for a user to fill in before the statement is executed. The application could use the currently proposed bind functions to bind a value to the parameter. int sqlite3_bind_int32(sqlite3_stmt*, int iParm, int value); int sqlite3_bind_int64(sqlite3_stmt*, int iParm, long long int value); int sqlite3_bind_double(sqlite3_stmt*, int iParm, double value); int sqlite3_bind_null(sqlite3_stmt*, int iParm); int sqlite3_bind_text(sqlite3_stmt*, int iParm, const char* value, int length, int eCopy); int sqlite3_bind_text16(sqlite3_stmt*, int iParm, const char* value, int length, int eCopy); int sqlite3_bind_blob(sqlite3_stmt*, int iParm, const void* value, int length, int eCopy); I noticed a error in the prototypes for the bind text and blob API functions in the proposal. You have the value data pointers declared as static char and void pointers. I believe they should be const pointers. I would propose adding a second set of parallel API functions that would allow the application to bind the parameters by name for those cases (likely far more common) where the application knows the names of the parameters beforehand. This way the application can bind to the parameters by name without being forced to use the parameter inspection functions to determine the parameter indexes. This would also be resistant to errors introduced by reordering the parameters while editing the SQL statement without reordering the bind calls. int sqlite3_bind_name_int32(sqlite3_stmt*, const char* name, int value); int sqlite3_bind_name_int64(sqlite3_stmt*, const char* name, long long int value); int sqlite3_bind_name_double(sqlite3_stmt*, const char* name, double value); int sqlite3_bind_name_null(sqlite3_stmt*, const char* name); int sqlite3_bind_name_text(sqlite3_stmt*, const char* name, const char* value, int length, int eCopy); int sqlite3_bind_name_text16(sqlite3_stmt*, const char* name, const char* value, int length, int eCopy); int sqlite3_bind_name_blob(sqlite3_stmt*, const char* name, const void* value, int length, int eCopy); This second set of these bind functions could be eliminated by the use of a single new API function that would return the index for a parameter given its name. int sqlite3_param_index(sqlite3_stmt* stmt, const char* name); Using this function and one of the index based bind functions together would accomplish the same thing, but it may introduce slightly more overhead due to repeated checking of the statement pointer etc. This function could return an invalid index of zero if the name does not match any of the parameters for the statement. I think the invalid, zero, index approach should be quite safe since the bind functions will need to check and report the invalid index through its error
[sqlite] SQLite version 3.0
I've looked at the proposed changes for SQLite V3, and, whilst it all looks reasonable, it does absolutely nothing for me... The things I'd like would be more at the 'lower' levels of the database. I'd like to see the query engine be able to use multiple indices if appropriate, rather than just one as it can currently do. (It might be possible then to extend this to a simple query optimiser or automatic index generation etc at a later stage - possibly as a plug-in-able system based on the 'explain' output of the parser) I'd also like to see the facility for writing our own locking system to be used by SQLite. For instance, SQLite could have callbacks for 'table locks', 'row locks', 'page locks' or whatever. If the callback isn't implemented in the application they could equate to file locks as they do now, but having the callbacks would allow the application to put a file lock on the database to stop other programs interfering, then implement its own table/row/page lock as appropriate if it would help. This would help concurrency within a single (multithreaded) application or server, whilst still allowing the current system for simple usage. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] A proposal for SQLite version 3.0
The proposal for version 3.0 looks very promising and I hope all the major changes will be implemented. Most of the enhancements seem to add flexibility and scalability to to the "C" based api. This is great as at the "C" api call level you can do anything you want. But I do not see any changes to add similar flexibility at the SQL language level. Declarative programming is much more productive than writing many lines of "c" code to do any data manipulation tasks. The current SQL language implementation excels at the vast majority of data manipulation needs but falls short in being able to implement a complete data management system at the SQL language level. One example is creating a database. SQLite can only create a database at the "c" api level. This should be changed to be able to use a CREATE DATABASE command. The sqlite3_open function should be implemented with one major change. Instead of passing the complete file name of the database, pass in the name of the directory where the master/system database will be created/opened. Only the master/system database should be created automatically if one is not located in that directory. A sqlite3_create function as well as a CREATE DATABASE command would create a user database in the same directory as the master/system database by default as the system database would contain a list of all user database that have been created under that system database. Once a database has been created, a USE command would open that database if it is found in the master/system database and make this the current database. The DETACH command could then delete the user database link into the master/system database and this user database would then be free to ATTACH to another master/system database. Once this functionality is implemented it should not be hard to implement a sqlite_users table in the master/system database to holder user information so the GRANT/REVOKE command could be implemented against any user database listed in the master/system database. All of the above is based on the simple command of creating a database at the SQL language level. Many other enhancements to the SQL language could/should be implemented and I would gladly make more suggestions if any other current users of SQLite feel that this would be a good step forward B.Thomas -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 9:22 AM To: [EMAIL PROTECTED] 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] 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] A proposal for SQLite version 3.0
Yes, that is right, my database consists of mainly numbers. Some of them have 10 digits, some have less. This would explain part of the difference. I was also wondering if Access perhaps has some type of index comprimation, like cutting pre- and postfixes in the B-tree. Jakub Simon Berthiaume wrote: It might be true if you mostly store large quantities of NUMERICAL data (float, int, ...) since they are currently stored as strings (the value 1234567, would be stored using at least 7-8 bytes instead of 4). If you were in the same situation as I, where I use database mostly for strings, you would be in the opposite situation, the SQLite database would be about half the size of the MS Access one, since MS Access seems to save all strings as UTF-16. Simon B. On Wed, 2004-04-07 at 10:50, Jakub Adamek wrote: Hello, I am using SQLite in a car navigation system which should work on PDAs as well. Thus speed and size is crucial for us. SQLite is superb in the speed category, but the size of its file is not so superb. I see you mentioned something about file size. My experience is that SQLite makes roughly about 3x bigger files than MS Access. How would this change in 3.0? Thanks for your excellent work, Jakub Adamek D. Richard Hipp wrote: 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. - 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] Clustered indicies Was: [sqlite] A proposal for SQLite version 3.0
Ben, I think you're asking for is a mechanism to define the nature of the primary key. e.g. If you could define the primary key as some kind of function or to use a specific integer field, then the table will be naturally ordered by that field. I don't know about other SQL engines, but our baby (BASIS) allows you to create your primary key like this - this gives us tremendous performance improvements for some apps. For instances, we have some clients who store newspaper articles from various publications in a table. The natural way in which users want to see these articles is sorted by the published date, latest first - that's fine, but these tables contain 40 million articles and their queries can produce pretty large sets. We create a primary key based on the published date which means we don't need to sort their queries. Just a thought Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 08 April 2004 04:05 To: [EMAIL PROTECTED] Subject: [sqlite] Clustered indicies Was: [sqlite] A proposal for SQLite version 3.0 Jeff, Jeff Pleimling <[EMAIL PROTECTED]> 08/04/2004 12:42 PM To: [EMAIL PROTECTED] cc: Subject:Re: [sqlite] A proposal for SQLite version 3.0 At 12:08 PM 4/8/2004 +1000, [EMAIL PROTECTED] wrote: > I believe you're thinking of a 'clustered index'. This puts the data > into the order of the index. There can be, of course, only one clustered > index per table. > Since at least some of the data in the table is moved around on every insert, > regular indexs need to take this into account (usually by indirection, rather > then modifying all of the indexes with each insert). Ahh, I didn't think of that. I don't have any other indices on my table, so this wouldn't be a problem for me... but I can see now how it would harm the general case. I guess the simplest implementation would have a proabition on having -any- other indicies on the table. > >If a table could be ordered according to an index, rather than having an > >external index, I think it would significantly improve the time and space > >performance of my databases. I don't know whether my experience would be > >shared by other users. It it were something that could go into 3.0 it > >would at least do me some good. > Clustered indexes can really slow the performance for OLTP (On-Line > Transaction Processing) and other systems where data is added/deleted in a > mixed fashion. Every time a record is inserted, data is possibly moved on > the disk (with page splits causing even more slowdowns). Yes, that's what's happening already in the index whenever I do an insert. My thinking was that maintaining an index-ordered table would be less work overall than maintaining a table with an ordered index. I could be wrong on that, but I'm not sure I see the flaw in my logic. > If your system is entirely historic data, that would be great - but if your > system is inserting meter readings in (near) real-time, you'd probably > get a big performance hit. It's certainly real-time, with the occasional query. > There are many pros and cons. A google search turns up articles (usually for > MS SQL Server) on both side - some people saying 'always' and some 'never'. I can certainly see how the usefulness of this feature could be limited. I guess the problem is primarily related to how the rowid is chosen. If I could choose a rowid that would put the table in the correct order, and renumber as required I might make some ground. I could order by ROWID, then... although there may have to be some kind of code tweakage to allow the where clauses to operate correctly. Hmm... maybe something like this: BEGIN TRANSACTION; SELECT * FROM mytable WHERE -- if the insertion point is after current data: INSERT INTO mytable VALUES (MAX(ROWID) + 10, ) -- else if insertion point is between two values INSERT INTO mytable VALUES (( + )/2, ) -- else rearrange contiguous values UPDATE mytable SET ROWID = ROWID+1 WHERE ROWID >= AND ROWID < INSERT INTO mytable VALUES (, ) END TRANSACTION; Perhaps the changes to sqlite could be as minimal as providing a facility to say: "I promise to keep these rows in an order consistent with this index" so sqlite will use the index in queries. Benjamin. - 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 proposal for SQLite version 3.0
> Could we please see the current behaviour set in stone? I'd like to know > that generated keys will always fit into 32 bits (provided I don't > exceed ~4.3 billion records, naturally). I think that it's a dangerous precent to fix these things in stone, certainly at the source level. Perhaps, should it become a concern, offer the option at compile time to have 32/64/n bit keys. Far too often I've seen stone-cast items such as this become the agony of others. Paul. -- Paul L Daniels - PLD Software - Xamime Unix systems Internet Development A.B.N. 19 500 721 806 ICQ#103642862,AOL:pldsoftware,Yahoo:pldaniels73 PGP Public Key at http://www.pldaniels.com/gpg-keys.pld - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A proposal for SQLite version 3.0
I have an application which depends on INTEGER PRIMARY KEY values fitting into 32 bits. I know that currently, the keys are generated with MAX()+1, but this behaviour is "undocumented and liable to change.: Now that rowid's are going 64 bit, a truly random INTEGER PRIMARY KEY may not fit into 32 bits. Could we please see the current behaviour set in stone? I'd like to know that generated keys will always fit into 32 bits (provided I don't exceed ~4.3 billion records, naturally). Thanks -- Andrew Francis Lead Developer Family Health Network - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
