Re: [sqlite] Removing the 'Delete message(s)' confirmation.

This list is mostly about SQLite database engine. And it has neither Configuration, nor Preferences, neither messages, nor confirmation of their deletion. If you're talking about some GUI tool for SQLite (there're many of them) then you better name what you're talking about. Pavel On Tue, Nov

Re: [sqlite] Asynchronous I/O and shared cache

>> So, does it possible to have more than one shared cache within a single >> process ? > > Open the same database twice, using two different handles. At least I think > it will work. Nope, it won't. That's the purpose of shared cache: if you open the same database several times with different

Re: [sqlite] Asynchronous I/O and shared cache

Shared cache instance is created on file-by-file basis, i.e. if you open connections to file1.db and file2.db they will have different cache instances and any manipulations with these database files won't influence one another at all (any write operations can be executed in parallel). But if you

Re: [sqlite] Asynchronous I/O and shared cache

I don't know what Dan meant by his words but AFAIK there's no mutex making exclusive grab of shared cache by sqlite3_step() call. There is only mutex making sqlite3_step() execution exclusive for connection object. Pavel On Wed, Nov 18, 2009 at 8:40 AM, presta wrote: > > I'm

Re: [sqlite] another Feature Request: char from codepoint?

> The blob (x'41' is a blob literal) is expected to contain a UTF-8 sequence, I > believe. I think it should be a database encoding which is either UTF-8 or UTF-16. So for Tim's case if his database encoding is UTF-16 insert statement can be like this: insert test (id, myTextColumn) values(1,

Re: [sqlite] SQL error: disk I/O error and symlink

> That should not be a problem if I use symlink to access database for > reads only and > actual db file name for updating, isn't? I believe there can be a problem with such scenario. I don't know exactly but I doubt that file system can operate with locks on file handle open via symlink and via

Re: [sqlite] Asynchronous I/O and shared cache

gt; > On Nov 18, 2009, at 10:03 PM, Pavel Ivanov wrote: > >> I don't know what Dan meant by his words but AFAIK there's no mutex >> making exclusive grab of shared cache by sqlite3_step() call. There is >> only mutex making sqlite3_step() execution exclusive for connection >

Re: [sqlite] Doubt in Trigger

> is it possible to have[insert or update] ? No. http://www.sqlite.org/lang_createtrigger.html Pavel On Thu, Nov 19, 2009 at 8:53 AM, greensparker wrote: > > hi im using Before TRIGGER for validation PURPOSE (before inserting) > > i just want to know , is there any

Re: [sqlite] Suggested user-defined-function example

> can what Walter is doing be done in other languages, namely Adobe Flex > AIR/ActionScript? I don't know Flex's API but I bet you have documentation for that and can find there whether it has registration of user-defined functions or not. > Is "return [expr { $a + $b }]" also written in TCL?

Re: [sqlite] Difference between two dates fails

strftime('%d', ...) returns you day of month. So you're subtracting two days of month disregarding the month they belong. So what are you expecting to achieve in this way? Maybe you need to use '%J' instead of '%d'? http://www.sqlite.org/lang_datefunc.html Pavel On Fri, Nov 20, 2009 at 11:15 AM,

Re: [sqlite] CEROD Extension Linking Error

Try to add -ldl to g++ flags like this: g++ -o localVal Topic.o LinguisticDataStore.o StringUtil.o CppSQLite3.o LocalValidator.o -L../Debug -lpthread -lsns -lcommon -ltagger -ldl -lboost_regex-mt -lZThread ../Debug/libsqlite3.cerod.fedo.a /usr/lib/libz.a Pavel On Fri, Nov 20, 2009 at 3:00 PM,

Re: [sqlite] begin transaction

According to SQLite sources: ** The pager invokes the busy-handler if sqlite3OsLock() returns ** SQLITE_BUSY when trying to upgrade from no-lock to a SHARED lock, ** or when trying to upgrade from a RESERVED lock to an EXCLUSIVE ** lock. It does *not* invoke the busy handler when upgrading from

Re: [sqlite] begin transaction

w.sqlite.org/c3ref/busy_handler.html. Pavel On Mon, Nov 23, 2009 at 10:17 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > > On Nov 23, 2009, at 9:26 PM, Pavel Ivanov wrote: > >> According to SQLite sources: >> >> ** The pager invokes the busy-handler if sqlite3OsLock

Re: [sqlite] begin transaction

