Re: [sqlite] Feature request: add support for COMMENT statement

[Simon Slavin]

On 18 Jun 2013, at 9:39pm, Stephen Chrzanowski  wrote:

> Just out of curiosity, either by including it as another SQLite built in
> table, or, a user added table after the fact, couldn't a relationship be
> built between the new table and a comment, or a field in a table and
> comment be made?   So for example (A very VERY loose example)
> 
> create table sqlite_comment (identifier char, comment char)
> or
> create table sqlite_comment (identifier integer, comment char)

This idea, acceptable to many users, was referred to upthread.  The OP is 
working with an existing solution which makes the creation of new tables 
undesirable.

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

Re: [sqlite] escape quote for csv import

[Petite Abeille]

On Jun 18, 2013, at 11:07 PM, Roland Hughes  wrote:

> and "tools.ietf.org" is exactly WHERE on the sqlite.org path?

"Everything is amazing and nobody is happy"  --  Louis CK 

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

Re: [sqlite] escape quote for csv import

[Roland Hughes]

and "tools.ietf.org" is exactly WHERE on the sqlite.org path?



From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Tuesday, June 18, 2013 5:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] escape quote for csv import

On Jun 18, 2013, at 10:46 PM, Roland Hughes  wrote:

> It isn't documented anywhere, but, you have to BOTH quote the string AND 
> double up the quotes inside of it.

Indeed:

   7.  If double-quotes are used to enclose fields, then a double-quote
   appearing inside a field must be escaped by preceding it with
   another double quote.  For example:

   "aaa","b""bb","ccc"


http://tools.ietf.org/html/rfc4180
___
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] escape quote for csv import

[Petite Abeille]

On Jun 18, 2013, at 10:46 PM, Roland Hughes  wrote:

> It isn't documented anywhere, but, you have to BOTH quote the string AND 
> double up the quotes inside of it.

Indeed:

   7.  If double-quotes are used to enclose fields, then a double-quote
   appearing inside a field must be escaped by preceding it with
   another double quote.  For example:

   "aaa","b""bb","ccc"


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

Re: [sqlite] escape quote for csv import

[Roland Hughes]

Actually,

I found the "correct" solution for the version of SQLite3 bundled with Linux 
Mint13 KDE.

2in|"2"""

It isn't documented anywhere, but, you have to BOTH quote the string AND double 
up the quotes inside of it.

Something like this should be documented in the FAQ or on the home page.  I 
found it to be a very common question with a lot of worthless answers, at least 
worthless given the current state of the tools, perhaps they were valid at one 
point.

Roland

From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Tuesday, June 18, 2013 4:19 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] escape quote for csv import

On Jun 18, 2013, at 10:02 PM, Clemens Ladisch  wrote:

> (There is no official CSV standard, and there is no widely supported
> escaping mechanism.)

Perhaps. But that's not an excuse to ignore the de facto convention:

Common Format and MIME Type for Comma-Separated Values (CSV) Files
http://tools.ietf.org/html/rfc4180

Plus, that tired argument about "no official csv standard" is a bit self 
-fulfilling.
___
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] escape quote for csv import

[Roland Hughes]

Not an option, but thanks for the suggestion.

From: sqlite-users-boun...@sqlite.org on behalf of Clemens Ladisch
Sent: Tuesday, June 18, 2013 4:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] escape quote for csv import

Roland Hughes wrote:
> How does one escape a in a CSV file so it will correctly import?

The sqlite3 tool allows to configure the separator, but the quote
character for delimiting fields is hardcoded.

(There is no official CSV standard, and there is no widely supported
escaping mechanism.)

> I can only massage the CSV

Convert it into properly formatted SQL INSERT statements.


Regards,
Clemens
___
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] Feature request: add support for COMMENT statement

[Stephen Chrzanowski]

Just out of curiosity, either by including it as another SQLite built in
table, or, a user added table after the fact, couldn't a relationship be
built between the new table and a comment, or a field in a table and
comment be made?   So for example (A very VERY loose example)

create table sqlite_comment (identifier char, comment char)
or
create table sqlite_comment (identifier integer, comment char)

