Re: [sqlite] readers and writer

On 14 Oct 2009, at 1:21am, priimak wrote: > http://www.sqlite.org/lockingv3.html By the way, I just read some of that page and a bit of it, while possibly technically correct, may be putting some people off from using SQLite. "One should note that POSIX advisory locking is known to be buggy

Re: [sqlite] readers and writer

On 14 Oct 2009, at 3:37am, Roger Binns wrote: > Are you willing to stake your reputation and whatever else on there > being > bug free implementations of AFP and SMB. (BTW in a past life I > coded an SMB > server - the other clients and servers out there are definitely not > bug free :-) >

Re: [sqlite] readers and writer

On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote: > Yes, I understood that, but the impression I got is that SELECT will > place shared lock on the database. While INSERT or UPDATE will first > place PENDING lock indicating that it wants to write. Okay, I see what you mean. I don't know how long

Re: [sqlite] readers and writer

On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote: > sqlite> .timeout 1 What is it that that command does ? I looked for a PRAGMA but didn't find one. Or does it correspond to a check-and-retry loop which the programmer has to do yourself in her or his own code ? Simon. __

Re: [sqlite] readers and writer

On 14 Oct 2009, at 11:19pm, Roger Binns wrote: > Simon Slavin wrote: >> On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote: >> >>> sqlite> .timeout 1 >> >> What is it that that command does ? I looked for a PRAGMA but didn't >> find one. Or does

Re: [sqlite] regular expression search

On 15 Oct 2009, at 10:24pm, Farkas, Illes wrote: > I have strings in a database and I would like to find all of them > matching a pattern that is 5-10 characters long. In each position of > the pattern up to three different characters may be allowed. This > would be a typical regular expression t

Re: [sqlite] suggestions for avoiding "database locked" on ajax

On 16 Oct 2009, at 8:53pm, P Kishor wrote: > If neither the username nor the email exist in the db, then the > application creates a record and informs the user of success. Except, > the previous ajax request (I am assuming it is the previous ajax > request from onblur event from the email field)

Re: [sqlite] SQLite encription

On 17 Oct 2009, at 1:11am, Francisc Romano wrote: > Is it possible to encript SQLite databases so that they cannot be > referenced > outside the program that uses it? > If not, can you set a user and password for a SQLite database? The head honcho of SQLite runs a company that has a product wh

Re: [sqlite] sqlite in-memory database far too slow in my use case

On 18 Oct 2009, at 8:37am, Ron Arts wrote: > Is there a way to bypass the virtual machine altogether and reach > directly > into the btree and just retrieve one record by it's oid (primary > integer key), > and return it in a form that would allow taking out the column > values by name? Th

Re: [sqlite] sqlite in-memory database far too slow in my use case

On 18 Oct 2009, at 4:37pm, Ron Arts wrote: > I want to skip the SQL processing entirely > and write a C function that reaches directly into the > internal memory structures to gets my record from there. I assume that you've already tested the fastest way of doing this that the standard library

Re: [sqlite] sqlite in-memory database far too slow in my use case

On 18 Oct 2009, at 7:23pm, Ron Arts wrote: > because the application is evolving, columns > get added/changed on a regular basis. Currently this means adding > the new columns to my C-structures, writing access functions, and > recompiling. I don't want to do that, because this means my appl >

Re: [sqlite] SQLite encription

On 19 Oct 2009, at 1:42am, Roger Binns wrote: > Francisc Romano wrote: >> Is it possible to encript SQLite databases so that they cannot be >> referenced >> outside the program that uses it? >> If not, can you set a user and password for a SQLite database? > > The answer has not changed from wh

Re: [sqlite] suggestions for avoiding "database locked" on ajax

