Questions about tables for SQLITE

[Overkillz]

Hello dear community, I have a simple question about SQLITE

Well, I have been using the default system from race_toptime resource for several years. Nowadays, I cannot still using it due to I need to deal with an efficent way to store somethings.

However, I won't lost the data recorded inside there and here comes my first question.

Is there a way to check if a table exists without creating it ?

Obyously you can do the query, but I won't get the debugscript error, thats why Im asking about it. I have currently tested the function executeSQLQuery (I will test later with dbQuery, might this clear out this question)

And my 2nd question is: Is there a way to get all the table names from the database ?

Might something like this

'SELECT * FROM *'

Thanks for reading. best regards.

[Addlibs]

If you want to get a listing of existing tables, you need to query the hidden sqlite_master table: SELECT name FROM sqlite_master WHERE type='table'

If you want to know if a specific table exists: SELECT name FROM sqlite_master WHERE type='table' AND name=? LIMIT 1 sending the table name as a parameter. The no. of returned rows will be 1 if the table exists.

[Overkillz]

Thanks for your help, it helped me a lot the first part of your comment, but, Im curious with the 2nd part.

I didnt get at all the the LIMIT.

Currently the way Im following the check if a table exists is to loop the sqlite_master table.

Thanks and regards.

[IIYAMA]

8 hours ago, Overkillz said:

I didnt get at all the the LIMIT

Limit is used to collect a maximal amount of results. This also allows your database to stop searching when you got the result you need.

If you do not apply a limit. The database will keep searching (for example an account) even after finding it. Depending of the location of the data, you can reduce search time from 0% to 99%.

LIMIT 1

If search based on row numbers:

> 99% = target data is on the first row (the rest can be skipped)

> 0% = target data is on the last row

--------------

But in the example of MrTasty it is used  to make sure that the returned table length can either be 0 or 1. Just to keep things simple.

if #result == 1 then

 

 

Edited November 29, 2019 by IIYAMA

[Overkillz]

Ohh, I see.

Well, currently the way im following is that when I start the resource, do a loop to store all the names from the database into a table.

So, If I want to check if the table exists, instead doing a query I just do the search on the table (Im not even sure if this is more efficent)

Doing this, i can avoid of using limits ...etc

Thanks for your help @MrTasty & @IIYAMA