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

[Christian Niles]

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 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) or ' ' (space) as delimiters:
> 
> CREATE VIRTUAL TABLE documents USING fts4(title, content, tokenize=simple
> '' '# ');
> 
> Above, the first argument is an empty string (simple tokenizer ignores the
> first argument), and the second argument is the list of delimiters to use.
> 
> There was a brief discussion on this list about the feature in 2012 [1].
> 
> Quote (regarding lack of documentation):
> "Likely the reason is that we forgot that this feature even exists.  It
> seems to have existed in the simple tokenizer, unchanged, since the
> original introduction of FTS1 back in 2006."
> 
> Quote (regarding whether it's safe to use the feature):
> "But it has been in the code for so long now that we dare not
> change it for fear of breaking long-established programs."
> 
> ...however it was also mentioned that the feature is not likely to have
> been tested thoroughly.
> 
> Nonetheless, the relevant source code looks fairly straightforward [2].
> 
> In a current project, we are doing tokenization outside of SQLite. And so
> the ability to tell SQLite which delimiter character we have used, without
> needing to import ICU etc. is very appealing.
> 
> Can we document this hidden feature?
> 
> Best regards,
> Niall Gallagher
> 
> [1] Previous discussion:
> http://article.gmane.org/gmane.comp.db.sqlite.general/74199
> [2] Source code:
> http://www.sqlite.org/src/artifact/5c98225a53705e5ee34824087478cf477bdb7004
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maverick RoR ans sqlite3

[Keith Medcalf]

>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 columns.  (See .width)
 html HTML  code
 insert   SQL insert statements for TABLE
 line One value per line
 list Values delimited by .separator string
 tabs Tab-separated values

as explained at http://www.sqlite.org/cli.html

The table name parameter is only required for .mode insert where it specifies 
the string to be used for table in the constructed insert into statements:

sqlite> create table x(a,b);
sqlite> insert into x values (1,1), (2,2);
sqlite> .mode insert
sqlite> select * from x;
INSERT INTO table VALUES(1,1);
INSERT INTO table VALUES(2,2);
sqlite> .mode insert AbraCadAbra
sqlite> select * from x;
INSERT INTO AbraCadAbra VALUES(1,1);
INSERT INTO AbraCadAbra VALUES(2,2);
sqlite>

For all other formats the extraneous characters have no effect.  Perhaps the 
difference is that one version of the sqlite shell silently ignores the 
meaningless extra parameter, but the other is not silent and lets you know that 
you have made an error.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Niall Gallagher]

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) or ' ' (space) as delimiters:

CREATE VIRTUAL TABLE documents USING fts4(title, content, tokenize=simple
'' '# ');

Above, the first argument is an empty string (simple tokenizer ignores the
first argument), and the second argument is the list of delimiters to use.

There was a brief discussion on this list about the feature in 2012 [1].

Quote (regarding lack of documentation):
"Likely the reason is that we forgot that this feature even exists.  It
seems to have existed in the simple tokenizer, unchanged, since the
original introduction of FTS1 back in 2006."

Quote (regarding whether it's safe to use the feature):
"But it has been in the code for so long now that we dare not
change it for fear of breaking long-established programs."

...however it was also mentioned that the feature is not likely to have
been tested thoroughly.

Nonetheless, the relevant source code looks fairly straightforward [2].

In a current project, we are doing tokenization outside of SQLite. And so
the ability to tell SQLite which delimiter character we have used, without
needing to import ICU etc. is very appealing.

Can we document this hidden feature?

Best regards,
Niall Gallagher

[1] Previous discussion:
http://article.gmane.org/gmane.comp.db.sqlite.general/74199
[2] Source code:
http://www.sqlite.org/src/artifact/5c98225a53705e5ee34824087478cf477bdb7004
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Maverick RoR ans sqlite3

[Bjorn]

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), "address" varchar(255), "group" integer, "created_at" datetime, 
"updated_at" datetime);
 
