Re: [sqlite] Execute Error Notification

On 16 Mar 2014, at 7:16pm, RSmith wrote: > My question is though, is there a way to learn somehow whether a rollback or > constraint violation happened during the whole execute process which caused a > rollback? > > Kind of like saying: "Yes I know the script executed

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

On 18 Mar 2014, at 11:49pm, Tim Streater wrote: > I'm using PHP's sqlite3 class rather than PDO, and I've done nothing explicit > about the journal mode. Good. From PHP using sqlite3 is more efficient and leads to simpler programming than using the PDO. > My

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

On 19 Mar 2014, at 11:21am, Tim Streater wrote: > So I could do: $dbh = new sqlite3 ($dbname, SQLITE_OPEN_READONLY); ?? Yes. I think that's the way you're meant to do it if you really do want readonly. > 3) Is the lock always released if I do $dbh->close(); ? > >>

Re: [sqlite] Using ICU case folding support

On 19 Mar 2014, at 3:36pm, Alex Loukissas wrote: > Thanks everyone for your comments. IIUC, the correct way of going about > what I want to do is to use BINARY collation on the column I'm interested > in and when I want to do unicode-aware case-insensitive lookups, they >

Re: [sqlite] subquery performance

On 19 Mar 2014, at 7:50pm, Sander Jansen wrote: > SELECT * FROM tracks WHERE (SELECT id FROM artists WHERE name LIKE 'John > Williams') IN (performer,conductor,composer); The art of using JOIN is obviously dead. The is a classic case of a UNION of three JOINs. Also,

Re: [sqlite] SQLite auto_vacuum doesn't work?

On 19 Mar 2014, at 10:53pm, m.d.berce...@gmail.com wrote: > I'm running the "pragma auto_vacuum = 'full'" a C++ code, then I go to > SQLiteSpy, open the database and run pragma auto_vacuum in the query window > and the result is 0. The syntax is PRAGMA auto_vacuum = FULL no quotes or

[sqlite] Documentation improvement request: PRAGMAs

If I got this right, there are five types of PRAGMA: A) some return information without making changes B) some affect the loaded SQLite library: all connections and future connections until the library is unloaded C) some change the behaviour of one connection without changing anything in the

Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:

On 20 Mar 2014, at 2:19pm, Stefano Ravagni wrote: > I know, in facts i use a TEXT field...so you intend datagrid usually > understand if the field is or not a DATE field showing the value in > auto-formatted way ? SQLite does not have a DATE datatype and does not

Re: [sqlite] How good is pragma integrity_check

On 20 Mar 2014, at 11:33pm, Richard Hipp wrote: > On Thu, Mar 20, 2014 at 7:18 PM, Tim Streater wrote: > >> I had a case where attempts to access a table in a user's db gave "no such >> table", where 60 mins previously (according to the log) querying

Re: [sqlite] How good is pragma integrity_check

On 21 Mar 2014, at 7:57pm, Roger Binns wrote: > It also doesn't check the data, just the structure of the data. There was > a feature request ticket for several years for checksums to at least catch > unexpected changes to the data itself: > >

Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark

On 23 Mar 2014, at 11:19pm, piotr maliński wrote: > So maybe this particular SSD is slow with write barriers, while other > doesn't have a problem with it (as they don't change performance between > SATA and USB3). You have missed the point. When Florian write "Many USB

Re: [sqlite] How good is pragma integrity_check

On 24 Mar 2014, at 5:23am, Roger Binns <rog...@rogerbinns.com> wrote: > On 21/03/14 15:24, Simon Slavin wrote: >> Checksums stored with the page index lists, > > SQLite already has the ability to carve out data on each page for other > uses. For example the e

Re: [sqlite] How good is pragma integrity_check

On 24 Mar 2014, at 12:10pm, Markus Schaber wrote: > Not agreed. Another way to solve this problem is to include the file offset > or sector number into the checksum - this will also detect "movements" of > data to the wrong place, without the need for an atomically

Re: [sqlite] How good is pragma integrity_check

On 24 Mar 2014, at 1:34pm, Tim Streater wrote: > My app does some periodic housekeeping, which includes occasional vacuuming > of some files. From the logs, I deduce that the OS completely lost this file > [1], which got recreated at the next housekeeping and then

Re: [sqlite] SQLite sorting/searching algorithm

