My company switched our business software a couple years ago. We sometimes still need to access the old software, but it is now being discontinued and going away. They sent us a copy of our data with 2 files, a .db and a .log. According to the company, the files are in a "MySQL format".

I worked in software and light programming 20 years ago including with SQL Server, SQL statements, Access, MySQL, and a couple others I can't remember...so I'm not totally ignorant about this sort of thing and I can generally figure out with some Google searches what I need to do to access something like this.

I've tried DBeaver, SQLLiteStudio, MySQL Server/Workbench, and DB Browser and cannot access it. Get this "File is not a database" error in most of them. Tried changing the file extension to .sql but that didn't work either.

The company will not help me and just told me that that's all they can do and that this is the same format they send to other companies that integrated with their software and there was never a problem.

Any ideas what I'm missing or what I might try to access this file? My goal is to access the data and create a simple interface so we can view some of the information in it like client records and history.

I wonder how could I professionally and efficiently document a database. I have a bunch of postgreSQL databases. I would like to document them and search for the different methods people use. I came with this question on stackoverflow. And there are two questions appeared in my mind:

1- Is there really a specification for database documenting? Any specified formatting, method, rule, etc?

2- Why there is so much tools while you can easily comment your tables & fields inside postgreSQL? Sure, if you have multiple different DBMs (postgreSQL, msSQL, mongo, Cassandra ...) and would like to document them in a single, it is better to stick with single documentation method. I don't think most startups use multiple DBMs, but in the link above, there is only single person suggesting commenting.

I am currently using PostgreSQL but have earlier used MSSQL and MySQL for many years. I'm dead tired of SQL as a language. Sure, very convenient for low and medium complexity queries, but a nightmare for highly complex queries and very hard to debug due to its declarative nature. You never know exactly what happens in the execution.

But I like relational databases (schemas, indexes, constraints and foreign keys). They map very well to how I think about data in general. So I hope to avoid working with key-value stores, document databases, or object databases.

So I'm thinking that someone is probably as fed up as me and has written an extension to PostgreSQL where you bypass SQL entirely. But I haven't found any. I want a Javascript API similar to the one MongoDB uses. But one that doesn't get translated to SQL behind the scenes, because that will set a serious limitation on how flexible that API can be. A Javascript API that talks directly to the low level libraries of PostgreSQL.

I could switch to MongoDB I guess. It is well known and robust. I like the API. But it is a document database with BSON/JSON entries, which means a lot of redundant data and lower performance even when you use schemas and carefully constructed indexes. I might accept that.

Do you have any suggestions?

• Robust database, high performance, can handle large datasets, for a backend server
• Has a Javascript query API that does not resemble SQL in the slightest, not even reliant on SQL, where I can put the Javascript on the server itself (stored procedure) and set breakpoints.

I found Realm from MongoDB which looks exactly like what I want. But it is designed for mobile, so I'm weary to take a chance with on a server backend.

Hi all, I’m trying to write a Petri net system, part of which needs to check if something is rented out or not for availability. I don’t know if this is the subreddit for it, but the one for Petri is completely dead, so I’m gonna ask here.

hi guys! what is your opinion? which is the most recent database management system in engineering-related topics? MySql, Microsoft Access, SQL Server, MariaDB, Oracle, SQLite, CouchDB, and MongoDB or generally, which might have the most power in the future?

Is there a database that's available online (free or paid) that has a list of all items sold on Walmart, Kroger, Target, etc's online websites? Ideally a breakdown of all SKUs with categories and sub-categories listed.

New to database work here. Using a program called Skyward and am kinda left to myself to figure it out. The modules on its site from what I've seen are quite unhelpful in terms of what I actually need to know since there's so much going on; what are some essential paths in the program or even just some basic things I should know?

https://preview.redd.it/wqjgwufyswrd1.png?width=859&format=png&auto=webp&s=7a08a4becda2b035ec8d98e7490860b09c66cf16