sqlite> .mode csv consult_facts
Error: invalid arguments:  "consult_facts". Enter ".help" for help
 
Are there known incompatibilities between ruby (2.0.0p451 (2014-02-24 revision 
45167)) and sqlite3 (3.7.13 2012-07-17) versions? If so how do I proceed?

Is this a Maverick (OSX) problem - I had no issues with snow leopard?

Thanks

Bjorn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Squeal, a Swift interface to SQLite

[Christian Niles]

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 exposes access to 
prepared statements, parameter binding, and other features of SQLite. My goal 
is to eventually expose all of the C/C++ interface as makes sense.

I’d appreciate any feedback, and especially pull requests! I’ve posted to HN as 
well, in case this isn’t the best list for this sort of discussion:

https://news.ycombinator.com/item?id=8324461

Thanks!
- christian.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Keith Medcalf]

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, text columns will not carry over the collation 
to the new table definition.  If you need the columns to have a specific 
collation, or columns containing computed results to have a specific affinity 
(and you do not want to always have to cast(...) either the source or 
destination, then you need to create the destination table first, then insert 
into ... as select ...;


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of RSmith
>Sent: Tuesday, 16 September, 2014 08:38
>To: sqlite-users@sqlite.org
>Subject: 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 tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT, country TEXT);
>INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
>tab2 C;
>
>tab 3 should now look like this (according to your sample tables):
>rowNo|   name   |   country
>-
> 1 |  paul   | uk
> 2 |  paul   | scotland
> 3 |  helen | uk
> 4 |  helen | scotland
> 5 |  melanie | uk
> 6 |  melanie | scotland
>
>
>NOTE:
>In-case you are not familiar with it - That insert omits the rowNo and
>can be thought of as a variation of this query which achieves
>the same:
>INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
>FROM tab1 N, tab2 C;
>
>
>Hope that widens your SQL arsenal another micron, Cheers!
>Ryan
>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Petite Abeille]

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' as name union all
  select  3 as id, 'melanie' as name
),
CountrySet
as
(
  select  1 as id, 'uk' as name union all
  select  20 as id, 'scotland' as name -- 
),
DataSet
as
(
  select  NameSet.id || '.' || CountrySet.id as key,
  NameSet.id as name_id,
  NameSet.name as name_name,
  CountrySet.id as country_id,
  CountrySet.name as country_name
  fromNameSet
  cross join  CountrySet
)
selectcount( * ) as id,
  DataSet.name_id as name_id,
  DataSet.name_name as name_name,
  DataSet.country_id as country_id,
  DataSet.country_name as country_name
from  DataSet

join  DataSet self
onself.key >= DataSet.key

group by  DataSet.name_id,
  DataSet.name_name,
  DataSet.country_id,
  DataSet.country_name

order by  1;


> id|name_id|name_name|country_id|country_name
> 1|3|melanie|2|scotland
> 2|3|melanie|1|uk
> 3|2|helen|2|scotland
> 4|2|helen|1|uk
> 5|1|paul|2|scotland
> 6|1|paul|1|uk


Or something :D

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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_, * 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 tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT, country TEXT);
>> INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
>> tab2 C;
>>
>> tab 3 should now look like this (according to your sample tables):
>> rowNo|   name   |   country
>> -
>> 1 |  paul   | uk
>> 2 |  paul   | scotland
>> 3 |  helen | uk
>> 4 |  helen | scotland
>> 5 |  melanie | uk
>> 6 |  melanie | scotland
>>
>>
>> NOTE:
>> In-case you are not familiar with it - That insert omits the rowNo and
>> can be thought of as a variation of this query which achieves the same:
>> INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
>> FROM tab1 N, tab2 C;
>>
>>
>> Hope that widens your SQL arsenal another micron, Cheers!
>>
>
> Ryan,
>
> I don't know if it helped Paul at all, but it did widen my SQL arsenal a
> good 7-8 microns.  Thanks.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[jose isaias cabrera]

"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:


CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name 
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, 
tab2 C;


