sqlite commands verification

[naxo]

Hi all

I have several questions for sqlite experts about checking bd data for sqlite:

1) Which sqlite command should I use to find out how many columns of data does have one table inside a db?

2) Which sqlite command could I use to find out how many tables could have inside a database?

3) How many rows does correspond (since I read somewhere in internet...) his iimit data size of 140 TB (theoretically said)?

4) Can two separated tables but both included in a same and single database file be intercommunicated with sqlite commands? I mean, could 2 tables parse data or get data from them to another third virtual table by using join command?

5) As for the first and second questions, which command could I use to set the size parameters about db, row and column sizes? Pragma could be the correct command?

6) How can I avoid fragmentation data when many rows or data are being deleted in process to get fast loading data?

[BioHazard]

My grasp of SQLite is appalling, so can't offer much in the way of 'direct' help. However, there's a ton of SQLite APZ examples (40+ of them, spread over 3 pages) here:

https://www.4shared.com/folder/zmYD6eBh/001/SQLITE.html

https://www.4shared.com/folder/zmYD6eBh/002/SQLITE.html

https://www.4shared.com/folder/zmYD6eBh/003/SQLITE.html

Among these are some examples from TiggTV tutorials (always very good). And one which deals specifically with tables in SQLite. It's an awful lot to dig thru, I know. But there is an option there, to download all 40+ examples, as a single (16.4mb) zipfile. And I'd imagine that you'll find at least one or two of your answers, buried in there somewhere.

Happy treasure hunting!

PS.
And if u don't already have it, there's also this here, old SQLite_Sample.apz from 2005 (which I think colc actually re-posted a few weeks back) which could 'possibly??' be of some help:

[BioHazard]

Edit,

Okay, found the answer to your 1st question: how to return number of columns of table data in db.
And just in case someone's tempted to yell RTFM at us, yep, comes straight outta the manual, LOL.

You need the SQLite.QueryToTable command (which will return a table containing the query results - among which is the number of columns).

Example:

-- create a database in memory
dbname = ":memory:";
db = SQLite.Open(dbname);

 -- create a table and fill it with data
 SQLite.Query(db,"create table Users(userid integer primary key, LastName text, FirstName text, Age integer)");
 SQLite.Query(db,"insert into Users values(1,'Sellers','Ted',48)");
 SQLite.Query(db,"insert into Users values(2,'Blow','Joe',64)");
 
 -- perform a query and store all of the results in a table called tbUsers
 tbUsers = SQLite.QueryToTable(db,"SELECT * FROM Users");
    
-- display number of columns in the table (ie. myTable.Columns)
Dialog.Message("Notice", "Number of columns\r\nin table: "..tbUsers.Columns, MB_OK, MB_ICONNONE, MB_DEFBUTTON1);

The rest of it's all a bit beyond my 'SQLite paygrade' at this stage.
So, I'll leave you to dig thru that mountain of APZ, my man! LOL, glad it's not my headache!

[naxo]

Thanks bio I will see whats up with those sqlite exameples some are unknow for me

[BioHazard]

This SQLite stuff is foreign ground to me, so I'm learning as I go. (And apologies, if the stuff linked above was too generalized). Was just looking over some of your other threads - from which I'm now gathering that you're already more familiar with this database stuff, than I'd 'somewhat arrogantly' presupposed. So, sorry about that.

BUT - in relation to your 4th question about communicating between tables via the JOIN clause (command), I'm personally, finding the following SQLite reference-material quite helpful in understanding the wider concepts behind using the SQLite plugin. (And again, let me stress that this is coming from my own limited understanding of how this stuff actually works. So apologies in advance, if you already know this material):

At http://www.sqlitetutorial.net you'll find info relating specifically to querying/combining data from multiple tables via the JOIN clause. Read particularly, the stuff under the following subheadings:

• SQLite Inner Join – query data from multiple tables.
• SQLite Left Join – combine data from multiple tables.
• SQLite Cross Join – produce a cartesian product of result sets of the tables involved in the join.
• SQLite Self Join – joins rows with other rows within the same table.
• SQLite Full Outer Join – emulate the full outer join, using left join and union clauses.
• SQLite Union – combine result sets of multiple queries into a single result set.

Lastly, in regard to your 5th question about manipulating the database via Pragma statements - is actually too bad Rizla's no longer with us because he did a ton of work on database stuff - but he recommended NOT to modify an AMS database via an SQLite browser See reasoning behind his comment: here.

Also, (if u haven't already seen it) you might actually be interested in taking a look at his Database LUA Actions v1.0.0.1 script (which for simple stuff CAN sometimes be a better option than the SQLite plugin). I can't find the original forum-link for it (strangely enough, all traces of it seem to have been removed) but I've zipped & attached a copy below (which also contains a dupe of the original thread).

Hope this was a little more helpful.

[BioHazard]

RizlaUK actually made that Database LUA Actions script into a plugin. Would've mentioned it earlier but didn't realize I even had a copy. LOL, is about time I re-organized my HDD. You can probably still find the original link for it on the forum, somewhere - but to save from some searching, a copy is attached below:

[naxo]

thanks dude!