Re: [sqlite] INSERT OR REPLACE
Alek Paunov <a...@declera.com> writes: > So maybe it's worth to give it a try ... Is there someone else, which > is interested to work on VDBE dump/load to assembler representation? > Once these base tools are available, maybe other people would reuse > bytecode instrumentation for other purposes also ;-) I don't have time to work on this at the moment, but here is my input: I think the improved update functionality is important, and presumably easily supported by sqlite's existing virtual machine. I disagree that databases should not be used to perform calculations. Actually SQL can be very concise for expressing calculations, and in fact the database engine may be able to optimise the query better than what a client could achieve by issuing separate SELECT and UPDATE queries. Also, by making use of views, triggers and stored procedures, the database can provide a useful abstraction layer. Sqlite already does this very well with views and triggers, but lacks parameterised view/stored procedure functionality. Another reason to use the database for doing calculations (including iterative or recursive ones) is that you can use the native SQL data representation, including things like NULL support which are often missing from other languages, plus it can be faster because of not needing to convert types between sqlite's internal representation and another language representation. It has been noted that sqlite already has a turing-complete virtual machine, so why not provide the front-end facilities to make full use of it? I expect it wouldn't take much effort to implement a procedural language and/or recursive joins based on the current virtual machine. I realise this is a 'lite' database, but it seems as if the heavy lifting has already been done. In researching this previously I noted an existing effort at adding a procedural language to sqlite: 1. http://www.sqliteconcepts.org/PL_index.html This effort looks quite good although I think it needs some refinement. It adds the ability to execute procedural code immediately or in a stored procedure. Both would be exceptionally useful additions to sqlite, even if available only as an optional module. I would suggest using PostgreSQL as a reference for implementing a procedural language, since it has a robust procedural language implementation, and I and I suspect others already use both postgres and sqlite. I realise I have gone off in a somewhat different direction from the VDBE dump/load capability. I think this would be a useful feature as well, but more useful for experimenting with the development of new functionality, such as compiling new language constructs. Probably both could could be done at once. -- Mario Becroft <m...@becroft.co.nz> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tuning a SQLite database for best compression
I don't know whether this is practical in your application, but if you want to reduce redundancy in the database file when downloading, the obvious thing is to leave off the indexes, and generate them once the file is downloaded onto the device. This way you'll have to download only the actual data set, and this combined with compression should be about as small as you can go without using domain-specific data compression or reformulating the data model. This is similar to loading from DML statements, which you don't want to do, but executing only the CREATE INDEX statements after downloading might be more acceptable than loading the entire data set. -- Mario Becroft <m...@becroft.co.nz> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] seeking database design advice
I agree with what has been said so far regarding normalization, getting the underlying structure right, and using views to access the data. I would add a couple of points: Database refactoring is not necessarily as hard as has been suggested. In fact, a good design, separating data storage (tables) and data access (views) will not only minimize the need for future changes, but make such changes easy to do when required. For instance you can change the tables while retaining the same views, or vice versa. Writing queries to migrate data from old to new schemas without interrupting operations (on databases suitably designed to enable this) is every-day work for DBA's. Normalizing your data is a good idea, but the question really is what is normalized in this case? Treating the set of fields and their names as data (as in your design B) may be the best way. However, were this mode of reasoning taken too far, you might end up with a database having no structure at all, besides that implied by the data stored in it (which is open to interpretation). The whole point of SQL is that it provides tools to help specify the structure of your data. Making schema changes, such as adding or removing attributes, is not necessarily difficult. This is what DML is for. However one thing SQL does not handle well is temporal schemas, i.e. ones in effect during different periods of time. The main reason why you might need to use design B instead of A in this case is if you need to be aware of which fields were valid at past times or will be valid in the future; for instance, if you remove a field but you do not wish to lose the information previously reported while that field existed, or more subtly, if after adding a new field, you need to know that past reports explicitly did not include that field. There are also other reasons why the field names seem more like data than schema in this case and I would agree that, based on the information provided so far, design B is almost certainly better. I am just making the point that the question is a little more subtle than the people saying 'you must normalize' have implied, and you are right to try and better understand the reasoning behind that opinion. -- Mario Becroft <m...@becroft.co.nz> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Constraint error messages
Just adding my voice to the choir. The constraints are of limited value if you can't tell which one failed, and the system is not much more 'lite' if the constraints have to be duplicated using CHECK clauses anyway. -- Mario Becroft <m...@becroft.co.nz> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
