1. Jonathan Ashwell
  2. Valentina Database ADK
  3. Thursday, March 16 2017, 04:42 PM
  4.  Subscribe via email
Is it possible to make a VText field that is not nullable? I have an existing database where I have many VText fields that were created as nullable:

authors = CreateTextField("authors", 128, EVFlag.fNullable + EVFlag.fIndexed + EVFlag.fIndexByWords)

I have rewritten the constructor to use this

authors = CreateTextField("authors", 128, EVFlag.fIndexed + EVFlag.fIndexByWords)

But when I create a new database I see this when I examine the authors field in VStudio

authors [I:N:W]

Also, in trying to update an existing database, I open the database and try to convert nullable fields to non-nullable fields

for i = 1 to vdb.Table("thetable";).FieldCount
if vdb.Table("thetable";).Field(i).IsNullable then
vdb.Table("thetable";).Field(i).IsNullable = false
end if
next

Many fields do have isNullable set to false when the code is executed, but when I check immediately thereafter they are still nullable. For example, running the above code twice in a row catches the same fields in both.
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
Hi Jon,

BLOB and therefore TEXT fields, are Nullable by their nature.

They do not use special BIT as do other field types,
BLOB value is { FirstSegmentPtr + SegmentFile }, so you always have FirstSegmentPtr (4bytes) in table.
Which is just 0 if there is no BLOB value itself. And we consider this internally as NULL.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Ruslan Zasukhin Accepted Answer
I think this should not harm you.

Nullable field is always even more nice.

Just for other types, this requires overhead +1 BIT,
this is why on default DBMS make fields NOT NULL.

but in Valentina DB, BLOB fields always can be Nullable with zero overhead for this.
This is good. Don't fight with this :-)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Jonathan Ashwell Accepted Answer
OK, if it's not possible I understand. Please know that I wanted to do this because of the issues raised by this thread:

http://valentina-db.com/en/discussions/2644-not-regex-search-not-working-as-expected

I let our users perform a subset of SQL searches themselves (simple boolean searches of a flat database). The search in that thread was sent to our tech support because it didn't work, and it's hard to explain to them why a search for a field NOT having some text content fails when it should succeed.

Thanks, Ruslan.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Ruslan Zasukhin Accepted Answer
@It should success@ - is not correct guess.
It works according SQL rules.

I think you need
- or explain them this,
- or self add that "not null" check to NOT operation. I guess can be not easy.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
Jonathan Ashwell Accepted Answer
I'd like to revisit this. I have a VText called "attachments". I want to be able to search for ...WHERE attachments=''

The solution I tried in V5.8.6 was to set the field to an empty string after it was created:

myCursor.field("attachments";).setString ""

Then the above search worked. But I see in Valentina 7.0.3 that's not the case anymore. The field is reset to NULL when I set it to an empty string.

Did this change between V5.8.6 and 7.0.3, and if so is it by design? I know I can search for "IS NULL", and I will if I have to, but I'd prefer to search for an empty string.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 5
Ivan Smahin Accepted Answer
Empty string for BLOB-based field is not a good idea, just because you are spending at least one segment per row data. Default segment size is 1024 bytes, so you are wasting 1Kb just to store empty string.

Meanwhile, we are planning to make able blob-based fields be not-null and probably, we will do some optimizations for effectively storing empty data in the BLOB.

And yes, for now, you should extend you search predicates with "IS NULL".
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 6
Jonathan Ashwell Accepted Answer
Yes, that would be a nice feature I'd use.

Thank you, I'll follow your advice.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 7
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories