Problems with binary links
Andrew Dempsey
andrew at andrewdempsey.com
Sun Jan 31 16:23:30 CST 2010
Heh... yeah sorry about that... that was a classic pointless tech-
support-email! Let me try again. This will be a fairly long
explanation. I am not *expecting* anyone to be able to help me with
my own coding, but I am hoping that someone may be able to spot
something I haven't. If not, at least the process of writing out the
problem in detail may help me think of a new angle.
I am using V4RB, and no Vserver. Developing on Leopard, for use on
mac and windows.
*BACKGROUND:*
I have an app that has lists of vocabulary that can be sorted into
categories. Something like putting songs in playlists in iTunes. It
starts with several thousand words already in the database, linked
into categories. These are the ones I have set up in advance.
It also allows the user to drag words into new or other categories.
These are the "on the fly" links that get created in the application.
There are 3 main tables involved in this. vocabItemMasterlist,
itemCategories, and categoryLinks.
CREATE TABLE "vocabitemMasterList" (
"listEnglish" STRING (400) NOT NULL INDEXED,
"listArabic" STRING (400) NOT NULL,
CREATE TABLE "ItemCategories" (
"itemCategoryName" STRING (256) NOT NULL INDEXED,
"isCustomCategory" BOOLEAN NOT NULL INDEXED,
CREATE TABLE "categoryLinks" (
"ptrCategory" ULONG INDEXED, [points to rec in ItemCategories]
"ptrItem" ULONG INDEXED); [points to rec in vocabitemMasterList]
I also created a binary links between categoryLinks and each of the
other 2 tables.
CREATE BINARY LINK "bl_categoryLinks_to_itemCategories" ON TABLES
("ItemCategories", "categoryLinks") AS 1 TO 0 ON DELETE SET NULL;
CREATE BINARY LINK "bl_categoryLinks_to_vocabItemMasterList" ON TABLES
("vocabitemMasterList", "categoryLinks") AS 1 TO 0 ON DELETE SET NULL;
When I first imported the data from the old database (about 4000
items), I wrote a small realbasic script to put the ptrCategory and
ptrItem values into categoryLinks. I also used the following code to
link the tables using the binary links:
/////////////////////////--------------------
dim tblCategoryLinks As VTable = currDB.Table("categoryLinks")
dim lnkCategoryLinks_to_ItemCategories As VLink =
currDB.Link("bl_categoryLinks_to_itemCategories")
dim lnkCategoryLinks_to_vocabItemMasterList AS VLink =
currDB.Link("bl_categoryLinks_to_vocabItemMasterList")
dim i,currPtrCategory,currPtrItem,Recs(1),numCat,numItem As Integer
numCat = 0
numItem = 0
for i = 1 to tblCategoryLinks.RecordCount
tblCategoryLinks.RecID = i
currPtrCategory = tblCategoryLinks.Field("ptrCategory").Value
currPtrItem = tblCategoryLinks.Field("ptrItem").Value
//link to category
recs(0) = currPtrCategory
recs(1)= i
lnkCategoryLinks_to_ItemCategories.LinkRecords(recs)
if currDB.ErrNumber <> 0 then
break
else
numCat = numCat + 1
lnkCategoryLinks_to_ItemCategories.Flush
end if
//link to item
recs(0) = currPtrItem
recs(1) = i
lnkCategoryLinks_to_vocabItemMasterList.LinkRecords(recs)
if currDB.ErrNumber <> 0 then
break
else
numItem = numItem + 1
lnkCategoryLinks_to_vocabItemMasterList.Flush
end if
next i
///////////////////////---------------------------
This was successful. To check and make sure the links were actually
there, I checked the data inside VStudio and could see the linked data
all connected to each record in the categoryLinks table, both in the
binary links as well as the object pointers.
Back in my realbasic app, I would use the following code to pull up
all of the records from vocabitemMasterList that corresponded to a
given 'ptrCategory' that the user would click.
///////////////////////-------------------------
1 dim as_CategoryLinks, as_ItemLinks As VArraySet
2 dim bs_vocabItems As VBitSet
3 dim bl_categoryLinks_to_itemCategories As VLink =
currDB.Link("bl_categoryLinks_to_itemCategories")
4 dim bl_categoryLinks_to_vocabItemMasterList As VLink =
currDB.Link("bl_categoryLinks_to_vocabItemMasterList")
5 if ptrCategory >0 then //if a specific category is selected
6 as_CategoryLinks =
bl_categoryLinks_to_itemCategories
.FindLinked
(ptrCategory
,currDB.Table("ItemCategories"),currDB.Table("categoryLinks"))
7 if as_CategoryLinks <> nil then
// there is a set of items
8 WorkingBitSet = tblVocabItemMasterList.SelectAllRecords
9 bs_vocabItems =
bl_categoryLinks_to_vocabItemMasterList
.FindLinkedAsBitSet
(as_CategoryLinks,tblVocabItemMasterList,currDB.Table("categoryLinks"))
10 WorkingBitSet = WorkingBitSet.Intersection(bs_vocabItems)
11 //.... process the data....
//////////////////////-------------------------------
WorkingBitSet would contain all of the recID's from
vocabitemMasterList that I needed (line 10). This works perfectly,
and is extremely fast.
*THE PROBLEM: *
In my application when the user drags a word onto a new category in
the list (like dragging a song into a new playlist in iTunes) a new
record in categoryLinks is created, with ptrItem pointing to a record
in vocabitemMasterList, and ptrCategory pointing to a record in
itemCategories.
I then use the SAME code I used earlier to link the files in the
binary links.
////////////////////------------------------------
tblCategoryLinks.SetBlank
tblCategoryLinks.Field("ptrCategory").Value = ptrCategory
tblCategoryLinks.Field("ptrItem").Value =ptrItem
newRecID = tblCategoryLinks.AddRecord
//link to category
recs(0) = ptrCategory
recs(1)= newRecID
lnkCategoryLinks_to_ItemCategories.LinkRecords(recs)
//link to item
recs(0) = ptrItem
recs(1) = newRecID
lnkCategoryLinks_to_vocabItemMasterList.LinkRecords(itemRecs)
////////////////////------------------------------
This doesn't give me any Vexception. But when i use the code given
above to try to pull up all the vocabitemMasterList records associated
with each categoryLink record, given the ptrCategory in which the new
links were created, the link to table itemCategories returns a
Varrayset with the proper number of records in it (line 6 above), but
the FindLinkedAsBitset on line 9 above would return all of the records
previously in the database, but not the newly created one (or any
newly created ones, when I tried a few times).
When I double-checked the data outside of RB to see if the binary
links had indeed been made, in Vstudio it showed all of the records
linked to each other properly through both the binary links and the
objectpointers.
I have tried a huge number of variations on these scripts and checks
on the data to try to figure out where I have gone wrong, but haven't
been able to come up with anything yet.
If anyone made it this far, I will be really impressed! Would love to
hear any thoughts - I have been thinking about this all day! Sorry
for the huge email.
Thanks,
Andrew
------------------------------------------------
Andrew Dempsey
andrew at andrewdempsey.com
On Jan 31, 2010, at 11:10 PM, Ruslan Zasukhin wrote:
> On 1/31/10 5:39 PM, "Andrew Dempsey" <andrew at andrewdempsey.com> wrote:
>
> Hi Andrew,
>
>> I am having strange results with binary links in my application. I
>> have a binary link that joins 2 tables, and is working fine with all
>> the records that I created imported in a batch into the database
>> (using a script that I wrote).
>>
>> When I use the exact same script inside my application to link items
>> in the two tables on the fly, the tables are linked - I can see the
>> links inside Vstudio when I look at the data afterward. But in the
>> application when I try to pull up records using the
>> vLink.FindLinkAsBitset method, it only returns the originally
>> imported
>> data records, not the ones created on the fly (even though they
>> appear
>> to be linked in Vstudio).
>
> So your app creates new links "on the fly".
> Then you close db and open it in Vstudio?
>
> Then again close db and go back to your app to try
> FindLinkAsBitSet() ?
>
> What then means "on the fly"?
>
> You do not use vserver, right?
>
> What API? V4RB? Else?
> OS ?
>
>
>> I could give the code for my tables and for the way I am creating the
>> links, and using FindLinkAsBitset, but I thought I would ask in
>> general terms first in case there is something obvious that I am
>> forgetting to do. It seems to me to be inconsistent.
>
> --
> Best regards,
>
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
>
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
>
> [I feel the need: the need for speed]
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
More information about the Valentina
mailing list