C#

Exoskeletor wrote:

but it's not working and i'm guessing it is not working because you cannot query with parameter "Images", in the database is stored as a blob. So im guessing i have to convert List to something else that is more easily supported in sqlite.

Exoskeletor wrote:

In my application every template has 5 images, but this might change in the future. What is the correct approach according to DB design, to have a TemplateImage object with 5 images or to have only one image for every TemplateImage object like this:

Bastard Programmer from Hell
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 would have to retrieve all the images and check them one by one

 public static int GetSequenceHashCode<T>(this IList<T> sequence)
        {
            const int seed = 487;
            const int modifier = 31;

            unchecked
            {
                return sequence.Aggregate(seed, (current, item) =>
                    (current * modifier) + item.GetHashCode());
            }
        }
public static void AddTemplate(int category, List<int> images)
        {
var tmpl = new Template()
            {
                Category = category,
            };
            var img1 = new TemplateImage()
            {
                Category = category,
                Image = images[0],
            };
            var img2 = new TemplateImage()
            {
                Category = category,
                Image = images[1],
            };
            var img3 = new TemplateImage()
            {
                Category = category,
                Image = images[2],
            };
            var img4 = new TemplateImage()
            {
                Category = category,
                Image = images[3],
            };
            var img5 = new TemplateImage()
            {
                Category = category,
                Image = images[4],
            };
            tmpl.TemplateImages = new List<TemplateImage>() { img1, img2, img3, img4, img5 };
            tmpl.ImagesHash = tmpl.TemplateImages.GetSequenceHashCode();
            var result = DatabaseHelper.db().Query<Template>("Select * From Templates where ImagesHash=?", tmpl.ImagesHash).ToList();
            if (result.Count == 0)
            {
                DatabaseHelper.db().InsertAll(tmpl.TemplateImages);
                DatabaseHelper.db().Insert(tmpl);
                DatabaseHelper.db().UpdateWithChildren(tmpl);

                var employeeStored = DatabaseHelper.db().GetWithChildren<Template>(tmpl.Id);
            }

var result = DatabaseHelper.db().Query<TemplateImage>("Select * from TemplateImages where Image=?", images[0]).Count +
     DatabaseHelper.db().Query<TemplateImage>("Select * from TemplateImages where Image=?", images[1]).Count +
      DatabaseHelper.db().Query<TemplateImage>("Select * from TemplateImages where Image=?", images[2]).Count +
       DatabaseHelper.db().Query<TemplateImage>("Select * from TemplateImages where Image=?", images[3]).Count +
        DatabaseHelper.db().Query<TemplateImage>("Select * from TemplateImages where Image=?", images[4]).Count;


    ;//.ToList();
if (result.Count == 5)

Exoskeletor wrote:

however the GetSequenceHashCode doesnt work as expected, it gives different results on every run, so i will have to check with another code.

Exoskeletor wrote:

But looks very bad to me, will this have impact on performance? is there any other way for the same result?

Bastard Programmer from Hell
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.

[Table("Templates")]
public class Template
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int Category { get; set; }
    //[TextBlob("imagesBlobbed")]
    [OneToMany]
    public List<TemplateImage> TemplateImages { get; set; }
    public int 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 int Image { get; set; }
    [ForeignKey(typeof(Template))]
    public int TemplateId { get; set; }
}

Quote:

You could combine those five queries into one. Something like "SELECT Id FROM TemplateImages WHERE ImageHash IN (@value1, @value2, @value3, @value4, @value5". If the values already exist in the table, such a query would give you their Id's.

var result = DatabaseHelper.db().Query<TemplateImage>("Select * from TemplateImages where Image=?", images[0]);

Exoskeletor wrote:

So the question is, how i can md5 List<templateimage>? (or another consistent hash)

Exoskeletor wrote:

If i can find a single query that can do that for all the images, i think im good

Bastard Programmer from Hell
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:

how i can store 5 hashes

Exoskeletor wrote:

and check them when im inserting a new template with one query?

Bastard Programmer from Hell
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.

[Table("Templates")]
    public class Template
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public int Category { get; set; }
        //[TextBlob("imagesBlobbed")]
        [OneToMany, Indexed(Name = "TemplateImagesUnique", Unique = true)]
        public List<TemplateImage> 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 int Image { get; set; }
        [ForeignKey(typeof(Template))]
        public int TemplateId { get; set; }
    }

Exoskeletor wrote:

which blob? i don't use blobs any more

[Table("TemplateImages")]
public class TemplateImage
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int Category { get; set; }
    public stream Image { get; set; } // why was that an int? Numbers aren't images
    public string ImageHash { get; set; }
    [ForeignKey(typeof(Template))]
    public int TemplateId { get; set; }
}

Bastard Programmer from Hell
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.

ResourceId 

Exoskeletor wrote:

2) convert them with +s from all the images to one string,

Exoskeletor wrote:

i think i have to read the images as files, and in order to do that i have to open a stream, this sounds very heavy to me

Bastard Programmer from Hell
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.