Identifier could either be a fully qualified name (IE: tMyTable or
tMyTable.UserDetails) and comment could be the required meta data?


On Tue, Jun 18, 2013 at 1:05 PM, Klaas V  wrote:

> Dear fellow users and managers of SQLite,
>
> Alexey Pechnikov wrote:
>
> >Bad idea. The schema definition can't be modified!
>
> In fact it's possible if you use the writable schema pragma
> http://www.sqlite.org/pragma.html#pragma_writable_schema
>
> An application could drop all applicable `ls -l` and `id -P`-results in a
> database, fire triggers execute one or more smart functions and "presto".
> Can be done, me seems. This way not such a bad idea as Alexey thinks.
>
> As said in the link  you have to know well what you're doing, but this is
> a good advice always.
>
> Kind regards,
> Klaas `Z4us` V
>
> ___
> 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] escape quote for csv import

[Petite Abeille]

On Jun 18, 2013, at 10:02 PM, Clemens Ladisch  wrote:

> (There is no official CSV standard, and there is no widely supported
> escaping mechanism.)

Perhaps. But that's not an excuse to ignore the de facto convention:

Common Format and MIME Type for Comma-Separated Values (CSV) Files
http://tools.ietf.org/html/rfc4180

Plus, that tired argument about "no official csv standard" is a bit self 
-fulfilling. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] escape quote for csv import

[Igor Tandetnik]

On 6/18/2013 4:02 PM, Clemens Ladisch wrote:

(There is no official CSV standard, and there is no widely supported
escaping mechanism.)


Well, there's http://tools.ietf.org/html/rfc4180
--
Igor Tandetnik

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

Re: [sqlite] escape quote for csv import

[Clemens Ladisch]

Roland Hughes wrote:
> How does one escape a in a CSV file so it will correctly import?

The sqlite3 tool allows to configure the separator, but the quote
character for delimiting fields is hardcoded.

(There is no official CSV standard, and there is no widely supported
escaping mechanism.)

> I can only massage the CSV

Convert it into properly formatted SQL INSERT statements.


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

Re: [sqlite] How to get the longest common sequence using C

[Dan Kennedy]

On 06/19/2013 02:17 AM, Stadin, Benjamin wrote:

Hi,

 From the documentation on fts3 it is not clear to me how to access the
longest common distance structure. Could you please provide an example how
to calculate the offset to the stuct inside the match info?

According to the docs I thought it should be something like this, but so
far no luck:

unsigned int *longestCommonSubsequence = [2 + (nPhrase*nCol *
3) + iCol * iPhrase];



I thought hat should be the lcs offset of the match info struct when using
"matchinfo(some_fts3_table, 'pcxs')" but I seem to be missing something.

Regards
Ben

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


int nPhrase = aMatchinfo[0];
int nCol = aMatchinfo[1];
int iOff = 1 + 1 + nPhrase*nCol*3 + iCol;
unsigned int longestCommonSubsequence = aMatchinfo[iOff];

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

[sqlite] How to get the longest common sequence using C

[Stadin, Benjamin]

Hi,

>From the documentation on fts3 it is not clear to me how to access the
longest common distance structure. Could you please provide an example how
to calculate the offset to the stuct inside the match info?

According to the docs I thought it should be something like this, but so
far no luck:

unsigned int *longestCommonSubsequence = [2 + (nPhrase*nCol *
3) + iCol * iPhrase];



I thought hat should be the lcs offset of the match info struct when using
"matchinfo(some_fts3_table, 'pcxs')" but I seem to be missing something.

Regards
Ben

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

[sqlite] escape quote for csv import

[Roland Hughes]

I'm sure this question has been asked a thousand times, but I did not find a 
useable answer anywhere on the web.  

How does one escape a in a CSV file so it will correctly import?

I have data coming from a translation database.  No, I cannot go fix the 
database, I can only massage the CSV and possibly request a tiny tweak to the 
module which generates the csv file.  The source database is NOT sqlite.

We have many occurrences where I need to escape a single " so it can survive 
and get into the database.  Our separator has dutifully been changed to the | 
character.  Why does it need to survive?  There are many things like this:

