Search Values of Array in "One Go"

classic Classic list List threaded Threaded
46 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Search Values of Array in "One Go"

Mike Kerner via use-livecode
We use these arrays with media metadata that is extracted from our database of media metadata. The dbase was originally designed for lots of columns so that "there is nothing we cannot know about a media item" incorporating DCIM columns and also W3C media metadata initiative's recommended properties.  In actually usage for any give media item only a small subset of columns containe any data. but we do use almost all the columns at one time or another.  There are no blobs, so this is very light weight data.

Anyway… this results in arrays which contain 1 top level key per record and that element contains another 40 or so keys for the columns of the database. most of which are empty/unused. So these arrays are never "heavy" in terms of bytes.

e.g.

aPlayList  may have 1000 keys (numeric 1-1000)

where each top level key contains a whole record:

in each one we have

aPlayList [1]["title"]
aPlayList [1]["subtitle"]
aPlayList [1]["genre"]
aPlayList [1]["description"]
aPlayList [1]["artist"]
aPlayList [1]["theme"]
aPlayList [1]["creator"]
aPlayList [1]["audience"]

# and 30+  more "properties" for the media item

OK so let's say user  want sto search "blues"  and I want the UX to check *all* columns in the array.  What is the best way to do this?

I tried a simple test.

on mouseup
put "apple" into aFruits["1"]
put "orange" into aFruits["2"]
put "plum" into aFruits ["3"]
put aFruits contains "orange"
end mouseup

and it returns "false"

The only other strategy I can see would be

put 1 into z # for our new filtered play list

repeat for each key x in aPlaylist
       repeat for each key y in aPlayList[x]
          if aPlayList[x][y]contains "blues" then
             put aPlayList[x] into aFilteredPlayList[z]
             add 1 to z
         end if
        end repeat
end repeat

So then if we started with 1000 "records" in the array and we found "blues" in any column of any record, for, say 20 of them, we end up with a new array.

  aFilteredPlayList  # containing top level keys 1-20 for 20 records, put the titles into a list field and viola: the clickline number matches the array key and away we go.

