Re: [sqlite] Undocumented feature of FTS simple tokenizer to customize delimiters

This would be great for me too, since I'm developing an Android app and can't guarantee the version available without packaging a custom SQLite. > On Sep 16, 2014, at 08:15, Niall Gallagher wrote: > > Hi, > > The FTS simple tokenizer has an undocumented feature, which

Re: [sqlite] Maverick RoR ans sqlite3

>sqlite> .mode csv consult_facts >Error: invalid arguments: "consult_facts". Enter ".help" for help .help says, inter alia: .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned

[sqlite] Undocumented feature of FTS simple tokenizer to customize delimiters

Hi, The FTS simple tokenizer has an undocumented feature, which allows the set of characters it treats as delimiters to be configured. By default it simply treats all non-alphanumeric ASCII characters as delimiters, but the following example shows how it can be customized to use only '#' (hash)

[sqlite] Maverick RoR ans sqlite3

OSX 10.9.4 When populating csv files in RoR app i get "Error: invalid arguments" (example): sqlite> .schema consult_facts CREATE TABLE "consult_facts" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255), "login" integer, "mail" varchar(255), "full_name" varchar(255),

[sqlite] Squeal, a Swift interface to SQLite

I’d love some feedback on a general-purpose SQLite library I’ve written in Swift: https://github.com/nerdyc/Squeal I’ve tried to make it easy to get started with SQLite, through documentation and helpers for the most common SQL tasks. But it also

Re: [sqlite] Create join and add unique column

You will also note that doing something like: create [temporary] table x as select a, b, ... from y ... will result in calculated columns being inserted with affinity None. If you need a specific affinity you need to cast the expression result to that type affinity. Also, if it matters,

Re: [sqlite] Create join and add unique column

On Sep 16, 2014, at 8:15 PM, RSmith wrote: > could you show how to achieve this in SQL via the ranking method you linked Well, ranking is the same as numbering, no? So, for example: with NameSet as ( select 1 as id, 'paul' as name union all select 2 as id, 'helen'

Re: [sqlite] Create join and add unique column

It all helped me thanks - just not in solving this particular problem (which I have now manage to do) Thanks all. On 16 September 2014 19:33, jose isaias cabrera wrote: > > "RSmith" wrote... > > >> On 2014/09/16 15:32, Paul Sanderson wrote: >> >>> select _rowid_, *

Re: [sqlite] Create join and add unique column

"RSmith" wrote... On 2014/09/16 15:32, Paul Sanderson wrote: select _rowid_, * from tab3 does the trick - thanks all Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then fill it, some variation on this:

Re: [sqlite] Divide by 0 not giving error

> This would means that if ever an SQL statement encounters divide by > zero, the application will crash with no way handle the situation > gracefully, nor to locate the source of the problem. Seriously, what are you talking about? Why is there "no way to handle" the error, gracefully

Re: [sqlite] Create join and add unique column

On 2014/09/16 20:00, James K. Lowden wrote: Or not use a temporary table. http://www.schemamania.org/sql/#rank.rows Now easier with CTE. In theory it could be *faster* than a temporary table, because the insertion I/O is avoided. But only testing will tell. --jkl Hi James, I'm

Re: [sqlite] Automatic conversion between keywords and literals

On 15/09/14 19:28, lchis...@paradise.net.nz wrote: > I have been caught several times when a previously working piece of SQL has > silently failed, due to a column or view change in the database not matched > with > a Delphi code change, an inadvertent character injection into a column name, >

Re: [sqlite] Create join and add unique column

On Tue, 16 Sep 2014 16:38:09 +0200 RSmith wrote: > On 2014/09/16 15:32, Paul Sanderson wrote: > > select _rowid_, * from tab3 does the trick - thanks all > > Indeed, and if you are pedantic or do not work in a table with > rowids, the solution is to explicitly give the table

Re: [sqlite] Divide by 0 not giving error

On Tue, 16 Sep 2014 08:59:31 +0200 Jean-Christophe Deschamps wrote: > This would means that if ever an SQL statement encounters divide by > zero, the application will crash with no way handle the situation > gracefully, nor to locate the source of the problem. Seriously,

Re: [sqlite] Divide by 0 not giving error

On Tue, 16 Sep 2014 01:42:11 +0100 Simon Slavin wrote: > > Whether or not something "is an error" is a matter of definition. > > SQLite defines division by zero to be NULL. It's very unusual in > > that regard. > MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is

Re: [sqlite] Create join and add unique column

Back to the drawing board. Create table as creates the columns with types based on their declared affinity - I particularly need to identify columns in the new table that existed as blobs in the original tables, create table as gives these an affinity of "", I really need the original type

Re: [sqlite] Create join and add unique column

Thanks Ryan. That doesn't work for me though as I am looking for a generic solution that will work on multiple tables - so no hard coding of column definitions :( I think I am getting there On 16 September 2014 15:38, RSmith wrote: > > On 2014/09/16 15:32, Paul Sanderson

Re: [sqlite] Create join and add unique column

On 2014/09/16 15:32, Paul Sanderson wrote: select _rowid_, * from tab3 does the trick - thanks all Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then fill it, some variation on this: CREATE TEMPORARY TABLE

Re: [sqlite] Create join and add unique column

select _rowid_, * from tab3 does the trick - thanks all On 16 September 2014 13:13, Paul Sanderson wrote: > _rowid_ is probably the answer with a temporary table > > On 16 September 2014 13:00, Paul Sanderson > wrote: > >> Thanks - I

Re: [sqlite] Create join and add unique column

Try having a look at post "How to determine player's leaderboard rank efficiently?" (24th August) this could give you idea on how to solve this problem through temporary tables. Regards Il 16/set/2014 14:14 "Paul Sanderson" ha scritto: > _rowid_ is probably the

Re: [sqlite] Create join and add unique column

_rowid_ is probably the answer with a temporary table On 16 September 2014 13:00, Paul Sanderson wrote: > Thanks - I like the temporary table idea, but now sure how it would work. > > say for instance I have two tables > > create tab1 (id int, name text) > 1,

Re: [sqlite] Create join and add unique column

Thanks - I like the temporary table idea, but now sure how it would work. say for instance I have two tables create tab1 (id int, name text) 1, 'paul' 2, 'helen' 3, 'melanie' create tab2 (id int, country text) 1, 'uk' 2, 'scotland' I can create a temporary table create table tab3 as select *

[sqlite] Automatic conversion between keywords and literals

Hi all, The page http://www.sqlite.org/lang_keywords.html explains the way SQLite attempts to fix assumed misuse of single and double quotes, with the note that this (mis-)feature may go away. I would like to request a pragma or option to make it go away now :-) Although it is there for

Re: [sqlite] Bug and fix in lemon.c

Am Freitag, 12. September 2014, 18:59:24 schrieb Richard Hipp: > On Fri, Sep 12, 2014 at 7:23 AM, Benjamin Franksen < > benjamin.frank...@helmholtz-berlin.de> wrote: > > I am using the lemon parser generator for a different project. It > > appears that an attempt to pro-actively avoid a 64-bit

Re: [sqlite] Divide by 0 not giving error

What the SQL standard calls an "exception" is not necessarily exactly the same as an exception in other programming languages. Granted. If SQLite were to change the division-by-zero handling, it could be reported exactly like most other errors, by returning SQLITE_ERROR from sqlite3_step().

Re: [sqlite] Create join and add unique column

Hi, If you can insert into another table then you might want to use a trigger on the target table. I've done that with good effect (only on low volumes though, I don't know what would happen on larger volume and if you have larger volumes). Cheers, Dave Ward Analytics Ltd - information in