On 25 Mar 2014, at 4:02pm, michal.pilszak wrote: > Hello, I've got a few questions: 1. Firstly, I was wondering what are the > types of sorting and searching algorithms used in SQLite?2. Secondly, I'm > using SQLite in my Android app, and I was wondering is there any

Re: [sqlite] SQLite sorting/searching algorithm

On 25 Mar 2014, at 5:48pm, michal.pilszak wrote: > Is there any parameter I can set to select another algorithm (e.g. another > algorithm of ORDER BY) and check its efficiency? You can tell SQLite to search for different rows or order them in a different order. But you

Re: [sqlite] Primary key used in compound index

On 25 Mar 2014, at 8:19pm, Pavel Vazharov wrote: > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Can you try this as exactly the following: "id" INTEGER PRIMARY KEY, leaving everything else out. I assure you that it will obey the 'NOT NULL' and 'AUTOINCREMENT'

Re: [sqlite] Primary key used in compound index

On 26 Mar 2014, at 1:20pm, Clemens Ladisch wrote: > In this case, it appears that listing the rowid explicitly confuses > SQLite somehow. That would be a bug, then ? Well, not a bug leading to incorrect results, but certainly one that slows down searches. Simon.

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

On 27 Mar 2014, at 4:00am, Muharrem Bilgin (Bright Software) wrote: > The blob issue mentioned is happening with another table in another > application. The reason it was mentioned because we have the feeling that > whenever resulting data reaches to a certain size,

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

On 28 Mar 2014, at 12:58am, Muharrem Bilgin (Bright Software) wrote: > Agreed, and no offence taken :-) The difficulty is to how to get the shell > tool run on Windows RT/Windows Phone 8 ? I wonder if there is any utility > out there based on 3.8.4.1 (or 3.8.4.2 )

Re: [sqlite] Is this normal behavior for a unique index?

On 28 Mar 2014, at 1:12am, SongbookDB wrote: > However, if, for example the DiscId is blank, but the artist and title are > not, a new record is created when there is already one with a blank DiscId > and the same artist and title - BAD. What's the primary key on

Re: [sqlite] Is this normal behavior for a unique index?

On 28 Mar 2014, at 6:06am, SongbookDB wrote: > Never mind Simon. It turns out that SQLite allows NULLable fields to > participate in UNIQUE indexes. > > I'd screwed up the code I'd made to replace anything undefined with "". > I've changed it, and it's properly

Re: [sqlite] Use of AUTOINCREMENT

On 28 Mar 2014, at 12:23pm, Kees Nuyt wrote: > The next ID will usually be 11, but it is not guaranteed. > One day, it could suddenly be 5. Just to add more to that, the rule is currently along the lines of "New IDs will continue to increment until SQLite runs out of

Re: [sqlite] Use of AUTOINCREMENT

On 28 Mar 2014, at 2:09pm, Richard Hipp wrote: > The current "burn rate" on the SQLite repository is about 3650 record IDs > per year. Let's assume 10x the burn rate. Even then, it is another 58,000 > years or so before the 32-bit signed integer overflows. At that burn rate

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On 2 Apr 2014, at 4:58am, Kevin Xu wrote: > The app works by reading in the FASTQ file (through Boost::memory_mapped > regions), running the parser through it, binding the variables using > sqlite3_bind_xxx, and inserting them in a prepared statement wrapped within >

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On 2 Apr 2014, at 4:55pm, Kevin Xu wrote: > PRAGMA journal_mode=MEMORY; Since you're not using WAL mode, try it using WAL mode. If you were already using WAL mode, I'd suggest trying with it off ! Given your answers to the questions so far, I can't think of anything else

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 6:08pm, Donald Steele wrote: > The first part is where my confusion is. First of course is where it takes > place but then since I would apply it to a column of a table as part of a > select statement what are the strings in the compare and how do I set

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 6:38pm, Donald Steele wrote: > So am I misunderstanding how to get what I am after? > > I am using a select statement to produce a list of records “ordered by” the > highway column. > My understanding was that the select statement would include my

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 6:56pm, Donald Steele wrote: > Registering of the custom collation is far from intuitive (for me) and thus > the reason for my question. What programming language is your collation function written in ? How are you calling your SQLite functions ? Are

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 7:04pm, Donald Steele <xln...@sbcglobal.net> wrote: > I am working in iOS (aka ObjC) so I am using it’s built in framework for all > my SQLite calls. > > On Apr 2, 2014, at 10:58 AM, Simon Slavin <slav...@bigfraud.org> wrote: > >> On 2 Apr 20

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 8:51pm, Donald Steele wrote: > If I was having no problems I would not have asked the question. I will > continue to look for better information & will eventually get it done. Thanks > anyway. Well, your answer doesn't make sense and people are drawing

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On 3 Apr 2014, at 3:29am, Kevin Xu wrote: > I have not discovered how to find internal memory throughput usage in OSX, > and I agree that something is not allowing the system from maxing out the CPU > or I/O. A single application can max a single core of a single CPU if

