|
|
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[^]
|
|
|
|
|
As long as you are reading and writing the files in binary mode there should not be any problems.
|
|
|
|
|
This worked fine until I got to version 11, where the version numbers sorted like this - vers_id
0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9, I was expecting 0, 1, 2, 3, 4, ....
I'm thinking that I need another sort clause but sure how to integrate it into my case
But then I could be wrong, and my case statements may just need to be better.
Everything I tried failed. Looking for help on this to keep my customers project clean.
SELECT
Proj_Stage,
vers_id,
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)<br />
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3<br />
END
I tried this
END, vers_id
And I tried this
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2, vers_id DESC
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Just a stab in the dark, but you can Cast/Convert vers_id to a int. Cast(vers_id as int) in the order by should do what you want. Assuming vers_id is will always convert to an int.
varchars will be sorted in the way you are experiencing.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Great idea!
I just checked the table and vers_id was set to char.
Just crafted this and it does what I want now. I didn't think of that, where it sorted as chars and not numbers.
Good eye, or stab in the dark, very helpful and spot on.
SELECT
Proj_Stage,
CAST(vers_id AS int),
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)<br />
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3<br />
END, CAST(vers_id AS int) ASC
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Off topic - I would assume that "Proj_Stage" would be in another table and should have a sequence no in that table, this would eliminate the case statement in the where clause.
If it is input text then you have another problem altogether!
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm stuck with the existing database to make it backwards capable, with older construction projects. Every record has a ...
project number
project stage
version number
This table keeps track of the versions of the construction project. As typical, customers that have construction done always change their mind during the construction process, and don't understand once you pull a permit from the city, you can't change the project. But they change it anyways and then you have to get a new permit.
I didn't get any documentation, nor useful comments and had to reverse engineer the old version to make this new version of the software. And it was very broken and needed fixing, thus below ...
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Even if you cannot change the existing tables in the database, you should be able to create new entities.
I'd still create another table with 2 columns in it as suggested by @Mycroft Holmes[^] as suggested above[^] (cols: Proj_Stage and SortSequence), indexed by Proj_Stage. Then
LEFT JOIN ProjStageSortSequence AS ps ON proj_vers.Proj_Stage = ps.Proj_Stage and change the ORDER BY to be
ORDER BY ps.SortSequence, vers_id . You would also need to change Proj_Stage in the SELECT to proj_vers.Proj_Stage. These changes will save loads of CONVERT() operations and should (not tested) run faster as SQL SERVER is very good at optimizing joins with low nos of rows.
This leaves the existing table unaltered and would be more efficient that the query it already has. It would not be as efficient or as normalised as using the sort sequence in the existing table; but it is a stepwise improvement.
|
|
|
|
|
Took a few minutes for me to absorb, but I get it now, and that's an enhancement that would be backwards compatible. I would be able to apply this new table to every other table that uses the project stages, which is pretty much about 15 more tables.
I'll dabble with this tonight at home in my new home office. Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your Awesome! 
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your welcome. Been there done that as they say.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Presumably no one is ever going to use version number 1.1 or even 1.0
|
|
|
|
|
Just whole integers, maybe up to 20 if the customer gets real bad, or excited about their swimming pool while it's being built.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions.
What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures.
This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum.
Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well.
I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient.
What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon.
something like this
select * from people
where if @fNameActive then fname like @fNameParam
and if @dobActive then dob = @dobParam
Otherwise name and dob should not be evaluated.
I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case.
Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well.
Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.
So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner?
I can't be the only developer to run up against this wall. But it's so complicated that it's difficult to formulate an effective www search.
Could I have coded this? Just saw something similar on Stack Overflow.
SELECT * FROM people
WHERE
( @fNameActive = 1 AND fname like @fNameParam )
AND
( @dobActive = 1 AND dob = @dobParam)
It looks to me like the only way to get it to work is by omitting the column. I actually tried this on MySQL and it failed. If one of the () were false because of my simulated "activate test" I just coded 1=0 it returned no rows.
How about...
SELECT * FROM people
WHERE
( @nameactive = 1 AND name like @nameparam )
OR
( @dobActive = 1 AND dob = @dobParam)
Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
I ask this question because I'm curious. I have no problem using parameterized concats on my home projects.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
