SQL

Flutter Plugin Used to Query Audios/songs infos From Device Storage

Platforms:

✔️ -> Supported
❌ -> Not Supported
 

See all platforms methods support

Installation:

Add the following code to your pubspec.yaml:

dependencies:
  on_audio_query: ^2.9.0

Request Permission:

Android:

To use this plugin add the following code to your AndroidManifest.xml

<manifest>
  
  <!-- Android 12 or below  -->
  <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
  <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>

  <!-- Android 13 or greater  -->
  <uses-permission android:name="android.permission.READ_MEDIA_IMAGES"/>
  <uses-permission android:name="android.permission.READ_MEDIA_VIDEO"/>
  <uses-permission android:name="android.permission.READ_MEDIA_AUDIO"/>

</manifest>

IOS:

To use this plugin add the following code to your Info.plist

<dict>

	<key>NSAppleMusicUsageDescription</key>
	<string>$(PROJECT_NAME) requires access to media library</string>

</dict>

Some Features:

• Optional and Built-in storage READ and WRITE permission request
• Get all audios/songs.
• Get all albums and album-specific audios.
• Get all artists and artist-specific audios.
• Get all playlists and playlists-specific audios.
• Get all genres and genres-specific audios.
• Get all query methods with specific keys [Search].
• Create/Delete/Rename playlists.
• Add/Remove/Move specific audios to playlists.
• Specific sort types for all query methods.

Overview:

All types of methods on this plugin:

Artwork Widget

  Widget someOtherName() async {
    return QueryArtworkWidget(
      id: <audioId>,
      type: ArtworkType.AUDIO,
    );
  }

See more: QueryArtworkWidget

Examples:

OnAudioQuery

final OnAudioQuery _audioQuery = OnAudioQuery();

Query methods:

• queryAudios();
• queryAlbums();
• queryArtists();
• queryPlaylists();
• queryGenres().

  someName() async {
    // Query Audios
    List<AudioModel> audios = await _audioQuery.queryAudios();

    // Query Albums
    List<AlbumModel> albums = await _audioQuery.queryAlbums();
  }

scanMedia

You'll use this method when updating a media from storage. This method will update the media 'state' and Android MediaStore will be able to know this 'state'.

  someName() async {
    OnAudioQuery _audioQuery = OnAudioQuery();
    File file = File('path');
    try {
      if (file.existsSync()) {
        file.deleteSync();
        _audioQuery.scanMedia(file.path); // Scan the media 'path'
      }
    } catch (e) {
      debugPrint('$e');
    }
  }

queryArtwork

  someName() async {
    // DEFAULT: ArtworkFormat.JPEG, 200 and false
    Uint8List something = await _audioQuery.queryArtwork(
        <audioId>,
        ArtworkType.AUDIO,
        ...,
      );
  }

Or you can use a basic and custom Widget. See example QueryArtworkWidget

Gif Examples:

Songs	Albums	Playlists	Artists

LICENSE:

• LICENSE

• Back to top

Use this package as a library

Depend on it

Run this command:

With Flutter:

 $ flutter pub add on_audio_query

This will add a line like this to your package's pubspec.yaml (and run an implicit flutter pub get):

dependencies:
  on_audio_query: ^2.9.0

Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.

Import it

Now in your Dart code, you can use:

import 'package:on_audio_query/on_audio_query.dart';

example/lib/main.dart

/*
=============
Author: Lucas Josino
Github: https://github.com/LucJosin
Website: https://www.lucasjosino.com/
=============
Plugin/Id: on_audio_query#0
Homepage: https://github.com/LucJosin/on_audio_query
Pub: https://pub.dev/packages/on_audio_query
License: https://github.com/LucJosin/on_audio_query/blob/main/on_audio_query/LICENSE
Copyright: © 2021, Lucas Josino. All rights reserved.
=============
*/

import 'package:flutter/material.dart';
import 'package:on_audio_query/on_audio_query.dart';

void main() {
  runApp(
    const MaterialApp(
      home: Songs(),
    ),
  );
}

class Songs extends StatefulWidget {
  const Songs({Key? key}) : super(key: key);

  @override
  _SongsState createState() => _SongsState();
}

class _SongsState extends State<Songs> {
  // Main method.
  final OnAudioQuery _audioQuery = OnAudioQuery();

  // Indicate if application has permission to the library.
  bool _hasPermission = false;

  @override
  void initState() {
    super.initState();
    // (Optinal) Set logging level. By default will be set to 'WARN'.
    //
    // Log will appear on:
    //  * XCode: Debug Console
    //  * VsCode: Debug Console
    //  * Android Studio: Debug and Logcat Console
    LogConfig logConfig = LogConfig(logType: LogType.DEBUG);
    _audioQuery.setLogConfig(logConfig);

    // Check and request for permission.
    checkAndRequestPermissions();
  }

  checkAndRequestPermissions({bool retry = false}) async {
    // The param 'retryRequest' is false, by default.
    _hasPermission = await _audioQuery.checkAndRequest(
      retryRequest: retry,
    );

    // Only call update the UI if application has all required permissions.
    _hasPermission ? setState(() {}) : null;
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("OnAudioQueryExample"),
        elevation: 2,
      ),
      body: Center(
        child: !_hasPermission
            ? noAccessToLibraryWidget()
            : FutureBuilder<List<SongModel>>(
                // Default values:
                future: _audioQuery.querySongs(
                  sortType: null,
                  orderType: OrderType.ASC_OR_SMALLER,
                  uriType: UriType.EXTERNAL,
                  ignoreCase: true,
                ),
                builder: (context, item) {
                  // Display error, if any.
                  if (item.hasError) {
                    return Text(item.error.toString());
                  }

                  // Waiting content.
                  if (item.data == null) {
                    return const CircularProgressIndicator();
                  }

                  // 'Library' is empty.
                  if (item.data!.isEmpty) return const Text("Nothing found!");

                  // You can use [item.data!] direct or you can create a:
                  // List<SongModel> songs = item.data!;
                  return ListView.builder(
                    itemCount: item.data!.length,
                    itemBuilder: (context, index) {
                      return ListTile(
                        title: Text(item.data![index].title),
                        subtitle: Text(item.data![index].artist ?? "No Artist"),
                        trailing: const Icon(Icons.arrow_forward_rounded),
                        // This Widget will query/load image.
                        // You can use/create your own widget/method using [queryArtwork].
                        leading: QueryArtworkWidget(
                          controller: _audioQuery,
                          id: item.data![index].id,
                          type: ArtworkType.AUDIO,
                        ),
                      );
                    },
                  );
                },
              ),
      ),
    );
  }

  Widget noAccessToLibraryWidget() {
    return Container(
      decoration: BoxDecoration(
        borderRadius: BorderRadius.circular(10),
        color: Colors.redAccent.withOpacity(0.5),
      ),
      padding: const EdgeInsets.all(20),
      child: Column(
        mainAxisSize: MainAxisSize.min,
        children: [
          const Text("Application doesn't have access to the library"),
          const SizedBox(height: 10),
          ElevatedButton(
            onPressed: () => checkAndRequestPermissions(retry: true),
            child: const Text("Allow"),
          ),
        ],
      ),
    );
  }
}

Download details:

Author: lucasjosino.com

Source: https://github.com/LucJosin/on_audio_query/tree/main/packages/on_audio_query

#flutter #dart #sql #video #mobile-apps 

SQL Explained: Common Sequelize Queries

You're comfortable writing queries in Sequelize ORM but when it comes to doing anything in raw SQL you have no clue where to begin.

"SQL feels like magic"

"Developers who can write clever SQL queries are ninja-rockstar gurus"

These are some of the quotes I hear when SQL comes up and I understand. Writing SQL queries can feel overwhelming. You spend valuable time trying to understand how a query works instead of writing the actual application and solving real-world problems.

So let's unveil that not-so-magic behind SQL. Here are 6 queries you know how to make in Sequelize. Rewritten and explained in SQL.

#sql 

Cartesian Product in SQL: How Should We Work with It?

Cartesian product in SQL is a term from the set theory of mathematics. However, we can also find this term in SQL database manuals. What does it mean, and how should we work with it? Let’s learn it.

A Cartesian product of two sets X and Y, denoted X × Y, is the set of all ordered pairs where x is in X and y is in Y.

In terms of SQL, the Cartesian product is a new table formed of two tables. If those tables have 3 and 4 lines respectively, the Cartesian product table will have 3×4 lines. Therefore, each row from the first table joins each row of the second table. You get the multiplication result of two sets making all possible ordered pairs of the original sets’ elements.

#sql 

Entity Framework Core & SQL Server with Blazor WebAssembly in .NET 5

Learn how to implement all CRUD operations using Entity Framework Core with a SQL Server database in a Blazor WebAssembly ASP.NET Core hosted web application in .NET 5.