On 19 Oct 2009, at 5:44pm, P Kishor wrote: > But, if I enter something in the field, and, instead of tabbing out of > it, if I click on the submit button, two events fire simultaneously. > There is the onblur() from the field (this is a SELECT query), and the > submit from the form (this is an UP

Re: [sqlite] Slow SELECTs in application

On 21 Oct 2009, at 2:47pm, Unabashed wrote: > I'm using SQLite as DB in my application. My problem consists of two > aspects. First , it works great, but on large data SELECTs are very > slow > (10-20s!). Queries are in one transaction. My table structure is: > CREATE TABLE mgWords ( > id INTE

Re: [sqlite] LAN and exclusive lock

On 21 Oct 2009, at 5:58pm, Jan wrote: > thx bruce, but I am addicted to open-source. If you want an open source server/client SQL engine, designed from the ground up to work correctly when accessed from many computers at once, I recommend MySQL. It is easier, faster and more efficient to ju

Re: [sqlite] index for a group by

On 21 Oct 2009, at 9:19pm, Sylvain Pointeau wrote: > Thank you for your answers. > knowing that I have a table T (a,b,c,d,t) > where d is a value > a,b,c some dimensions > and t the time > > where I need to make a subset with a "group by" like > > select a,b,c,sum(d) > from T > where t>x1 and t g

Re: [sqlite] index for a group by

On 21 Oct 2009, at 11:34pm, Sylvain Pointeau wrote: > if your "book" contains all lines (a,b,c,t,d)and you create an index > on > (a,b,c,t) I assume you meant to add ',d'in there. > then your index is as fat as your book, isn't it? Yes. And it still isn't as useful for any SELECT that doesn

Re: [sqlite] Detecting real updates

On 22 Oct 2009, at 3:51pm, Marian Aldenhoevel wrote: > Is there a way to have sqlite3 detect the actual changes after I did a > number of INSERT OR UPDATE statements? Using a trigger maybe? > > If so I could keep the intermediate storage nicely organized and still > not incur a lot of read-and-th

Re: [sqlite] Inner Join Performance Issue

On 22 Oct 2009, at 9:55pm, Ralf wrote: > I just read, that with an operator "like '%a%'" SQLite won't use an > Index. > Is this the case? The '%' at the beginning of that operand indicates that the first character of the string can be anything. An index is of no use if you don't know what

[sqlite] Order of operations when using TRIGGERs

I'm looking for assurance that things work the way I think they do, and will continue to do so in future versions. I'm using a TRIGGER that is triggered AFTER INSERT. The INSERT commands do not know or set the value for the ROWID column. Can I reliably fetch the value for this column from

Re: [sqlite] Order of operations when using TRIGGERs

On 23 Oct 2009, at 1:23am, D. Richard Hipp wrote: > On Oct 22, 2009, at 7:11 PM, Igor Tandetnik wrote: >> >> In SQLite, ROWID _is_ the unique identifier for each record. Any >> field declared INTEGER PRIMARY KEY is simply an alias for ROWID. >> "Position in the set", whatever it is, has nothing t

Re: [sqlite] Order of operations when using TRIGGERs

On 23 Oct 2009, at 3:00am, D. Richard Hipp wrote: > On Oct 22, 2009, at 9:11 PM, Simon Slavin wrote: >> > >> So now my original query comes down to this: if a column's value is >> generated by SQLite rather than being supplied by my INSERT command, >> i.e. if

Re: [sqlite] Limiting memory usage

On 23 Oct 2009, at 4:06pm, Doug wrote: > PRAGMA page_size=4096 This PRAGMA governs characteristics of a database you have not yet created: it's pages on disk, not pages in memory. The page_size is a characteristic of the database file on disk. It has no effect in a program which opens a

Re: [sqlite] SQLITE_BUSY

On 26 Oct 2009, at 8:21am, Mark Flipphi wrote: > Now an other application is running on a desktop pc and opens the > database from the server harddisk (shared drive) Please verify for us that it's using a standard Windows shared folder. This tells us how the sharing is achieved. > We need to

Re: [sqlite] Slow SELECTs in application

On 27 Oct 2009, at 8:30am, Unabashed wrote: > I have two general types of selects for this table. First one is > "SELECT > word,wform,id_norm FROM mgWords WHERE id=" > and second is > "SELECT > id FROM mgWords WHERE (word='') AND (wform='') > AND > (id_norm=)". > So I tried to add indexes t

Re: [sqlite] Late data typing. Am I missing something?

On 28 Oct 2009, at 5:57pm, Ted Rolle wrote: > Doesn't dynamic data typing lead to bad data? > And proliferation of home-grown editing routines? True in an application which interacts with a user. Not true in a database backend. SQLite does not at any time interact with a user: it does not

Re: [sqlite] How to input a double num?

On 29 Oct 2009, at 12:38pm, Griggs, Donald wrote: > Regarding: How to input the num > "212345678901234567890123456.988290112"? > > > Purely curious, if it's not confidential -- how are you acquiring > numbers of such precision? > > If these are measurements, I'm just awed by the precision the de

Re: [sqlite] Late data typing. Am I missing something?

On 29 Oct 2009, at 9:36am, John Crenshaw wrote: >> Consider the case of an application using an SQLite database to store >> its settings (like the Windows registry, but portable). The dynamic >> type system is great for this. >> >> CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB); >> >>