Hi everyone, may you kindly assist. I am 3rd year Computer Science Student (Bachelor's) and one of my final modules is titled Database Fundamentals. The book in the picture is one of the resources that we are using, I have never done anything with Database related. I have been looking for free courses on YouTube but I feel like I am not finding the right ones (I watched an hour of this one https://www.youtube.com/watch?v=4cWkVbC2bNE&t=1889s ), for finals I am required to submit a Database related Project (MySQL). May you kindly recommend the right courses to watch that will help me understand better, also where I can learn SQL. Thank you.

I am trying to figure out a way or line of thinking to determine if an attribute should belong to an entity, or rather the relationship that connects an entity to another. An example:

Customer (entity) places (relationship) order (entity).

Would such entities of (specified quantity of an item, date of receipt, expected ship date, actual ship date, price) be attributes of the relationship (places) or belong to the order entity?

I am sharing my latest Medium article that covers Distributed table engine and distributed tables in ClickHouse. It covers creation of distributed tables, data insertion, and query performance comparison.

Read here: https://medium.com/@suffyan.asad1/scaling-clickhouse-achieve-faster-queries-using-distributed-tables-1c966d98953b

ClickHouse is a fast, horizontally scalable data warehouse system, which has become popular due to its performance and ability to handle big data.

I'm currently facing an issue connecting to a client’s on-premise Microsoft SQL Server 2005. I can successfully connect to their server, but the login process fails and doesn’t proceed.

After some investigation, I found out that their server is still using TLS 1.0, which is likely part of the issue since many modern applications and services no longer support it.

Has anyone encountered a similar problem or know of a workaround for this? I'm looking for advice on how to proceed or if there are specific steps I should take to resolve this issue.

Any help would be greatly appreciated! Thanks in advance

hi i'd like to ask why do i hear ribbit noises whenever he's working.. i assume it's a database thing because my dad works in databases but when i search "database ribbit" up on google nothing works.. so i'd like to ask why is there ribbit noises? i can't ask him because when i do he just gives me an answer of like he's busy and databases and all that.. please someone help thank you

As I mentioned in the title, I want to read the book Designing Data-Intensive. But my English is not very good, I am just learning, so I will translate the document with DeepL. Will I lose a lot of meaning?

https://preview.redd.it/59h72m67g5rd1.png?width=1091&format=png&auto=webp&s=b85c6f0c0a3dfd13e1c23c970852962d99cbf49b

I do some researching on Internet and I find these source about Circular References on stack overflow and internet said that Circular References is bad.

But when reading I find these source really contradictory each other because the same diagram was said it is Circular References in this source but another source said it don't. That make me very confuse so may I asked does my DB is Circular References or not ? How can I knowing a DB have Circular References or not ?
And if it is, then how can solve it ?
These are those source that I reading:
https://www.codeproject.com/Articles/38655/Prevent-Circular-References-in-Database-Design
https://medium.com/akurey/dont-be-circular-b59c5609d472
https://stackoverflow.com/questions/30742345/is-it-always-a-bad-practice-to-have-circular-relationships-in-your-database-desi

I posted this yestreday in r/MongoDB but couldn't get any replies.

I have been designing a flashcard application and also reading a bit about database design (very interesting!) for a hobby project.

I have hit an area where I can't really make a decision as to how I can proceed and need some help.

The broad structure of the database is that there are:

A. Users collection (auth and profile)

B. Words collection to be learned (with translations, parts of speech, a level, an order number in which they are learned)

C. WordRecords collection of each user's experiences with the words: their repetitions, ease factor, next view date, etc.

D. ContextSentences collection (multiple) that apply to each word: sentences and their translations

• Users have a one to many relationship with Words (the words they've learned)
• Users have a one to many relationship with their WordRecords (learning statistics for each word in a separate collection)
• Words have a one to many relationship with with WordRecords (one word being learned by multiple users)\
• Words have a one to many relationship with their ContextSentences of which there can be multiple for each word (the same sentences will not be used for multiple words)

I have a few questions and general issues with how to structure this database and whether I have identified the correct collections / tables to use

1. If each user has 100s or 1000s of WordRecords, is it acceptable for all those records to be stored in the same collection and to retrieve them (say 50 at a time) using the userId AND according to their next interval date. Would that be too time consuming or resource intensive?
2. Is the option of storing all of a user's WordRecords in the user's entry, say as an array of objects for each word worth exploring or is it an issue storing hundreds or thousands of objects in a single field?
3. And are there any general flaws with the overall design or improvements I should consider?

Thank you

Me and my friend are in the process of creating an ER diagram for a professional soccer league, we are both very new to Database so we aren't sure if everything we do is correct.

When creating a relationship between a manager and a club, he made this: https://imgur.com/a/EpokkSM

I'm quite confused about why the 2 relationships managedBy and signsWith are Many to Many. My friend said that multiple managers can manage multiple clubs, one-on-one at a time. But shouldn't that be One to One instead? Since at any moment in time, a club can only have a single manager managing it and a manager can only have a single club to manage (we do not care about assistant manager in this instance); same thing about the signsWith relationship.

So which one of us is right?

I'm using H2 2.3.232 (2024-08-11) in server mode on Windows 11.

Lately I've figured out how to use generatedColumnExpression and I'm pretty happy with it.

This is the ddl for my table "URLS":

CREATE TABLE 
    "URLS" 
    ( 
        "ID"          	INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, 
        "URL"         	CHARACTER VARYING(128) NOT NULL, 
        "WHENCREATED" 	TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
        "VALID"       	BOOLEAN DEFAULT 'FALSE' NOT NULL, 
	"TIMESTAMP"   	TIMESTAMP GENERATED ALWAYS AS 
		CASE WHEN REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 1) IS NOT NULL THEN 
			CONCAT(
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 1),'-',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 2),'-',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 3),' ',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 4),':',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 5),':00'
				)
			ELSE
				null
		END,
        "BRAND" 	ENUM('eversolo', 'zidoo') NOT NULL GENERATED ALWAYS AS (REGEXP_SUBSTR("URL", '(eversolo|zidoo)', 1, 1, NULL, 1)), 
        "MODEL"   	CHARACTER VARYING(10) GENERATED ALWAYS AS (REGEXP_SUBSTR("URL", '^.*\/.*?\/(.*?)_.*?_(.*?)_.*.zip$', 1, 1, NULL, 1)), 
        "VERSION" 	CHARACTER VARYING(10) GENERATED ALWAYS AS (REGEXP_SUBSTR("URL", '^.*\/.*?\/(.*?)_.*?_(.*?)_.*.zip$', 1, 1, NULL, 2)), 
        PRIMARY KEY ("ID") 
    );

