Re: [sqlite] Near misses

[Eric Bohlman]

Simon Slavin wrote:
> On 26 Jun 2009, at 12:25pm, Alberto Simões wrote:
> 
>> one adition, one remotion or one substitution
> 
> I am always amazed at how well people use English.  For your word  
> 'remotion' you probably mean 'removal' or 'omission'.  You have joined  
> the two possibilities together !

Although Alberto has explained the etymology of the term, in general the 
condensation of two or more words into one is called a "portmanteau." My 
favorite portmanteau arose when about 30 years ago a co-worker reported 
that software problems on an embedded device were caused by two routines 
"interfecting with each other." Interacting, interfering, affecting, 
infecting and probably more, all packed with a remarkable economy of 
expression.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DBD::SQLite reporting corruption sqlite3 CL program does not

[Eric Bohlman]

Craig Talbert wrote:
>>From Perl, when I attempt to make a database connection using SQLite,
> I get the following error:
> 
> [Tue Jun 23 17:10:22 2009] projectory.cgi:
> DBI->connect(dbname=projectory.sqlite3) failed: database disk image is
> malformed at ./projectory.cgi line 1577
> 
> At line 1577 it is executing this code
> 
> $dbh = DBI->connect("dbi:SQLite:dbname=projectory.sqlite3","","") or
> die "$DBI::errstr\n";
> 
> When I use the sqlite3 tool to do an integrity check, I get the following:
> 
> rintintin> sqlite3 projectory.sqlite3
> SQLite version 3.6.15
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> PRAGMA integrity_check;
> ok
> sqlite> .exit

Pretty obvious, but: since your dbname is unqualified, are you sure your 
working directory in your code is the same as your current directory 
when using the command line? Also, what version of SQLite is linked into 
your DBD::SQLite3? (if you're using DBD::SQLite, make sure it's not so 
old as to be using SQLite v2; I forget when the naming change was made).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Column headers of result

[BareFeet]

Hi,

Is there any way in the command line to get the columns in a query  
result?

For example, given an ad-hoc SQL command, such as:

begin;
insert into MyTableOrView select * from SomeSource;
select * from MyTableOrView join SomeOtherTableOrView where condition;
end;

how can I get the column headers in the result?

I know I can get the column info of a table using pragma table_info,  
but I don't think that works for an ad-hoc query.

Thanks,
Tom
BareFeet

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

Re: [sqlite] [ANN] SQLiteManager 3.0

[BareFeet]

Hi Marco,

> SQLabs is proud to announce today the worldwide availability of  
> SQLiteManager 3.0, the most powerful sqlite database manager tool  
> for MacOS X and Windows.

Congrats on the new version.

I've revised my comparison table at:
http://www.tandb.com.au/sqlite/compare/?ml
to show your new version.

Please let me know of any errors or omissions there. I ran some quick  
tests on the demo version.

SQLiteManager looks great. Some initial impressions:

1. Good to see that SQLiteManager supports editing of data in views  
(where "instead of triggers" exist). (It did in v2.5 as well)

2. SQLiteManager fails in several actions when the table name (or  
probably also the column name) requires quoting (eg if the name  
contains a space)

Thanks,
Tom
BareFeet

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

Re: [sqlite] Comparison of numbers as text

[Jay A. Kreibich]

On Sun, Jun 28, 2009 at 07:59:54PM -0400, Tim Largy scratched on the wall:
> Can someone explain what is going on in the third select statement
> below? I would have expected it to return a row because the number is
> quoted.
> 
> sqlite> select 'foo' where 1 in (1, '2', 'three');
> foo
> sqlite> select 'foo' where 2 in (1, '2', 'three');
> sqlite> select 'foo' where '2' in (1, '2', 'three');
> sqlite> select 'foo' where 'three' in (1, '2', 'three');
> foo

  The third select does return 'foo' for me.
  
  Tested on 3.6.11 and 3.4.0, Mac OS X Intel.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparison of numbers as text

[P Kishor]

On 6/28/09, Tim Largy  wrote:
> Can someone explain what is going on in the third select statement
> below? I would have expected it to return a row because the number is
> quoted.
>
> sqlite> select 'foo' where 1 in (1, '2', 'three');
> foo
> sqlite> select 'foo' where 2 in (1, '2', 'three');
> sqlite> select 'foo' where '2' in (1, '2', 'three');
> sqlite> select 'foo' where 'three' in (1, '2', 'three');
> foo
>

dunno... works fine for me

[09:57 PM] ~$sqlite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 'foo' where 1 in (1, '2', 'three');
foo
sqlite> select 'foo' where 2 in (1, '2', 'three');
sqlite> select 'foo' where '2' in (1, '2', 'three');
foo
sqlite> select 'foo' where 'three' in (1, '2', 'three');
foo
sqlite>