B's 'item not found in locker!' is a read > operation.  so possibly i've (inadvertently) hit the 'shared lock upgrade to > reserved lock' immediate fail. > > thoughts? > > thanks > tom > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto

Re: [sqlite] ATTACH & Shared cache

What difference does it make for you? What do you call "shared cache" here? Two different files cannot share cache anyway because they're different files with different pages. Or do you call caches shared if they can use the same memory? Then cache in SQLite is always shared. Or do you ask whether

Re: [sqlite] ATTACH & Shared cache

> So if I want to perform a query with the connection with the 2 db attached > does the query will search data to the shared cache to have the fresh data ? Yes, this 3rd connection will use the same cache as 1st connection for db1 data and the same cache as 2nd connection for db2 data. Pavel On

Re: [sqlite] trigger on update bug in 3.6.20?

> create table t1(_id integer primary key, v integer, d integer); > CREATE TRIGGER t1_trig BEFORE UPDATE ON t1 > BEGIN >update t1 SET v=OLD.v+1 WHERE NEW._id=OLD._id AND NEW.d!= OLD.d; > END; I guess your trigger does something different from what you wanted to do: it changes value of v in

Re: [sqlite] trigger on update bug in 3.6.20?

same _id). So you're hitting exactly this explained undefined behavior. Pavel On Tue, Nov 24, 2009 at 3:06 PM, Vasu Nori <vn...@google.com> wrote: > On Tue, Nov 24, 2009 at 11:25 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > yes it is risky, in general. but in t

Re: [sqlite] multiple threads with shared cache mode

Indeed, it's weird. And I've just realized that if we have two simultaneous write transactions they both have to write their own journal whenever they wish to write something to disk. SQLite database cannot have two different journal files, so it should serialize transactions whenever they want to

Re: [sqlite] trigger on update bug in 3.6.20?

Tue, Nov 24, 2009 at 8:27 PM, Vasu Nori <vn...@google.com> wrote: > On Tue, Nov 24, 2009 at 12:28 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > I am not what you mean by Oracle's Before triggers have different >> concept? >> > care to explain? >> &

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

Does this answers question? sqlite> create table log (t); sqlite> create table t1 (a); sqlite> create table t2 (a); sqlite> create trigger tt1 after update on t1 begin ...> insert into t2 values (new.a); ...> insert into log values ("update of t1, a="||new.a); ...> end; sqlite> create

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

m to fire is considered successful. Otherwise all triggers checking some constraints and prohibiting incorrect data (and thus raise(...) function) are useless. Pavel On Wed, Nov 25, 2009 at 8:59 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 25 Nov 2009, at 1:38pm, Pavel Iv

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

keys too. See section 4.2 here: http://www.sqlite.org/foreignkeys.html. Pavel On Wed, Nov 25, 2009 at 9:13 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote: > >>> I couldn't find the answer documented anywhere, so I wil

Re: [sqlite] Foreign Key constraint as column constraint?

According to http://www.sqlite.org/lang_createtable.html you can mention foreign-key-clause (starting with REFERENCES) as column-constraint. Why it doesn't work for you? Pavel On Wed, Nov 25, 2009 at 10:33 AM, Jan wrote: > Hi, > > I am testing the new fk support in my db.

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 26

> reasons. Can primary key values available because of deletes get re- > used at some point? I guess you're talking about rowids not about general primary keys. For rowids reuse is possible only if rows with maximum values of rowids are deleted and only if your INTEGER PRIMARY KEY column doesn't

Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

> Because there are several process who use the database. I have another > question:Could I close the database of other process in main process? Just use your favorite IPC mechanism and write your application so that main process sends message to other process and when other process receives it

Re: [sqlite] Convert Access sql to SQLite sql

Your Access query doesn't have good equivalent in SQLite. Your options are: 1. Execute SELECT separately and then for each row in the result issue an UPDATE with necessary values (UPDATE can be prepared and you can just bind all values). 2. Insert results of SELECT into some temporary table and

Re: [sqlite] Multiples natural left joins problem

If using "natural left join" is not a requirement for you then this works as you expect it: select movies.id, title, rating, tag from movies left join user on movies.id = user.id left join tag on movies.id = tag.id; Pavel On Thu, Dec 3, 2009 at 9:49 AM, Yuzem

Re: [sqlite] Multiples natural left joins problem

