Re: [sqlite] Commit fails due to "database is locked" in active transaction
On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > Requiring the second transaction to complete first is expected in > > terms of SQLIte's concurrency system. > So in terms of using SQLite, I need to close the entire transaction and > restart it when I get a "database locked" return code in a writer > thread? It's not enough to just retry the commit in a little while? It's safe to retry a commit. It may not be safe to retry a writing statement, depending on the presence of other writers. These two messages should help explain what you need to consider to avoid deadlocks: http://www.mail-archive.com/sqlite-users@sqlite.org/msg27284.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg28638.html > Wouldn't it be more intuitive to allow the single handle holding the > RESERVED lock to finish? Right now, the SQLite behaviour allows only the > serialized isolation level. Making this change would make the isolation > level be more like "read committed". You could get that behavior now by simply not using an explicit transaction in the reader. Actual "read committed" isolation support comes in when there are concurrent writers, so one transaction can see its own changes as well as the changes of others that have committed in parallel. Keep in mind, SQLite has no central transaction arbiter managing the file; its concurrency is implemented in terms of OS-level file locks. In order to implement parallel writers at any isolation level, a writer would need to somehow distinguish its changes from those of other writers in progress. That makes the act of committing itself, as well as crash recovery, much more complex. It also has to deal with potential conflicts on pending changes, and with "read committed" as an option, it's complicated even more by transactions using a mix of isolation levels. A very difficult kind of change. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote: > I'm using SQLite3 version 3.4.2 (latest in Debian testing and unstable), > and I have a scenario using transactions in SQLite3 that is a bit > counter-intuitive. You'll want to look at http://sqlite.org/lockingv3.html for this. > I open an SQLite3 database from two terminals. In the first terminal I > run the following SQLs: > > sqlite> begin; > > sqlite> insert into a values (null); > > sqlite> insert into a values (null); This transaction has acquired a RESERVED (intent to write) lock, at the first INSERT statement. Others may read, but no others may announce an intent to write. > In the second terminal, I run the following SQLS: > > sqlite> begin; > > sqlite> insert into a values (null); > > SQL error: database is locked This transaction has acquired a SHARED (reading) lock at the first access to the database. At the INSERT statement, it tries to acquire RESERVED, but fails because another has already announced its intent to write. This transaction remains SHARED. > I go back to the first terminal at this stage and I try to end the > transaction using commit: > > sqlite> commit; > > SQL error: database is locked The second connection still has a SHARED (reading) lock, so this transaction cannot make any physical changes to the file yet. The INSERT statements you already executed are buffered internally, so it did not need to make physical changes before. > At this point, I can't commit the transaction in the first terminal, > until I run a commit in the second terminal, even though the first > terminal is the one with the active transaction, and the second terminal > shouldn't have any effect on the active transaction. The second connection has an active transaction too, just in read-only state. It must end before the first can proceed with physical changes to the file. > This behaviour varies, depending on the filesystem type on which the > sqlite database file is created in. If I use reiserfs, it sometimes > takes a long while until I can commit from any of the terminals. In > ext3, this is usually resolved after retrying the commit a few times in > both terminals. In tmpfs, there is never any issue, the first terminal > can always commit. > > What am I missing here? Is this behaviour the expected one? Requiring the second transaction to complete first is expected in terms of SQLIte's concurrency system. The fact that you are seeing changes in behavior depending on the filesystem is disturbing though. They should all behave the same if they are implementing locking correctly. I will let others speak to this point; if you can post more detail on the steps (e.g. if I do "commit" here and "commit" here nothing happens for N minutes), it will probably help them. Just to be clear, these are local filesystems, correct? Network mounts like NFS are not expected to work. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
On 12/31/07, Hugo Ferreira <[EMAIL PROTECTED]> wrote: > a) It should be completely integrated/embedded within the application; no > separate install. Just a single .DLL ;-) > b) It must have bindings with .Net 2.0 and Mono >1.2.5. > c) Open-Source. On the subject of alternatives, Firebird might fit the bill. What I'm unsure of is its embedded support on multiple platforms; last time I looked (some years ago) embedded was only working fully on Windows. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
On 12/31/07, Hugo Ferreira <[EMAIL PROTECTED]> wrote: > Yes, indeed, it is doing a sequential scan according to EXPLAIN. Still, the > slowness is unbelievable in such a small database. The whole db takes 11Mb, > and doing a LEFT JOIN between a few hundred and a few thousand of rows on a > "Core 2 Duo" taking 6 seconds is... I don't even know what it is :P There > must be something very strange going on... I suppose it is not possible to > create indexes on views, right? > > I'll play some more with queries and try to figure out if I can tweak this. > If not, then I guess I'll unfortunately have to move to PostgreSQL :-( If you haven't found this page yet, it may be useful: http://sqlite.org/optoverview.html If a client-server database engine like PostgreSQL is better suited to your application, I'd probably use it anyway. SQLite is good, but it's not a compact version of a major database engine. If you're simply looking for something that's easily deployable, and SQLite turns out to not meet your needs, there are other database engines that might. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and Columnar Databases
On 12/18/07, Tom Briggs <[EMAIL PROTECTED]> wrote: >This implies that there are databases that provide multiple storage > mechanisms and allow users to choose between the options. Does such a > database exist? It may not be exactly what you had in mind, but see MySQL: http://dev.mysql.com/doc/refman/5.1/en/storage-engine-overview.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Default Encoding In Sqlite
I wrote: > The default storage encoding on disk is UTF-8, but it can be changed > to UTF-16 with a PRAGMA. As Igor reminds me, if you create the database file using sqlite3_open16() the default will be UTF-16 instead. You can still set it explicitly via a PRAGMA if you wish. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Default Encoding In Sqlite
On 12/14/07, Kalyani Phadke <[EMAIL PROTECTED]> wrote: > Whats default encoding in SQLite3 ? How does SQLite3 handles storing > Japanese/Chinese text in database? I know in SQL express/sql server I > have to use nVarchar/nchar/ntext datatypes to store Japanese/Chinese > text in database. It seems that in SQLite3 column having text datatype > can also store chinese characters.. SQLite assumes TEXT data is Unicode. You can work with it in either UTF-8 by using the *_text() APIs, or UTF-16 using the *_text16() calls. It will convert between the two encodings as necessary. The default storage encoding on disk is UTF-8, but it can be changed to UTF-16 with a PRAGMA. Note that I said it "assumes" the data is in that form. SQLite does not validate the encoding, so it is possible to store text data in some other encoding, like SJIS. You will just get strange results when asking SQLite to convert the data, such as when storing it with *_text(), but retrieving it with *_text16(). The sqlite3 shell is intended to work with UTF-8, but because of the way different platforms handle the terminal/console encodings, it can be difficult to use properly. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to check if the table has some specific values
I think Joanne's example may be simplified, and the question was really more general... On 12/12/07, Joanne Pham <[EMAIL PROTECTED]> wrote: > Basiclly there is no SQL logic in SQLite. Right, there's nothing like T-SQL. Even in other databases, most procedural logic along IF..THEN lines is done with a dedicated language used for functions or stored procedures, not part of the primary SQL interface. SQLite does have a limited form of procedural logic in its trigger statements. Keep in mind that because SQLite is an embedded database, it's considered normal to do such logic within the application as necessary. There isn't any overhead for a network protocol or similar, and you generally don't need to deal with arbitrary applications accessing your database, so there's less gain for pushing things into the database interface itself. After all, the database is inside your application already. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite gives Access Violation in Windows
On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote: > It is SQLite 3.4.2, and I downloaded the ready binary, the DLL and sqlite3.h; > I didn't compile anything. But the sqlite3.lib didn't come ready; it came as > sqlite3.def together with the DLL, then I made it into a sqlite3.lib by > using the LIB.EXE utility of Visual Studio 6.0 -> LIB.EXE /DEF:sqlite3.def. > Thank you in advance. Ok. It sounds like there's something weird about the environment in the GUI version of your app. Pelles C is new to me, so it's a bit of a wild card too. If you're able to provide the source to the Win32 app, and the database you're testing with, I can dig into it myself, although it'll take me a little while. One thing you can try now is getting the 3.5.3 binary, and replace the DLL your app uses with it. It should just be a drop-in replacement, without needing to build the import library and link your app again. I still want to find out what the problem is, but it would be good to know if this changes things. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite gives Access Violation in Windows
On 12/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "Trevor Talbot" <[EMAIL PROTECTED]> wrote: > > > test.exe: WinMain( ) + 71 > > > sqlite3.dll: sqlite3_exec( ) + 154 > > > sqlite3.dll: sqlite3_column_text( ) + 1A > > > sqlite3.dll: sqlite3_data_count( ) + AC > > > ntdll.dll: RtlEnterCriticalSection( ) + B > > Hmm, looks like a fault within SQLite's internal mutex handling. What > > version of sqlite is this, and did you compile it yourself? I'm > > wondering if it's not a compiler-related bug. > sqlite3_data_count() never touches a mutex. sqlite3_data_count() > consists of 3 lines of code that extracts a value from the structure > that is passed in as its only parameter. > > Furthermore, sqlite3_column_text() does not call sqlite3_data_count(), > either directly or through intermediate subroutines. > > So I would be very suspicious about drawing conclusions from the > stack trace above. Most of the Windows debugging tools will, in the absence of full symbols, choose the closest public/exported symbol and print an instruction offset from it. Not many optimizers are aggressive about reordering functions, so usually code gets laid out in the order it was written. That puts the mutex-related call not very far after sqlite3_data_count(), like perhaps columnMem(). Still quite a bit of guesswork involved, but that's pretty much the only way it's going to get from sqlite into RtlEnterCriticalSection(), and it'll do as a starting point. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite gives Access Violation in Windows
On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote: > For example, in a database where searching for text "key1" (column a) > returns "value1", "value2", "value3", and "value4" in column b, the callback > fnuction of that code would be called THREE times, returning thus three > message boxes: > - key1 -> value1 > - key1 -> value2 > - key1 -> value3 > And, in the fourth call to the callback function in this same sqlite3_exec > call, it gives "Access Violation", and never gets to reach the callback > function for the fourth time. But, if there were only value1, value2 and > value3, it would work fine. Actually, if we called, several times, > sqlite3_exec() for an SELECT that return 3 or less calls to the callback > function, it would work fine. Thus, it runs into trouble apparently when it > is about to call the callback function for the fourth time in a same > sqlite3_exec. > test.exe: WinMain( ) + 71 > sqlite3.dll: sqlite3_exec( ) + 154 > sqlite3.dll: sqlite3_column_text( ) + 1A > sqlite3.dll: sqlite3_data_count( ) + AC > ntdll.dll: RtlEnterCriticalSection( ) + B Hmm, looks like a fault within SQLite's internal mutex handling. What version of sqlite is this, and did you compile it yourself? I'm wondering if it's not a compiler-related bug. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to cast utf16 text to int?
On 12/11/07, Maxim V. Shiyanovsky <[EMAIL PROTECTED]> wrote:
> One more question - is there any way to insert utf-16 string from
> sqlite3.exe?
> As I understand I can't use syntax like this:
> insert into t values (X'31003700', 1);
No; the sqlite3 shell deals only in UTF-8, which is hard to make work
on Windows because the console doesn't have proper support for it.
However, it does perform casts from BLOB to TEXT by treating the blob
as a set of bytes in UTF-8 form, so that could be used as an escape
syntax:
insert into t values(X'3137', 1);
But since these characters are in the ASCII range anyway, you might as
well just enter them as plain text:
insert into t values('17', 1);
(That also explains the "first digit" trouble you were having: null
bytes are not allowed in strings, so the final TEXT form only
contained one character. It was a happy coincidence that you were
using characters from the ASCII range, and UTF-16 in little endian
form as the input bytes.)
> As I wrote in the first letter I need cast from utf-16 text to int.
> In other words I need select like this:
> Select * from t, d where cast(t.value as int) = d.id
With the above in mind, this should work now.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQLite Consortium Launches
Very cool. Congratulations on getting this organized. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode support for Sqlite?
On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote: > I am using the sqlite to store the metadata of audio files. > Is it possible to store the metadata in unicode character format in sqlite. Yes; SQLite assumes all TEXT type data in the database is Unicode. You can work with it in UTF-8 with the *_text() APIs, or UTF-16 using the *_text16() calls. SQLite will convert between the two encodings as necessary. The sqlite3 shell assumes UTF-8, but it depends on the platform's console to actually use UTF-8 when talking to it, so it may be difficult to properly test with it. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode support for Sqlite?
On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote: > Does Sqlite support unicode? > I have seen that it supports utf-8 and utf-16. > I want to know whether it supports unicode character formats. Unicode is a very large and complex topic, so that question is way too vague to answer. Can you provide an example of what you're looking for? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite gives Access Violation in Windows
On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote: > I'm programing in C, new to SQLite, in Windows, with the Pelles C Compiler > for Windows. > It works well. BUT, when I try to put this code in a Win32 application, like > inside a WinMain procedure, for example, a strange behavior happens: > everytime sqlite3_exec finds exactly more than 3 records in the database, it > gives an "Access Violation" error. Pelles C's debugger gives "Exception: > Access Violation", apparently in NTDLL.DLL. It happens with any database of > any size, as long as SELECT returns more than 3 results. We tested it in 2 > computers Windows XP Pro (one of them 2.4 GHz and 1GB RAM, and the > other 1.1GHz and 384 MB RAM), and, in both of them, the error happens > every time > SELECT finds more than 3 records (so, I could never have noticed it if I had > tested it only with SELECTs that return 3 or less results); we tried to > increase Stack Reserve size, but no difference. Important to notice is the > fact that it works fine in a Console application, and also in sqlite3.exe it > works fine. You'll need to get more information out of the debugger, like a stack trace. Alternatively, scatter MessageBox() calls through your code to find out where it is when it runs into trouble. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to cast utf16 text to int?
On 12/5/07, Maxim V. Shiyanovsky <[EMAIL PROTECTED]> wrote: > I have table that holds values of different types in utf16. > I also know value type for the current row. > How should I cast value to compare it with integer? > > This test shows 1 instead of 17 that I expected. > > sqlite> create table t (value text, field_type int); > sqlite> insert into t values (X'31003700', 1); > sqlite> select value from t; > 1 Based on your description, I think you mean you're trying to store a string of Unicode characters, and you're just working with them as UTF-16 in your application. In that case, the problem is that you're trying to use blobs. Use text instead. sqlite3_bind_text16() will let you use UTF-16 in your application, and SQLite will take care of the on-disk encoding and byte order. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: > IBM DB2 9.5 > > select a AS "foo" from t1 union select b from t1 order by foo > SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 The problem here is with the inconsistent quoting. PostgreSQL uses the opposite case folding as everyone else, hence the behavior difference. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On 12/7/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Fri, Dec 07, 2007 at 05:49:56PM -0500, Igor Tandetnik wrote: > > >So I would to ask: are there any benefits from using VARCHAR() and > > >not TEXT? > > There is no difference whatsoever to SQLite. It maintains the types for > > compatibility with other DMBS only > Just from curiosity: perhaps could you tell, does it make any differences > (other than just coercing a limit) in case of other database systems (like > f.e. PostgreSQL, or other known to you)? > > If so - is it significant difference (in data access speed, or any other...)? It varies wildly depending on the database and how its storage was designed. In PostgreSQL, there is no difference; VARCHAR is just TEXT with a constraint on length. However, its storage subsystem is such that any data of variable length is stored the same way, up to a maximum of 1GB, and all variable-length data types are built on that. Firebird and Interbase have a 32KB limit on VARCHAR size (the limit you use may be lower depending on encoding selection), but it's stored essentially the same way as other database fields. It does not have a TEXT type, so your next option is BLOB (with a subtype label of text), which is stored separately from other data. It's not entirely transparent due to the way it interacts with Firebird's transactional architecture, so there are some caveats to using it. That's beside obvious disadvantages of it being a separate type, and therefore not as easy to use string manipulation functions with. And so on... In general, you probably wion't find any significant difference between a TEXT type (if it exists) and a VARCHAR type. As another reply mentioned, the major historical difference was between CHAR and VARCHAR, since the former can be optimized based on a fixed-length architecture. You're less likely to see that now, since storage and processing capabilities have changed such that it's better to spend more CPU time in an effort to make the on-disk data as compact as possible, since storage is so slow to access. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance tuning using PRAGMA, other methods
On 11/30/07, Eduardo Morras <[EMAIL PROTECTED]> wrote: > e) I'm not sure, but using a journaling or softupdate enabled > filesystem should means you can disable syncronous pragma. No; SQLite's synchronous code ensures data reaches disk intact and in proper order at specific times, regardless of system cache effects. The mechanism the filesystem uses to guarantee internal consistency doesn't affect that at all. It's not safe to disable synchronous. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
On 11/28/07, Ralf Junker <[EMAIL PROTECTED]> wrote: > >> Imagine that a SQLite3 database opened in a custom application with a > >> registered a collation sequence named "unknown" has created the following > >> table: > >> > >> CREATE TABLE a (b COLLATE unknown); > Trevor, I am not sure what you mean by "subselect on its own". Is this what > you are looking for? > sqlite> SELECT * FROM (SELECT * FROM a); > SQL error: no such collation sequence: unknown Yes, exactly. I was curious to see if it made any kind of difference. Unfortunately I don't have an explanation/fix for you though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ShawnMilo intro
On 11/28/07, Shawn Milochik <[EMAIL PROTECTED]> wrote: > I have a couple of questions, but I am going to lurk a bit first. However, > is there an archive? I didn't receive one by e-mailing the automated help > address for this list. I'd like to see if the answers are there first. Welcome :) There are 3 archives listed here: http://sqlite.org/support.html I think gmane is said to have a good search engine, but it's been ages since I've used it, so I can't be sure. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
On 11/28/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: > > egrep gets to work with a flat text file, which it can easily read > > sequentially and get optimal performance from the OS's file > > buffering/cache management. It only needs to read a piece of the file > > and scan for patterns, repeating until done. The only structure it > > needs to be aware of is line breaks, but that is so simple it can be > > folded into the pattern scan itself. > > You are mostly describing fgrep. Egrep does a regular expression search > thus honouring column layout in the example I gave earlier. (FYI fgrep > is 50% faster than egrep than egrep in this query) I was describing grep and all related tools. The only relevant difference between egrep and fgrep is what kind of pattern it looks for; the searching method is the same. Egrep is not aware of column structure, only lines. > The points are two: > 1) Could sqlite structure allow for faster full column scan queries? > More than 1000% slower performance seems way too much difference. The short answer is no. The longer answer is maybe in theory (see research into column-oriented or "vertical" datastores, such as the much-publicized Vertica), but SQLite's usage scenarios don't really make it appropriate. It would probably mean abandoning the single-file database, for instance, and would make many other common queries much less efficient. That's ignoring all sorts of other practical issues, like drh's careful avoidance of patent-encumbered things. It's possible there is room for optimizing this case a bit more the way it is, but it will always be much slower than grep. You may not realize what I mean about structured data. A plaintext file looks like this: LineLineLineLineLineLineLine... An sqlite database, on the other hand, has lots of structure. It's broken into fixed-sized pages, each of which has a small header at the front. Each table needs a way to find the rows in that belong to it (index on the rowid field). For each row, it needs to be able to locate each field of data belonging to that row. Each field has, besides the data itself, a type code and data length. All of this is encoded in various space-efficient forms in the same file. So, for instance, consider that a field of data is stored like: TypeLengthData... and you can see where this is going. When SQLite searches it, it needs to follow the structure to know where it is and what it's looking at. It can't simply read the next chunk of data and do pattern matching on it immediately, like grep can. > 2) an index could (and should) be used when using LIKE 'asdf' > > Well I actually solved this problem by using > PRAGMA case_sensitive_like = 1; > Now the index is being used (for all non-english characters). Which is what you wanted, right? > But the online manual states: > > " > if case_sensitive_like mode is enabled then the column must use the > default BINARY collating sequence, > if case_sensitive_like mode is disabled then the column must use the b > uilt-in NOCASE collating sequence. > > > NOCASE - The same as binary, except the 26 upper case characters used > by the English language are folded to their lower case equivalents > before the comparison is performed. > " > > so as I understand it there's no reason that the index was not used in > the NOCASE scenario. It should do case-insensitive matches for the 26 > latin characters and case-sensitive (binary) for all the others. It might be a little hard to process that part the first few times. You can declare a collation when creating the index itself, either BINARY or NOCASE. The default is BINARY, which is case sensitive (does not assume US ASCII or do anything with case). By default, LIKE is case insensitive, which means it does care about case so it can treat 'A' as 'a'. Therefore it can only use a NOCASE index for optimizing prefix matches, because the only way for an index to work is if it treats the characters the same way. NOCASE is not the index default though, and it isn't what you wanted because you have non-english data anyway. By turning case_sensitive_like on, you made LIKE look for an index with BINARY collation instead. That matches the index you created, so it can optimize that case now. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
On 11/28/07, arbalest06 <[EMAIL PROTECTED]> wrote: > ok..thanx for that..now if process A is writing into the database, and > process B attempts to write, does sqlite take note of B's attempt and gives > the permission to B when A is done? like would it be a queue that the first > process that attempted to write should be given priority to write? or is it > that when process A is done, then process C attempts to write, C is given > the permission to write even though process B attempted to write first than > C? There's no queue. When process B attempts to write, it will be refused, and must try again later itself. It controls if/when it does so; SQLite doesn't force any particular behavior. If process C tries before B tries again, then C will get to write and B will have to wait again. In API terms, an sqlite function will return SQLITE_BUSY if it can't do what it needs to do right now. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
On 11/27/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: > I had a 135MB, 1256132 lines, '@' separated text file containing > various words and text fields (like a dictionary). > Example record: > [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός > ανθόκηπος - εξώστης, > ταράτσα@@@ > I imported the data in sqlite3.3.6 but when querying with the 'like' > operator, the performance way too slow (about 1.5-2 seconds/query): > > >time sqlite3 dicts.db "select * from table1 where word like 'asdf%';" > 1.156u 0.491s 0:01.64 100.0%0+0k 0+0io 0pf+0w > FYI using egrep takes only 0.14s to get results in the worse case scenario: > >time egrep -i "[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@asdf" > meta.txt > 0.077u 0.069s 0:00.14 92.8% 0+0k 0+0io 0pf+0w > > 1) I know egrep is not a DB but does sqlite use such an inefficient > search algorithm for content that cannot be indexed? Why not reverting > to simple 'grep-like' methods? Or am I missing something trivial here? As a database, the file contains a LOT of structure. SQLite must follow the structure to locate the table, each record in the table, and expand the text field from its stored format. (The text itself is not a big deal, but the row/column that stores it must be found and extracted.) The data is not necessarily stored end-to-end sequentially in the file, as it's impossible to do that and still maintain all the necessary properties of a structured database. egrep gets to work with a flat text file, which it can easily read sequentially and get optimal performance from the OS's file buffering/cache management. It only needs to read a piece of the file and scan for patterns, repeating until done. The only structure it needs to be aware of is line breaks, but that is so simple it can be folded into the pattern scan itself. While someone would need to do profiling to examine exactly where the time goes, it would not suirprise me to find that SQLite's LIKE pattern matcher is more efficient than egrep, but that the overhead from dealing with structured data is responsible for the time difference. I don't find the time itself surprising at all. > 2) Why doesn't an index raise performance at all in this case? Is it > because non-latin chars are used? Careful use of an index should help for the specific query you posted (see http://sqlite.org/optoverview.html#like_opt), but it's not possible for an index to speed up arbitrary patterns. If you need to perform arbitrary pattern searches on a flat text file, SQLite (and most other structured storage for that matter) is simply the wrong tool for the job. grep and friends are highly optimized for just that purpose.
Re: [sqlite] Undefined collation: Peculiar observations ...
On 11/25/07, Ralf Junker <[EMAIL PROTECTED]> wrote: > Imagine that a SQLite3 database opened in a custom application with a > registered a collation sequence named "unknown" has created the following > table: > > CREATE TABLE a (b COLLATE unknown); > > Now open this table in the default SQLite3 CLI. Up to here, everything works > as expected. > > Now some peculiar observations: > 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, > in their most basic form and with no sorting or comparisons, do not: > > sqlite> SELECT * FROM a, (SELECT * FROM a); That's not just a subselect, it's also a join. Does a subselect on its own have the same behavior? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
On 11/26/07, arbalest06 <[EMAIL PROTECTED]> wrote: > John Stanton-3 wrote: > > All you need to do is to test the returned status of your sqlite3_step > > calls and if you get an error launch an SQL statement "ROLLBACK" and > > bail out of the transaction. If there are no errors you complete your > > transaction with an SQL "COMMIT". > i think its really a good solution to this problem. However, im required to > implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and > sqlite3_rollback_hook..im doing some prototyping to see their > functionalities but i really cant make it work..and i also cant find some C > sample source code using these apis..if it is possible, can someone post > some c code using these apis?.. SQL *is* part of the API of SQLite. There is no separate C API to implement transactions; they are part of SQL. The commit and rollback hooks are called when someone uses the relevant SQL statements. They cannot be used to do what you have described, a transactional batch insert. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing Application - SQLite Connection Creation/Sharing
On 11/23/07, Sabyasachi Ruj <[EMAIL PROTECTED]> wrote: > I have an application that uses SQLite extensively. > In a part of that application, I need to do the following steps:- > 1. I need to create a thread per request basis. > 2. Open SQLite connection. > 3. Then retrieve data from SQLite and do some calculation. > 4. Construct the result and send. > 5. Then close SQLite connection. > > This type of threads are being created very frequently. > And thats the reason my application is becoming very CPU hungry. > The following ideas came in my mind:- > 1. I can share open one SQLite connection and share the connection > between threads with PROPER MANUAL synchronization (so that only one > thread uses the connection at a time). > => This I ruled out because it will introduce unnecessary > waiting. > > 2. Create a pool of SQLite connections and allocate them the threads > on demand > basis. > => This is still in 'idea' phase. > I want to avoid the complexity that it will introduce in the > application. > > Is there any way by which I can optimize my application? That's really going to depend on what your application actually does. In general, for a high efficiency application the goal is to have exactly one thread per logical CPU running at any given time. You don't want 100 threads trying to run at the same time just because you have 100 users active at the moment. The most straightforward approach is to find the number of CPUs, create N worker threads (maybe a few more depending on I/O vs CPU distribution), keep an open sqlite connection in each one, and have them wait on a single queue for requests to answer. Your main thread will only be a manager, setting up requests and placing them on the queue, throttling requests to prevent backlog, etc. There is necessarily some complexity in this approach. What platform is this for? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite version for libc 2.1.3
On 11/22/07, Tara_Nair <[EMAIL PROTECTED]> wrote: > I just recently started to use Sqlite3 version 3.5.2 on an ARM-LINUX > based embedded platform. > Sqlite3-v3.5.2. has a dependency on libc, ld and libpthread versions 2.2.3. Er.. it should depend on whatever versions you link it against when you cross-compile for your platform. How/where did you get the version of SQLite you're trying to use now? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory Usage
On 11/19/07, John Stanton <[EMAIL PROTECTED]> wrote: > Joe Wilson wrote: > > If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no > > memory leaks, then garbage collection is irrelevant to the topic of > > memory fragmentation. It's not like C can employ a copying garbage > > collector that moves memory blocks after free() without the knowledge > > or participation of the host program. The malloc() call is where > > fragmentation happens. Fragmentation in malloc depends on your allocation > > strategy: first-fit, best-fit, short-lived versus long-lived pools, > > per-allocation-size pools, statistical prediction, etc. Malloc must > > try to guess where an allocation must go to try to prevent future > > memory fragmentation. > If you never execute a free your dynamic memory is essentially contiguous. Not necessarily, and that was his point about where fragmentation happens. Many of the common allocators maintain multiple size classes to reduce degenerative fragmentation under most workloads. If you allocate several different sizes, your allocations will in fact be spread all over the available memory pool, and therefore be fragmented without ever calling free(). Most common allocators are optimized to reach a steady-state quickly, so they have the least fragmentation necessary to handle most arbitrary workloads. That means putting up with some fragmentation so that applications that don't leak memory at the interface level will also not leak memory due to the allocator's internal management, no matter what allocation pattern they use. The allocation pattern used by the application can still affect how much fragmentation there is, of course. The overall point here, though, is that even commonly implemented malloc/free interfaces can be reliable enough to keep applications running for years without trouble. Completely deterministic behavior is not required when probabilistic determinism is sufficient. (Sorry, I just had to use big words there. IOW, building an application to average perfect behavior is fine when you don't need to guarantee perfect behavior at every arbitrary point. Most applications don't measurably benefit from such a guarantee.) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory Usage
On 11/19/07, John Stanton <[EMAIL PROTECTED]> wrote: > Malloc is a concept implemented in various ways, some more successful > than others but all of them hidden from the programmer. Free tries to > give back memory but as you can appreciate unless you use some garbage > collection scheme with backwards pointers fragmentation and > checkerboarding is very difficult to avoid. Malloc and free as a concept do not imply fragmentation; it depends on the implementation and how you use it. > Various OS's have ways of allocating memory under control of the virtual > memory manager so that it is not in the heap and can be completely > returned. This is no different than the malloc and free interface: you can easily fragment virtual address space. One form of allocator that is sometimes used in large task-oriented programs is an arena-style one. Essentially, a given task creates an arena and performs malloc/free as necessary within the arena. When the task completes, the entire arena is destroyed, much like cleaning up a stack. Thus no memory-related issue such as fragmentation is propagated between separate tasks. It can be quite reliable when used properly. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] returning Blobs in an oldstyle query ...
On 11/19/07, Rob Sciuk <[EMAIL PROTECTED]> wrote: > When using the sqlite3_query command, and a callback function, is there a > way of getting the _*STATEMENT*_ for the query string which is currently > executing, reliably from the sqlite3 *opaque type?? No. sqlite3_exec() is simply a wrapper around prepare/step/finalize; you can see what it does in legacy.c. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Threads
On 11/14/07, John Stanton <[EMAIL PROTECTED]> wrote: > Threads simulated in software are a kludge to better utilize current > processor and operating system architectures. In time machines where > the parallelism is handled in hardware will be more widely available and > the threading will be transparent and highly efficient. If the software task is expressed in a serial manner, like most current programming methods today, how exactly is the hardware supposed to magically parallelize it? Expressing tasks capable of being executed in parallel is very much a software problem. (That actually happens to some extent already, with out-of-order CPU instruction execution. Some of it is transparent, and some of it isn't: the various "memory models" implemented by CPUs drive a certain class of programmers nuts, since the problems are even harder than threading with shared state.) On 11/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > If you machine has a single disk it fundamentally does not have parallel > > I/O. If you have a machine with multiple dik spindles and multiple > > channels then you can have parallel access. Multiple Sqlite databases > > residing on the same disk are accessed sequentially because the access > > depends upon the disk head positioning. > It can be added that while disks can only perform one operation at a time, > modern disks have NCQ capabilities that enable them to reduce seek times by > using an elevator algorithm for example. > The OS is also performing some optimizations when queuing up several I/O > requests to the same device. Not to mention caching, by both the disk and OS. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] suggestion on improving performance on UPDATE
On 11/13/07, Benilton Carvalho <[EMAIL PROTECTED]> wrote: > Then each column is added on the fly, using: > > ALTER TABLE table ADD COLUMN colunm REAL; > > The columns in then filled used UPDATE as described previously. I haven't investigated the storage layout of this, but from what the docs imply, I suspect making use of ADD COLUMN will result in SELECTs being as slow as UPDATEs, possibly worse. When I said I expect the final queries to be faster, I was envisioning all of the columns created initially, just filled with zeros. I suspect filling them with NULL initially would make update performance worse also. I'll let others confirm/deny this, and comment on the performance of ADD COLUMN in general. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
On 11/13/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > If you use the idiom whereby each thread solely takes its tasks > from a thread-safe work queue, you can have a clear separation of > responsibilities and minimal or preferably no shared-state between > threads. You get concurrency as a side effect of this simple hybrid > event-passing/thread scheme. That model is one Windows NT has explicit support for. Its "I/O Completion Port" is essentially a message queue that worker threads can wait on for tasks. In cooperation with the scheduler, it tries to keep exactly as many threads as there are CPU cores active at any given time, such as by waking a new thread when a busy one blocks for I/O. In the context of sqlite, though, I don't see much point to sharing a single connection across threads. I'd prefer to just dedicate a message-based thread to the job. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] suggestion on improving performance on UPDATE
On 11/13/07, John Stanton <[EMAIL PROTECTED]> wrote: > The point is that the more statements you have in each transaction the > better, up to a limit of perhaps a thousand or so. Actually, I get the impression that number of statements is not really the problem here. On 11/13/07, Benilton Carvalho <[EMAIL PROTECTED]> wrote: > I'm in a situation where I need to handle 6 matrices with 1M rows and > about 2K columns, ie each matrix takes roughly 15GB RAM. > My (very naive) idea was to use SQLite to store these matrices (via > RSQLite package). So I have the following: > > CREATE TABLE alleleA (sample1 REAL, sample2 REAL > sample2000 REAL); > > When I have the data for sample1, I use an INSERT statement, which > takes about 4secs. For all 1 million rows? > For all the other columns, I use and UPDATE statement, which is taking > hours (more the 8 now). Using one UPDATE per row, identifying each individual row with SQLite's rowid? So to verify: you're building a 100 x 2000 matrix by column, and intend to process it by row afterward. Is there any way you can make the two operations use the same direction: either build by row or process by column? In physical layout terms, handling a matrix of that size in two directions is lousy in the efficiency department, as there's no room for effective caching. If you consider a storage format that stores a row at a time sequentially, then the first pass simply writes out rows of 2K zero values, in order, which is fine. In disk and OS terms, a file consists of blocks or pages, and it does I/O and caching in units of that size. We'll say 4KB pages for the sake of argument. So that first pass collects 4KB of data and writes it to the disk in one shot, which is about as efficient as you can get. When you decide to update a column at a time, then it's skipping around: modify a tiny value on the first page, skip 16000 bytes ahead (2000x 8byte REAL values) modify another tiny value, etc. The I/O cost of this is huge, since you're reading and writing an entire page just to touch one tiny little value. Since you never do anything else with that page, it gets evicted from cache pretty quickly, to make room for the following pages as you work through the file. That works out to roughly 4GB of raw I/O for each column updated. Repeat 2000 times. If you can't change how you do the operations, you'll have to find some storage structure that's more efficient. There are still some things to try, but it may simply mean discarding SQLite (and most other relational databases) as inappropriate for the job. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data encryption
On 11/12/07, Andreas Volz <[EMAIL PROTECTED]> wrote: > I think about to encrypt the data in my DB. Does sqlite offer a data > encryption on a lower level? Or should I encrypt my data before putting > it into the table on a higher level in my application without involving > sqlite? Dr. Hipp sells encryption support for sqlite: http://www.hwaci.com/sw/sqlite/prosupport.html Several others also maintain their own versions of sqlite with encryption support; I believe the .NET wrapper from phxsoftware uses the Windows crypto libraries, for example. The goal of all of them is to encrypt the entire database file, except for a small part of the beginning of the file that contains physical layout info. Encrypting specific data in your application may still be useful in some circumstances. When sqlite is encrypting the entire database, that means any application that uses the database needs to use the specific version of the sqlite library with the encryption support. If you have a situation where it's ok if other applications access most of the data, that's probably a bad thing, since you only need specific parts protected. The same holds true for pretty much any scenario where changing the sqlite library is inconvenient. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
On 11/12/07, Andreas <[EMAIL PROTECTED]> wrote: > Am 12.11.2007 um 14:40 schrieb Igor Tandetnik: > > I wonder - do you have any plans to eventually drop some of these > > views? Or are you just going to keep creating them, exploding the > > size of the database schema? In fact, why on earth do you need so > > many views in the first place? > hmm, honestly i planned a ON DELETE-Trigger as soon as the ON INSERT- > Trigger works ;-) to sum it up : i expect to gather 25-30 rows in the > config-table and ~200-250 corresponding rows in the files table. That > makes at worst 7500 rows in the files-table. That said, i see myself > far away from performance-issues. My question belongs to the area > 'research-while-developing' or maybe howto place as much logic into > the DB, not polluting my apps-source lines with SELECT-staements. I'm > used to do things dynamically and usually use OODB's with python. For > this tiny project it's JS and sqlite and things work different here - > but it's gone work. In general, DDL should not be a common operation in an SQL database. The SQL schema should be fixed, using a relational model that fits what you need to do with your data, and queries do the actual data manipulation and retrieval. In sqlite terms, those VIEWs will actually come at a performance cost: every time the database is opened, sqlite must parse the schema to determine the layout of the database. There is no performance to be gained from using them, and the example above does not appear to simplify application queries at all (which is the entire point of using VIEWs). You've simply exchanged a column identifier for a table identifier. As you said, you're not yet near performance issues, and there's obviously room to do whatever you want, the above is just general guideline. I'd call creating such VIEWs as the above to be bad design, unless there's a detail you left out that makes them more appropriate. You mentioned using OODBs, so I think I understand where you're coming from, although I've never used anymyself. You might try to find what some common ORMs (Object-Relational Mappers) do. They're essentially middleware that converts between an object and SQL data. The "column=foo" bit is the type of thing they do behind the scenes. Your project sounds small enough that I would probably just bite the bullet and use SQL myself, rather than trying to use some abstraction layer. It'll take some mental adjustment, but it's probably worth it just to learn the SQL way of doing things. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Read and write in SQLite
On 11/9/07, Joanne Pham <[EMAIL PROTECTED]> wrote: > I have an application to writh to database with begin transaction and end > transaction. > Before end transaction another application try to connect to the database to > read the data and in the middle of the read of the second application > the first application isues the end transaction. Because the read is still > reading the data the first application return an error message that "Couldn't > end the transaction ..." ( I don't remember the exactly error message) but > the first application couldn't "end the transaction" becuase the read of the > second application. That sounds like a message returned by a wrapper you're using. > So What you said is if the second application is connected to the database > while the first application is writing then the error message SQLITE_BUSY is > return back to application and not connect to the the database right. When the first transaction tries to COMMIT, it will get SQLITE_BUSY because the second transaction is still using the database file. The first writing transaction should simply wait a short time and try again, in a loop. When the second reading transaction is done, the first will be able to COMMIT. If you don't want concurrency like this at all, so that there can only be one transaction using the database at any time, you can use BEGIN EXCLUSIVE for all transactions. If BEGIN EXCLUSIVE returns SQLITE_BUSY, then another is using the database and this one must wait. If it succeeds, no others can use the database until this transaction is done. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Read and write in SQLite
On 11/9/07, Joanne Pham <[EMAIL PROTECTED]> wrote: > I knew that SQLite doesn't allow concurrency for both read and write at the > same time. > My application is written in C++ so Is there any way that we can check if > there is any connection to the database so the second connection needs to > wait. For example my application write to the database and other application > is reading data from database so both read and write need to check if there > is any connection to the database before making the connection to the > database. I'm not clear on what problem you're trying to solve. If SQLite cannot do something now it will return SQLITE_BUSY from most calls. What other behavior do you need? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
On 11/9/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I put up 4 variations. Please, everyone, offer your opinions: > >(1) http://sqlite.hwaci.com/v1/ No CSS of any kind. >(2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners >(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners >(4) http://sqlite.hwaci.com/v4/ CSS font specification only > > (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. > That leaves me with (4). I like (1); my browsers' default fonts suit me perfectly, TYVM. (4) is therefore ugly and harder to read for no good reason. That holds true for all 4 of my primary browsers across two different platforms, incidentally. (2) and (3) feel heavy/slow, and pulldown menus are irritating to navigate. They also do not render correctly with larger font sizes. You cannot properly account for things like mobile browsers unless you make a *lot* more effort on designs like (2) and (3). If you have to expend significant effort to handle style in even just different desktop browsers, the style is not worth pursuing. Don't waste your time; keep the site simple. Those advocating more complex designs or just installing Firefox need to remember 3 important things: 1) All the web is not viewsed on a desktop. 2) All the desktops are not 2+ GHz monsters. 3) Firefox is a slow pig of a browser. See 2. Considering SQLite is a lightweight, embedded database engine, these points are extremely relevant to your target audience. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
The documentation page is too scattered, and needs to be categorized: short bits, like the intro and features lists; reference material, like the SQL syntax and API pages; and detailed discussion, like the locking methods and the like. I echo P Kishor's comment that the date/time functions need to be moved from the wiki to the docs, and that goes for anything else on the wiki that's stable. For the most part, I hate wikis. I find some random wiki on the internet, and it has no organization, the quality is dubious, the search pages suck, etc. When I want tinformation, I go straight to the official documentation. Even if there's a "wiki" link in the site's menu, I ignore it as irrelevant. SQLite's wiki, on the other hand, is quite useful. That means it needs to be promoted: it should be mentioned on the main documentation page, along with examples of what information you can find there (users, tools, enhanced versions, examples, etc). It should also be mentioned why it's on this separate "wiki" thing: it is open to real-time user contributions to help stay current (or something). If you can integrate user-contributed comments into the reference material, similar to PostgreSQL's release documentation, I believe that would be useful too. It's a way to get data that's on the wiki now closer to where it's needed. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Disk caching impacts performance.
On 11/8/07, Julien Renggli <[EMAIL PROTECTED]> wrote: > As I said, the "nasty" trick works and we'll live with it (writing our > own FileSystem is not an option). We would just like to understand > better what's happening, to know why and when the "first run" is so much > slower. And should we read the whole file beforehand, only parts of it? > Does it depend on how much RAM is installed, ...? If you have any hints > they are welcome. I guess I should ask NTFS experts as well. What you've discovered is pretty accurate: when the delay is due to disk I/O, pre-reading the file will load it into the OS's disk cache, so subsequent accesses are faster. It will depend on available RAM, not only what is physically installed but also memory pressure from other running applications, other disk I/O in progress, various OS settings, etc. There isn't any way to accurately predict it. Reading the entire file when the OS is unwilling to cache all of it will simply result in only part of the file being cached. It may also cause other data to be pushed into the pagefile, slowing down applications when they later access their own stale data. In the worst case, it could slow everything down for a short time. Reading the entire file from start to finish is pretty much the only effective way to pull it into cache. An ifstream is not the most efficient way to do that, since it does its own buffering, but that's not important as far as the disk caching effects are concerned. VACUUM removes internal fragmentation and writes rows in order, which helps make disk I/O more sequential. You may be able to one-up it by inserting data in the order you intend to access it (but create any indexes afterward). Beyond that, I'm not aware of anything that would help. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is possible put a password in database ?
On 11/8/07, Jonas Sandman <[EMAIL PROTECTED]> wrote: > How much is it for the encrypted version? The info on that is here: http://www.hwaci.com/sw/sqlite/prosupport.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is possible put a password in database ?
On 11/8/07, paulito santana <[EMAIL PROTECTED]> wrote: > is possible i put a password in the file that represents a SQLite database > ?? There is any instruction in SQLite command line that allows this ? No. drh sells a version of SQLite with encryption support, and some others maintain their own versions. The free standard version has no such support though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
On 11/6/07, John Firebaugh <[EMAIL PROTECTED]> wrote: > > You're confusing threads with the context of the connection. When you > > call sqlite3_open() you get a single connection to a db. That > > connection can then be used across threads, but it is for all intents > > and purposes a single line of communication with a database (using it > > twice at the same time doesn't somehow multiply that relationship). > > Thank you for attempting to clarify this for me. You are saying that the > transaction state is part of the connection context, whereas I was > expecting it to be a per-thread-per-connection state. > What else, besides transaction state, is part of the connection context > and shared with shared connections? I know of the page cache, of which > sharing is desired. Anything else to be aware of? To put things another way: there is no per-thread behavior in SQLite. Everything works on a per-connection basis. (The only new thing is that SQLite is more threadsafe than it was before.) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Affinity modes
On 11/4/07, Ken <[EMAIL PROTECTED]> wrote: > In order to maximize compatibility between SQLite and other database > engines, SQLite support the concept of "type affinity" on columns. The type > affinity of a column is the recommended type for data stored in that column. > The key here is that the type is recommended, not required. Any column can > still store any type of data, in theory. It is just that some columns, given > the choice, will prefer to use one storage class over another. The preferred > storage class for a column is called its "affinity". > Seems pretty specific and explanatory to me. He's referring to section 6 of http://www.sqlite.org/datatype3.html which says otherwise. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > If your platform has a file modification notification mechanism, you > > may be able to sleep on that instead. Of course the problem with this > > approach is that it's only a coarse-grained "something changed" > > notification, and doesn't tell you what changed. You may be able to > > counter that by having the trigger store a note about what changed in > > a separate table that your GUI queries when it wants to know > > specifics. > Perhaps, but that may just move the problem. The "GUI information" > table might not be committed before the GUI process wakes up to find out > what happened. If it's created in a trigger, it will be part of the same transaction as the updated data. The database file won't be modified until commit time. Depending on the platform, it may fire the notification before the commit has finished writing data, but since sqlite holds an exclusive lock on the file during that time, the GUI process will see SQLITE_BUSY and can simply sleep a short time and retry. You'd have to handle that case anyway, as another writer might start committing before the GUI process has a chance to respond to the previous notification. > > If you don't have a cheap file notification, something more creative > > might be necessary. Do you control the sqlite library used by the > > data writers? Perhaps you can modify it to provide notifications > > immediately after commit instead. (I'm assuming you don't want to > > modify the writers themselves directly, by having them signal after > > they issue a COMMIT.) > I control the code on both ends. I looked at the sqlite3 code, and the > places to modify didn't exactly jump off the page at me. :) > What about sqlite3_commit_hook()? Though the (very few) examples I've > managed to find don't make it clear how to figure out the rowid that was > updated. And without that, I can't easily determine which semaphore I > need to post to... Can I pass the database handle as the argument to > the callback? It's a pre-commit hook, so it has the same problem as the triggers. I haven't tried modifying the code to add a post-commit hook, but sqlite3_commit_hook() is actually a good place to start: in main.c it sets db->xCommitCallback, which is called by vdbeCommit() in vdbeaux.c. It looks like you could add a call at the end of that function, after testing needXcommit. You'll still want the triggers to note what specific data changed, but you could simply have them store some state in a global variable that your new post-commit function checks to decide what semaphore to post to. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
Terms like "foreign" and "federated" conjure up images of physical tables that reside in external databases, which is not at all what this feature is. It's simply an interface that looks like a table. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: > My application is a mobile platform with a GUI that wants to display > frequently-updated data in a database. The GUI is a separate process > from the one providing the data, and is one of several consumers of that > data. > I prefer not to poll for changes, because the system is > performance-constrained. So instead, I'm using an AFTER UPDATE trigger > in the data-generating process to launch a C function that posts to a > semaphore. The GUI process is thus unblocked, and it then does a SELECT > to get the data. I'm using the C/C++ API everywhere. > > The problem I'm seeing is that the GUI process is getting stale data in > its SELECT, unless it does a brief sleep between the sem_wait() and the > sqlite3_exec(). Specifically, the value returned is the value > immediately before the UPDATE. It's as if the trigger in the > data-generating process and the query in the GUI process are both > running before the new value is actually committed to the database file > and/or whatever caches are in between. The trigger necessarily fires immediately after the row change, but before the transaction is committed. It's the committing that takes most of the time in a database write operation. So yes, definitely expected behavior. If your platform has a file modification notification mechanism, you may be able to sleep on that instead. Of course the problem with this approach is that it's only a coarse-grained "something changed" notification, and doesn't tell you what changed. You may be able to counter that by having the trigger store a note about what changed in a separate table that your GUI queries when it wants to know specifics. If you don't have a cheap file notification, something more creative might be necessary. Do you control the sqlite library used by the data writers? Perhaps you can modify it to provide notifications immediately after commit instead. (I'm assuming you don't want to modify the writers themselves directly, by having them signal after they issue a COMMIT.) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB managers that do searches?
To help the conversation along a bit, phpMyAdmin is a web-based administrative and design interface for MySQL. I don't use it so I don't know exactly what kind of searching the OP is looking for either, but at least we know the type of tool :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
I wrote: > On 10/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > On win32, we have to initialize mutexes at run-time, but this > > can be done within a contrived mutex that we build off of > > a static integer using InterlockedIncrement(). And mutex > > initialization apparently never fails on win32, so we do not > > have to worry with reporting errors that occur during > > mutex initialization. > > That isn't actually true, but handling that particular, rare > out-of-memory error condition that can occur with CriticalSections is > so amazingly inconvenient that most people don't even bother trying :) I realize how fragile that sounds, so to expand a bit: the default behavior is for the process to die immediately. It's sort of like getting a signal when you hit a ulimit on unix. There's no danger of silent failure and mysterious problems later. I'd consider it to be in roughly the same class as handling running out of stack during execution. Not a bug, just not something designed for. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
On 10/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > On win32, we have to initialize mutexes at run-time, but this > can be done within a contrived mutex that we build off of > a static integer using InterlockedIncrement(). And mutex > initialization apparently never fails on win32, so we do not > have to worry with reporting errors that occur during > mutex initialization. That isn't actually true, but handling that particular, rare out-of-memory error condition that can occur with CriticalSections is so amazingly inconvenient that most people don't even bother trying :) > But there are other operating systems using SQLite that do > not work this way. They need a way to initialize mutexes > (and possibly other objects such as malloc) prior to running > any SQLite interface. And the initialization needs to be able > to fail and return an error code. > > To accomodate this need, we are considering an incompatible > API change to SQLite. We are thinking of requiring that an > application invoke: > > int sqlite3_initialize(...); > > prior to using any other SQLite interface. (The parameters to > sqlite3_initialize() are not yet designed.) It will be an error > to use any other SQLite interface without first invoking > sqlite3_initialize() exactly one. It is also an error to > invoke sqlite3_initialize() more than once. Some thoughts: * Definitely a major version change. * In order to correctly handle dynamic library scenarios, there needs to be a pair of initialize/finalize functions, and they need to be counted. The first intitialize() does the work, the rest simply increment a counter. The last finalize() does the work, the rest simply decrement. * For future flexibility, I'd suggest making one of the arguments to initialize() be a pointer to a struct. The first member of the struct should be a version indicator, like the VFS interface. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
I wrote: > I would still perform rollbacks for any errors other than the above > expected SQLITE_BUSY cases, of course, since they indicate something > else went wrong (such as running out of disk space). I think it's > safe to say those are all unusual cases though. Hmm, http://sqlite.org/lang_transaction.html suggests even SQLITE_BUSY might result in automatic transaction rollback. sqlite3_step() suggests the same thing. Other docs suggest that SQLITE_IOERR_BLOCKED is actually returned in such cases (like a writing transaction failing a cache spill attempt). Which is correct? Silently rolling back a transaction automatically is bad enough, but doing that on SQLITE_BUSY would be just plain evil. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: > Perhaps the best solution is to follow these rules: > > IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db): > -- > (1) Begin the transaction with 'BEGIN IMMEDIATE'. If you get SQLITE_ > BUSY, it means that another writer is already accessing the db. Just > sleep awhile and retry, as many times as necessary. Once you get > SQLITE_OK, you're the only writer accessing the db. > > (2) If you get SQLITE_BUSY later in the transaction when you want to > write your cache to disk, it can only be due to one or more readers > holding SHARED locks (because you're the only writer). They will > eventually exit without trying to acquire any stronger locks (again, > because you're the only writer). Therefore, no deadlock is possible. > Just sleep awhile and retry, as many times as necessary. > > IF THE TRANSACTION IS A READER (i.e. will only read from the db): > - > (1) Begin the transaction with a simple 'BEGIN'. This starts the > transaction in the UNLOCKED state, so you *cannot* get a SQLITE_ > BUSY at this point. > > (2) If you get SQLITE_BUSY later in the transaction, it can only be > due to your trying to acquire a SHARED lock while some other process > holds a PENDING or EXCLUSIVE lock. That process will eventually > finish its writing and exit. Therefore, no deadlock is possible. > Just sleep awhile and retry, as many times as necessary. > > > If all transactions follow the above rules, then explicit rollbacks > should never be necessary. For avoiding deadlocks, yes. It can also be abstracted so the rest of your code works on just "read-only" and "read/write" transactions, which is convenient. I would still perform rollbacks for any errors other than the above expected SQLITE_BUSY cases, of course, since they indicate something else went wrong (such as running out of disk space). I think it's safe to say those are all unusual cases though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: > But am I correct in assuming that one way that SQLite provides > serializable transactions is by automatically rolling back > transactions when necessary (and returning SQLITE_IOERR)? No. That will happen in some specific unavoidable cases, but you cannot assume it will happen all of the time. What will most likely happen instead is that the first INSERT or UPDATE in a transaction will return SQLITE_BUSY, and you will have to roll back the transaction yourself. It's possible to continue within a transaction by ignoring an error from an individual statement (depending on the nature of the error), is why I make this distinction. It's also possible to simply keep retrying a failing statement until it succeeds, but in the case of INSERT or UPDATE that may lead to deadlock, as two connections that want to write cannot proceed until one of them yields. On the other hand, if you get SQLITE_BUSY on COMMIT, you probably want to retry it, as it's just waiting for other readers to go away first. Automatically rolling back then would be bad :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
On 10/28/07, Michael Ruck <[EMAIL PROTECTED]> wrote: > I'd suggest putting this into the documentation of > sqlite3_last_insert_rowid(), that > the call is not reliable in scenarios such as this one. It might be appropriate to just stress it only works for successful INSERTs. I'd just assumed that was true anyway. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/26/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > This is true of SQLite because isolation in SQLite > is "SERIALIZABLE". This is the highest level of isolate > provided by SQL. Most client/server database engines > by default implement "READ COMMITTED". The value of > "balance" might change between the SELECT and the > UPDATE in MySQL, for example. (I'm less clear about > what happens in PostgreSQL and Oracle. The point ism > that your mileage may vary so be cautious.) PostgreSQL supports both READ COMMITTED and SERIALIZABLE, but the out-of-box default is READ COMMITTED. It can be selected per-transaction, or changed as a default for all transactions. I believe Oracle is similar. MySQL is a little weird due to different storage engines. From what I can tell, MyISAM doesn't actually support transactions, so you essentially get READ UNCOMMITTED if you try; InnoDB supports all 4 levels and defaults to REPEATABLE READ. The differences between MVCC (Multi-Version Concurrency Control) and locking -based implementations can also show up in corner cases, so it's something you definitely want to look at whenever examing a database engine. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/25/07, Richard Klein <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > The thing is, SQLite's synchronization mechanism is simpler than most > > full-featured SQL databases. In all cases, the point is that if you > > are attempting to do simultaneous writes from two connections, each > > connection must be prepared to receive an error, rollback the > > transaction, and try again. Personally, I don't see anything terribly > > complicated about that. > > It's not complicated at all. It's just an aspect of database programming > that I was unaware of, until now (I'm a database newbie). You're also implementing SQLite's locking system for your own platform, so you need to be concerned with these details anyway. You're not just blindly implementing it, you're also verifying your understanding of the goals and taking the time to question SQLite's behavior to make sure everything is correct. I think that's absolutely wonderful. I just wanted to point out to anyone seeing this thread and thinking "eek" that from the perspective of using SQLite one doesn't need to be concerned with these details, because the original implementors, and people like yourself, keep verifying those details do what they're supposed to do. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly complicated about that. Concerning yourself with the intricate details in order to get the best performance is complicated, sure, but the basic principle isn't. You have to do error checking anyway, the only difference is that it may affect your entire transaction rather than just one statement. That's true for working with SQL databases in general. For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. Adding your own mechanisms on top just means another thing for you to test independently, which seems like adding complexity. I do recognize that none of the above necessarily applies to specific environments, where you need to do your own thing anyway. I'm just commenting that in general, SQLite is already simple to use. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > As far as I know, Postgres does not have a virtual engine. I could be wrong. It's not a virtual machine style, where it has a specific instruction set; instead it's more like a graph of operations. Execution means walking a graph instead of interpreting an instruction stream. It's still an abstract virtual engine, just implemented differently. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > One other question, when a query is issued, does SQLite cache the results, so > that future queries can be processed off the cache (I think not) Like the "query cache" in some other databases? No. SQLite does have a cache of database pages, but they mimic what's on disk, not the results of a particular query. A query cache would not be very useful for an embedded database. If you're caching results, you might as well do it in the application's native form -- it's the same process after all. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Accessing external applications from within SQLite triggers
A couple other things come to mind here, that might be relevant to what you're doing: * A trigger that has a "final" side effect, like signaling another process, will both have that effect early (before the transaction is committed), and will have that effect even if the transaction is later rolled back. * Since database modifications are serialized, the file is updated when a change is committed. You might be able to use the OS's notifications of file modification to determine when to check for data changes. Of course, this won't tell you _what_ changed, which might not help if you're only watching for a tiny item in a large database. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Accessing external applications from within SQLite triggers
On 10/13/07, Vladimir Stokic <[EMAIL PROTECTED]> wrote: > I tried to do what you and Igor said, but I found out that it does not really > work that way. I can make a user-defined function, but it stays active only > while the current connection is open. It is not persisted in the "database". > From this test, I can just deduce that I have to define the user-defined > function each time I open any connection, which does not look like a good > solution. Correct. The database contains data, not your application code, so there's nothing else it can do. If you need a centrally managed database, an embedded database like SQLite doesn't help you much, unless you want to create the central management yourself. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What is RESERVED state for?
On 10/12/07, Richard Klein <[EMAIL PROTECTED]> wrote: > (1) Can more than one process have a PENDING > lock on a database? (I'm guessing that the > answer is "no". My guess is that if one > process has a PENDING lock on a database, > then all the other processes that have > opened that database have either no lock > or a SHARED lock on the database. I'm > further guessing that when all the SHARED > locks eventually drop away, the PENDING lock > is automatically promoted to EXCLUSIVE. > Do I have this right?) Correct. > (2) What is the purpose of the RESERVED lock? > Since it doesn't seem to lead (directly or > indirectly) to the PENDING or EXCLUSIVE state, > and since it doesn't prevent other processes > from acquiring SHARED locks, what is it good > for? A RESERVED lock indicates a process is planning to write to the database file sometime in the future, so everyone else must be read only. It blocks future writers. A process holds a RESERVED lock as long as it has changes to make (because it has executed an INSERT or similar), but those changes are still held within its own cache and no COMMIT has been executed, so it doesn't need to actually modify the file yet. New readers (SHARED) are therefore okay. It will be promoted to PENDING when it must modify the file to proceed. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
I wrote: > On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > > >> Is this a garbage collection situation, where you want a row in B to go > > >> away when all referring rows in A are deleted? > > > > Yes exactly this is what I wanted :) > Maintaining a reference count in table B may be more efficient from an > I/O standpoint, but you'll probably have to do checking within app > code (instead of using SQL triggers directly), and it would likely > only pay off if table A has both a vey large number of total rows, as > well as many references to the same key in table B. It's also not as > clean from an SQL standpoint, since it's an additional item that must > be tracked, yet has nothing to do with your actual data dependencies. After thinking a bit more about implementing this, a pair of triggers can accomplish it without doing anything special in the app: CREATE TRIGGER DeRef AFTER DELETE ON A BEGIN UPDATE B SET refcount = refcount-1 WHERE key = OLD.refkey; END; CREATE TRIGGER DelOrphans AFTER UPDATE ON B WHEN NEW.refcount = 0 BEGIN DELETE FROM B WHERE rowid = NEW.rowid; END; I still favor the simpler approach, but at least this one is workable. I also choose lousy trigger names :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > >> Is this a garbage collection situation, where you want a row in B to go > >> away when all referring rows in A are deleted? > > Yes exactly this is what I wanted :) Okay. A trigger on table A can accomplish it without any extra tracking: CREATE TRIGGER CleanUpOrphans AFTER DELETE ON A WHEN NOT EXISTS (SELECT 1 FROM A WHERE refkey = OLD.refkey) BEGIN DELETE FROM B WHERE key = OLD.refkey; END; If table A has an index on the refkey column, it should be reasonably efficient. Maintaining a reference count in table B may be more efficient from an I/O standpoint, but you'll probably have to do checking within app code (instead of using SQL triggers directly), and it would likely only pay off if table A has both a vey large number of total rows, as well as many references to the same key in table B. It's also not as clean from an SQL standpoint, since it's an additional item that must be tracked, yet has nothing to do with your actual data dependencies. Keeping a list of referring rowids within a table B row would be hard to maintain; you'd probably instead end up with a relationship table that maps keys in table A to keys in table B -- the same thing you'd use for a classic many-to-many relationship. I don't see this being a win in any form, since it's essentially just a manual version of an index on table A's refkey column. I'd start with the no-extra-tracking approach, and only test other methods if it turns out to be too slow for your data. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > We got two tables, tableA & tableB. > > tableB is turning out to be a many-one relation where in we have many rows of > tableA mapping to one row of tableB, would like to know what is the best way > to delete a row in tableB > > 1. Keep a reference count of the number of rows that are referring to this ( > to be honest I dont think this is good) > 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Is this a garbage collection situation, where you want a row in B to go away when all referring rows in A are deleted? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems with SQLite and PHP
On 10/8/07, Markus Wolff - NorthClick <[EMAIL PROTECTED]> wrote: > I find this to be a bit irritating - shouldn't it be enough that the > server can dive into that subdir and find a file it can actually write > to within the dir? How does it make sense that the entire directory > containing the directory must be writeable? Is this an SQLite > requirement or a PHP quirk? SQLite needs to be able to create and delete a journal file during database modification. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Threads in 3.5.0
On 10/4/07, Michael Scharf <[EMAIL PROTECTED]> wrote: > The difference is: with threads you have to take action to > protect your data structures from being accessed by other > threads. With multiple processes, you have to take action to > access the data of the other process. That *is* a difference. But that's only true for specific implementation details (like VM space). If your data is a file (like sqlite ;) there's typically no difference at all. Localized file buffering can actually make the multiple processes case even more unpredictable than multiple threads. As another example of how the model is separate from the implementation, consider CLI/.NET AppDomains. They effectively behave like separate processes in terms of data/state separation, yet may share a single thread of execution. It's a rather bizarre state of being if you try to categorize it purely from the implementation side. > http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf I skimmed this; it looks like he's mostly talking about the model, but manages to confuse things with implementation details sometimes. It keeps the discussion from getting too theoretical and gives people something to recognize when they read it, but I'm not sure it helps with clarity at all. I'll read it more thoroughly when I get time. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Threads in 3.5.0
On 10/4/07, Jonas Sandman <[EMAIL PROTECTED]> wrote: > Well I am certainly a newbie in this context, threads must certainly > be used when writing Windows application since you are not at liberty > to splinter your process like you can on a *nix based system, no? It's just that the classic Unix environment encourages multiple processes, and the Windows environment encourages multiple threads, when you have parallel tasks to perform. There aren't any fundamental issues that prevent you from using either method in either environment, as appropriate. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and html character entities
I should also mention, a text editor I like to use is SubEthaEdit (I have the old 2.2 version), and it supports switching encodings via the Format menu. If you're switching to find the encoding of an existing file, just choose Reinterpret when it asks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and html character entities
On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote: > On 9/20/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: > > On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote: > > > Lucknow:~/Data/ecoservices punkish$ less foo.csv > > > "the first record" > > > "\351 \347 \361 \356" > > > "more from 3rd row" > > > "row four" > > > "these \223volunteered\224 activities" > > > "<\341 \370 \343 \374 \356 & others>" > > > foo.csv (END) > > > - > > > > Note that this is *not* UTF-8. If you're still using this as test > > data, you need to get rid of it and use UTF-8 encoded data instead. > this is where I lost you... when you say "this" is not UTF8, what is > "this"? The data in the file shown by less, and since sqlite3 exported that data exactly as it was stored, the data in the db as well. > All I want is that I want (1) the user to be able to type ç in > the web form, and (2) I want to be able to save ç in the db. (3) Then > when I look at that data, either on the command line, but definitely > back on the web, I want it to appear as ç. (4) If I export it, I > should still be able to see it as ç and not something else. > > Seems like I was able to do 1, 2, and 3 with my test case, but not 4 > (I got \347 instead ç). > > Also, in my production case, 1,2, and 3 are not very reliable. Are you > saying my data above are not UTF8? If so, I would like to know how you > can tell that, so I can recognize it in the future myself. Also, I > would like to know how I can do what you are suggesting I should do, > that is, how can I ensure that I "use UTF8 encoded data"? Okay, first a quick primer on character sets and encodings. A byte can hold one of 256 different values (0-255), and most processing tends to happen on bytes, so it makes sense that individual characters should be stored as individual bytes. First we have US ASCII, the character encoding standard that defines 128 characters, including the basic english alphabet, numbers, and some punctuation (www.asciitable.com). However, this obviously doesn't cover all the symbols in common use, or characters from other languages, so more definitions are needed. Given that a byte supports twice as many values (ASCII takes up only half), that leaves 128 values for other purposes. Many other character sets keep the bottom half as ASCII, and assign different characters to the top 128 values. The ISO-8859 family of standards works this way. ISO-8859-1 is also known as Latin-1, and is most common for languages that use characters similar to English, Spanish, etc. It adds a few more symbols (copyright, paragraph, etc) and some common characters with diacritical marks (like é ç ñ î). The data you posted above was entered into your database using this encoding (or Windows-1252, which is identical except for adding some characters in places 8859-1 does not use). ISO-8859-2 is also known as Latin-2, and covers another set of European languages (such as Romanian). It contains a different set of symbols and characters with diacritical marks needed for these languages, characters that don't fit in 8859-1. It keeps going, of course (Wikipedia has info: http://en.wikipedia.org/wiki/Category:ISO_8859). There are many other encodings that work this way, and collectively they're known as single-byte encodings: they all represent a character as a single byte, but the actual meaning of that byte depends in the character set in use. This situation is ripe for confusion, since interpreting a sequence of bytes as being in a different encoding than it was stored in will lead to strange results. This is exactly what you saw in your Cocoa editor, since it defaulted to using the classic MacRoman encoding, which uses those same byte values to store uppercase characters instead. It gets worse: there are multi-byte encodings too. You typically see these in the East Asian languages, since they don't use the same alphabetic writing system, and instead have thousands of characters to encode. A byte only supports a mere 256 values, so more than one byte is needed to represent a single character. By now you can see how this can spiral into an unmaintainable mess: you have to worry about this encoding and that encoding and you can store the encoding with the text but what do you do if someone requests data in another encoding and what if they are using a specific encoding but that text only contains ASCII characters and therefore everyone should see it anyway and how do you tell the difference and *brain asplode* Enter Unicode, which has the goal of putting all the world's commonly used language characters and symbols into one single character set. By using Unicode, you don't have to worry about which ch
Re: [sqlite] SQLite and html character entities
On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote: > sqlite> select * from tbl; > the first record > é ç ñ î > more from 3rd row > row four > these "volunteered" activities > <á ø ã ü î & others> > - > sqlite> .mode csv > sqlite> .output foo.csv > sqlite> select * from tbl; > sqlite> .q > Lucknow:~/Data/ecoservices punkish$ less foo.csv > "the first record" > "\351 \347 \361 \356" > "more from 3rd row" > "row four" > "these \223volunteered\224 activities" > "<\341 \370 \343 \374 \356 & others>" > foo.csv (END) > - Note that this is *not* UTF-8. If you're still using this as test data, you need to get rid of it and use UTF-8 encoded data instead. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error in SQLITE on applyNumericAffinity
On 9/12/07, Stéphane Thiers <[EMAIL PROTECTED]> wrote: Somehow I missed replying earlier, sorry about that. > The column which contains these numbers is > declared as REAL. This raises another question to > me: I thought that the numbers were stored as > strings, so why sqlite would try to transform these strings into double? By declaring the column as REAL, you provided a type affinity. That means sqlite will try to convert any provided input into REAL form (which is double), and only store the original type if that fails. If you don't declare a type for the column, then the value you input will stay in the same type you provided it in (i.e. string). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
On 9/16/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > I've found a remark regarding write concurrency in SQLite: > > "All SQLite write operations obtain an exclusive lock on the whole database" > ( http://www.mail-archive.com/sqlite-users@sqlite.org/msg18342.html ) > > Doesn't it mean, that database is protected enough already by it's internal > design, and there's no need to take care about that "dot locks"? The default locking mechanism relies on the underlying filesystem to provide the needed locking guarantees. In this case, the OP is needing to access a database on a networked filesystem, and many networked filesystems are unable to provide proper locking. So no, if the underlying filesystem is "broken", the database is not protected. Dot files replace the locking mechanism with a convention that skips the filesystem, but the consquence is that if anything else accesses the database file without respecting that convention, corruption may occur. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error in SQLITE on applyNumericAffinity
On 9/12/07, Stéphane Thiers <[EMAIL PROTECTED]> wrote: > I got this stack error in SQLITE when executing the following request: I assume there's an exception that goes with this; what is it? > "INSERT INTO _scalars_float > VALUES(237,220,'ComputedNoise','Noise','Noise > level estimated by the smoothing > algorithm','DouglasPeuckerAlgorihtm',0,0,NULL,'-1.79769e+308','1.79769e+308','%.2f','') If this is part of C-style code, what value was used as an argument for "%.2f"? > mscorlib.ni.dll!793612c2() > mscorlib.ni.dll!79361141() What are you using the CLR for in this process? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 9/7/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > I'm currently using a transaction for this on the database side and > rolling it back if the file cannot be deleted. But what if the file is > gone and then SQLite says it doesn't accept my records? Since we're > inside a transaction, integrity checks should be deferred until a > COMMIT. Is there a way to tell whether the COMMIT will succeed under the > current conditions so that I can safely delete the file? What integrity checks? You could also take the approach of maintaining a "delete journal" inside the database itself, but controlled by the application. E.g. write a record to a table that says you're deleting file foo as part of the transaction, and when the COMMIT succeeds, your application can delete the file. If something happens between the commit and delete, your application can consult the journal to find out what file was supposed to be deleted, and pick up where it left off. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to iterate on SELECT query results
On 9/3/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > After doing some SELECT operation on a TABLE, say we get 100 result > items out of 1000 records, Is there any way where I can iterate > through this result set. i.e., Get N items out of 100, say get > previous 10, get next 10, etc, > > This should be done without creating a temporary table or virtual > tables. As there is overhead of space and time. There is always the overhead of space or time, as it's impossible to know what the results are unless you actually calculate them. To get row 100, the query must find rows 1-99 first. The LIMIT and OFFSET clauses of the SELECT statement can be used to get parts of a query without storing anything. This is the worst case of time overhead though, since the entire query must be run every time. An approach that works for many people is to store markers for use in relative queries. This requires a column with unique, ordered values, and that you run the query using it as an ORDER BY. An INTEGER PRIMARY KEY AUTOINCREMENT column is useful for this; let's assume you have one named ID. If you retrieve rows 101-110, you can store the IDs for row #101 (let's say this is ID 234), and row #110 (ID 280). When you need to retrieve the previous 10 rows, you get them backwards using a query such as: SELECT * FROM table WHERE id < 234 ORDER BY id DESC LIMIT 10; This avoids having to retrieve rows for the entire result set when changing "pages" sequentially. You can imagine other schemes that involve storing all the IDs for the query result set so that you can jump to them at random, etc. > If it is possible to use VIEWs then how can I? As I think VIEWs are > little better than temp table or virtual table. Please correct me if > I'm wrong. Better for doing what? A view isn't going to help with paging result sets. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
On 9/2/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > Why a VIEW doesn't contain a ROWID field. Even though if it is > accessed, it contains (null) value. How to initialise this or how to > make it work as in TABLE. The rowid field exposes SQLite's internal storage mechanism for table rows. It has certain properties, such as being unique and relatively stable. Views are just stored queries which may reference many tables or calculate columns from other data, but do not store any data themselves. Since a view does not store a row, a rowid simply doesn't exist. It's not possible to create one. If you want table behavior, then use a table. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 9/2/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> select * from test where column in
> ('98451234','9845123','984512','98451',
> '9845','984','98','9','-1') limit 1;
>
> even when limit 1 is provided it continues to search.
Continues to search in what way? What exactly are you looking at?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> I want to know why
> prepareStatement: select * from xxx where IN (?);
> stmt.bind("abc,xyz,123"); is not supported for multiple
> values.
It's not supported because it doesn't make sense. The parametric
binding mechanism is for single values; it's not a macro-like text
replacement system. With your syntax, how do I bind a set of
integers? Strings? Blobs?
One common use for parametric binding (besides convenience) is to
avoid SQL injection attacks. The example you posted doesn't do that;
you have to manually escape each individual value to make sure it's
valid syntax for the IN() group in text form. Why even use parameters
in that case? It's the same amount of work whether you build the
entire SQL statement or not.
All common databases I'm aware of work exactly the same way.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite3 Database Pointer multithreading / concurrency
On 8/29/07, Ben Mann <[EMAIL PROTECTED]> wrote: > The problem is that connection A is calling a function which is attempting > to open a new SHARED connection. Since it can't (B's PENDING prevents this, > because of A's earlier SHARED connection), A blocks. > > If instead, A does not open a new connection, but uses what it already has > open, it all works like a charm. > > I'm beginning to think this is expected behaviour. What you described about the locking is correct. The threading isn't strictly relevant; locking is done on a per-connection basis, not per-thread. Note that the only reason it blocks is because you're using sqlite_busy_timeout(). If you don't use that, you can detect and handle the (immediate) SQLITE_BUSY return. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Skype client using SQLite?
On 8/28/07, Jeremy Hinegardner <[EMAIL PROTECTED]> wrote: > On Tue, Aug 28, 2007 at 03:13:47PM +, [EMAIL PROTECTED] wrote: > > In reference to > > > >http://www.sqlite.org/cvstrac/tktview?tn=2592 > > > > This is the first public indication we have had that > > Skype is using SQLite in their windows clients. However, > > the person who wrote the ticket seems to be a bit confused. > > Can any able hackers in the SQLite community confirm that > > the Skype windows client is using SQLite? It would be > > nice to add them to the page of high-profile users. > > I can confirm that there is a sqlite db in my Skype on Mac OSX > > In the file $HOME/Library/Application\ > Support/Skype//dyncontent/bundle.dat Also confirmed on Windows. It appears to contain downloaded UI data -- icons, tips, promotions, etc. It's notable that the contact list, chat history etc are using something other than SQLite. Hard to tell if what's in that ticket is actually from Skype or not, but at least they're using SQLite for some things. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
You're confused about the locking; see http://sqlite.org/lockingv3.html On 8/26/07, John Stanton <[EMAIL PROTECTED]> wrote: > How about the case of: > BEGINsets reserved lock on thread one No lock. > SELECT promotes lock to shared on thread one Thread one acquires SHARED lock. > BEGIN sets reserved lock from thread two No lock. > SELECT promotes reserved lock in thread two to shared Thread two acquires SHARED lock. > ...at this point two threads are simultaneously processing SELECTs > INSERT tries to promote shared lock to exclusive on thread one Thread one acquires RESERVED lock (upgrade from SHARED). > but fails because second thread holds a shared lock Does not fail, as this is merely an intent to write and changes are currently buffered. > My understanding is that once a reserved lock has been promoted to > shared, a further reserved lock can be set. That opens the possibility > that the concurrent transactions can conflict when they try to each set > a write lock. All active readers have SHARED (reading) locks. A writer acquires a RESERVED (intent to write) lock, which blocks all future writers. Readers are not blocked. The writer buffers changes in memory. When a writer needs to commit (or spill) changes, it acquires a PENDING (need to write ASAP) lock while other SHARED locks exist. A PENDING lock blocks all future readers. When all SHARED locks are gone, it acquires an EXCLUSIVE (writing now) lock long enough to commit the changes. For two reading transactions that decide to write at the same time, one will fail with SQLITE_BUSY. The successful writer will not be able to commit until the failed writer ends the transaction. The failed writer will not be able to write until it ends the transaction and starts a new one. The successful writer may later encounter SQLITE_BUSY if it has to spill or commit changes while readers still exist. It is safe to retry under the assumption that the readers will eventually finish. However, the _first_ writing failure must not be retried due to the above, which will lead to deadlock. BEGIN IMMEDIATE replaces the first writing failure: if BEGIN IMMEDIATE succeeds, all writes that fail with SQLITE_BUSY (due to cache spill or commit) are safe to retry without deadlock, since only readers are present and they will eventually finish. If BEGIN EXCLUSIVE succeeds, there will never be SQLITE_BUSY failures. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Locking queries
On 8/23/07, Pavan <[EMAIL PROTECTED]> wrote: > As per my understanding I see that SQLite supports only database locking, as > opposed to table/row locking. Does anyone know if you can read from a locked > database ? (i.e. if one application has locked the database for writing, > then can another application read/query it whilst its locked? No, however the blocking time for readers is made as short as possible. See http://sqlite.org/lockingv3.html for the details. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Aggregate and query limit
On 8/19/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > http://archives.devshed.com/forums/showpost.php?p=5772144=8 I've seen some interesting things out of web forums and mailing list gateways, but having all of the capital 'O's go missing is a new one... http://www.mail-archive.com/sqlite-users@sqlite.org/msg15096.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd error on "BEGIN IMMEDIATE"
On 8/17/07, Scott Derrick <[EMAIL PROTECTED]> wrote: > exec a "BEGIN IMMEDIATE", with a sleep loop if I can't acquire the > reserved lock. > > Then prepare, step, finalize, exit the function > > When I come back into the function and exec a "BEGIN IMMEDIATE" I get an > error > > "Cannot start a transaction within a transaction". > > Whats wrong? Doesn't sqlite3_finalize(stmt), release the locks, > deletes the prepared statement and causes the database to be updated? It deletes the prepared statement. The statement's action would have been performed by a previous sqlite3_step(). Locks and database updates are another matter, because... > Why does the engine think I'm still in a transaction? ...you started a transaction with BEGIN. Unless the statement you stepped is a COMMIT, your transaction isn't done yet :) Exec a COMMIT at the end of the loop. Incidentally, you can keep the prepared statement around if it's appropriate. You can open the database, prepare the statement, and only step() and reset() within the loop. This way you don't have to keep preparing it over and over again. You must finalize() it before closing the database, though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ~/sqliterc example/documentation avaible?
On 8/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > You can put any command seen in ".help" in the sqlite3 shell > or any SQL command in ~/.sqliterc, such as: ...and of course, I would completely forget about the shell. Oops. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ~/sqliterc example/documentation avaible?
On 8/10/07, Brickl Roland <[EMAIL PROTECTED]> wrote: > where can i find information about how the config-file should look like and > what is possible? There is no ~/sqliterc or config file. SQLite is an embedded database engine; applications use it in application-specific ways. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FW: Security Problem C/C++
On 8/6/07, Severin Müller <[EMAIL PROTECTED]> wrote: > I'm trying to use SQLite3 for my File, because I thinks it's a great API and > easy to use. But I'm having trouble to use the library properly. > When i run the Program, I get get the following Error: > Unhandled exception… so its some kind of a segmentation fault. Which exception, exactly? > And my Debugger is tracing the error in a file called "gs_support."… I believe gs_support is part of VS's buffer overflow security checks (mostly stack-based). It's possible you have an error in your own code that is only triggered when you try to call out to sqlite3. It's also possible there's something wrong with the way the sqlite3 library was built; did you compile it yourself or are you using a precompiled version from somewhere else? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a c++ newbie question
On 8/5/07, Stephen Sutherland <[EMAIL PROTECTED]> wrote: > I am trying to treat a string before passing it through my SQL statement > into the database. > > I know that a single apostrophe will break the SQL statement. > So I have to replace them all to double apostrophes. > But are there any other characters that will break the SQL statement ? > I actually have a situation where the user creates an XML file and the > contents of the XML file gets dumped in the database. So there is opportunity > for a hacker to create an XML file which has some SQL statements in it like ' > DELETE TABLE X ; > > So any thoughts or existing code would be great. Don't attempt to treat strings at all. Instead, always use the parametric binding API for whatever database you're using. You prepare statements like "INSERT INTO table VALUES (?)", and then pass in the input string as a separate argument for the database engine to put in place of the "?". This avoids the entire problem of escaping special characters, and you don't need to treat your input data specially. For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text(). http://sqlite.org/capi3.html should get you up to speed on the process, and browse through the other documents on the site for more information. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE INDEX that is case insensitive?
On 7/30/07, Chase <[EMAIL PROTECTED]> wrote: > Right now, when i do a select in sqlite that is supposed to be in > alphabetical order, i get: > > DC > Da > De > Do > We ultimately will be creating an index for this column anyway, so > let's just jump ahead and talk about creating an INDEX which would spit > out: > > Da > DC > De > Do You can use the COLLATE clause when creating the index (or the table column, or with ORDER BY in a query). See http://sqlite.org/datatype3.html#collation for available collations. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how do i generate a uniqueidentifier ?
Hm, something ate the last part of my message... > Note that SQLite contains a decent PRNG which is well seeded, > if you are running on Mac or Linux. (We need to work on better > seeding on Win32...) The crypto framework on recent versions of Windows can be used to get a good quality seed. I'm not sure it's worth trying to do better on older versions. It's something that can be done dynamically, much like the Unicode filename stuff. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how do i generate a uniqueidentifier ?
On 7/27/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Trevor Talbot" <[EMAIL PROTECTED]> wrote:
> >
> > > > sqlite> select "{" || hex(randomblob(4)) || "-" || hex(randomblob(2))
> > > > || "-" || hex(randomblob(2)) || "-" || hex(randomblob(2)) || "-" ||
> > > > hex(randomblob(6)) || "}";
> > > > {4EA4A842-6ACA-0A81-5B42-F9E1D295BC27}
> >
> > Note that this is *NOT* a GUID/UUID. Be very sure of your
> > requirement: if you need a GUID, write a function that uses your
> > platform tools to get one. If you just need some long random
> > identifier, the above is fine, but don't call it a GUID. See RFC
> > 4122.
> Please reread RFC-4122, especially section 4.4. While Chase's
> guid is technically in violation of RFC-4122 in that it contains
> 4 extra bits of randomness, it is very close. And in a sense,
> the extra 4 bits of randomness provide a stronger GUID than
> RFC-4122 specifies.
The reason RFC 4122 defines the format that way is to avoid collision
of the randomly generated version with other versions. Random
generation of GUIDs is weaker than the timestamp-based version, which
is actually guaranteed to be unique rather than merely
probabilistically unique. (Assuming perfect implementations, that is
-- I'm aware of the related practical problems of MAC number reuse,
clock sequencing storage, etc.)
If you don't pay attention to RFC 4122's defined format, what you're
generating simply isn't a GUID, and you run the risk of bad things
happening if you try to interoperate with a system that deals with
GUIDs.
Hence my warning :)
> I personally find all the syntax in RFC-4122 to be annoying
> and so I usually do my GUIDs using hex(randomblob(20)). But
> I guess that is just personal preference.
Yeah, if all you need is something that's unique with extremely high
probability, there's not much sense in bothering with syntax designed
for working with other systems. You already know how to meet your
needs, the rest is just pointless overhead.
> Note that SQLite contains a decent PRNG which is well seeded,
> if you are running on Mac or Linux. (We need to work on better
> seeding on Win32...)
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] how do i generate a uniqueidentifier ?
On 7/27/07, Chase <[EMAIL PROTECTED]> wrote:
> hhmmm... i just noticed something weird. if i insert a string of ANY
> length or format into the uniqueidentifier field, sqlite allows it...
> so i guess it's just a text type under the hood with no rules enforced
> on the format of the string inserted into it.
sqlite datatypes are not like other database systems you may be used
to: http://sqlite.org/datatype3.html
> not sure if i like that.
Browse through the docs to make sure sqlite is actually what you want :)
> > sqlite> select "{" || hex(randomblob(4)) || "-" || hex(randomblob(2))
> > || "-" || hex(randomblob(2)) || "-" || hex(randomblob(2)) || "-" ||
> > hex(randomblob(6)) || "}";
> > {4EA4A842-6ACA-0A81-5B42-F9E1D295BC27}
Note that this is *NOT* a GUID/UUID. Be very sure of your
requirement: if you need a GUID, write a function that uses your
platform tools to get one. If you just need some long random
identifier, the above is fine, but don't call it a GUID. See RFC
4122.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] problem with date/time
On 7/26/07, JJ <[EMAIL PROTECTED]> wrote: Hi, I've solved the problem at last. One thing I didn't know that creating DirectX device will lower float point precision, it is written in their documentation... So creating DX app and running float calculations is no, no... :) Ah, interesting. Thanks for posting this, good to know the problem! - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this SQL command supported?
On 7/24/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: That's a real pumper. How do you alter constraints on existing tables ??! You can't. You would need to create a new table with the desired constraints, copy the data into it, drop the old table, and rename the new one. alter table assetdownload add constraint assets_assetdownload_FK1 foreign key (assetId) references assets (assetId) ON UPDATE CASCADE ON DELETE CASCADE; Also note that this entire set of constraints won't do what you want: sqlite does not enforce foreign keys. (Triggers can get you most of the same things if needed.) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] problem with date/time
On 7/24/07, JJ <[EMAIL PROTECTED]> wrote: Yes it were returning same number all the time, I've broke down calculations of UTC to Julian date type and saw significant floating point errors (rounding) in calculation, it couldn't hold such numbers and to me seemed to ignore double data type, but used float instead. (i've played through bunch of compiler settings, pragmas, etc with no significant gain). Actually tried my own calculation which operates with smaller numbers, but in the end when result should be stored, numbers got messed again. Damn, I didn't meant to spend 4 hours on this... Hmm, that's worrying. I've abandoned msvc compiler and using trial intel compiler for now... No problems on new compiler with calculations.. Would you be willing to post a small test case (or send to me privately)? Along with the exact compiler version and CPU. I very much want to figure out exactly what this problem is, since it's supposed to work. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this SQL command supported?
On 7/24/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote:
I wonder if there any SQL commands that are not supported by SQLite. I
am trying to read an entire DDL script file as a string and pass it as
one SQL command to SQLite. The same script worked fine in Firebird batch
execution. But with SQLite I get an SQLite exception:
{"SQLite error\r\nnear \"DOMAIN\": syntax error"}
DOMAINs are not supported.
http://sqlite.org/lang.html
http://sqlite.org/omitted.html
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