tab 3 should now look like this (according to your sample tables):
rowNo|   name   |   country
-
1 |  paul   | uk
2 |  paul   | scotland
3 |  helen | uk
4 |  helen | scotland
5 |  melanie | uk
6 |  melanie | scotland


NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can 
be thought of as a variation of this query which achieves the same:
INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country 
FROM tab1 N, tab2 C;



Hope that widens your SQL arsenal another micron, Cheers!


Ryan,

I don't know if it helped Paul at all, but it did widen my SQL arsenal a 
good 7-8 microns.  Thanks.


josé 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Jean-Christophe Deschamps]

> 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 otherwise?  How do you know there would be no way
to "locate the source of the problem"?

I imagine an error SQLITE_EMATH returned by sqlite4_step.  With some
care, perhaps the expression returning zero could be mentioned in the
error text.  I can't imagine how that would present a problem.


Yes but raising an exception has been mentionned at some point in the 
discussion. I was just saying that doing so is pretty different from 
returning an error at function-level. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[RSmith]

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 well familiar with the ranking SQL (thanks to your site btw), but I fail to see how that can be applied to solve the OP's 
problem which I will list again hereunder.  I know the OP in the meantime realized he had other problems which renders any of these 
solutions unhelpful, but allow me the learning experience please, could you show how to achieve this in SQL via the ranking method 
you linked (or any other method you might come up with - I can do it in CTE, but am hoping to learn how to do without):


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'


what I want is

1 | 1 | paul  | 1 | uk
2 | 1 | paul  | 2 | scotland
3 | 2 | helen  | 1 | uk
4 | 2 | helen  | 2 | scotland
5 | 3 | melanie | 1 | uk
6 | 3 | melanie | 2 | scotland
 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic conversion between keywords and literals

[Roger Binns]

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, 
> or
> when placing double quotes around a tablename.columnname in the Delphi source
> for tidyness/completeness I have put quotes around the whole thing rather than
> the individual elements.

I've been pushing for a "lint mode" where SQlite would flag this and similar
issues.  It would help developers for testing and result in more robust code
using SQLite.  To amuse yourself try doing a "natrual" join (sic) sometime!
 Sadly the SQLite team rejected it:

  http://www.sqlite.org/src/tktview?name=25e09aa2ab

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[James K. Lowden]

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 definition

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
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[James K. Lowden]

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, what are you talking about?  Why is there "no way to handle"
the error, gracefully otherwise?  How do you know there would be no way
to "locate the source of the problem"?

I imagine an error SQLITE_EMATH returned by sqlite4_step.  With some
care, perhaps the expression returning zero could be mentioned in the
error text.  I can't imagine how that would present a problem.   

--jkl



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[James K. Lowden]

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 enabled:
...
> PostgreSQL doesn't do it, and that annoys some people

MySQL is a model for what not to do.  Postgres has been the model for
SQLite.  

> SQL Server does it too unless SET ARITHABORT is ON:
> 
> 

That setting has always been ON by default.  It's interesting to compare
that page to the current version, 

http://msdn.microsoft.com/en-us/library/ms190306.aspx

which advises against turning it off.  AFAIK in SQL Server the
only choices are an error message or a warning message.  There's
no option for SQLite's silent convert-to-NULL behavior.  The
application can achieve that by suppressing the message, of course.  

> NULLs propagate harmlessly downstream.  

FSVO harmlessly.  I understand if you want an average of zero things,
maybe NULL is a nice default.  If it is, though, you always have the
choice of 

case N when 0 then NULL else sum(A)/N end as Mean

The problem is, it's not always harmless.  You like it because you can
print them and eyeball them.  But in dealing with large datasets
updated by an external process, it is often the case that the
denominator should never be zero.  Examples include the Price/Book
ratio for a stock or the current constituency of the S 500.  With a
large enough compound computation, NULL can be a legitimate result
(because the quotient is added to or multiplied by NULL), but a zero in
the divisor is an error.  

