|
Apologies for the shouting but this is important.
When answering a question please:
- Read the question carefully
- Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
- If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
- If the question is inappropriate then click the 'vote to remove message' button
Insults, slap-downs and sarcasm aren't welcome. Let's work to help developers, not make them feel stupid.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
For those new to message boards please try to follow a few simple rules when posting your question.- Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
- Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
- Keep the subject line brief, but descriptive. eg "File Serialization problem"
- Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
- Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
- Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
- If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
- Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
- Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
- Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
- If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
- No advertising or soliciting.
- We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
Is there possible to get an UNION in such a way that second part of UNION to be ordered ?
I have:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2 ORDER BY 3
The select from table1 will always get one row, and I need to order just records that come from table2, which could be more than one row ... it is possible to achieve that by SQL ?
P.S. I am using SQL Server.
modified 29-May-23 13:54pm.
|
|
|
|
|
_Flaviu wrote: it is possible to achieve that by SQL ?
No.
There are however solutions using derived languages such as TSQL or PL/SQL. You would need to specify which database you are using however for any consideration of that.
|
|
|
|
|
SQL Server (from Microsoft)
|
|
|
|
|
The ORDER BY clause applies to the entire results; you can't make it only apply to one part of a UNION.
However, you could add an additional column to indicate which part of the UNION the row belongs to, and add that to your ORDER BY statement:
SELECT column_name(s), 0 As QueryPart FROM table1
UNION ALL
SELECT column_name(s), 1 As QueryPart FROM table2
ORDER BY QueryPart, SomeOtherColumn
If you don't want the additional column to be included in your results, you can use a subquery or CTE to hide it:
WITH cteUnion As
(
SELECT column_names(s), 0 As QueryPart FROM table1
UNION ALL
SELECT column_name(s), 1 As QueryPart FROM table2
)
SELECT
column_name(s)
FROM
cteUnion
ORDER BY
QueryPart,
SomeOtherColumn
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
How about:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM
(SELECT column_name(s) FROM table2 ORDER BY 3)?
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
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