Re: [sqlite] querys for analysis for performace measures

On 30 Oct 2009, at 1:12am, oryann9 wrote: > Other than setting up typical cron jobs such as iostat, vmstat and > the like, will you provide me with a query(s) to test DB performance > time it takes to return data such as table joins and or any other > relevant system tables? The command-li

Re: [sqlite] SQLite on a Windows Network

On 30 Oct 2009, at 5:47am, mark m wrote: > I have heard problems with SQLite and NFS but I have no idea if a > standard > Windows shared drive uses NFS or not. Am I o.k. to use SQLite??? Standard Windows drive sharing uses SMB, sometimes called SAMBA. NFS is not involved. Simon. _

Re: [sqlite] feature proposal - strong but dynamic typing

On 30 Oct 2009, at 2:14pm, P Kishor wrote: > Actually, there can be one bad effect of Darren's suggestion, now that > I think of it, and that would be for those who don't care for strong > typing. They will end up getting strong typing for all non-UNIVERSAL > columns whether they like it or not,

Re: [sqlite] BUG: datatypes conversion logic error

On 30 Oct 2009, at 4:31pm, Alexey Pechnikov wrote: > The datatype conversion logic is not correct and is not compatible > with > other RDBMS. It is impossible to be compatible with even the three most popular SQL DBMSs. They are not compatible with one-another. I can show you places wher

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

On 30 Oct 2009, at 4:07pm, Kristoffer Danielsson wrote: > "Some database engines like SQL Server decide which table is the > outer table and which table is the inner table" > > > How do I simulate that behavior in SQLite? A misformed SQL statement > from the user results in unacceptable locku

Re: [sqlite] BUG: datatypes conversion logic error

On 30 Oct 2009, at 9:47pm, Alexey Pechnikov wrote: > Now SQLite think that 1 is equal to '1' in some causes and think > different in other. Just like every other language, once you get into it you have to learn how the language works to understand what's going on. Your problem is not really

Re: [sqlite] Data loss after vacuum

On 31 Oct 2009, at 3:22am, chen jia wrote: > Before I ran vacuum, this table, firmsret11, has 2338120 rows. > > After I ran vacuum as follows, > $ sqlite3 hq.db > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> vacuum; > sqlite> .exit

Re: [sqlite] Data loss after vacuum

On 31 Oct 2009, at 7:03pm, chen jia wrote: > Before I ran vacuum, I ran pragma integrity_check; and got > sqlite> pragma integrity_check; > ok > > After I ran vacuum, I ran pragma integrity_check; again and got > sqlite> pragma integrity_check; > *** in database main *** > On tree page 15 cell 36

Re: [sqlite] Data loss after vacuum