Yes, you can check.  You can make a separate pass over the data to
ensure none of the divisors are zero.  Two ramifications to that
approach: 

1.  It doubles the work for a rare condition. 
2.  If it's not done, the NULL will conceal the condition. 

You can save #1 by returning the divisor in the output and checking it
in the application.  That leaves #2, which is inescapable.  

If OTOH the system produces an error for divide by zero, that can be
trapped, or prevented by the above SQL.  

> Errors crash the program.  

Why?  Perhaps divide-by-zero presents a difficulty to the programmer
who doesn't account for it.  If so, it won't be the last one;
error-handling is a big part of programming.  

And therein lies the rub.  Errors are a fact of life, and disguising
them is no help.  You can't extract a weekday from a non-date; you
can't take the log of a negative. And you can't divide by zero.  It's
not special.  Zero is invalid input as a divisor, just as much as 13 is
invalid as a month.  Errors are errors.  Report them, full stop, and be
done with it. 

My basic argument is very,very simple: division by zero is an error.
It is not special in any way.  If you want SQLite to ignore it, you
have to explain either why it's special, or agree that *all* domain
errors should be converted to NULL.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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 definition :(

On 16 September 2014 18:18, Paul Sanderson 
wrote:

> 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 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 tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT, country TEXT);
>> INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
>> tab2 C;
>>
>> tab 3 should now look like this (according to your sample tables):
>> rowNo|   name   |   country
>> -
>> 1 |  paul   | uk
>> 2 |  paul   | scotland
>> 3 |  helen | uk
>> 4 |  helen | scotland
>> 5 |  melanie | uk
>> 6 |  melanie | scotland
>>
>>
>> NOTE:
>> In-case you are not familiar with it - That insert omits the rowNo and
>> can be thought of as a variation of this query which achieves the same:
>> INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
>> FROM tab1 N, tab2 C;
>>
>>
>> Hope that widens your SQL arsenal another micron, Cheers!
>> Ryan
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> 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
> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> Deleted SQLite recovery
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>
>


-- 
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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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 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 tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name
> TEXT, country TEXT);
> INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
> tab2 C;
>
> tab 3 should now look like this (according to your sample tables):
> rowNo|   name   |   country
> -
> 1 |  paul   | uk
> 2 |  paul   | scotland
> 3 |  helen | uk
> 4 |  helen | scotland
> 5 |  melanie | uk
> 6 |  melanie | scotland
>
>
> NOTE:
> In-case you are not familiar with it - That insert omits the rowNo and can
> be thought of as a variation of this query which achieves the same:
> INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
> FROM tab1 N, tab2 C;
>
>
> Hope that widens your SQL arsenal another micron, Cheers!
> Ryan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[RSmith]

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 tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name 
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, tab2 C;

tab 3 should now look like this (according to your sample tables):
rowNo|   name   |   country
-
1 |  paul   | uk
2 |  paul   | scotland
3 |  helen | uk
4 |  helen | scotland
5 |  melanie | uk
6 |  melanie | scotland


NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can be thought of as a variation of this query which achieves 
the same:

INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country FROM 
tab1 N, tab2 C;


Hope that widens your SQL arsenal another micron, Cheers!
Ryan


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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 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 * from tab1, tab2
>>
>> and I get values
>>
>> 1|paul|1|uk
>> 1|paul|2|scotland
>> 2|helen|1|uk
>> 2|helen|2|scotland
>> 3|melanie|1|uk
>> 3|melanie|2|scotland
>>
>> what I want is
>>
>> 1|1|paul|1|uk
>> 2|1|paul|2|scotland
>> 3|2|helen|1|uk
>> 4|2|helen|2|scotland
>> 5|3|melanie|1|uk
>> 6|3|melanie|2|scotland
>>
>> How do I get that extra column?
>>
>> Cheers
>> Paul
>>
>>
>>
>>
>>
>> On 16 September 2014 12:26, Dave Wellman 
>> wrote:
>>
>>> 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 motion
>>> Tel: +44 (0) 118 9740191
>>> Fax: +44 (0) 118 9740192
>>> www: http://www.ward-analytics.com
>>>
>>> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
>>> United Kingdom, GU1 3SR
>>> Registered company number: 3917021 Registered in England and Wales.
>>>
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org
>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker
>>> Sent: 16 September 2014 12:02
>>> To: General Discussion of SQLite Database
>>> Subject: 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 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
>>> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
>>> > Deleted SQLite recovery
>>> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>>> > processing made easy ___
>>> > sqlite-users mailing list
>>> > sqlite-users@sqlite.org
>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> 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
>> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
>> Deleted SQLite recovery
>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>> processing made easy
>>
>>
>
>
> --
> 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
> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> Deleted SQLite recovery
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>
>