and tag value. After that you won't have to deal with left joins and with simple "natural join" you won't have such problem... Pavel On Thu, Dec 3, 2009 at 12:30 PM, Yuzem <naujnit...@gmail.com> wrote: > > Thanks both for the replies. > > Pavel Ivanov-2 wrote:

Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key

Maybe select distinct bar.* from foo, bar where foo.col2 = bar.col2 and foo.col3 = bar.col3 It's not clear from your requirements written below whether you need 'distinct' here or not but I've added it just in case... Pavel On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni

Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key

ber of rows in each table is VERY > LARGE (lets say 50,000) would my solution maybe outperform the first > (on the surface seems like n^2 vs n*S where S is concat string length > (which will always be < 50)). > > On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov <paiva...@gmail.com>

Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

{ >    ... >    signal (SIGUSR2, (void*)sig_handler); >     > } > > void sig_handler(int sig) > { >    ... >    switch(sig) >    { >        case SIGUSR2: >            ... >            sqlite3_interrupt (db); >            ret = sqlite3_close (db);  // is blocked here >    

Re: [sqlite] sqlite help requested to support this requirement

> SQLite is an implementation of SQL. SQL is used to store information in a > way that allows you to look things up more quickly than searching a flat > file. If you can search for data your flat files fast enough to make your > software work acceptably, you do not need SQL and your project

Re: [sqlite] sqlite help requested to support this requirement

more about concepts of relational databases. Pavel On Fri, Dec 4, 2009 at 8:01 AM, Pronab Ganguly <pgangul...@gmail.com> wrote: > Thanks a lot for all help suggestion.Let me know if you have any experience > on file parsing using sqlite. > -P > > On Fri, Dec 4, 2009 at 6:15 PM

Re: [sqlite] Generic indexes on FTS3 table

> (2) Is there a way to create FTS virtual table that only holds the > fulltext index and not the data itself? (This way I'd able to solve > the problem by creating a separate FTS3 table and hooking triggers on > r/w queries). Can you exclude text fields from your table, put it into separate FTS

Re: [sqlite] sqlite3_exec() returns SQLITE_OK but Database showsdifferent result

> 1) You are opening a different file than the one you think you are opening. > E.g. you are using a relative path to the file, and the workding directory is > not what you expect it to be. > > 2) You are starting an explicit transaction (see BEGIN) and forgetting to > commit it. 3) You are

Re: [sqlite] char,ascii function in sqlite

As Simon said use substr() to get first character and use cast(X'FF' as text) to convert some hexadecimal character code to symbol (in my example the code is FF = 255). But there's no way to convert character into its code, so you cannot do any arithmetics with it though you probably don't need it

Re: [sqlite] Cache size tuning

Maybe 'pragma cache_size'? Pavel On Fri, Dec 4, 2009 at 10:05 PM, Richard Klein wrote: > Does SQLite provide any tools to help the > developer tune the database cache size? > > Thanks, > - Richard Klein > ___ > sqlite-users

Re: [sqlite] Where is CASE documented ?

Apparently document writer assumed that it's pretty obvious how CASE works because it works the same way in all DBMSes. :) You can read any documentation Google gives, e.g. this: http://msdn.microsoft.com/en-us/library/ms181765.aspx. In brief, when you give optional expression then CASE works

Re: [sqlite] BUG: the rowid column in view is automatically named as id

It's been said in this list not once already: unless you're using "as ..." to name the column it's not guaranteed to have any particular name you expect it to. So it's not a bug. Also: sqlite> create table test (id INTEGER PRIMARY KEY, a text); sqlite> insert into test (a) values (1); sqlite> .h

Re: [sqlite] [sqlite-dev] query on sqlite3_open16

According to documentation on CreateFile() function: "In the ANSI version of this function, the name is limited to MAX_PATH characters. To extend this limit to 32,767 wide characters, call the Unicode version of the function and prepend "\\?\" to the path." SQLite is already calling CreateFileW()

Re: [sqlite] SQL question

> So for a set of measurements identified by everything but their name > select the latest rows as defined by the timestamp. > > Can I do that in SQL in one query? Maybe this is what you want: select Name, Value, CreateTS from TableName where Oper = 'op' and Category = 'cat' and Product =

Re: [sqlite] Bug when uUsing Parameters with views

Apparently result of count() and probably all other aggregate functions (as well as result of any function at all) has no affinity. So when you compare it to anything having no affinity too you have no type transformation during comparison. And thus values have to be exactly the same including

