|
I have tried:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT b.id, b.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416)
But it doesn't like it:
SQL
Executing SQL directly; no cursor.
Incorrect syntax near ')'.
Statement(s) could not be prepared.
Did I understand correctly your thought?
|
|
|
|
|
I'm sorry, try this slight modification:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Yes, that's worked:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S
but soon as I put ORDER BY:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416 ORDER BY 2) AS S
Error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
|
|
|
|
|
Whoops! I guess that's why nobody else recommended that. I forgot that ORDER BY is not allowed in derived tables.
I think the temporary table is probably your best option.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I know this is not going to be a popular opinion, but ...
1) Create a #TEMP table
2) Insert into the table with part 1 of the query
3) Insert into the table with part 2 (sorted)
Return the dataset from the #TEMP table
|
|
|
|
|
That is probably how I would do it. Although temp tables are also database specific the general idea and the layout of the solution works across different types of databases.
|
|
|
|
|
Is there any guarantee that the temp table will be returned in the insertion order? If not, you'd have to add an identity column and return the temp table sorted on that.
Keep Calm and Carry On
|
|
|
|
|
k5054 wrote: will be returned in the insertion order?
Unlikely.
And would not work anyways. Because you would do first one query then the second.
So of course the temp table would need to provide a way to order it.
|
|
|
|
|
I'm trying to set up a repository project in a WPF solution. I'm following this[^]
First I do
cd MyApp.Repository
Add-Migration InitialCreate -Project MyApp.Repository
So far, so good. The Migrations folder appears in he repository project and everything seems ok.
Then I do
update-database
and get back
No DbContext was found in assembly 'MyApp.Crypto'. Ensure that you're using the correct assembly and that the type is neither abstract nor generic.
The DBContext is in MyAppRepository. MyApp.Crypto is another C# class library in the solution. I have no clue wy EF is lookin in there.
What am I doing wroing????
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
|
I'm wokring on a mobile messenger app using Flutter. I'd write backend code in Node.js.
My app will have a list of users and each user can send request to other users to get paired with them. Each user can reject/accept other users' requests. If two users get paired, they can send message to each other or make a voice/video call. All messages transfered between users will be registered on the database for further analysis or detecting any criminal materials or misuses. I'd have about 2000 users, meaning the maximum online users are about 2000 people. The overal nature of the backend model is relational.
I need to choose a proper database for this app. My options are MongoDb and Sqlite. Which one is good for this project? Can Sqlite handle this project with that amount of users?
|
|
|
|
|
You can size it like the following
2000 users
100 messages a day
Total = 200,000 message a day
Messages per year = 400 * 200,000 = 80,000,000
Why 400? Because precision is not needed. All of the numbers are just guesses to give a goal.
Then further what is the average message size? 100 bytes or 1,000,000. Obviously the second is going to be because they are transferring files and not just messages. So do you keep the files also.
One thing I don't see in your post is growth rate. Are you starting with 200 users and only expect them to grow to 2000? Or the market that you are targeting, at best, only has about 2000 users?
You also do not document retention rates. How long do you keep the messages? 1 year? 7 years (often sufficient for most legal/business reasons)? 20 years?
So what database do you need? Any really. Nothing you posted suggests a need for 'speed'. All you are doing is keeping it for future analysis. You could even just write it to files.
You also do not mention personal security. Presumably, excluding the analysis, the messages are not subject to random poking around by operations personnel so how do you secure that?
|
|
|
|
|
My main problem is concurrent database read/write capability. I'll delete "seen" messages every month. I think Sqlite is not good for this project. What about SQL Server? Or Mongodb?
|
|
|
|
|
|
Alex Dunlop wrote: My main problem is concurrent database read/write capability
Nothing in what you posted suggests that is a concern.
Each new message is just a new record regardless of how you store it. Even if you allow edits you should handle that as a new message rather than replacing the previous one.
Alex Dunlop wrote: What about SQL Server? Or Mongodb?
Not a matter of whether those would work but rather if they are overkill.
When I looked up the transactions per second speed for Sqlite I did not see anything that concerned me for the sizing I previously posted.
|
|
|
|
|
Sqlite is meant as a single-user database. MongoDb is for documents.
Alex Dunlop wrote: users will be registered on the database for further analysis or detecting any criminal materials or misuses.
The answer is a clear no.
You might want to consult a lawyer, not a software-dev.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Eddy Vluggen wrote: You might want to consult a lawyer, not a software-dev.
At least in the US and presuming a single company then users can have no expectation of privacy for company resources. Notifications to company employees are nothing more than a courtesy. Although perhaps also that they should keep the personal stuff off the company sites.
|
|
|
|
|
Assuming the database will be controlled by some hosted system that is only accessed by your backend system, then SQLite would be a resonable choice. See Implementation Limits For SQLite[^] for guidance on sizing.
|
|
|
|
|
If the SQLite database is accessed only by the backend, could it handle 500 read/write requests which are sent to the beckend at the same time?
|
|
|
|
|
That is impossible to answer as it depends on many factors. If you need specific performance levels then you would need to do some testing of your own.
|
|
|
|
|
Code4Ever wrote: could it handle 500 read/write requests
No sorry that is not a valid question based on your original post.
Exactly how are 2000 users supposed to generate that many messages at one time?
|
|
|
|
|
Through the this content
SQL Server - BLOB Import and Export[^]
and
How to store and fetch binary data into a file stream column[^]
, I was able to retrieve the blob from the FileImage column.
But it doesn't work for some records. In other words, it opens with an error for this type of file that has been fetched.
Table structure:
CREATE TABLE [dbo].[Attches](
[AttchID] [int] IDENTITY(1,1) NOT NULL,
[FileType] [varchar](30) NOT NULL,
[FileSize] [int] NULL,
[FileImage] [varbinary](max) NULL
)
I tried to extract a damaged document or pdf file in raw form and opened it with Hexinator software.
All the file that is corrupted, first its hex starts with the path of the file. In other words, it is manipulated.
Is there a way to retrieve data safely?
Best regards
|
|
|
|
|
lookilok wrote: But it doesn't work for some records
As stated this is not a database problem.
Potential causes. Could be others.
- You are not reading the entire contents completely. It works for some because the process you are using accidently reads if fully to the end or the lost parts are not actually needed.
- You succeed for some because you make assumptions about what it is or how it should be used. So nothing at all to do with the database. For example you assume that every one is a pdf when if fact some are word docs. There should be information in the database that tells you what the type is.
- The data is in fact corrupted in the database. So a data issue and absolutely no way you can fix it in code.
|
|
|
|
|
If we assume that this record is encrypted or compressed, Is there a way to show how the records with this problem were encrypted?
What encryption or compress method?
modified 6-Apr-23 3:57am.
|
|
|
|
|
Some encryptions and some compressions might put a signature at the beginning of the file.
If it exists it is not guaranteed to be unique but likely is sufficient to guess about it.
You would need to research each possibility. For example...
"Conventionally the first thing in a ZIP file is a ZIP entry, which can be identified easily by its local file header signature. However, this is not necessarily the case, as this is not required by the ZIP specification - most notably, a self-extracting archive will begin with an executable file header."
ZIP (file format) - Wikipedia[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