-- 
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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Alessandro Marzocchi]

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 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, '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 * from tab1, tab2
> >
> > and I get values
> >
> > 1|paul|1|uk
> > 1|paul|2|scotland
> > 2|helen|1|uk
> > 2|helen|2|scotland
> > 3|melanie|1|uk
> > 3|melanie|2|scotland
> >
> > what I want is
> >
> > 1|1|paul|1|uk
> > 2|1|paul|2|scotland
> > 3|2|helen|1|uk
> > 4|2|helen|2|scotland
> > 5|3|melanie|1|uk
> > 6|3|melanie|2|scotland
> >
> > How do I get that extra column?
> >
> > Cheers
> > Paul
> >
> >
> >
> >
> >
> > On 16 September 2014 12:26, Dave Wellman 
> > wrote:
> >
> >> 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 motion
> >> Tel: +44 (0) 118 9740191
> >> Fax: +44 (0) 118 9740192
> >> www: http://www.ward-analytics.com
> >>
> >> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
> >> United Kingdom, GU1 3SR
> >> Registered company number: 3917021 Registered in England and Wales.
> >>
> >>
> >> -Original Message-
> >> From: sqlite-users-boun...@sqlite.org
> >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker
> >> Sent: 16 September 2014 12:02
> >> To: General Discussion of SQLite Database
> >> Subject: 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 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
> >> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> >> > Deleted SQLite recovery
> >> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> >> > processing made easy ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > 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
> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> > Deleted SQLite recovery
> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> > processing made easy
> >
> >
>
>
> --
> 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
> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> Deleted SQLite recovery
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

_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, '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 * from tab1, tab2
>
> and I get values
>
> 1|paul|1|uk
> 1|paul|2|scotland
> 2|helen|1|uk
> 2|helen|2|scotland
> 3|melanie|1|uk
> 3|melanie|2|scotland
>
> what I want is
>
> 1|1|paul|1|uk
> 2|1|paul|2|scotland
> 3|2|helen|1|uk
> 4|2|helen|2|scotland
> 5|3|melanie|1|uk
> 6|3|melanie|2|scotland
>
> How do I get that extra column?
>
> Cheers
> Paul
>
>
>
>
>
> On 16 September 2014 12:26, Dave Wellman 
> wrote:
>
>> 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 motion
>> Tel: +44 (0) 118 9740191
>> Fax: +44 (0) 118 9740192
>> www: http://www.ward-analytics.com
>>
>> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
>> United Kingdom, GU1 3SR
>> Registered company number: 3917021 Registered in England and Wales.
>>
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker
>> Sent: 16 September 2014 12:02
>> To: General Discussion of SQLite Database
>> Subject: 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 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
>> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
>> > Deleted SQLite recovery
>> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>> > processing made easy ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> 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
> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> Deleted SQLite recovery
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>
>


-- 
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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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 * from tab1, tab2

and I get values

1|paul|1|uk
1|paul|2|scotland
2|helen|1|uk
2|helen|2|scotland
3|melanie|1|uk
3|melanie|2|scotland

what I want is

1|1|paul|1|uk
2|1|paul|2|scotland
3|2|helen|1|uk
4|2|helen|2|scotland
5|3|melanie|1|uk
6|3|melanie|2|scotland