On 31 Oct 2009, at 9:39pm, chen jia wrote: > I have not updated sqlite from version 3.6.10 to version 3.6.12 yet. 3.6.10 was January 2009. 9 months ago. Perhaps there's an easy way to get a later version. > But, I have tried .dump by following the instructions on >

Re: [sqlite] Data loss after vacuum

On 2 Nov 2009, at 6:39pm, chen jia wrote: > Thanks for your replies, Simon and Dair. > > Yes, it is probably a bug in sqlite version 3.6.10. After I upgraded > to version 3.6.12, I ran vacuum again and had no problems. That's good news. > I do have more questions. > > I want to clean fragmenta

Re: [sqlite] index optimal?

On 3 Nov 2009, at 7:33pm, Sylvain Pointeau wrote: > I just think that it could be useful to know if an index is optimal > or not, > kind of a tool that could give you the best index for speed up a > query. Your brain. A simplified explanation on how to decide what indices you need follows

Re: [sqlite] triggers revisited

On 3 Nov 2009, at 7:23pm, Victor Mayevski wrote: > Ok, I will rephrase my question although it appears that the answer is > has been already preconceived. I am writing an abstraction layer to > Sqlite where tables, views, triggers etc are presented as XOTcl > Classes/Objects. If two different scr

Re: [sqlite] FTS3 IGNORE OR REPLACE????

On 4 Nov 2009, at 5:12am, sorka wrote: > Is there any way to have an intsert into an FTS3 table ignore a row > if the > ROWID being inserted already exists? First, make whatever column you're using for ROWID explicit, not implicit. Declare one of your own named columns as INTEGER PRIMARY

Re: [sqlite] Locking bug?

On 4 Nov 2009, at 1:17pm, Nico Coesel wrote: > Two seperate processes (process A and process B) on a Linux system > read > and write to the same database. Process A performs a transaction > every 5 > seconds. Every now and then the locks from process A are not released > (judging from /proc/l

Re: [sqlite] FTS3 IGNORE OR REPLACE????

On 4 Nov 2009, at 5:05pm, sorka wrote: > Hmm. Have you actually tried this yourself? > > Here's what I get with a simplified example: > > CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY, > title); > INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A"); > INSERT OR

Re: [sqlite] SQLite on PocketBook

On 5 Nov 2009, at 10:45pm, D. Richard Hipp wrote: > I'm sorry that you are having difficulty and that SQLite is not > working to your satisfaction. But the fact remains that it works > great on every platform that I have access to. If you are able to > find a bug in SQLite we will be happy to l

Re: [sqlite] Avoiding "Ambigious column"?

On 7 Nov 2009, at 10:56pm, Kristoffer Danielsson wrote: > SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4) > NATURAL JOIN (T2 NATURAL JOIN T3); > > Error: Ambigious column name: Salary > > However, if I add two parentheses around "T2 NATURAL JOIN T4", the > error goes away: > >

Re: [sqlite] Output in currency format

On 11 Nov 2009, at 4:51pm, Fred Williams wrote: > The best (safest?) way I have found to handle non scientific math is to > work strictly with integers and multiply and divide using ROUND/TRUNC as > required to gain the precision required. This includes way more than > SQLite situations as we

Re: [sqlite] Reverse Referencing Rows

On 11 Nov 2009, at 7:12pm, Rick Ratchford wrote: > To determine if the set is complete, there would clearly be data rows PRIOR > to the start date and data rows that FOLLOW the end date. This is how I'd > determine that a set is complete with all available data for those 'sample > date windows'.

Re: [sqlite] Output in currency format

On 12 Nov 2009, at 7:17pm, Peter Haworth wrote: > Just to be sure I get my calculations correct, the suggestion is that > all currency amounts should be stored in the database as whole numbers > in fields of type INTEGER. Calculations would be done using the whole > numbers and I'll need s

Re: [sqlite] Output in currency format

