Converting List<int> to List<object> for One to Many relation with sqlite-net-extensions
-
well i cant seem to find a way to do that, i have also create this funny post in stackoverflow with that matter :) c# - Checking if data exist in sqlite-net before inserting them to database - Stack Overflow[^]
Are you trying to compare bitmaps, or simply looking for a way to do an insert-query while checking if there's already such a record? In pseudo, comparing bitmaps would be similar to below;
Bitmap bitmap1 = (get bitmap from somewhere)
string hashedImage = MakeMD5(bitmap1);
Bitmap compareTo = (get bitmap you want to compare to)
string compareToHash = MakeMD5(compareTo);
bool imagesAreEqual = (hashedImage == compareToHash);That's not helping though if you are trying an insert that does an update when the record is already there.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Are you trying to compare bitmaps, or simply looking for a way to do an insert-query while checking if there's already such a record? In pseudo, comparing bitmaps would be similar to below;
Bitmap bitmap1 = (get bitmap from somewhere)
string hashedImage = MakeMD5(bitmap1);
Bitmap compareTo = (get bitmap you want to compare to)
string compareToHash = MakeMD5(compareTo);
bool imagesAreEqual = (hashedImage == compareToHash);That's not helping though if you are trying an insert that does an update when the record is already there.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
I just want to insert a template in the database only if does not exist. By not exist i mean the List of images to not exist in another template. The only solution i can think of is a predefined hash value
-
I just want to insert a template in the database only if does not exist. By not exist i mean the List of images to not exist in another template. The only solution i can think of is a predefined hash value
Exoskeletor wrote:
I just want to insert a template in the database only if does not exist.
:D You don't need a hash for that, sorry for pointing you in the wrong direction :rolleyes: Instead of an insert-command you usually use, you can use the "INSERT OR REPLACE" command; see SQLite REPLACE: Insert or Replace The Existing Row[^]
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Exoskeletor wrote:
I just want to insert a template in the database only if does not exist.
:D You don't need a hash for that, sorry for pointing you in the wrong direction :rolleyes: Instead of an insert-command you usually use, you can use the "INSERT OR REPLACE" command; see SQLite REPLACE: Insert or Replace The Existing Row[^]
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
but how can sqlite know to replace a record? both my tables have a primary unique key
[Table("Templates")]
public class Template
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
//[TextBlob("imagesBlobbed")]
[OneToMany, Unique]
public List TemplateImages { get; set; }
public string ImagesHash { get; set; }
//public string imagesBlobbed { get; set; }
}
[Table("TemplateImages")]
public class TemplateImage
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
public string ImagesHash { get; set; }
public int Image { get; set; }
[ForeignKey(typeof(Template))]
public int TemplateId { get; set; }
}doesnt that make them unique. Also, this is replacing items according to the primary key it says? so if i have a template with id 5, and i delete it one day, the template that belong to id 6 wll go to the position of id 5? and so on?
-
but how can sqlite know to replace a record? both my tables have a primary unique key
[Table("Templates")]
public class Template
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
//[TextBlob("imagesBlobbed")]
[OneToMany, Unique]
public List TemplateImages { get; set; }
public string ImagesHash { get; set; }
//public string imagesBlobbed { get; set; }
}
[Table("TemplateImages")]
public class TemplateImage
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
public string ImagesHash { get; set; }
public int Image { get; set; }
[ForeignKey(typeof(Template))]
public int TemplateId { get; set; }
}doesnt that make them unique. Also, this is replacing items according to the primary key it says? so if i have a template with id 5, and i delete it one day, the template that belong to id 6 wll go to the position of id 5? and so on?
also i tried in this code
public static void AddTemplate(int category, List images)
{
var templateDB = new TemplateDB();
var imageByteList = new List();foreach (int image in images) { //imageByteList.Add(templateDB.DrawableToByteArray(image)); } var tmpl = new Template() { Category = category, }; var img1 = new TemplateImage() { Category = category, Image = images\[0\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[0\]), }; var img2 = new TemplateImage() { Category = category, Image = images\[1\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[1\]), }; var img3 = new TemplateImage() { Category = category, Image = images\[2\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[2\]), }; var img4 = new TemplateImage() { Category = category, Image = images\[3\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[3\]), }; var img5 = new TemplateImage() { Category = category, Image = images\[4\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[4\]), }; tmpl.TemplateImages = new List() { img1, img2, img3, img4, img5 }; //tmpl.ImagesHash = DatabaseHelper.GetMD5Hash(ConcatByteList(imageByteList)); var result = DatabaseHelper.db().Query("Select \* from Templates where ImagesHash=?", tmpl.ImagesHash); if (result.Count == 0) { DatabaseHelper.db().InsertAll(tmpl.TemplateImages); DatabaseHelper.db().Insert(tmpl); DatabaseHelper.db().UpdateWithChildren(tmpl); }to replace insert with insertorupdate and my template database was empty. id was always 0 in every attemp to insert something to db, do you know why? here is the database structure
[Table("Templates")]
public class Template
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
//[TextBlob("imagesBlobbed") -
but how can sqlite know to replace a record? both my tables have a primary unique key
[Table("Templates")]
public class Template
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
//[TextBlob("imagesBlobbed")]
[OneToMany, Unique]
public List TemplateImages { get; set; }
public string ImagesHash { get; set; }
//public string imagesBlobbed { get; set; }
}
[Table("TemplateImages")]
public class TemplateImage
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
public string ImagesHash { get; set; }
public int Image { get; set; }
[ForeignKey(typeof(Template))]
public int TemplateId { get; set; }
}doesnt that make them unique. Also, this is replacing items according to the primary key it says? so if i have a template with id 5, and i delete it one day, the template that belong to id 6 wll go to the position of id 5? and so on?
The page that I linked to lets you try the SQL statements online :) Hadn't seen that it is a real "replace", not an insert "or" update; but it explains that too. It does look like it honors the unique constraint, so should work with a primary key too.
Exoskeletor wrote:
so if i have a template with id 5, and i delete it one day, the template that belong to id 6 wll go to the position of id 5? and so on?
Id's that have been used already do not get re-used; so if you delete 5 and 6 exists, another insert would give you 7, not 5, even if that position is empty.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
also i tried in this code
public static void AddTemplate(int category, List images)
{
var templateDB = new TemplateDB();
var imageByteList = new List();foreach (int image in images) { //imageByteList.Add(templateDB.DrawableToByteArray(image)); } var tmpl = new Template() { Category = category, }; var img1 = new TemplateImage() { Category = category, Image = images\[0\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[0\]), }; var img2 = new TemplateImage() { Category = category, Image = images\[1\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[1\]), }; var img3 = new TemplateImage() { Category = category, Image = images\[2\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[2\]), }; var img4 = new TemplateImage() { Category = category, Image = images\[3\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[3\]), }; var img5 = new TemplateImage() { Category = category, Image = images\[4\], //ImagesHash = DatabaseHelper.GetMD5Hash(imageByteList\[4\]), }; tmpl.TemplateImages = new List() { img1, img2, img3, img4, img5 }; //tmpl.ImagesHash = DatabaseHelper.GetMD5Hash(ConcatByteList(imageByteList)); var result = DatabaseHelper.db().Query("Select \* from Templates where ImagesHash=?", tmpl.ImagesHash); if (result.Count == 0) { DatabaseHelper.db().InsertAll(tmpl.TemplateImages); DatabaseHelper.db().Insert(tmpl); DatabaseHelper.db().UpdateWithChildren(tmpl); }to replace insert with insertorupdate and my template database was empty. id was always 0 in every attemp to insert something to db, do you know why? here is the database structure
[Table("Templates")]
public class Template
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Category { get; set; }
//[TextBlob("imagesBlobbed")Not quite sure what the code in the
DatabaseHelperdoes, but it looks like you're executing aSELECTstatement, not anINSERT.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Not quite sure what the code in the
DatabaseHelperdoes, but it looks like you're executing aSELECTstatement, not anINSERT.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
With insert the database works but on every app run it reenters the data. with insertorupdate nothing is insert in the database, i read it 2-3 times the page but i cant understand what i'm doing wrong
-
That's reading all the images and making one big array of bytes of it; may be a bit much for the memorymanager.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
insert or update and insert or replace doesnt work with autoincrement fields [^] so i guess again i'm stuck in generating my own guid and use them, or i should run insertorupdate if the database already exist while manually giving the id's starting from Id 1. guid sounds better idea
-
With insert the database works but on every app run it reenters the data. with insertorupdate nothing is insert in the database, i read it 2-3 times the page but i cant understand what i'm doing wrong
-
insert or update and insert or replace doesnt work with autoincrement fields [^] so i guess again i'm stuck in generating my own guid and use them, or i should run insertorupdate if the database already exist while manually giving the id's starting from Id 1. guid sounds better idea
Nothing against Guids; and the order in which the inserts are done is usually not guaranteed, so an autoincrement-assignment may not always be in the exact same order.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
thats why im trying to check if the entry exist, in order to not recreate it. this is the whole point of my question. how to create data once and only check if it exist in the next run. i just show that guid's doesnt change forever, on the same project, so i will use this, i will mark it as unique in the db structure, and everything will work amazingly good i hope :) Is there any other way of checking if data already exist? i dont like the idea of checking if the table exist, what if only the half data have been created and after that the app crash? what is the proper way to verify and create data in a database?
-
Nothing against Guids; and the order in which the inserts are done is usually not guaranteed, so an autoincrement-assignment may not always be in the exact same order.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
what is usually the correct way to check first and then only create data if it is necessary? i have read somewhere that you can see if the table exist, or the table count, but this approach seems so poor to me, what do you say , what is the best way?
-
what is usually the correct way to check first and then only create data if it is necessary? i have read somewhere that you can see if the table exist, or the table count, but this approach seems so poor to me, what do you say , what is the best way?
I usually perform a select (on the pk) to verify if it exists, and if it doesn't, execute an insert; whether that's the "best" idea is debatable. I'm trying to stay away from parts of SQL that aren't part of the SQL92 spec, making it easier to move queries between different types of databases. Another little benefit is that I can use most databases to test any queries, since most support the SQL92 standard. If your code is designed specifically for SQLite, then it makes little sense to limit yourself and not use the options that the database gives you. There's an "upsert" command for example (SQLite Query Language: upsert[^]), combining insert and update (in a somwhat similar way to "insert or replace").
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
thats why im trying to check if the entry exist, in order to not recreate it. this is the whole point of my question. how to create data once and only check if it exist in the next run. i just show that guid's doesnt change forever, on the same project, so i will use this, i will mark it as unique in the db structure, and everything will work amazingly good i hope :) Is there any other way of checking if data already exist? i dont like the idea of checking if the table exist, what if only the half data have been created and after that the app crash? what is the proper way to verify and create data in a database?
Most of what I wrote was based on the (wrong) assumption that you were trying to compare images :D
Exoskeletor wrote:
i dont like the idea of checking if the table exist,
The table should always exist; don't create them on the fly, define them beforehand.
Exoskeletor wrote:
what if only the half data have been created and after that the app crash? what is the proper way to verify and create data in a database?
If you perform multiple inserts, you'll find that SQLite "feels" slow. That's where transactions come in - before the first insert, you start a transaction, and after the last one, you commit the transaction (or roll it back if there was an error). The transaction will be treated as a single statement, and only be written once committed. That way you can ensure all the stuff is there that you want.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
I usually perform a select (on the pk) to verify if it exists, and if it doesn't, execute an insert; whether that's the "best" idea is debatable. I'm trying to stay away from parts of SQL that aren't part of the SQL92 spec, making it easier to move queries between different types of databases. Another little benefit is that I can use most databases to test any queries, since most support the SQL92 standard. If your code is designed specifically for SQLite, then it makes little sense to limit yourself and not use the options that the database gives you. There's an "upsert" command for example (SQLite Query Language: upsert[^]), combining insert and update (in a somwhat similar way to "insert or replace").
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Great friend, with your help im executing a transaction and if it is completed i save this state somewhere. and to check if db exist and not to recreated i have made this code that works great,im also saving the current database version as int in order to easily do more updates in the future. Now all i have to do is call GetTemplateById or GetAllTemplates and if any creation is required will happen :) :)
public bool FirstRun { get; set; } = true; public int DatabaseCreatedVersionOf { get; set; } = -1; public override void OnCreate() { base.OnCreate(); } public void UpdateDatabaseCreatedVersion() { DatabaseCreatedVersionOf = Preferences.Get(Settings.DatabaseCreatedVersionOfKey, Settings.DatabaseCreatedVersionOfDefault); } public void CreateTemplateDB() { UpdateDatabaseCreatedVersion(); if (DatabaseCreatedVersionOf == -1) TemplateDB.CreateDB(); FirstRun = false; } public Template GetTemplateById(int id) { if (FirstRun) { CreateTemplateDB(); FirstRun = false; } return TemplateDB.GetTemplate(id); } public List -
Great friend, with your help im executing a transaction and if it is completed i save this state somewhere. and to check if db exist and not to recreated i have made this code that works great,im also saving the current database version as int in order to easily do more updates in the future. Now all i have to do is call GetTemplateById or GetAllTemplates and if any creation is required will happen :) :)
public bool FirstRun { get; set; } = true; public int DatabaseCreatedVersionOf { get; set; } = -1; public override void OnCreate() { base.OnCreate(); } public void UpdateDatabaseCreatedVersion() { DatabaseCreatedVersionOf = Preferences.Get(Settings.DatabaseCreatedVersionOfKey, Settings.DatabaseCreatedVersionOfDefault); } public void CreateTemplateDB() { UpdateDatabaseCreatedVersion(); if (DatabaseCreatedVersionOf == -1) TemplateDB.CreateDB(); FirstRun = false; } public Template GetTemplateById(int id) { if (FirstRun) { CreateTemplateDB(); FirstRun = false; } return TemplateDB.GetTemplate(id); } public ListGood to hear it works; you're welcome, and I'll be more careful in the future to make sure I understand the question :D
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.