Follow Marin Events

SQL scripts OS mostly assets & folders

SELECT
inventoryfolders.folderName, inventoryitems.inventoryName,
assets.name AS Asset, -- fsassets.name AS FsName, always the same a assests name
useraccounts.FirstName , fsassets.create_time, fsassets.Type
, assets.description AS "Assets Desc" , inventoryitems.inventoryDescription
-- fsassets.description AS "fsassets desc" always = assets
FROM assets , fsassets ,
inventoryitems LEFT JOIN inventoryfolders ON inventoryitems.parentFolderID = inventoryfolders.folderID
LEFT JOIN useraccounts ON useraccounts.PrincipalID = inventoryfolders.agentID
WHERE
-- fsassets.create_time > 1645999999
-- fsassets.access_time > 1645999999 OR
-- AND
assets.assetType > -999 -- 10=script 7=notecard 20=anim 6=object 0=textur 1=sound 3=LM 5=clothing 13=body/shape 2=CC 21-gest
AND assets.id = fsassets.id
AND assets.id = inventoryitems.assetID
ORDER BY useraccounts.PrincipalID , inventoryfolders.folderName, inventoryitems.inventoryName, assets.name
-- fsassets.Type DESC, -- fsassets.create_time DESC
/*assets.name LIKE "%Frank%" AND inventoryfolders.folderName IS NULL*/

=========================================================

SELECT inventoryfolders.folderName, inventoryitems.inventoryName,
assets.name AS Asset, -- fsassets.name AS FsName,
useraccounts.FirstName , fsassets.create_time, fsassets.Type
, assets.description AS "Assets Desc" , inventoryitems.inventoryDescription
-- fsassets.description AS "fsassets desc" always = assets
FROM assets , fsassets , useraccounts ,
inventoryitems LEFT JOIN inventoryfolders ON inventoryitems.parentFolderID = inventoryfolders.folderID
WHERE
fsassets.create_time > 1645999999
-- fsassets.access_time > 1645999999 OR
AND inventoryfolders.agentID = useraccounts.PrincipalID
AND assets.assetType > -999 -- 10=script 7=notecard 20=anim 6=object 0=textur 1=sound 3=LM 5=clothing 13=body/shape 2=CC 21-gest
AND assets.id = fsassets.id
AND assets.id = inventoryitems.assetID
ORDER BY useraccounts.PrincipalID , inventoryfolders.folderName, inventoryitems.inventoryName, assets.name
-- fsassets.Type DESC, -- fsassets.create_time DESC
/*assets.name LIKE "%Frank%" AND inventoryfolders.folderName IS NULL*/

=========================================================

SELECT -- DISTINCT
parent1.folderName AS Parent, child1.type , child1.folderName AS Child,
child2.folderName AS GrandChild, child3.folderName AS GGChild , child4.folderName AS GGGChild,
child5.folderName AS GGGGChild, child6.folderName AS GGGGGChild , child7.folderName AS GGGGGGChild,
useraccounts.FirstName
FROM
useraccounts ,
inventoryfolders parent1
LEFT JOIN inventoryfolders child1 ON child1.parentFolderID = parent1.folderID
LEFT JOIN inventoryfolders child2 ON child2.parentFolderID = child1.folderID
LEFT JOIN inventoryfolders child3 ON child3.parentFolderID = child2.folderID
LEFT JOIN inventoryfolders child4 ON child4.parentFolderID = child3.folderID
LEFT JOIN inventoryfolders child5 ON child5.parentFolderID = child4.folderID
LEFT JOIN inventoryfolders child6 ON child6.parentFolderID = child5.folderID
LEFT JOIN inventoryfolders child7 ON child7.parentFolderID = child6.folderID
WHERE
parent1.agentID = useraccounts.PrincipalID
AND parent1.folderName = "My Inventory"

-- AND OUTER JOIN child3.parentFolderID = child2.folderID
-- AND OUTER JOIN child4.parentFolderID = child3.folderID
ORDER BY child1.agentID , parent1.folderName, child1.folderName, child2.folderName
-- and child.type > 0

-- AND `parentFolderID` != '00000000-0000-0000-0000-000000000000';

======================================================

SELECT -- on fsassets and not on assets. Assuming create time starts around 18900+331 days ago ( 1970 ? )
-- "Days since oldest" of those not in assets
fsassets.name, ROUND( (fsassets.create_time / 86400) - 18900) AS "Days since oldest",
fsassets.type, fsassets.description AS "Desc", asset_flags
FROM fsassets
WHERE NOT EXISTS ( SELECT assets.name FROM assets WHERE assets.id = fsassets.id )
ORDER BY fsassets.create_time DESC
/*
assets.name AS Asset,
assets.create_time, assets.assetType
, assets.description AS "Assets Desc"
fsassets.create_time > 1645999999 -- fsassets.name AS FsName, always the same a assests name
-- fsassets.access_time > 1645999999 OR
AND assets.assetType > -999 -- 10=script 7=notecard 20=anim 6=object 0=textur 1=sound 3=LM 5=clothing 13=body/shape 2=CC 21-gest
AND
AND assets.id = inventoryitems.assetID
ORDER BY useraccounts.PrincipalID , inventoryfolders.folderName, inventoryitems.inventoryName, assets.name
*/

==========================================================

SELECT COUNT(*), fs1.id, fs1.name, ROUND( (fs1.create_time / 86400) - 18900) AS "Days since oldest",
fs1.type, fs1.description AS "Desc", fs1.asset_flags
FROM fsassets fs1 , fsassets fs2
WHERE fs1.id = fs2.id
GROUP BY fs1.id
ORDER BY 1 DESC
LIMIT 10

=========================================================