On 13 Nov 2009, at 12:34am, Nicolas Williams wrote: > On Thu, Nov 12, 2009 at 11:40:23PM +0000, Simon Slavin wrote: >> There's still some possibility for confusion, however: how many places >> of decimals do you use for each currency ? As far as I know, no >> currently

Re: [sqlite] Output in currency format

On 13 Nov 2009, at 3:30am, Roger Binns wrote: > Simon Slavin wrote: >> Integers in all languages I'm aware of are not stored as mantissa/exponent, >> they're stored as bits with complete precision. > > There is one huge exception I found out the hard way re

Re: [sqlite] sqlite3_column_name

On 13 Nov 2009, at 2:07pm, Marco Bambini wrote: > CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT); > a > SELECT rowid, col1, col2 > > returns the following column names with sqlite3_column_name: > col1, col1, col2 > > Is there a way to force the first column name to be returned as rowid

Re: [sqlite] Maintenance Tasks

On 13 Nov 2009, at 4:03pm, Robert Rawlins - Think Blue wrote: > What tasks would you recommend running on a SQLite database and on what sort > of regularity? I know that's a bit of a 'how longs a piece of string' type > question but I'm just trying to understand if a SQLite database requires the

Re: [sqlite] Output in currency format

On 13 Nov 2009, at 4:40pm, Peter Haworth wrote: > That often give rise to some rounding issues. I do all the math using > however many decimal places are given to me and then round the total > to two decimal places, then calculate how much is owed to each band > member based on that total.

Re: [sqlite] sqlite3_column_name

On 13 Nov 2009, at 9:11pm, Kees Nuyt wrote: > SELECT rowid AS rowid, col1, col2 > FROM foo; > rowid|col1|col2 > 1|1|row1 > 2|2|row2 Heh. Neat. Also you can do things like SELECT col1 AS rowid, col1, col2 Simon. ___ sqlite-users mailing list sqlit

Re: [sqlite] Help me please to optimize sql query

On 15 Nov 2009, at 12:08am, Igor Tandetnik wrote: > select wf1.wordForm > from wform wf1 join mgWordsRelations rel on (wf1.wordNorm = rel.id_norminrel) >join wform wf2 on (rel.id_norm = wf2.wordNorm) > where wf1.ancode = 'someAncode' and wf2.wordForm = 'someWord' > order by random() limit 1;

Re: [sqlite] SQLite, network share, and Qt

On 16 Nov 2009, at 7:32pm, Israel Brewster wrote: > Now I know that > this sounds like a Qt problem, and in fact I believe that to be the > case. However, when I ask them about it, they stubbornly keep > insisting that it is a sqlite problem. I think you have already proved it isn't: > in

Re: [sqlite] SQLite, network share, and Qt

On 17 Nov 2009, at 7:45am, Dan Kennedy wrote: > On Nov 17, 2009, at 2:32 AM, Israel Brewster wrote: > >> Simple (I hope) question here for my first posting to this list: >> Running SQLite 3.4.0 on Mac OS X 10.5.8, should I or should I not be >> able to place a SQLite database on a network share

Re: [sqlite] feature request: built-in FLIP(string) function