Re: [sqlite] EPC Based Search

> Why not just make epc column "unique on conflict ignore"? > i.e. > CREATE TABLE readmode( epc text unique on conflict ignore, col2 text... ); > >> one EPC ( say:'e2003411b802010994095761' i.e. 24char ) out of 5000 records. >> and select query take 2-3 sec. to give result output. >> >> By any

Re: [sqlite] Bug when uUsing Parameters with views

t should take on the > affinity > of the value, no ? AH, GOT IT. values do NOT have affinity. so I would > either need to cast the expression OR the value. > > is this correct ? > > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-

Re: [sqlite] pragma database_list

pragma database_list returns record set as any select statement. So you can capture this record set from C code as you do with any other select statement and based on that make your checking as you need. Pavel On Tue, Dec 15, 2009 at 12:58 PM, Angelo wrote: > Hi to

Re: [sqlite] EPC Based Search

out except create index on it. > > > Pavel Ivanov-2 wrote: >> >>> Why not just make epc column "unique on conflict ignore"? >>> i.e. >>> CREATE TABLE readmode( epc text unique on conflict ignore, col2 text... >>> ); >>> >>>> o

Re: [sqlite] Compiling SQLite as .lib increases project size and build time?

> 1>LINK : fatal error LNK1181: cannot open input file > '..\release\sqlite.lib' Is this filename something that you wrote yourself in configuration of your project? If you compile something as dll you don't need to mention its library as additional linking source in dependent projects. Pavel

Re: [sqlite] Compiling SQLite as .lib increases project size andbuild time?

> If I remove the dependency on sqlite from HouseKeeper, it gives linker > errors with unresolved symbols for sqlite3_* functions. Adding sqlite as > a dependency to HouseKeeper causes it to look for the sqlite.lib file. Make sure that .def file is included in you Sqlite project. When compiling

Re: [sqlite] Error in retreiving DATETIME('NOW')

> pls tell me, why  DATETIME() function gives wrong time? http://www.sqlite.org/lang_datefunc.html "Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. Universal Coordinated Time (UTC) is used." Use

Re: [sqlite] Compiling SQLite as .lib increases projectsizeandbuild time?

> > -- > It is set to generate a .lib according to that setting, but from what Pavel > said, it never does because the .def file is missing from the project. I've > never heard of .def files before so this is a new issue for me. This is all > quite exciting :) > > Thank

Re: [sqlite] Error in retreiving DATETIME('NOW')

> but i want to use my localtime as default time in sqlite(without giving > 'localtime'). is it possible? No. And documentation clearly says about that. Pavel On Thu, Dec 17, 2009 at 11:08 AM, greensparker wrote: > > SELECT DATETIME('NOW','localtime');  is WORKED >

Re: [sqlite] usage of indexes - query performance

As you said because of your LEFT JOIN SQLite (or any other DBMS in its place) is forced to use t2 as a base table. You have no conditions on t2, so SQLite will make full scan on it and for each row it will need to pick up a corresponding rows from t1 which it does using primary index. And FYI, by

Re: [sqlite] ambiguous column name

> You have three distinct columns here - t1.a, t2.a and t3.a. With left joins, > it's possible for some but not all of them to be null, so it matters which > one you select. > > Even with inner joins, it may matter which column you pick. E.g., in SQLite > it's possible that a=b but typeof(a) !=

Re: [sqlite] Problem with cast(xx as YY)

>     The behavior of cast(XX as YY) is not the same as the old version, such > as 3.3.4, and also different to SQL Server or  MYSQL. Don't complain that SQLite's cast works the other way than SQL Server or MySQL. SQLite behaves in a very unique way when the type-related matters are a concern.

Re: [sqlite] Help with Insert using select

Why this doesn't work? insert into table (creationdate, modifieddate, mytext, title) select date('now'), date('now'), "show text", "show title"; Pavel On Wed, Dec 23, 2009 at 11:24 AM, Christopher Doss wrote: > Hello, I have a table that I'd like to create that needs

Re: [sqlite] Problem with cast(xx as YY)

ame the same. Pavel On Thu, Dec 24, 2009 at 12:40 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 24 Dec 2009, at 1:17pm, Pavel Ivanov wrote: > >> But this behavior is definitely a bug: > > You might change the behaviour by defining the type of the column: &