💻 GitHub repo: https://github.com/patrickgod/BlazingSuperHeroes

00:00:00 Intro
00:01:35 Short Recap of Part 1
00:03:19 Install SQL Server
00:05:12 Install Entity Framework
00:06:42 Connection String
00:08:40 Implement the DataContext
00:12:45 Register the DataContext
00:16:13 First Migration
00:26:48 Data Seeding
00:34:00 Get Comics & Dependency Injection
00:41:45 Get all Super Heroes
00:44:32 Get a Single Super Hero
00:46:05 Create a new Super Hero with POST
00:53:28 Update a Super Hero with PUT
00:59:24 Remove a Super Hero with DELETE

#entityframework #dotnet #sql #blazor #webassembly

How to Build a Spring Boot CRUD Operations example with Microsoft SQL

In this tutorial, we’re gonna build a Spring Boot CRUD Operations example with Maven that use Spring Data JPA to interact with Microsoft SQL Server (MSSQL). You’ll know:

• Way to use SQL Server maven dependency in Spring Boot
• How to configure Spring Data, JPA, Hibernate to work with Database
• How to define Data Models and Repository interfaces
• Way to create Spring Rest Controller to process HTTP requests
• Way to use Spring Data JPA to interact with MSSQL Database

#spring #spring-boot #sql-server 

Create Secure Database Environment Using SQL Server Lockdown

Security is the main concern these days in every sector. Database Security is also a major worry for any customer. SQL Server is designed to secure all your data stored in the databases but sometimes we fail to apply the right set of configurations and leave a hole in our system. Hackers and unauthorized aliens might use these loopholes to penetrate your system. Your data can be compromised or maligned, your system resources can be deleted to create an outage to stop your business activities, your clients’ data might be exposed to competitors, etc.

#sql-server 

How to Write SQL Query using Frame Clause, CUME_DIST

This video is about Window Functions in SQL which is also referred to as Analytic Function in some of the RDBMS. SQL Window Functions covered in this video are FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, CUME_DIST and PERCENT_RANK. Also we cover how to use FRAME Clause while writing SQL Queries using window function. We also look at alternate way of writing SQL Query using window function.

We discuss in detail about the Frame clause and how RANGE is different from ROWS and how to use Unbounded Preceding and Unbounded Following while using certain window functions.

This video is focused on teaching how to write SQL Queries using different window functions or analytic functions. We go through the syntax of using first value, last value, frame clause, nth value. ntile, cume dist and percent rank as window function in SQL query.

We look at how to use WINDOW clause while writing SQL query using window functions. We talk about the OVER clause and how partition by clause can impact the result set.

Over clause is explained in detail in this video. Over clause is used in SQL when we need to use window function. Inside Over clause, we also use Partition By clause and also Order by clause.
Partition By clause is used to specify the column based on which different windows needs to be created.

The window function you learn in this video is applicable to any RDBMS since these functions are commonly used across most of the popular RDBMS such as Oracle, MySQL, PostgreSQL, Microsoft SQL Server etc.

Timestamp:
00:00 Intro
02:12 FIRST_VALUE
07:25 LAST_VALUE
10:25 Frame Clause
22:26 Alternate way of writing SQL Query using window function using Window clause
26:41 NTH_VALUE
31:47 NTILE
38:02 CUME_DIST
47:35 PERCENT_RANK

#sql 

NoSQL vs SQL Databases | MongoDB Explained in 10 Minutes

SQL, NoSQL, Relational, Non-Relational, Table, Collection, Row, Document, Column, Field. What does it all mean??

Stick around. In this short video, Senior Developer Advocate Jesse Hall explains it all and hopefully helps you decide which database to learn and use in your next project.

Timestamps:
00:00 - Intro
00:44 - Overview
01:24 - SQL: Structure
03:02 - SQL: Schema
03:24 - SQL: Scaling
04:14 - NoSQL: Structure
05:13 - JSON (JavaScript Object Notation)
06:33 - MongoDB: Schema
07:11 - Terminology
07:39 - MongoDB Ecosystem
07:45 - MongoDB Self-Hosted
08:03 - MongoDB Atlas
08:34 - MongoDB Compass
08:55 - MongoDB Realm
09:13 - MongoDB Atlas Search
09:34 - MongoDB Charts
09:52 - MongoDB Online Archive
10:11 - MongoDB Data Lake

#mongodb #sql #nosql #database

Is It Necessary That I Learn SQL?