Re: [sqlite] if possible point another table

On 4 Apr 2014, at 7:55am, Darren Duncan wrote: > Putting that aside, for any SQL DBMS that supports the PREPARE and EXECUTE > keywords, you can have a SQL string value that contains a SQL statement and > execute it, and you can build that string in other SQL from your

Re: [sqlite] Is there a way to load a blob from the shell?

On 6 Apr 2014, at 9:01pm, wrote: > I haven't figured out how to load a blob (e.g., image) from the shell. I > would think there should be something like this but can't find anything: > > insert into table values(file('path_to_binary_file')); You can represent

Re: [sqlite] Is there a way to load a blob from the shell?

On 7 Apr 2014, at 12:46am, Larry Brasfield wrote: > I, too, thought there should be something like that. > Here is the .help portion for a shell enhancement I wrote awhile ago: > .blobextract TABLE COL ROW FILE ?DB? Extract DB blob to a file. Table, >

Re: [sqlite] Is there a way to load a blob from the shell?

On 7 Apr 2014, at 1:31am, Larry Brasfield <larry_brasfi...@iinet.com> wrote: > Quoting me, Simon Slavin writes: > >> Good idea for the function to create a file. However, to conform closer to >> expectations of how SQL works, had you thought of creating a SQLite fun

Re: [sqlite] How to speed up a bulk import

On 7 Apr 2014, at 7:33pm, J Trahair wrote: > Would having a field index or key help? Adding more indexes and keys make searches faster, at the expense of making the original INSERT slower. As a test, instead of executing the INSERT commands, write the

Re: [sqlite] How to handle simultaneous reads/writes in SQLite

On 8 Apr 2014, at 3:20pm, David Brown wrote: > It's my understanding that any number of reads can be going on > simultaneously (from other threads even) but ONLY ONE thread can write at a > time. Is this correct? Right. > Also, how would one handle multiple

Re: [sqlite] What's the purpose of the "automatic index on" warning message?

On 8 Apr 2014, at 2:22pm, Jens Miltner wrote: > So what would cause SQLite not being able to use one of the two indexes I > have? First, run "ANALYZE". Then run "EXPLAIN QUERY PLAN ". This may give you some clues about how SQLite is understanding your SELECT requirements when

Re: [sqlite] about the apparently arriving soon "threads"

On 8 Apr 2014, at 8:00pm, big stone wrote: > I did experiment splitting my workload in 4 threads on my cpu i3-350m to > see what are the scaling possibilities. > > Timing : > 1 cpu = 28 seconds > 2 cpu = 16 seconds > 3 cpu = 15 seconds > 4 cpu = 14 seconds > > Analysis :

Re: [sqlite] Database corruption issue

On 10 Apr 2014, at 5:04pm, Grzegorz Sikorski wrote: > The problem is we occasionally observe database corruption and whole database > becomes rubbish. The problem is very rare and occurs only (as far as we were > able to confirm so far) if there is a power lose during

Re: [sqlite] Database corruption issue

On 11 Apr 2014, at 11:49am, Grzegorz Sikorski wrote: > No, the journal files (-wal/-shm) are not modified at all. The only think > that may be done (but rarely) is changing its privileges to rw-rw-r--. We > need to be able to open database in read-only mode by group

Re: [sqlite] Bug with some combination of unique/partial indices

On 13 Apr 2014, at 12:32pm, Tim Streater wrote: >> CREATE INDEX t1b ON t1(b) WHERE b=1; > > I get no assertion fault but rather a syntax error here on "where". Partial indexes were introduced in 3.8.0. See the last section of

Re: [sqlite] Problems with executereader in System.Data.SQLite

