SPOD macros SQLite target PHP

[MatthijsKamstra]

I was testing SPOD macros with sqlite, php target and got this error: PHP Fatal error: Call to undefined function sqlite_open() in /[path]/bin/www/lib/sys/db/_Sqlite/SqliteConnection.class.php on line 6

I found an answer here: https://github.com/HaxeFoundation/haxe/issues/1641 My feedback on the solution above: there should be a if else statement in there unless the index.php check will set to 5.4.0

The solution described above did fix this PHP error, but introduced another:

The next error I got had something to do with auto-increment: uncaught exception: Error while executing INSERT INTO User (name,birthday,phoneNumber) VALUES ('Jason O-Neil', '1987-11-16 00:00:00', '(08) 9350 0000') (User.id may not be NULL)

import sys.db.Types;

class User extends sys.db.Object {
    public var id : SId;
    public var name : SString<32>;
    public var birthday : SDate;
    public var phoneNumber : SNull<SText>;
}

[Simn]

@jasononeil: Could you have a look at this? I believe you're the only person other than Nicolas who knows something about SPOD.

[MatthijsKamstra]

Don't know if this will help, but it seems to have something to do with this issue: https://github.com/HaxeFoundation/haxe/issues/1880

[jasononeil]

Haha, well, my name and birthday do appear in the posted bug... (I don't remember writing that example code, but oh well)

I'll try take a quick look today and check:

• sys.db.DbMacros generating incorrect sql (probably the second issue, I guess it's not auto-incrementing the ID)
• sys.db.Connection needing different implementations for different PHP version.

Both the issues Matthijs suggested look relevant so I'll have to take a look at those.

Does anyone have advice for testing multiple PHP versions? I use Ubuntu and just have whatever version is installed by default...

Jason

On 30/07/2013 7:58 PM, "Simon Krajewski" notifications@github.com wrote:

@jasononeil: Could you have a look at this? I believe you're the only person other than Nicolas who knows something about SPOD.

— Reply to this email directly or view it on GitHub.

[porfirioribeiro]

I was the guy that submited that patch, still waiting that someone look at it and include it, or some other code, to fix the problem...

[MatthijsKamstra]

I was kinda hoping it would be fixed in the next release, I have updated to Haxe 3.0.1 but this bug isn't fixed yet.

[ncannasse]

3.0.1 is a bugfix release, it's based on 3.0 but does not include changes that will be part of 3.1. Also, this issue being still opened it usually means it's not yet fixed.

[erikwatson]

Just ran into this bug myself, it's a real bummer :(

[waneck]

Already fixed. PHP already uses PDO and TableCreate is working as intended

[mattmccray]

To get this to work with PHP and SQLite I had to change the id from SId to SNull<SInt>, like this:

  // public var id : SId;
  public var id : SNull<SInt>;

[waneck]

Are you using the haxe nightlies? This should be fixed already. Also, how so that SNull<SInt> would help creating a class with id being recognized as an AUTO_INCREMENT field?

[mattmccray]

Oddly enough, using AUTO_INCREMENT and NOT NULL actually breaks auto-incrementing.

Sqlite will however fill in a null integer column with the ROWID, thereby approximating auto-incrementing support -- Which is why this works for me.

They recommend using INTEGER PRIMARY KEY for auto-incrementing in sqlite3. See http://www.sqlite.org/autoinc.html for more about all that.

I'm not sure how to make connection-specific SQL for an SId field though, so my example isn't a fix... More of a work-around that works for me. :smiley_cat:

-I tried with both the latest official and nightly builds of haxe.- Actually, I may not have testing it that well in the nightly version. Let me try again.

[waneck]

Are you using SQLite on Neko on Windows? It seems that Neko is shipping an ancient (8 years old) version of SQLite on Windows, so this may be part of the problem. I can't reproduce AUTO_INCREMENT and NOT_NULL bringing any problem on SQLite on Linux. Actually they are unit tested on PHP, Java and Neko.

[nadako]

Neko is shipping an ancient (8 years old) version of SQLite on Windows

That reminds me that I wanted to update it and make a pull request to Neko :wink:

[mattmccray]

OK -- After trying different things I see that the haxe nightly version of sys.db.TableCreate.create() does actually create the correct SQL, sweet! The reason I didn't think it was working was because the DB I was using was created using the official release (which still uses malformed/invalid INTEGER AUTO_INCREMENT NOT NULL).

So it's not a problem for me since I'm developing a new site. But it is somewhat problematic for existing databases.

[waneck]

Well, I guess this isn't a big deal since the database created earlier won't work anyway, so I can't see how anyone would have built a working structure on top of this.

[waneck]

That reminds me that I wanted to update it and make a pull request to Neko :wink:

Please do :)

[erikwatson]

I can't see how anyone would have built a working structure on top of this.

There very probably isn't anybody who's made something significant on top of this as it existed. However! I was trying to brute force my way around the problem a while back and managed to get away with it by setting the ID of the row manually by counting the rows in the table.

myUser.userID = User.manager.count($userID >= 0);

I don't think this is worth worrying about too much, because it's kind of crazy, but I felt I should point it out.

Anyway, thanks for sorting it out :)