SQLite Format Support

[3PIV]

Would it be possible to support SQLite as a file format for maps/tiles? SQLite is a great candidate for storing Tiled files on disk and easing integration of Tiled into other software.

[bjorn]

It would be possible to support, but the question is what should go into the SQLite file?

Would we still have a separate file for each map and tileset, or would it make more sense to store an entire project in a single file, possibly only keeping images as separate files because that way they're easier to edit?

Also, I guess for convenience reasons we'd still store part of the data as (probably compressed) XML or (more likely) JSON. Either way, we need to define the structure of the database. Do you have any suggestions on this front?

[3PIV]

SQLite would excel at combining the multiple files and datasets currently used and creating a singular database from them. It provides an excellent API that would allow separating the database into separate maps and tilesets, eliminating the need for multiple files.

You are correct about the images, placing an image as data into the database is quite easy but it would make editing the image on the user end quite difficult. It would be possible to still have the option to place the image as data in the file though.

Perhaps users could have the option to "save images as archive" where the images are placed into the database as compressed data? This archive option would make the format extremely portable. A tiled map could be saved to a single file and easily distributed with the images required for a map. Of course, to modify the images the project file would need to be unarchived.

I am currently toying with a python script that converts a JSON map in CSV Tile Format into an SQLite database. It is still a Work-In-Progress and limited to housing a single Map, the Tilesets, and Animations of the tilesets of the single map. I am hoping to expand the support of this converter to encompass a Project of multiple maps as well as more features that Tiled provides like Objects and Groups. I don't expect this script to be a production tool but I can share any insight it gives on Database Structure.

If SQLite support is a feature to keep on this project's radar I can shoot over a follow-up message on the potential structure for the database when I have a better working output from my script.

[bjorn]

If SQLite support is a feature to keep on this project's radar I can shoot over a follow-up message on the potential structure for the database when I have a better working output from my script.

That would be great!

A few more things I realized regarding such a feature:

• Since everything is in the database, we need custom file selection UI for opening maps, selecting tilesets, etc. and we also need to provide the actions to delete assets from the database (currently Tiled never deletes maps/tilesets). Regarding the file selection, this is something I already wanted to change anyway since now that we have projects it would in many cases be preferable to select a tileset from the project than by using the system file dialog. And deletion is a feature we should probably support on the project level already as well.

• A database file for the entire project is an incredibly bad idea if you keep your data under version control. It means we may need to provide both a custom solution for version control (being able to go back revisions or restore deleted assets) and an alternative solution for collaborating on a project (potentially a custom merge tool or a client/server approach for concurrent editing).

• Given the above, much of this feature would need to be behind an abstraction layer since there will likely be many who'll prefer to keep using the file-per-asset way of working.

[julientype]

SQLite works 30% faster then file request ....... You need a good relational joint engine........ all your tiles are uploaded as a relation object..... since game are just read entry for logic SQLite on server works well..... no record locking you never delete assets from a database .... you simply query what you want into your modules...... like if you want to save your game...... it also can page catch a record set open door draw room....... 50,000 tiny images can be load to dom in a flash....... the neat thing it only holds 1 image to paint base on the relational joint entry.... its also faster then json where you have to create a json object..... you did it in one SQL call at C level engine not JavaScript....... it streams out the data to JavaScript like an object array.....

Here is how i do it ...... i send a dom element to the transaction call ...... my JavaScript will now go to the next statement.... and the dom element is waiting for the sql to finish..... then it runs on a separate thread with the dom object.... like animation ..... function run ( object , variables) db get SQL transaction base on variable input.... rec = return then do what you want to the object you sent in base on the return record..... your on a different thread holding a dom object your regular JavaScript is running actions and getting events..... is the object i hold dead.. no.... then fire a shot....... your runtime JavaScript just got an event from the object on different thread from an SQL transaction...... you can leave the transaction or stay in it ...loop till dead........ or go to a new transaction with the object from a transaction..... fire off this sql statement and take me with you...... no more gold..... or you died.... the obj you sent in can attach and unatach events within the sql transaction..... its isolated till you leave.... or an error happens ending the transaction on error send object to this transaction or fix your code error..... SQL light is good for quey read records not add edit delete too many transaction request..... your Object arrays hold the runtime logic..... to save delete or load game.... SQLite is good for that its just one transaction in a record lock mode...... 30% faster and Json filter and query stinks its slow and hard to code in.... where SQL call is nice an fast no mater how complex the call is... less code.....

[julientype]

Would we still have a separate file for each map and tileset, or would it make more sense to store an entire project in a single file, possibly only keeping images as separate files because that way they're easier to edit?

yes you can keep all in one file..... it must be done correctly....... sql calls..... images are index binary or 64base when you open a recorder set you don't ask for everything..... the sql query and the relation table joints will do the work for you.... Table map array has ... 1000 by 1000 records with 100 different tiles.. rec 1 tile 1 to index tile 3 rec 2 tile 1-2 to index tile 3 and so on...... rec 3 is funny its 4 tiles as 1 tile...... /\ one to many images rec entry..... or tile dimensions is held in the record...... like you would see on flat earth map is not square its a circular tile.... the record entry holds the dimension logic..... its also tells you how many rings by the rec entry with clock divider lines... a relational object layout....... wow...... simply hook up the sql data file view on one computer and code in the other....... the grid data will show you what to code and reference.... Like a foreign key field map join..... x y tile holds a map record set...... can be any type.... a tube with a foreign key field map joint to a circle tile..... like if you wanted to build a chain...... lol in your code you take the rec and Object.assing its logic = [] push all done...lol nice and clean and your code shrinks...... i want to build a house..... your now in a transaction with SQL.....
one tile bock at a time under an order entry SQL statement thread.... this is not json it has its own tread all ready to go in an object.... i did not have to phrase anything i simply pass it over.... done By C lib

