[sqlite] Mixing journal modes from different threads
Hello dear sqlite users, is it ok to mix journal modes from different threads accessing the same database? The typical scenario is that I use one thread for reading using normal (default) journalling (e.g. only SELECT queries are performed) and two to four threads for writing using WAL journalling. I'm using it like that because most of the times I'm using SELECT queries only and I don't want journal files to be created (as it's the case with WAL). Is this the right way of doing it? Thank you, George. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing journal modes from different threads
Good evening Dr. Hipp, ok, got it. Thank you for the quick clarification. I only asked on the mailing list because I couldn't find this kind of information in the docs (or I didn't look thoroughly enough); if it's the first case, perhaps it should be mentioned somewhere... Thanks again, George. On Thu, Aug 21, 2014 at 10:07 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Aug 21, 2014 at 3:05 PM, George Ionescu geoione...@gmail.com wrote: Hello dear sqlite users, is it ok to mix journal modes from different threads accessing the same database? SQLite does not allow you to mix WAL mode with other journal modes. Wal-mode is a property of the database file. If you change to WAL-mode, then *all* database connections must go to WAL-mode. -- D. Richard Hipp d...@sqlite.org ___ 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] Diacritics handling in FTS with a custom tokenizer
Hello Dan, thank you for the response, that did it. Unfortunately, this 'breaks' the fts_table_terms functionality in the sense that real terms (e.g. with diacritics) cannot be retrieved using that table, since they're stored without. I wanted to use that table for autocomplete feature in a text input. But I can live with that, as long as searching works with *and* without diacritics. Thanks again, George. On Wed, Feb 8, 2012 at 8:30 PM, Dan Kennedy danielk1...@gmail.com wrote: On 02/09/2012 12:49 AM, George Ionescu wrote: Hello Dan, yes, I thought of that. But wouldn't this break the snippet's function? If the tokenizer will return text without diacritics, wouldn't the snippet return the same? Should be Ok. Snippet should be based on the original content. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] Diacritics handling in FTS with a custom tokenizer
Hello all, I would like to know how are diacritics handled in FTS, specifically if I can index text with diacritics and search for terms without them. For example, given the queries CREATE VIRTUAL TABLE fts_pages USING fts4(tokenize=snowball ro_RO); INSERT INTO fts_pages (docid,content) VALUES (1, 'România este o ţară frumoasă'); the search SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'este' returns 1, but the next search SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'Romania' returns 0. The tokenizer I'm using is based on snowball and can be found at https://bitbucket.org/sevkin/snowball_fts3 Thank you, George. PS: Other FTS engines (e.g. DTSearch/Sphinx) handle this: you can index text with diacritics and search with or without them. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics handling in FTS with a custom tokenizer
Hello Dan, yes, I thought of that. But wouldn't this break the snippet's function? If the tokenizer will return text without diacritics, wouldn't the snippet return the same? Thanks, George. 2012/2/8 Dan Kennedy danielk1...@gmail.com On 02/08/2012 11:34 PM, George Ionescu wrote: Hello all, I would like to know how are diacritics handled in FTS, specifically if I can index text with diacritics and search for terms without them. For example, given the queries CREATE VIRTUAL TABLE fts_pages USING fts4(tokenize=snowball ro_RO); INSERT INTO fts_pages (docid,content) VALUES (1, 'România este o ţară frumoasă'); the search SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'este' returns 1, but the next search SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'Romania' returns 0. The tokenizer I'm using is based on snowball and can be found at https://bitbucket.org/sevkin/**snowball_fts3https://bitbucket.org/sevkin/snowball_fts3 The custom tokenizer needs to normalize the tokens. So when it parses România it should return romania (with no diacritic) to FTS. Then when you query for romania, it will match. Note that the custom tokenizer is also used to tokenize queries as well as documents. So if I query for România, the tokenizer will normalize the query term to romania as well - which will match the normalized entry in the index. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] User defined functions naming problem
Hello dear sqlite users, Hello dr. Hipp, I'm trying to create a user-defined function having the name LEFT but that does not seem to work (I get an sql parsing error). All is fine if I rename it to STRLEFT, but I'd stick to the first one since I'd like to add some compatibility with other db engines. I understand there are a number of keywords which sqlite uses, but according to http://www.sqlite.org/lang_keywords.html, these keywords cannot be used as 'names of tables, indices, columns, or databases'. Function names are not included here. So, is there a way to do it, not possible or is it a bug somewhere in the parser? Thanks. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite vs MS Access
Helo Allan, This would be used in place of an MS Access database on a local/network disk. I believe that SQLite should be quicker for both transactions and queries than Access. The one draw back that comes to mind maybe portability (i.e. accessing data outside of the application), although the data would be portable across machines (PC, Mac, Unix, etc) should we ever need it to be in the future. Is there any webpage, or does anyone have any information comparing the benefits of the two. I can only find comparisons between MySQL and PostgreSQL. This information would aid us greatly in deciding whether to use SQLite or stick with Access. Any help/advice will be gratefully received. Trying to compare sqlite to MySQL or PostgreSQL is (a little) like comparing apples and oranges: they have different targets. While sqlite was made for embedding into an application, it is not a database server (like the other two you mentioned). This doesn't necessarily mean it won't be faster in some situations. It just means that it won't handle exceptionally well the concurrent access to your database. Putting the database on a network share and accessing it from there is definitively something to be avoided. I've had two customers (for our SQLiteDb product) which experienced 'database is locked' errors with only two clients accessing a single db on a network. This happens because of the faulty implementation of file locking on NFS. We do have a product (a multithreaded TCP/IP server, which is currently in beta stage) for accessing sqlite databases over TCP/IP. It won't boost performance to handle hundreds of users but we have succesfully tested against 10 clients. And, if your queries are written right, the server should be able to handle more concurent users. As far as performance, sqlite is definitively a winner here. If you're interested in some benchmarks, check out http://www.terrainformatica.com/sqlitedb/downloads.whtm. You don't have to purchase SQLiteDb: just download the demo and you will find there a direct comparison between JET (accessed via ADO) and SQLiteDb. Accessing sqlite via ODBC is an option. However, the ADO dependecy you're stuck with is something that should be avoided. Regards, George Ionescu - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: AW: AW: Re: spatial sqlite anyone ?
Hello Noel, I'm reposting this message because I have the feeling that you missed the original one. I don't plan to replace the normal indexing, I plan to have a set of function to create a (memory ?) index. But how do I retrieve the data without doing a select where rowid = xxx ? If you're going to create a memory index, than this will be no sqlite spatial index extension: I'm already doing this now by selecting records from a table and creating an in-memory spatial index. I don't know whether by coincidence or not, dr. Hipp has just published a wiki page regarding Virtual Tables which might do the trick, and although it's in very incipient stage (e.g. proposal) it sounds interesting. Go check it out at http://www.sqlite.org/cvstrac/wiki?p=VirtualTables. I must confess that I'm a little tired right now and I cannot see the Virtual Table's application in Spatial Indexes :-) Perhaps tomorrow morning my luck will change and I'll be enlightened. And another think, regarding your second wannado: 2 - to be able to load and exchange data from WKT (well know text format) and binary (shape file for instance) I don't know / think whether this extension should / must be able to read an ESRI shape. You should design your extension carefully with a pluggable way of doing readers/writers. This way, if anyone needs to work with a special format he/she could write it if it doesn't exist. I'm saying that because, for example, I've chosen to use an SVG-style notation for storing my gis elements. George.
[sqlite] Re: spatial sqlite anyone ?
Hello dear Noel, hello all sqlite users, a spatial extension for sqlite would be nice, although I think that replacing the indexing scheme (e.g. replace the current b-tree with a quad-tree or another spatial index) is alot of work. Just some questions / thoughts: 1. How would you handle indexing? Would you replace completely the btree with a spatial index (the hardest thing to do)? And if so, btree indexing would still be available or not? 2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did some research awhile ago on open-source solutions regarding spatial indexes). 3. You might want to take a look at SpatialIndex (http://u-foria.org/marioh/spatialindex/). The library is pretty clever designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I found with this library was that it was slower than GEOS at the time I last checked. One of the features I like is that it allows persisting the index. This might be an intermediate solution: you could store the index as a blob in the database. It would not be very efficient (as efficient as replacing indexing) but it would be a place to start. 4. Another nice library who might do the job is CGAL (http://www.cgal.org/). I didn't benchmarked it mostly because GEOS suited my needs just fine, but how knows, it might be better than the ones I mentioned. George.
[sqlite] Re: spatial sqlite anyone ?
Hello Noel, I don't plan to replace the normal indexing, I plan to have a set of function to create a (memory ?) index. But how do I retrieve the data without doing a select where rowid = xxx ? If you're going to create a memory index, than this will be no sqlite spatial index extension: I'm already doing this now by selecting records from a table and creating an in-memory spatial index. I don't know whether by coincidence or not, dr. Hipp has just published a wiki page regarding Virtual Tables which might do the trick, and although it's in very incipient stage (e.g. proposal) it sounds interesting. Go check it out at http://www.sqlite.org/cvstrac/wiki?p=VirtualTables. I must confess that I'm a little tired right now and I cannot see the Virtual Table's application in Spatial Indexes :-) Perhaps tomorrow morning my luck will change and I'll be enlightened. And another think, regarding your second wannado: 2 - to be able to load and exchange data from WKT (well know text format) and binary (shape file for instance) I don't know / think whether this extension should / must be able to read an ESRI shape. You should design your extension carefully with a pluggable way of doing readers/writers. This way, if anyone needs to work with a special format he/she could write it if it doesn't exist. I'm saying that because, for example, I've chosen to use an SVG-style notation for storing my gis elements. George. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
[sqlite] Problems running the Lemon parser to generate parse.c and parse.h
Hello dr. Hipp, hello sqlite users, I'm trying to build sqlite from cvs and I'm experiencing problems with generating parse.h and parse.c: I've compiled lemon.exe and it succesfully generated parse.c and parse.h by using lemon.exe parse.y. The problem I'm facing is that the generated parse.c is different from the one in official distribution (using v3.2.7). Is there something I'm doing wrong here? Thanks. George.
[sqlite] RDBMS handling of column names (was: Trouble with column names)
Hello Dr. Hipp, Hello dear sqlite users, following my post regarding how sqlite treats column names and the reply from Dr. Hipp, I've studied the way various RDBMS treat column names. The document containing the results obtained is attached to this message. The conclusions I can draw from these investigations is this: although not all queries tested work on all tested database engines, the ones that do work provide consistent results (e.g. SQL Server and MySQL yield the same results). Although things could further change in order to provide a uniform handling with column names, I only insist on one thing: if I ask for column Field1 and that column exists in the table as FIELD1, sqlite should return it as I ask, e.g. Field1 (instead of the way it does now, FIELD1). I cannot understand why this behavior has changed (it did not happen with 3.0.8). If this is not a bug, I guess it's a feature. If it's a feature, what advantages do I have if the database engine doesn't give me what I ask for? Please understand that I'm not trying to criticise anything or anyone: I'm just trying to understand why this has happened and why people do not complain about it. Thanks. George.
Re: [sqlite] RDBMS handling of column names (was: Trouble with column names)
Hello Dr. Hipp, Hello dear sqlite users, following my previous post: I did not know that I'm not allowed to post attachments in this group... I've added the document which compares how SQL Server, MySQL and sqlite treats column names as an attachment to the wiki page (http://www.sqlite.org/cvstrac/wiki?p=ColumnNames) created by Dr. Hipp. It can be found at: http://www.sqlite.org/cvstrac/attach_get/167/ColumnNames.htm George.
Re: [sqlite] RDBMS handling of column names (was: Trouble with column names)
Hello Clay, thanks for replying to the message, I suspect very strongly that most SQLite users are submitting themselves to that discipline which I suggested in an earlier message, principally that we don't expect SQLite to cover for our own inconsistencies in column naming. The discipline is fairly simple and I heartily encourage you to submit to it. I do agree that programming requires discipline. I myself am quite disciplined in programming (well, that's my personal oppinion about me, some people may think differently). The trouble is that my users aren't :-) The SQLiteDb ActiveX wrapper is written by me. My users complain that after the 3.2.1 update they aren't able to reference columns unless they respect case-sensitivityness. And I, as an independent software developer, cannot send emails to clients asking them to be disciplined :-) I could change my code to perform a case-insensitive comparison for column names but that would lead to a performance penalty (big enough not to do it). On the other hand, if you've read the document attached on the wiki, you'll notice that there are differences in the way sqlite treats column names as opposed to SQL Server and MySQL. And I don't really think I'm asking much: all I want is that when I ask for Field1 column, the database engine to report Field1 and not FIELD1. Do you think this is a stupid think to ask? Thanks again. George.
Re: [sqlite] RDBMS handling of column names (was: Trouble with column names)
Hello Dr. Hipp, Hello dear sqlite users, And I don't really think I'm asking much: all I want is that when I ask for Field1 column, the database engine to report Field1 and not FIELD1. Set PRAGMA short_column_names=OFF; and that will happen. Why doesn't that solution work for you? Hmmm, too much caffeine and less sleep! I didn't notice, neither by reading the docs nor by experimenting with command line tool, that setting short_column_names=OFF is just what I need and think is the correct way of handling column names. Sorry for insisting so much. I didn't want to start a riot... Thanks again. George.
[sqlite] Trouble with column names
Hello Dr. Hipp,
Hello dear sqlite users,
while working with sqlite v3.2.1, I've noticed another annoyance in the way
sqlite returns column names (besides the bug with #1141, duplicated as #1218).
Using sqlite command line, enter the following:
sqlite3 test.db
create table test(Field1 text(20));
insert into test values ('a value');
select field1 from test;
Field1
a value
notice the uppercase F from Field1, although field1 is requested.
So, it seems that sqlite returns column names as defined in schema, not as
requested by the user. This may not seem a big problem, but it becomes one when
working with columns and trying to refer them by name and not wanting to do a
case-insensitive comparison (I'm using a std::map to store columns).
Any ideas? Should I write a ticket?
If this is so hard to resolve, could someone point out the routine in which
sqlite computes column names? I could try to solve it myself and post a patch.
Thanks.
Re: Re: [sqlite] Trouble with column names
notice the uppercase F from Field1, although field1 is requested. So, it seems that sqlite returns column names as defined in schema, not as requested by the user. This may not seem a big problem, but it becomes one when working with columns and trying to refer them by name and not wanting to do a case-insensitive comparison (I'm using a std::map to store columns). Any ideas? Should I write a ticket? If this is so hard to resolve, could someone point out the routine in which sqlite computes column names? I could try to solve it myself and post a patch. It may make for some pretty long queries, but you can just tell sqlite what to call each of your columns: sqlite select field1 as field1 from test; field1 a value -Eli Yes, I'm well aware of the AS workaround. However, I was looking for a way to avoid this, since it would mean rewriting most of our queries. Thanks.
Re: [sqlite] HEllo All
Hello Gupta, depending on the platform you're using, you could try our SQLiteDb (ActiveX COM Wrapper) which support on-the-fly database encryption. (search for it at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers) Best regards, George Ionescu
[sqlite] Bug from ticket 1141 breaks existing applications
Hello Dr. Hipp, Hello sqlite users, while trying to upgrade to sqlite 3.1.3, I've encountered the bug described in ticket #1141 (sqlite returns the primary key's column name, if any, instead of the ROWID column, in a query like SELECT rowid, * FROM table). Any idea how to fix this (if it's an easy one), since it breaks existing applications. Thanks. Regards. George Ionescu. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
[sqlite] Very slow and blocking SELECT query
Hello dear SQLite users, Hello Dr. Hipp, Given the following table: CREATE TABLE [History] ( [License] VARCHAR(50) COLLATE NOCASE, [ControlCode] VARCHAR(20) COLLATE NOCASE, [ActivationKey] VARCHAR(50) COLLATE NOCASE, [Contact] VARCHAR(160) COLLATE NOCASE, [Obs] VARCHAR(255) COLLATE NOCASE, [Date], [UserIP] VARCHAR(20) COLLATE NOCASE, [UserName] VARCHAR(20) COLLATE NOCASE); ) having 3 indexes created on the table: CREATE INDEX idxHistory0 ON History (License); CREATE INDEX idxHistory1 ON History (Date); CREATE INDEX idxHistory2 ON History (ControlCode); and approximatively 17.500 records in the table. The table holds data for software activation. It is possible to have records containing same License/ControlCode pair, since the software has been activated more than once. I'm trying to find the License/ControlCode pair which has been activated in the past but is no longer valid (any past ControlCodes on one License are no longer valid). To be more specific, I'll try to give an example. Given the data: License ControlCode Date Lic1 CC2 2004.Jan.03 Lic1 CC1 2004.Jan.01 Lic2 CC3 2004.Feb.02 Lic2 CC3 2004.Feb.01 the output of the query should be: Lic1 CC1 2004.Jan.01 since Lic2 has been activated in the past but is still valid (since the current active pair is Lic2/CC3) and Lic1/CC1 is no longer valid because it has been activated in the past but the active license has another ControlCode. For this, I run the following query: SELECT DISTINCT LH.License, LH.ControlCode FROM History LH LEFT JOIN ( SELECT L1.License AS Lic, L1.ControlCode AS CC FROM History L1 INNER JOIN ( SELECT License, MAX([Date]) AS MaxDeDate FROM History GROUP BY License ) AS L2 ON L1.License = L2.License AND L1.[Date] = L2.MaxDeDate ) AS J2 ON J2.Lic = LH.License AND J2.CC = LH.ControlCode WHERE J2.CC IS NULL; I have two major problems with the above query: 1. It takes 91 seconds to complete! (Microsoft Access runs it in 2 seconds) Any hints for making it at least as fast as Access? 2. While the query executes, the whole database is locked! Why? If the engine creates temporary tables, should they block the whole database for writing? This is one major problem for me: does this mean that if I'll have more long-running queries, I shouldn't be able to update the database while they run? Please explain the way things work so I can understand better what it happens. I was really expecting to be able to make changes to the database while SELECT queries are executing... I'm using version sqlite command-line v3.1.1 beta. Thanks. George Ionescu.
Re: [sqlite] ODBC ADO
Hello Bert, have you tried using one of the ActiveX wrappers? I know it's not ODBC/ADO, but my wrapper (http://www.terrainformatica.com/sqlitedb) has at least an ADO-compatible interface, so that might help. I didn't want to write a full-blown ADO provider because this is the beauty of sqlite: you can have a whole database engine by delivering a single file to the client. Best regards, George Ionescu
Re: [sqlite] db admin tool
Hello sten, you could try SQLiteDb Query Analyzer from http://www.terrainformatica.com/sqlitedb (it's included in the SQLiteDb install package). It's not much right now but it's going to be improved alot in the next weeks. Best regards, George Ionescu
Re: [sqlite] db admin tool
Hello sten, you could try SQLiteDb Query Analyzer from http://www.terrainformatica.com/sqlitedb (it's included in the SQLiteDb install package). It's not much right now but it's going to be improved alot in the next weeks. Best regards, George Ionescu
[sqlite] Legal to use sqlite docs in commercial applications?
Hello Dr. Hipp, Hello sqlite users, I would like to know if it's legal to use sqlite documentation (sql syntax, sqlite logo etc.) into commercial applications' documentation (which are obviously built on top of sqlite). Thanks. Regards, George Ionescu _ Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
[sqlite] Legal to use sqlite docs in commercial applications ?
Hello Dr. Hipp, Hello sqlite users, I would like to know if it's legal to use sqlite documentation (sql syntax, sqlite logo etc.) into commercial applications' documentation (which are obviously built on top of sqlite). Thanks. Regards, George Ionescu
Re: [sqlite] Legal to use sqlite docs in commercial applications ?
Hello Dr. Hipp, sorry for the second repost. I've had some problems with mailing lists and I didn't notice my message actually arrived and that I've even got a reply. I have no objection to you using the parts that I wrote. But bare in mind that many others have contributed to the documentation. Thanks. How can I distinguish between *your* documentations and everyone else's ? Regards, George Ionescu
[sqlite] Anyone knows any sqlite based bullettin board ?
Hello Dr. Hipp, hello sqlite users, I'm looking for an sqlite-based bulletin board to run under linux. Any recommendations? Thanks. Regards, George Ionescu
[sqlite] Bug with sqlite3_column_decltype?
Hello Dr. Hipp, Hello sqlite users I have the following table definition: CREATE TABLE [Orders] ([OrderID] AFFINITY INT, [CustomerID] AFFINITY VARCHAR(5)) when I compile a statement with sqlite3_prepare16 ( SELECT * FROM Orders ), ask for sqlite3_column_count and retrieve values, all is ok (results are as expected). However, when I ask for first column's type with sqlite3_column_decltype, the result I get is: AFFINITYINT,[Cu I'm using v3.0.7 Any ideas? Regards, George Ionescu
[sqlite] Re: Bug with sqlite3_column_decltype?
Gerhard Haering wrote: Where did you get this interesting syntax with AFFINITY from? Check out http://www.sqlite.org/cvstrac/getfile/sqlite/doc/affinity.html Clay Dowling wrote: Try this instead: CREATE TABLE Orders (OrderID int, CustomerID varchar(5)); Of course I could try it. However, I'd like to tell sqlite myself the each column's affinity. And, according to the docs, the syntax I'm using is supported. Regards, George Ionescu _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.com/
Re: [sqlite] SQLITE_ERROR instead of SQLITE_FULL
Kurt Welgehausen [EMAIL PROTECTED] writes: Your disk is full; the database is not full. Hmm... I really do not understand: it is true that my disk was full, but how can my database be full? Isn't it supposed to expand as it needs? And, regarding the error message I'm getting: don't you think that you're disk is full is more correct than sql error or missing database? Since there's really no sql error and the database is still there. Regards, George Ionescu
[sqlite] SQLITE_ERROR instead of SQLITE_FULL
Hello sqlite users, Hello dr. Hipp, while working with sqlite and trying to insert large binary data into the database, I got the error SQL error or missing database. I thought it had become corrupted and I ran a check on it. Everything ok. Then, I've noticed I had 6 Mbytes of free space on C: (the location where was the database). So, my question is this: if sqlite cannot insert data into the database because it doesn't have enough free space, shouldn't it return SQLITE_FULL insted of SQLITE_ERROR ? Regards, George Ionescu
[sqlite] Encrypting data stored in database
Hello sqlite users,
Hello Dr. Hipp,
I would like to know if there is a way to encrpyt the data stored in the database but
to still be able to use SQL queries with plain text, something like
CREATE TABLE TEST (fld)
INSERT INTO Test VALUES ('some string')
SELECT * FROM Test WHERE fld = 'some string'
but, when the sqlite database is opened in a file viewer, 'some string' would not be
visible but only it's encrypted form.
Regards,
George Ionescu
[sqlite] More important details on synchronous PRAGMA - suggested changes
Hello sqlite users, Hello dr. Hipp, I've read the info you've provided. Thanks. 1. For Windows systems: in the CreateFileA call to open an sqlite database (os_win.c), could the file flag FILE_FLAG_WRITE_THROUGH also be specified? This flag is quite important and is also used by SQL Server when opening it's own logs and databases (seeQ234656 and Q230785 in MSDN). Quote from MSDN: Applications can specify the FILE_FLAG_WRITE_THROUGH Win32 flag to instruct the system to write through any intermediate cache and go directly to disk. The system can still cache write operations, but cannot lazily flush them. 2. If synchronous is off, and I write a single record (a single INSERT) and a power failure occurs, will the database be affected in any way? Regards, George Ionescu
[sqlite] PRAGMA default_synchronous not working?
Hello sqlite users, Hello dr. Hipp, using v3.0.6, I've discovered that PRAGMA default_synchronous directive is not working. Will this be implemented in the next version? Regards, George Ionescu
[sqlite] Risks involved with using synchronous=OFF (was 'Single INSERT is very slow')
Hello sqlite users, Hello dr. Hipp, as Nuno Lucas suggested, I've tried inserting one record using synchronous = OFF. That makes a *huge* difference: 150 ms (synchronous=FULL) vs. 3 ms (synchronous=OFF) !! So I'm asking this: anyone has had any real-word experience with multi-user access and setting synchronous = OFF on Windows NT systems ? I've read the docs and I know the risks, but I was wondering if any of you could share your real-word experience... If the database becomes corrupted after a power failure while inserting records, is there any way of repairing the database? Best regards, George Ionescu
[sqlite] Single INSERT is very slow
Hello sqlite users,
Hello Dr. Hipp,
while using sqlite v3.0.6, I've discovered that doing a single INSERT or
UPDATE on a table which has only one field is very slow:
CREATE TABLE Test (Field1 VARCHAR(10));
INSERT INTO Test VALUES ('123');
the INSERT statement above takes approx. 150 milliseconds to complete, which
is very slow compared to Access/ADO, which does the same in 3 milliseconds.
The timings were done on a 2.4 Celeron, 512 RAM, Windows XP.
Is there something I'm missing here or is this the expected behavior?
Timings on other operations (SELECT) yielded expected results (e.g. faster
than Access/ADO).
Best regards,
George Ionescu
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
[sqlite] Re: sqlite3_busy_timeout ignored while doing multi-threaded updates ?
More information on this:
Busy handler's return value is indeed ignored: in sqlite3pager_begin I've seen
this:
..
#if 0
int busy = 1;
do {
rc = sqlite3OsLock(pPager-fd, RESERVED_LOCK);
}while( rc==SQLITE_BUSY
pPager-pBusyHandler
pPager-pBusyHandler-xFunc
pPager-pBusyHandler-xFunc(pPager-pBusyHandler-pArg, busy++)
);
#endif
..
Will this behavior be implemented in the next release?
Regards,
George Ionescu
[sqlite] sqlite3_busy_timeout ignored while doing multi-threaded updates ?
Hello SQLite users, Hello Dr. Hipp, while doing updates from multiple threads, I'm only able to perform an update on one thread, all the other threads returning SQLITE_BUSY. This is ok: I've read the docs and it behaves as it should. However, what I don't understand is the behavior of sqlite when I set a busy timeout. The documentation states: ** This routine sets a busy handler that sleeps for a while when a ** table is locked. The handler will sleep multiple times until ** at least ms milleseconds of sleeping have been done. After ** ms milleseconds of sleeping, the handler returns 0 which ** causes sqlite3_exec() to return SQLITE_BUSY. So my question is this: given two threads, T1 and T2 which try to do simultaneous updates from two separate threads, and T1 is beginning and commiting the UPDATE, shouldn't T2 try for x milliseconds (value specified in sqlite3_busy_timeout) before returning SQLITE_BUSY ? In v3.0.4, the result is that T2 returns immediately reporting SQLITE_BUSY, as if it is ignoring the busy timeout. Regards, George Ionescu
[sqlite] ANN: SQLiteDB, a COM wrapper around sqlite has been released
Hello SQLite users, and VB sqlite users, I have just released SQLiteDB, a COM wrapper around sqlite written in ATL. Go get your copy from http://www.terrainformatica.com/users/george (note that the link will change in a few days to http://www.terrainformatica.com/SQLiteDB). You will also get some samples written in VB: SQLiteImport, a tiny database import utility, SQLiteBenchmark - compare SQLiteDB's performance with an Access database (ADO/JET) and SQLiteMultiThread - hammer an sqlite database by reading data from multiple threads. Best regards, George Ionescu
[sqlite] Another way of corrupting the database.
Hello SQLite users, Hello Dr. Hipp, I think I have found another way to corrupt an sqlite database: create it with v3.0.2, add some data, open it with v2.8.14 and then try to open it back with v3.0.2. Regards, George Ionescu
[sqlite] Reusing SQLite's parser
Hello SQLite users, Hello Dr. Hipp, I would like to reuse sqlite's parser to do some sql parsing against CREATE TABLE statements. However, while stepping through code from an sqlite_compile, into sqliteRunParser I cannot see the Parse structure filling appropriatelly (e.g.: trying to execute a CREATE TABLE statement, after parsing the Parse struct isn't filled with a Table structure). Any ideas ? Best regards, George Ionescu PS: I don't want to 'reinvent the wheel': since the parser is already there, I don't want to create my own sql parsing functions.
[sqlite] Bug with UNION
Hello SQLite users, Hello Dr. Hipp, I think I have found a bug related to UNION SELECT; try this: CREATE TABLE Test1 (fld1) CREATE TABLE Test2 (fld2) INSERT INTO Test1 VALUES (1) INSERT INTO Test2 VALUES (1) SELECT * FROM Test1 UNION SELECT * FROM Test2 And I get only one record ? Shouldn't I get two records, both having 1 value ? Regards, George Ionescu
[sqlite] Re: Life of a cursor
Hello Tito, Would you expect a cursor to live after is database has been closed? Sure. Imagine a Client - Server scenario: the client makes a request (e.g. SELECT * FROM Orders), request which is passed to a server. The server executes the sql and obtains a recordset. After that, it's of no use to keep the database open, so it closes it. And sends the results obtained back to the client. This is what's known in ADO as disconnected recordsets, and is the way I've been programming database applications for years. My COM wrapper thinks in the same way (it cashes the result of a SELECT statement). One improvement could be made here, but quite hard to implement: a client could receive a disconnected set of records, change it (e.g. add one row and delete another) and send it back to the server for processing. The server would then be capable of applying the modifications in the real database. Regards, George Ionescu
[sqlite] Re: SQLite version 3 design question: '500'=500?
Hello Dr. Hipp,
Hello SQLite users,
CREATE TABLE test1(a VARCHAR(100));
INSERT INTO test1 VALUES('501');
INSERT INTO test1 VALUES(' 502 ');
SELECT * FROM test1 WHERE a=501;
SELECT * FROM test1 WHERE a=502;
SELECT * FROM test1 WHERE a'502';
in MS SQL Server yelds the following results:
a
501
a
502
a
501
502
(please observe spacing from ' 502 ', meaning that it's treated on output as a
string (field's datatype from CREATE TABLE statement))
and the following:
CREATE TABLE test2(b INTEGER);
INSERT INTO test2 VALUES(503);
INSERT INTO test2 VALUES(504);
SELECT * FROM test2 WHERE b='503';
SELECT * FROM test2 WHERE b'503';
produces:
b
503
b
504
Best regards,
George Ionescu
[sqlite] SQLite COM Wrapper written in ATL
To all VB users out there trying to figure out how to use sqlite in VB: I have written a COM wrapper in ATL which features an ADO like object model (Connection and Recordset). From the tests I've run, it's quite fast. It doesn't need a separate sqlite.dll (sqlite source is in the COM wrapper). Just tell me how interested you are and I will try to publish it. Regards, George Ionescu - Original Message - From: Carlos Garces [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 13, 2004 10:59 PM Subject: sqlite with Visual Basic Hi! I can use SQLLite with Visual Basic without using other external DLL Any sample of using sqlite.dll API? Thanks Carlos Garcés - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: SQLite version 3 design question: '500'=500?
Hello Dr. Hipp, Hello SQLite users, in MS SQL Server, the following line SELECT '500' = 500; returns a column having the alias '500' and the value 500 :-o However, wanting to test how the engine compares strings and numbers: SELECT 'match' WHERE '500' = 500; returns 'match'; also, the following statements return the same result: SELECT 'match' WHERE '500' = 500; SELECT 'match' WHERE '500' = 499 + 1; Hope I've been of some help. Regards, George Ionescu
[sqlite] Concurrency Control and Recovery in v3.0
Hello SQLite users, Hello Dr. Hipp, Since I'm beginning to like more and more sqlite, I'd like to bring my contribution to it's development. For anyone interested, I have found a very interesting material regarding 'Concurency Control and Recovery in Database Systems'. I've browsed the docs but since I'm not *that* smart, I couldn't understand much of it ;-) If this document could be of any help in implementing a better concurency access in sqlite, you decide. Go get it at http://research.microsoft.com/pubs/ccontrol/. Regards, George Ionescu
[sqlite] Create a function to limit results based on conditions
Hello SQLite users, Hello Dr. Hipp, I would like to implement my own RegExp functions (to limit results based on conditions). From my understanding, sqlite_create_function cannot be used for this (the function can only return a value; it cannot tell sqlite not to return a row). What is the approach you would recommend? Regards, George Ionescu
[sqlite] [Repost] Implementing Full Text Search
Hello dear SQLite users, Hello dr. Hipp, I'm reposting this because I didn't actually got an answer, just oppinions, which are fine as long as they are constructive... The question is this: since most of RDBMS implement full text search, shouldn't this be a feature sqlite could support ? Brad wrote: My regex patch should do that SELECT * FROM Categories WHERE CategoryDescription RLIKE 'Beverages and CategoryDescription NOT RLIKE 'Whiskey'; You can find a working test version here http://www.wasp.net.au/~brad/sqlite-110104-snapshot-bkc1.tgzCould I have a 'Windows' version?I would like to test your regex, however I need to now if what you're doing is a full scan of the column...D. Morel wrote: Regarding Full Text Search syntax, I think CONTAINS statement would be ok (as implemented in other database engines). So: SELECT * FROM Categories WHERE CONTAINS(CategoryDescription, 'Beverages') would return all the rows where column CategoryDescription contains the word 'Beverages' :-) doesn't SELECT * FROM Categories WHERE CategoryDescription LIKE '%Beverages%'; do just that ? It will, but it won't be a true 'Full Text Search', since the LIKE operator will not benefit from indexes, so a full column scan will be necessary to perform such a query. I'm quite familiar with the anayomy of a full text search engine (parsers / tokenizers / stop words etc.), but since this was a suggestion from Dr. Hipp, I was really wondering what did he have in mind: just a 'smarter' LIKE operator (which returns ranking), or something more... Best regards, George Ionescu
[sqlite] Changing default_temp_store
Hello sqlite users, Hello dr. Hipp, I'm trying to speed up sqlite and I would like to change the default_temp_store so that all temporary tables are created in memory, instead of file. I was wondering how can I do that. I've tried changing the TEMP_STORE macro to 3 - Always use memory, with no success: when doing PRAGMA default_temp_store, I always get 0. I would also like to know if there are any other things involved with changing this? Is the data integrity affected in any way? ( This might sound like a silly question, but I have to ask it ) Best regards, George Ionescu
[sqlite] Full text search implementation
Hello dear SQLite users. Hello Dr. Hipp, I've been using sqlite for quite some time now and I am a fan (thank you Dr. Hipp). I will try to rewrite my (unfinished) Document Management application using SQLite and I was wondering if Full Text Search will be implemented. I'vee seen a ticket posted by Dr. Hipp on 2003-09-03 where he got a response from a guy who's oppinion was that Full Text Search is not useful in sqlite; I have a different oppinion: since all major database engines implement this feature, why should sqlite be left aside? Regarding Full Text Search syntax, I think CONTAINS statement would be ok (as implemented in other database engines). So: SELECT * FROM Categories WHERE CONTAINS(CategoryDescription, 'Beverages') would return all the rows where column CategoryDescription contains the word 'Beverages' :-) similar SELECT * FROM Categories WHERE CONTAINS(CategoryDescription, ' Beverages AND NOT Whiskey ') would return all the rows where column CategoryDescription contains the word 'Beverages' and does not contain the word 'Whiskey' Ranking return would be a cool feature. However, another feature I drewl about would be 'highlighting hits'. For this, the position / positions in the text where the word was found would be enough (I think). Eagerly waiting for your answers and comments, Sincerely, George Ionescu - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
