[sqlite] How to find Rank in SQLite3?
I have to migrate from MySQL to SQLite3. Following query in mysql gives the Rank for each employee within his dept on the basis of salary. mysql-> select e.DEPTNO, e.EMPNO, e.LASTNAME, e.FIRSTNAME, e.SAL, find_in_set(e.SAL, x.SALLIST) RANK -> from EMPLOYEES as e, (select DEPTNO, group_concat(SAL order by SAL desc) SALLIST -> from EMPLOYEES -> group by DEPTNO) as x -> where e.DEPTNO = x.DEPTNO -> order by DEPTNO, RANK; I have used following query but it takes more than one hour even after indexing, mysql->select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL, -> (select 1 + count(*) -> from EMPLOYEES b -> where b.DEPTNO = a.DEPTNO -> and b.SAL > a.SAL) RANK -> from EMPLOYEES as a -> order by x.DEPTNO, x.RANK; My questions are 1. How to find Rank in SQLite? 2. group_concat does not have order by clause, My data is already sorted, but would output of group_concat be unsorted? 3. How to implement find_in_set in SQLite? Please note that my database contains more than two lakhs rows. Regards, shahj Disclaimer note on content of this message including enclosure(s)and attachments(s): The contents of this e-mail are the privileged and confidential material of National Stock Exchange of India Limited (NSE). The information is solely intended for the individual/entity it is addressed to. If you are not the intended recipient of this message, please be aware that you are not authorized in any which way whatsoever to read, forward, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, we would request you to please notify the sender immediately by return e-mail and delete it from your computer. This e-mail message including attachment(s), if any, is believed to be free of any virus and NSE is not responsible for any loss or damage arising in any way from its use. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed regression after 3.6.17
> > After much playing about, I have determined that it is necessary to violate > the guidelines on not using INDEXED BY and NOT INDEXED in an attempt to > optimise performance. Although you're very determined about your conclusions, I saw a misunderstanding about INDEXED BY in your statements. The docs say "The INDEXED BY clause is *not* intended for use in tuning the preformance of a query". From my point of you one should think about these extensions as sqlite's sql equivalent of assertions from high-level languages. For example, a quick test. I have a table Contacts with an index ids_Contacts using field CenterId. If I try to execute SELECT * FROM Contacts INDEXED BY idx_Contacts sqlite says: "cannot use index: idx_Contacts". Only when a change made adding explicit ORDER BY clause applied SELECT * FROM Contacts INDEXED BY idx_Contacts ORDER BY CenterId ... the query executes successfully. If I remove now INDEXED BY from the latter statement nothing will change in how the query is executed, _only_ if someone excplicitely deletes the index from the database. So any of your statements that argues that adding or removing INDEXED BY affects the way your queries is executed is have to be double checked. If you're still sure that you found a major flaw in the most deployed sql database in the world, please narrow your tests and conclusions to something easier reproducible. Everyone understands that you're solving one particular problem related to you, but if you find time to make things more simple, it will be to everyone's benefit. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] after recover lost data still lost
Try: sqlite3 /path/to/SSD/databasefile .dump >db.sql If that doesn't recover enough, do the same for every single table in the database: sqlite3 /path/to/SSD/databasefile .dump tbl1 >db_tbl1.sql sqlite3 /path/to/SSD/databasefile .dump tbl2 >db_tbl2.sql :: sqlite3 /path/to/SSD/databasefile .dump tblN >db_tblN.sql That is what i get after doing (sqlite3 memos.db .dump > db.sql) db.sql contains the following three lines: PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; To get tha names of the tables i did the following: sqlite> SELECT name FROM sqlite_master ...> WHERE type='table' ...> ORDER BY name; and i get: Error: database disk image is malformed -- View this message in context: http://old.nabble.com/after-recover-lost-data-still-lost-tp27135953p27143298.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to find Rank in SQLite3?
> I have used following query but it takes more than one hour even after > indexing, Shouldn't be - query is not so hard if a proper index used. What index did you create? > mysql->select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL, > ... > -> order by x.DEPTNO, x.RANK; Interesting how you're talking about trying query in SQLite but command line is from MySQL and even query is incorrect because there's no table 'x' here... > 1. How to find Rank in SQLite? I believe your rewritten query is the best you can do here, of course if you have good index and don't want to calculate the rank ins the application. > 2. group_concat does not have order by clause, My data is already sorted, > but would output of group_concat be unsorted? It's undefined. If SQLite chooses to use an appropriate index so that data is processed in sorted way then result inside group_concat will just happen to be sorted. But if SQLite chooses to use some other index or to not use index at all then your data will be unsorted. > 3. How to implement find_in_set in SQLite? I believe with the answer on the 2nd question answer to this one is unnecessary, isn't it? Pavel On Wed, Jan 13, 2010 at 5:06 AM, Jigar Shah wrote: > I have to migrate from MySQL to SQLite3. > > Following query in mysql gives the Rank for each employee within his dept > on the basis of salary. > > mysql-> select e.DEPTNO, e.EMPNO, e.LASTNAME, e.FIRSTNAME, e.SAL, > find_in_set(e.SAL, x.SALLIST) RANK > -> from EMPLOYEES as e, (select DEPTNO, group_concat(SAL order by > SAL desc) SALLIST > -> from EMPLOYEES > -> group by DEPTNO) as x > -> where e.DEPTNO = x.DEPTNO > -> order by DEPTNO, RANK; > > > I have used following query but it takes more than one hour even after > indexing, > > > mysql->select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL, > -> (select 1 + count(*) > -> from EMPLOYEES b > -> where b.DEPTNO = a.DEPTNO > -> and b.SAL > a.SAL) RANK > -> from EMPLOYEES as a > -> order by x.DEPTNO, x.RANK; > > > > My questions are > > 1. How to find Rank in SQLite? > > 2. group_concat does not have order by clause, My data is already sorted, > but would output of group_concat be unsorted? > > 3. How to implement find_in_set in SQLite? > > Please note that my database contains more than two lakhs rows. > > > > > Regards, > > shahj > > > > Disclaimer note on content of this message including enclosure(s)and > attachments(s): The contents of this e-mail are the privileged and > confidential material of National Stock Exchange of India Limited > (NSE). The information is solely intended for the individual/entity > it is addressed to. If you are not the intended recipient of this > message, please be aware that you are not authorized in any which > way whatsoever to read, forward, print, retain, copy or disseminate > this message or any part of it. If you have received this e-mail in > error, we would request you to please notify the sender immediately > by return e-mail and delete it from your computer. This e-mail > message including attachment(s), if any, is believed to be free of > any virus and NSE is not responsible for any loss or damage arising > in any way from its use. > ___ > 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] Writes during sleep of backup_step
On Jan 13, 2010, at 10:27 AM, Jean-Christophe Deschamps wrote: > I've finally implemented the backup API and it works like a charm > except on an important point. > The example given on the site clearly says: > > "If another thread writes to database connection pDb while this > function is sleeping, then the backup database (database connection > pFile) is automatically updated along with pDb. The backup process is > continued after the xSleep() call returns as if nothing had happened. > If the database file underlying connection pDb is written to by a > different process or thread using a different database connection > while > this function is sleeping, then SQLite detects this within the next > call made to sqlite3_backup_step() and restarts the backup from the > beginning." Is it an in-memory database you're backing up (i.e. is pDb in-memory)? I have a feeling that statement won't apply if the source db is an in-memory database. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error in sqlite3 database opening
Dear Sir/Maa'mI working in the applicaton where I need to open the sqlite3 database and then to call system command but I am not able to call any system command after calling any sqlite3 database command please help me.Thanks & RegardsRohan SharmaProgrammer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to find Rank in SQLite3?
Would all employees with the same salary have the same rank within a department? On 1/13/2010 5:06 AM, Jigar Shah wrote: > I have to migrate from MySQL to SQLite3. > > Following query in mysql gives the Rank for each employee within his dept > on the basis of salary. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to find Rank in SQLite3?
Assuming you're doing this by department, try to get just a list of the distinct salary rankings into an inline view, using your count(*) +1 approach to set the salary rank, where these three columns are unique in combination: (dept, salary, rank ) as S and then : select EE.dept, EE.name, EE.salary, S.rank from employees as EE JOIN (inline view to create distinct salary bands by department goes here) as S on EE.dept = S.dept and EE.salary = S.salary order by dept, rank If you make the innermost inline view (select distinct dept, salary) and then use an outer to set the salary-band rankings within department, you will be working with far fewer than 200,000 rows, and the salary-bank rankings-by-department inline view will probably be held in a transient table and used as the inner loop. I would put an index on dept and salary in the EEs table. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error in sqlite3 database opening
On 13 Jan 2010, at 8:39am, Rohan wrote: > Dear Sir/Maa'mI working in the applicaton where I need to open the sqlite3 > database and then to call system command but I am not able to call any > system command after calling any sqlite3 database command What happens when you try ? And if it's an error message, tell us which one, don't just say "error message". Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to find Rank in SQLite3?
In my last post, I wrote "salary bands" but it would be clearer to say "salary tiers". Dept, salary, rank 1, 75000, 1 1, 5, 2 1, 49000, 3 2, 7, 1 2, 68000, 2 3, 52000, 1 Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLJet 1.0.2 is released.
Hello, We're glad to announce that SQLJet 1.0.2 has been released and available for download at http://sqljet.com/ web site. SQLJet is an independent pure Java open source implementation of the SQLite core functionality. SQLJet provides API to create, read and modify SQLite databases, but not to execute SQL queries yet. SQLJet 1.0.2 version is maintenance release that improves SQLJet stability and performance. Additionally, this version of SQLJet is available through Maven repository or as an OSGi bundle. For more information, including detailed changelog, please visit SQLJet home page at http://sqljet.com/ With best regards, SQLJet developers. -- Alexander Kitaev, TMate Software, http://svnkit.com/ - Java [Sub]Versioning Library! http://sqljet.com/ - Java SQLite Library! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writes during sleep of backup_step
Hi Dan, > > I've finally implemented the backup API and it works like a charm > > except on an important point. > > The example given on the site clearly says: > > > > "If another thread writes to database connection pDb while this > > function is sleeping, then the backup database (database connection > > pFile) is automatically updated along with pDb. The backup process is > > continued after the xSleep() call returns as if nothing had happened. > > If the database file underlying connection pDb is written to by a > > different process or thread using a different database connection > > while > > this function is sleeping, then SQLite detects this within the next > > call made to sqlite3_backup_step() and restarts the backup from the > > beginning." > >Is it an in-memory database you're backing up (i.e. is pDb in-memory)? > >I have a feeling that statement won't apply if the source db is >an in-memory database. That's correct: only disk-based databases enjoy the feature. Thanks for the hint. This really should be clarified in both the documentation and the example page. OTOH, is there a possibility that someday the backup sub-engine rewrites only dirty pages and not the whole file from scratch? This would requires that SQLite makes a note of which pages have become dirty between two backup-steps and certainly other housekeeping as well. Is this something that could be made in some future version? I feel it would make backups _much_ easier to cope with in the case of live databases where spurious writes are difficult to avoid. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed regression after 3.6.17
"Max Vlasov" wrote in message news:7cb963ca1001130315o69235717n92393be027eef...@mail.gmail.com... > > >> After much playing about, I have determined that it is necessary to >> violate >> the guidelines on not using INDEXED BY and NOT INDEXED in an attempt to >> optimise performance. > > > Although you're very determined about your conclusions, I saw a > misunderstanding about INDEXED BY in your statements. > The docs say "The INDEXED BY clause is *not* intended for use in tuning > the > preformance of a query". From my point of you one should think about these > extensions as sqlite's sql equivalent of assertions from high-level > languages. Max, thanks for your follow up. I think we understand the guideline the same way. It says that one should not use INDEXED BY for tuning a query. I had to use INDEXED BY and NOT INDEXED to tune my query because the query optimiser mal-tuned it by ~70:1. After 3.6.17, the query optimiser tuned even worse - to ~1000:1. Operating on ANALYZE stats took what was the fastest result with a couple of INDEXED BY clauses down to ~70:1. The only way I could get the fastest result across different sqlite versions and with/without ANALYZE stats was to do the very opposite of what the docs say - i.e., use INDEXED clauses liberally to restrict what the query optimiser can do to screw up my query. I don't understand your last sentence as I am not a programmer and have no experience of working with sql assertions. > For example, a quick test. > I have a table Contacts with an index ids_Contacts using field CenterId. > If I try to execute > > SELECT * FROM Contacts INDEXED BY idx_Contacts > > sqlite says: "cannot use index: idx_Contacts". > > Only when a change made adding explicit ORDER BY clause applied > > SELECT * FROM Contacts INDEXED BY idx_Contacts ORDER BY CenterId > > ... the query executes successfully. That's odd. Using a SQLite manager with sqlite 3.6.21, both lines give the same error on one of my tables with 5 indexes. SELECT Surname FROM NameTable INDEXED BY idxSurname SELECT Surname FROM NameTable INDEXED BY idxSurname ORDER BY Surname COLLATE NOCASE And EXPLAIN QUERY PLAN indicates that the query optimiser used no index in both cases when the INDEXED BY clause was deleted. > If I remove now INDEXED BY from the latter statement nothing will change > in > how the query is executed, _only_ if someone excplicitely deletes the > index > from the database. Assuming that this was indeed a multi-table query that worked, the query can only execute identically with/without the INDEXED clauses provided the query optimiser consistently used the same indexes. And, yes, it would throw up an error if the INDEXED BY index had been dropped. > So any of your statements that argues that adding or removing INDEXED BY > affects the way your queries is executed is have to be double checked. Isn't that what I demonstrated with the comparative results of EXPLAIN QUERY PLAN? The optimiser chose inappropriate indexes when allowed to do so. Your statement assumes that the query optimiser chose the same indexes as the ones I chose in iteratively tuning for fastest speed. > If you're still sure that you found a major flaw in the most deployed sql > database in the world, please narrow your tests and conclusions to > something > easier reproducible. Everyone understands that you're solving one > particular > problem related to you, but if you find time to make things more simple, > it > will be to everyone's benefit. Max, I don't know that I have found a major flaw in the database engine or that I have thrown up a very exceptional database structure that its optimiser can't properly deal with. I most certainly had to do the opposite of what the online docs said about the use of INDEXED clauses. My tests are easily reproducible if you care to let me send you the database and queries. The SQLite manager developer who led me to investigate the change in performance around 3.6.18 of my early query that lacked INDEXED clauses has also offered the database in the thread he started titled "Performance regression". I don't know how I can make things more simple - I'm not a programmer and my interest in sqlite is pretty narrow. I am hoping that bringing my problem and observations to this forum will elicit the necessary interest from the sqlite developer community as to why it was necessary for me to violate their very clear instruction that one should not use INDEXED clauses to tune a query. That instruction can only be correct if, and only if, the query optimiser can be relied on to tune the query optimally. In this case, it clearly does not. Thanks to the feedback so far, I have learned how to use EXPLAIN and that I was mistaken to initially report this as a speed regression. Rather it turns out to be that query optimisation has changed after 3.6.17, that there is at least one database structure for which the query optimiser is extremely sub-
Re: [sqlite] simple question about database lookup
On 1/12/2010 5:36 PM, Kees Nuyt wrote: > There is a maximum list size the parser can cope with. > Internally "id IN (val1,val2,...valN)" > is converted to > "WHERE id = val1 OR id = val2 OR id = valN" > > http://www.sqlite.org/limits.html > > I think here > 5. Maximum Depth Of An Expression Tree > applies, which defaults to 1000. > > Experiment to verify ;) > > thank you very much for all the insightful comments. The suggested syntax also worked nicely. Qianqian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q. about core SQLite library
Jay A. Kreibich wrote: > On Tue, Jan 12, 2010 at 02:53:55PM -0500, Ray Gold scratched on the wall: > >> Hello. >> >> My name is Ray Gold with Berliner, Corcoran & Rowe, LLP in Washington, DC. >> >> I am writing to ask if the core SQLite library (not the proprietary >> SQLite Extensions) has any encryption/decryption capabilities. >> > > No. > > However, the SQLite Encryption Extension is maintained by the same > people that maintain the core library. It is "proprietary" only in > the sense that it is commercial, licensed software. > > The core library does provide hooks to the OS layer and the > file-system layer. It is possible to write your own encryption > layer if the extension did not meet your needs. > >-j > > I would avoid using the OS layer. While you could implement your own encryption at the file system level, the license for the encryption extension is relatively inexpensive and maintained for you. As I understand it, once purchased, it is good indefinitely. Further, if it doesn't meet your encryption needs (unlikely) you could extend the encryption extension easier than adding encryption at the file system level. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q. about core SQLite library
On Wed, Jan 13, 2010 at 11:07:50AM -0800, Mark Spiegel scratched on the wall: > > > Jay A. Kreibich wrote: > > On Tue, Jan 12, 2010 at 02:53:55PM -0500, Ray Gold scratched on the wall: > >> > >> My name is Ray Gold with Berliner, Corcoran & Rowe, LLP in Washington, DC. > >> > >> I am writing to ask if the core SQLite library (not the proprietary > >> SQLite Extensions) has any encryption/decryption capabilities. > > > > No. > > > > However, the SQLite Encryption Extension is maintained by the same > > people that maintain the core library. It is "proprietary" only in > > the sense that it is commercial, licensed software. > > > > The core library does provide hooks to the OS layer and the > > file-system layer. It is possible to write your own encryption > > layer if the extension did not meet your needs. > I would avoid using the OS layer. I was under the impression that is how SEE works. Hooking the SQLite Virtual File System interface seems like a very straight forward method. All you really need to do is splice your code between the VFS that the SQLite engine sees and the native VFS layer that comes with the distribution, adding a bit of extra code to xRead() and xWrite() to munge the data. I've never tried it but the documentation makes it look like something you could do without modification to the core source and with very little code other than the actual encrypt/decrypt routines, plus the interfaces required to configure them. No actual file system code, nor any OS specific code. In theory. 8-) http://sqlite.org/c3ref/vfs.html http://sqlite.org/c3ref/io_methods.html > the license for the encryption > extension is relatively inexpensive and maintained for you. As I > understand it, once purchased, it is good indefinitely. Further, if it > doesn't meet your encryption needs (unlikely) you could extend the > encryption extension easier than adding encryption at the file system level. Yes. I would definitely encourage people to look at the supported product before going off and writing their own. The value, cost, and license terms are exceptionally good. It is the right choice for most commercial situations. -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] simple question about database lookup
On Tue, Jan 12, 2010 at 04:12:01PM -0500, Qianqian Fang scratched on the wall: > On 1/12/2010 4:08 PM, Jay A. Kreibich wrote: >> SELECT group_concat( value ) FROM mydata WHERE id IN ( 'id_1', 'id_2', ... ) > > thank you very much for your quick response. I guess this will be > significantly faster than looping through each id. > One more question, will this work if you have a long list of ids (say > a few hundred)? Yes, but it might not be fast, and the SQL gets very messy. If the set of IDs that you need is somewhat static, it might be best to load them into a temp table and then just join your data able to that id table. You might end up inserting them one at a time, but if you use a prepared insert and bind statements, it should be pretty fast. Also, be aware that the order of the values is random. If you use an IN statement, the order of the values has no relation to the order of the IDs. Similar things can be said with the JOIN. -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] Q. about core SQLite library
> I was under the impression that is how SEE works. Hooking the SQLite > Virtual File System interface seems like a very straight forward method. > All you really need to do is splice your code between the VFS that > the SQLite engine sees and the native VFS layer that comes with the > distribution, adding a bit of extra code to xRead() and xWrite() to > munge the data. I've never tried it but the documentation makes it > look like something you could do without modification to the core > source and with very little code other than the actual encrypt/decrypt > routines, plus the interfaces required to configure them. No actual > file system code, nor any OS specific code. > > In theory. 8-) > > http://sqlite.org/c3ref/vfs.html > http://sqlite.org/c3ref/io_methods.html > > > No, it is probably more correct to say that the SEE plugs into the pager. It does not layer the VFS. Look where the CODEC1 and CODEC2 macros are used. Mr. Kreibich should contact DRH and ask about the encryption extension. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q. about core SQLite library
Sorry, Mr. Gold should contact DRH and ask about the encryption extension. My error. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] compiler warning
Hello, I am compiling sqlite-amalgamation-3.6.22 and get this warning: sqlite3.c: In function 'rtreeInit': sqlite3.c:109910: warning: cast from pointer to integer of different size on x86_64 GNU/Linux using the defaults from the configure script. Is this warning an issue to be concerned with since 8 bytes are being truncated to 4 on a 64 bit system? Thanks, Juan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q. about core SQLite library
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jay A. Kreibich wrote: > All you really need to do is splice your code between the VFS that > the SQLite engine sees and the native VFS layer that comes with the > distribution, adding a bit of extra code to xRead() and xWrite() to > munge the data. It is certainly possible to do it this way. It is however a really bad idea to write your own encryption scheme. Anyone can come up with a design for the capabilities they cannot break themselves, but that does not mean that others cannot break it. (Note that I am not talking about the encryption algorithms themselves - I assume no one would be silly enough to try and come up with new ones of those - but rather how one or more encryption algorithms are combined to act on the data.) History is littered with experts (no less) who designed schemes that later turned out to be no good. Some examples are SSL version 1, SSL version 2 and WEP. There really are a lot of things to consider. For example if the scheme doesn't account for timing attacks (ensuring all routines take a constant amount of time no matter if or how much of the key is correct or incorrect) then it becomes rather easy to crack the correct key. Consequently it would be foolish to not use the SEE extension, and pay the small fee for it. You can then work on an improved scheme but need to ensure it is reviewed by multiple security experts and verify the implementation is solid, and at the very least better than SEE. This of course will set you back way more than SEE costs. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktOYDcACgkQmOOfHg372QQ65wCfaeeQj27LcpksjAwhNOkh2Q/U 8BYAoLkDupRYnA/Q16cZPNHseTYT1kxm =iZWS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q. about core SQLite library
On 14 Jan 2010, at 12:07am, Roger Binns wrote: > It is however a really bad idea > to write your own encryption scheme. [snip] > > History is littered with experts (no less) who designed schemes that later > turned out to be no good. Some examples are SSL version 1, SSL version 2 > and WEP. Yeah. Don't spend time writing encryption code unless you have studied encryption. It's one of those subjects which gets more complicated the longer you study it. And spotting 19 of the 20 trivial ways to crack your encryption still gets you a mark of 0%. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sort Alphanumeric, Numeric data in a VARCHAR
Hi, I'm having trouble sorting the following data: point_number - VARCHAR(10) 11.01 11.23 77.01 55.09 A06 1010.01 1010.09 A03 If I do: SELECT point_number FROM mytable ORDER BY point_number; I get the following: 1010.01 1010.09 11.01 11.23 55.09 77.01 A03 A06 When I do the following: SELECT point_number FROM mytable ORDER BY point_number+0; I get the following: A03 A06 11.01 11.23 55.09 77.01 1010.01 1010.09 I would like to have them ordered in the following sequence: 11.01 11.23 55.09 77.01 1010.01 1010.09 A03 A06 How can I get the A03, A06 at the end of the list? TIA gary ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Q. about core SQLite library
On Wed, Jan 13, 2010 at 04:07:19PM -0800, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Jay A. Kreibich wrote: > > All you really need to do is splice your code between the VFS that > > the SQLite engine sees and the native VFS layer that comes with the > > distribution, adding a bit of extra code to xRead() and xWrite() to > > munge the data. > > It is certainly possible to do it this way. It is however a really bad idea > to write your own encryption scheme. Absolutely. I never meant to imply someone should write the actual encryption algorithm, only the glue to attach SQLite's pager to an existing crypto library. Anything else would be utterly foolish. Even then, mistakes with key management and proper defensive coding can frequently allow back-door attacks. For example, since the most obvious choice is a stream cypher that works on a per database-page basis, it would be advisable to use larger pages to avoid constantly re-starting the stream. One might also consider transforming the key based off page position so that you don't have any page headers or structure that could be analyzed as semi-known plain-text. You would also want to alter the new-page allocator to scramble any unused part of the page for the same reasons. > Consequently it would be foolish to not use the SEE extension, and pay the > small fee for it. As I said before, I think SEE is a great choice. It is a fantastic value and a logical first choice for anyone looking to evaluate encrypted databases. That said, it doesn't fit every need. Most notably, it isn't practical to use within an open-source project, commercial or otherwise. SEE may be very reasonably priced and come with a full source license, but it also comes with an NDA and a specific limitation on redistribution of the source code. Plus, if everyone would remember, the original poster specifically asked about alternatives to the SEE extension. I think dismissing SEE is a mistake, but that's their business, not mine. I'd like to assume they know what they're doing and have their reasons for looking elsewhere. I seriously doubt anyone could replicate the functionality of the SEE extension for less than the cost of a license. In fact, I suspect this is the main reason why there isn't a popular open-source alternative-- the real deal is a fantastic value. But there are still situations were SEE is simply not a feasible choice. I would also guess most of those situations are going to involve reasons and decisions that are firmly based in the realm of business, license, and political issues, and not technical issues. I have full confidence in the technical capabilities of SEE. But the world does not run on technical decisions alone. -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] Sort Alphanumeric, Numeric data in a VARCHAR
On Jan 13, 2010, at 8:11 PM, Gary Baranzini wrote: > Hi, > > I'm having trouble sorting the following data: > > point_number - VARCHAR(10) > > 11.01 > 11.23 > 77.01 > 55.09 > A06 > 1010.01 > 1010.09 > A03 > > How can I get the A03, A06 at the end of the list? SELECT * FROM mytable ORDER BY CASE WHEN point_number GLOB '*[^0-9.]*' THEN point_number ELSE cast(point_number AS real) END; D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sort Alphanumeric, Numeric data in a VARCHAR
On Wed, Jan 13, 2010 at 05:11:16PM -0800, Gary Baranzini scratched on the wall: > If I do: SELECT point_number FROM mytable ORDER BY point_number; > I get the following: > > 1010.01 > 1010.09 > 11.01 > 11.23 > 55.09 > 77.01 > A03 > A06 Yes, that's the text sort order. > When I do the following: > SELECT point_number FROM mytable ORDER BY point_number+0; > > I get the following: > > A03 > A06 > 11.01 > 11.23 > 55.09 > 77.01 > 1010.01 > 1010.09 And that's the numeric order. By making an expression, you're attempting to convert the text to a number before the sort. Since 'A..' isn't a valid part of a number, it is translated to 0.0, which makes it sort first in this case. > I would like to have them ordered in the following sequence: > > 11.01 > 11.23 > 55.09 > 77.01 > 1010.01 > 1010.09 > A03 > A06 > > How can I get the A03, A06 at the end of the list? Make the numbers numbers, not text values. The natural sort-order for types is NULL, numeric (INTEGER & REAL mixed), TEXT, BLOB. If the numbers were actual REAL values and not TEXT representations of numbers, you'd get the sort order you describe here-- numbers in their natural order, followed by TEXT values in their own natural order. SQLite lets you mix types within a column, so this isn't a problem. I'm guessing you could also use CAST, CASE, and glob() to do the correct conversions for you... use glob() to look for strings that have characters other than [-+.eE0-9] and use CASE to return the TEXT value as-is or CAST it to a number if it looks like a valid number. My brain hurts too much at the moment to actually figure that out right here and now, however. I'll let Igor do that. Sort orders: http://www.sqlite.org/datatype3.html#comparisons Translation rules: http://www.sqlite.org/c3ref/column_blob.html (see table) -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] Sort Alphanumeric, Numeric data in a VARCHAR
>I'm having trouble sorting the following data: > >point_number - VARCHAR(10) I've developped an SQLite extension including a very similar collation: it sorts the (integral) prefix first and, in case of a draw, orders based on the Unicode suffix. It currently doesn't cope with floating-point prefixes but can surely be adapted easily to do so. It's only a small part of a larger code for facilitating scalar handling of Unicode text, but you can trim it down to only the part you need. If you're interessed in the source, just drop me a mail. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_CANTOPEN Bohrbug
Hello all-
Bear with me, my problem is a little goofy and has stolen six hours of my life.
I'm using the C API of sqlite 3.6.12 on Mac OS X 10.6.2.
I'm using a sqlite database table to collect paths from a filesystem. So, I
have some C code that opens the database at the beginning of an indexing job,
prepares an insert statement, and then for each file: sqlite3_bind_text()'s the
path into the insert statement and sqlite3_step()'s it for every file I'm
indexing, and then sqlite3_reset()'s the statement for the next path to insert.
My code works for hundreds of iterations at a time; if I start iterating
through a folder with a few hundred paths, I generally have no problem.
However, if I start higher up, and have a directory tree with 1000 or more
paths, at some point or other sqlite_step() will return SQLITE_CANTOPEN and any
call I try against my sqlite3 *database after that point will return
SQLITE_MISUSE.
For a given set of paths, sqlite_step() will always fail on the same path, plus
or minus 1. If I start in a completely different part of the filesystem,
sqlite_step() will usually fail eventually, with the same consistency, but
never after the same number of inserts -- for one folder, it fails consistently
after 1598 inserts +/-1, for another folder, it fails at 620 +/-1.
* At first, given the consistency, I thought there might be some sort of
memory/allocation problem, but going over my code carefully and with profiling,
I can find no leaks.
* I breakpoint on sqlite3_step() returning an error, and when I look at the
frame, I see no suspicious nulls or bad argument values. If I use dummy code
that inserts an identical dummy string for every call into sqlite3_step(), it
still eventually fails.
* When I change my sql "create" statement for the table I'm inserting into into
a temp table, the code can index tens of thousands of files and never has an
issue.
* This made me think there was some sort of issue with sqlite's IO with the
disk, but I can't figure out where the problem is. Googling tells me a lot of
people have a problem when they are trying to write a db file to a directory
their code doesn't have readwrite on, but I've verified that my database
directory (for purposes of testing) is chmod 777.
So, has anyone else run into SQLITE_CANTOPEN in a situation where it wasn't a
permissions issue?
I'll include the code if you'd really like to see it but it's essentially like
this: (eliding all error checking, etc)
/* EXAMPLE.c */
char *nextPath(); /* returns next path to index */
sqlite3 *_database;
int retVal;
sqlite3_open_v2(PATH_TO_DB, &_database, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL);
sqlite3_stmt *statement;
const char *szStmt = "replace into AudioFiles (file_url) values (?)";
retVal = sqlite3_prepare_v2(_database, szStmt, strlen(szStmt), &statement,
NULL);
char *path;
while ( path = nextPath() ) {
retVal = sqlite3_bind_text(statement, 1, path , strlen(path) , SQLITE_STATIC);
/* I've tried every variation for argument 5 here */
retVal = sqlite3_step(statement);
retVal = sqlite3_reset(statement);
}
sqlite3_finalize(_upsertStatement);
sqlite3_close(_database);
/* end EXAMPLE.c */
--
Jamie Hardt
http://www.imdb.com/name/nm0362504/
http://www.soundepartment.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
