I've been doing a lot of work with SQL APIs lately. I am thinking that they API abstractions we have aren't the right level for a core, fundamental system API. Too many assumptions are built into the APIs which makes it unsuitable for building higher level frameworks for connection pooling, ORM, etc.

Here are my proposed breaking changes:

• Get rid of SqlService, replace its basic functionality with new class SqlConn
• SqlConn would map to low-level JDBC connection directly, no actor local tricks, reference counting etc (leave that to higher levels)
• Get rid of Dialect, replace with SqlMeta which would look more like JDBC's DatabaseMetaData class, this class would be reflected from DB itself instead of some custom Fantom class

This is a pretty significant breaking change to an API that I suspect is pretty heavily used. So I'd really like to get comments before embarking on such a project.

Looks good.

Give the pod a new name(like isql or nsql) for easy moving to the new version.

Promoted to ticket #1318 and assigned to brian

Any more comments? This is a pretty big design change...

Is there a sample explaining the usage after the change? Will the higher level API be part of the core? What is the main motivator (particular problem)?

This change seems logical to me. Could we do it in two phases, so that existing users have a full release cycle to update their code?

The main motivation for switching to a lower level API is that as part of the core API, we need to provide libraries and frameworks flexibility to manage connections rather than dictate everything. Plus the connection counting stuff was done before actors and I am not sure it fits well - I think connections should just be a low level mutable classes. Maybe eventually we should have a higher level API with connection pooling, etc - but maybe not immediately until we get the low level stuff worked out.

I am not sure the original author's intention with dialect, but much of that information is available in JDBC via database meta-data which seems like a much more solid path - reflect it from driver versus hardcode it in a bunch of Fantom classes. Plus there is a bunch of other meta-data I want to expose such as driver version, etc.

And yes my plan is to leave the old API intact for the next build as deprecated (I am hoping).

Give the pod a new name(like isql or nsql) for easy moving to the new version.

Why? Can't we make it sql v 2.0

Note I have pushed the first of these changes to hg.

Instead of trying to load JDBC drivers by dialect, the etc/sql/config.props file just specifies a list of Java classnames to try and load on startup. Simpler and more "fool proof".

Can't we just use SPI and JDBC4 to load the appropriate driver? We shouldn't need to manually load the driver at all. The JDBC code should find the correct driver for you.

Can't we just use SPI and JDBC4 to load the appropriate driver? We shouldn't need to manually load the driver at all. The JDBC code should find the correct driver for you.

Isn't that when you just call DriverManager.getConnection and it automatically works? I haven't actually ever seen it work myself. Is there something else to it that we have to do?

Yeah. That should do it. It requires a JDBC4 driver but that should cover just about all the major drivers these days.

The MySql driver I am using doesn't support that, and I was working with another guy this week whose driver didn't auto-load. So I don't think the world is quite there yet, which means we still need the old Class.forName trick.

Why? Can't we make it sql v 2.0

A depends C 1.0 and B depends C 2.0. In this case, A and B can not work together.

@go4

Why can't they work together? a should still be able to run on c 2.0 since the functions will be only deprecated not totally removed, so it should still run correctly... Right?

Ticket resolved in 1.0.57

These changes have been made for build 1.0.57. Summary of changes:

• SqlConn provides same function as SqlService, but as a direct low-level JDBC connection without any special actor local/reference counting
• SqlMeta provides API to reflect meta-data including version info, limits, and tables
• SqlService is deprecated
• Dialect and its subclasses are deprecated
• JDBC driver preloading happens using simple list of Java classnames in "etc/sql/config.props"
• Col.of is deprecated for Col.type

Although key aspects of the old API have been deprecated, everything will continue to work exactly like it did before. There are no breaking changes in this build, in fact the unit tests continue to use old API to verify old behavior. However, I will be removing all the deprecated APIs from hg shortly after the build. So code should be upgraded if working off hg tip or before build 1.0.58 (probably not until late Feb).

