[Bug] SQLSTATE[HY000]: General error: 1 near "SHOW": syntax error (SQL: SHOW INDEX FROM failed_jobs;)

[SiL3NC3]

Bug report

Illuminate \ Database\ QueryException
PHP 8.0.16
9.52.5
SQLSTATE[HY000]: General error: 1 near "SHOW": syntax error
SHOW INDEX FROM failed_jobs;

What I did

Fresh installation using sqlite database and devtools, clicked on "create new model"

What I expected to happen

view is properly coming up

What happened

error occurred

What I've already tried to fix it

yes, with a fresh migration

Backpack, Laravel, PHP, DB version

    "backpack/crud": "^5.5",
    "backpack/devtools": "^1.3",

When I run php artisan backpack:version the output is:

LARAVEL VERSION:

v9.52.5@e14d28c0f9403630d13f308bb43f3d3cb73d6d67

BACKPACK PACKAGE VERSIONS:

backpack/crud: 5.5.5
backpack/devtools: 1.3.5
backpack/generators: 3.3.16

Operating System and Server Setup

Windows 10, Server built-in VSCode

[SiL3NC3]

https://flareapp.io/share/RmrRVJdm#F119

[pxpm]

Hey @SiL3NC3

Did you setup backpack/crud before using devtools ? If not you should do php artisan backpack:install. That command will also call the migrate command that would migrate those tables.

In case you already did, what database engine and version are you using ?

Cheers

[SiL3NC3]

Sure, backpack was installed previously. Also after a fresh migration, still no luck... :|
I'm using SQLite3...

Is DevTools not working with SQLite?

[braedonsaunders]

SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'INDEX'.
https://flareapp.io/share/oPRNEZXP#F119

That's the error I'm getting when creating a model. It's not the DevTools sqlite database, it's the main application database. I'm using MS SQL Server. DevTools doesn't look to be compatible with this database, needs MySQL.

Adding this to InteractsWithApplication.php fixes that. Will go further into DevTools now.

case 'sqlsrv': $indexes = collect(DB::select("SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('$item')")) ->map(function ($item) { return Str::of($item->name)->match('/\((.*)\)/'); }) ->toArray(); break;

[pxpm]

Thanks @braedonsaunders for the explanation 🙏

Can you confirm you are using MS SQL too @SiL3NC3 ?

@braedonsaunders that's the only place devtools breaks with MS SQL ? If it is just a small fix on the way we get the db indexes we can think on a way to fix it (even if we don't "officially" provide support for).

But if that requires more changes across the whole package we may need to re-think adding support that way, and maybe in the future come up with some DB abstraction layer if we decide to add support to other rdbms.

Cheers 🙏

[braedonsaunders]

@pxpm I have created a new model, crud, factory, and seeders successfully.

However, I would expect that some changes might need to be made for the definitions of specific database column types when generating a model. Everything I have tried so far has been successful.

If I run into any specific issues with DevTools for MS SQL, I will include them and the solution in this thread.

[tabacitu]

Hey @braedonsaunders ,

Have you found any other issues with DevTools + MSSQL? We're eager to add MSSQL support to it, even if "unofficial". And you experience with this would be helpful.

Thanks!

[zaghadon]

Hey, you all (@SiL3NC3 @tabacitu @pxpm ), I also figured the default case can't work for SQLite. I mirrored the first case for sqlite, same logic as pssql.

            case 'sqlite':
                $indexes = collect(DB::select("SELECT sql FROM sqlite_master WHERE type = 'index' AND name = '$item'")) 
		->map(function ($item) { 
			return Str::of($item->sql)->match('/\((.*)\)/');  
		})
                ->toArray();

                break;

I added this block to the switch statement on the InteractsWithApplication.php, and so far I've successfully created a model, with migration, seeder and factory.

[mmoollllee]

Just bought DevTools aswell and can't use it as this error appears on trying to Create a Migration or Model in Devtools.
Any news on a fix?
I'm also using a sqlite database

[pxpm]

This issue feel through the cracks, sorry guys. 🙏

I've just tagged devtools 3.0.3 and 3.0.4 with support both for sqlite indexes and sqlsrv.

It should take a couple hours for our automatic deploy to pick the changes in the repos and make the versions available, so later today or tomorrow you should be able to do a composer update and get the fixes.

Cheers