OK I can make this work, (have to since I don't see another way)

But it seems like a lot of looping.

Is there a more efficient approach?

BR













_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
Yes. Don't use an array, use a memory based sqLite table.

Bob S


> On Aug 23, 2017, at 08:24 , Sannyasin Brahmanathaswami via use-livecode <[hidden email]> wrote:
>
> OK I can make this work, (have to since I don't see another way)
>
> But it seems like a lot of looping.
>
> Is there a more efficient approach?
>
> BR


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
Oh wait I see you ARE using an SQL database to begin with. Why would you then want to search an array?

Bob S


> On Aug 23, 2017, at 08:34 , Bob Sneidar via use-livecode <[hidden email]> wrote:
>
> Yes. Don't use an array, use a memory based sqLite table.
>
> Bob S
>
>
>> On Aug 23, 2017, at 08:24 , Sannyasin Brahmanathaswami via use-livecode <[hidden email]> wrote:
>>
>> OK I can make this work, (have to since I don't see another way)
>>
>> But it seems like a lot of looping.
>>
>> Is there a more efficient approach?
>>
>> BR
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
I'm with bob on this.. If for some reason you can't use the database that
produced the array, shove the data into an sqlite db instead. In memory, or
if you need persistence a db file.

Alternatively, if you store your data flat rather than in an array. Then
you can....
repeat for each line tline in tData
  if tLine contains tSearchString then
       -- do whatever you want with tline
  end if
end repeat

You would still have to loop once through every record, but wouldn't have
the overhead of the subloop checking each column of each record for your
string.  It would just check the whole line.  (you could use itemoffset or
something of that nature, but its could be a pain to do so if the chosen
delimiter appears in the text. As a full string search of the line though,
it should work fine. )

On Wed, Aug 23, 2017 at 9:36 AM, Bob Sneidar via use-livecode <
[hidden email]> wrote:

> Oh wait I see you ARE using an SQL database to begin with. Why would you
> then want to search an array?
>
> Bob S
>
>
> > On Aug 23, 2017, at 08:34 , Bob Sneidar via use-livecode <
> [hidden email]> wrote:
> >
> > Yes. Don't use an array, use a memory based sqLite table.
> >
> > Bob S
> >
> >
> >> On Aug 23, 2017, at 08:24 , Sannyasin Brahmanathaswami via use-livecode
> <[hidden email]> wrote:
> >>
> >> OK I can make this work, (have to since I don't see another way)
> >>
> >> But it seems like a lot of looping.
> >>
> >> Is there a more efficient approach?
> >>
> >> BR
> >
> >
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
I'm not going to disagree with the other replies ....

Plan A.   use your database

Plan B.   create an in-memory SQLite database.

Plan C. use an array, kind of like you are doing.

And *only* if there are good reasons for not doing Plans A or B, then :

C1.  Instead of

repeat for each key x in aPlaylist
        repeat for each key y in aPlayList[x]
   

do

repeat for each key x in aPlaylist
        repeat for each element ey in aPlayList[x]

Also, instead of
     put aPlayList[x] into aFilteredPlayList[z]

do
     put x into aFilteredPlayListIndex[z]
(there's no need to replicate all the data when the array will continue to exist, just keep the indices you want).


C2. Did you really mean "check *all* columns in the array" ?
Should it not be "check all the 'properties' columns" ?
If so - that's another reason to use a/the database :-)

C3. Create an 'allproperties' column ?
There's probably a fast/easy way to do that either in the database, or as you extract from it - but a database expert would have to tell us how to do that.

Alternatively, when you extract, just do

repeat for each key x in aPlayList
   repeat for each item y in thePropertyColumns
       put aPlayList[x][y] & SPACE after aPlayList[x]["allproperties"]
   end repeat
end repeat

or, delay this until first search :

repeat for each key x in aPlayList
    if aPlayList[x]["allproperties"] is empty then
       repeat for each item y in thePropertyColumns
           put aPlayList[x][y] & SPACE after aPlayList[x]["allproperties"]
       end repeat
       if aPlayList[x]["allproperties"] is empty then -- so that subsequent searches never rebuild this
         put SPACE into aPlayList[x]["allproperties"]
       end if
    end if
    if aPlayList[x]["allproperties"] contains pFilterPattern then
      put x & CR after z
    end if
end repeat

 
Alex.


On 23/08/2017 16:24, Sannyasin Brahmanathaswami via use-livecode wrote:

> We use these arrays with media metadata that is extracted from our database of media metadata. The dbase was originally designed for lots of columns so that "there is nothing we cannot know about a media item" incorporating DCIM columns and also W3C media metadata initiative's recommended properties.  In actually usage for any give media item only a small subset of columns containe any data. but we do use almost all the columns at one time or another.  There are no blobs, so this is very light weight data.
>
> Anyway… this results in arrays which contain 1 top level key per record and that element contains another 40 or so keys for the columns of the database. most of which are empty/unused. So these arrays are never "heavy" in terms of bytes.
>
> e.g.
>
> aPlayList  may have 1000 keys (numeric 1-1000)
>
> where each top level key contains a whole record:
>
> in each one we have
>
> aPlayList [1]["title"]
> aPlayList [1]["subtitle"]
> aPlayList [1]["genre"]
> aPlayList [1]["description"]
> aPlayList [1]["artist"]
> aPlayList [1]["theme"]
> aPlayList [1]["creator"]
> aPlayList [1]["audience"]
>
> # and 30+  more "properties" for the media item
>
> OK so let's say user  want sto search "blues"  and I want the UX to check *all* columns in the array.  What is the best way to do this?
>
> I tried a simple test.
>
> on mouseup
> put "apple" into aFruits["1"]
> put "orange" into aFruits["2"]
> put "plum" into aFruits ["3"]
> put aFruits contains "orange"
> end mouseup
>
> and it returns "false"
>
> The only other strategy I can see would be
>
> put 1 into z # for our new filtered play list
>
> repeat for each key x in aPlaylist
>         repeat for each key y in aPlayList[x]
>            if aPlayList[x][y]contains "blues" then
>               put aPlayList[x] into aFilteredPlayList[z]
>               add 1 to z
>           end if
>          end repeat
> end repeat
>
> So then if we started with 1000 "records" in the array and we found "blues" in any column of any record, for, say 20 of them, we end up with a new array.
>
>    aFilteredPlayList  # containing top level keys 1-20 for 20 records, put the titles into a list field and viola: the clickline number matches the array key and away we go.
>
> OK I can make this work, (have to since I don't see another way)
>
> But it seems like a lot of looping.
>
> Is there a more efficient approach?
>
> BR
>
>
>
>
>
>
>
>
>
>
>
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
Bob, Mike,

thanks…"use the dbase" good thought

The reason we use the array is once we load that into a local than I use the click line number of the song list (titles in a simple list field)  to get the data for that particular song.  

click on line 9

get use everything we need from sSelectionsA ["audiofiles"]["9"]  
   -- title
   -- URL to the mp3 on the server to set for the player or for a download later
   -- artist

   etc..

But you make a good case ifwe are going back to get a new list, onemight as well just use the query params and just utllize that original handler that queries the dBase for a playlist in the first place.

At least for theme and genre these are direct "hits" because there is an "equals" look up against these two enum values; but the searchString look up will still need to use   %like%  for  "blues" is in "title, subtitle and description" . I have to check my sql query hand book aka "the internets" to see if there is an efficient way to write that query.

So then it comes down to whether doing a query semi-complex query that looks at 50,000 records and uses  %like% against 3 columns is faster than iterating loops on a 1000 key array already in memory.

I can't wait to use the new tools for watching processes to see where bottle necks arise…



_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
Using equal queries is efficient *if* the columns are indexed. If you use LIKE, I do not believe that the indexes are used, unless you employ some kind of full text search indexing. Only certain databases support that.

Bob S


> On Aug 23, 2017, at 12:07 , Sannyasin Brahmanathaswami via use-livecode <[hidden email]> wrote:
>
> At least for theme and genre these are direct "hits" because there is an "equals" look up against these two enum values; but the searchString look up will still need to use   %like%  for  "blues" is in "title, subtitle and description" . I have to check my sql query hand book aka "the internets" to see if there is an efficient way to write that query.
>
> So then it comes down to whether doing a query semi-complex query that looks at 50,000 records and uses  %like% against 3 columns is faster than iterating loops on a 1000 key array already in memory.
>
> I can't wait to use the new tools for watching processes to see where bottle necks arise…

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
Sannyasin Brahmanathaswami wrote:

 > I can't wait to use the new tools for watching processes to see where
 > bottle necks arise…

?

--
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode


On 23/08/2017 20:07, Sannyasin Brahmanathaswami via use-livecode wrote:
> Bob, Mike,
>
> thanks…"use the dbase" good thought
> ...
>
> So then it comes down to whether doing a query semi-complex query that looks at 50,000 records and uses  %like% against 3 columns is faster than iterating loops on a 1000 key array already in memory.
>
3 columns ?  I thought it was :
> and 30+  more "properties" for the media item
So, 3 or 30+ ? Probably make a difference ...

Alex.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
did you see the pitch for business upgrade?  scroll down.. there some new thing that give you time on processes

 

On 8/23/17, 9:28 AM, "use-livecode on behalf of Richard Gaskin via use-livecode" <[hidden email] on behalf of [hidden email]> wrote:

    > I can't wait to use the new tools for watching processes to see where
    > bottle necks arise…
   
    ?
   
   

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
The media meta dbase "jnanam.item" table actually has  87 columns, ha! way over kill, but I'm actually glad we did that because there is indeed "nothing we cannot know about any media item in the universe"

But  any given item… only uses a subset.



media_type = "audio/song"

and

media_type = "audio/inspiredTalk"

are the context where users need search.

will only use a subset.. but I _do_have all 87 props in the array.

For this use case we are only looking at/"into"

item.media_type
item.title
item.sub_title
item.approved_for_public
item.file_id (a varchar text string)
item.description
item.artists
item.date_entered # added to the dbase - allow us to fetch "recent additions"
item.genre
item.theme
item.transcription

So radio buttons will give the user the genre and theme "enum" values  to choose from, but the search string can't be constrained..we need to check into

item.title
item.sub_title
item.description
item.transcription.

do the look up into the array will first filter by genre and theme and *then check inside the above three… if "all" is check in genre and theme then we look for the string in all three var char fields title, sub_title, description.

Hmm audio/songs is only running at 400 records (= sSelectionsA[audiofiles][1-400]  

that will go up after I open the gate for user generated uploads

audio/inspired_talks  is where we start hitting 1000+ records… and it "gets worse" there because we need to start looking into

item.transcription  = long text for 30 minute talk…










On 8/23/17, 10:04 AM, "use-livecode on behalf of Alex Tweedly via use-livecode" <[hidden email] on behalf of [hidden email]> wrote:

   
    3 columns ?  I thought it was :
    >and 30+  more "properties" for the media item
    So, 3 or 30+ ? Probably make a difference ...
   
    Alex.
   
   

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
Alex, thanks, this is very helpful

1) put x into aFilteredPlayListIndex[z]
(there's no need to replicate all the data when the array will continue to exist, just keep the indices you want).

awesome… I think I could increment the  z from 1  up and then my clickline number will still work.

2) "create an in-memory SQLite Database."

 this is news! Where do we find docs on this? right now the SQLite data base is in

 specialFolderPath("documents")/jnanam.sqlite

how do you "create in memory?


 

On 8/23/17, 9:06 AM, "Alex Tweedly via use-livecode" <[hidden email]> wrote:

    I'm not going to disagree with the other replies ....
   
    Plan A.   use your database
   
    Plan B.   create an in-memory SQLite database.
   
    Plan C. use an array, kind of like you are doing.
   
    And *only* if there are good reasons for not doing Plans A or B, then :
   
    C1.  Instead of
   
    repeat for each key x in aPlaylist
            repeat for each key y in aPlayList[x]
       
   
    do
   
    repeat for each key x in aPlaylist
            repeat for each element ey in aPlayList[x]
   
    Also, instead of
         put aPlayList[x] into aFilteredPlayList[z]
   
    do
         put x into aFilteredPlayListIndex[z]
    (there's no need to replicate all the data when the array will continue to exist, just keep the indices you want).
   
   
    C2. Did you really mean "check *all* columns in the array" ?
    Should it not be "check all the 'properties' columns" ?
    If so - that's another reason to use a/the database :-)
   
    C3. Create an 'allproperties' column ?
    There's probably a fast/easy way to do that either in the database, or as you extract from it - but a database expert would have to tell us how to do that.
   
    Alternatively, when you extract, just do
   
    repeat for each key x in aPlayList
       repeat for each item y in thePropertyColumns
           put aPlayList[x][y] & SPACE after aPlayList[x]["allproperties"]
       end repeat
    end repeat
   
    or, delay this until first search :
   
    repeat for each key x in aPlayList
        if aPlayList[x]["allproperties"] is empty then
           repeat for each item y in thePropertyColumns
               put aPlayList[x][y] & SPACE after aPlayList[x]["allproperties"]
           end repeat
           if aPlayList[x]["allproperties"] is empty then -- so that subsequent searches never rebuild this
             put SPACE into aPlayList[x]["allproperties"]
           end if
        end if
        if aPlayList[x]["allproperties"] contains pFilterPattern then
          put x & CR after z
        end if
    end repeat
   
     
    Alex.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode


On 24/08/2017 01:04, Sannyasin Brahmanathaswami via use-livecode wrote:
> 2) "create an in-memory SQLite Database."
>
>   this is news! Where do we find docs on this? right now the SQLite data base is in
>
>   specialFolderPath("documents")/jnanam.sqlite
>
> how do you "create in memory?
>
>
According to an old message (i.e. I've not tried it :-):

On Fri, Jun 15, 2012 at 5:21 PM, Bob Sneidar <bobs at twft.com <http://lists.runrev.com/mailman/listinfo/use-livecode>> wrote:

//>//>/On Jun 15, 2012, at 4:13 PM, Mike Bonner wrote: />//>/ > Don't specify a database name and it will open the db in memory. />/ > />/ > get revopendatabase("sqlite",,,,,,) --not sure how many commas off the />/top />/ > of my head. />/ > />/ > On Fri, Jun 15, 2012 at 5:08 PM, Bob Sneidar <bobs at twft.com
<http://lists.runrev.com/mailman/listinfo/use-livecode>> wrote: />/ > />/ >> I am just certain that I read on this forum that you could create an />/ >> sqLite database in memory! But when looking through the dictionary and />/in />/ >> the LiveCode lessons there seems to be no mention of how! Can someone />/point />/ >> me to the right docs for this? />/ >> />/ >> Bob />/ >> />/ >> /


-- Alex.
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
Yep… I just found that too.

and related 4-yr old discussion

http://use-livecode.runrev.narkive.com/ntBLeXGM/relative-performance-of-two-dimensional-array-and-in-memory-sqlite-database


What I don't get is

 "don't give it a name"    open/create in memory,

but the data is on SQLite dbase on disk, so you *have* to read it one way or another.

(scratch his head…)
 

On 8/23/17, 2:13 PM, "use-livecode on behalf of Alex Tweedly via use-livecode" <[hidden email] on behalf of [hidden email]> wrote:

    On 24/08/2017 01:04, Sannyasin Brahmanathaswami via use-livecode wrote:
    >2) "create an in-memory SQLite Database."
    >
    >   this is news! Where do we find docs on this? right now the SQLite data base is in
    >
    >   specialFolderPath("documents")/jnanam.sqlite
    >
    >how do you "create in memory?
    >
    >
    According to an old message (i.e. I've not tried it :-):
   
    On Fri, Jun 15, 2012 at 5:21 PM, Bob Sneidar <bobs at twft.com <http://lists.runrev.com/mailman/listinfo/use-livecode>> wrote:
   
    //>//>/On Jun 15, 2012, at 4:13 PM, Mike Bonner wrote: />//>/ > Don't specify a database name and it will open the db in memory. />/ > />/ > get revopendatabase("sqlite",,,,,,) --not sure how many commas off the />/top />/ > of my head. />/ > />/ > On Fri, Jun 15, 2012 at 5:08 PM, Bob Sneidar <bobs at twft.com
    <http://lists.runrev.com/mailman/listinfo/use-livecode>> wrote: />/ > />/ >> I am just certain that I read on this forum that you could create an />/ >> sqLite database in memory! But when looking through the dictionary and />/in />/ >> the LiveCode lessons there seems to be no mention of how! Can someone />/point />/ >> me to the right docs for this? />/ >> />/ >> Bob />/ >> />/ >> /

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
I think you need to use ":memory:" as the name actually.  It looks (from
the sqlite docs) that supplying no name creates a temporary db file.
And now i'm curious.. Is there a way to attach the file based db to a
memory db?

IE Open the file database, then use the info here:
https://www.sqlite.org/lang_attach.html and attach a memory database.

Then (way over my head, but I seem to recall reading somewhere..) that one
can use the results of a select to create a temporary table..  If
interested, you might look here under create table as select...
http://sqlite.org/lang_createtable.html

On Wed, Aug 23, 2017 at 6:31 PM, Sannyasin Brahmanathaswami via
use-livecode <[hidden email]> wrote:

> Yep… I just found that too.
>
> and related 4-yr old discussion
>
> http://use-livecode.runrev.narkive.com/ntBLeXGM/relative-
> performance-of-two-dimensional-array-and-in-memory-sqlite-database
>
>
> What I don't get is
>
>  "don't give it a name"    open/create in memory,
>
> but the data is on SQLite dbase on disk, so you *have* to read it one way
> or another.
>
> (scratch his head…)
>
>
> On 8/23/17, 2:13 PM, "use-livecode on behalf of Alex Tweedly via
> use-livecode" <[hidden email] on behalf of
> [hidden email]> wrote:
>
>     On 24/08/2017 01:04, Sannyasin Brahmanathaswami via use-livecode wrote:
>     >2) "create an in-memory SQLite Database."
>     >
>     >   this is news! Where do we find docs on this? right now the SQLite
> data base is in
>     >
>     >   specialFolderPath("documents")/jnanam.sqlite
>     >
>     >how do you "create in memory?
>     >
>     >
>     According to an old message (i.e. I've not tried it :-):
>
>     On Fri, Jun 15, 2012 at 5:21 PM, Bob Sneidar <bobs at twft.com <
> http://lists.runrev.com/mailman/listinfo/use-livecode>> wrote:
>
>     //>//>/On Jun 15, 2012, at 4:13 PM, Mike Bonner wrote: />//>/ > Don't
> specify a database name and it will open the db in memory. />/ > />/ > get
> revopendatabase("sqlite",,,,,,) --not sure how many commas off the />/top
> />/ > of my head. />/ > />/ > On Fri, Jun 15, 2012 at 5:08 PM, Bob Sneidar
> <bobs at twft.com
>     <http://lists.runrev.com/mailman/listinfo/use-livecode>> wrote: />/ >
> />/ >> I am just certain that I read on this forum that you could create an
> />/ >> sqLite database in memory! But when looking through the dictionary
> and />/in />/ >> the LiveCode lessons there seems to be no mention of how!
> Can someone />/point />/ >> me to the right docs for this? />/ >> />/ >>
> Bob />/ >> />/ >> /
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
Well thanks to all of you urging me to stay away from re-processing the array but going back to the dbase.

I Looked back at my code and realized that I *had* set it up to be re-usable, I just forgot how well I had done that

I have this function in the behavior_Listen  which is attached to the stack

local sPlayListQueryA

command playlist_FetchData

# this handler gets everything we need from the dbase and assumes the column values for the query
# are set in the sPlayListQueryA

-----

duh!  I had written everything I needed already!

I only needws to add this

on playList_FetchData pQueryArray
   
   if pQueryArray is not empty then # we are passing a query from the search behavior
      put pQueryArray into sPlaylistQueryA
   end if
   
and away we go, exact code to instantiate the entire play list of all audio items for that class is now reused to just fetch what the user wants.
Awesome… this  all I needed in my search behavior  was to set up  the query (values for column look ups)

     put tGenre into tFilterListQueryA["genre"]
     put tTheme into tFilterListQueryA["theme"]
         put tStringToFind into tFilterListQueryA["title"]
            put tStringToFind into tFilterListQueryA["subtitle"]
            put tStringToFind into tFilterListQueryA["description"]
            put tStringToFind into tFilterListQueryA["artists"]

   revealMoreActions "false"  # hide the form and fetch a new list from the dbase
   playList_fetchData tFilterListQueryA

all the discussion on speed seems mute in this context as the data sets are small an it happens in "the flash of an eye"

Thanks gentlemen, you saved me from going off the deep end re-parsing the local array!



BR



On 8/23/17, 2:47 PM, "use-livecode on behalf of Mike Bonner via use-livecode" <[hidden email] on behalf of [hidden email]> wrote:

    I think you need to use ":memory:" as the name actually.  It looks (from
    the sqlite docs) that supplying no name creates a temporary db file.
    And now i'm curious.. Is there a way to attach the file based db to a
    memory db?
   
    IE Open the file database, then use the info here:
    https://www.sqlite.org/lang_attach.html and attach a memory database.
   
    Then (way over my head, but I seem to recall reading somewhere..) that one
    can use the results of a select to create a temporary table..  If
    interested, you might look here under create table as select...
    http://sqlite.org/lang_createtable.html

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
Sannyasin Brahmanathaswami wrote:
 > I can't wait to use the new tools for watching processes to see where
 > > bottle necks arise…
...
 > did you see the pitch for business upgrade?  scroll down.. there some
 > new thing that give you time on processes

Thanks.  I'd forgotten that was a new premium feature.

MetaCard included a profiler.

Might be nice if someone had time to update it for use in LC Community.

--
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
Sannyasin Brahmanathaswami wrote:

 > OK so let's say user  want sto search "blues"  and I want the UX to
 > check *all* columns in the array.  What is the best way to do this?
...
 > Is there a more efficient approach?

At my local Linux User Group last year a fella from NASA gave a
presentation about a system he'd developed for querying planetary data
via URLs.

The data is huge (basically everything every interplanetary
satellite/robot delivers to JPL) and his choice of HDF (Hierarchical
Data Format) as the storage base was interesting in itself (imagine a
large encoded array but designed for traversal on disk).

But even more so was his query API.  A thing of beauty.  One-line URLs
could be entered in a browser to locate just about anything in the
massive HDF store.

Ultimately it could be seen as a simplified form of XQuery, every bit as
elegant and flexible but without the XML-specific cruft (having to
account for the mix of elements and attributes).

Of course this led me to thinking of ways to use this for LC arrays.

It should be possible to craft a library to allow XQuery-like one-liners
to locate anything anywhere in any array.

It should also be possible to do this in the engine with
blindingly-faster efficiency.

But as a starting point, fleshing out the API in script seemed at least
a good first step.

I do not have the time to pursue it in earnest.  But XQuery is well
documented, and a sufficiently-inspired person may be able to come up
with a pretty great query system given sufficient time to explore the
possibilities...

--
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
I mentioned a few years ago when arrays first were introduced that there ought to be an engine based way to seach arrays for values, returning matching key references (not just one but ALL). If you are only interested in certain keys you could then filter the results.

To make this fast for large arrays, you might have to have a way to index the arrays, but now we are talking about a memory based database system, and I am not sure Livecode arrays are the way to go. It may be enough to be able to do a kind of full text search on an array in memory.

I suppose thinking about it you could arrayencode an array then do a search on it, but I am not sure how you would discern the array keys. I'll have to look at the encoded format. At that point if that approach worked, the only advantage to searching a memory based array is you would be skipping the encoding step, and searching the actual array in memory with the engine might be considerably faster than having LC search a text variable.

I'll play around with it to see what I can come up with.

Bob S


> On Aug 24, 2017, at 09:00 , Richard Gaskin via use-livecode <[hidden email]> wrote:
>
> At my local Linux User Group last year a fella from NASA gave a presentation about a system he'd developed for querying planetary data via URLs.
>
> The data is huge (basically everything every interplanetary satellite/robot delivers to JPL) and his choice of HDF (Hierarchical Data Format) as the storage base was interesting in itself (imagine a large encoded array but designed for traversal on disk).
>
> But even more so was his query API.  A thing of beauty.  One-line URLs could be entered in a browser to locate just about anything in the massive HDF store.
>
> Ultimately it could be seen as a simplified form of XQuery, every bit as elegant and flexible but without the XML-specific cruft (having to account for the mix of elements and attributes).
>
> Of course this led me to thinking of ways to use this for LC arrays.
>
> It should be possible to craft a library to allow XQuery-like one-liners to locate anything anywhere in any array.
>
> It should also be possible to do this in the engine with blindingly-faster efficiency.
>
> But as a starting point, fleshing out the API in script seemed at least a good first step.
>
> I do not have the time to pursue it in earnest.  But XQuery is well documented, and a sufficiently-inspired person may be able to come up with a pretty great query system given sufficient time to explore the possibilities...
>
> --
> Richard Gaskin
> Fourth World Systems
> Software Design and Development for the Desktop, Mobile, and the Web


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Search Values of Array in "One Go"

Mike Kerner via use-livecode
Okay it's not entirely straight forward. I took a simple array from a datagrid with one record and ran this on it:

on mouseUp pButtonNumber
   put the dgData of group "dgsites" into aSiteData
   put arrayencode(aSiteData) into tSiteData
   repeat for each char tChar in tSiteData
      add 1 to tCounter
      if cleanAscii(tChar) is not empty then next repeat
      put tCounter & tab & charToNum(tChar) & cr after tAscii
   end repeat
   set the itemDelimiter to tab
   sort lines of tAscii numeric by item 2 of each
   put tAscii
end mouseUp

This returns a tab delimited list of position and non-printing ascii characters ranging from 0 to 58. What the ascii characters designate is the mystery. Any documentation on that?

Bob S


> On Aug 24, 2017, at 10:50 , Bob Sneidar via use-livecode <[hidden email]> wrote:
>
> I suppose thinking about it you could arrayencode an array then do a search on it, but I am not sure how you would discern the array keys. I'll have to look at the encoded format. At that point if that approach worked, the only advantage to searching a memory based array is you would be skipping the encoding step, and searching the actual array in memory with the engine might be considerably faster than having LC search a text variable.
>
> I'll play around with it to see what I can come up with.
>
> Bob S


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
123