Changing your code is pretty simple:

• verify JDBC driver preload changes
• switch from SqlService to SqlConn, almost everything works just the same
• methods like tableExists, tables, tableRow are now on SqlMeta

I hope the Sql API support:

1. JDBC Driver can be specified in code, like SqlConn.open(url, user, password, driverClassName), not need to put in the etc/sql/config.props file, since the etc/ directory is shared by many program, but the JDBC connection is bound to an app.
2. Row.col(name) should support case inensitive. we would like row->name more than row->NAME.
3. is there possible of the groovy GString support in Sql API? eg. sql.execute("select * from student where name = ${name}"
4. more api support such as batch insert?

#2. IIRC this is platform dependent. Since db rely on the FS of the OS on Linux this won't work but on Windows it should.

#3 If I'm not mistaken 3 is already possible.

db := SqlConn.open("jdbc:mysql://localhost:3306/fantest", "fantest", "fantest")
db.sql("select * from student where name = ${name}").execute

This should work. I haven't tested it. Statement is merely just a string. I think using parametrized statement is better since in theory they should do all the escaping of illegal characters for you.

#2 Some db do case sensitive for tablename, column, but most db not, eg, oracle, sql server, mysql etc.

#3. not the same. Groovy style using PreparedStatement which is instead of select * from student where name = ? which is much effective.

I know MySQL had some issues with small letters on column/table names in Linux it didn't have in Windows. You said e.g. which means for example. And if I'm not mistaken that example works.

Problem with select * from student where name = ? is that anything more complicated gets quite bizzare. select * from student where name = ? and age = ? where grade > ?. Now what number does it start? 0,1? Is 1 name or age, etc. And these are only the easy examples. what happens when you have several joins and seven ? which is ? #4? Which one is #5?

JDBC Driver can be specified in code, like SqlConn.open(url, user, password, driverClassName), not need to put in the etc/sql/config.props file, since the etc/ directory is shared by many program, but the JDBC connection is bound to an app.

The problem is that the Sql API doesn't or shouldn't care about Java's JDBC implementation. Can't you just use Java FFI to force your JDBC driver to load using Class.forName?

Row.col(name) should support case inensitive. we would like row->name more than row->NAME.

How does JDBC deal with this issue? Can you lookup columns case insensitive?

s there possible of the groovy GString support in Sql API? eg. sql.execute("select * from student where name = ${name}"

Well at one level you can use normal Fantom interpolation. But I think you are referring more to prepared statements. You can prepare a statement with place holders already:

addBook := db.sql("insert into Books (title, author, year)
                  values (@title, @author, @year)").prepare

Also see docs. Is that not what you are talking about?

more api support such as batch insert?

Do you have a specific idea about this might look in the API? I don't typically use anything but basic JDBC features, so will need some community design input.

The problem is that the Sql API doesn't or shouldn't care about Java's JDBC implementation. Can't you just use Java FFI to force your JDBC driver to load using Class.forName?

Yes. It is not a problem using FFI

How does JDBC deal with this issue? Can you lookup columns case insensitive?

yes, most driver ignore column case, eg, rs.getString("name") will be same for rs.getString("NAME").

Okay, anyone agree or disagree with making the sql::Row lookups for column be case insensitive? If not lets at least fix that.

Any idea how that will work on .Net side? And can that be verified on Linux/MacOS...

Today, I found the H2 database auto convert field name to uppercase. The sqlite jdbc driver can't support auto generate keys. And the postgreSql driver also have some bugs on auto generate keys.

Actually, the sql API can only works with MySQL.

For H2 database, I voted for case insensitive.

Let's create a new topic and try and track all the specific issues. Right now we have a clear problem with case sensitivity.

The sqlite jdbc driver can't support auto generate keys.

This shouldn't break anything, if it does can you provide some specifics (in a new topic)

I have fixed Row.col and Row.trap to be case insensitive lookups.