New Property Attribute "default"

[gramian]

Dear Developers,

I would like to propose a new attribute for properties which assigns a default value instead of null to a missing value.

CREATE PROPERTY mytype.myprop STRING (default 'not set') 

A default <value> constraint is part of the SQL standard and available in Oracle, mySQL or MS SQL Server. As far as I know, this default value applies also to notnull columns in SQL databases, so even if a column is constraint to be not null, if created empty the default value will still be set.

Thank you for considering

[lvca]

We already have default in properties :-)

[gramian]

Wait, what? Sorry, I must have totally overlooked that.

[gramian]

I think this is undocumented yet. I will add documentation about that ASAP. I should have checked OrientDB's docs, too, I guess: https://orientdb.org/docs/3.0.x/sql/SQL-Alter-Property.html . Thanks for the fast "implementation" and sorry again for not checking thoroughly.

[lvca]

Thanks for figuring out wasn't documented :-)

[gramian]

@lvca I was testing the default values for a DATETIME property, particularly this example from OrientDBs ALTER PROPERTY:

orientdb> ALTER PROPERTY Client.created DEFAULT "sysdate()"

which does not work in ArcadeDB. I assume this default value is just treated as string and thus leads to a null default value as there is no matching conversion.
So, is this a bug, supposed to work like this (in case of DATETIME), something wrong with the syntax, or is there another way to assign a default value to a DATETIME property being the time of creation (of the associated type)?
Expectedly, If one leaves out the quotation marks, the default value is the time of creating / altering the property.

[lvca]

I've modified the test case to execute an update, and the createdOn attribute doesn't change:

@Test
  public void sqlAlterPropertyDefaultFunctions() {
    database.command("sql", "CREATE VERTEX TYPE Log");
    Assertions.assertTrue(database.getSchema().getType("Log").getSuperTypes().isEmpty());

    database.command("sql", "CREATE PROPERTY Log.createdOn DATETIME_MICROS");
    Assertions.assertTrue(database.getSchema().getType("Log").existsProperty("createdOn"));
    Assertions.assertEquals(Type.DATETIME_MICROS, database.getSchema().getType("Log").getProperty("createdOn").getType());

    database.command("sql", "ALTER PROPERTY Log.createdOn DEFAULT sysDate()");

    database.transaction(() -> {
      database.command("sql", "CREATE VERTEX Log");
      ResultSet result = database.command("sql", "SELECT FROM Log");
      Assertions.assertTrue(result.hasNext());

      Vertex v = result.next().getVertex().get();
      final LocalDateTime createdOn = v.getLocalDateTime("createdOn");
      Assertions.assertNotNull(createdOn);

      v.modify().set("lastUpdateOn", LocalDateTime.now()).save();

      result = database.command("sql", "SELECT FROM Log");
      Assertions.assertTrue(result.hasNext());

      v = result.next().getVertex().get();
      Assertions.assertEquals(createdOn, v.getLocalDateTime("createdOn"));
      Assertions.assertNotNull(v.getLocalDateTime("lastUpdateOn"));
    });
  }

[gramian]

@lvca I think this is not testing the thing. I probably am not describing the issue correctly, sorry for that.
I meant, that the sysdate() function is already evaluated in this line ALTER PROPERTY Log.createdOn DEFAULT sysDate(). So all vertices will have exactly the same time stamp.

I tested by creating two vertices with a 2 second pause in between the two inserts and then compared the createdOn properties.

[lvca]

Oh, I got it now, it's evaluated at execution of the ALTER PROPERTY command. Ok, working on it, thanks.

[lvca]

Created this issue and just fixed. The right syntax when using function is without any quotes. Example:

ALTER PROPERTY Log.createdOn DEFAULT sysDate()

[lvca]

For reference, this was the issue #863.