Re: [sqlite] Using incremental BLOB I/O when processing result set

> The implementation for the SQLite ODBC driver does not use incremental > BLOB I/O, it reads a BLOB into memory for each row > fetched. Not much use if the size of each image in the SQLite database > is 3gb and you only have 2gb memory. I'm looking for a solution that > will work on BLOBs larger

Re: [sqlite] SQLitedb problem

What's the problem with the answers already given? Pavel On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous wrote: > Hello, > > I wait for your help. Please can you answer to me ASAP. > > Thanks, > Othman. > > On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous

Re: [sqlite] SQLitedb problem

hey are not removed. > > I think that these data are ignored. So how data can be ignored on a > sqlitedn file? How can i fix this problem so i can view them on my iphone or > SQLite Database Browser. > > I thank you in advance for your return. > > Othman. > > On Mon,

Re: [sqlite] SQLitedb problem

i doesn't receive on my inbox answers from the mailing list. > > Is there any answers? > > Othman. > > On Mon, Dec 28, 2009 at 6:05 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> >> 1. Don't reply directly to me. If you write your question to the >> maili

Re: [sqlite] Which is faster raw file I/O or sqlite BLOB

It depends on numerous facts. I'd say if size of all your files is measured in megabytes and you're not dealing with thousands of files in the same directory then raw file I/O will be faster. If size of files is measured mostly in tens or hundreds of bytes and you need thousands and millions of

Re: [sqlite] Using incremental BLOB I/O when processing result set

> Its a shame that a BLOB handle is not returned as a result of a query > rather than the BLOB itself. Let me correct you. It's not a shame, it's database specifics and it has its good points. For me personally I'd hate if SQLite returned me some abstract handle if I requested blob value. Note:

Re: [sqlite] selective result columns

> I don't have access to that level of software, so that's the problem. You mean your experience with SQLite is based on using sqlite3 command line utility only? If so you can just redirect its output to sed which will do something like s/|\+/|/g. If your experience with SQLite is based on using

Re: [sqlite] Requirements for index-aware INSERT SELECT

> INSERT OR IGNORE INTO Table (Field) SELECT SomeOtherField FROM OtherTable > ORDER BY SomeOtherField > > produced significant reduce in data flow. (Field Is indexed in Table). I > don't think the difference is related to some caching since the variant > without ORDER BY shows 50 MB data transfer

Re: [sqlite] Confusing FAQ(26) wording

To be honest I'm a bit confused about the wording of these two bullets too. But here're my comments and clarifications: >>     Perhaps you are referring to the following statement from SQL92: >> >>         A unique constraint is satisfied if and only if no two rows in >>         a table have the

Re: [sqlite] selective result columns

a java application.  The application > allows me to run an SQL query, then it renders the output into HTML.  I want > to avoid showing empty colums in the HTML output, so I wanted the > include/exclude column logic in my actual SQL statement. > > > > > Pavel Ivanov-2 wrote: >> &

Re: [sqlite] Is there a way to "predict" autoincrement rowid range

max(rowid) + 1 before the insert is a good approximation for the left bound. But if you want to know the exact value you can remember max(rowid) + 1 before insert and execute this after insert: select min(rowid) from table_name where rowid >= remembered_value It will be guaranteed to give you

Re: [sqlite] Is there a way to "predict" autoincrement rowid range

>  At the very least, do the initial INSERT and get-last in a >  single transaction. Not necessarily. You can do INSERT and get-last without starting transaction because get-last is per-connection, not per-database. So the only thing that should be taken care of is no inserts are executed on the

Re: [sqlite] Creation of a sqlite database in VC++

Use an API to open a database. If database file does not exist at the time of opening then it will be automatically created (unless you try to open it for read-only access). Pavel On Thu, Jan 7, 2010 at 3:43 PM, gary clark wrote: > Hi, > > I'm looking for an API in

Re: [sqlite] [sqlite-dev] Why I always got error SQLITE_MISUSE 21 /* Library used incorrectly */

Oh, and one more thing: sqlite-dev list is for questions related to developing SQLite itself. For problems with using SQLite you should write to sqlite-users list (put in CC). Pavel On Fri, Jan 8, 2010 at 7:50 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > As you're new to SQLite

Re: [sqlite] Associating items of a column with one item of another column

