Looping on making columns

[zenera]

Will it be possible to make a table using for loop in sqlite_orm? The example code below is what I want to make.

const int max_columns = 1000; struct Book { int id; std::vector values(max_columns); // store 1000 property values of string };

Table_type *table = define_table("books", define_column("id", &Book::id, primary_key()));

std::vector column_names {"c1", "c2", "c3", ..., "c1000"}; std::vector columns(max_columns); for(int i=0; i<max_columns; ++i) { columns[i] = define_column(column_names[i], &Book::values[i]); table->add_column(columns[i]); } table->create();

[fnc12]

Hi. Thanks for using the lib. How would you do it with a raw SQLite client? There is no query like this. If you need to store an array you'd better store it in a separate table and chain it with another table with a foreign key. Could you please tell me more details about your schema and task?

[zenera]

I'm a newbie to ORM and just now testing the sqlite_orm. So, you may ignore my question or ideas safely. Maybe SQLite client can't do that, and I understand a separate table with foreign key might be a good enough solution. Just my thought. It would be very useful if tables and columns could be created dynamically as need basis. And SQLite can't make std::vector things to another sub-table, but you can. :-)

Anyway, thank you for creating and distributing sqlite_orm.

[fnc12]

Your idea visits a lot of developers but in fact most of them do not need dynamic schema. But if you need to make a dynamic storage you can create different storages using different file name (make_storage's first argument). Let's assume sqlite_orm can create table/columns dynamically and you made a loop of 1000 iterations and created a table with 1000 columns. What if next day you require 1001th item? Altering table is not a good solution in such a case. You have to preserve schema immutable if app logic doesn't change. If you have new objects they must be put as rows in a specific table - you must not create a new column once objects limit is reached. This is how database management works. If you need to map an array to a single object you'd better use foreign key. sqlite_orm doesn't support subentities cause it can hide important implementation details that can require customization by a developer. There is an example which describes subentities: students stored with marks. I think it would be interesting for you. Idea with adding std::vector things to another sub-table feature is a good idea but first of all we need to decide how it would make foreign keys inside cause it is very important. sqlite_orm handles a lot of work for you and it also gives you all tools for customization. If I add std::vector things to another sub-table feature I'll leave users without ability of setting custom foreign key name. It is better to know database well that use smart things that do a lot of work in one line that users doesn't understand clearly. sqlite_orm tries to follow a simple principle: one function call make one query.

[zenera]

I'm living in a very complex and dynamically changing world. And I'm fighting with legacy frameworks - just a joke. If 1001th item is needed, I'll predictably make 2000 columns. 2001th item? Ok. I'll drop table and create a new table with 3000 columns, if only I could make a table dynamically so easily. In reality I'm dealing with real-time experimental data by some calculation. I don't know some calculated variables are useless or valuable. SQLite is very useful for this work. Frankly speaking, I don't like those principles. There are always pros and cons. "std::vector things to another sub-table" feature may break your principle and make some users confused. But many users will be very happy including me. :-P

Thank you for your reply and the decision is yours. I'll respect your decision.

[fnc12]

Many users will be happy only at the very first sight. Once this code grows into something serious you'll find out that it is better to make it right not quick cause those database storage principles are created to make our life easier not harder. Assume such an example: you're too lazy to make insurance for the house you live in but once your house is burned you remember that it would be better to think about it the very beginning. Or maybe you're too lazy to use a seat belt? But these three seconds you spend once you get in car can save your life. Database management is the same: there is a working solution which can work at any conditions without altering your tables. But you do like to leave it and to drive without seatbelt. It's ok. Thanks for using the lib. Also you can give me the example you want to implement and I'll give you an advice how to perform it in the best way.

[fnc12]

Also you can try to implement it by yourself and provide a pull request. If it is ok I can merge it

[zenera]

I'm little confused with between subentities and foreign_key examples. What are the differences in the point of use-cases? If I don't delete or update a Student id, subentities example is a convenience purpose, right? Anyway, my data can be saved in this way. But there may be a performance issue to search values for the matching foreign keys. And unlike with subentities example, all marks should be assigned to all students and the order of marks should be maintained for each student. If I accidentally delete one of records of Mark table all the data might be useless. The marks are Student's properties in my case. If I can create a table dynamically, the issues won't be a matter. I'v already gave you an example above. How can I create a table in such a way?

Thank you again for your help.

[fnc12]

Difference is great. In my case marks are different kind of entity (a different class) and that is why marks are stored in a different table. Table can store infinity amount of rows so you don't need to think about whether or not you reached your columns limit. Performance is not an issue if you have correct indexes. One of database goals is to provide a very quick access to any set of data. What do you mean saying all marks should be assigned to all students? There are three types of entity relations in database: 1 to 1 (one user avatar belongs to one user), 1 to many (one student has more than one mark, but one mark belongs to one student) and many to many (user can apply invitation to many events and every event can have more that one user applied). What kind of entity relationship do you want? If you have one to many relationship and you remove one mark then nothing critical would happen - just one mark will be erased, other marks will be safe and students too.

[zenera]

struct Property { int value; int student_id; };

struct Student { int id std::vector<decltype(Property::value)> values; };

value student_id 10 1 <= p1 (property1 for student1) 2 1 <= p2 12 1 <= p3 = p1 + p2 => assume accidently deleted record 20 1 <= p4 = p1 * p2 6 1 <= p5 = p3 / p2 ..... 50 1 <= p1000 = p100 + p999 20 2 <= p1 (property1 for student2) ..... 12 2 <= p1000 for student2 ..... I have simple 1 to many relationship data between a student and his 1000 properties. The calculated properties are dependent on each others. If I have accidentally deleted the row3 of the Property table and assume I don't know which property was deleted. p4 will be recognized as p3 and so on... I lost all about Student1. Even more, I have to analyze some realtionships between p4 of Student1 and p4 of Student2 and so on. I can't do that because Student1's data becomes meaningless. All the data but one record are still keeping their positions as you said, but I can't analyse the data. So, all the data becomes useless. I'm not a native English speaker, so I can't express exactly my intention. Anyway isn't it impossible to create columns by using for loop? I have to use 1000 lines of make_columns function in sqlite_orm?

[fnc12]

You say assume accidently deleted record. If you see a record as a column how can you delete it? You can delete a row easily but deleting a column is not a regular action. One more question: are properties evaluated before inserting or on every select? Thanks P.S. You don't need to create 1000 columns. Databases created especially for people who does not want to make 1000 same lines of code.

[zenera]

Ok! I'll give up your answer.

I'm just using 2 columns in the Property table. AND I don't delete ANY columns! Just delete 1 row(3rd row) in the table.

If I do not use Property table, I would have to use 1000 columns of a Student table for 1000 properties.

struct Student { int weight(1st property); int height(2nd); ..... int volume(1000th); };

By using the Property table instead, the properties should be saved as 1000 rows in the Property table.

Is it right method using 1000 lines of make_column function in sqlite_orm? Anyway, thanks. I'm so tired.

[fnc12]

struct Student {
    int id = 0;
};

struct Property {
    int studentId = 0;
    int index = 0;
    int value = 0;
};

using namespace sqlite_orm;

inline auto makeStorage(const std::string &path) {
    return make_storage(path,
                        make_table("students",
                                   make_column("id", &Student::id, autoincrement(), primary_key())),
                        make_table("properties",
                                   make_column("student_id", &Property::studentId),
                                   make_column("index", &Property::index),
                                   make_column("value", &Property::value),
                                   foreign_key(&Property::studentId).references(&Student::id)));
}

using Storage = decltype(makeStorage({}));

void addPropertyToStudent(const Student &student, Storage &storage, int propertyValue) {
    auto newPropertyIndex = storage.count<Property>(where(c(&Property::studentId) == student.id));
    storage.insert(Property{student.id, newPropertyIndex, propertyValue});
}

std::unique_ptr<Property> getProperty(int studentId, int index, Storage &storage) {
    auto properties = storage.get_all<Property>(where(c(&Property::studentId) == studentId and c(&Property::index) == index));
    if(properties.size()){
        assert(properties.size() == 1);
        return std::make_unique<Property>(std::move(properties.front()));
    }else{
        return {};
    }
}

int main() {

    auto storage = makeStorage("students.sqlite");
    storage.sync_schema(true);

    //  create student
    Student student1{0};

    //  insert student in the storage
    student1.id = storage.insert(student1);

    addPropertyToStudent(student1, storage, 50);    //  this is property[0]
    addPropertyToStudent(student1, storage, 180);   //  this is property[1]

    assert(getProperty(student1.id, 0, storage)->value == 50);
    assert(getProperty(student1.id, 1, storage)->value == 180);

    return 0;
}

[zenera]

Thank you for your concern.

But using index doesn't help me. Even if I knew the deleted property, a deleted snapshot of all data is still useless.

I'm not good at template programming, but I'm looking at the sqlite_orm source. I don't know if it is possible now, but I'll try to separate the APIs - make_storage/make_table/make_column. And by using alter_table, I'll try to add columns in for loop.

[fnc12]

Wait, what exactly you miss in my concern? I just cannot understand it quite right. Please describe me what do you miss. Spoiler: you won't be able to add column in a loop cause single column maps to a single class member. You cannot map a column to a vector value. Column are defined at compile time but for is a runtime operation. The only way to make it is adding compile time recursive template loop. Also you can store a vector of 1000 values and create 1000 getter and setter and them map getters and setters to 100 column. But it still will be several block of 1000 lines of code. If you want it that way - please do whatever you want. But I'm sure that you can do it correct way without thousands lines of code

[zenera]

You gave me the answer for my original question of this thread through your spoiler. The information is the most valuable to me. Now I can choose plan B. Risk taking the deletion of some records might be an alternative.

Thank you for your spoiler. I'll close this issue.

[zenera]

I'v found the parallel use of other SQLite wrapper like QSqlQuery with sqlite_orm is very useful for this exceptional use-case. Another useful use-case of dynamic creation of table is for storages of model/view framework in Qt.

For Qt users, I'v tested sample below successfully. Dynamic CRUD bindings are possible(not shown here).

using RowItem = std::vector<QString>;

class ModelDAO
{
public:
  ModelDAO(const QString& name);

  void createTable();
  //void addRow(size_t rowId, const RowItem& rowItem);
  //void updateRow(size_t rowId, const RowItem& rowItem);
  //void removeRow(size_t rowId);
  void saveModel(QAbstractTableModel* model);
  std::unique_ptr<std::vector<std::unique_ptr<RowItem>>> loadModel() const;

private:
  QString m_name;     // model/table name
  int m_columns{0};   // number of columns in a table
  QSqlDatabase& m_db; // DB instance
};

ModelDAO::ModelDAO(const QString& name) :
  m_name(name),
  m_db(*DBManager::instance().getDB())
{
  if(!m_db.tables().contains(m_name)) return;
  QSqlQuery query(m_db);
  query.exec("SELECT * FROM " + m_name + " LIMIT 1");
  if(query.next()) m_columns = query.record().count(); // check if there is a returned record.
  else query.exec("DROP TABLE " + m_name);
}

void ModelDAO::createTable()
{
  if(!m_columns) return;
  QSqlQuery query(m_db);
  QString qs = "CREATE TABLE " + m_name + " (id INTEGER PRIMARY KEY AUTOINCREMENT)";
  query.exec(qs);
  DBManager::debugQuery(query);
  qs = "ALTER TABLE " + m_name + " ADD COLUMN ";
  for(int col = 1; col < m_columns; ++col) {
    QString cs = "col" + QVariant(col).toString() + " TEXT";
    query.exec(qs + cs);
  }
}

void ModelDAO::saveModel(QAbstractTableModel* model)
{
  if(!model) return;
  if(!m_columns) {
    m_columns = model->columnCount() + 1; // add id column.
    createTable();
  }

  QString bs = " VALUES (";
  for(int col = 0; col < m_columns - 1; ++col) bs += "?, ";
  bs += "?)";
  QString qs = "REPLACE INTO " + m_name + bs;
  m_db.transaction();
  QSqlQuery query;
  for(int row = 0; row < model->rowCount(); ++row) {
    query.prepare(qs);
    query.bindValue(0, row);
    for(int i = 1; i < m_columns; ++i) query.bindValue(i, model->data(model->index(row, i - 1)));
    query.exec();
  }
  m_db.commit();
}

std::unique_ptr<std::vector<std::unique_ptr<RowItem>>> ModelDAO::loadModel() const
{
  if(!m_columns) return nullptr;
  QString qs = "SELECT * FROM " + m_name;
  QSqlQuery query(qs, m_db);
  query.exec();
  DBManager::debugQuery(query);
  std::unique_ptr<std::vector<std::unique_ptr<RowItem>>> list(new std::vector<std::unique_ptr<RowItem>>());
  while(query.next()) {
    std::unique_ptr<RowItem> rowItem(new RowItem(size_t(m_columns - 1)));
    for(int i = 1; i < m_columns; ++i) {
      QString cs = "col" + QVariant(i).toString();
      (*rowItem)[size_t(i - 1)] = query.value(cs).toString();
    }
    list->push_back(std::move(rowItem));
  }
  return list;
}

[fnc12]

if it is more comfortable for you please use it. I've never tried to make people use only my lib