How do I get that extra column?

Cheers
Paul





On 16 September 2014 12:26, Dave Wellman 
wrote:

> 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 motion
> Tel: +44 (0) 118 9740191
> Fax: +44 (0) 118 9740192
> www: http://www.ward-analytics.com
>
> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
> United Kingdom, GU1 3SR
> Registered company number: 3917021 Registered in England and Wales.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker
> Sent: 16 September 2014 12:02
> To: General Discussion of SQLite Database
> Subject: 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 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
> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> > Deleted SQLite recovery
> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> > processing made easy ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Automatic conversion between keywords and literals

[lchishol]

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 historical compatibility, it is actually a hindrance to
new development by converting what should be a "missing column" error into a
valid literal mismatch.
I will say in advance that I know I shouldn't make silly mistakes, shouldn't
code while a zombie, should check-in more often, should test more effectively,
should track my schemas better etc but:

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, or
when placing double quotes around a tablename.columnname in the Delphi source
for tidyness/completeness I have put quotes around the whole thing rather than
the individual elements.
Obviously my tests didn't pick the problem up soon enough, although in one case
I'd changed the schema in one database file but not updated the other one and
got caught by the silent failover.

Thanks,
Len.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Benjamin Franksen]

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 problem in fact
> > created one in the first place. I am referring to the following change:
> >
> >
> > http://www3.sqlite.org/cgi/src/fdiff?sbs=1=445f18999b700d83b83a5d9be00c
> > 596546c21052=90f46af31c92b940fec25b491f39409fd95dcdfa
> >
> > which says in its comment: "Fix a typecast problem in lemon that could
> > cause problems on 64-bit machines."
> >
> > One of my users noticed that on Windows 8.1 x64 with Visual Studio 2013
> > the version of lemon I bundle with my project crashed. With a debugger
> > he found that the problem is caused exactly by the line the above patch
> > changes and found that using '(unsigned long)' in the cast solves the
> > problem. I propose to revert this change. I tested this on Linux (32 and
> > 64 Bit).
>
> I think the code is better as it stands.  And I am unable to recreate the
> problem.
>
> Why do you think (unsigned long) is better?

You are right. The crash was caused by the (unsigned long) cast, and the
(char*) cast fixes the problem. Everything is as it should be.

I was just completely confused by stuff being different on different branches
of my project. Sorry for the noise.

Cheers
Ben



Helmholtz-Zentrum Berlin für Materialien und Energie GmbH

Mitglied der Hermann von Helmholtz-Gemeinschaft Deutscher Forschungszentren e.V.

Aufsichtsrat: Vorsitzender Prof. Dr. Dr. h.c. mult. Joachim Treusch, stv. 
Vorsitzende Dr. Beatrix Vierkorn-Rudolph
Geschäftsführung: Prof. Dr. Anke Rita Kaysser-Pyzalla, Thomas Frederking

Sitz Berlin, AG Charlottenburg, 89 HRB 5583

Postadresse:
Hahn-Meitner-Platz 1
D-14109 Berlin

http://www.helmholtz-berlin.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Jean-Christophe Deschamps]

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().


It could as well deliver +/- Inf or Nan.

select 15 % 'abc' returns null as well.
Integer overflow could also raise some new SQLite-level error.
SQLite string functions could also raise new errors when invalid 
indices are provided, like substr("abc", 456, 17) which doesn't make 
any sense.


All in all I don't see any serious enough reason to change behaviors at 
this stage.
Complaints posted here about these error situations being kept silent 
are rather rare.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Dave Wellman]

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 motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker
Sent: 16 September 2014 12:02
To: General Discussion of SQLite Database
Subject: 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 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
> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - 
> Deleted SQLite recovery 
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC 
> processing made easy ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[John McKown]

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 I wonder if a RECURSIVE CTE could
be used to generate the number, somehow.

