0

I have found an answer here on how I can specify an AUTO_INCREMENT on a secondary column in a multiple column index in MySQL e.g.

CREATE TABLE foo (
  id INTEGER NOT NULL AUTO_INCREMENT,
  grp INTEGER NOT NULL ,
  name VARCHAR(64),
  PRIMARY KEY (id, grp)
) ENGINE = MyISAM;

How would I extend a slick.driver.MySQL.api.Table so that such a table is generated, if indeed it is at all possible? Difficulties I am having currently: (1) I don't know how to create a composite primary key in slick within the main create statement and (2) I don't know how to specify to use the MyISAM engine.

Update: Following @ulas' advice, I used slick.codegen to generate the slick data model from the (already created) SQL table. However, the data model cannot be used to then recreate the table - it generates two statements instead of one, and neither reference MyISAM. Regarding this, I have listed an issue in the slick github repos.

For now this leaves me with following @RickJames' advice, which I would rather do anyway since it doesn't rely on MyISAM, a non-default engine for the current version of MySQL.

So my question can now be collapsed to, how would I execute the following using slick?

BEGIN;
SELECT @id := IFNULL(MAX(id), -1) + 1 FROM foo WHERE grp = 1 FOR UPDATE;
INSERT INTO foo VALUES (@id, 1, 'bar');
COMMIT;

I have no idea how to do it using the 'higher-level' abstraction, so I tried following the Plain SQL Queries section of the slick manual. My attempt went something like:

val statements = DBIO.seq(
  sqlu" SELECT @id := IFNULL(MAX(id), -1) + 1 FROM foo WHERE grp = 1 FOR UPDATE",
  sqlu"INSERT INTO foo VALUES (@id, 1, 'bar')"
)

db.run(statements.transactionally)

But I got the error:

Exception in thread "main" slick.SlickException: Update statements should not return a ResultSet

Help appreciated.

Community
  • 1
  • 1
Mullefa
  • 1,237
  • 1
  • 15
  • 27
  • I'm new to Slick and its code generation feature helps me to understand how to create Table classes. It allows you to auto-generate corresponding Table classes for an existing database. You can learn codegen from the [documentation](http://slick.typesafe.com/doc/3.1.1/code-generation.html) or this [example](https://github.com/slick/slick-codegen-example). Good luck! – ulas Feb 08 '16 at 00:07
  • "I don't know how" is another way of saying "This 3rd party software (Slick) is getting in the way"? – Rick James Feb 08 '16 at 00:52
  • If you really need this rare feature of MyISAM, but are stuck with the otherwise better InnoDB, see the workaround in [my blog](http://mysql.rjweb.org/doc.php/myisam2innodb). – Rick James Feb 08 '16 at 00:53
  • @ulas Thanks, I haven't explored codegen yet - I'll look into it today. – Mullefa Feb 08 '16 at 09:21
  • @RickJames I guess the two are the same, but I'm not sure if I understand your point regarding this. I you saying I shouldn't use slick? Thanks to the link for your blog. – Mullefa Feb 08 '16 at 09:23
  • I know nothing about `slick` per se. I merely find that a lot of questions on forums come from 3rd party software not fully insulating the enduser (you) from knowing about MySQL. At first, the 3rd party software looks like a good thing to use; later one finds that one has to learn not just that, but also MySQL. – Rick James Feb 08 '16 at 18:41
  • @RickJames I am inclined to agree. Conceptually this layer of abstraction is attractive, but in practice I've found it more of a hinderance than a help. – Mullefa Feb 08 '16 at 23:27

0 Answers0