This is an example for a URL https://music.eversolo.com/dmp/ota/DMP_A6/v1.3.29/DMP-A6_R_v1.3.29_202409231613_ota-package.zip

1. How do I alter an existing generatedColumnExpression, except drop and recreate?

I'd like to alter the generatedColumnExpression for the column TIMESTAMP to

		CASE WHEN REGEXP_SUBSTR("URL", '^.*_([\d]{12}).*.zip$', 1, 1, NULL, 1) IS NOT NULL THEN 
			CONCAT(
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 1),'-',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 2),'-',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 3),' ',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 4),':',
				REGEXP_SUBSTR("URL", '^.*_([\d]{4})([\d]{2})([\d]{2})([\d]{2})([\d]{2}).*.zip$', 1, 1, NULL, 5),':00'
				)
			ELSE
			null
		END,

2. Is it possible to use user variables in the generatedColumnExpression?

In a select statement I'm able to write

SELECT 
    SET(@TIMESTAMP_, REGEXP_SUBSTR("URL", '^.*_([\d]{12}).*.zip$', 1, 1, NULL, 1)) as t, 
    CASE WHEN @TIMESTAMP_ IS NOT NULL THEN
        CONCAT(
            SUBSTRING(@TIMESTAMP_, 1, 4),'-',
            SUBSTRING(@TIMESTAMP_, 5, 2),'-',
            SUBSTRING(@TIMESTAMP_, 7, 2),' ',
            SUBSTRING(@TIMESTAMP_, 9, 2),':',
            SUBSTRING(@TIMESTAMP_, 11, 2),':00'
        )
    ELSE
        null
    END AS "TIMESTAMP"
