3

I have a 'bill' vertex with 'date' property and made an automatic SB-tree not unique index on the bill's date for faster searches, now I want every inserted bill to have edge with next bill and create a linked nodes structure,

the solutions I got so far:

1- to use gremlin to calculate the minimal difference between the inserted bill's date and every other bill to get the closest bill, but it required me to scan all the bills and didn't use the index

2- I could get the keys of the index and use Collections.binarySearch() and get the index of insertion point, hence get the adjacent bills,

but I wonder is there any other better solutions to have linked bills, and how can I find Insertion point in the OrientDB index using SQL, any ideas?

DrAhmedJava
  • 731
  • 8
  • 16

1 Answers1

1

I tried your example with a simple DB of two classes:

  • Bill (extends V) with the property billDate as datetime;
  • nextBill (extends E).

with this query

create vertex Bill set billDate=sysdate(), in_nextBill=(select @rid from Bill where billDate in (select max(billDate) from Bill))

you can at the same time create the bill and the edge referred to the previous.

EDITED

I created a Javascript Function that removes the edge between two records and inserts a new record (with relative edges) between the previous two. The function accepts three input parameters: * date1: datetime format; * date2: datetime format; * newBillDate: datetime format is the date of new bill you want to insert between the previous two ;

var g=orient.getGraph();
var d1=g.command('sql','select from Bill where billDate in "'+date1+'"');
var d2=g.command('sql','select from Bill where billDate in "'+date2+'"');
var startDate=d1[0];
var endDate=d2[0];
if(endDate.getRecord().field("billDate").getTime()<startDate.getRecord().field("billDate").getTime()){
  var temp=endDate;
  endDate=startDate;
  startDate=temp;
}
var selectEdge=g.command('sql','select from nextBill where in='+endDate.getId()+' and out='+startDate.getId());
g.command('sql','delete edge '+selectEdge[0].getId());
var newIns=g.command('sql','create vertex Bill set billDate="'+newBillDate+'"');
g.commit();
g.command('sql','create edge nextBill from '+startDate.getRecord().getIdentity()+' to '+newIns.getRecord().getIdentity());
g.command('sql','create edge nextBill from '+newIns.getRecord().getIdentity()+' to '+endDate.getRecord().getIdentity());
LucaS
  • 1,418
  • 9
  • 12
  • thanks @LucaS for answer, but what if I want to insert the new bill between 2 bills, lets say today is tuesday and the new bill will not have `sysdate()`, rather it will have 13:00 PM of sunday – DrAhmedJava Dec 23 '15 at 08:02
  • Hi, do you want to insert the new bill between 2 predefined dates ? – LucaS Dec 23 '15 at 08:32
  • yes, this exactly what i want, the problem is searching time for this point increases with increased bills in database, I know that this will involve changing edges between previous, current and the next bills, but this should be easy once I get the correct insertion point – DrAhmedJava Dec 23 '15 at 08:59
  • Hi, I'm editing the answer with a javascript function that deletes the edge between two given dates and inserts a new record 'Bill' with the creation of the new two edges between the new and previous records. – LucaS Dec 24 '15 at 10:54
  • Hi, did you have a chance to try the function ? – LucaS Jan 28 '16 at 02:47
  • we went with a mix between your function and building and index on bills date, using index will find the correct bill quickly then rewire the edges between bills, thanks for your concern and so sorry for this late response. – DrAhmedJava Feb 11 '16 at 07:06
  • Hi, no problem. Have the function and my guidelines been helpful ? – LucaS Feb 11 '16 at 07:17