If you've been thinking about becoming a Back-End Engineer, you've probably come across SQL in your research. Or, if you're a developer who's interested in getting more familiar with databases, SQL may be a valuable addition to your toolkit. Data Scientists, Back-End Developers, and programmers of all types rely on SQL to perform their daily responsibilities. So, the next question you might be asking yourself is: "Is It Necessary That I Learn SQL?"

#sql 

Following is the best book to learn SQL

So you want to learn SQL and you're looking for a good book? I think these two books by No Starch Press and O'Reilly are both excellent introductions to SQL. But which is the best? Watch the video to find out! 
#sql 

How to Use Spark Structured Query Language (SQL) Scalar

In this video, you learn how to use Spark Structured Query Language (SQL) scalar and aggregate functions.  Spark SQL is the most performant way to do data engineering on  Databricks and Spark and you want to leverage SQL functions as much as possible versus writing custom code.  I'll explain the concepts and demonstrate them with code in a Databricks notebook.

#bigdata #sql 

SQL Statements That Come in Handy While Building EF Core 5 Migrations

Entity Framework Core 5 is a great ORM and I love how efficient and concise it is. With the migrations mechanism enabled, you can generate the next migration based on changes applied to your model. This is so cool, but when it comes to other database objects, you are on your own. I mean – you can still use migrations, but you have to figure out a SQL statement yourself. Let’s take a look at some cool statements you can use with the SQL Server database.

#sql 

Using Filter by Parameters for SQL Server Stored Procedure

Hey

What am i doing wrong here? Its something ive done many times with more complicated statements but for some reason, this logic is working (at least for me).....

I am passing in 3 parameters and assigning each parameter with a default value of NULL
I am then selecting the fields from the table
Finally, i am using a WHERE clause to return the data based on the parameter values.

Here is my Stored Procedure

     ALTER PROC [dbo].[usp_tbl_PartListSelect] 
         @Type varchar(100) = NULL,
         @Size varchar(100) = NULL,
         @CategoryID int = NULL
     AS 
      SET NOCOUNT ON 
      SET XACT_ABORT ON  
        
      BEGIN TRAN
        
     SELECT        id, part_number, description, categoryID, size, type
     FROM            dbo.tbl_PartList
     WHERE        ([type] = @Type) OR @Type IS NULL AND ([size] = @Size OR @Size IS NULL) AND ([categoryID] = @CategoryID OR @CategoryID IS NULL)
     COMMIT

The problems i get.
1. If i call the Stored Procedure without any parameters, all rows are returned (this is correct)
2. If i call the Stored Procedure with only @Size, All rows at that size are returned (this is correct)
3. If i call the Stored Procedure with @Size AND @Type, i expect to get rows back only of that size & type but i get all rows of Type with size being ignored

Id like to call this SP 3 ways
1. no Parameter values to get all rows returned
2. by @Type & by @Size to get the size & type returned
3. by @Type & by @Size & by @CategoryID so that i get all filtered rows only returned.

Examples
--[usp_tbl_PartListSelect]
return all table rows

--[usp_tbl_PartListSelect] 'FRAME'
return all table rows where type = FRAME - any row with FRAME is returned

--[usp_tbl_PartListSelect] 'FRAME', '4in'
return all table rows where type = FRAME AND size = 4in - any row with FRAME and 4in is returned

--[usb_tbl_PartListSelect] NULL, '4in'
return all table rows where type = NULL AND size = 4in - any row with 4in returned

--[usp_tbl_PartListSelect] 'FRAME', '4in', 2
return all table rows where type = FRAME AND size = 4in AND CategoryID = 2 - any row with FRAME and 4in and 2 is returned

Is there a specific way to order the WHERE clause for conditional parameter values?

Thanks

#sql #sqlserver #database 

Create and Load The Project CSV Files into SQL Tables

In this video, you will learn how to create and load the project CSV files into SQL tables on open-source Apache Spark using Zeppelin Notebook.  The prior video, Lesson 9, showed you how to create the tables using Databricks.  The files are available with the notebook and slides at the link below.  This video lays the foundation for the ones that follow so make sure you watch it and create your own database.  

#sql #apache-spark #bigdata 

Tutorial to Data Dictionary in Oracle PL/SQL Part 48

 In this article you'll find out Tutorial Data Dictionary in Oracle PL/SQL Part 48

FREE URDU/HINDI Lecture 48 Data Dictionary in Oracle PL/SQL
.Data Dictionary

#sql