SELECT DISTINCT avatars.name, inventoryitems.inventoryName , inventoryitems.invType, useraccounts.FirstName
FROM avatars, useraccounts , inventoryitems
WHERE avatars.PrincipalID = useraccounts.PrincipalID
AND
(
inventoryitems.inventoryID = SUBSTRING( avatars.Value, 1 ,36)
OR inventoryitems.inventoryID = SUBSTRING( avatars.Value, 38 ,73)
OR inventoryitems.inventoryID = SUBSTRING( avatars.Value, 75 ,111)
)
ORDER BY useraccounts.FirstName , inventoryitems.inventoryName
/*
-- inventoryitems inv1, inv1.invType, inv1.inventoryName , -- , fsassets.name
*/

======================================================

SELECT parent.foldername AS "ParentFolder", child.foldername AS "ChildFolder",
parent.type AS "ParentType", child.type AS "ChildType" ,
inventoryitems.inventoryName, inventoryitems.invType ,
useraccounts.FirstName, useraccounts.LastName
FROM -- inventoryfolders parent, -- inventoryfolders child ,
inventoryitems LEFT JOIN inventoryfolders child ON inventoryitems.parentFolderID = child.folderID ,
inventoryfolders parent LEFT JOIN useraccounts ON useraccounts.PrincipalID = parent.agentID
WHERE -- parent.foldername = 'My Outfits'
-- AND child.foldername LIKE "Beach%"
-- AND
child.type NOT IN ( -1, 47) -- = -1
-- AND inventoryitems.inventoryName LIKE "Beach%"
AND child.parentfolderid = parent.folderid
AND inventoryitems.parentFolderID = child.folderID
ORDER BY useraccounts.FirstName, useraccounts.LastName, ChildFolder, inventoryitems.inventoryName
;
-- select parentfolderid from inventoryfolders where foldername='My Outfits' and type = -1;
/*
CREATE TABLE useraccounts (
PrincipalID char(36) NOT NULL PRIMARY KEY,
ScopeID char(36) NOT NULL,
FirstName varchar(64) NOT NULL,
LastName varchar(64) NOT NULL,
*/

=========================================================

INVENTORY FOLDERS parent child NOT EXIST

SELECT child.folderName, child.type , child.agentID, child.parentFolderID, useraccounts.FirstName -- , parent.folderName, child.type
FROM inventoryfolders child , useraccounts -- , inventoryfolders parent
WHERE NOT EXISTS ( SELECT * FROM inventoryfolders parent
WHERE child.parentFolderID = parent.folderID
AND child.agentID = parent.agentID
)
AND child.agentID = useraccounts.PrincipalID
-- child.parentFolderID = parent.folderID
-- AND child.agentID = parent.agentID
-- ORDER BY parent.folderName, child.folderName
-- and child.type > 0

-- AND `parentFolderID` != '00000000-0000-0000-0000-000000000000';

============================================================

/* // need to be ROOT to access both robust DB and opensim DB */
SELECT estate_settings.EstateName AS "Estate Name" , regions.regionName AS "Region Name",
useraccounts.FirstName, useraccounts.LastName
/* , regionsettings.water_height AS "water height" */
FROM opensim.estate_map, opensim.estate_settings,
robust.regions , robust.useraccounts /* , opensim.regionsettings */
WHERE estate_map.RegionID = regions.uuid
AND estate_map.EstateID = estate_settings.EstateID
AND estate_settings.EstateOwner = useraccounts.PrincipalID
/* AND estate_map.RegionID = regionUUID.regionsettings */

==================================================

AVATARS current outfit

=======================================================

select a1.name, a1.description AS "Assets Desc" -- , a2.description AS "fsassets desc" , a3.inventoryDescription , a3.inventoryID
from assets a1 -- , fsassets a2 , inventoryitems a3
WHERE a1.id = "edd48da8-9674-4e11-bbe5-1b5958c73150" -- a1.name like "NPC%"
-- and a1.id = a2.id
-- and a1.id = a3.assetID
;

/*

-- WARN [GETASSET]: asset with empty data: edd48da8-9674-4e11-bbe5-1b5958c73150 type 0
SELECT name , description
FROM robust.assets
-- prims
-- primitems
WHERE id = "edd48da8-9674-4e11-bbe5-1b5958c73150"

*/

=======================================================

fsassets duplicate NO

===========================================================

information_schema

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.global_status
WHERE VARIABLE_NAME IN ('COM_SELECT', 'COM_INSERT', 'COM_UPDATE', 'COM_DELETE'
, 'INNODB_ROWS_DELETED', 'INNODB_ROWS_INSERTED', 'INNODB_ROWS_READ', 'INNODB_ROWS_UPDATED'
);

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

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.session_status
/*
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.global_status
*/
WHERE VARIABLE_NAME IN ('COM_SELECT', 'COM_INSERT', 'COM_UPDATE', 'COM_DELETE'
, 'INNODB_ROWS_DELETED', 'INNODB_ROWS_INSERTED', 'INNODB_ROWS_READ', 'INNODB_ROWS_UPDATED'
);

=================================================

SELECT EVENT_NAME, SOURCE, TIMER_WAIT, CURRENT_SCHEMA, SQL_TEXT
FROM performance_schema.events_statements_current

SELECT EVENT_NAME, ROWS_AFFECTED, ROWS_SENT, ROWS_EXAMINED, TIMER_WAIT, CURRENT_SCHEMA, SQL_TEXT
FROM performance_schema.events_statements_current
ORDER BY TIMER_WAIT DESC ;

===================================================

SQL scripts OS mostly assets & folders

Questions or problems regarding this web site should be directed to Info@marincounty.info Last modified: Monday December 30, 2024.

Questions or problems regarding this web site should be directed to Info@marincounty.info
Last modified: Monday December 30, 2024.