Re: [sqlite] comma-separated string data

[James K. Lowden]

On Fri, 4 Apr 2014 14:20:57 -0400
"peter korinis"  wrote:

> How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to
> extract these values and use them in an SQL statement, perhaps a
> WHERE id='66'?

http://www.schemamania.org/sql/#lists

HTH, really.  

--jkl

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

[sqlite] Bug with FTS3 parenthesis and ICU (2)

[Stadin, Benjamin]

(Please disregard my first email. I hit the wrong button and sent the
email to early.)

It seems that creating a virtual FTS3 table with ICU tokenizer breaks
FTS3 parenthesis. Example:

— Create test table participant
CREATE VIRTUAL TABLE 'participant' USING fts4 (tokenize=icu de_DE,
firstName, lastName);


— Create some test entries. E.g. „Christian“, „Christopher“, „Christina“
INSERT into…..

— Select using MATCH with FTS3 parenthesis
SELECT * FROM participant WHERE participant MATCH '(lastname:c*) OR
(firstname:c*)' ORDER BY lastname, firstname


==> This doesn’t return any results.

— Select using MATCH without parenthesis:

SELECT * FROM participant WHERE participant MATCH 'lastname:c* OR
firstname:c*' ORDER BY lastname, firstname

==> This returns our three test entries



However, when the table is created without the tokenize option, the MATCH
statement with parenthesis works as expected and returns the three
results. Matching and sorting using ICU otherwise works (without
parenthesis). 

I compile SQLite with ICU for the iPhone with the following flags:


#define SQLITE_ENABLE_FTS4
#define SQLITE_ENABLE_RTREE
#define SQLITE_ENABLE_FTS3_PARENTHESIS
#define SQLITE_ENABLE_ICU
#define SQLITE_ENABLE_MEMSYS5
#define SQLITE_ENABLE_FTS4_UNICODE61

Ben

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

[sqlite] Bug with FTS3 parenthesis and ICU

[Stadin, Benjamin]

It seems like that creating a virtual FTS3 table with ICU tokenizer breaks
FTS3 parenthesis. Example:

SQLITE_ENABLE_FTS3_PARENTHESIS


SQLite is of course built with SQLITE_ENABLE_FTS3_PARENTHESIS, ICU, etc.

I compile SQLite with ICU for the iPhone with the following flags:
. Though I think it shouldn¹t matter.

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

[sqlite] sqlite3 shell man page not installed

[Andy Goth]

"make install" in the SQLite source tree (obtained via Fossil) does not 
install the sqlite3 shell man page, sqlite3.1.


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

Re: [sqlite] Problem using ICU

[Stadin, Benjamin]

Answering to my own question. After some lengthy debugging session I
finally figured that
it’s a problem with the ICU library which I build with SQLite for iOS.
The problem was related to ICU’s obscure loading mechanism.

Ben


Am 03.04.14 19:37 schrieb "Stadin, Benjamin" unter
:

>I¹m having problems to return results from a FTS4 table using ICU.
>
>I¹m doing the usual routine to load an ICU collation:
>
>1) Directly after opening the DB:
>
>SELECT icu_load_collation('de_DE', 'LOCALIZED');
>
>2) Creating some table
>
>CREATE VIRTUAL TABLE ¹sometable' USING fts4 (tokenize='icu' 'LOCALIZED',
>status, firstName, lastName)
>
>
>3) Then inserting some Date via prepared statement and binding params
>
>4) List all entries
>
>SELECT * FROM sometable
>
>So far, so good. The collation loads ok, and all entries in the table are
>listed. 
>
>5) But when I use a MATCH statement, I just get no results back from the
>table:
>
>SELECT * FROM sometable WHERE sometable MATCH '(lastname:a*) OR
>(firstname:a*)' ORDER BY lastname, firstname COLLATE LOCALIZED
>
>
>
>It is definitely related to the CREATE table with "tokenize='icu'
>LOCALIZEDŒ³. When I create the table without this, I get the expected
>results from the very same query. But I¹m completely stuck here, because I
>just get an empty result set and don¹t see any errors at all.
>
>I should mention that I target iOS and compile in ICU with my build of
>SQLite. There is one place in SQLite which I changed like to enable
>loading an ICU data file on the iPhone like so:
>
>SQLITE_PRIVATE int sqlite3IcuInit(sqlite3 *db){
>  struct IcuScalar {
>const char *zName;/* Function name */
>int nArg; /* Number of arguments */
>int enc;  /* Optimal text encoding */
>void *pContext;   /* sqlite3_user_data()
>context */
>void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>  } scalars[] = {
>{"regexp", 2, SQLITE_ANY,  0, icuRegexpFunc},
>
>{"lower",  1, SQLITE_UTF16,0, icuCaseFunc16},
>{"lower",  2, SQLITE_UTF16,0, icuCaseFunc16},
>{"upper",  1, SQLITE_UTF16, (void*)1, icuCaseFunc16},
>{"upper",  2, SQLITE_UTF16, (void*)1, icuCaseFunc16},
>
>{"lower",  1, SQLITE_UTF8, 0, icuCaseFunc16},
>{"lower",  2, SQLITE_UTF8, 0, icuCaseFunc16},
>{"upper",  1, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
>{"upper",  2, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
>
>{"like",   2, SQLITE_UTF8, 0, icuLikeFunc},
>{"like",   3, SQLITE_UTF8, 0, icuLikeFunc},
>
>{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>  };
>
>  // begin custom ICU lib stuff
>
>  // app dir is specified here because i'll avoid to have any calls to
>system icu data dir,
>  // which i think would happen otherwise
>(http://userguide.icu-project.org/icudata)
>  const char *icuDatPath = getPathForICU();
>  u_setDataDirectory(icuDatPath);
>
>  const char *icuBuf = icuData();
>  if (icuBuf != NULL) {
>  UErrorCode err = U_ZERO_ERROR;
>  udata_setAppData_53(getPathForICUCommonDataFile(), , );
>  }
>
>  // end custom ICU stuff
>
>
>  int rc = SQLITE_OK;
>  int i;
>
>  for(i=0; rc==SQLITE_OK && i<(int)(sizeof(scalars)/sizeof(scalars[0]));
>i++){
>struct IcuScalar *p = [i];
>rc = sqlite3_create_function(
>db, p->zName, p->nArg, p->enc, p->pContext, p->xFunc, 0, 0
>);
>  }
>
>  return rc;
>}
>
>
>Any pointers greatly appreciated.
>
>Regards
>Ben
>
>___
>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] comma-separated string data

[Clemens Ladisch]

peter korinis wrote:
> A data column in a link table contains comma-separated string data, where
> each value represents a value to link to another table. (many-to-many
> relationship)

Every time you use non-normalized data ... God kills a kitten.

> How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract
> these values and use them in an SQL statement, perhaps a WHERE id='66'?

The value could appear at the beginning or in the middle or at the end or
be the only value:
  ... id LIKE '66,%' OR id LIKE '%,66,%' OR id LIKE '%,66' OR id = '66'

But please, think of the kittens!  


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

[sqlite] comma-separated string data

[peter korinis]

A data column in a link table contains comma-separated string data, where
each value represents a value to link to another table. (many-to-many
relationship)

 

How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract
these values and use them in an SQL statement, perhaps a WHERE id='66'?

 

Thanks,

peter

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

Re: [sqlite] comma-separated string data

[Robert Hairgrove]

You need to normalize the database design.

--

On Fri, 2014-04-04 at 14:20 -0400, peter korinis wrote:
> A data column in a link table contains comma-separated string data, where
> each value represents a value to link to another table. (many-to-many
> relationship)
> 
>  
> 
> How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract
> these values and use them in an SQL statement, perhaps a WHERE id='66'?
> 
>  
> 
> Thanks,
> 
> peter
> 
> ___
> 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] Database to SQLite Population

[danap]

Hello,

On working with the MyJSQLView database GUI access tool it
has been determined that a local file/memory database would
be valuable to perform recurring analysis on datasets from
the connected datasource. Sqlite is being considered as the
local database.

All the underlining code has been created for conversion
between datasource and local database. The code is now being
developed to perform the DB to DB transfer population. The
basic algorithm being considered is:

Process 1, Loop: 1,2,3
1. Read Datasource row from query.
2. Create SQL INSERT statement.
3. Write SQL INSERT into queue.

Process 2, Loop: 4,5
4. Read SQL INSERT from queue.
5. Write SQL INSERT to SQLite db.}

Perhaps someone from this forum could possibly comment on
another open source project with similar type of db to db
transfer that could be studied or alternative algorithm.

Thanks,

Dana M. Proctor
MyJSQLView Open Source Project Manager.

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

Re: [sqlite] Can sqlite access storage system directly?

[Richard Hipp]

On Fri, Apr 4, 2014 at 4:52 AM, 김병준  wrote:

> After spend quite long time search for regarding to use file I/O in
> SQLite,
>
> I've found that through the VFS layer is the only way go into the
> kernel.
>
> However I am wandering about if there is other method to detour
> filesystem,
>
> so that SQLite is directly interface with device driver layer.
>

http://www.sqlite.org/src/artifact/0396f220561f3b4e?ln=15-17


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

Re: [sqlite] Can sqlite access storage system directly?

[Stephan Beal]

On Fri, Apr 4, 2014 at 10:52 AM, 김병준  wrote:

> filesystem help, but there will be performance gain. ( e.g. From not
> using Journaling
>

See:

http://www.sqlite.org/pragma.html#pragma_journal_mode


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can sqlite access storage system directly?

[김병준]

After spend quite long time search for regarding to use file I/O in
SQLite,
   
I've found that through the VFS layer is the only way go into the
kernel.
   
However I am wandering about if there is other method to detour
filesystem,
   
so that SQLite is directly interface with device driver layer.
   
I think there might be number of disadvantage if SQLite run without
   
filesystem help, but there will be performance gain. ( e.g. From not
using
   
Journaling and eliminate block I/O overhead.)
   
Is there anyone have tried it or know about method? 
 
   
it would very helpful at least tell me whether it is possible or not.


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

Re: [sqlite] if possible point another table

[Simon Slavin]

On 4 Apr 2014, at 7:55am, Darren Duncan  wrote:

> Putting that aside, for any SQL DBMS that supports the PREPARE and EXECUTE 
> keywords, you can have a SQL string value that contains a SQL statement and 
> execute it, and you can build that string in other SQL from your table like 
> with any string manipulation.  This is a standard way to do it, if not the 
> most elegant, it is simple and powerful.

And represents a huge vulnerability if hackers can trick the application into 
executing their own string.

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