Deciding on JSON format

[CemIskir]

This is my proposal. However it is open to modification.

{ "operation": "create", "table": [ { "name": "myMaliciousta''=''ble2", "fields": [ { "name": "username", "type": "TEXT", "primary": true, "unique": true, "null": false, "default": null }, { "name": "password", "type": "TEXT", "primary": false, "unique": false, "null": false, "default": null }, { "name": "site", "type": "TEXT", "primary": false, "unique": false, "null": false, "default": "localhost" } ] }, { "name": "myMaliciousta''=''ble3", "fields": [ { "name": "username", "type": "TEXT", "primary": false, "unique": true, "null": false, "default": null }, { "name": "password", "type": "TEXT", "primary": false, "unique": false, "null": false, "default": null }, { "name": "site", "type": "TEXT", "primary": false, "unique": false, "null": false, "default": "localhost" } ] } ] }

[CemIskir]

NOTES and WARNINGS

1-Operation can either be "create" or "update" 2-If a field is PRIMARY, then it should automatically be NOT NULL and UNIQUE 3-Type can be one of those: TEXT, REAL(floating point), INTEGER, BOOLEAN(normally this isn't supported in SQLite, I will still hold an INTEGER instead) 4-as you can see, values of NAME and TYPE should be string, and the others should be boolean. 5-I am assuming user can create one table at a time.

6- I am converting every string to characters and digits in my code. However I shouldn't be getting an input like myMaliciousTable';DROP TABLE myMaliciousTable; at first place as a table name. I am checking this not to be hacked if a user tries to send his/her maliciously formatted JSON file. 7- All fields should be filled 8-Table names and field names should be limited to some number of reasonable characters. 9-If the type is boolean, default value should be given as "default": true (not "true")

[tirthpandya]

Looks good to me. I shall assume this format coming to me as input and accordingly code the rest.

Thanks @CemIskir for putting this across.

[CemIskir]

@fabianmuehlboeck

Do we allow user to select compound key (more than one column) as a primary key?

CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname) );

Do we allow them to create referential integrity among tables?

[fabianmuehlboeck]

If possible, we should avoid bothering the users with primary and foreign keys (this is a tool for learning programming, not database design. Ideally they have to know nothing about databases). You should come up with the primary and possibly foreign keys as needed for whatever the user's data design is.

[CemIskir]

Primary keys and/or foreign keys (if it has) will be passed to me right? I managed to create a table from a given JSON file as the one above.

I will do the table structure modification part. Since SQLite doesn't support field alteration, I need to create a new table and do everything manually. I think I need some more clarification on that.

How much change should we allow? Do we expect a completely flexible structure that a table like this:

table user -field1 TEXT -field2 INTEGER -field3 REAL

can become like this:

table u2 -f1 BOOLEAN -f2 INTEGER -field3 INTEGER

Should I preserve the records? If so, for instance, if the table is changed like above, what data should I preserve?

[tirthpandya]

The way lingbo will give the data could also be the following. What do we plan to do then?

Table friend -field1 TEXT -field2 List of Text

So this essentially translates to field2 becoming a FK to some other table where the list of text is stored.

From an API standpoint, if I remember, the persistent type would still have it as a List and not create a new object(or type) of the new table that gets generated on the DB; primarily because we want to keep the DB intricacies away from the user.

Thoughts?

[fabianmuehlboeck]

@CemIskir No, the idea was that you'll figure out what the primary keys and foreign keys are. This should in any case be true for foreign keys. It might be interesting to give the user the option to select some other field as primary key (let's call it "identifier") instead of the default option "unique number".

As for the changes, you make a good point - the current version of the JSON format does not capture field renaming. That seems like a good feature to have, but we would need to track in the editor what fields are renamed and what fields are deleted and created as new. If a field's type is changed, we can attempt some simple conversions, but those are not guaranteed. So I guess we could translate between strings and numbers and Booleans, and maybe strings and dates, but all the directions from string to something else might fail. So if we do that, we would need to notify the user in the editor when they change the type ("Warning: no conversion available, data will be lost" / "Warning: conversion might fail for some or all values", etc.).

@tirthpandya : Yes, there does not need to be a 1-to-1 correspondence between tables and data types (since for lists and maps we will just use default ones).