-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Simon Slavin wrote: > I googled, and there doesn't seem to be a page which describes the abilities > and limitations the extension system has.
A C function of your choosing can be called on each new connection being created: http://sqlite.org/c3ref/auto_extension.html You can create collations, functions, virtual tables and VFS: http://sqlite.org/c3ref/collation_needed.html http://sqlite.org/c3ref/create_collation.html http://sqlite.org/c3ref/create_function.html http://sqlite.org/c3ref/create_module_v2.html http://sqlite.org/c3ref/vfs_find.html [Details given to help future searches] > (A) Is it possible to define a new datatypen ? At the SQLite level no. This is because a new datatype would require a change to the file format and would require adding APIs for that type (eg to bind values, receive values etc). If you wanted it visible in SQL then you'd also need new syntax which means updating the tokenizer and grammar. (Note: SQLite code is public domain so you are free to make an incompatible version with desired capabilities - it is just a lot of work.) > If the data itself was really stored in a BLOB, that would be okay, but I wouldn't want my users to know that, I'd want them to use this new datatype when they created columns. You can do this by wrapping your access to the SQLite C API. For example some language bindings do it (but there are gotchas). I'll use an example of a Point object (with x and y members). You would need to provide an equivalent to the bind API: http://sqlite.org/c3ref/bind_blob.html For example you could make bind_point which then created the blob from the point data supplied and called sqlite3_bind_blob. You could even make this possible at the SQL level by a user defined function that returns a blob so this would work: INSERT INTO foo VALUES( Point(3,4) ); When values are returned you'd need some way of trying to extract a Point instead of a blob. In some cases you can use the declared type of a column: http://sqlite.org/c3ref/column_decltype.html However that only works when there are no calculations on a column. IIRC you also get NULL when going via a view. Note that existing SQLite functions are not going to know anything about your "type". For example you'll be able to quite happily get the length of a Point and + or || operators certainly won't do what the user would expect. > (B) If not, then if I needed to store some additional information for every > TEXT field, could I do that ? If it was necessary to store it in a different > table in the same database, That is trivial to do using virtual tables. > but can an extension do that sort of thing Yes, extensions can implement virtual tables. > And can a routine of an extension be automatically called whenever any row, > column or table in the database is created/deleted ? Mostly, see the virtual table method list: http://sqlite.org/c3ref/module.html Note: you only get called with tables specifically created using your virtual table module. > (C) Is it possible to put a note inside a database file so that if a database > was opened without a particular extension being loaded, an error would occur ? No. That would require a file format change. However if the file has a virtual table and the module for that virtual table is not loaded then there will be an appropriate error message generated on first access to that virtual table. If you want to experiment with virtual tables and know Python then APSW is a good place to start (disclosure I am the author of APSW). http://apsw.googlecode.com/svn/publish/vtable.html http://apsw.googlecode.com/svn/publish/example.html#example-vtable Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEZsIACgkQmOOfHg372QSUoQCgyPr14la+YXkx67hxIBG9njhI otoAoIsR3ASbbuMoYxqbEwnaeBhOQr2O =N5UO -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