FROM "URLS";

Is something like this possible in a generatedColumnExpression?

I finished my PhD in computer science and as I am very interested in databases I wonder whether there are jobs for me in this field? Do you know somebody that works in the database industry with a PhD?

Hi !
For a SaaS that has many users and many external API requests but doesn't need lot of ROM (no upload, videos, pictures...) , how to best optimise pricings when it comes to hosting + database  ?

For the moment i'm going full supabase, especially for their unlimited api calls function which i find great, But i heard that using SQLite and hosting can spare you the price of the database since it's 'serverless', I'm not experienced so idk.

I was also full vercel till I saw this video about free self hosted Coolify alternative (https://www.youtube.com/watch?v=hl8ebudhqZU)

I know there are lot of tricks and ways we can really reduce the price, what's the go to option for a limited budget ? .

Is SQLite worth it ? vercel ? is supabase a go to choice for lot of API Calls ?
Thanks !

I'm currently into creative journaling and scrapbooking. The supplies (washi tapes, papers, ink, markers, stickers, etc.) I've amassed is getting too large that I'm failing to maximize my collection by forgetting what I previously bought - I usually only remember the recent ones.

I have this idea of maintaining a catalog / database of sorts for all my supplies, containing the visual swatches / samples of each, and tagging them individually either through AI or manually with various applicable keywords like "neon", "pastel", "stripes", "pattern", "Disney", or "hearts" and hex codes of the color(s) for each.

Usage can be as simple as knowing if I already have a similar color / pattern so I can save on buying doubles. Or can be as powerful as integrating with a color scheme creator and matching them with the supplies you currently have - you can easily preview without scrambling through your collection. Plus points if you can catalog the specific supply to a location (like a box or a drawer with a code) for easier retrieval.

Is there a database / database app that can at least help cataloging my collection? I think I need:

• A database that supports images for the swatches
• A database that allows creation of multiple fields associated to the image / swatch, to allow a description, tags, and other info
• A database where the search results display the swatches of the supplies

Used SQLite FTS on a 18GB table (well normalized), we've got the results in 0-3ms.

It is a file which changes every month, we import it using some text files to create the table and normalize them.

Breakdown:

• around 200 M rows,
• added index to specific columns for query.

We initially used a left join with LIKE operator to find what we needed, but with trial and error (using EXPLAIN QUERY PLAN), we ended up with CTE and FTS5. Here is a gist:

Query:

used a mixture of CTE with join.

WITH search_results as (
   select oid from that_table MATCH '...*';
)
SELECT * from other_table... join ...
where id in ( select oid from search_results);

TLDR; SQLite is amazing !

First let me say I am not a database or even a server hardware guy, my background is mainly networking. I am contending with a problem where an Oracle database sync traffic using encryption has latency. I know in the networking side, if you want to do proper and speed encryption, you usually need some encryption accelerator card to offload the encryption/decryption work. I tried to do some research as to what could accelerate database encryption, and best I could find was that there was some built in acceleration built into Xeon Gen 3 processors. So my question is this...

Have you ever encountered slowness with encryption, if so then how did you resolve it? Did you use Xeon processes to solve the latency or some other solution?

I'm curious about the current landscape for database development roles. While I understand the importance of SQL skills in many companies, I'm wondering if the specific title of Database/SQL Developer is still common or if it's been replaced by broader roles like DBA, DB Engineer, BI/BA, or Data Science.

I have experience with MySQL, PostgreSQL, and am currently learning SQL Server. I enjoy working with databases and data manipulation. I'm looking for guidance on what titles or positions I should be targeting in my job search, given the ever-evolving landscape of data-related roles.

I've been actively researching database development roles and have come across some listings for Database/SQL Developers. However, I've noticed that titles like DBA, DB Engineer, BI/BA, and Data Science seem to be more prevalent. I'm curious about the overall trends and whether there's a significant shift away from the traditional Database/SQL Developer role.