On 17 Nov 2009, at 5:52pm, Igor Tandetnik wrote: > But for your goals, it has to be sortable, right? In a proper Unicode > collation, U+0041 U+0301 would behave quite differently from U+0301 U+0041. > Consider "A ' E" (where ' stands for a combining acute accent). In most > locales, this would

[sqlite] Unicode support

On 17 Nov 2009, at 6:37pm, Igor Tandetnik wrote: > Simon Slavin wrote: >> On 17 Nov 2009, at 5:52pm, Igor Tandetnik wrote: >> >>> But for your goals, it has to be sortable, right? In a proper >>> Unicode collation, U+0041 U+0301 would behave quite differently

Re: [sqlite] Unicode support

On 17 Nov 2009, at 10:05pm, Beau Wilkinson wrote: > I think a better approach (to the design of Unicode) would have been for > Spanish and German (for instance) to share absolutely nothing in the encoding > standards. Each language ought to have its own little span of letters, > immortalized i

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

On 18 Nov 2009, at 10:00am, presta wrote: > 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. Simon. ___ sqlite-users mailing

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

On 18 Nov 2009, at 6:05pm, Roger Binns wrote: > Tim Romano wrote: >> This would be a convenient function to have. Are there technical >> issues/obstacles? > > In general for all these feature requests for more functions there is no > need for them to be added to the SQLite core. There is a ver

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

On 18 Nov 2009, at 7:15pm, priimak wrote: > I see. However, the problem is related to journal been named after the > database name. > 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? If you use a symbolic link for

Re: [sqlite] listing free bytes in sqlite files

On 18 Nov 2009, at 8:31pm, Matt wrote: > Given a sqlite database file I would like to create two lists: one of bytes > which are in use and one of bytes which are "free". > What would be the most efficient way to do this? > > I have taken a look over the file format documentation but can't see

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

On 18 Nov 2009, at 8:38pm, priimak wrote: > I understand that. However, that would not present a problem if I access > database by its symlink for reading only and database directly if I am > updating isn't? According to the documentation that could be a problem. The update application creat

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

On 18 Nov 2009, at 9:27pm, Roger Binns wrote: > A C function of your choosing can be called on each new connection being > created:[snip] Thanks for your detailed answer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8

Re: [sqlite] temp directory?

On 19 Nov 2009, at 8:05am, Artur Reilin wrote: > My host have sqlite support vor sqlite 2.8.17 and i can use it. But i have > to give chMod 0777 to the directory where i use sqlite. Not only on the > directory, that contains the sqlite database. let me show it: > > /index.php (uses sqlite databa

Re: [sqlite] temp directory?

On 19 Nov 2009, at 10:05am, Artur Reilin wrote: > The user which runs php (or has the highest rights) is called nobody. Okay. That suggests something strange is happening. Perhaps instead of making sure that the right 'user' has access to the database directory you can use the 'group' that '

Re: [sqlite] Watch Sqlite databases and delete folders based on it.

On 19 Nov 2009, at 7:45pm, Sadnem wrote: > Well, I'm configuring my server to Download some folders to it and then > to upload them to another web and i was wondering how could i delete > that folders from the hdd once they were completely uploaded (thats when > the folder get marked with a 0

Re: [sqlite] Problem with database (SELECT hangs)

On 21 Nov 2009, at 12:57am, Phil Longstaff wrote: > I have an sqlite3 database where a SELECT hangs. The statement is: > > SELECT DISTINCT t.* FROM transactions AS t, splits AS s WHERE > s.tx_guid=t.guid > AND ((s.account_guid IN ('d076626fc5be59df7e592f8f0d66c7cd'))) > > Are there any tools

Re: [sqlite] Is it possible for SQLite to notify an application connected to it of database changes?

On 24 Nov 2009, at 4:12am, Erin Drummond wrote: > Currently it finds changes by constantly polling the database What, precisely, do you poll ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] Is it possible for SQLite to notify anapplicationconnected to it of database changes?

On 24 Nov 2009, at 6:17pm, Erin Drummond wrote: >> What, precisely, do you poll ? > A few mutually exclusive tables in the database to check for changes. Depending on what you care about, you might find it easier to check PRAGMA count_changes either instead of what you currently poll, or as an

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

This new implementation of recursive TRIGGERs. Is it depth-first or width-first ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple threads with shared cache mode

On 24 Nov 2009, at 9:17pm, Pavel Ivanov wrote: > 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, s

Re: [sqlite] Running out of space

On 25 Nov 2009, at 9:40am, Antti Nietosvaara wrote: > I have an application which keeps an index of data in an SQLite > database. I'm trying to figure out the best way to handle the possible > scenario of database filling out the entire hard disk. I could just > delete some of the oldest row

Re: [sqlite] Running out of space

On 25 Nov 2009, at 12:26pm, Antti Nietosvaara wrote: > Simon Slavin wrote: >> I assume your database file is on your boot volume. What operating system >> are you using ? >> > > Actually the database is alone in its own partition. Ah. That's better in some

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

On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote: > Does this answers question? I think it does for the current version: depth first. Thanks. I couldn't find the answer documented anywhere, so I will have to assume that it may change in future versions. Unless the requirement for depth first is

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

On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote: >> I couldn't find the answer documented anywhere, so I will have to assume >> that it may change in future versions. Unless the requirement for depth >> first is somewhere in the SQL specification. > > I believe it should be. Triggers should be

Re: [sqlite] Error: file is encrypted or is not a database

On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: > 000 6166 6c69 6465 7420 206f 706f 6e65 6420 > 010 7461 6261 7361 2065 7274 6e61 6173 7463 > 020 6f69 206e 3632 3a20 6620 6c69 2065 7369 > 030 6520 636e 7972 7470 6465 6f20 2072 7369 > 040 6e20 746f 6120 6420 7461 6261 7361 206

Re: [sqlite] Error: file is encrypted or is not a database

On 25 Nov 2009, at 6:19pm, Dmitri Priimak wrote: > Simon Slavin wrote: >> On 25 Nov 2009, at 6:09pm, Dmitri Priimak wrote: >> >>> 000 6166 6c69 6465 7420 206f 706f 6e65 6420 >>> 010 7461 6261 7361 2065 7274 6e61 6173 7463 >>> 020 6f69 206e

Re: [sqlite] Getting the rowid after an insert

On 26 Nov 2009, at 5:54am, Edward Diener wrote: > I have a table with an integer primary key as the first type. My > understanding is that this is an alias for the rowid. When I insert a > row in this table using _sqlite3_prepare and then sqlite3_step I need to > retrieve the rowid for the row

Re: [sqlite] Index on joined statements

On 26 Nov 2009, at 11:02am, Jonas Sandman wrote: > If you have a join in an SQL-query, how do you add the statements to > optimize the query in an index? Create good indexes. SQLite uses its own cleverness to pick which of the available indexes is the best one. It is much better at picking th

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

On 26 Nov 2009, at 2:04pm, Thomas Lenders wrote: > I am using SQLite on a mobile device. > > I have this one table which has only one field, but I need to search in > the table very quickly. > When creating an index on this field the size of the database is doubled > - which makes sense. > >

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

On 26 Nov 2009, at 2:50pm, Thomas Lenders wrote: > I am looking for an exact match as in I need to know if a number exists > in the table or not. > Something like "select count(*) from ART where artnr='0123456789'". > > I could store the data in a text file instead but then I would have to > s

Re: [sqlite] Location of Sqlite Files

On 27 Nov 2009, at 7:54pm, mr_orange wrote: > Hey, I am kind of new to SQLite. I was wondering if anyone could help me with > a couple things: > 1) Where are the SQLite databases stored? I was told in the C:\WINDOWS\temp > folder, but I can't seem to find any SQLite-related files there. > 2) How

