Re: [sqlite] comma-separated string data
On Fri, Apr 4, 2014 at 10:20 PM, peter korinis wrote: > A data column in a link table contains comma-separated string data, where > > 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'? > > In similar cases I use my virtual table explained here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html Actually the table works more like "function", so only one instance is required in the db to apply this trick. Finally the db contains CREATE VIRTUAL TABLE cmlist Using vtcommalist and the query SELECT value FROM cmlist WHERE commalist='45,56,78,125' returns 4 rows "45" "56" "78" "125" And this was a blank db with this virtual table only and the query doesn't reference anything outside while still generating table data. So the data for the virtual table is actually supplied at the time of the query itself from WHERE clause. This trick successfully works with joins and everything else. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
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)
(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
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
"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] comma-separated string data
On 4/4/2014 4:12 PM, peter korinis wrote: Does SQL have any string commands, like REGEXP or something else to strip out the commas, and transform the string into multiple discrete values, then use those perhaps in a SQL subquery . or something like that? Do the processing outside of SQLite. Let's say you have an entry "4,66,51,3009,2,678, .", and you want to get the "66" out of there to feed back into another query. Well, just do whatever magic you like to get that entry into your program, then use regexp or character array indexing or Tcl [split $val ,] or whatever to get "66", and finally use that as a parameter in your query. Doing all this inside SQLite will add complexity with no performance benefit. It's not like SQLite is a database server over a slow network link which you want to use as little as possible. It's a library embedded in your application, which means it's on equal footing with all your other application code. Also yes, do normalize your database design. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
Thanks, Simon. The database was created by a developer as part of a larger project involving a mobile app and portal. the portal is written in which calls the db. so that's where the 'parsing' is done. But unfortunately a SQL alone cannot provide the data in this case. This portal is in production so we're not about to rewrite the code with a db redesign. Does SQL have any string commands, like REGEXP or something else to strip out the commas, and transform the string into multiple discrete values, then use those perhaps in a SQL subquery . or something like that? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On 4 Apr 2014, at 7:20pm, 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'? This is not simple, and would lead to slow execution in a live environment. Are you willing, at this stage, to use that column to create a proper TABLE that links the two existing TABLEs, or do you need to keep the data in your database in that format, and use that column in a live environment ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database to SQLite Population
On 4/4/2014 1:21 PM, da...@dandymadeproductions.com wrote: 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. If I understand you correctly, you're suggesting making a local snapshot of a networked database to optimize performance. I'm not sure what remote database you're using, but it seems to me with properly designed prepared statements there won't be much gain in downloading everything in advance to the local machine, especially since the download will certainly include more data than is actually needed. Additionally consider the loss of coherency when the upstream database is modified but the local snapshot becomes stale. 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.} The queue seems to be an unnecessary intermediary. Simply alternate between reading from the remote database and writing the received data to the SQLite database. This simpler design is also more amenable to prepared statements which offer indispensible performance and security benefits. Do keep in mind that an SQL database consists not only of INSERTs (the data) but also CREATE TABLEs (the schema) plus indexes and triggers and views. 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. It doesn't matter what database you use, the algorithm remains the same: read source, write destination, repeat. Many database systems have convenient import routines for common data sources. For example, the SQLite shell has the .import command for loading a file into a table. -- 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
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(), &icuBuf, &err);
> }
>
> // 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 = &scalars[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
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
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
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
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?
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?
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
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