English  TargetLanguage
2in 2"

Nice huh?

Under MOST text systems you can do something like  or <> to replace 
a quote and make it pass through to the database.  What escape sequences does 
the SQLite import utility recognize?

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

Re: [sqlite] Feature request: add support for COMMENT statement

[Klaas V]

Dear fellow users and managers of SQLite,

Alexey Pechnikov wrote:

>Bad idea. The schema definition can't be modified!

In fact it's possible if you use the writable schema pragma
http://www.sqlite.org/pragma.html#pragma_writable_schema

An application could drop all applicable `ls -l` and `id -P`-results in a 
database, fire triggers execute one or more smart functions and "presto".
Can be done, me seems. This way not such a bad idea as Alexey thinks.

As said in the link  you have to know well what you're doing, but this is a 
good advice always.

Kind regards,
Klaas `Z4us` V

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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Simon Davies]

On 17 June 2013 23:19, dochsm  wrote:
> I have a table in the main database called 'students' and a two page block of
> sql that processes it, referring to it as simply 'students'.
>
> Experimenting with a different approach, I created a temp table, also called
> 'students' that contained a subset of the main.students. (I intended to
> modify the sql, replacing 'students' with 'temp.students' but have not yet
> done so)
>
> However, running my original sql now, before editing it, it appears to be
> using both the table 'students' and the table temp.students as it is now
> repeating some rows.
>
> Can sombody please explain what might be happening?
> If no database prefix is given, doesn't SQLite default to the 'main'
> database or does it somehow use a combination of all the tables with the
> same name regardless of database?

Does the following output give a clue?

SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t( data text );
sqlite> create temp table t( data text );
sqlite> insert into t values( 'main' );
sqlite> insert into temp.t values( 'temp' );
sqlite>
sqlite>
sqlite> select * from t;
main
temp
sqlite> select * from main.t;
sqlite> select * from temp.t;
main
temp
sqlite> insert into main.t values( 'really main' );
sqlite> select * from t;
main
temp
sqlite> select * from main.t;
really main
sqlite> select * from temp.t;
main
temp
sqlite>

It looks like the temp db is searched first for a match of table name...

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

[sqlite] How to select from a temp table with same name as a main table.

[dochsm]

I have a table in the main database called 'students' and a two page block of
sql that processes it, referring to it as simply 'students'. 

Experimenting with a different approach, I created a temp table, also called
'students' that contained a subset of the main.students. (I intended to
modify the sql, replacing 'students' with 'temp.students' but have not yet
done so)

However, running my original sql now, before editing it, it appears to be
using both the table 'students' and the table temp.students as it is now
repeating some rows.

Can sombody please explain what might be happening?
If no database prefix is given, doesn't SQLite default to the 'main'
database or does it somehow use a combination of all the tables with the
same name regardless of database?





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: add support for COMMENT statement

[Alexey Pechnikov]

Hello!

> Roman Fleysher Mon, 17 Jun 2013 09:14:59 -0700

> ... However, I do not understand why new
> functionality of SQLite is needed. Why can't the mapper use a special
table of
> three columns (I will use example from Alexey):

We can replace the "drop column" functionality by easy SQL script but we
can't replace the COMMENT statements functionality without uncontrolled
grow of database schema complexity and the loss of independance of single
table.

Main questions:
Can you dump only single table from your database and load it into second
database?
Can you use Fossil SCM or other for easy versioning schemas of your tables
independently?

And optionally:
Can you easy transform your database dump by shell utilities (sed,
awk,etc)?
Can you search by grep dump of your database?

The really simple and useful ideology:

sqlite3 1.db '.dump'|sed ... | sqlite3 2.db

or

sqlite3 1.db '.dump' > dump.sql
fossil diff dump.sql

With SQLite we can do many things very simple. Why not?

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Custom Ranking: How to get length of search word

[Stadin, Benjamin]

Hi,

I'm implementing a custom rank function which should give an exact match a
higher score. So in the ranking function I get the length of the found
matches via offsets(). But how can I get the length of the original match
search word? match info() doesn't provide this.

In addition, is it possible to get the actual matched term?

Kind Regards
Ben

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