SQLite for ags (experimental plugin)

[eri0o]

I did a plugin that gives sqlite capabilities for ags. GitHub repo is here and already includes the dll and so for download under Releases->Assets.

I wonder if anyone has any suggestions regarding reading the resulting SQL requests when searching the database, right now it will return pure text with the results.

Probably no one will answer this right now, but someday in the future when someone browses around for sqlite and AGS I will get an answer.

You can create a table using

Code: ags

AgsSQLite* db = AgsSQLite.Open("test.db");
  
  String sql = "";
  
  sql = sql.Append("CREATE TABLE CHARACTERS(");
  sql = sql.Append( "ID INT PRIMARY KEY     NOT NULL,");
  sql = sql.Append("NAME           TEXT    NOT NULL,");
  sql = sql.Append("ROOM            INT     NOT NULL,");
  sql = sql.Append("X               INT     NOT NULL,");
  sql = sql.Append("Y               INT     NOT NULL);");
  
  SQLiteQueryStatus rc = db.ExecuteQuery(sql);
    
  Display(db.GetQueryStatusText());  

Apparently I need to conceive some sort of table object so that the result of a query is easier to traverse. I imagine the simplest object would be something like:

Code: ags

managed struct Table {
  import attribute String Cell[];
  int RowCount;
  int ColumnCount;
};

And it would be accessible with something like:

Code: ags

Table* result = db.GetQueryResult();
Display(result.Cell[x+y*result.RowCount])

Still need to think a little more.

[Pax Animo]

Sorry for bringing up an old post but this is actually something i'm really interested in, if i can bring something like this to my AGS projects i'd be on a good path forwards.

[eri0o]

Hey Pax Animo!

You are interested in this plugin? Can you tell me a bit about your usecase? Like platforms and such.

At the time I was starting with this, there was a SQLite like API for browsers in development, which was available in chrome, but it has been discontinued, so for instance, using SQLite in the browser nowadays would mean having the db in the JS/wasm VM instead of being able to access an external DB.

I stopped when I was going to add something like a ResultSet, mostly because it's what I am used from Java - ha, apparently I wrote exactly this in the repo in 2019, but I wrote here from memory. The queries themselves though I thought to keep as is and only change for the query compilation mechanism if some performance issue arises, but it all was really quick when I tested.

So anyway, if you have things you can comment on iteration on tables, and SQLite usage, that would be helpful, because I barely had an usecase when I started this for myself at the time - I mostly liked the Editor for SQLite tables and thought it a was a neat way to organize data.