Probably these commands would help: CREATE TABLE temp_forms (form TEXT, root TEXT); .separator "|" .import forms.txt forms CREATE TABLE roots(id INTEGER PRIMARY KEY, root TEXT); INSERT INTO roots (root) SELECT DISTINCT root FROM temp_forms; CREATE TABLE forms (form TEXT, root INTEGER); INSERT

Re: [sqlite] [sqlite-dev] Why I always got error SQLITE_MISUSE 21 /* Library used incorrectly */

ensure i > compile with this option? > > The second question is when i met SQLITE_MISUSE error, can i reset or reopen > the database to avoid this error? > > Regards, > Tim > > > Pavel Ivanov-2 wrote: >> >> Oh, and one more thing: sqlite-dev list is for qu

Re: [sqlite] [sqlite-dev] Why I always got error SQLITE_MISUSE 21 /* Library used incorrectly */

for a temp solution, I will open the database again if the > application detect there is a SQLITE_MISUSE, is this solution safe enough? > > Thanks, > Tim > > > > Pavel Ivanov-2 wrote: >> >>> I am now using SQLite version 3.3.5, and i cannot find flag >

Re: [sqlite] Limitation on Column count

> so normalization would lead to a doubling > of the storage space (add a measurement_id to each measurement). My strong belief is that when you try this normalization you'll see that such doubling of storage is a good enough trade-off for the speed you'll achieve. I don't think that speed of

Re: [sqlite] Limitation on Column count

test cases to > compare. > But that will be tomorrow I guess... > When doing so: Any idea of what would be the worst case column select > strategy on the huge table to compare with? > > Stefan > > > Pavel Ivanov-2 wrote: >> >>> so normalization would lead to

Re: [sqlite] How to find Rank in SQLite3?

> I have used following query but it takes more than one hour even after > indexing, Shouldn't be - query is not so hard if a proper index used. What index did you create? >  mysql->select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL, >        ... >        -> order by x.DEPTNO, x.RANK;

Re: [sqlite] How to find Rank in SQLite3?

> Is there is any other possible way to find out rank? As I said you can do it in your application, it will be a whole lot faster than doing it with sql. Just select all your data with 'order by deptno, sal desc'. Then during iteration over result set assign rank 1 to the person when you first

Re: [sqlite] Limitation on Column count

This is from your last e-mail: > So, I can't see the advantage of normalization here. (Note that I do not > plan to join or index > on measurement columns!) This is from your first e-mail: > Queries on this big table will be rather straight-forward: either on the > table alone (SELECT * FROM

Re: [sqlite] fasted way to get the min/max

Create an index on the field which you're getting min/max of. Then getting min/max will take some milliseconds. Pavel On Wed, Jan 20, 2010 at 7:54 AM, hi wrote: > Hi, > > For my application I am storing about "1770" rows into sqlite table, and > when taking 'min'

Re: [sqlite] Attached database