Re: [sqlite] Create join and add unique column

On Tue, Sep 16, 2014 at 5:51 AM, Paul Sanderson wrote: > I want to create a join on two tables and add a unique number to each > returned row. Can this be done with a SQL query? > > Thanks > -- > Paul > www.sandersonforensics.com Just a bit of thinking out loud, but

Re: [sqlite] Create join and add unique column

could create a temporary table with a incrementing key and 'insert into temp_table select join ...' something like that? maybe use the existing keys and create a composite key sorta thing? On Tue, Sep 16, 2014 at 3:51 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > I want to

[sqlite] Create join and add unique column

I want to create a join on two tables and add a unique number to each returned row. Can this be done with a SQL query? Thanks -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786

Re: [sqlite] Divide by 0 not giving error

Jean-Christophe Deschamps wrote: > There is another good reason why raising an exception would be > a terrible choice. When SQLite is used as a shared library by some > scripting language, there is /*no*/ possibility to trap exceptions > raised within the library. What the SQL standard calls an

Re: [sqlite] Divide by 0 not giving error

Dear forum, MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is enabled: SQL Server does it too unless SET ARITHABORT is ON: PostgreSQL doesn't do

Re: [sqlite] Divide by 0 not giving error

Hi, Dave, Von: Dave Wellman > The problem deals with dividing by 0. As far as I can remember, in every > programming language that I have ever used and in all databases that I've > used, if you try and divide by 0 the process will fail with a 'divide by > zero' error. Sqlite doesn't seem to

Re: [sqlite] SQL Query to Vdbe Instructions

The sqlite3_prepare() functions convert the SQL statement into an executable VDBE program. You can view the results in the Sqlite shell by typing: .explain explain ; which will show the VDBE opcodes generated for the query (the .explain switches the output format to something suitable for