On 15 Apr 2014, at 10:14am, Ralf wrote: > Yes. Everything is Working as expected when i access the DB with any Browser. > It is just the powershell environment Then the chances are that the problem is with PowerShell, not SQLite. Simon.

Re: [sqlite] SQLite for single user data manipulation

On 16 Apr 2014, at 2:39am, Nick Eubank wrote: > -- am I going to have problems using all 16gb of ram on my Windows 8 > machine for data manipulations if I switch to SQLite? Or will SQLite set me > free? If you're talking about per-query memory cap, then that won't figure

Re: [sqlite] Lock before beginning SELECT statement

On 15 Apr 2014, at 1:47pm, MikeD wrote: > If another thread deletes/updates or inserts while a SELECT statement is > processing the results could > be unpredictable? Only if your timing is unpredictable. If you know which command is issued first, you know what the

Re: [sqlite] deleting dupicate rows

On 16 Apr 2014, at 4:02pm, Christoph P.U. Kukulies wrote: > Am 16.04.2014 15:57, schrieb Richard Hipp: >> >> CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date); > > Though this seems to work, could I achieve this also by a table constraint, > like

Re: [sqlite] SQLite for single user data manipulation

On 16 Apr 2014, at 4:42pm, Dominique Devienne <ddevie...@gmail.com> wrote: > On Wed, Apr 16, 2014 at 3:41 PM, Simon Slavin <slav...@bigfraud.org> wrote: >> I would recommend that you download the SQLite shell tool and get to know it >> a little. Not only is it

Re: [sqlite] deleting dupicate rows

On 17 Apr 2014, at 1:21am, James K. Lowden <jklow...@schemamania.org> wrote: > Simon Slavin <slav...@bigfraud.org> wrote: > >> If you really want to do it in the TABLE definition, use the SQLite >> shell tool to '.dump' the table as a set of SQL commands

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

On 17 Apr 2014, at 3:42pm, Igor Tandetnik wrote: > If there's a bug anywhere in this, I'd say it's the fact that SQLite allowed > "DROP VIEW v2" statement to proceed. Agreed. Is there a similar bug if you try to drop a table that a FOREIGN KEY depends on ? Simon.

Re: [sqlite] field length retreval

On 17 Apr 2014, at 11:24pm, David Clark wrote: > If I have a table of > field1 varchar(25) > field2 varchar(50) > field3 varchar(75) You don't. SQLite does not support a datatype of varchar(). Fields you declare like that will be implemented as TEXT fields and handled

Re: [sqlite] field length retreval

On 18 Apr 2014, at 3:21pm, Dominique Devienne <ddevie...@gmail.com> wrote: > On Fri, Apr 18, 2014 at 12:53 AM, Simon Slavin <slav...@bigfraud.org> wrote: >> There are ways to enforce field length limits entirely within SQLite but >> they're complicated so post agai

Re: [sqlite] field length retreval

On 18 Apr 2014, at 5:28pm, Dominique Devienne wrote: > I'm not sure where you get that declaring a column as varchar() > implicitly truncate While I can't find any reference one way or another in a SQL standard, all implementations I've seen that understand VARCHAR(n)

Re: [sqlite] field length retreval

On 18 Apr 2014, at 7:29pm, Donald Griggs wrote: > I tried a simple test with recent [Postgres] (9.3.4) version (using default > settings, > if that matters) and verified that an insert with oversized string will > fail to insert, unless the overage characters are spaces

Re: [sqlite] printf function is shown in docs but not found when I try it