-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor/
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
"assertions are politics... backing up assertions with evidence is science"
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comparison of numbers as text

[Tim Largy]

Can someone explain what is going on in the third select statement
below? I would have expected it to return a row because the number is
quoted.

sqlite> select 'foo' where 1 in (1, '2', 'three');
foo
sqlite> select 'foo' where 2 in (1, '2', 'three');
sqlite> select 'foo' where '2' in (1, '2', 'three');
sqlite> select 'foo' where 'three' in (1, '2', 'three');
foo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Near misses

[John Machin]

On 27/06/2009 7:00 AM, Jean-Christophe Deschamps wrote:
> At 13:25 26/06/2009, you wrote:
> ´¯¯¯
>> I am trying to find words in a dictionary stored in sqlite, and trying
>> a near miss approach.
>> For that I tried an algorithm to create patterns corresponding to
>> Levenshtein distance of 1 (edit distance of 1).
>> That means, one adition, one remotion or one substitution.
>>
>> Any hint on how to speed up this thing?
> `---
> 
> Hi,
> 
> I'm currently finishing an C extension offering, among other functions, 
> a "TYPOS" scalar operator which is meant to perform just that, and a 
> bit more.

There's a strong presumption that it doesn't handle CJK text, but what 
about alphabets other than Latin-based e.g. Arabic, Cyrillic, Greek, 
Hebrew, ...?

> Internally, it applies a Unicode fold() function,

What does fold() do? Strip off accents/umlauts/etc?

> a Unicode lower() 

upper() might be more suitable; consider the German eszett (U+00DF).

> function and then computes the Damerau-Levenshtein distance between the 
> strings.  It returns the number of insertions, omissions, change and 
> transposition (of adjacent letters only).

Consider an additional API which returns a scaled similarity score e.g 
1.0 - float(distance) / max(length(string1), length(string2))

> If the reference string is 'abcdef', it will return 1 (one typo) for
> 'abdef' missing c
> 'abcudef'   u inserted
> 'abzef' c changed into z
> 'abdcef'c & d exchanged
> 
> It will also accept a trailing '%' in string2 acting as in LIKE.
> 
> You can use it this way:
> 
>select * from t where typos(col, 'levencht%') <= 2;
> 
> or this way
> 
>select typos(str1, str2)
> 
> The code currently makes use of a couple of Win32 functions, which 
> should have Un*x equivalent.  It runs at really decent speed even if I 
> didn't fight for optimization.  It will obviously outperform any SQL 
> solution by a large factor.

Does it use the icu library? What is the memory footprint?

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

Re: [sqlite] Data Extraction Question

[Jay A. Kreibich]

On Sun, Jun 28, 2009 at 05:58:39PM -0500, Rick Ratchford scratched on the wall:
> Hello.
>  
> I have the following task:
>  
> Suppose that you have a recordset that contains the following:
>  
> DATE
> Color1
> Offset1
>  
> Okay. Now suppose you want to extract from this recordset 15 records only
> that are just before the record whose Color1 and Offset1 matches those 15
> records.
>  
> Example:
>  
> Suppose Record number 98 is our REFERENCE record. Color1 = "RED" and Offset1
> = 6.
>  
> I would like to retrieve the most recent 15 records just prior to record 98
> that also have Color1 = "RED" and Offset1 = 6.

  Since SQL tables have no inherent ordering, I'm assuming your "Record
  number 98" is just an arbitrary naming that has no specific relevance
  to this problem.  Along the same lines, the only way to define "just
  prior" is by the DATE value.

> How might this be accomplished?

  Find all records "WHERE Color1='Red' AND Offset1=6 AND DATE

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unable to write the string "1.0" into a database table

[mwnn]

chandan wrote:
> I am really sorry, The correct code is given below:
>
> //
> #include 
> #include 
> #include 
> #include 
> #include 
>
> const char *create_and_insert = "create table some_tbl (id int primary 
> key, version text check (version in (\"1.0\")));"
> "insert into some_tbl (id) values (1);";
> const char *update_sql = "update some_tbl set version = ? where id = ?";
>
> int32_t main(int32_t argc, char *argv[])
> {
> sqlite3 *db;
> char *err_msg;
> sqlite3_stmt *stmt;
> int32_t ret;
>
> if (argc != 2) {
> fprintf(stderr, "Usage: %s .\n", argv[0]);
> goto out1;
> }
>
> db = NULL;
> ret = sqlite3_open(argv[1], );
> if (ret != SQLITE_OK) {
> fprintf(stderr, "Unable to open database.\n");
> goto out1;
> }
>
> err_msg = NULL;
> ret = sqlite3_exec(db, create_and_insert, NULL, NULL, _msg);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_exec: %s.\n", err_msg);
> sqlite3_free(err_msg);
> }
>
> stmt = NULL;
> ret = sqlite3_prepare_v2(db, update_sql, strlen(update_sql) + 1,
>  , NULL);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_stmt: %s", sqlite3_errmsg(db));
> goto out2;
> }
>
> /* The second argument indicates the posistion of the column */
> ret = sqlite3_bind_text(stmt, 1, "1.0", strlen("1.0") + 1,
> SQLITE_TRANSIENT);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_text: %s",
> sqlite3_errmsg(db));
> goto out3;
> }
>
> ret = sqlite3_bind_int(stmt, 2, 0);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_int: %s",
> sqlite3_errmsg(db));
> goto out3;
> }
>
> ret = sqlite3_step(stmt);
> if (ret != SQLITE_DONE) {
> fprintf(stderr, "sqlite3_step: %s",
> sqlite3_errmsg(db));
> goto out3;
> }
>
> ret = sqlite3_finalize(stmt);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_finalize: %s",
> sqlite3_errmsg(db));
> }
>
> ret = sqlite3_close(db);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "Unable to close the database.\n");
> }
>
> exit(0);
>
>  out3:
> ret = sqlite3_finalize(stmt);
> if (ret != SQLITE_OK) {
> fprintf(stderr, "sqlite3_finalize: %s",
> sqlite3_errmsg(db));
> }
>  out2:
> ret = sqlite3_close(db);
>  out1:
> exit(1);
> }
> /*/
>
> Simon Slavin wrote:
>   
>> On 27 Jun 2009, at 8:47am, chandan wrote:
>>
>>   
>> 
>>> const char *create_and_insert = "create table some_tbl (id int primary
>>> key, version text check (version in (\"1.0\")));"
>>>"insert into some_tbl (id) values (1);";
>>> const char *update_sql = "update some_tbl set version = ? where id  
>>> = ?";
>>> 
>>>   
>> I note you then do
>>
>> ret = sqlite3_bind_int(stmt, 2, 0);
>>
>> doesn't this look for id=2 ?
>> 
The value "2" indicates the position of the ? in the SQL statement. 
Since the value of id is 0(zero), i am binding the value 0 to the second ?.
>>
>>
>>
>> To diagnose your problem, first try the whole thing as text: execute  
>> the command
>>
>> update some_tbl set version = '1.0' where id = 1
>> 
When i execute the above the query using sqlite3_exec() the database is 
updated correctly without any issues.
>> and see if it works.  If it doesn't, try it in sqlite3 command-line  
>> tool and see if that works.
>>
>> Simon.
>> ___
>> 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] Near misses

[Alberto Simões]

On Fri, Jun 26, 2009 at 10:00 PM, Jean-Christophe
Deschamps wrote:
> Hi,
>
> I'm currently finishing an C extension offering, among other functions,
> a "TYPOS" scalar operator which is meant to perform just that, and a
> bit more.
>
> Internally, it applies a Unicode fold() function, a Unicode lower()
> function and then computes the Damerau-Levenshtein distance between the
> strings.  It returns the number of insertions, omissions, change and
> transposition (of adjacent letters only).
>
> If the reference string is 'abcdef', it will return 1 (one typo) for
> 'abdef'     missing c
> 'abcudef'   u inserted
> 'abzef'     c changed into z
> 'abdcef'    c & d exchanged
>
> It will also accept a trailing '%' in string2 acting as in LIKE.
>
> You can use it this way:
>
>   select * from t where typos(col, 'levencht%') <= 2;
>
> or this way
>
>   select typos(str1, str2)
>
> The code currently makes use of a couple of Win32 functions, which
> should have Un*x equivalent.  It runs at really decent speed even if I
> didn't fight for optimization.  It will obviously outperform any SQL
> solution by a large factor.
>
> I can't promise a very clean version tomorrow but just mail if you're
> interested in the C source. You could tailor it to your precise needs
> easily.

I can't help and test it in the next few days. But I would be happy to
test and give some results about it
Cheers

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

[sqlite] (no subject)

[ArbolOne]

humm

-- 
ArbolOne.org specializes in Custom Web Site Design, Web Site Re Design, Web 
Site Template Modifications, Web Site Maintenance, Integration of Payment 
Gateways (API's), Database Applications, Custom Applications and much more. 

416.838.2057
arbol...@gmail.com

"O Allah, make my love for You the most beloved thing to me, and my fear for 
You the most fearful thing to me, and remove from me all worldly needs and 
wants by instilling a passion for meeting You, make the coolness of my eyes to 
worshipping You.”

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