I wouldn't say anything about views - from my POV there's no much harm in supporting cross-database views other than possible user confusion (personally I don't know why it was decided to not support this). But concerning foreign keys: how do you think SQLite is supposed to enforce foreign key

Re: [sqlite] Attached database

esn't allow to detach > database when foreign key constraints exist on it. > > Sylvain > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Wednesday, January 20, 2010 4:40 PM >

Re: [sqlite] Attached database

> Is it possible? If I attach database B to database A and database A to > database B. As these statements are committed into A and B, they stay > attached even if I close and reopen. So If another process opens A (or B), > it has B (or A) attached to A (or B). I don't have to repeat the ATTACH >

Re: [sqlite] fastest way to get the min/max

> Why the difference in search time between searching individually and > searching together? Apparently SQLite is not smart enough to optimize the search for both min and max to make double entrance to the index - first from the beginning, then from the end. It does search through the full index

Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline

> I am unable to reproduce this problem.  Using the script below, with > RMNOCASE changed to just NOCASE Probably that's exactly the point of crash in the OP's test case. He created table when RMNOCASE collation existed but then tries to execute query when that collation is not registered and

Re: [sqlite] Variable type and/or number of data in a row?

> So my question is : how to create a table in which rows (keywords) > could contain variable type and number of data ? You cannot create table containing variable number of data values - it contradicts relational data model. You can either create 2 tables - one containing keywords, another all

Re: [sqlite] lock database

SQLite cannot lock/unlock tables. But by executing "BEGIN IMMEDIATE TRANSACTION"/"BEGIN EXCLUSIVE TRANSACTION" and "COMMIT"/"ROLLBACK" you can lock/unlock the database. Pavel On Fri, Jan 22, 2010 at 12:06 AM, Qianqian Fang wrote: > hi > > I wanna ask a naive

Re: [sqlite] famous "constraint failed" error

> Does any sqlite MVP know what could be beyond this strange > error message. Exactly what message says: you tried to insert data that is not allowed by constraints in the table. "Read"/"write" threads construct doesn't have anything to do with that - everything can be reproduced in one "write"

Re: [sqlite] famous "constraint failed" error

ror. Are you sure you don't mess with your data from some other process? Pavel On Fri, Jan 22, 2010 at 2:28 PM, Jan Bilek <bil...@gmail.com> wrote: > But why it only happens when any concurrent select query is in progress? > Thanks for any response. > > Jan > > > > Pavel Ivan

Re: [sqlite] Append data to a BLOB field

No, it's not possible. You can try something like this: update table_name set blob_value = blob_value||appendix where ... But here SQLite will still need to read original data, concatenate and write new, although you by yourself won't read and concatenate anything. And I'm not sure whether it

Re: [sqlite] Concurrency support for multiple process

It's not clear what locks did you want to show with your diagram but in reality locks would be held as following: *Process A*: Begin Transaction - no lock *Process B*: Select DB1 <-- shared lock *Process A*: Insert DB1 <-- if insert is small then only reserved lock *Process B*: Select DB1 <--

Re: [sqlite] sqllite and sub queries

Your query looks perfectly correct. You say that it returns 0 in count(*) sub-selects and you think that it's not correct? Could you show as your schema creation statements? And check that your StatusId really contains integers, not text. Also check that BlogPostId in BlogComment and BlogPost has

Re: [sqlite] SQLite Question: What is the best way to "buffer" the database in memory while still being able to save the database to disk when needed (programmatically)?

I can't see all your requirements for this database but I believe you'd better not use temporary or in-memory databases. You'd better use regular database but use all kinds of tricks to speed up work with it (like "pragma synchronous off", "pragma journal_mode off", in some cases some tricks can

Re: [sqlite] what are the limitations for IN() lists?

SQLite doesn't have this type of optimization. Internally IN-list will be converted by SQLite parser to sequence of equality checks that will be performed on each row. So generally the more the IN-list the worse the performance of the query. So with IN-list growing to 100 or more elements I'd say

Re: [sqlite] SQLite Question: Is there a SQLite function that can "copy" one database to another (programmatically)?

What's wrong with the link already given you in another thread? http://www.sqlite.org/backup.html Also see http://www.sqlite.org/c3ref/backup_finish.html. Pavel On Thu, Jan 28, 2010 at 9:43 AM, Trapper Schuler wrote: > Hello, > > Is there a SQLite function that

Re: [sqlite] SQLite Question: Is there a SQLite function that can "copy" one database to another (programmatically)?

, Jan 28, 2010 at 12:44 PM, Trapper Schuler <trapper.schu...@technosoft.com> wrote: > Hi, > > I do not understand the question. > > I appreciate the information that you have provided. > > Thank you. > > Pavel Ivanov wrote: >> >> What's wrong with the link

Re: [sqlite] Getting an error "table insert failed for eventLog" any idea what is the reason

No, we don't. We can help sometimes when script contains about 5 short lines of code, but not with some big complicated scripts. For help with those scripts you should go to script authors. This group is for problems with using SQLite - when you see that SQLite behaves not in a way you expect or

Re: [sqlite] Update/Delete problem

Are you sure that Spiceworks or SQLLite Database Browser commit your changes? What happens if you close those applications and restart it - will they see their own changes? If they will and they still won't see other application's changes then you can be sure that you're looking at different

Re: [sqlite] Update/Delete problem

> Fra: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > På vegne af Pavel Ivanov > Sendt: 1. februar 2010 19:01 > Til: General Discussion of SQLite Database > Emne: Re: [sqlite] Update/Delete problem > > Are you sure that Spiceworks or SQLLite Datab

Re: [sqlite] journal files

What do you want to see in journal files? You can execute 'PRAGMA journal_mode = persist' and all information in journal file except first 4 bytes will be left on disk for you. Is it enough? Pavel On Tue, Feb 2, 2010 at 7:00 AM, rishabh wrote: > > hey, > > I am coding

<    3   4   5   6   7   8   9   10   11   12   >