Re: [sqlite] [solved] SQLite won't import 53, 066, 244 row 11 gigabyte CSV file

On 28 Nov 2009, at 3:55pm, Walter Dnes wrote: > ...and import worked. So it appears that the maximum csv file is probably > around 2 gigabytes. That does make sense of some kind, but it indicates that someone is using signed integers for file sizes. Signed would be about 2 gigabytes, unsigned

Re: [sqlite] Moving Average?

On 29 Nov 2009, at 5:08am, npearson99 wrote: > select sum(salary) over > (partition by months order by minutes rows between unbounded preceding and > current row) > from tblSalaryData > > I get an error by partition. I know this doesn't give me the moving > average, I'm just trying to get the

Re: [sqlite] WHERE CLAUSE in UNICODE

On 30 Nov 2009, at 1:58am, Igor Tandetnik wrote: > Note that Unicode collation is not as simple as you might think. Did you know > that in Estonian, 'y' sorts between 'i' and 'j'? Or that in German phonebook > sort, 'oe' sorts as if it were a single letter between 'o' and 'p'? > Basically, you

Re: [sqlite] sqlite3 bind and insert double values

On 30 Nov 2009, at 1:50pm, Michael Lippautz wrote: > I am using sqlite_bind_double on a prepared statement (C API). The > insert completes, however, the value stored in the sqlite table is > different from the output of a casual printf("%f",..) By 'different' do you mean that it is obviously a d