load up all your collision data records in one sql call cycle it inside the transaction function set it up with a set time out function.... the record function holds the logic...its a static record array object you can change the data values in the loaded array records ......the c lib sent to you..... its in its own space memory....... you just cant load more records in the same function you can only pass the object to a new sql transaction...... like you get to see all from in the function but no one sees you;;; its why you pass an object to the transaction function to play with....

as you can see you can hold tones of static load logic in the database ready to use in a flash or sqlite parameterized query <---- nice feature for a trigger simply inject variable and send back data you need..... send this object to this sql transaction with these variables.. calculation done..... no real JavaScript did the calculations the best part is it sent back a list of record instruction you loop code on...... need a path they are virtual table load to game engine table ..... ======= its only good for a read engine and faster then a file reader=========== https://sqlite.org/lang_transaction.html 2.1. Read transactions versus write transactions SQLite supports multiple simultaneous read transactions coming from separate database connections, possibly in separate threads or processes, but only one simultaneous write transaction. ==== only one lock write transaction =====

[julientype]

a database accounting program...... you need all the tables and joints built..... game engine is the same...... fetch records and render.... https://sqlite.org/lang_expr.html

your javascript object would hold array data.... var a = loadmap(sendAarry) const obj = {}; obj.dbarray =[]; create what ever you need obj.get = function{ db.run sql with this object a

} obj.map = document.createElement("div") obj.a = document.createElement("img") obj.a.onclick = function(e) obj.a.ondrop = function(e) obj.map.appendchild(obj.a) document.appendchild(obj.map) return obj /// all done var b = loadmap(sendArray) ////// both independent object with events///// did i register an event ... no... and when you do ...you must unregister it

in an sql database you create a view query Table and dump obj.dbarray the return records are the objects instructions..... loop these return records

when you create and save a query Table your building C code .....

select viewName and dump this obj.dbarray in it........ a transaction the expression builder is part the saved sql statement as a query Table view....

let say you have builders and they run out of food.... query the database for new instructions

createObjectURL() to transfer db images you create array pointers now only 1 image is loaded...... obj.mapimages = [] in db transaction
obj.mapimages.push(new urlImage(db.rec)) next rec function urlImages (db.rec) const obj = {}; obj.url = createObjectURL( db.rec.???? 64Base) obj.pos = db.rec.???? URL.revokeObjectURL() when you no longer need them. return obj

30% faster then file request and your JavaScript shrink 60% your after layout tables and query command logic.....

[julientype]

My sqlite is a file service database

when map editing read right delete works... but not in game play..... you will corrupt the database.... let say you selected 30 men and want to move them ???? think of a database as a spreadsheet you dump numbers into query templates....... Now your database will process the records for 30 men to move..... you may get a 1000s record back..... telling you frame by frame how to move 30men it can be 10men 5men the query view templates works the same ....... each man is an object in JavaScript with it own logic from a JavaScript object constructor. like an AI enemy is coming at you with 30 men and each respond differently .....

did i use any json...... no did i use XML.....no just JavaScript array objects and SQL statements with dom elements when you fetch records its automatically packaged it as JavaScript array objects.... obj,AImove.push(???) or flush them out before you push more in..... do not set as null or redefine the obj,AImove = [] other obj can hold reference to it

this type of game building can only be done from the floor up.... a temple engine for basic constructor tables and animation effect... like to produce output records you need the map grid entries... 1000 by 1000

to expand it now becomes simple create new table or create new view..... like an accounting package your all set to go... and you can also expand it... append new field to any table....... then add the logic to the JavaScript obj.newField

DB version template engine and its predefine JavaScript obj modules that go with it.....
v2 v3 v4 v5

[julientype]

to make things easy you call the JavaScript object file name a query view name... so when you open the game editor you can find the object in code back and forth... as you can see it becomes simple all your variables are not hidden in a json file... this would be your commend and control view to your JavaScript objects your working on as a template engine..... and for me i want build a game so i simply load the db and enter the data.....embedded images...... and a tile could be just a simple color code entry....css tile very tiny or color canvas or an svg for extra control or the simple bitmap you now use...... svg element offers expand and shrink animation unlike bitmap they distort... i can use a simple svg a one object line vector like fire a shot as it moves it changes colors and shape visible on off......svg event data... a flower keeps growing ..yes its a heavy load.... yet it less code.... bitmaps cant do that..... svg a line and a ball .... ball fallow line path its built in the svg object. db.rec load vector line path and run ball at this speed ...... pitcher at a base ball game..... since its a svg it can shrink and grow with no distortions...... a reusable animated over lay object.... you inject the variables and db produce the record to inject into the animated object..... 1 record the bat is an svg animated not the player an svg object will hold all the math...... and the ball object is coming at you with math x y z.....now swing do nothing the svg object has the math to inject the db.... one rec return out goes the ball how mush JavaScript did a need....lol 1000s lines right to just throw and hit a ball a fake 3d engine math with only 2 svg elements