On 18 Apr 2014, at 2:49pm, c...@isbd.net wrote: > Xubuntu 13.10 (which I'm currently using) has sqlite 3.7.17. > > Xubuntu 14.04 has sqlite 3.8.2, I'm not sure if they're likely to move > to anything newer during its lifetime. If you're writing C or C++ code then you don't need to use

Re: [sqlite] detach failed with error code 1

On 20 Apr 2014, at 12:58pm, dd wrote: > Given different database name for in-memory database for every > iteration(looped for 1000 times). Still, it's throwing Database Locked at > least once on Windows, not on Mac/Linux. Is there any way to track this > issue? I am using

Re: [sqlite] Remote access to SQLite in Linux from Windows.

On 21 Apr 2014, at 12:43am, Stephen Chrzanowski wrote: > Short of writing a server type application that listens for incoming > communication, I'm not aware of anything of the sort. SQLite is an > embedded library which compiles with your code, or, with references to >

Re: [sqlite] Bug report: column name includes table alias when CTE is used

On 21 Apr 2014, at 6:16am, Andre wrote: > Apparently when a CTE is used, the column name includes the table alias. > However, when no CTE is used, the alias is not present in the returned > column name. SQLite has no standards at all about column names unless you

Re: [sqlite] INSERT OR REPLACE

On 21 Apr 2014, at 11:18am, Neville Dastur wrote: > I have a column defined as hospitals_id TEXT PRIMARY KEY UNIQUE which stores > a GUID. Hence the text primary key. When I do an INSERT OR REPLACE the rows > are duplicated. I want to check that these keys really

Re: [sqlite] Linux :Custom build and locking

On 21 Apr 2014, at 2:03pm, Alain Meunier wrote: > I was discussing about the latest sqlite3 version and in the discussion came > the statement that one should not use a custom builds out of the system one SQLite is not a centralised system and isn't wired into the OS at any

Re: [sqlite] INSERT OR REPLACE

On 21 Apr 2014, at 6:18pm, Richard Hipp wrote: > SQLite does the right thing and enforces NOT NULL on PRIMARY KEY for the > newer WITHOUT ROWID tables. But long ago there was a bug that prevented > enforcement NOT NULL on PRIMARY KEY of ordinary tables and by the time the >

Re: [sqlite] BLOBs and NULLs

On 22 Apr 2014, at 10:07am, Dominique Devienne wrote: > using GUIDs Don't particularly mind if anyone is using GUIDs, but if anyone is using calling something GUID can you please make sure it's a real GUID ? They look like this:

Re: [sqlite] BLOBs and NULLs

On 22 Apr 2014, at 4:55pm, Dominique Devienne wrote: > Simply because of the extra space needed to store it. 36 bytes vs 16 > bytes. That's 20 wasted bytes for the PK, and everytime that PK is > references in other tables' FKs too. Times millions of rows, it adds > up, for

Re: [sqlite] Is there any document, when I wanna implement own VFS?

On 23 Apr 2014, at 8:58am, 김병준 wrote: > I spend much time to find for reference of > implementing VFS. > But I can’t find > any reference of that. > I wanna know some requirement and manual > for implementing my own VFS. > > Is there anything that help me? Please see

Re: [sqlite] Sqlite3 & Concurrency

On 23 Apr 2014, at 10:29am, techi eth wrote: > 1) Sqlite3 doesn’t have any concurrency issue when multiple process > reading one database file at same time. This doesn’t required any special > mode setting (Like WAL) Correct. > 2) One process is reading & other

Re: [sqlite] Sqlite3 & Concurrency

On 23 Apr 2014, at 10:42am, techi eth wrote: > Many Thanks for quick reply. > > Could you please suggest me other journal mode for second case.My target > filesytem dosen't support mmap & WAL required mmap support.

Re: [sqlite] Long table fails to create with exec sql on windows 8.1

On 23 Apr 2014, at 10:47pm, Mark Potter wrote: > I have this table and SQL LITE crashes on windows when I try to create using > exec sql. System just stops responding. Does it crash inside the routine ? If so, can you show us a crash message of some kind ? Donald

Re: [sqlite] Entity Framewoork 6 and Connection String question

On 27 Apr 2014, at 1:21pm, Matthijs ter Woord wrote: > I'm trying to have my DbContext connect to a sqlite database specified by > the end user. > The end user is given a File Open dialog, and then the application should > connect to it. > > The DbContext has a

Re: [sqlite] When to open/close connections

On 28 Apr 2014, at 11:11pm, RSmith wrote: > Second approach is better when you rarely access the database, also it will > make sure releases happen (or at least provide immediate errors if not), but > keeping a connection open is much better when hundreds of accesses

Re: [sqlite] Problems with INT columns

On 29 Apr 2014, at 10:15am, Kleiner Werner wrote: > If I understand the SQLite Docu correct there is no difference between INT or > INTEGER, except if you use a column as Primary Autoincrement. > I thought an Int column in SQLite is always 64bit integer, but why does the >

Re: [sqlite] When to open/close connections

