Declare unique column in MySQL

[magit]

Declare unique column in MySQL

[skeeto]

Try using :autoincrement instead of :auto_increment. It's spelled as AUTOINCREMENT in SQLite. Unfortunately it looks like it's spelled as AUTO_INCREMENT in MySQL and PostgreSQL, so this all depends on the backend. I wasn't aware of this issue until now, but I'm interested in fixing it sometime since I want the different backends fairly normalized. I don't know what the SQL standard specifies for the spelling (generally what PostgreSQL does), if it's even standardized at all.

Just in case you didn't know, you can use the emacsql-show-last-sql command to examine the SQL query sent to the backend.

CREATE TABLE IF NOT EXISTS info (id &INTEGER PRIMARY KEY
    AUTOINCREMENT, node &TEXT, contents &TEXT UNIQUE);

That should help in debugging this sort of issue. The & tokens will be replaced with the backend's specific types.

[wvxvw]

Actually, the problem is not exactly syntactical. Latest version of MySQL (MariaDB) will recognize UNIQUE in column definition as valid syntax (a more traditional way in MySQL is to write the constraints after column definitions, as if they were separate columns).

The problem is that unless you specify the minimum length for the field, e.g. varchar(100), MySQL cannot make it unique (in order to make it unique, it needs to build an index, and in order to do that, it needs to know the size of the field). So, if, instead of &TEXT I could put &VARCHAR(100), that would have worked too.

[skeeto]

Ah, gotcha, you were already ahead of me on this. Currently EmacSQL uses LONGTEXT for "object" columns in MySQL. I hadn't planned for types to be this fine-tuned on a per-column basis, just on a per-connection-class basis. Perhaps the schema s-exp syntax could be extended to support custom types? I'm thinking maybe if a string is present in the column specifier list in place of a type (so immediately after the column name), the content of the string is coped in literally for these special situations.

[:create-table :if :not :exists info
 ([(id integer :primary-key :auto_increment)
   (node object)
   (contents "varchar(100)" :unique)])]))

What do you think of that?

[wvxvw]

I may be overthinking it, but here's what I think: From what I see, you are caching the queries, and the "problem" with strings is that in Emacs they are mutable. This is definitely not a huge problem if you are careful, still, symbols are better for this. Another concern is that if this type repeats very often, an obvious optimization would be to try to use a variable instead of a literal string.

I also see that the expression parser implements something similar to define-syntax (a Scheme way to deal with macros), so why not try to extend the parser by reserving a symbol, or a convention, which, when matched, would call back to the user code with the AST node currently being generated and the original data the user put in there, something like: [(sql (unique-constraint contents (varchar 100))) ...] where (sql ...) form would call back to unique-constraint with arguments 'contents and '(varchar 100) and would try to incorporate the result back into the statement being build?

[tarsius]

(I don't claim to have understood everything that was said above.)

We decided a long time ago to stick to storing everything (except nil) as a readable string, regardless of whether that was a good early design decision or not.

I have taken over as the maintainer of this package because there are several popular packages (including some of mine), which use a SQLite backend. I have decided to keep support for PostgreSQL and MySQL, just in case someone (still) uses EmacSQL with such a back-end, but the focus is on SQLite and I do not plan to invest any time improving other back-ends.

I hope you are fine with me closing this issue.