Android Question A unique code for a sqlite field

[Daniel44]

Hi Everyone!

I'd like to know if exists some function to create a unique code for a field in a sqlite table type of String or number, no matter how long it is. I had thought to collect hour minutes and seconds in a single line to be unique and not repeat but I don't know how to do it. Thank you

 

[RB Smissaert]

Hi Everyone!

I'd like to know if exists some function to create a unique code for a field in a sqlite table type of String or number, no matter how long it is. I had thought to collect hour minutes and seconds in a single line to be unique and not repeat but I don't know how to do it. Thank you

Will need some more information.
What are you trying to do?
How about an autoincrement field (integer)?

RBS

 

[Daniel44]

Will need some more information.
What are you trying to do?
How about an autoincrement field (integer)?

RBS

Thank you for answering. I'm doing a master-detail app and for details I need identify the detail (by a foreign key) with a unique code. I don't want to do that by Id of details table, instead that by a unique code. Thank you again RB Smissaert

 

[Jeffrey Cameron]

We still need more clarification. If all you need is a unique code then an auto-increment column in the detail would give a unique number to every detail entry. If you're looking to identify the string value itself then the MD5 hash of the string might work as your key. Although, just searching for the string value directly would accomplish that as well.... So, yeah, we need a little more clarification of what you are trying to actually accomplish.

 

[Daniel44]

Hey Jeffrey.

Let me explain it. I have 2 table. One Purchases and Purchases Details.

Purchases Table

id
Date
purchase_number
Total
buyer_name

purchase_details

id
purchase_number
product
quantity
product_price
sub-total
Total



Inside Table Purchase it gonna be just some data but in details it gonna be all purchases, I mean all buyer, all product but the only way to identify the details of a purchase is that unique number by a Sql sentence. I hope you've explained well.

 

[Daniel44]

in each purchase I need to generate a number or a set of characters unique and never repeated. That's why I had thought of collecting the current date, hour, minutes and seconds at the time the purchase occurs.

 

[AnandGupta]

There is a GUID for it, as far as I remember, is unique in all cases. Used by our .Net team.

Regards,

Anand

 

[KMatle]

See "autoincrement". This feature exist in all DB systems.

Example:

Purchases Table

id ---> set this column to autoincrement
Date
purchase_number
Total
buyer_name

When inserting a new Purchase, just set id to NULL. Here the db system creates a unique number = what you need. Right after the insert get the new id by

LastIndertedCustomerID=Sql1.ExecQuerySingleResult("SELECT last_insert_rowid()")

This ID is used to assign the details to THIS purchase.

See my example where I do the same for customers which have n orders: https://www.b4x.com/android/forum/threads/b4x-sqlite-with-6-million-rows.91563/

 

[emexes]

the only way to identify the details of a purchase is that unique number by a Sql sentence

Whilst there's nothing wrong with having a unique id number for every record in the detail table, I think here that what you are wanting to do is to be able to discern between the detail lines of a purchase. I have previously done this by having a "line number" field that is the position of that detail line on a printed representation of the purchase quote/order/invoice, and within the array that held the detail lines for display and editing.

But our editing was done in-memory, with the database only updated when the user clicked the "save" button... or abandoned when the user clicked the "cancel" button.
If you are editing the purchase "live" within the database, then this solution is probably better:

id ---> set this column to autoincrement

than you reimplementing a similar functionality. Happily, it doesn't particularly matter that the number is more unique than it needs to be, and you can still sort the detail lines by this field to ensure that duplicate printouts have the detail lines in the same order (auditors hate stuff where prints of the same document appear different).

 

[emexes]

Purchases Table

id
Date
purchase_number
Total
buyer_name

purchase_details

id
purchase_number
product
quantity
product_price
sub-total
Total

BTW why do you have Total in the details table? I can understand having sub_total, to protect against changes after the purchase is finalised. But to have Total on every detail line, would mean that a change to one detail line that changes Total, requires all the other detail lines to be updated also.

 

[Erel]

GUID implementation is available here: https://www.b4x.com/android/forum/threads/b4x-guid.99529/#content

 

[fredo]

create a unique code for a field in a sqlite table type of String or number,

See here for an easy way: https://www.b4x.com/android/forum/threads/sqlite-automatic-primary-key-generation.93206/

You implement it in the table design

Using autoincrement is a bad idea if you can assume that the database will be used for a long time. Be inspired by this: https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/

From painful experience I must urgently advise against AutoInkrements, since with non-trivial applications sooner or later data from different sources must be merged.
It is a very time-consuming matter to adjust the primary keys and all associated secondary keys from the second db without any mistakes.

If you build the UUID cleverly, you can force worldwide distinctiveness.

​

 

[Daniel44]

GUID implementation is available here: https://www.b4x.com/android/forum/threads/b4x-guid.99529/#content

Thank you Erel

 

[Daniel44]

See here for an easy way: https://www.b4x.com/android/forum/threads/sqlite-automatic-primary-key-generation.93206/

You implement it in the table design
View attachment 83744​

Using autoincrement is a bad idea if you can assume that the database will be used for a long time. Be inspired by this: https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/

From painful experience I must urgently advise against AutoInkrements, since with non-trivial applications sooner or later data from different sources must be merged.
It is a very time-consuming matter to adjust the primary keys and all associated secondary keys from the second db without any mistakes.

If you build the UUID cleverly, you can force worldwide distinctiveness.

​

Thank you fredo I didn't know about using autoincrement keys