Re: [sqlite] sql statement to update the data in the table
On 20 Oct 2011, at 1:49am, Joanne Pham wrote: > it seems like it didn't work. > For example the password is 'password'. I ran the update statement below and > do the AES_DECRYPT the password is null instead of 'password'. Try just SELECT AES_ENCRYPT(password, 'abcddsfddafdasfddasd'); Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql statement to update the data in the table
On 10/19/2011 8:49 PM, Joanne Pham wrote: Yes, That is what i want but it seems like it didn't work. For example the password is 'password'. I ran the update statement below and do the AES_DECRYPT the password is null instead of 'password'. Any idea? You'll have to raise the issue with the author of custom functions AES_ENCRYPT and AES_DECRYPT. They are not part of SQLite proper, you must be using some kind of third party extension library. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql statement to update the data in the table
Thanks, Yes, That is what i want but it seems like it didn't work. For example the password is 'password'. I ran the update statement below and do the AES_DECRYPT the password is null instead of 'password'. Any idea? JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Wednesday, October 19, 2011 5:35 PM Subject: Re: [sqlite] sql statement to update the data in the table On 10/19/2011 7:23 PM, Joanne Pham wrote: > update vpn set password = AES_ENCRYPT((select password from vpn) , > "abcddsfddafdasfddasd"). I suspect you want update vpn set password = AES_ENCRYPT(password, 'abcddsfddafdasfddasd'); -- Igor Tandetnik ___ 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] sql statement to update the data in the table
On 10/19/2011 7:23 PM, Joanne Pham wrote: update vpn set password = AES_ENCRYPT((select password from vpn) , "abcddsfddafdasfddasd"). I suspect you want update vpn set password = AES_ENCRYPT(password, 'abcddsfddafdasfddasd'); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql statement to update the data in the table
Hi Igor, update vpn set password = AES_ENCRYPT((select password from vpn) , "abcddsfddafdasfddasd"). Basically, I want to encrypt the password in vpn table so the passwords in this table are different. Above mysql statement still didn't work. Any idea. Thanks, JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Wednesday, October 19, 2011 3:58 PM Subject: Re: [sqlite] sql statement to update the data in the table On 10/19/2011 6:34 PM, Joanne Pham wrote: > Curently I had the table with the plain text and I want to encrypt these > passwords by using the following sql statement but I got the error mesages.. > Any suggestion? > update vpn set password = AES_ENCRYPT(select password from mytable, > "abcddsfddafdasfddasd"). Do you want vpn.password set to the same value in all rows? I would have expected a WHERE clause on the select statement that somehow correlates mytable with vpn. Anyway, the immediate cause of the syntax errors is the fact that a subselect needs to be enclosed in parentheses: update vpn set password = AES_ENCRYPT((select password from mytable), "abcddsfddafdasfddasd"); -- Igor Tandetnik ___ 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] sql statement to update the data in the table
On 10/19/2011 6:34 PM, Joanne Pham wrote: Curently I had the table with the plain text and I want to encrypt these passwords by using the following sql statement but I got the error mesages.. Any suggestion? update vpn set password = AES_ENCRYPT(select password from mytable, "abcddsfddafdasfddasd"). Do you want vpn.password set to the same value in all rows? I would have expected a WHERE clause on the select statement that somehow correlates mytable with vpn. Anyway, the immediate cause of the syntax errors is the fact that a subselect needs to be enclosed in parentheses: update vpn set password = AES_ENCRYPT((select password from mytable), "abcddsfddafdasfddasd"); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On 19 Oct 2011, at 11:20pm, Nico Williams wrote: > On Wed, Oct 19, 2011 at 4:16 PM, Jay A. Kreibich wrote: >> Just because the standards include such features, you seem to be >> under the impression that every client and every server running on >> top of any filesystem actually implement said features correctly, >> and that every combination of client and server interacts and >> operates correctly. > > I assume that? I'm aware that the Linux NFS client did not support > distributed byte range locking prior to kernerl 2.6.12. That doesn't > mean that SQLite3 couldn't have an option to do locking in a way that > would work with NFS -- it just means that it'd be unsafe to use > SQLite3 on NFS on older Linux distros with any concurrency, that the > application would need additional locking (like, e.g., Firefox does or > used to). I started off with that impression but it turns out that pretty much all implementations of everything have bugs in the locking code. Forget 'older Linux distros', we're talking distros yet to be issued. If you actually demand proper implementation of range-locking you're out of luck. Fixing the bugs slows them down to the point everyone would complain about how stuff has slowed down. I spent some time on this before concluding that it would be simpler to write a server/client overlay for SQLite, and that would yield faster results than getting all the locking code working properly for any of the big well-known network file systems. People who know what I work with know I'm a huge Apple fan and when I give up on getting AFS working right you know there's something seriously wrong with it. A server/client architecture has the additional advantage of platform interoperability, and that you can use PRAGMAs to turn off some of the features of SQLite that slow things down. You just turn file sharing off and assume that your server has sole access to the file. >> As for modifying SQLite, the locking mechanism is actually somewhat >> modular, and I believe SQLite already includes the ability to some >> types of range locks. That still doesn't do you any good if the >> filesystem doesn't answer the APIs correctly. > > True, but one would have to make sure that older releases of SQLite3 > don't open a DB that uses a lock protocol that they don't support. > That can be done by, e.g., having a different magic, but I think we'd > all want the new thing to NOT be the default. Putting SQLite aside I wrote a program which generated a list of commands to test the network APIs: random ranges to lock, things to write to the file, ranges to unlock, things to read from the file. Couldn't get it to work on modern implementations of anything: SMB, NFS, AFP. Always found situations where range locks were ignored by another client, or unlocks apparently got executed without having any effect. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sql statement to update the data in the table
Hi all, Curently I had the table with the plain text and I want to encrypt these passwords by using the following sql statement but I got the error mesages. Any suggestion? update vpn set password = AES_ENCRYPT(select password from mytable, "abcddsfddafdasfddasd"). Thanks in advance, JP ___ 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] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 4:16 PM, Jay A. Kreibich wrote: > On Wed, Oct 19, 2011 at 03:24:35PM -0500, Nico Williams scratched on the wall: >> Also, regarding NFS, it would be safe to use if SQLite3 were to use >> whole-file byte range locks. NFS makes concurrent access to byte >> ranges that are not locked unsafe. Of course, safely making changes >> to how SQLite3 locks files may be difficult now... >> >> CIFS does support byte range locking, and I suspect that the same rule >> I mentioned above regarding NFS applies. Plus, as a last resort >> there's CIFS open deny modes that SQLite3 could use to prevent >> concurrency. > > Just because the standards include such features, you seem to be > under the impression that every client and every server running on > top of any filesystem actually implement said features correctly, > and that every combination of client and server interacts and > operates correctly. I assume that? I'm aware that the Linux NFS client did not support distributed byte range locking prior to kernerl 2.6.12. That doesn't mean that SQLite3 couldn't have an option to do locking in a way that would work with NFS -- it just means that it'd be unsafe to use SQLite3 on NFS on older Linux distros with any concurrency, that the application would need additional locking (like, e.g., Firefox does or used to). > As for modifying SQLite, the locking mechanism is actually somewhat > modular, and I believe SQLite already includes the ability to some > types of range locks. That still doesn't do you any good if the > filesystem doesn't answer the APIs correctly. True, but one would have to make sure that older releases of SQLite3 don't open a DB that uses a lock protocol that they don't support. That can be done by, e.g., having a different magic, but I think we'd all want the new thing to NOT be the default. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/10/11 13:24, Nico Williams wrote: > That explains why concurrent access over NFS or CIFS is dangerous. It > doesn't explain that it's not possible to use SQLite3 over NFS or > CIFS. The simple answer is that they don't provide *exactly* the same behaviour and semantics when using the same system calls accessing files remotely versus locally. This is typically due to bugs in their implementations in code paths that aren't often used. > Also, regarding NFS, it would be safe to use if SQLite3 were to use > whole-file byte range locks. NFS has other issues. For example NFS lock daemons had a tendency to crash when dealing with offsets in the 2GB range. Samba has code to translate locks from the crash sensitive range to elsewhere. > Of course, safely making changes to how SQLite3 locks files may be > difficult now... It is impossible since a different version of SQLite could also be accessing the file on the client or the server. > CIFS does support byte range locking, and I suspect that the same rule > I mentioned above regarding NFS applies. CIFS locking is worlds apart from NFS locking with many differences. One of the hardest parts of writing a CIFS server for Unix is mapping into the limited locking on Unix (which is substantially similar to NFS locking). > Plus, as a last resort there's CIFS open deny modes that SQLite3 could > use to prevent concurrency. The UNIX VFS has something similar already implemented and could be ported to the Windows VFS. See section 2: http://www.sqlite.org/vfs.html > In response to other comments regarding other filesystems, I'll point > out that on Lustre, because Lustre is fully POSIX-compliant regarding > write visibility, SQLite3 should work fine as-is. The problem isn't necessarily that the filesystems work as documented, but rather that the sequence of operations that SQLite does is somewhat unusual compared to normal programs and hence hits various obscure bugs as has been repeatedly found by user experience. There is both client and server side code where those bugs could lie. It is virtually impossible to prove the absence of bugs, so if you want to risk your data then go ahead, but expect an "I told you so" if you end up with corruption. And since you are already using networking there are numerous database products that already work that way and won't have the issues. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6fS+gACgkQmOOfHg372QQENACeJrNUpnFgSIvLlgNrRICOF6Oc ajgAnRoBSwRk0uKQ/0D3i+zAmU2sEqnZ =yIct -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database full error?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/10/11 14:03, PA Newsgroups wrote: > Is there anything else that could cause this error? It's on a Windows > computer if that makes any difference. SQLite code has a tendency to report virtually any error involving write calls as the disk being full. Also be aware that the write may not be to the main database but could be elsewhere (eg temporary space while sorting, journals etc). The easiest way to find out exactly what happened is to use the Process Monitor tool from Sysinternals (now owned by Microsoft). Then look to see what error happened with which file. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6fQpoACgkQmOOfHg372QT70wCfQ3jK0F73O5PGg5dPaL+moKl1 4aUAn1zAWHWVqOciS4VAoYnhlUvKuswd =2/Ya -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 03:24:35PM -0500, Nico Williams scratched on the wall: > On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich wrote: > > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the > > wall: > >> On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal > >> wrote: > >> > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > >> > wrote: > >> >> What can I do to make SQLite run safely on CIFS? > >> > > >> > Nothing. Even MS Access cannot (or could not way back when i used it) be > >> > safely used on SMB/CIFS storage. > >> > >> Can you elaborate as to why? > > > > ??http://sqlite.org/faq.html#q5 > > > > > > ??In short: buggy filesystem code that doesn't support distributed > > ??locks correctly. > > That explains why concurrent access over NFS or CIFS is dangerous. For most applications using a database, if there is a moderately high possible loss of data (i.e. "dangerous"), that's the same thing as "not possible." "Not a very good idea," at minimum. > It doesn't explain that it's not possible to use SQLite3 over NFS or > CIFS. As with concurrent access, it is *possible*, just dangerous. Which is "not possible" for most applications. Even if concurrency isn't an issue in your specific case, locks and synchronization tend to go hand in hand. Correct SQLite transactions depend on the ability to verify data has been physically written to storage. Most network file systems don't do this correctly. Heck... a lot of physical hard drives don't do this correctly. See: http://sqlite.org/lockingv3.html#how_to_corrupt http://sqlite.org/atomiccommit.html#sect_9_0 > Also, regarding NFS, it would be safe to use if SQLite3 were to use > whole-file byte range locks. NFS makes concurrent access to byte > ranges that are not locked unsafe. Of course, safely making changes > to how SQLite3 locks files may be difficult now... > > CIFS does support byte range locking, and I suspect that the same rule > I mentioned above regarding NFS applies. Plus, as a last resort > there's CIFS open deny modes that SQLite3 could use to prevent > concurrency. Just because the standards include such features, you seem to be under the impression that every client and every server running on top of any filesystem actually implement said features correctly, and that every combination of client and server interacts and operates correctly. Needless to say, it just ain't so. Not by a long-shot. As for modifying SQLite, the locking mechanism is actually somewhat modular, and I believe SQLite already includes the ability to some types of range locks. That still doesn't do you any good if the filesystem doesn't answer the APIs correctly. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database full error?
I have a customer with a database whose file size is about 39GB. He's getting error 13 (database or disk is full) on an insert. My app sets the page size to 4096, but even if it was the default, this error shouldn't happen until it's around 4TB in size based on what I read the default SQLITE_MAX_PAGE_COUNT value is. There is plenty of free disk space on the drive, and other databases are being successfully written to in that same folder, so I don't think it's a rights issue. Is there anything else that could cause this error? It's on a Windows computer if that makes any difference. Doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
2011/10/19 Fabian :
> I always do inserts in batches of 100.000 rows, and after each batch I
> manually merge the b-trees using:
>
> INSERT INTO table(table) VALUES('optimize');
>
> Is there a possibility that it will do automatic maintenance half-way during
> a batch? Or will it always wait untill the transaction is finished?
I think you are victim of the premature optimization :)
See documentation:
"several different b-trees that are incrementally merged as rows are inserted,
updated and deleted. This technique improves performance when writing to an
FTS table, but causes some overhead for full-text queries that use the index."
So you can work with a big FTS tables without using the "optimize" method.
I use some FTS tables with tens of millions records and effect of the
"optimize"
isn't measurable.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
On Wed, Oct 19, 2011 at 12:50 PM, Fabian wrote:
> 2011/10/19 Scott Hess
>> To be clear, how it works is that new insertions are batched into a
>> new index tree, with index trees periodically aggregated to keep
>> selection efficient and to keep the size contained. So while the
>> speed per insert should remain pretty stable constant, periodically an
>> insert will require index maintenance, so that insert will be slower.
>> If you have a lot of documents (or a small page cache) these
>> maintenance events can get pretty expensive relative to the cost of a
>> non-maintenance insert. So it's not a clear-cut win, but it probably
>> would be interesting as an alternative sort of index for some tables.
>
> I always do inserts in batches of 100.000 rows, and after each batch I
> manually merge the b-trees using:
>
> INSERT INTO table(table) VALUES('optimize');
>
> Is there a possibility that it will do automatic maintenance half-way during
> a batch? Or will it always wait untill the transaction is finished?
It does it when it does it, in fact you're probably getting some small
merges during this process already. If you're doing your batch
inserts within a surrounding transaction, and are inserting documents
by ascending docid (or letting the system choose docid), it can buffer
up many updates in memory before flushing them to disk, which is
pretty efficient. Inserting 100,000 documents this way will probably
not hit any very large merges, unless your documents tend to have a
very large number of unique terms.
-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich wrote: > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the wall: >> On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: >> > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren >> > wrote: >> >> What can I do to make SQLite run safely on CIFS? >> > >> > Nothing. Even MS Access cannot (or could not way back when i used it) be >> > safely used on SMB/CIFS storage. >> >> Can you elaborate as to why? > > http://sqlite.org/faq.html#q5 > > > In short: buggy filesystem code that doesn't support distributed > locks correctly. That explains why concurrent access over NFS or CIFS is dangerous. It doesn't explain that it's not possible to use SQLite3 over NFS or CIFS. Also, regarding NFS, it would be safe to use if SQLite3 were to use whole-file byte range locks. NFS makes concurrent access to byte ranges that are not locked unsafe. Of course, safely making changes to how SQLite3 locks files may be difficult now... CIFS does support byte range locking, and I suspect that the same rule I mentioned above regarding NFS applies. Plus, as a last resort there's CIFS open deny modes that SQLite3 could use to prevent concurrency. In response to other comments regarding other filesystems, I'll point out that on Lustre, because Lustre is fully POSIX-compliant regarding write visibility, SQLite3 should work fine as-is. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS: Reduce tokens
Using the default tokenizer, everything that is not an alphanumeric character or an underscore, will generate a new token. I have a lot of columns that contains e-mail addresses or URL's, and most of them have characters like '.', '@' and '/'. Is there a simple way to make FTS see them as one single token, instead of splitting those strings into many small ones? I know it's possible to develop a custom tokenizer, but that's way over my head I'm afraid :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
2011/10/19 Scott Hess
>
> To be clear, how it works is that new insertions are batched into a
> new index tree, with index trees periodically aggregated to keep
> selection efficient and to keep the size contained. So while the
> speed per insert should remain pretty stable constant, periodically an
> insert will require index maintenance, so that insert will be slower.
> If you have a lot of documents (or a small page cache) these
> maintenance events can get pretty expensive relative to the cost of a
> non-maintenance insert. So it's not a clear-cut win, but it probably
> would be interesting as an alternative sort of index for some tables.
>
>
I always do inserts in batches of 100.000 rows, and after each batch I
manually merge the b-trees using:
INSERT INTO table(table) VALUES('optimize');
Is there a possibility that it will do automatic maintenance half-way during
a batch? Or will it always wait untill the transaction is finished?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 9:40 PM, Simon Slavin wrote: > Think is, it's possible to do it right. It's just that doing it right > means every operation takes two or three times as long as the slightly buggy > implementations we have now. And people prefer fast-but-buggy. > A slight elaboration: Simone does not mean to imply that locking over networked filesystems is necessarily fast per se, but that "doing it right" would be slower than "fast but buggy." i once had a relatively simple app whose runtime increased 100x+ over NFS when locking was enabled, even though it only made a few lock/unlock calls. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On 19 Oct 2011, at 8:26pm, Stephan Beal wrote: > On Wed, Oct 19, 2011 at 9:23 PM, Stephan Beal wrote: > >> race conditions, unclean network connection errors, blah blah blah. That >> goes for all applications, not just databases. > > And not just for CIFS, but NFS and other networked filesystems as well. i'm > not personally aware of any (except maybe specialized clustered > environments) where file locking is "reliably well-behaved," but if there's > one out there someone else on this list certainly knows about it (and will > hopefully correct me). There are bugs in the AFS implementation too. Slightly different ones in different versions. Think is, it's possible to do it right. It's just that doing it right means every operation takes two or three times as long as the slightly buggy implementations we have now. And people prefer fast-but-buggy. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 9:23 PM, Stephan Beal wrote: > race conditions, unclean network connection errors, blah blah blah. That > goes for all applications, not just databases. > > And not just for CIFS, but NFS and other networked filesystems as well. i'm not personally aware of any (except maybe specialized clustered environments) where file locking is "reliably well-behaved," but if there's one out there someone else on this list certainly knows about it (and will hopefully correct me). -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 9:13 PM, Nico Williams wrote: > > Nothing. Even MS Access cannot (or could not way back when i used it) be > > safely used on SMB/CIFS storage. > > Can you elaborate as to why? > > i unfortunately can't, except to say that file locking on networked filesystems has, historically speaking, always been problematic. Communicating the locks between separate machines, race conditions, unclean network connection errors, blah blah blah. That goes for all applications, not just databases. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the wall: > On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: > > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > > wrote: > >> What can I do to make SQLite run safely on CIFS? > > > > Nothing. Even MS Access cannot (or could not way back when i used it) be > > safely used on SMB/CIFS storage. > > Can you elaborate as to why? http://sqlite.org/faq.html#q5 In short: buggy filesystem code that doesn't support distributed locks correctly. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > wrote: >> What can I do to make SQLite run safely on CIFS? > > Nothing. Even MS Access cannot (or could not way back when i used it) be > safely used on SMB/CIFS storage. Can you elaborate as to why? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
On Wed, Oct 19, 2011 at 7:56 AM, Fabian wrote: > 2011/10/19 Alexey Pechnikov >> FTS use index multi-tree and de-facto has _no_ insert speed degradation. > > Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that > same multi-tree mechanism for regular indexes, but that's a whole different > question. To be clear, how it works is that new insertions are batched into a new index tree, with index trees periodically aggregated to keep selection efficient and to keep the size contained. So while the speed per insert should remain pretty stable constant, periodically an insert will require index maintenance, so that insert will be slower. If you have a lot of documents (or a small page cache) these maintenance events can get pretty expensive relative to the cost of a non-maintenance insert. So it's not a clear-cut win, but it probably would be interesting as an alternative sort of index for some tables. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
Nope -- didn't note the insert speed on that test. Why don't you take my benchmark data and test it yourself? Then post the results. The saying "your mileage may vary" comes to mind... Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Fabian [fabianpi...@gmail.com] Sent: Wednesday, October 19, 2011 9:44 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] FTS vs INDEX Very interesting benchmarks! However it seems to focus mainly on the speed of SELECT queries, and the total size of the resulting database on disk. But my main concern is about the speed of INSERT queries vs normal tables. Any chance you compared that too? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
2011/10/19 Fabian : > Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that > same multi-tree mechanism for regular indexes, but that's a whole different > question. It's impossible with SQLite3 database format. May be SQLite4 will be support it :) -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
2011/10/19 Alexey Pechnikov > FTS use index multi-tree and de-facto has _no_ insert speed degradation. > Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that same multi-tree mechanism for regular indexes, but that's a whole different question. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
FTS use index multi-tree and de-facto has _no_ insert speed degradation. I did do test for 400+ millions of records. With b-tree index there is insert speed degradation: http://geomapx.blogspot.com/2010/04/sqlite-index-degradation-tests.html http://geomapx.blogspot.com/search?q=index+speed So FTS as hash-index is nice. 2011/10/19 Fabian : > Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT > INDEX column? I don't need many of the extra features of FTS, because I > always need to look up rows by prefix or exact match, and both can be > implemented efficiently via TEXT INDEX too. But if the overhead is > comparable, I'd rather use FTS. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
Very interesting benchmarks! However it seems to focus mainly on the speed of SELECT queries, and the total size of the resulting database on disk. But my main concern is about the speed of INSERT queries vs normal tables. Any chance you compared that too? 2011/10/19 Black, Michael (IS) > I recently benchmarked this...FTS4 has a prefix option that can make it > slightly faster than TEXT. Other than that it's about the same speed. > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg64591.html > > > > The older part of the thread has the benchmark data > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Fabian [fabianpi...@gmail.com] > Sent: Wednesday, October 19, 2011 9:20 AM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] FTS vs INDEX > > Did anyone do some benchmarks how the insert-speed of FTS compares to a > TEXT > INDEX column? I don't need many of the extra features of FTS, because I > always need to look up rows by prefix or exact match, and both can be > implemented efficiently via TEXT INDEX too. But if the overhead is > comparable, I'd rather use FTS. > ___ > 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] FTS vs INDEX
I recently benchmarked this...FTS4 has a prefix option that can make it slightly faster than TEXT. Other than that it's about the same speed. http://www.mail-archive.com/sqlite-users@sqlite.org/msg64591.html The older part of the thread has the benchmark data Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Fabian [fabianpi...@gmail.com] Sent: Wednesday, October 19, 2011 9:20 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] FTS vs INDEX Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT INDEX column? I don't need many of the extra features of FTS, because I always need to look up rows by prefix or exact match, and both can be implemented efficiently via TEXT INDEX too. But if the overhead is comparable, I'd rather use FTS. ___ 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] FTS vs INDEX
Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT INDEX column? I don't need many of the extra features of FTS, because I always need to look up rows by prefix or exact match, and both can be implemented efficiently via TEXT INDEX too. But if the overhead is comparable, I'd rather use FTS. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems with nested Joins
Hello I'm currently evaluating SQLite for replacing a Microsoft Access (Jet) Database in one of our projects. We use a data layer abstraction which allows us to quickly implement other database. Therefore we allow the user of the data acces layer to make arbitrary nested joins. Currently I have a problem when using a nested join as a right hand side of another join (doesn't depent if it's an inner join or left outer join). I use the following test database. CREATE TABLE TableA ( IDinteger PRIMARY KEY NOT NULL, ForeignB integer NOT NULL DEFAULT -1 ); CREATE TABLE TableB ( IDinteger PRIMARY KEY NOT NULL, ForeignC integer NOT NULL DEFAULT -1 ); CREATE TABLE TableC ( IDinteger PRIMARY KEY NOT NULL, Value integer NOT NULL DEFAULT -1 ); INSERT INTO TableA(ForeignB) Values(1); INSERT INTO TableA(ForeignB) Values(-1); INSERT INTO TableB(ForeignC) Values(1); INSERT INTO TableC(Value) Values(333); Basically TableA has an optional foreign key to TableB and TableB has a foreign key to TableC. Now we would like to execute the following SQL. SELECT * FROM TableA LEFT OUTER JOIN ( TableB INNER JOIN TableC ON TableB.ForeignC=TableC.ID ) ON TableA.ForeignB=TableB.ID; Executing the statement above yields to a 'SQL Error: no such column: TableB.ID'. Although accordingly to http://sqlite.org/lang_select.html this should be a valid statement. Executing the same statement on other databases e.g. MsAccess/SQL Server works as expected. Same applies if one replaces the LEFT OUTER JOIN with an INNER JOIN. SELECT * FROM TableA INNER JOIN ( TableB INNER JOIN TableC ON TableB.ForeignC=TableC.ID ) ON TableA.ForeignB=TableB.ID; Executing the statement above yields to a 'SQL Error: no such column: TableB.ID'. I know one could say, the Join (TableA -> (Nested)) doesn't know anything about TableB. But let's try to change the order of the join. SELECT * FROM ( TableB INNER JOIN TableC ON TableB.ForeignC=TableC.ID ) INNER JOIN TableA ON TableA.ForeignB=TableB.ID; Executing this statement now works as expected, without any error. But here one could also say the join ( (Nested) -> TableA)) doesn't know anything about TableB but still it works. Also the documentation of SQLite states the optimizer could rearrange the joins, with this inner join example then it shouldn't make any difference if the nested join is used as left or right hand side of the join. I know one could rewrite both inner join examples without any brackets, leading to the same results of the queries. But for the first example, the left outer join one, it's not possible. Another really interesting query is the following. SELECT * FROM TableA INNER JOIN ( TableB INNER JOIN TableC ON TableB.ForeignC=TableC.ID ) As T ON TableA.ForeignB=T.ID; This one works, but which column will be used as T.ID, because both TableB and TableC have a column ID. Also no ambiguous column error is raised. Thanks for any help regarding this problem. Kind Regards Fabrizio ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in using overloaded functions for virtual tables?
Stepping through execution for queries with and without GROUP BY clauses, I could see that, when preparing a query containing a Group By clause, the parser assigns a TK_AGG_COLUMN type to first argument of the offsets() and snippet() functions, thus breaking out of sqlite3VtabOverloadFunction() that is called from sqlite3ExprCodeTarget() with a TK_FUNCTION op code, finally not being able to look up the overloaded version of the functions, executing the default, sqlite3InvalidFunction implementation. Doing some tests and including the TK_AGG_COLUMN in the conditions that should continue the overload mechanism, the correct implementations are found and assigned, but execution crashes later on, when stepping the resultset - looks like we then have an invalid fulltext cursor. -- View this message in context: http://old.nabble.com/Bug-in-using-overloaded-functions-for-virtual-tables--tp32364864p32681827.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] SQLite
On 7 Oct 2011, at 4:10pm, Arbol One wrote: > Looking at the choices given at http://www.sqlite.org/download.html, I would > like to know what would be the best way to add SQLite to my app. > > I am using Windows 7-64bit as the OS and Code::Blocks with MinGW/GNU C++. Download the amalgamation sourcecode. Include the .c and .h in your project. Use the C API as documented here: http://www.sqlite.org/c3ref/intro.html With regard to your later query: The most probable reason this didn't get answered before is that we were all hoping someone would turn up who was familiar with whatever Code::Blocks is. Unfortunately, nobody did. So my answer above is extremely general advice to most people who start using SQLite. If an expert turns up, believe them, not me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is my posting?
In-fact there is at-least one more post from you (other than this one), regarding the best way to add sqlite to your app (Sorry I am not sure of the answer). I think that confirms that your mails are being received.. Regards Taleeb Anawr *Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!* On Wed, Oct 19, 2011 at 4:59 PM, Taleeb Anwar wrote: > Now that is a difficult question (in fact one of the most difficult > questions of all) -- why nobody replies to your posts. > > Well, I don't know the answer - but am replying - to confirm that your > posts are being received by others. And for the questions you didn't get any > answer -- try rephrasing them or adding some code or something to make them > more clear (for I can't think of any other reason for such an active group > to remain unresponsive to your questions) > > Regards > Taleeb Anwar > > *Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!* > > > > > On Wed, Oct 19, 2011 at 4:48 PM, Arbol One wrote: > >> Hey boys and girls! >> >> Anyone knows why is it that I cannot see my own postings, worst yet! >> Nobody >> replies to my postings?! >> >> How can I know if my email actually is being considered by other members >> of >> the list or if it was never posted? >> >> >> >> TIA >> >> ___ >> 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] Where is my posting?
OH! nice! thanks Taleeb, now I know it is working. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Taleeb Anwar Sent: Wednesday, October 19, 2011 7:30 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Where is my posting? Now that is a difficult question (in fact one of the most difficult questions of all) -- why nobody replies to your posts. Well, I don't know the answer - but am replying - to confirm that your posts are being received by others. And for the questions you didn't get any answer -- try rephrasing them or adding some code or something to make them more clear (for I can't think of any other reason for such an active group to remain unresponsive to your questions) Regards Taleeb Anwar *Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!* On Wed, Oct 19, 2011 at 4:48 PM, Arbol One wrote: > Hey boys and girls! > > Anyone knows why is it that I cannot see my own postings, worst yet! Nobody > replies to my postings?! > > How can I know if my email actually is being considered by other members of > the list or if it was never posted? > > > > TIA > > ___ > 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] Where is my posting?
Now that is a difficult question (in fact one of the most difficult questions of all) -- why nobody replies to your posts. Well, I don't know the answer - but am replying - to confirm that your posts are being received by others. And for the questions you didn't get any answer -- try rephrasing them or adding some code or something to make them more clear (for I can't think of any other reason for such an active group to remain unresponsive to your questions) Regards Taleeb Anwar *Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!* On Wed, Oct 19, 2011 at 4:48 PM, Arbol One wrote: > Hey boys and girls! > > Anyone knows why is it that I cannot see my own postings, worst yet! Nobody > replies to my postings?! > > How can I know if my email actually is being considered by other members of > the list or if it was never posted? > > > > TIA > > ___ > 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] Where is my posting?
Hello AO> How can I know if my email actually is being considered by other AO> members of the list or if it was never posted? Somehow this one made it through. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where is my posting?
Hey boys and girls! Anyone knows why is it that I cannot see my own postings, worst yet! Nobody replies to my postings?! How can I know if my email actually is being considered by other members of the list or if it was never posted? TIA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble about ordering records by name (or any varchar column)
In my SQLite 3 Database, I have some records with Turkish characters such as "Ã", "Ã", "Ä°" etc. When I select my values with SELECT * FROM TABLE ORDER BY COLUMN_NAME query, the records that begin with these characters are coming at the end. Bare bone SQLite only collates correctly on the lower ASCII charset. While that's fine for plain english, it doesn't work for most of us. Normally, they should've come after the letter that is dot-less version of each. Like "Ã" is after "O", "Ã" is after "U". Is it something about regional settings? Is there a way to control these settings? You have the choice among some ways to get it right or close to right for your language(s): o) use ICU either as an extension (for third-party managers) or linked to your application. Advantages: it works 100% correctly for a given language at a time in each operation. Drawbacks: it's huge and slow and it requires you register a collation for every language you deal with. Also it won't work well for columns containing several non-english languages. o) write your own collation(s) invoking your OS' ICU routines to collate strings. Advantages: doesn't bloat your code with huge libraries. Drawbacks: requires you write this extension (in C or something), same other drawbacks as ICU. o) If you use Windows, download and use the functions in the extension I wrote for a close-to-correct result. Advantages: it's small, fairly fast and ready to use, it is language- independant yet works decently well for many languages at the same time; it also offers a number of Unicode-aware string manipulation functions (unaccenting or not) functions, a fuzzy search function and much more. Comes as a C source and x86 DLL, free for any purpose. Drawback: it probably doesn't work 100% correctly for any language using more than "vanilla english letters": your dotless i will collate along dotted i, for instance. It's a good compromise between absolute correctness for ONE language and "fair" correctness for most languages (including some asian languages using diacritics) Download: http://dl.dropbox.com/u/26433628/unifuzz.zip I use SQLite Manager in Firefox to manage my DB. My little extension will work with this one. You might also want to try SQLite Expert which has ICU built-in (at least in its Pro version) and _much_ more. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren wrote: > What can I do to make SQLite run safely on CIFS? > Nothing. Even MS Access cannot (or could not way back when i used it) be safely used on SMB/CIFS storage. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
2011/10/18 Sune Ahlgren : > What can I do to make SQLite run safely on CIFS? > /Sune Do not use SQLite on shared device. Use client/server database or client/server front-end of SQLite. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trouble about ordering records by name (or any varchar column)
Hi all, I'm having some trouble about ordering records by name (or any varchar column). I first asked for help at StackOverFlow(I wasn't aware of this list) then had to dig out more. In my SQLite 3 Database, I have some records with Turkish characters such as "Ö", "Ü", "İ" etc. When I select my values with SELECT * FROM TABLE ORDER BY COLUMN_NAME query, the records that begin with these characters are coming at the end. Normally, they should've come after the letter that is dot-less version of each. Like "Ö" is after "O", "Ü" is after "U". Is it something about regional settings? Is there a way to control these settings? I use SQLite Manager in Firefox to manage my DB. Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