On 29 Apr 2014, at 2:24pm, Drago, William @ MWG - NARDAEAST wrote: > Does closing the connection force, or at least encourage, the OS to write to > disk whatever it might have been caching? Closing a connection calls fclose() on the database file (as long as fopen()

Re: [sqlite] Is there any reason which use OS file system on default mode?

On 30 Apr 2014, at 8:49am, 김병준 wrote: > If sqlite3 access storage device directly, I think, it must be faster than > using os file system. > Because using os file system mean there must be some overhead. > So, I'm curious about the reason why use os file system default.

Re: [sqlite] select 1 where 1 - 1;

On 2 May 2014, at 7:47pm, David King wrote: > Am I missing something? It's very clever for people who write programming languages to invent the idea of a boolean variable, and have 'IF' and 'WHERE' take boolean values as arguments, but I don't think it's obvious. Simon.

Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole

On 3 May 2014, at 1:59pm, Hayden Livingston wrote: > My workload is quite straightforward, I have a multi-threaded application > that logs to this file but from within any single thread at any given time. > So from SQLite's perspective only one person will be writing to

Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole

On 3 May 2014, at 2:35pm, Hayden Livingston wrote: > That's clever. I'm thinking how I'll go about this .. essentially the > filename is devised by time splits. I could do what you're saying but then > I can't move the file. Basically, the good part is that our

Re: [sqlite] decomposing a path into its components?

On 3 May 2014, at 3:47pm, Petite Abeille wrote: > Let further assume one would like to use only SQLite's build-in mechanism There are two kinds of programmers ... Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Sqlite Crash In Windows Kernel

On 5 May 2014, at 6:42am, NULL wrote: > the attached files is the code i used,and the code would be cause system > crash when call like this, > sqlite3_exec(db, "create table hello(one varchar(10), two smallint)", > callback, 0, ) You cannot attach files to posts to

Re: [sqlite] select 1 where 1 - 1;

On 5 May 2014, at 4:18pm, mm.w <0xcafef...@gmail.com> wrote: > "an empty string should be false" strongly disagree, a NULL string should > be solely false, now in this case, the question is: comparisons should be > handled as bin or by; 'literal' values? or equality/comparison must not be >

Re: [sqlite] Bug in division?

On 6 May 2014, at 2:06am, Gene Connor wrote: > SELECT DISTINCT 2/4 AS RESULT FROM TABLE; > returns 0 Not a bug. By providing two integer operands you have asked for integer arithmetic, and will get an integer answer. It's something that happens in several

Re: [sqlite] Bug in division?

On 6 May 2014, at 1:52pm, RSmith wrote: > I think the OP might be seeing the list via one of those connected sites and > not getting the feedback. Maybe send a direct mail to him. I'll send a personal email to him. Simon. ___

Re: [sqlite] Very slow when query max(col) on empty set?

On 7 May 2014, at 1:29pm, Woody Wu wrote: > The 'explain query plan' gives same result for the first and the second query: > > 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq (~1 rows) > > BTW: I dont understand what the (~1 rows) mean.

Re: [sqlite] WITHOUT ROWID option

On 7 May 2014, at 3:31pm, Richard Hipp wrote: > (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master > WHERE tbl_name='table'". If the PRAGMA mentions an > "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you > have a WITHOUT ROWID

Re: [sqlite] Foreign Key errors

On 8 May 2014, at 9:00am, Constantine Yannakopoulos wrote: > When the fk violation occurs inside a large transaction, > e.g. during a bulk data import it is essential that the user is given any > possible help to be able to locate the data that violates ref integrity and

[sqlite] Documentation improvement requests

In the discussion on this page the results of trying to violate a FK are shown only like this: "SQL error: foreign key constraint failed" There's no mention of which result code the API will return. Similarly on this page

Re: [sqlite] rowid question

On 8 May 2014, at 9:57am, Alain Meunier wrote: > Are there any way to insert a row at a specified rowid ? > > Imagine that i wish to insert a row at rowid 9 then 1150; Is it possible ? Declare a table column as INTEGER PRIMARY KEY. This will make the 'rowid' pseudocolumn

Re: [sqlite] duplicate row in sqlite3 database

On 8 May 2014, at 10:14am, techi eth wrote: > SQlite3 have any method where it can avoid adding duplicate row or throwing > error status code on duplication., Instead of INSERT ... do INSERT OR IGNORE ... This means that instead of returning an error code if you violate

Re: [sqlite] Foreign Key errors

On 8 May 2014, at 10:40am, Constantine Yannakopoulos <alfasud...@gmail.com> wrote: > On Thu, May 8, 2014 at 11:50 AM, Simon Slavin <slav...@bigfraud.org> wrote: > >> That's your job. You're the programmer. SQLite does not talk to users >> and its result values s

Re: [sqlite] Storing amount?

On 8 May 2014, at 12:13pm, Kleiner Werner wrote: > a PHP script stores an amount into a SQLite table column "salary" which is > defined as float(10,0) > I a user fill in 5 into a textfield, this value is stored as 5000.0 Which way of accessing SQLite are you using ?

Re: [sqlite] Storing amount?

On 8 May 2014, at 12:41pm, Werner Kleiner wrote: > I use the PDO library. > The typeOf results in "real" > > If I change the "swsalary" column to "numeric(10,0) it results in integer > and then the value of > 5 ist stored correct without . (dot). > > So is it better to

Re: [sqlite] Very slow when query max(col) on empty set?

On 8 May 2014, at 2:14pm, Clemens Ladisch wrote: > Woody Wu wrote: >> And, as a general question, for a query in the form of >> select max(a) from table where a < InF and b=B and c=C. >> what's the best index in the case of (1) B is an existed one, or (2) B is >> not

Re: [sqlite] Storing amount?

On 8 May 2014, at 3:06pm, Werner Kleiner wrote: > Hmm, but would it not be better to store the value 5 as > 5.0 , which would be correct for the decimal point > instead of > 5000.0 ? Numeric values in numeric fields are not really stored as text, they're stored as

Re: [sqlite] WITHOUT ROWID option

On 9 May 2014, at 11:17am, Sky Meena wrote: > i need to transfer sqlite.db from server to client in udp socket Please start a new thread about this. It has nothing to do with 'WITHOUT ROWID option'. Simon. ___ sqlite-users

Re: [sqlite] General error: 11 malformed database schema - near "'NOCASE'":

On 9 May 2014, at 1:16pm, Werner Kleiner wrote: > If I connect to this db with PHPLiteadmin it tells me "No Table in Database" > But if I connect on Windows with e.g. Sqlitestudio all is fine. I'm betting that you're not connecting to the same database file in all these

Re: [sqlite] sqlite db transfer

On 9 May 2014, at 1:23pm, Sky Meena wrote: > i working in server client... c program. i need to transfer db from server > to client. in udp socket .. i to send a db. SQLite does not involve a server or a client. All processing and access of the database is done inside

Re: [sqlite] LIKE operator and collations

On 12 May 2014, at 3:05pm, Constantine Yannakopoulos wrote: > ​I understand that it is difficult to find the least greater character of a > given character if you are unaware of the inner workings of a collation, > but maybe finding a consistent upper limit for all characters in all > possible

Re: [sqlite] LIKE operator and collations

On 12 May 2014, at 4:05pm, Jan Slodicka wrote: > And whether we'll call it "LIKE optimization" or "using BETWEEN", the > problem remains basically the same. They're not the same, Jan. This optimization is for one very specific use of LIKE: where the match string has no wildcards except for a

Re: [sqlite] Best compression for sqlite3 ?

On 13 May 2014, at 7:20am, Alain Meunier wrote: > I would like to know if some of you know a good compression tool to run with > sqlite. > > I worry about the size because other implementations (hamsterdb) shave approx > 40-50% of the sqlite size for the same datas. > > I saw about zipvfs

Re: [sqlite] LIKE operator and collations

On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote: > ​This is very interesting Jan. The only way this could fail is if the > collation implementation does something funny if it encounters this > character​, e.g. choose to ignore it when comparing. That cuts out a very large number of

Re: [sqlite] Corrupt database

On 14 May 2014, at 8:56am, Kleiner Werner wrote: > A collegue has strange behavior with a sqlite database. > The original DB is about 13 MB. > After storing 40 entries in a table the disk space is double and about 26 MB. I assume from fact that you're concerned about this that the data you're

Re: [sqlite] SQLite Datareader problems with Int?

On 14 May 2014, at 3:19pm, Kleiner Werner wrote: > Could it be a problem or does it matter if we convert all SQLite int columns > to INTEGER? SQLite does not have an 'int(10)' type. For integers it has only INTEGER. There should be no problem with the conversion. There may be a problem

<    5   6   7   8   9   10   11   12   13   14   >