Re: [sqlite] Proposed new sqlite3_open_v3() interface
On 3 May 2010, at 15:47, D. Richard Hipp wrote: Community feedback is requested for the following proposed new SQLite C API: int sqlite3_open_v3(const char*, sqlite3**, int, const char*); ... (3) The default database file format would be format 4 (meaning that new databases would be unreadable by versions of SQLite prior to 3.1.3). Just to confirm, would opening an existing database using sqlite3_open_v3() be moved to format 4, or left as-is? Also, is there any information on the newer format for curious users? I can only see a single paragraph on it at: http://www.sqlite.org/compile.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed new sqlite3_open_v3() interface
On Tue, May 4, 2010 at 3:17 AM, Ben sqlite_l...@menial.co.uk wrote: On 3 May 2010, at 15:47, D. Richard Hipp wrote: Community feedback is requested for the following proposed new SQLite C API: int sqlite3_open_v3(const char*, sqlite3**, int, const char*); ... (3) The default database file format would be format 4 (meaning that new databases would be unreadable by versions of SQLite prior to 3.1.3). Just to confirm, would opening an existing database using sqlite3_open_v3() be moved to format 4, or left as-is? Left as-is. Format 4 would be used when creating new databases. This would be the equivalent of running: PRAGMA legacy_file_format=OFF; Also, is there any information on the newer format for curious users? I can only see a single paragraph on it at: http://www.sqlite.org/compile.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed new sqlite3_open_v3() interface
On Tue, May 04, 2010 at 08:17:11AM +0100, Ben scratched on the wall: Also, is there any information on the newer format for curious users? I can only see a single paragraph on it at: http://www.sqlite.org/compile.html That's all there is to it. v4 added a different encoding for integers that allows the values 0 and 1 to be stored with one less byte. There is also support for descending indexes, which I assume is just a simple flag in the index header. Of course, a significant amount of raw SQL is stored in the database file (like all the table, index, and view definitions). If you use newer features and SQL structures in your database, there may be other versioning issues. Just because the file format is backwards compatible doesn't always mean the database is. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?
On Mon, 3 May 2010 00:39:37 +0400, Alexey Pechnikov pechni...@mobigroup.ru wrote: it's easy for TCL developers. You may build tclsqlite+tclhttpd+your tcl scripts as starpack (single executable binary). For integrate SQLite database into starpack use this extension: http://www.siftsoft.com/tclsqlitevfs.html Thanks. It looks like it's the easiest way to pack a web server + SQLite in one go. I don't know TCL: Is it easy to quickly write the script that will turn POSTed queries into SQL and send them to SQLite, before returning status/data? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Encryption of sqlite DB
Hi, What’s the simplest way to encrypt only certain rows in an sqlite DB? If there is no way to do this (for storing passwords etc), I would like to know the best way to encrypt the whole sqlite DB. (Prefer only encrypting some rows, but if this introduces complexity, I’m willing to encrypt the whole database) kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption of sqlite DB
On Tue, May 4, 2010 at 9:19 AM, Kavita Raghunathan kavita.raghunat...@skyfiber.com wrote: Hi, What’s the simplest way to encrypt only certain rows in an sqlite DB? If there is no way to do this (for storing passwords etc), I would like to know the best way to encrypt the whole sqlite DB. (Prefer only encrypting some rows, but if this introduces complexity, I’m willing to encrypt the whole database) Do you mean you want to encrypt only some columns ? Stephan kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Stephan Wehner - http://stephan.sugarmotor.org (blog and homepage) - http://loggingit.com - http://www.thrackle.org - http://www.buckmaster.ca - http://www.trafficlife.com - http://stephansmap.org -- http://blog.stephansmap.org - http://twitter.com/stephanwehner / @stephanwehner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption of sqlite DB
On Tue, May 4, 2010 at 11:19 AM, Kavita Raghunathan kavita.raghunat...@skyfiber.com wrote: Hi, What’s the simplest way to encrypt only certain rows in an sqlite DB? If there is no way to do this (for storing passwords etc), You certainly mean some or all columns in all the rows, don't you? Well, no matter -- you can encrypt any column in any row using any one way hashing algorithm. Good enough for routine password storage, etc. I would like to know the best way to encrypt the whole sqlite DB. (Prefer only encrypting some rows, but if this introduces complexity, I’m willing to encrypt the whole database) The sqlite developer sells a proprietary encryption mechanism for a very reasonable price. While the sqlite source code is in public domain, the encryption-enabled source code is not in public domain. So, if you buy it, you are supposed to NOT resell or redistribute it. I have no experience with it, but from occasional hearsay, it is supposed to be just as good as sqlite itself, so probably worth every cent spent on it. kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption of sqlite DB
Hello KR What’s the simplest way to encrypt only certain rows in an sqlite DB? KR If there is no way to do this (for storing passwords etc), I would KR like to know the best way to encrypt the whole sqlite DB. (Prefer only KR encrypting some rows, but if this introduces complexity, I’m willing KR to encrypt the whole database) There is an encryption extension which costs money, from the same people that brought you SQLite. Or, assuming you mean you want to encrypt certain columns, you could do this from your application (C, PHP or whatever). If you have the clear text password as input, you can put it through an encryption function, and then use that as a parameter for a query, rather than the clear text. Or you could create a user defined function to call your encryption function, and then use this function in your SQL statements. Swithun.___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption of sqlite DB
This really depends on what you are trying to protect. Passwords are the common data pieces that would be protected in this manner. The easiest way to encrypt a specific row is to put the data through some sort of one way hash function before you write the data to the table. However since this is symmetric, anyone with the key can decrypt the data easily. Also, depending on the strength of the hash function, anyone with the hashed data could decrypt the data using a brute force attack. I believe that Java and C both have basic hash functions. In terms of performance, hashing is probably the most economical and widely accepted method. Use the largest key strength possible without hindering performance. This will take some trial and effort. I would be remiss if I didn't mention that this is in no way a bullet proof method of protecting data in a database. You have to first analyze the data you are protecting, and determine the cost to you if someone were to intercept the data. If the data is transmitted over the network in clear text for example, anyone with a sniffer and a laptop can intercept and change the data without you knowing about it. In that case, your hash function has little or no effect. In the case of passwords, this is a most dangerous method for password storage. You also have to consider the security of the database itself. How is it accessed? Where is it stored? If the data is widely accessible, then this is akin to closing the barn door after the horse got out. Timothy A. Sawyer, CISSP Managing Director MBD Solutions -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Tuesday, May 04, 2010 12:19 PM To: Discussion of SQLite Database Subject: [sqlite] Encryption of sqlite DB Hi, What's the simplest way to encrypt only certain rows in an sqlite DB? If there is no way to do this (for storing passwords etc), I would like to know the best way to encrypt the whole sqlite DB. (Prefer only encrypting some rows, but if this introduces complexity, I'm willing to encrypt the whole database) kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Doing fine with SQLite
I can work SQLite from by R stat package, but I am having hard time mixing special sqlite command intermixed with SQL statements when I send a text sequence to swqlite (even from the dos consol) sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, and can't find it in the docs. That is my last minor detail, and I have indices, joins, and unions going on the Bureau of Labor Statistic under R. Will make SQLite quite popular among the economists. Getting access to reams of data from economic we sites, directly into R dataframes via a set of common key words familiar to economists. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing fine with SQLite
On Tue, May 04, 2010 at 10:02:06AM -0700, Matt Young scratched on the wall: sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, You can't put dot-commands on the command line. Try: $ sqlite3 -csv test.db select * from selected limit 4 and can't find it in the docs. $ sqlite3 --help -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing fine with SQLite
echo .mode csv input.sql echo select * from selected limit 4 input.sql sqlite3 test.db input.sql Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Matt Young Sent: Tue 5/4/2010 12:02 PM To: sqlite-users@sqlite.org Subject: [sqlite] Doing fine with SQLite I can work SQLite from by R stat package, but I am having hard time mixing special sqlite command intermixed with SQL statements when I send a text sequence to swqlite (even from the dos consol) sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, and can't find it in the docs. That is my last minor detail, and I have indices, joins, and unions going on the Bureau of Labor Statistic under R. Will make SQLite quite popular among the economists. Getting access to reams of data from economic we sites, directly into R dataframes via a set of common key words familiar to economists. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing fine with SQLite
You can't put dot-commands on the command line. Try: I think you can with something like this (assuming your shell is bash): echo $'.mode csv\nselect * from selected limit 4' | sqlite3 test.db Pavel On Tue, May 4, 2010 at 1:11 PM, Jay A. Kreibich j...@kreibi.ch wrote: On Tue, May 04, 2010 at 10:02:06AM -0700, Matt Young scratched on the wall: sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, You can't put dot-commands on the command line. Try: $ sqlite3 -csv test.db select * from selected limit 4 and can't find it in the docs. $ sqlite3 --help -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption of sqlite DB
Timothy A. Sawyer tsaw...@mybowlingdiary.com wrote: The easiest way to encrypt a specific row is to put the data through some sort of one way hash function before you write the data to the table. However since this is symmetric, anyone with the key can decrypt the data easily. A function is either one-way or it is symmetric - it can't possibly be both at the same time. You seem to be confusing hashing and encryption. Also, depending on the strength of the hash function, anyone with the hashed data could decrypt the data using a brute force attack. Brute force attack doesn't depend on the strength of the hash function. Use the largest key strength possible without hindering performance. Hash functions don't use keys. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing fine with SQLite
You don't need to dump the data to a csv file and then read it into R and there is no need to use the sqlite3 console at all as R's RSQLite package can directly read and write SQLite databases. Also see the sqldf package. On Tue, May 4, 2010 at 1:02 PM, Matt Young youngsan...@gmail.com wrote: I can work SQLite from by R stat package, but I am having hard time mixing special sqlite command intermixed with SQL statements when I send a text sequence to swqlite (even from the dos consol) sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, and can't find it in the docs. That is my last minor detail, and I have indices, joins, and unions going on the Bureau of Labor Statistic under R. Will make SQLite quite popular among the economists. Getting access to reams of data from economic we sites, directly into R dataframes via a set of common key words familiar to economists. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: O'Reilly book Using SQLite available for pre-order
In conjunction with O'Reilly Media, I am happy to announce that the book Using SQLite is now available for pre-order. When released later this summer, the title should also be available in several popular ebook formats. O'Reilly Media: http://oreilly.com/catalog/9780596521189/ Amazon: http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/ You can help! Using SQLite is taking part in O'Reilly Lab's Open Feedback Publishing System (OFPS). While we continue to prepare the final draft, you can read an online version of the book and leave feedback. Changes and edits to the current draft are pushed to the website daily, allowing you to track and watch as the book takes its final form. I invite all SQLite list members to register and participate: http://using-sqlite.labs.oreilly.com/ Thank you for your help and support! -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] why sqlite engine gets slower when executing transactions
Hi, We are using sqlite to generate a schema with ~600 tables + 500 indexes + 50 views and some other triggers. Our module: - Opens a transaction - Creates a table - Inserts some rows - Commits the transaction It seems this takes a lot of time to execute (doing it for all objects created), however in case we close and reopen the connection from time to time (at each 1000th transaction) things are working way faster. It looks like sqlite engine is doing something wrong when transactions are over used. Is there a known bug regarding this? Regards, Romy. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT questions
Hi there,
trying to INSERT a new row into a table and got a few errors which I think I've
sorted, I was getting a couple of errors that date columns for FirstCreated and
LastModified (datetime Type) may not be NULL, so I included them in my insert
line as follows
INSERT INTO Aircraft (FirstCreated,LastModified,ModeS) values ('2010-05-04
09:21:31','2010-05-04 09:21:31','C4');
and it seems to have worked as that row is now in my (test)database, what I was
wondering about were the boolean fields, they are all filled in with zeroes, so
this is a good sign, yes? Also, what about the relationships between this
table and others in the database, should everything be OK as I want to share
this but not screw up other peoples databases
mtia
Mark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] reuse of named parameters within a single statement
Sorry if this is a repeat but I am having a heck of a time figuring out a definitive answer to a this question on the list. Certainly it is not addressed in the documentation. Is the following valid string to prepare: SELECT @myparam, @myparam, @myparam, @myotherparam If so, which is it equivalent to: A - SELECT ?1, ?1, ?1, ?2 or B - SELECT ?1, ?2, ?3, ?4 Aron -- Aron Rubin Handy Husband Daddy Jungle Gym Senior Engineer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] reuse of named parameters within a single statement
Aron Rubin aronru...@gmail.com wrote: Is the following valid string to prepare: SELECT @myparam, @myparam, @myparam, @myotherparam Yes. If so, which is it equivalent to: A - SELECT ?1, ?1, ?1, ?2 or B - SELECT ?1, ?2, ?3, ?4 A -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] reuse of named parameters within a single statement
On Tue, May 04, 2010 at 01:48:52PM -0400, Aron Rubin scratched on the wall: Sorry if this is a repeat but I am having a heck of a time figuring out a definitive answer to a this question on the list. Certainly it is not addressed in the documentation. Is the following valid string to prepare: SELECT @myparam, @myparam, @myparam, @myotherparam If so, which is it equivalent to: A - SELECT ?1, ?1, ?1, ?2 or B - SELECT ?1, ?2, ?3, ?4 A. -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] reuse of named parameters within a single statement
It would be great to include SELECT @myparam, @myparam, @myotherparam, @myparam is it equivalent to SELECT ?1, ?1, ?2, ?1 in the documentation on parameters (in expressions). Thank you, Aron On 5/4/10, Jay A. Kreibich j...@kreibi.ch wrote: On Tue, May 04, 2010 at 01:48:52PM -0400, Aron Rubin scratched on the wall: Sorry if this is a repeat but I am having a heck of a time figuring out a definitive answer to a this question on the list. Certainly it is not addressed in the documentation. Is the following valid string to prepare: SELECT @myparam, @myparam, @myparam, @myotherparam If so, which is it equivalent to: A - SELECT ?1, ?1, ?1, ?2 or B - SELECT ?1, ?2, ?3, ?4 A. -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Aron Rubin Handy Husband Daddy Jungle Gym Senior Engineer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Small change to support poor man's extended features
If I could have a SQL function that returned an id of the current frame I could create poor man's variables. In turn that would allow me to create return locations for stored procedure triggers and branching. The id would need to be unique against any parallel executions. -- Aron Rubin Handy Husband Daddy Jungle Gym Senior Engineer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing fine with SQLite
All working. R had sufficient piping to files and allowed me to get it. On 5/4/10, Gabor Grothendieck ggrothendi...@gmail.com wrote: You don't need to dump the data to a csv file and then read it into R and there is no need to use the sqlite3 console at all as R's RSQLite package can directly read and write SQLite databases. Also see the sqldf package. On Tue, May 4, 2010 at 1:02 PM, Matt Young youngsan...@gmail.com wrote: I can work SQLite from by R stat package, but I am having hard time mixing special sqlite command intermixed with SQL statements when I send a text sequence to swqlite (even from the dos consol) sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, and can't find it in the docs. That is my last minor detail, and I have indices, joins, and unions going on the Bureau of Labor Statistic under R. Will make SQLite quite popular among the economists. Getting access to reams of data from economic we sites, directly into R dataframes via a set of common key words familiar to economists. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: O'Reilly book Using SQLite available for pre-order
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/04/2010 02:19 PM, Jay A. Kreibich wrote: I invite all SQLite list members to register and participate: OpenID - no need for yet another username and password. I've always been mystified why these kind of books duplicate installation instructions that already exist on the download site of whatever they are documenting. Same thing with the copious amounts of 'reference' information that adds no value over what is on the web site and the book will quickly become out of date. I suggest there is far more value in the kind of questions that pop up here fairly frequently. For example a lot more detail on modeling trees with a thorough worked example (eg storing information about every file on a filesystem) and way more on performance (how to measure it, how to diagnose where the time goes, how to reduce time taken, concurrency etc). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvgsFwACgkQmOOfHg372QTCcQCfZZBo5oRgCuSr/xBt5NoP+Kd1 zS8AoJMepLyfYHM9FbUvbl1J0ISCvc4Y =zrYM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Small change to support poor man's extended features
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/04/2010 03:50 PM, Aron Rubin wrote: If I could have a SQL function that returned an id of the current frame I could create poor man's variables. You'll need to be more specific about what you mean here. Give an example of the code you would like to write. The only use of the word frame I know of in this context is stack frame. It is certainly possible to do what you ask in Python (sys.getframe) but SQLite is in C and that doesn't require physical frames or return locations. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvgwygACgkQmOOfHg372QSDmQCfab0xF6Mk6m7heU8Anhoyeims gJkAn0++iJqxLqRpm/1XoNmdzZ28LcqZ =MA96 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: O'Reilly book Using SQLite available for pre-order
I've always been mystified why these kind of books duplicate installation instructions that already exist on the download site of whatever they are documenting. Same thing with the copious amounts of 'reference' information that adds no value over what is on the web site and the book will quickly become out of date. It's quite hard to add comments in the margin of a website (you should see my OpenSSL book!). Sure, if the book is just a copy-paste of the website, that's not too helpful. But hopefully things are stated differently, or examples are given which can be valuable. Good luck with the book Jay. Doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Small change to support poor man's extended features
On Tue, May 4, 2010 at 9:00 PM, Roger Binns rog...@rogerbinns.com wrote: On 05/04/2010 03:50 PM, Aron Rubin wrote: If I could have a SQL function that returned an id of the current frame I could create poor man's variables. You'll need to be more specific about what you mean here. Give an example of the code you would like to write. The only use of the word frame I know of in this context is stack frame. It is certainly possible to do what you ask in Python (sys.getframe) but SQLite is in C and that doesn't require physical frames or return locations. Most execution environments that support calling including C and Sqlite use a stack of frames. Looking at the Sqlite code, VDBE maintains a stack of frames. I am suggesting that these frames are assigned an id that is unique for that parallel execution. Since I do not know the rules, present or planned, for parallelism in Sqlite I cannot clearly define what those ids are. If each parallel path, i.e. database open from programs, is assigned a unique id then the combination of that id and the call depth would be sufficient for my purposes. Aron -- Aron Rubin Handy Husband Daddy Jungle Gym Senior Engineer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Implementing a CREATE_FUNCTION function
It's convenient to be able to define new functions in C. But sometimes,
it would be *more* convenient to be able to define new functions in
SQL. This could be done by registering a CREATE_FUNCTION() function;
then you could write something like:
SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)');
My first idea for implementing CREATE_FUNCTION is:
1. Create (if not exists) a table with columns for the function name,
number of arguments, and SQL expression.
2. Add the new function to the table.
3. Call sqlite3_create_function to register the new function.
C doesn't have the ability to create functions at runtime, so the xFunc
parameter would refer to a common global function, which would:
1. Look up the SQL expression corresponding to the SQL function name.
2. Evaluate the expression.
But how do I get the SQL function name from within the xFunc function?
Can I get it from the sqlite3_context object, or do I have to use
sqlite3_user_data()?
Or is there a better approach?
As a first step, I've written an EVAL() function, which supports
parameter binding.
// EVAL(expr [, param]*)
void x_sqlite_eval(sqlite3_context* pContext,
int argc,
sqlite3_value* argv[])
{
sqlite3* pDB = sqlite3_context_db_handle(pContext);
sqlite3_stmt*pStmt = NULL;
const unsigned char* expr= NULL;
char*sql = NULL;
size_t len;
int err;
int index;
if (argc == 0)
{
sqlite3_result_null(pContext);
return;
}
expr = sqlite3_value_text(argv[0]);
len = sqlite3_value_bytes(argv[0]);
// Build the SQL statement SELECT (expr)
sql = malloc(len + 10);
if (sql == NULL)
{
sqlite3_result_error_nomem(pContext);
return;
}
memcpy(sql, SELECT (, 8);
memcpy(sql + 8, expr, len);
sql[8 + len] = ')';
err = sqlite3_prepare_v2(pDB, sql, len + 9, pStmt, NULL);
if (err != SQLITE_OK)
{
sqlite3_result_error_code(pContext, err);
goto EXIT;
}
// bind parameters
for (index = 1; index argc; index++)
{
err = sqlite3_bind_value(pStmt, index, argv[index]);
if (err != SQLITE_OK)
{
sqlite3_result_error_code(pContext, err);
goto EXIT;
}
}
// execute the statement
err = sqlite3_step(pStmt);
if (err != SQLITE_ROW)
{
sqlite3_result_error_code(pContext, err);
goto EXIT;
}
sqlite3_result_value(pContext, sqlite3_column_value(pStmt, 0));
EXIT:
sqlite3_finalize(pStmt);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