Re: [sqlite] Getting up and running

On 30 Nov 2009, at 2:09pm, FrankLane wrote: > Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with > three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to > do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1. Can > anyone guide me

Re: [sqlite] sqlite3 bind and insert double values

On 30 Nov 2009, at 2:05pm, Michael Lippautz wrote: > 47.824669 / 47.824669167 Same number. If you need better precision than that, declare the column type as TEXT and bind your data as text. But since you're using GPS coordinates I can tell you it's not necessary. That seventh digit in a GP

Re: [sqlite] WHERE CLAUSE in UNICODE

On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote: > Consider a column that contains a person's last name. Q: do proper > names have a language? A: No, since people can be from all over and > even within a single country may have last names of various radically > different origins. But what is

Re: [sqlite] WHERE CLAUSE in UNICODE

On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote: > On Mon, Nov 30, 2009 at 06:37:11PM +0000, Simon Slavin wrote: >> > >> The column 'language' >> could perhaps be absolute, or perhaps be used as a default if the >> individual values did not declare a l

Re: [sqlite] Sampling Data

On 1 Dec 2009, at 7:29pm, npearson99 wrote: > Example Table: > tableID > Minute > Watt > > I'm trying to sum average the watt column by minute. Use 'avg(X)' on the result of a SELECT which finds all the samples within a particular minute. http://www.sqlite.org/lang_aggfunc.html It is unfortu

Re: [sqlite] Sampling Data

On 1 Dec 2009, at 8:06pm, npearson99 wrote: > So I could go select Avg(watts) from tblData where minute = 1 or minute =2 > or minute =3 or minute = 4 SELECT avg(watts) FROM tblData WHERE minute BETWEEN 1 AND 4 would be far faster, especially if you have an index on minute. > But then I would h

Re: [sqlite] Pragma not allowed in SQL error

On 3 Dec 2009, at 2:42pm, Tim Romano wrote: > I was trying to do > > PRAGMA set case_sensitive_like =1 Wrong syntax: http://www.sqlite.org/pragma.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailma

Re: [sqlite] Pragma not allowed in SQL error

On 3 Dec 2009, at 2:51pm, Tim Romano wrote: > The statement I'm issuing to the database > via Adobe's libraries is > > PRAGMA case_sensitive_like =1 > > But I take it from your answer that SQLite has no problem with this and > that it is Adobe who are preventing my PRAGMA directive from being

Re: [sqlite] Multiples natural left joins problem

On 3 Dec 2009, at 5:30pm, Yuzem wrote: > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: movies with no ra

Re: [sqlite] SQLite Optimization

On 3 Dec 2009, at 6:26pm, mr_orange wrote: > I am trying to optimize the speed of my SQLite transactions. The goal is to > beat query and insertion times that we have with MS SQL. I guess there are 2 > main issues: > > 1) The query times are faster when I do simple select statements on a large >

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

On 4 Dec 2009, at 11:50am, Pronab Ganguly wrote: > I am newbie to sqlite.Would like to is sqlite is better than flat file > parsing? > > We have platform specific files of different formats to be read so our > code is complex and difficult for maintenance considering support for > newer platfor

<    1   2   3   4   5   6   7   8   9   10   >