CODEWITHSUNDEEP
pythonstored-proceduresorientdbpyorient
TomG
TomG

Reputation: 155

How to use PyOrient to create functions (stored procedures) in OrientDB?

I'm trying to create an OrientDB graph database using PyOrient, and I can't find enough documentation to allow me to get Functions working. I've been able to create a function using record_create into the ofunction cluster, but although it doesn't crash, it doesn't appear to work either. Here's my code:

#!/usr/bin/python

import pyorient

ousername="user"
opassword="pass"

client = pyorient.OrientDB("localhost", 2424)
session_id = client.connect( ousername, opassword )

db_name="database"

client.db_create( db_name, pyorient.DB_TYPE_GRAPH, pyorient.STORAGE_TYPE_PLOCAL )

# Set up the schema of the database
client.command( "create class URL extends V" )
client.command( "CREATE PROPERTY URL.url STRING")
client.command( "CREATE PROPERTY URL.id INTEGER")
client.command( "CREATE SEQUENCE urlseq")
client.command( "CREATE INDEX urls ON URL (url) UNIQUE")

# Get the id numbers of all the clusters
info=client.db_reload()
clusters={}
for c in info:
  clusters[c.name]=c.id
print(clusters)

# Construct a test function
# All this should do is create a new URL vertex. Eventually it will check for uniqueness of url, etc.
code="INSERT INTO URL SET id = sequence('urlseq').next(), url='?'"
addURL_func = { '@OFunction': { 'name': 'addURL', 'code':'orient.getGraph().command("sql","%s",[urlparam]);' % code, 'language':'javascript', 'parameters':'urlparam', 'idempotent':False } }
client.record_create( clusters['ofunction'], addURL_func )

# Assume  allURLs  contains the list of URLs I want to store
for url in allURLs:
  client.command("select addURL('%s')" % url)

vs = client.command("select * from URL")
for v in vs:
    print(v.url)

Doing all the select addURL bits runs happily, but doing select * from URL simply times out. Presumably because (as I've discovered by examining the database in Studio) there are still no URL vertices. Although why that should timeout rather than returning an empty list or giving a useful error message, I'm not sure.

What am I doing wrong, and is there an easier way to create Functions through PyOrient?

I don't want to just write the Functions in Studio, because I am prototyping and want them written from the Python code rather than being lost every time I drop the mangled experimental graph!

I've mainly been using the OrientDB wiki page to find out about OrientDB functions, and the PyOrient github page as almost my only source of documentation for that.

Edit: I've been able to create a working Function in SQL (see my own answer below) but I still can't create a working Javascript Function which creates a vertex. My current best attempt is:

code2="""var g=orient.getGraph();g.command('sql','CREATE VERTEX URL SET id = sequence(\\"urlseq\\").next(), url = \\"'+urlparam+'\\"',[urlparam]);"""
myFunction2 = 'CREATE FUNCTION addURL2 "' + code2 + '" parameters [urlparam] idempotent false language javascript'
client.command(myFunction2)

which runs without crashing when called from PyOrient, but doesn't actually create any vertices. But if I call it from Studio, it works!?! I have no idea what's going on.

Upvotes: 0

Views: 782

Answers (2)

TomG
TomG

Reputation: 155

OK, after a lot of hacking and Googling, I've got it working:

code="CREATE VERTEX URL SET id = sequence('urlseq').next(), url = :urlparam;"
myFunction = 'CREATE FUNCTION addURL "' + code + '" parameters [urlparam] idempotent false language sql'
client.command(myFunction)

The key here seems to be the use of a colon before parameter names in OrientDB's version of SQL. I couldn't find any reference to this anywhere in the OrientDB docs, but someone online had discovered it somehow. I'm answering my own question in the hope that this will help others struggling wth ODB's poor documentation!

Upvotes: 2

Alessandro Rota
Alessandro Rota

Reputation: 3570

You could try something like :

code="var g=orient.getGraph();\ng.command(\\'sql\\',\\'%s\\',[urlparam]);"
myFunction = "CREATE FUNCTION addURL '" + code + "' parameters [urlparam] idempotent false language javascrip"
client.command(myFunction);

UPDATE

I used this code (version 2.2.5) and it worked for me

code="var g=orient.getGraph().command(\\'sql\\',\\'%s\\',[urlparam]);"
myFunction = "CREATE FUNCTION addURL '" + code + "' parameters [urlparam] idempotent false language javascrip"
client.command(myFunction);

Hope it helps

Upvotes: 1

Related Questions