WITH RECURSIVE counter(x) AS )
   SELECT 1 AS x
   UNION ALL
   SELECT x+1 FROM counter)
SELECT x AS uniqueNo FROM counter
OUTER LEFT JOIN
( SELECT a.col1 AS col1 , b.col2 as col2 FROM table1 AS a
  JOIN
  table2 AS b
  ON a.col3 = b.col3) AS joinTable
ORDER BY col1;

That may well not work. I don't have anything around to try it on.
And, at 06:23 local time, with insufficient caffeine intake, that is
the best that I can do so far.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[J Decker]

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 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
> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
> Deleted SQLite recovery
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Create join and add unique column

[Paul Sanderson]

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
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Clemens Ladisch]

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 "exception" is not necessarily exactly
the same as an exception in other programming languages.  It just means
that the entire SQL command is aborted and has no normal result:

  $ python
  >>> import sqlite3
  >>> sqlite3.connect(':memory:').execute('select * from "no such table"')
  Traceback (most recent call last):
File "", line 1, in 
  sqlite3.OperationalError: no such table: no such table
  >>>

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().


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Jean-Christophe Deschamps]

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 it, and that annoys some people:



> It's also unhelpful because the NULL can mask an error in the
> data or logic.  I hope SQL 4.0 will define it as an error instead.

I would rather it didn't because it's yet another thing to look for 
and trap in your code, and it's one that most people will not think of 
most of the time.  It expands the test suite.  It expands your software.


NULLs propagate harmlessly downstream.  You display or print them and 
they appear as something blank or "NULL" or otherwise weird.  If a 
user finds a field unexpectedly NULL, they can figure out what the 
source of the problem is and correct the data at their leisure.  The 
rest of the program continues to function.


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.


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.


I often see experienced people here completely disregard the contexts 
where SQLite is used this way and I find it is a form of myopia (or is 
that disdain?). Just because a share of users build applications in 
languages like C[++|#], Delphi, Python, Ruby, YouNameIt with SQLite 
statically linked or embedded in the language as a standard component, 
that shouldn't hide or dismiss different contexts which don't enjoy the 
same power.


Returning null may not be the best choice but I don't see that changing 
now. Float signed infinity or Nan could be considered, anything but not 
an exception.


Errors crash the program.  Start it up again and it might just crash 
again.  Or it might crash again some unpredictable but inconvenient 
time in the future.  And as a user you can't put it right because each 
time you open the window where you can type the correct data in, the 
program crashes.  You need the help of the developer.  At 5:30pm the 
day before The Big Report is due.


Exactly!


BTW I often read here that "_sqlite3_get_table is deprecated". This 
opinion is common but is very dommageable to future uses in contexts 
where calling a shared library comes with a significant time penalty.
This API has been there for very long, is well tested and has proven 
reliability. Should it be removed anytime in the future, countless 
applications written in some scripting languages will have to replace 
it by a loop of multiple calls to the shared library, slowing down 
applications dramatically (like 20-fold or more).


Why penalize a share of users and not let this API live in the library 
forever, and why not offer its counterpart in SQLite v4?


In general I see SQLite developpers take great care for widenning 
potential use contexts of SQLite, not limiting it. Some attention is 
requested to what some may call "unusual environments", which are 
nonetheless many users everyday's reality.


Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Markus Schaber]

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 do that, it instead returns NULL.

AFAIR, there are environments where division by zero for floating points can 
lead to a +INF or NaN value without an exception.

But you're right insofar as the SQL standard seems to mandate an error in this 
case.
http://postgresql.1045698.n5.nabble.com/Division-by-zero-td1922266.html



Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Query to Vdbe Instructions

[Hick Gunter]

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 explain).

 If you just want to know what the optimizer was thinking, use:

explain query plan ;

-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Dienstag, 16. September 2014 07:53
An: sqlite-users@sqlite.org
Betreff: [sqlite] SQL Query to Vdbe Instructions

Hi,
Can you please tell me which function/set of functions convert the SQL query to 
Vdbe program ?

Thanks
Prakash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users